2. Data Model Documentation

This section is the developer's map of the platform's data. It is organized so you can start from a high-level architectural view and drill into any single domain. The authoritative typed contract is lib/schema.ts; where the production model adds structure beyond the mock schema (real keys, join tables, audit and consent), it is noted.

2.1 How to read this section

• 2.2 High-level architecture gives the whole picture: the domain map, the domain catalogue, and a condensed master ER diagram.
• 2.3 to 2.13 are the domains. Each has an ER diagram (with keys marked) and per-entity detail: purpose, key attributes, keys and indexes, constraints, and relationships.
• 2.14 to 2.16 cover cross-entity rules, the consolidated indexing strategy, and conventions.

Conventions

• Identifiers. Surrogate primary keys are UUIDs unless a natural, stable business key already exists. The Case is the exception: its primary key is the human-readable caseId (the file number surfaced in the UI, format GC-####).
• Money. Stored as an integer minor unit plus an ISO-4217 currency. Never a float. All amounts on a case use the corridor currency.
• Time. Timestamps are ISO-8601 UTC. Mutable entities carry createdAt and updatedAt; lifecycle entities also carry event timestamps (enteredAt, submittedAt, releasedAt).
• Soft delete. Operational rows carry a nullable deletedAt; hard deletion runs only on the retention schedule and is audited. A legal hold pauses deletion.
• Tenancy. Every tenant-owned row carries a tenant key (clinicId) and, for case data, the caseId, enforced by row-level security. See multi-tenant architecture.

Key and constraint notation

The attribute tables and ER diagrams use this notation:

The case is the spine

Almost every operational record hangs off a Case, identified by caseId. A Case belongs to one Patient, runs along one Corridor (configured by a Country Pack), targets one Clinic, is owned by one Coordinator, and progresses through the 14 Stages. Keep this in mind: when in doubt about how two records relate, they usually relate through the Case.

2.2 High-level architecture

Domain map

The model divides into ten functional domains plus one cross-cutting domain. Arrows show the primary direction of reference (a domain reads or links to the domain it points at).

flowchart TD
  IAM[Identity and Access] --> CASE[Case Management]
  CORR[Corridors and Country Packs] --> CASE
  PROV[Provider and Marketplace] --> CASE
  CASE --> CLIN[Clinical and Records]
  CASE --> VISA[Visa and Immigration]
  CASE --> TRAVEL[Travel and Logistics]
  CASE --> PAY[Payments and Escrow]
  CASE --> COMMS[Communications]
  AGENT[Growth and Agents] --> CASE
  VISA --> CLIN
  PAY --> PROV
  CASE --> AUDIT[Audit, Consent and Lifecycle]
  CLIN --> AUDIT
  PAY --> AUDIT
  VISA --> AUDIT

Domain catalogue

Master ER diagram (condensed)

The condensed view shows the backbone across domains. Each domain section below expands its own slice with full attributes.

erDiagram
  USER ||--o{ CASE : "staffs / owns"
  PATIENT ||--o{ CASE : "is subject of"
  CORRIDOR ||--o{ CASE : "routes"
  COUNTRY_PACK ||--|| CORRIDOR : "configures"
  CLINIC ||--o{ CASE : "treats"
  CLINIC ||--o{ CLINIC_USER : "employs"
  CLINIC ||--o{ PROCEDURE_PRICE : "prices"
  CLINIC ||--o{ INQUIRY : "receives"
  COORDINATOR ||--o{ CASE : "owns"
  CASE ||--o{ STAGE_PROGRESS : "advances through"
  CASE ||--o{ DOCUMENT : "holds"
  CASE ||--o{ CONSULTATION : "has"
  CASE ||--o{ QUOTE : "receives"
  CASE ||--|| VISA_APPLICATION : "has"
  CASE ||--|| ESCROW_ACCOUNT : "funds"
  CASE ||--o{ APPOINTMENT : "schedules"
  CASE ||--o{ TRAVEL_SELECTION : "books"
  CASE ||--o{ THREAD : "communicates via"
  CASE ||--o{ REVIEW : "yields"
  CASE ||--o{ CONSENT : "records"
  CASE ||--o{ AUDIT_EVENT : "is audited by"
  VISA_APPLICATION ||--o{ VISA_CHECKLIST_ITEM : "requires"
  VISA_CHECKLIST_ITEM }o--o| DOCUMENT : "verified by"
  ESCROW_ACCOUNT ||--o{ PAYMENT_MILESTONE : "schedules"
  ESCROW_ACCOUNT ||--o{ LEDGER_ENTRY : "records"
  THREAD ||--o{ MESSAGE : "contains"
  AGENT ||--o{ REFERRAL : "submits"
  REFERRAL }o--|| CASE : "attributes"

2.3 Domain: Identity and Access

Authentication, sessions and role grants. In the reference build this is a client-side mock (lib/auth.tsx, two demo accounts); production replaces it with real identity. A User is the login principal; a Patient and a ClinicAccount each link to a User.

erDiagram
  USER ||--o{ SESSION : "opens"
  USER ||--o{ ROLE_GRANT : "is granted"
  USER ||--o| PATIENT : "is"
  USER ||--o{ CLINIC_USER : "is"
  USER {
    uuid id PK
    string email UK "NN"
    string passwordHash "NN"
    boolean emailVerified
    boolean mfaEnrolled
    timestamp createdAt
    timestamp deletedAt
  }
  SESSION {
    uuid id PK
    uuid userId FK "NN"
    string refreshTokenHash
    string ip
    timestamp expiresAt
    timestamp revokedAt
  }
  ROLE_GRANT {
    uuid id PK
    uuid userId FK "NN"
    string role "CK"
    string scopeType "CK: tenant|case|global"
    string scopeId
  }

User

• Purpose. The login principal for every human (patient, clinic staff, internal staff, agent).
• Key attributes. id PK; email UQ NN; passwordHash NN; emailVerified, mfaEnrolled (staff and clinic users require MFA).
• Indexes. Unique on email.
• Constraints. Email format validated; password policy enforced at the service layer; staff roles cannot authenticate without a completed MFA session.
• Relationships. One User opens many Sessions, holds many RoleGrants, and is linked one-to-one to a Patient or one-to-many to ClinicUser records.

Session

• Purpose. A refresh-token-backed login session supporting rotation and revocation.
• Key attributes. id PK; userId FK NN; refreshTokenHash; expiresAt; revokedAt.
• Indexes. userId IDX.
• Constraints. Refresh-token reuse detection; idle and absolute lifetimes; revoked on logout.

RoleGrant

• Purpose. Binds a User to a role within a scope. This is the storage behind the RBAC matrix.
• Key attributes. id PK; userId FK NN; role CK (patient, coordinator, specialist, provider, visa, travel, finance, admin, agent, medical_director, support); scopeType CK (tenant, case, global); scopeId (the clinicId or caseId the grant applies to).
• Constraints. Deny by default; a grant is the only way a non-owner principal gains access to a tenant or case.

2.4 Domain: Case Management (the spine)

The operational core. Case is the root entity; Patient, Coordinator and the 14 Stages attach to it. StageProgress records movement through the lifecycle with SLA timers.

erDiagram
  PATIENT ||--o{ CASE : "subject of"
  COORDINATOR ||--o{ CASE : "owns"
  CORRIDOR ||--o{ CASE : "routes"
  CLINIC ||--o{ CASE : "treats"
  CASE ||--o{ STAGE_PROGRESS : "advances"
  STAGE ||--o{ STAGE_PROGRESS : "instantiated by"
  CASE {
    string caseId PK "format GC-####"
    uuid patientId FK "NN"
    string clinicId FK
    string corridorId FK "NN"
    uuid coordinatorId FK
    int currentStage "CK 0..13"
    string status "CK open|on_hold|closed|cancelled"
    string dataRegime
    string riskTier "CK standard|enhanced"
    timestamp createdAt
    timestamp updatedAt
  }
  PATIENT {
    uuid id PK
    string caseId FK "active case"
    string name "NN"
    int age "CK 0..120"
    string origin
    string treatment
    string lang
    int currentStage
  }
  COORDINATOR {
    uuid id PK
    string name
    string langs
    string status
  }
  STAGE {
    string key PK
    string label
    string group "CK Discover|Plan|Treat|Return"
    string owner
    string slaTarget
  }
  STAGE_PROGRESS {
    uuid id PK
    string caseId FK "NN"
    string stageKey FK "NN"
    string status "CK done|active|upcoming"
    uuid ownerUserId FK
    timestamp enteredAt
    timestamp dueAt
    timestamp completedAt
  }

Case

• Purpose. The spine that ties every other domain together; one per treatment journey.
• Key attributes. caseId PK (human-readable file number); patientId FK NN; clinicId FK (chosen provider); corridorId FK NN; coordinatorId FK (named owner); currentStage CK (0 to 13); status CK (open, on_hold, closed, cancelled); dataRegime and riskTier denormalized from the Country Pack so handling rules travel with the case.
• Indexes. patientId, clinicId, corridorId, coordinatorId, status all IDX (the dominant access patterns are per-owner queues and per-corridor reporting).
• Constraints. currentStage advances forward only; a regression requires an explicit, audited override. At most two attendants per case.
• Relationships. Belongs to one Patient, Corridor, Clinic and Coordinator; parents Documents, Consultations, Quotes, the VisaApplication, the EscrowAccount, Appointments, TravelSelections, Threads, Reviews, Consents and AuditEvents.

Patient

• Purpose. The person seeking treatment (Patient in the schema).
• Key attributes. id PK; caseId FK (active case); name NN; age CK (0 to 120); origin, treatment, specialty, hospital, surgeon, surgeryDate, attendant, corridor, lang, currentStage.
• Constraints. name, origin and all linked medical and identity records are sensitive PII (see retention and PII handling).
• Relationships. Subject of one or more Cases; the active case is the one surfaced in the portal.

Coordinator

• Purpose. The named human who owns the patient relationship across all stages (Coordinator).
• Key attributes. id PK; name; langs (languages spoken); reply (typical reply time); status.
• Relationships. Owns many Cases; appears as a Thread participant.

Stage and StageProgress

• Purpose. Stage is reference data, the catalogue of the 14 lifecycle stages. StageProgress records a single case's movement through one stage with SLA timing.
• Stage key attributes. key PK; label, short, group CK (Discover, Plan, Treat, Return), owner (accountable role), slaTarget (service-level target).
• StageProgress key attributes. id PK; caseId FK NN; stageKey FK NN; status CK (done, active, upcoming); ownerUserId FK; enteredAt, dueAt (derived from slaTarget), completedAt.
• Indexes. caseId IDX; composite (caseId, status) for the active-stage lookup; dueAt IDX for SLA-breach queries.
• Why it matters. The owner and slaTarget fields exist in the mock schema precisely so the planned operations console can drive SLA timers and escalation.

2.5 Domain: Corridors and Country Packs

A Corridor is a source-to-destination route. A CountryPack is its versioned, feature-flagged configuration: the rules engine behind visas, payments, language and risk.

erDiagram
  CORRIDOR ||--|| COUNTRY_PACK : "current version"
  COUNTRY_PACK ||--o{ COUNTRY_PACK : "supersedes (version history)"
  CORRIDOR ||--o{ CASE : "routes"
  CORRIDOR {
    string id PK
    string from "NN"
    string to "NN"
    string type "CK e-Visa|Paper visa"
    string currency
    string payment
    string dataRegime
    string riskTier "CK standard|enhanced"
    boolean active
  }
  COUNTRY_PACK {
    uuid id PK
    string corridorId FK "NN"
    int version "NN"
    date effectiveFrom
    json requiredDocuments
    json featureFlags
    string processingWindow
    int feeMinor
  }

Corridor

• Purpose. Defines a route such as Oman to India and the high-level rules that apply to every case on it (Corridor).
• Key attributes. id PK; from, to NN; type CK (e-Visa, Paper visa); time (processing window); language, currency, payment, dataRegime; riskTier CK (standard, enhanced); active.
• Indexes. riskTier IDX (enhanced-diligence corridors).
• Relationships. Routes many Cases; has one current Country Pack version.

CountryPack

• Purpose. The versioned configuration bundle for a corridor. Editing a corridor's rules means publishing a new Country Pack version, never mutating live cases.
• Key attributes. id PK; corridorId FK NN; version NN; effectiveFrom; requiredDocuments (the visa document set), featureFlags, processingWindow, feeMinor, plus currency, payment, dataRegime, riskTier.
• Constraints. (corridorId, version) UQ; exactly one version is current per corridor at a time; versions are immutable once published.
• Why it matters. Country Packs are the rules engine: the visa engine, payment currency, language assignment and risk tier all read from the corridor's current pack, so a corridor's behaviour changes without a code release.

2.6 Domain: Provider and Marketplace

The partner side: a Clinic (the marketplace and clinical record), the ClinicAccount and ClinicUser/ClinicTeamMember who operate it, the catalogue (Specialty, Procedure, ClinicProcedurePrice), reputation (Review, Reputation, Accreditation), and provider operations (ClinicInquiry, ClinicCase).

erDiagram
  CLINIC ||--|| CLINIC_ACCOUNT : "operated via"
  CLINIC ||--o{ CLINIC_TEAM_MEMBER : "staffs"
  CLINIC ||--o{ CLINIC_PROCEDURE_PRICE : "prices"
  CLINIC ||--o{ REVIEW : "receives"
  CLINIC ||--o{ CLINIC_INQUIRY : "receives"
  CLINIC ||--o{ CLINIC_CASE : "delivers"
  SPECIALTY ||--o{ PROCEDURE : "categorises"
  PROCEDURE ||--o{ CLINIC_PROCEDURE_PRICE : "priced as"
  CLINIC {
    string id PK
    string name "NN"
    string country "IDX"
    json accreditations
    json specialties
    int priceFromUsd "IDX"
    int savingsPct
    boolean vetted "IDX"
  }
  CLINIC_ACCOUNT {
    uuid id PK
    string clinicId FK "UQ"
    string contactEmail
    string payoutAccount "masked"
    string status "CK verified|pending|draft"
    string joined
  }
  PROCEDURE {
    string key PK
    string name
    string specialty FK
    int priceFromUsd "IDX"
    int refPriceUsd
    int savingsPct
  }
  CLINIC_PROCEDURE_PRICE {
    uuid id PK
    string clinicId FK
    string procedure FK
    int priceUsd
    string stay
  }
  REVIEW {
    uuid id PK
    string clinicId FK "IDX"
    string caseId FK
    int rating "CK 1..5"
    string source "CK Trustpilot|Google|Verified visit"
    boolean verified "IDX"
    string date
  }
  CLINIC_INQUIRY {
    uuid id PK
    string clinicId FK "IDX"
    string status "CK new|quoted|accepted|declined"
    int budgetUsd
    string date
  }

Clinic

• Purpose. A partner hospital or clinic: its public marketplace presence and its clinical identity (Clinic).
• Key attributes. id PK; name NN; city, country; accreditations[] (JCI, NABH, ISO), specialties[], procedures[], reviews (a ClinicReviewSummary); priceFromUsd, savingsPct, wait, founded, intlPatients, tags[]; vetted.
• Indexes. country IDX; priceFromUsd IDX; specialties and accreditations GIN (array containment for filters); partial index where vetted = true; full-text search vector on name and blurb.
• Constraints. A clinic is publicly listed only when its account status = verified; only JCI or NABH-accredited clinics may be empanelled.
• Relationships. Operated via one ClinicAccount; staffs ClinicTeamMembers; prices Procedures; receives Reviews, ClinicInquirys; delivers ClinicCases; treats Cases.

ClinicAccount and ClinicTeamMember

• ClinicAccount. id PK; clinicId FK UQ (links to the marketplace Clinic); contactName, contactEmail, phone; payoutAccount (masked); status CK (verified, pending, draft); joined. This is the tenant root for clinic users.
• ClinicTeamMember. id PK; clinicId FK; name, role, specialty, experience, highlight; status CK (active, invited). The clinical staff and surgeons.

Specialty, Procedure, ClinicProcedurePrice

• Specialty. key PK; name, icon, blurb. Reference data categorising procedures.
• Procedure. key PK; specialty FK; name, priceFromUsd IDX, refPriceUsd, refCountry, savingsPct, stay, clinicCount, popular. The catalogue item.
• ClinicProcedurePrice. id PK; clinicId FK; procedure FK; priceUsd, stay. Indicative price for one procedure at one clinic; (clinicId, procedure) UQ.

Review, Reputation, Accreditation

• Review. id PK; clinicId FK IDX; caseId FK (a verified review ties to a completed, attributed case); rating CK (1 to 5); source CK (Trustpilot, Google, Verified visit); verified IDX; author, country, date, procedure, title, body. Genuine negative verified reviews are not suppressed.
• Reputation. Platform aggregate: overall, totalReviews, verifiedPct, sources[] (ReputationSource), distribution[]. Largely derived/materialized.
• Accreditation. code PK (JCI, NABH); name, body, blurb, facilities. Reference data.

ClinicInquiry and ClinicCase (provider operations)

• ClinicInquiry. id PK; clinicId FK IDX; patient, country, procedure, date; status CK (new, quoted, accepted, declined); budgetUsd; message. Composite index (clinicId, status) powers the clinic inbox.
• ClinicCase. id PK; clinicId FK; patient, procedure; stage CK (Inquiry, Quoted, Treatment planned, In treatment, Recovery, Closed); surgeryDate, valueUsd, coordinator. The provider's view of an active case; index (clinicId, stage) for the case board.

2.7 Domain: Clinical and Medical Records

The documents wallet, the specialist consultation and opinion, the care plan, and scheduling.

erDiagram
  CASE ||--o{ DOCUMENT : "holds"
  CASE ||--o{ CONSULTATION : "has"
  CONSULTATION ||--o| OPINION : "produces"
  CONSULTATION }o--o{ DOCUMENT : "reviews"
  CASE ||--o{ CARE_PHASE : "plans"
  CASE ||--o{ APPOINTMENT : "schedules"
  DOCUMENT {
    uuid id PK
    string caseId FK "NN"
    string name
    string type "CK Identity|Medical|Visa|Financial|Travel"
    string status "CK pending|verified"
    string storageKey "encrypted"
    uuid verifiedBy FK
    timestamp createdAt
  }
  CONSULTATION {
    uuid id PK
    string caseId FK "NN"
    string status "CK open|awaiting_records|opinion_ready|closed"
    uuid specialistId FK
  }
  OPINION {
    uuid id PK
    uuid consultationId FK "UQ"
    text body
    uuid authorId FK
    timestamp issuedAt
  }
  APPOINTMENT {
    uuid id PK
    string caseId FK "NN"
    string kind "CK consult|admission|procedure|followup"
    timestamp startsAt "IDX"
    string status "CK scheduled|completed|cancelled|no_show"
  }
  CARE_PHASE {
    uuid id PK
    string caseId FK
    string phase
    json items
  }

Document

• Purpose. A single item in the patient's wallet (Document), and the source of visa checklist completion.
• Key attributes. id PK; caseId FK NN; name; type CK (Identity, Medical, Visa, Financial, Travel); status CK (pending, verified); size, date; storageKey (encrypted object-storage key, server-set); verifiedBy FK (reviewer).
• Indexes. caseId IDX; composite (caseId, type); partial index where status = pending (the verification queue).
• Constraints. File type and size validated at upload; virus-scanned before verified; encrypted at rest; only an authorised reviewer can set verified. Verifying a document with a linked visa checklist item updates that item (the signature cross-screen flow).

Consultation and Opinion

• Consultation. id PK; caseId FK NN; status CK (open, awaiting_records, opinion_ready, closed); specialistId FK. Reviews a set of Documents (many-to-many).
• Opinion. id PK; consultationId FK UQ (one opinion per consultation); body; authorId FK; issuedAt. The opinion is the specialist's, recorded by the platform; authorship is stored explicitly.

Appointment

• Purpose. Scheduling for consultations, admission, procedure and follow-up tele-consults.
• Key attributes. id PK; caseId FK NN; kind CK (consult, admission, procedure, followup); startsAt IDX; status CK (scheduled, completed, cancelled, no_show).
• Constraints. Follow-up appointments default to day 7, 30 and 90; scheduling conflicts are rejected.

CarePhase and Vital

• CarePhase. id PK; caseId FK; phase (Pre-op, Procedure, Post-op, Recovery); items ([label, status] tuples). Derived from the opinion; drives the Care Plan screen.
• Vital. A [label, value, unit] tuple shown on the care plan; display-oriented today.

2.8 Domain: Visa and Immigration

The flagship engine. A VisaApplication per case, driven by the corridor Country Pack, with a derived checklist and a status timeline.

erDiagram
  CASE ||--|| VISA_APPLICATION : "has"
  VISA_APPLICATION ||--o{ VISA_CHECKLIST_ITEM : "requires"
  VISA_APPLICATION ||--o{ VISA_TIMELINE_STEP : "tracks"
  VISA_CHECKLIST_ITEM }o--o| DOCUMENT : "completed by"
  COUNTRY_PACK ||--o{ VISA_APPLICATION : "rules for"
  VISA_APPLICATION {
    uuid id PK
    string caseId FK "UQ NN"
    string type
    string ref UK "govt reference"
    string status "CK in_review|granted|rejected"
    int feeMinor
    timestamp submittedAt
    timestamp grantedAt
  }
  VISA_CHECKLIST_ITEM {
    uuid id PK
    uuid visaApplicationId FK "NN"
    string label
    boolean done
    uuid docId FK "nullable"
  }
  VISA_TIMELINE_STEP {
    uuid id PK
    uuid visaApplicationId FK "NN"
    string label
    boolean done
    boolean active
    string date
  }

VisaApplication

• Purpose. The case's medical-visa application and its live status (Visa).
• Key attributes. id PK; caseId FK UQ NN (one per case); type, country, from, fee, processing, validity; status CK (in_review, granted, rejected); ref UQ (government medical reference number); submittedAt, grantedAt.
• Constraints. May be submitted only when every checklist item resolves to done (else 409); submission is idempotent. Required documents, fee, processing window and routing come from the corridor Country Pack.

VisaChecklistItem

• Purpose. One required item; the engine's unit of completeness (VisaChecklistItem).
• Key attributes. id PK; visaApplicationId FK NN; label; done; docId FK (nullable link to a Document).
• Indexes. visaApplicationId IDX; docId IDX (to resolve linked-document completion).
• The signature rule. When docId is present, done is derived from that document being verified, not from a stored flag. This is what makes "upload a document" unlock "submit application".

VisaTimelineStep

• id PK; visaApplicationId FK NN; label, date, done, active (the single in-progress step). The vertical status stepper.

2.9 Domain: Travel and Logistics

The trip plan and the bookable options. The patient selects preferences; the Travel Desk confirms. Options are largely corridor-scoped reference data; TravelSelection is the per-case transactional record.

erDiagram
  CASE ||--|| TRIP_PLAN : "has"
  CASE ||--o{ TRAVEL_SELECTION : "chooses"
  FLIGHT_OPTION ||--o{ TRAVEL_SELECTION : "selected in"
  STAY_OPTION ||--o{ TRAVEL_SELECTION : "selected in"
  TRANSFER_OPTION ||--o{ TRAVEL_SELECTION : "selected in"
  TRAVEL_ADDON }o--o{ TRAVEL_SELECTION : "added to"
  TRIP_PLAN {
    uuid id PK
    string caseId FK "UQ"
    string corridorFrom
    string corridorTo
    int travellers
    date departDate
    date returnDate
    int nights
  }
  FLIGHT_OPTION {
    string id PK
    string airline
    string flightNo
    int priceUsd
    boolean recommended
  }
  STAY_OPTION {
    string id PK
    string name
    string kind
    int nightlyUsd
    boolean recommended
  }
  TRAVEL_SELECTION {
    uuid id PK
    string caseId FK "NN"
    string flightId FK
    string stayId FK
    string transferId FK
    string status "CK selected|submitted|confirmed"
  }

TripPlan

• Purpose. The static trip parameters for a case (TripPlan): corridor, travellers, dates, nights, surgery date.
• Key attributes. id PK; caseId FK UQ; corridorFrom, corridorTo, travellers, travellerNote, departDate, returnDate, nights, surgeryDate.

FlightOption, StayOption, TransferOption, TravelAddon

• FlightOption. id PK; airline, flightNo, from, to, depart, arrive, duration, stops, cabin, priceUsd, tags[], recommended. A bookable outbound flight for the corridor.
• StayOption. id PK; name, kind, distance, rating, nightlyUsd, tags[] (attendant bed, halal kitchen), recommended. Accommodation near the hospital.
• TransferOption. id PK; name, blurb, priceUsd, note, recommended. Ground transport.
• TravelAddon. id PK; label, blurb, priceUsd, unit, defaultOn. Optional concierge add-on.

TravelSelection

• Purpose. The per-case record of what the patient chose and the Travel Desk confirmed.
• Key attributes. id PK; caseId FK NN; flightId FK, stayId FK, transferId FK, addonIds[]; status CK (selected, submitted, confirmed).
• Constraints. All prices are indicative until confirmed; confirmation calls the airline, hotel and transfer providers (see Integrations).

2.10 Domain: Payments and Escrow

Regulated, multi-currency money movement. A Quote becomes an EscrowAccount that schedules PaymentMilestones and records an append-only LedgerEntry stream; Refunds are compensating entries.

erDiagram
  CASE ||--o{ QUOTE : "receives"
  CASE ||--|| ESCROW_ACCOUNT : "funds"
  ESCROW_ACCOUNT ||--o{ PAYMENT_MILESTONE : "schedules"
  ESCROW_ACCOUNT ||--o{ LEDGER_ENTRY : "records"
  PAYMENT_MILESTONE ||--o{ LEDGER_ENTRY : "releases via"
  ESCROW_ACCOUNT ||--o{ REFUND : "issues"
  QUOTE {
    uuid id PK
    string caseId FK "NN"
    string clinicId FK
    int totalMinor
    string currency
    string status "CK sent|accepted|declined|expired"
  }
  ESCROW_ACCOUNT {
    uuid id PK
    string caseId FK "UQ NN"
    int totalMinor
    int heldMinor
    string currency
  }
  PAYMENT_MILESTONE {
    uuid id PK
    uuid escrowAccountId FK "NN"
    string label
    int amountMinor
    string status "CK released|held|scheduled"
    string date
  }
  LEDGER_ENTRY {
    uuid id PK
    uuid escrowAccountId FK "NN IDX"
    uuid milestoneId FK
    string desc
    int amountMinor
    string kind "CK out|rel"
    timestamp createdAt "IDX"
  }

Quote

• Purpose. An itemised cost estimate sent to the patient by the clinic and coordinator.
• Key attributes. id PK; caseId FK NN; clinicId FK; lines[] (a CostLine[] grouped Treatment, Hospital, Travel & stay, Services); totalMinor, currency; status CK (sent, accepted, declined, expired).
• Constraints. Itemised, never a hidden markup; acceptance is the trigger to open an EscrowAccount.

EscrowAccount

• Purpose. Holds the patient's funds for one case (Payments in the schema).
• Key attributes. id PK; caseId FK UQ NN (one per case); totalMinor, heldMinor, currency.
• Relationships. Schedules many PaymentMilestones and records many LedgerEntrys.

PaymentMilestone

• Purpose. A tranche released on a verified event (PaymentMilestone).
• Key attributes. id PK; escrowAccountId FK NN; label, amountMinor; status CK (released, held, scheduled); date, note.
• Indexes. escrowAccountId IDX; status IDX (held and scheduled tranches).
• Constraints. Moves to released only on its verified trigger (for example provider milestone confirmation), which posts a ledger entry.

LedgerEntry

• Purpose. The immutable money trail (LedgerEntry).
• Key attributes. id PK; escrowAccountId FK NN IDX; milestoneId FK (when a release); desc; amountMinor (negative for money in or charged, positive for released); kind CK (out, rel); createdAt IDX.
• Constraints. Append-only and immutable. Corrections are compensating entries, never edits or deletes. This is the auditable money trail finance reconciles against the payment partner.

Refund

• id PK; escrowAccountId FK NN; reason, amountMinor (omit for a full pre-arrival refund); posts a compensating LedgerEntry. Pre-arrival refunds are full; post-milestone follow the disclosed schedule.

2.11 Domain: Communications

Secure messaging and notifications, both case-scoped.

erDiagram
  CASE ||--o{ THREAD : "has"
  THREAD ||--o{ MESSAGE : "contains"
  CASE ||--o{ NOTIFICATION : "notifies"
  THREAD {
    uuid id PK
    string caseId FK "NN"
    string name
    string role
    int unread
    boolean online
  }
  MESSAGE {
    uuid id PK
    uuid threadId FK "NN IDX"
    boolean fromPatient
    text body
    json attachmentIds
    timestamp createdAt "IDX"
  }
  NOTIFICATION {
    uuid id PK
    string caseId FK "NN"
    string text
    string tone "CK amber|green|teal"
    string channel "CK in_app|whatsapp|email|sms"
    timestamp createdAt
  }

Thread and Message

• Thread. id PK; caseId FK NN; name, role, initials, unread, online, last (preview). One per care-team conversation.
• Message. id PK; threadId FK NN IDX; fromPatient (right-aligned bubble when true); body; attachmentIds[]; createdAt IDX (for paging). Production is real-time with an audit trail.

Notification

• id PK; caseId FK NN; text, tone CK (amber, green, teal); channel CK (in_app, whatsapp, email, sms); createdAt; read state. Emitted by domain events (milestone, visa, travel, message) and delivered omnichannel, prioritising WhatsApp.

2.12 Domain: Growth and Agents

Source-country referrers and their milestone-gated, strictly non-clinical attribution.

erDiagram
  AGENT ||--o{ REFERRAL : "submits"
  REFERRAL }o--|| CASE : "attributes"
  AGENT {
    uuid id PK
    string name
    string referralCode UK
    string tier "CK casual|agent|clinic"
    string payoutAccount "masked"
  }
  REFERRAL {
    uuid id PK
    uuid agentId FK "NN IDX"
    string caseId FK "IDX"
    string milestoneStatus "CK lead|arrived|admitted|paid"
    int payoutMinor
  }

Agent

• id PK; name; referralCode UQ; tier CK (casual, agent, clinic); payoutAccount (masked). A tracked referrer.

Referral

• id PK; agentId FK NN IDX; caseId FK IDX (attribution); milestoneStatus CK (lead, arrived, admitted, paid); payoutMinor.
• Constraints. Payouts gate on an arrived or admitted milestone, never on a raw lead, and are a marketing fee from Global Clinic's margin, never a clinical kickback (see the compliance boundary).

2.13 Audit, consent and data lifecycle

A cross-cutting domain. Every clinical, financial, consent and visa action is recorded immutably; consent is a first-class ledger; deletion is governed by retention and legal holds.

erDiagram
  CASE ||--o{ AUDIT_EVENT : "records"
  CASE ||--o{ CONSENT : "tracks"
  USER ||--o{ AUDIT_EVENT : "acts in"
  AUDIT_EVENT {
    uuid id PK
    string caseId FK "IDX"
    string entity "IDX"
    string entityId
    string action
    uuid actorId FK "IDX"
    string actorRole
    json before
    json after
    string ip
    timestamp createdAt "IDX"
  }
  CONSENT {
    uuid id PK
    string caseId FK "IDX"
    uuid subjectId FK
    string purpose
    string scope
    string state "CK granted|withdrawn"
    timestamp grantedAt
    timestamp withdrawnAt
  }

AuditEvent

• Purpose. The append-only record of who did what to which entity, with before and after state.
• Key attributes. id PK; caseId FK IDX; entity IDX, entityId, action; actorId FK IDX, actorRole; before, after (JSON snapshots); ip; createdAt IDX.
• Indexes. caseId, actorId, createdAt, and composite (entity, entityId) for per-record history.
• Covers. Document verification, escrow funding and release, milestone confirmation, refunds, visa submission and grant, consent grant and withdrawal, role and access changes, and case status changes.
• Integrity. Immutable and tamper-evident; retained for the longest applicable regime.

Consent

• Purpose. The system of record for privacy compliance and DSAR handling.
• Key attributes. id PK; caseId FK IDX; subjectId FK (patient or attendant); purpose, scope; state CK (granted, withdrawn); grantedAt, withdrawnAt.
• Constraints. Granular and purpose-bound (clinical review, hospital sharing, travel and visa, marketing); marketing consent is never bundled with service consent; withdrawal stops the relevant processing prospectively.

Data lifecycle

• Soft delete. Operational rows carry deletedAt; history is preserved.
• Legal hold. Pauses retention-driven deletion.
• Hard deletion runs only on the retention schedule and is itself audited.
• Tracking timestamps. createdAt/updatedAt on every mutable entity; lifecycle event timestamps (enteredAt, submittedAt, grantedAt, releasedAt) drive SLA measurement and reporting.

2.14 Cross-entity validation and referential integrity

These rules span domains and are the authoritative source for QA and service-layer validation.

• Stage pointer. Case.currentStage is 0 to 13 and forward-only; regressions require an explicit, audited override.
• Visa submit gate. A VisaApplication may be submitted only when every VisaChecklistItem resolves to done (linked items via a verified Document).
• Document verification. Only an authorised reviewer sets Document.status = verified; patients cannot self-verify.
• Escrow release. A PaymentMilestone moves to released only on its verified trigger and posts an immutable LedgerEntry.
• Refund policy. Pre-arrival refunds are full; post-milestone refunds follow the disclosed schedule.
• Clinic visibility. A Clinic is publicly listed only when its ClinicAccount.status = verified.
• Attendant count. At most two attendants per case (e-Medical Attendant visa).
• Currency consistency. All monetary amounts on a case use the corridor currency; cross-currency conversions are recorded with rate and timestamp.
• Referential integrity. caseId, clinicId, corridorId, procedure and specialty foreign keys must resolve; orphan rows are rejected. Deletes are soft, so foreign keys remain valid through history.

2.15 Indexing strategy (consolidated)

Indexes follow the dominant access patterns: fetch a case and its children, a clinic's queue, a corridor's cases, and reporting. The per-domain tables above mark indexes inline; the consolidated set:

Tenant scoping: composite indexes lead with the tenant key (clinicId) or caseId so row-level-security-scoped queries stay selective. Full-text marketplace search uses a generated tsvector with a GIN index. Time-series reporting is offloaded to the analytics warehouse rather than indexed on the operational store.

2.16 Reference data versus transactional data

• Reference and configuration data (Stage, Specialty, Procedure, Accreditation, Corridor, CountryPack, the catalogue FlightOption/StayOption/TransferOption) changes rarely and is read constantly. Cache it aggressively; version it where rules matter (Country Packs are versioned and feature-flagged).
• Transactional data (Case and its children: documents, consultations, visa, escrow, ledger, messages) is written constantly and is strictly tenant- and case-scoped, audited, and soft-deleted.
• Derived and materialized data (Reputation aggregates, savings percentages, reporting rollups) is computed from the above and rebuilt on change or on a schedule, never hand-edited.

This split guides where to put load (cache reference data, scale the operational store, offload analytics to the warehouse) and how to reason about consistency.