canopy-wic Data Model

On this page

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

Tables

Table Purpose

wic_participants

Per-person WIC enrollment row. One row per (person, certification period). participant_category CHECK ∈ {pregnant, postpartum, breastfeeding, infant, child} (the five WIC categories per 7 CFR 246.7(c)). certification_start / certification_end are the certification-period bounds per 7 CFR 246.7(g) (1 year for breastfeeding women / children; 6 months for postpartum women; through the last day of the month in which the infant turns 1 for infants; through the end of pregnancy + 6 weeks for pregnant women). food_package CHECK ∈ {I, II, III, IV, V, VI, VII} per 7 CFR 246.10(e) — the assignable families since #770 are I/II/IV/V/VI/VII (III needs a qualifying-condition input canopy lacks; catalogued gap). status CHECK ∈ {active, expired, terminated, transferred} (transferred-out handled via VOC under 7 CFR 246.7(l)).

wic_determinations

Signed eligibility determinations per applicant. Carries the four WIC gates (categorical_eligible, income_eligible, adjunctive_eligible, nutritional_risk_documented), the adjunctive program name when adjunctive-eligible (adjunctive_program — SNAP / Medicaid / TANF per 7 CFR 246.7(d)(2)(vi)), the resolved participant_category and food_package on approval, denial_reasons TEXT[] on denial, ruleset_version for audit replay, the ADR-002 jws_token, 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 participant determination (PK = FK determination_id → wic_determinations(id)): the typed DeterminationSnapshot as a canonical snapshot JSONB blob (the participant’s categorical+income rules_input + ruleset output, the gate results, assigned food package, resolved policy params, ruleset corpus-hash, and the participant set 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 wic_determinations.snapshot_hash. WIC is non-FTI, so the snapshot does not join the ADR-014 chain. T2-2 (#679): each per-participant blob also carries the self-explaining derivation_graph (the categorical + income firings + the Rust-side adjunctive gate, food-package assignment, and certification-end-date) — 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.

wic_nutritional_risk_assessments

Required nutritional risk assessment per 7 CFR 246.7(e). One row per assessment with the four 800-series risk-category booleans: anthropometric_risk (100-series codes), biochemical_risk (200-series), dietary_risk (300-series), medical_risk (400-series and others). risk_codes TEXT[] holds the specific FNS risk codes identified. assessor_worker_id ties the assessment to a Competent Professional Authority (CPA).

wic_appointments (#448)

Certification appointment scheduling. One row per scheduled appointment. Carries household_id, optional certification_id (links to wic_participants when the appointment is for an existing certification rather than a fresh applicant), appointment_at, appointment_type, optional notes, scheduled_by (worker login), and status (defaults 'scheduled'). Write target for the worker-portal action actions_wic::schedule_certification_appointment_wic (#392).

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-wic holds no Postgres-level foreign keys to other services. The cross-service identifiers it stores are:

  • wic_participants.person_id → canopy-persons (application-level)

  • wic_determinations.application_id → canopy-applications, household_id + person_id → canopy-persons (application-level)

  • wic_nutritional_risk_assessments.person_id → canopy-persons, assessor_worker_id → identity-provider claims (application-level)

  • wic_appointments.household_id → canopy-persons; certification_id is application-level and intentionally nullable (a fresh applicant may not yet have a wic_participants row)

The one intra-database FK is determination_snapshots.determination_id → wic_determinations(id). Cross-service consistency is the orchestrator’s responsibility, not the program DB’s.

Retention

7 CFR 246.25 governs WIC record retention: at least 3 years from the end of the federal fiscal year in which the records were created (longer if any audit, claim, investigation, or other action involving the records is open). canopy-wic does not handle FTI; no Pub 1075 retention floor applies. Operationally, wic_determinations and wic_participants are retained indefinitely in the production system to support transfer-of-certification (VOC) lookups, with archive moves operator-driven.

Indexes

  • idx_wic_participants_person — per-person enrollment lookup (VOC, recertification)

  • idx_wic_participants_status — active-roster listing

  • idx_wic_participants_category — per-category reporting

  • idx_wic_participants_cert_end — expiring-certification scan for the renewal scheduler

  • idx_wic_determinations_application — per-application lookup

  • idx_wic_determinations_household — per-household lookup

  • idx_wic_determinations_person — per-person lookup

  • idx_wic_determinations_status — list endpoint filter

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

  • idx_wic_assessments_person — per-person assessment history

  • idx_wic_assessments_date — chronological assessment listing

  • idx_wic_appointments_household — per-household upcoming-appointment listing

  • idx_wic_appointments_upcoming (partial, WHERE status = 'scheduled') — upcoming-appointments dashboard query

  • 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_wic_tables.sql — original schema (wic_participants, wic_determinations, wic_nutritional_risk_assessments) with per-FK indexes

  • 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

  • 20260512000000_create_wic_appointments.sql — #448 certification appointment table for the worker-portal schedule_certification_appointment_wic action (#392); per-household and upcoming-only partial indexes

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

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