canopy-snap Data Model

On this page

Cross-link: canopy-snap API Reference · Source: migrations/

Tables

Table Purpose

snap_applications

SNAP application intake records. One row per application received from canopy-eligibility. Carries the orchestrator’s application_id, household_id, the full application_context JSONB (income, assets, deductions, expenses snapshot), and a coarse lifecycle status (received default).

snap_determinations

Signed SNAP determinations. Carries the lifecycle status, benefit_amount + benefit_unit, certification window (effective_date / expiration_date / renewal_date; expiration_date is the inclusive last covered day per 7 CFR 273.10 — #1474 aligned the writer with every in-force reader’s end >= today; renewal_date is the bare anniversary the renewal process starts on), basis narrative, denial_reason_codes text array on denial, program_service_version for audit replay, the determined_at timestamp, the ADR-002 detached-JWS signature over the canonical signing payload, the snapshot_hash (hex SHA-256 of the ADR-028 input snapshot, bound into the signature; NOT NULL since #911 — the pre-snapshot legacy rows were deleted with the ADR-028 §58 backstop), and previous_determination_id (ADR-028 §57 — the determination this one supersedes; a signed envelope field, also a self-FK to snap_determinations(id); NULL for a first/legacy determination). Supersession is derived (who supersedes a determination is resolved in reverse via a one-to-one partial-unique index; prior rows are never updated — the determination stays append-only-by-convention + signature-tamper-evident). FK application_id to snap_applications(id).

determination_snapshots (ADR-028)

Immutable determination input snapshots (T1-10). One row per determination (PK = FK determination_id → snap_determinations(id)): the typed DeterminationSnapshot as a canonical snapshot JSONB blob (proven facts with provenance + fact identity, the exact evaluated input, resolved policy params, ruleset corpus-hash, and the snap-local IEVS reconstruction), plus denormalized corpus_hash + as_of columns and the signing_kid (ADR-028 §53 key retention). Append-only — a statement-level trigger blocks UPDATE/DELETE/TRUNCATE unless canopy.snapshot_maintenance is set. Re-verification deserialises the blob to the typed struct and re-hashes via RFC 8785 JCS (serde_json_canonicalizer since #1281; never over raw JSONB), comparing to snap_determinations.snapshot_hash. T2-2 (#679): once it carries the self-explaining derivation_graph (every derived fact’s value + its input edges + the versioned rule/fn that produced it — SNAP captures the eligibility-ruleset firings, the self-employment-deduction edge, and the inferred utility tier as a provisional node, #669) the blob is schema_version: 3 (ADR-028 Amendment 2); without it the snapshot stays byte-identical to its pre-T2-2 encoding. Since T2-6 (#687, ADR-036) the snapshot’s PII-bearing value leaves (money amounts, program_input, derived-graph node values) are AEAD-SealedValue envelopes hashed over ciphertext; schema_version is now uniformly 4. T2-7 (#680, ADR-027 §6): the plaintext policy_params now freezes the complete verdict-affecting policy bundle — the 15 eligibility thresholds (kept flat + identically named, so the derivation-graph Param resolution that addresses context.thresholds.<k> as the unwrapped <k> is unchanged) plus the 5 pay-period factors and the 2 self-employment-deduction settings — so the non-persisting materiality dry-run replays the exact policy the verdict scored against (certification/renewal months are excluded: they set dates, not the verdict). Pre-T2-7 snapshots carry only the 15 thresholds; a dry-run treats such an incomplete bundle as NoBaselineSnapshot (manual review), never a wrong verdict. D4 (#880, ADR-028 Amendment 5) then made the policy_params_version stamp required — schema_version: 5. Since #1467 (ADR-028 Amendment 6) snap emits schema_version: 6: the blob additionally carries the all-or-none params_provenance projection — params_digest (content hash of the selected effective-dated federal triple + budgeting factors + jurisdiction.toml raw bytes) + the set’s intrinsic effective_period [start, next-Oct-1) — so the snapshot names WHICH money-table bytes scored the verdict (the corpus hash cannot: the rules loader skips parameter JSONs), and as_of is the caller’s evaluation date (legal-today fallback), no longer a clock read at assembly. The accepted window is [5, 6]; v5 rows (pre-#1467, and every other program) remain valid with the projection absent.

redaction_keys (T2-6 #687, ADR-036)

Per-value DEK store for crypto-shred redaction. One row per per-determination DEK: dek_id (PK), wrapped_dek BYTEA (the DEK wrapped under the service KEK = CANOPY_ENCRYPTION_KEY, AAD-bound, zero-sentinel after shred), kek_version, subject_kind / subject_id (e.g. determination_snapshot / the determination id), created_at, shredded_at (NULL = live; non-NULL = redacted). Append + one-way-tombstone only — a trigger rejects DELETE/TRUNCATE/un-tombstone/identity-mutation.

ievs_verification_data (ADR-004)

Legacy IEVS verification outcomes — (person_id, data_source, match_result JSONB, verified_at). Pre-dates the ievs_match_results + ievs_discrepancies tables; retained for backfill compatibility. SNAP-only per 7 USC §2025(e).

snap_program_participations

Categorical-eligibility participation records per PAMMS 3030 / 7 CFR 273.2(j)(2)+(j)(3). One row per (person, program, effective_date). program is the qualifying program (SSI, TANF cash, GA). verification_status tracks self_attested vs verified. Drives both standard CE and BBCE (Georgia state option). Soft-delete via active; unique (person_id, program) WHERE active = true.

snap_student_status

Student-exclusion screening per 7 CFR 273.5. enrollment_half_time_plus flips the gate; institution_name + enrollment_verified carry the supporting document; exception_type + exception_verified record any 273.5(b) exception (work hours, work-study, dependent-care, etc.). Soft-delete via active.

abawd_tracking

ABAWD time-limit tracking per 7 USC §2015(o) / 7 CFR 273.24. One row per ABAWD; window_start_date / window_end_date bound the 36-month rolling window; months_used counts countable months toward the 3-month limit; current_status is the live state; exemption_type / exemption_expires and waiver_area_code carry the exemption / waiver basis; discretionary_exemption_id ties to a granted discretionary exemption. Soft-delete via active; unique (person_id) WHERE active = true.

abawd_monthly_activity

Monthly ABAWD work-activity reports — one row per (person, benefit_month). Hours by category (work, job search, training, community service, self-employment); snap_received flags whether benefits were drawn that month; reported_by distinguishes self_attestation from verified sources. Unique (person_id, benefit_month).

abawd_discretionary_exemptions

Annual discretionary-exemption pool per fiscal year (7 CFR 273.24(g)). quota_allocated is the state’s annual allocation from FNS; quota_used is the running count.

abawd_discretionary_exemption_grants

Per-person discretionary-exemption grants. FK to abawd_discretionary_exemptions(id). granted_by (worker UUID) + reason carry the audit trail. Soft-delete via active.

abawd_waiver_areas

FNS-approved geographic waiver areas (7 CFR 273.24(f)). area_code + area_name identify the area; waiver_start_date / waiver_end_date bound it; fns_waiver_approval_number ties to the FNS letter. Soft-delete via active.

abawd_time_clock (PAMMS 3355)

36-month ABAWD time clock as a single row per (person, period_start). month_statuses is a JSONB map of {"YYYY-MM": "<status_code>"} with the 15 PAMMS 3355 status codes (C/E/D/A/G/H/M/N/O/P/R/S/W/X/T documented inline in the migration); countable_months is the rolled-up countable total. Unique (person_id, period_start).

snap_disqualification_screenings

Special-situations disqualification screenings — drug felony (7 CFR 273.11(m)), fleeing felon (273.11(n)), probation/parole violator (273.11(n)), striker (273.11(e)). Discriminator: screening_type. Self-attestation captured via self_attested + self_attested_date; conviction_date and pre_strike_income carry the relevant supporting facts; screening_result lifecycle starts at pending. Soft-delete via active.

ievs_match_results (ADR-004, 7 USC §2025(e))

IEVS income-match results per (application_id, person_id, match_source). match_source identifies the federal-data source (UI wage, IRS 1099, SSA SOLQ, etc.); verified_monthly_income + verified_income_type + verified_frequency carry the verified values; match_status lifecycle starts at pending. Unique (application_id, person_id, match_source).

ievs_discrepancies (ADR-004)

Discrepancies between self-reported and IEVS-verified income. FK to ievs_match_results(id). variance_monthly is a STORED generated column: verified_monthly_income - COALESCE(self_reported_monthly_income, 0). resolution_status lifecycle starts at pending; partial index excludes resolved rows. resolved_by_sub records the resolving worker’s keycloak sub (T1-9 #677); resolved_fact_id records the canopy-persons fact_id the worker authored on accept (#876) — a race-free discrepancy↔fact link, nullable (open/rejected rows have none), opaque to snap (no cross-service FK, ADR-001).

citizenship_verifications (ADR-004)

SAVE-source immigration-status outcomes. Per ADR-004, the raw SAVE query lives transiently in canopy-verification; only the eligibility-relevant outcome lands here. Carries save_case_number, immigration_status_category, lawful_presence_verified, qualified_alien_category, the five_year_bar_applies / five_year_bar_met_date 8 USC §1613 gate, and the snap_eligible + snap_eligibility_basis resolution.

snap_tsnap_certifications (7 CFR 273.26 / PAMMS 3704)

Transitional SNAP (TSNAP) certification windows when a household exits TANF cash assistance. Five months of frozen benefits per 7 CFR 273.26. Carries the certification_start_date / certification_end_date window, frozen_benefit_amount, the pre_closure_snap_allotment baseline, tanf_grant_removed (the amount excluded from income calc), reporting_required / sanctions_applicable flags, and the tanf_closure_reason.

overpayment_claims (PAMMS 9000 / 7 CFR 273.18)

Per-program overpayment claims. Byte-identical schema across all five program services. claim_basis ∈ {agency_error, inadvertent_household_error, ipv}; error_type carries the narrative discriminator; claim_amount_cents is the original assessed balance (outstanding is computed at read time from the ledger); status lifecycle ∈ {open, in_repayment, closed, written_off, void}. Since #1104 (epic &72 MR 4.1) claims carry pipeline provenance — appeal_id, adverse_action_id, assessment_id, source_event_id — with partial UNIQUEs on assessment_id and source_event_id (one claim per assessment / per delivery, ever: a redelivered appeal.overpayment_assessed is a no-op, not a duplicate), plus the void path (voided_at, void_reason ∈ {veto, action_canceled, reallocation}): a vetoed/canceled assessment retires its claim without pretending it never existed.

repayment_plans

One or more repayment plans per claim. monthly_amount_cents + starts_on / ends_on define the schedule; status ∈ {active, suspended, completed, defaulted}. FK to overpayment_claims(id).

recoupment_ledger

Append-only ledger of recoupment events. FK to overpayment_claims(id) and optional FK to repayment_plans(id). method ∈ {allotment_reduction, cash_payment, tax_offset, write_off, manual_adjustment} — allotment_reduction is 7 CFR 273.18 default. Outstanding balance = claim_amount_cents + SUM(claim_adjustments.delta_cents) - SUM(amount_cents); status recompute in Rust (not a DB trigger) — closed at zero, and an upward adjustment REOPENS a closed claim (append-only corrections make closed a derived fact, not a ratchet; void/written_off are sticky).

claim_adjustments (#1104)

Append-only principal corrections. FK to overpayment_claims(id). Signed delta_cents (downward corrections negative), reason ∈ {reallocation, void, correction, manual} (crate-owned vocabulary), optional source_assessment_id/actor/notes, and requires_ops_review — stamped when the entry leaves the claim over-recovered (refund/credit is an operator action; the ledger records the fact). Corrections never rewrite claim_amount_cents.

overpayment_recomputes (T2-8 #681, ADR-028 §70)

Audit + idempotency record of a worker-actioned overpayment recompute-from-snapshot — one row per recompute attempt that resolved a corpus. Unique (baseline_determination_id, correction_as_of) makes a retry idempotent (Decision J). Captures the baseline determination + household + recipient person_id (the snapshot’s head-of-household, Decision N), the correction_as_of (which bounds the clawback window, never the fact-read date), the baseline + recomputed allotments in cents (NULL when denied), the summed overpayment_cents (≥ 0) + affected_months, the [covered_period_start, covered_period_end] window (the overlap-guard key, Decision K), the typed outcome (claim_created / no_overpayment / underpayment_found / below_threshold / provisional_excluded / overlapping_claim — every one HTTP 200) + an optional outcome_message, the claim_basis, the claim_id of the #382 overpayment_claims row (present iff claim_created), a notice_id (NULL — the OverpaymentNotice is generated async by canopy-notices), the corpus_hash_used pinned for the replay, and the requesting worker. Degraded outcomes (no baseline snapshot / unavailable corpus / shredded DEK) write no row (the replay never ran) — so corpus_hash_used NOT NULL holds. Append-only audit; the #382 overpayment_claims row stays the claim of record.

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). Janitor (canopy-mq::InboxDrainer) sweeps processed rows older than 7 days.

Relationships

Diagram

Cross-service FKs (ADR-001 boundary)

Per ADR-001, canopy-snap holds no Postgres-level foreign keys to other services. The DB-level FKs in this schema are intra-database only: snap_determinations.application_id → snap_applications(id), snap_determinations.previous_determination_id → snap_determinations(id) (the ADR-028 §57 supersession self-FK), determination_snapshots.determination_id → snap_determinations(id), abawd_monthly_activity.abawd_tracking_id → abawd_tracking(id), abawd_discretionary_exemption_grants.exemption_pool_id → abawd_discretionary_exemptions(id), ievs_discrepancies.match_result_id → ievs_match_results(id), repayment_plans.overpayment_claim_id → overpayment_claims(id), recoupment_ledger.overpayment_claim_id → overpayment_claims(id), and recoupment_ledger.repayment_plan_id → repayment_plans(id). Every other UUID column referencing a foreign service — household_id / person_id (canopy-persons), application_id on snap_applications and ievs_* and citizenship_verifications (canopy-applications), overpayment_claims.determination_id (this service’s own snap_determinations but unconstrained because the column also accepts cross-service determinations during overpayment back-rebill flows) — is application-level only.

Retention

canopy-snap does NOT hold FTI. IEVS-sourced wage / UI / SSA-income data is a separate authorization (7 USC §2025(e); SNAP-specific CMA with SSA) and lives in ievs_match_results, ievs_discrepancies, and ievs_verification_data — sited in canopy-snap per ADR-004 and never replicated to other services' databases. IEVS retention follows the FNS schedule (3-year minimum for access logs and match results). SNAP record retention proper is 7 CFR 272.1(f): 3 years from the end of the fiscal year. The longer of the two governs; archive moves are operator-driven, not migration-driven (ADR-016 forward-only).

Indexes

  • idx_snap_applications_{household,application,status} — application list endpoints

  • idx_snap_determinations_{application,household,status,determined_at} — determination list + audit replay

  • idx_snap_determinations_previous_unique — partial UNIQUE on previous_determination_id WHERE NOT NULL (ADR-028 §57: a one-to-one supersession chain — at most one determination supersedes a given prior)

  • idx_determination_snapshots_as_of — input-snapshot lookup by evaluation date (ADR-028)

  • idx_ievs_verification_data_{person,source} — legacy IEVS lookups

  • idx_snap_participations_{person,household,program} and idx_unique_participation (unique, partial, WHERE active = true) — categorical eligibility

  • idx_snap_student_{person,household} — student-exclusion lookups

  • abawd_tracking_person_active (unique, partial, WHERE active = true), idx_abawd_tracking_{household,status} — ABAWD live state

  • abawd_activity_month (unique, (person_id, benefit_month)) — monthly activity report uniqueness

  • idx_abawd_clock_person, idx_abawd_clock_person_period (unique) — 36-month time clock

  • snap_disqual_screening_household_idx, snap_disqual_screening_type_idx (partial, WHERE active = true) — disqualification screening lookups

  • ievs_results_{application,person,source} and idx_unique_ievs_match (unique) — IEVS match results

  • ievs_discrepancies_{application,person,status} (partial on pending) — discrepancy worklist

  • idx_citizenship_verification_{application,person} — citizenship/SAVE lookups

  • idx_tsnap_{household,end_date} — TSNAP certification listings

  • overpayment_claims_status, repayment_plans_by_claim, recoupment_ledger_by_claim — overpayment lifecycle 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

  • 20260326000000_create_snap_tables.sql — original schema (snap_applications, snap_determinations, ievs_verification_data)

  • 20260328000000_create_snap_categorical.sql — snap_program_participations + snap_student_status for categorical eligibility (7 CFR 273.2(j))

  • 20260329000000_create_abawd_tables.sql — ABAWD core (abawd_tracking, abawd_monthly_activity, abawd_discretionary_exemptions, abawd_discretionary_exemption_grants, abawd_waiver_areas)

  • 20260329000001_create_disqualification_screenings.sql — snap_disqualification_screenings (drug felony / fleeing felon / probation+parole / striker)

  • 20260330000000_create_ievs_tables.sql — ievs_match_results + ievs_discrepancies (ADR-004; replaces / augments legacy ievs_verification_data)

  • 20260330000001_create_citizenship_verification.sql — citizenship_verifications for SAVE outcomes (ADR-004 — raw SAVE stays in canopy-verification)

  • 20260402000000_add_constraints.sql — uniqueness fixes from test-coverage audit (idx_unique_participation, idx_unique_ievs_match)

  • 20260407000000_create_abawd_time_clock.sql — PAMMS 3355 15-status time clock JSONB

  • 20260414000000_create_tsnap_certifications.sql — TSNAP transitional benefits (7 CFR 273.26)

  • 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

  • 20260510000001_create_overpayments.sql — canonical overpayment schema (claims, plans, ledger) per PAMMS 9000 / 7 CFR 273.18

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

  • 20260620000000_add_discrepancy_resolved_by_sub.sql — ievs_discrepancies.resolved_by_sub worker-attribution column (T1-9 #677)

  • 20260621000000_create_determination_snapshots.sql — snap_determinations.snapshot_hash column + the immutable determination_snapshots table + its append-only trigger (T1-10 #678; ADR-028)

  • 20260622000000_add_previous_determination_id.sql — snap_determinations.previous_determination_id supersession self-FK + the one-to-one partial-unique index (T2-1 Half B #683; ADR-028 §57)

  • 20260624140000_create_redaction_keys.sql — the per-service redaction_keys table + its one-way-tombstone trigger (T2-6 #687, ADR-036)

  • 20260627000000_create_overpayment_recomputes.sql — the overpayment_recomputes audit/idempotency table for replay-based overpayment recompute (T2-8 #681, ADR-028 §70); unique (baseline_determination_id, correction_as_of) + a (household_id, covered_period_start, covered_period_end) overlap-guard index

  • 20260727000000_claim_provenance_adjustments.sql — #1104 (epic &72 MR 4.1): claim provenance columns + partial UNIQUEs (assessment_id, source_event_id), the void path, and the append-only claim_adjustments table; canonical copy in crates/canopy-overpayments/migrations/, byte-parity asserted by the crate’s tests

  • 20260630000000_add_ievs_resolved_fact_id.sql — ievs_discrepancies.resolved_fact_id nullable column: the canopy-persons fact_id the worker authored on accept, a race-free discrepancy↔fact link + retry idempotency (#876, T1-9 #677); no index / no cross-service FK (ADR-001)

  • 20260902000000_overpayment_claims_keyset_idx.sql — #1222 (scale audit M11): overpayment_claims_keyset (created_at DESC, id DESC) serving the roll-up keyset page; transactional CREATE INDEX (not CONCURRENTLY — the sqlx migrator’s advisory lock deadlocks against CONCURRENTLY’s snapshot wait); canonical copy in `crates/canopy-overpayments/migrations/, byte-parity asserted by the crate’s tests

All migrations are forward-only per ADR-016.

snap_determinations.trigger (#1213, migration 20261121000000_snap_determinations_trigger.sql)

Nullable TEXT, CHECK-pinned to the six D9 kebab-case spellings (an always-running test mirrors the CHECK against DeterminationTrigger::ALL). Signed on the envelope, stored on the row, carried additively on the read DTO, hearing view, and determination.completed.snap.

Edit this page · default