# 07 — Database Schema Expansion (Phase 6–13, master index)

Same conventions as `02-database-schema.md`: `$table->foreignId('x_id')->constrained('table')`
(`nullOnDelete()` for optional links, `restrictOnDelete()` for FKs that must not dangle),
`created_by`/`updated_by` → nullable FK to `admins`, module-prefixed table names (`clinic_...`),
no `SoftDeletes` on transactional tables. Column-by-column definitions live in the matching
`models/*.md` file; this file is the index and build order.

## Reused as-is (no migration needed)

| Table | Model | Note |
|---|---|---|
| `clinic_bookings` | `Booking` | `Encounter` optionally links to a booking, but can also stand alone (walk-in follow-up not tied to a slot). |
| `clinic_doctors` | `Doctor` | Nutritionists are `Doctor` rows too — no new provider table. |
| `clinic_specialties` | `Specialty` | Extended with a "Therapeutic Nutrition" seed row; no schema change. |
| `clinic_medical_history_options` | `MedicalHistoryOption` | Reused for any new small controlled vocabulary (e.g. surgery procedure type list) via its existing `type` enum. |
| `App\Models\Sales\Customer` / `finance_receipt_vouchers` / `finance_journal_entries` | Finance/Sales | Reused for Phase 11 billing — see that section below. |

## Altered

| Migration (new file) | Table | Change |
|---|---|---|
| `..._add_clinic_invoice_id_to_receipt_vouchers_table.php` | `finance_receipt_vouchers` | `$table->foreignId('clinic_invoice_id')->nullable()->after('invoice_reference')->constrained('clinic_invoices')->nullOnDelete();` — see Phase 11. General-purpose fix (not clinic-specific in effect), closes the same gap that exists for Sales invoices today. |
| `..._add_specialty_id_to_clinic_medical_history_options...` *(only if the `type` enum needs a new value)* | `clinic_medical_history_options` | No column change expected; only a new seeded row(s) for `type = 'surgery_procedure'` if that vocabulary is added here instead of a dedicated catalog (decide during Phase 10 build — see `models/SurgeryCase.md`). |

## New tables — build order (respects FK dependencies)

### Phase 6 — Structured Clinical Core (EMR)

| # | Migration filename (pattern) | Table | Purpose |
|---|---|---|---|
| 1 | `..._create_clinic_encounters_table.php` | `clinic_encounters` | One row per clinical visit/documentation event — chief complaint, history, exam, assessment, plan. Optionally linked to a `Booking`. Supersedes `clinic_medical_records` as the primary record. |
| 2 | `..._create_clinic_vitals_table.php` | `clinic_vitals` | Weight/height/BMI/waist/BP/pulse/temperature/glucose, one row per encounter. Foundation for every weight/BP/glucose trend used in Phase 7 and Phase 13. |
| 3 | `..._create_clinic_diagnoses_table.php` | `clinic_diagnoses` | Structured, trackable problem list per patient (active/resolved/chronic), not free text. |

`clinic_medical_records` is **not dropped** — it stays for existing rows and as a fallback quick-note field; new encounters go through `clinic_encounters` going forward. See `06-overview-medical-center-expansion.md` §Reuse map.

### Phase 7 — Specialty Modules

| # | Migration filename (pattern) | Table | Purpose |
|---|---|---|---|
| 4 | `..._create_clinic_diabetes_screenings_table.php` | `clinic_diabetes_screenings` | Periodic complication screening (retinopathy, nephropathy/microalbumin, neuropathy/monofilament, foot exam), 1:1-optional with an encounter. |
| 5 | `..._create_clinic_bariatric_assessments_table.php` | `clinic_bariatric_assessments` | BMI at assessment, comorbidities checklist, psych-eval flag, proposed procedure, surgical readiness — 1:1-optional with an encounter. |
| 6 | `..._create_clinic_nutrition_plans_table.php` | `clinic_nutrition_plans` | Calorie target, macro split, diet type, follow-up cadence, dietitian notes — 1:1-optional with an encounter. |

All three are **optional extensions** of `clinic_encounters` (nullable `encounter_id` FK, not every encounter has one) — not every visit needs a bariatric assessment or a nutrition plan.

### Phase 8 — Pharmacy & e-Prescriptions

| # | Migration filename (pattern) | Table | Purpose |
|---|---|---|---|
| 7 | `..._create_clinic_medications_table.php` | `clinic_medications` | Lightweight drug master (name, generic name, form, strength) for autocomplete/reporting — **not** an inventory item, no stock column. |
| 8 | `..._create_clinic_prescriptions_table.php` | `clinic_prescriptions` | One prescription per encounter. |
| 9 | `..._create_clinic_prescription_items_table.php` | `clinic_prescription_items` | Line items: medication (FK, nullable if free-text drug not in the catalog), dose, frequency, duration, route, instructions. |

Replaces the free-text `prescription` column's role going forward (that column stays on `clinic_medical_records` for old rows).

### Phase 9 — Lab & Radiology Orders

| # | Migration filename (pattern) | Table | Purpose |
|---|---|---|---|
| 10 | `..._create_clinic_lab_test_catalog_table.php` | `clinic_lab_test_catalog` | Test name, unit, normal range — seeded with HbA1c, Fasting Glucose, Lipid Profile, TSH/T3/T4, Creatinine/eGFR, Microalbumin. |
| 11 | `..._create_clinic_lab_orders_table.php` | `clinic_lab_orders` | One order per encounter, may request multiple tests. |
| 12 | `..._create_clinic_lab_results_table.php` | `clinic_lab_results` | One row per test per order: value, unit, flag (normal/high/low), entered manually (no lab-device integration). Feeds the HbA1c/glucose/lipid trend views used by Phase 7 and Phase 13. |

### Phase 10 — Surgery Scheduling & Documentation

| # | Migration filename (pattern) | Table | Purpose |
|---|---|---|---|
| 13 | `..._create_clinic_surgery_cases_table.php` | `clinic_surgery_cases` | Procedure type, surgeon, assistants (json), anesthesiologist, scheduled date/time, duration estimate, status — **no room/bed FK** per the no-admissions decision. |
| 14 | `..._create_clinic_surgery_preop_assessments_table.php` | `clinic_surgery_preop_assessments` | Fitness-for-surgery checklist, labs-reviewed flag, fasting-confirmed flag, consent signed-at + file path. |

Post-op notes are **not** a new table — they're a `clinic_encounters` row with `encounter_type = 'post_op'`, linked to the `clinic_surgery_cases` row via a nullable `surgery_case_id` on `clinic_encounters` (added in this phase's migration set). This keeps one documentation model instead of two.

### Phase 11 — Finance Integration

| # | Migration filename (pattern) | Table | Purpose |
|---|---|---|---|
| 15 | `..._create_clinic_invoices_table.php` | `clinic_invoices` | Mirrors `Sales\Invoice` shape: `customer_id` (→ `Sales\Customer`, auto-provisioned), `subtotal`, `tax_amount`, `total_amount`, `paid_amount`, `status`, `payment_status`, `journal_entry_id`, `posted_at`. |
| 16 | `..._create_clinic_invoice_items_table.php` | `clinic_invoice_items` | One line per billable service (consultation, procedure, lab test, surgery) — each line resolves its own revenue account (see `models/ClinicInvoice.md`). |
| — | `..._add_clinic_invoice_id_to_receipt_vouchers_table.php` | `finance_receipt_vouchers` | See "Altered" above — the FK fix. |

No new Chart-of-Accounts seeder exists anywhere in this project (confirmed by search); new Revenue/AR accounts for the clinic are created manually via the existing account-management screen, same as every other module's accounts today. New Settings keys (`clinic_journal_id`, `default_clinic_ar_account_id`, `default_clinic_revenue_account_id`) are documented in `flows/12-billing-and-posting-flow.md`, managed via the existing generic `SettingService` — no new migration needed since `settings` is a generic key/value table.

### Phase 12 & 13 — no new tables

Phase 12 (print center) renders existing data as PDF/print views — no schema change. Phase 13
(reporting) adds query methods to `ClinicReportService` against tables already created above — no
schema change.

## Why `clinic_encounters` instead of extending `clinic_medical_records` in place

`clinic_medical_records` is hard-wired 1:1 to a `Booking` (`booking_id` unique FK) and holds only
free text. Chronic-disease management (diabetes, obesity, endocrine) needs encounters that
**aren't** always tied to a paid booking slot (e.g. a nurse recording a walk-in weight check,
or a post-op note days after the surgery booking), and needs the fields queryable for trending
(a `bmi` column, not a paragraph mentioning weight). Altering the existing table in place would
break its `booking_id` uniqueness assumption and mix two incompatible shapes in one table — a new
table alongside it, with the old one kept for backward compatibility, is the lower-risk path
(same reasoning `02-database-schema.md` used for keeping `branches` untouched via `clinic_profiles`).

## Why specialty tables are optional 1:1 extensions of `clinic_encounters` rather than one wide table

A single `clinic_encounters` table with every specialty's fields bolted on would grow one column
per specialty forever and be mostly NULL on every row (an internal-medicine follow-up has no
bariatric fields, a nutrition-only visit has no diabetes-screening fields). Separate
optionally-linked tables (`clinic_diabetes_screenings`, `clinic_bariatric_assessments`,
`clinic_nutrition_plans`) keep `clinic_encounters` generic and let each specialty module evolve
its own fields independently — same reasoning `02-database-schema.md` used for keeping
`clinic_doctor_branch` (commercial facts) separate from `clinic_doctor_schedules` (recurring
availability).