# Munal LMS — Database Schema Reference (`SCHEMA.md`)

> **System**: Munal Laboratory Management System (LMS)  
> **Database**: PostgreSQL 16+  
> **ORM**: SQLAlchemy 2.0 (AsyncIO)  
> **Migration Revision**: `6d38ab84d20f` (`initial_schema`)  
> **Total Tables**: 25  
> **Total Named Enums**: 15  
> **Architecture Spec**: [Tech-Reference-Spec.md](file:///d:/Atulesh/Projects/Munal/munalmedical-be/docs/Tech-Reference-Spec.md)  
> **Last Updated**: 2026-09-20  

---

## 1. Global Architectural Conventions

All 25 tables adhere strictly to the following cross-cutting database design rules:

### 1.1 Primary Keys
- **Type**: PostgreSQL `UUID` (`UUID(as_uuid=True)`).
- **Default**: Server-side default `gen_random_uuid()` (PostgreSQL 16 built-in, zero extension requirement).
- **Constraint**: Primary key naming follows `pk_<table>`.
- **Rationale**: Prevents enumeration attacks, enables distributed or client-generated IDs when necessary, and guarantees collision-free branch data consolidation.

### 1.2 Constraint Naming Conventions
Alembic and SQLAlchemy use an explicit `MetaData(naming_convention=...)` dictionary:
- **Primary Key (`pk`)**: `pk_%(table_name)s`
- **Foreign Key (`fk`)**: `fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s`
- **Unique Constraint (`uq`)**: `uq_%(table_name)s_%(column_0_name)s` (or composite `uq_<table>_<col1>_<col2>`)
- **Index (`ix`)**: `ix_%(table_name)s_%(column_0_name)s`
- **Check Constraint (`ck`)**: `ck_%(table_name)s_%(constraint_name)s`

### 1.3 Timestamps
- All entities carry `created_at` and `updated_at` via `TimestampMixin`.
- Data type is PostgreSQL `TIMESTAMP WITH TIME ZONE` (`TIMESTAMPTZ`).
- `created_at`: `server_default=sa.text("now()")`, non-nullable.
- `updated_at`: `server_default=sa.text("now()")`, `onupdate=func.now()`, non-nullable.

### 1.4 Soft Deletion
Entities that represent medical, financial, or regulatory records use `SoftDeleteMixin`:
- Column: `deleted_at TIMESTAMPTZ NULL DEFAULT NULL`.
- `deleted_at IS NULL` signifies an active record.
- **Tables using Soft Delete**: `lab`, `user`, `patient`, `test_category`, `test`, `test_package`, `visit_order`, `order_test`, `order_package`, `sample`, `report`, `discount_rule`, `referral`.
- **Tables using Physical Delete (Hard Delete / Cascade)**: `permission`, `role_permission`, `user_permission_override`, `test_parameter`, `test_parameter_range`, `test_package_item`, `appointment_slot`, `appointment`, `bill`, `payment`, `commission_entry`, `notification`.

### 1.5 Branch Scoping (`lab_id`)
Munal LMS is architecturally designed as single-tenant with multi-branch capabilities.
- The `lab` table holds branch records (e.g., Main Lab, Satellite Collection Center).
- Branch-scoped entities inherit `LabScopedMixin`, providing:
  - `lab_id UUID NOT NULL REFERENCES lab(id) ON DELETE RESTRICT`
  - Explicit B-tree index on `lab_id` (`ix_<table>_lab_id`).
- All queries in branch context MUST filter by `lab_id`.

### 1.6 Monetary & Quantity Precision
- Monetary amounts (`subtotal`, `tax_amount`, `total_amount`, `paid_amount`, `balance_amount`, `unit_price`, `commission_amount`) are stored as `Numeric(10, 2)` or `Numeric(12, 2)`.
- Floating-point representations (`FLOAT`, `REAL`, `DOUBLE`) are strictly forbidden.
- Check constraints enforce non-negative values across all financial ledgers.

---

## 2. PostgreSQL Named ENUM Types (15 Total)

All enum types are defined as native PostgreSQL enums with explicit types and names:

| Enum Type Name | Python Enum | Values | Used In Tables & Columns |
|---|---|---|---|
| `user_role` | `UserRole` | `superadmin`, `admin`, `manager`, `doctor`, `pathologist`, `radiologist`, `receptionist`, `phlebotomist`, `accountant`, `general_user` | `user.role`, `role_permission.role` |
| `gender` | `Gender` | `male`, `female`, `other` | `patient.gender`, `test_parameter_range.gender` |
| `test_result_type` | `TestResultType` | `numeric`, `text`, `options`, `document` | `test.result_type` |
| `appointment_status` | `AppointmentStatus` | `pending`, `confirmed`, `completed`, `cancelled`, `no_show` | `appointment.status` |
| `booking_channel` | `BookingChannel` | `walk_in`, `phone`, `online` | `appointment.channel` |
| `order_status` | `OrderStatus` | `registered`, `sample_collected`, `processing`, `completed`, `cancelled` | `visit_order.status` |
| `order_test_status` | `OrderTestStatus` | `pending`, `sample_collected`, `in_progress`, `completed`, `cancelled` | `order_test.status` |
| `sample_status` | `SampleStatus` | `pending`, `collected`, `rejected`, `in_lab`, `analyzed`, `disposed` | `sample.status` |
| `report_status` | `ReportStatus` | `draft`, `entered`, `verified`, `released`, `cancelled` | `report.status` |
| `discount_type` | `DiscountType` | `percentage`, `fixed` | `discount_rule.discount_type` |
| `bill_status` | `BillStatus` | `unpaid`, `partial`, `paid`, `void` | `bill.status` |
| `payment_mode` | `PaymentMode` | `cash`, `card`, `online`, `cheque`, `credit` | `payment.payment_mode` |
| `referral_type` | `ReferralType` | `doctor`, `hospital`, `clinic`, `agent` | `referral.type` |
| `notification_channel` | `NotificationChannel` | `sms`, `email`, `portal` | `notification.channel` |
| `notification_status` | `NotificationStatus` | `pending`, `sent`, `failed`, `delivered` | `notification.status` |

---

## 3. Entity Summary Table

| # | Table Name | Domain Module | Scoping | Soft Delete | Primary Responsibility |
|---|---|---|---|---|---|
| 1 | `lab` | Core / Organization | Organization root | Yes | Branch profile, name, branding logo |
| 2 | `user` | RBAC & Staff | Branch (`lab_id`) | Yes | Staff login credentials, role, active status |
| 3 | `permission` | RBAC & Staff | Global | No | System capability keys (`code`) |
| 4 | `role_permission` | RBAC & Staff | Global | No | Default permission grants per role |
| 5 | `user_permission_override` | RBAC & Staff | Via `user_id` | No | Per-staff explicit grant/deny override |
| 6 | `patient` | Patient Registry | Branch (`lab_id`) | Yes | Patient demographics, mobile, blood group |
| 7 | `test_category` | Catalog | Branch (`lab_id`) | Yes | Grouping of tests (e.g., Biochemistry, Hematology) |
| 8 | `test` | Catalog | Branch (`lab_id`) | Yes | Individual diagnostic test definitions & base prices |
| 9 | `test_parameter` | Catalog | Via `test_id` | No | Analytes/parameters measured under a test |
| 10 | `test_parameter_range` | Catalog | Via `parameter_id` | No | Age/gender-specific normal reference intervals |
| 11 | `test_package` | Catalog | Branch (`lab_id`) | Yes | Bundled wellness profiles & package prices |
| 12 | `test_package_item` | Catalog | Via `package_id` | No | Membership mapping tests into packages |
| 13 | `appointment_slot` | Scheduling | Branch (`lab_id`) | No | Daily appointment windows and capacity limits |
| 14 | `appointment` | Scheduling | Branch (`lab_id`) | No | Patient booking records and lifecycle state |
| 15 | `visit_order` | Orders & Visits | Branch (`lab_id`) | Yes | Core patient encounter order and tracking |
| 16 | `order_test` | Orders & Visits | Via `order_id` | Yes | Ordered test line item with price snapshot |
| 17 | `order_package` | Orders & Visits | Via `order_id` | Yes | Ordered package line item with price snapshot |
| 18 | `sample` | Lab Workflow | Branch (`lab_id`) | Yes | Specimen barcoding, tube type, collection status |
| 19 | `report` | Lab Workflow | Via `order_id` | Yes | Diagnostic report version, structured findings, verification |
| 20 | `discount_rule` | Billing & Finance | Branch (`lab_id`) | Yes | Configurable discount policies and criteria |
| 21 | `bill` | Billing & Finance | Branch (`lab_id`) | No | 1:1 visit order invoice and balance ledger |
| 22 | `payment` | Billing & Finance | Branch (`lab_id`) | No | Payment transaction receipts (split payments) |
| 23 | `referral` | Partner Network | Branch (`lab_id`) | Yes | Referring doctors, clinics, commission agreements |
| 24 | `commission_entry` | Partner Network | Branch (`lab_id`) | No | Commission ledger entries and payout tracking |
| 25 | `notification` | Dispatch & Comms | Branch (`lab_id`) | No | Outbound SMS/Email delivery log and status |

---

## 4. Detailed Table-by-Table Reference

---

### 4.1 `lab`
- **Domain**: Core Organization & Branches
- **Lab Scoped**: Root entity
- **Soft Delete**: Yes (`deleted_at`)

#### Columns
| Column | Type | Nullable | Default | Constraints & Indexes | Description |
|---|---|---|---|---|---|
| `id` | `UUID` | No | `gen_random_uuid()` | `PK` | Primary key |
| `name` | `VARCHAR(200)` | No | — | — | Lab/branch display name (printed on reports) |
| `address` | `TEXT` | Yes | — | — | Physical address of the branch |
| `logo_url` | `VARCHAR(500)` | Yes | — | — | Storage URL or relative path for lab header logo |
| `created_at` | `TIMESTAMPTZ` | No | `now()` | — | Record creation timestamp (UTC) |
| `updated_at` | `TIMESTAMPTZ` | No | `now()` | — | Record update timestamp (UTC) |
| `deleted_at` | `TIMESTAMPTZ` | Yes | `NULL` | — | Soft-delete timestamp |

#### Inbound Relationships
- `lab.users` → `user.lab_id`
- `lab.patients` → `patient.lab_id`
- `lab.test_categories` → `test_category.lab_id`
- `lab.tests` → `test.lab_id`
- `lab.test_packages` → `test_package.lab_id`
- `lab.appointment_slots` → `appointment_slot.lab_id`
- `lab.appointments` → `appointment.lab_id`
- `lab.orders` → `visit_order.lab_id`
- `lab.samples` → `sample.lab_id`
- `lab.bills` → `bill.lab_id`
- `lab.payments` → `payment.lab_id`
- `lab.discount_rules` → `discount_rule.lab_id`
- `lab.referrals` → `referral.lab_id`
- `lab.commissions` → `commission_entry.lab_id`
- `lab.notifications` → `notification.lab_id`

---

### 4.2 `permission`
- **Domain**: Access Control & RBAC
- **Lab Scoped**: No (System-wide capability catalog)
- **Soft Delete**: No (Hard delete / seed-managed)

#### Columns
| Column | Type | Nullable | Default | Constraints & Indexes | Description |
|---|---|---|---|---|---|
| `id` | `UUID` | No | `gen_random_uuid()` | `PK` | Primary key |
| `code` | `VARCHAR(100)` | No | — | `UNIQUE`, `INDEX` | Stable machine key (e.g. `patient:create`, `report:verify`) |
| `description` | `VARCHAR(500)` | No | — | — | Human-readable capability description |
| `created_at` | `TIMESTAMPTZ` | No | `now()` | — | Creation timestamp |
| `updated_at` | `TIMESTAMPTZ` | No | `now()` | — | Update timestamp |

#### Inbound Relationships
- `permission.role_permissions` → `role_permission.permission_id` (CASCADE)
- `permission.user_overrides` → `user_permission_override.permission_id` (CASCADE)

---

### 4.3 `role_permission`
- **Domain**: Access Control & RBAC
- **Lab Scoped**: No (System-wide role mapping)
- **Soft Delete**: No

#### Columns
| Column | Type | Nullable | Default | Constraints & Indexes | Description |
|---|---|---|---|---|---|
| `id` | `UUID` | No | `gen_random_uuid()` | `PK` | Primary key |
| `role` | `user_role` | No | — | `INDEX`, Composite `UQ` | Target staff role |
| `permission_id` | `UUID` | No | — | `FK → permission.id ON DELETE CASCADE`, `INDEX`, Composite `UQ` | Assigned permission |
| `default_granted` | `BOOLEAN` | No | `TRUE` | — | `TRUE` = granted by default, `FALSE` = denied by default |
| `created_at` | `TIMESTAMPTZ` | No | `now()` | — | Creation timestamp |
| `updated_at` | `TIMESTAMPTZ` | No | `now()` | — | Update timestamp |

#### Constraints
- `uq_role_permission_role_permission_id`: UNIQUE (`role`, `permission_id`)

---

### 4.4 `user`
- **Domain**: Access Control & Staff Accounts
- **Lab Scoped**: Yes (`lab_id`)
- **Soft Delete**: Yes (`deleted_at`) — Preserves audit history across orders, reports, and payments.

#### Columns
| Column | Type | Nullable | Default | Constraints & Indexes | Description |
|---|---|---|---|---|---|
| `id` | `UUID` | No | `gen_random_uuid()` | `PK` | Primary key |
| `lab_id` | `UUID` | No | — | `FK → lab.id ON DELETE RESTRICT`, `INDEX`, Composite `UQ` | Branch assignment |
| `name` | `VARCHAR(200)` | No | — | — | Full name of staff member |
| `phone` | `VARCHAR(20)` | No | — | `INDEX`, Composite `UQ` | Login identifier (mobile number) |
| `email` | `VARCHAR(255)` | Yes | — | Composite `UQ` | Contact email for notifications |
| `role` | `user_role` | No | — | `INDEX` | Staff role (`superadmin`, `pathologist`, etc.) |
| `hashed_password` | `VARCHAR(255)` | No | — | — | Bcrypt-hashed password |
| `is_active` | `BOOLEAN` | No | `TRUE` | — | Account suspension flag |
| `created_at` | `TIMESTAMPTZ` | No | `now()` | — | Creation timestamp |
| `updated_at` | `TIMESTAMPTZ` | No | `now()` | — | Update timestamp |
| `deleted_at` | `TIMESTAMPTZ` | Yes | `NULL` | — | Soft-delete timestamp |

#### Constraints
- `uq_user_lab_id_phone`: UNIQUE (`lab_id`, `phone`)
- `uq_user_lab_id_email`: UNIQUE (`lab_id`, `email`)

---

### 4.5 `user_permission_override`
- **Domain**: Access Control & Granular Overrides
- **Lab Scoped**: Indirect via `user_id`
- **Soft Delete**: No (Revocation is a row deletion)

#### Columns
| Column | Type | Nullable | Default | Constraints & Indexes | Description |
|---|---|---|---|---|---|
| `id` | `UUID` | No | `gen_random_uuid()` | `PK` | Primary key |
| `user_id` | `UUID` | No | — | `FK → user.id ON DELETE CASCADE`, `INDEX`, Composite `UQ` | Subject staff user |
| `permission_id` | `UUID` | No | — | `FK → permission.id ON DELETE CASCADE`, `INDEX`, Composite `UQ` | Overridden permission |
| `is_granted` | `BOOLEAN` | No | — | — | `TRUE` = explicit grant, `FALSE` = explicit deny |
| `created_at` | `TIMESTAMPTZ` | No | `now()` | — | Creation timestamp |
| `updated_at` | `TIMESTAMPTZ` | No | `now()` | — | Update timestamp |

#### Constraints
- `uq_user_permission_override_user_id_permission_id`: UNIQUE (`user_id`, `permission_id`)

---

### 4.6 `patient`
- **Domain**: Patient Management & Master Patient Index
- **Lab Scoped**: Yes (`lab_id`)
- **Soft Delete**: Yes (`deleted_at`)

#### Columns
| Column | Type | Nullable | Default | Constraints & Indexes | Description |
|---|---|---|---|---|---|
| `id` | `UUID` | No | `gen_random_uuid()` | `PK` | Primary key |
| `lab_id` | `UUID` | No | — | `FK → lab.id ON DELETE RESTRICT`, `INDEX`, Composite `UQ` | Registration branch |
| `patient_code` | `VARCHAR(50)` | No | — | `INDEX`, Composite `UQ` | Human-friendly identifier (e.g. `P-2026-0001`) |
| `name` | `VARCHAR(200)` | No | — | `INDEX` | Patient full name |
| `gender` | `gender` | No | — | — | Biological sex (`male`, `female`, `other`) |
| `dob` | `DATE` | Yes | — | — | Date of birth (used for age reference calculation) |
| `age_years` | `INTEGER` | Yes | — | — | Captured age when DOB is unknown |
| `age_months` | `INTEGER` | Yes | — | — | Captured months (critical for infant reference ranges) |
| `age_days` | `INTEGER` | Yes | — | — | Captured days (critical for neonatal reference ranges) |
| `phone` | `VARCHAR(20)` | No | — | `INDEX` | Contact mobile number (primary search field) |
| `email` | `VARCHAR(255)` | Yes | — | — | Email for electronic report dispatch |
| `address` | `TEXT` | Yes | — | — | Residential address |
| `blood_group` | `VARCHAR(10)` | Yes | — | — | Blood group with Rh factor (e.g. `O+`, `A-`) |
| `emergency_contact_name` | `VARCHAR(200)` | Yes | — | — | Next of kin contact name |
| `emergency_contact_phone`| `VARCHAR(20)` | Yes | — | — | Next of kin phone |
| `medical_history` | `TEXT` | Yes | — | — | Clinical notes, known allergies, chronic conditions |
| `created_at` | `TIMESTAMPTZ` | No | `now()` | — | Creation timestamp |
| `updated_at` | `TIMESTAMPTZ` | No | `now()` | — | Update timestamp |
| `deleted_at` | `TIMESTAMPTZ` | Yes | `NULL` | — | Soft-delete timestamp |

#### Constraints
- `uq_patient_lab_id_patient_code`: UNIQUE (`lab_id`, `patient_code`)

---

### 4.7 `test_category`
- **Domain**: Diagnostic Test Catalog
- **Lab Scoped**: Yes (`lab_id`)
- **Soft Delete**: Yes (`deleted_at`)

#### Columns
| Column | Type | Nullable | Default | Constraints & Indexes | Description |
|---|---|---|---|---|---|
| `id` | `UUID` | No | `gen_random_uuid()` | `PK` | Primary key |
| `lab_id` | `UUID` | No | — | `FK → lab.id ON DELETE RESTRICT`, `INDEX`, Composite `UQ` | Branch ownership |
| `name` | `VARCHAR(100)` | No | — | Composite `UQ` | Category display name (e.g. `Biochemistry`) |
| `description` | `TEXT` | Yes | — | — | Department/section notes |
| `created_at` | `TIMESTAMPTZ` | No | `now()` | — | Creation timestamp |
| `updated_at` | `TIMESTAMPTZ` | No | `now()` | — | Update timestamp |
| `deleted_at` | `TIMESTAMPTZ` | Yes | `NULL` | — | Soft-delete timestamp |

#### Constraints
- `uq_test_category_lab_id_name`: UNIQUE (`lab_id`, `name`)

---

### 4.8 `test`
- **Domain**: Diagnostic Test Catalog
- **Lab Scoped**: Yes (`lab_id`)
- **Soft Delete**: Yes (`deleted_at`)

#### Columns
| Column | Type | Nullable | Default | Constraints & Indexes | Description |
|---|---|---|---|---|---|
| `id` | `UUID` | No | `gen_random_uuid()` | `PK` | Primary key |
| `lab_id` | `UUID` | No | — | `FK → lab.id ON DELETE RESTRICT`, `INDEX`, Composite `UQ` | Branch ownership |
| `category_id` | `UUID` | No | — | `FK → test_category.id ON DELETE RESTRICT`, `INDEX` | Category section |
| `name` | `VARCHAR(200)` | No | — | `INDEX` | Test full name (e.g. `Complete Blood Count`) |
| `code` | `VARCHAR(50)` | No | — | `INDEX`, Composite `UQ` | Standard test code (e.g. `CBC`, `LFT`, `TSH`) |
| `short_name` | `VARCHAR(50)` | Yes | — | — | Abbreviated label for compact layouts |
| `sample_type` | `VARCHAR(100)` | No | — | — | Specimen required (e.g. `Whole Blood EDTA`, `Serum`) |
| `method` | `VARCHAR(200)` | Yes | — | — | Methodology (e.g. `Automated Cell Counter`, `CLIA`) |
| `price` | `NUMERIC(10,2)`| No | — | `CK (price >= 0)` | Standard branch retail price |
| `cost_price` | `NUMERIC(10,2)`| Yes | — | `CK (cost_price >= 0)` | Internal reagent/processing cost |
| `tat_hours` | `INTEGER` | Yes | — | — | Turnaround time SLA in hours |
| `result_type` | `test_result_type` | No | `'numeric'` | — | Format (`numeric`, `text`, `options`, `document`) |
| `instructions` | `TEXT` | Yes | — | — | Pre-analytical prep instructions (e.g. `12 hr fasting`) |
| `is_active` | `BOOLEAN` | No | `TRUE` | — | Available for booking/ordering flag |
| `created_at` | `TIMESTAMPTZ` | No | `now()` | — | Creation timestamp |
| `updated_at` | `TIMESTAMPTZ` | No | `now()` | — | Update timestamp |
| `deleted_at` | `TIMESTAMPTZ` | Yes | `NULL` | — | Soft-delete timestamp |

#### Constraints
- `uq_test_lab_id_code`: UNIQUE (`lab_id`, `code`)
- `ck_test_price_positive`: CHECK (`price >= 0`)
- `ck_test_cost_price_positive`: CHECK (`cost_price IS NULL OR cost_price >= 0`)

---

### 4.9 `test_parameter`
- **Domain**: Diagnostic Test Catalog & Analytes
- **Lab Scoped**: Indirect via `test_id`
- **Soft Delete**: No (Hard delete / cascade with parent test)

#### Columns
| Column | Type | Nullable | Default | Constraints & Indexes | Description |
|---|---|---|---|---|---|
| `id` | `UUID` | No | `gen_random_uuid()` | `PK` | Primary key |
| `test_id` | `UUID` | No | — | `FK → test.id ON DELETE CASCADE`, `INDEX` | Parent diagnostic test |
| `name` | `VARCHAR(100)` | No | — | — | Analyte name (e.g. `Hemoglobin`, `Platelet Count`) |
| `unit` | `VARCHAR(50)` | Yes | — | — | Measurement unit (e.g. `g/dL`, `cells/mcL`) |
| `display_order` | `INTEGER` | No | `0` | — | Rendering sequence on reports |
| `options` | `JSONB` | Yes | — | — | Allowed values when `result_type = 'options'` |
| `created_at` | `TIMESTAMPTZ` | No | `now()` | — | Creation timestamp |
| `updated_at` | `TIMESTAMPTZ` | No | `now()` | — | Update timestamp |

---

### 4.10 `test_parameter_range`
- **Domain**: Diagnostic Reference Intervals
- **Lab Scoped**: Indirect via `parameter_id`
- **Soft Delete**: No

#### Columns
| Column | Type | Nullable | Default | Constraints & Indexes | Description |
|---|---|---|---|---|---|
| `id` | `UUID` | No | `gen_random_uuid()` | `PK` | Primary key |
| `parameter_id` | `UUID` | No | — | `FK → test_parameter.id ON DELETE CASCADE`, `INDEX` | Parent parameter |
| `gender` | `gender` | Yes | `NULL` | — | Specific gender interval (`NULL` = applicable to all) |
| `min_age_days` | `INTEGER` | No | `0` | — | Lower age threshold in days |
| `max_age_days` | `INTEGER` | No | `36500` | — | Upper age threshold in days (~100 yrs default) |
| `min_val` | `NUMERIC(10,3)` | Yes | — | — | Normal lower reference bound |
| `max_val` | `NUMERIC(10,3)` | Yes | — | — | Normal upper reference bound |
| `critical_low` | `NUMERIC(10,3)` | Yes | — | — | Panic low value (triggers urgent alert) |
| `critical_high`| `NUMERIC(10,3)` | Yes | — | — | Panic high value (triggers urgent alert) |
| `text_range` | `TEXT` | Yes | — | — | Qualitative normal reference (e.g. `Negative`, `Non-reactive`) |
| `created_at` | `TIMESTAMPTZ` | No | `now()` | — | Creation timestamp |
| `updated_at` | `TIMESTAMPTZ` | No | `now()` | — | Update timestamp |

#### Constraints
- `ck_test_parameter_range_min_max`: CHECK (`min_val IS NULL OR max_val IS NULL OR min_val <= max_val`)

---

### 4.11 `test_package`
- **Domain**: Test Catalog & Wellness Panels
- **Lab Scoped**: Yes (`lab_id`)
- **Soft Delete**: Yes (`deleted_at`)

#### Columns
| Column | Type | Nullable | Default | Constraints & Indexes | Description |
|---|---|---|---|---|---|
| `id` | `UUID` | No | `gen_random_uuid()` | `PK` | Primary key |
| `lab_id` | `UUID` | No | — | `FK → lab.id ON DELETE RESTRICT`, `INDEX`, Composite `UQ` | Branch ownership |
| `name` | `VARCHAR(200)` | No | — | — | Package display name (e.g. `Full Body Checkup`) |
| `code` | `VARCHAR(50)` | No | — | `INDEX`, Composite `UQ` | Package code (e.g. `PKG-WELL-01`) |
| `description` | `TEXT` | Yes | — | — | Marketing and clinical package summary |
| `price` | `NUMERIC(10,2)`| No | — | `CK (price >= 0)` | Discounted package bundle price |
| `is_active` | `BOOLEAN` | No | `TRUE` | — | Active availability flag |
| `created_at` | `TIMESTAMPTZ` | No | `now()` | — | Creation timestamp |
| `updated_at` | `TIMESTAMPTZ` | No | `now()` | — | Update timestamp |
| `deleted_at` | `TIMESTAMPTZ` | Yes | `NULL` | — | Soft-delete timestamp |

#### Constraints
- `uq_test_package_lab_id_code`: UNIQUE (`lab_id`, `code`)
- `ck_test_package_price_positive`: CHECK (`price >= 0`)

---

### 4.12 `test_package_item`
- **Domain**: Package Composition
- **Lab Scoped**: Indirect via `package_id`
- **Soft Delete**: No

#### Columns
| Column | Type | Nullable | Default | Constraints & Indexes | Description |
|---|---|---|---|---|---|
| `id` | `UUID` | No | `gen_random_uuid()` | `PK` | Primary key |
| `package_id` | `UUID` | No | — | `FK → test_package.id ON DELETE CASCADE`, `INDEX`, Composite `UQ` | Parent bundle |
| `test_id` | `UUID` | No | — | `FK → test.id ON DELETE RESTRICT`, `INDEX`, Composite `UQ` | Included diagnostic test |
| `created_at` | `TIMESTAMPTZ` | No | `now()` | — | Creation timestamp |
| `updated_at` | `TIMESTAMPTZ` | No | `now()` | — | Update timestamp |

#### Constraints
- `uq_test_package_item_package_id_test_id`: UNIQUE (`package_id`, `test_id`)

---

### 4.13 `appointment_slot`
- **Domain**: Scheduling & Capacity Management
- **Lab Scoped**: Yes (`lab_id`)
- **Soft Delete**: No

#### Columns
| Column | Type | Nullable | Default | Constraints & Indexes | Description |
|---|---|---|---|---|---|
| `id` | `UUID` | No | `gen_random_uuid()` | `PK` | Primary key |
| `lab_id` | `UUID` | No | — | `FK → lab.id ON DELETE RESTRICT`, `INDEX`, Composite `UQ` | Branch ownership |
| `date` | `DATE` | No | — | `INDEX`, Composite `UQ` | Calendar date for slot |
| `start_time` | `VARCHAR(8)` | No | — | Composite `UQ` | Start time string (e.g. `09:00:00`) |
| `end_time` | `VARCHAR(8)` | No | — | Composite `UQ` | End time string (e.g. `10:00:00`) |
| `capacity` | `INTEGER` | No | — | `CK (capacity > 0)` | Total booking capacity for this window |
| `booked_count`| `INTEGER` | No | `0` | `CK (booked_count >= 0)` | Current confirmed booking tally |
| `created_at` | `TIMESTAMPTZ` | No | `now()` | — | Creation timestamp |
| `updated_at` | `TIMESTAMPTZ` | No | `now()` | — | Update timestamp |

#### Constraints
- `uq_slot_lab_date_window`: UNIQUE (`lab_id`, `date`, `start_time`, `end_time`)
- `ck_slot_capacity`: CHECK (`capacity > 0`)
- `ck_slot_booked_count`: CHECK (`booked_count >= 0 AND booked_count <= capacity`)

---

### 4.14 `appointment`
- **Domain**: Patient Appointments & Scheduling
- **Lab Scoped**: Yes (`lab_id`)
- **Soft Delete**: No (Status transitions to `cancelled` or `no_show`)

#### Columns
| Column | Type | Nullable | Default | Constraints & Indexes | Description |
|---|---|---|---|---|---|
| `id` | `UUID` | No | `gen_random_uuid()` | `PK` | Primary key |
| `lab_id` | `UUID` | No | — | `FK → lab.id ON DELETE RESTRICT`, `INDEX` | Branch where appointment takes place |
| `slot_id` | `UUID` | Yes | — | `FK → appointment_slot.id ON DELETE SET NULL`, `INDEX` | Booked time window |
| `patient_id` | `UUID` | No | — | `FK → patient.id ON DELETE RESTRICT`, `INDEX` | Booked patient |
| `status` | `appointment_status` | No | `'pending'` | `INDEX` | Status (`pending`, `confirmed`, `completed`, `cancelled`, `no_show`) |
| `channel` | `booking_channel` | No | `'walk_in'` | — | Origin channel (`walk_in`, `phone`, `online`) |
| `scheduled_at` | `TIMESTAMPTZ` | No | — | `INDEX` | Appointment scheduled start datetime |
| `notes` | `TEXT` | Yes | — | — | Patient or receptionist booking notes |
| `created_at` | `TIMESTAMPTZ` | No | `now()` | — | Creation timestamp |
| `updated_at` | `TIMESTAMPTZ` | No | `now()` | — | Update timestamp |

---

### 4.15 `visit_order`
- **Domain**: Core Orders & Patient Encounters
- **Lab Scoped**: Yes (`lab_id`)
- **Soft Delete**: Yes (`deleted_at`)

#### Columns
| Column | Type | Nullable | Default | Constraints & Indexes | Description |
|---|---|---|---|---|---|
| `id` | `UUID` | No | `gen_random_uuid()` | `PK` | Primary key |
| `lab_id` | `UUID` | No | — | `FK → lab.id ON DELETE RESTRICT`, `INDEX`, Composite `UQ` | Servicing branch |
| `order_number` | `VARCHAR(50)` | No | — | `INDEX`, Composite `UQ` | Human-readable order code (e.g. `ORD-2026-0001`) |
| `patient_id` | `UUID` | No | — | `FK → patient.id ON DELETE RESTRICT`, `INDEX` | Subject patient |
| `appointment_id`| `UUID` | Yes | — | `FK → appointment.id ON DELETE SET NULL`, `INDEX` | Associated booking (if converted from appointment) |
| `created_by` | `UUID` | No | — | `FK → user.id ON DELETE RESTRICT`, `INDEX` | Staff user who registered the visit |
| `referral_id` | `UUID` | Yes | — | `FK → referral.id ON DELETE SET NULL`, `INDEX` | Referring doctor or clinic |
| `status` | `order_status` | No | `'registered'` | `INDEX` | Progress (`registered`, `sample_collected`, `processing`, `completed`, `cancelled`) |
| `total_amount` | `NUMERIC(10,2)`| No | `0.00` | `CK (total_amount >= 0)` | Total order value sum |
| `notes` | `TEXT` | Yes | — | — | Internal clinical/visit notes |
| `created_at` | `TIMESTAMPTZ` | No | `now()` | — | Creation timestamp |
| `updated_at` | `TIMESTAMPTZ` | No | `now()` | — | Update timestamp |
| `deleted_at` | `TIMESTAMPTZ` | Yes | `NULL` | — | Soft-delete timestamp |

#### Constraints
- `uq_visit_order_lab_id_order_number`: UNIQUE (`lab_id`, `order_number`)
- `ck_visit_order_total_amount_positive`: CHECK (`total_amount >= 0`)

---

### 4.16 `order_test`
- **Domain**: Order Line Items (Individual Tests)
- **Lab Scoped**: Indirect via `order_id`
- **Soft Delete**: Yes (`deleted_at`)

#### Columns
| Column | Type | Nullable | Default | Constraints & Indexes | Description |
|---|---|---|---|---|---|
| `id` | `UUID` | No | `gen_random_uuid()` | `PK` | Primary key |
| `order_id` | `UUID` | No | — | `FK → visit_order.id ON DELETE CASCADE`, `INDEX`, Composite `UQ` | Parent order |
| `test_id` | `UUID` | No | — | `FK → test.id ON DELETE RESTRICT`, `INDEX`, Composite `UQ` | Ordered test definition |
| `status` | `order_test_status` | No | `'pending'` | `INDEX` | Per-test lifecycle status |
| `unit_price` | `NUMERIC(10,2)` | No | — | `CK (unit_price >= 0)` | **Price snapshot** at moment of booking |
| `created_at` | `TIMESTAMPTZ` | No | `now()` | — | Creation timestamp |
| `updated_at` | `TIMESTAMPTZ` | No | `now()` | — | Update timestamp |
| `deleted_at` | `TIMESTAMPTZ` | Yes | `NULL` | — | Soft-delete timestamp |

#### Constraints
- `uq_order_test_order_id_test_id`: UNIQUE (`order_id`, `test_id`)
- `ck_order_test_positive_price`: CHECK (`unit_price >= 0`)

---

### 4.17 `order_package`
- **Domain**: Order Line Items (Test Packages)
- **Lab Scoped**: Indirect via `order_id`
- **Soft Delete**: Yes (`deleted_at`)

#### Columns
| Column | Type | Nullable | Default | Constraints & Indexes | Description |
|---|---|---|---|---|---|
| `id` | `UUID` | No | `gen_random_uuid()` | `PK` | Primary key |
| `order_id` | `UUID` | No | — | `FK → visit_order.id ON DELETE CASCADE`, `INDEX`, Composite `UQ` | Parent order |
| `package_id` | `UUID` | No | — | `FK → test_package.id ON DELETE RESTRICT`, `INDEX`, Composite `UQ` | Ordered package definition |
| `package_price` | `NUMERIC(10,2)`| No | — | `CK (package_price >= 0)`| **Price snapshot** at moment of booking |
| `created_at` | `TIMESTAMPTZ` | No | `now()` | — | Creation timestamp |
| `updated_at` | `TIMESTAMPTZ` | No | `now()` | — | Update timestamp |
| `deleted_at` | `TIMESTAMPTZ` | Yes | `NULL` | — | Soft-delete timestamp |

#### Constraints
- `uq_order_package_order_id_package_id`: UNIQUE (`order_id`, `package_id`)
- `ck_order_package_positive_price`: CHECK (`package_price >= 0`)

---

### 4.18 `sample`
- **Domain**: Specimen Collection & Barcoding
- **Lab Scoped**: Yes (`lab_id`)
- **Soft Delete**: Yes (`deleted_at`)

#### Columns
| Column | Type | Nullable | Default | Constraints & Indexes | Description |
|---|---|---|---|---|---|
| `id` | `UUID` | No | `gen_random_uuid()` | `PK` | Primary key |
| `lab_id` | `UUID` | No | — | `FK → lab.id ON DELETE RESTRICT`, `INDEX` | Processing branch |
| `order_id` | `UUID` | No | — | `FK → visit_order.id ON DELETE RESTRICT`, `INDEX` | Associated order |
| `barcode` | `VARCHAR(100)` | No | — | `UNIQUE`, `INDEX` | Scanned barcode sticker value |
| `sample_type` | `VARCHAR(100)` | No | — | — | Specimen type (e.g. `Serum`, `EDTA Whole Blood`) |
| `status` | `sample_status` | No | `'pending'` | `INDEX` | Lifecycle (`pending`, `collected`, `rejected`, `in_lab`, `analyzed`, `disposed`) |
| `collected_at` | `TIMESTAMPTZ` | Yes | — | — | Timestamp specimen was drawn |
| `collected_by` | `UUID` | Yes | — | `FK → user.id ON DELETE SET NULL`, `INDEX` | Phlebotomist user ID |
| `rejection_reason` | `TEXT` | Yes | — | — | Documented reason if rejected (e.g. `Hemolyzed`, `Insufficient volume`) |
| `created_at` | `TIMESTAMPTZ` | No | `now()` | — | Creation timestamp |
| `updated_at` | `TIMESTAMPTZ` | No | `now()` | — | Update timestamp |
| `deleted_at` | `TIMESTAMPTZ` | Yes | `NULL` | — | Soft-delete timestamp |

---

### 4.19 `report`
- **Domain**: Diagnostic Findings, Verification & PDF Release
- **Lab Scoped**: Indirect via `order_id`
- **Soft Delete**: Yes (`deleted_at`)

#### Columns
| Column | Type | Nullable | Default | Constraints & Indexes | Description |
|---|---|---|---|---|---|
| `id` | `UUID` | No | `gen_random_uuid()` | `PK` | Primary key |
| `order_id` | `UUID` | No | — | `FK → visit_order.id ON DELETE RESTRICT`, `INDEX`, Composite `UQ` | Subject visit order |
| `version` | `INTEGER` | No | `1` | Composite `UQ` | Incremental report version (1, 2, 3...) |
| `status` | `report_status` | No | `'draft'` | `INDEX` | Workflow state (`draft`, `entered`, `verified`, `released`, `cancelled`) |
| `verified_by` | `UUID` | Yes | — | `FK → user.id ON DELETE SET NULL`, `INDEX` | Pathologist/Doctor who verified the report |
| `verified_at` | `TIMESTAMPTZ` | Yes | — | — | Exact verification timestamp |
| `pdf_url` | `VARCHAR(500)` | Yes | — | — | Storage path/URL for rendered PDF document |
| `result_data` | `JSONB` | Yes | — | — | Structured parameter observations, values, abnormal flags |
| `remarks` | `TEXT` | Yes | — | — | Clinical notes and pathologist impressions |
| `created_at` | `TIMESTAMPTZ` | No | `now()` | — | Creation timestamp |
| `updated_at` | `TIMESTAMPTZ` | No | `now()` | — | Update timestamp |
| `deleted_at` | `TIMESTAMPTZ` | Yes | `NULL` | — | Soft-delete timestamp |

#### Constraints
- `uq_report_order_id_version`: UNIQUE (`order_id`, `version`)

---

### 4.20 `discount_rule`
- **Domain**: Pricing & Promotion Policies
- **Lab Scoped**: Yes (`lab_id`)
- **Soft Delete**: Yes (`deleted_at`)

#### Columns
| Column | Type | Nullable | Default | Constraints & Indexes | Description |
|---|---|---|---|---|---|
| `id` | `UUID` | No | `gen_random_uuid()` | `PK` | Primary key |
| `lab_id` | `UUID` | No | — | `FK → lab.id ON DELETE RESTRICT`, `INDEX` | Branch where discount is configured |
| `name` | `VARCHAR(100)` | No | — | — | Campaign/rule name (e.g. `Senior Citizen 15%`) |
| `discount_type`| `discount_type`| No | — | — | Calculation type (`percentage` or `fixed`) |
| `percentage` | `NUMERIC(5,2)` | Yes | — | — | Percentage discount rate (0.00 to 100.00) |
| `fixed_amount` | `NUMERIC(10,2)`| Yes | — | — | Fixed NPR discount deduction |
| `is_active` | `BOOLEAN` | No | `TRUE` | — | Rule availability flag |
| `created_at` | `TIMESTAMPTZ` | No | `now()` | — | Creation timestamp |
| `updated_at` | `TIMESTAMPTZ` | No | `now()` | — | Update timestamp |
| `deleted_at` | `TIMESTAMPTZ` | Yes | `NULL` | — | Soft-delete timestamp |

#### Constraints
- `ck_discount_rule_percentage_or_fixed`: CHECK (`(discount_type = 'percentage' AND percentage IS NOT NULL AND fixed_amount IS NULL AND percentage >= 0 AND percentage <= 100) OR (discount_type = 'fixed' AND fixed_amount IS NOT NULL AND percentage IS NULL AND fixed_amount >= 0)`)

---

### 4.21 `bill`
- **Domain**: Invoicing & Accounts Receivable
- **Lab Scoped**: Yes (`lab_id`)
- **Soft Delete**: No (Financial invoices are immutable; cancelled via `status = 'void'`)

#### Columns
| Column | Type | Nullable | Default | Constraints & Indexes | Description |
|---|---|---|---|---|---|
| `id` | `UUID` | No | `gen_random_uuid()` | `PK` | Primary key |
| `lab_id` | `UUID` | No | — | `FK → lab.id ON DELETE RESTRICT`, `INDEX` | Billing branch |
| `order_id` | `UUID` | No | — | `FK → visit_order.id ON DELETE RESTRICT`, `UNIQUE`, `INDEX` | 1:1 relation to parent order |
| `invoice_number`| `VARCHAR(50)` | No | — | `UNIQUE`, `INDEX` | Formal tax invoice series number |
| `subtotal` | `NUMERIC(10,2)`| No | — | — | Gross sum of order line items |
| `discount_amount`| `NUMERIC(10,2)`| No | `0.00`| — | Total discount applied |
| `discount_rule_id`| `UUID` | Yes | — | `FK → discount_rule.id ON DELETE SET NULL`, `INDEX` | Applied discount campaign rule |
| `tax_amount` | `NUMERIC(10,2)`| No | `0.00`| — | Calculated tax (VAT/TDS if applicable) |
| `total_amount` | `NUMERIC(10,2)`| No | — | — | Net payable: `subtotal - discount_amount + tax_amount` |
| `paid_amount` | `NUMERIC(10,2)`| No | `0.00`| — | Cumulative payments received |
| `balance_amount`| `NUMERIC(10,2)`| No | — | — | Outstanding balance: `total_amount - paid_amount` |
| `status` | `bill_status` | No | `'unpaid'`| `INDEX` | Invoice status (`unpaid`, `partial`, `paid`, `void`) |
| `created_at` | `TIMESTAMPTZ` | No | `now()` | — | Creation timestamp |
| `updated_at` | `TIMESTAMPTZ` | No | `now()` | — | Update timestamp |

#### Constraints
- `uq_bill_invoice_number`: UNIQUE (`invoice_number`)
- `uq_bill_order_id`: UNIQUE (`order_id`) (Guarantees strict 1:1 bill per visit order)
- `ck_bill_amounts`: CHECK (`subtotal >= 0 AND discount_amount >= 0 AND tax_amount >= 0 AND total_amount >= 0 AND paid_amount >= 0`)

---

### 4.22 `payment`
- **Domain**: Payment Receipts & Split-Tender
- **Lab Scoped**: Yes (`lab_id`)
- **Soft Delete**: No (Strict financial ledger)

#### Columns
| Column | Type | Nullable | Default | Constraints & Indexes | Description |
|---|---|---|---|---|---|
| `id` | `UUID` | No | `gen_random_uuid()` | `PK` | Primary key |
| `lab_id` | `UUID` | No | — | `FK → lab.id ON DELETE RESTRICT`, `INDEX` | Branch where payment was received |
| `bill_id` | `UUID` | No | — | `FK → bill.id ON DELETE RESTRICT`, `INDEX` | Target invoice |
| `receipt_number`| `VARCHAR(50)` | No | — | `UNIQUE`, `INDEX` | Official payment voucher number |
| `amount` | `NUMERIC(10,2)`| No | — | `CK (amount > 0)` | Payment value tendered |
| `payment_mode` | `payment_mode` | No | — | `INDEX` | Tender mode (`cash`, `card`, `online`, `cheque`, `credit`) |
| `transaction_ref`| `VARCHAR(100)` | Yes | — | — | External gateway ref (Fonepay, POS slip, Cheque #) |
| `received_by` | `UUID` | No | — | `FK → user.id ON DELETE RESTRICT`, `INDEX` | Staff cashier who processed receipt |
| `paid_at` | `TIMESTAMPTZ` | No | `now()` | `INDEX` | Exact moment payment was processed |
| `created_at` | `TIMESTAMPTZ` | No | `now()` | — | Creation timestamp |
| `updated_at` | `TIMESTAMPTZ` | No | `now()` | — | Update timestamp |

#### Constraints
- `uq_payment_receipt_number`: UNIQUE (`receipt_number`)
- `ck_payment_positive_amount`: CHECK (`amount > 0`)

---

### 4.23 `referral`
- **Domain**: Referral Network (Doctors, Clinics, Agents)
- **Lab Scoped**: Yes (`lab_id`)
- **Soft Delete**: Yes (`deleted_at`)

#### Columns
| Column | Type | Nullable | Default | Constraints & Indexes | Description |
|---|---|---|---|---|---|
| `id` | `UUID` | No | `gen_random_uuid()` | `PK` | Primary key |
| `lab_id` | `UUID` | No | — | `FK → lab.id ON DELETE RESTRICT`, `INDEX`, Composite `UQ` | Branch ownership |
| `name` | `VARCHAR(200)` | No | — | — | Doctor, hospital, or agent full name |
| `code` | `VARCHAR(50)` | No | — | `INDEX`, Composite `UQ` | Partner code (e.g. `DOC-001`, `CLINIC-KTM`) |
| `type` | `referral_type`| No | — | `INDEX` | Category (`doctor`, `hospital`, `clinic`, `agent`) |
| `phone` | `VARCHAR(20)` | Yes | — | — | Contact mobile |
| `email` | `VARCHAR(255)` | Yes | — | — | Contact email |
| `commission_rate`| `NUMERIC(5,2)` | No | `0.00` | `CK (0 <= rate <= 100)` | Agreed referral commission percentage |
| `is_active` | `BOOLEAN` | No | `TRUE` | — | Active partner flag |
| `created_at` | `TIMESTAMPTZ` | No | `now()` | — | Creation timestamp |
| `updated_at` | `TIMESTAMPTZ` | No | `now()` | — | Update timestamp |
| `deleted_at` | `TIMESTAMPTZ` | Yes | `NULL` | — | Soft-delete timestamp |

#### Constraints
- `uq_referral_lab_id_code`: UNIQUE (`lab_id`, `code`)
- `ck_referral_commission_rate`: CHECK (`commission_rate >= 0 AND commission_rate <= 100`)

---

### 4.24 `commission_entry`
- **Domain**: Referral Ledger & Partner Settlement
- **Lab Scoped**: Yes (`lab_id`)
- **Soft Delete**: No (Immutable accounting ledger)

#### Columns
| Column | Type | Nullable | Default | Constraints & Indexes | Description |
|---|---|---|---|---|---|
| `id` | `UUID` | No | `gen_random_uuid()` | `PK` | Primary key |
| `lab_id` | `UUID` | No | — | `FK → lab.id ON DELETE RESTRICT`, `INDEX` | Branch ledger |
| `referral_id` | `UUID` | No | — | `FK → referral.id ON DELETE RESTRICT`, `INDEX` | Credited referrer |
| `order_id` | `UUID` | No | — | `FK → visit_order.id ON DELETE RESTRICT`, `INDEX` | Qualifying visit order |
| `commission_amount`| `NUMERIC(10,2)`| No | — | `CK (commission_amount >= 0)` | Calculated commission snapshot |
| `is_settled` | `BOOLEAN` | No | `FALSE` | `INDEX` | Settlement payout status |
| `settled_at` | `TIMESTAMPTZ` | Yes | — | — | Payout execution timestamp |
| `created_at` | `TIMESTAMPTZ` | No | `now()` | — | Creation timestamp |
| `updated_at` | `TIMESTAMPTZ` | No | `now()` | — | Update timestamp |

#### Constraints
- `ck_commission_entry_positive_amount`: CHECK (`commission_amount >= 0`)

---

### 4.25 `notification`
- **Domain**: Multi-Channel Outbound Dispatch
- **Lab Scoped**: Yes (`lab_id`)
- **Soft Delete**: No (Dispatch audit log)

#### Columns
| Column | Type | Nullable | Default | Constraints & Indexes | Description |
|---|---|---|---|---|---|
| `id` | `UUID` | No | `gen_random_uuid()` | `PK` | Primary key |
| `lab_id` | `UUID` | No | — | `FK → lab.id ON DELETE RESTRICT`, `INDEX` | Originating branch |
| `recipient` | `VARCHAR(255)` | No | — | `INDEX` | Destination mobile number or email address |
| `channel` | `notification_channel`| No | — | `INDEX` | Transport medium (`sms`, `email`, `portal`) |
| `message` | `TEXT` | No | — | — | Body content dispatched |
| `status` | `notification_status` | No | `'pending'` | `INDEX` | Delivery status (`pending`, `sent`, `failed`, `delivered`) |
| `sent_at` | `TIMESTAMPTZ` | Yes | — | — | Dispatch completion timestamp |
| `error` | `TEXT` | Yes | — | — | Gateway error payload on delivery failure |
| `created_at` | `TIMESTAMPTZ` | No | `now()` | — | Creation timestamp |
| `updated_at` | `TIMESTAMPTZ` | No | `now()` | — | Update timestamp |

---

## 5. Entity Relationship Model (Mermaid Diagram)

```mermaid
erDiagram
    LAB ||--o{ USER : employs
    LAB ||--o{ PATIENT : registers
    LAB ||--o{ TEST_CATEGORY : groups
    LAB ||--o{ TEST : offers
    LAB ||--o{ TEST_PACKAGE : bundles
    LAB ||--o{ APPOINTMENT_SLOT : schedules
    LAB ||--o{ APPOINTMENT : receives
    LAB ||--o{ VISIT_ORDER : services
    LAB ||--o{ SAMPLE : processes
    LAB ||--o{ BILL : invoices
    LAB ||--o{ PAYMENT : collects
    LAB ||--o{ DISCOUNT_RULE : configures
    LAB ||--o{ REFERRAL : partners
    LAB ||--o{ COMMISSION_ENTRY : credits
    LAB ||--o{ NOTIFICATION : dispatches

    USER ||--o{ USER_PERMISSION_OVERRIDE : has
    PERMISSION ||--o{ ROLE_PERMISSION : mapped_to
    PERMISSION ||--o{ USER_PERMISSION_OVERRIDE : overrides

    PATIENT ||--o{ APPOINTMENT : books
    PATIENT ||--o{ VISIT_ORDER : visits
    APPOINTMENT_SLOT ||--o{ APPOINTMENT : allocates
    APPOINTMENT ||--o| VISIT_ORDER : converts_to

    TEST_CATEGORY ||--o{ TEST : classifies
    TEST ||--o{ TEST_PARAMETER : defines
    TEST_PARAMETER ||--o{ TEST_PARAMETER_RANGE : specifies
    TEST_PACKAGE ||--o{ TEST_PACKAGE_ITEM : includes
    TEST ||--o{ TEST_PACKAGE_ITEM : member_of

    VISIT_ORDER ||--o{ ORDER_TEST : contains
    VISIT_ORDER ||--o{ ORDER_PACKAGE : includes
    TEST ||--o{ ORDER_TEST : snapshots
    TEST_PACKAGE ||--o{ ORDER_PACKAGE : snapshots
    VISIT_ORDER ||--o{ SAMPLE : generates
    VISIT_ORDER ||--o{ REPORT : results_in
    VISIT_ORDER ||--|| BILL : charges
    VISIT_ORDER ||--o{ COMMISSION_ENTRY : triggers

    BILL ||--o{ PAYMENT : accepts
    DISCOUNT_RULE ||--o{ BILL : discounts
    REFERRAL ||--o{ VISIT_ORDER : refers
    REFERRAL ||--o{ COMMISSION_ENTRY : earns
```

---

## 6. Developer Guidelines for Phase 2 Routes & Beyond

1. **Always Filter by `lab_id`**: For all queries against `LabScopedMixin` entities, ensure `lab_id == current_user.lab_id` is included in the query predicate (or via a repository/tenant middleware filter).
2. **Respect Soft Delete**: Filter by `Model.deleted_at.is_(None)` by default on all queries involving soft-deletable models.
3. **Never Mutate Snapshotted Financial Records**: `order_test.unit_price` and `order_package.package_price` freeze catalogue prices at order creation time. Do not recalculate existing orders when test catalogue prices update.
4. **Enforce 1:1 Invoicing**: Every `VisitOrder` has at most one active `Bill` (`uq_bill_order_id`).
5. **Report Immutability**: Verified reports (`status = 'verified'` or `'released'`) cannot be overwritten directly; corrections require issuing a new report incrementing `version` with a reference to the same `order_id`.

## Hosting migration — 2026-10-12

Current head: `20261012_column_comments`, following `20261012_expiring_state`.
`expiring_state` has `key VARCHAR(128)` as its primary key, `value JSONB NOT NULL`, and
`expires_at TIMESTAMPTZ NOT NULL` with an expiry index. Namespaced keys are hashed; refresh
and OTP values are digests. Application code enforces expiry on every read and supports daily cleanup.
It stores temporary authentication state, patient email links, shared rate counters and dashboard cache.

Metadata now matches the existing migration-defined defaults, JSONB audit data, foreign keys,
role/report uniqueness and performance/partial indexes. Application defaults remain unchanged.
The follow-up migration only updates column documentation; it rewrites no clinical/financial rows.
The four `_role_merge_*_backup` tables belong to the earlier staff-role migration and are retained
for its rollback; Alembic comparison excludes these explicit migration-owned snapshots.