Master Civil Identity & Multi-Provider Logins

1. Domain Modeling: Subject vs. Identity Provider

The GERCIA Platform decouples who a person is (Master Civil Identity / Subject) from how they authenticate (Identity Provider / IdP) and where they receive deliveries (Addresses / Logistics).

erDiagram
    USER ||--o{ USER_LOGIN : "has multiple logins (1:N)"
    USER ||--o{ USER_ADDRESS : "has multiple delivery addresses (1:N)"
    USER ||--o{ TENANT_USER_ACCESS : "has roles across tenants (1:N)"
    
    USER {
        Guid Id PK "Guid v7 temporal key"
        string FullName "Legal civil name"
        string SocialName "Preferred name"
        DateOnly BirthDate "Date of birth"
        string Cpf "Citizen Tax ID (11 digits)"
        string Email "Canonical lowercase login email"
        string PhoneNumber "Mobile phone"
        bool PhoneNumberConfirmed "Phone verification flag"
        string PasswordHash "Argon2id hash (optional)"
        bool TwoFactorEnabled "TOTP RFC 6238 flag"
        string TwoFactorSecret "Encrypted AES-GCM TOTP secret"
        string Timezone "User timezone (default America/Sao_Paulo)"
        bool IsActive "Account status"
    }

    USER_ADDRESS {
        Guid Id PK "Guid v7 key"
        Guid UserId FK "Reference to core.users"
        string Label "Address label (Principal, Casa, Trabalho)"
        bool IsDefault "Default delivery address flag"
        string Street "Street name"
        string Number "Street number"
        string Complement "Complement"
        string Neighborhood "Neighborhood"
        string City "City name"
        string State "2-letter UF"
        string ZipCode "8-digit CEP"
        string ReferencePoint "Delivery landmark"
    }

    USER_LOGIN {
        Guid Id PK "Guid v7 key"
        Guid UserId FK "Reference to core.users"
        string LoginProvider "Google, Apple, Microsoft, GovBr"
        string ProviderKey "External subject ID (sub)"
        string ProviderDisplayName "Google Account, Apple ID"
    }

    TENANT_USER_ACCESS {
        Guid Id PK
        Guid UserId FK
        Guid TenantId FK
        Guid BranchId FK "Optional branch scope"
        Guid RoleId FK
        bool IsActive
    }

---

2. Advantages of the 1:N UserLogin Architecture

1. Multi-Platform Single Identity: The same user can log in via Google on Android, Apple ID on iOS, and Email + Password on Web, resolving to the exact same UserId.
2. Future-Proof Extensibility: New login providers (such as Gov.br, Microsoft Entra ID, or SAML SSO) can be added with zero schema migrations or changes to core.users.
3. Clean Account Linking: Users can manage their connected authentication providers directly from their profile (“Connected Accounts: [x] Google, [ ] Apple”).

---

3. Advantages of the 1:N UserAddress Architecture

1. Ultra-Lean Identity Table: core.users contains zero logistical bloat, maximizing buffer cache density and authentication query speed.
2. Multi-Delivery E-Commerce Readiness: Patients can maintain multiple delivery destinations (“Casa”, “Trabalho”, “Consultório”) for pharmacy compounding orders without overwriting their primary residence.
3. Zero Waste for B2B: Corporate clinicians, pharmacists, and operations staff maintain 0 address records, completely eliminating dead columns in the identity table.