Care
Schema deep-dive · living document · #11 in the reading sequence
| Tables | episodes, appointments, provider_slots (db care, schema public)[1]; second ring: prescriptions, diagnostics_referrals, appointment_reminders_sent |
| Owner service | care (sole writer) |
| Locators | EP- / APT- / SLT- + 8 hex chars, minted app-side (no sequence) |
| Last updated | 2026-08-18 |
| Companion | ERD story, slide 11 · Care Pathways (narrative) · previous: Provider |
1. Scope and usage
Care is where the insurer stops adjudicating and starts coordinating: an Episode is one health concern being handled ("this member's physio issue"), an Appointment is a concrete booking against a provider inside it, and provider_slots is the slot inventory care itself owns (D-12, #1015). Prescriptions and diagnostics referrals hang off episodes as the second ring.
Two structural facts make this schema unlike every other Go service:
- It is the only service whose tables live on the
publicschema of its database rather than a service-named schema (caredb,public.episodes; everywhere else it isprovider.providers,claims.claims, …). - It is the only service using real foreign keys on locator values:
appointments.episode_locator REFERENCES episodes(locator)[1] (and the same for prescriptions and referrals). Elsewhere the pattern is uuid FKs in-service, locator soft refs across services; care joins its own tables on the external identifier itself.
Care is also the middle link of the platform's traceability spine: the triage_session_locator stamped here at booking is the same value claims stamps at submission, so chat → episode → appointment → claim is one join key end to end (§6).
2. Boundaries and relationships
| Care is not… | That concern lives in | Join |
|---|---|---|
| the provider directory | provider.providers; care re-validates a provider is ACTIVE over HTTP at every booking[2] and sources the bookable list from the provider service's internal read | provider_locator, no FK |
| the triage session | triage.sessions (AI service, separate DB). The link is a nullable, shape-checked uuid column - existence is deliberately never verified cross-service[3] | triage_session_locator |
| the claim | claims.claims carries its own triage_session_locator as "the reverse half of the triage double-entry"[4] - claims and episodes join through the session id, never directly | shared session uuid |
| an eligibility gate | nothing here checks coverage; verdicts (REQUIRES_REFERRAL etc.) are eligibility's, enforced by callers | - |
| the video call | link_out_url stores whatever the caller (or the Meet integration) supplies - care never runs the call. D-38 · #1136 | opaque URL |
| a notification sender | care only emits care.events; the notifications service consumes care.appointment.confirmed / .reminder and talks to Novu[5] | Kafka |
3. Structure
episodes
| Field | Type | Req | Notes |
|---|---|---|---|
id / locator | uuid / text | ✓ | PK / UNIQUE EP- + hex8 |
party_locator | text | ✓ | The patient's PTY-; indexed |
triage_session_locator | text | Nullable; CHECK enforces uuid shape[7]; partial index WHERE NOT NULL | |
care_type | text | ✓ | Free text; live vocab is dirty (see below) |
status | text | ✓ | Default 'OPEN'; OPEN | CLOSED | CANCELLED by convention |
summary | text | Written at close | |
created_at, closed_at | timestamptz | ✓/- | Bookkeeping / close stamp |
appointments
| Field | Type | Req | Notes |
|---|---|---|---|
id / locator | uuid / text | ✓ | PK / UNIQUE APT- + hex8 |
episode_locator | text | ✓ | Real FK to episodes(locator) - the locator-valued FK |
provider_locator | text | ✓ | Soft ref to the provider directory |
type | text | ✓ | Free-form; live: video, SEARCHED_SLOT, VIRTUAL_GP (see wart below) |
status | text | ✓ | Default 'PENDING'; PENDING | CONFIRMED | ATTENDED | NO_SHOW | CANCELLED |
slot_locator | text | The slot claimed or the synthetic searched-slot id | |
link_out_url | text | Caller-supplied video link; Meet link on confirm; kry:<id> ref on Kry bookings | |
scheduled_at | timestamptz | Part of the double-booking guard | |
cancellation_reason, cancellation_comment | text | #1131 taxonomy + free text[8] | |
notes, created_at | text / timestamptz | -/✓ |
provider_slots
| Field | Type | Req | Notes |
|---|---|---|---|
id / locator | uuid / text | ✓ | PK / UNIQUE SLT- + hex8 |
provider_locator | text | ✓ | Whose diary; indexed |
start_time, end_time | timestamptz | ✓ | The slot window |
status | text | ✓ | Default 'AVAILABLE'; AVAILABLE | BOOKED | BLOCKED; indexed |
appointment_locator | text | Back-pointer set on claim, cleared on release |
Field-by-field: what and why
Locators: EP-805e14fc - minted app-side as prefix + first 8 hex of a uuid[6]. No sequence, no counter table, no year segment - the only Go service that mints this way. Why acceptable here? Episodes are high-volume, member-facing-transient records; the hex8 space (4 billion) plus the UNIQUE constraint is collision-safe enough, and nothing downstream parses care locators for provenance. The cost: they sort in creation order only by created_at, never by locator.
episodes.triage_session_locator - nullable by design, not by neglect: the episode is created at booking, by the member-app, not at triage disposition (D-17, #1020 - "the episode is the we're committed to care signal; that commitment lives at booking"). A member who books directly without chatting first produces a NULL, honestly. When present it must be a canonical lowercase uuid: the handler normalises and 400s malformed values[3], and a NOT VALID CHECK mirrors the same rule at the storage layer[7] - including forbidding '', the exact ''-vs-NULL split that caused the docsvc invoice_locator incident. Existence in the triage DB is deliberately NOT checked: a malformed id is a caller bug worth a 400; a well-formed id care cannot resolve is not care's call to make. Live: 29 of 141 episodes carry a session link.
episodes.care_type and status - convention-only text, and the live data shows what convention-only buys: care_type holds GP (97), Physio (41), physio (2) and VIRTUAL_GP (1); status holds OPEN (136), open (2), CLOSED (1), CANCELLED (2). Mixed-case duplicates of the same vocabulary, written by different callers at different times. A wart, plainly: any WHERE care_type = 'Physio' silently misses rows.
appointments.type - free-form, and doubly conflated. Live values are video (65), SEARCHED_SLOT (1), VIRTUAL_GP (1): one names a delivery channel, one names a booking mechanism, one names a service type. The code branches on exactly two values - OWN_SLOT triggers the atomic slot claim[9] (zero live rows) and video triggers the Meet invite[10]. The channel-vs-type split is exactly what the standardised catalogue's channels_covered direction resolves: each appointment records its delivery channel, and eligibility compares it against the module's covered channels. Until then this column is both things at once.
appointments.link_out_url - the fulfilment seam (D-38, #1136). Care stores whatever it is given: a caller-supplied URL at booking, a Google Meet link written back on confirm of a video appointment (best-effort - an invite failure never fails the confirm)[10], or - a deliberate column-reuse - kry:<appointmentId> on Kry/Livi bookings so cancel can reach the right upstream booking without a schema migration[11]. Google Meet is the stated interim; the column is provider-agnostic.
cancellation_reason / cancellation_comment - the #1131 taxonomy: change_of_plans | feeling_better | provider_unavailable | scheduling_conflict | cost | other, validated in code[12], with other carrying the free-text comment. This is the data the late-cancellation forfeit rule needs (who cancelled, why, how close to scheduled_at); the forfeit itself is a plan-terms rule not yet computed anywhere.
provider_slots + the claim/release cycle - care owns slot inventory (D-12): booking an OWN_SLOT appointment claims the slot atomically with the appointment insert - status flips to BOOKED and appointment_locator back-points, in one transaction[9]; cancelling releases it (status back to AVAILABLE, back-pointer cleared) in the cancel transaction[13]. The slot-source seam is that SearchSlots has three interchangeable sources behind one API: the DB table, a deterministic in-memory generator over the ACTIVE provider directory (the live default - synthetic SLOT-<provider>-<time> ids, 2pm-8pm London weekdays, ~25% deterministically blocked)[14], and Kry/Livi real GP availability behind KRY_BOOKING_ENABLED (default off), whose opaque slot ids pass through verbatim[15]. Because the synthetic path stores its generated id in appointments.slot_locator, most live slot_locator values do not reference provider_slots rows - which is why that column has no FK while episode_locator does.
Double-booking guard - since the searched-slot path claims no DB row, what stops two members booking the same generated slot? A partial unique index: at most one active (not CANCELLED/NO_SHOW) appointment per (provider_locator, scheduled_at)[8], surfaced as ErrSlotTaken. The full TTL-hold (reserve-before-book) design is explicitly deferred.
appointment_reminders_sent - a one-column dedupe ledger: appointment_locator PRIMARY KEY[16]. The T-24h reminder sweep (15-minute in-process ticker) claims via INSERT … ON CONFLICT DO NOTHING in the same transaction as the outbox enqueue, so only the inserting run emits care.appointment.reminder - restart-safe and replica-safe[17][18].
4. Invariants
| Invariant | Enforced by |
|---|---|
| All three locators unique | DB unique constraints[1] |
| Appointment / prescription / referral belongs to a real episode | DB FK on the locator value[1] - unique in the estate |
triage_session_locator is a canonical uuid or NULL, never '' | Handler 400[3] + NOT VALID DB CHECK[7] |
| One active appointment per provider + time | Partial unique index[8] |
| Slot claim atomic with appointment insert; release atomic with cancel | Application transaction[9] |
| One reminder per appointment, ever | DB PK + same-tx claim[16] |
| Provider must be ACTIVE to book or add slots | Application, via HTTP to the provider service[2] |
| Cancellation reason in the v1 taxonomy | Application map[12] |
| One OPEN episode per (party, care type, triage session) | Application idempotency check on create - lookup-failure falls through to create (duplicate episode < refused booking)[19] |
care_type / status / type vocabularies | Nothing - no CHECKs, and the live mixed-case values prove it |
| Appointment status transitions form a valid machine | Nothing - UpdateAppointmentStatus applies any action to any current status[20] |
| Events reach Kafka | Transactional outbox (outbox table, topic care.events) drained by a background worker - never a direct publish[21] |
| Row changes captured to CDC | Debezium publication dbz_care (live \d) |
5. Lifecycle
The appointment is the state-bearing entity (episodes go OPEN → CLOSED/CANCELLED):
The diagram shows the intended machine; per §4 the code does not guard transitions, so attend on a CANCELLED appointment would succeed. Live distribution: 54 PENDING, 9 CANCELLED, 2 CONFIRMED, 1 ATTENDED, 1 NO_SHOW - the confirm step is not yet part of the routine booking flow, so most rows sit at PENDING, and the reminder sweep (which scans CONFIRMED rows) stays quiet until they advance.
What each transition emits on care.events (transactional outbox): care.episode.opened and .closed from the episode side[22], care.appointment.confirmed, .attended, .reminder from the appointment side. The book and cancel paths do not enqueue an outbox row, so a consumer learns of a cancellation by reading care rather than the stream (§9). Live outbox contents match: 145 opened, 3 confirmed, 1 attended, 1 reminder, 1 closed.
How the pieces run: booking validates the provider (or routes to Kry), then books[23]; the reminder ticker and outbox worker are goroutines in the service process[17] - care has no Temporal, no scheduler, no saga.
6. Populated example: one triage chat, walked to its booking
141 episodes and 68 appointments live. This is a real chain from 2026-07-31 (party locator real, summary fields empty as they are in the row):
The episode - EP-805e14fc
{
"locator": "EP-805e14fc",
"party_locator": "PTY-2026-000652",
"triage_session_locator": "9399a51e-fd81-4330-9dc4-575202ef55d3",
"care_type": "Physio",
"status": "OPEN",
"summary": null,
"created_at": "2026-07-31T13:10:25Z",
"closed_at": null
}| Key | Read by | What actually happens |
|---|---|---|
triage_session_locator | GET /internal/episodes?triageSessionLocator=…[24] + the care.episode.opened payload | "which care came out of this chat" resolves without polling; consumers can join chat → care off the stream |
same value on claims.claims | claims' partial index[4] | the full thread: this chat's session id joins triage → this episode → any claim filed from it, across three databases, no FK anywhere |
care_type: "Physio" | slot search's type→specialty map | the booking screen showed physios, not GPs |
status: "OPEN" + party + care type + session | create-idempotency[19] | a double-tap of Book returns this row instead of minting a sibling |
Its appointment - APT-22f465c8
{
"locator": "APT-22f465c8",
"episode_locator": "EP-805e14fc",
"provider_locator": "PRV-2026-000065",
"type": "video",
"status": "PENDING",
"slot_locator": "SLOT-PRV-2026-000065-20260731T133000Z",
"link_out_url": null,
"scheduled_at": "2026-07-31T13:30:00Z",
"cancellation_reason": null
}| Key | Read by | What actually happens |
|---|---|---|
episode_locator | the real FK | the DB itself refuses an appointment against a non-existent episode |
provider_locator: "PRV-2026-000065" | booking-time ValidateActive[2] | a real, ACTIVE directory provider - the searched-slot path books real providers against generated times |
slot_locator: "SLOT-PRV-…-133000Z" | nothing after booking | the synthetic searched-slot id[14] - it references no provider_slots row; the (provider, scheduled_at) unique index is what protects the time |
status: "PENDING" + scheduled_at | double-booking guard[8]; reminder sweep scans CONFIRMED rows only | holds 13:30 against this physio; the sweep picks it up once it is confirmed |
link_out_url: null | confirm path | populates with a Meet link when this video appointment is confirmed and the Meet client is configured |
A DB slot, for contrast - SLT-5e4dd8d8
{ "locator": "SLT-5e4dd8d8", "provider_locator": "PRV-GP-001",
"start_time": "2026-06-16T09:00:00Z", "end_time": "2026-06-16T09:30:00Z",
"status": "AVAILABLE", "appointment_locator": null }The only 3 live provider_slots rows all belong to PRV-GP-001 - a locator from the retired in-code stub list[14] that does not even match the directory's PRV-YYYY-NNNNNN format. The owned-inventory table, its atomic claim and its release are built and exercised by tests; the generated searched-slot path is what live traffic uses today. D-12 chose care-owns precisely so a provider-sync can fill this table behind the same API, with no change to the booking contract.
7. Who references care
| Where | Mechanism | Meaning there |
|---|---|---|
claims.claims | shared triage_session_locator value[4] | the traceability spine: triage → care → claim on one uuid |
| timeline service | GET /care/v1/episodes, party-scoped[25] | care lane of the member timeline |
| notifications service | consumes care.events (care.appointment.confirmed, .reminder)[5] | booking confirmations + T-24h reminders via Novu |
| triage / member-app | POST /episodes at booking (D-17); /internal/episodes?triageSessionLocator= for chat → outcome | the writers of the spine |
| provider service | referenced, never referencing - care calls it, it never calls care | one-way dependency (D-12) |
8. Design determinations
- Care owns slot inventory; provider-sync deferred - the slot table, atomic claim and release live here; the provider service has no availability concept. D-12 · #1015.
- Episode is created at booking by the member-app, not at triage disposition - triage disposition is advisory; commitment lives at booking. Hence
triage_session_locatornullable with a linkage-only contract. D-17 · #1020. - Fulfilment is a stored URL, not an integration - Google Meet interim;
link_out_urlstays provider-agnostic (and gets reused as the Kry back-ref). D-38 · #1136. - Shape validated, existence not - the triage link is checked to be a canonical uuid at the edge and in the DB, and deliberately never resolved cross-service. (§3; same contract on the claims side.)
- Interim double-booking guard over TTL holds - the partial unique index stops concurrent booking of one searched slot; reserve-before-book is explicitly v2. #1118. (§3)
- Events via transactional outbox only - no direct Kafka writes from the request path. (§4)
- Cancellation gets a taxonomy - six reasons,
other+ comment, feeding the late-cancellation-forfeit rule. #1131. (§3)
9. Caveats and extensibility
Group and individual. Care is purely party-scoped: party_locator in, everything else follows. No scheme, policy or plan-tier column exists here, so group and individual cover share this schema unchanged - whether the booking is covered is eligibility's question, asked by callers before booking, never recorded here.
The episode is the spine. One health concern gets one row, and every other care fact hangs off it: appointments by real FK, prescriptions and referrals the same way, the chat by triage_session_locator, the claim by that same session uuid on the claims side. Adding a care fact is adding a child of the episode; adding a source of care (a partner booker, a new modality, a new fulfilment surface) is filling a seam that already exists. The vocabularies here are free text and the fulfilment link is an opaque column on purpose: both were chosen so a new modality or partner arrives as data and configuration rather than a migration.
Extension points - when, what, where:
| When we need ... | What to add | Where |
|---|---|---|
| a new care modality (dermatology, podiatry, dietetics) | one enum entry plus one specialty mapping, and provider-directory rows carrying that specialty. episodes.care_type and appointments.type are free text, so the stored value is a value, not a migration | validProviderTypes in care/internal/handler/slots.go · providerTypeToSpecialty in care/internal/service/slot_search.go |
| real provider availability instead of generated slots | rows in provider_slots from a provider-availability sync, then flip search to prefer the table. The atomic claim, the release and the back-pointer are built and unit-tested[9][13] | provider_slots (D-12's chosen seam) · SearchSlots[14] |
| a second partner booking source beyond Kry/Livi | another implementation of care's four-method booking interface, a synthetic provider locator for it, and its own enable flag. Opaque slot ids already pass through verbatim[15] and the cancel round-trip rides the kry:-style back-ref[11] | care/internal/client (booking interface + noop) · KRY_BOOKING_ENABLED-shaped flag in care/internal/config/config.go |
| a new fulfilment surface (phone bridge, home visit, another video partner) | write that surface's URL or reference into link_out_url; the column is provider-agnostic by determination, and Meet is the current occupant, not the contract | appointments.link_out_url (D-38 · #1136)[10] |
| delivery channel separated from service type | adopt the standardised catalogue's channels_covered split so eligibility can compare a booking's channel against the module's covered channels; the live appointments.type values are the migration's input | appointments.type |
| a consumer that reacts to bookings and cancellations | an outbox enqueue in the book and cancel paths, on the existing care.events topic; the outbox, the worker and the payload shape of every other transition are already in place | BookAppointment[23] · the cancel arm of UpdateAppointmentStatus[20] |
| the late-cancellation forfeit computed | the rule and the ledger entry it writes; who cancelled, why, and how close to scheduled_at are captured at cancel time | plan-terms rule · CancellationReasons[12] |
| TTL slot holds (reserve-before-book) | a hold row or a RESERVED slot status with an expiry sweep; the partial unique index holds the line meanwhile, so this is an upgrade, not a rescue | provider_slots · uniq_active_provider_slot[8] |
| a third surface joined to the same chat | stamp triage_session_locator on the new record with the same uuid CHECK + partial index. The double-entry (episode here, claim there) extends by adding a stamp - no FK, no cross-service lookup, no change to the two existing halves | episodes.triage_session_locator[7] · claims.claims[4] |
Prescriptions, diagnostics referrals, the provider-slot operations and the reminder ledger hang off this same spine and are covered in Care fulfilment (#16).
Ahead of need: the OWN_SLOT claim path matches zero live rows because live traffic books generated slots - the branch, its transaction and its tests are the inventory story arriving before the provider sync that feeds it. Most slot_locator values reference no provider_slots row for the same reason, which is why that column carries no FK while episode_locator does.
Known warts, stated (each as the fix and where it goes):
- Mixed-case vocabularies -
care_typeholdsPhysio/physio,statusholdsOPEN/open, soWHERE care_type = 'Physio'misses rows. Fix: normalise on write in the episode handler and backfill + CHECK in a newservices/care/migrations/step. - Unguarded status transitions -
attendon a CANCELLED appointment succeeds. Fix: a from→to transition table inUpdateAppointmentStatus[20], rejecting anything off the §5 machine. - Book and cancel emit no outbox row - the two transitions a consumer most wants are the two not on the stream. Fix: enqueue in the same transaction as the write, exactly as confirm/attend do (table row above).
CancelEpisodeneither emits an event nor cascades to the episode's appointments[26], so a cancelled episode can keep live bookings. Fix: cancel the child appointments (releasing their slots) and enqueuecare.episode.cancelledin that transaction.- Phantom goose version 4 - the migration history carries a version with no file behind it, which is why the triage-link migration is numbered 00005[7]. Fix: leave it documented, or reconcile
goose_db_versionon a clean rebuild. - Fallback topic default disagrees with the rows - the worker's configured default is
care-events(dash) while every enqueued row sayscare.events(dot); the per-row topic wins, so nothing misroutes today. Fix: change the default incare/internal/config/config.go[27].
References
Code links are pinned to commit 8329d7b on main (2026-08-19); the file is the anchor if lines drift. Pins are checked mechanically by docs/site/scripts/check-code-refs.py.
care/migrations/00001_care_schema.sql- episodes L3-13 · provider_slots L18-27 · appointments L32-44 (locator FK L35) · prescriptions L48-59 · diagnostics_referrals L61-72care/internal/client/provider.go#L49-ValidateActiveagainst the provider directorycare/internal/handler/episodes.go#L25-normaliseTriageSessionLocator: NULL-not-'' + shape-yes/existence-no rationaleclaims/migrations/0012_claims_triage_session.sql- "the reverse half of the triage double-entry"; claims-side write atclaims.go#L82notifications/internal/projection/consumer.go#L37-care.appointment.confirmed/.reminderconsumers; topic subscription atconfig.go#L88care/internal/service/service.go#L73-newLocator: prefix + hex8 of a uuidcare/migrations/00005_episodes_triage_session_idx.sql- phantom-00004 note L3-10 · partial index L21-23 · uuid CHECK (NOT VALID, forbids '') L31-36care/migrations/00004_cancellation_and_slot_guard.sql- cancellation columns (#1131) +uniq_active_provider_slot(#1118)care/internal/service/service.go#L313- atomic OWN_SLOT claim: appointment insert + slot BOOKED + back-pointer, one txcare/internal/service/service.go#L569- best-effort Meet invite on confirm ofvideo, link written tolink_out_urlcare/internal/service/service.go#L20- Kry constants + thekry:LinkOutURL reusecare/internal/service/service.go#L500-CancellationReasonsv1 taxonomycare/internal/service/service.go#L639- slot release on cancel (AVAILABLE + back-pointer cleared)care/internal/service/slot_search.go#L210- deterministic slot generation, syntheticSLOT-id at L231; retired stub list L33-47care/internal/service/slot_search.go#L156- Kry GP slots: 90-day clamp, opaque ids verbatimcare/migrations/00006_appointment_reminders_sent.sql- reminder dedupe ledger, PK = appointment_locatorcare/internal/service/reminders.go#L43-SweepReminders: claim-in-tx + enqueue; ticker loop L25-39care/internal/repository/gorm_appointments.go#L61-TryMarkReminderSent: INSERT ON CONFLICT DO NOTHINGcare/internal/service/service.go#L102- episode create-idempotency (same party + care type + session, still OPEN)care/internal/service/service.go#L528-UpdateAppointmentStatus: confirm / attend / cancel / no-show, no current-state guardcare/internal/service/service.go#L32- outbox-only event posture (Deps comment); worker atoutbox/worker.go#L53care/internal/service/service.go#L130-care.episode.openedpayload incl. nullabletriageSessionLocatorcare/internal/service/service.go#L264-BookAppointment: precondition reads outside the tx, Kry routing, ACTIVE gatecare/internal/handler/handler.go#L116-/internal/episodes?triageSessionLocator=chat → outcome lookup; public routes L82-113timeline/src/sources/care.ts#L23- timeline's party-scoped episode readcare/internal/service/service.go#L217-CancelEpisode: no event, no appointment cascadecare/internal/config/config.go#L61-KAFKA_CARE_TOPICdefaultcare-events(fallback only; rows carrycare.events)
Live-schema facts (constraint list including the locator FKs and the NOT VALID CHECK, Debezium publication, care_type / status / type distributions, outbox event-type counts, slot and reminder row counts) come from PGPASSWORD=… psql -h 10.0.1.2 -U olly -d care · \d episodes, \d appointments, \d provider_slots, \d outbox, select care_type, count(*) …, 2026-08-18. The traceability check on the claims side comes from psql -h 10.0.1.2 -d claims · \d claims.claims, same date.