Entity Relationship Diagram - Core Schema

The core schema encapsulates the sovereign multi-tenancy foundation, IAM identity models, external OAuth federations, and distributed auditing logs for the GERCIA SaaS platform, hosted on Percona Distribution for PostgreSQL.

---

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"
    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"
        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 (Encrypted AES-256 + Blind Index)"
        full_name varchar "Official legal civil name"
        email varchar "Primary authentication and login email (Unique)"
        phone_number varchar "Mobile contact telephone number"
        password_hash varchar "Argon2id cryptographic password digest"
        timezone varchar "User timezone preference (America/Sao_Paulo)"
        preferred_language varchar "Culture code (pt-BR / en-US)"
        two_factor_enabled boolean "MFA enforcement flag"
        is_active boolean "User account active flag"
        is_account_claimed boolean "Account onboarding completion flag"
    }

    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

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. 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.

3. 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+.

4. Cryptographic Security & LGPD Compliance

• Sensitive PII fields (such as CPF) are stored using AES-256-GCM authenticated encryption with an HMAC-SHA256 blind index for exact-match querying without full-table decryption.