# Models: LabTestCatalog + LabOrder + LabResult

**Tables**: `clinic_lab_test_catalog`, `clinic_lab_orders`, `clinic_lab_results` (new, Phase 9)

## Purpose

Manual-entry lab/radiology ordering and results — no lab-device or LIS integration in this phase.
The primary data feed for the diabetes/lipid trend views introduced conceptually in Phase 7 and
wired to real data here.

## Fields — `clinic_lab_test_catalog`

| Column | Type | Constraints | Notes |
|---|---|---|---|
| `id` | bigint | PK | |
| `name` | string | | e.g. "HbA1c", "Fasting Blood Glucose", "TSH" |
| `unit` | string | nullable | e.g. "%", "mg/dL", "mIU/L" |
| `reference_range_low` | decimal(8,2) | nullable | |
| `reference_range_high` | decimal(8,2) | nullable | |
| `is_active` | boolean | default `true` | |

Seeded at minimum with: HbA1c, Fasting Glucose, Lipid Profile (or split into Total
Cholesterol/LDL/HDL/Triglycerides as separate catalog rows — decide at build time based on how
granularly results should trend), TSH, T3, T4, Creatinine, eGFR, Microalbumin.

## Fields — `clinic_lab_orders`

| Column | Type | Constraints | Notes |
|---|---|---|---|
| `id` | bigint | PK | |
| `encounter_id` | bigint | `foreignId('encounter_id')->constrained('clinic_encounters')->cascadeOnDelete()` | |
| `patient_id` | bigint | `foreignId('patient_id')->constrained('users')->restrictOnDelete()` | denormalized, same reasoning as `Diagnosis.patient_id` |
| `ordered_by_doctor_id` | bigint | `foreignId('ordered_by_doctor_id')->constrained('clinic_doctors')->restrictOnDelete()` | |
| `status` | string | default `'ordered'` | `ordered` → `resulted` / `cancelled` |
| `ordered_at` | datetime | default `now()` | |
| `created_at` / `updated_at` | timestamp | | |

## Fields — `clinic_lab_results`

| Column | Type | Constraints | Notes |
|---|---|---|---|
| `id` | bigint | PK | |
| `lab_order_id` | bigint | `foreignId('lab_order_id')->constrained('clinic_lab_orders')->cascadeOnDelete()` | |
| `lab_test_catalog_id` | bigint | `foreignId('lab_test_catalog_id')->constrained('clinic_lab_test_catalog')->restrictOnDelete()` | |
| `value` | decimal(8,2) | | |
| `unit` | string | nullable | copied from the catalog at result time, same "snapshot" reasoning as `Booking.price` |
| `flag` | string | nullable | `normal` / `high` / `low`, computed against the catalog range on save |
| `resulted_at` | datetime | default `now()` | |
| `entered_by_doctor_id` | bigint | `foreignId('entered_by_doctor_id')->nullable()->constrained('clinic_doctors')->nullOnDelete()` | |
| `created_at` / `updated_at` | timestamp | | |

## Relationships

| Relation | Type | Target |
|---|---|---|
| `LabOrder::encounter()` | `belongsTo` | `Encounter` |
| `LabOrder::patient()` | `belongsTo` | `User` |
| `LabOrder::orderedByDoctor()` | `belongsTo` | `Doctor` |
| `LabOrder::results()` | `hasMany` | `LabResult` |
| `LabResult::labOrder()` | `belongsTo` | `LabOrder` |
| `LabResult::test()` | `belongsTo` | `LabTestCatalog` |

## Decisions

- One order can request multiple tests, and results come back one row per test — matches how lab
  panels actually work (a "Diabetes Panel" order yields separate HbA1c and Fasting Glucose
  results), and keeps each result independently queryable for trending, rather than one row per
  order with N nullable value columns.
- `flag` is computed and stored (not derived at read time) so list/trend views can filter
  `where('flag', 'high')` directly without recomputing against the catalog range on every query.
- No device/LIS integration fields — results are typed in by a doctor or lab staff, matching the
  "manual entry" scope confirmed for this phase.