canopy-tanf Data Model

On this page

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

Tables

Table Purpose

tanf_applications

TANF application intake records. One row per application received from canopy-eligibility. Carries the orchestrator’s application_id, household_id, applicant_person_id, a coarse lifecycle status (pending → in_progress → determined / error), and received_at / determined_at timestamps.

tanf_income

Income records relevant to TANF determination. Includes both self-reported and FTI-verified income. source discriminator ∈ {self_report, fti, ssa_solq, employer}; verification_status lifecycle starts at unverified. FK to tanf_applications(id).

fti_tax_data (IRS Pub 1075 / IRC §6103(l)(7))

Federal Tax Information received from IRS. All access MUST be wrapped with FTI audit logging. Carries tax_year, filing_status, adjusted_gross_income, wages_salaries_tips, self_employment_income. FK to tanf_applications(id).

ssa_match_results

SSA SOLQ/BINDEX match results (TANF CMA). match_type ∈ {solq, bindex}; carries ssn_verified flag, benefits_status ∈ {title_ii, ssi, both, none}, monthly_benefit_amount, and match_date.

tanf_time_limits

Per-person federal + state time-limit tracking (42 USC 608(a)(7)). months_used counts countable months; federal_limit_months carries the 60-month floor as a per-row regulatory snapshot (the schema DEFAULT was dropped in 2026-05-10 per #441 — values now flow through jurisdiction.toml → TanfParameterTable at INSERT time per ADR-003); state_limit_months is the optional state-imposed shorter window; exempt / exemption_reason carry domestic-violence / hardship exemptions.

tanf_work_requirements

Per-person work-requirement state. exempt + exemption_reason ∈ {age, disability, caring_for_infant, domestic_violence}; status lifecycle ∈ {pending, compliant, non_compliant, sanctioned}; sanction_level carries the PAMMS 1351 progressive tier (0/1/2/3); the #416 sanction_imposed_at / sanction_expires_at / sanction_reason triple disambiguates active vs lifted sanctions. FK to tanf_applications(id).

tanf_work_activities

Per-week work activity log. activity_type ∈ {employment, job_search, community_service, education, vocational_training}; hours_per_week + effective window. Source-of-truth for the ACF-199 Work Participation Rate computation. FK to tanf_work_requirements(id).

tanf_determinations

Signed TANF determinations. status ∈ {approved, denied, pending_verification}; benefit_amount + benefit_unit (default monthly_usd), certification window, basis narrative, denial_reason free-text, denial_reason_code canonical code emitted by o-denial-code JDM output (#422; nullable on approval, no backfill for pre-fix rows), program_service_version for audit replay, the ADR-002 detached-JWS signature, and 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). FK to tanf_applications(id).

determination_snapshots (ADR-028)

Immutable determination input snapshots (T2-4). One row per determination (PK = FK determination_id → tanf_determinations(id)): the typed DeterminationSnapshot as a canonical snapshot JSONB blob (the eligibility rules_input + output, the benefit calc, the income/asset/expense facts + household composition, resolved policy params, ruleset corpus-hash), 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 tanf_determinations.snapshot_hash. TANF is FTI-bearing — the snapshot’s creation also appends an fti_audit_log chain entry (resource_type='determination_snapshot'), so the FTI-derived artifact joins the ADR-014 tamper-evident chain (§4) + §9 breach pathway. T2-2 (#679): the blob also carries the self-explaining derivation_graph (the eligibility + benefit firings, the PAMMS 1540/1615 earned-income edge, and the inferred deprivation basis as a provisional node, #669) — schema_version: 3 (ADR-028 Amendment 2; the edges reference FTI by id, not value — ADR-014). 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.

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.

tanf_lump_sum_periods (PAMMS 1650)

Lump-sum ineligibility windows. Nonrecurring income ≥ 100% FPL = lump sum; ineligibility_months = net_amount / fpl_100_pct rounded up to whole months. shortening_events JSONB array carries the catastrophic-event exceptions that can shorten the window.

tanf_grg_payments (PAMMS 1210)

GRG (Grandparents Raising Grandchildren) payments. payment_type ∈ {msp, crisp} — MSP = $100/month per child; CRISP = one-time 4× Family Maximum; au_size carries the AU size used to compute the CRISP multiplier.

tanf_personal_responsibilities (PAMMS 1345-1370)

Per-person Personal Responsibility requirement tracking. requirement_type ∈ {immunization, school_attendance, prenatal_care, tfsp_signature, minor_living_arrangement}; status lifecycle ∈ {pending, compliant, non_compliant, good_cause, exempt}. FK to tanf_applications(id).

tanf_discrepancies (#448)

TANF verification discrepancies — discriminator: discrepancy_type. Modeled on canopy-snap’s ievs_discrepancies but without the IEVS-specific generated column. resolution_status lifecycle starts at pending; partial index excludes resolved rows. Write target for the #392 worker-portal resolve_discrepancy_tanf action.

fti_audit_log (IRS Pub 1075 §4, ADR-014)

FTI access audit log. Maintained separately from the application audit log; available for IRS on-site inspection independently. Carries accessed_by, accessed_at, purpose_code, data_elements_accessed text array, originating_system, action, resource_type, resource_id, request_id, ip_address, success. ADR-014 added previous_hash + event_hash SHA-256 columns forming an append-only tamper-evident chain; pre-migration rows have NULL chain columns and verification skips the NULL-prefix to the genesis row.

fti_audit_log_archive

Retention archive table (LIKE fti_audit_log INCLUDING ALL). Pub 1075 AU-11 7-year retention floor (ADR-004 Amendment 2). ADR-014 chain extends across the archive boundary.

overpayment_claims (42 USC 609(a)(1); 45 CFR 263.11)

Per-program overpayment claims. Byte-identical schema across all five program services. claim_basis ∈ {agency_error, inadvertent_household_error, ipv}; claim_amount_cents is original assessed balance (outstanding is computed at read time from the ledger); status lifecycle ∈ {open, in_repayment, closed, written_off, void}; since #1104: pipeline provenance (appeal_id, adverse_action_id, assessment_id, source_event_id — partial UNIQUEs on the last two make event redelivery a no-op) + the void path (voided_at, void_reason).

repayment_plans

One or more repayment plans per claim. monthly_amount_cents + window; status ∈ {active, suspended, completed, defaulted}. FK to overpayment_claims(id).

recoupment_ledger

Append-only ledger of recoupment events. method ∈ {allotment_reduction, cash_payment, tax_offset, write_off, manual_adjustment}. Outstanding balance = claim_amount_cents + SUM(claim_adjustments.delta_cents) - SUM(amount_cents); status recompute in Rust (closed at zero; upward adjustments reopen; void/written_off sticky).

claim_adjustments (#1104)

Append-only principal corrections (signed delta_cents, reason ∈ {reallocation, void, correction, manual}, requires_ops_review on over-recovery). FK to overpayment_claims(id). Corrections never rewrite claim_amount_cents.

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-tanf holds no Postgres-level foreign keys to other services. DB-level FKs are intra-database only: every tanf_application_id column references tanf_applications(id); determination_snapshots.determination_id → tanf_determinations(id); tanf_work_activities.work_requirement_id → tanf_work_requirements(id); repayment_plans.overpayment_claim_id → overpayment_claims(id); recoupment_ledger.overpayment_claim_id → overpayment_claims(id) + repayment_plan_id → repayment_plans(id). Every other UUID column referencing a foreign service — household_id / person_id / applicant_person_id / head_of_household_person_id / grandparent_person_id / grandchild_person_id (canopy-persons), application_id on tanf_applications (canopy-applications), overpayment_claims.determination_id (this service’s tanf_determinations, unconstrained for back-rebill flows) — is application-level only.

Retention

canopy-tanf holds FTI (IRC §6103(l)(7); IRS Publication 1075) in fti_tax_data. FTI audit access is logged to fti_audit_log per Pub 1075 §4; minimum 7-year retention applies to the audit log per Pub 1075 AU-11 (ADR-004 Amendment 2; rows aged out of fti_audit_log migrate to fti_audit_log_archive with the ADR-014 hash chain extending across the boundary). SSA SOLQ/BINDEX data in ssa_match_results is retained per the TANF Computer Matching Agreement terms. TANF case records overall follow the HHS uniform-administrative-requirements retention floor (45 CFR 75.361, 3-year minimum — TANF has no dedicated retention CFR analogous to SNAP’s 7 CFR 272.1(f)) and the Georgia state records-retention schedule. The longest applicable floor governs. Archive moves and FTI-expiry purges are operator-driven (ADR-016 forward-only — no destructive migrations).

Indexes

  • idx_tanf_applications_{application,household} — application lookups

  • idx_tanf_income_application — income lookup per application

  • idx_fti_tax_data_{application,person} — FTI lookup (audit-wrapped)

  • idx_ssa_match_results_application — SSA match lookup

  • idx_tanf_time_limits_person — time-limit lookup

  • idx_tanf_work_requirements_person — work-requirement lookup

  • idx_tanf_work_req_sanctioned (partial, (sanction_level, sanction_expires_at) WHERE sanction_level >= 1, migration 20260816000000) — supervisor sanctions-rollup panel (#1233); the rollup query carries a matching WHERE sanction_level >= 1 so the aggregate scans only the sanctioned subset via this index instead of seq-scanning the level-0-dominated table (predicate must stay in lockstep with the query; pinned by an EXPLAIN regression test)

  • idx_tanf_determinations_application — determination lookup

  • idx_tanf_determinations_determined_at_id ((determined_at DESC, id DESC), migration 20260817000000) — the keyset cursor backing the paginated GET /v1/determinations list (#1195); serves the newest-first page (and the ACF-199 extractor’s month-scoped page-loop) as an index scan with no top-N sort

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

  • idx_tanf_lump_sum_person — lump-sum ineligibility windows

  • idx_tanf_grg_grandparent, idx_tanf_grg_grandchild — GRG payment lookups

  • idx_tanf_pr_application, idx_tanf_pr_person — Personal Responsibility lookups

  • idx_tanf_discrepancies_household, idx_tanf_discrepancies_status (partial on pending) — discrepancy worklist

  • idx_fti_audit_{accessed_at,accessed_by,purpose_code} — FTI audit query shapes

  • idx_fti_audit_created_at — (created_at) for the ADR-014 §9 in-lock predecessor-hash lookup (ORDER BY created_at DESC LIMIT 1, held under the per-originating_system advisory lock on every FTI-bearing determination commit) and verify_chain’s ascending walk; #1197, migration 20260811000000. Closes the ADR-014 §9 doc/schema drift (the §9 budget asserted a `created_at DESC index that did not exist — the pre-#1197 indexes are on accessed_at/accessed_by/purpose_code)

  • idx_fti_audit_event_hash, idx_fti_audit_archive_event_hash — ADR-014 hash-chain verification

  • overpayment_claims_status, repayment_plans_by_claim, recoupment_ledger_by_claim — overpayment lifecycle

  • 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

  • 20260325000000_create_tanf_tables.sql — original schema (9 tables: applications, household_snapshots, income, fti_tax_data, ssa_match_results, time_limits, work_requirements, work_activities, determinations)

  • 20260325000001_create_fti_audit_log.sql — Pub 1075 §4 audit log + archive table (LIKE …​ INCLUDING ALL)

  • 20260407000000_add_lump_sum_grg_personal_resp.sql — tanf_lump_sum_periods (PAMMS 1650), tanf_grg_payments (PAMMS 1210), tanf_personal_responsibilities (PAMMS 1345-1370)

  • 20260422000000_add_denial_reason_code.sql — tanf_determinations.denial_reason_code from JDM o-denial-code output (no backfill — pre-fix rows use the legacy substring-categorized basis)

  • 20260425000000_add_fti_audit_hash_chain.sql — ADR-014 previous_hash + event_hash on fti_audit_log + archive

  • 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

  • 20260510000000_drop_federal_time_limit_default.sql — drops DEFAULT 60 from tanf_time_limits.federal_limit_months so value flows from jurisdiction.toml per ADR-003 / #441

  • 20260510000001_create_overpayments.sql — canonical overpayment schema per 42 USC 609(a)(1); 45 CFR 263.11

  • 20260727000000_claim_provenance_adjustments.sql — #1104: claim provenance + partial UNIQUEs, the void path, and claim_adjustments (canonical copy in crates/canopy-overpayments/migrations/; byte-parity crate-tested)

  • 20260511000000_add_sanction_lifecycle.sql — #416 sanction_imposed_at / sanction_expires_at / sanction_reason on tanf_work_requirements

  • 20260512000000_create_tanf_discrepancies.sql — #448 worker-portal verification discrepancies

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

  • 20260603120000_fti_audit_append_only_guard.sql — statement-level append-only guard on fti_audit_log + archive (GUC canopy.audit_maintenance)

  • 20260623000000_create_determination_snapshots.sql — tanf_determinations.snapshot_hash column + the immutable determination_snapshots table + its append-only trigger (T2-4 #685; ADR-028)

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

  • 20260630000000_drop_tanf_household_snapshots.sql — drops the superseded write-only tanf_household_snapshots table (created in 20260325000000); the verdict-affecting household composition is frozen in determination_snapshots instead (D9 #883, ADR-016 expand→contract)

  • 20260811000000_fti_audit_created_at_idx.sql — idx_fti_audit_created_at for the ADR-014 §9 in-lock predecessor lookup + verify walk (#1197, scale audit H13; closes the §9 doc/schema drift). Transactional (NOT CONCURRENTLY) for the same reason as the #1196 eligibility index migration

  • 20260816000000_sanctions_rollup_idx.sql — idx_tanf_work_req_sanctioned partial index for the supervisor sanctions-rollup panel (#1233, scale audit L1). Transactional (NOT CONCURRENTLY), same rationale

  • 20260817000000_tanf_determinations_list_keyset_idx.sql — idx_tanf_determinations_determined_at_id (determined_at DESC, id DESC) backing the keyset-paginated GET /v1/determinations list (#1195, scale audit C1). Transactional (NOT CONCURRENTLY), same rationale

  • 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

  • 20260910000000_chain_v2_substrate.sql — #1246 MR-2 (ADR-014 Amendment 6): the dormant chain-v2 fti substrate — see the chain-v2 section below

All migrations are forward-only per ADR-016.

chain-v2 substrate (dormant, #1246 / ADR-014 Amendment 6)

Installed by 20260910000000_chain_v2_substrate.sql — the fti-family copy (canopy-tanf is a chain SOURCE; the anchor store + C6 verification state live only in canopy_security). Dormant until the #1279 cutover.

Table Purpose

chain_instances / chain_topology / chain_epochs / chain_sources / chain_heads

The shared registry substrate (identical DDL to the canopy-security copy): instance history, active pointer, fenced epochs, the source registry chain_append_rows_fti validates its baked canopy-tanf literal against, and pre-created heads.

fti_audit_log_v2 / fti_audit_log_archive_v2

The FTI family’s strict-from-row-one event store (same constraint set as the audit copy). Hashed business columns are DERIVED from the payload — including the row id (a hashed, routing-relevant field, never server-minted for fti); request_id / ip_address / success ride the pinned unhashed ingress.

Ownership: every object is owned by NOLOGIN canopy_chain_owner_tanf; the canopy_tanf_app runtime role appends ONLY through the SECURITY DEFINER function (C8). Details: ADR-014 Amendment 6.

Edit this page · default