canopy-appeals Data Model

appeal_requests

Fair-hearing request rows under 7 CFR 273.15. One row per hearing request. Carries the requestor (requestor_person_id), the CHECK-constrained program (typed Program vocabulary since #1098), the linked determination_id (required) plus optional application_id / notice_id / adverse_action_id (the epic-&72 spine id binding the appeal to an enrollment adverse action; NULL = narrative grievance), the request itself (request_date — server-stamped at filing, request_method), the hearing-scheduling triplet (hearing_scheduled_date, hearing_officer_id, decision_due_date — 60-day SOP per 7 CFR 273.15(c)(1), extendable via appeal_postponements; #1099 recomputed open rows off the wrong 90), the decision (decision_date, typed decision ∈ {upheld_agency, reversed_household, dismissed, legacy_unmapped} with the raw pre-#1099 value preserved in decision_legacy, decision_basis, decision_signed_date / decision_received_date — Chart B1’s case-action anchor — and decision_actor), the 7 CFR 273.15(j) dismissal fields (dismissal_basis ∈ {abandoned, federal_mass_change, untimely_request}, dismissal_good_cause), the P12 withdrawal lifecycle block (withdrawal_method ∈ {written, oral}, withdrawal_stage ∈ {pre_submission, post_submission}, withdrawal_requested_date, withdrawal_confirmation_due, withdrawal_confirmation_sent_date, withdrawal_reinstate_by, withdrawal_finalized_date, withdrawal_actor; status gains withdrawal_pending, and ONLY finalization releases the enrollment stay), the P2 cessation record (cb_cessation_reason ∈ {initial_decision, certification_end, federal_policy_change, mass_change, eligibility_change, withdrawal_finalized, final_decision} — withdrawal_finalized added by #1105 for the P12 liable disposition, final_decision by #1132 for the judicial outcome, cb_cessation_date — the ACTUAL stop date Phase-4 assessment windows end at), and the #1132 final-appeal block (decision_federal_policy_issue — the typed Chart B3 row-3 fact recorded at decision time, final_appeal_filed_date / final_appeal_actor / final_appeal_continuation — a timely filing with continuation supersedes the cessation back to NULL and re-grants the stay, final_appeal_decision ∈ {affirmed, reversed} with final_appeal_decision_received_date / final_appeal_decision_actor). Continued benefits are the #1098 Chart B2 election block: cb_election state machine (granted_timely / granted_reinstate / granted_legacy — pre-#1098 grant, no action binding or stay receipt — / pending_stay / pending_good_cause / waived / not_electable / not_applicable), plain continued_benefits_eligible (written by the election engine — replaced the pre-#1098 GENERATED column, whose request_date < adverse_action_effective_date rule could never fire for adequate notices), cb_waived (Form 118), cb_good_cause, cb_repayment_disclosed, cb_election_deadline, cb_reinstate_by (5 working days, adequate path), the persisted stay receipt (cb_stay_link_status, cb_stay_receipt_at), the continued_benefits_granted / continued_benefits_start_date / continued_benefits_end_date triple, and the post-decision overpayment hook (overpayment_amount, overpayment_claim_id).

appeal_postponements

P13 (#1099): household-requested postponements of the 60-day decision SOP. One append-only row per postponement (days > 0, reason, requested_by, created_at); each also advances the parent appeal’s decision_due_date.

appeal_timeline_events

Per-appeal event log. One row per state transition or operator note tied to an appeal — request received, hearing scheduled, hearing held, decision rendered, etc. Carries event_type, event_date, recorded_by (worker), and free-text notes. Append-only.

cb_assessments (#1104, epic &72 MR 4.1)

The continued-benefits overpayment assessment as a recorded entity — replaces the overpayment_amount-as-sentinel model (a Decimal stamped iff > 0, which silently erased zero-dollar assessments; assessed_cents >= 0 and zero persists as a completed row with no claim projected). Window per P2: window_start (CB start) to the RECORDED cessation_date + typed cessation_reason — never a formula. liable_person_id is the head-of-household snapshot (the adverse action’s recipient), immutable once written. The retention snapshot pins the enrollment state the assessment excluded retained issuances at: enrollment_id, lifecycle_revision, partial_retention, retained_through. status ∈ {computed, applied, void} — #1105: computed → applied flips when the program service’s *.overpayment_claimed acknowledgment lands (zero-dollar applies immediately: no claim to confirm); assessments still computed past the grace window are swept by the acknowledgment scanner (partial index idx_cb_assessments_unacked) and their claim event re-emitted. Since #1224 (ADR-001 Amendment 1 §B6-iii) both the acknowledgment and dead-action sweeps are PROGRESSING-CURSOR revisits: each orders by / advances its own mutable cursor (claim_reemitted_at / action_verified_at, NULLS FIRST so new rows jump the queue; partial indexes idx_cb_assessments_reemit / idx_cb_assessments_action_verify) — every active assessment is revisited within the configurable CANOPY_APPEALS__ASSESSMENT_REVISIT_SLA_SECS (default 1 day), where the pre-#1224 frozen ORDER BY created_at LIMIT 200 re-checked the same oldest rows forever. Rows past the SLA feed the canopy_appeals_assessments_past_revisit_sla alarm gauge. allocation_version bumps on P6 supersession (the household advisory lock is what serializes allocation rounds); voids carry voided_at + void_reason ∈ {veto, action_canceled, reallocation}. Partial UNIQUE (appeal_id) WHERE status <> 'void' — one active assessment per appeal; supersession = void + successor. Cross-service ids (adverse_action_id, enrollment_id) are application-level only (ADR-001).

cb_assessment_lines

Per-issuance itemization under an assessment. window lines carry the P6 allocation (billed unless PAMMS-2415 retained or zero); post_cessation lines itemize issuances after the recorded cessation per P7 (itemized — recorded for Chart B1 claim-ability, never silently billed); already_billed marks window issuances another assessment holds; released frees a billed line when its assessment is voided (flipped in the void tx). Partial UNIQUE (issuance_id) WHERE disposition = 'billed' is the mechanical P6 backstop: an issuance is billable once across ALL assessments, DB-enforced. UNIQUE (assessment_id, issuance_id); carries benefit_month, actual issued_at (the P7 classifier), amount_cents.

assessment_work

The #1105 worker’s queue (live since MR 4.2), on the #1091 notice_work_items pattern: enqueued in the decision tx (decision_cessation / dismissed) or the fenced withdrawal-finalization tx (withdrawal_finalized — a liable disposition per P1/P12, which also records the P2 cessation at the next issuance cycle), by the backfill sweep (backfill), or by a P6 displacement (reallocation — resets ONLY terminal items). Claimed FOR UPDATE SKIP LOCKED with a 10-minute stale-lease reclaim (claimed_at/claimed_by/heartbeat_at); attempts increments AT claim time so crash loops still converge on the 8-attempt budget; external HTTP outside any tx; the apply commits in one short tx under the per-household allocation lock. due_at carries the exponential backoff (60s·2^attempts, 6h cap) and the future-cessation / pending-EBT deferrals; terminal status ∈ {pending, done, failed} (failed is the operator surface — the backfill sweep never re-enqueues it) with a pruning index. UNIQUE (appeal_id) — a redelivered trigger can never mint a second assessment run; assessment_id links the result.

ipv_cases

Intentional Program Violation case file under 7 CFR 273.16. One row per IPV referral. Carries the subject (person_id, household_id), the program, the allegation (allegation_type ∈ {fraud, misrepresentation, concealment, trafficking}, allegation_description, evidence_summary), the referral (referred_by, referred_at), the calculated overissuance_amount, and the ADH lifecycle (status ∈ {referred, adh_scheduled, adh_notice_sent, adh_completed, waiver_accepted, court_referred, disqualified, cleared, withdrawn}; adh_scheduled_date, adh_notice_sent_at, adh_decision, adh_decision_at). On a sustained IPV, disqualification_start_date / disqualification_end_date plus disqualification_offense_number (1st / 2nd / 3rd offense, drives the escalating penalty under 7 CFR 273.16(b)) and the historical prior_ipv_count are set.

ipv_timeline_events

Per-IPV-case event log. One row per state transition or operator note tied to an IPV case. Carries event_type, structured event_data (JSONB), occurred_at, and recorded_by. Append-only.

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-appeals.scheduler, canopy-appeals.reconciliation). Schema single-sourced in crates/canopy-db/scheduler-migrations/ and parity-gated by cargo xtask outbox-migrations; documented ONCE in the data-models index.