# Database Architecture

**Status:** Design document · 2026-08-02 · **awaiting approval — no migrations written**

## 0. What this document is

The brief this responds to asks for a greenfield database design. That would be
fiction: **57 tables already exist, are migrated, and are covered by 244 passing
tests.** Phases 0–6 and the Laboratory module are built.

So this document does three things instead:

1. **Inventories what exists**, mapped against the brief, so the gap is visible.
2. **Designs the 11 tables that are genuinely missing**, in full.
3. **Names three places where the brief conflicts with decisions already made and
   approved**, with a recommendation for each.

Section 9 lists what needs your decision. Nothing is built until it does.

---

## 1. Design rules already in force

These were approved in `docs/ARCHITECTURE.md` §7 and the ADR set, and everything
below obeys them.

| Rule | How it is applied |
|---|---|
| MySQL 8.0+ floor | Enforced at boot ([ADR 0006](adr/0006-mysql-floor-and-test-database.md)); tests run on MySQL, never SQLite |
| 3NF wherever practical | Deliberate exceptions listed in §6 |
| Every table timestamped | `created_at`/`updated_at` on all 57 |
| Soft deletes where appropriate | Master data only — never clinical or financial records |
| Foreign keys everywhere | `restrictOnDelete` for master data, `cascadeOnDelete` for owned children, `nullOnDelete` for optional references |
| Public identifier | **ULID**, not UUID — see §9.1 |
| Money | `BIGINT` minor units + currency ([ADR 0016](adr/0016-money-and-financial-corrections.md)) |
| Multi-branch | `branch_id NOT NULL` on every operational table, global scope, from day one |
| Document numbering | Gapless per-branch per-period `sequences` table with `SELECT … FOR UPDATE` |
| Clinical records | Append-only. Amended or retracted, never edited or deleted |
| Financial records | Never deleted. Void, credit note, or reversal |

---

## 2. Table inventory — what exists today

57 tables. Grouped by owning module; `↳` marks a child table.

### 2.1 Core / platform (12)

| Table | Why it exists |
|---|---|
| `branches` | Multi-branch from day one. Retrofitting branches later is the single most expensive change in this class of product |
| `users` | Staff accounts. Doctors are users with a role plus a profile row, so multi-doctor is the default shape and N=1 is not special-cased |
| `roles`, `permissions` | Spatie. Roles are **data**, so "add a new role" is a data change, not a code change |
| `model_has_roles`, `model_has_permissions`, `role_has_permissions` | Spatie pivots |
| `settings` | Key/value with `type`, `group`, `is_encrypted`, `is_public`. Nothing that varies per clinic is hardcoded |
| `sequences` | `(branch_id, key, period, current_value)` under a row lock. `MAX(id)+1` is unusable under concurrency |
| `audit_logs` | Hash-chained, append-only ([ADR 0005](adr/0005-tamper-evident-audit-trail.md)) |
| `record_access_logs` | Who *viewed* which record. Required by most health-data regimes and separate from mutations, which have different volume and retention |
| `login_attempts` | Brute-force lockout |

### 2.2 Auth module (5)

`doctor_profiles` (specialty, licence number, consultation fee, slot minutes),
`password_histories` (reuse prevention), `passkeys`, `personal_access_tokens`,
`sessions`.

### 2.3 Patients module (6)

| Table | Why it exists |
|---|---|
| `patients` | 36 columns. National ID **encrypted** with a HMAC blind index for exact search ([ADR 0009](adr/0009-patient-identity-and-duplicates.md)); `name_normalized` and `phone_normalized` drive duplicate detection |
| ↳ `patient_allergies` | Append-only. Retracted as `entered_in_error`, never deleted — "recorded then retracted" and "never recorded" are different clinical facts |
| ↳ `patient_conditions` | Problem list, same lifecycle |
| ↳ `patient_medications` | Current medication, same lifecycle |
| ↳ `patient_contacts` | Emergency contacts and next of kin |
| ↳ `patient_documents` | Files on the private disk; `checksum` and `mime_type` recorded at upload |

### 2.4 Appointments module (3)

`appointments` (UTC instants **plus originating timezone**, `slot_key` unique
index against double-booking, `queue_number` for walk-ins), `doctor_schedules`
(weekly rotas in local wall-clock time), `schedule_exceptions` (leave, holidays).

### 2.5 Consultations module (4)

`encounters`, `encounter_notes` (versioned, `supersedes_id` /
`superseded_by_id` — [ADR 0013](adr/0013-clinical-notes-are-versioned.md)),
`encounter_vitals` (derived BMI stored), `encounter_diagnoses` (with explicit
certainty, so a ruled-out diagnosis stays off the problem list while remaining
evidence it was considered).

### 2.6 Prescriptions module (3)

`drugs` (clinic-editable formulary; the `ingredients` column is what lets an
allergy check catch amoxicillin against a recorded penicillin allergy),
`prescriptions`, `prescription_items` (drug name, generic, strength **copied
onto the row**).

### 2.7 Billing module (11)

`tax_rates` (basis points), `billable_services`, `payment_methods`,
`cash_sessions`, `invoices`, `invoice_items`, `invoice_item_taxes`, `payments`
(signed amounts — payment, reversal, refund in one table), `credit_notes`,
`credit_note_items`.

### 2.8 Laboratory module (7)

`lab_tests`, `lab_panel_items`, `lab_reference_ranges` (by sex and age band in
days), `lab_orders`, `lab_order_items`, `lab_specimens`, `lab_results`
(versioned, with a verification gate).

### 2.9 Framework (5)

`migrations`, `cache`, `cache_locks`, `jobs`, `job_batches`, `failed_jobs`,
`password_reset_tokens`.

---

## 3. Gap analysis — the brief against reality

### 3.1 Patient module

| Brief asks for | Status |
|---|---|
| Patient Unique ID | ✅ `patients.ulid` |
| MR Number | ✅ `patients.mrn`, gapless per branch |
| Personal information | ✅ first / middle / last, DOB, gender, marital status |
| Contact information | ✅ phone, alternate phone, email |
| Emergency contact | ✅ `patient_contacts` |
| Blood group | ✅ |
| **Age** | ⚠️ **derived, not stored — see §9.2** |
| CNIC / National ID | ✅ encrypted + blind index |
| Photo | ✅ `photo_path` |
| Address, City, State, Postal Code | ✅ |
| **Country** | ❌ only `nationality` (ISO-2), which is a different fact from country of residence |
| Occupation | ✅ |
| **Insurance (future)** | ❌ **new table** |
| Allergies | ✅ `patient_allergies` |
| **Medical Alerts** | ❌ **new table** — distinct from allergies |
| Status | ✅ `is_active` |
| Notes | ✅ |
| Registration date | ✅ `registered_at` |
| **Patient Tags** | ❌ **new tables** |

### 3.2 Patient timeline

❌ **Missing entirely.** The largest new piece. Designed in §4.1.

### 3.3 Appointments

| Brief asks for | Status |
|---|---|
| Doctor, Patient, Date, Start, End, Duration, Status | ✅ |
| Reason, Cancellation reason | ✅ |
| Queue number | ✅ |
| Walk-in / Scheduled | ✅ `source` |
| **Department** | ❌ **new table** + FK |
| **Priority** | ❌ new column |
| **Visit type** (first / follow-up / emergency) | ❌ new column — `source` records *how it was booked*, not *what kind of visit it is* |
| **Follow-up flag** | ❌ new column |

### 3.4 Medical records

| Brief asks for | Status |
|---|---|
| Chief complaint | ✅ `encounters.chief_complaint` |
| History of present illness | ✅ `subjective` note section |
| Clinical notes, Treatment plan, Follow-up advice | ✅ note sections + `follow_up_on` |
| Vitals: BP, pulse, temp, height, weight, BMI | ✅ all, BMI derived and stored |
| Diagnosis | ✅ with certainty |
| **Past medical history** | ⚠️ partly — `patient_conditions` is the problem list, which is not the same thing |
| **Family history** | ❌ **new table** |
| **Surgical history** | ❌ **new table** |

Family and surgical history are **patient-level, not encounter-level**. Recording
them as note sections would mean re-entering them at every visit and having no
single answer to "has this patient had abdominal surgery?".

### 3.5 Prescriptions, Billing, Audit, Settings

✅ **Complete.** Prescription header/items/formulary with dosage, frequency,
duration, instructions and print layout; invoices, items, payments, methods,
discounts, taxes, outstanding balance, refunds and partial payments; audit log
with action, user, IP, browser, old values, new values and timestamp — plus hash
chaining the brief did not ask for; settings in the database with `is_encrypted`
for SMTP credentials and for the backup bucket secret and archive encryption
key.

### 3.6 Documents

`patient_documents` exists with category, checksum and MIME type. Missing:

- ❌ **Version history** — the brief requires Replace + Version History
- ⚠️ Category is a free string with no enum. Should be constrained to the
  brief's list (Lab, Blood, MRI, CT, X-Ray, ECG, Ultrasound, Insurance,
  Referral, Consent, Other)

### 3.7 Missing modules

| Module | Status |
|---|---|
| **Departments** | ❌ new |
| **Notifications** | ❌ new |
| **License** | ❌ new — Phase 8 |
| **Reports** | ✅ no tables needed; queries over existing data. One optional table in §4.11 |

---

## 4. New tables — full design

Eleven tables. Every one carries `id BIGINT UNSIGNED AUTO_INCREMENT`, `ulid
CHAR(26) UNIQUE` where publicly addressable, `created_at`, `updated_at`, and
`branch_id` where operational.

### 4.1 `patient_timeline_events` — the timeline

The brief's most demanding requirement. Thirteen event types drawn from eight
modules, displayed chronologically.

**The design question is materialise or union.** A `UNION ALL` across
appointments, encounters, prescriptions, lab orders, invoices, payments and
documents is correct and needs no new table — and it is unusable. Seven
subqueries, no usable composite index across them, and it degrades linearly as
the record grows. A patient with ten years of history would make their own
profile the slowest page in the product.

So: **an append-only materialised table**, written by domain-event listeners that
already exist.

| Column | Type | Notes |
|---|---|---|
| `id` | BIGINT | |
| `ulid` | CHAR(26) UNIQUE | |
| `branch_id` | BIGINT FK → `branches` | RESTRICT |
| `patient_id` | BIGINT FK → `patients` | CASCADE — the timeline is owned by the patient |
| `occurred_at` | TIMESTAMP | **The clinical time, not the insert time.** A document uploaded today for an X-ray taken last month sits at last month |
| `type` | VARCHAR(40) | registration, appointment, encounter, prescription, diagnosis, lab_order, lab_result, document, invoice, payment, follow_up, note, status_change |
| `subject_type` | VARCHAR(60) | Morph map alias, never a class name |
| `subject_id` | BIGINT | Deliberately **not** an FK — see below |
| `title` | VARCHAR(190) | Rendered at write time |
| `summary` | VARCHAR(500) NULL | |
| `severity` | VARCHAR(20) NULL | normal, warning, critical — a critical lab result reads differently |
| `meta` | JSON NULL | Type-specific extras |
| `actor_id` | BIGINT FK → `users` NULL | SET NULL |
| `is_visible_to_patient` | BOOLEAN default false | For the future portal |

**Indexes**

- `(patient_id, occurred_at DESC)` — the only query that matters, and it is covering
- `(branch_id, occurred_at)` — clinic-wide activity feed
- `(subject_type, subject_id)` — find the event for a given record
- `(patient_id, type, occurred_at)` — filtered timeline

**Why `subject_id` is not a foreign key.** It is polymorphic across eight tables;
MySQL cannot express that. More importantly, the timeline must survive its
source: a prescription superseded by a correction still belongs in the history of
what happened. Integrity is enforced by the writing service, not the schema, and
this is stated rather than hidden.

**Why titles are rendered at write time.** A timeline entry reading "Prescribed
Amoxicillin 500mg" must keep saying that after the formulary entry is renamed.
Same rule as invoice lines and prescription items.

**Consistency.** Listeners run in the same transaction as the event they record.
A rebuild command (`timeline:rebuild --patient=`) reconstructs from source tables
for recovery and for backfilling the existing data.

**Growth.** ~40 rows per patient-year for an active patient. 5,000 patients over
10 years ≈ 2M rows — trivial for MySQL with the composite index. Partitioning by
`occurred_at` year is available if a clinic ever needs it.

### 4.2 `departments`

| Column | Type | Notes |
|---|---|---|
| `branch_id` | BIGINT FK | A department belongs to a branch — "Cardiology" in one building is not the one in another |
| `code`, `name` | VARCHAR(20), VARCHAR(120) | |
| `description` | VARCHAR(500) NULL | |
| `head_user_id` | BIGINT FK → `users` NULL | SET NULL |
| `colour` | CHAR(7) NULL | Calendar rendering |
| `is_active`, `sort_order` | | |
| `deleted_at` | | Soft delete |

**Index:** `UNIQUE (branch_id, code, deleted_at)`, `(branch_id, is_active)`

**Changes to existing tables** (additive, nullable — no data migration):

- `appointments.department_id` → FK, `nullOnDelete`, indexed with
  `(branch_id, department_id, starts_at)`
- `doctor_profiles.department_id` → FK, `nullOnDelete`
- `billable_services.department_id` → FK — makes "revenue by department" a
  single indexed group-by rather than a join through appointments

### 4.3 `patient_tags` and `patient_tag_assignments`

Two tables, not a JSON column: tags must be renamed once and counted.

**`patient_tags`:** `branch_id`, `name`, `slug`, `colour`, `description`,
`is_active`. `UNIQUE (branch_id, slug)`.

**`patient_tag_assignments`:** `patient_id` FK CASCADE, `patient_tag_id` FK
CASCADE, `assigned_by` FK → `users` SET NULL, `assigned_at`.
`UNIQUE (patient_id, patient_tag_id)`, plus `(patient_tag_id, patient_id)` for
the reverse lookup — "show me every diabetic".

### 4.4 `patient_alerts` — medical alerts

**Distinct from allergies, and the distinction matters.** An allergy is an
immune response to a substance. An alert is anything the next clinician must
know before touching the patient: difficult airway, on anticoagulants, MRSA
carrier, falls risk, no blood products. Forcing these into `patient_allergies`
would either corrupt the allergy check with non-substances or hide them.

| Column | Notes |
|---|---|
| `patient_id` | FK CASCADE |
| `type` | clinical, infection_control, safeguarding, behavioural, administrative |
| `severity` | info, warning, critical |
| `title`, `detail` | |
| `starts_on`, `ends_on` | NULL = indefinite. A pregnancy alert expires; a difficult airway does not |
| `status` | active, resolved, entered_in_error — append-only, same as allergies |
| `recorded_by`, `status_reason` | |

**Index:** `(patient_id, status, severity)` — the banner query on every clinical
screen.

### 4.5 `patient_insurance`

Shaped now, unused until the insurance module. Building the shape costs nothing;
retrofitting a payer onto issued invoices costs a migration over financial
records that must not change.

`patient_id` FK CASCADE, `provider_name`, `plan_name`, `policy_number`
(**encrypted**, with `policy_number_index` blind index — same treatment as the
national ID), `holder_name`, `relationship_to_holder`, `valid_from`,
`valid_until`, `coverage_percent` (SMALLINT, basis points), `annual_limit`
(BIGINT minor units) + `currency`, `is_primary`, `notes`.

**Index:** `(patient_id, is_primary)`, `(policy_number_index)`

`invoices.patient_insurance_id` is added as a nullable FK now, so the column
exists before any invoice is issued against it.

### 4.6 `patient_history_entries` — family, surgical, social

One table with a `category`, not three: the shape is identical and three tables
would triple the queries on a screen that shows all three together.

`patient_id` FK CASCADE, `category` (family, surgical, social, obstetric),
`condition_or_procedure`, `relationship` (NULL unless family — mother, father,
sibling), `occurred_on` / `occurred_age_years` (either may be known),
`outcome`, `notes`, `status` (active, entered_in_error), `recorded_by`.

**Index:** `(patient_id, category)`

### 4.7 `patient_document_versions`

The brief requires Replace + Version History. `patient_documents` gains
`current_version` and `version_count`; the file metadata moves into versions.

`patient_document_id` FK CASCADE, `version` SMALLINT, `path`, `original_name`,
`mime_type`, `size_bytes`, `checksum`, `uploaded_by`, `replaced_reason`,
`created_at`.

**Index:** `UNIQUE (patient_document_id, version)`

**Old versions are never deleted from disk.** A consent form replaced after a
dispute is exactly the version somebody will ask for.

### 4.8 `notifications` and `notification_deliveries`

Laravel's stock `notifications` table is one row per notification with no
delivery record. For a clinic sending appointment reminders, "was it actually
sent, and did it fail?" is the whole question.

**`notifications`:** `branch_id`, `type`, `notifiable_type`/`notifiable_id`,
`title`, `body`, `data` JSON, `severity`, `read_at`, `action_url`.
Index `(notifiable_type, notifiable_id, read_at)`.

**`notification_deliveries`:** `notification_id` FK CASCADE, `channel`
(database, mail, sms, whatsapp), `recipient`, `status` (queued, sent, delivered,
failed, bounced), `provider_message_id`, `error`, `attempts`, `sent_at`,
`delivered_at`. Index `(status, created_at)` for the retry sweep.

### 4.9 `license_state` and `license_events`

Phase 8. `license_state` is a **single row** — enforced by a `CHECK (id = 1)`.
Holds the signed token, licence key, plan, features JSON, limits JSON,
`activated_at`, `expires_at`, `last_check_at`, `grace_until`, `domain`,
`installation_id`.

`license_events` is append-only: `event` (activated, validated, failed,
expired, entered_grace, deactivated), `payload` JSON, `ip`, `created_at`.

Deliberately **not** hash-chained. It is operational telemetry, not evidence.

### 4.10 `appointment` column additions

Additive and nullable: `department_id`, `priority` (routine / urgent /
emergency), `visit_type` (first / follow-up / review / procedure),
`is_follow_up` BOOLEAN, `follow_up_for_appointment_id` self-FK SET NULL.

`source` is kept and is not the same field — it records *how the booking
arrived*, `visit_type` records *what kind of visit it is*. A walk-in can be a
follow-up.

### 4.11 `report_definitions` — optional

The brief says reports are generated by queries, which is right. This table is
only needed for **saved and scheduled** reports: `name`, `slug`, `type`,
`filters` JSON, `columns` JSON, `schedule` (cron), `recipients` JSON,
`created_by`, `is_active`, `last_run_at`.

Recommended as **deferred to Phase 7** unless you want scheduled email reports
in v1.

---

## 5. Relationships and foreign key policy

```mermaid
erDiagram
    BRANCHES ||--o{ PATIENTS : "scopes"
    BRANCHES ||--o{ USERS : "scopes"
    BRANCHES ||--o{ DEPARTMENTS : "scopes"

    PATIENTS ||--o{ PATIENT_ALLERGIES : "has"
    PATIENTS ||--o{ PATIENT_ALERTS : "has"
    PATIENTS ||--o{ PATIENT_CONDITIONS : "has"
    PATIENTS ||--o{ PATIENT_HISTORY_ENTRIES : "has"
    PATIENTS ||--o{ PATIENT_INSURANCE : "has"
    PATIENTS ||--o{ PATIENT_DOCUMENTS : "has"
    PATIENT_DOCUMENTS ||--o{ PATIENT_DOCUMENT_VERSIONS : "versions"
    PATIENTS }o--o{ PATIENT_TAGS : "tagged"

    PATIENTS ||--o{ PATIENT_TIMELINE_EVENTS : "chronology"

    PATIENTS ||--o{ APPOINTMENTS : "books"
    USERS ||--o{ APPOINTMENTS : "attends"
    DEPARTMENTS ||--o{ APPOINTMENTS : "categorises"
    APPOINTMENTS ||--o| ENCOUNTERS : "becomes"

    ENCOUNTERS ||--o{ ENCOUNTER_NOTES : "documents"
    ENCOUNTERS ||--o{ ENCOUNTER_VITALS : "observes"
    ENCOUNTERS ||--o{ ENCOUNTER_DIAGNOSES : "concludes"
    ENCOUNTERS ||--o{ PRESCRIPTIONS : "issues"
    ENCOUNTERS ||--o{ LAB_ORDERS : "requests"
    ENCOUNTERS ||--o{ INVOICES : "bills"

    PRESCRIPTIONS ||--o{ PRESCRIPTION_ITEMS : "lines"
    DRUGS ||--o{ PRESCRIPTION_ITEMS : "referenced by"

    LAB_ORDERS ||--o{ LAB_ORDER_ITEMS : "analytes"
    LAB_ORDERS ||--o{ LAB_SPECIMENS : "sampled"
    LAB_ORDER_ITEMS ||--o{ LAB_RESULTS : "measured"

    INVOICES ||--o{ INVOICE_ITEMS : "lines"
    INVOICES ||--o{ PAYMENTS : "settled by"
    INVOICES ||--o{ CREDIT_NOTES : "corrected by"
    CASH_SESSIONS ||--o{ PAYMENTS : "reconciles"
```

### FK deletion policy

| Case | Rule | Reason |
|---|---|---|
| Master data referenced by records | `RESTRICT` | A branch with patients in it cannot be deleted |
| Children owned by a parent | `CASCADE` | Invoice items die with the invoice — but the invoice itself cannot be deleted |
| Optional cross-module reference | `SET NULL` | An encounter is deleted from a prescription's view without deleting the prescription |
| Polymorphic (`audit_logs`, `timeline`) | **No FK** | MySQL cannot express it; enforced in the service layer and stated openly |

### On "each module must be independent"

The brief asks for module independence. **Foreign keys across module tables are
kept anyway**, and this is deliberate ([ADR 0012](adr/0012-cross-module-directories.md)):

- Isolation is enforced in **code** — a Deptrac layer rule and an architecture
  test fail the build if `Modules\Billing` imports `Modules\Patients\Models`.
- Referential integrity is enforced in the **database**, because a single MySQL
  database with orphaned `patient_id`s is a corrupted clinical record, and no
  amount of architectural purity is worth that.

Modules communicate through five mechanisms, none of which crosses a namespace
boundary: **directories** (reads), **events** (notifications), **published
contracts** (synchronous writes), **screen slots** (UI), and **ports in core**
(the charging port). All five are documented in `docs/MODULE-AUTHORING.md` §5.

---

## 6. Deliberate denormalisations

3NF is the default. Five exceptions, each with a reason that outweighs it:

| Denormalisation | Why |
|---|---|
| Drug name, strength, form copied onto `prescription_items` | The formulary is editable. A prescription written in 2026 must read the same in 2031 |
| Price, tax name and rate copied onto `invoice_items` / `invoice_item_taxes` | An invoice states what was owed on the day it was issued |
| Reference range copied onto `lab_results` | Ranges are revised. A result reinterpreted against a range that did not exist when it was reported is a different result |
| Invoice totals stored, not derived | Same reason, plus a debtor list becomes one indexed read |
| Timeline titles rendered at write time | The timeline is a historical narrative, not a live join |

Every one is the same principle: **a document copies what it needs; it never
renders itself by joining to a mutable table.**

---

## 7. Index strategy

1. **Every foreign key is indexed.** MySQL creates these automatically for
   InnoDB FK constraints; composite indexes are added where the FK is only part
   of the access path.
2. **Every `ulid` is unique-indexed.** It is the only public identifier and every
   route binding resolves through it.
3. **Composite indexes are ordered by selectivity, then by range column last** —
   `(patient_id, occurred_at)`, not the reverse.
4. **Soft-deletable uniques include `deleted_at`** — `UNIQUE (branch_id, code,
   deleted_at)` — so a withdrawn code can be reused.
5. **Blind indexes are `CHAR(64)`** holding a HMAC, unique per branch, so an
   encrypted national ID is still searchable by exact match.
6. **Covering indexes on the hot paths:** the timeline, the outstanding-invoice
   list, the laboratory worklist, and the appointment slot lookup.
7. **No index on low-cardinality columns alone.** `is_active` is never indexed
   by itself; it appears as the trailing column of a composite.

---

## 8. Scalability

**Per-clinic volume.** A busy 5-doctor clinic generates roughly 30,000
appointments, 30,000 encounters, 25,000 prescriptions, 20,000 invoices and
200,000 timeline events per year. After ten years the largest table is the
timeline at ~2M rows and the audit log at ~5M. Both are indexed for their only
access pattern and neither is joined in a hot path. **This is comfortably inside
what a single shared-hosting MySQL instance handles.**

**What was designed for and is not yet needed:**

| Pressure | Ready today | Action when it arrives |
|---|---|---|
| Multiple branches | `branch_id` everywhere + global scope | None — it works now |
| Audit log growth | `audit.retention_days` setting, scheduler prune | Archive to a cold table |
| Timeline growth | Composite index, no joins | `PARTITION BY RANGE (YEAR(occurred_at))` |
| Reporting load | Queries hit indexed columns | Read replica, or nightly summary tables |
| Multi-currency | Currency stored beside every amount | None — it works now |
| Multi-tax jurisdictions | `invoice_item_taxes` is one row per rate | None — it works now |
| Insurance / third-party payers | Table shaped, FK column present | Build the claims module |
| Patient portal | `is_visible_to_patient` on the timeline | Build the portal |
| Mobile app | No business logic in controllers; services take ids and DTOs | Add API controllers over the same services |

**What deliberately does not scale, and why that is correct.** This is
one database per clinic. There is no sharding strategy, no tenant column, and no
cross-clinic query — because there is no cross-clinic anything. A clinic that
outgrows a single MySQL instance has outgrown this product's deployment model,
not its schema.

---

## 9. Decisions needed before anything is built

### 9.1 ULID, not UUID

The brief says "use UUID where beneficial". The system already uses **ULID**
([ADR 0004](adr/0004-identifiers-money-and-time.md)) on all 57 tables.

| | UUIDv4 | ULID |
|---|---|---|
| Storage | 36 chars | **26 chars** |
| Sortable | No | **Yes, by creation time** |
| Index locality | Random inserts fragment the B-tree | **Monotonic — appends to the right** |

On a table with millions of rows, random UUIDv4 primary or unique keys cause
measurable page-split churn. ULID does not. Both are 128-bit and neither leaks a
sequence count.

**Recommendation: keep ULID.** Changing now means rewriting the identifier on
every row of 57 tables and every URL. Say the word if you want UUID anyway and
I will cost it properly.

### 9.2 Age: derived, not stored

The brief lists both DOB and Age as patient fields. **Age is a function of DOB
and today's date.** Storing it is a transitive dependency — a 3NF violation — and
it goes stale silently at midnight.

It is not academic. Laboratory reference ranges are matched on **age in days**;
paediatric bands are narrow. A stored age drifting by even a day flags a neonate
against the wrong normal, and the failure is silent.

`Patient::age()` and `ageLabel()` already exist and are used throughout.

**Recommendation: keep age derived.** If you need age for reporting speed, the
right answer is a computed column or a reporting view, not a stored field the
application must remember to update.

### 9.3 Timeline: materialised, not a union

Explained in §4.1. The alternative is correct-but-unusable.

**Recommendation: materialised table with event-listener writes and a rebuild
command.** The cost is that it can theoretically drift; the rebuild command and
same-transaction writes are the mitigation, and the alternative makes the patient
profile the slowest page in the product.

---

## 10. Build order, once approved

| Step | Tables | Depends on |
|---|---|---|
| 1 | `departments` + 3 nullable FK columns | Nothing |
| 2 | `patient_alerts`, `patient_tags`, `patient_tag_assignments`, `patient_history_entries`, `patients.country` | Nothing |
| 3 | `patient_document_versions` + backfill of existing documents | Nothing |
| 4 | `patient_timeline_events` + listeners + `timeline:rebuild` | Steps 1–3, so their events are captured |
| 5 | Appointment columns: priority, visit_type, follow-up | Step 1 |
| 6 | `patient_insurance` + `invoices.patient_insurance_id` | Nothing |
| 7 | `notifications`, `notification_deliveries` | Nothing |
| 8 | `license_state`, `license_events` | Phase 8 |
| 9 | `report_definitions` | Optional — Phase 7 |

Steps 1–3 are independent and can be built in one pass. **Step 4 is the large
one** and should be its own phase with its own tests.

Total: **11 new tables, 8 additive nullable columns, 1 backfill.** No destructive
change to any existing table.