Schema deep-dives

One page per core entity: what every field is and why it exists, the invariants (DB-enforced vs convention), lifecycle flows, a populated JSON example, and design determinations - with every claim carrying an inline citation pinned to the codebase. Living documents: the code link is the anchor, the page keeps up.

The purpose of the series is to document the platform's architectural flexibility and extensibility: what the model can already absorb, and where to make the change when a need arrives. The schema deliberately carries capability ahead of product maturity. Tables that are empty today are designed headroom, and each page's extension-point table (When we need ... | What to add | Where) names the trigger, the change and the surface to touch. That is a property of the design, not a shortfall.

Read them in order - the sequence builds the domain the same way the ERD story does, one concept at a time, each new entity motivated by what the previous ones cannot express.

Reading sequence

#	Deep-dive	Tables	Status
1	Party	parties	published
2	Scheme & roster	schemes, scheme_members	published
3	Product & catalogue	products, product_versions, clinical_terms	published
4	Quote & Policy	quotes, policies	published
5	Terms, transactions & elements	policy_terms, policy_transactions, policy_elements	published
6	Eligibility projection	member_coverage, coverage_accumulators	published
7	Claim	claims, claim_lines, claim_events	published
8	Billing account & charges	accounts, charges	published
9	Invoices, payments & ledger	invoices, invoice_line_items, payments, ledger_entries	published
10	Provider	providers	published
11	Care	episodes, appointments	published
12	Consent, documents & notifications	consent_records, documents, notification_log	published

Phase 2 - transactional depth

The twelve pages above introduce each core entity once. These eight go a layer deeper on the domains already built - the workflow tables, audit ledgers, and config that sit beside a head table. Each is best read after the Phase-1 page it extends, not in strict numeric order. Several model capability ahead of the stage that exercises it: the tables are coded and migrated, and each page names the trigger that switches them on.

#	Deep-dive	Tables	Extends
13	Prior authorization	prior_auths, claim_documents	Claim
14	Billing lifecycle	adjustments, installment_schedules, installment_items, autopay_preferences, quote_payments, stripe_events, reconciliation_drifts	Invoices, payments & ledger
15	Enrollment: dynamic fields & underwriting	policy_field_values, quote_field_values, transaction_field_values, quote_events, underwriting_flags	Terms, transactions & elements
16	Care fulfilment	prescriptions, diagnostics_referrals, provider_slots, appointment_reminders_sent	Care
17	Consent audit & data-subject rights	consent_audit, deletion_requests	Consent, documents & notifications
18	Provider credentialing & reviews	credentialing_requests, provider_reviews	Provider
19	Scheme operations	bulk_enrollment_jobs, slack_workspaces, slack_member_links	Scheme & roster
20	Operational depth	notification_preferences, templates, accumulator_applications	Eligibility · Claim · Consent, docs & notifications

Phase 3 - wearable streams

A separate domain from the insurance spine above. The balance service ingests raw wearable samples and compacts them into episodes, rollups, baselines, scores and findings. These five pages read as a pipeline - each stage feeds the next

• so read them in order, though they stand apart from the Phase-1/2 sequence. Owner throughout: the balance service (runs on dev-1; data on dev-2, schema streams). Much of the analysis layer is modelled ahead of the stage that exercises it: coded and migrated, with each page naming where it switches on.

#	Deep-dive	Tables	Stage
21	Model & ingest	event_raw, sync_state, connection, deletion_log	raw sample intake
22	Registries	metric, episode_type, met_activity, metric_alias, activity_alias	the reference vocabulary
23	Compaction pipeline	episode, rollup, baseline	raw to derived
24	Movement & shape scores	movement_score, shape_score, shape_contribution	scoring
25	Findings & profile	finding, finding_evidence, insight, knowledge_gap, profile, onboarding_state, self_assessment	analysis + member state

Companions: the ERD story (slide-per-step visual walk), the full ERD, and worked examples - a real policy · a real product.

On the product side two companion notes go deeper than a schema page should: Product catalogue: the shape for why every module carries the same keys, and What a UK product specification has to say for what a product sold in the UK must state beyond its cover.

The format

Each page follows the same contract (adapted from FHIR resource pages, in Oxide living-doc discipline, with GoCardless-style flow diagrams):

1. Scope and usage - what the entity is for
2. Boundaries and relationships - what it is not, and where that concern lives
3. Structure - every field: what and why
4. Invariants - DB-enforced vs application vs convention, stated honestly
5. Lifecycle - states and flows (mermaid)
6. Populated example - real-shape JSON
7. Who references it - the locator fan-out
8. Design determinations - numbered, linked to decision records
9. Caveats and extensibility - honest caveats, plus an extension-point table (When we need ... | What to add | Where) for every capability not yet exercised, including how group and individual cover share the design
10. References - every citation pinned to a commit SHA

Citations are checked mechanically rather than by eye. check-code-refs.py resolves each cited range at the pinned commit and at current main and reports anything that moved or changed, so a claim cannot quietly outlive the code it points at. The Phase-1 pages (#1-#12) pin to 8329d7b (2026-08-19); the Phase-2 and Phase-3 pages (#13-#25) pin to b61c5802 (2026-08-21), where they all resolve clean. As main advances a pin drifts by design - the check surfaces it, and it is re-read on a repin pass rather than silently re-aimed.

Pages #14, #21, #23 and #24 (billing-lifecycle, streams-overview-ingest, streams-compaction, streams-scores) were re-read and repinned to 08e65216 (2026-08-28), after the D2C billing write path landed (olly#1739) and the wearable-sync compaction trigger moved from the in-process APScheduler to a Temporal workflow run by its own worker. The rest of the series still pins as above. Sections describing unmerged branches (billing-lifecycle §3.1 and invoices-payments-ledger §10/§10.1, both ENG-454) cite plain path:line and pin when that work merges.