# Database Schema Draft v0.3

## Design Principles (v0.3)

- **Two-stage identity:** Healthie form submitters are `intake_records` first. `beneficiaries` are created only after safety screening, clinical/physician review, CMS eligibility, consent, and successful CMS alignment (§client request #2).
- **Canonical data model:** Raw source data, normalized observations, approved evidence, and CMS submission records remain distinguishable but traceable to one another. Approved drafts migrate to canonical tables rather than coexisting as duplicates. JSONB FK lists are replaced with relational association tables (§client request #7, #13).
- **PHI minimization:** DOB is the only field encrypted at rest today; name/email/phone/address/MBI encryption is a pending follow-up rollout (see `EncryptedDateField` note below). SSNs are not stored unless a confirmed requirement exists. Audit logs store changed field names plus SHA-256 value hashes — never raw PHI values (§client request #13).
- **Offshore separation:** `pseudonymous_case_records` carry no identity fields (name, DOB, email, phone, MBI); the `beneficiary_id` linkage is gated by RBAC so offshore reviewer roles cannot traverse it (§client request #13).
- **Synthetic record flag:** All beneficiary, intake, and observation tables include an `is_synthetic` boolean to distinguish test data from production records.
- **Coding standards:** LOINC codes, UCUM units, and future FHIR-compatible mappings are tracked in reference tables (§client request #13).
- **Naming:** Table and column names below are logical. Django's physical table names follow the `<app>_<model>` convention (e.g., `intake_intakerecord`, `identity_beneficiary`) — see `access_core/*/models.py` for exact identifiers.

---

## 1. Identity & Access

### `users`
- `id` — uuid, PK — generated
- `email` — text — user registration / admin-created
- `full_name` — text — user registration
- `is_active` — boolean — admin-managed
- `created_at` — timestamp — system-generated
- `last_login_at` — timestamp — system-generated

> **Change from v0.1:** Removed `role` column. Roles are assigned via M2M `user_roles` table.

### `roles`
- `id` — uuid, PK — generated
- `role_name` — text (enum: owner, contractor, data_assistant, clinical_reviewer, physician_reviewer, offshore_reviewer, patient, admin, technical_tester) — admin-defined
- `description` — text (nullable) — admin-defined
- `is_active` — boolean — system-managed
- `created_at` — timestamp — system-generated

### `user_roles`
- `id` — uuid, PK — generated
- `user_id` — uuid, FK → users — system-assigned
- `role_id` — uuid, FK → roles — system-assigned
- `assigned_by` — uuid, FK → users — admin
- `assigned_at` — timestamp — system-generated
- `revoked_at` — timestamp (nullable) — system-generated

> **Change from v0.1:** Many-to-many relationship replaces single `role` column on `users`.

### `roles_permissions`
- `id` — uuid, PK — generated
- `role_id` — uuid, FK → roles — admin-defined
- `resource` — text (table/module name) — admin-defined
- `permission` — text (enum: view, create, edit, approve, export, delete) — admin-defined
- `created_at` — timestamp — system-generated

### `access_grants`
- `id` — uuid, PK — generated
- `user_id` — uuid, FK → users — system-assigned
- `beneficiary_id` — uuid, FK → beneficiaries (nullable) — system-assigned
- `grant_scope` — text (enum: global, beneficiary, care_period, task) — system-assigned
- `granted_at` — timestamp — system-generated
- `revoked_at` — timestamp (nullable) — system-generated

### `intake_records`
- `id` — uuid, PK — generated
- `external_form_id` — text, unique — Healthie `FormAnswerGroup.id`
- `first_name` — text — Healthie `Name.First`
- `middle_name` — text (nullable) — Healthie `Name.Middle`
- `last_name` — text — Healthie `Name.Last`
- `dob` — date (nullable) — Healthie `DateOfBirth` — **encrypted at rest** (`EncryptedDateField`, `enc:v1` envelope; KMS in staging/prod, local key in dev/test). Name/email/phone rollout pending follow-up.
- `sex` — text (enum: male, female, other, unknown) — Healthie `SexOnMedicalOrLabRecords`
- `is_synthetic` — boolean — system-set (true for synthetic/test data)
- `status` — text (enum: prospective, intake_in_progress, pending_clinical_review, pending_cms_eligibility, pending_consent, pending_alignment, active_access_beneficiary, rejected, withdrawn, inactive) — system-driven
- `submitted_at` — timestamp — Healthie `FormAnswerGroup.created_at`
- `created_at` — timestamp — system-generated
- `updated_at` — timestamp — system-generated

> **New in v0.2:** Replaces the initial `beneficiaries` table as the entry point for Healthie submitters. This is a prospective patient/applicant record, not yet an ACCESS Beneficiary.
> **Updated v0.3:** Status enum updated per client-approved lifecycle. External form source is Healthie.

### `intake_identifiers`
- `id` — uuid, PK — generated
- `intake_record_id` — uuid, FK → intake_records — from intake
- `identifier_type` — text (enum: email, phone, address) — Healthie form answers
- `identifier_value` — text — Healthie `Email`, `Phone`, `Address_*`
- `identifier_metadata` — jsonb (nullable, for address city/state/zip) — Healthie address fields
- `is_primary` — boolean — system-assigned
- `created_at` — timestamp — system-generated

> **New in v0.2:** Identity data for intake records. No MBI or SSN at this stage.

### `external_system_identifiers`
- `id` — uuid, PK — generated
- `beneficiary_id` — uuid, FK → beneficiaries — system-linked
- `system_name` — text (enum: healthie, cms, labcorp, quest) — system-captured
- `external_id` — text — external system record ID
- `entity_type` — text (enum: patient, account, form_submission) — system-captured
- `created_at` — timestamp — system-generated

> **New in v0.2:** Moves Healthie and other external system IDs out of individual tables into a unified external identifier table.

### `beneficiaries`
- `id` — uuid, PK — generated
- `intake_record_id` — uuid, FK → intake_records — linked at promotion
- `first_name` — text — from `intake_records.first_name`
- `middle_name` — text (nullable) — from `intake_records.middle_name`
- `last_name` — text — from `intake_records.last_name`
- `dob` — date — from `intake_records.dob` — **encrypted at rest** (same `EncryptedDateField` as intake)
- `sex` — text (enum: male, female, other, unknown) — from `intake_records.sex`
- `is_synthetic` — boolean — inherited from `intake_records`
- `created_at` — timestamp — system-generated (at promotion time)
- `updated_at` — timestamp — system-generated

> **Change from v0.1:** Beneficiaries are now created only after the full eligibility/alignment workflow completes. Identity data is copied from `intake_records` at promotion.

### `beneficiary_identifiers`
- `id` — uuid, PK — generated
- `beneficiary_id` — uuid, FK → beneficiaries — CMS API / system-captured
- `identifier_type` — text (enum: email, phone, address, mbi, cms_id) — CMS API / system-captured
- `identifier_value` — text — CMS API / system-captured
- `identifier_metadata` — jsonb (nullable, for address city/state/zip) — CMS data
- `is_primary` — boolean — system-assigned
- `created_at` — timestamp — system-generated

> **Change from v0.1:** Removed `ssn` from `identifier_type` enum. SSNs are not stored unless a confirmed requirement exists. MBI and CMS IDs are added.

### `patient_consent`
- `id` — uuid, PK — generated
- `intake_record_id` — uuid, FK → intake_records — linked at intake
- `beneficiary_id` — uuid, FK → beneficiaries (nullable) — linked after promotion
- `consent_type` — text (enum: data_sharing, participation, withdrawal, notice_acknowledgment, cms_data_sharing) — system-defined
- `status` — text (enum: pending, granted, declined, withdrawn) — system-driven
- `granted_at` — timestamp (nullable) — system-captured
- `granted_by` — uuid, FK → users (nullable) — system-captured
- `withdrawn_at` — timestamp (nullable) — system-captured
- `withdrawal_reason` — text (nullable) — system-captured
- `notice_provided` — boolean — system-tracked
- `notice_provided_at` — timestamp (nullable) — system-captured
- `authorization_document_id` — uuid, FK → clinical_documents (nullable) — linked at normalization
- `data_sharing_scope` — text (nullable, enum: cms_only, metaflow_only, cms_and_metaflow, all_providers) — system-defined
- `created_at` — timestamp — system-generated

> **New in v0.2:** Tracks patient consent, authorization, withdrawal, notice, and data-sharing status per ACCESS Participation Agreement requirements.

---

## 2. Intake & Screening

> **Convention:** Fields annotated as `text[]` represent multi-value (checkbox/multi-select) data stored as
> newline-separated strings in a single `TextField`. The `text[]` notation indicates semantic type; physical
> storage is PostgreSQL `text`.

### `raw_ingestion_records`
- `id` — uuid, PK — generated
- `source_system` — text (enum: healthie, lab_feed, patient_upload, prom_form, cms_api, polling_reconciliation) — system-captured
- `source_record_id` — text — external system ID (composite unique with `source_system` for deduplication)
- `raw_payload` — jsonb — webhook body / API response payload
- `intake_record_id` — uuid, FK → intake_records (nullable until linked) — system-linked post-processing
- `beneficiary_id` — uuid, FK → beneficiaries (nullable) — system-linked after promotion
- `webhook_signature_valid` — boolean (nullable) — system-captured (for webhook events)
- `processing_status` — text (enum: received, queued, processing, normalized, failed, dead_letter) — system-driven
- `form_version` — text (nullable) — form metadata
- `ingested_at` — timestamp — system-generated

> **Change from v0.2:** Added `webhook_signature_valid`, `processing_status` for webhook ingestion tracking. Added `intake_record_id` for pre-beneficiary linkage.

### `intake_assessments`
- `id` — uuid, PK — generated
- `intake_record_id` — uuid, FK → intake_records — from intake
- `raw_ingestion_id` — uuid, FK → raw_ingestion_records — linked at normalization
- `primary_concerns` — text[] (nullable) — Healthie form answer (Whole System section)
- `other_concerns` — text (nullable) — Healthie form answer (Whole System section)
- `symptom_duration` — text (nullable) — Healthie form answer (Whole System section)
- `primary_symptoms` — text (nullable) — Healthie form answer (Whole System section)
- `medical_conditions` — text[] (nullable) — Healthie form answer (Medical History section)
- `infection_immune_conditions` — text[] (nullable) — Healthie form answer (Infection/Immune section)
- `autoimmune_conditions` — text[] (nullable) — Healthie form answer (Infection/Immune section)
- `conditions_active` — boolean (nullable) — Healthie form answer (Infection/Immune section)
- `medications` — text[] (nullable) — Healthie form answer (Medical History section)
- `medication_details` — text (nullable) — Healthie form answer (Medical History section)
- `medications_list` — text (nullable) — Healthie form answer (Medical History section)
- `allergies` — text (nullable) — Healthie form answer (Medical History section)
- `created_at` — timestamp — system-generated

> **Change from v0.2:** FK changed from `beneficiary_id` to `intake_record_id`.

### `intake_lifestyle`
- `id` — uuid, PK — generated
- `intake_record_id` — uuid, FK → intake_records — from intake
- `raw_ingestion_id` — uuid, FK → raw_ingestion_records — linked at normalization
- `weight_lbs` — number — Healthie form answer (Body Metrics section)
- `waist_circumference_inches` — number — Healthie form answer (Body Metrics section)
- `height_inches` — number — Healthie form answer (Body Metrics section)
- `bmi` — number, computed — derived from weight/height
- `weight_change_6mo` — text (nullable) — Healthie form answer (Body Metrics section)
- `weight_control_patterns` — text[] (nullable) — Healthie form answer (Body Metrics section)
- `activity_level` — text (nullable) — Healthie form answer (Body Metrics section)
- `activity_types` — text[] (nullable) — Healthie form answer (Body Metrics section)
- `post_activity_response` — text[] (nullable) — Healthie form answer (Body Metrics section)
- `digestive_patterns` — text[] (nullable) — Healthie form answer (Digestion/Intake section)
- `diet_patterns` — text[] (nullable) — Healthie form answer (Digestion/Intake section)
- `digestive_conditions` — text[] (nullable) — Healthie form answer (Digestion/Intake section)
- `bowel_patterns` — text[] (nullable) — Healthie form answer (Bowel/Urinary section)
- `urination_patterns` — text[] (nullable) — Healthie form answer (Bowel/Urinary section)
- `hemorrhoids` — text (nullable) — Healthie form answer (Bowel/Urinary section)
- `sleep_quality` — text[] (nullable) — Healthie form answer (Sleep/Stress section)
- `stress_emotional_state` — text[] (nullable) — Healthie form answer (Sleep/Stress section)
- `sweating_patterns` — text[] (nullable) — Healthie form answer (Skin/Sweating section)
- `skin_patterns` — text[] (nullable) — Healthie form answer (Skin/Sweating section)
- `skin_longterm_meds` — text (nullable) — Healthie form answer (Skin/Sweating section)
- `mouth_throat_ear_eye_symptoms` — jsonb (nullable) — Healthie form answer (Mouth/Throat/Nose/Eyes/Ears section)
- `created_at` — timestamp — system-generated

> **Change from v0.2:** FK changed from `beneficiary_id` to `intake_record_id`.

### `intake_female_health`
- `id` — uuid, PK — generated
- `intake_record_id` — uuid, FK → intake_records — from intake (sex = female)
- `raw_ingestion_id` — uuid, FK → raw_ingestion_records — linked at normalization
- `menstrual_status` — text (nullable) — Healthie form answer (Female Health section)
- `menstrual_flow` — text (nullable) — Healthie form answer (Female Health section)
- `menstrual_blood_color` — text (nullable) — Healthie form answer (Female Health section)
- `menstrual_clots_cramps` — text (nullable) — Healthie form answer (Female Health section)
- `discharge_discomfort` — text (nullable) — Healthie form answer (Female Health section)
- `diagnosed_conditions` — text[] (nullable) — Healthie form answer (Female Health section)
- `free_text` — text (nullable) — Healthie form answer (Female Health section)
- `created_at` — timestamp — system-generated

### `intake_male_health`
- `id` — uuid, PK — generated
- `intake_record_id` — uuid, FK → intake_records — from intake (sex = male)
- `raw_ingestion_id` — uuid, FK → raw_ingestion_records — linked at normalization
- `prostate_conditions` — text[] (nullable) — Healthie form answer (Male Health section)
- `sexual_function` — text (nullable) — Healthie form answer (Male Health section)
- `ejaculation_concerns` — text (nullable) — Healthie form answer (Male Health section)
- `perineal_testicular_symptoms` — text[] (nullable) — Healthie form answer (Male Health section)
- `pelvic_surgery` — text[] (nullable) — Healthie form answer (Male Health section)
- `fertility_status` — text (nullable) — Healthie form answer (Male Health section)
- `free_text` — text (nullable) — Healthie form answer (Male Health section)
- `created_at` — timestamp — system-generated

### `intake_msk`
- `id` — uuid, PK — generated
- `intake_record_id` — uuid, FK → intake_records — from intake
- `raw_ingestion_id` — uuid, FK → raw_ingestion_records — linked at normalization
- `pain_locations` — text[] (nullable) — Healthie form answer (Pain/MSK section)
- `msk_diagnoses` — text[] (nullable) — Healthie form answer (Pain/MSK section)
- `injury_history` — text[] (nullable) — Healthie form answer (Pain/MSK section)
- `functional_impact` — text[] (nullable) — Healthie form answer (Pain/MSK section)
- `pain_quality` — text[] (nullable) — Healthie form answer (Pain/MSK section)
- `pain_duration` — text (nullable) — Healthie form answer (Pain/MSK section)
- `pain_severity` — number (nullable) — Healthie form answer (Pain/MSK section)
- `created_at` — timestamp — system-generated

### `safety_screening`
- `id` — uuid, PK — generated
- `intake_record_id` — uuid, FK → intake_records — from intake
- `raw_ingestion_id` — uuid, FK → raw_ingestion_records — linked at normalization
- `safety_stop_applies` — boolean — Healthie form answer (Safety Screening section)
- `exclusion_flags` — text[] (nullable, derived from medical conditions, medications, safety stop) — system-derived
- `screening_status` — text (enum: passed, failed, flagged, needs_review) — system-derived / physician-assigned
- `free_text` — text (nullable) — Healthie form answer (Final Review section)
- `created_at` — timestamp — system-generated

### `participation_readiness`
- `id` — uuid, PK — generated
- `intake_record_id` — uuid, FK → intake_records — from intake
- `acknowledged` — boolean — Healthie form answer (Participation Readiness section)
- `status` — text (enum: acknowledged, pending, declined) — system-derived
- `created_at` — timestamp — system-generated

### `intake_review_status`
- `id` — uuid, PK — generated
- `intake_record_id` — uuid, FK → intake_records — system-assigned
- `status` — text (enum: new, under_review, approved, rejected, needs_more_info) — physician / reviewer assigned
- `reviewed_by` — uuid, FK → users (nullable) — system-captured
- `reviewed_at` — timestamp (nullable) — system-captured
- `notes` — text (nullable) — reviewer input
- `created_at` — timestamp — system-generated

### `form_submission_events`
- `id` — uuid, PK — generated
- `intake_record_id` — uuid, FK → intake_records — linked intake record
- `raw_ingestion_id` — uuid, FK → raw_ingestion_records — source raw payload
- `submitted_at` — timestamp — Healthie `FormAnswerGroup.created_at`
- `form_version` — text (nullable) — Healthie form version metadata
- `source_system` — text (enum: healthie, manual) — system-captured
- `status` — text (enum: submitted, processing, normalized, failed) — system-driven
- `created_at` — timestamp — system-generated

---

## 3. ACCESS Workflow

### `clinical_tracks`
- `id` — uuid, PK — generated
- `track_name` — text (enum: CKM, eCKM, MSK) — system-defined
- `description` — text (nullable) — system-defined
- `is_active` — boolean — system-managed

### `eligibility_checks`
- `id` — uuid, PK — generated
- `intake_record_id` — uuid, FK → intake_records — from intake workflow (pre-beneficiary)
- `beneficiary_id` — uuid, FK → beneficiaries (nullable) — linked after promotion
- `requested_track` — uuid, FK → clinical_tracks — beneficiary selection / physician routing
- `check_status` — text (enum: pending, eligible, ineligible, control_group, error) — CMS API response / system-derived
- `cms_response` — jsonb (nullable, PHI-minimized) — CMS Eligibility API response
- `ineligibility_reason` — text (nullable) — CMS API / system-assigned
- `checked_at` — timestamp — system-generated
- `created_at` — timestamp — system-generated

> **Change from v0.2:** Linked to `intake_record_id` first. `beneficiary_id` populated after promotion.

### `alignments`
- `id` — uuid, PK — generated
- `intake_record_id` — uuid, FK → intake_records — from eligibility workflow
- `beneficiary_id` — uuid, FK → beneficiaries (nullable) — linked after promotion
- `clinical_track_id` — uuid, FK → clinical_tracks — from eligibility routing
- `aligned_at` — timestamp — CMS Alignment API response date
- `cms_response` — jsonb (nullable, PHI-minimized) — CMS Alignment API response
- `created_at` — timestamp — system-generated

### `unalignment`
- `id` — uuid, PK — generated
- `beneficiary_id` — uuid, FK → beneficiaries — from alignment workflow
- `unaligned_at` — timestamp — CMS Unalignment API / system record
- `reason` — text (nullable) — CMS API / system-assigned / patient request
- `cms_response` — jsonb (nullable) — CMS Unalignment API response
- `created_at` — timestamp — system-generated

### `enrollment_decisions`
- `id` — uuid, PK — generated
- `intake_record_id` — uuid, FK → intake_records — from intake review
- `beneficiary_id` — uuid, FK → beneficiaries (nullable) — linked after promotion
- `decision` — text (enum: approved, rejected, deferred, withdrawn) — physician assigned
- `decision_by` — uuid, FK → users — physician
- `decision_at` — timestamp — system-captured
- `reason` — text (nullable) — physician input
- `created_at` — timestamp — system-generated

> **Change from v0.2:** Linked to `intake_record_id` first. Removed `healthie_patient_id` — now tracked in `external_system_identifiers`.

### `care_periods`
- `id` — uuid, PK — generated
- `beneficiary_id` — uuid, FK → beneficiaries — from alignment
- `clinical_track_id` — uuid, FK → clinical_tracks — from alignment
- `start_date` — date — alignment date / system-set
- `end_date` — date (nullable) — CMS-defined end or manual
- `baseline_status` — text (enum: pending, complete, overdue, waived) — system-derived / physician-set
- `operational_status` — text (enum: active, ended, withdrawn, unaligned) — system-derived / physician-set
- `physician_review_status` — text (enum: not_required, pending, approved, needs_revision) — system-derived
- `reporting_schedule` — jsonb (nullable, reporting window dates) — system-derived from care period start
- `reconciliation_status` — text (enum: not_applicable, pending, in_progress, complete, recoupment) — system-derived / CMS data
- `protocol_version_id` — uuid, FK → protocol_versions (nullable) — system-assigned
- `created_at` — timestamp — system-generated

---

## 4. OAP Reporting

### `oap_measure_definitions`
- `id` — uuid, PK — generated
- `measure_name` — text — ACCESS technical manual / CMS spec
- `clinical_track` — text (enum: CKM, eCKM, MSK) — CMS spec
- `unit` — text (nullable) — CMS spec (e.g., mg/dL, mmHg, score)
- `reporting_types` — text[] (enum values: baseline, quarterly, end_of_period, early_success) — CMS spec
- `loinc_code` — uuid, FK → loinc_codes (nullable) — standards mapping
- `ucum_unit` — text (nullable) — UCUM standard unit code
- `fhir_code_mapping` — text (nullable) — future FHIR-compatible coding
- `description` — text (nullable) — CMS spec
- `is_active` — boolean — system-managed

> **Change from v0.2:** Added LOINC, UCUM, and FHIR mapping fields.

### `oap_measure_observations`
- `id` — uuid, PK — generated
- `measure_definition_id` — uuid, FK → oap_measure_definitions — system-linked
- `beneficiary_id` — uuid, FK → beneficiaries — linked via care period
- `care_period_id` — uuid, FK → care_periods — linked at reporting time
- `canonical_observation_id` — uuid, FK → clinical_observations — the single source of truth for this value
- `reporting_type` — text (enum: baseline, quarterly, end_of_period, early_success) — system-derived from collection date + care period
- `collection_date` — date — from source observation
- `submission_date` — date (nullable) — CMS submission timestamp
- `validity_window_status` — text (enum: in_window, late, missing, out_of_window, not_applicable) — system-calculated
- `data_source` — text (enum: lab, prom, patient_entered, device, physician_entered, curation) — system-captured at normalization
- `collection_method` — text (nullable) — system-captured
- `source_document_id` — uuid, FK → clinical_documents (nullable) — linked at normalization
- `entry_user_id` — uuid, FK → users (nullable) — system-captured
- `review_user_id` — uuid, FK → users (nullable) — system-captured
- `physician_review_status` — text (enum: not_required, pending, approved, rejected) — system-derived
- `cms_submission_payload` — jsonb (nullable, PHI-minimized) — CMS Data Reporting API request
- `cms_submission_response` — jsonb (nullable, PHI-minimized) — CMS Data Reporting API response
- `status` — text (enum: draft, approved, rejected, submitted_to_cms) — system-derived
- `created_at` — timestamp — system-generated
- `updated_at` — timestamp — system-generated

> **Change from v0.2:** Removed `value`, `unit` — these are inherited from the canonical `clinical_observations` record via `canonical_observation_id`. This table is a reporting projection, not a data duplicate (§canonical data model).

### `oap_reporting_windows`
- `id` — uuid, PK — generated
- `care_period_id` — uuid, FK → care_periods — system-linked
- `reporting_type` — text (enum: baseline, quarterly, end_of_period, early_success) — system-derived
- `window_start` — date — calculated from care period start + ACCESS rules
- `window_end` — date — calculated from care period start + ACCESS rules
- `is_active` — boolean — system-derived
- `created_at` — timestamp — system-generated

---

## 5. CMS Integration

### `cms_api_requests`
- `id` — uuid, PK — generated
- `api_category` — text (enum: eligibility, alignment, unalignment, data_reporting) — system-captured
- `endpoint` — text — system-captured
- `method` — text — system-captured
- `request_payload` — jsonb (PHI-minimized) — system-captured
- `request_headers` — jsonb (sensitive, scrubbed of auth tokens) — system-captured
- `status_code` — number (nullable) — from response
- `beneficiary_id` — uuid, FK → beneficiaries (nullable) — system-linked
- `requested_at` — timestamp — system-generated (creation time; no separate `created_at` column)

### `cms_api_responses`
- `id` — uuid, PK — generated
- `request_id` — uuid, FK → cms_api_requests — system-linked
- `response_payload` — jsonb (PHI-minimized) — CMS response body
- `response_headers` — jsonb (sensitive, scrubbed) — system-captured
- `status_code` — number — from response
- `received_at` — timestamp — system-generated

### `cms_claims_files`
- `id` — uuid, PK — generated
- `beneficiary_id` — uuid, FK → beneficiaries — system-linked
- `file_name` — text — CMS file reference
- `file_reference` — text (nullable, object storage path) — system-captured
- `file_type` — text (enum: claim, payment, recoupment) — system-captured
- `received_at` — date — system-captured
- `processed_at` — timestamp (nullable) — system-generated
- `created_at` — timestamp — system-generated

### `cms_payment_files`
- `id` — uuid, PK — generated
- `beneficiary_id` — uuid, FK → beneficiaries — system-linked
- `care_period_id` — uuid, FK → care_periods (nullable) — system-linked
- `file_name` — text — CMS file reference
- `file_reference` — text (nullable) — system-captured
- `oap_amount` — number — CMS payment data
- `payment_date` — date — CMS data
- `created_at` — timestamp — system-generated

### `recoupment_events`
- `id` — uuid, PK — generated
- `beneficiary_id` — uuid, FK → beneficiaries — CMS data
- `care_period_id` — uuid, FK → care_periods — system-linked
- `amount` — number — CMS data
- `reason` — text (nullable) — CMS data
- `event_date` — date — CMS data
- `status` — text (enum: notified, disputed, accepted, resolved) — system-assigned
- `created_at` — timestamp — system-generated

---

## 6. Clinical Data

### `clinical_observations`
- `id` — uuid, PK — generated
- `beneficiary_id` — uuid, FK → beneficiaries — system-linked
- `care_period_id` — uuid, FK → care_periods (nullable) — system-linked
- `observation_type` — text (enum: lab, vital_sign, symptom, imaging_finding, prom_score, other) — system-captured at normalization
- `measure_name` — text — from curation / lab / observation
- `loinc_code` — uuid, FK → loinc_codes (nullable) — standards mapping
- `value_text` — text (nullable, for non-numeric values) — from curation
- `value_numeric` — number (nullable) — from curation
- `unit` — text (nullable) — from curation / source
- `ucum_unit` — text (nullable) — UCUM standard unit code
- `collection_date` — date — from source document / lab report
- `data_source` — text (enum: labcorp, quest, patient_upload, device, physician_entered, curation) — system-captured
- `source_record_id` — uuid, FK → raw_ingestion_records (nullable) — linked at normalization
- `source_document_id` — uuid, FK → clinical_documents (nullable) — linked at normalization
- `collection_method` — text (nullable) — system-captured
- `entry_user_id` — uuid, FK → users (nullable) — system-captured
- `review_user_id` — uuid, FK → users (nullable) — system-captured
- `is_synthetic` — boolean — system-set
- `status` — text (enum: imported, cleaning_pending, curated_draft, needs_clarification, qc_passed, clinician_review_required, physician_review_required, approved, locked_for_reporting, submitted_to_cms, rejected, superseded) — system-driven
- `created_at` — timestamp — system-generated
- `updated_at` — timestamp — system-generated

> **Change from v0.2:** Added LOINC and UCUM fields. Added `is_synthetic` flag. Renamed `value` → `value_text` for clarity.

### `clinical_observation_sources`
- `id` — uuid, PK — generated
- `observation_id` — uuid, FK → clinical_observations — system-linked
- `source_type` — text (enum: lab_result, prom_score, imaging_finding, execution_log, status_log, daily_checkin) — system-captured
- `source_record_id` — uuid — polymorphic FK to the source table
- `created_at` — timestamp — system-generated

> **New in v0.2:** Replaces JSONB FK arrays. Relational association table linking observations to their source records (§canonical data model).

### `lab_results`
- `id` — uuid, PK — generated
- `beneficiary_id` — uuid, FK → beneficiaries — from lab report / feed
- `care_period_id` — uuid, FK → care_periods (nullable) — system-linked
- `lab_order_date` — date (nullable) — from lab report
- `specimen_collection_date` — date (nullable) — from lab report
- `result_date` — date (nullable) — from lab report
- `ordering_physician` — text (nullable) — from lab report
- `lab_facility` — text (nullable) — from lab report (e.g., Labcorp, Quest)
- `source_document_id` — uuid, FK → clinical_documents (nullable) — linked at normalization
- `source_type` — text (enum: pdf_upload, structured_feed, patient_upload) — system-captured
- `is_synthetic` — boolean — system-set
- `created_at` — timestamp — system-generated

> **Change from v0.2:** Added `is_synthetic` flag.

### `lab_result_items`
- `id` — uuid, PK — generated
- `lab_result_id` — uuid, FK → lab_results — system-linked
- `loinc_code` — uuid, FK → loinc_codes (nullable) — LOINC standard code
- `test_name` — text — from lab report
- `value` — number (nullable) — from lab report
- `unit` — text (nullable) — from lab report
- `ucum_unit` — text (nullable) — UCUM standard unit code
- `reference_range_low` — number (nullable) — from lab report
- `reference_range_high` — number (nullable) — from lab report
- `flags` — text[] (nullable, e.g., "H", "L", "critical") — from lab report
- `status` — text (enum: imported, curated_draft, approved, rejected) — system-driven
- `entry_user_id` — uuid, FK → users (nullable) — system-captured
- `review_user_id` — uuid, FK → users (nullable) — system-captured

> **Change from v0.2:** `loinc_code` is now a FK to `loinc_codes` reference table. Added `ucum_unit`.

### `prom_instruments`
- `id` — uuid, PK — generated
- `instrument_name` — text (e.g., "PROMIS Pain Interference", "WOMAC") — ACCESS technical manual / CMS spec
- `version` — text — CMS spec / instrument documentation
- `scoring_logic` — jsonb — CMS spec / instrument documentation
- `response_options` — jsonb (nullable) — CMS spec / instrument documentation
- `applicable_track` — text (enum: CKM, eCKM, MSK) — CMS spec
- `is_active` — boolean — system-managed

### `prom_responses`
- `id` — uuid, PK — generated
- `beneficiary_id` — uuid, FK → beneficiaries — from PROM submission
- `care_period_id` — uuid, FK → care_periods (nullable) — system-linked
- `instrument_id` — uuid, FK → prom_instruments — from PROM form
- `body_site` — text (nullable) — from PROM form
- `raw_item_responses` — jsonb — from PROM form submission
- `completion_date` — timestamp — from PROM form submission
- `device_metadata` — jsonb (nullable) — browser/device info captured at submission
- `is_synthetic` — boolean — system-set
- `created_at` — timestamp — system-generated

### `prom_scores`
- `id` — uuid, PK — generated
- `prom_response_id` — uuid, FK → prom_responses — system-linked
- `instrument_id` — uuid, FK → prom_instruments — from instrument definition
- `score` — number — calculated per instrument scoring logic
- `score_interpretation` — text (nullable) — system-derived / clinician-assigned
- `reporting_status` — text (enum: baseline, quarterly, end_of_period, not_applicable) — system-derived
- `status` — text (enum: computed, reviewed, approved, rejected) — system-driven
- `review_user_id` — uuid, FK → users (nullable) — system-captured

---

## 7. Structured Imaging (New in v0.2)

### `imaging_studies`
- `id` — uuid, PK — generated
- `beneficiary_id` — uuid, FK → beneficiaries — from uploaded report
- `care_period_id` — uuid, FK → care_periods (nullable) — system-linked
- `imaging_type` — text (enum: carotid_ultrasound, fatty_liver_ultrasound, kidney_ultrasound, thyroid_ultrasound, other) — from report / curation
- `facility` — text (nullable) — from report
- `study_date` — date (nullable) — from report
- `report_date` — date (nullable) — from report
- `radiologist` — text (nullable) — from report
- `source_document_id` — uuid, FK → clinical_documents — linked at normalization
- `physician_interpretation` — text (nullable) — physician input
- `is_synthetic` — boolean — system-set
- `status` — text (enum: imported, curated_draft, approved, rejected) — system-driven
- `created_at` — timestamp — system-generated

> **Change from v0.2:** Renamed from `imaging_reports`. Removed free-text `findings` and `before_after_comparison` — replaced by structured findings tables and comparison table below.

### `carotid_ultrasound_findings`
- `id` — uuid, PK — generated
- `imaging_study_id` — uuid, FK → imaging_studies — system-linked
- `laterality` — text (enum: left, right, bilateral) — from report / curation
- `anatomic_segment` — text (nullable, e.g., "proximal ICA", "distal CCA", "bifurcation") — from report
- `plaque_location` — text (nullable) — from report
- `plaque_length_mm` — number (nullable) — from report / curation
- `plaque_thickness_mm` — number (nullable) — from report / curation
- `residual_lumen_diameter_mm` — number (nullable) — from report
- `psv_cm_s` — number (nullable, Peak Systolic Velocity) — from report
- `edv_cm_s` — number (nullable, End Diastolic Velocity) — from report
- `stenosis_classification` — text (nullable, e.g., "<50%", "50-69%", "70-99%", "occlusion") — from report / physician
- `plaque_morphology` — text (nullable, e.g., "hypoechoic", "hypoechoic with ulceration", "calcified", "mixed") — from report
- `flow_direction` — text (nullable, e.g., "antegrade", "reversed", "to-and-fro") — from report
- `measurement_method` — text (nullable) — from report
- `status` — text (enum: drafted, reviewed, approved) — system-driven
- `created_at` — timestamp — system-generated

### `liver_ultrasound_findings`
- `id` — uuid, PK — generated
- `imaging_study_id` — uuid, FK → imaging_studies — system-linked
- `steatosis_grade` — text (nullable, enum: none, mild, moderate, severe) — from report / curation
- `liver_echogenicity` — text (nullable, e.g., "normal", "increased", "markedly increased") — from report
- `liver_size_cm` — number (nullable) — from report
- `other_findings` — text (nullable) — from report / curation
- `status` — text (enum: drafted, reviewed, approved) — system-driven
- `created_at` — timestamp — system-generated

### `imaging_study_comparisons`
- `id` — uuid, PK — generated
- `current_study_id` — uuid, FK → imaging_studies — system-linked
- `baseline_study_id` — uuid, FK → imaging_studies (nullable) — system-linked (first study in care period)
- `prior_study_id` — uuid, FK → imaging_studies (nullable) — system-linked (most recent prior study)
- `finding_type` — text (enum: carotid_plaque, liver_steatosis, other) — system-defined
- `calculated_change` — text (nullable, e.g., "plaque_reduced_2mm", "steatosis_improved_mild_to_none", "stable", "progressed") — physician / system-derived
- `trajectory` — text (nullable, enum: improving, stable, worsening, reversal, normalization) — physician-assigned
- `physician_interpretation` — text (nullable) — physician input
- `created_at` — timestamp — system-generated

### `structural_evidence`
- `id` — uuid, PK — generated
- `beneficiary_id` — uuid, FK → beneficiaries — from imaging / lab / observation
- `care_period_id` — uuid, FK → care_periods — system-linked
- `evidence_type` — text (enum: carotid_plaque, fatty_liver, kidney_structure, thyroid, other_imaging, lab_trajectory, composite) — system-assigned at curation
- `evidence_summary` — text — from curation / physician interpretation
- `trajectory` — text (nullable, enum: improving, stable, worsening, reversal, normalization) — physician-assigned
- `is_synthetic` — boolean — system-set
- `created_at` — timestamp — system-generated
- `updated_at` — timestamp — system-generated

### `structural_evidence_sources`
- `id` — uuid, PK — generated
- `evidence_id` — uuid, FK → structural_evidence — system-linked
- `source_type` — text (enum: imaging_study, lab_result, clinical_observation) — system-captured
- `source_record_id` — uuid — polymorphic FK to the source table
- `created_at` — timestamp — system-generated

> **New in v0.2:** Replaces JSONB `source_records` on `structural_evidence`. Relational association table.

---

## 8. MetaFlow Evidence

### `protocol_versions`
- `id` — uuid, PK — generated
- `version_number` — text — system-defined
- `clinical_track` — uuid, FK → clinical_tracks — system-defined
- `description` — text (nullable) — system-defined
- `protocol_content` — jsonb (nullable, not stored in repo per §21) — physician-defined (plaintext JSON; no PHI by design)
- `is_active` — boolean — system-managed
- `effective_from` — date — system-defined
- `effective_to` — date (nullable) — system-defined
- `created_at` — timestamp — system-generated

### `protocol_assignment_versions`
- `id` — uuid, PK — generated
- `beneficiary_id` — uuid, FK → beneficiaries — system-assigned
- `care_period_id` — uuid, FK → care_periods — system-assigned
- `protocol_version_id` — uuid, FK → protocol_versions — physician / system-assigned
- `assigned_at` — timestamp — system-generated
- `assigned_by` — uuid, FK → users — physician
- `notes` — text (nullable) — physician input
- `created_at` — timestamp — system-generated

### `patient_execution_logs`
- `id` — uuid, PK — generated
- `beneficiary_id` — uuid, FK → beneficiaries — from daily check-in
- `care_period_id` — uuid, FK → care_periods — system-linked
- `protocol_assignment_id` — uuid, FK → protocol_assignment_versions (nullable) — system-linked
- `execution_date` — date — from daily check-in
- `completed` — boolean — from daily check-in
- `duration_minutes` — number (nullable) — from daily check-in
- `body_areas` — text[] (nullable) — from daily check-in
- `steps_completed` — number (nullable) — from daily check-in
- `steps_skipped` — text (nullable) — from daily check-in
- `missed_reason` — text (nullable) — from daily check-in
- `unusual_reaction` — text (nullable) — from daily check-in
- `source_system` — text (enum: healthie, patient_app) — system-captured
- `created_at` — timestamp — system-generated

### `daily_status_logs`
- `id` — uuid, PK — generated
- `beneficiary_id` — uuid, FK → beneficiaries — from daily check-in
- `care_period_id` — uuid, FK → care_periods — system-linked
- `log_date` — date — from daily check-in
- `symptoms_new_worsening` — text[] (nullable) — from daily check-in
- `pain_change` — text (nullable) — from daily check-in
- `fatigue` — text (nullable) — from daily check-in
- `sleep_quality` — text (nullable) — from daily check-in
- `dizziness` — boolean (nullable) — from daily check-in
- `shortness_of_breath` — boolean (nullable) — from daily check-in
- `chest_pain` — boolean (nullable) — from daily check-in
- `fever` — boolean (nullable) — from daily check-in
- `swelling` — boolean (nullable) — from daily check-in
- `bleeding` — boolean (nullable) — from daily check-in
- `skin_reaction` — boolean (nullable) — from daily check-in
- `gi_reaction` — boolean (nullable) — from daily check-in
- `urination_change` — boolean (nullable) — from daily check-in
- `other_concern` — text (nullable) — from daily check-in
- `source_system` — text (enum: healthie, patient_app) — system-captured
- `created_at` — timestamp — system-generated

### `track_specific_daily_logs`
- `id` — uuid, PK — generated
- `beneficiary_id` — uuid, FK → beneficiaries — from daily check-in
- `care_period_id` — uuid, FK → care_periods — system-linked
- `log_date` — date — from daily check-in
- `clinical_track` — text (enum: CKM, eCKM, MSK) — system-linked
- `home_bp_systolic` — number (nullable, CKM only) — from daily check-in
- `home_bp_diastolic` — number (nullable, CKM only) — from daily check-in
- `home_weight_lbs` — number (nullable, CKM only) — from daily check-in
- `blood_sugar` — number (nullable, CKM only) — from daily check-in
- `urination_changes` — text (nullable, CKM only) — from daily check-in
- `swelling` — text (nullable, CKM only) — from daily check-in
- `energy_level` — text (nullable, CKM only) — from daily check-in
- `pain_score_0_10` — number (nullable, MSK only) — from daily check-in
- `function_today` — text (nullable, MSK only) — from daily check-in
- `activity_tolerance` — text[] (nullable, MSK only, enum: walking, stairs, lifting, sitting) — from daily check-in
- `sleep_disruption` — boolean (nullable, MSK only) — from daily check-in
- `flare_status` — boolean (nullable, MSK only) — from daily check-in
- `pain_medication_use` — text (nullable, MSK only) — from daily check-in
- `source_system` — text (enum: healthie, patient_app) — system-captured
- `created_at` — timestamp — system-generated

---

## 9. Patient-Specific Dynamic Monitoring Plan (New in v0.2)

### `monitoring_plan_templates`
- `id` — uuid, PK — generated
- `template_name` — text — system-defined (e.g., "eCKM Abnormal Baseline", "CKM Standard")
- `clinical_track` — text (enum: CKM, eCKM, MSK, all) — system-defined
- `trigger_description` — text (nullable) — system-defined (when this template applies)
- `is_active` — boolean — system-managed
- `created_at` — timestamp — system-generated

### `monitoring_plans`
- `id` — uuid, PK — generated
- `beneficiary_id` — uuid, FK → beneficiaries — system-linked
- `care_period_id` — uuid, FK → care_periods — system-linked
- `template_id` — uuid, FK → monitoring_plan_templates (nullable) — system-linked
- `created_by` — uuid, FK → users — physician
- `clinical_trigger` — text — physician / system-defined (reason for this plan)
- `baseline_abnormality` — text (nullable) — physician-defined (what baseline finding triggered monitoring)
- `status` — text (enum: active, paused, completed, discontinued) — system-driven
- `created_at` — timestamp — system-generated
- `updated_at` — timestamp — system-generated

### `monitoring_plan_items`
- `id` — uuid, PK — generated
- `monitoring_plan_id` — uuid, FK → monitoring_plans — system-linked
- `item_description` — text — physician / system-defined (e.g., "Monthly carotid ultrasound", "Quarterly eGFR and uACR")
- `monitoring_category` — text (enum: access_oap, metaflow_enhanced, structural_evidence, operational) — system-defined
- `measure_name` — text (nullable) — system-defined
- `loinc_code` — uuid, FK → loinc_codes (nullable) — standards mapping
- `frequency_interval` — text (nullable, e.g., "monthly", "quarterly", "as_indicated") — system-defined
- `planned_due_date` — date (nullable) — system-calculated or physician-set
- `ordering_clinician` — uuid, FK → users (nullable) — physician
- `cms_reportable` — boolean — system-defined
- `reportability_reason` — text (nullable) — system-defined (why this item is or is not CMS-reportable)
- `evidence_layer` — text (enum: access_oap, metaflow_enhanced, structural_evidence, operational) — system-defined
- `status` — text (enum: scheduled, completed, overdue, waived, discontinued) — system-driven
- `created_at` — timestamp — system-generated
- `updated_at` — timestamp — system-generated

### `monitoring_plan_results`
- `id` — uuid, PK — generated
- `monitoring_plan_item_id` — uuid, FK → monitoring_plan_items — system-linked
- `result_date` — date — system-captured
- `value_text` — text (nullable) — from source observation
- `value_numeric` — number (nullable) — from source observation
- `unit` — text (nullable) — from source observation
- `source_type` — text (enum: lab_result, imaging_study, clinical_observation, prom_score, daily_log) — system-captured
- `source_record_id` — uuid — polymorphic FK to the source table
- `source_document_id` — uuid, FK → clinical_documents (nullable) — linked at normalization
- `baseline_comparison` — text (nullable, e.g., "improved", "worsened", "stable") — system-derived or physician-assigned
- `prior_result_comparison` — text (nullable, e.g., "decreased_10mg_dL", "no_change") — system-derived or physician-assigned
- `review_status` — text (enum: pending, reviewed, physician_approved) — system-driven
- `review_user_id` — uuid, FK → users (nullable) — system-captured
- `completed_at` — timestamp (nullable) — system-generated
- `created_at` — timestamp — system-generated

> **New in v0.2:** Patient-specific dynamic monitoring plan module. Longitudinal, configurable. No fixed `month_1_result` / `month_2_result` columns. Each result is a row linked to a plan item, with baseline and prior-result comparison.

---

## 10. Patient Daily Operations

### `daily_checkin_templates`
- `id` — uuid, PK — generated
- `template_name` — text — system-defined
- `clinical_track` — text (enum: CKM, eCKM, MSK, all) — system-defined
- `version` — text — system-defined
- `is_active` — boolean — system-managed
- `created_at` — timestamp — system-generated

### `daily_checkin_items`
- `id` — uuid, PK — generated
- `template_id` — uuid, FK → daily_checkin_templates — system-linked
- `item_key` — text — system-defined (field identifier)
- `item_label` — text — system-defined (patient-facing question)
- `item_type` — text (enum: boolean, number, text, text[], select, select_multi) — system-defined
- `options` — jsonb (nullable, for select/select_multi) — system-defined
- `is_required` — boolean — system-defined
- `display_order` — number — system-defined
- `safety_rule` — jsonb (nullable, red/yellow/green thresholds) — system-defined

### `daily_checkin_submissions`
- `id` — uuid, PK — generated
- `beneficiary_id` — uuid, FK → beneficiaries — from daily check-in
- `care_period_id` — uuid, FK → care_periods — system-linked
- `template_id` — uuid, FK → daily_checkin_templates — system-linked
- `submission_date` — date — system-captured
- `source_system` — text (enum: healthie, patient_app) — system-captured
- `status` — text (enum: submitted, reviewed, flagged) — system-derived
- `created_at` — timestamp — system-generated

### `daily_checkin_responses`
- `id` — uuid, PK — generated
- `submission_id` — uuid, FK → daily_checkin_submissions — system-linked
- `item_id` — uuid, FK → daily_checkin_items — system-linked
- `value` — jsonb — from patient check-in
- `created_at` — timestamp — system-generated

### `raw_daily_checkin_submissions`
- `id` — uuid, PK — generated
- `beneficiary_id` — uuid, FK → beneficiaries (nullable) — system-linked (null until beneficiary is matched)
- `raw_payload` — jsonb — Healthie raw submission
- `source_system` — text (enum: healthie, patient_app) — system-captured
- `source_record_id` — text (nullable) — external system ID
- `ingested_at` — timestamp — system-generated
- `form_version` — text (nullable) — system-captured

> **Note:** `beneficiary_id` is nullable because check-ins may arrive before the patient is promoted to beneficiary status. These records are processed later once the beneficiary exists.

### `missed_checkin_events`
- `id` — uuid, PK — generated
- `beneficiary_id` — uuid, FK → beneficiaries — system-derived
- `care_period_id` — uuid, FK → care_periods — system-linked
- `missed_date` — date — system-calculated
- `consecutive_days_missed` — number — system-calculated
- `notified` — boolean — system-tracked
- `notified_at` — timestamp (nullable) — system-generated
- `created_at` — timestamp — system-generated

### `patient_adherence_summaries`
- `id` — uuid, PK — generated
- `beneficiary_id` — uuid, FK → beneficiaries — system-linked
- `care_period_id` — uuid, FK → care_periods — system-linked
- `period_start` — date — system-defined
- `period_end` — date — system-defined
- `expected_checkins` — number — system-calculated
- `completed_checkins` — number — system-calculated
- `missed_checkins` — number — system-calculated
- `adherence_rate` — number — system-calculated
- `created_at` — timestamp — system-generated

### `monthly_patient_summaries`
- `id` — uuid, PK — generated
- `beneficiary_id` — uuid, FK → beneficiaries — system-linked
- `care_period_id` — uuid, FK → care_periods — system-linked
- `month` — date (first of month) — system-defined
- `summary_content` — jsonb — system-assembled (adherence, safety flags, key observations, trajectory)
- `review_status` — text (enum: generated, reviewed_by_clinician, reviewed_by_physician) — system-driven
- `review_user_id` — uuid, FK → users (nullable) — system-captured
- `created_at` — timestamp — system-generated

### `patient_notifications` (Implemented: `monitoring.PatientNotification`)
- `id` — uuid, PK — generated
- `beneficiary_id` — uuid, FK → beneficiaries — system-linked
- `notification_type` — text (enum: missed_checkin_reminder, safety_alert, general, protocol_update) — system-defined
- `message` — text — system-generated or clinician-authored
- `channel` — text (enum: email, sms, in_app) — system-defined
- `sent_at` — timestamp (nullable) — system-generated
- `read_at` — timestamp (nullable) — system-captured
- `created_at` — timestamp — system-generated

---

## 11. Data Curation

### `data_curation_tasks`
- `id` — uuid, PK — generated
- `source_record_id` — uuid, FK → raw_ingestion_records — system-assigned
- `beneficiary_id` — uuid, FK → beneficiaries (nullable) — system-linked (pseudonymized for offshore)
- `assigned_to` — uuid, FK → users — system-assigned
- `task_type` — text (enum: lab_extraction, prom_scoring, intake_normalization, document_summarization, daily_log_review) — system-assigned
- `status` — text (enum: pending, in_progress, completed, needs_clarification, rejected) — system-driven
- `assigned_at` — timestamp — system-generated
- `completed_at` — timestamp (nullable) — system-generated
- `notes` — text (nullable) — assistant input
- `created_at` — timestamp — system-generated

### `curated_observation_drafts`
- `id` — uuid, PK — generated
- `curation_task_id` — uuid, FK → data_curation_tasks — system-linked
- `beneficiary_id` — uuid, FK → beneficiaries — system-linked
- `care_period_id` — uuid, FK → care_periods (nullable) — system-linked
- `observation_type` — text (enum: lab, vital_sign, symptom, imaging_finding, prom_score, other) — assistant entered
- `measure_name` — text — assistant entered
- `loinc_code` — uuid, FK → loinc_codes (nullable) — standards mapping
- `value_text` — text (nullable) — assistant entered
- `value_numeric` — number (nullable) — assistant entered
- `unit` — text (nullable) — assistant entered
- `collection_date` — date (nullable) — assistant extracted from source
- `data_source` — text — assistant entered
- `collection_method` — text (nullable) — assistant entered
- `notes` — text (nullable) — assistant entered
- `status` — text (enum: drafted, needs_clarification, qc_passed, qc_rejected, clinician_review_required, approved, rejected) — system-driven
- `migrated_to_observation_id` — uuid, FK → clinical_observations (nullable) — set when draft is approved and migrated to canonical record
- `created_at` — timestamp — system-generated
- `updated_at` — timestamp — system-generated

> **Change from v0.2:** Added `migrated_to_observation_id` — when a draft is approved, its data migrates to `clinical_observations` and this FK links the two. The draft remains as an immutable record of the curation process. Added LOINC field.

### `curation_review_events`
- `id` — uuid, PK — generated
- `draft_id` — uuid, FK → curated_observation_drafts — system-linked
- `reviewer_id` — uuid, FK → users — system-captured
- `review_type` — text (enum: qc_review, clinician_review, physician_review) — system-assigned
- `decision` — text (enum: approved, rejected, needs_revision) — reviewer input
- `comments` — text (nullable) — reviewer input
- `corrected_value_text` — text (nullable) — reviewer input
- `corrected_value_numeric` — number (nullable) — reviewer input
- `reviewed_at` — timestamp — system-generated

### `source_document_records`
- `id` — uuid, PK — generated
- `beneficiary_id` — uuid, FK → beneficiaries (nullable) — system-linked
- `intake_record_id` — uuid, FK → intake_records (nullable) — system-linked (pre-beneficiary)
- `document_type` — text (enum: lab_report, imaging_report, prom_form, daily_log, intake_form, other) — system-assigned
- `source_system` — text (enum: healthie, labcorp, quest, patient_upload, other) — system-assigned
- `source_record_id` — text (nullable) — external system reference
- `document_date` — date (nullable) — from document metadata
- `linked_ingestion_id` — uuid, FK → raw_ingestion_records (nullable) — system-linked
- `created_at` — timestamp — system-generated

---

## 12. Documents

### `clinical_documents`
- `id` — uuid, PK — generated
- `beneficiary_id` — uuid, FK → beneficiaries (nullable) — system-linked
- `intake_record_id` — uuid, FK → intake_records (nullable) — system-linked (pre-beneficiary)
- `document_type` — text (enum: lab_pdf, imaging_pdf, prom_screenshot, clinical_note, consent_form, other) — system-assigned
- `file_name` — text — from upload / system-captured
- `storage_path` — text — object storage path (S3)
- `mime_type` — text — system-captured
- `file_size_bytes` — number — system-captured
- `upload_date` — date — system-captured
- `uploaded_by` — uuid, FK → users (nullable) — system-captured
- `is_encrypted` — boolean — system-captured
- `created_at` — timestamp — system-generated

### `document_metadata`
- `id` — uuid, PK — generated
- `document_id` — uuid, FK → clinical_documents — system-linked
- `metadata_key` — text — system-defined
- `metadata_value` — text — system-captured or curation-entered
- `created_at` — timestamp — system-generated

### `document_versions`
- `id` — uuid, PK — generated
- `document_id` — uuid, FK → clinical_documents — system-linked
- `version_number` — number — system-assigned
- `storage_path` — text — object storage path
- `change_reason` — text (nullable) — system-captured
- `uploaded_by` — uuid, FK → users — system-captured
- `created_at` — timestamp — system-generated

### `document_access_events`
- `id` — uuid, PK — generated
- `document_id` — uuid, FK → clinical_documents — system-linked
- `user_id` — uuid, FK → users — system-captured
- `action` — text (enum: view, download, upload, delete, share) — system-captured
- `accessed_at` — timestamp — system-generated

---

## 13. Offshore Pseudonymous Review (New in v0.2)

### `pseudonymous_case_records`
- `id` — uuid, PK — generated
- `beneficiary_id` — uuid, FK → beneficiaries — **not accessible to offshore reviewer role**
- `case_id` — text, unique — pseudonymous identifier (visible to offshore)
- `age_band` — text (nullable, e.g., "65-69", "70-74") — system-derived
- `sex` — text — from beneficiary
- `clinical_track` — text (nullable) — from care period
- `care_period_id` — uuid, FK → care_periods — system-linked
- `created_at` — timestamp — system-generated

> **New in v0.2:** Offshore pseudonymous review table. Contains no identity data (no name, DOB, email, phone, MBI). The `beneficiary_id` FK exists for system linkage but is gated by RBAC so offshore reviewers cannot traverse it.

### `pseudonymous_clinical_packets`
- `id` — uuid, PK — generated
- `case_record_id` — uuid, FK → pseudonymous_case_records (`id`) — system-linked
- `packet_type` — text (enum: lab_summary, symptom_summary, imaging_summary, execution_summary, composite) — system-defined
- `structured_data` — jsonb — de-identified clinical values and trends
- `deidentified_at` — timestamp — system-generated
- `deidentification_method` — text (nullable, e.g., "field_removal", "value_banding", "tokenization") — system-defined
- `review_status` — text (enum: pending, under_review, reviewed, approved) — system-driven
- `reviewed_by` — uuid, FK → users (nullable) — offshore reviewer
- `reviewed_at` — timestamp (nullable) — system-generated
- `created_at` — timestamp — system-generated

---

## 14. Governance

### `physician_reviews`
- `id` — uuid, PK — generated
- `intake_record_id` — uuid, FK → intake_records (nullable) — system-linked (pre-beneficiary)
- `beneficiary_id` — uuid, FK → beneficiaries (nullable) — system-linked (post-beneficiary)
- `care_period_id` — uuid, FK → care_periods (nullable) — system-linked
- `review_type` — text (enum: intake_approval, eligibility_decision, enrollment_decision, observation_approval, pause_escalation, monthly_summary, other) — system-assigned
- `review_subject_id` — uuid (nullable) — polymorphic FK to the subject record
- `review_subject_type` — text — system-captured
- `decision` — text (enum: approved, rejected, deferred, needs_more_info, pause, escalate) — physician input
- `notes` — text (nullable) — physician input
- `reviewed_by` — uuid, FK → users — system-captured
- `reviewed_at` — timestamp — system-generated

> **Change from v0.2:** Added `intake_record_id` for pre-beneficiary reviews (intake approval, enrollment decisions).

### `clinical_review_tasks`
- `id` — uuid, PK — generated
- `beneficiary_id` — uuid, FK → beneficiaries (nullable) — system-linked
- `care_period_id` — uuid, FK → care_periods (nullable) — system-linked
- `assigned_to` — uuid, FK → users — system-assigned
- `task_type` — text (enum: observation_review, safety_signal_review, monthly_summary_review, adverse_event_review) — system-assigned
- `status` — text (enum: pending, in_progress, completed, escalated) — system-driven
- `assigned_at` — timestamp — system-generated
- `completed_at` — timestamp (nullable) — system-generated
- `notes` — text (nullable) — reviewer input

### `safety_signal_events`
- `id` — uuid, PK — generated
- `beneficiary_id` — uuid, FK → beneficiaries — system-linked
- `care_period_id` — uuid, FK → care_periods — system-linked
- `signal_severity` — text (enum: red, yellow, green) — system-derived from safety rules
- `signal_type` — text (enum: chest_pain, fever, bleeding, er_visit, hospitalization, new_diagnosis, adverse_event, missed_checkin, medication_change, other) — system-derived or patient-entered
- `signal_details` — jsonb — system-assembled (relevant field values from daily logs)
- `source_record_id` — uuid (nullable) — polymorphic FK to source table
- `auto_generated` — boolean — system-captured
- `review_status` — text (enum: pending, reviewed, escalated, resolved) — system-driven
- `reviewed_by` — uuid, FK → users (nullable) — system-captured
- `reviewed_at` — timestamp (nullable) — system-generated
- `created_at` — timestamp — system-generated

### `clinical_event_logs`
- `id` — uuid, PK — generated
- `beneficiary_id` — uuid, FK → beneficiaries — from daily check-in
- `care_period_id` — uuid, FK → care_periods — system-linked
- `event_date` — date — from daily check-in
- `event_type` — text (enum: medication_change, er_visit, hospitalization, new_diagnosis, procedure, doctor_visit, adverse_event, other) — from daily check-in
- `event_details` — text — from daily check-in
- `source_system` — text (enum: healthie, patient_app) — system-captured
- `requires_review` — boolean — system-derived
- `created_at` — timestamp — system-generated

### `care_team_review_tasks`
- `id` — uuid, PK — generated
- `beneficiary_id` — uuid, FK → beneficiaries — system-linked
- `care_period_id` — uuid, FK → care_periods — system-linked
- `assigned_to` — uuid, FK → users (nullable) — system-assigned
- `trigger_type` — text (enum: safety_signal, missed_checkin, symptom_escalation, monthly_summary, adverse_event) — system-derived
- `trigger_record_id` — uuid — polymorphic FK to source event
- `priority` — text (enum: low, medium, high, urgent) — system-derived from signal severity
- `status` — text (enum: pending, in_progress, completed, dismissed) — system-driven
- `notes` — text (nullable) — care team input
- `assigned_at` — timestamp — system-generated
- `completed_at` — timestamp (nullable) — system-generated

### `audit_events`
- `id` — uuid, PK — generated
- `user_id` — uuid, FK → users (nullable) — system-captured
- `action` — text (enum: view, create, edit, approve, reject, export, delete, lock, submit_to_cms) — system-captured
- `target_table` — text — system-captured
- `target_record_id` — uuid — system-captured
- `changed_fields` — jsonb (nullable) — system-captured (field names and `[REDACTED]` for PHI fields, actual values for non-PHI fields)
- `old_value_hash` — text (nullable) — system-captured (SHA-256 hash of old value for PHI fields, to verify without storing plaintext)
- `new_value_hash` — text (nullable) — system-captured (SHA-256 hash of new value for PHI fields)
- `ip_address` — text (nullable) — system-captured
- `user_agent` — text (nullable) — system-captured
- `created_at` — timestamp — system-generated, immutable

> **Change from v0.2:** Replaced `old_value` / `new_value` (which stored full JSONB including PHI) with `changed_fields` (only changed fields, PHI replaced with `[REDACTED]`) and hash fields for PHI verification without storing plaintext PHI in audit logs.

---

## 15. Standards Reference Tables (New in v0.2)

### `loinc_codes`
- `id` — uuid, PK — generated
- `loinc_number` — text, unique — LOINC code (e.g., "2345-7")
- `component` — text — LOINC component name (e.g., "Glucose")
- `property` — text (nullable) — LOINC property (e.g., "Mass Concentration")
- `time_aspect` — text (nullable) — LOINC time aspect (e.g., "Pt")
- `system` — text (nullable) — LOINC system (e.g., "Serum/Plasma")
- `method` — text (nullable) — LOINC method
- `class_name` — text (nullable) — LOINC class (e.g., "Chemistry")
- `common_name` — text (nullable) — common test name
- `is_active` — boolean — system-managed
- `created_at` — timestamp — system-generated

### `ucum_units`
- `id` — uuid, PK — generated
- `ucum_code` — text, unique — UCUM code (e.g., "mg/dL", "mm[Hg]")
- `description` — text — unit description
- `is_active` — boolean — system-managed
- `created_at` — timestamp — system-generated

### `fhir_concept_mappings`
- `id` — uuid, PK — generated
- `source_system` — text (enum: loinc, ucum, snomed_ct, cvx, local) — system-defined
- `source_code` — text — code in the source system
- `fhir_system` — text — FHIR code system URI
- `fhir_code` — text — FHIR code value
- `display` — text (nullable) — human-readable display
- `mapping_status` — text (enum: confirmed, proposed, pending_review) — system-defined
- `created_at` — timestamp — system-generated
- `updated_at` — timestamp — system-generated

---

## 16. Dashboard / Analytics

### `reporting_views`
- `id` — uuid, PK — generated
- `view_name` — text — system-defined
- `view_sql` — text — system-defined (stored as view definition)
- `aggregation_level` — text (enum: population, clinical_track, care_period, beneficiary) — system-defined
- `is_public_reportable` — boolean — system-defined
- `suppression_required` — boolean — system-calculated
- `data_origin` — text (enum: cms_derived, metaflow_derived, combined) — system-defined
- `is_active` — boolean — system-managed
- `created_at` — timestamp — system-generated

### `aggregate_outcomes`
- `id` — uuid, PK — generated
- `clinical_track` — text (enum: CKM, eCKM, MSK) — system-defined
- `measure_name` — text — from oap_measure_definitions
- `period_start` — date — system-defined
- `period_end` — date — system-defined
- `denominator` — number — system-calculated
- `numerator` — number — system-calculated
- `rate_or_mean` — number — system-calculated
- `cell_size` — number — system-calculated
- `suppression_required` — boolean — system-calculated (cell_size < threshold)
- `data_origin` — text (enum: cms_derived, metaflow_derived) — system-defined
- `created_at` — timestamp — system-generated

### `suppression_rules`
- `id` — uuid, PK — generated
- `rule_name` — text — system-defined
- `min_cell_size` — number — system-defined (e.g., N < 10 suppress)
- `applies_to` — text (enum: all, cms_derived, metaflow_derived) — system-defined
- `is_active` — boolean — system-managed
- `created_at` — timestamp — system-generated

### `public_report_exports`
- `id` — uuid, PK — generated
- `report_name` — text — system-defined
- `export_date` — date — system-generated
- `storage_path` — text — object storage path
- `approved_by` — uuid, FK → users — system-captured
- `approved_at` — timestamp — system-generated
- `is_published` — boolean — system-managed
- `created_at` — timestamp — system-generated

---

**Notes on v0.3:**

- **Two-stage identity:** `intake_records` → (eligibility + alignment + consent) → `beneficiaries`. Intake tables link to `intake_record_id`; post-enrollment tables link to `beneficiary_id`.
- **Canonical data model:** `oap_measure_observations` references `clinical_observations` via `canonical_observation_id` (reporting projection, not duplicate). `curated_observation_drafts` → on approval, values migrate to `clinical_observations` via `migrated_to_observation_id`. Drafts remain as immutable curation records.
- **JSONB FK arrays replaced:** `clinical_observation_sources` and `structural_evidence_sources` are relational association tables.
- **PHI minimization:** DOB is encrypted. SSN is not stored. Audit `old_value`/`new_value` replaced with `changed_fields` (PHI redacted) + hash columns.
- **Offshore separation:** `pseudonymous_case_records` and `pseudonymous_clinical_packets` contain no identity data. The `beneficiary_id` FK is gated by RBAC.
- **Structured imaging:** Carotid and liver ultrasound findings are stored as structured relational measurements with longitudinal comparison tables, not free-text summaries.
- **Monitoring plans:** Longitudinal, configurable, with baseline/prior-result comparison. No fixed `month_N_result` columns.
- **Coding standards:** LOINC, UCUM, and FHIR mapping reference tables for future interoperability.
- **Healthie-first model:** Healthie is the unified patient-facing and clinical workflow platform. Healthie GraphQL API validated in sandbox (July 2026). See `docs/healthie/Healthie_Integration_Mapping.md`.
- **Remaining pending items:** Lab feed structure for LOINC mapping, MSK PROM instrument details, daily check-in template with exact safety rules.