# RonanRx / Elite Care Pharmacy: Database Field Census (database.md) **Frozen census date:** 2026-07-17 · **Current-ref comparison:** 2026-07-21 **Status:** Historical field census. Scope is the `ronanrx-core` schema plus core-facing iOS contracts; sibling repos remain out of scope. This document preserves the frozen table and column structure and does not claim to be the current schema. **Frozen totals:** **177 tables · 2,238 columns**; every table and column from the snapshot appears exactly once. Snapshot classification: **139 ACTIVE · 6 REFERENCE · 24 DORMANT · 6 PRESERVE · 2 REVIEW-HOLD**. **Method:** generated from `db/structure.sql` at historical staging @ `f246ed27` on 2026-07-17 and exact-verified against that snapshot. Caller evidence and status labels below describe that snapshot unless a current-ref qualifier says otherwise. **Current refs:** production main @ `d26d5e1c238bcedffef44fb4acadc1a6a3eb7788` and staging @ `c00248749c0287f6c87f5a553394b339fb2dedd5` each contain 168 Rails public tables with identical table lists. The ten mobile and HealthKit tables in this census (`dose_logs`, `healthkit_batch_receipts`, `healthkit_samples`, `intake_logs`, `mobile_sessions`, `patient_goals`, `pending_mobile_oauth_authorizations`, `regimen_items`, `regimens`, `side_effect_logs`) and the `/api/mobile` namespace are absent from both current refs. `suggested_prescriptions` exists on both current refs but post-dates and is absent from this frozen census. The Flue `agent.onboarding_records` schema is separate from the Rails public-table census. **Why this document exists**: one inventory so new schema reuses what exists. Additions must be justified against the census. A zero-caller finding does not authorize removal: verify code/history, production counts/samples, dependencies, replacement/migration, and obtain product+engineering plus applicable clinical/compliance approval. **Table status legend (used in this document):** - **ACTIVE**: production code both writes and reads the table. - **REFERENCE**: seed/offline-maintained lookup data with production readers; the correct state is "no runtime writes". Not dormant: the table is doing its job. - **DORMANT**: real table, modeled and often surfaced, but the write path, the read path, or both have no production caller (built-but-unwired, or rake/console-only paths standing in for a missing production path). - **PRESERVE**: an incomplete intended or legacy product substrate explicitly retained for completion/redesign. A missing end-to-end production loop is still recorded, but does not make the substrate disposable or a dormant product. - **REVIEW-HOLD**: zero production references found in the audited tree, but preservation is required until history, production data, dependencies, migration, and approvals are complete. **Status qualifiers:** - **(write-only ledger)**: capture/audit tables are ACTIVE when production WRITES exist; reads happen out-of-band (console/ops) by design. **Status rule:** a table's status answers one question: is the table doing its job in production? **Feature status legend (for `Spec ref` cross-references into business.md / technical.md):** 🟢 EXISTS · 🟡 PARTIAL · 🔴 FUTURE. No currently mapped in-scope feature is classified as dormant. **Reading a table entry**: heading = exact table name (deep-linkable); then purpose, status + evidence, owning spec feature, encryption (listed only where encrypted columns or tamper-evidence exist), CHECK constraints, notable indexes, and the full column table in schema order. --- ## REVIEW-HOLD register Items formerly labeled dead candidates. This is not a delete order. Treat existing records as real until production evidence proves otherwise; use reversible deprecation/export/migration first. | Item | Kind | Reason / evidence | Disposition | | --------------------------------- | ------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------- | | `ehr_intake_reviews` | whole table | No production caller found in audited tree | REVIEW-HOLD: verify history, production rows/samples, dependencies, migration, approvals | | `ehr_state_prescribing_rules` | whole table + model | No production caller found in audited tree | REVIEW-HOLD: same system-wide verification | | `prescriptions.external_id` | column | Zero code references found | REVIEW-HOLD: verify integrations/history/data before migration | | `uploaded_documents.fixture_path` | column | Appears only in structure.sql | REVIEW-HOLD: verify history/data before migration | | `programs.template` | column | Unused/empty in seeds; no consumer found | REVIEW-HOLD: document intended use or migrate after full review | | `ehr_icd10_codes.billable` | column | Default true; no consumer found | REVIEW-HOLD: verify history/data/dependencies | | `patients.date_of_birth_year` | column | Zero code references found; encrypted `dob` is active | REVIEW-HOLD: verify history/data/migration | | `pharmacy_orders.external_id` | column | No code reader/writer found | REVIEW-HOLD: verify pharmacy integrations/data | | `shipments.external_id` | column | No code writer found | REVIEW-HOLD: preserve with carrier-integration design in view | **Adjacent items outside this register:** `prescriptions.doctor_approval_id` remains live because it is still validated unless `issued_from_signed_ehr_encounter?` (`prescription.rb:27`); `prescriptions.expires_at` has no production writer but is needed for the §3.4 duration target; `lab_orders` and `lab_results` belong with the §4.1 build and the DORMANT register. The frozen audit found active references for `compounding_tasks.beyond_use_date`, `shipments.temperature_trace`, and `governed_artifact_versions.eval_runs`, so those items live in the more precise registers below. --- ## DISPLAYED-BUT-NEVER-WRITTEN register Two columns have live readers but no production writer; implement the writer or review the surface. They are not REVIEW-HOLD because zero references was disproven. | Item | Production read surfaces | Missing writer / risk | Disposition | | ----------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | ------------------------------------------------------------------------------------------------------ | | `compounding_tasks.beyond_use_date` | Rendered to patients (app/views/patient/orders/preparation.html.erb:79-82, app/views/patient/orders/show.html.erb:160-164), lab (app/views/lab/queue/index.html.erb:72, app/views/lab/compounding/show.html.erb:17-21 + 160-161, app/views/lab/compounding/index.html.erb:28, app/views/shared/daily_prep/\_print_labels.html.erb:20, app/views/shared/daily_prep/\_open_batches.html.erb:29-30), and pharmacist (app/views/pharmacist/compounding_tasks/show.html.erb:21-23) | No production writer was found in the frozen audit. ReleaseQa and Agents::QualityGate hard-code `bud_set => true` without checking for a supplied value. The pharmacy supplies and prints BUD; software does not derive it. | **Preserve:** accept and display the pharmacy-supplied value, and make release test for an actual value | | `shipments.temperature_trace` | app/views/lab/shipments/show.html.erb:4 (`Array(s.temperature_trace)`) | jsonb default `[]`; nothing writes it in the current tree: cold-chain trace never implemented | Implement with the internal network; preserve pending full review | --- ## LEGACY / DEPRECATION register (migration required) Legacy fields still referenced by live code but superseded by a decided replacement or policy: droppable only after the caller is retired and existing data is handled. | Item | Live read path | Replacement | Removal precondition | | -------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ----------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------- | | `governed_artifact_versions.eval_runs` | Still read as the fallback branch of GovernedArtifactVersion#passing_eval_for? (app/models/governed_artifact_version.rb:30-38), whose production callers are Linq::RedFlagDetector (app/services/linq/red_flag_detector.rb:64-67) and Workflows::SignPolicyArtifact (app/services/workflows/sign_policy_artifact.rb:40-42) | `governed_artifact_eval_runs` table (REFERENCE) | Verify every version relying on legacy JSON evidence has a backfilled `governed_artifact_eval_runs` row, remove the fallback branch, then drop | | `uploaded_documents.object_key_back` / `generation_back` | `Linq::IdAutoPromote#build_or_extend_document` can still attach/store a license back through `Identity::IdDocumentStore#attach_back!`; model/store read/delete helpers also reference the fields | Decided front-only ID capture; portal and OCR extraction already use only the front | Remove the Linq back-image path; census rows and GCS objects; delete any retained back objects under an approved migration; remove back-side helpers/tests; then drop both columns | --- ## REFERENCE register The 6 reference tables (seed/offline-maintained lookup data with production readers; correct state is "no runtime writes"). Full column detail in each table's section entry. | Table | § | Maintained by (offline writer) | Production readers | | ----------------------------- | --- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------- | | `biomarker_crosswalks` | 4.2 | db/seeds/biomarker_crosswalks.rb:47 | BiomarkerCrosswalk.lookup! in the lab import path (function_health_importer.rb:94, function_health_extractor.rb:92) | | `ehr_dea_schedules` | 3.2 | db/seeds/dea_schedule_classifications.rb:22 via Ehr::Workflows::UpsertDeaSchedule (its only caller) | Ehr::ControlledSubstances::ScheduleResolver (schedule_resolver.rb:71) on the Rx card | | `ehr_icd10_codes` | 3.1 | db/seeds/icd10_codes.rb | ehr/encounters_controller.rb:437 + :775, sign gate sign_encounter.rb:158, agents icd_suggestions.rb:89 | | `fda_reference_strengths` | 3.2 | db/seeds/fda_reference_strengths.rb | Ehr::Integration::DoseGuardrail (dose_guardrail.rb) from prescribing.rb + prescription_card.rb | | `governed_artifact_eval_runs` | 9.7 | Offline-only by design: lib/tasks/governed_artifacts.rake:15, seeds (Registry.seed_defaults! -> SeededEvalEvidence.apply!, registry.rb:114-116), migration backfill (emergency_triage_registry_v1.rb:290); CHECK pins provenance to offline_runner\|migration_backfill | GovernedArtifactVersion#passing_eval_for? consumed by Workflows::SignPolicyArtifact:41 and Linq::RedFlagDetector:67 | | `program_formula_mappings` | 5.1 | db/seeds.rb:96 -> db/seeds/program_formula_mappings.rb (2 seeded mappings) | ehr/integration/prescribing.rb:693 + :733 | --- ## PRESERVE register These six tables are part of explicitly retained product substrate. Their caller evidence still shows an incomplete or unverified production loop; the disposition is completion/redesign rather than dormancy or removal. | Table | § | Caller evidence | Disposition | | --- | --- | --- | --- | | `compounding_tasks` | 5.2 | Pharmacist read UI exists; `StartCompounding` and seed/demo paths are the only verified writers | Preserve and complete the internal fulfillment network | | `formulations` | 5.2 | Seeder-written; readers belong to the incomplete internal fulfillment lane | Preserve and complete | | `label_proofs` | 5.2 | Writer is test/demo-reached `CompleteCompounding`; label-print readers exist | Preserve and complete | | `quality_releases` | 5.2 | Writer is test/demo-reached `ReleaseQa`; downstream readers exist | Preserve and complete; make the BUD checklist test for a supplied value | | `shipments` | 5.2 | Production read surfaces exist; `ConfirmShippingReadiness` has no verified production caller | Preserve and complete carrier/shipping workflow | | `refill_tasks` | 7.2 | Patient confirm/hold surfaces exist; creator is reached through the incomplete delivery workflow | Preserve legacy scaffolding for redesign; production/history census remains unresolved | --- ## DORMANT register The 24 remaining dormant tables (no complete production read+write loop and no explicit preserve disposition above), one line each. Full column detail appears in each table's section entry. | Table | § | Why it is dormant | | ------------------------------------ | ----- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | `active_storage_variant_records` | infra | Framework-installed; no `.variant(` call anywhere in app/: never written or read | | `biomarker_review_items` | 4.2 | Write-only ledger (crosswalk misses enqueue in production) awaiting a review UI: nothing reads or works the queue | | `dead_letter_intakes` | 3.5 | Written by recurring `PublicIntakePullJob`, but nothing reads rows back: no admin UI, no retry consumer | | `doctor_calendar_feeds` | 2.2 | Prod endpoint only reads existing rows (doctor/calendar_feeds_controller.rb:15-20); rows console-minted only via DoctorCalendarFeed.for_doctor (doctor_calendar_feed.rb:26-27, no production caller): feed provisioning not wired in prod | | `ehr_assessments` | 3.1 | Rows arrive only via ops rake records:import_extract (records/extract_importer.rb via lib/tasks/records.rake:109); chart read surface exists but no production writer: same lane as ehr_observations/ehr_procedures | | `ehr_coverages` | 3.5 | Same rake-only import writer (extract_importer.rb:12 via records.rake:109); chart read surface exists, no production write path | | `ehr_diagnostic_reports` | 3.5 | Same rake-only import writer (extract_importer.rb:7 via records.rake:109); chart read surface exists, no production write path | | `ehr_health_history_reviews` | 8.2 | Write-only via `HealthHistoryProjector`; `review_status` never leaves `pending_review`, no review UI exists | | `ehr_immunizations` | 3.5 | Same rake-only import writer (extract_importer.rb:11 via records.rake:109); chart read surface exists, no production write path | | `ehr_lab_import_export_runs` | 4 | Facade write methods (`start_import_export_run!`/`finish_…`) have zero callers; nothing reads the table | | `ehr_observations` | 3.1 | Rows arrive only via `rake records:import_extract`; charts read surface exists but no production writer | | `ehr_patient_account_bindings` | 3.1 | Read by EHR controllers, but the only writer is rake-driven `CoreChartBootstrapper`; `find_by_user` falls back to `user.patient` | | `ehr_procedures` | 3.1 | Same rake-only import writer as `ehr_observations`; no production write path | | `ehr_retention_policies` | 3.1 | Only writer `DefineRetentionPolicy` has zero callers; no production read or write path | | `ehr_safety_screen_acknowledgements` | 3.2 | Only writer `AcknowledgeSafetyScreen` has no production caller (its own test only) | | `lab_orders` | 4.1 | Read paths exist, but NOTHING creates LabOrders in production (canonical "LabOrder write path" register entry) | | `lab_results` | 4.1 | No production writer: live lab data flows through `imported_lab_results` instead; dormant with parent `lab_orders` | | `medwatch_reports` | 7.5 | Written in prod on adverse events, but no reader/UI exists and `SubmitMedwatchReport` has zero callers: reports are never surfaced or submitted | | `patient_goals` | 10.1 | Historical f246 table with no reader or writer in that snapshot; absent from both current refs | | `patient_health_history_revisions` | 8.2 | Write-only revision trail; nothing reads `.revisions` (only `dependent: :destroy`) | | `recommendations` | 3.2 | Only creator `SubmitForDoctorReview` is tests-only; prod read surfaces exist but rows only arise from demo journeys | | `records_pdf_chunks` | 3.5 | Production digestion pipeline writes them; the retrieval/consumption layer is not built | | `records_pdf_elements` | 3.5 | Written by the digestion pipeline; no production reader outside the pipeline | | `records_pdf_pages` | 3.5 | Written by the digestion pipeline; no production reader outside the pipeline | Unwired and legacy **code paths** without their own tables are cataloged in technical.md's evidence-gated review register. `ReplyToSupportMessage` appeared at historical f246 and is absent from both current refs. --- ## §1: Patient Entry & Onboarding (17 tables) ### funnel_counters Durable anonymous daily funnel counters (Postgres replacement for the old Cloudflare KV FUNNEL_COUNTERS namespace); atomic ON CONFLICT upsert-increment, aggregate-only, never PII. - **Status:** **ACTIVE**: Incremented in prod from app/controllers/api/event_controller.rb:30, app/jobs/posthog_capture_job.rb:17, and app/services/analytics/server_event.rb:29; read by Observability::SlackDigest (app/services/observability/slack_digest.rb:79) on the daily ObservabilityDigestJob cron - **Spec ref:** §1 Patient Entry & Onboarding: section-level core table - **Notable indexes:** `UNIQUE index_funnel_counters_on_day_and_key (day, key): ON CONFLICT increment target` 6 columns: | Column | Type | Null | Note | | ------------ | ----------------- | -------- | ---------------------- | | `id` | bigint | NOT NULL | | | `count` | bigint | NOT NULL | default 0 | | `created_at` | timestamp(6) | NOT NULL | | | `day` | date | NOT NULL | | | `key` | character varying | NOT NULL | max length 120 (model) | | `updated_at` | timestamp(6) | NOT NULL | | ### patients Central patient record; PII encrypted at column level with SHA256 shadow lookup hashes for querying. - **Status:** **ACTIVE**: Core entity read+written across nearly every production controller/service (e.g. app/controllers/api/v1/signups_controller.rb, app/services/records/core_chart_bootstrapper.rb:107) - **Spec ref:** §1 Patient Entry & Onboarding: section-level core table - **Encryption:** name, email, phone, dob, address: Rails Active Record Encryption (app/models/patient.rb:53-57); email_lookup_hash/phone_lookup_hash are SHA256 shadow columns for lookup - **Notable indexes:** `UNIQUE (email_lookup_hash)` · `UNIQUE (external_id) WHERE external_id IS NOT NULL` · `UNIQUE (stripe_customer_id) WHERE stripe_customer_id IS NOT NULL` · `UNIQUE (user_id) WHERE user_id IS NOT NULL` 21 columns: | Column | Type | Null | Note | | ------------------------------ | ----------------- | -------- | ---------------------------------------------------------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `address` | text | nullable | encrypted | | `clinic_id` | bigint | NOT NULL | FK clinics | | `created_at` | timestamp(6) | NOT NULL | | | `date_of_birth_year` | date | nullable | REVIEW-HOLD: zero code references found; dob (encrypted text) is the active field | | `dob` | text | nullable | encrypted | | `email` | text | nullable | encrypted | | `email_lookup_hash` | character varying | nullable | SHA256 of normalized email; unique | | `external_id` | character varying | nullable | tags imported/demo patients ('imported:%' scope, patient.rb:66); matched in records/core_chart_bootstrapper.rb:107-155 | | `name` | text | nullable | encrypted | | `phone` | text | nullable | encrypted | | `phone_lookup_hash` | character varying | nullable | SHA256 of normalized digits (US leading 1 stripped) | | `sex` | character varying | NOT NULL | enum male\|female\|other | | `state` | character varying | nullable | 2-letter; model validates presence+length despite NULL-able column | | `status` | character varying | NOT NULL | default 'active'; enum active\|inactive\|blocked | | `stripe_customer_id` | character varying | nullable | membership Stripe account customer | | `updated_at` | timestamp(6) | NOT NULL | | | `user_id` | bigint | nullable | FK users; unique when present | | `state_of_record` | character varying | nullable | prescribing state of record | | `state_of_record_source` | character varying | nullable | enum confirmed_address\|patient_reported\|system_default | | `state_of_record_confirmed_at` | timestamp(6) | nullable | | ### users Cross-cutting account table for all humans: patients, doctors, pharmacists, lab_ops, admins: auth, MFA/passkey, analytics consent. - **Status:** **ACTIVE**: Core auth/identity model referenced throughout every controller (has_secure_password, staff passkey login via staff/passkey_sessions_controller.rb, patient signup); read+write everywhere in prod. - **Spec ref:** §1 Patient Entry & Onboarding: section-level core table - **Encryption:** none via Rails encrypts; password_digest is bcrypt, phone_binding_digest is an HMAC digest (not reversible) - **CHECK constraints:** `users_staff_mfa_required: role='patient' OR mfa_required=true (all staff must have MFA)` - **Notable indexes:** `UNIQUE index_users_on_email` · `UNIQUE index_users_on_webauthn_id` · `UNIQUE partial index_users_on_google_meet_user_id WHERE NOT NULL` · `UNIQUE partial index_users_on_setup_link_nonce WHERE NOT NULL` 31 columns: | Column | Type | Null | Note | | ---------------------------- | ----------------- | -------- | ----------------------------------------------- | | `id` | bigint | NOT NULL | | | `clinic_id` | bigint | nullable | FK clinics | | `created_at` | timestamp(6) | NOT NULL | | | `email` | character varying | nullable | unique; validated present at model layer | | `first_name` | character varying | nullable | | | `last_name` | character varying | nullable | | | `license_number` | character varying | nullable | clinician licensure | | `license_state` | character varying | nullable | | | `locked_at` | timestamp(6) | nullable | | | `phone_binding_digest` | character varying | nullable | HMAC of phone for lookup without storing number | | `phone_binding_version` | character varying | nullable | | | `role` | character varying | NOT NULL | patient\|doctor\|pharmacist\|lab_ops\|admin | | `status` | character varying | NOT NULL | active\|disabled\|locked; default active | | `updated_at` | timestamp(6) | NOT NULL | | | `analytics_consent` | boolean | NOT NULL | default false | | `analytics_basis` | character varying | NOT NULL | default none | | `analytics_decided_at` | timestamp(6) | nullable | | | `analytics_revoked_at` | timestamp(6) | nullable | | | `analytics_role_at_decision` | character varying | nullable | | | `app_preferences` | jsonb | NOT NULL | default {} | | `password_digest` | character varying | nullable | bcrypt | | `mfa_required` | boolean | NOT NULL | default false; forced true for staff by CHECK | | `last_authenticated_at` | timestamp(6) | nullable | | | `last_reauth_at` | timestamp(6) | nullable | | | `failed_login_count` | integer | NOT NULL | default 0 | | `locked_until` | timestamp(6) | nullable | | | `email_verified_at` | timestamp(6) | nullable | | | `google_meet_user_id` | character varying | nullable | unique partial; Meet integration (2.6) | | `mfa_session_version` | integer | NOT NULL | default 0; bump invalidates MFA sessions | | `webauthn_id` | text | nullable | unique; WebAuthn user handle | | `setup_link_nonce` | character varying | nullable | unique partial; staff/provider setup links | ### login_audits Append-only ledger of passwordless-login events (OTP sent/verified, lockouts, denials, mobile session issue/rotate/revoke); user optional since attempts can precede a resolved user. - **Status:** **ACTIVE (write-only ledger)**: Written in the production login flow via LoginAudit.record! from Workflows::SendLoginCode/VerifyLoginCode/IssueMobileSession/RotateMobileSession/RevokeMobileSession (reached from app/controllers/sessions/otp_controller.rb and app/controllers/api/mobile/v1/auth_controller.rb); write-once capture/audit ledger doing its production job on the write side: reads happen out-of-band (console/ops) by design, no in-app reader - **Spec ref:** §1.1 iPhone Link (SMS entry + secure signup links): 🟢 EXISTS - **Encryption:** none (ip_hash/ua_hash/phone_binding_digest are hashes) - **Notable indexes:** `no updated_at column: append-only; readonly?/before_destroy enforced in model` 11 columns: | Column | Type | Null | Note | | ---------------------- | ----------------- | -------- | -------------------------------------------------- | | `id` | bigint | NOT NULL | | | `action` | character varying | NOT NULL | | | `channel` | character varying | nullable | | | `created_at` | timestamp(6) | NOT NULL | set explicitly by record!; table has no updated_at | | `failure_reason` | character varying | nullable | | | `ip_hash` | character varying | nullable | | | `metadata` | jsonb | NOT NULL | default {} | | `outcome` | character varying | nullable | | | `phone_binding_digest` | character varying | nullable | | | `ua_hash` | character varying | nullable | | | `user_id` | bigint | nullable | FK users, optional (pre-resolution attempts) | ### login_challenges One passwordless OTP challenge per login attempt: HMAC digest only (raw code never stored), attempt cap 5, atomic verify+consume. - **Status:** **ACTIVE**: Written by Workflows::SendLoginCode and consumed by Workflows::VerifyLoginCode (production callers app/controllers/sessions/otp_controller.rb and app/controllers/api/mobile/v1/auth_controller.rb); reaped hourly by LoginChallengeReapJob (config/recurring.yml login_challenge_reap) - **Spec ref:** §1.1 iPhone Link (SMS entry + secure signup links): 🟢 EXISTS - **Encryption:** none (otp_digest is an HMAC via OtpDigest.for(:login, ...); ip_hash/phone_binding_digest are hashes) - **Notable indexes:** `unique partial idx_login_challenges_one_active_per_phone_channel (phone_binding_digest, channel) WHERE consumed_at IS NULL AND invalidated_at IS NULL` 14 columns: | Column | Type | Null | Note | | ---------------------- | ----------------- | -------- | --------------------------------------------- | | `id` | bigint | NOT NULL | | | `attempts` | integer | NOT NULL | default 0; ATTEMPT_CAP = 5 | | `channel` | character varying | NOT NULL | enum sms only, default sms | | `consumed_at` | timestamp(6) | nullable | | | `created_at` | timestamp(6) | NOT NULL | | | `invalidated_at` | timestamp(6) | nullable | | | `ip_hash` | character varying | nullable | | | `metadata` | jsonb | NOT NULL | default {} | | `otp_digest` | character varying | nullable | HMAC digest of 6-digit code; raw never stored | | `otp_expires_at` | timestamp(6) | NOT NULL | | | `otp_sent_at` | timestamp(6) | nullable | | | `phone_binding_digest` | character varying | nullable | | | `updated_at` | timestamp(6) | NOT NULL | | | `user_id` | bigint | NOT NULL | FK users | ### public_intakes Raw public intake submissions from the entry funnel (encrypted payload, dedupe, verification, later linked to patient). - **Status:** **ACTIVE**: Written by PublicIntakes::Upsert (app/services/public_intakes/upsert.rb:25) from api/intake_controller.rb:30 and api/v1/intakes_controller.rb:7; read by admin/public_intakes_controller.rb:7 and ops/integration/dashboard.rb:78 - **Spec ref:** §1.1 iPhone Link (SMS entry + secure signup links): 🟢 EXISTS - **Encryption:** name, email, phone, qualifiers, triage, doctor, consents, raw_payload, verification_token: Rails Active Record Encryption (app/models/public_intake.rb:8-16); email/phone lookup hashes are SHA256 shadows - **Notable indexes:** `UNIQUE (request_id)` 31 columns: | Column | Type | Null | Note | | -------------------------- | ----------------- | -------- | ------------------------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `consents` | text | nullable | encrypted | | `created_at` | timestamp(6) | NOT NULL | | | `doctor` | text | nullable | encrypted | | `duplicate_conflict_at` | timestamp(6) | nullable | | | `duplicate_conflict_count` | integer | NOT NULL | default 0 | | `email` | character varying | nullable | encrypted | | `email_lookup_hash` | character varying | nullable | SHA256 lookup | | `flow_version` | character varying | nullable | | | `name` | character varying | nullable | encrypted | | `outcome` | character varying | NOT NULL | default 'submitted'; OUTCOMES = ['submitted'] only (public_intake.rb:3) | | `payload_hash` | character varying | NOT NULL | dedupe digest | | `phone` | character varying | nullable | encrypted | | `phone_lookup_hash` | character varying | nullable | SHA256 lookup | | `programs` | jsonb | NOT NULL | default [] | | `qualifiers` | text | nullable | encrypted | | `raw_payload` | text | nullable | encrypted | | `request_id` | character varying | NOT NULL | unique | | `schema_version` | integer | NOT NULL | default 1 | | `serviceable` | boolean | NOT NULL | default true | | `source` | character varying | nullable | | | `source_object_key` | character varying | nullable | | | `state` | character varying | NOT NULL | | | `status` | character varying | NOT NULL | default 'submitted'; submitted\|verified\|closed | | `submitted_at` | timestamp(6) | NOT NULL | | | `triage` | text | nullable | encrypted | | `updated_at` | timestamp(6) | NOT NULL | | | `verification_channel` | character varying | nullable | | | `verification_token` | character varying | nullable | encrypted | | `verified_at` | timestamp(6) | nullable | | | `patient_id` | bigint | nullable | FK patients; nullified on patient destroy, reconciled by Patient lookup-hash callback | ### rate_limits Append-only sliding-window attempt counter for passwordless login, email change, and ops API throttles. - **Status:** **ACTIVE**: Written+read atomically via RateLimit.exceeded? in prod paths (app/services/workflows/send_login_code.rb:161, app/services/workflows/verify_login_code.rb:33, app/controllers/api/ops/v1/base_controller.rb:148); pruned by LoginChallengeReapJob (app/jobs/login_challenge_reap_job.rb:25) - **Spec ref:** §1.1 iPhone Link (SMS entry + secure signup links): 🟢 EXISTS - **Encryption:** none (subject_key stores hashes/digests, not raw identifiers) - **Notable indexes:** `composite (scope, subject_key, occurred_at) for window counts` 5 columns: | Column | Type | Null | Note | | ------------- | ----------------- | -------- | ------------------------------------------------------------ | | `id` | bigint | NOT NULL | | | `created_at` | timestamp(6) | NOT NULL | no updated_at column; set explicitly on insert | | `occurred_at` | timestamp(6) | NOT NULL | | | `scope` | character varying | NOT NULL | e.g. login_send_ip_60s, email_change_user_hr, ops API scopes | | `subject_key` | character varying | NOT NULL | hashed IP / phone digest / user key | ### secure_links Short-code alias (`/p/`) for portal magic links so SMS carries a short, OG-card-friendly URL instead of a ~250-char signed token. - **Status:** **ACTIVE**: Written via SecureLink.issue_or_reuse_completion!/id_upload! from production paths (app/services/linq/signup_forward.rb:306, app/services/signup_completion_links/resend.rb:30, app/controllers/api/v1/linq_controller.rb:564); read/resolved by Portal::ShortLinksController (config/routes.rb:193,196) and app/jobs/linq_reply_job.rb:1127 - **Spec ref:** §1.1 iPhone Link (SMS entry + secure signup links): 🟢 EXISTS - **Encryption:** none (token is a signed magic-link token, stored plaintext; no encrypts declarations in app/models/secure_link.rb) - **Notable indexes:** `unique index on code (index_secure_links_on_code)` 9 columns: | Column | Type | Null | Note | | ---------------------- | ----------------- | -------- | --------------------------------------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `code` | character varying | NOT NULL | 22-char alphanumeric short code, unique; ~131 bits, alphabet chosen to avoid SendGuard PHI patterns | | `created_at` | timestamp(6) | NOT NULL | | | `expires_at` | timestamp(6) | NOT NULL | 24h TTL; SecureLink.active scope filters on it | | `intake_response_id` | bigint | NOT NULL | FK-style belongs_to intake_responses (indexed, no DB FK) | | `token` | text | NOT NULL | full SignupMagicLink signed token the code resolves to | | `updated_at` | timestamp(6) | NOT NULL | | | `purpose` | character varying | NOT NULL | enum-by-validation: completion \| id_upload (default completion) | | `linq_conversation_id` | bigint | nullable | belongs_to optional; required when purpose=id_upload | ### linq_agent_generation_claims Single-flight claim ticket for LLM signup-agent generation per Linq message (claim->generate->commit lifecycle with nonce; blocks double generation per phone). - **Status:** **ACTIVE**: Managed by Linq::AgentGenerationCoordination with production callers Api::V1::IntakeChatController, Linq::ConversationalIntake (LinqReplyJob path), Workflows::LatchLinqEmergencyHold, Agents::OnboardingConcierge - **Spec ref:** §1.2 Onboarding Agent: 🟢 EXISTS - **Encryption:** none (phone_binding_digest / nonce_digest are SHA-256 digests, not encryption) - **CHECK constraints:** `chk_linq_agent_claims_status: status IN (claimed, generated, committed, invalidated, expired)` · `chk_linq_agent_claims_lifecycle: state machine: claimed => artifact_id/generated_at/committed_at/invalidated_at all NULL; generated => artifact_id+generated_at set, committed_at/invalidated_at NULL; committed => artifact_id+generated_at+committed_at set; invalidated/expired => invalidated_at set, committed_at NULL` - **Notable indexes:** `unique partial idx_linq_agent_claims_message (message_id) WHERE status IN (claimed,generated,committed)` · `unique idx_linq_agent_claims_nonce (nonce_digest)` · `unique partial idx_linq_agent_claims_one_open_per_phone (phone_binding_digest) WHERE status IN (claimed,generated)` 13 columns: | Column | Type | Null | Note | | ---------------------- | ----------------- | -------- | ------------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `linq_conversation_id` | bigint | NOT NULL | FK linq_conversations | | `artifact_id` | bigint | nullable | FK artifacts; set at generated per lifecycle CHECK | | `phone_binding_digest` | character varying | NOT NULL | SHA-256 hex (validated \h{64}) | | `message_id` | character varying | NOT NULL | | | `nonce_digest` | character varying | NOT NULL | unique; SHA-256 of claim nonce, constant-time compared | | `status` | character varying | NOT NULL | enum claimed\|generated\|committed\|invalidated\|expired, default claimed | | `expires_at` | timestamp(6) | NOT NULL | | | `generated_at` | timestamp(6) | nullable | | | `committed_at` | timestamp(6) | nullable | | | `invalidated_at` | timestamp(6) | nullable | | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### linq_conversations FSM state for one Linq (iMessage) signup conversation: answers, flow_state, intake linkage, opt-out, human takeover; port of Cloudflare KV linq-state. - **Status:** **ACTIVE**: Written/read on every inbound webhook: POST /api/v1/linq/inbound (config/routes.rb:484,504) -> Api::V1::LinqController -> LinqReplyJob; pg advisory chat lock in app/models/linq_conversation.rb (with_chat_lock) - **Spec ref:** §1.2 Onboarding Agent: 🟢 EXISTS - **Encryption:** none (phone_binding_digest is a SHA-256 digest) - **CHECK constraints:** `linq_conversations_legacy_hold_binding: if meta red_flag_hold or founder_red_flag = 'true', a 64-hex phone_binding_digest must exist on the column or in meta` - **Notable indexes:** `unique index_linq_conversations_on_chat_id` · `partial index_linq_conversations_on_opt_out WHERE opt_out = true` 19 columns: | Column | Type | Null | Note | | --------------------------- | ----------------- | -------- | --------------------------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `answers` | jsonb | NOT NULL | default {} | | `chat_id` | character varying | NOT NULL | unique; advisory-lock key | | `completed_version` | integer | nullable | | | `contact_key` | character varying | NOT NULL | | | `created_at` | timestamp(6) | NOT NULL | | | `flow_state` | character varying | NOT NULL | FSM state, default inbound_seen; 'complete' terminal | | `intake_response_id` | bigint | nullable | FK intake_responses; not unique: stale/opted-out rows possible (see active_for_intake) | | `intake_state` | jsonb | NOT NULL | default {} | | `intake_version` | integer | NOT NULL | default 6 | | `last_decision` | character varying | nullable | | | `last_template_id` | character varying | nullable | | | `meta` | jsonb | NOT NULL | default {}; legacy emergency-hold flags live here (CHECK) | | `opt_out` | boolean | NOT NULL | default false | | `updated_at` | timestamp(6) | NOT NULL | | | `id_upload_token_nonce` | character varying | nullable | | | `phone_binding_digest` | character varying | nullable | SHA-256 hex, validated format | | `human_takeover_actor_id` | bigint | nullable | FK users; takeover is console-set only | | `human_takeover_started_at` | timestamp(6) | nullable | 24h TTL (HUMAN_TAKEOVER_TTL) | ### linq_emergency_holds Tier 1/2 emergency latch on a Linq conversation with pinned governed response copy; one active hold per conversation, immutable once released. - **Status:** **ACTIVE**: Latched in the production reply path via Workflows::LatchLinqEmergencyHold (LinqReplyJob / linq controllers); read gates replies (LinqConversation#emergency_hold_active?); release side (Workflows::ReleaseLinqEmergencyHold) has no UI and remains console/workflow only - **Spec ref:** §1.2 Onboarding Agent: 🟢 EXISTS - **Encryption:** release_reason, response_body: Rails AR encrypts (app/models/linq_emergency_hold.rb:2-3) - **CHECK constraints:** `chk_linq_emergency_holds_auto_tier: tier IN (1, 2)` · `chk_linq_emergency_holds_status: status IN (active, released)` · `chk_linq_emergency_holds_release_fields: active => released_at/released_by_id/release_reason NULL; released => all three present with non-blank reason` - **Notable indexes:** `unique partial idx_linq_emergency_holds_one_active (linq_conversation_id) WHERE status = 'active'` · `unique idx_linq_emergency_holds_source_message (source_message_id)` 18 columns: | Column | Type | Null | Note | | ------------------------------ | ----------------- | -------- | ------------------------------------------------------------------------------------------ | | `id` | bigint | NOT NULL | | | `linq_conversation_id` | bigint | NOT NULL | FK linq_conversations; unique while active | | `governed_artifact_version_id` | bigint | NOT NULL | FK governed_artifact_versions: pinned emergency policy version | | `source_message_id` | character varying | NOT NULL | unique | | `tier` | integer | NOT NULL | 1 or 2 (CHECK) | | `urgency` | character varying | NOT NULL | | | `registry_trigger` | character varying | NOT NULL | | | `detection_source` | character varying | NOT NULL | governed_registry\|legacy_static_backstop\|legacy_meta_backfill, default governed_registry | | `response_template_id` | character varying | nullable | | | `response_body` | text | nullable | AR-encrypted; must match pinned governed copy on create | | `phone_binding_digest` | character varying | NOT NULL | | | `status` | character varying | NOT NULL | enum active\|released, default active; released rows immutable | | `latched_at` | timestamp(6) | NOT NULL | | | `released_at` | timestamp(6) | nullable | | | `released_by_id` | bigint | nullable | FK users; release gated by IsolatedExecutionState actor check | | `release_reason` | text | nullable | AR-encrypted | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### linq_events Per-message audit + replay-dedupe log for Linq conversations (unique message_id blocks webhook double-delivery); stores encrypted verbatim body. - **Status:** **ACTIVE**: Created on every inbound/outbound Linq turn via LinqReplyJob (POST /api/v1/linq/inbound); read by Linq::NightlyJudge and Ops::Integration dashboard/message_line - **Spec ref:** §1.2 Onboarding Agent: 🟢 EXISTS - **Encryption:** body: Rails AR encrypts (app/models/linq_event.rb:8); nil for guard-dropped bubbles - **Notable indexes:** `unique index_linq_events_on_message_id` 15 columns: | Column | Type | Null | Note | | ---------------------------------- | ----------------- | -------- | ------------------------------------------------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `classification` | character varying | nullable | | | `created_at` | timestamp(6) | NOT NULL | | | `decision` | character varying | nullable | OPS_STAFF_DECISIONS values mark staff-authored outbound turns excluded from agent context and nightly judging | | `direction` | character varying | NOT NULL | inbound\|outbound, default inbound | | `from_state` | character varying | nullable | | | `linq_conversation_id` | bigint | NOT NULL | FK linq_conversations | | `message_id` | character varying | NOT NULL | unique: replay/dedupe key | | `payload` | jsonb | NOT NULL | default {}; red_flag_evaluation key is insert-time immutable (model validation) | | `template_id` | character varying | nullable | | | `to_state` | character varying | nullable | | | `updated_at` | timestamp(6) | NOT NULL | | | `body` | text | nullable | AR-encrypted verbatim message text | | `must_ask_question_set_version_id` | bigint | nullable | FK governed_artifact_versions; pin immutable after create | | `provider_occurred_at` | timestamp(6) | nullable | immutable after create | ### linq_media_attachments GCS-stored raw media captured from inbound Linq messages (ID photos, PDFs, video) with promotion to UploadedDocument and video-only purge retention. - **Status:** **ACTIVE**: Written by Linq::RawMediaCapture in the inbound webhook path; read by Linq::IdAutoPromote and Ops dashboard; purged by LinqRawMediaPurgeJob cron (config/recurring.yml linq_raw_media_purge '30 4 * * * America/Chicago') - **Spec ref:** §1.2 Onboarding Agent: 🟢 EXISTS - **Encryption:** none (content_sha256 is an integrity digest; payload lives in GCS) - **Notable indexes:** `unique partial idx_linq_media_on_gcs_object_generation (bucket, object_key, generation) WHERE all three NOT NULL` · `unique idx_linq_media_one_per_event_attachment (linq_event_id, attachment_index)` · `unique idx_linq_media_one_per_message_attachment (message_id, attachment_index)` · `partial idx_linq_media_purge_scan (status, created_at) WHERE purged_at IS NULL` 22 columns: | Column | Type | Null | Note | | ------------------------------- | ----------------- | -------- | ----------------------------------------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `attachment_index` | integer | NOT NULL | | | `attachment_kind` | character varying | NOT NULL | image/attachment retained under the current policy (may be patient ID); only 'video' purgeable | | `bucket` | character varying | nullable | GCS bucket; required when stored | | `byte_size` | bigint | nullable | | | `content_sha256` | character varying | nullable | | | `content_type` | character varying | nullable | | | `created_at` | timestamp(6) | NOT NULL | | | `fetch_reason` | character varying | nullable | required when status=fetch_failed | | `field` | character varying | nullable | | | `generation` | bigint | nullable | GCS object generation | | `linq_conversation_id` | bigint | NOT NULL | FK linq_conversations | | `linq_event_id` | bigint | NOT NULL | FK linq_events | | `message_id` | character varying | NOT NULL | | | `object_key` | character varying | nullable | | | `promoted_uploaded_document_id` | bigint | nullable | FK uploaded_documents: set when promoted to canonical document | | `purged_at` | timestamp(6) | nullable | | | `review_metadata` | jsonb | NOT NULL | default {} | | `source` | character varying | nullable | | | `status` | character varying | NOT NULL | stored\|fetch_failed\|purged, default stored | | `stored_at` | timestamp(6) | nullable | | | `updated_at` | timestamp(6) | NOT NULL | | ### ehr_intake_reviews Clinician review shell for an IntakeResponse (INFO #1) with clinical snapshot: modeled but never instantiated. - **Status:** **REVIEW-HOLD**: No production caller found in the audited tree; preserve pending history, production-data, dependency, migration, and approval review. - **Spec ref:** §1.3 INFO #1 (Goal, Basic Bio): 🟢 EXISTS - **Encryption:** review_note, clinical_snapshot: AR encrypts (non-deterministic) - **Notable indexes:** `UNIQUE index_ehr_intake_reviews_on_intake_response_id (intake_response_id)` 10 columns: | Column | Type | Null | Note | | -------------------- | ----------------- | -------- | ------------------------------------------------------ | | `id` | bigint | NOT NULL | | | `intake_response_id` | bigint | NOT NULL | FK intake_responses, unique | | `patient_id` | bigint | NOT NULL | FK patients | | `review_status` | character varying | NOT NULL | enum pending_review\|approved\|denied\|needs_follow_up | | `reviewed_by_id` | bigint | nullable | FK users | | `reviewed_at` | timestamp(6) | nullable | | | `review_note` | text | nullable | encrypted | | `clinical_snapshot` | text | NOT NULL | encrypted json | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### intake_responses Patient onboarding intake (INFO #1): answers JSONB, signup/portal token lifecycle, feeds PatientGraph and physician brief. - **Status:** **ACTIVE**: Written by chat signup API (app/models/intake_response.rb:15 comment re Api::V1::SignupsController; source='chat') and portal (config/routes.rb:174); read across EHR (app/services/ehr/integration.rb:4), Linq, pre-doctor intake - **Spec ref:** §1.3 INFO #1 (Goal, Basic Bio): 🟢 EXISTS - **Notable indexes:** `unique partial index_intake_responses_on_finalize_key WHERE finalize_key IS NOT NULL` · `unique partial index_intake_responses_on_signup_session_token WHERE signup_session_token IS NOT NULL` · `partial idx_intake_responses_consumed_token WHERE portal_token_consumed_at IS NOT NULL` · `partial index_intake_responses_on_source WHERE source IS NOT NULL` 15 columns: | Column | Type | Null | Note | | ------------------------------- | ----------------- | -------- | ------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `answers` | jsonb | NOT NULL | default {}; pivoted into PatientGraph attrs and brief payload | | `completed_step_index` | integer | NOT NULL | default 0 | | `consent_id` | bigint | nullable | FK consents (waiver) | | `created_at` | timestamp(6) | NOT NULL | | | `finalize_key` | character varying | nullable | idempotency key, unique when present | | `id_document_id` | bigint | nullable | FK uploaded_documents (audit/display pointer) | | `patient_program_enrollment_id` | bigint | NOT NULL | FK patient_program_enrollments | | `portal_token_consumed_at` | timestamp(6) | nullable | | | `portal_token_nonce` | character varying | nullable | | | `signup_session_token` | character varying | nullable | unique when present; draft_signup scope discriminator | | `source` | character varying | nullable | 'chat' marks chat-driven signup API intakes (SOURCE_CHAT) | | `status` | character varying | NOT NULL | enum draft\|submitted, default draft | | `transcript` | text | nullable | | | `updated_at` | timestamp(6) | NOT NULL | | ### consents Signed/sealed patient consent records (agreement packet, clinical, import, texting kinds). - **Status:** **ACTIVE**: Written by portal signing + mobile API (app/controllers/portal/completions_controller.rb, app/controllers/api/mobile/v1/consents_controller.rb) and sealed by SealTextAuthorizationJob; read by import gating (app/controllers/concerns/import_session.rb) and authorization_base_controller - **Spec ref:** §1.4 SIGN DOCS (agreement packet): 🟢 EXISTS - **Encryption:** none (signature_hash/ip_hash/user_agent_hash are digests, body stored plaintext) - **Notable indexes:** frozen f246 `UNIQUE idx_consents_one_active_ehr_clinical_kind (patient_id, kind) WHERE signed, not revoked, not superseded, kind IN (treatment, telehealth, recording, healthkit_import, whoop_import)`; the HealthKit and Whoop additions are historical and absent from current core behavior. Other frozen indexes: `partial idx_consents_stalled_seals`, `expression idx_consents_text_messaging_chat_id`, and `partial index_consents_on_revoked_at`. 23 columns: | Column | Type | Null | Note | | ----------------------- | ----------------- | -------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `body` | text | nullable | | | `created_at` | timestamp(6) | NOT NULL | | | `document_version` | character varying | nullable | | | `expires_at` | timestamp(6) | nullable | | | `ip_hash` | character varying | nullable | | | `kind` | character varying | NOT NULL | Frozen kinds include healthkit_import and whoop_import; those integrations are absent from both current refs | | `metadata` | jsonb | NOT NULL | default {}; metadata->>'chat_id' indexed for text_messaging | | `patient_id` | bigint | NOT NULL | FK patients | | `phone_binding_digest` | character varying | nullable | | | `phone_binding_version` | character varying | NOT NULL | default v1 | | `revoked_at` | timestamp(6) | nullable | | | `seal_state` | character varying | NOT NULL | enum pending/sealing/signed/failed, default pending | | `signature_hash` | character varying | nullable | | | `signed_at` | timestamp(6) | nullable | | | `status` | character varying | NOT NULL | default pending | | `updated_at` | timestamp(6) | NOT NULL | | | `user_agent_hash` | character varying | nullable | | | `version` | integer | NOT NULL | default 1 | | `encounter_id` | bigint | nullable | FK ehr_encounters | | `supersedes_id` | bigint | nullable | self-FK consents (re-consent chain) | | `signed_by_id` | bigint | nullable | FK users | | `superseded_at` | timestamp(6) | nullable | | ### patient_program_enrollments Links a patient to a program with membership subscription state (Stripe subscription lifecycle). - **Status:** **ACTIVE**: Created at signup (app/controllers/api/v1/signups_controller.rb:251), updated by Stripe webhook (app/controllers/webhooks/stripe_controller.rb:43,52), read by app/services/health_history/nudge_dispatcher.rb:43 - **Spec ref:** §1.5 Subscription Pay (platform membership): 🟢 EXISTS - **Notable indexes:** `UNIQUE (stripe_subscription_id) WHERE stripe_subscription_id IS NOT NULL` · `partial index on subscription_status WHERE NOT NULL` 10 columns: | Column | Type | Null | Note | | ------------------------ | ----------------- | -------- | ---------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `created_at` | timestamp(6) | NOT NULL | | | `enrolled_at` | timestamp(6) | nullable | | | `patient_id` | bigint | NOT NULL | FK patients | | `program_id` | bigint | NOT NULL | FK programs | | `referring_doctor_id` | bigint | nullable | FK users | | `status` | character varying | NOT NULL | default 'draft'; enum draft\|enrolled\|active\|paused\|completed | | `stripe_subscription_id` | character varying | nullable | | | `subscription_status` | character varying | nullable | raw Stripe subscription status | | `updated_at` | timestamp(6) | NOT NULL | | --- ## §2: Doctor Matching & Scheduling (14 tables) Provider-side tables the census filed under §2 (`provider_invites`, `provider_agreement_acceptances`) now live in **§11 Provider Onboarding**. ### appointments Booked doctor visits: slot, times, status lifecycle (booked/cancelled/completed), visit token for the Meet join URL. - **Status:** **ACTIVE**: Created by Onboarding::BookAppointment (called from app/controllers/portal/completions_controller.rb booking flow and ops/conversation_actions_controller.rb); read by app/controllers/appointments/visits_controller.rb, AppointmentMeetRoomJob, reminder jobs - **Spec ref:** §2.2 Appointment Setting: 🟡 PARTIAL - **Notable indexes:** `UNIQUE partial idx_appointments_one_booked_per_enrollment (patient_program_enrollment_id) WHERE status='booked'` · `UNIQUE partial idx_appointments_one_booked_per_slot (slot_id) WHERE status='booked'` · `UNIQUE index_appointments_on_visit_token (visit_token)` 16 columns: | Column | Type | Null | Note | | ------------------------------- | ----------------- | -------- | ---------------------------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `patient_program_enrollment_id` | bigint | NOT NULL | FK -> patient_program_enrollments; one booked appt per enrollment (partial unique) | | `patient_id` | bigint | NOT NULL | FK -> patients | | `slot_id` | character varying | NOT NULL | one booked appt per slot (partial unique) | | `starts_at` | timestamp(6) | NOT NULL | | | `ends_at` | timestamp(6) | NOT NULL | | | `doctor_name` | character varying | nullable | | | `doctor_slug` | character varying | nullable | | | `timezone` | character varying | nullable | | | `status` | character varying | NOT NULL | enum: booked, cancelled, completed; default booked | | `source` | character varying | nullable | | | `metadata` | jsonb | NOT NULL | holds label, schedule_revision | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | | `visit_token` | character varying | NOT NULL | unique 22-char alphanumeric join-URL token (no -/_ so SendGuard PHI patterns never trip) | | `doctor_id` | bigint | nullable | FK -> users (staff doctor); nullable for legacy/doctorless clinics | ### doctor_calendar_feeds Revocable/rotatable token for a doctor's private ICS calendar feed. - **Status:** **DORMANT**: Prod endpoint reads existing rows (config/routes.rb:214 -> app/controllers/doctor/calendar_feeds_controller.rb:15-20 authenticate); rows console-minted only via DoctorCalendarFeed.for_doctor (app/models/doctor_calendar_feed.rb:26-27, no production caller): feed provisioning not wired in prod (one doctor at launch) - **Spec ref:** §2.2 Appointment Setting: 🟡 PARTIAL - **Encryption:** none (token stored plaintext, ~190-bit random) - **Notable indexes:** `UNIQUE index_doctor_calendar_feeds_on_token (token)` 7 columns: | Column | Type | Null | Note | | ------------- | ----------------- | -------- | ----------------------------------------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `doctor_slug` | character varying | NOT NULL | aliased via APPOINTMENT_SLUGS_BY_FEED_SLUG (dr-reiner covers legacy dr-amelia-voss, dr-payton-reiter) | | `doctor_name` | character varying | nullable | | | `token` | character varying | NOT NULL | unique credential, revocable/rotatable | | `revoked_at` | timestamp(6) | nullable | | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### ehr_contraindication_answers Provenance-checked yes/no/unclear answers to pinned contraindication screening questions, pinned to a governed question-set version. - **Status:** **ACTIVE**: Written via Ehr::PreDoctorIntakes::RecordAnswer from patient/pre_doctor_intakes_controller.rb:57 and linq/pre_doctor_intake_router.rb:104 (webhook); read by ehr/pre_doctor_intakes/evidence_collector.rb:233 and integration/contraindication_answers.rb:287 - **Spec ref:** §2.3 INFO #2 (pre-doctor intake): 🟡 PARTIAL - **Encryption:** item_id, answer, detail, answered_at, provenance: AR encrypts (non-deterministic); item_lookup_digests is the plaintext lookup shadow (GIN-indexed digests) - **CHECK constraints:** `idempotency_digest ~ ^[0-9a-f]{64}$` · `idempotency_key_version = 1` · `item_lookup_digests valid per ehr_valid_item_lookup_digests()` · `source in (agent_chat, call_transcript, doctor_entry, portal)` · `per-source provenance coherence: doctor_entry requires recorded_by + clinician_verified; call_transcript requires recorded_by + encounter_id + source_artifact + patient_reported; agent_chat requires linq_event + patient_reported; portal requires pre_doctor_intake + recorded_by + patient_reported` · `verification in (patient_reported, clinician_verified)` - **Notable indexes:** `UNIQUE idx_ehr_contra_answers_idempotency (idempotency_digest)` · `GIN idx_ehr_contra_answers_item_lookup (item_lookup_digests)` · `idx_ehr_contra_answers_patient_item_history (patient_id, created_at, id)` 20 columns: | Column | Type | Null | Note | | ------------------------------ | --------------------- | -------- | ------------------------------------------------------ | | `id` | bigint | NOT NULL | | | `patient_id` | bigint | NOT NULL | FK patients | | `encounter_id` | bigint | nullable | FK ehr_encounters; required for call_transcript source | | `recorded_by_id` | bigint | nullable | FK users | | `governed_artifact_version_id` | bigint | NOT NULL | pinned question-set version | | `source_artifact_id` | bigint | nullable | FK artifacts (call transcript) | | `linq_event_id` | bigint | nullable | FK linq_events (agent_chat source) | | `pre_doctor_intake_id` | bigint | nullable | FK ehr_pre_doctor_intakes (portal source) | | `item_id` | text | NOT NULL | encrypted; question id within pinned set | | `item_lookup_digests` | text[] | NOT NULL | plaintext digest shadow for encrypted item_id lookup | | `answer` | text | NOT NULL | encrypted; yes\|no\|unclear | | `source` | character varying | NOT NULL | enum agent_chat\|call_transcript\|doctor_entry\|portal | | `verification` | character varying | NOT NULL | enum patient_reported\|clinician_verified | | `detail` | text | nullable | encrypted | | `answered_at` | text | NOT NULL | encrypted datetime attribute | | `provenance` | text | NOT NULL | encrypted json | | `idempotency_digest` | character varying(64) | NOT NULL | | | `idempotency_key_version` | integer | NOT NULL | pinned = 1 by CHECK | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### ehr_pre_doctor_intake_answers Append-only event log of every answer received during pre-doctor intake (SMS/portal/etc.), with idempotency and correction chaining. - **Status:** **ACTIVE**: Written by Ehr::PreDoctorIntakes::RecordAnswer / RecordPortalSection reached from api/v1/linq_controller.rb webhook, api/v1/intake_chat_controller.rb, and patient/pre_doctor_intakes_controller.rb:28; read for packet building and manual takeover - **Spec ref:** §2.3 INFO #2 (pre-doctor intake): 🟡 PARTIAL - **Encryption:** AR encrypts: question_id, response, normalized_answer, source_reference, unresolved_reason_code; DB CHECK chk_pre_doctor_answers_encrypted enforces the encrypted envelope on all five - **CHECK constraints:** `chk_pre_doctor_answers_encrypted: question_id/response/normalized_answer/source_reference/unresolved_reason_code must be ActiveRecord-encrypted envelopes (ehr_active_record_encrypted_envelope)` - **Notable indexes:** `unique idempotency_digest` · `unique partial contraindication_answer_id WHERE NOT NULL` · `history index (pre_doctor_intake_id, created_at, id)` · `triggers: append-only + truncate guard (ehr_pre_doctor_immutable_rows), insert authority guard (ehr_pre_doctor_child_authority_guard)` 19 columns: | Column | Type | Null | Note | | ---------------------------- | --------------------- | -------- | ---------------------------------------------------------------------------------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `pre_doctor_intake_id` | bigint | NOT NULL | FK ehr_pre_doctor_intakes | | `plan_revision_id` | bigint | NOT NULL | FK ehr_pre_doctor_intake_plan_revisions | | `prompt_id` | bigint | nullable | FK ehr_pre_doctor_intake_prompts | | `linq_event_id` | bigint | nullable | FK linq_events | | `contraindication_answer_id` | bigint | nullable | FK ehr_contraindication_answers; unique when present | | `corrects_answer_id` | bigint | nullable | self-FK for corrections | | `recorded_by_id` | bigint | nullable | FK users | | `question_id` | character varying | NOT NULL | encrypted | | `response` | jsonb | NOT NULL | encrypted envelope stored in jsonb, default {} | | `normalized_answer` | character varying | nullable | encrypted | | `source_kind` | character varying | NOT NULL | enum: linq_event\|portal\|id_document\|chart\|scale\|agent_chat\|call_transcript\|clinician_entry\|evidence_resolution\|staff_handoff\|derived | | `source_reference` | character varying | nullable | encrypted | | `source_occurred_at` | timestamp(6) | NOT NULL | | | `received_at` | timestamp(6) | NOT NULL | | | `outcome` | character varying | NOT NULL | enum: valid\|invalid\|unresolved\|handed_to_staff | | `unresolved_reason_code` | character varying | nullable | encrypted; 15-value inclusion list in model | | `idempotency_digest` | character varying(64) | NOT NULL | unique SHA-256 hex | | `created_at` | timestamp(6) | NOT NULL | no updated_at: append-only | ### ehr_pre_doctor_intake_packets Immutable clinician-review packet snapshots compiled from an intake at a given plan revision. - **Status:** **ACTIVE**: Created by Ehr::PreDoctorIntakes::PacketBuilder (app/services/ehr/pre_doctor_intakes/packet_builder.rb:68, reached from RecordAnswer completion via linq/intake-chat/portal controllers, plus ReconcileMissingPackets); read by ehr/encounters_controller.rb via PreDoctorIntakePacketPresenter and Ehr::Integration::PreCallBrief - **Spec ref:** §2.3 INFO #2 (pre-doctor intake): 🟡 PARTIAL - **Encryption:** AR encrypts: payload; DB CHECK chk_pre_doctor_packet_payload_encrypted enforces envelope - **CHECK constraints:** `chk_pre_doctor_packet_payload_encrypted: payload must be an ActiveRecord-encrypted envelope` - **Notable indexes:** `unique (pre_doctor_intake_id, packet_revision)` · `unique (pre_doctor_intake_id, source_revision_number)` · `triggers: append-only + truncate guard, insert authority guard` 11 columns: | Column | Type | Null | Note | | ------------------------------ | --------------------- | -------- | ------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `pre_doctor_intake_id` | bigint | NOT NULL | FK ehr_pre_doctor_intakes | | `plan_revision_id` | bigint | NOT NULL | FK ehr_pre_doctor_intake_plan_revisions | | `governed_artifact_version_id` | bigint | NOT NULL | FK governed_artifact_versions (question-set pin) | | `packet_revision` | integer | NOT NULL | | | `source_revision_number` | integer | NOT NULL | | | `plan_fingerprint` | character varying(64) | NOT NULL | SHA-256 hex | | `status` | character varying | NOT NULL | only value allowed by model: ready_for_clinician_review | | `payload` | jsonb | NOT NULL | encrypted packet body | | `payload_digest` | character varying(64) | NOT NULL | | | `created_at` | timestamp(6) | NOT NULL | no updated_at: append-only | ### ehr_pre_doctor_intake_plan_revisions Immutable snapshots of each planner output (question plan) for an intake, numbered per intake. - **Status:** **ACTIVE**: Created during Ehr::PreDoctorIntakes::StartForPatient / StartOrResume and replans inside RecordAnswer (app/services/ehr/pre_doctor_intakes/record_answer.rb:342 creates prompts against new revisions) reached via linq webhook / intake chat / portal controllers; FK anchor read by answers, prompts, packets - **Spec ref:** §2.3 INFO #2 (pre-doctor intake): 🟡 PARTIAL - **Encryption:** AR encrypts: plan_snapshot; DB CHECK chk_pre_doctor_plan_snapshot_encrypted enforces envelope - **CHECK constraints:** `chk_pre_doctor_plan_snapshot_encrypted: plan_snapshot must be an ActiveRecord-encrypted envelope` - **Notable indexes:** `unique (pre_doctor_intake_id, revision_number)` · `history index (pre_doctor_intake_id, created_at, id)` · `triggers: append-only + truncate guard, insert authority guard` 9 columns: | Column | Type | Null | Note | | ------------------------------ | --------------------- | -------- | ----------------------------------------- | | `id` | bigint | NOT NULL | | | `pre_doctor_intake_id` | bigint | NOT NULL | FK ehr_pre_doctor_intakes | | `governed_artifact_version_id` | bigint | NOT NULL | FK governed_artifact_versions | | `source_linq_event_id` | bigint | nullable | FK linq_events (triggering inbound event) | | `revision_number` | integer | NOT NULL | unique per intake | | `planner_ruleset_version` | integer | NOT NULL | | | `plan_fingerprint` | character varying(64) | NOT NULL | | | `plan_snapshot` | jsonb | NOT NULL | encrypted | | `created_at` | timestamp(6) | NOT NULL | no updated_at: append-only | ### ehr_pre_doctor_intake_prompts Outbound question prompts planned/sent to the patient during SMS intake, one active at a time. - **Status:** **ACTIVE**: Created by Ehr::PreDoctorIntakes::PromptFactory (app/services/ehr/pre_doctor_intakes/prompt_factory.rb:17) from LinqReplyJob:739, Linq::PreDoctorIntakeRouter, RecordAnswer, StartForPatient: all reachable from the api/v1/linq_controller.rb webhook and intake chat; answers link back via prompt_id - **Spec ref:** §2.3 INFO #2 (pre-doctor intake): 🟡 PARTIAL - **Encryption:** AR encrypts: question_id; DB CHECK chk_pre_doctor_prompt_question_encrypted enforces envelope - **CHECK constraints:** `chk_pre_doctor_prompt_question_encrypted: question_id must be an ActiveRecord-encrypted envelope` - **Notable indexes:** `unique idempotency_digest` · `unique partial (pre_doctor_intake_id) WHERE status IN (planned, sent): one active prompt per intake` · `trigger: insert/update authority guard (ehr_pre_doctor_child_authority_guard)` 13 columns: | Column | Type | Null | Note | | ------------------------ | --------------------- | -------- | --------------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `pre_doctor_intake_id` | bigint | NOT NULL | FK ehr_pre_doctor_intakes | | `plan_revision_id` | bigint | NOT NULL | FK ehr_pre_doctor_intake_plan_revisions; validated to belong to same intake | | `outbound_linq_event_id` | bigint | nullable | FK linq_events (sent SMS) | | `question_id` | character varying | NOT NULL | encrypted | | `attempt_number` | integer | NOT NULL | > 0 | | `prompt_digest` | character varying(64) | NOT NULL | | | `idempotency_digest` | character varying(64) | NOT NULL | unique | | `status` | character varying | NOT NULL | enum: planned\|sent\|failed\|cancelled\|answered (default planned) | | `planned_at` | timestamp(6) | NOT NULL | | | `sent_at` | timestamp(6) | nullable | | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### ehr_pre_doctor_intake_section_submissions Append-only idempotency ledger for patient-portal section (A/B/C) submissions of the pre-doctor intake. - **Status:** **ACTIVE**: Written by Ehr::PreDoctorIntakes::RecordPortalSection (subclass of RecordAnswer) called from Patient::PreDoctorIntakesController#create (app/controllers/patient/pre_doctor_intakes_controller.rb:28); read for duplicate-submission replay via the unique idempotency index - **Spec ref:** §2.3 INFO #2 (pre-doctor intake): 🟡 PARTIAL - **Encryption:** none (digests + result_snapshot; no encrypts in app/models/ehr/pre_doctor_intake_section_submission.rb) - **CHECK constraints:** `chk_pre_doctor_section_submissions_digests: idempotency_key_digest and canonical_payload_digest match ^[0-9a-f]{64}$` · `chk_pre_doctor_section_submissions_section: section IN ('A','B','C')` - **Notable indexes:** `unique (pre_doctor_intake_id, section, recorded_by_id, idempotency_key_digest)` · `triggers: append-only + truncate guard (ehr_pre_doctor_immutable_rows)` 8 columns: | Column | Type | Null | Note | | -------------------------- | --------------------- | -------- | --------------------------------------------------- | | `id` | bigint | NOT NULL | | | `pre_doctor_intake_id` | bigint | NOT NULL | FK ehr_pre_doctor_intakes | | `recorded_by_id` | bigint | NOT NULL | FK users (portal patient user) | | `section` | character varying | NOT NULL | A\|B\|C | | `idempotency_key_digest` | character varying(64) | NOT NULL | | | `canonical_payload_digest` | character varying(64) | NOT NULL | | | `result_snapshot` | jsonb | NOT NULL | replayed response for duplicate submits, default {} | | `created_at` | timestamp(6) | NOT NULL | no updated_at: append-only | ### ehr_pre_doctor_intakes Root aggregate of the INFO #2 pre-doctor intake: status machine, pinned question-set authority, cursor, and current plan/packet pointers. - **Status:** **ACTIVE**: Created/updated by Ehr::PreDoctorIntakes::StartOrResume (SMS via Linq::PreDoctorIntakeRouter <- api/v1/linq_controller.rb webhook + app/jobs/linq_reply_job.rb) and StartForPatient (portal / api/v1/intake_chat_controller.rb); read by the EHR appointment Pre-Brief and PreDoctorIntakeReminders - **Spec ref:** §2.3 INFO #2 (pre-doctor intake): 🟡 PARTIAL - **Encryption:** AR encrypts: planning_inputs, cursor_question_id, unresolved_reason_codes; DB CHECKs enforce envelopes - **CHECK constraints:** `chk_pre_doctor_intakes_cursor_encrypted: cursor_question_id NULL or encrypted envelope` · `chk_pre_doctor_intakes_planning_inputs_encrypted: planning_inputs encrypted envelope` · `chk_pre_doctor_intakes_reasons_encrypted: unresolved_reason_codes encrypted envelope` · `chk_pre_doctor_intakes_origin_conversation: origin='sms' IFF linq_conversation_id IS NOT NULL` - **Notable indexes:** `unique partial linq_conversation_id WHERE status IN (collecting, manual_review, ready_for_clinician_review): one active intake per conversation` · `unique partial intake_response_id under the same active statuses` · `(patient_id, status, updated_at)` · `trigger ehr_pre_doctor_intakes_authority_guard on INSERT/UPDATE (ehr_pre_doctor_intake_authority_guard)` 25 columns: | Column | Type | Null | Note | | ------------------------------ | --------------------- | -------- | ------------------------------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `patient_id` | bigint | NOT NULL | FK patients | | `intake_response_id` | bigint | NOT NULL | FK intake_responses; unique while active | | `linq_conversation_id` | bigint | nullable | FK linq_conversations; required iff origin=sms | | `governed_artifact_version_id` | bigint | NOT NULL | FK governed_artifact_versions: pinned question-set authority, immutable after create | | `formula_version_id` | bigint | nullable | FK formula_versions | | `status` | character varying | NOT NULL | enum: collecting\|manual_review\|ready_for_clinician_review\|abandoned (default collecting) | | `activation_mode` | character varying | NOT NULL | enum: synthetic_rehearsal\|real_patient | | `planner_ruleset_version` | integer | NOT NULL | default 1 | | `plan_fingerprint` | character varying(64) | NOT NULL | | | `cursor_question_id` | character varying | nullable | encrypted; current question pointer | | `selected_tracks` | jsonb | NOT NULL | default []; presence-validated | | `planning_inputs` | jsonb | NOT NULL | encrypted | | `progress` | jsonb | NOT NULL | default {} | | `unresolved_reason_codes` | jsonb | NOT NULL | encrypted | | `revision_number` | integer | NOT NULL | default 1 | | `lock_version` | integer | NOT NULL | optimistic locking | | `started_at` | timestamp(6) | NOT NULL | | | `ready_at` | timestamp(6) | nullable | | | `abandoned_at` | timestamp(6) | nullable | | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | | `current_plan_revision_id` | bigint | nullable | FK ehr_pre_doctor_intake_plan_revisions | | `current_packet_id` | bigint | nullable | FK ehr_pre_doctor_intake_packets | | `origin` | character varying | NOT NULL | enum: sms\|portal (default sms); added after timestamps | ### pre_doctor_intake_reminders Ledger of pre-doctor intake (INFO #2) nudge sends per appointment, with dedupe key and dry_run/send delivery mode. - **Status:** **ACTIVE**: Written by PreDoctorIntakeReminders::Notifier (app/services/pre_doctor_intake_reminders/notifier.rb:42) via Ops::Commands::PreDoctorIntakeReminder gateway (ops command surface); read by app/jobs/linq_reply_job.rb:793 and eligibility.rb:89 - **Spec ref:** §2.3 INFO #2 (pre-doctor intake): 🟡 PARTIAL - **CHECK constraints:** `delivery_mode IN ('dry_run','send')` · `nudge_number > 0` - **Notable indexes:** `UNIQUE (dedupe_key)` · `UNIQUE (appointment_id, scheduled_start_at, nudge_number)` 14 columns: | Column | Type | Null | Note | | -------------------------- | ----------------- | -------- | --------------------------------- | | `id` | bigint | NOT NULL | | | `appointment_id` | bigint | NOT NULL | FK appointments | | `patient_id` | bigint | NOT NULL | FK patients (restrict_with_error) | | `ehr_pre_doctor_intake_id` | bigint | nullable | FK ehr_pre_doctor_intakes | | `linq_conversation_id` | bigint | NOT NULL | FK linq_conversations | | `requested_by_id` | bigint | NOT NULL | FK users (actor) | | `nudge_number` | integer | NOT NULL | CHECK > 0 | | `scheduled_start_at` | timestamp(6) | NOT NULL | | | `dedupe_key` | character varying | NOT NULL | unique | | `delivery_mode` | character varying | NOT NULL | CHECK dry_run\|send | | `dispatched_at` | timestamp(6) | NOT NULL | | | `enqueued_at` | timestamp(6) | nullable | | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### web_id_uploads State machine for web/portal (and Linq) ID-photo upload attempts tied to an intake response, with purge bookkeeping. - **Status:** **ACTIVE**: Written by portal/id_uploads_controller.rb and api/v1/linq_controller.rb via Identity::AcceptIdPhoto; processed by WebIdOcrJob; WebIdUploadPurgeJob and WebIdUploadRecoveryJob are scheduled in config/recurring.yml. - **Spec ref:** §2.3 INFO #2 (pre-doctor intake): 🟡 PARTIAL - **Note:** Also the code substrate of **§1.7 ID Verification: 🟡 PARTIAL** (the portal wizard/OCR path is front-only, but the Linq intake path can still store a second image as the license back; visit-time provider acknowledgment remains advisory rather than blocking). - **CHECK constraints:** `web_id_uploads_attempt_positive: attempt_no > 0` · `web_id_uploads_failed_reason: failed state requires reason` · `web_id_uploads_needs_review_document: needs_review requires document_id and reason` · `web_id_uploads_origin_valid: origin = 'portal' (only value allowed)` · `web_id_uploads_state_valid: state in (pending, verified, failed, needs_review)` · `web_id_uploads_verified_document: verified requires document_id` - **Notable indexes:** `UNIQUE partial idx_web_id_uploads_wizard_intake_attempt (intake_response_id, attempt_no) WHERE linq_conversation_id IS NULL` · `UNIQUE partial idx_web_id_uploads_wizard_intake_sha256 (intake_response_id, content_sha256) WHERE linq NULL AND not terminal-failed states/reasons` · `UNIQUE index_web_id_uploads_on_conversation_and_attempt (linq_conversation_id, attempt_no)` · `UNIQUE partial index_web_id_uploads_on_conversation_and_sha256 with same failed-state carve-out` 14 columns: | Column | Type | Null | Note | | ---------------------- | ----------------- | -------- | ------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `intake_response_id` | bigint | NOT NULL | FK intake_responses | | `state` | character varying | NOT NULL | pending\|verified\|failed\|needs_review; default pending | | `reason` | character varying | nullable | failure/review reason, e.g. storage_failed, processing_failed | | `attempt_no` | integer | NOT NULL | | | `content_sha256` | character varying | NOT NULL | dedupe key per intake/conversation | | `origin` | character varying | NOT NULL | CHECK-locked to 'portal' | | `document_id` | bigint | nullable | FK uploaded_documents | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | | `purge_object_key` | character varying | nullable | GCS object queued for purge job | | `purge_generation` | bigint | nullable | | | `purge_document_id` | bigint | nullable | | | `linq_conversation_id` | bigint | nullable | FK linq_conversations; NULL = portal wizard path | ### appointment_messages Per-appointment outbound-message ledger (confirmation, T-24h, T-1h reminders) that dedupes sends even in dry_run/log-only SendGuard mode. - **Status:** **ACTIVE**: Written by Appointments::Notifier from app/jobs/appointment_confirmation_job.rb:12 and Appointments::ReminderDispatcher via AppointmentReminderJob scheduled in config/recurring.yml:100-101; unique indexes are the anti-double-text floor - **Spec ref:** §2.4 Meeting Reminders: 🟡 PARTIAL - **Notable indexes:** `UNIQUE idx_appointment_messages_one_per_kind_time (appointment_id, kind, scheduled_start_at)` · `UNIQUE index_appointment_messages_on_dedupe_key (dedupe_key)` 10 columns: | Column | Type | Null | Note | | -------------------- | ----------------- | -------- | ------------------------------------------------------ | | `id` | bigint | NOT NULL | | | `appointment_id` | bigint | NOT NULL | FK -> appointments | | `kind` | character varying | NOT NULL | enum: confirmation, reminder_24h, reminder_1h | | `dedupe_key` | character varying | NOT NULL | unique; also used as LinqEvent.message_id in send mode | | `delivery_mode` | character varying | NOT NULL | default 'dry_run' | | `dispatched_at` | timestamp(6) | NOT NULL | | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | | `enqueued_at` | timestamp(6) | nullable | | | `scheduled_start_at` | timestamp(6) | NOT NULL | truncated to whole seconds before validation | ### ehr_video_sessions Telehealth video session per appointment chart record (`ehr_encounters`) with recording/transcript refs (Google Meet conference records). - **Status:** **ACTIVE**: Created by Ehr::Workflows::IngestVisitTranscript:156 via MeetVisitTranscriptIngestJob enqueued from Appointments::MeetSweep (cron: config/recurring.yml appointment_meet_sweep / appointment_meet_transcript_sweep); read by visit_transcript facade, note drafting, transcript provenance - **Spec ref:** §2.6 Dr. Appointment (MEET): 🟢 EXISTS - **Notable indexes:** `unique partial on external_ref WHERE external_ref IS NOT NULL` · `partial idx_ehr_video_sessions_ready_transcript on (updated_at, encounter_id) WHERE transcript_ref present and non-empty` 12 columns: | Column | Type | Null | Note | | ---------------- | ----------------- | -------- | ------------------------------------------------------ | | `id` | bigint | NOT NULL | | | `encounter_id` | bigint | NOT NULL | FK ehr_encounters | | `vendor` | character varying | NOT NULL | default 'linq' | | `external_ref` | character varying | nullable | Meet conference record name, unique when present | | `status` | character varying | NOT NULL | scheduled\|started\|ended\|failed, default 'scheduled' | | `started_at` | timestamp(6) | nullable | | | `ended_at` | timestamp(6) | nullable | | | `recording_ref` | character varying | nullable | | | `transcript_ref` | character varying | nullable | | | `failure_reason` | character varying | nullable | | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### meet_spaces Google Meet space lifecycle per appointment: provisioning, join/conference telemetry, transcript ingest, access lockdown. - **Status:** **ACTIVE**: Written by Appointments::MeetRoom.ensure! via AppointmentMeetRoomJob (enqueued in app/services/onboarding/book_appointment.rb:69); swept by AppointmentMeetSweepJob / AppointmentMeetTranscriptSweepJob scheduled in config/recurring.yml:104-111; read by app/jobs/meet_visit_transcript_ingest_job.rb. - **Spec ref:** §2.6 Dr. Appointment (MEET): 🟢 EXISTS - **Notable indexes:** `UNIQUE (appointment_id): one space per appointment` · `UNIQUE partial (space_name) WHERE space_name IS NOT NULL` 18 columns: | Column | Type | Null | Note | | ------------------------------- | ----------------- | -------- | ---------------------------------------------------- | | `id` | bigint | NOT NULL | | | `appointment_id` | bigint | NOT NULL | FK appointments, 1:1 | | `space_name` | character varying | nullable | Google Meet space resource name, unique when present | | `meeting_uri` | character varying | nullable | | | `meeting_code` | character varying | nullable | | | `status` | character varying | NOT NULL | enum pending\|active\|failed, default pending | | `failure_reason` | text | nullable | | | `subscription_name` | character varying | nullable | Google Workspace Events subscription | | `subscription_expires_at` | timestamp(6) | nullable | | | `doctor_joined_at` | timestamp(6) | nullable | | | `patient_joined_at` | timestamp(6) | nullable | | | `conference_started_at` | timestamp(6) | nullable | | | `conference_ended_at` | timestamp(6) | nullable | | | `active_conference_record` | character varying | nullable | | | `transcript_ingest_enqueued_at` | timestamp(6) | nullable | | | `access_locked_at` | timestamp(6) | nullable | | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | --- ## §3: EHR & Prescribing (48 tables) Provider credentialing tables the census filed under §3.2 (`ehr_provider_credentials`, `ehr_provider_state_licenses`) now live in **§11 Provider Onboarding**. ### ehr_allergies Patient allergy/intolerance list incl. NKDA flag, for chart + safety screening. - **Status:** **ACTIVE**: Written by chart UI and records import (app/controllers/ehr/charts_controller.rb, app/services/records/core_chart_importer.rb); read by safety screens (app/services/ehr/safety_screens/evaluator.rb) and clinical summaries - **Spec ref:** §3.1 CHARTING EHR: 🟡 PARTIAL - **Encryption:** substance, reaction (AR-encrypted); substance_key (AR deterministic: queryable) - **Notable indexes:** `UNIQUE idx_ehr_allergies_import_fingerprint (patient_id, import_fingerprint) WHERE import_fingerprint IS NOT NULL: import idempotency` · `UNIQUE idx_ehr_allergies_one_nkda_per_patient (patient_id) WHERE nkda = true` 14 columns: | Column | Type | Null | Note | | -------------------- | ----------------- | -------- | -------------------------------------------------- | | `id` | bigint | NOT NULL | | | `patient_id` | bigint | NOT NULL | FK patients | | `substance` | text | NOT NULL | AR-encrypted | | `substance_key` | text | NOT NULL | AR deterministic encryption, normalized lookup key | | `reaction` | text | nullable | AR-encrypted | | `severity` | character varying | NOT NULL | enum mild/moderate/severe, default moderate | | `verification` | character varying | NOT NULL | enum confirmed/unconfirmed, default unconfirmed | | `kind` | character varying | NOT NULL | enum allergy/intolerance, default allergy | | `nkda` | boolean | NOT NULL | one true row per patient (partial unique index) | | `source` | character varying | NOT NULL | default patient_reported | | `recorded_by_id` | bigint | nullable | FK users | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | | `import_fingerprint` | character varying | nullable | records-import dedupe | ### ehr_amendment_requests HIPAA-style chart amendment request with review decision and resulting note addendum. - **Status:** **ACTIVE**: Written via app/controllers/ehr/amendment_requests_controller.rb -> Ehr::Workflows::SubmitAmendmentRequest; read in chart view (app/controllers/ehr/charts_controller.rb:130) - **Spec ref:** §3.1 CHARTING EHR: 🟡 PARTIAL - **Encryption:** request_body, decision_reason, addendum_body, request_metadata (AR-encrypted, app/models/ehr/amendment_request.rb:14-17) 15 columns: | Column | Type | Null | Note | | ------------------ | ----------------- | -------- | ------------------------------------------------- | | `id` | bigint | NOT NULL | | | `patient_id` | bigint | NOT NULL | FK patients | | `encounter_id` | bigint | nullable | FK ehr_encounters; validated to belong to patient | | `requested_by_id` | bigint | NOT NULL | FK users | | `reviewed_by_id` | bigint | nullable | FK users | | `note_addendum_id` | bigint | nullable | FK ehr_note_addenda | | `status` | character varying | NOT NULL | default pending | | `request_body` | text | NOT NULL | AR-encrypted | | `requested_at` | timestamp(6) | NOT NULL | | | `decision_at` | timestamp(6) | nullable | | | `decision_reason` | text | nullable | AR-encrypted | | `addendum_body` | text | nullable | AR-encrypted | | `request_metadata` | text | NOT NULL | AR-encrypted | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### ehr_assessments Standardized-instrument assessment scores (imported or clinician-recorded) on the chart. - **Status:** **DORMANT**: Chart read surface exists (app/controllers/ehr/charts_controller.rb:77), but rows arrive only via ops rake records:import_extract (app/services/records/extract_importer.rb via lib/tasks/records.rake:109): rake-only writer, no production write path; same lane as ehr_observations/ehr_procedures - **Spec ref:** §3.1 CHARTING EHR: 🟡 PARTIAL - **Encryption:** administered_on, total_score, score_interpretation, item_scores (AR-encrypted, app/models/ehr/assessment.rb:21-24) - **Notable indexes:** `UNIQUE idx_ehr_assessments_import_fingerprint (patient_id, import_fingerprint) WHERE import_fingerprint IS NOT NULL` 15 columns: | Column | Type | Null | Note | | ---------------------- | ----------------- | -------- | ------------------------------------------ | | `id` | bigint | NOT NULL | | | `patient_id` | bigint | NOT NULL | FK patients | | `recorded_by_id` | bigint | nullable | FK users | | `instrument` | character varying | NOT NULL | | | `instrument_version` | character varying | nullable | | | `date_precision` | character varying | NOT NULL | enum (date/...), default date | | `source` | character varying | NOT NULL | enum clinician/imported, default imported | | `clinical_date` | date | nullable | | | `import_fingerprint` | character varying | nullable | records-import dedupe | | `administered_on` | text | nullable | AR-encrypted (date kept as encrypted text) | | `total_score` | text | nullable | AR-encrypted | | `score_interpretation` | text | nullable | AR-encrypted | | `item_scores` | text | nullable | AR-encrypted | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### ehr_care_team_memberships Which users (clinicians) are on a patient's care team: drives chart access policy. - **Status:** **ACTIVE**: Written by app/services/records/core_chart_bootstrapper.rb and ehr/workflows/open_encounter.rb; read by app/services/ehr/access/chart_access_policy.rb and doctor case visibility (app/controllers/concerns/doctor/case_visibility.rb) - **Spec ref:** §3.1 CHARTING EHR: 🟡 PARTIAL - **Notable indexes:** `UNIQUE idx_ehr_care_team_one_active_user_patient (patient_id, user_id) WHERE status = 'active'` 11 columns: | Column | Type | Null | Note | | --------------- | ----------------- | -------- | -------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `patient_id` | bigint | NOT NULL | FK patients | | `user_id` | bigint | NOT NULL | FK users | | `clinic_id` | bigint | NOT NULL | FK clinics | | `role` | character varying | NOT NULL | default clinician | | `status` | character varying | NOT NULL | default active; one active membership per (patient,user) | | `starts_at` | timestamp(6) | nullable | | | `ends_at` | timestamp(6) | nullable | | | `created_by_id` | bigint | nullable | FK users | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### ehr_chart_access_logs Audit trail of who accessed which chart records (incl. break-glass), HIPAA access logging. - **Status:** **ACTIVE**: Written on every audited patient read via app/controllers/concerns/audits_patient_reads.rb and chart_access_policy break-glass; read by app/controllers/admin/audit_controller.rb and ehr/break_glass_alert_job.rb - **Spec ref:** §3.1 CHARTING EHR: 🟡 PARTIAL - **Notable indexes:** `idx_on_actor_type_actor_id_occurred_at_756a33c303 (actor_type, actor_id, occurred_at)` · `idx_on_target_type_target_id_occurred_at_c7decf57b2 (target_type, target_id, occurred_at)` 11 columns: | Column | Type | Null | Note | | ------------- | ----------------- | -------- | ------------------------- | | `id` | bigint | NOT NULL | | | `actor_type` | character varying | NOT NULL | polymorphic actor | | `actor_id` | bigint | NOT NULL | | | `target_type` | character varying | NOT NULL | polymorphic target | | `target_id` | bigint | NOT NULL | | | `action` | character varying | NOT NULL | | | `request_id` | character varying | nullable | | | `reason` | character varying | nullable | break-glass justification | | `occurred_at` | timestamp(6) | NOT NULL | | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### ehr_clinical_notes One clinical note per appointment chart record (SOAP + HPI/MDM fields), AI-drafted then doctor-edited. - **Status:** **ACTIVE**: Written by Ehr::Workflows::DocumentClinicalNote from app/controllers/ehr/encounters_controller.rb:115; read in chart view (app/controllers/ehr/charts_controller.rb:44) and records/core_chart_importer.rb - **Spec ref:** §3.1 CHARTING EHR: 🟡 PARTIAL - **Encryption:** subjective, objective, assessment, plan, hpi, ros, exam, medical_decision_making, risks_benefits, instructions, follow_up, doctor_addendum (AR-encrypted, app/models/ehr/clinical_note.rb:20-31) - **Notable indexes:** `UNIQUE index_ehr_clinical_notes_on_encounter_id: one note per appointment chart record` 20 columns: | Column | Type | Null | Note | | ------------------------- | ----------------- | -------- | ----------------------------------------- | | `id` | bigint | NOT NULL | | | `encounter_id` | bigint | NOT NULL | FK ehr_encounters, unique | | `note_type` | character varying | NOT NULL | default hpi_mdm | | `subjective` | text | nullable | AR-encrypted | | `objective` | text | nullable | AR-encrypted | | `assessment` | text | nullable | AR-encrypted | | `plan` | text | nullable | AR-encrypted | | `hpi` | text | nullable | AR-encrypted | | `ros` | text | nullable | AR-encrypted | | `exam` | text | nullable | AR-encrypted | | `medical_decision_making` | text | nullable | AR-encrypted | | `risks_benefits` | text | nullable | AR-encrypted | | `instructions` | text | nullable | AR-encrypted | | `follow_up` | text | nullable | AR-encrypted | | `authored_by_id` | bigint | nullable | FK users | | `ai_draft_source_ref` | character varying | nullable | e.g. Artifact# provenance of AI draft | | `ai_draft_edited` | boolean | NOT NULL | default false | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | | `doctor_addendum` | text | nullable | AR-encrypted | ### ehr_disclosure_logs HIPAA accounting-of-disclosures log: immutable record of every PHI disclosure (recipient, purpose, legal basis). - **Status:** **ACTIVE**: Written via Ehr::Workflows::RecordDisclosure from ehr/disclosures_controller.rb:4 and ehr/chart_prints_controller.rb:26, plus workflows/mark_faxed_to_external_pharmacy.rb:64; read in chart audit tab at ehr/charts_controller.rb:115. Model readonly? after persist (append-only) - **Spec ref:** §3.1 CHARTING EHR: 🟡 PARTIAL - **Encryption:** recipient_name, recipient_relationship, purpose, description, disclosure_metadata: AR encrypts (non-deterministic) - **Notable indexes:** `index_ehr_disclosure_logs_on_patient_id_and_disclosed_at (patient_id, disclosed_at)` 13 columns: | Column | Type | Null | Note | | ------------------------ | ----------------- | -------- | ------------------------------------------------------------------------------ | | `id` | bigint | NOT NULL | | | `patient_id` | bigint | NOT NULL | FK patients | | `disclosed_by_id` | bigint | NOT NULL | FK users | | `recipient_name` | text | NOT NULL | encrypted | | `recipient_relationship` | text | nullable | encrypted | | `purpose` | text | NOT NULL | encrypted | | `description` | text | nullable | encrypted | | `legal_basis` | character varying | NOT NULL | treatment\|payment\|operations\|authorization\|legal_required\|patient_request | | `disclosed_at` | timestamp(6) | NOT NULL | | | `request_id` | character varying | nullable | | | `disclosure_metadata` | text | NOT NULL | encrypted json | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### ehr_encounter_diagnoses ICD-10 diagnoses attached to an appointment chart record (one primary max), locked once that record is signed. - **Status:** **ACTIVE**: Written via Ehr::Workflows::DocumentClinicalNote from ehr/encounters_controller.rb:779 (persist_submitted_diagnoses!) and Records::CoreChartImporter (core_chart_importer.rb:574, wired to admin/core_chart_imports_controller.rb:13); read by sign_encounter.rb:158 gate and chart/appointment views - **Spec ref:** §3.1 CHARTING EHR: 🟡 PARTIAL - **Encryption:** icd10_code: AR encrypts deterministic; description: AR encrypts (non-deterministic) - **Notable indexes:** `UNIQUE partial idx_ehr_encounter_diagnoses_one_primary_per_encounter (encounter_id) WHERE primary = true` 8 columns: | Column | Type | Null | Note | | -------------- | ------------ | -------- | ---------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `encounter_id` | bigint | NOT NULL | FK ehr_encounters | | `problem_id` | bigint | nullable | FK ehr_problems | | `icd10_code` | text | NOT NULL | deterministic-encrypted, upcased/stripped before validation | | `description` | text | nullable | encrypted | | `primary` | boolean | NOT NULL | quoted identifier; one true per appointment chart record (partial unique index) | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### ehr_encounters Core visit record (draft -> open -> signed/voided) with HMAC signature over a canonical payload; anchor for notes, diagnoses, vitals, prescriptions. - **Status:** **ACTIVE**: Full production CRUD: created/updated/signed via ehr/encounters_controller (config/routes.rb:326-336; Ehr::Workflows::SignEncounter), imported via admin/core_chart_imports_controller -> core_chart_importer.rb:488; read everywhere (charts, review_queue, ehr_prescription_requires_signed_encounter gate) - **Spec ref:** §3.1 CHARTING EHR: 🟡 PARTIAL - **Encryption:** chief_complaint, patient_state_at_visit, patient_location, provider_location, participants, canonical_payload, signed_payload_bytes, void_reason: AR encrypts (non-deterministic) - **Notable indexes:** `UNIQUE index_ehr_encounters_on_appointment_id (appointment_id): one chart record per appointment` · `UNIQUE partial idx_ehr_encounters_import_fingerprint (patient_id, import_fingerprint) WHERE import_fingerprint IS NOT NULL` 31 columns: | Column | Type | Null | Note | | ------------------------------- | ----------------- | -------- | ------------------------------------------------------------ | | `id` | bigint | NOT NULL | | | `patient_id` | bigint | NOT NULL | FK patients | | `provider_id` | bigint | NOT NULL | FK users | | `clinic_id` | bigint | nullable | FK clinics | | `patient_program_enrollment_id` | bigint | nullable | | | `status` | character varying | NOT NULL | enum draft\|open\|signed\|voided | | `modality` | character varying | NOT NULL | enum video\|phone\|asynchronous\|in_person | | `encountered_at` | timestamp(6) | NOT NULL | | | `chief_complaint` | text | nullable | encrypted | | `patient_state_at_visit` | text | nullable | encrypted | | `patient_location` | text | nullable | encrypted | | `provider_location` | text | nullable | encrypted | | `identity_verification_method` | character varying | nullable | | | `identity_verified` | boolean | NOT NULL | | | `participants` | text | NOT NULL | encrypted json array | | `visit_started_at` | timestamp(6) | nullable | | | `visit_ended_at` | timestamp(6) | nullable | | | `signed_at` | timestamp(6) | nullable | record becomes readonly when set | | `signed_by_id` | bigint | nullable | FK users | | `signature_hmac` | character varying | nullable | | | `signature_key_version` | character varying | nullable | | | `canonical_payload` | text | NOT NULL | encrypted json; the signed content | | `voided_at` | timestamp(6) | nullable | | | `void_reason` | text | nullable | encrypted | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | | `appointment_id` | bigint | nullable | FK appointments; unique: idempotent open-visit | | `import_fingerprint` | character varying | nullable | | | `signed_payload_bytes` | text | nullable | encrypted; exact signed bytes | | `signature_payload_version` | character varying | nullable | | | `governed_artifact_version_ids` | jsonb | NOT NULL | policy versions pinned at signing (GovernedArtifactPinnable) | ### ehr_icd10_codes Curated ICD-10 code allowlist (reference table) gating diagnosis entry, ICD suggestions, and appointment-record signing. - **Status:** **REFERENCE**: Seed-maintained reference data (db/seeds/icd10_codes.rb: no runtime writes by design); read in production at ehr/encounters_controller.rb:437 and :775, sign gate at ehr/workflows/sign_encounter.rb:158, agents at ehr/agents/icd_suggestions.rb:89 - **Spec ref:** §3.1 CHARTING EHR: 🟡 PARTIAL - **Notable indexes:** `UNIQUE index_ehr_icd10_codes_on_code (code)` 8 columns: | Column | Type | Null | Note | | ------------- | ----------------- | -------- | ------------------------------------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `code` | character varying | NOT NULL | unique | | `description` | character varying | NOT NULL | | | `chapter` | character varying | nullable | | | `billable` | boolean | NOT NULL | default true; not consumed by any code path (only code/active read): REVIEW-HOLD (see register) | | `active` | boolean | NOT NULL | drives .active scope / on_list? | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### ehr_legal_holds Litigation/legal hold on a patient's records blocking data purges; one active hold per patient. - **Status:** **ACTIVE**: Placed/released via ehr/legal_holds_controller.rb:41 (config/routes.rb:349-350) through Ehr::Workflows::PlaceLegalHold (place_legal_hold.rb:9); read as purge guard in workflows/purge_healthkit_data.rb:32, ehr/patient_clinical_summary.rb:66, and chart audit tab charts_controller.rb:121 - **Spec ref:** §3.1 CHARTING EHR: 🟡 PARTIAL - **Encryption:** reason, scope, release_reason, hold_metadata: AR encrypts (non-deterministic) - **Notable indexes:** `UNIQUE partial idx_ehr_legal_holds_one_active_per_patient (patient_id) WHERE status = 'active'` 13 columns: | Column | Type | Null | Note | | ---------------- | ----------------- | -------- | -------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `patient_id` | bigint | NOT NULL | FK patients | | `placed_by_id` | bigint | NOT NULL | FK users | | `released_by_id` | bigint | nullable | FK users; required when released | | `status` | character varying | NOT NULL | enum active\|released; record readonly except via with_release | | `reason` | text | NOT NULL | encrypted | | `scope` | text | NOT NULL | encrypted | | `placed_at` | timestamp(6) | NOT NULL | | | `released_at` | timestamp(6) | nullable | | | `release_reason` | text | nullable | encrypted; required when released | | `hold_metadata` | text | NOT NULL | encrypted json | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### ehr_medical_histories Categorized medical/surgical/family/social history entries (optionally coded) on the patient chart, from patient report, clinician entry, or import. - **Status:** **ACTIVE**: Written in production by Records::PromoteExtractedFields (promote_extracted_fields.rb:97) from doctor/uploaded_documents_controller.rb:33 and Records::CoreChartImporter (core_chart_importer.rb:407) from admin/core_chart_imports_controller.rb:13; read in chart at ehr/charts_controller.rb:99 - **Spec ref:** §3.1 CHARTING EHR: 🟡 PARTIAL - **Encryption:** category, code, code_system: AR encrypts deterministic (queried/deduped); entry: AR encrypts (non-deterministic) - **Notable indexes:** `UNIQUE partial idx_ehr_histories_import_fingerprint (patient_id, import_fingerprint) WHERE import_fingerprint IS NOT NULL` · `UNIQUE partial idx_ehr_medical_histories_import_code (patient_id, code_system, code) WHERE both NOT NULL` 12 columns: | Column | Type | Null | Note | | -------------------- | ----------------- | -------- | ---------------------------------------------------- | | `id` | bigint | NOT NULL | | | `patient_id` | bigint | NOT NULL | FK patients | | `category` | text | NOT NULL | deterministic-encrypted | | `entry` | text | NOT NULL | encrypted | | `code` | text | nullable | deterministic-encrypted | | `code_system` | text | nullable | deterministic-encrypted | | `status` | character varying | NOT NULL | enum active\|inactive\|resolved | | `source` | character varying | NOT NULL | enum patient_reported\|clinician\|imported\|ops_edit | | `recorded_by_id` | bigint | nullable | FK users | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | | `import_fingerprint` | character varying | nullable | | ### ehr_medications Patient medication list (active/inactive) with clinician, intake, reconciled, imported, and ops_edit sources. - **Status:** **ACTIVE**: Written via Ehr::Integration::ClinicalFields.write (app/services/ehr/integration/clinical_fields.rb:179, called from app/controllers/ops/conversations_controller.rb and ops/conversation_actions_controller.rb) and Records::CoreChartImporter:339 behind admin/core_chart_imports_controller.rb:13; read in app/controllers/ehr/charts_controller.rb:25 and safety screens - **Spec ref:** §3.1 CHARTING EHR: 🟡 PARTIAL - **Encryption:** AR encrypts: name (deterministic), rxnorm (deterministic), dose, strength, route, frequency, start_date, end_date (app/models/ehr/medication.rb) - **Notable indexes:** `unique partial (patient_id, import_fingerprint) WHERE import_fingerprint IS NOT NULL` · `partial index on rxnorm WHERE rxnorm IS NOT NULL` · `trigger serialize_therapy_hold_patient_chart_write on INSERT/UPDATE/DELETE` 17 columns: | Column | Type | Null | Note | | -------------------- | ----------------- | -------- | --------------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `patient_id` | bigint | NOT NULL | FK patients | | `name` | text | NOT NULL | deterministic-encrypted | | `rxnorm` | text | nullable | deterministic-encrypted RxNorm code | | `dose` | text | nullable | encrypted | | `strength` | text | nullable | encrypted | | `route` | text | nullable | encrypted | | `frequency` | text | nullable | encrypted | | `start_date` | text | nullable | encrypted date cast via attribute :date | | `end_date` | text | nullable | encrypted date; required when status=inactive | | `status` | character varying | NOT NULL | enum: active\|inactive (default active) | | `source` | character varying | NOT NULL | enum: clinician\|intake\|reconciled\|imported\|ops_edit (default clinician) | | `otc` | boolean | NOT NULL | | | `recorded_by_id` | bigint | nullable | FK users | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | | `import_fingerprint` | character varying | nullable | dedup key for Records::Importable imports (3.5) | ### ehr_note_addenda Append-only signed addenda to clinical notes (post-signing provider notes / video-review attestations). - **Status:** **ACTIVE**: Created by Ehr::EncountersController#addendum (app/controllers/ehr/encounters_controller.rb:283, passkey-reauth gated); read in charts_controller.rb:130 and review_queue_presenter.rb; verified by Ehr::SignedAddendumVerifier - **Spec ref:** §3.1 CHARTING EHR: 🟡 PARTIAL - **Encryption:** AR encrypts: body (app/models/ehr/note_addendum.rb) - **Notable indexes:** `trigger ehr_note_addenda_append_only blocks UPDATE/DELETE (ehr_append_only_guard); model readonly? after persist` 6 columns: | Column | Type | Null | Note | | ------------------ | ------------ | -------- | --------------------- | | `id` | bigint | NOT NULL | | | `clinical_note_id` | bigint | NOT NULL | FK ehr_clinical_notes | | `author_id` | bigint | NOT NULL | FK users | | `body` | text | NOT NULL | encrypted | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### ehr_observations Non-vital clinical observations (body composition, screening, point-of-care, functional) sourced from structured records extracts. - **Status:** **DORMANT**: Only writer is Records::ExtractImporter, invoked solely by rake records:import_extract (lib/tasks/records.rake:109): no controller/job/cron writer; production read exists (app/controllers/ehr/charts_controller.rb:72) but rows only arrive via the ops rake import - **Spec ref:** §3.1 CHARTING EHR: 🟡 PARTIAL - **Encryption:** AR encrypts: name, value, taken_at, device, measurement_method, body_site (app/models/ehr/observation.rb) - **Notable indexes:** `unique partial (patient_id, import_fingerprint) WHERE import_fingerprint IS NOT NULL` · `(patient_id, code, clinical_date) trend index` 18 columns: | Column | Type | Null | Note | | -------------------- | ----------------- | -------- | ----------------------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `patient_id` | bigint | NOT NULL | FK patients | | `recorded_by_id` | bigint | nullable | FK users | | `category` | character varying | NOT NULL | enum: body_composition\|screening\|point_of_care\|functional\|other (default other) | | `flag` | character varying | NOT NULL | enum: normal\|abnormal\|unknown (default unknown) | | `source` | character varying | NOT NULL | enum: clinician\|imported (default imported) | | `unit` | character varying | nullable | | | `code` | character varying | NOT NULL | observation code, plaintext to allow trend_for index queries | | `clinical_date` | date | nullable | | | `import_fingerprint` | character varying | nullable | | | `name` | text | nullable | encrypted | | `value` | text | nullable | encrypted; presence-validated in model | | `taken_at` | text | nullable | encrypted datetime cast | | `device` | text | nullable | encrypted | | `measurement_method` | text | nullable | encrypted | | `body_site` | text | nullable | encrypted | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### ehr_patient_account_bindings 1:1 binding between a Patient chart and a login User account, resolving which chart a patient user sees. - **Status:** **DORMANT**: Production read exists (Ehr::Integration::Patients.find_by_user, app/services/ehr/integration/patients.rb:17, used by ehr base/charts/encounters controllers) but the only writer is Records::CoreChartBootstrapper:215, reached solely via rake records:bootstrap_patient / records:import_core_from_gcs (lib/tasks/records.rake:205,240); find_by_user falls back to user.patient when no binding row exists - **Spec ref:** §3.1 CHARTING EHR: 🟡 PARTIAL - **Notable indexes:** `unique index on patient_id` · `unique index on user_id` 7 columns: | Column | Type | Null | Note | | ------------- | ------------ | -------- | --------------------------------- | | `id` | bigint | NOT NULL | | | `patient_id` | bigint | NOT NULL | FK patients; unique | | `user_id` | bigint | NOT NULL | FK users; unique | | `bound_by_id` | bigint | nullable | FK users (actor) | | `bound_at` | timestamp(6) | NOT NULL | defaults to Time.current in model | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### ehr_patient_profiles Singleton per-patient chart header: pregnancy/lactation/substance-use flags, med/allergy review attestations, chart revision counter. - **Status:** **ACTIVE**: Written via Ehr::Integration::Patients.profile_for find_or_create (app/services/ehr/integration/patients.rb:32, called from ehr controllers) and Records::CoreChartImporter:286; read in charts_controller.rb:8, safety screens, pre-call brief, note/rx agents - **Spec ref:** §3.1 CHARTING EHR: 🟡 PARTIAL - **Encryption:** AR encrypts: pregnancy_status, lactation, substance_use, no_current_medications, medications_reviewed_at, allergies_reviewed_at (app/models/ehr/patient_profile.rb): hence NOT NULL text columns for boolean/datetime casts - **Notable indexes:** `unique index on patient_id` · `trigger serialize_therapy_hold_patient_chart_write on INSERT/UPDATE/DELETE` 13 columns: | Column | Type | Null | Note | | ---------------------------- | ------------ | -------- | ----------------------------------------------------- | | `id` | bigint | NOT NULL | | | `patient_id` | bigint | NOT NULL | FK patients; unique (singleton) | | `pregnancy_status` | text | NOT NULL | encrypted; values unknown\|clear\|pregnant\|lactating | | `lactation` | text | NOT NULL | encrypted boolean cast | | `substance_use` | text | NOT NULL | encrypted json cast | | `no_current_medications` | text | NOT NULL | encrypted boolean cast | | `medications_reviewed_at` | text | nullable | encrypted datetime cast | | `medications_reviewed_by_id` | bigint | nullable | FK users | | `allergies_reviewed_at` | text | nullable | encrypted datetime cast | | `allergies_reviewed_by_id` | bigint | nullable | FK users | | `chart_revision` | integer | NOT NULL | default 0 | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### ehr_problems Patient problem list (ICD-10 coded, active/resolved) with one-active-per-code enforcement. - **Status:** **ACTIVE**: Written by Records::CoreChartImporter:386 behind admin/core_chart_imports_controller.rb:13; read/managed in app/controllers/ehr/charts_controller.rb:11, linked from appointment-record diagnoses (app/models/ehr/encounter_diagnosis.rb), and safety screens evaluator/context snapshot - **Spec ref:** §3.1 CHARTING EHR: 🟡 PARTIAL - **Encryption:** AR encrypts: icd10_code (deterministic), description, onset_date, resolved_date, off_list_reason (app/models/ehr/problem.rb) - **Notable indexes:** `unique partial (patient_id, icd10_code, status) WHERE status='active': one active problem per code` · `unique partial (patient_id, import_fingerprint) WHERE import_fingerprint IS NOT NULL` · `trigger serialize_therapy_hold_patient_chart_write on INSERT/UPDATE/DELETE` 13 columns: | Column | Type | Null | Note | | -------------------- | ----------------- | -------- | ----------------------------------------------------- | | `id` | bigint | NOT NULL | | | `patient_id` | bigint | NOT NULL | FK patients | | `icd10_code` | text | NOT NULL | deterministic-encrypted; ICD-10 format-validated | | `description` | text | NOT NULL | encrypted | | `status` | character varying | NOT NULL | enum: active\|resolved (default active) | | `onset_date` | text | nullable | encrypted date cast | | `resolved_date` | text | nullable | encrypted date; required when status=resolved | | `source` | character varying | NOT NULL | enum: clinician\|intake\|imported (default clinician) | | `off_list_reason` | text | nullable | encrypted | | `recorded_by_id` | bigint | nullable | FK users | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | | `import_fingerprint` | character varying | nullable | | ### ehr_procedures CPT-coded procedure history rows, currently only populated by structured records extracts. - **Status:** **DORMANT**: Only writer is Records::ExtractImporter, invoked solely by rake records:import_extract (lib/tasks/records.rake:109): no controller/job/cron writer; production read exists (app/controllers/ehr/charts_controller.rb:82) but no production write path - **Spec ref:** §3.1 CHARTING EHR: 🟡 PARTIAL - **Encryption:** AR encrypts: description, narrative, performed_on, modifiers, performer, body_site (app/models/ehr/procedure.rb) - **Notable indexes:** `unique partial (patient_id, import_fingerprint) WHERE import_fingerprint IS NOT NULL` · `(patient_id, cpt_code, clinical_date)` 17 columns: | Column | Type | Null | Note | | -------------------- | ----------------- | -------- | ------------------------------------------------------ | | `id` | bigint | NOT NULL | | | `patient_id` | bigint | NOT NULL | FK patients | | `recorded_by_id` | bigint | nullable | FK users | | `status` | character varying | NOT NULL | enum: completed\|planned\|not_done (default completed) | | `source` | character varying | NOT NULL | enum: clinician\|imported (default imported) | | `units` | integer | NOT NULL | default 1, > 0 | | `cpt_code` | character varying | nullable | format \d{4}[0-9A-Z], optional | | `clinical_date` | date | nullable | | | `import_fingerprint` | character varying | nullable | | | `description` | text | nullable | encrypted; presence-validated in model | | `narrative` | text | nullable | encrypted | | `performed_on` | text | nullable | encrypted date cast | | `modifiers` | text | nullable | encrypted | | `performer` | text | nullable | encrypted | | `body_site` | text | nullable | encrypted | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### ehr_retention_policies Record-retention policy register (category, years, retirement): readonly-after-persist model. - **Status:** **DORMANT**: Only writer Ehr::Workflows::DefineRetentionPolicy has zero callers (no controller/job/rake); model appears only in app/lib/ehr/phi_manifest.rb: no production read or write path - **Spec ref:** §3.1 CHARTING EHR: 🟡 PARTIAL - **Encryption:** AR encrypts: name, retirement_reason, policy_metadata (app/models/ehr/retention_policy.rb) - **Notable indexes:** `unique partial idx_ehr_retention_policies_one_active_per_category on (record_category) WHERE active=true` 13 columns: | Column | Type | Null | Note | | ------------------- | ----------------- | -------- | ------------------------ | | `id` | bigint | NOT NULL | | | `name` | text | NOT NULL | encrypted | | `record_category` | character varying | NOT NULL | | | `retention_years` | integer | NOT NULL | | | `effective_on` | date | NOT NULL | | | `active` | boolean | NOT NULL | default true | | `created_by_id` | bigint | NOT NULL | FK users | | `retired_by_id` | bigint | nullable | FK users | | `retired_at` | timestamp(6) | nullable | | | `retirement_reason` | text | nullable | encrypted | | `policy_metadata` | text | NOT NULL | encrypted JSON attribute | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### ehr_vitals Patient vital-sign measurements (BP, HR, weight, height, BMI, temp) with encrypted values. - **Status:** **ACTIVE**: Written by Ehr::ScaleVitalsWriter (via app/services/ehr/integration/vitals.rb), intake inventory, Records::CoreChartImporter; read by patient_header_projector, adherence/prescription-card presenters in EHR UI - **Spec ref:** §3.1 CHARTING EHR: 🟡 PARTIAL - **Encryption:** AR encrypts: systolic, diastolic, heart_rate, weight, height, bmi, temperature, taken_at (hence text column types; app/models/ehr/vital.rb) - **Notable indexes:** `unique partial idx_ehr_vitals_import_fingerprint on (patient_id, import_fingerprint) WHERE import_fingerprint IS NOT NULL` · `unique on intake_response_id` 21 columns: | Column | Type | Null | Note | | ------------------------ | ----------------- | -------- | ------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `patient_id` | bigint | NOT NULL | | | `encounter_id` | bigint | nullable | | | `systolic` | text | nullable | encrypted integer | | `diastolic` | text | nullable | encrypted integer | | `heart_rate` | text | nullable | encrypted integer | | `weight` | text | nullable | encrypted decimal | | `weight_unit` | character varying | NOT NULL | lb\|kg, default 'lb' | | `height` | text | nullable | encrypted decimal | | `height_unit` | character varying | NOT NULL | in\|cm, default 'in' | | `bmi` | text | nullable | encrypted decimal, auto-calculated before_validation | | `temperature` | text | nullable | encrypted decimal | | `temp_unit` | character varying | NOT NULL | f\|c, default 'f' | | `source` | character varying | NOT NULL | patient_reported\|measured\|ops_edit | | `taken_at` | text | NOT NULL | encrypted datetime: cannot SQL-sort; recency uses created_at | | `recorded_by_id` | bigint | nullable | FK users | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | | `import_fingerprint` | character varying | nullable | import dedupe (Records::Importable) | | `intake_response_id` | bigint | nullable | unique FK intake_responses | | `height_carried_forward` | boolean | NOT NULL | default false | ### doctor_approvals Doctor sign-off (approve/reject) on a protocol Recommendation, parent of prescriptions. - **Status:** **ACTIVE**: Created by Workflows::ApproveByDoctor (app/services/workflows/approve_by_doctor.rb:82) from doctor UI (app/controllers/doctor/cases_controller.rb:67); read by reports and ehr/integration/prescribing.rb - **Spec ref:** §3.2 RX (prescription generation): 🟢 EXISTS 9 columns: | Column | Type | Null | Note | | ------------------- | ----------------- | -------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `created_at` | timestamp(6) | NOT NULL | | | `doctor_id` | bigint | NOT NULL | FK users | | `reasoning_note` | text | nullable | | | `recommendation_id` | bigint | NOT NULL | FK recommendations | | `signature_hash` | character varying | nullable | | | `signed_at` | timestamp(6) | nullable | | | `status` | character varying | NOT NULL | enum pending/signed/rejected/requested_more_data, default pending. Related round-1 dead candidate prescriptions.doctor_approval_id (deprecation) lives on prescriptions table (other group) | | `updated_at` | timestamp(6) | NOT NULL | | ### ehr_dea_schedules Reference table classifying substances by DEA schedule (with aliases + primary-source citation) for controlled-substance resolution on the Rx card. - **Status:** **REFERENCE**: Seed-maintained reference data via Ehr::Workflows::UpsertDeaSchedule, whose only caller is db/seeds/dea_schedule_classifications.rb:22 (no runtime writes by design); read in production via Ehr::ControlledSubstances::ScheduleResolver (schedule_resolver.rb:71) <- Ehr::Integration::PrescriptionCard (prescription_card.rb:73) <- PrescriptionCardPresenter <- ehr/encounters_controller.rb:689. The dea_schedule_controls_prescribing hard-fail gate is vestigial (enforcement removed 2026-07-14); the table itself is still read for Rx-card resolution - **Spec ref:** §3.2 RX (prescription generation): 🟢 EXISTS - **CHECK constraints:** `jsonb_typeof(aliases) = 'array'` · `schedule in (unclassified, not_controlled, I, II, III, IV, V)` - **Notable indexes:** `UNIQUE index_ehr_dea_schedules_on_substance_name (substance_name)` · `GIN index_ehr_dea_schedules_on_aliases (aliases)` 11 columns: | Column | Type | Null | Note | | ---------------- | ----------------- | -------- | --------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `substance_name` | character varying | NOT NULL | NFKC-normalized canonical name, unique | | `schedule` | character varying | NOT NULL | unclassified\|not_controlled\|I\|II\|III\|IV\|V | | `metadata` | jsonb | NOT NULL | | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | | `aliases` | jsonb | NOT NULL | normalized alias array; cross-row overlap validated | | `source_name` | character varying | nullable | primary-source citation; required by model + source_ready scope | | `source_uri` | character varying | nullable | HTTPS-only validated | | `source_version` | character varying | nullable | | | `reviewed_on` | date | nullable | not-in-future validated | ### ehr_prescription_confirmations Doctor's fingerprinted confirmation of prescription content on an appointment chart record (per row_key line, invalidated on change), gating signing/issuance. - **Status:** **ACTIVE**: Written by Ehr::Workflows::ConfirmPrescription called from Ehr::EncountersController#confirm_prescription (app/controllers/ehr/encounters_controller.rb:224); read/enforced by Ehr::Workflows::SignEncounter and Ehr::PrescriptionCardPresenter - **Spec ref:** §3.2 RX (prescription generation): 🟢 EXISTS - **Encryption:** none (fingerprint digests only) - **Notable indexes:** `unique partial (encounter_id, row_key) WHERE row_key IS NOT NULL` · `unique partial (encounter_id) WHERE row_key IS NULL: legacy appointment-record singleton` 11 columns: | Column | Type | Null | Note | | ------------------------------- | ----------------- | -------- | ----------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `encounter_id` | bigint | NOT NULL | FK ehr_encounters | | `confirmed_by_id` | bigint | NOT NULL | FK users (doctor) | | `revision` | integer | NOT NULL | default 1, > 0 | | `fingerprint` | character varying | NOT NULL | SHA-256 over PRESCRIPTION_FINGERPRINT_FIELDS canonical JSON | | `confirmed_at` | timestamp(6) | NOT NULL | | | `invalidated_at` | timestamp(6) | nullable | set when prescription content changes after confirm | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | | `governed_artifact_version_ids` | jsonb | NOT NULL | GovernedArtifactPinnable pins, default {} | | `row_key` | character varying | nullable | per-prescription-row scoping; NULL = legacy singleton | ### ehr_prescription_correction_requests Immutable staff-prepared proposals to correct an issued prescription, routed to the appointment doctor for review/replacement. - **Status:** **ACTIVE**: Written by Ehr::Workflows::PreparePrescriptionCorrection via Ops::Integration::PrescriptionCorrections (ops agent surface, app/services/ops/integration/prescription_corrections.rb:36); reviewed via app/controllers/ehr/prescription_correction_requests_controller.rb and encounters_controller; reconciled by Ehr::Workflows::ReconcilePrescriptionCorrection - **Spec ref:** §3.2 RX (prescription generation): 🟢 EXISTS - **Encryption:** AR encrypts: proposed_medication, proposed_strength, proposed_sig, reason, source_evidence; DB CHECK ehr_rx_correction_proposal_encrypted enforces envelopes on all five - **CHECK constraints:** `ehr_rx_correction_proposal_encrypted: proposed_medication/proposed_strength/proposed_sig/reason/source_evidence must be ActiveRecord-encrypted envelopes` - **Notable indexes:** `unique partial (original_prescription_id) WHERE status IN (pending_doctor_review, in_doctor_review): one open correction per prescription` · `unique (original_prescription_id, preparation_digest)` · `trigger ehr_prescription_correction_requests_immutable blocks identity/proposal mutation on UPDATE/DELETE (ehr_prescription_correction_identity_immutable)` 22 columns: | Column | Type | Null | Note | | ----------------------------- | ----------------- | -------- | -------------------------------------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `original_prescription_id` | bigint | NOT NULL | FK prescriptions | | `source_encounter_id` | bigint | NOT NULL | FK ehr_encounters; must equal original prescription's appointment chart record | | `patient_id` | bigint | NOT NULL | FK patients; must own original prescription | | `appointment_id` | bigint | NOT NULL | FK appointments; must match source appointment record | | `assigned_doctor_id` | bigint | NOT NULL | FK users; must be appointment doctor, active | | `prepared_by_id` | bigint | NOT NULL | FK users (staff preparer) | | `replacement_prescription_id` | bigint | nullable | FK prescriptions | | `review_encounter_id` | bigint | nullable | FK ehr_encounters; consistency-validated | | `status` | character varying | NOT NULL | enum: pending_doctor_review\|in_doctor_review\|rejected\|cancelled\|replacement_issued\|reconciled | | `preparation_digest` | character varying | NOT NULL | HMAC-SHA256 over canonicalized proposal (key ehr-prescription-correction-digest-v1) | | `proposed_medication` | text | NOT NULL | encrypted | | `proposed_strength` | text | NOT NULL | encrypted | | `proposed_sig` | text | NOT NULL | encrypted | | `reason` | text | NOT NULL | encrypted | | `source_evidence` | text | NOT NULL | encrypted json cast, default [] | | `requested_at` | timestamp(6) | NOT NULL | | | `reviewed_at` | timestamp(6) | nullable | | | `replacement_issued_at` | timestamp(6) | nullable | | | `reconciled_at` | timestamp(6) | nullable | | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### ehr_safety_screen_acknowledgements Immutable human acknowledgement of a warning safety-screen run. - **Status:** **DORMANT**: Only writer Ehr::Workflows::AcknowledgeSafetyScreen has no production caller (referenced only by its own test test/services/ehr/workflows/acknowledge_safety_screen_test.rb); readonly? true after persist - **Spec ref:** §3.2 RX (prescription generation): 🟢 EXISTS 7 columns: | Column | Type | Null | Note | | ---------------------- | ----------------- | -------- | ------------------------- | | `id` | bigint | NOT NULL | | | `safety_screen_run_id` | bigint | NOT NULL | FK ehr_safety_screen_runs | | `acknowledged_by_id` | bigint | NOT NULL | FK users | | `reason` | character varying | NOT NULL | | | `acknowledged_at` | timestamp(6) | NOT NULL | | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### ehr_safety_screen_runs Immutable safety-screen result pinned to a chart snapshot (fingerprint + revision) before prescribing. - **Status:** **ACTIVE**: Written by Ehr::Workflows::RecordSafetyScreen via Ehr::Agents::RxSuggestions/NoteDraft invoked from app/controllers/ehr/encounters_controller.rb; freshness read by prescribing and app/services/ehr/integration/therapy_hold_release.rb - **Spec ref:** §3.2 RX (prescription generation): 🟢 EXISTS - **Encryption:** AR encrypts: result (JSON attribute, app/models/ehr/safety_screen_run.rb) - **Notable indexes:** `idx_ehr_safety_runs_scope_revision on (encounter_id, formula_version_id, prescription_id, chart_revision, chart_fingerprint)` 16 columns: | Column | Type | Null | Note | | ------------------------------- | ----------------- | -------- | --------------------------------------------------- | | `id` | bigint | NOT NULL | | | `patient_id` | bigint | NOT NULL | | | `prescription_id` | bigint | nullable | | | `formula_version_id` | bigint | nullable | | | `screened_by_id` | bigint | nullable | FK users | | `chart_revision` | integer | NOT NULL | | | `severity` | character varying | NOT NULL | none\|warn\|contraindicated, default 'none' | | `result` | text | NOT NULL | encrypted JSON | | `artifact_id` | bigint | nullable | | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | | `encounter_id` | bigint | nullable | nullable in schema but belongs_to required in model | | `chart_fingerprint` | character varying | nullable | sha256 hex, required by model | | `governed_artifact_version_ids` | jsonb | NOT NULL | GovernedArtifactPinnable pins | | `checklist_id` | character varying | nullable | glp1_post_call\|glp1_fill_safety | | `policy_fingerprint` | character varying | nullable | | ### ehr_state_prescribing_rules Per-state prescribing rule requirements (jsonb): never consumed. - **Status:** **REVIEW-HOLD**: No production caller found in the audited tree; preserve pending the system-wide removal review. - **Spec ref:** §3.2 RX (prescription generation): 🟢 EXISTS - **Notable indexes:** `unique on (state, rule_kind)` 7 columns: | Column | Type | Null | Note | | -------------- | ----------------- | -------- | ----------------------------------- | | `id` | bigint | NOT NULL | REVIEW-HOLD: entire table unused in audited tree (see register) | | `state` | character varying | NOT NULL | | | `rule_kind` | character varying | NOT NULL | | | `requirements` | jsonb | NOT NULL | | | `active` | boolean | NOT NULL | default true | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### fda_reference_strengths Signed-off FDA reference product strengths used as dose guardrails for free-text prescribing. - **Status:** **REFERENCE**: Seed-maintained reference data (db/seeds/fda_reference_strengths.rb: no runtime writes by design); read in the prescribing path by Ehr::Integration::DoseGuardrail (app/services/ehr/integration/dose_guardrail.rb, called from prescribing.rb and prescription_card.rb) - **Spec ref:** §3.2 RX (prescription generation): 🟢 EXISTS - **CHECK constraints:** `substance_name must equal lower(btrim(substance_name)) and be non-empty` · `strength_value > 0` · `signed_off status requires non-blank signed_off_by and signed_off_at` · `clinical_signoff_status in (pending_signoff, signed_off)` - **Notable indexes:** `unique index_fda_reference_strengths_on_identity (substance_name, product_name, strength_value, strength_unit)` 10 columns: | Column | Type | Null | Note | | ------------------------- | ----------------- | -------- | ------------------------------------------------------ | | `id` | bigint | NOT NULL | | | `substance_name` | character varying | NOT NULL | normalized lowercase | | `product_name` | character varying | NOT NULL | | | `strength_value` | numeric(12,6) | NOT NULL | | | `strength_unit` | character varying | NOT NULL | | | `clinical_signoff_status` | character varying | NOT NULL | pending_signoff\|signed_off, default 'pending_signoff' | | `signed_off_by` | character varying | nullable | | | `signed_off_at` | timestamp(6) | nullable | | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### prescriptions The prescription record: medication, sig, encrypted legal snapshots at issue, signed by provider from an EHR appointment chart record. - **Status:** **ACTIVE**: Created/signed via app/services/ehr/integration/prescribing.rb (production EHR sign path); read across doctor/patient/pharmacist controllers (e.g. app/controllers/doctor/cases_controller.rb:20) - **Spec ref:** §3.2 RX (prescription generation): 🟢 EXISTS - **Encryption:** state_at_issue, signature_ref, patient_snapshot, prescriber_snapshot, provider_credential_snapshot, pharmacy_snapshot, structured_sig: Rails Active Record Encryption (app/models/prescription.rb:38-44) - **Notable indexes:** `UNIQUE (idempotency_key) WHERE NOT NULL` · `UNIQUE (encounter_id) WHERE status='draft' AND encounter_id IS NOT NULL: one draft per appointment chart record` · `UNIQUE (external_id) WHERE NOT NULL (review hold)` 30 columns: | Column | Type | Null | Note | | ------------------------------- | ----------------- | -------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `clinical_rationale` | text | nullable | | | `created_at` | timestamp(6) | NOT NULL | | | `directions` | text | nullable | | | `doctor_approval_id` | bigint | nullable | DEPRECATION CANDIDATE (round-1 confirmed): legacy pre-EHR approval path; still validated presence unless issued_from_signed_ehr_encounter? (app/models/prescription.rb:27) and read in doctor/cases_controller.rb:20: not fully dead | | `external_id` | character varying | nullable | REVIEW-HOLD: zero code references found; preserve pending history/data/integration review | | `form` | character varying | nullable | | | `frequency` | character varying | nullable | | | `medication_name` | character varying | NOT NULL | | | `patient_id` | bigint | NOT NULL | FK patients | | `quantity` | integer | nullable | | | `route` | character varying | nullable | | | `status` | character varying | NOT NULL | default 'draft'; enum draft\|valid_rx\|dispensed\|cancelled (prefix rx) | | `strength` | character varying | nullable | | | `updated_at` | timestamp(6) | NOT NULL | | | `encounter_id` | bigint | nullable | FK ehr_encounters; unique per draft | | `patient_program_enrollment_id` | bigint | nullable | FK patient_program_enrollments | | `signed_by_id` | bigint | nullable | FK users (prescriber) | | `idempotency_key` | character varying | nullable | unique when present (EHR sign idempotency) | | `issued_at` | timestamp(6) | nullable | | | `expires_at` | timestamp(6) | nullable | | | `state_at_issue` | text | nullable | encrypted | | `signature_ref` | text | nullable | encrypted | | `patient_snapshot` | text | nullable | encrypted | | `prescriber_snapshot` | text | nullable | encrypted | | `provider_credential_snapshot` | text | nullable | encrypted | | `pharmacy_snapshot` | text | nullable | encrypted | | `structured_sig` | text | nullable | encrypted | | `formula_version_id` | bigint | nullable | FK formula_versions | | `governed_artifact_version_ids` | jsonb | NOT NULL | default {} | ### recommendations Doctor-review case: protocol/medication options for a patient+program that a doctor approves into a prescription. - **Status:** **DORMANT**: Only creator Workflows::SubmitForDoctorReview (app/services/workflows/submit_for_doctor_review.rb:23) has no production caller (tests only per canonical register); prod read surfaces exist (app/controllers/admin/audit_controller.rb:26, app/controllers/concerns/doctor/case_visibility.rb:17) but rows only arise from demo journeys - **Spec ref:** §3.2 RX (prescription generation): 🟢 EXISTS 7 columns: | Column | Type | Null | Note | | ------------ | ----------------- | -------- | ---------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `created_at` | timestamp(6) | NOT NULL | | | `options` | jsonb | NOT NULL | default []; candidate protocol options | | `patient_id` | bigint | NOT NULL | FK patients | | `program_id` | bigint | NOT NULL | FK programs | | `status` | character varying | NOT NULL | enum: draft/pending/approved/rejected/superseded (default draft) | | `updated_at` | timestamp(6) | NOT NULL | | ### therapy_holds Clinical therapy holds that block prescribing/fulfillment until released with a safety screen and fingerprints. - **Status:** **ACTIVE**: Written in prod via Linq::TherapyHoldReconciler (api/v1/linq_controller.rb:563, Workflows::LatchLinqEmergencyHold, Workflows::RecordDosingError) and read as an enforcement gate by TherapyHolds::Guard.with_clearance! in ehr/prescriptions_controller.rb:20 and fulfillment workflows. Release path (Workflows::ReleaseTherapyHold) has no production caller: console-only (canonical: ReleaseLinqEmergencyHold no UI). - **Spec ref:** §3.2 RX (prescription generation): 🟢 EXISTS - **Encryption:** reason, clinical_state, resolution_confirmation: Rails encrypts (app/models/therapy_hold.rb:16-18); *_binding columns are sha256 digests of plaintext - **CHECK constraints:** `chk_therapy_holds_application_source: manual_clinician requires applied_by and no source; linq_emergency_hold/dosing_error_incident require polymorphic source, no applied_by, no prescription` · `chk_therapy_holds_reason_binding: reason_binding is 64-hex sha256` · `chk_therapy_holds_release_fields: active rows have all release fields NULL/false; released rows require released_at/by, trigger_resolved, non-blank resolution_confirmation, 64-hex confirmation/chart/policy fingerprints and a release_safety_screen_run_id` · `chk_therapy_holds_status: status in (active, released)` - **Notable indexes:** `UNIQUE index_therapy_holds_on_idempotency_key` · `idx_therapy_holds_patient_status (patient_id, status)` · `idx_therapy_holds_prescription_status (prescription_id, status)` · `index_therapy_holds_on_source (source_type, source_id) polymorphic` 24 columns: | Column | Type | Null | Note | | --------------------------------- | ----------------- | -------- | ------------------------------------------------------------ | | `id` | bigint | NOT NULL | | | `patient_id` | bigint | NOT NULL | FK patients | | `clinic_id` | bigint | NOT NULL | FK clinics | | `prescription_id` | bigint | nullable | FK prescriptions; NULL for source-driven holds per CHECK | | `applied_by_id` | bigint | nullable | FK users; required iff manual_clinician | | `released_by_id` | bigint | nullable | FK users | | `source_type` | character varying | nullable | polymorphic trigger (LinqConversation / DosingErrorIncident) | | `source_id` | bigint | nullable | | | `idempotency_key` | character varying | NOT NULL | unique | | `reason` | character varying | NOT NULL | encrypted | | `reason_binding` | character varying | NOT NULL | sha256 of reason | | `application_source` | character varying | NOT NULL | manual_clinician\|linq_emergency_hold\|dosing_error_incident | | `clinical_state` | character varying | nullable | encrypted | | `status` | character varying | NOT NULL | active\|released; default active | | `applied_at` | timestamp(6) | NOT NULL | | | `released_at` | timestamp(6) | nullable | | | `trigger_resolved` | boolean | NOT NULL | default false | | `resolution_confirmation` | text | nullable | encrypted | | `resolution_confirmation_binding` | character varying | nullable | sha256 | | `release_safety_screen_run_id` | bigint | nullable | FK agent run backing the release | | `release_chart_fingerprint` | character varying | nullable | sha256 | | `release_policy_fingerprint` | character varying | nullable | sha256 | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### prescription_payments One payment record per prescription: staff-priced flat Rx charge, Stripe Checkout session, per-state billing account (tx/ca). - **Status:** **ACTIVE**: Written by Workflows::PricePrescription (app/services/workflows/price_prescription.rb:75) and RxPayments::FinalizeCheckout:62; read/updated by app/controllers/webhooks/rx_stripe_controller.rb:79 and app/jobs/rx_checkout_reconcile_job.rb:26 - **Spec ref:** §3.3 RX Pay (Subscription / Offer): 🟡 PARTIAL - **Notable indexes:** `UNIQUE (stripe_checkout_session_id) WHERE NOT NULL` · `UNIQUE (prescription_id): one payment per Rx` 17 columns: | Column | Type | Null | Note | | ---------------------------- | ----------------- | -------- | --------------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `prescription_id` | bigint | NOT NULL | FK prescriptions; unique | | `patient_id` | bigint | NOT NULL | FK patients | | `priced_by_id` | bigint | nullable | FK users (staff who priced) | | `amount_cents` | integer | nullable | flat $195 default (RxPricing DEFAULT_AMOUNT_CENTS 19_500) | | `currency` | character varying | NOT NULL | default 'usd' | | `status` | character varying | NOT NULL | default 'unpaid'; enum unpaid\|paid (prefix payment) | | `stripe_checkout_session_id` | character varying | nullable | | | `stripe_payment_intent_id` | character varying | nullable | | | `stripe_customer_id` | character varying | nullable | Rx-account customer, separate from membership Stripe account | | `priced_at` | timestamp(6) | nullable | | | `paid_at` | timestamp(6) | nullable | | | `ready_to_pay_notified_at` | timestamp(6) | nullable | | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | | `pay_link_nonce` | character varying | nullable | | | `billing_account` | character varying | NOT NULL | default 'tx'; tx\|ca per-state Stripe account (RxBilling.account_for_state) | ### authorization_tokens Single-use digest-at-rest tokens bound to a consent: text-PHI access/OTP/share, records import handoff, Function Health import, mobile OAuth state/completion (also serves 4.4 and 10.1). - **Status:** **ACTIVE**: Minted in production workflows (app/services/workflows/mint_authorization_token.rb:62, seal_text_authorization.rb:106, mint_import_handoff_token.rb:32, stage_mobile_oauth_authorization.rb:26, app/lib/mobile_oauth_state.rb:30); located/consumed by authorizations_controller.rb, api/mobile/v1/consents_controller.rb, Workflows::ConsumeAuthorizationToken - **Spec ref:** §3.5 Previous Charts (records ingestion): 🟡 PARTIAL - **Encryption:** no AR-encrypted columns: token_digest is SHA-256 of the raw secret (AuthorizationTokenSecret), otp_digest is HMAC under phone pepper bound to row id (raw secrets never stored) - **Notable indexes:** `UNIQUE partial index_authorization_tokens_on_token_digest (token_digest) WHERE token_digest IS NOT NULL` 17 columns: | Column | Type | Null | Note | | ----------------------- | ----------------- | -------- | -------------------------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `attempts` | integer | NOT NULL | OTP verify attempts, cap 5 | | `consent_id` | bigint | NOT NULL | FK -> consents | | `consumed_at` | timestamp(6) | nullable | | | `created_at` | timestamp(6) | NOT NULL | | | `expires_at` | timestamp(6) | NOT NULL | | | `ip_hash` | character varying | nullable | | | `last_attempt_at` | timestamp(6) | nullable | | | `metadata` | jsonb | NOT NULL | | | `otp_digest` | character varying | nullable | HMAC'd 6-digit code | | `otp_sent_at` | timestamp(6) | nullable | | | `phone_binding_digest` | character varying | nullable | | | `phone_binding_version` | character varying | nullable | | | `purpose` | character varying | NOT NULL | enum: access, otp, share, import, handoff, mobile_oauth_state, mobile_oauth_completion | | `revoked_at` | timestamp(6) | nullable | | | `token_digest` | character varying | nullable | SHA-256 of raw secret; unique where present | | `updated_at` | timestamp(6) | NOT NULL | | ### connect_session_grants One-time token+OTP grant for the existing-patient records-import /connect checkpoint. - **Status:** **ACTIVE**: Minted from admin UI via Workflows::MintConnectSession (app/controllers/admin/connect_sessions_controller.rb:22) and consumed/verified in app/controllers/connect_controller.rb (routes.rb:219-225) - **Spec ref:** §3.5 Previous Charts (records ingestion): 🟡 PARTIAL - **Encryption:** none (stores digests: token_digest, otp_digest, phone_binding_digest) - **Notable indexes:** `UNIQUE index_connect_session_grants_on_token_digest WHERE token_digest IS NOT NULL` · `partial index_connect_session_grants_on_revoked_at WHERE revoked_at IS NOT NULL` · `idx_connect_session_grants_patient_expiry (patient_id, expires_at)` 16 columns: | Column | Type | Null | Note | | ----------------------- | ----------------- | -------- | ------------------------------ | | `id` | bigint | NOT NULL | | | `attempts` | integer | NOT NULL | OTP attempt counter, default 0 | | `consumed_at` | timestamp(6) | nullable | | | `created_at` | timestamp(6) | NOT NULL | | | `expires_at` | timestamp(6) | NOT NULL | | | `ip_hash` | character varying | nullable | | | `last_attempt_at` | timestamp(6) | nullable | | | `metadata` | jsonb | NOT NULL | default {} | | `otp_digest` | character varying | nullable | | | `otp_sent_at` | timestamp(6) | nullable | | | `patient_id` | bigint | NOT NULL | FK patients | | `phone_binding_digest` | character varying | NOT NULL | | | `phone_binding_version` | character varying | NOT NULL | default v1 | | `revoked_at` | timestamp(6) | nullable | | | `token_digest` | character varying | NOT NULL | unique (partial) | | `updated_at` | timestamp(6) | NOT NULL | | ### dead_letter_intakes Dead-letter log for public-intake bucket objects that failed to import. - **Status:** **DORMANT**: Written in production by recurring PublicIntakePullJob (config/recurring.yml:26; app/services/public_intakes/pull_from_bucket.rb:69 DeadLetterIntake.record!) but nothing reads rows back: no admin UI, no retry consumer; scope :open is unused - **Spec ref:** §3.5 Previous Charts (records ingestion): 🟡 PARTIAL - **Notable indexes:** `UNIQUE index_dead_letter_intakes_on_object_key (object_key)` 10 columns: | Column | Type | Null | Note | | --------------- | ----------------- | -------- | ------------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `created_at` | timestamp(6) | NOT NULL | | | `error_class` | character varying | NOT NULL | | | `error_message` | text | NOT NULL | truncated to 2000 chars | | `last_seen_at` | timestamp(6) | NOT NULL | | | `object_key` | character varying | NOT NULL | unique S3 object key | | `raw_body_hash` | character varying | nullable | SHA256 of raw body | | `retry_count` | integer | NOT NULL | default 0 | | `status` | character varying | NOT NULL | open/reviewed/resolved, default open; reviewed/resolved never set by code | | `updated_at` | timestamp(6) | NOT NULL | | ### ehr_coverages Patient insurance coverage rows (payer/plan/member), imported from previous-charts records extracts. - **Status:** **DORMANT**: Chart read surface exists (app/controllers/ehr/charts_controller.rb:62), but the only writer is Records::ExtractImporter (extract_importer.rb:12) invoked via ops rake records:import_extract (lib/tasks/records.rake:109, prod-gated by RECORDS_IMPORT_ALLOWED=1): rake-only writer, no production write path; same lane as ehr_observations/ehr_procedures - **Spec ref:** §3.5 Previous Charts (records ingestion): 🟡 PARTIAL - **Encryption:** payer_name, plan_name, member_id, group_number, relationship_to_subscriber, effective_on, expired_on: AR encrypts (non-deterministic) - **Notable indexes:** `UNIQUE partial idx_ehr_coverages_import_fingerprint (patient_id, import_fingerprint) WHERE import_fingerprint IS NOT NULL` 16 columns: | Column | Type | Null | Note | | ---------------------------- | ----------------- | -------- | ------------------------------------------ | | `id` | bigint | NOT NULL | | | `patient_id` | bigint | NOT NULL | FK patients | | `recorded_by_id` | bigint | nullable | FK users | | `rank` | character varying | NOT NULL | enum primary\|secondary, default primary | | `status` | character varying | NOT NULL | enum active\|inactive, default active | | `source` | character varying | NOT NULL | enum clinician\|imported, default imported | | `import_fingerprint` | character varying | nullable | dedup key for imports | | `payer_name` | text | nullable | encrypted; required by model validation | | `plan_name` | text | nullable | encrypted | | `member_id` | text | nullable | encrypted | | `group_number` | text | nullable | encrypted | | `relationship_to_subscriber` | text | nullable | encrypted | | `effective_on` | text | nullable | encrypted date attribute | | `expired_on` | text | nullable | encrypted date attribute | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### ehr_diagnostic_reports Imported diagnostic study reports (imaging/cardiology/etc.) with findings/impression/measurements for the patient chart. - **Status:** **DORMANT**: Chart read surface exists (app/controllers/ehr/charts_controller.rb:67), but the only writer is Records::ExtractImporter (extract_importer.rb:7) via ops rake records:import_extract (lib/tasks/records.rake:109, prod-gated): rake-only writer, no production write path; same lane as ehr_observations/ehr_procedures - **Spec ref:** §3.5 Previous Charts (records ingestion): 🟡 PARTIAL - **Encryption:** title, body_site, study_date, performer, interpreting, operator, referring, facility, accession, clinical_history, technique, comparison, findings, impression, measurements, addenda: AR encrypts (non-deterministic) - **Notable indexes:** `UNIQUE partial idx_ehr_reports_import_fingerprint (patient_id, import_fingerprint) WHERE import_fingerprint IS NOT NULL` · `idx_ehr_reports_patient_type_date (patient_id, report_type, clinical_date)` 29 columns: | Column | Type | Null | Note | | -------------------- | ----------------- | -------- | --------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `patient_id` | bigint | NOT NULL | FK patients | | `recorded_by_id` | bigint | nullable | FK users | | `report_type` | character varying | NOT NULL | enum imaging\|cardiology\|pulmonary\|vascular\|neurodiagnostic\|other | | `modality` | character varying | NOT NULL | enum xr\|ct\|mr\|us\|echo\|ecg\|pft\|abi\|other | | `laterality` | character varying | NOT NULL | enum left\|right\|bilateral\|unspecified | | `status` | character varying | NOT NULL | enum preliminary\|final\|amended\|unconfirmed | | `date_precision` | character varying | NOT NULL | enum date\|datetime\|approximate\|unknown | | `source` | character varying | NOT NULL | enum clinician\|imported | | `clinical_date` | date | nullable | required unless date_precision=unknown | | `import_fingerprint` | character varying | nullable | | | `title` | text | nullable | encrypted; required by model | | `body_site` | text | nullable | encrypted | | `study_date` | text | nullable | encrypted datetime attribute | | `performer` | text | nullable | encrypted | | `interpreting` | text | nullable | encrypted | | `operator` | text | nullable | encrypted | | `referring` | text | nullable | encrypted | | `facility` | text | nullable | encrypted | | `accession` | text | nullable | encrypted | | `clinical_history` | text | nullable | encrypted | | `technique` | text | nullable | encrypted | | `comparison` | text | nullable | encrypted | | `findings` | text | nullable | encrypted | | `impression` | text | nullable | encrypted | | `measurements` | text | nullable | encrypted json hash | | `addenda` | text | nullable | encrypted json array | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### ehr_immunizations Imported immunization records (vaccine, CVX, administration detail) for the patient chart. - **Status:** **DORMANT**: Chart read surface exists (app/controllers/ehr/charts_controller.rb:87), but the only writer is Records::ExtractImporter (extract_importer.rb:11) via ops rake records:import_extract (lib/tasks/records.rake:109, prod-gated): rake-only writer, no production write path; same lane as ehr_observations/ehr_procedures - **Spec ref:** §3.5 Previous Charts (records ingestion): 🟡 PARTIAL - **Encryption:** name, administered_on, lot_number, dose, route, site, performer, status_reason: AR encrypts (non-deterministic) - **Notable indexes:** `UNIQUE partial idx_ehr_immunizations_import_fingerprint (patient_id, import_fingerprint) WHERE import_fingerprint IS NOT NULL` · `idx_ehr_immunizations_patient_cvx_date (patient_id, cvx_code, clinical_date)` 18 columns: | Column | Type | Null | Note | | -------------------- | ----------------- | -------- | ------------------------------------------------- | | `id` | bigint | NOT NULL | | | `patient_id` | bigint | NOT NULL | FK patients | | `recorded_by_id` | bigint | nullable | FK users | | `status` | character varying | NOT NULL | enum completed\|not_done | | `source` | character varying | NOT NULL | enum clinician\|imported | | `cvx_code` | character varying | nullable | | | `clinical_date` | date | nullable | | | `import_fingerprint` | character varying | nullable | | | `name` | text | nullable | encrypted; required by model | | `administered_on` | text | nullable | encrypted date attribute; required when completed | | `lot_number` | text | nullable | encrypted | | `dose` | text | nullable | encrypted | | `route` | text | nullable | encrypted | | `site` | text | nullable | encrypted | | `performer` | text | nullable | encrypted | | `status_reason` | text | nullable | encrypted; required when not_done | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### extracted_clinical_fields Structured fields (clinical + ID-document identity) extracted from uploaded documents by LLM extractors. - **Status:** **ACTIVE**: Written by Records::PatientDocumentExtractor / GeminiPatientRecordExtractor via app/jobs/records/extract_uploaded_document_job.rb; read by Identity::IdDocumentMatcher/AcceptIdPhoto, Biometrics::Height, api/v1/linq_controller - **Spec ref:** §3.5 Previous Charts (records ingestion): 🟡 PARTIAL - **Encryption:** AR encrypts: value (support_unencrypted_data), identity_value, source_location (support_unencrypted_data): app/models/extracted_clinical_field.rb - **Notable indexes:** `idx_ecf_on_uploaded_document_id_and_field_kind` 11 columns: | Column | Type | Null | Note | | ---------------------- | ----------------- | -------- | ---------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `confidence` | numeric(4,3) | nullable | 0..1 | | `created_at` | timestamp(6) | NOT NULL | | | `field_kind` | character varying | NOT NULL | 10 clinical kinds + 10 id_* identity kinds | | `identity_value` | text | nullable | encrypted; identity kinds routed here, value column nulled | | `name` | character varying | nullable | | | `source_location` | text | NOT NULL | encrypted JSON | | `unit` | character varying | nullable | | | `updated_at` | timestamp(6) | NOT NULL | | | `uploaded_document_id` | bigint | NOT NULL | | | `value` | text | nullable | encrypted (support_unencrypted_data for pre-20260707000000 rows) | ### import_runs Idempotency ledger for bulk records-ingestion runs (legacy EHR extract + Core Chart zip imports), unique per (source, extract_sha256) with phase/counts bookkeeping. - **Status:** **ACTIVE**: Written by Records::CoreChartImporter:82 via Records::CoreChartZipImporter from admin/core_chart_imports_controller.rb:13 (env-gated: DEPLOY_ENV=staging or RECORDS_IMPORT_ALLOWED=1, core_chart_zip_importer.rb:18-20) and by Records::ExtractImporter:67 via lib/tasks/records.rake ops tasks; find_or_create_by! on (source, extract_sha256) is the production dedupe read - **Spec ref:** §3.5 Previous Charts (records ingestion): 🟡 PARTIAL - **Notable indexes:** `UNIQUE idx_import_runs_source_extract (source, extract_sha256)` 12 columns: | Column | Type | Null | Note | | ---------------- | ----------------- | -------- | ------------------------------------------------ | | `id` | bigint | NOT NULL | | | `patient_id` | bigint | nullable | FK patients; nullable for multi-patient extracts | | `source` | character varying | NOT NULL | | | `status` | character varying | NOT NULL | default 'running'; running\|completed\|failed | | `phase` | character varying | NOT NULL | default 'started' | | `extract_sha256` | character varying | NOT NULL | unique per source: run idempotency key | | `counts` | jsonb | NOT NULL | default {} | | `metadata` | jsonb | NOT NULL | default {}; carries failure error_class on fail! | | `started_at` | timestamp(6) | NOT NULL | | | `finished_at` | timestamp(6) | nullable | | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### phi_access_logs Append-only consent-scoped audit log of every PHI issue/read/denial (authorization downloads, wearable syncs, waiver verifies). - **Status:** **ACTIVE**: Written via PhiAccess.log (app/lib/phi_access.rb:10) from app/controllers/authorization_base_controller.rb:108-124, connect_consents_controller.rb:193, wearable sync jobs; read in production by app/jobs/authorization_token_reap_job.rb:16 - **Spec ref:** §3.5 Previous Charts (records ingestion): 🟡 PARTIAL - **Encryption:** none (stores ip_hash/user_agent_hash, not raw values) 14 columns: | Column | Type | Null | Note | | ------------------------ | ----------------- | -------- | ---------------------------------------------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `action` | character varying | NOT NULL | | | `authorization_token_id` | bigint | nullable | FK authorization_tokens; referenced tokens are never reaped | | `consent_id` | bigint | NOT NULL | FK consents | | `created_at` | timestamp(6) | NOT NULL | no updated_at: append-only | | `failure_reason` | character varying | nullable | | | `generation` | bigint | nullable | | | `ip_hash` | character varying | nullable | | | `metadata` | jsonb | NOT NULL | default {} | | `object_key` | character varying | nullable | | | `outcome` | character varying | NOT NULL | success\|denied\|error\|not_found\|integrity_mismatch\|client_disconnect (app/models/phi_access_log.rb:15) | | `purpose` | character varying | NOT NULL | | | `request_id` | character varying | NOT NULL | | | `user_agent_hash` | character varying | nullable | | ### records_pdf_chunks Retrieval-sized text chunks produced by the records PDF digestion pipeline for an uploaded document. - **Status:** **DORMANT**: Written in a production path (patient upload -> app/controllers/patient/uploaded_documents_controller.rb:64 -> ExtractUploadedDocumentJob:48 -> DigestPdfDocumentJob -> DigestDocument) but no production reader outside the pipeline: retrieval/consumption layer not built (only assoc uploaded_document.rb:14 and PHI manifest) - **Spec ref:** §3.5 Previous Charts (records ingestion): 🟡 PARTIAL - **Encryption:** content and metadata via ActiveRecord encrypts (app/models/records/pdf_chunk.rb:10-11) - **Notable indexes:** `UNIQUE (pdf_digestion_run_id, chunk_index)` · `btree (uploaded_document_id, page_numbers) on integer[] column` 11 columns: | Column | Type | Null | Note | | ---------------------- | ------------ | -------- | -------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `pdf_digestion_run_id` | bigint | NOT NULL | FK records_pdf_digestion_runs | | `uploaded_document_id` | bigint | NOT NULL | FK uploaded_documents; validated to match run's document | | `chunk_index` | integer | NOT NULL | | | `page_numbers` | integer[] | NOT NULL | default {} | | `element_indexes` | integer[] | NOT NULL | default {} | | `bounding_box` | jsonb | NOT NULL | default []; array of 4 numbers when present | | `content` | text | NOT NULL | AR-encrypted PHI | | `metadata` | text | NOT NULL | AR-encrypted JSON (attribute :metadata, :json) | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### records_pdf_digestion_runs One parse run of an uploaded records PDF: parser identity, fingerprint dedupe, counts, lifecycle status. - **Status:** **ACTIVE**: Production write+read: patient upload enqueues Records::ExtractUploadedDocumentJob (app/controllers/patient/uploaded_documents_controller.rb:64) -> DigestPdfDocumentJob; DigestDocument reads runs for fingerprint dedupe and status transitions (app/services/records/pdf_digestion/digest_document.rb:91,232) - **Spec ref:** §3.5 Previous Charts (records ingestion): 🟡 PARTIAL - **Notable indexes:** `UNIQUE (fingerprint)` 20 columns: | Column | Type | Null | Note | | ------------------------ | ----------------- | -------- | ------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `uploaded_document_id` | bigint | NOT NULL | FK uploaded_documents | | `status` | character varying | NOT NULL | enum: pending/running/succeeded/failed/superseded (default pending) | | `parser_name` | character varying | NOT NULL | | | `parser_version` | character varying | NOT NULL | | | `parser_options` | jsonb | NOT NULL | default {} | | `source_checksum_sha256` | character varying | NOT NULL | | | `fingerprint` | character varying | NOT NULL | unique; dedupe key (doc checksum + parser identity) | | `page_count` | integer | NOT NULL | default 0 | | `element_count` | integer | NOT NULL | default 0 | | `chunk_count` | integer | NOT NULL | default 0 | | `table_count` | integer | NOT NULL | default 0 | | `output_sha256` | character varying | nullable | | | `failure_code` | character varying | nullable | | | `failure_class` | character varying | nullable | | | `started_at` | timestamp(6) | nullable | | | `finished_at` | timestamp(6) | nullable | | | `metadata` | jsonb | NOT NULL | default {} | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### records_pdf_elements Parser-level layout elements (paragraphs, tables, headers) extracted per page during PDF digestion. - **Status:** **DORMANT**: Written by the production digestion pipeline (same path as records_pdf_chunks) but no production reader outside the pipeline; consumption layer not built - **Spec ref:** §3.5 Previous Charts (records ingestion): 🟡 PARTIAL - **Encryption:** content and metadata via ActiveRecord encrypts (app/models/records/pdf_element.rb:11-12) - **Notable indexes:** `UNIQUE (pdf_digestion_run_id, element_index)` 15 columns: | Column | Type | Null | Note | | ---------------------- | ----------------- | -------- | ------------------------------------------------------ | | `id` | bigint | NOT NULL | | | `pdf_digestion_run_id` | bigint | NOT NULL | FK records_pdf_digestion_runs | | `pdf_page_id` | bigint | NOT NULL | FK records_pdf_pages | | `uploaded_document_id` | bigint | NOT NULL | FK uploaded_documents; validated to match run and page | | `element_index` | integer | NOT NULL | | | `parser_element_id` | integer | nullable | | | `parent_element_index` | integer | nullable | | | `element_type` | character varying | NOT NULL | | | `page_number` | integer | NOT NULL | | | `bounding_box` | jsonb | NOT NULL | default [] | | `content` | text | nullable | AR-encrypted PHI | | `confidence` | numeric(5,4) | nullable | 0..1 | | `metadata` | text | NOT NULL | AR-encrypted JSON | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### records_pdf_pages Per-page geometry and metadata for a PDF digestion run. - **Status:** **DORMANT**: Written by the production digestion pipeline but no production reader outside the pipeline; consumption layer not built - **Spec ref:** §3.5 Previous Charts (records ingestion): 🟡 PARTIAL - **Encryption:** metadata via ActiveRecord encrypts (app/models/records/pdf_page.rb:12) - **Notable indexes:** `UNIQUE (pdf_digestion_run_id, page_number)` 9 columns: | Column | Type | Null | Note | | ---------------------- | ------------- | -------- | --------------------------------------------- | | `id` | bigint | NOT NULL | | | `pdf_digestion_run_id` | bigint | NOT NULL | FK records_pdf_digestion_runs | | `uploaded_document_id` | bigint | NOT NULL | FK uploaded_documents; validated to match run | | `page_number` | integer | NOT NULL | | | `width_points` | numeric(10,3) | nullable | | | `height_points` | numeric(10,3) | nullable | | | `metadata` | text | NOT NULL | AR-encrypted JSON | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### source_citations Links extracted chart facts (polymorphic citable) back to the source uploaded_document and page numbers. - **Status:** **ACTIVE**: Written by Records::ExtractImporter, reached in prod via Records::ExtractUploadedDocumentJob enqueued from app/controllers/patient/uploaded_documents_controller.rb:64; read by app/services/ehr/integration/labs.rb and app/lib/ehr/phi_manifest.rb. - **Spec ref:** §3.5 Previous Charts (records ingestion): 🟡 PARTIAL - **Encryption:** locator: Rails encrypts (support_unencrypted_data, app/models/source_citation.rb:25) - **Notable indexes:** `UNIQUE idx_source_citations_one_per_doc (citable_type, citable_id, uploaded_document_id)` · `idx_source_citations_reverse (uploaded_document_id, citable_type)` 8 columns: | Column | Type | Null | Note | | ---------------------- | ----------------- | -------- | --------------------------- | | `id` | bigint | NOT NULL | | | `citable_type` | character varying | NOT NULL | polymorphic with citable_id | | `citable_id` | bigint | NOT NULL | | | `uploaded_document_id` | bigint | NOT NULL | FK uploaded_documents | | `pages` | integer[] | NOT NULL | default {} | | `locator` | text | nullable | encrypted | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### uploaded_documents GCS-backed patient document store: records/lab PDFs, CCDs, Function Health exports, sealed agreement packets, and ID photos (license/passport) with identity-match review. - **Status:** **ACTIVE**: Written/read across production paths: patient/uploaded_documents_controller.rb, portal/id_uploads_controller.rb via Identity::AcceptIdPhoto, admin/uploaded_documents_controller.rb, Records::DigestPdfDocumentJob/ExtractUploadedDocumentJob, ProviderAgreementPacket::RenderAndSeal. - **Spec ref:** §3.5 Previous Charts (records ingestion): 🟡 PARTIAL - **Encryption:** none (content stored in GCS, not in-row) - **Notable indexes:** `UNIQUE partial idx_uploaded_docs_on_gcs_object_generation (bucket, object_key, generation) WHERE all three NOT NULL` · `UNIQUE partial index_uploaded_documents_on_sealed_object_key WHERE sealed_object_key NOT NULL` · `partial index_uploaded_documents_on_doc_type WHERE doc_type NOT NULL` · `partial index_uploaded_documents_on_needs_id_review on created_at WHERE review_state='pending' OR (review_state='none' AND accepted_at NOT NULL)` 31 columns: | Column | Type | Null | Note | | ------------------- | ----------------- | -------- | -------------------------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `bucket` | character varying | nullable | GCS bucket | | `byte_size` | bigint | nullable | | | `checksum_sha256` | character varying | nullable | | | `content_sha256` | character varying | nullable | | | `content_type` | character varying | nullable | | | `created_at` | timestamp(6) | NOT NULL | | | `doc_type` | character varying | nullable | license\|passport (ID documents only) | | `filename` | character varying | nullable | | | `fixture_path` | character varying | nullable | REVIEW-HOLD: appears only in structure.sql; zero references in app/, lib/, config/ (see register) | | `generation` | bigint | nullable | GCS object generation | | `generation_back` | bigint | nullable | legacy back-of-ID storage; retirement candidate under the front-only decision | | `identity_matched` | boolean | nullable | | | `identity_score` | numeric(5,4) | nullable | | | `metadata` | jsonb | NOT NULL | default {} | | `object_key` | character varying | nullable | | | `object_key_back` | character varying | nullable | legacy back-of-ID storage; retirement candidate under the front-only decision | | `override_at` | timestamp(6) | nullable | | | `override_by_id` | bigint | nullable | FK users | | `patient_id` | bigint | NOT NULL | FK patients | | `sealed_generation` | bigint | nullable | sealed agreement-packet copy | | `sealed_object_key` | character varying | nullable | | | `source` | character varying | nullable | patient_upload\|ehr_export\|lab_pdf\|ccd\|function_health\|agreement_packet | | `status` | character varying | NOT NULL | received\|parsed\|no_data\|failed; default received | | `threshold_used` | numeric(5,4) | nullable | identity-match threshold at decision time | | `updated_at` | timestamp(6) | NOT NULL | | | `review_state` | character varying | NOT NULL | default none; pending/none drive ID-review queue index | | `review_flags` | jsonb | NOT NULL | default [] | | `reviewed_by_id` | bigint | nullable | FK users | | `reviewed_at` | timestamp(6) | nullable | | | `accepted_at` | timestamp(6) | nullable | | --- ## §4: Labs (12 tables) ### ehr_lab_import_export_runs Consent-scoped audit record of a lab data import/export run (token digest, expiry, output summary). - **Status:** **DORMANT**: Write facade exists (Ehr::Integration::Labs#start_import_export_run! at app/services/ehr/integration/labs.rb:264 and finish_import_export_run!) but grep finds zero callers of either method outside the facade itself: no controller/job/webhook invokes them, and nothing reads the table - **Spec ref:** §4 Labs: section-level table - **Encryption:** output_summary, failure_reason: AR encrypts (non-deterministic) - **Notable indexes:** `idx_on_authorization_token_id_created_at_a71ccc0654 (authorization_token_id, created_at)` · `index_ehr_lab_import_export_runs_on_source_token_digest (source_token_digest)` 14 columns: | Column | Type | Null | Note | | ------------------------ | ----------------- | -------- | --------------------------------------------------------- | | `id` | bigint | NOT NULL | table-level dormant | | `patient_id` | bigint | NOT NULL | FK patients | | `consent_id` | bigint | NOT NULL | FK consents | | `authorization_token_id` | bigint | nullable | FK authorization_tokens | | `status` | character varying | NOT NULL | enum running\|succeeded\|failed\|expired (prefix :export) | | `source_token_digest` | character varying | NOT NULL | | | `runner_job_id` | character varying | nullable | | | `started_at` | timestamp(6) | NOT NULL | | | `finished_at` | timestamp(6) | nullable | | | `expires_at` | timestamp(6) | NOT NULL | | | `output_summary` | text | NOT NULL | encrypted json | | `failure_reason` | text | nullable | encrypted | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### lab_orders Doctor-ordered lab panel order (draft->ordered->result_received->reviewed lifecycle). - **Status:** **DORMANT**: Read paths exist (app/services/ehr/integration/labs.rb:122,377; clinic scoping app/controllers/application_controller.rb:366-368), but nothing creates LabOrders in production: no LabOrder.create outside tests - **Spec ref:** §4.1 Order Labs: 🟡 PARTIAL 8 columns: | Column | Type | Null | Note | | -------------------- | ----------------- | -------- | --------------------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `created_at` | timestamp(6) | NOT NULL | | | `ordering_doctor_id` | bigint | NOT NULL | FK users | | `panels` | jsonb | NOT NULL | default [] | | `patient_id` | bigint | NOT NULL | FK patients | | `rationale` | text | nullable | | | `status` | character varying | NOT NULL | enum draft\|ordered\|result_received\|reviewed\|abnormal\|deferred, default draft | | `updated_at` | timestamp(6) | NOT NULL | | ### lab_results Result values for an internal LabOrder (distinct from imported_lab_results used by Function Health import). - **Status:** **DORMANT**: Read/merged into EHR lab review lists (app/services/ehr/integration/labs.rb:384) but no production writer: all live lab data flows through imported_lab_results (app/services/workflows/import_lab_results.rb:65); dormant with its parent lab_orders - **Spec ref:** §4.1 Order Labs: 🟡 PARTIAL 7 columns: | Column | Type | Null | Note | | -------------- | ----------------- | -------- | --------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `collected_at` | timestamp(6) | nullable | | | `created_at` | timestamp(6) | NOT NULL | | | `lab_order_id` | bigint | NOT NULL | FK lab_orders | | `status` | character varying | NOT NULL | enum received\|parsed\|abnormal\|normal, default received | | `updated_at` | timestamp(6) | NOT NULL | | | `values` | jsonb | NOT NULL | default []; reserved word, quoted in SQL | ### biomarker_crosswalks Approved mapping from Quest biomarker codes to LOINC codes / canonical keys / units, used to normalize imported lab results. - **Status:** **REFERENCE**: Seed-maintained reference data (db/seeds/biomarker_crosswalks.rb:47: no runtime writes by design); read in the production lab import path: BiomarkerCrosswalk.lookup! at app/services/lab_import/function_health_importer.rb:94 and function_health_extractor.rb:92 (4.4 Function Health import is EXISTS) - **Spec ref:** §4.2 Quest Diagnostics integration: 🟡 PARTIAL - **Notable indexes:** `UNIQUE idx_biomarker_crosswalks_identity (quest_biomarker_code, loinc_code, default_unit, method)` 11 columns: | Column | Type | Null | Note | | ---------------------- | ----------------- | -------- | ------------------------------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `canonical_key` | character varying | NOT NULL | | | `created_at` | timestamp(6) | NOT NULL | | | `default_unit` | character varying | nullable | | | `display_name` | character varying | NOT NULL | | | `loinc_code` | character varying | NOT NULL | | | `metadata` | jsonb | NOT NULL | | | `method` | character varying | nullable | disambiguation axis alongside unit | | `quest_biomarker_code` | character varying | NOT NULL | join key to biomarker_review_items | | `review_status` | character varying | NOT NULL | enum: approved, needs_review, retired; default approved; only approved rows resolve lookups | | `updated_at` | timestamp(6) | NOT NULL | | ### biomarker_review_items Review queue for unknown/ambiguous Quest biomarker codes encountered during lab import (one open row per code+unit+method). - **Status:** **DORMANT**: Written in production via BiomarkerReviewItem.enqueue! from BiomarkerCrosswalk.lookup! failures (app/models/biomarker_crosswalk.rb:24,38), but no controller/job/console surface reads or works the queue: write-only ledger awaiting a review UI - **Spec ref:** §4.2 Quest Diagnostics integration: 🟡 PARTIAL - **Notable indexes:** `idx_biomarker_review_items_lookup (quest_biomarker_code, observed_unit, observed_method, status): non-unique; open-row dedupe done in Ruby via find_or_create_by (NULL-matching)` 9 columns: | Column | Type | Null | Note | | ---------------------- | ----------------- | -------- | ----------------------------------------------------- | | `id` | bigint | NOT NULL | | | `created_at` | timestamp(6) | NOT NULL | | | `metadata` | jsonb | NOT NULL | | | `observed_method` | character varying | nullable | | | `observed_name` | character varying | nullable | | | `observed_unit` | character varying | nullable | | | `quest_biomarker_code` | character varying | NOT NULL | loose FK -> biomarker_crosswalks.quest_biomarker_code | | `status` | character varying | NOT NULL | enum: open, reviewed, ignored; default open | | `updated_at` | timestamp(6) | NOT NULL | | ### function_health_account_bindings One-per-patient binding of a RonanRX patient to a Function Health member account, anchored to a function_health_account_import consent. - **Status:** **ACTIVE**: Written by Workflows::VerifyFunctionHealthAccountBinding inside FunctionHealthImportJob (app/jobs/function_health_import_job.rb:15 via Workflows::IngestFunctionHealthBundle), enqueued from app/controllers/api/v1/function_health_imports_controller.rb:52; read on every live import - **Spec ref:** §4.4 Function Health lab import: 🟢 EXISTS - **Encryption:** external_member_id (Rails Active Record encrypts, app/models/function_health_account_binding.rb:9) - **Notable indexes:** `UNIQUE idx_function_health_bindings_patient (patient_id): one binding per patient` 7 columns: | Column | Type | Null | Note | | -------------------- | ------------ | -------- | -------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `patient_id` | bigint | NOT NULL | FK patients; unique | | `consent_id` | bigint | NOT NULL | FK consents; must be kind=function_health_account_import | | `external_member_id` | text | NOT NULL | AR-encrypted; immutable after create | | `bound_at` | timestamp(6) | NOT NULL | | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### function_health_resource_sync_states Per-binding, per-resource high-water mark for Function Health live sync (last accepted snapshot ordering, used to reject stale fetches). - **Status:** **ACTIVE**: Upserted via binding.function_health_resource_sync_states in app/services/workflows/ingest_function_health_bundle.rb:209 and app/services/workflows/upsert_function_health_resource.rb:48, both run inside FunctionHealthImportJob enqueued from api/v1/function_health_imports_controller.rb:52; stale_snapshot? read gates every upsert - **Spec ref:** §4.4 Function Health lab import: 🟢 EXISTS - **CHECK constraints:** `fh_sync_states_resource_name_valid: resource_name IN (profile, labs, clinician_notes, personalized_recommendations, biological_age, bmi)` - **Notable indexes:** `UNIQUE idx_fh_sync_states_unique_resource (function_health_account_binding_id, resource_name)` 8 columns: | Column | Type | Null | Note | | ------------------------------------ | ----------------- | -------- | ---------------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `function_health_account_binding_id` | bigint | NOT NULL | FK function_health_account_bindings | | `import_connection_attempt_id` | bigint | NOT NULL | FK import_connection_attempts; must be function_health_live and same patient | | `resource_name` | character varying | NOT NULL | enum of 6 FH resources | | `fetched_at` | timestamp(6) | NOT NULL | | | `attempt_started_at` | timestamp(6) | NOT NULL | denormalized copy of attempt.started_at for snapshot ordering | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### function_health_source_records Encrypted content-addressed store of raw Function Health non-lab resource payloads (profile, clinician notes, recommendations, biological age, BMI) with keyed digests for dedupe and source-deletion tracking. - **Status:** **ACTIVE**: Written by Workflows::UpsertFunctionHealthResource (app/services/workflows/upsert_function_health_resource.rb) inside FunctionHealthImportJob from api/v1/function_health_imports_controller.rb:52; active_records scope read during ingest/dedupe - **Spec ref:** §4.4 Function Health lab import: 🟢 EXISTS - **Encryption:** external_source_id and payload_json (Rails Active Record encrypts, app/models/function_health_source_record.rb:22-23); source_key_digest/content_digest are keyed digests, not plaintext - **CHECK constraints:** `fh_source_records_deletion_coherent: status=active <=> deleted_at IS NULL; status=source_deleted <=> deleted_at IS NOT NULL` · `fh_source_records_digest_version_valid: digest_version = 1` · `fh_source_records_resource_type_valid: resource_type IN (profile_membership, clinician_note, personalized_recommendation, biological_age, bmi)` · `fh_source_records_schema_version_valid: schema_version = 1` · `fh_source_records_status_valid: status IN (active, source_deleted)` - **Notable indexes:** `UNIQUE idx_fh_source_records_unique_source (binding_id, resource_type, digest_version, source_key_digest)` · `partial idx_fh_source_records_active_read (binding_id, resource_type, source_updated_at) WHERE status='active'` 18 columns: | Column | Type | Null | Note | | ------------------------------------ | ----------------- | -------- | ---------------------------------------- | | `id` | bigint | NOT NULL | | | `function_health_account_binding_id` | bigint | NOT NULL | FK function_health_account_bindings | | `consent_id` | bigint | NOT NULL | FK consents | | `last_import_connection_attempt_id` | bigint | NOT NULL | FK import_connection_attempts | | `resource_type` | character varying | NOT NULL | enum of 5 non-lab FH resource types | | `status` | character varying | NOT NULL | default 'active'; active\|source_deleted | | `schema_version` | integer | NOT NULL | default 1; CHECK pinned to 1 | | `digest_version` | integer | NOT NULL | default 1; CHECK pinned to 1 | | `source_key_digest` | character varying | NOT NULL | 64-hex keyed identity digest | | `content_digest` | character varying | NOT NULL | 64-hex keyed content digest | | `external_source_id` | text | nullable | AR-encrypted | | `payload_json` | text | NOT NULL | AR-encrypted raw payload | | `source_created_at` | timestamp(6) | nullable | | | `source_updated_at` | timestamp(6) | nullable | | | `fetched_at` | timestamp(6) | NOT NULL | | | `deleted_at` | timestamp(6) | nullable | set only when status=source_deleted | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### import_connection_attempts Ledger of external import attempts (Function Health PDF/live, Oura) with status lifecycle, lease/credential handoff fields for the live FH pipeline, and a safe-metadata contract. - **Status:** **ACTIVE**: Created by Workflows::EnqueueFunctionHealthImport from api/v1/function_health_imports_controller.rb:52 and patient/function_health_connections_controller.rb; reaped every minute by FunctionHealthImportReapJob (config/recurring.yml function_health_import_reap); read by ImportConnections::SourceStatus for patient UI - **Spec ref:** §4.4 Function Health lab import: 🟢 EXISTS - **Encryption:** function_health_credential (Rails Active Record encrypts, app/models/import_connection_attempt.rb:24); request_ip_hash/request_user_agent_hash store hashes, not plaintext - **Notable indexes:** `partial UNIQUE idx_import_attempts_unique_authorization_token (authorization_token_id) WHERE NOT NULL` · `partial idx_import_attempts_lease_expiry (lease_expires_at) WHERE source='function_health_live' AND status IN (started,queued,running) AND lease_expires_at NOT NULL` · `partial idx_import_attempts_credential_expiry (credential_expires_at) with same live-active predicate` · `partial idx_import_attempts_legacy_started_at (started_at) WHERE live 'started' with no lease/credential` · `partial index_import_connection_attempts_on_failed_at (failed_at) WHERE NOT NULL` 21 columns: | Column | Type | Null | Note | | ---------------------------- | ----------------- | -------- | --------------------------------------------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `completed_at` | timestamp(6) | nullable | | | `consent_id` | bigint | nullable | FK consents | | `created_at` | timestamp(6) | NOT NULL | | | `error_code` | character varying | nullable | | | `failed_at` | timestamp(6) | nullable | | | `metadata` | jsonb | NOT NULL | default {}; sanitized via FunctionHealth::ImportMetadataContract safe count keys/warning codes | | `patient_id` | bigint | NOT NULL | FK patients | | `source` | character varying | NOT NULL | enum function_health_pdf\|function_health_live\|oura | | `started_at` | timestamp(6) | NOT NULL | | | `status` | character varying | NOT NULL | default 'started'; started\|queued\|running\|succeeded\|partial\|failed | | `updated_at` | timestamp(6) | NOT NULL | | | `authorization_token_id` | bigint | nullable | FK authorization_tokens; required + unique for function_health_live | | `idempotency_key` | character varying | nullable | required for function_health_live | | `lease_expires_at` | timestamp(6) | nullable | live worker lease; reaped by FunctionHealthImportReapJob | | `lease_token` | character varying | nullable | | | `function_health_credential` | text | nullable | AR-encrypted short-lived FH credential; nulled on reap/finalize (reap_stale_function_health_import.rb:47) | | `credential_expires_at` | timestamp(6) | nullable | | | `request_id` | character varying | nullable | originating HTTP request id (function_health_imports_controller.rb:57) | | `request_ip_hash` | character varying | nullable | hashed request IP; replayed into job telemetry (function_health_import_job.rb:32) | | `request_user_agent_hash` | character varying | nullable | hashed UA (function_health_import_job.rb:33) | ### imported_lab_panels A patient-imported lab panel (Function Health import or PDF upload) with idempotency fingerprint and explicit as-reported provenance label; parent of imported_lab_results. - **Status:** **ACTIVE**: Written by Workflows::ImportLabResults from Workflows::IngestFunctionHealthBundle:100 (FunctionHealthImportJob) and the PDF import path; read in prod by doctor/cases_controller.rb:43, patient/imported_labs_controller.rb:10, api/mobile/v1/labs_controller.rb:10, Ehr::Integration::Labs - **Spec ref:** §4.4 Function Health lab import: 🟢 EXISTS - **Notable indexes:** `UNIQUE idx_imported_lab_panels_idempotency (patient_id, source, import_fingerprint)` 12 columns: | Column | Type | Null | Note | | ---------------------- | ----------------- | -------- | ------------------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `consent_id` | bigint | nullable | FK consents | | `counts` | jsonb | NOT NULL | default {}; draws/values cache via refresh_counts! | | `created_at` | timestamp(6) | NOT NULL | | | `external_ref` | jsonb | NOT NULL | default {} | | `import_fingerprint` | character varying | NOT NULL | unique per (patient, source) | | `imported_at` | timestamp(6) | NOT NULL | | | `patient_id` | bigint | NOT NULL | FK patients | | `provenance_label` | character varying | NOT NULL | default 'patient-imported (Function Health), as-reported, not RonanRX-verified' | | `source` | character varying | NOT NULL | enum function_health\|pdf_upload | | `updated_at` | timestamp(6) | NOT NULL | | | `uploaded_document_id` | bigint | nullable | FK uploaded_documents (pdf_upload source) | ### imported_lab_results One lab draw within an imported panel: jsonb values array plus status, deduped per panel by external_draw_id. - **Status:** **ACTIVE**: Written alongside imported_lab_panels by Workflows::ImportLabResults (production FH import job path); read via panel includes by doctor/patient/mobile controllers and FK'd by ehr_lab_result_reviews (unique partial index idx_ehr_lab_reviews_imported_result) - **Spec ref:** §4.4 Function Health lab import: 🟢 EXISTS - **Notable indexes:** `partial UNIQUE idx_imported_lab_results_unique_external_draw (imported_lab_panel_id, external_draw_id) WHERE external_draw_id NOT NULL` 9 columns: | Column | Type | Null | Note | | ----------------------- | ----------------- | -------- | --------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `collected_at` | timestamp(6) | nullable | | | `created_at` | timestamp(6) | NOT NULL | | | `external_draw_id` | character varying | nullable | unique per panel when present | | `external_ref` | jsonb | NOT NULL | default {} | | `imported_lab_panel_id` | bigint | NOT NULL | FK imported_lab_panels | | `status` | character varying | NOT NULL | default 'received'; received\|parsed\|abnormal\|normal | | `updated_at` | timestamp(6) | NOT NULL | | | `values` | jsonb | NOT NULL | default []; array of clinical values (quoted identifier in SQL) | ### ehr_lab_result_reviews Provider acknowledgement/critical-override review record for exactly one lab result (native or imported). - **Status:** **ACTIVE**: Written and read via Ehr::Integration::Labs (labs.rb:400-439, acknowledge/critical_override transitions) driven by ehr/lab_reviews_controller.rb:91 (config/routes.rb:342-345: index/create/acknowledge) - **Spec ref:** §4.5 Lab follow-up compliance: 🟢 EXISTS - **Encryption:** flag: AR encrypts deterministic (indexed/queried); provider_note, critical_override_reason, review_metadata: AR encrypts (non-deterministic) - **CHECK constraints:** `exactly one of lab_result_id / imported_lab_result_id set (chk_ehr_lab_reviews_one_result)` - **Notable indexes:** `UNIQUE partial idx_ehr_lab_reviews_lab_result (lab_result_id) WHERE NOT NULL` · `UNIQUE partial idx_ehr_lab_reviews_imported_result (imported_lab_result_id) WHERE NOT NULL` · `index_ehr_lab_result_reviews_on_flag_and_acknowledged_at (flag, acknowledged_at): usable because flag is deterministic-encrypted` 15 columns: | Column | Type | Null | Note | | -------------------------- | ----------------- | -------- | ------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `lab_result_id` | bigint | nullable | FK lab_results; XOR with imported_lab_result_id | | `imported_lab_result_id` | bigint | nullable | FK imported_lab_results | | `review_status` | character varying | NOT NULL | default pending_review; transitions include critical_override | | `flag` | text | nullable | deterministic-encrypted severity flag | | `acknowledged_at` | timestamp(6) | nullable | | | `acknowledged_by_id` | bigint | nullable | FK users | | `provider_note` | text | nullable | encrypted | | `patient_notified_at` | timestamp(6) | nullable | | | `critical_override_at` | timestamp(6) | nullable | | | `critical_override_by_id` | bigint | nullable | FK users | | `critical_override_reason` | text | nullable | encrypted | | `review_metadata` | text | NOT NULL | encrypted json | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | --- ## §5: Pharmacy, Protocols & Fulfillment (14 tables) ### formula_masters Named compounding formula (protocol) with a pointer to its current approved version. - **Status:** **ACTIVE**: Managed via app/controllers/admin/formula_versions_controller.rb + Workflows::ApproveFormulaVersion/RetractFormulaVersion; read in prescribing/dose paths (app/services/ehr/integration/prescribing.rb); seeded by FormulationSeeder (7 profiles) - **Spec ref:** §5.1 PROTOCOLS (compounding logic): 🟡 PARTIAL - **Notable indexes:** `unique on slug` 8 columns: | Column | Type | Null | Note | | -------------------- | ----------------- | -------- | ---------------------------------------- | | `id` | bigint | NOT NULL | | | `created_at` | timestamp(6) | NOT NULL | | | `current_version_id` | bigint | nullable | FK formula_versions | | `name` | character varying | NOT NULL | | | `slug` | character varying | NOT NULL | unique | | `status` | character varying | NOT NULL | draft\|active\|archived, default 'draft' | | `therapeutic_class` | character varying | nullable | | | `updated_at` | timestamp(6) | NOT NULL | | ### formula_versions Versioned recipe of a formula master (ingredients, excipients, approval workflow, structured dosing). - **Status:** **ACTIVE**: Written via admin controller + Workflows::ApproveFormulaVersion/RetractFormulaVersion; approved_dosing read in production prescribing/safety/dose paths (app/services/ehr/integration/prescribing.rb, safety_screens.rb, dose_guardrail.rb) - **Spec ref:** §5.1 PROTOCOLS (compounding logic): 🟡 PARTIAL - **CHECK constraints:** `canonical_concentration > 0 OR NULL` - **Notable indexes:** `unique partial idx_formula_versions_one_approved_per_master on (formula_master_id) WHERE approval_status='approved'` · `unique on (formula_master_id, version_number)` 17 columns: | Column | Type | Null | Note | | ------------------------- | ----------------- | -------- | ------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `active_ingredients` | jsonb | NOT NULL | default [] | | `approval_status` | character varying | NOT NULL | draft\|approved\|superseded\|retracted, default 'draft' | | `approved_at` | timestamp(6) | nullable | | | `approved_by_id` | bigint | nullable | FK users | | `change_summary` | text | nullable | | | `created_at` | timestamp(6) | NOT NULL | | | `dosage_form` | character varying | nullable | | | `excipients` | jsonb | NOT NULL | default [] | | `formula_master_id` | bigint | NOT NULL | | | `stability_notes` | text | nullable | | | `supersedes_id` | bigint | nullable | self-FK to prior version | | `updated_at` | timestamp(6) | NOT NULL | | | `version_number` | integer | NOT NULL | | | `canonical_concentration` | numeric(12,6) | nullable | amount per mL for dose->volume conversion | | `concentration_unit` | character varying | nullable | must match unit/mL pattern | | `allowed_dose_steps` | jsonb | NOT NULL | default []; approved dose steps | ### program_formula_mappings Maps a program (+visit_type) to a formula master/version with titration rules: the protocol-to-compound lookup used at prescribing. - **Status:** **REFERENCE**: Seed-maintained reference data (rows written only by db/seeds.rb:96, 2 seeded mappings: no runtime writes by design); read in production prescribing path (app/services/ehr/integration/prescribing.rb:693,733) - **Spec ref:** §5.1 PROTOCOLS (compounding logic): 🟡 PARTIAL - **Notable indexes:** `UNIQUE (program_id, visit_type, formula_master_id)` 8 columns: | Column | Type | Null | Note | | -------------------- | ----------------- | -------- | ------------------- | | `id` | bigint | NOT NULL | | | `program_id` | bigint | NOT NULL | FK programs | | `visit_type` | character varying | NOT NULL | default '' | | `formula_master_id` | bigint | NOT NULL | FK formula_masters | | `formula_version_id` | bigint | NOT NULL | FK formula_versions | | `titration_rules` | jsonb | NOT NULL | default {} | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### programs Clinic program catalog (weight/energy/longevity, 3 seeded) that patients enroll in and formulas map to. - **Status:** **ACTIVE**: Read at signup (app/controllers/api/v1/signups_controller.rb:230) and linq/signup_forward.rb:356; created by db/seeds.rb:20-32 and records/core_chart_bootstrapper.rb:100 - **Spec ref:** §5.1 PROTOCOLS (compounding logic): 🟡 PARTIAL - **Notable indexes:** `UNIQUE (clinic_id, slug)` 9 columns: | Column | Type | Null | Note | | ------------- | ----------------- | -------- | ----------------------------------------------------- | | `id` | bigint | NOT NULL | | | `clinic_id` | bigint | NOT NULL | FK clinics | | `created_at` | timestamp(6) | NOT NULL | | | `description` | text | nullable | | | `name` | character varying | NOT NULL | | | `slug` | character varying | NOT NULL | unique per clinic; 'weight' hardcoded in signup paths | | `status` | character varying | NOT NULL | default 'active'; enum active\|archived | | `template` | jsonb | NOT NULL | default {}; REVIEW-HOLD: no consumer found: document intended use or migrate after full review (see register) | | `updated_at` | timestamp(6) | NOT NULL | | ### compounding_tasks Pharmacy compounding work item (pick list + checklist) per pharmacy order. - **Status:** **PRESERVE**: Completion required. Only verified writers are Workflows::StartCompounding (no production caller found) and FormulationSeeder (seed/demo); pharmacist read UI exists but nothing is verified to populate the queue in production. This is required internal-fulfillment infrastructure to complete, not a dormant/removal candidate. - **Spec ref:** §5.2 Internal Fulfillment Network: 🟡 PARTIAL 9 columns: | Column | Type | Null | Note | | ------------------- | ----------------- | -------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `beyond_use_date` | date | nullable | BUD display column; frozen audit found no writer; pharmacy supplies the value; see DISPLAYED-BUT-NEVER-WRITTEN register | | `checklist` | jsonb | NOT NULL | default [] | | `created_at` | timestamp(6) | NOT NULL | | | `pharmacy_order_id` | bigint | NOT NULL | FK pharmacy_orders | | `pick_list` | jsonb | NOT NULL | default []; rows matched to lot_consumptions by index | | `status` | character varying | NOT NULL | enum queued/in_process/qa_pending/released/rework, default queued | | `technician_id` | bigint | nullable | FK users | | `updated_at` | timestamp(6) | NOT NULL | | ### external_fulfillments External-pharmacy fulfillment sidecar for an issued Rx (print + fax handoff, launch lane). - **Status:** **ACTIVE**: Created in Ehr::Integration::Prescribing (app/services/ehr/integration/prescribing.rb:473) when clinic routes external (Pharmacy::FulfillmentRouting); transitioned by Workflows::MarkFaxedToExternalPharmacy from app/controllers/ehr/prescriptions_controller.rb - **Spec ref:** §5.2 Internal Fulfillment Network: 🟡 PARTIAL - **Notable indexes:** `unique on prescription_id (one sidecar per Rx)` 10 columns: | Column | Type | Null | Note | | ----------------- | ----------------- | -------- | -------------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `prescription_id` | bigint | NOT NULL | | | `faxed_by_id` | bigint | nullable | FK users | | `pharmacy_name` | character varying | NOT NULL | | | `pharmacy_fax` | character varying | nullable | real fax numbers still pending from pharmacies (STATE_PHARMACIES fax: nil) | | `status` | character varying | NOT NULL | pending_handoff\|faxed, default 'pending_handoff' | | `faxed_at` | timestamp(6) | nullable | | | `metadata` | jsonb | NOT NULL | | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### formulations Per-pharmacy-order snapshot of a compounded formulation (internal compounding lane). - **Status:** **PRESERVE**: Completion required. Only verified writer is FormulationSeeder (app/services/formulation_seeder.rb:148, seeds/demo); readers belong to the incomplete internal fulfillment lane. Preserve and complete rather than classifying the product substrate as dormant. - **Spec ref:** §5.2 Internal Fulfillment Network: 🟡 PARTIAL 10 columns: | Column | Type | Null | Note | | -------------------- | ----------------- | -------- | ---------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `active_ingredients` | jsonb | NOT NULL | default [] | | `created_at` | timestamp(6) | NOT NULL | | | `dosage_form` | character varying | nullable | | | `excipients` | jsonb | NOT NULL | default [] | | `feasibility` | character varying | NOT NULL | unknown\|feasible\|feasible_with_change\|infeasible, default 'unknown' | | `formula_version_id` | bigint | nullable | | | `pharmacy_order_id` | bigint | NOT NULL | | | `stability_notes` | character varying | nullable | | | `updated_at` | timestamp(6) | NOT NULL | | ### inventory_lots Pharmacy raw-substance inventory lots with quantity, COA reference and expiry for compounding pick lists. - **Status:** **ACTIVE**: Production write via Workflows::ConsumeLot from Pharmacist::CompoundingTasksController#consume_lot (app/controllers/pharmacist/compounding_tasks_controller.rb:22); read in Admin::InventoryLotsController#show; lot creation itself only via FormulationSeeder/console - **Spec ref:** §5.2 Internal Fulfillment Network: 🟡 PARTIAL - **CHECK constraints:** `chk_inventory_lots_nonnegative_quantity: quantity_on_hand >= 0` · `chk_inventory_lots_status_enum: status IN (available, depleted, quarantined, expired)` - **Notable indexes:** `unique index_inventory_lots_on_substance_name_and_lot_number (substance_name, lot_number)` 11 columns: | Column | Type | Null | Note | | ------------------ | ----------------- | -------- | ----------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `coa_reference` | character varying | nullable | | | `created_at` | timestamp(6) | NOT NULL | | | `expiration_date` | date | nullable | | | `lot_number` | character varying | NOT NULL | unique per substance_name | | `quantity_on_hand` | numeric(12,4) | nullable | CHECK >= 0 | | `status` | character varying | NOT NULL | enum available\|quarantined\|depleted\|expired, default available | | `substance_name` | character varying | NOT NULL | | | `supplier_name` | character varying | nullable | | | `unit` | character varying | nullable | | | `updated_at` | timestamp(6) | NOT NULL | | ### label_proofs Draft/approved label text (primary label, auxiliary labels, insert) for a compounding task; feeds BUD-printable labels view. - **Status:** **PRESERVE**: Completion required. Only verified writer is Workflows::CompleteCompounding (app/services/workflows/complete_compounding.rb:24; test-helper demo journeys); label-print/read surfaces exist. Preserve and complete with the internal fulfillment network. - **Spec ref:** §5.2 Internal Fulfillment Network: 🟡 PARTIAL 8 columns: | Column | Type | Null | Note | | --------------------- | ----------------- | -------- | ----------------------------------- | | `id` | bigint | NOT NULL | | | `auxiliary_labels` | jsonb | NOT NULL | default [] | | `compounding_task_id` | bigint | NOT NULL | FK compounding_tasks | | `created_at` | timestamp(6) | NOT NULL | | | `insert_text` | text | nullable | | | `primary_label_text` | text | nullable | | | `status` | character varying | NOT NULL | enum draft\|approved, default draft | | `updated_at` | timestamp(6) | NOT NULL | | ### lot_consumptions Records which inventory lot supplied each pick-list row of a compounding task (traceability / blast radius). - **Status:** **ACTIVE**: Written via Workflows::ConsumeLot from pharmacist/compounding_tasks_controller.rb:22 (routed POST consume_lot, config/routes.rb:377); read in admin/inventory_lots_controller.rb:12-23, lab/compounding/show.html.erb:29, patient/orders/show.html.erb:197. - **Spec ref:** §5.2 Internal Fulfillment Network: 🟡 PARTIAL - **CHECK constraints:** `quantity_consumed > 0` - **Notable indexes:** `UNIQUE (compounding_task_id, pick_list_index): one consumption per pick-list row (idempotency at DB level, not model)` · `(inventory_lot_id, consumed_at) for recall blast-radius queries` 10 columns: | Column | Type | Null | Note | | --------------------- | ----------------- | -------- | --------------------- | | `id` | bigint | NOT NULL | | | `compounding_task_id` | bigint | NOT NULL | FK compounding_tasks | | `consumed_at` | timestamp(6) | NOT NULL | | | `consumed_by_id` | bigint | nullable | FK users (pharmacist) | | `created_at` | timestamp(6) | NOT NULL | | | `inventory_lot_id` | bigint | NOT NULL | FK inventory_lots | | `pick_list_index` | integer | NOT NULL | | | `quantity_consumed` | numeric(12,4) | NOT NULL | | | `unit` | character varying | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### pharmacist_reviews Pharmacist sign-off record on a pharmacy order (approve / hold / clarification) with signature hash and counseling notes. - **Status:** **ACTIVE**: Created by Workflows::ApprovePharmacistReview / HoldPharmacistReview from app/controllers/pharmacist/orders_controller.rb:26,38; read via pharmacist queue includes (app/models/pharmacy_order.rb:29) and doctor/cases_controller.rb:20 - **Spec ref:** §5.2 Internal Fulfillment Network: 🟡 PARTIAL 10 columns: | Column | Type | Null | Note | | ------------------- | ----------------- | -------- | ------------------------------------------------------------------------ | | `id` | bigint | NOT NULL | | | `counseling_notes` | text | nullable | | | `created_at` | timestamp(6) | NOT NULL | | | `hold_reason` | text | nullable | | | `pharmacist_id` | bigint | NOT NULL | FK users | | `pharmacy_order_id` | bigint | NOT NULL | FK pharmacy_orders | | `signature_hash` | character varying | nullable | | | `signed_at` | timestamp(6) | nullable | | | `status` | character varying | NOT NULL | default 'pending'; enum pending\|approved\|held\|clarification_requested | | `updated_at` | timestamp(6) | NOT NULL | | ### pharmacy_orders One fulfillment order per prescription driving the pharmacist review queue and compounding pipeline. - **Status:** **ACTIVE**: Created on Rx sign in app/services/ehr/integration/prescribing.rb:461 (status review_pending); read by app/controllers/pharmacist/queue_controller.rb:7 and admin/audit_controller.rb:36 (Workflows::OpenPharmacyOrder itself remains dormant per round-1 register) - **Spec ref:** §5.2 Internal Fulfillment Network: 🟡 PARTIAL - **Notable indexes:** `UNIQUE (external_id) WHERE external_id IS NOT NULL` 6 columns: | Column | Type | Null | Note | | ----------------- | ----------------- | -------- | --------------------------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `created_at` | timestamp(6) | NOT NULL | | | `external_id` | character varying | nullable | REVIEW-HOLD: no code reader/writer found; preserve pending full review | | `prescription_id` | bigint | NOT NULL | FK prescriptions | | `status` | character varying | NOT NULL | default 'intake_pending'; enum intake_pending\|review_pending\|reviewed\|held\|released | | `updated_at` | timestamp(6) | NOT NULL | | ### quality_releases QA-gate sign-off record for a compounding task before shipment (checklist + signature). - **Status:** **PRESERVE**: Completion required. Only verified writer Workflows::ReleaseQa (app/services/workflows/release_qa.rb:34) is test/demo-reached; downstream shipment/admin readers exist. Preserve and complete, but fix the false `bud_set` attestation before any real release. - **Spec ref:** §5.2 Internal Fulfillment Network: 🟡 PARTIAL 9 columns: | Column | Type | Null | Note | | --------------------- | ----------------- | -------- | -------------------------------------------------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `checklist_results` | jsonb | NOT NULL | default {}; frozen code hard-coded bud_set=>true instead of checking for a supplied value | | `compounding_task_id` | bigint | NOT NULL | FK compounding_tasks | | `created_at` | timestamp(6) | NOT NULL | | | `released_at` | timestamp(6) | nullable | | | `released_by_id` | bigint | nullable | FK users | | `signature_hash` | character varying | nullable | | | `status` | character varying | NOT NULL | enum: pending/released/held (default pending) | | `updated_at` | timestamp(6) | NOT NULL | | ### shipments Shipment record per pharmacy_order for the internal compounding delivery pipeline (ready_check -> scheduled -> dispatched -> delivered). - **Status:** **PRESERVE**: Completion required. Production read surfaces are wired, but the only write path is Workflows::ConfirmShippingReadiness -> Shipment.create! (app/services/workflows/confirm_shipping_readiness.rb:18), which has no verified controller/job/cron caller in the current tree. Preserve and complete the required 5.2/5.4 shipping workflow. - **Spec ref:** §5.2 Internal Fulfillment Network: 🟡 PARTIAL - **Notable indexes:** `unique partial index on external_id WHERE external_id IS NOT NULL` · `index on status` 16 columns: | Column | Type | Null | Note | | ------------------------- | ----------------- | -------- | ------------------------------------------------------------------------------------------------------------------ | | `id` | bigint | NOT NULL | | | `carrier` | character varying | nullable | canonicalized via Shipment::CARRIER_DISPLAY (fedex/ups/usps/dhl/ontrac); workflow hardcodes FedEx | | `cold_chain_required` | boolean | NOT NULL | | | `created_at` | timestamp(6) | NOT NULL | | | `delivered_at` | timestamp(6) | nullable | | | `dispatched_at` | timestamp(6) | nullable | | | `external_id` | character varying | nullable | unique when present; no code populates it in current tree: REVIEW-HOLD (see register) | | `pharmacy_order_id` | bigint | NOT NULL | belongs_to pharmacy_orders (indexed, no DB FK) | | `readiness_confirmed` | boolean | NOT NULL | guarded by DispatchShipment gate shipment_requires_pharmacist_release | | `scheduled_dispatch_date` | date | nullable | | | `service_level` | character varying | nullable | Rails enum: standard \| express \| saturday | | `status` | character varying | NOT NULL | Rails enum: ready_check \| scheduled \| dispatched \| in_transit \| delivered \| exception (default ready_check) | | `temperature_trace` | jsonb | NOT NULL | default []; read at lab/shipments/show.html.erb:4 but nothing writes it: see DISPLAYED-BUT-NEVER-WRITTEN register | | `tracking_events` | jsonb | NOT NULL | appended by DispatchShipment/ConfirmDelivery workflows; tracking numbers are synthetic (VAL-) | | `tracking_number` | character varying | nullable | | | `updated_at` | timestamp(6) | NOT NULL | | --- ## §6: Billing Architecture (0 tables) No table is owned by §6. Billing state lives on `patients.stripe_customer_id` (§1), `prescription_payments` (§3.3), and `patient_program_enrollments` (§1.5); separate Stripe accounts are external systems, not a split charge. The schema has no recurring medication-price lifecycle and no provider-payout or Connect-transfer record. Approved membership economics are a $10 RonanRx share and a flat $29 doctor payout through Stripe Connect; implementation remains bead `m5l2`. --- ## §7: Async Care Loop (10 tables) ### outcome_check_ins Scheduled cadence-based outcome check-ins per program enrollment (scheduled -> sent -> responded/missed, 1-5 score + free text). - **Status:** **ACTIVE**: Written by CheckInDispatcher via OutcomeCheckInJob scheduled daily 9am CT (config/recurring.yml:18) and Workflows::ConfirmDelivery; responses via Workflows::RespondToOutcomeCheckIn from api/mobile/v1/check_ins_controller (routes config/routes.rb:556-557) and admin/check_ins_controller. - **Spec ref:** §7.1 CHECK UPS / Follow-up on Timeline: 🟡 PARTIAL - **Notable indexes:** `UNIQUE (patient_program_enrollment_id, scheduled_at): one check-in per enrollment per day` 11 columns: | Column | Type | Null | Note | | ------------------------------- | ----------------- | -------- | ---------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `cadence` | character varying | NOT NULL | enum weekly\|biweekly\|monthly | | `created_at` | timestamp(6) | NOT NULL | | | `free_text` | text | nullable | | | `patient_program_enrollment_id` | bigint | NOT NULL | FK patient_program_enrollments | | `responded_at` | timestamp(6) | nullable | | | `scheduled_at` | date | nullable | | | `score` | integer | nullable | model-validated 1..5, nullable | | `sent_at` | timestamp(6) | nullable | | | `status` | character varying | NOT NULL | enum scheduled\|sent\|responded\|missed, default scheduled | | `updated_at` | timestamp(6) | NOT NULL | | ### refill_tasks Legacy scheduled refill checkpoint per prescription; patient confirms or holds, but no production consumer starts another compounding run. - **Status:** **PRESERVE**: Redesign required. Only verified creator `Workflows::ConfirmDelivery` is test/demo-reached, and read/update surfaces exist. Lloyd directed preservation of this legacy refill scaffolding; verify production/history evidence and redesign around prescription-specific depletion/eligibility/clinician approval. - **Spec ref:** §7.2 REFILLS RX: 🟡 PARTIAL 7 columns: | Column | Type | Null | Note | | ----------------- | ----------------- | -------- | ---------------------------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `created_at` | timestamp(6) | NOT NULL | | | `due_date` | date | nullable | delivery date + 28 days; this refill interval is separate from BUD | | `hold_reason` | text | nullable | | | `prescription_id` | bigint | NOT NULL | FK prescriptions | | `status` | character varying | NOT NULL | enum: scheduled/confirmed/held/completed/cancelled (default scheduled) | | `updated_at` | timestamp(6) | NOT NULL | | ### events Append-only polymorphic activity log powering timelines, dashboards, and audit trails across all domains. - **Status:** **ACTIVE**: Ubiquitous production read+write: has_many :events on dozens of models, written throughout workflows/controllers, read by timeline/newsfeed and ops dashboards; app/models/event.rb enforces a 70+ EVENTABLE_TYPES allowlist - **Spec ref:** §7.3 Doctor Newsfeed: 🟡 PARTIAL - **Notable indexes:** `expression partial indexes on metadata->>'order_id' / 'recommendation_id' / 'task_id' WHERE key present` · `idx_events_on_type_action_id_created (eventable_type, action, eventable_id, created_at)` 10 columns: | Column | Type | Null | Note | | ---------------- | ----------------- | -------- | ------------------------------------------ | | `id` | bigint | NOT NULL | | | `action` | character varying | NOT NULL | | | `actor_id` | bigint | nullable | polymorphic; actor_type restricted to User | | `actor_type` | character varying | nullable | | | `created_at` | timestamp(6) | NOT NULL | | | `eventable_id` | bigint | NOT NULL | | | `eventable_type` | character varying | NOT NULL | inclusion-validated allowlist in model | | `metadata` | jsonb | NOT NULL | | | `summary` | text | nullable | | | `updated_at` | timestamp(6) | NOT NULL | | ### adverse_event_escalation_entries Append-only, idempotent timeline of escalation actions taken on an adverse event (acknowledgement, patient contact, clinical assessment, paging). - **Status:** **ACTIVE**: Written via Workflows::RecordAdverseEventEscalation from app/jobs/linq_reply_job.rb:58,471, app/jobs/on_call_escalation_job.rb:131, and app/controllers/staff/adverse_event_escalations_controller.rb:32 (routes.rb:157); read by AdverseEvent#acknowledged?/patient_contacted? and the escalation ladder - **Spec ref:** §7.5 Urgent Text Escalation (Patient → Dr / Pharmacist / 911): 🟡 PARTIAL - **Encryption:** details (Rails `encrypts :details`, app/models/adverse_event_escalation_entry.rb:6; JSON attribute); listed in Ehr::PhiManifest (app/lib/ehr/phi_manifest.rb:184) - **Notable indexes:** `UNIQUE idx_ae_escalation_entries_idempotency (idempotency_key)` · `idx_ae_escalation_entries_timeline (adverse_event_id, kind, occurred_at)` 10 columns: | Column | Type | Null | Note | | ------------------ | ----------------- | -------- | ----------------------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `adverse_event_id` | bigint | NOT NULL | FK -> adverse_events | | `actor_type` | character varying | nullable | polymorphic actor, optional (automated entries) | | `actor_id` | bigint | nullable | | | `kind` | character varying | NOT NULL | e.g. clinician_acknowledged, patient_contact_recorded, clinical_assessment_recorded | | `occurred_at` | timestamp(6) | NOT NULL | | | `details` | text | nullable | AR-encrypted JSON | | `idempotency_key` | character varying | NOT NULL | unique; rows readonly after persist | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### adverse_events Registry row for a detected adverse event or urgent safety signal in a Linq patient conversation, with tier, seriousness, and pending-clinical-review classification. - **Status:** **ACTIVE**: Created by Workflows::RecordAdverseEvent from app/controllers/api/v1/linq_controller.rb:827 and app/controllers/api/v1/intake_chat_controller.rb:469 (inbound SMS/chat paths); read by staff escalation UI, Workflows::AssessAdverseEvent, OnCallEscalationJob - **Spec ref:** §7.5 Urgent Text Escalation (Patient → Dr / Pharmacist / 911): 🟡 PARTIAL - **Encryption:** verbatim_message, drug_name, dose_description, lot_number, outcome (Rails `encrypts`, app/models/adverse_event.rb:11-15) - **CHECK constraints:** `product_related_status IN (undetermined, plausibly_related, not_related)` · `seriousness IN (pending_clinical_review, serious, not_serious)` · `tier IN (1,2,3)` - **Notable indexes:** `UNIQUE index_adverse_events_on_linq_event_id (linq_event_id): one AE per triggering event` 23 columns: | Column | Type | Null | Note | | ------------------------------ | ----------------- | -------- | ---------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `linq_conversation_id` | bigint | NOT NULL | FK -> linq_conversations | | `linq_event_id` | bigint | NOT NULL | FK -> linq_events, unique | | `governed_artifact_version_id` | bigint | NOT NULL | FK -> governed_artifact_versions; policy version in force at detection | | `linq_emergency_hold_id` | bigint | nullable | FK -> linq_emergency_holds | | `dosing_error_incident_id` | bigint | nullable | FK -> dosing_error_incidents | | `tier` | integer | NOT NULL | CHECK 1-3 | | `urgency` | character varying | NOT NULL | | | `registry_trigger` | character varying | NOT NULL | | | `detection_source` | character varying | NOT NULL | | | `detector_version` | character varying | NOT NULL | | | `response_template_id` | character varying | nullable | | | `verbatim_message` | text | NOT NULL | AR-encrypted patient message | | `category` | character varying | NOT NULL | default pending_clinical_review | | `drug_name` | text | nullable | AR-encrypted | | `dose_description` | text | nullable | AR-encrypted | | `lot_number` | text | nullable | AR-encrypted | | `outcome` | text | nullable | AR-encrypted | | `seriousness` | character varying | NOT NULL | CHECK enum, default pending_clinical_review | | `product_related_status` | character varying | NOT NULL | CHECK enum, default undetermined | | `detected_at` | timestamp(6) | NOT NULL | | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### dosing_error_incidents Detected dosing-error safety incident (near-miss or tier-two) from a Linq conversation message. - **Status:** **ACTIVE**: Written by Workflows::RecordDosingError (app/services/workflows/record_dosing_error.rb) reached from api/v1/linq_controller.rb:810 and api/v1/intake_chat_controller.rb:452; read by apply_therapy_hold.rb and record_adverse_event.rb - **Spec ref:** §7.5 Urgent Text Escalation (Patient → Dr / Pharmacist / 911): 🟡 PARTIAL - **Encryption:** amounts (Active Record encryption, app/models/dosing_error_incident.rb:10) - **CHECK constraints:** `chk_dosing_error_hold: severity <> 'tier_two' OR linq_emergency_hold_id IS NOT NULL` · `chk_dosing_error_ratio: ratio NULL or > 0` · `chk_dosing_error_severity: near_miss/tier_two` · `chk_dosing_error_unit: NULL or units/ml/mg` - **Notable indexes:** `UNIQUE index_dosing_error_incidents_on_source_message_id: message-level idempotency` 11 columns: | Column | Type | Null | Note | | ------------------------------ | ----------------- | -------- | ----------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `linq_conversation_id` | bigint | NOT NULL | FK linq_conversations | | `governed_artifact_version_id` | bigint | NOT NULL | FK governed_artifact_versions (detector version provenance) | | `linq_emergency_hold_id` | bigint | nullable | FK linq_emergency_holds; required by CHECK when tier_two | | `source_message_id` | character varying | NOT NULL | unique | | `severity` | character varying | NOT NULL | enum near_miss/tier_two | | `ratio` | numeric(10,4) | nullable | | | `unit` | character varying | nullable | units/ml/mg | | `amounts` | text | NOT NULL | AR-encrypted | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### medwatch_reports FDA MedWatch report tracking record spawned from a dosing-error incident or adverse event (due date + submission attestation). - **Status:** **DORMANT**: Written in prod by Workflows::RecordAdverseEvent (called from api/v1/linq_controller.rb:827 and api/v1/intake_chat_controller.rb:469) and Workflows::RecordDosingError, but no production reader/UI exists and Workflows::SubmitMedwatchReport has zero callers (console-only): nothing surfaces or submits the reports. - **Spec ref:** §7.5 Urgent Text Escalation (Patient → Dr / Pharmacist / 911): 🟡 PARTIAL - **Encryption:** report_reference (Rails Active Record encryption, app/models/medwatch_report.rb:6) - **CHECK constraints:** `exactly one of dosing_error_incident_id / adverse_event_id set` · `status in (required, submitted)` · `required => submitted_at/submitted_by_id/report_reference NULL; submitted => all three present and reference non-blank` - **Notable indexes:** `UNIQUE (adverse_event_id)` · `UNIQUE (dosing_error_incident_id)` · `(status, report_due_at): due-report sweep index with no sweep job reading it` 10 columns: | Column | Type | Null | Note | | -------------------------- | ----------------- | -------- | ---------------------------------------------------- | | `id` | bigint | NOT NULL | | | `dosing_error_incident_id` | bigint | nullable | FK dosing_error_incidents; XOR with adverse_event_id | | `status` | character varying | NOT NULL | enum required\|submitted, default required | | `report_due_at` | timestamp(6) | NOT NULL | | | `submitted_at` | timestamp(6) | nullable | | | `submitted_by_id` | bigint | nullable | FK users | | `report_reference` | text | nullable | AR-encrypted | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | | `adverse_event_id` | bigint | nullable | FK adverse_events; XOR with dosing_error_incident_id | ### on_call_escalation_schedules Durable ladder of timed escalation actions per adverse event (live call +5m, ops fallback +10m, ack deadline +15m, patient-contact deadline +1h). - **Status:** **ACTIVE**: Written/executed by OnCallEscalationJob (enqueued from app/models/adverse_event.rb:48) and swept by OnCallEscalationSweepJob every minute (config/recurring.yml:129); OnCall::Dispatch::ADAPTERS = {}, so the paging transport is empty even though the schedule machinery runs. - **Spec ref:** §7.5 Urgent Text Escalation (Patient → Dr / Pharmacist / 911): 🟡 PARTIAL - **CHECK constraints:** `action in (live_call, ops_fallback, acknowledgment_deadline, patient_contact_deadline)` · `completed_at IS NULL OR completed_at >= run_at` - **Notable indexes:** `UNIQUE (adverse_event_id, action)` · `(completed_at, run_at) due-work sweep index` 7 columns: | Column | Type | Null | Note | | ------------------ | ----------------- | -------- | ------------------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `adverse_event_id` | bigint | NOT NULL | FK adverse_events | | `action` | character varying | NOT NULL | enum live_call\|ops_fallback\|acknowledgment_deadline\|patient_contact_deadline | | `run_at` | timestamp(6) | NOT NULL | | | `completed_at` | timestamp(6) | nullable | | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### red_flag_safety_events Clinician-labeled false-negative / severity-undercall events for the Linq emergency-triage detector (eval-corpus seed). - **Status:** **ACTIVE**: Production write via POST staff/red_flag_safety_events (config/routes.rb:155 -> app/controllers/staff/red_flag_safety_events_controller.rb:9 -> Workflows::RecordRedFlagSafetyEvent, which also reads for idempotency); rows immutable (readonly? once persisted); downstream eval-corpus reviewer not built (review_status CHECK-locked to pending_eval_corpus_review) - **Spec ref:** §7.5 Urgent Text Escalation (Patient → Dr / Pharmacist / 911): 🟡 PARTIAL - **CHECK constraints:** `expected_tier between 1 and 4` · `label_kind in (false_negative, severity_undercall)` · `label_source = 'clinician_review' AND labeled_by_id NOT NULL` · `observation coherence: observed_matched=false => observed tier/urgency/trigger all NULL; observed_matched=true => all NOT NULL` · `observed_tier NULL or between 1 and 4` · `review_status = 'pending_eval_corpus_review' (only allowed value)` · `label_source = 'clinician_review' (only allowed value)` - **Notable indexes:** `UNIQUE (idempotency_key)` · `UNIQUE (linq_event_id)` 18 columns: | Column | Type | Null | Note | | ------------------------------ | ----------------- | -------- | ---------------------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `linq_event_id` | bigint | NOT NULL | FK linq_events; unique (one label per event) | | `linq_conversation_id` | bigint | NOT NULL | FK linq_conversations; must own the source event | | `governed_artifact_version_id` | bigint | NOT NULL | FK governed_artifact_versions; must be emergency_triage_registry signed/superseded | | `labeled_by_id` | bigint | nullable | FK users; required by CHECK; same-clinic doctor/pharmacist | | `idempotency_key` | character varying | NOT NULL | unique | | `label_source` | character varying | NOT NULL | CHECK-locked to clinician_review | | `label_kind` | character varying | NOT NULL | false_negative \| severity_undercall | | `expected_tier` | integer | NOT NULL | 1..4 | | `observed_matched` | boolean | NOT NULL | | | `observed_tier` | integer | nullable | 1..4 when matched | | `observed_urgency` | character varying | nullable | | | `observed_registry_trigger` | character varying | nullable | | | `observed_detection_source` | character varying | NOT NULL | | | `detector_version` | character varying | NOT NULL | | | `review_status` | character varying | NOT NULL | CHECK-locked to pending_eval_corpus_review (default) | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### support_messages Patient support inbox messages (inbound from patient, outbound staff replies) with triage kind/status. - **Frozen f246 status:** **ACTIVE** through portal and mobile writers plus CheckInDispatcher. On both current refs, the portal writer remains; the mobile API, `Workflows::ReplyToSupportMessage`, and `CareTeamReplyNudgeJob` are absent. `Agents::PatientSupport` remains unwired. - **Spec ref:** §7.5 Urgent Text Escalation (Patient → Dr / Pharmacist / 911): 🟡 PARTIAL - **Encryption:** the frozen f246 model encrypted `body` with `support_unencrypted_data`; that encryption path is absent from both current refs, where the body is plaintext. 9 columns: | Column | Type | Null | Note | | --------------- | ----------------- | -------- | ----------------------------------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `body` | text | nullable | encrypted | | `created_at` | timestamp(6) | NOT NULL | | | `direction` | character varying | NOT NULL | enum inbound\|outbound | | `handled_by_id` | bigint | nullable | FK users (staff) | | `kind` | character varying | NOT NULL | enum general\|side_effect_report\|refill_question\|shipping_question\|check_in; default general | | `patient_id` | bigint | NOT NULL | FK patients | | `status` | character varying | NOT NULL | enum new\|read\|replied\|escalated; default new | | `updated_at` | timestamp(6) | NOT NULL | | --- ## §8: App Data & Health Profile (17 tables) ### analytics_idempotency_keys TTL'd dedupe claims for inbound analytics/webhook events (scope + SHA-256 key hash), claimed atomically via unique-index race. - **Status:** **ACTIVE**: Claimed via Analytics::Idempotency.claim in app/controllers/api/event_controller.rb:19 and app/services/whoop/webhook.rb (production webhook paths); AnalyticsIdempotencyKey.claim_hash self-reaps expired rows - **Spec ref:** §8.1 APP DATA COLLECTION (Withings, Whoop, Oura, HealthKit): 🟡 PARTIAL - **Encryption:** none (key_hash is a 64-char SHA-256; no raw payload stored) - **Notable indexes:** `UNIQUE index_analytics_idempotency_keys_on_scope_and_key_hash (scope, key_hash)` · `index on expires_at for reaping` 6 columns: | Column | Type | Null | Note | | ------------ | ----------------- | -------- | -------------------------- | | `id` | bigint | NOT NULL | | | `scope` | character varying | NOT NULL | format /[a-z0-9:_-]{1,80}/ | | `key_hash` | character varying | NOT NULL | 64-char SHA-256 | | `expires_at` | timestamp(6) | NOT NULL | | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### dose_logs Patient-logged medication doses (iOS app) with injection site and pain score. - **Frozen f246 status:** **ACTIVE** through mobile CRUD keyed by `client_uuid`. **Current refs:** table, model, controller, and route are absent from both main and staging. - **Spec ref:** §8.1 APP DATA COLLECTION (Withings, Whoop, Oura, HealthKit): 🟡 PARTIAL - **Encryption:** notes (Active Record encryption, app/models/dose_log.rb:13) - **CHECK constraints:** `chk_dose_logs_dose_amount: dose_amount > 0` · `chk_dose_logs_injection_site: NULL or one of abdomen_upper_left/right, abdomen_lower_left/right, thigh_left/right, upper_arm_left/right, glute_left/right, other` · `chk_dose_logs_pain_score: NULL or 0..10` · `chk_dose_logs_status: taken/skipped/missed` - **Notable indexes:** `UNIQUE index_dose_logs_on_client_uuid (client_uuid): client-side idempotency key` 18 columns: | Column | Type | Null | Note | | ----------------- | ----------------- | -------- | ----------------------------------- | | `id` | bigint | NOT NULL | | | `patient_id` | bigint | NOT NULL | FK patients | | `regimen_item_id` | bigint | NOT NULL | FK regimen_items | | `client_uuid` | uuid | NOT NULL | unique, mobile idempotency key | | `tz_identifier` | character varying | NOT NULL | | | `logged_at` | timestamp(6) | NOT NULL | | | `logged_on` | date | NOT NULL | | | `dose_amount` | numeric(12,4) | NOT NULL | CHECK > 0 | | `dose_unit` | character varying | NOT NULL | | | `compound_name` | character varying | NOT NULL | | | `strength` | character varying | nullable | | | `injection_site` | character varying | nullable | CHECK enum, 11 sites | | `pain_score` | integer | nullable | CHECK 0..10 | | `notes` | text | nullable | AR-encrypted | | `status` | character varying | NOT NULL | taken/skipped/missed, default taken | | `deleted_at` | timestamp(6) | nullable | soft delete | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### healthkit_batch_receipts Idempotency receipt per HealthKit upload batch from the iOS app: stores the per-sample results array so duplicate batch replays return the original response. - **Frozen f246 status:** **ACTIVE** through `Workflows::IngestHealthkitSamples` and the mobile HealthKit batches controller. **Current refs:** table and ingest path are absent from both main and staging. - **Spec ref:** §8.1 APP DATA COLLECTION (Withings, Whoop, Oura, HealthKit): 🟡 PARTIAL - **Encryption:** none (idempotency_key_digest is a sha256 of the client key, not encryption) - **CHECK constraints:** `chk_healthkit_batch_receipts_digest: idempotency_key_digest ~ ^[0-9a-f]{64}$` · `chk_healthkit_batch_receipts_results_array: jsonb_typeof(results) = 'array'` - **Notable indexes:** `UNIQUE idx_healthkit_batch_receipts_idempotency (patient_id, idempotency_key_digest)` 7 columns: | Column | Type | Null | Note | | ------------------------ | --------------------- | -------- | -------------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `patient_id` | bigint | NOT NULL | FK patients | | `wearable_connection_id` | bigint | NOT NULL | FK wearable_connections; must belong to same patient | | `idempotency_key_digest` | character varying(64) | NOT NULL | sha256 hex of client idempotency key | | `results` | jsonb | NOT NULL | default []; per-sample accept/reject outcomes replayed on duplicate submit | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### healthkit_samples Raw HealthKit samples uploaded from the iOS app, deduped by (patient, hk_uuid); raw detail retained ~180 days (HEALTHKIT_RAW_RETENTION_DAYS) then pruned once covered by wearable_daily_metrics rollups. - **Frozen f246 status:** **ACTIVE** through HealthKit ingest, retention, and rollup jobs. **Current refs:** table, services, jobs, and route are absent from both main and staging. - **Spec ref:** §8.1 APP DATA COLLECTION (Withings, Whoop, Oura, HealthKit): 🟡 PARTIAL - **CHECK constraints:** `chk_healthkit_samples_time_order: end_at >= start_at` - **Notable indexes:** `UNIQUE idx_healthkit_samples_patient_hk_uuid (patient_id, hk_uuid)` · `idx_healthkit_samples_patient_deleted (patient_id, deleted_at)` 14 columns: | Column | Type | Null | Note | | ------------------------ | ----------------- | -------- | ----------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `patient_id` | bigint | NOT NULL | FK patients | | `wearable_connection_id` | bigint | NOT NULL | FK wearable_connections; must be a healthkit connection of same patient | | `sample_type` | character varying | NOT NULL | | | `value` | numeric(18,6) | NOT NULL | | | `unit` | character varying | NOT NULL | | | `start_at` | timestamp(6) | NOT NULL | | | `end_at` | timestamp(6) | NOT NULL | | | `hk_uuid` | uuid | NOT NULL | HealthKit sample UUID; unique per patient | | `source_bundle_id` | character varying | NOT NULL | | | `recorded_at` | timestamp(6) | NOT NULL | | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | | `deleted_at` | timestamp(6) | nullable | soft delete (active scope) | ### intake_logs Patient water/protein/fiber intake entries from the iOS app (or derived from HealthKit dietary samples), client-UUID idempotent, soft-deletable. - **Frozen f246 status:** **ACTIVE** through mobile CRUD, HealthKit ingestion, and Today presentation. **Current refs:** table and mobile paths are absent from both main and staging. - **Spec ref:** §8.1 APP DATA COLLECTION (Withings, Whoop, Oura, HealthKit): 🟡 PARTIAL - **CHECK constraints:** `chk_intake_logs_amount: amount > 0` · `chk_intake_logs_kind: kind IN (water, protein, fiber)` · `chk_intake_logs_source: source IN (app, healthkit)` · `chk_intake_logs_unit: unit IN (g, ml, oz)` - **Notable indexes:** `UNIQUE index_intake_logs_on_client_uuid (client_uuid)` · `partial UNIQUE idx_intake_logs_unique_source_ref (source_ref) WHERE NOT NULL` 14 columns: | Column | Type | Null | Note | | --------------- | ----------------- | -------- | ------------------------------------------------------------------ | | `id` | bigint | NOT NULL | | | `patient_id` | bigint | NOT NULL | FK patients | | `client_uuid` | uuid | NOT NULL | client-generated idempotency id, globally unique | | `tz_identifier` | character varying | NOT NULL | | | `kind` | character varying | NOT NULL | water\|protein\|fiber | | `amount` | numeric(12,4) | NOT NULL | > 0 | | `unit` | character varying | NOT NULL | g\|ml\|oz | | `consumed_at` | timestamp(6) | NOT NULL | | | `logged_on` | date | NOT NULL | | | `source` | character varying | NOT NULL | default 'app'; app\|healthkit | | `source_ref` | character varying | nullable | required + unique when source=healthkit (dedupe against HK sample) | | `deleted_at` | timestamp(6) | nullable | soft delete | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### pending_mobile_oauth_authorizations Staging record holding wearable OAuth tokens (Withings/Oura) between web OAuth callback and iOS app finalization via completion code. - **Frozen f246 status:** **ACTIVE** as the mobile wearable-OAuth bridge. **Current refs:** table and bridge workflow are absent from both main and staging. - **Spec ref:** §8.1 APP DATA COLLECTION (Withings, Whoop, Oura, HealthKit): 🟡 PARTIAL - **Encryption:** access_token, refresh_token, provider_user_id: Rails Active Record Encryption (app/models/pending_mobile_oauth_authorization.rb:10-12) - **Notable indexes:** `UNIQUE (authorization_token_id)` 15 columns: | Column | Type | Null | Note | | ------------------------ | ----------------- | -------- | -------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `patient_id` | bigint | NOT NULL | FK patients | | `consent_id` | bigint | NOT NULL | FK consents | | `authorization_token_id` | bigint | NOT NULL | FK authorization_tokens; unique; holds digest of the completion code | | `provider` | character varying | NOT NULL | withings \| oura | | `access_token` | text | nullable | encrypted | | `refresh_token` | text | nullable | encrypted | | `provider_user_id` | text | nullable | encrypted | | `token_expires_at` | timestamp(6) | NOT NULL | provider access-token expiry | | `scopes` | jsonb | NOT NULL | default [] | | `token_type` | character varying | nullable | | | `expires_at` | timestamp(6) | NOT NULL | staging-record TTL (COMPLETION_TTL) | | `finalized_at` | timestamp(6) | nullable | | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### scale_measurements Deduped weight measurement from Withings scale / HealthKit ingest, charted into EHR vitals. - **Status:** **ACTIVE**: Written by Withings webhook -> WithingsSyncJob -> Workflows::IngestScaleMeasurements (app/services/withings/webhook.rb:22, app/services/workflows/ingest_scale_measurements.rb:55) and mobile HealthKit ingest (ingest_healthkit_samples.rb:303); read by patient UI (app/controllers/patient/health_integrations_controller.rb:157), Ehr::ScaleVitalsWriter, WeighIn::NudgeDispatcher - **Spec ref:** §8.1 APP DATA COLLECTION (Withings, Whoop, Oura, HealthKit): 🟡 PARTIAL - **Encryption:** weight via ActiveRecord encrypts (app/models/scale_measurement.rb:6; text column) - **Notable indexes:** `UNIQUE (wearable_connection_id, external_group_id)` 13 columns: | Column | Type | Null | Note | | ------------------------ | ----------------- | -------- | ------------------------------------------- | | `id` | bigint | NOT NULL | | | `patient_id` | bigint | NOT NULL | FK patients | | `wearable_connection_id` | bigint | NOT NULL | FK wearable_connections | | `external_group_id` | character varying | NOT NULL | vendor dedupe key; unique per connection | | `device_id` | character varying | nullable | | | `weight` | text | NOT NULL | AR-encrypted decimal string (PHI) | | `measured_at` | timestamp(6) | NOT NULL | | | `status` | character varying | NOT NULL | enum with single value: charted (default) | | `ehr_vital_id` | bigint | nullable | FK ehr_vitals (Ehr::Vital) once charted | | `payload` | jsonb | NOT NULL | default {} | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | | `deleted_at` | timestamp(6) | nullable | soft delete (Workflows::PurgeHealthkitData) | ### side_effect_logs Patient-logged medication side effects (category + 0-10 severity per day) synced from the iOS app. - **Frozen f246 status:** **ACTIVE** through mobile CRUD and Today presentation. **Current refs:** table and mobile paths are absent from both main and staging. - **Spec ref:** §8.1 APP DATA COLLECTION (Withings, Whoop, Oura, HealthKit): 🟡 PARTIAL - **Encryption:** notes encrypted via Rails ActiveRecord::Encryption (encrypts :notes, app/models/side_effect_log.rb:11) - **CHECK constraints:** `chk_side_effect_logs_category: category IN (nausea, vomiting, diarrhea, constipation, abdominal_pain, heartburn_reflux, dizziness, headache, fatigue, injection_site_reaction, other)` · `chk_side_effect_logs_severity: severity BETWEEN 0 AND 10` - **Notable indexes:** `unique partial index idx_side_effect_logs_patient_category_day on (patient_id, category, logged_on) WHERE deleted_at IS NULL (daily-upsert race net)` · `unique index on client_uuid` 12 columns: | Column | Type | Null | Note | | --------------- | ----------------- | -------- | ---------------------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `patient_id` | bigint | NOT NULL | belongs_to patients (indexed, no DB FK) | | `client_uuid` | uuid | NOT NULL | unique; UUIDv5(patient+category+logged_on) from the iOS client for idempotent sync | | `tz_identifier` | character varying | NOT NULL | | | `category` | character varying | NOT NULL | 11-value enum, CHECK-constrained | | `severity` | integer | NOT NULL | 0-10, CHECK-constrained | | `logged_at` | timestamp(6) | NOT NULL | | | `logged_on` | date | NOT NULL | one row per patient+category+day (partial unique index) | | `notes` | text | nullable | AR-encrypted; required when category=other | | `deleted_at` | timestamp(6) | nullable | soft-delete tombstone | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### wearable_connections Per-patient OAuth connection to a wearable provider (oura, whoop, withings, healthkit) with tokens and consent. - **Status:** **ACTIVE**: Written by patient/health_integrations_controller.rb OAuth flows (Withings::Oauth etc.); read by scheduled sync jobs in config/recurring.yml (oura_daily_sync, whoop_daily_sync, withings_daily_sync) and Whoop/Withings webhooks. - **Spec ref:** §8.1 APP DATA COLLECTION (Withings, Whoop, Oura, HealthKit): 🟡 PARTIAL - **Encryption:** access_token, refresh_token: Rails encrypts (app/models/wearable_connection.rb:17-18) - **Notable indexes:** `UNIQUE index_wearable_connections_on_patient_id_and_provider` · `index on (provider, status)` · `index on token_expires_at` 15 columns: | Column | Type | Null | Note | | ------------------ | ----------------- | -------- | ------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `access_token` | text | nullable | encrypted | | `consent_id` | bigint | nullable | FK consents | | `created_at` | timestamp(6) | NOT NULL | | | `external_user_id` | character varying | nullable | provider-side user id | | `last_synced_at` | timestamp(6) | nullable | | | `metadata` | jsonb | NOT NULL | default {} | | `patient_id` | bigint | NOT NULL | FK patients | | `provider` | character varying | NOT NULL | oura\|whoop\|withings\|healthkit | | `refresh_token` | text | nullable | encrypted | | `revoked_at` | timestamp(6) | nullable | | | `scopes` | jsonb | NOT NULL | default [] | | `status` | character varying | NOT NULL | active\|revoked\|membership_inactive\|errored; default active | | `token_expires_at` | timestamp(6) | nullable | | | `updated_at` | timestamp(6) | NOT NULL | | ### wearable_daily_metrics One row per patient/provider/day of normalized wearable metrics (jsonb) with adjunctive-wellness provenance label. - **Status:** **ACTIVE**: Written by Workflows::IngestWearableDailyMetrics from scheduled sync jobs (oura/whoop/withings, config/recurring.yml) and Healthkit::DailyRollup; read by patient/dashboard_controller.rb and patient_graph.rb. - **Spec ref:** §8.1 APP DATA COLLECTION (Withings, Whoop, Oura, HealthKit): 🟡 PARTIAL - **Notable indexes:** `UNIQUE idx_wearable_daily_metrics_patient_provider_date (patient_id, provider, measured_on)` · `idx_wearable_daily_metrics_connection_date (wearable_connection_id, measured_on)` 10 columns: | Column | Type | Null | Note | | ------------------------ | ----------------- | -------- | -------------------------------------------------------------------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `created_at` | timestamp(6) | NOT NULL | | | `measured_on` | date | NOT NULL | | | `metrics` | jsonb | NOT NULL | default {} | | `patient_id` | bigint | NOT NULL | FK patients | | `provenance_label` | character varying | NOT NULL | default 'Oura Ring adjunctive wellness trend, patient baseline, not diagnostic': Oura-specific default even for other providers | | `provider` | character varying | NOT NULL | | | `source_ref` | jsonb | NOT NULL | default {} | | `updated_at` | timestamp(6) | NOT NULL | | | `wearable_connection_id` | bigint | NOT NULL | FK wearable_connections | ### weigh_in_nudges Dedupe/throttle ledger for weekly Linq weigh-in nudges sent to patients with an active Withings connection. - **Status:** **ACTIVE**: WeighInNudgeJob scheduled weekly (config/recurring.yml: '0 10 * * 1 America/Chicago') runs WeighIn::NudgeDispatcher which both reads (dedupe_key/throttle checks) and writes rows before enqueuing LinqReplyJob; delivery_mode comes from Linq::SendGuard so rows may record dry_run in guarded envs. - **Spec ref:** §8.1 APP DATA COLLECTION (Withings, Whoop, Oura, HealthKit): 🟡 PARTIAL - **Notable indexes:** `UNIQUE idx_weigh_in_nudges_dedupe_key` · `idx_weigh_in_nudges_patient_sent_at (patient_id, sent_at) for throttle window` 8 columns: | Column | Type | Null | Note | | ------------------------ | ----------------- | -------- | ------------------------------------------------------------------ | | `id` | bigint | NOT NULL | | | `patient_id` | bigint | NOT NULL | FK patients | | `wearable_connection_id` | bigint | NOT NULL | FK wearable_connections (Withings) | | `sent_at` | timestamp(6) | NOT NULL | | | `dedupe_key` | character varying | NOT NULL | unique; patient+ISO-week | | `delivery_mode` | character varying | NOT NULL | default dry_run; actual mode set from Linq::SendGuard at send time | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### account_email_changes Pending patient account email-change requests with verification token, verified/consumed via emailed link. - **Status:** **ACTIVE**: Written by Accounts::EmailChange.request (app/services/accounts/email_change.rb:91) and read/consumed via app/controllers/accounts_controller.rb:10,26 (routes.rb:168-170 account/email endpoints) - **Spec ref:** §8.2 INFO #3 Full Health Profile: 🟡 PARTIAL - **Encryption:** email (Rails `encrypts :email`, app/models/account_email_change.rb:4); email_lookup_hash is the deterministic lookup surrogate - **Notable indexes:** `UNIQUE index_account_email_changes_on_token_digest (token_digest)` · `index_account_email_changes_on_user_id_and_consumed_at (user_id, consumed_at)` 11 columns: | Column | Type | Null | Note | | ------------------- | ----------------- | -------- | -------------------------------------------- | | `id` | bigint | NOT NULL | | | `user_id` | bigint | NOT NULL | FK -> users | | `email` | text | NOT NULL | AR-encrypted | | `email_lookup_hash` | character varying | NOT NULL | Patient.hash_email of new email for lookup | | `token_digest` | character varying | NOT NULL | unique; digest of emailed verification token | | `expires_at` | timestamp(6) | NOT NULL | | | `verified_at` | timestamp(6) | nullable | | | `consumed_at` | timestamp(6) | nullable | | | `ip_hash` | character varying | nullable | | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### ehr_health_history_reviews Clinician review shell for a patient's full health history submission, holding a projected clinical snapshot + projection fingerprint. - **Status:** **DORMANT**: Write-only: created/updated by Ehr::HealthHistoryProjector (health_history_projector.rb:328) from patient/health_histories_controller.rb:249, but no controller/view reads it: review_status never transitions from pending_review and no review UI exists (grep of app/controllers + app/views finds zero references) - **Spec ref:** §8.2 INFO #3 Full Health Profile: 🟡 PARTIAL - **Encryption:** review_note, clinical_snapshot: AR encrypts (non-deterministic) - **Notable indexes:** `UNIQUE idx_ehr_hh_reviews_on_patient_health_history (patient_health_history_id)` · `idx_ehr_hh_reviews_patient_projected (patient_id, projected_at)` 12 columns: | Column | Type | Null | Note | | --------------------------- | ----------------- | -------- | ------------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `patient_health_history_id` | bigint | NOT NULL | FK patient_health_histories, unique | | `patient_id` | bigint | NOT NULL | FK patients; validated to match health history | | `review_status` | character varying | NOT NULL | enum pending_review\|approved\|needs_follow_up; never transitions in code | | `reviewed_by_id` | bigint | nullable | FK users; never set in code | | `reviewed_at` | timestamp(6) | nullable | never set in code | | `review_note` | text | nullable | encrypted; never set in code | | `clinical_snapshot` | text | NOT NULL | encrypted json projection | | `projected_at` | timestamp(6) | nullable | | | `projection_fingerprint` | character varying | nullable | idempotency key for projector re-runs | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### health_history_nudges Per-patient ledger of 'health profile N% complete' SMS nudges: dedupe floor via unique (patient, nudge_number), 3-lifetime cap and 72h throttle source; recorded even in dry_run mode where LinqEvent cannot dedupe. - **Status:** **ACTIVE**: Written by HealthHistory::NudgeNotifier (app/services/health_history/nudge_notifier.rb:74) inside HealthHistoryNudgeJob, scheduled daily 10:00 America/Chicago in config/recurring.yml (health_history_nudge); read by throttle/cap logic in HealthHistory::NudgeDispatcher - **Spec ref:** §8.2 INFO #3 Full Health Profile: 🟡 PARTIAL - **Notable indexes:** `UNIQUE idx_health_history_nudges_one_per_number (patient_id, nudge_number)` · `UNIQUE index_health_history_nudges_on_dedupe_key (dedupe_key)` 10 columns: | Column | Type | Null | Note | | -------------------- | ----------------- | -------- | ----------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `patient_id` | bigint | NOT NULL | FK patients | | `nudge_number` | integer | NOT NULL | 1..3 (MAX_SENDS=3); unique per patient | | `completion_percent` | integer | NOT NULL | | | `dedupe_key` | character varying | NOT NULL | hh_nudge::; doubles as LinqEvent.message_id in send mode | | `delivery_mode` | character varying | NOT NULL | default 'dry_run'; actual mode recorded at dispatch | | `dispatched_at` | timestamp(6) | NOT NULL | | | `enqueued_at` | timestamp(6) | nullable | set after LinqReplyJob enqueue succeeds (nudge_notifier.rb:78) | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### patient_graphs Versioned structured clinical snapshot per patient (allergies, diagnoses, labs, meds, vitals, timeline as JSONB arrays) backing the health profile. - **Status:** **ACTIVE**: Read+written by patient/health_profiles_controller.rb:16,76 (routed resource :health_profile, config/routes.rb:259); read by admin/outcomes_controller.rb:46 and Agents::PharmacistReview packet builder. - **Spec ref:** §8.2 INFO #3 Full Health Profile: 🟡 PARTIAL - **Encryption:** none (clinical JSONB stored in plaintext: contrast with encrypted patient_health_histories) 12 columns: | Column | Type | Null | Note | | -------------- | ----------------- | -------- | ---------------------------------------------- | | `id` | bigint | NOT NULL | | | `allergies` | jsonb | NOT NULL | default [] | | `created_at` | timestamp(6) | NOT NULL | | | `diagnoses` | jsonb | NOT NULL | default [] | | `labs` | jsonb | NOT NULL | default [] | | `medications` | jsonb | NOT NULL | default [] | | `missing_data` | jsonb | NOT NULL | default [] | | `patient_id` | bigint | NOT NULL | FK patients | | `status` | character varying | NOT NULL | enum draft\|current\|superseded, default draft | | `timeline` | jsonb | NOT NULL | default [] | | `updated_at` | timestamp(6) | NOT NULL | | | `vitals` | jsonb | NOT NULL | default [] | ### patient_health_histories Versioned full health-history questionnaire (INFO #3): encrypted answers, per-section progress, revision counter, draft->complete->archived lifecycle. It is currently stored separately from clinician EHR facts; decided target is to synchronize/promote its information into the official EHR while preserving patient-reported provenance and revision history. - **Status:** **ACTIVE**: Read+written by patient/health_histories_controller.rb via HealthHistory::QuestionFlow (routed config/routes.rb:260); read by doctor/health_histories_controller.rb and Ehr::Integration::HealthHistories; nudged by HealthHistoryNudgeJob (config/recurring.yml, daily 10am CT). - **Spec ref:** §8.2 INFO #3 Full Health Profile: 🟡 PARTIAL - **Encryption:** answers and section_progress (Rails Active Record encryption, app/models/patient_health_history.rb:17-18) - **Notable indexes:** `UNIQUE partial (patient_id, questionnaire_version) WHERE status <> 'archived': one live history per version` 12 columns: | Column | Type | Null | Note | | ----------------------- | ----------------- | -------- | -------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `patient_id` | bigint | NOT NULL | FK patients | | `questionnaire_version` | character varying | NOT NULL | | | `status` | character varying | NOT NULL | enum draft\|complete\|archived, default draft | | `answers` | text | NOT NULL | AR-encrypted JSON | | `section_progress` | text | NOT NULL | AR-encrypted JSON | | `current_section_key` | character varying | nullable | | | `revision_number` | integer | NOT NULL | default 0; revisions in patient_health_history_revisions | | `last_saved_at` | timestamp(6) | nullable | | | `completed_at` | timestamp(6) | nullable | | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### patient_health_history_revisions Append-only audit snapshot of a patient's full health history (INFO #3) taken on every section save/completion. - **Status:** **DORMANT**: Write-only: created in production via PatientHealthHistory#record_revision! (app/models/patient_health_history.rb:365, called from app/controllers/patient/health_histories_controller.rb:136,226) but nothing reads .revisions anywhere (only dependent: :destroy) - **Spec ref:** §8.2 INFO #3 Full Health Profile: 🟡 PARTIAL - **Encryption:** answers_snapshot, section_progress_snapshot: Rails Active Record Encryption (app/models/patient_health_history_revision.rb:9-10) - **Notable indexes:** `UNIQUE (patient_health_history_id, revision_number)` 14 columns: | Column | Type | Null | Note | | --------------------------- | ----------------- | -------- | --------------------------- | | `id` | bigint | NOT NULL | | | `patient_health_history_id` | bigint | NOT NULL | FK patient_health_histories | | `patient_id` | bigint | NOT NULL | FK patients | | `created_by_id` | bigint | nullable | FK users (actor) | | `revision_number` | integer | NOT NULL | | | `event` | character varying | NOT NULL | section_saved \| completed | | `status` | character varying | NOT NULL | | | `changed_section_key` | character varying | nullable | | | `answers_snapshot` | text | NOT NULL | encrypted | | `section_progress_snapshot` | text | NOT NULL | encrypted | | `current_section_key` | character varying | nullable | | | `completed_at` | timestamp(6) | nullable | | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | --- ## §9: AI / Agent Layer (8 tables) ### artifacts Polymorphic saved snapshots of generated outputs (agent briefs, lab-import source PDFs, records-source PDFs, sealed text-PHI authorization PDFs, EHR note drafts): shared substrate across sections 2.5/3.5/4.4/9. - **Status:** **ACTIVE**: Written by Agents::BaseAgent, Workflows::SealTextAuthorization (app/services/workflows/seal_text_authorization.rb), app/services/lab_import/source_pdf_store.rb, app/services/records/source_pdf_store.rb; read by ehr/integration/note_draft_artifacts.rb, authorization_base_controller.rb, observability/workflow_metrics.rb - **Spec ref:** §9 AI / Agent Layer: section-level table (agent artifacts) - **Encryption:** none (body/payload stored plaintext) - **Notable indexes:** `UNIQUE partial one-per-artifactable indexes for kinds lab_import_source_pdf, records_source_pdf, text_phi_authorization_pdf` · `partial idx_artifacts_ehr_encounter_note_drafts (artifactable_id) WHERE artifactable_type='Ehr::Encounter' AND kind='note_draft'` 11 columns: | Column | Type | Null | Note | | ------------------- | ----------------- | -------- | -------------------------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `artifactable_id` | bigint | NOT NULL | | | `artifactable_type` | character varying | NOT NULL | polymorphic; validated against Event::EVENTABLE_TYPES | | `body` | text | nullable | | | `created_at` | timestamp(6) | NOT NULL | | | `created_by_id` | bigint | nullable | | | `created_by_type` | character varying | nullable | polymorphic actor; validated against Event::ACTOR_TYPES | | `kind` | character varying | NOT NULL | e.g. note_draft, lab_import_source_pdf, records_source_pdf, text_phi_authorization_pdf | | `payload` | jsonb | NOT NULL | fallback/unavailable flags live here | | `title` | character varying | nullable | | | `updated_at` | timestamp(6) | NOT NULL | | ### research_citations Links a deduped ResearchDocument to one medication slug with evidence type and rank. - **Status:** **ACTIVE**: Written+read by Research::IngestMedicationPapersJob (app/jobs/research/ingest_medication_papers_job.rb:71,82); pipeline kickoff is operator-run (rake research:ingest_all -> IngestAllMedicationsJob) and the only other reader is rake-rendered Research::CorpusReport: no patient/doctor-facing consumer yet (9.5 PARTIAL) - **Spec ref:** §9.5 Medical Research Agent: 🟡 PARTIAL - **Encryption:** none (non-PHI literature) - **Notable indexes:** `UNIQUE (research_document_id, medication_slug)` 8 columns: | Column | Type | Null | Note | | ---------------------- | ----------------- | -------- | ------------------------------------------- | | `id` | bigint | NOT NULL | | | `created_at` | timestamp(6) | NOT NULL | | | `evidence_type` | character varying | nullable | enum: review/rct/meta_analysis/safety/other | | `medication_name` | character varying | nullable | | | `medication_slug` | character varying | NOT NULL | aligned with future P1.8 Substance.slug | | `rank` | integer | nullable | | | `research_document_id` | bigint | NOT NULL | FK research_documents | | `updated_at` | timestamp(6) | NOT NULL | | ### research_documents One deduped paper (identity = PMID) in the medication evidence corpus: download + parse lifecycle, storage keys, license gating. - **Status:** **ACTIVE**: Production read+write via the LlamaParse webhook (config/routes.rb:111 -> app/controllers/api/v1/llama_parse_controller.rb:16) and the Research::* job pipeline (parse/finalize/reconcile jobs); ingestion kickoff is operator-run rake research:ingest_all; corpus has no patient/doctor-facing reader yet (9.5 PARTIAL) - **Spec ref:** §9.5 Medical Research Agent: 🟡 PARTIAL - **Encryption:** none (non-PHI literature; explicitly unencrypted per model comment) - **Notable indexes:** `UNIQUE partial (parse_job_id) WHERE parse_job_id IS NOT NULL` · `UNIQUE partial (pmid) WHERE pmid IS NOT NULL` · `GIN (metadata)` 35 columns: | Column | Type | Null | Note | | -------------------------- | ----------------- | -------- | ---------------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `abstract_text` | text | nullable | | | `authors` | jsonb | NOT NULL | default [] | | `completed_at` | timestamp(6) | nullable | | | `created_at` | timestamp(6) | NOT NULL | | | `doi` | character varying | nullable | | | `download_status` | character varying | NOT NULL | enum: pending/stored/unavailable/failed (default pending) | | `error_class` | character varying | nullable | | | `error_message` | text | nullable | | | `full_text_source` | character varying | nullable | enum: pmc_xml/pmc_pdf/unpaywall_pdf/contextdev_oa_pdf/contextdev_markdown | | `full_text_url` | text | nullable | | | `journal` | character varying | nullable | | | `license` | character varying | nullable | gates surfaceability (COMMERCIAL_SURFACEABLE_LICENSES) | | `markdown_object_key` | character varying | nullable | | | `metadata` | jsonb | NOT NULL | default {}; GIN-indexed | | `parse_job_id` | character varying | nullable | unique when present; LlamaParse job correlation | | `parse_status` | character varying | NOT NULL | enum: not_started/queued/parsing/parsed/partial/failed (default not_started) | | `parse_tier` | character varying | nullable | enum: fast/cost_effective/agentic/agentic_plus | | `parse_tier_review_needed` | boolean | NOT NULL | default false | | `parse_version` | character varying | nullable | | | `pdf_bucket` | character varying | nullable | | | `pdf_byte_size` | bigint | nullable | | | `pdf_object_key` | character varying | nullable | | | `pdf_sha256` | character varying | nullable | | | `pmcid` | character varying | nullable | | | `pmid` | character varying | nullable | identity key; model-level required+unique, partial-unique in SQL | | `published_year` | integer | nullable | | | `retry_count` | integer | NOT NULL | default 0 | | `source` | character varying | NOT NULL | default pubmed | | `source_url` | text | nullable | | | `source_url_expires_at` | timestamp(6) | nullable | | | `submitted_at` | timestamp(6) | nullable | | | `title` | text | nullable | required at model level | | `updated_at` | timestamp(6) | NOT NULL | | | `xml_object_key` | character varying | nullable | | ### governed_artifact_eval_runs Append-only ledger of passing eval-suite runs against a governed artifact version (digest-pinned evidence that a policy version passed its adversarial suite). - **Status:** **REFERENCE**: Offline-maintained evidence ledger: writes are offline/seed/migration-only by design (lib/tasks/governed_artifacts.rake:15; seeds via GovernedArtifacts::Registry.seed_defaults! -> SeededEvalEvidence.apply!, registry.rb:114-116; migration backfill emergency_triage_registry_v1.rb:290): CHECK pins provenance to offline_runner|migration_backfill; read in production gating: GovernedArtifactVersion#passing_eval_for? consumed by Workflows::SignPolicyArtifact:41 (from admin/governed_artifact_versions_controller.rb:68) and Linq::RedFlagDetector:67 - **Spec ref:** §9.7 Drift / "Greptile" Agent (self-checking layer): 🟡 PARTIAL - **CHECK constraints:** `chk_governed_eval_runs_case_count: case_count > 0` · `chk_governed_eval_runs_digests: suite_fingerprint, content_sha256, detector_source_sha256, provider_run_digest, evidence_digest all ~ ^[0-9a-f]{64}$` · `chk_governed_eval_runs_passing_only: passed = true (failing runs cannot be stored)` · `chk_governed_eval_runs_provenance: provenance_source IN (offline_runner, migration_backfill)` - **Notable indexes:** `UNIQUE idx_governed_eval_runs_evidence_digest (evidence_digest)` 15 columns: | Column | Type | Null | Note | | ------------------------------ | --------------------- | -------- | ----------------------------------------------- | | `id` | bigint | NOT NULL | | | `governed_artifact_version_id` | bigint | NOT NULL | FK governed_artifact_versions | | `suite_id` | character varying | NOT NULL | | | `suite_fingerprint` | character varying(64) | NOT NULL | sha256 hex | | `content_sha256` | character varying(64) | NOT NULL | sha256 of the version content evaluated | | `detector_source_sha256` | character varying(64) | NOT NULL | | | `provider_run_digest` | character varying(64) | NOT NULL | | | `passed` | boolean | NOT NULL | CHECK forces true; rows readonly? after persist | | `run_at` | timestamp(6) | NOT NULL | validated not future beyond 5min skew | | `model` | character varying | NOT NULL | LLM model used by the eval runner | | `case_count` | integer | NOT NULL | > 0 | | `provenance_source` | character varying | NOT NULL | offline_runner \| migration_backfill | | `evidence_digest` | character varying(64) | NOT NULL | globally unique | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### governed_artifact_versions Immutable-after-signing versioned content of a governed policy artifact (draft -> signed -> superseded), pinned by clinical records via GovernedArtifactPinnable. - **Status:** **ACTIVE**: Signed via Workflows::SignPolicyArtifact from admin/governed_artifact_versions_controller.rb:68; read in prod by Linq::RedFlagDetector, Workflows::LatchLinqEmergencyHold, Ehr::PreDoctorIntakes::StartOrResume, and FK-pinned by adverse_events, red_flag_safety_events, ehr_pre_doctor_intakes, dosing_error_incidents, linq_emergency_holds - **Spec ref:** §9.7 Drift / "Greptile" Agent (self-checking layer): 🟡 PARTIAL - **Notable indexes:** `UNIQUE idx_governed_artifact_versions_number_per_artifact (governed_artifact_id, version_number)` · `partial UNIQUE idx_governed_artifact_versions_one_signed_per_artifact (governed_artifact_id) WHERE status='signed'` 12 columns: | Column | Type | Null | Note | | ---------------------- | ----------------- | -------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | `id` | bigint | NOT NULL | | | `governed_artifact_id` | bigint | NOT NULL | FK governed_artifacts | | `version_number` | integer | NOT NULL | unique per artifact, auto-assigned on create | | `status` | character varying | NOT NULL | default 'draft'; draft\|signed\|superseded; cannot regress after signing | | `content` | jsonb | NOT NULL | default {}; immutable after signing; shape validated against GovernedArtifacts::Registry | | `content_diff` | jsonb | nullable | | | `signed_by_id` | bigint | nullable | FK users | | `signed_at` | timestamp(6) | nullable | | | `supersedes_id` | bigint | nullable | self-FK governed_artifact_versions | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | | `eval_runs` | jsonb | NOT NULL | default []; legacy embedded eval evidence superseded by governed_artifact_eval_runs but still read as fallback by passing_eval_for? (governed_artifact_version.rb:30-38): see LEGACY / DEPRECATION register (backfill verification required before removal) | ### governed_artifacts Registry-keyed governed policy artifacts (emergency triage registry, intake inventory, etc.) with owner role, review cadence, and pointer to the current signed version. - **Status:** **ACTIVE**: CRUD via app/controllers/admin/governed_artifacts_controller.rb and admin/governed_artifact_versions_controller.rb; read in prod by api/v1/linq_controller.rb, api/v1/intake_chat_controller.rb, and GovernedArtifactPinnable consumers - **Spec ref:** §9.7 Drift / "Greptile" Agent (self-checking layer): 🟡 PARTIAL - **Notable indexes:** `UNIQUE index_governed_artifacts_on_key (key): key must be in GovernedArtifacts::Registry.keys` 9 columns: | Column | Type | Null | Note | | ---------------------- | ----------------- | -------- | ---------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `key` | character varying | NOT NULL | unique; validated against GovernedArtifacts::Registry.keys | | `title` | character varying | NOT NULL | | | `description` | text | nullable | | | `owner_role` | character varying | NOT NULL | enum medical_director\|compliance_director\|founder | | `review_interval_days` | integer | NOT NULL | > 0 | | `current_version_id` | bigint | nullable | FK governed_artifact_versions | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### linq_judge_reports Nightly LLM-judge tape-read of the live Linq signup agent: one row per report date, keys-only flag counts (no message text). - **Status:** **ACTIVE**: Written by LinqNightlyJudgeJob on cron (config/recurring.yml production linq_nightly_judge '0 6 * * * America/Chicago' -> app/services/linq/nightly_judge.rb); read by Ops::Integration dashboard/message_line and Linq::AdminMessages::Send - **Spec ref:** §9.7 Drift / "Greptile" Agent (self-checking layer): 🟡 PARTIAL - **Notable indexes:** `unique index_linq_judge_reports_on_report_date` 12 columns: | Column | Type | Null | Note | | ----------------------- | ----------------- | -------- | ------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `report_date` | date | NOT NULL | unique | | `status` | character varying | NOT NULL | enum pending\|complete\|partial\|skipped, default pending | | `conversations_scanned` | integer | NOT NULL | default 0 | | `events_scanned` | integer | NOT NULL | default 0 | | `judged_count` | integer | NOT NULL | default 0 | | `truncated_count` | integer | NOT NULL | default 0 | | `warmth_avg` | numeric(3,2) | nullable | | | `flag_counts` | jsonb | NOT NULL | default {}; blank => clean? | | `flagged` | jsonb | NOT NULL | default []; conversation ids + flag labels only, never message text | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### observability_findings Deduplicated (fingerprinted) findings from the deterministic workflow-metrics analyzer, with triage lifecycle and post-ship verification fields. - **Status:** **ACTIVE**: Written by Observability::DeterministicAnalyzer inside ObservabilityDigestJob (app/jobs/observability_digest_job.rb:15, scheduled daily config/recurring.yml:96); read by admin/observability_controller.rb (routed config/routes.rb:398) and Observability::SlackDigest. - **Spec ref:** §9.7 Drift / "Greptile" Agent (self-checking layer): 🟡 PARTIAL - **Notable indexes:** `UNIQUE (fingerprint): dedupe key` · `(status, severity, created_at) lifecycle index` · `(clinic_id, status, created_at)` 21 columns: | Column | Type | Null | Note | | ---------------------- | ----------------- | -------- | ------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `clinic_id` | bigint | nullable | FK clinics | | `fingerprint` | character varying | NOT NULL | unique dedupe key | | `source_rule` | character varying | NOT NULL | | | `metric_name` | character varying | nullable | | | `severity` | character varying | NOT NULL | low\|medium\|high\|critical, default medium | | `status` | character varying | NOT NULL | open\|accepted\|rejected\|shipped\|closed, default open | | `owner` | character varying | nullable | | | `priority` | integer | nullable | | | `title` | character varying | NOT NULL | | | `summary` | text | nullable | | | `evidence` | jsonb | NOT NULL | default {} | | `linked_issue_id` | character varying | nullable | | | `linked_pr_url` | character varying | nullable | | | `accepted_at` | timestamp(6) | nullable | | | `rejected_at` | timestamp(6) | nullable | | | `shipped_at` | timestamp(6) | nullable | | | `post_ship_checked_at` | timestamp(6) | nullable | | | `post_ship_metric` | jsonb | NOT NULL | default {} | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | --- ## §10: Platform & Sibling Surfaces (9 tables) Ops-console tables the census filed under §10.3 (`ops_*`) now live in **§12 Ops Console & Oversight**. ### clinics Tenancy root: white-label clinic that owns users, patients, programs. - **Status:** **ACTIVE**: Created in signup flows (app/services/doctor_signups/register.rb, app/controllers/api/v1/signups_controller.rb) and read as scoping root across controllers (app/controllers/application_controller.rb:372 current_clinic scoping) - **Spec ref:** §10 Platform & Sibling Surfaces: section-level table - **Notable indexes:** `UNIQUE index_clinics_on_slug (slug)` 8 columns: | Column | Type | Null | Note | | --------------------------- | ----------------- | -------- | ------------------------------------------- | | `id` | bigint | NOT NULL | | | `created_at` | timestamp(6) | NOT NULL | | | `name` | character varying | NOT NULL | | | `settings` | jsonb | NOT NULL | default {} | | `slug` | character varying | NOT NULL | unique; used as to_param | | `status` | character varying | NOT NULL | enum active/paused/archived, default active | | `updated_at` | timestamp(6) | NOT NULL | | | `white_label_pharmacy_name` | character varying | nullable | | ### investor_leads Lead capture from the gated /investors video page (name+email form with honeypot); replaces Cloudflare video-lead.js. - **Status:** **ACTIVE (write-only ledger)**: Production write via POST /api/video-lead (config/routes.rb:69 -> Api::VideoLeadsController#create, app/controllers/api/video_leads_controller.rb:17-26); capture ledger doing its production job on the write side: reads happen out-of-band (console/DB via suspected_bot/legitimate scopes, app/models/investor_lead.rb:9-10) by design - **Spec ref:** §10 Platform & Sibling Surfaces: section-level table 9 columns: | Column | Type | Null | Note | | ------------ | ----------------- | -------- | ------------------------------------- | | `id` | bigint | NOT NULL | | | `created_at` | timestamp(6) | NOT NULL | | | `email` | character varying | NOT NULL | | | `ip` | character varying | nullable | | | `name` | character varying | NOT NULL | | | `referrer` | character varying | nullable | | | `updated_at` | timestamp(6) | NOT NULL | | | `user_agent` | character varying | nullable | | | `website` | character varying | nullable | honeypot: non-blank => suspected_bot | ### leads Partnership-call / contact leads from public marketing forms (POST /api/submit), viewed at /admin/leads; replaced Cloudflare R2 leads/ writes. - **Status:** **ACTIVE**: Written by Api::SubmitController#create (app/controllers/api/submit_controller.rb:17, route config/routes.rb:107); read by Admin::LeadsController (/admin/leads) - **Spec ref:** §10 Platform & Sibling Surfaces: section-level table - **Encryption:** name, email, message: Rails ActiveRecord encrypts, non-deterministic (app/models/lead.rb:6) - **Notable indexes:** `unique index_leads_on_request_id` 12 columns: | Column | Type | Null | Note | | --------------- | ----------------- | -------- | -------------------------- | | `id` | bigint | NOT NULL | | | `company` | character varying | nullable | | | `created_at` | timestamp(6) | NOT NULL | | | `email` | text | nullable | AR-encrypted | | `ip` | character varying | nullable | | | `message` | text | nullable | AR-encrypted | | `name` | text | nullable | AR-encrypted | | `pdf_requested` | character varying | nullable | | | `request_id` | character varying | NOT NULL | unique idempotency key | | `source` | character varying | NOT NULL | default 'partnership_form' | | `updated_at` | timestamp(6) | NOT NULL | | | `user_agent` | character varying | nullable | | ### webauthn_challenges Short-lived WebAuthn ceremony challenges (register/authenticate) for staff/provider passkey auth; cross-cutting security, no dedicated spec feature. - **Status:** **ACTIVE**: Written/consumed by app/controllers/concerns/webauthn_ceremony.rb used by staff/passkey_sessions_controller.rb, staff/passkeys_controller.rb, staff/enrollments_controller.rb, provider/onboarding_controller.rb; reaped by LoginChallengeReapJob (scheduled hourly in config/recurring.yml). - **Spec ref:** §10 Platform & Sibling Surfaces: section-level table - **Encryption:** none (challenge is a random nonce, not PHI) - **CHECK constraints:** `webauthn_challenges_valid_purpose: purpose in (register, authenticate)` - **Notable indexes:** `UNIQUE index_webauthn_challenges_on_nonce` · `partial idx_webauthn_challenges_live_by_purpose (purpose, expires_at) WHERE consumed_at IS NULL` 13 columns: | Column | Type | Null | Note | | ---------------- | ----------------- | -------- | -------------------------------------------------- | | `id` | bigint | NOT NULL | | | `user_id` | bigint | nullable | FK users; NULL for pre-account register ceremonies | | `nonce` | character varying | NOT NULL | unique client-side lookup handle | | `challenge` | text | NOT NULL | | | `purpose` | character varying | NOT NULL | register\|authenticate | | `rp_id` | character varying | NOT NULL | | | `origin` | character varying | NOT NULL | | | `binding_digest` | character varying | nullable | | | `webauthn_id` | text | nullable | user handle for register ceremonies | | `expires_at` | timestamp(6) | NOT NULL | | | `consumed_at` | timestamp(6) | nullable | | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### webauthn_credentials Registered WebAuthn passkeys (public key, sign counter) per user for staff/provider login and MFA. - **Status:** **ACTIVE**: Created by Workflows::RegisterWebauthnCredential (staff/passkeys_controller.rb, staff/enrollments_controller.rb, provider/onboarding_controller.rb) and verified on every passkey login in staff/passkey_sessions_controller.rb via webauthn_ceremony.rb. - **Spec ref:** §10 Platform & Sibling Surfaces: section-level table - **Encryption:** none (public key material only) - **CHECK constraints:** `webauthn_credentials_nonnegative_sign_count: sign_count >= 0` - **Notable indexes:** `UNIQUE index_webauthn_credentials_on_external_id` 10 columns: | Column | Type | Null | Note | | -------------- | ----------------- | -------- | ---------------------------------------- | | `id` | bigint | NOT NULL | | | `user_id` | bigint | NOT NULL | FK users | | `external_id` | character varying | NOT NULL | credential ID from authenticator; unique | | `public_key` | text | NOT NULL | | | `sign_count` | bigint | NOT NULL | default 0; clone detection | | `nickname` | character varying | nullable | | | `transports` | jsonb | NOT NULL | default [] | | `last_used_at` | timestamp(6) | nullable | | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### mobile_sessions iOS refresh-token session with rotation-family reuse detection, device metadata, and revocation state. - **Frozen f246 status:** **ACTIVE** through mobile OTP, issue, rotate, revoke, and access-token verification. **Current refs:** table and mobile auth paths are absent from both main and staging. - **Spec ref:** §10.1 RonanRX iOS app: 🟡 PARTIAL - **Encryption:** none (refresh_token_digest and ip_hash are SHA-256 digests, not reversible encryption) - **Notable indexes:** `UNIQUE (refresh_token_digest)` · `UNIQUE (rotated_from_id): linear rotation chain` · `(token_family_id) for family-wide revocation on token reuse` 19 columns: | Column | Type | Null | Note | | ---------------------- | --------------------- | -------- | --------------------------------------------- | | `id` | bigint | NOT NULL | | | `user_id` | bigint | NOT NULL | FK users | | `patient_id` | bigint | NOT NULL | FK patients | | `clinic_id` | bigint | NOT NULL | FK clinics | | `refresh_token_digest` | character varying(64) | NOT NULL | SHA-256 hex digest, unique | | `token_family_id` | uuid | NOT NULL | | | `rotated_from_id` | bigint | nullable | self-FK mobile_sessions | | `status` | character varying | NOT NULL | enum active\|rotated\|revoked, default active | | `refresh_expires_at` | timestamp(6) | NOT NULL | | | `revoked_at` | timestamp(6) | nullable | | | `reason` | character varying | nullable | | | `device_name` | character varying | nullable | | | `device_model` | character varying | nullable | | | `device_os` | character varying | nullable | | | `app_version` | character varying | nullable | | | `ip_hash` | character varying(64) | nullable | hashed via app/lib/mobile_session_ip_hash.rb | | `read_only` | boolean | NOT NULL | default false | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### patient_goals Patient nutrition/weight goal targets (target weight, protein/fiber/water) with client UUID + timezone, shaped for iOS sync. - **Frozen f246 status:** **DORMANT**; modeled and associated but with no reader or writer in that snapshot. **Current refs:** table and model are absent from both main and staging. - **Spec ref:** §10.1 RonanRX iOS app: 🟡 PARTIAL - **Encryption:** target_weight (Rails Active Record encryption, app/models/patient_goal.rb:8) - **CHECK constraints:** `fiber_g NULL or >= 0` · `protein_g NULL or >= 0` · `water_ml NULL or >= 0` · `weight_unit NULL or in (lb, kg)` - **Notable indexes:** `UNIQUE partial (patient_id) WHERE deleted_at IS NULL: one active goal per patient` · `UNIQUE (client_uuid): client-generated idempotency key` 12 columns: | Column | Type | Null | Note | | --------------- | ----------------- | -------- | ----------------------- | | `id` | bigint | NOT NULL | | | `patient_id` | bigint | NOT NULL | FK patients | | `client_uuid` | uuid | NOT NULL | unique, client-supplied | | `tz_identifier` | character varying | NOT NULL | | | `target_weight` | text | nullable | AR-encrypted | | `weight_unit` | character varying | nullable | lb\|kg | | `protein_g` | integer | nullable | | | `fiber_g` | integer | nullable | | | `water_ml` | integer | nullable | | | `deleted_at` | timestamp(6) | nullable | soft delete | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### regimen_items One medication/supplement line in a patient regimen (prescription-locked or patient-added) with schedule and titration steps, synced to the iOS app. - **Frozen f246 status:** **ACTIVE** through nightly regimen sync and the mobile API. **Current refs:** table, sync job, workflow, and routes are absent from both main and staging. - **Spec ref:** §10.1 RonanRX iOS app: 🟡 PARTIAL - **CHECK constraints:** `dose_amount > 0` · `dose pair: source=prescription => dose_amount AND dose_unit NULL, else both NOT NULL` · `item_type in (injection, supplement)` · `position >= 0` · `schedule is a JSON object` · `source in (prescription, patient)` · `source lock: prescription => prescription_id NOT NULL AND locked=true; patient => prescription_id NULL AND locked=false` · `titration_steps is a JSON array` - **Notable indexes:** `UNIQUE (client_uuid)` · `UNIQUE partial (prescription_id) WHERE locked=true AND deleted_at IS NULL: one live locked item per prescription` · `partial (patient_id, active) WHERE deleted_at IS NULL` 20 columns: | Column | Type | Null | Note | | ----------------- | ----------------- | -------- | -------------------------------------------------- | | `id` | bigint | NOT NULL | | | `patient_id` | bigint | NOT NULL | FK patients; must match regimen.patient_id | | `regimen_id` | bigint | NOT NULL | FK regimens | | `prescription_id` | bigint | nullable | FK prescriptions; required iff source=prescription | | `client_uuid` | uuid | NOT NULL | unique; mobile sync identity | | `tz_identifier` | character varying | NOT NULL | patient time zone captured at sync | | `item_type` | character varying | NOT NULL | injection \| supplement | | `source` | character varying | NOT NULL | prescription \| patient | | `name` | character varying | NOT NULL | | | `strength` | character varying | nullable | | | `dose_amount` | numeric(12,4) | nullable | | | `dose_unit` | character varying | nullable | | | `schedule` | jsonb | NOT NULL | default {}; object enforced by CHECK | | `titration_steps` | jsonb | NOT NULL | default []; dated steps (starts_on) | | `locked` | boolean | NOT NULL | default false; true iff prescription-sourced | | `active` | boolean | NOT NULL | default true | | `position` | integer | NOT NULL | default 0; quoted "position" in SQL | | `deleted_at` | timestamp(6) | nullable | soft delete | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### regimens Patient-level regimen container (named set of regimen items) synced to the iOS app. - **Frozen f246 status:** **ACTIVE** through nightly regimen sync and the mobile API. **Current refs:** table, sync job, workflow, and routes are absent from both main and staging. - **Spec ref:** §10.1 RonanRX iOS app: 🟡 PARTIAL - **Notable indexes:** `UNIQUE (client_uuid)` · `partial (patient_id, active) WHERE deleted_at IS NULL` 9 columns: | Column | Type | Null | Note | | --------------- | ----------------- | -------- | ---------------------------- | | `id` | bigint | NOT NULL | | | `patient_id` | bigint | NOT NULL | FK patients | | `client_uuid` | uuid | NOT NULL | unique; mobile sync identity | | `tz_identifier` | character varying | NOT NULL | | | `name` | character varying | NOT NULL | | | `active` | boolean | NOT NULL | default true | | `deleted_at` | timestamp(6) | nullable | soft delete | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | --- ## §11: Provider Onboarding (4 tables) Current §11 mapping: provider onboarding uses an invite-code gate → `DoctorSignups::Register` → provider agreement packet (6 documents) → credentials + state licenses → clinics linkage. Self-service signup and activation-path parity remain incomplete. §11.1 Provider Onboarding: 🟡 PARTIAL. ### provider_agreement_acceptances Records a provider's signed onboarding agreement packet (sealed PDF locator, content hash, attestations). - **Status:** **ACTIVE**: Written by Workflows::CreateProviderFromInvite (app/services/workflows/create_provider_from_invite.rb:181) called from provider/onboarding_controller.rb:130; read at provider/onboarding_controller.rb:439 and provider_agreement_packet_cleanup_job.rb:20 - **Spec ref:** §11.1 Provider Onboarding: 🟡 PARTIAL - **Note:** Census section §2 → remapped to §11. - **Encryption:** signer_name: Rails Active Record Encryption (app/models/provider_agreement_acceptance.rb:6) 11 columns: | Column | Type | Null | Note | | -------------------- | ----------------- | -------- | ---------- | | `id` | bigint | NOT NULL | | | `provider_id` | bigint | NOT NULL | FK users | | `packet_id` | character varying | nullable | | | `packet_version` | character varying | nullable | | | `content_sha256` | character varying | nullable | | | `sealed_pdf_locator` | character varying | nullable | | | `signer_name` | text | nullable | encrypted | | `signed_at` | timestamp(6) | nullable | | | `attestations` | jsonb | NOT NULL | default {} | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### provider_invites Admin-issued invite for a prescribing provider: encrypted contact/credential prefill plus one-time token to complete onboarding. - **Status:** **ACTIVE**: Created/listed in app/controllers/admin/provider_invites_controller.rb:16-46; consumed by Workflows::CreateProviderFromInvite via provider/onboarding_controller.rb:130 - **Spec ref:** §11.1 Provider Onboarding: 🟡 PARTIAL - **Note:** Census section §2 → remapped to §11. - **Encryption:** first_name, last_name, contact_email, phone, npi, dea_number, prefill: Rails Active Record Encryption (app/models/provider_invite.rb:5-11); contact_email_lookup_hash is the SHA256 shadow column 17 columns: | Column | Type | Null | Note | | ---------------------------- | ----------------- | -------- | ----------------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `created_by_user_id` | bigint | NOT NULL | FK users | | `clinic_id` | bigint | nullable | FK clinics | | `status` | character varying | NOT NULL | default 'pending'; enum pending\|completed | | `first_name` | text | nullable | encrypted | | `last_name` | text | nullable | encrypted | | `contact_email` | text | nullable | encrypted | | `phone` | text | nullable | encrypted | | `npi` | text | nullable | encrypted | | `dea_number` | text | nullable | encrypted | | `contact_email_lookup_hash` | character varying | nullable | SHA256 lookup | | `prefill` | text | nullable | encrypted; includes free-text specialty (no doctor-drug mapping table exists) | | `token_nonce` | character varying | nullable | | | `token_consumed_at` | timestamp(6) | nullable | | | `completed_provider_user_id` | bigint | nullable | FK users | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### ehr_provider_credentials Provider medical credential record (NPI/DEA, expiry) gating prescribing. - **Status:** **ACTIVE**: Written by DoctorSignups::Register (app/services/doctor_signups/register.rb:59); read by prescribing license gate (app/services/ehr/integration/prescribing.rb) and ops doctor admin - **Spec ref:** §11.1 Provider Onboarding: 🟡 PARTIAL - **Note:** Census section §3.2 → remapped to §11. - **Notable indexes:** `unique partial on dea_number WHERE dea_number IS NOT NULL` · `unique partial on npi WHERE npi IS NOT NULL` 11 columns: | Column | Type | Null | Note | | ----------------- | ----------------- | -------- | ---------------------------------------------------- | | `id` | bigint | NOT NULL | | | `provider_id` | bigint | NOT NULL | FK users | | `credential_type` | character varying | NOT NULL | default 'medical_license' | | `npi` | character varying | nullable | | | `dea_number` | character varying | nullable | | | `status` | character varying | NOT NULL | active\|inactive\|expired\|revoked, default 'active' | | `issued_on` | date | nullable | | | `expires_on` | date | nullable | | | `metadata` | jsonb | NOT NULL | | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### ehr_provider_state_licenses Per-state medical license under a provider credential; drives state-licensure prescribing checks. - **Status:** **ACTIVE**: Written by DoctorSignups::Register (app/services/doctor_signups/register.rb:64); read by Ehr::Integration::Prescribing/Providers and onboarding doctor_roster - **Spec ref:** §11.1 Provider Onboarding: 🟡 PARTIAL - **Note:** Census section §3.2 → remapped to §11. - **Notable indexes:** `unique partial idx_ehr_provider_state_one_active_license on (provider_id, state) WHERE status='active'` 10 columns: | Column | Type | Null | Note | | ------------------------ | ----------------- | -------- | ---------------------------------------------------- | | `id` | bigint | NOT NULL | | | `provider_credential_id` | bigint | NOT NULL | FK ehr_provider_credentials | | `provider_id` | bigint | NOT NULL | FK users (denormalized) | | `state` | character varying | NOT NULL | 2-letter, normalized upcase | | `license_number` | character varying | NOT NULL | | | `status` | character varying | NOT NULL | active\|inactive\|expired\|revoked, default 'active' | | `issued_on` | date | nullable | | | `expires_on` | date | nullable | | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | --- ## §12: Ops Console & Oversight (6 tables) Current §12 mapping: the built Ops Console (`app/controllers/ops`), "View as" impersonation, and assistant pause/resume. Staff sends from the Ops Console bypass the SendGuard body scan. §12.1 Ops Console & Oversight: 🟢 EXISTS. `audit_logs` (census: cross-cutting) is housed here as the platform-wide oversight ledger. ### ops_access_logs Fail-closed PHI access audit trail for the ronanrx-brain Ops API zone (audit insert raises => request 500s, no PHI decrypts). - **Status:** **ACTIVE**: Written by Ops::Integration::Audit.access! from api/ops/v1/base_controller.rb:205 (routes mounted config/routes.rb:75, gated by Ops::Config.enabled?); read by Ops::Diagnostics::OperationLookup behind the operations#show endpoint. - **Spec ref:** §12.1 Ops Console & Oversight: 🟢 EXISTS - **Note:** Census section §10.3 → remapped to §12. - **Notable indexes:** `(actor_id, occurred_at)` · `(patient_id, occurred_at)` 10 columns: | Column | Type | Null | Note | | ---------------------- | ----------------- | -------- | --------------------- | | `id` | bigint | NOT NULL | | | `actor_id` | bigint | NOT NULL | FK users | | `action` | character varying | NOT NULL | | | `patient_id` | bigint | nullable | FK patients | | `linq_conversation_id` | bigint | nullable | FK linq_conversations | | `request_id` | character varying | nullable | | | `metadata` | jsonb | NOT NULL | default {} | | `occurred_at` | timestamp(6) | NOT NULL | | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### ops_command_executions Immutable record of every executed Ops API command with input digest, before/after summaries, and result. - **Status:** **ACTIVE**: Written by Ops::CommandRecorder and Ops::MutationProtocol from routed api/ops/v1 commands/mutations endpoints (config/routes.rb:75-92); read by Ops::Diagnostics::OperationLookup via operations#show. - **Spec ref:** §12.1 Ops Console & Oversight: 🟢 EXISTS - **Note:** Census section §10.3 → remapped to §12. - **CHECK constraints:** `before_summary and after_summary must be JSON objects` · `target_refs, warnings, next_actions must be JSON arrays` - **Notable indexes:** `UNIQUE (request_id)` · `(command, created_at)` · `(credential_id, created_at)` 14 columns: | Column | Type | Null | Note | | ------------------------- | ----------------- | -------- | --------------------------- | | `id` | bigint | NOT NULL | | | `actor_id` | bigint | NOT NULL | FK users | | `credential_id` | bigint | NOT NULL | FK ops_credentials | | `request_id` | character varying | NOT NULL | unique | | `command` | character varying | NOT NULL | | | `target_refs` | jsonb | NOT NULL | default [], must be array | | `input_digest` | character varying | NOT NULL | | | `before_summary` | jsonb | NOT NULL | default {}, must be object | | `after_summary` | jsonb | NOT NULL | default {}, must be object | | `result` | character varying | NOT NULL | | | `warnings` | jsonb | NOT NULL | default [], must be array | | `next_actions` | jsonb | NOT NULL | default [], must be array | | `human_approval_required` | boolean | NOT NULL | default false | | `created_at` | timestamp(6) | NOT NULL | no updated_at: append-only | ### ops_credentials Scoped bearer credentials (token digests) that authenticate agents/operators to the Ops API. - **Status:** **ACTIVE**: Authenticated on every Ops API request in api/ops/v1/base_controller.rb (Ops::Credential lookup by token_digest; scopes/expiry/revocation enforced) and surfaced in capabilities_controller.rb: routes config/routes.rb:75. - **Spec ref:** §12.1 Ops Console & Oversight: 🟢 EXISTS - **Note:** Census section §10.3 → remapped to §12. - **Encryption:** none (token_digest is a hash, not encrypted plaintext) - **CHECK constraints:** `scopes must be a JSON array` - **Notable indexes:** `UNIQUE (reference)` · `UNIQUE (token_digest)` · `(owner_id, revoked_at)` 11 columns: | Column | Type | Null | Note | | -------------- | ----------------- | -------- | ---------------------------- | | `id` | bigint | NOT NULL | | | `owner_id` | bigint | NOT NULL | FK users | | `reference` | character varying | NOT NULL | unique human-readable handle | | `label` | character varying | NOT NULL | | | `token_digest` | character varying | NOT NULL | unique hash of bearer token | | `scopes` | jsonb | NOT NULL | default [], must be array | | `expires_at` | timestamp(6) | nullable | | | `revoked_at` | timestamp(6) | nullable | | | `last_used_at` | timestamp(6) | nullable | | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### ops_mutation_intents Two-phase mutation preflight record: planned changes, warnings, and a single-use confirm token binding the eventual execute to the previewed input. - **Status:** **ACTIVE**: Created and consumed by Ops::MutationProtocol behind routed POST api/ops/v1/mutations/:command/preflight + execute endpoints (config/routes.rb:91). - **Spec ref:** §12.1 Ops Console & Oversight: 🟢 EXISTS - **Note:** Census section §10.3 → remapped to §12. - **Encryption:** none (token/idempotency/input digests are SHA-256 hex) - **CHECK constraints:** `token_digest, idempotency_digest, input_digest each match ^[0-9a-f]{64}$` · `before_summary must be object; target_refs/warnings/next_actions must be arrays` - **Notable indexes:** `UNIQUE (token_digest): single-use confirm token` · `(credential_id, command, idempotency_digest) binding index` · `(credential_id, expires_at)` 17 columns: | Column | Type | Null | Note | | ------------------------- | --------------------- | -------- | ------------------ | | `id` | bigint | NOT NULL | | | `actor_id` | bigint | NOT NULL | FK users | | `credential_id` | bigint | NOT NULL | FK ops_credentials | | `command` | character varying | NOT NULL | | | `token_digest` | character varying(64) | NOT NULL | unique, hex-64 | | `idempotency_digest` | character varying(64) | NOT NULL | hex-64 | | `input_digest` | character varying(64) | NOT NULL | hex-64 | | `target_refs` | jsonb | NOT NULL | default [] | | `before_summary` | jsonb | NOT NULL | default {} | | `planned_changes` | jsonb | NOT NULL | default [] | | `warnings` | jsonb | NOT NULL | default [] | | `next_actions` | jsonb | NOT NULL | default [] | | `human_approval_required` | boolean | NOT NULL | default true | | `expires_at` | timestamp(6) | NOT NULL | | | `consumed_at` | timestamp(6) | nullable | | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### ops_mutation_receipts Idempotency receipt for an executed ops mutation: replays with the same (credential, command, idempotency key) return the stored result payload. - **Status:** **ACTIVE**: Written/read by Ops::MutationProtocol on the routed mutations execute path (config/routes.rb:91) and read by Ops::Diagnostics::OperationLookup (operations#show). - **Spec ref:** §12.1 Ops Console & Oversight: 🟢 EXISTS - **Note:** Census section §10.3 → remapped to §12. - **CHECK constraints:** `idempotency_digest and input_digest match ^[0-9a-f]{64}$` · `result_payload must be a JSON object` - **Notable indexes:** `UNIQUE (credential_id, command, idempotency_digest): idempotency replay key` · `UNIQUE (mutation_intent_id)` · `UNIQUE (request_id)` 10 columns: | Column | Type | Null | Note | | -------------------- | --------------------- | -------- | ---------------------------- | | `id` | bigint | NOT NULL | | | `actor_id` | bigint | NOT NULL | FK users | | `credential_id` | bigint | NOT NULL | FK ops_credentials | | `mutation_intent_id` | bigint | NOT NULL | FK ops_mutation_intents, 1:1 | | `command` | character varying | NOT NULL | | | `idempotency_digest` | character varying(64) | NOT NULL | | | `input_digest` | character varying(64) | NOT NULL | | | `request_id` | character varying | NOT NULL | unique | | `result_payload` | jsonb | NOT NULL | default {}, must be object | | `created_at` | timestamp(6) | NOT NULL | no updated_at: append-only | ### audit_logs Immutable HMAC-signed record of hard-fail-gate transitions (the 11 AuditLog::HARD_FAIL_GATES) plus policy-gate references, spanning prescribing (3), pharmacy (5), and agent governance. - **Status:** **ACTIVE**: Written by Workflows::Base and gate workflows (app/services/workflows/apply_therapy_hold.rb, latch_linq_emergency_hold.rb, app/services/ehr/integration/prescribing.rb); read by Reports::LegalGateAudit and ehr/integration/audit.rb; signature computed before_create (app/models/audit_log.rb) - **Spec ref:** §12.1 Ops Console & Oversight: 🟢 EXISTS - **Note:** Census section: _cross-cutting_: housed under §12 because the audit layer is part of the oversight domain; every gate-writing workflow platform-wide writes here. - **Encryption:** no encrypted columns; tamper-evidence via HMAC signature_hash (SIGNATURE_VERSION 1, legacy 0) - **Notable indexes:** `idx_audit_logs_on_target_and_created (target_type, target_id, created_at)` · `index_audit_logs_on_gate_name (gate_name)` 14 columns: | Column | Type | Null | Note | | ------------------------ | ----------------- | -------- | -------------------------------------------------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `actor_id` | bigint | nullable | nullable only for automated emergency latch | | `actor_type` | character varying | nullable | | | `after_snapshot` | jsonb | NOT NULL | | | `before_snapshot` | jsonb | NOT NULL | | | `created_at` | timestamp(6) | NOT NULL | | | `gate_name` | character varying | NOT NULL | 11 hard-fail gates + 24 policy gates; dea_schedule_controls_prescribing vestigial (name only since 2026-07-14) | | `reason` | text | nullable | | | `signature_hash` | character varying | nullable | 64-hex HMAC over row content | | `target_id` | bigint | NOT NULL | | | `target_type` | character varying | NOT NULL | polymorphic target | | `updated_at` | timestamp(6) | NOT NULL | | | `signature_version` | integer | NOT NULL | 0 = legacy unsigned, 1 = current | | `signature_generated_at` | timestamp(6) | nullable | | --- ## Infrastructure (framework tables) (18 tables) Rails framework substrate (Active Storage, Solid Queue/Cache/Cable, schema bookkeeping). No spec feature owns these; they are not candidates for the field-census rule: the framework manages them. ### active_storage_attachments Rails Active Storage join table binding blobs to records. - **Status:** **ACTIVE**: UploadedDocument has_one_attached :file (app/models/uploaded_document.rb:5); production writers e.g. app/controllers/portal/completions_controller.rb:346, app/services/lab_import/source_pdf_store.rb:37, app/services/identity/id_document_store.rb:103 - **Spec ref:** Rails framework substrate: no owning spec feature - **Notable indexes:** `UNIQUE index_active_storage_attachments_uniqueness (record_type, record_id, name, blob_id)` 6 columns: | Column | Type | Null | Note | | ------------- | ----------------- | -------- | ----------------------------------------- | | `id` | bigint | NOT NULL | | | `blob_id` | bigint | NOT NULL | FK -> active_storage_blobs | | `created_at` | timestamp(6) | NOT NULL | | | `name` | character varying | NOT NULL | | | `record_id` | bigint | NOT NULL | | | `record_type` | character varying | NOT NULL | polymorphic; in practice UploadedDocument | ### active_storage_blobs Rails Active Storage blob metadata (key, checksum, service). - **Status:** **ACTIVE**: Backing store for UploadedDocument#file attachments written in production (portal/completions_controller.rb:346 and the identity/lab/records PDF stores) - **Spec ref:** Rails framework substrate: no owning spec feature - **Notable indexes:** `UNIQUE index_active_storage_blobs_on_key (key)` 9 columns: | Column | Type | Null | Note | | -------------- | ----------------- | -------- | ------------------ | | `id` | bigint | NOT NULL | | | `byte_size` | bigint | NOT NULL | | | `checksum` | character varying | nullable | | | `content_type` | character varying | nullable | | | `created_at` | timestamp(6) | NOT NULL | | | `filename` | character varying | NOT NULL | | | `key` | character varying | NOT NULL | unique storage key | | `metadata` | text | nullable | | | `service_name` | character varying | NOT NULL | | ### active_storage_variant_records Rails Active Storage image-variant tracking table. - **Status:** **DORMANT**: Framework-installed; no `.variant(` call anywhere in app/: no code produces image variants, so nothing ever writes or reads this table - **Spec ref:** Rails framework substrate: no owning spec feature - **Notable indexes:** `UNIQUE index_active_storage_variant_records_uniqueness (blob_id, variation_digest)` 3 columns: | Column | Type | Null | Note | | ------------------ | ----------------- | -------- | -------------------------- | | `id` | bigint | NOT NULL | | | `blob_id` | bigint | NOT NULL | FK -> active_storage_blobs | | `variation_digest` | character varying | NOT NULL | | ### ar_internal_metadata Rails framework table storing environment metadata (e.g. environment => production). - **Status:** **ACTIVE**: Framework-managed; read/written by ActiveRecord on every boot/migration: no application code touches it - **Spec ref:** Rails framework substrate: no owning spec feature 4 columns: | Column | Type | Null | Note | | ------------ | ----------------- | -------- | ----------- | | `key` | character varying | NOT NULL | primary key | | `value` | character varying | nullable | | | `created_at` | timestamp(6) | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### schema_migrations Rails framework ledger of applied migrations. - **Status:** **ACTIVE**: Framework-managed by the ActiveRecord migrator on every deploy/migration; no app model - **Spec ref:** Rails framework substrate: no owning spec feature 1 columns: | Column | Type | Null | Note | | --------- | ----------------- | -------- | ----------- | | `version` | character varying | NOT NULL | primary key | ### solid_cable_messages Solid Cable pub/sub message store backing Action Cable in production. - **Status:** **ACTIVE**: config/cable.yml production adapter=solid_cable; prod shares one Postgres across primary/cache/queue/cable logical DBs (config/database.yml:86-103), so this table in structure.sql is the live store; 1-day message_retention - **Spec ref:** Rails framework substrate: no owning spec feature - **Encryption:** none (framework bytea payloads) - **Notable indexes:** `indexes on channel, channel_hash, created_at (framework-standard)` 5 columns: | Column | Type | Null | Note | | -------------- | ------------ | -------- | ---- | | `id` | bigint | NOT NULL | | | `channel` | bytea | NOT NULL | | | `channel_hash` | bigint | NOT NULL | | | `created_at` | timestamp(6) | NOT NULL | | | `payload` | bytea | NOT NULL | | ### solid_cache_entries Solid Cache key/value store backing Rails.cache in production. - **Status:** **ACTIVE**: config/environments/production.rb:60 cache_store = :solid_cache_store; cache logical DB shares the primary Postgres (config/database.yml:86-99) - **Spec ref:** Rails framework substrate: no owning spec feature - **Encryption:** none (framework bytea values) - **Notable indexes:** `unique index on key_hash; composite (key_hash, byte_size) for size-aware eviction` 6 columns: | Column | Type | Null | Note | | ------------ | ------------ | -------- | ---- | | `id` | bigint | NOT NULL | | | `byte_size` | integer | NOT NULL | | | `created_at` | timestamp(6) | NOT NULL | | | `key` | bytea | NOT NULL | | | `key_hash` | bigint | NOT NULL | | | `value` | bytea | NOT NULL | | ### solid_queue_blocked_executions Solid Queue: jobs blocked on a concurrency semaphore awaiting release. - **Status:** **ACTIVE**: active_job.queue_adapter = :solid_queue (config/environments/production.rb:63-64); queue logical DB shares the primary Postgres (config/database.yml:86-100), so structure.sql tables are the live queue - **Spec ref:** Rails framework substrate: no owning spec feature - **Notable indexes:** `unique index on job_id` · `maintenance index (expires_at, concurrency_key)` · `release index (concurrency_key, priority, job_id)` 7 columns: | Column | Type | Null | Note | | ----------------- | ----------------- | -------- | ----------------------------------------- | | `id` | bigint | NOT NULL | | | `concurrency_key` | character varying | NOT NULL | | | `created_at` | timestamp(6) | NOT NULL | | | `expires_at` | timestamp(6) | NOT NULL | | | `job_id` | bigint | NOT NULL | FK solid_queue_jobs(id) ON DELETE CASCADE | | `priority` | integer | NOT NULL | | | `queue_name` | character varying | NOT NULL | | ### solid_queue_claimed_executions Solid Queue: jobs currently claimed by a worker process. - **Status:** **ACTIVE**: Framework-managed by the Solid Queue supervisor/workers; adapter wired in config/environments/production.rb:63-64 - **Spec ref:** Rails framework substrate: no owning spec feature - **Notable indexes:** `unique index on job_id` · `index (process_id, job_id)` 4 columns: | Column | Type | Null | Note | | ------------ | ------------ | -------- | ------------------------------------------- | | `id` | bigint | NOT NULL | | | `created_at` | timestamp(6) | NOT NULL | | | `job_id` | bigint | NOT NULL | FK solid_queue_jobs(id) ON DELETE CASCADE | | `process_id` | bigint | nullable | references solid_queue_processes (no DB FK) | ### solid_queue_failed_executions Solid Queue: terminal failure records with error text for failed jobs. - **Status:** **ACTIVE**: Framework-managed; written by Solid Queue on job failure; adapter wired in config/environments/production.rb:63-64 - **Spec ref:** Rails framework substrate: no owning spec feature - **Notable indexes:** `unique index on job_id` 4 columns: | Column | Type | Null | Note | | ------------ | ------------ | -------- | ----------------------------------------- | | `id` | bigint | NOT NULL | | | `created_at` | timestamp(6) | NOT NULL | | | `error` | text | nullable | | | `job_id` | bigint | NOT NULL | FK solid_queue_jobs(id) ON DELETE CASCADE | ### solid_queue_jobs Solid Queue: canonical job row (ActiveJob payload) that all execution tables reference. - **Status:** **ACTIVE**: Every enqueued ActiveJob in prod lands here (config/environments/production.rb:63-64); recurring task clear_solid_queue_finished_jobs prunes it hourly (config/recurring.yml:15-17) - **Spec ref:** Rails framework substrate: no owning spec feature - **Notable indexes:** `alerting index (scheduled_at, finished_at)` · `filtering index (queue_name, finished_at)` · `indexes on active_job_id, class_name, finished_at` 11 columns: | Column | Type | Null | Note | | ----------------- | ----------------- | -------- | ------------------------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `active_job_id` | character varying | nullable | | | `arguments` | text | nullable | serialized ActiveJob args: PHI-bearing job args stored plaintext (framework default) | | `class_name` | character varying | NOT NULL | | | `concurrency_key` | character varying | nullable | | | `created_at` | timestamp(6) | NOT NULL | | | `finished_at` | timestamp(6) | nullable | | | `priority` | integer | NOT NULL | | | `queue_name` | character varying | NOT NULL | | | `scheduled_at` | timestamp(6) | nullable | | | `updated_at` | timestamp(6) | NOT NULL | | ### solid_queue_pauses Solid Queue: queues manually paused (row present = queue paused). - **Status:** **ACTIVE**: Framework-consulted on every dispatch poll; part of the wired Solid Queue install (config/environments/production.rb:63-64); normally empty - **Spec ref:** Rails framework substrate: no owning spec feature - **Notable indexes:** `unique index on queue_name` 3 columns: | Column | Type | Null | Note | | ------------ | ----------------- | -------- | ---- | | `id` | bigint | NOT NULL | | | `created_at` | timestamp(6) | NOT NULL | | | `queue_name` | character varying | NOT NULL | | ### solid_queue_processes Solid Queue: registry + heartbeat of supervisor/worker/dispatcher processes. - **Status:** **ACTIVE**: Framework-managed heartbeats; Solid Queue can also run inside Puma (config/puma.rb:38, SOLID_QUEUE_IN_PUMA); adapter wired in config/environments/production.rb:63-64 - **Spec ref:** Rails framework substrate: no owning spec feature - **Notable indexes:** `unique index (name, supervisor_id)` · `index on last_heartbeat_at` 9 columns: | Column | Type | Null | Note | | ------------------- | ----------------- | -------- | ---------------------------------------------------- | | `id` | bigint | NOT NULL | | | `created_at` | timestamp(6) | NOT NULL | | | `hostname` | character varying | nullable | | | `kind` | character varying | NOT NULL | Supervisor \| Worker \| Dispatcher \| Scheduler | | `last_heartbeat_at` | timestamp(6) | NOT NULL | | | `metadata` | text | nullable | | | `name` | character varying | NOT NULL | | | `pid` | integer | NOT NULL | | | `supervisor_id` | bigint | nullable | self-referential to solid_queue_processes (no DB FK) | ### solid_queue_ready_executions Solid Queue: jobs ready to be claimed by workers (the poll set). - **Status:** **ACTIVE**: Framework-managed hot path for every job execution; adapter wired in config/environments/production.rb:63-64 - **Spec ref:** Rails framework substrate: no owning spec feature - **Notable indexes:** `unique index on job_id` · `poll_all index (priority, job_id)` · `poll_by_queue index (queue_name, priority, job_id)` 5 columns: | Column | Type | Null | Note | | ------------ | ----------------- | -------- | ----------------------------------------- | | `id` | bigint | NOT NULL | | | `created_at` | timestamp(6) | NOT NULL | | | `job_id` | bigint | NOT NULL | FK solid_queue_jobs(id) ON DELETE CASCADE | | `priority` | integer | NOT NULL | | | `queue_name` | character varying | NOT NULL | | ### solid_queue_recurring_executions Solid Queue: dedup ledger recording each fired run of a recurring task. - **Status:** **ACTIVE**: Written every time a config/recurring.yml task fires (~29 production recurring tasks defined); unique (task_key, run_at) prevents double-fire - **Spec ref:** Rails framework substrate: no owning spec feature - **Notable indexes:** `unique index on job_id` · `unique index (task_key, run_at)` 5 columns: | Column | Type | Null | Note | | ------------ | ----------------- | -------- | ----------------------------------------- | | `id` | bigint | NOT NULL | | | `created_at` | timestamp(6) | NOT NULL | | | `job_id` | bigint | NOT NULL | FK solid_queue_jobs(id) ON DELETE CASCADE | | `run_at` | timestamp(6) | NOT NULL | | | `task_key` | character varying | NOT NULL | key from config/recurring.yml | ### solid_queue_recurring_tasks Solid Queue: loaded cron-style recurring task definitions (mirrors config/recurring.yml). - **Status:** **ACTIVE**: Populated by the Solid Queue scheduler from config/recurring.yml (29 production entries incl. appointment_reminders, oura/whoop/withings syncs, on_call_escalation_sweep) - **Spec ref:** Rails framework substrate: no owning spec feature - **Notable indexes:** `unique index on key` · `index on static` 12 columns: | Column | Type | Null | Note | | ------------- | ----------------------- | -------- | ---------------------------------------------------------------------------------------------------------------- | | `id` | bigint | NOT NULL | | | `arguments` | text | nullable | | | `class_name` | character varying | nullable | | | `command` | character varying(2048) | nullable | used by clear_solid_queue_finished_jobs (command-style task) | | `created_at` | timestamp(6) | NOT NULL | | | `description` | text | nullable | | | `key` | character varying | NOT NULL | | | `priority` | integer | nullable | | | `queue_name` | character varying | nullable | | | `schedule` | character varying | NOT NULL | Fugit cron/interval; resolved in process TZ (UTC in prod), hence explicit America/Chicago crons in recurring.yml | | `static` | boolean | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | ### solid_queue_scheduled_executions Solid Queue: future-scheduled jobs awaiting dispatch to ready_executions. - **Status:** **ACTIVE**: Framework-managed; every set(wait:)/recurring enqueue lands here; adapter wired in config/environments/production.rb:63-64 - **Spec ref:** Rails framework substrate: no owning spec feature - **Notable indexes:** `unique index on job_id` · `dispatch_all index (scheduled_at, priority, job_id)` 6 columns: | Column | Type | Null | Note | | -------------- | ----------------- | -------- | ----------------------------------------- | | `id` | bigint | NOT NULL | | | `created_at` | timestamp(6) | NOT NULL | | | `job_id` | bigint | NOT NULL | FK solid_queue_jobs(id) ON DELETE CASCADE | | `priority` | integer | NOT NULL | | | `queue_name` | character varying | NOT NULL | | | `scheduled_at` | timestamp(6) | NOT NULL | | ### solid_queue_semaphores Solid Queue concurrency-control semaphores (framework table). - **Status:** **ACTIVE**: Framework-managed by Solid Queue (queue adapter in prod; recurring.yml schedules run through it); no app model. - **Spec ref:** Rails framework substrate: no owning spec feature - **Notable indexes:** `UNIQUE index_solid_queue_semaphores_on_key (key)` · `index on (key, value)` · `index on expires_at` 6 columns: | Column | Type | Null | Note | | ------------ | ----------------- | -------- | --------- | | `id` | bigint | NOT NULL | | | `created_at` | timestamp(6) | NOT NULL | | | `expires_at` | timestamp(6) | NOT NULL | | | `key` | character varying | NOT NULL | | | `updated_at` | timestamp(6) | NOT NULL | | | `value` | integer | NOT NULL | default 1 | --- ## Changelog | Date | Change | Approval | | ---------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------- | | 2026-07-17 | Initial frozen census: 177 tables and 2,238 columns generated from `db/structure.sql` at `f246ed27`; first review registers and §11/§12 mappings added. | Historical record. | | 2026-07-17 | Second audit corrected taxonomy, index names, encryption citations, and exact schema types. | Historical record. | | 2026-07-19 | Renamed DEAD-CANDIDATE to REVIEW-HOLD and added PRESERVE for six fulfillment and refill tables. Frozen totals became 139 ACTIVE / 6 REFERENCE / 24 DORMANT / 6 PRESERVE / 2 REVIEW-HOLD. | Historical record. | | 2026-07-21 | Preserved the frozen census structure; compared current main `d26d5e1c` and staging `c0024874`; marked ten f246 mobile/HealthKit tables and related code as absent from both; noted `suggested_prescriptions` on both current refs; corrected BUD and $10/$29 decision text. | Current-ref comparison complete; D10 production-data census remains open. | --- _Frozen historical census. Current `ronanrx-core` remains authoritative for implemented schema behavior; drift produces review, not automatic removal or merge rejection. Companion documents: business.md · technical.md._