Skip to content
Engineering

Healthcare Verticals Domain Models & ERD

GERCIA organizes its flagship industry product not as a single monolith, but as an extensible Healthcare Verticals Ecosystem (Ecossistema de Verticais de Saúde). The platform foundation (Foundation/) hosts specialized healthcare domain modules that operate as isolated Bounded Contexts, each mapped to an exclusive PostgreSQL schema (clinical, pharmacy, dental, veterinary, and future planned verticals such as diagnostics). Inpatient hospital ERP systems are strictly out of scope.

The inaugural production deployment triangulates between the Clinical Vertical (Gercia.Clinical), the Compounding Pharmacy Vertical (Gercia.Pharmacy), and the universal Patient Mobile Companion (GERCIA Care - apps/mobile/). In accordance with hexagonal architecture principles, these verticals share no direct project dependencies and interact exclusively via domain events and sovereign API gateways.

1. Clinical Bounded Context (clinical Schema)

Section titled “1. Clinical Bounded Context (clinical Schema)”

The clinical schema is responsible for patient master records, medical charts, healthcare provider credentials, and professional council compliance.

Core Aggregates & Domain Invariants

Section titled “Core Aggregates & Domain Invariants”
  1. Patient Aggregate Root:
    • Minor Guardianship Invariant: In accordance with the Brazilian Civil Code and LGPD, if a patient’s birth date indicates an age under 18 years, the GuardianName and validated GuardianCpf are mandatory invariants for medical record creation.
    • Sanitization & Masking: CPFs and CNS (Cartão Nacional de Saúde) numbers are sanitized into unpunctuated numeric strings upon entry.
    • Multi-Channel Consent: Explicit communication consent flags (AllowWhatsAppNotifications, AllowSmsNotifications, AllowEmailNotifications) are managed at the patient aggregate root level.
  2. HealthcareProfessional Aggregate Root:
    • Council Credentials: Tracks council type (CRM, CRO, CRMV, COREN, CRF), registration number, and state of issuance.
    • RQE Qualification: Specialty registration (Registro de Qualificação de Especialista - RQE) ensures regulatory compliance when issuing prescriptions for specialized treatments.
    • ICP-Brasil Digital Signature: Stores the SHA-1/SHA-256 thumbprint, subject, issuer, and expiration date of the professional’s digital certificate (A1 or A3 token) for signing digital prescriptions compliant with CFM Resolution 2.299/2021.

Entity-Relationship Diagram: clinical Schema

Section titled “Entity-Relationship Diagram: clinical Schema”
erDiagram
    %% Entities
    patients {
        uuid id PK "UUIDv7 (Default / Time-Ordered)"
        uuid tenant_id FK "Tenant Isolation (Indexed)"
        uuid user_id FK "Optional Civil Identity Link"
        uuid insurance_provider_id FK "Health Plan Carrier"
        varchar medical_chart_number "Unique within Tenant"
        varchar full_name "Legal Patient Name"
        varchar social_name "Preferred Social Name"
        varchar cpf "Sanitized 11 Digits"
        varchar cns_number "Cartao Nacional de Saude (15 digits)"
        date birth_date "Date of Birth"
        int biological_sex "0=Unspecified, 1=Male, 2=Female"
        int gender_identity "0=Unspecified, 1=Cis, 2=Trans..."
        int race_color "IBGE Classification"
        int marital_status "Civil Marital Status"
        int blood_type "ABO and Rh Factor"
        int disability_type "Disability Classification"
        varchar guardian_name "Mandatory if Minor"
        varchar guardian_cpf "Validated Guardian CPF"
        text allergies "Known Adverse Drug Reactions"
        jsonb address "Value Object (Zip, Street, City...)"
        jsonb emergency_contact "Value Object (Name, Phone, Relation)"
        boolean allow_whatsapp "LGPD Communication Consent"
        boolean allow_sms "SMS Consent"
        boolean allow_email "Email Consent"
        boolean is_deceased "Deceased Flag"
        timestamptz deceased_at "Timestamp of Death"
        timestamptz created_at "Creation Timestamp"
        timestamptz last_modified_at "Last Modification Timestamp"
    }

    healthcare_professionals {
        uuid id PK "UUIDv7"
        uuid user_id FK "Civil Identity User ID"
        int council_type "1=CRM, 2=CRO, 3=CRF, 4=CRMV..."
        varchar council_number "Council Number"
        varchar council_state "UF (2 characters, e.g. SP)"
        varchar specialty "Primary Stated Specialty"
        varchar rqe_number "Specialist Registration Number"
        boolean is_digital_signature_enabled "ICP-Brasil Enabled"
        varchar digital_signature_thumbprint "Certificate SHA Hash"
        varchar certificate_subject "Subject DN"
        varchar certificate_issuer "AC Issuer DN"
        timestamptz certificate_expiration "Certificate Expiry"
        timestamptz created_at "Creation Timestamp"
    }

    specialties {
        uuid id PK "UUIDv7"
        varchar code "CBO / CFM Specialty Code"
        varchar name "Specialty Name (e.g. Cardiologia)"
        varchar council_type "Governing Council"
        boolean is_active "Active Status"
    }

    professional_specialties {
        uuid professional_id PK,FK "Healthcare Professional"
        uuid specialty_id PK,FK "Medical Specialty"
        varchar rqe_number "RQE for this Specialty"
    }

    insurance_providers {
        uuid id PK "UUIDv7"
        uuid tenant_id FK "Tenant Isolation"
        varchar ans_code "ANS Registration Code"
        varchar corporate_name "Razao Social"
        varchar trade_name "Nome Fantasia"
        varchar cnpj "14 Digits"
        boolean is_active "Active Status"
    }

    occupations {
        uuid id PK "UUIDv7"
        varchar cbo_code "CBO 6-digit Code"
        varchar title "Official CBO Title"
        boolean is_active "Active Status"
    }

    %% Relationships
    insurance_providers ||--o{ patients : "covers"
    healthcare_professionals ||--o{ professional_specialties : "holds"
    specialties ||--o{ professional_specialties : "categorizes"

Referential Integrity & Foreign Key Constraints

Section titled “Referential Integrity & Foreign Key Constraints”

In accordance with the platform’s relational integrity standards, intra-schema relationships enforce database-level physical foreign keys with ON DELETE RESTRICT:

  • clinical.professional_specialties.professional_id $\rightarrow$ clinical.healthcare_professionals.id: Prevents orphan specialty associations and protects registered professionals from deletion while linked to specialties.
  • clinical.professional_specialties.specialty_id $\rightarrow$ clinical.specialties.id: Prevents deletion of active medical specialties while referenced by healthcare practitioners.
  • clinical.patients.insurance_provider_id $\rightarrow$ clinical.insurance_providers.id: Optional FK binding patient coverage records to verified insurance providers.
  • Cross-Schema Decoupling (core.users): Relationships linking clinical.patients.user_id or clinical.healthcare_professionals.user_id to core.users.id deliberately remain logical references by identity (Guid) without physical foreign key constraints. This preserves bounded context autonomy, isolates migration locks, and avoids cross-schema DDL coupling.

2. Pharmacy Bounded Context (pharmacy Schema)

Section titled “2. Pharmacy Bounded Context (pharmacy Schema)”

The pharmacy schema manages the manufacturing lifecycle of customized medications, formula specifications, excipient calculations, sub-milligram weighing, and final pharmaceutical quality release.

Compounding Manufacturing Lifecycle

Section titled “Compounding Manufacturing Lifecycle”
flowchart TD
    RX["1. Digital Prescription Received<br>(Imported from Clinical or Uploaded by Patient)"] --> VAL["2. Pharmacotechnical Evaluation<br>(Pharmacist verifies dosage, interactions & stability)"]
    VAL --> OM["3. Generate Compounding Order (OM)<br>(Calculates batch size, capsule volume & excipient q.s.p.)"]
    OM --> WEIGH["4. Assisted Precision Weighing<br>(Real-time scale capture via Gercia.Edge)"]
    WEIGH --> BLEND["5. Homogenization, Encapsulation & Packaging<br>(Capsule fill, liquid dissolution, topical cream mix)"]
    BLEND --> QC["6. Quality Release & Physical Inspection<br>(Average weight test, visual inspection, pH verification)"]
    QC --> LABEL["7. Thermal Label Dispatch via /hubs/print<br>(ZPL / TSPL label adhering to Anvisa RDC 67/2007)"]
    LABEL --> READY["8. Ready for Patient Pickup / Courier Dispatch"]

Essential Compounding Invariants & Pharmacotechnical Calculations

Section titled “Essential Compounding Invariants & Pharmacotechnical Calculations”
  1. Moisture & Purity Correction Factors (Fator de Correção / Fator de Equivalência): When an active pharmaceutical ingredient (API) has a raw material certificate of analysis (CoA / Laudo de Análise) specifying moisture (teor de umidade) or purity less than 100%, Gercia.Pharmacy calculates the exact corrected weighing target: 
    Weight Target = Prescribed Dose × (100 / Purity %) × (100 / (100 - Moisture %))
  2. Excipient Volumetric Calculation (q.s.p.): For hard gelatin capsules (sizes 00, 0, 1, 2, 3, 4), the total bulk density of all active powders is computed against the capsule body volume. The system automatically computes the required volume of inert excipient (amido, celulose microcristalina, dióxido de silício) needed to completely fill the capsule body without settling.
  3. Assisted Weighing Tolerance Thresholds: According to the Brazilian Pharmacopoeia and Anvisa RDC 67/2007:
    • Substances of High Potency / Narrow Therapeutic Index (Faixa Terapêutica Estreita): Maximum deviation of ±1.0%.
    • Standard Active Ingredients: Maximum deviation of ±5.0%. Any weight reading outside this mathematical threshold prevents the operator from completing the weighing step in @gercia/app.

Entity-Relationship Diagram: pharmacy Schema

Section titled “Entity-Relationship Diagram: pharmacy Schema”
erDiagram
    compounding_orders {
        uuid id PK "UUIDv7"
        uuid tenant_id FK "Tenant Isolation"
        uuid branch_id FK "Physical Pharmacy Laboratory"
        varchar order_number "Human-Readable OM-2026-XXXX"
        varchar patient_name "Patient Legal Name"
        varchar patient_cpf "Patient Document"
        varchar prescriber_name "Doctor Full Name"
        varchar prescriber_crm "CRM and UF"
        int pharmaceutical_form "1=Capsule, 2=Cream, 3=Syrup..."
        int total_units "Number of doses (e.g. 60 caps)"
        int status "1=Draft, 2=Weighing, 3=Packaging, 4=Released"
        text usage_instructions "Posology Directions"
        timestamptz created_at "Order Creation"
    }

    compounding_order_items {
        uuid id PK "UUIDv7"
        uuid compounding_order_id FK "Parent Order"
        uuid raw_material_id FK "Raw Material Stock"
        varchar raw_material_name "Chemical Substance Name"
        varchar batch_number "Raw Material Internal Batch"
        decimal prescribed_quantity "Prescribed Active Dose"
        varchar unit_of_measure "mg, g, mcg, mL, UI"
        decimal purity_factor "Purity Multiplier"
        decimal moisture_factor "Moisture Multiplier"
        decimal target_weighing_grams "Calculated Target Weight"
        decimal actual_weighed_grams "Actual Recorded Weight"
        decimal deviation_percentage "Recorded Deviation %"
        boolean is_weighed "Weighing Completed Flag"
    }

    weighing_sessions {
        uuid id PK "UUIDv7"
        uuid compounding_order_item_id FK "Component Weighed"
        uuid station_id "Physical Compounding Station"
        uuid operator_user_id "Operator Civil Identity"
        varchar scale_serial_number "Analytical Balance Serial"
        decimal tare_weight_grams "Tare Weight"
        decimal final_net_weight_grams "Captured Net Weight"
        boolean was_stable "Captured Under Stable Flag"
        timestamptz captured_at "Hardware Timestamp"
    }

    quality_releases {
        uuid id PK "UUIDv7"
        uuid compounding_order_id FK "Compounding Order"
        uuid pharmacist_user_id "Responsible Pharmacist"
        varchar pharmacist_crf "Pharmacist CRF Number"
        decimal average_weight_deviation "Average Weight Test %"
        decimal ph_reading "Measured pH (if liquid/semi-solid)"
        int organoleptic_inspection "Color, Odor, Aspect"
        boolean is_approved "Final Pharmaceutical Approval"
        text rejection_reason "Reason if Disapproved"
        timestamptz released_at "Release Timestamp"
    }

    compounding_orders ||--o{ compounding_order_items : "contains"
    compounding_order_items ||--o{ weighing_sessions : "audits"
    compounding_orders ||--|| quality_releases : "finalizes"

3. Cross-Domain Triangulation Workflow

Section titled “3. Cross-Domain Triangulation Workflow”

The following sequence illustrates the end-to-end triangulation connecting all three platform participants:

sequenceDiagram
    autonumber
    actor DOC as Doctor (Clinical Web)
    actor PAT as Patient (Mobile App)
    actor PHARM as Pharmacist (Pharmacy Web & Edge)

    DOC->>DOC: Issues Digital Prescription in @gercia/app
    DOC->>DOC: Signs prescription with ICP-Brasil Digital Certificate
    Note over DOC: Prescription stored in clinical.prescriptions

    DOC-->>PAT: Push Notification: "New Digital Prescription Available"
    PAT->>PAT: Opens GERCIA Care Mobile App
    PAT->>PAT: Reviews formula & selects preferred Compounding Pharmacy

    PAT->>PHARM: Dispatches Order Request to Pharmacy
    PHARM->>PHARM: Technical Review & Accepts Prescription
    PHARM->>PHARM: Generates Compounding Order (OM)
    PHARM->>PHARM: Performs Assisted Weighing on Analytical Scale (Gercia.Edge)
    PHARM->>PHARM: Quality Release & Prints Anvisa RDC 67 Label
    
    PHARM-->>PAT: Push Notification: "Your Medication is Ready for Delivery"

4. Dental Bounded Context (dental Schema)

Section titled “4. Dental Bounded Context (dental Schema)”

The dental schema encapsulates the clinical and operational workflows of GERCIA Dental, serving dental clinics, specialized odontological centers, and oral surgery practices under CFO (Conselho Federal de Odontologia) and CRO standards.

Core Aggregates & Domain Invariants

Section titled “Core Aggregates & Domain Invariants”
  1. ToothSurface Value Object & Invariants:
    • Teeth are partitioned into five anatomical surfaces: Mesial (M), Distal (D), Occlusal/Incisal (O/I), Vestibular/Buccal (V), and Lingual/Palatal (L/P).
    • Enforces the international FDI / ISO 3950 two-digit numbering system: permanent dentition (11–48) and primary/deciduous dentition (51–85).
  2. PeriodontalChart Aggregate Root:
    • Encapsulates six-point circumferential probing depth measurements per tooth (Mesio-Buccal, Mid-Buccal, Disto-Buccal, Mesio-Lingual, Mid-Lingual, Disto-Lingual).
    • Tracks clinical attachment loss (CAL), bleeding on probing (BOP), plaque index, tooth mobility (Miller classification 0–III), and furcation lesions (Hamp classification I–III).
  3. DentalProcedurePlan Aggregate Root:
    • Links planned interventions (restorations, endodontics, implants, extractions, orthodontics) to specific teeth and surfaces, transitioning across states: Planned $\rightarrow$ Authorized $\rightarrow$ Completed.

Entity-Relationship Diagram: dental Schema

Section titled “Entity-Relationship Diagram: dental Schema”
erDiagram
    odontograms ||--o{ tooth_records : "maps teeth"
    tooth_records ||--o{ tooth_surfaces : "specifies surfaces"
    odontograms ||--o{ periodontal_charts : "records health"
    odontograms ||--o{ dental_procedure_plans : "schedules treatments"

    odontograms {
        uuid id PK "UUIDv7 (Default / Time-Ordered)"
        uuid tenant_id FK "Tenant Isolation (Indexed)"
        uuid patient_id FK "Link to clinical.patients"
        uuid professional_id FK "Operating Dentist (CRO)"
        int dentition_type "1=Permanent, 2=Deciduous, 3=Mixed"
        timestamptz created_at "Created Timestamp UTC"
    }

    tooth_records {
        uuid id PK "UUIDv7"
        uuid odontogram_id FK "Parent Odontogram"
        int tooth_number "ISO 3950 FDI Code (11-48, 51-85)"
        int status "1=Sound, 2=Carious, 3=Restored, 4=Missing, 5=Impacted, 6=Prosthetic"
    }

    tooth_surfaces {
        uuid id PK "UUIDv7"
        uuid tooth_record_id FK "Parent Tooth Record"
        int surface "1=Mesial, 2=Distal, 3=Occlusal, 4=Vestibular, 5=Lingual"
        int condition "1=Decayed, 2=Filled, 3=Fractured"
    }

    periodontal_charts {
        uuid id PK "UUIDv7"
        uuid odontogram_id FK "Parent Odontogram"
        date examination_date "Evaluation Date"
        int plaque_score "Plaque Index Percentage"
        int bleeding_score "Bleeding on Probing Percentage"
    }

    dental_procedure_plans {
        uuid id PK "UUIDv7"
        uuid odontogram_id FK "Parent Odontogram"
        int tooth_number "Target Tooth"
        varchar procedure_code "CFO / TUSS Procedure Code"
        varchar description "Clinical Procedure Name"
        int status "1=Planned, 2=InExecution, 3=Completed, 4=Canceled"
        decimal estimated_cost "Procedure Value (BRL)"
    }

5. Sovereign Veterinary Bounded Context (veterinary Schema)

Section titled “5. Sovereign Veterinary Bounded Context (veterinary Schema)”

The veterinary schema encapsulates the specialized healthcare domain of GERCIA Vet, operating sovereign web endpoints (app.gercia.vet.br) and public portals (gercia.vet.br) in strict compliance with the Ministério da Agricultura e Pecuária (MAPA) and the Federal Council of Veterinary Medicine (CFMV).

Core Aggregates & Domain Invariants

Section titled “Core Aggregates & Domain Invariants”
  1. AnimalPatient Aggregate Root:
    • Tutor-Patient Triangulation: Unlike human healthcare where the patient is a legal citizen with a CPF, veterinary care binds one or more animal patients to a Legal Tutor (core.users civil identity or validated CPF). The tutor bears financial, legal, and civil liability.
    • Species & Breed Taxonomy: Classifies patients under standardized biological taxonomy (AnimalSpecies: Canine, Feline, Equine, Bovine, Avian, Exotic) paired with controlled breed registries.
    • Weight-Based Ponderal Dosing: Tracks verified patient body mass in kilograms (WeightKg). Pharmacological calculations for magistral and alopatic prescriptions compute doses strictly per kilogram ($mg/kg$), preventing fatal subdosages or toxicities across divergent breeds (e.g., Chihuahua vs. Great Dane).
    • Microchip Identification: Stores ISO 11784/11785 15-digit electronic microchip transponder codes.
  2. VeterinaryPrescription Aggregate Root:
    • Conforms to MAPA / SIPEAGRO and Portaria SVS/MS 344/98 veterinary dispensing standards.
    • Stores veterinarian council credentials (CRMV with issuing state) and ICP-Brasil digital signature verification.

Entity-Relationship Diagram: veterinary Schema

Section titled “Entity-Relationship Diagram: veterinary Schema”
erDiagram
    legal_tutors ||--o{ animal_patients : "owns and cares for"
    animal_patients ||--o{ veterinary_consultations : "receives care"
    animal_patients ||--o{ veterinary_prescriptions : "dispenses"
    animal_patients ||--o{ weight_histories : "tracks mass"

    legal_tutors {
        uuid id PK "UUIDv7 (Default / Time-Ordered)"
        uuid tenant_id FK "Tenant Isolation (Indexed)"
        uuid user_id FK "Optional Civil Identity Link"
        varchar full_name "Legal Tutor Name"
        varchar cpf "Sanitized 11 Digits"
        varchar phone_number "E.164 Contact Number"
        varchar emergency_contact "Alternative Contact"
    }

    animal_patients {
        uuid id PK "UUIDv7"
        uuid tenant_id FK "Tenant Isolation"
        uuid legal_tutor_id FK "FK to Legal Tutor"
        varchar name "Animal Name"
        int species "1=Canine, 2=Feline, 3=Equine, 4=Bovine, 5=Avian, 6=Exotic"
        varchar breed "Breed Name"
        int biological_sex "1=Male, 2=Female"
        boolean is_neutered "Spayed / Castrated Flag"
        varchar microchip_number "15-Digit ISO Microchip"
        date estimated_birth_date "Estimated DOB"
        decimal current_weight_kg "Latest Weight (kg)"
    }

    veterinary_prescriptions {
        uuid id PK "UUIDv7"
        uuid animal_patient_id FK "Target Animal"
        uuid veterinarian_id FK "Prescribing Vet (CRMV)"
        varchar crmv_number "CRMV License Number"
        varchar crmv_state "Licensing State (UF)"
        int prescription_type "1=Simple, 2=Controlled MAPA, 3=Antimicrobial"
        timestamptz issued_at "Issuance UTC"
    }

    weight_histories {
        uuid id PK "UUIDv7"
        uuid animal_patient_id FK "Target Animal"
        decimal weight_kg "Measured Weight in Kg"
        timestamptz measured_at "Measurement Timestamp"
    }