canopy-caps Data Model

On this page

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

Tables

Table Purpose

caps_applications

CAPS application intake records. One row per (application, child) pair. Carries the child’s age + special-needs flag, household size, optional provider_id (FK to caps_providers per #396), and the initial / continued eligibility-type flag that drives the SMI threshold lookup.

caps_determinations

Signed eligibility determinations per child. Carries the three CAPS gate flags (income_eligible, activity_eligible, age_eligible), the computed copayment_weekly_cents + authorized_weekly_hours, the denial_reasons text array on denial, ruleset_version for audit replay, the ADR-002 jws_token over the canonical signing payload, 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).

determination_snapshots (ADR-028)

Immutable determination input snapshots (T2-4). One row per child determination (PK = FK determination_id → caps_determinations(id)): the typed DeterminationSnapshot as a canonical snapshot JSONB blob (the household income rules_input + ruleset output, the per-child gate results, resolved policy params, ruleset corpus-hash, and the household composition as the fact record), 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 caps_determinations.snapshot_hash. CAPS is non-FTI, so the snapshot does not join the ADR-014 chain. T2-2 (#679): each per-child blob also carries the self-explaining derivation_graph (the income-test firing + the Rust-side activity/age gates + the copayment-tier lookup) — schema_version: 3 (ADR-028 Amendment 2). 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.

caps_authorizations

Provider authorization rows. One row per (determination, provider) — a single determination can spawn multiple authorizations over time (current active row + historical terminated rows from #398 seed lifecycle). FK to caps_determinations(id); FK to caps_providers(id) per #396. Carries weekly_hours, rate_cents_per_hour, and the per-row copayment_weekly_cents so the determination’s snapshot at issuance time is preserved.

caps_providers (#396)

Provider registry. provider_code UNIQUE; legal_name, optional license fields, status ∈ {active, inactive}. Soft-delete only — historical authorizations carry the FK, so a hard delete would break referential integrity. Partial-index on active rows for the default /v1/providers?status=active listing.

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-caps holds no Postgres-level foreign keys to other services. The intra-database FKs are caps_authorizations.{determination_id,provider_id} and determination_snapshots.determination_id → caps_determinations(id). The columns marked FK → canopy-persons / FK → canopy-applications above are application-level foreign keys: canopy-caps trusts the orchestrator (canopy-eligibility) to supply real IDs but does not enforce existence in canopy_caps. This is the program-isolation contract; cross-service consistency is the orchestrator’s responsibility, not the program DB’s.

Retention

CAPS does not handle FTI or PHI; no Pub 1075 / HIPAA retention floor applies. Operational retention is governed by 45 CFR 98.65 (CCDF record retention — minimum 3 years for state administrative records). caps_determinations and caps_authorizations are retained indefinitely in the production system; archive moves are operator-driven, not migration-driven.

Indexes

  • idx_caps_applications_{app,household,child} — per-FK lookups

  • idx_caps_applications_provider (partial, WHERE provider_id IS NOT NULL) — provider rollups across applications (#396)

  • idx_caps_determinations_{application,household,child,status,effective} — list endpoints

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

  • idx_caps_authorizations_{determination,child,provider,status} — authorization-tab queries on the worker portal

  • idx_caps_providers_active (partial, WHERE status='active') — default /v1/providers listing

  • idx_caps_providers_code — unique-key support for provider_code

  • 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

  • 20260413000000_create_caps_tables.sql — original schema (caps_applications, caps_determinations, caps_authorizations)

  • 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

  • 20260512000001_add_caps_providers.sql — #396 provider registry; drops pre-1.0 TEXT provider_id columns and re-adds them as UUID FKs

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

  • 20260622000000_create_determination_snapshots.sql — caps_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)

All migrations are forward-only per ADR-016.

Edit this page · default