Data Model: Financial & Claims

The financial service manages foster care maintenance payments, adoption and guardianship assistance, Title IV-E eligibility tracking, payment adjustments, and federal fiscal claiming records for CFS-2 submission.

Rate tables define jurisdiction-specific daily rates by payment type and age range. Payments are generated from placements (per-diem origin) and — since #1068/ADR-053 — from active subsidy agreements (monthly subsidy origin) and approved through the same multi-step workflow. Adjustments modify payment amounts with supervisor approval. Claiming records aggregate issued payments for federal reimbursement.

The federal FFP figure payments.ffp_amount is a GENERATED ALWAYS column (#861): the DB derives it as CASE WHEN ive_eligible THEN round(net_amount * ffp_rate, 2) ELSE 0 on every write, so no handler computes or writes it and the IV-E claim can never drift from net_amount/ffp_rate/eligibility. DB-level backstops (#861) additionally reject malformed writes from any writer: CHECK (period_end >= period_start), CHECK (daily_rate >= 0), CHECK (ffp_rate >= 0 AND ffp_rate ⇐ 1), and CHECK (day_count >= 1) on payments; CHECK (age_min ⇐ age_max) and CHECK (effective_date ⇐ end_date) on rate_tables.

Since #1068 (ADR-053) payments is a NULLABLE-ORIGIN table: placement-origin rows carry the NOT-NULL case_id/placement_id/foster_home_id trio, subsidy-origin rows carry agreement_id + term_id (the term REVISION that priced the row — composite-FK-pinned to the same agreement) with a NULL placement pair (case_id optional — post-case children); payments_origin_exclusive admits exactly those two shapes. Subsidy rows are UNIT-MONTH rows: one exact calendar month (payments_subsidy_calendar_month) with day_count = 1 and daily_rate = gross_amount = the monthly award (payments_subsidy_unit_month) — the per-diem day×rate reading never applies — and are structurally never IV-E (payments_subsidy_never_ive; TANF-funded per 22.8). At most one LIVE subsidy row exists per agreement-month: the key is payments_one_subsidy_per_agreement_month_v2 on (agreement_id, payment_month) (the F9 month-identity GENERATED column) — a partial unique EXCLUDING voided rows, because the ADR-053 correction protocol voids a stale undisbursed month and regenerates it under the freed key. The legacy (agreement_id, period_start) twin that covered old replicas' ON CONFLICT targets through the rolling deploy was dropped by the #1106 contract step, which also raised schema_compat_floor to #1069 M1’s version — recording that a pre-M1 binary (whose conflict target no longer exists) is incompatible with the post-drop schema. No released binary is newly refused: pre-M1 binaries predate verify-only boot and were already VersionMissing-refused against any ahead database.

Placement-origin (per-diem) rows carry the OPPOSITE uniqueness semantics (#1179): payments_one_perdiem_per_placement_period on (placement_id, period_start) WHERE agreement_id IS NULL is a LIFETIME key with no status filter — a voided row still holds it. It is a replay guard, not a correction protocol: placement.activated is at-most-once per placement (no re-activation transition exists), so a key collision is always a replayed/duplicate execution — and because placement.ended voids the row while an ended placement can never re-emit events, a voided-exclusive key would let a stale replay mint a fresh payment nothing ever voids again. The placement.activated consumer inserts through ON CONFLICT … DO NOTHING against this key and stages its payment_created event iff the row was actually inserted. The companion payments_dedupe_repairs ledger (same migration) records any rows the one-time fail-closed dedupe removed.

The catalog below covers the domain tables. The fleet-standard platform tables also live in this schema and are documented centrally: event_outbox / event_inbox / event_inbox_collisions (ADR-022 and the ADR-062 §A attempt machine), request_claims (ADR-062 §B), and schema_compat_floor (ADR-063).

The five subsidy_agreement* tables form a close-only temporal ledger with a single-writer store (craig_financial::subsidy::store) — see ADR-052 for the window semantics, coverage predicate, and blocking invariants, and ADR-054 for the review chain
verified-office authority (proof-typed writers; nothing approval-shaped is caller-supplied). The store primitives (transition, term append, schedule / complete / reschedule-supersede) land with #1094; the mutation API arrives with U2 (#1095), creation with the eligibility flows (ERR landed with #1069 M2; the SG/NRSG two-step + witnessed activation with #1070/ADR-056; the #1071/ADR-057 closed-cohort conversion import’s substrate — proof-typed materialization over the staging ledger below — with MR-A, its staging/finalize surface with MR-B).

#1069 M2 (ADR-055) adds six nullable columns to subsidy_agreements — written only by native creation (originally the one-shot ERR create; since #1070 the two-step guardianship create shares placement_started_on, relationship_evidence_key, and the F8 pair); all six are NULL for imports and every pre-existing row. The clock snapshot placement_started_on
foster_home_approval_due (the first day of the qualifying placement and the policy-derived 120-day approval due date) is both-or-neither and forward-running for ERR — originally CHECK subsidy_agreements_approval_clock_valid, reworked by #1070 into the three-shape subsidy_agreements_program_snapshot_valid below. custody_evidence_key / relationship_evidence_key are UNVERIFIED evidence references — object-store keys recorded as supplied; existence is deliberately NOT verified (a recorded ADR-055 non-goal). The F8 idempotency pair create_request_id + create_request_hash (client-supplied request id
canonical-payload SHA-256 hex) is paired by CHECK (subsidy_agreements_create_request_pair) so a replay row can never exist half-keyed, and backed by the partial unique below — the endpoint checks BEFORE volatile dependencies, and the unique turns a two-writers race into a typed replay, never a double enrollment.

#1070 (ADR-056) adds five more nullable columns to subsidy_agreements — the native SG/NRSG guardianship flow is the only writer; all five are NULL for ERR, imports (#1071), and every pre-existing row. The CREATE step (the two-step PENDING mint) writes caregiver_assessment_evidence_key (UNVERIFIED evidence reference: the approved kinship assessment / foster-home evaluation) alongside the shared residence snapshot + F8 pair; the witnessed ACTIVATION writes the rest atomically with the pending → active interval: guardianship_transfer_on (the anchoring placement’s derived end date — the transfer proxy, ⁂ #1073; the first Active interval opens here), payments_begin_month (the F2 money boundary: max(month after transfer, month after TANF termination) — the generator pays nothing before it), court_order_evidence_key (UNVERIFIED reference: the permanent-guardianship court order), and tanf_terminated_on (operator ATTESTATION, unverified — CRAIG holds no TANF data). Three CHECKs shape the columns: the ERR-only approval-clock CHECK is DROPPED and replaced by the three-shape program snapshot subsidy_agreements_program_snapshot_valid — all-NULL (legacy/import) | the ERR clock pair (both set, due > start) | the guardianship residence shape (program IN ('sg','nrsg') ∧ placement_started_on set ∧ clock column NULL — SG has no 120-day clock); subsidy_agreements_guardianship_flow_shape couples the flow (a transfer snapshot exists ONLY on a flow-created family agreement with the full activation record — residence snapshot, F8 key, court evidence, money boundary, transfer > placement_started_on, and the F1 strict signed_at < transfer); and subsidy_agreements_payments_begin_month_valid pins the boundary to a month START strictly after a PRESENT transfer (TANF can only push it later, and the explicit presence arm avoids a silent > NULL pass). The F12 family-exclusivity partial unique (subsidy_agreements_one_open_guardianship_family, below) is the race backstop. Three REPORT-ONLY invariants watch the flow’s facts, keyed on the flow-written columns so legacy/seed rows never fire: sg_transfer_matches_activation (transfer = the earliest Active interval’s start, plus a flow-scoped completeness arm keyed on Active-interval EXISTENCE — a later termination cannot hide the gap — and a snapshot-without-any-Active bypass arm), sg_no_payment_before_boundary (no live payment with payment_month < payments_begin_month), and sg_residence_floor (GA devstack: placement_started_on + 6 months ≤ transfer; Postgres interval clamping is equivalent to the store’s chrono checked_add_months clamping).

#1071 (ADR-057) adds the conversion substrate (migration 20260725090000_subsidy_import_ledger.sql): the two staging tables above, plus ONE column on subsidy_agreements — payment_cutover_month, the machine money boundary written by the materializing identity insert (SHINES pays every month strictly before it, CRAIG from it onward; the generator’s BeforeCutover rule enforces CRAIG’s side on the scheduled, manual, AND reconcile paths). Two CHECKs shape it: subsidy_agreements_cutover_iff_imported (present exactly when source_system is set — provenance all-or-none extends to the money boundary) and subsidy_agreements_cutover_month_start. Family exclusivity widens to the full taxonomy (ADR-057 D8): the #1070 guardianship one-open index is RECREATED over ('sg','nrsg','ersg','enrsg') and a NEW relative-care one-open index covers ('rcs','ercs') — safe pre-1.0 because import is the only path that can mint enhanced/relative-care rows. idx_subsidy_agreements_import_batch (partial, WHERE import_batch_id IS NOT NULL) serves batch reconciliation. Four BLOCKING invariants guard the substrate: subsidy_active_missing_review_slot (every active/suspended head holds a live renewal slot, plus a paper slot for non-ERR programs when the head terms carry a paper anchor — scoped to imported rows and any row whose review chain already exists, so a native row whose chain never materialized is exempt), subsidy_terms_cover_active (every Active interval priced from its first day), subsidy_closed_cohort_qualifying_date (rcs/ercs first received before 2014-01-01, ersg/enrsg before 2014-08-01 — earliest Active start, signed_at fallback), and subsidy_cutover_iff_imported (the DDL shape plus the money consequence: no live payment before the cutover).

#1072 adds the UAS payment classification (migration 20260725160000_subsidy_uas_classification.sql): subsidy_agreements.uas_code TEXT NOT NULL — derived by the service at the only write boundaries (native create + conversion import) from the active state bundle’s effective-dated vocabulary as of the write’s business date, immutable thereafter (as-of snapshot: a vocabulary change never re-derives a row) — and payments.uas_code TEXT, denormalized from the AGREEMENT at the generator’s single insert site (deliberately outside the RowShape derivation compare, so vocabulary changes can never void/regenerate rows). The migration backfilled pre-#1072 rows with GA literals (every historical row is GA-shaped — subsidy writes have been jurisdiction-gated since the table’s birth), drift-pinned against the GA bundle by uas_backfill_matches_the_georgia_bundle. Shapes: subsidy_agreements_uas_code_nonblank and payments_uas_iff_subsidy_origin ((uas_code IS NULL) = (agreement_id IS NULL) — per-diem billing rows have no enrollment to classify). One new BLOCKING invariant: subsidy_payment_uas_consistent (a payment’s code always equals its agreement’s).

subsidy_perdiem_handoffs (#1069 M3/ADR-055) is the durable "per diem began" fact ledger: a mutable billing row is not the fact — the payment can be voided, repriced, or re-pointed after the sweep acted on it. Columns: agreement_id (FK subsidy_agreements ON DELETE CASCADE, like every agreement child table — production never deletes agreements; a dev/test delete takes the ledger with it), payment_id (FK payments), perdiem_started_on (the per diem’s period_start as detected — the F6 termination truth date, max’d with head start + 1 day at enforcement), detected_run_id and nullable enforced_run_id (both FK subsidy_sweep_runs), and the correction pair corrected_at
correction_note. Its writers are the M3 sweep engine and store: detection inserts once per (agreement, payment) on every scheduled sweep scan (subsidy_perdiem_handoffs_once UNIQUE); leg-3 enforcement stamps enforced_run_id (NULL while detected-only — knob off, or an operator acted manually); a state-office reinstatement out of a per_diem_begins termination stamps corrected_at + correction_note on every uncorrected fact on the agreement (the per diem itself must be voided/ended first). A note cannot exist unstamped (CHECK subsidy_perdiem_handoffs_note_shape). Rows are never deleted.

The subsidy-agreement ledger (ADR-052) is a separate cluster:

The financial service references entities from other services by UUID (no foreign keys across databases):

The four subsidy references are walked by cargo xtask reconcile (cross-service reconciliation). * payments.placement_id → craig_placement.placements.id * payments.foster_home_id → craig_placement.foster_homes.id

Rate table lookups use jurisdiction (from CRAIG_FINANCIAL__JURISDICTION env var) and payment_type to match the appropriate daily rate for a child’s age.