Skip to content
Engineering

Entity Relationship Diagram - Core Schema

The core schema encapsulates the sovereign multi-tenancy foundation, IAM identity models, external OAuth federations, delivery logistics addresses, and distributed auditing logs for the GERCIA SaaS platform, hosted on Google Cloud SQL for PostgreSQL.

1. Core Relational Model

Section titled “1. Core Relational Model”
erDiagram
    TENANTS ||--o{ BRANCHES : "has operational units"
    TENANTS ||--o{ TENANT_USER_ACCESSES : "grants access"
    TENANTS ||--o{ AUDIT_HISTORY : "audits"
    BRANCHES ||--o{ TENANT_USER_ACCESSES : "scopes membership"
    USERS ||--o{ TENANT_USER_ACCESSES : "holds roles"
    USERS ||--o{ USER_LOGINS : "links OAuth identities"
    USERS ||--o{ USER_ADDRESSES : "maintains delivery addresses"
    ROLES ||--o{ TENANT_USER_ACCESSES : "defines permissions"
    USERS ||--o{ AUDIT_HISTORY : "triggers mutations"

    TENANTS {
        id uuid PK "UUIDv7 Temporal Primary Key"
        corporate_name varchar "Legal corporate entity name (Razao Social)"
        trade_name varchar "Commercial brand name (Nome Fantasia)"
        type varchar "Tenant tier (Standard, Professional, Enterprise)"
        cnpj varchar "Federal Corporate Tax ID"
        logo_url varchar "Tenant branding logo URL (up to 2048 chars)"
        is_individual boolean "Individual practitioner flag"
        is_active boolean "Tenant subscription active flag"
        version integer "Optimistic concurrency token"
    }

    BRANCHES {
        id uuid PK "UUIDv7 Temporal Primary Key"
        tenant_id uuid FK "FK to parent Tenant boundary"
        name varchar "Operational branch name"
        code varchar "Internal branch code (e.g. MATRIZ, FIL-01)"
        cnpj varchar "Branch specific CNPJ tax registration"
        anvisa_license_number varchar "ANVISA Sanitary Permit number"
        is_main_branch boolean "Primary headquarters branch flag"
        is_virtual_branch boolean "Virtual or telehealth branch flag"
        is_active boolean "Branch operational active flag"
    }

    USERS {
        id uuid PK "UUIDv7 Temporal Primary Key"
        cpf varchar "Citizen Tax ID (11 sanitized digits)"
        full_name varchar "Official legal civil name"
        social_name varchar "Preferred social name"
        birth_date date "Date of birth"
        email varchar "Canonical lowercase login email (Unique)"
        email_confirmed boolean "Email verification flag"
        phone_number varchar "Personal mobile telephone number"
        phone_number_confirmed boolean "Mobile phone verification flag"
        password_hash varchar "Argon2id cryptographic password digest"
        avatar_url varchar "Cloud Storage or OAuth profile picture URL (up to 2048 chars)"
        timezone varchar "User timezone preference (America/Sao_Paulo)"
        two_factor_enabled boolean "MFA enforcement flag"
        two_factor_secret varchar "Encrypted TOTP RFC 6238 shared secret (AES-GCM)"
        is_active boolean "User account active flag"
    }

    USER_ADDRESSES {
        id uuid PK "UUIDv7 Temporal Primary Key"
        user_id uuid FK "FK to Master User record"
        label varchar "Address label (Principal, Casa, Trabalho)"
        is_default boolean "Default delivery address flag"
        street varchar "Thoroughfare name (street, avenue)"
        number varchar "Building or street number"
        complement varchar "Address complement (Apt, Bloco)"
        neighborhood varchar "District or neighborhood"
        city varchar "City name"
        state varchar "Two-letter Brazilian state code (UF)"
        zip_code varchar "Postal code (CEP - 8 digits)"
        ibge_city_code varchar "7-digit IBGE city code"
        reference_point varchar "Delivery landmark reference"
    }

    ROLES {
        id uuid PK "UUIDv7 Temporal Primary Key"
        name varchar "Human-readable role name"
        normalized_name varchar "Uppercase normalized role identifier"
        description varchar "Granted permissions summary"
    }

    TENANT_USER_ACCESSES {
        id uuid PK "UUIDv7 Temporal Primary Key"
        tenant_id uuid FK "FK to Tenant boundary"
        branch_id uuid FK "Optional FK to specific Branch scope"
        user_id uuid FK "FK to Master User record"
        role_name varchar "Assigned RBAC role identifier"
        is_active boolean "Membership access active flag"
    }

    USER_LOGINS {
        id uuid PK "UUIDv7 Temporal Primary Key"
        user_id uuid FK "FK to Master User record"
        login_provider varchar "External provider (Google, Apple, Microsoft)"
        provider_key varchar "Subject ID from OAuth provider"
        provider_display_name varchar "Friendly external account name"
    }

    AUDIT_HISTORY {
        id uuid PK "UUIDv7 Temporal Primary Key"
        tenant_id uuid "Tenant boundary identifier"
        branch_id uuid "Branch boundary identifier"
        user_id uuid "Operator user UUID (or System actor)"
        entity_name varchar "Audited domain entity class name"
        entity_id uuid "Primary key of modified record"
        action varchar "DML action (Added, Modified, Deleted)"
        property_name varchar "Modified property name"
        old_value text "Property value prior to modification"
        new_value text "Updated property value after mutation"
        created_at timestamptz "Audit event timestamp in UTC"
    }

2. Core Architecture Rules & Patterns

Section titled “2. Core Architecture Rules & Patterns”

1. Multi-Tenant Boundary Enforcement

Section titled “1. Multi-Tenant Boundary Enforcement”
  • Every organizational entity contains tenant_id and optional branch_id.
  • Isolation is enforced via EF Core Global Query Filters and strictly backstopped by PostgreSQL Row-Level Security (RLS) using SET LOCAL app.current_tenant_id = '...'.

2. Master Civil Identity & Decoupled Logistics

Section titled “2. Master Civil Identity & Decoupled Logistics”
  • core.users represents the universal Master Civil Identity across all Monorepo domains (connecting Doctors, Clinic/Pharmacy Staff, and Patients).
  • Physical logistics and delivery addresses are decoupled into core.user_addresses (1:N), allowing patients to manage multiple delivery destinations (“Casa”, “Trabalho”) for compounded medication fulfillment without polluting the primary authentication table.

3. Time-Sorted UUIDv7 Standard

Section titled “3. Time-Sorted UUIDv7 Standard”
  • All tables utilize sequential UUIDv7 (Guid.CreateVersion7()) as their primary keys. This ensures temporal sorting on insertion and eliminates B-Tree index fragmentation inside PostgreSQL.

4. Metadata Column Ordering Standard (0 to 10)

Section titled “4. Metadata Column Ordering Standard (0 to 10)”
  • All platform tables reserve physical column indices 0 to 10 for standard auditing and tenancy metadata (id, created_at, created_by, last_modified_at, last_modified_by, is_deleted, deleted_at, deleted_by, version), with business-specific properties starting at position 11+.

5. Cryptographic Security & LGPD Compliance

Section titled “5. Cryptographic Security & LGPD Compliance”
  • Sensitive security credentials (such as two_factor_secret) are stored using authenticated AES-256-GCM encryption via IEncryptionProvider / [Encrypted]. Passwords enforce Argon2id cryptographic hashing.

6. Referential Integrity & Physical Foreign Key Constraints

Section titled “6. Referential Integrity & Physical Foreign Key Constraints”

In accordance with the platform’s relational integrity principles, all relationships within the core schema enforce physical foreign key constraints at the database engine level with ON DELETE RESTRICT:

  • core.branches.tenant_id $\rightarrow$ core.tenants.id: Guarantees that an operational branch cannot be created without an existing corporate tenant, and active tenants cannot be physically dropped while child branches exist.
  • core.tenant_user_accesses.tenant_id $\rightarrow$ core.tenants.id: Binds memberships strictly to valid corporate tenants.
  • core.tenant_user_accesses.user_id $\rightarrow$ core.users.id: Binds memberships strictly to valid master civil identities.
  • core.tenant_user_accesses.branch_id $\rightarrow$ core.branches.id: Optional FK scoping access to a specific branch unit.
  • core.user_addresses.user_id $\rightarrow$ core.users.id: Delivery logistics records strictly cascade from valid master user identities.
  • core.user_logins.user_id $\rightarrow$ core.users.id: Federated identity links (Google, Apple, Entra ID) strictly cascade from valid master user identities.
  • Zero Navigation Properties in Domain Entities: To preserve DDD Reference by Identity, all foreign keys are mapped using EF Core shadow relationships (builder.HasOne<T>().WithMany().HasForeignKey(...).OnDelete(DeleteBehavior.Restrict)), preventing in-memory aggregate graph bloat.