reference/data-model.mdEngineering, reference~3 min read

6. Canonical Data Model

6.1 Conventions

  • Primary keys: UUIDv7 (id). Every PHI-bearing table carries tenant_id (FK, indexed first in every composite index), cross-tenant queries are impossible by construction; a SQLAlchemy session-level tenant filter is applied on every request.
  • Timestamps: created_at/updated_at UTC (timestamptz); clinical times keep source timezone in a companion *_tz column when supplied.
  • Nothing is hard-deleted. Lifecycle is expressed in status columns; caregiver revocation, consent withdrawal, and supersession are state changes with audit events.
  • Optimistic concurrency: version (int) on episode, task_instance, medication_plan_item, brief_snapshot; writers must supply expected version.
  • Money/none in MVP; quantities as numeric(12,4); enums are Postgres enums migrated via Alembic.

6.2 Entity Catalog

Entity Purpose Key Fields (beyond id/tenant/timestamps)
tenant / cohort Customer + clinical population config container. name, config_version, cohort criteria JSON, episode_template_id
patient Demographics + identity linkage. mrn (per tenant), fhir_patient_id, name, dob, phones[], language, tz, accessibility_prefs
episode One 30-day transition instance. patient_id, cohort_id, state, version, index_encounter_id, discharge_at, day30_at, activation_at, closed_at, close_reason
consent_record Versioned consent artifacts. episode_id, type(program|caregiver|comm_channel), scope JSON, granted_by, granted_at, withdrawn_at, doc_version
caregiver + caregiver_grant Separate identity + scoped access. caregiver: name, phone, relationship; grant: episode_id, scope JSON, status(active|revoked), revoked_at
clinician + access_grant External clinician + purpose-limited grant. clinician: npi, name, email, org; grant: episode_id, purpose, expires_at, device_fingerprints[], status, step_up_at
source_document Ingested artifact + fingerprint. episode_id, type(avs|dc_summary|post_visit_note|upload), fhir_ref, s3_key, sha256, source_level(1-9), finality(final|amended|prelim), supersedes_id, received_at
extracted_fact LLM/parsed proposition pre/post gates. source_document_id, kind, payload JSON (schema_version), span_start/end, span_text, gate_status(g1..g7|published|rejected|pending_review), confidence, model_run_id
task_instance Universal task (all patient/clinician actions). episode_id, category(medication|appointment|lab|education|safety|admin), priority(critical|important|routine), state (11), due_at, source_fact_id, supersedes_id, version
medication_plan_item Instruction authority dimension. episode_id, rxnorm, name, dose, route, freq, change_type(new|changed|continued|stopped|uncertain), status(proposed|active|superseded|stopped), source_fact_id, confirmed_by/at
medication_status_event Evidence dimensions (transmission/access/use). plan_item_id, state(ordered|sent|available|obtained|started|taking|stopped|barrier), evidence_class(patient_report|pharmacy_confirmed|clinician_confirmed|ehr), reported_by, occurred_at, barrier_code(cost|stock|transport|confusion|side_effect|other)
appointment_requirement / appointment Need vs. booked visit. req: specialty, window_start/end, status(open|satisfied|waived); appt: req_id nullable, source(ehr|patient_report|assisted), starts_at, provider, status(booked|completed|no_show|cancelled), verified bool
notification_message Every outbound touch. episode_id, task_id, channel(push|sms|voice|email), template_id+version, lang, scheduled_at, sent_at, delivery_status, suppress_reason
agent_action Evidence record of every agent step. agent(engagement|appointment|med_access|records|brief|outcome), tool, params_hash, params JSON (redacted), result(success|fail|blocked), evidence JSON, tokens_cost, envelope_version, killed_by
brief_snapshot Immutable clinician brief. episode_id, appointment_id, content JSON, episode_version_at_build, s3_pdf_key, generated_at, opened_at[], stale_superseded_by
outcome_event ED/readmission signals. episode_id, type(ed|readmit), source(adt|claims|hie|patient_report), facility, occurred_at, within_30d bool, completeness_note
audit_event Append-only ledger (own schema). actor_type/id, action, object_type/id, purpose, context JSON, ip, trace_id, occurred_at, monthly partitions
config_version / template / model_registry Versioned governance artifacts. published_by/at, diff JSON, approval_ref; model_registry: model_id, prompt_version, schema_version, safety_policy_version, status(active|rollback)

6.3 Core Table Details

task_instance (authoritative for everything the patient sees)

Field Type Rule
state enum(11) proposed, required, scheduled, in_progress, completed, blocked, pending_clinical_decision, declined, superseded, cancelled, unresolved_at_close. Transitions only via §7.1.
priority enum critical | important | routine, set by deterministic rules on category+source_fact (e.g., new/changed cardiac med → critical); drives notification tier and Today ordering.
due_at / window timestamptz / daterange From extracted timeframe; null means episode-long.
patient_response enum done | not_yet | cannot_do | not_sure | none, PAT-005; cannot_do requires barrier_code, opens the reason-specific automation path.
supersedes_id uuid Set when a post-visit update replaces a task; original keeps state superseded and stays queryable (audit invariant).
idempotency guard unique(episode_id, natural_key) natural_key = category+source span hash, prevents duplicate tasks on reprocessing.

medication dimensions (MED-003/006 invariants)

The four dimensions are physically separated: Instruction Authority lives on medication_plan_item; Transmission, Fulfillment/Access, and Use are medication_status_event rows. Enforced invariants: (1) a status event can never mutate a plan item; (2) evidence_class=patient_report can never be rendered with verified styling, the API serializes verified:false and the design system binds badge color to that flag; (3) plan-item changes require a source_fact_id whose gate_status is published and source_level ≤7, or an explicit clinician confirmation (MED-010, VIS-007).

6.4 Indexing & Partitioning (day-one)

  • Hot paths: task_instance (tenant_id, episode_id, state, due_at); notification_message (tenant_id, scheduled_at) WHERE sent_at IS NULL; extracted_fact (source_document_id, gate_status); episode (tenant_id, state, day30_at).
  • audit_event monthly range partitions + BRIN on occurred_at; archived partitions detached after S3 WORM export (never dropped inside 6 y).
  • Transactional outbox table event_outbox (id, aggregate, event_type, payload, created_at, processed_at) written in the same transaction as any state change; notify-worker polls every 5 s, this is the mechanism behind every “within 60 seconds” requirement (PAT-003, ENG-004).