canopy-eligibility Data Model

On this page

Tables

Table Purpose

eligibility_requests

Root orchestration record. One row per cross-program eligibility run, keyed by (application_id, household_id) with the programs_requested text array (which program services to dispatch to). requested_by is the caller’s identity (worker Keycloak sub or service principal); requested_at / completed_at bound the run. status is constrained by eligibility_requests_status_check to the closed set pending / in_progress / completed / failed (migration 20260402000000). The idx_unique_pending_request partial unique index prevents two concurrent in-flight runs for the same (application, household).

program_determinations

Per-program signed-determination record. One row per program service response within a run; eligibility_request_id FKs back to eligibility_requests(id) (the only Postgres-level FK in this schema). Stores the verified envelope, never raw program data per ADR-002: status, benefit_amount / benefit_unit, the effective_date / expiration_date / renewal_date window, the basis string, the emitting program_service_version, determined_at, the JWS signature, and signature_verified (the orchestrator’s JWS-verification verdict); plus the policy-provenance columns — trigger and policy_target (JSONB: corpus hash + params digest + effective period) from #1213, stamped on bulk rows with the run’s expected target (adopted rows included; the adoption matcher is the control), and since #1479 policy_target is also populated on INTERACTIVE rows from the signed #1467 envelope exactly as it arrived (quarantined rows included) together with the new evaluated_as_of (DATE — envelope-arrival only; NULL on adopted rows, which the read view cannot attest). NULL means "no attestation arrived" (pre-#1467 rows, emitters with attestation off); no backfill, by decision. received_at marks ingest. ADR-002 forbids persisting raw inputs — only the program service’s signed output lands here.

combined_results

Assembled cross-program result for one run. One row per eligibility_request_id (FK to eligibility_requests(id)). Rolls up the per-program verdicts into the programs_approved / programs_denied / programs_pending text arrays (each defaulting to '{}'), carries the EE15-propagated medicaid_assigned_group, the total_monthly_benefit sum, and assembled_at. This is the worker-facing combined view the orchestrator returns after all program dispatches resolve.

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-eligibility holds no Postgres-level foreign keys to other services. The only DB-level FKs in this schema are intra-database: program_determinations.eligibility_request_id → eligibility_requests(id) and combined_results.eligibility_request_id → eligibility_requests(id). Every UUID column that points outside this database — eligibility_requests.application_id (→ canopy-applications), eligibility_requests.household_id (→ canopy-persons), and the same application_id / household_id carried on program_determinations and combined_results — is an application-level foreign key only; canopy-eligibility trusts the caller and the BFFs to supply real IDs but does not enforce existence. Per ADR-002 the orchestrator never persists raw program inputs: program_determinations stores only each program service’s signed, JWS-verified determination envelope, not the data that produced it.

Retention

canopy-eligibility does not hold FTI or PHI; no Pub 1075 / HIPAA retention floor applies at this orchestrator layer. The signed determinations it persists are program-output envelopes (ADR-002 black-box contract), not raw program data, so the FTI/PHI floors live in the program services that own the underlying records (canopy-tanf, canopy-medicaid). SNAP record retention (7 CFR 272.1(f)) requires 3-year minimum for case records; TANF (45 CFR 75.361, the HHS uniform-administrative-requirements floor — no dedicated TANF retention CFR exists) and Medicaid (42 CFR 431.17) likewise require 3-year minimums. The longest applicable floor governs in deployments that run multiple programs. Retention is operator-driven (archive moves, not migration-driven destructive changes — ADR-016 forward-only).

Indexes

  • idx_eligibility_requests_application — request lookup by application

  • idx_eligibility_requests_household — request lookup by household

  • idx_eligibility_requests_status — status filter on the orchestration queue

  • idx_unique_pending_request (unique, partial, WHERE status IN ('pending','in_progress'), migration 20260402000000) — prevents duplicate concurrent runs for the same (application_id, household_id)

  • idx_program_determinations_request — per-run determination fan-in

  • idx_program_determinations_program — program filter across determinations

  • idx_program_determinations_application — determination lookup by application

  • idx_program_determinations_household_determined_at (migration 20260804000000) — newest-first case-status lookup per household (#1196; the history is append-only and unbounded, so this read must stay index-scan class)

  • idx_program_determinations_household_program_determined_at (migration 20260804000000) — the #694 program-scoped case-status variant

  • idx_program_determinations_alert_status (partial, WHERE status IN (denied, sanctioned, time_limit_exceeded, disqualified, terminated, abawd_exceeded), migration 20260804000000) — cross-program alerts panel feed; the predicate must stay in lockstep with list_recent_alert_determinations or the planner stops substituting it (pinned by the EXPLAIN regression test in determination_index_scan_test.rs)

  • idx_combined_results_request — combined result by run

  • idx_combined_results_application — combined result by application

  • idx_combined_results_household — combined result by household

  • 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_eligibility_tables.sql — original orchestrator schema (eligibility_requests, program_determinations, combined_results) plus base lookup indexes

  • 20260402000000_add_constraints.sql — eligibility_requests_status_check closed-set status constraint + idx_unique_pending_request partial unique index (test-coverage-audit hardening)

  • 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

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

  • 20260804000000_pd_lookup_indexes.sql — the three #1196 program_determinations lookup indexes. Deliberately transactional (NOT CONCURRENTLY): sqlx serializes concurrent migrators on a per-database advisory lock, and a second booting replica blocks on it holding the very snapshot CONCURRENTLY waits out — a reproduced hard deadlock whose victim can be the build itself, stranding an INVALID index behind IF NOT EXISTS. Operators upgrading an already-huge live table pre-create the same indexes CONCURRENTLY out of band (verify pg_index.indisvalid), and the migration no-ops

All migrations are forward-only per ADR-016.

Cohort-run substrate (#1213, migration 20261120000000_cohort_runs.sql)

Table Purpose

cohort_runs

One row per bulk run: state machine (materializing → previewing → previewed → enacting → completed*, plus paused/canceling/canceled/failed), the frozen expected_policy_target, canary + breaker watermark, deadline, principals. A constant-expression partial unique index holds the fleet to ONE active run. Immortal (H13).

cohort_cases

One row per cohort member: dispatch_generation (delivery fence) vs program_epoch (logical execution epoch — the idempotency-key input), the frozen pair (request_body write-once, frozen_context per epoch), attempt/yield counters, preview verdicts, successor_determination_id for skips. Reaped after retention.

cohort_case_results

THE durable ledger (B2/B4): UNIQUE (case_id, program, program_epoch), inserted in the SAME transaction as the program_determinations row; adopted marks D-2c recoveries. Recovery reads this, never request status.

cohort_case_attempts

Append-only attempt audit: real attempts carry a partial-unique ordinal per (case, phase, generation); yields and late-dropped settles ride ordinal-free. Feeds the failure-rate breaker window.

cohort_run_actions

The H22 operator ledger: actor, actual privilege, reason, from/to states, typed detail. Immortal.

eligibility_requests gains bulk_case_id/bulk_program_epoch (+ a live-rows partial unique per case); program_determinations gains nullable trigger + policy_target.

Edit this page · default