canopy-applications Data Model

On this page

Tables

Table Purpose

applications

Root intake record. One row per submission. Carries household_id, submitted_by, optional authorized_representative_id, the programs_requested text array (ACA §1413 single-streamlined), the submission_channel, the SNAP-driver expedited-screening fields (expedited_screened_at, expedited_eligible, expedited_basis), and the interview lifecycle (interview_required, interview_completed_at, interview_waived, interview_waived_reason). submitted_by_role discriminates self-service vs. worker-entered submissions. status is constrained by applications_status_check to the closed set submitted / processing / data_collected / determined / withdrawn / denied / approved (migration 20260601000001, worker-intake program-independence Plan MR2). The nullable notify_email / notify_phone_e164 columns (Plan 3 MR4) hold the application-time contact used by the recovery side-channel (applicant-portal design ref §3.7 — notify the contact on file at original application, not a recently-changed one). The recovery-gate columns (Plan 3 MR8a, #634) confidentiality (CHECK-constrained to standard / confidential / address_confidential / both, default standard) and recovery_locked (boolean, default false) drive the lost-credential recovery gate: confidential / both disable self-serve recovery (applicant-portal design ref §3.8 — route to the helpline), and the kill-switch sets recovery_locked so a worker must clear it before self-serve is available again. They are an orthogonal flag + boolean, deliberately not new status values (which would pollute every status consumer). Soft-delete via active.

application_programs

Per-program child rows on each application. One row per (application, program); a single application requesting SNAP + Medicaid + TANF creates three rows. Carries the per-program processing_deadline (7 CFR 273.2 for SNAP, 42 CFR 435.912 for Medicaid; TANF has no federal processing-deadline regulation — its deadline follows the Georgia state plan / PAMMS timeliness standard), anchored to the jurisdiction’s legal receipt day (date_in(received_at, [jurisdiction].timezone), #1581), determination_id + determination_received_at once the program service responds, denial_reason_codes text array on denial, and TANF’s tanf_service_type discriminator. status is constrained by application_programs_status_check to the closed set pending / processing / data_collected / determined / approved / denied / withdrawn (migration 20260601000001). Soft-delete via active; unique on (application_id, program) WHERE active = true.

application_sections (worker-intake Plan MR2)

Per-program intake-section storage. One active row per (application_id, program, section_name), capturing the worker’s verification action on a named person / household / income / asset / expense record per ADR-001 + ADR-002. The payload JSONB is action metadata only — it never duplicates SSN / DOB / amounts (those live in canopy-persons). completed_at marks section completion; last_edited_by is the worker’s Keycloak sub (not FK’d — workers are not canopy-persons rows). Soft-delete via active; unique on (application_id, program, section_name) WHERE active = true.

authorized_representatives

Authorized-rep records per household (7 CFR 273.1(f), 45 CFR 162). One row per (household, representative_person_id, effective_date) window. written_consent_on_file records the policy gate; effective_date / expiration_date bound the authorization. Soft-delete via active.

household_assignments (#408)

Per-worker case-assignment table. Sole source of truth across services that gate household-scoped reads on assignment (canopy-enrollment first; canopy-renewals / canopy-notices / canopy-reporting on adoption). Pub 1075 AC-6 least-privilege baseline. Soft-delete via unassigned_at; unique on (worker_id, household_id) WHERE unassigned_at IS NULL. ADR-001 sites this table here because assignment is application-lifecycle metadata, not program-benefit data.

application_id_codes (Plan 3 MR4, ADR-026)

Applicant-facing Application ID code (HH-[a-f0-9]{8}; code is CHECK-constrained to that shape + UNIQUE). Keyed on the reserved application_id (UNIQUE) with no FK to applications — the credential is minted at draft-start, before any applications row exists (ADR-026 reserved-id lifecycle); integrity is enforced by finalize + the reaper, not a DB FK. Generated by canopy_common::credentials::generate_application_code, with the store-layer collision-retry that persists it landing with create-draft (Plan 3 MR6).

passcode_hashes (Plan 3 MR4, ADR-026)

argon2id hash of the applicant’s 12-digit passcode (NNNN-NNNN-NNNN, ADR-008 Amendment 3 — digit_count is CHECK-constrained to 12; no wordlist word_count / language columns). Keyed on the reserved application_id with no FK to applications and no table-level UNIQUE (which would block rotation); the partial unique index passcode_hashes_active_per_app WHERE revoked_at IS NULL enforces one active hash per application, while revoked rows persist for audit (revoked_at / revoke_reason). Hashed/verified by canopy_common::credentials::{hash_passcode, verify_passcode}.

application_drafts (Plan 3 MR6, ADR-026)

Client-side-encrypted Apply-form draft — the server stores ciphertext it cannot bulk-read. Keyed on the reserved application_id (PRIMARY KEY) with no FK to applications (the row is created only at finalize; reserved-id lifecycle). Holds the per-draft Argon2id kdf_salt (non-secret, persisted for cross-session resume), the XChaCha20-Poly1305 ciphertext + nonce the WASM client produces, enc_version (crypto agility), and current_step (CHECK BETWEEN 1 AND 4). expires_at is the sliding deadline last_saved_at + 30 days, rewritten on every save; the reaper deletes WHERE expires_at < now() and no non-terminal finalize_operations row exists (ADR-038 MR6 — a live saga or in-flight compensation protects the draft) (and, in explicit ordered deletes — not ON DELETE CASCADE — the matching reserved application_id_codes / passcode_hashes, since finalize must delete the draft while keeping the credentials). Written by store::drafts::{mint_draft, patch_draft} behind the service-caller-gated create-draft / patch-draft endpoints.

recovery_pending (Plan 3 MR8a, ADR-026 / applicant-portal design ref §3.7)

24-hour pending lost-credential recovery state. One active row per application (partial unique index recovery_pending_active_per_app WHERE killed_at IS NULL AND completed_at IS NULL — a re-initiation while one is pending is absorbed, so the contact is not re-notified). FKs application_id → applications(id) (recovery is for submitted applications only, so unlike the credential tables this one does carry the FK). Holds reveal_at (now + 24h — the passcode is held this long, applicant-portal design ref §3.7), the kill_switch_token (UNIQUE, a 256-bit secret carried in the side-channel notification; the "this wasn’t me" capability), the notify_email / notify_phone_e164 snapshot (the application-time contact, copied at insert), the terminal killed_at / completed_at timestamps, and the initiator_ip / initiator_device_cookie capture (portal-populated in MR8b). Written by store::recovery::{initiate, kill}; housekept by the recovery-pruner daily scheduler tick. The kill-switch token is read from this row by the MR8c notification subscriber (it never rides the outbox event — a capability secret stays off the broadcast bus).

application_documents (Plan 3 MR9a)

Uploaded applicant/worker documents. The bytes live in object storage (canopy-store) keyed by s3_key ({program}/{application_id}/{sha256}); the row records the post-validate_upload metadata (#435 — content_type, size_bytes, the raw BYTEA sha256, scan_status, the untrusted original_filename + the sanitised sanitized_filename). FKs application_id → applications(id); person_id is the subject (a bare UUID — persons live in canopy-persons per ADR-001, no cross-DB FK). document_type is CHECK-constrained to the coarse applicant-facing set (identity / income / residency / citizenship / other); the optional document_kind is the worker-facing form class. uploaded_by_source (applicant_portal / worker_intake) is an explicit upload parameter, not inferred from the caller (both BFFs authenticate as services). The worker review state is accepted_at / accepted_by / rejection_reason / rejected_by (accept + reject are mutually exclusive — each clears the other’s columns; surfaced as a derived review_status on read). Both reviewer columns are populated ONLY from the reviewing worker’s own verified identity — since #1443 the exchanged bearer’s sub (#1009 introduced the never-a-request-body rule via the since-retired actor claim). ADR-042 (#1006) makes the table ALSO the scan-promotion queue: every insert binds scan_status = 'pending' (the noop token is dead); verdict provenance (scan_backend, scan_backend_version, scanned_at, scan_detail), worker-queue bookkeeping (scan_attempts, scan_due_at, the claim triple scan_claimed_at/scan_claimed_by/scan_claim_token, scan_last_error, scan_generation), and the supervisor-override triple (scan_override_by/scan_override_at/scan_override_reason) ride the row. Seven named CHECKs enforce the state machine in the DB — token set, non-negative attempts, all-or-none claim + override triples, override-only-on-skipped, terminal-verdict provenance, and ck_docs_accepted_viewable (an acceptance can only exist on a viewable document). Written by store::documents::{insert_document, accept_document, reject_document, claim_next_scan, settle_scan, defer_scan, requeue_scan, override_scan} behind the service-caller-gated document endpoints and the scan worker.

finalize_operations (epic &71 MR4, ADR-038)

Durable finalize-saga record — one row per reserved application id (= application_drafts PK; no FK — the row outlives the draft, which the final transaction deletes). Carries the state machine (state CHECK IN (in_progress / compensating / completed / aborted) — TEXT + CHECK per service convention), the per-attempt fencing lease (lease_holder UUID + lease_expires_at; every saga write is WHERE lease_holder = $claim_id), the pinned per-generation inputs (basis_date, received_at, keyed-HMAC request_digest BYTEA) so a resumed attempt rebuilds byte-identical persons writes, household_id (set at completion — reconstructs the FinalizeResponse for a replayed claim), events_released (the post-commit persons release(op,gen) confirmation the reconciler retries until true), and attempts. Three CHECKs pin the state machine: a live attempt always carries its lease; a completed op always knows its household; terminal states carry no lease. Written by store::finalize_ops::{claim_or_resume, heartbeat, mark_completed, claim_for_compensation, mark_aborted, mark_events_released}.

finalize_steps (epic &71 MR4, ADR-038)

Local skip-cache of the persons-side finalize receipts: (application_id, generation, step_key) PK → the persons remote_kind (CHECK person/household/member/income/asset/expense) + remote_id (the correction-surviving stable id). The persons finalize_receipts table is the cross-service correctness source of truth — a lost cache row only costs one redundant, receipt-deduplicated persons call. FK application_id → finalize_operations ON DELETE CASCADE; rows are cleared on an aborted re-submit’s generation bump (a new filing, ADR-038). Written by store::finalize_ops::{record_step, load_progress}.

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.

scheduler_runs (#1211)

Wall-clock window fence for the service’s daily scheduler tick(s) (canopy-applications.draft-reaper, canopy-applications.recovery-pruner). Schema single-sourced in crates/canopy-db/scheduler-migrations/ and parity-gated by cargo xtask outbox-migrations; documented ONCE in the data-models index.

Relationships

Diagram

Cross-service FKs (ADR-001 boundary)

Per ADR-001, canopy-applications holds no Postgres-level foreign keys to other services. The only DB-level FKs in this schema are intra-database: application_programs.application_id → applications(id) and the in-row authorized_representative_id self-reference. Every UUID column marked FK → canopy-persons in the ERD (household_id, submitted_by, representative_person_id, worker_id) is an application-level foreign key — canopy-applications trusts the orchestrator (canopy-eligibility) and the BFFs to supply real IDs, but does not enforce existence. application_programs.determination_id is a cross-service ID pointing into the relevant program service’s database (canopy-snap / canopy-tanf / canopy-medicaid / canopy-caps / canopy-wic) and is likewise application-level only.

Retention

canopy-applications does not hold FTI or PHI; no Pub 1075 / HIPAA retention floor applies at this layer. 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) requires 3-year minimum; Medicaid (42 CFR 431.17) requires 3-year minimum from claim closure. 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

  • applications_household_idx — household lookup

  • applications_status_idx (partial, WHERE active = true) — status filter on caseload search

  • applications_submitted_by_received_at_idx (partial, WHERE active = true, #402) — caseload search by submitter ordered most-recent-first

  • applications_received_at_idx (partial, WHERE active = true, #402) — all-applications listing ordered most-recent-first

  • application_programs_unique (unique, partial, WHERE active = true) — enforces one active row per (application, program)

  • application_programs_program_active_idx (partial, WHERE active = true, #402) — supports the program-filter EXISTS clause in caseload search

  • application_sections_unique (unique, partial, WHERE active = true) — enforces one active section row per (application_id, program, section_name)

  • application_sections_app_program_idx (partial, WHERE active = true) — per-application/program section listing

  • application_sections_last_edited_by_idx (partial, WHERE active = true) — worker-attribution lookup

  • auth_reps_household_idx (partial, WHERE active = true) — household lookup of active representatives

  • household_assignments_active_uniq (unique, partial, WHERE unassigned_at IS NULL) — enforces one active assignment per (worker, household)

  • household_assignments_by_worker_active (partial, WHERE unassigned_at IS NULL) — worker caseload

  • household_assignments_by_household_active (partial, WHERE unassigned_at IS NULL) — reverse lookup ("who owns this household")

  • application_id_codes_code_idx (Plan 3 MR4) — code lookup for the /lookup resume/login path

  • passcode_hashes_active_per_app (unique, partial, WHERE revoked_at IS NULL, Plan 3 MR4) — one active passcode hash per application_id; revoked rows persist for audit

  • application_drafts_expires_idx (Plan 3 MR6) — expires_at scan for the sliding-window reaper (WHERE expires_at < now())

  • recovery_pending_active_per_app (unique, partial, WHERE killed_at IS NULL AND completed_at IS NULL, Plan 3 MR8a) — one active recovery per application (absorbs re-initiation; no duplicate notification)

  • recovery_pending_reveal_due_idx (partial, WHERE completed_at IS NULL AND killed_at IS NULL, Plan 3 MR8a) — reveal_at scan for the reveal screen + the pruner’s staleness sweep

  • application_documents_app_idx ((application_id, uploaded_at DESC), Plan 3 MR9a) — the case-detail Documents list query (newest first)

  • application_documents_person_idx ((person_id), Plan 3 MR9a) — per-person document lookup

  • application_documents_pending_idx (partial, (application_id, document_type) WHERE accepted_at IS NULL AND rejection_reason IS NULL, Plan 3 MR9a) — outstanding (un-reviewed) documents per application

  • application_documents_scan_due_idx (partial, (scan_due_at, id) WHERE scan_status = 'pending', ADR-042) — the scan worker’s deterministic claim order

  • finalize_operations_stuck_idx (partial, (state, lease_expires_at) WHERE state IN ('in_progress','compensating'), epic &71 MR4) — the reconciler’s scan for lease-lapsed / mid-compensation operations

  • finalize_operations_unreleased_idx (partial, (state) WHERE state = 'completed' AND events_released = false, epic &71 MR4) — the release-retry scan for committed apps whose persons events are still held

  • 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

  • 20260401000000_create_applications_tables.sql — original schema (applications, application_programs, authorized_representatives) including interview / expedited-screening / submission-channel columns

  • 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_add_list_filter_indexes.sql — composite + partial indexes for the caseload-search query shape per #402

  • 20260511000000_create_household_assignments.sql — #408 per-worker assignment table for cross-service household RBAC (Pub 1075 AC-6 least-privilege)

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

  • 20260601000000_create_application_sections.sql — worker-intake program-independence Plan MR2 per-program intake-section storage (application_sections)

  • 20260601000001_extend_applications_status.sql — worker-intake program-independence Plan MR2 applications_status_check + application_programs_status_check CHECK constraints (advisory-lock-guarded expand-only backfill)

  • 20260602000000_create_application_id_codes.sql — Plan 3 MR4 applicant Application ID code table (application_id_codes; reserved-id, no applications FK per ADR-026)

  • 20260602000001_create_passcode_hashes.sql — Plan 3 MR4 argon2id passcode hash table (passcode_hashes; reserved-id, partial-unique active row, rotation-friendly)

  • 20260602000002_extend_applications_notify_columns.sql — Plan 3 MR4 notify_email / notify_phone_e164 on applications for the recovery side-channel (applicant-portal design ref §3.7)

  • 20260603000000_create_application_drafts.sql — Plan 3 MR6 client-side-encrypted Apply-form draft table (application_drafts; reserved-id, no applications FK per ADR-026; sliding 30-day expires_at)

  • 20260604000000_extend_applications_recovery_gate.sql — Plan 3 MR8a (#634) confidentiality (CHECK-set) + recovery_locked boolean on applications — the lost-credential recovery gate (applicant-portal design ref §3.8)

  • 20260604000001_create_recovery_pending.sql — Plan 3 MR8a 24h pending-recovery state (recovery_pending; FKs applications; active-per-app partial unique index; kill-switch token)

  • 20260605000000_create_application_documents.sql — Plan 3 MR9a applicant/worker document uploads (application_documents; FKs applications; document_type / document_kind / scan_status / uploaded_by_source CHECK sets; per-app + per-person + pending-review indexes)

  • 20260722000001_document_rejected_by.sql — #1009: application_documents.rejected_by UUID (the rejecting reviewer, from the verified actor claim)

  • 20261106000000_document_scan_quarantine.sql — ADR-042 (#1006): the quarantine lifecycle — provenance/worker/override columns, the seven state-machine CHECKs, 'pending' default, the staggered all-legacy backfill (every pre-quarantine verdict was unprovable; acceptances cleared), the scan-due partial index

  • 20260714000000_create_finalize_operations.sql — epic &71 MR4 (ADR-038) durable finalize-saga record (finalize_operations; reserved-id, state machine + fencing lease + pinned inputs CHECKs; stuck + unreleased partial indexes)

  • 20260714000001_create_finalize_steps.sql — epic &71 MR4 (ADR-038) local receipt skip-cache (finalize_steps; PK (application_id, generation, step_key); FK finalize_operations CASCADE)

  • 20260905000000_create_scheduler_runs.sql — the generated #1211 window-fence table (single-sourced in crates/canopy-db/scheduler-migrations/); documented once in the data-models index

All migrations are forward-only per ADR-016.

Edit this page · default