canopy-enrollment Data Model

snap_enrollments

One row per SNAP household enrollment created off an approved determination. Carries the certification_start_date / certification_end_date window, the monthly_issuance_amount (#993 — the household’s NET monthly benefit, issued directly each month with first-month proration; renamed from max_monthly_allotment, whose name claimed a gross cap the service never enforced), the optional ebt_account_id returned by the EBT adapter, the expedited flag (drives 7-day issuance under 7 CFR 273.2(i)), the initial_issuance_due_date (lifecycle SLA) and matching initial_issuance_date on success. Status moves through pending_issuance → active → suspended/terminated/expired; suspended_reason / terminated_reason / terminated_date are populated on the exit transitions. Per #407 / PAMMS 2415, mid-month closure semantics are surfaced via partial_retention (BOOLEAN) and retained_through (DATE) so downstream overpayment math can distinguish retained vs recoverable months. head_of_household_person_id (#1096, nullable) is the legal-recipient snapshot source for adverse-action scheduling — populated at creation since #1096, backfilled for older rows by the MR 1.3 one-shot, and REQUIRED by the schedule API (422 while NULL). lifecycle_revision (#1095) is the shared lifecycle fence: every lifecycle transition (termination via the #1102 guarded enact primitive — sweep, on-demand trigger, or appeal-resolution consumer) bumps it, and the post-EBT issuance mark is preconditioned on the value read BEFORE the EBT call — a termination landing mid-issuance kills the late mark instead of resurrecting the closed case. One live enrollment per household is structural (#1130): the snap_enrollments_one_live_per_household partial unique (WHERE active AND status IN pending_issuance/active/suspended) is the fence every code path answers to — a duplicate INSERT (the determination subscriber’s approving re-determination, PARKED — the #1089 disposition, first wired ParkEvent caller — until #1133 designs adjustment semantics; the create API, 409) and the #1108 reopen’s un-terminate racing a re-application (typed raced refusal) all converge on it; its migration deterministically resolved pre-fence duplicates (keep the earliest-created live row, deactivate + revision-bump the rest so the issuance fence kills their in-flight marks — the replay test re-executes the marked SQL and drives the fence).

snap_benefit_issuances

Per-month issuance ledger. One row per (enrollment, benefit_month) — enforced by idx_snap_issuances_enrollment_month UNIQUE. Captures the allotment_amount, the proration triplet (prorated, proration_days_remaining, proration_days_total) used for the first cert month under 7 CFR 273.10(a), the EBT-adapter ebt_transaction_id + issued_at, the issuance_status (pending → issued → failed/reversed) and issuance_error on failure, the first-of-month benefit_month CHECK (#1095; NOT VALID for pre-existing devstack seeds — a benefit month is a MONTH), the expiry_date (9-month inactivity gate per 7 CFR 274.2(h)(2)) and the expungement triple (expungement_notice_sent_at, expunged_at, expunged_amount).

enrollment_pending_terminations (#1095)

THE adverse action (epic &72 plan §Design spine): its id IS the adverse_action_id carried through notice → appeal → stay/veto → enactment → assessment → claim. Carries the policy snapshot (policy_version, required_advance_days, cb_available, cb_rule, and reason_display_text — #1120: the household-facing reason line, resolved from the vocabulary’s display_text at schedule time; the letter renders the worker narrative when present, else this snapshot, so a vocabulary edit never rewrites an already-noticed action’s letter — P4: immune to later policy edits), provenance + generation idempotency (UNIQUE(created_source, source_reference, source_generation) — a re-fired source upserts, a new generation is a new action), the PAMMS 3705 advance-notice exemption (authority + actor pair-required by CHECK), a typed recipient snapshot, enact_not_before (derived from the latest dispatched notice version; re-read under the enrollment lock at Phase-3 enactment), and status ∈ scheduled/enacted/cancelled/vetoed (stays are NOT a status — they live per-appeal in the links table). The #1220 sweep-lease pair (sweep_claimed_at, sweep_claimed_by) lets enact-sweep passes claim due actions in bounded FOR UPDATE SKIP LOCKED batches so replicas share a month-end spike — a work-sharing hint only, never the enactment authority (the guarded gate re-checks status under the row locks); idx_pending_terminations_due_scheduled serves the claim predicate. Global lock order: snap_enrollments row FIRST, then action rows ORDER BY id.

adverse_action_notices (#1095)

Versioned notice records — the action’s CURRENT legal dates are the latest version with dispatched_date set (all legal fields are jurisdiction-timezone DATEs); a repair is a SUCCESSOR version with new dates, never an UPDATE; stamping dispatch supersedes every earlier version. UNIQUE(adverse_action_id, version).

termination_appeal_links (#1095)

One row per (action, appeal) with MONOTONIC transitions enforced by the store’s guarded upsert: stayed → released, stayed → vetoed, released → vetoed (a veto is durable and order-independent — it moots the action even post-release); vetoed is terminal. restayed (#1263) is the final-appeal stay era (Chart B3): only the explicit restay command enters it (released|stayed → restayed) and only the appeals-sequenced sync release, a veto, or cancel_action’s links-moot (the action is dead either way) ends it — the `appeal.decision_recorded convergence replay is fenced out, so a relay-lagged decision event can never release a judicial continuation granted after the decision. An action proceeds only at ZERO active stays; both stay eras count.

action_signals (#1095)

Append-only receipt ledger for the action (a DB trigger rejects UPDATE/DELETE) — what arrived, when, from where; consumers derive state from links/status, signals are the audit receipts.

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.

periodic_report_completions (#1107, epic &72 MR 5.2)

Per-(certification_id, generation) completion tombstones for the periodic-report action pipeline — the projection that makes the 3730 trigger/completion race converge from both sides: the schedule API refuses a periodic_report-sourced action whose tombstone exists (completion-before-trigger), and the consumer that records a tombstone cancels a still-scheduled matching action in the same inbox transaction (trigger-before-completion). Both paths serialize on a per-(certification, generation) advisory lock. Written by the renewal.snap_periodic_report_processed consumer; keys are application-level cross-service references (canopy-renewals owns the certification/cycle rows, ADR-001).

enrollment_reopens (#1108, epic &72 MR 5.3)

Append-only reopen receipts for the NARROW Chart 3730.1 periodic-report reopen: one row per reopened ENACTED periodic_report-sourced action (UNIQUE(adverse_action_id) — the idempotency key; a replayed reopen returns the stored row). received_date is the PRORATION ANCHOR the issuance path reads (the receipt month’s allotment prorates from it — reopen_proration_anchor matches the anchor’s month to the requested benefit month); actor audits who commanded it. The accompanying enrollment mutation (status restored, terminated_*/retention cleared, lifecycle_revision bumped) happens in the same transaction under the global lock order + the periodic-report advisory lock; the action row itself stays enacted (history — this receipt + the reopened signal ARE the reopen record). Missed months between termination and receipt are NOT restored (#1113).

scheduler_runs (#1211)

Wall-clock window fence for the service’s daily scheduler tick(s) (canopy-enrollment.scheduler, the expungement 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.