canopy-renewals Data Model

On this page

Tables

Table Purpose

snap_certifications

One row per SNAP certification period. Holds the certification_start_date / certification_end_date window, certification_type (caller-declared MT-87 category, persisted verbatim — standard / elderly_disabled / senior / abawd per PAMMS 3105 Chart 3105.1; #956 killed the period-length inference), reporting_model (simplified vs change_reporting per 7 CFR 273.12(a)), the optional interim_contact_due_date + interim_contact_completed_at pair (mid-certification contact, derived only for declared-standard certifications), and the renewal-cycle linkage (renewal_notice_sent_date, renewal_application_id, renewal_submitted_at, renewal_determination_id). The daily scheduler stamps renewal_notice_sent_date (renewal-due) and interim_contact_notice_sent_date (interim-due) in the same transaction as each event emit, so a due certification emits its event exactly once rather than once per daily tick across the whole lookahead window (#1198); interim_contact_completed_at records a worker completing the contact and is a distinct signal from the emission fence. Status moves through active → recertifying → terminated/expired; an early termination also stamps terminated_at (the instant the case left the caseload), which the caseload-depth trend (#702) reads to reconstruct historical depth losslessly; a #1108 periodic-report reopen is the sanctioned inverse (reinstate_certification, the ONLY path away from terminated) — it restores active and CLEARS terminated_at, so the reopened cert counts in future depth buckets and a later re-termination stamps fresh instead of resurrecting the first instant. Creating a certification supersedes the household’s prior active certification (→ expired) in the same transaction, so at most one certification is active per household at a time — enforced by the snap_certs_one_active_per_household partial unique index (a concurrent double-create loses the race and gets 409 Conflict) and by a non-empty-period snap_certs_period_order_chk CHECK (certification_end_date > certification_start_date, rejected 422 at the API) (#973). periodic_report_required (#1106, epic &72 P11) is the periodic-report cohort flag: set at creation (and backfilled by migration) for extended certifications assigned before the 2026-03-02 MT-87 phase-out (PAMMS 3730:21-23), false for anything at/after it, ≤6-month periods (3730:18), and declared-senior certifications (3730:64 waives Senior SNAP by name — keyed on the #956 persisted type at creation; the migration backfill retains its 36-month duration proxy because legacy rows carry no senior label) — it gates ALL periodic-report machinery, and creating a successor certification cancels the superseded cert’s open cycles in the same transaction. SNAP-only; per-program certification lifecycle for TANF/Medicaid/CAPS/WIC stays in each program’s own service.

snap_change_reports

Mid-certification change report ledger. One row per change reported by the household (or system-detected). Carries reported_at, the report_method (phone / mail / in_person / online / system), change_type, free-text description, the requires_redetermination flag, and the optional redetermination_application_id that links back to canopy-applications when the change kicks off a new intake. Per #448 (multi-program upstream gaps), the program column (DEFAULT 'snap') routes non-SNAP change reports from the worker portal’s /v1/renewals/{program}/…​ endpoints; certification_id is now nullable so non-SNAP programs (which own their own certification tables in their own services) can record changes via a direct household_id reference.

recert_nudges (T2-7 #680, ADR-027 §6)

One row per mid-certification reported fact change that the materiality subscriber re-scored. When a worker authors an income/asset/expense/member fact during an active SNAP certification, canopy-renewals runs a non-persisting materiality dry-run (canopy-eligibility) against the determination-of-record’s frozen policy + pinned corpus and records the verdict diff here: baseline_status / baseline_benefit_cents, dry_run_status / dry_run_benefit_cents, benefit_delta_cents (cents; NULL on a denied side), and is_material (Decision F — a verdict flip, or both-approved with a delta >= the [snap.materiality] threshold). Idempotent on UNIQUE (certification_id, source_event_id) (Decision G — the triggering persons fact-change event id), so at-least-once delivery yields exactly one nudge. A material row triggers a renewal.material_change event → ChangeInCircumstancesNotice. triggering_fact_kind ∈ income / asset / expense / member; action_taken (filed_recert / dismissed, worker-set in MR6) is NULL while pending; notice_id is reserved for a future async link-back from canopy-notices. FK certification_id → snap_certifications(id); household_id / baseline_determination_id / source_person_id are cross-service references (no DB-level FK, ADR-001).

snap_periodic_reports (#1106, epic &72 MR 5.1)

One row per periodic-report CYCLE on a certification (PAMMS 3730, eff. June 2026; 7 CFR 273.12(a)(5)(iii)). generation is the 1-based cycle number (UNIQUE (certification_id, generation); the base rule mints one midpoint cycle per 3730:19 — the 6th month of a 12-month cert, the 12th of a 24-month; since #1127 a QUALIFYING earned-income change (persons-fact income_type in [snap.periodic_reporting].frequency_shift_earned_income_types) on a cohort cert mints the 3730:25-26 shifted-cadence generations in the materiality subscriber’s inbox tx — every frequency_shift_interval_months (6) from the cert start, strictly inside the period, future-serviceable due months only (an elapsed 15th-notice window is skipped outright, never tombstoned), generation numbers continuing from MAX under the cert FOR UPDATE lock, idempotent on the per-due-month unique (the midpoint collision is the designed no-op)) and UNIQUE (certification_id, due_month) makes the daily materializer idempotent. The 3730 calendar is stored per cycle: initial_notice_due_date (15th of the prior month, 3730:83), combined_notice_due_date (5th of the due month, 3730:98 — that combined notice IS the adequate termination notice, 3730:100/:108), closure_due_date (end of the due month, 3730:76-79; last-prior-workday adjustment applied by the 5.2 closure executor). status walks scheduled → notice_sent → form_incomplete/form_complete → vcl_pending → verified → processed, with terminated (5.2 terminal consumer) and cancelled (cancel_reason = certification_superseded on recertification per 3730:21-23, or calendar_elapsed_at_cutover when the cohort scan first saw the cert after its due month began). Form receipt records form_kind (528, or 297/508 covering all sections per 3730:117) + form_received_date; an incomplete form is NOT filed (3730:35) and keeps the cycle combined-notice-eligible. The VCL trio (vcl_sent_date, vcl_due_date, vcl_reason ∈ reported_change/discrepancy, vcl_detail) is CHECK-constrained to the ≥10-calendar-day minimum (3730:218). Every state stamp is preserved — the row is its own audit trail. Since #1107 the row also records adverse_action_id + combined_notice_sent_date when the 5th-of-month trigger mints the termination action (the stamp removes the cycle from the combined due list; STATUS is untouched so a late form still files). Since #1128 a lapsed VCL gets the same treatment (Chart 3730.1 row 2): the drain_vcl_closures scheduler leg stamps vcl_termination_triggered_date + adverse_action_id when it schedules (or, on a provenance-triple replay against a still-live nonfiler action, ADOPTS) the exempt termination — reason failure_to_provide_verification under the periodic_report source, CB off via the P4 source override (3730:37) — and the stamp removes the cycle from the closure due set while STATUS stays vcl_pending, so a late verification still cures through verified → processed (whose completion tombstone cancels the in-flight action). Transition dates are server-stamped via the service’s single legal_today() funnel (never accepted from the wire); completion runs in ONE locked transaction (certification row FOR UPDATE in the same cert→cycle order as the supersede path) with its change-report rows and the renewal.snap_periodic_report_processed outbox event. Since #1108 (epic &72 MR 5.3) a TERMINATED cycle can REOPEN inside the Chart 3730.1 30-day window: it rejoins the state machine (terminated → verified on the verification-cure arm, row 4, or terminated → form_complete on the late-filing arm, row 5 — the arm derived from vcl_sent_date, never claimed) and stamps reopened_date (the legal receipt; also the enrollment-side proration anchor — terminated_date survives as history) + sop_due_date (receipt + 5 workdays on the cure arm, receipt + 30 days on the late-filing arm; the pair CHECK-constrained to travel together, deadline ≥ receipt).

pr_redeterminations (#1107, epic &72 MR 5.2)

The veto/cancel re-determination worker queue: one row per DEAD periodic-report adverse action (UNIQUE (adverse_action_id) — idempotent under at-least-once terminal-event delivery). trigger_kind ∈ action_vetoed (appeal veto) / action_cancelled (worker cancel); the paired cycle is stamped cancelled with a *_pending_redetermination reason in the same inbox tx. Worker decision = single conditional UPDATE (action_taken ∈ redetermined/dismissed, NULL-guarded — the first decision stands). Mirrors the recert-nudges worker-queue shape. NEVER an automatic re-trigger (plan MR 5.2).

event_outbox (ADR-018 + ADR-039)

Per-service transactional outbox (ADR-018), schema single-sourced in crates/canopy-mq/outbox-migrations/ and generated into this service (ADR-039). Columns, indexes, hold semantics, and the migration inventory are documented ONCE in the cross-cutting description — see the data-models index.

event_inbox (#433)

Per-service consumer inbox (#433 / ADR-018 amendment). Subscriber writes a row before invoking the handler; PK on event_id (the envelope’s UUID v7) makes RabbitMQ redelivery idempotent. Carries (event_id, routing_key, payload, enqueued_at, processed_at, attempts, last_error) plus the #1089 parked-state columns (parked_at, park_reason, park_min_schema, queue_name — see the event-delivery protocol). Janitor (canopy-mq::InboxDrainer) sweeps processed rows older than 7 days.

scheduler_runs (#1211)

Wall-clock window fence for the service’s daily scheduler tick(s) (canopy-renewals.scheduler, and since #1218 the canopy-renewals.caseload-rollup refresh job). Schema single-sourced in crates/canopy-db/scheduler-migrations/ and parity-gated by cargo xtask outbox-migrations; documented ONCE in the data-models index.

snap_caseload_daily (#1218)

The caseload-depth rollup — sole feed of GET /v1/renewals/caseload-trend (scale audit H11). One row per UTC evaluation day (rollup_date PK, household_count with a >= 0 CHECK, refreshed_at), recomputed in FULL over the serving window [anchor−735d, anchor+8d] (≈744 rows — the tiny serve table is the point) by the canopy-renewals.caseload-rollup window-fenced job (hourly probe, first tick at boot; manual twin POST /v1/renewals/caseload-rollup/refresh consumes the day’s window on success). The refresh is one atomic prune+upsert transaction using a sweep-line (gaps-and-islands per household, ±1 boundary events, running sum — O(certs·log certs), never O(days×certs)); full-window recomputation is what makes retroactive mutations (reopen clearing terminated_at; backdated inserts, which are UNBOUNDED) self-heal. Deliberately NO backfill and no retention beyond the window: absent/stale (>48 whole hours) coverage makes the endpoint 503 honestly rather than serve fabricated zeros; rows outside coverage are pruned each refresh. Equivalence to the legacy per-bucket predicate is oracle-pinned in store.rs tests.

Relationships

Diagram

Cross-service FKs (ADR-001 boundary)

Per ADR-001, canopy-renewals holds no Postgres-level foreign keys to other services. The columns marked FK → canopy-persons / FK → canopy-applications / FK → canopy-snap above are application-level foreign keys: canopy-renewals trusts the upstream services to supply real IDs but does not enforce existence in canopy_renewals. Cross-service IDs the service holds are household_id, application_id, determination_id, renewal_application_id, renewal_determination_id, redetermination_application_id, and processed_by.

Retention

SNAP renewal certification records are governed by 7 CFR 273.14 (recertification) and 7 CFR 274.6 (case records — 3 years from last activity). snap_certifications rows are retained for the full 3-year window post-cert-end-date; snap_change_reports rows inherit the same retention from their parent certification. Rows are not deleted in production.

Indexes

  • idx_snap_certs_household — household-scoped cert lookup

  • idx_snap_certs_status — list-by-status (recertification queue, expired sweep)

  • snap_certs_one_active_per_household (partial UNIQUE, WHERE status = 'active' AND active = true) — at most one active certification per household (#973)

  • idx_snap_certs_end_date — renewal-notice scheduler scan

  • idx_snap_certs_end_date_id (compound, (certification_end_date ASC, id ASC)) — the GET /v1/renewals/snap/due keyset order + cursor (#1204); supplies the (end_date, id) total order the single-column idx_snap_certs_end_date could not, removing the top-N Sort

  • idx_snap_certs_interim_due (partial, WHERE interim_contact_due_date IS NOT NULL) — interim-contact queue

  • idx_snap_certs_renewal_due_unnotified (partial, (certification_end_date ASC, id ASC) WHERE renewal_notice_sent_date IS NULL, #1198) — the scheduler’s fenced renewal-due drain; excludes fenced rows so each daily range-scan reads only the still-unfenced (newly-due) certs in the window (bounds the per-tick scan, not the index size — the index still covers every not-yet-fenced active cert)

  • idx_snap_certs_interim_due_unnotified (partial, (interim_contact_due_date ASC, id ASC) WHERE interim_contact_due_date IS NOT NULL AND interim_contact_completed_at IS NULL AND interim_contact_notice_sent_date IS NULL, #1198) — the scheduler’s fenced interim-contact drain

  • idx_snap_pr_household — household-scoped cycle list (#1106)

  • idx_snap_pr_due_initial (partial, WHERE status = 'scheduled') — the 15th-of-prior-month notice predicate (#1106)

  • idx_snap_pr_due_combined (partial, WHERE status IN ('scheduled','notice_sent','form_incomplete')) — the 5th-of-due-month combined-notice predicate (#1106)

  • idx_snap_pr_open (partial, WHERE status NOT IN ('processed','terminated','cancelled')) — the month-end closure / lapsed-VCL predicate (#1106)

  • idx_snap_change_reports_cert — certification-scoped change list

  • idx_snap_change_reports_household — household-scoped change list

  • idx_snap_change_reports_type — change-type analytics / reporting

  • idx_snap_change_reports_program — #448 program-scoped routing

  • idx_snap_change_reports_household_program — #448 composite for (household_id, program) worker-portal queries

  • event_outbox_* — the three generated partial outbox indexes (drainer hot path, lease-aware claim, ADR-039 held-skip); documented once in the data-models index

  • event_inbox_unprocessed_idx (partial, WHERE processed_at IS NULL) — replay / janitor hot path

Migration files

  • 20260401000000_create_renewals_tables.sql — original schema (snap_certifications, snap_change_reports; type / status / reporting / method CHECK constraints; cert + change indexes)

  • 20260508000000_create_event_outbox.sql + 20260518004851_event_outbox_lease_columns.sql + 20260713000000_event_outbox_hold.sql — the generated ADR-039 single-sourced outbox migrations (cargo xtask outbox-migrations --write); documented once in the data-models index

  • 20260512000000_add_program_to_renewals.sql — #448 multi-program support; adds program column with DEFAULT 'snap', relaxes certification_id to nullable, adds program-scoped indexes

  • 20260516000000_create_event_inbox.sql — #433 consumer-side inbox (ADR-018 amendment)

  • 20260720000000_event_inbox_parking.sql — #1089 parked-state columns + idx_event_inbox_parked (generated, single-sourced with the outbox; see the event-delivery protocol)

  • 20260608000000_add_certification_terminated_at.sql — adds terminated_at TIMESTAMPTZ NULL to snap_certifications so the caseload-depth trend (#702) reconstructs "active as of past W" losslessly across early terminations (safe additive, no backfill)

  • 20260626000000_create_recert_nudges.sql — T2-7 #680 recertification-nudge ledger: recert_nudges table, UNIQUE (certification_id, source_event_id) idempotency key, triggering_fact_kind / action_taken CHECK constraints, household + pending-action partial indexes

  • 20260731000000_pr_reopen.sql — #1108 (epic &72 MR 5.3): snap_periodic_reports.reopened_date + sop_due_date with the paired-travel CHECK (snap_pr_reopen_pair_check)

  • 20260722000000_pr_action_pipeline.sql — #1107 (epic &72 MR 5.2): snap_periodic_reports.adverse_action_id (the 5th-of-month trigger’s minted action, cross-service id) + the pr_redeterminations worker queue (trigger/action CHECKs, per-dead-action unique, household + pending partial indexes)

  • 20260721000000_periodic_report_cohort_and_state.sql — #1106 (epic &72 MR 5.1): adds snap_certifications.periodic_report_required with the marker-delimited P11 backfill (pre-2026-03-02 assignment, > 6-month and < 36-month duration, live rows only — the backfill-parity test re-executes the marked statement against the Rust predicate) and creates snap_periodic_reports with the status/form/VCL-window/calendar-order CHECKs, the per-due-month + per-generation unique keys, and the three predicate partial indexes

  • 20260706000000_snap_cert_active_uniqueness.sql — #973 worker cert-create guards: expires pre-existing duplicate active certs (fix-forward), adds the snap_certs_period_order_chk CHECK (end > start) and the snap_certs_one_active_per_household partial unique index

  • 20260818000000_snap_certs_due_keyset_idx.sql — #1204 (scale audit C2): adds the compound idx_snap_certs_end_date_id (certification_end_date ASC, id ASC) backing the keyset-paginated GET /v1/renewals/snap/due; transactional (NOT CONCURRENTLY — sqlx-migrator advisory-lock deadlock, #1196)

  • 20260819000000_snap_certs_notice_fences.sql — #1198 (scale audit H1): adds the interim_contact_notice_sent_date emission-fence column plus the two partial drain indexes (idx_snap_certs_renewal_due_unnotified, idx_snap_certs_interim_due_unnotified) so the daily scheduler emits each renewal-due / interim-contact-due event once, not once per tick; transactional (NOT CONCURRENTLY, same #1196 rationale)

  • 20260905000000_create_scheduler_runs.sql — the generated #1211 window-fence table (single-sourced in crates/canopy-db/scheduler-migrations/); documented once in the data-models index

  • 20261103000000_snap_caseload_daily.sql — #1218 (scale audit H11): the caseload-depth rollup table (table only — deliberately NO backfill; the endpoint 503s honestly until the boot-time probe materializes)

All migrations are forward-only per ADR-016.

snap_universe_snapshots / snap_universe_snapshot_rows (#1470)

Immutable federal-universe snapshot generations (ADR-002 Amendment 1 D5 — the #1213 bulk-cohort source). A generation header (id UUIDv7, active_on, row_count, created_at) plus frozen member rows keyed (snapshot_id, seq) — seq is dense 1..row_count minted at freeze time, so the PK IS the keyset. Rows carry certification_period_id, household_id, application_id, and determination_id (the establishing determination — the supersession baseline). Frozen in ONE transaction with the count, so paging to exhaustion returns exactly row_count regardless of live-table churn. ON DELETE CASCADE is deliberate (ephemeral working sets, not provenance): the daily fenced reaper (canopy-renewals.universe-snapshot-reaper) deletes generations older than CANOPY_RENEWALS__UNIVERSE_SNAPSHOT_RETENTION_DAYS (default 30).

Edit this page · default