canopy-verification Data Model

On this page

Tables

Table Purpose

verifications (#519)

Pending verification work items. One row per verification task, written by the eligibility orchestrator (canopy-eligibility) after a program determination yields verification_items_required. Carries the cross-service application_id / household_id / person_id (application-level FKs into canopy-applications and canopy-persons), the assigned worker_id, the verification_type (e.g. income, identity, residency), the due_date, and the completion lifecycle (completed_at, completed_by, notes). status is constrained by an inline CHECK to the closed set pending / in_progress / completed / cancelled. OPEN items dedup on the natural key: the partial unique index verifications_open_natural_key_uq on (application_id, household_id, verification_type) WHERE status IN ('pending','in_progress') AND application_id IS NOT NULL (#1480, ADR-002 A1 D6) — the producer create converges on the existing open row (ON CONFLICT + read-back). Read by the worker dashboard’s "Pending verifications" panel via GET /v1/verifications?status=pending&worker_id={user_id}. No soft-delete column — terminal states are completed / cancelled.

ievs_hits (#522)

IEVS adapter callback results, persisted on each match. One row per IEVS hit. Carries the cross-service application_id / household_id / person_id, the denormalized member_name, the source (the IEVS data source that produced the match), the hit_type, the hit_at timestamp, the raw adapter payload JSONB, and the review lifecycle (reviewed_at, reviewed_by, notes). status is constrained by an inline CHECK to the closed set unreviewed / reviewed / resolved / false_positive. Read by the worker dashboard’s "IEVS alerts / discrepancies" panel via GET /v1/verifications/ievs/discrepancies?limit={n}.

verification_responses (Plan 3 MR10b)

Applicant/worker responses to verification work items. Written by POST /v1/verifications/{id}/respond — one row per attached document plus an optional free-text-only row, all scoped to the verification’s authoritative application_id (the handler rejects a mismatched caller application_id with 403). Carries the verification_id (the only DB-level FK in this schema — REFERENCES verifications(id)), the cross-service document_id (→ canopy_applications.application_documents.id, nullable for a text-only row), application_id, person_id, the response_text, responded_at, and responded_by_source (inline CHECK to applicant_portal / worker_intake). Read by the worker case-detail Verifications section via GET /v1/verifications/{id}/responses.

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.
NOTE
canopy-verification carries no event_inbox table — it does not run a consumer-side Subscriber against the broker, so the #433 / ADR-018-amendment inbox shape present in consuming services (e.g. canopy-applications) is absent here. Verification persistence is written by orchestrator-driven HTTP calls and adapter callbacks, not by RabbitMQ redelivery.

Relationships

The two domain tables are siblings — both pivot on the same cross-service household_id / person_id keys but hold no Postgres-level FK between each other or to other services (ADR-001). event_outbox is infrastructure with no relationship to the domain tables.

Diagram

Cross-service FKs (ADR-001 boundary)

Per ADR-001, canopy-verification holds no Postgres-level foreign keys to other services. The only DB-level FK in this schema is intra-database: verification_responses.verification_id REFERENCES verifications(id) (a response cannot dangle off a non-existent work item). verifications, ievs_hits, and event_outbox carry no intra-database parent/child references. Every cross-service UUID column shown as FK → … in the ERD (application_id → canopy-applications; document_id → canopy-applications application_documents; household_id / person_id → canopy-persons; worker_id / completed_by / reviewed_by → Keycloak subjects) is an application-level foreign key only. canopy-verification trusts the orchestrator (canopy-eligibility) and the worker BFF to supply real IDs, but does not enforce existence — workers are Keycloak subjects, not canopy-persons rows.

Retention

canopy-verification’s domain tables (verifications, ievs_hits) record verification work items and IEVS match metadata, not FTI itself — the FTI-bearing IEVS audit trail lives in the legally-scoped program services (canopy-tanf / canopy-medicaid fti_audit_log per ADR-004 / ADR-014), and SSA SOLQ/BINDEX data is isolated to those services. canopy-verification holds no FTI or PHI at rest, so no Pub 1075 §9 / HIPAA at-rest retention floor applies to this database. IEVS program-record retention (7 USC §2025(e), 7 CFR 272.1(f)) implies a 3-year case-record floor for the underlying SNAP record; the longest applicable program floor governs in multi-program deployments. Retention is operator-driven (archive moves, not migration-driven destructive changes — ADR-016 forward-only).

Indexes

  • idx_verifications_worker_pending (partial, WHERE status = 'pending') — worker dashboard "Pending verifications" panel, ordered requested_at DESC

  • idx_verifications_pending_unassigned (partial, WHERE status = 'pending' AND worker_id IS NULL) — supervisor view of unassigned pending work, ordered requested_at DESC

  • idx_verifications_household — household-scoped lookup of verification items

  • idx_ievs_hits_recent — recent-hits feed, ordered hit_at DESC

  • idx_ievs_hits_unreviewed (partial, WHERE status = 'unreviewed') — worker dashboard "IEVS alerts" panel hot path, ordered hit_at DESC

  • idx_ievs_hits_household — household-scoped lookup of IEVS hits

  • verification_responses_verification_idx ((verification_id, responded_at DESC)) — the per-verification response read-back (worker case-detail Verifications section)

  • verification_responses_application_idx — application-scoped lookup of responses

  • 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

  • 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

  • 20260526001500_create_verification_tables.sql — #519 + #522 first domain DB: verifications (pending work items) + ievs_hits (IEVS callback results) with their supporting partial indexes

  • 20260601000000_create_verification_responses.sql — Plan 3 MR10b: verification_responses (applicant/worker responses, one row per attached document + optional text row) with its verification_id-scoped + application_id-scoped indexes

All migrations are forward-only per ADR-016.

Edit this page · default