canopy-reporting Data Model

On this page
NOTE

#1456 (ADR-004 A8b): every table and function in this schema is owned by canopy_reporting_owner (NOLOGIN); the runtime connects as the restricted canopy_reporting_app login holding only the enumerated per-object grants (migrations 20261111000000/20261111000001). The sealed extract is never UPDATEd and the six generation-scoped tables are DELETE-able only through the SECURITY DEFINER reporting_janitor_reap fn, which re-verifies superseded/abandoned-past-retention eligibility inside the definer. Cutover/rollback: security-operations.

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

Tables

Table Purpose

snap_monthly_reports

FNS-388 SNAP monthly state activity report header (7 CFR 272.11). One row per (generation_id, report_month) — uniqueness moved onto the output generation by the #1202 run substrate; readers resolve the month’s PUBLISHED generation and serve only its row. Carries the aggregate household / individual / benefit counts, expedited + elderly/disabled splits, initial-cert vs recert split (classified by the LEGAL receipt month — never the UTC one, #1584), the calculated average_household_benefit, and the submission_status ∈ {draft, final, submitted, accepted, rejected} plus fns_confirmation_number from FNS acceptance — the lifecycle is LIVE since #1335 (POST /v1/reporting/snap/fns-388/{month}/submission, CAS transitions on the published generation’s row; submitted/accepted arm the #1202 immutability guard). The app role’s UPDATE grant is column-scoped to the three lifecycle columns (report figures stay immutable to the service). report_data JSONB is a write-only freeform assembly-provenance blob (no reader consumes specific keys, #898; the run pipeline writes assembly_source: "report_run_pipeline" since #1202 MR5).

snap_qc_universe

FNS-7176 Quality Control universe snapshot (7 CFR Part 275). One row per (generation_id, household, snapshot_date) — uniqueness moved onto the output generation by the #1202 run substrate; readers resolve the date’s PUBLISHED generation and filter by it. Carries the full deduction stack used in the QC review (earned-income / standard / dependent-care / medical / shelter / child-support) and the resulting net_income + benefit_amount, plus flags QC reviewers reach for first (expedited_service, abawd_household, ievs_match_completed, categorical_eligibility). abawd_household is nullable since the #1202 MR5 honesty migration (plan D8): NULL = "unverified legacy" — all pre-migration values were NULLed because an outage-fabricated false is indistinguishable from a sourced false; a stored true/false is a SOURCED value from that migration onward, and the CSV renders NULL as an empty cell (#1155 precedent).

tanf_acf199_snapshots

ACF-199 monthly TANF case snapshot. One row per (generation_id, report_month, case_id) — uniqueness moved onto the output generation by the #1202 run substrate (MR6 cutover); readers resolve the month’s PUBLISHED generation (or the latest published, when no month is given) and serve only its rows. family_type CHECK ∈ {single_parent, two_parent, child_only}. Carries total_work_hours + core_activity_hours (the WPR source-of-truth numerators), state-vs-other-state month counts (60-month federal time limit + state extensions per 45 CFR 264), sanction status, and case open/closed lifecycle. childcare_funded (#1169) marks a two-parent AU receiving federally-funded child care during the report month — sourced at extraction from the CAPS linkage (any ACTIVE authorization overlapping the month; Georgia CAPS is the state CCDF program, so no finer funding-stream discriminator exists) — and flips the WPR two-parent numerator standard from 35 to 55 combined hours per 45 CFR 261.32(c).

tanf_acf196_reports

ACF-196 quarterly TANF financial report. Composite unique on (fiscal_year, fiscal_quarter, category). fiscal_quarter CHECK 1-4. Carries the federal/state/total expenditure split and families_served; partner-blocked on real state-accounting wiring.

tanf_wpr_calculations

Monthly Work Participation Rate calculation. One row per report_month (unique). Stores both the all-family rate (target 50%) and the two-parent rate (target 90%), each with numerator/denominator/rate/meets-target columns, plus the caseload_reduction_credit. Source numerators reconcile against tanf_acf199_snapshots.core_activity_hours.

medicaid_tmsis_eligibility_extracts

T-MSIS eligibility file rows (42 CFR 431 / 42 CFR 433). One row per (generation_id, report_month, enrollment_id) — uniqueness moved onto the output generation by the #1202 run substrate (MR6 cutover); readers resolve the month’s PUBLISHED generation (or latest) and serve only its rows. Encrypted at rest (#1256, ADR-004 A8a / Amendment 3): only the engine-evaluated keys are plaintext columns — person_id, enrollment_id, report_month, generation_id, chip_indicator, coverage_group (the 38-COA-mapped group, kept plaintext for the CMS-64 GROUP BY); the remaining attributes (eligibility_status, eligibility start/end dates, income-as-percent-of-FPL, citizenship, disability_indicator, dual_eligible_indicator + dual_eligible_category, managed-care fields, restricted_benefits_indicator) are sealed together in one restricted_payload JSONB (ADR-036 context-bound SealedValue) under a per-generation DEK, decrypted in-service on read/export. The 38-COA mapping is sourced from canopy-medicaid determinations (cross-service application FK).

redaction_keys

The ADR-036 crypto-shred key store (#1256): KEK-wrapped per-subject DEKs. subject_kind = 'report_generation' — one live DEK per report generation, sealing that generation’s T-MSIS restricted_payload rows. One-way tombstone trigger (INSERT + single live→shredded UPDATE only) + a partial unique index on the live subject (the atomic get-or-create’s conflict target). Identical DDL to the per-service stores shipped for canopy-persons / the program services.

medicaid_cms64_reports

CMS-64 quarterly expenditure report. Composite unique on (fiscal_year, fiscal_quarter, expenditure_category, population_group). Carries enrolled_count, member_months, and federal/state/total expenditure split. Member-months computed from T-MSIS extracts; federal share split is partner-blocked on MMIS.

medicaid_cms416_reports

CMS-416 annual EPSDT report. Composite unique on (generation_id, report_year, age_group) — uniqueness moved onto the output generation by the #1202 run substrate (MR6 cutover); readers resolve the year’s PUBLISHED generation (or latest) and serve only its rows. The producing run’s inputs are PINNED published T-MSIS generation ids (report_generations.input_generation_ids, plan D2). Carries total_enrolled_children, total_member_months, eligible_for_screening, the received_initial_screening / received_periodic_screening counts, and the calculated screening_ratio. Real screening counts partner-blocked on clinical systems.

report_generations / report_runs / report_run_universe

The #1202/#1203 durable report-run substrate (plan report-run-generations D1–D3): permanent generation provenance rows with atomic staged→published promotion, the guarded token-fenced job table, and the materialized per-run universe. Generation lifecycle: staged → published on a successful run, published → superseded when a newer run publishes, and (#1462) staged → abandoned when its run terminalizes to error (via either report_run_finalize or `report_run_claim’s attempts-cap path) — so the janitor (which reaps only superseded/abandoned generations' output/universe/sealed-extract rows) reclaims a failed run’s rows instead of leaking them. Model, lifecycle, and operations are documented in the report-runs runbook.

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.

Relationships

Diagram

Cross-service FKs (ADR-001 boundary)

canopy-reporting is a downstream aggregator and holds no Postgres-level FKs to other services. Every UUID that points outside canopy_reporting is an application-level reference: snap_qc_universe.household_id / certification_id (canopy-persons, canopy-snap), tanf_acf199_snapshots.case_id (canopy-tanf), medicaid_tmsis_eligibility_extracts.person_id / enrollment_id (canopy-persons, canopy-enrollment), snap_monthly_reports.generated_by (worker id, no service-side enforcement). The aggregator pulls data via the program services' HTTP APIs (no cross-database joins) per ADR-001.

Retention

Federal-submission records — retention is per-federal-partner. SNAP records (7 CFR 272.1(f)): minimum 3 years from the end of the fiscal year. TANF records (45 CFR 92.42): 3 years from the date of submission. Medicaid records (42 CFR 433.32): 3 years from the date of submission of the final claim, longer if audit is open. CMS-64 / CMS-416: typically 7 years to cover federal audit cycles. In practice rows are retained indefinitely in the production DB; archive moves are operator-driven and gated by federal partner sign-off on the submission window.

Indexes

  • snap_monthly_reports_generation_month_uq (unique) — one row per (generation, report_month); replaced idx_snap_monthly_reports_month when uniqueness moved onto the output generation (#1202 MR4 backfill migration)

  • idx_snap_qc_universe_snapshot — per-snapshot listing

  • idx_snap_qc_universe_household — per-household lookup

  • snap_qc_universe_generation_household_snapshot_uq (unique) — one row per (generation, household, snapshot); replaced idx_snap_qc_universe_household_snapshot when uniqueness moved onto the output generation (#1202 MR4 backfill migration). Also the keyset ride for the generation-scoped paginated QC-universe reads (#1202 MR5): each page (WHERE generation_id = $1 [AND household_id > $3] ORDER BY household_id LIMIT $2) is a contiguous index range — seek, read one page, stop

  • idx_snap_qc_universe_snapshot_household — the #1221 (snapshot_date, household_id) keyset composite the pre-generation reads rode; retained for snapshot-scoped scans

  • tanf_acf199_generation_month_case_uq (unique) — one case-snapshot per (generation, month, case); replaced tanf_acf199_month_case when uniqueness moved onto the output generation (#1202 MR4 backfill migration)

  • tanf_acf199_month — per-month listing

  • tanf_acf196_fy_quarter_cat (unique) — composite uniqueness on quarterly financial report key

  • tanf_wpr_month (unique) — single WPR row per report_month

  • medicaid_tmsis_generation_month_enrollment_uq (unique) — one extract row per (generation, month, enrollment); replaced medicaid_tmsis_month_enrollment when uniqueness moved onto the output generation (#1202 MR4 backfill migration)

  • medicaid_tmsis_cms416_universe_idx — the (report_month, chip_indicator, person_id) composite the CMS-416 local-universe drain rides (half-open report-year range + chip_indicator = false + person_id keyset order, EXPLAIN-pinned; #1202 MR6); replaced the prefix-redundant single-column medicaid_tmsis_month (dropped in 20261101000004)

  • medicaid_tmsis_person — per-person lookup

  • medicaid_cms64_fy_quarter_cat_pop (unique) — composite uniqueness on CMS-64 row key

  • medicaid_cms416_generation_year_age_uq (unique) — one row per (generation, report_year, age_group); replaced medicaid_cms416_year_age when uniqueness moved onto the output generation (#1202 MR4 backfill migration)

  • 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

Migration files

  • 20260401000000_create_reporting_tables.sql — SNAP FNS-388 monthly reports + FNS-7176 QC universe snapshot

  • 20260409000000_tanf_medicaid_reporting_tables.sql — TANF ACF-199 monthly snapshots, ACF-196 quarterly financial, WPR calculations; Medicaid T-MSIS eligibility extracts, CMS-64 quarterly expenditures, CMS-416 annual EPSDT

  • 20261110000000_reporting_redaction_keys.sql — the ADR-036 redaction_keys store (#1256): per-generation DEKs + one-way tombstone + live-subject unique index

  • 20261110000001_seal_restricted_reporting_stores.sql — #1256 A8a: adds error_code values admin_reset/crypto_failure; the quiesced fresh-start reset (terminalize in-flight tmsis/cms_416 runs, supersede tmsis generations, clear their universe rows + derived CMS-64/416 rows); reshapes medicaid_tmsis_eligibility_extracts (drop the 11 plaintext attribute columns, add restricted_payload)

  • 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

  • 20260723120000_qc_stub_fields_nullable.sql — #1155: the QC stub fields (expedited_service, work_registration_exempt_count, ievs_match_completed) nullable + historical values NULLed (unknown is NULL, never a fabricated false/0)

  • 20260820000000_qc_universe_keyset_index.sql — the (snapshot_date, household_id) keyset composite for the paginated QC-universe reads (#1221; transactional NOT CONCURRENTLY per the #1196 rationale)

  • 20261101000000_report_generations.sql + 20261101000001_report_runs.sql + 20261101000002_report_run_universe_and_legacy_backfill.sql — the #1202/#1203 durable report-run substrate (plan report-run-generations): the report_generations publication model, the guarded report_runs job table, the materialized report_run_universe, generation_id on all five output tables with uniqueness moved to (generation_id, natural key), and the legacy published-generation backfill. Backfilled legacy generations are pure DATA — run_id NULL (surfaced as provenance.run_id = null), build_version/params_hash 'legacy'; the legacy_generation_id write-path helper that stamped the synchronous extracts' rows was deleted in #1202/#1203 MR6 with its last consumers (every kind now writes through the run pipeline’s staged generations)

  • 20261101000003_abawd_household_unverified_legacy_null.sql — #1202 MR5 (plan D8): snap_qc_universe.abawd_household nullable + DEFAULT dropped + ALL historical values NULLed — outage-fabricated false is indistinguishable from sourced false; NULL = "unverified legacy"

  • 20261101000004_cms416_universe_index.sql — #1202/#1203 MR6 (plan D2): adds medicaid_tmsis_cms416_universe_idx (report_month, chip_indicator, person_id) for the CMS-416 local-universe drain and DROPS the prefix-redundant medicaid_tmsis_month

All migrations are forward-only per ADR-016.

Edit this page · default