Eligibility projection
Schema deep-dive · living document · #6 in the reading sequence
| Tables | eligibility.member_coverage, eligibility.coverage_accumulators, eligibility.accumulator_applications, eligibility.projection_checkpoints[1][2][3][4] |
| Owner service | eligibility (sole writer; every row derived from enrollment events or a claims apply call) |
| Locator | none minted here - rows are uuid-keyed and carry PTY- / POL- / SCH- / CLM- references |
| Last updated | 2026-08-19 |
| Companion | ERD story, slide 6 · real policy JSON · Eligibility & Accumulators (narrative) · previous: Terms, transactions & elements |
1. Scope and usage
The eligibility schema is the platform's CQRS read model: it answers "is this person covered for X today, and how much of the allowance is left" in a single-table read, at booking speed. It holds no source of truth. Enrollment owns the policy; the product catalogue owns the terms; this schema holds a flattened, query-shaped copy of both, maintained by consuming enrollment's Kafka events[5].
Four tables, four jobs:
member_coverage- what is covered: one row per (member, benefit element, term), with the catalogue snapshot inlined.coverage_accumulators- how much has been used: one counter per (member, term, metering type, benefit key).accumulator_applications- which claim line used it: the idempotency ledger that makes consumption exactly-once.projection_checkpoints- consumer bookkeeping: last processed (topic, partition, offset).
The model is eventually consistent by design: a just-issued policy becomes adjudicable only once its event has been projected, and the narrative page states the trade plainly - adjudication reads are single-table and fast, and a coverage gap usually means an unprojected event, not missing enrollment data.
2. Boundaries and relationships
| The projection is not… | That concern lives in | Join |
|---|---|---|
| the source of truth for cover | enrollment.policies / policy_elements; this schema is rebuilt from their events (policy.issued, element.added, policy.renewed, …)[5] | Kafka enrollment-events[6] |
| the product catalogue | policy_admin.product_versions.element_schema; each coverage row carries a coverage_terms snapshot of it (see Product & catalogue) | jsonb copy, not a ref |
| the claim | claims.claims / claim_lines; claims consumes allowance by calling PATCH /internal/members/{locator}/accumulators/{term}/apply over HTTP[7] | claim_locator / claim_line_id text in the ledger |
| membership | group_scheme.scheme_members; roster rows never gate eligibility - coverage starts at enrol (D-07, #1010) | scheme_locator, carried through |
| the consumer's resume position | Kafka consumer-group offsets (group eligibility-projection[8]); projection_checkpoints is written on every message[9] as the lag signal; the resume position itself stays in Kafka | - |
Zero foreign keys, on purpose. Not one table in this schema has an FK - not even to each other. Everything joins by uuid or locator value. This is the cross-service no-FK rule taken to its logical end: a projection that FK'd anything would block on the very data it exists to decouple from.
3. Structure
member_coverage
| Field | Type | Req | Notes |
|---|---|---|---|
id | uuid | ✓ | PK, internal |
party_id, policy_id, policy_element_id, element_static_id, term_id | uuid | ✓ | The provenance quintet - see below |
policy_locator, party_locator | text | ✓ | POL- / PTY- for human-facing reads; party_locator is the lookup key for checks |
scheme_locator | text | Added by migration 0008 for the employer utilisation panel; empty for direct-to-consumer[11] | |
product_code | text | ✓ | NOT NULL but empty string on live rows (see §9) |
element_type | text | ✓ | Two vocabularies live (see §9) |
coverage_terms | jsonb | Snapshot of the catalogue at projection time | |
status | text | ✓ | ACTIVE | TERMINATED | INACTIVE - convention, no CHECK |
effective_from / effective_to | date | ✓/- | The validity window; effective_to NULL = open, exclusive when set |
UNIQUE (party_id, element_static_id, term_id, effective_from)[1].
coverage_accumulators
| Field | Type | Req | Notes |
|---|---|---|---|
party_id, term_id | uuid | ✓ | Whose allowance, which policy year |
accumulator_type | text | ✓ | SESSION_LIMIT | BENEFIT_LIMIT | USAGE (live vocabulary; see below) |
coverage_term_key | text | ✓ | The catalogue module key: gp_video, diagnostics, … |
limit_amount / consumed_amount | numeric(14,4) | ✓ | The ceiling and the running total (consumed defaults 0) |
currency | text | ✓ | Default 'GBP' - every live row is GBP |
last_claim_locator | text | Legacy dedup guard, superseded by the ledger; still written (see §9) |
UNIQUE (party_id, term_id, accumulator_type, coverage_term_key)[2].
accumulator_applications
| Field | Type | Req | Notes |
|---|---|---|---|
claim_locator | text | ✓ | The consuming CLM- |
claim_line_id | text | ✓ | TEXT, not uuid: a line without an id falls back to its coverage-term key[12] |
party_id, term_id, accumulator_type, coverage_term_key | uuid/text | ✓ | Which accumulator this application moved |
amount | numeric(14,4) | ✓ | In the accumulator's own metering unit - sessions or pounds |
applied_at | timestamptz | ✓ | Default now() |
UNIQUE (claim_locator, claim_line_id)[3] - the constraint is the mechanism (see below).
projection_checkpoints
(topic, partition, "offset", processed_at), UNIQUE (topic, partition). One live row: enrollment-events / 0.
Field-by-field: what and why
The provenance quintet - five uuids per coverage row because each answers a different question. party_id - who. policy_id - under which contract. term_id - in which policy year (the accumulator scope). policy_element_id
- which exact element version produced this row.
element_static_id- the identity that is stable across element versions, and therefore the one in the unique key: when an endorsement re-versions an element, the new event must land on the same projection row, not beside it. The upsert converges on the latest event by design - the repository comment records that a DoNothing conflict policy once left an upgraded member TERMINATED with no active cover[13].
coverage_terms - the same catalogue bytes the policy element snapshotted at issue, carried again here so an eligibility check is one read: active row → parse modules → verdicts[14]. EvaluateExclusions reads it for channel exclusions, referral requirements and the MANUAL_REVIEW surfacings described on the product page.
effective_from / effective_to - the adjudication window. GetActiveByPartyLocator filters status = 'ACTIVE' AND effective_from <= date AND (effective_to IS NULL OR effective_to > date)[15] - so effective_to is exclusive. Terminate stamps it and flips status on every open row for the static id[16].
accumulator_type - the metering archetype, seeded from the catalogue through the shared reader[17]: unit: "sessions" + included_per_cycle → SESSION_LIMIT; cycle_limit → BENEFIT_LIMIT; unmetered → USAGE with a zero limit. The projection's comment is explicit that an unmetered module still gets a row, because consumption against it must remain recordable or "unmetered" silently means "untracked". Since 2026-08-18 unmetered is a declared unit rather than an absent limit, so "no ceiling" and "nobody filled it in" no longer produce the same row. Live counts: 1 404 SESSION_LIMIT, 948 USAGE, 468 BENEFIT_LIMIT. A legacy flat-key path (annual_deductible, oop_max, benefit_limit) would mint key-named types[19]; zero such rows exist. Per-benefit annual money caps are standard PMI practice - benefits run "usually up to a stated annual limit" - and the catalogue's cycle_limit is exactly that number landing in limit_amount.
term_id scoping on accumulators - allowances are per policy year, not per policy. PMI cover is written "typically on a rolling monthly or an annually renewable basis" , and the schema encodes it: policy.renewed terminates the old coverage rows and seeds fresh accumulators under the new term id[20] - the reset is a new counter, never an UPDATE of the old one, so history survives renewal.
The ledger INSERT is the lock - applying a claim line is a non-idempotent read-modify-write (consumed_amount = consumed_amount + n). The previous guard, last_claim_locator != $claim, only remembered the most recent claim: the sequence apply(A), apply(B), apply(A) sailed through and counted A twice. The migration comment tells this story in full[3]. Now Apply runs one transaction: INSERT … ON CONFLICT (claim_locator, claim_line_id) DO NOTHING; if the insert took no row it is a replay and returns before touching the accumulator; if the subsequent UPDATE moves no accumulator, a sentinel error rolls the ledger row back so the slot is not banked without the burn[21].
Sessions vs pounds is eligibility's call - the claims caller sends both quantity and amount per line; consumedUnits picks which one the stored accumulator's type meters (SESSION_LIMIT/USAGE count events, everything else counts money)[22], and resolveAccumulatorType lets the stored row win over any caller hint[23] - claims deliberately keeps no term-key → type map to drift.
projection_checkpoints.partition - named partition here, while the same table in billing and notifications names it partition_id[24][25] - a naming drift between three hand-rolled copies of the same idea. This copy also pairs it with "offset", which needs quoting in every statement that touches it[26].
4. Invariants
| Invariant | Enforced by |
|---|---|
| One projection row per (party, static element, term, start date) | DB UNIQUE[1] |
| One accumulator per (party, term, type, key) | DB UNIQUE[2] |
| A claim line consumes at most once | DB UNIQUE (claim_locator, claim_line_id) + the INSERT-claims-the-slot transaction[21] |
| Ledger row ⇔ accumulator moved | Application: same transaction, sentinel rollback when no accumulator exists[21] |
| Upsert refreshes ceiling, never consumption | Application: conflict updates limit_amount only[27] |
| A member checks only their own eligibility | Application: verified party_locator claim overrides the request body (#1544)[28] |
status / element_type / accumulator_type vocabularies | Nothing - no CHECK on any of them; conventions held by the writers |
consumed_amount <= limit_amount | Nothing, deliberately - consumption counts past the included allowance (that is the contribution-payable zone; see §6) |
| Every event is projected | Not guaranteed - the consumer is log-and-skip and always advances the offset[29]; a failed handler is a logged gap, not a retry |
| Row changes captured to CDC | Debezium publication dbz_eligibility (all four tables, live \d) |
5. Lifecycle
Rows here have no lifecycle of their own - they shadow enrollment's. The two write paths, end to end:
Nine event types are handled[5]. Three shapes worth knowing: policy.issued bootstraps by fetching the full policy back from enrollment over HTTP (a thin event, a fat fetch)[30]; element.updated and policy.endorsed terminate-then-upsert under the same static id; policy.renewed terminates the old term's rows and re-seeds coverage and accumulators under the new term_id[20] - the annual reset.
Consumption is synchronous and claims-initiated: every path that lands a claim in APPROVED calls the apply endpoint, and idempotency is what lets it call unconditionally[31] (the full claim-side story is the next page).
6. Populated example: PTY-2026-000459, walked end to end
One live member, all four tables. The coverage row (no PII lives in this database; locators and uuids are real):
{
"id": "bf6cdada-ea4c-4c53-a817-d3875a630d92",
"party_id": "53b82c7e-15cb-5daf-a04d-bee5f4c664cd",
"policy_id": "a5a79e39-94fd-449c-9a2d-53b30c5f0721",
"policy_element_id": "da9c5563-7ea2-4353-a83d-5543ed0b02f8",
"element_static_id": "fa1d5f6f-d181-4ccb-9cd4-c74d2fb774b0",
"term_id": "e5e93520-4367-4aea-8c97-fc3aa8db62c8",
"policy_locator": "POL-2026-001315",
"party_locator": "PTY-2026-000459",
"scheme_locator": "SCH-2026-001126",
"product_code": "",
"element_type": "employee",
"coverage_terms": { "…": "the full OHC-2026 catalogue snapshot - tier STANDARD, six modules, premium_per_member_monthly 19.5" },
"status": "ACTIVE",
"effective_from": "2026-07-12",
"effective_to": null
}coverage_terms is byte-for-byte the catalogue walked on the product page; note the empty product_code (§9).
The accumulators the projection seeded from it
Live rows for (party 53b82c7e…, term e5e93520…):
| accumulator_type | coverage_term_key | limit | consumed | last_claim_locator |
|---|---|---|---|---|
| SESSION_LIMIT | gp_video | 5 | 9 | CLM-2026-002725 |
| SESSION_LIMIT | physio_remote | 5 | 26 | CLM-2026-002730 |
| SESSION_LIMIT | mental_health | 5 | 2 | CLM-2026-002684 |
| BENEFIT_LIMIT | diagnostics | 250.00 | 0.00 | - |
| USAGE | digital | 0 | 0 | - |
| USAGE | neurodiversity | 0 | 0 | - |
| Observation | What it tells you |
|---|---|
gp_video 9 of 5, physio_remote 26 of 5 | Consumption counts past the included allowance by design - there is no ceiling check on Apply. Sessions 6+ are the contribution-payable zone (contribution_after_included: 25.0 in the snapshot); the counter keeps the truthful total either way |
digital / neurodiversity at limit 0 | The unmetered-still-metered rule[18]: USAGE rows exist so cover with no ceiling stays recordable |
last_claim_locator still moving | Apply still writes the legacy column on every burn[21]; it is display trivia now, not the guard |
The ledger that explains every unit
37 application rows exist for this member; the nine gp_video rows are the 9 on the counter. The most recent:
claim_locator claim_line_id type key amount applied_at
CLM-2026-002725 3098ce13-a064-47a8-95ef-a0cc721a0dd9 SESSION_LIMIT gp_video 1.0000 2026-07-30 14:24:49amount = 1.0000 is one session, not one pound - the unit is whatever the accumulator type meters[22]. That claim line is the worked example on the Claim page - the same row seen from the other side of the service boundary.
The money-metered case: PTY-2026-000075
| accumulator | limit | consumed | ledger behind it |
|---|---|---|---|
BENEFIT_LIMIT diagnostics | 250.00 | 240.00 | two applications of 120.0000 each (CLM-2026-002679, CLM-2026-002677) |
Same mechanics, pounds instead of sessions: this member is one blood test away from exhausting the diagnostics benefit, and the next eligibility check surfaces exactly that arithmetic to the caller.
The checkpoint
topic: enrollment-events partition: 0 offset: 1834 processed_at: 2026-08-17 08:33:31One partition, one row - the projection's heartbeat. Staleness of processed_at against topic activity is the lag signal.
7. Who reads the projection
Nothing joins these tables from outside - the fan-out is HTTP, which is the point of a read model:
| Caller | Route | What it reads |
|---|---|---|
| claims (adjudication) | PATCH /internal/members/{loc}/accumulators/{term}/apply[32] | burns allowance; also GET /internal/…/coverage for policy resolution |
| member app (BFF) | GET /check, GET /members/{loc}/coverage, /accumulators[33] | "am I covered", benefit balances; member locator forced from JWT |
| employer portal | GET /schemes/{loc}/utilisation (scheme-ownership gated)[34] | per-module used/limit summed across the roster[35] |
| triage health-chat agent | GET /internal/members/{loc}/accumulators (VPC-only)[36] | the accumulators tool behind "how many sessions do I have left" |
| web-admin | GET /coverage/list, /accumulators/list via APISIX | the ops read |
8. Design determinations
- CQRS read model, no source of truth, zero FKs - adjudication reads are single-table; integrity is events + idempotency, never constraints across services. (data architecture, narrative)
- Coverage starts at enrol - the projection consumes enrollment events; roster membership never gates it. D-07 · #1010.
- Accumulators are seeded from the catalogue - product is data; a new module needs no eligibility deploy to be metered. (§3, Product & catalogue)
- Idempotency by ledger, not by last-writer memory - the
(claim_locator, claim_line_id)UNIQUE replaced thelast_claim_locatorguard that double-counted on A, B, A. Migration 0009's comment is the decision record[3]. - Eligibility owns the metering semantics - callers send quantity and amount; the stored accumulator type decides which is consumed[22].
- Unmetered is metered at limit 0 - untracked and unmetered are not the same thing[18].
- Eventual consistency accepted; log-and-skip on poison events - a bad event is a logged gap to repair, never a stuck partition[29].
- Members can only check themselves - the verified JWT claim overrides the request. #1544.
- Metering archetypes modelled ahead of the products that use them - session, monetary and unmetered are all derived from catalogue data, and every non-session type already meters in money[22]. A deductible or an out-of-pocket max is therefore a product definition rather than an eligibility deploy, and because every row here is derived, the projection can be rebuilt from the source events at any time. §9 names each surface.
9. Caveats and extensibility
Group and individual share the projection unchanged. The only group-aware column is scheme_locator, added for the employer utilisation panel and documented as empty for direct-to-consumer policies in the model[10]. An individual policy projects rows with an empty scheme and every check, accumulator and ledger path behaves identically - the group vs individual fork was spent upstream on policy_structure, never here.
The join that keeps this cheap. Claims burns allowance without holding a foreign key into this schema: it names a coverage_term_key - a catalogue module key - and eligibility resolves the accumulator from its own stored row, letting the stored type win over any caller hint[23]. Any future consumer of allowance joins the same way, so a new metering surface needs no schema change on either side of the boundary.
Where to extend. Each row is a change the model already absorbs; the "where" is the surface someone touches when the need lands.
| When we need … | What to add | Where |
|---|---|---|
| A new benefit module metered | Nothing here: publish the module in the catalogue. parseAccumulatorSpecs walks whatever module and addon keys the snapshot carries and seeds one accumulator per key[17], and verdicts resolve a service type against the catalogue rather than a hardcoded map | policy_admin.product_versions.element_schema → coverage_terms |
| A session allowance ("5 GP video sessions a year") | unit: "sessions" + included_per_cycle on the module → SESSION_LIMIT with that ceiling | the module definition in the catalogue |
| A monetary annual cap ("£250 of diagnostics") | cycle_limit on the module → BENEFIT_LIMIT, the number landing in limit_amount | the module definition in the catalogue |
| Cover with no ceiling that still must be recordable | unmetered unit → USAGE at limit 0, so consumption stays countable[18] | the module definition in the catalogue |
| A deductible or out-of-pocket max | Seed rows with the new type; consumedUnits already meters every non-session type in money[22] and the stored row decides the type, so claims stays unchanged | seeding in services/eligibility/internal/projection/accumulators.go |
| To reverse consumption (a claim rejected after apply) | An un-apply endpoint that deletes the ledger row and decrements the accumulator in one transaction; ListApplications is already the per-claim basis for it[37]. Until it exists, consumption on a reversed claim stands | services/eligibility/internal/repository/gorm_accumulators.go + the /internal/…/accumulators route block[32] |
| To rebuild the projection from source | A replay command: reset the consumer-group offset, truncate coverage and accumulators, reconsume. Every row here is derived, so a rebuild loses nothing except the claim-side ledger, which is preserved | consumer group eligibility-projection[8] over topic enrollment-events |
| A new consumer of allowance (care, prevention, a wellness credit) | Call the same apply endpoint with a locator and a line id; the UNIQUE (claim_locator, claim_line_id) is the whole contract, and idempotency makes the call safe to repeat | PATCH /internal/members/{loc}/accumulators/{term}/apply |
| A scheme-level allowance (an employer pot, not a member one) | An accumulator scoped on scheme_locator beside the party-scoped one; the utilisation route already sums per-member entitlement across a roster[35] | a column beside coverage_accumulators; services/eligibility/internal/handler/utilisation.go |
| A non-annual cycle or carry-over between years | Nothing here: a new term from enrollment is already a new accumulator scope, so a different cycle length changes only the term window upstream | enrollment.policy_terms |
| Checkpoint-based recovery rather than Kafka offsets | Read projection_checkpoints back at consumer start; the row is already written per message | services/eligibility/internal/repository/gorm_checkpoint.go |
Known warts, stated:
element_typecarries two vocabularies - live values are lowercase catalogue types (employee×499,health×1) and uppercase legacy seeds (OUTPATIENT,INPATIENT,DENTAL, one row each), while the Go model's comment promises a third story ("employee | dependent")[10]. Nothing reads the column on the adjudication path, which is why the drift survives. The fix: normalise the fossil rows and settle the vocabulary on the model comment inpackages/go/domain/eligibility.go.product_codeis NOT NULL and empty - the enrollment element payload arrives without it, so live rows carry''(the example row above). The fix: carry the product code on the element event and set it in the upsert,services/eligibility/internal/projection/handlers.go.last_claim_locatoris superseded as a guard but still written on every apply - harmless, and one more thing to misread as authoritative. The fix, when a migration is cheap: drop the column and the write inservices/eligibility/internal/repository/gorm_accumulators.go.partitionvspartition_id- three services hand-rolled the same checkpoint table with drifting column names (§3). The fix: one shared migration snippet, renaming this copy'spartitionand unquoted-offsetcolumns to match billing and notifications.- Log-and-skip means silent under-projection is possible - a handler failure (for example the bootstrap HTTP fetch failing on
policy.issued) advances the offset anyway[30], and today the repair is re-emitting the event after reading the logs. The fix: a dead-letter table written from the error branch of the Run loop,services/eligibility/internal/projection/consumer.go.
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.
eligibility/migrations/0002_create_member_coverage.sql- member_coverage DDL, UNIQUE L190003_create_coverage_accumulators.sql- accumulators DDL, UNIQUE L13, legacylast_claim_locatorL110009_create_accumulator_applications.sql- the idempotency ledger; the A-B-A double-count story in the header comment L2-L130004_create_projection_checkpoints.sql- checkpoints DDL (partition, quoted"offset")projection/handlers.go#L15- the nine registered event handlersinternal/config/config.go- topic default, now the dual listenrollment-events,enrollment.eventsclaims/internal/client/eligibility.go#L129- the claims-side apply call (and the 404-for-months wrong-route history in its comment)cmd/server/main.go#L86- consumer groupeligibility-projectionprojection/consumer.go#L100- commit + checkpoint upsert per messagepackages/go/domain/eligibility.go#L9- MemberCoverage model ("never written by other services"); scheme comment L21; element_type comment L230008_member_coverage_scheme.sql- scheme_locator + why (employer utilisation)handler/internal.go#L191- line-id fallback to coverage_term_keyrepository/gorm_coverage.go#L22- Upsert converge-on-latest + the DoNothing regression storyhandler/verdicts.go#L44- EvaluateExclusions over the coverage_terms snapshotrepository/gorm_coverage.go#L69- GetActiveByPartyLocator window semanticsrepository/gorm_coverage.go#L84- Terminateprojection/accumulators.go#L56- unit / included_per_cycle / cycle_limit / unmetered → accumulator typeprojection/accumulators.go#L36- unlimited-still-metered rationaleprojection/accumulators.go#L76- legacy flat-key pathprojection/handlers.go#L421- handlePolicyRenewed: terminate + re-seed under new termrepository/gorm_accumulators.go#L39- Apply: INSERT-claims-the-slot transaction + sentinel rollbackhandler/internal.go#L104- consumedUnits: sessions vs poundshandler/internal.go#L120- resolveAccumulatorType: stored row winsbilling/migrations/0009_create_projection_checkpoints.sql#L5- billing names itpartition_idnotifications/migrations/0004_create_projection_checkpoints.sql#L5- notifications toorepository/gorm_checkpoint.go#L41- quoted"offset"upsertrepository/gorm_accumulators.go#L20- Upsert moves the ceiling, never consumptionhandler/check.go#L88- #1544 self-check overrideprojection/consumer.go#L69- Run loop: log-and-skip, always advanceprojection/handlers.go#L30- handlePolicyIssued bootstrap fetch (log-and-skip at L38-L42)claims/internal/service/claim.go#L922- applyAccumulators: safe to call on every path to APPROVEDhandler/handler.go#L68- the /internal route blockhandler/handler.go#L50- the JWT-guarded member routeshandler/handler.go#L63- scheme-ownership-gated utilisation routehandler/utilisation.go#L22- per-member entitlement summed to the scheme denominatorhandler/internal.go#L31- the triage agent's VPC-only accumulators readrepository/gorm_accumulators.go#L100- ListApplications, "the basis for reversing it"
Live-schema facts (constraint list, Debezium publication, type/element-type counts, the 9-of-5 / 26-of-5 / 240-of-250 rows, checkpoint row) come from PGPASSWORD=… psql -h 10.0.1.2 -U olly -d eligibility · \d eligibility.member_coverage, \d eligibility.coverage_accumulators, \d eligibility.accumulator_applications, \d eligibility.projection_checkpoints, 2026-08-18.