content/engineering/reference/data-model.html

<article class="doc" id="doc-reference-data-model" aria-hidden="true"><div class="strip"><span class="path">reference/data-model.md</span><span class="tag">Engineering, reference</span><span class="meta">~3 min read</span></div>
<h1>6. Canonical Data Model</h1>
<h2 id="doc-reference-data-model--h1">6.1 Conventions</h2>
<ul>
<li>Primary keys: UUIDv7 (<code>id</code>). Every PHI-bearing table carries <code>tenant_id</code> (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.</li>
<li>Timestamps: <code>created_at</code>/<code>updated_at</code> UTC (timestamptz); clinical times keep source timezone in a companion <code>*_tz</code> column when supplied.</li>
<li>Nothing is hard-deleted. Lifecycle is expressed in <code>status</code> columns; caregiver revocation, consent withdrawal, and supersession are state changes with audit events.</li>
<li>Optimistic concurrency: <code>version</code> (int) on <code>episode</code>, <code>task_instance</code>, <code>medication_plan_item</code>, <code>brief_snapshot</code>; writers must supply expected version.</li>
<li>Money/none in MVP; quantities as <code>numeric(12,4)</code>; enums are Postgres enums migrated via Alembic.</li>
</ul>
<h2 id="doc-reference-data-model--h2">6.2 Entity Catalog</h2>
<div class="tbl-wrap"><table class="col-table compact"><colgroup><col class="col-md"/><col class="col-md"/><col class="col-lg"/></colgroup>
<thead>
<tr>
<th>Entity</th>
<th>Purpose</th>
<th>Key Fields (beyond id/tenant/timestamps)</th>
</tr>
</thead>
<tbody>
<tr>
<td data-label="Entity">tenant / cohort</td>
<td data-label="Purpose">Customer + clinical population config container.</td>
<td data-label="Key Fields (beyond id/tenant/timestamps)">name, <code>config_version</code>, cohort criteria JSON, episode_template_id</td>
</tr>
<tr>
<td data-label="Entity">patient</td>
<td data-label="Purpose">Demographics + identity linkage.</td>
<td data-label="Key Fields (beyond id/tenant/timestamps)">mrn (per tenant), fhir_patient_id, name, dob, phones[], language, tz, accessibility_prefs</td>
</tr>
<tr>
<td data-label="Entity">episode</td>
<td data-label="Purpose">One 30-day transition instance.</td>
<td data-label="Key Fields (beyond id/tenant/timestamps)">patient_id, cohort_id, state, <code>version</code>, index_encounter_id, discharge_at, day30_at, activation_at, closed_at, close_reason</td>
</tr>
<tr>
<td data-label="Entity">consent_record</td>
<td data-label="Purpose">Versioned consent artifacts.</td>
<td data-label="Key Fields (beyond id/tenant/timestamps)">episode_id, type(program|caregiver|comm_channel), scope JSON, granted_by, granted_at, withdrawn_at, doc_version</td>
</tr>
<tr>
<td data-label="Entity">caregiver + caregiver_grant</td>
<td data-label="Purpose">Separate identity + scoped access.</td>
<td data-label="Key Fields (beyond id/tenant/timestamps)">caregiver: name, phone, relationship; grant: episode_id, scope JSON, status(active|revoked), revoked_at</td>
</tr>
<tr>
<td data-label="Entity">clinician + access_grant</td>
<td data-label="Purpose">External clinician + purpose-limited grant.</td>
<td data-label="Key Fields (beyond id/tenant/timestamps)">clinician: npi, name, email, org; grant: episode_id, purpose, expires_at, device_fingerprints[], status, step_up_at</td>
</tr>
<tr>
<td data-label="Entity">source_document</td>
<td data-label="Purpose">Ingested artifact + fingerprint.</td>
<td data-label="Key Fields (beyond id/tenant/timestamps)">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</td>
</tr>
<tr>
<td data-label="Entity">extracted_fact</td>
<td data-label="Purpose">LLM/parsed proposition pre/post gates.</td>
<td data-label="Key Fields (beyond id/tenant/timestamps)">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</td>
</tr>
<tr>
<td data-label="Entity">task_instance</td>
<td data-label="Purpose">Universal task (all patient/clinician actions).</td>
<td data-label="Key Fields (beyond id/tenant/timestamps)">episode_id, category(medication|appointment|lab|education|safety|admin), priority(critical|important|routine), state (11), due_at, source_fact_id, supersedes_id, <code>version</code></td>
</tr>
<tr>
<td data-label="Entity">medication_plan_item</td>
<td data-label="Purpose">Instruction authority dimension.</td>
<td data-label="Key Fields (beyond id/tenant/timestamps)">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</td>
</tr>
<tr>
<td data-label="Entity">medication_status_event</td>
<td data-label="Purpose">Evidence dimensions (transmission/access/use).</td>
<td data-label="Key Fields (beyond id/tenant/timestamps)">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)</td>
</tr>
<tr>
<td data-label="Entity">appointment_requirement / appointment</td>
<td data-label="Purpose">Need vs. booked visit.</td>
<td data-label="Key Fields (beyond id/tenant/timestamps)">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</td>
</tr>
<tr>
<td data-label="Entity">notification_message</td>
<td data-label="Purpose">Every outbound touch.</td>
<td data-label="Key Fields (beyond id/tenant/timestamps)">episode_id, task_id, channel(push|sms|voice|email), template_id+version, lang, scheduled_at, sent_at, delivery_status, suppress_reason</td>
</tr>
<tr>
<td data-label="Entity">agent_action</td>
<td data-label="Purpose">Evidence record of every agent step.</td>
<td data-label="Key Fields (beyond id/tenant/timestamps)">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</td>
</tr>
<tr>
<td data-label="Entity">brief_snapshot</td>
<td data-label="Purpose">Immutable clinician brief.</td>
<td data-label="Key Fields (beyond id/tenant/timestamps)">episode_id, appointment_id, content JSON, episode_version_at_build, s3_pdf_key, generated_at, opened_at[], stale_superseded_by</td>
</tr>
<tr>
<td data-label="Entity">outcome_event</td>
<td data-label="Purpose">ED/readmission signals.</td>
<td data-label="Key Fields (beyond id/tenant/timestamps)">episode_id, type(ed|readmit), source(adt|claims|hie|patient_report), facility, occurred_at, within_30d bool, completeness_note</td>
</tr>
<tr>
<td data-label="Entity">audit_event</td>
<td data-label="Purpose">Append-only ledger (own schema).</td>
<td data-label="Key Fields (beyond id/tenant/timestamps)">actor_type/id, action, object_type/id, purpose, context JSON, ip, trace_id, occurred_at, monthly partitions</td>
</tr>
<tr>
<td data-label="Entity">config_version / template / model_registry</td>
<td data-label="Purpose">Versioned governance artifacts.</td>
<td data-label="Key Fields (beyond id/tenant/timestamps)">published_by/at, diff JSON, approval_ref; model_registry: model_id, prompt_version, schema_version, safety_policy_version, status(active|rollback)</td>
</tr>
</tbody>
</table></div>
<h2 id="doc-reference-data-model--h3">6.3 Core Table Details</h2>
<h3 id="doc-reference-data-model--h4">task_instance (authoritative for everything the patient sees)</h3>
<div class="tbl-wrap"><table class="col-table compact"><colgroup><col class="col-auto"/><col class="col-xs"/><col class="col-lg"/></colgroup>
<thead>
<tr>
<th>Field</th>
<th>Type</th>
<th>Rule</th>
</tr>
</thead>
<tbody>
<tr>
<td data-label="Field">state</td>
<td data-label="Type">enum(11)</td>
<td data-label="Rule">proposed, required, scheduled, in_progress, completed, blocked, pending_clinical_decision, declined, superseded, cancelled, unresolved_at_close. Transitions only via §7.1.</td>
</tr>
<tr>
<td data-label="Field">priority</td>
<td data-label="Type">enum</td>
<td data-label="Rule">critical | important | routine, set by deterministic rules on <code>category</code>+<code>source_fact</code> (e.g., new/changed cardiac med → critical); drives notification tier and Today ordering.</td>
</tr>
<tr>
<td data-label="Field">due_at / window</td>
<td data-label="Type">timestamptz / daterange</td>
<td data-label="Rule">From extracted timeframe; <code>null</code> means episode-long.</td>
</tr>
<tr>
<td data-label="Field">patient_response</td>
<td data-label="Type">enum</td>
<td data-label="Rule">done | not_yet | cannot_do | not_sure | none, PAT-005; <code>cannot_do</code> requires <code>barrier_code</code>, opens the reason-specific automation path.</td>
</tr>
<tr>
<td data-label="Field">supersedes_id</td>
<td data-label="Type">uuid</td>
<td data-label="Rule">Set when a post-visit update replaces a task; original keeps state <code>superseded</code> and stays queryable (audit invariant).</td>
</tr>
<tr>
<td data-label="Field">idempotency guard</td>
<td data-label="Type">unique(episode_id, natural_key)</td>
<td data-label="Rule">natural_key = category+source span hash, prevents duplicate tasks on reprocessing.</td>
</tr>
</tbody>
</table></div>
<h3 id="doc-reference-data-model--h5">medication dimensions (MED-003/006 invariants)</h3>
<p>The four dimensions are physically separated: <strong>Instruction Authority</strong> lives on <code>medication_plan_item</code>; <strong>Transmission</strong>, <strong>Fulfillment/Access</strong>, and <strong>Use</strong> are <code>medication_status_event</code> rows. Enforced invariants: (1) a status event can never mutate a plan item; (2) <code>evidence_class=patient_report</code> can never be rendered with verified styling, the API serializes <code>verified:false</code> and the design system binds badge color to that flag; (3) plan-item changes require a <code>source_fact_id</code> whose gate_status is <code>published</code> <strong>and</strong> source_level ≤7, or an explicit clinician confirmation (MED-010, VIS-007).</p>
<h2 id="doc-reference-data-model--h6">6.4 Indexing &amp; Partitioning (day-one)</h2>
<ul>
<li>Hot paths: <code>task_instance (tenant_id, episode_id, state, due_at)</code>; <code>notification_message (tenant_id, scheduled_at) WHERE sent_at IS NULL</code>; <code>extracted_fact (source_document_id, gate_status)</code>; <code>episode (tenant_id, state, day30_at)</code>.</li>
<li><code>audit_event</code> monthly range partitions + BRIN on occurred_at; archived partitions detached after S3 WORM export (never dropped inside 6 y).</li>
<li>Transactional <strong>outbox</strong> table <code>event_outbox (id, aggregate, event_type, payload, created_at, processed_at)</code> 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).</li>
</ul>
<div class="pn"><a class="pn-prev" href="#"></a><a class="pn-next" href="#"></a></div></article>