canopy-persons Data Model

On this page

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

Tables

Table Purpose

persons

Individual master record. One row per natural person known to the system. Carries demographics (name, gender, race[], ethnicity), citizenship_status, language_preference (defaults 'en'), the disability_status flag added in the 20260406 migration (NULL / 'none' / 'disabled' / 'disabled_veteran' — nullable because historical records have no disability data and applicants may decline to report), plus the crypto-shred-sealed SSN + date of birth. Since T2-6 MR8 (#687, ADR-036) ssn_encrypted (JSONB, migrated off the former single-key BYTEA) and date_of_birth_sealed (JSONB, replacing the former plaintext date_of_birth DATE) each hold a SealedValue envelope under their own per-person DEK (subject_kind ssn / date_of_birth, subject_id = person_id) wrapped in redaction_keys — so SSN can be crypto-shredded (POST …/redact-ssn) independently of DOB. Plaintext PII never lands in the DB. Soft-delete via active = false.

households

Household identity row. Effective-dated (effective_date / end_date) so household composition can be reconstructed at any historical point. The name column is an optional caseworker label, not a legal identifier.

household_members

DROPPED in T2-1 CONTRACT (#890) — superseded by the valid-time household_member_versions corpus (see "Fact version tables" below), which is now the sole membership store. The legacy junction table + the one-time backfill_household_member_versions_v1() function were removed by 20260625000000_drop_legacy_address_household_member_tables.sql (the CONTRACT half of the T2-1 A2 EXPAND). It held an effective-dated relationship row (head / spouse / child / etc.); its unique partial index on (household_id, person_id) WHERE active = true — which prevented seating the same person twice in one household while leaving prior closed memberships intact for audit — survives in valid-time form as the (household_id, person_id) non-overlap EXCLUDE on the version corpus.

addresses

DROPPED in T2-1 CONTRACT (#890) — superseded by the valid-time address_versions corpus (see "Fact version tables" below), which is now the sole address store. The legacy per-person table + the one-time backfill_address_versions_v1() function were removed by 20260625000000_drop_legacy_address_household_member_tables.sql (the CONTRACT half of the T2-1 A1 EXPAND). It held address_type (mailing / residential / etc.), full street fields, and county_fips for jurisdictional routing — that role is now the version corpus’s, with full author/source/claim_status provenance.

income / assets / expenses

DROPPED in T1-4 Slice 3 (#672) — superseded by the valid-time {income,asset,expense}_versions corpus (below), which is now the sole fact store. These legacy line-item tables, their write endpoints, and the one-time backfill were removed by 20260618000000_drop_legacy_fact_tables.sql. (They held one row per (person, fact) with verified/verification_source provenance + non-negative CHECKs; that role is now the version corpus’s, with full author/source/claim_status provenance.)

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.

redaction_keys (T2-6 #687, ADR-036)

Per-service crypto-shred key store (migration 20260625120000, identical to the program-service stores). Holds the KEK-wrapped per-value DEKs for the sealed PII (the fact-version value columns + the persons-table ssn / date_of_birth). Columns (dek_id PK, wrapped_dek BYTEA, kek_version, subject_kind, subject_id, created_at, shredded_at); index on (subject_kind, subject_id). Sealing INSERTs a live row; redaction is a one-way tombstone (overwrite wrapped_dek with a 32-byte zero sentinel + stamp shredded_at) enforced by the canopy_redaction_keys_one_way_guard trigger — INSERT and the single live→tombstoned UPDATE are permitted; any other UPDATE, DELETE/TRUNCATE, and un-tombstoning are rejected. A tombstoned DEK makes its sealed value permanently unrecoverable (open() → the read surfaces redacted: true).

finalize_operation_generations (ADR-038, epic &71 / #1047)

Generation gate for the applicant-finalize saga. One row per (operation_id, generation) — the reserved application id + its attempt generation (bumped on each aborted re-submit). Columns (operation_id, generation, state, created_at), PK (operation_id, generation), state CHECK IN ('active','cancelled'). Every finalize-tagged persons write (create/claim carrying the X-Canopy-Finalize-* headers) checks this row FOR SHARE in its own tx before writing: an absent or cancelled generation refuses the write (409), closing the "a stale write from a compensated attempt lands after compensation" race. Rows are created by the persons register endpoint (MR2, #1048) and marked cancelled by cancel.

finalize_receipts (ADR-038, epic &71 / #1047)

Transactional idempotency receipt for the applicant-finalize saga — the exactly-once mechanism at the persons layer (NOT the generic idempotency middleware, which is at-least-once-on-crash and caches plaintext PII). One row per finalize step, written in the SAME tx as the entity it creates + the (held) outbox event it stages. Columns (operation_id, generation, step_key, entity_kind, stable_id, created_at), PK (operation_id, generation, step_key), FK (operation_id, generation) → finalize_operation_generations. stable_id is the correction-surviving id (person_id / household_id / fact_id, never a version_id); a replay of the same step conflicts on the PK and the handler returns the STORED stable_id (the original entity) instead of creating a duplicate.

Relationships

Diagram

Cross-service FKs (ADR-001 boundary)

canopy-persons is at the bottom of the ADR-001 dependency graph: every program service holds application-level FKs into canopy-persons (household_id, person_id), but canopy-persons holds no FKs out. The DB-level REFERENCES constraints in this schema are all intra-database: each of {income,asset,expense,address}_versions.person_id references persons, and household_member_versions references both persons (person_id) and households (household_id). There are no cross-service Postgres-level FKs. Caller services pass person_id / household_id over HTTP and trust canopy-persons to resolve them.

Retention

canopy-persons holds PII (names, DOBs, encrypted SSNs, addresses) but no FTI and no PHI claims data. No specific federal retention ceiling applies — retention is operator-driven and indefinite by default. The ssn_encrypted column uses canopy-common::crypto with the rotation discipline described in ADR-017 (encrypted-secrets-at-rest); rotation re-encrypts ciphertext without ever materialising plaintext outside the per-request decryption boundary. Soft-deletion (active = false) preserves rows for audit and for late-arriving cross-program references; hard deletion is operator-driven and rare.

Indexes

  • idx_persons_name — (last_name, first_name) btree; serves prefix/sort, but NOT the palette’s infix ILIKE '%q%' search (a btree cannot)

  • idx_persons_first_name_trgm / idx_persons_last_name_trgm (GIN gin_trgm_ops, partial WHERE active = true, #1209 scale audit H4) — make the infix ILIKE name search index-driven instead of a full-table scan per command-palette keystroke (requires the pg_trgm extension; engages fully at ≥3 characters)

  • idx_persons_active (partial, WHERE active = true) — default listing

  • idx_{income,asset,expense}_versions_person_current (partial, WHERE superseded_at IS NULL) — the as-of read hot path on the version corpus (the legacy idx_{income,assets,expenses}_person indexes were dropped with their tables in T1-4 Slice 3)

  • idx_address_versions_fact (on (fact_id)) + idx_address_versions_person_current (partial, WHERE superseded_at IS NULL, on (person_id)) — the read hot paths on address_versions (T2-1 A1; the legacy idx_addresses_person index was dropped with the addresses table in the T2-1 CONTRACT, #890)

  • idx_household_member_versions_fact (on (fact_id)), idx_household_member_versions_person_current (partial, WHERE superseded_at IS NULL, on (person_id) — the as-of-aware person→household JOIN), and idx_household_member_versions_household_current (partial, WHERE superseded_at IS NULL, on (household_id) — the as-of household read) — the read hot paths on household_member_versions (T2-1 A2; the legacy idx_household_members_* + idx_unique_household_member indexes were dropped with the household_members table in the T2-1 CONTRACT, #890)

  • 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, on (enqueued_at)) — replay / janitor hot path

Migration files

  • 20260326000000_create_persons_tables.sql — original 7-table schema (persons, households, household_members, addresses, income, assets, expenses) with per-FK indexes

  • 20260402000000_add_constraints.sql — non-negative CHECK constraints on income/assets/expenses amounts; unique partial index preventing duplicate active household members

  • 20260406000000_add_disability_status.sql — adds nullable disability_status TEXT column to persons

  • 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)

  • 20260604000000_create_fact_version_tables.sql — the {income,asset,expense}_versions valid-time tables + btree_gist non-overlap EXCLUDE + one-time backfill (T1-3 / #671 — the expand-contract EXPAND step)

  • 20260618000000_drop_legacy_fact_tables.sql — drops the legacy income/assets/expenses tables + the backfill_fact_versions_v1() function (T1-4 Slice 3 / #672 — the CONTRACT step, once the version corpus is the sole read+write store; function-before-table for the SQL dependency)

  • 20260623000000_create_address_versions.sql — the address_versions valid-time table + btree_gist non-overlap EXCLUDE + the idempotent backfill_address_versions_v1() (T2-1 A1 / #683 — EXPAND; the legacy addresses table kept frozen, dropped in the CONTRACT step below)

  • 20260624000000_create_household_member_versions.sql — the household_member_versions valid-time table + btree_gist non-overlap EXCLUDE over (household_id, person_id, daterange) + the idempotent backfill_household_member_versions_v1() (T2-1 A2 / #683 — EXPAND; the legacy household_members table kept frozen, dropped in the CONTRACT step below)

  • 20260625000000_drop_legacy_address_household_member_tables.sql — drops the legacy addresses + household_members tables + the backfill_address_versions_v1() / backfill_household_member_versions_v1() functions (T2-1 CONTRACT / #890 — the contract half of the A1/A2 EXPAND, once the version corpus is the sole store; function-before-table for the SQL dependency)

  • 20260714000000_create_finalize_operation_generations.sql — ADR-038 finalize generation gate (finalize_operation_generations) — the FOR SHARE late-write-after-compensation guard (epic &71 / #1047)

  • 20260714000001_create_finalize_receipts.sql — ADR-038 finalize transactional receipt (finalize_receipts, FK → finalize_operation_generations) — the exactly-once mechanism at the persons layer (epic &71 / #1047)

  • 20260819000000_persons_name_trgm_gin.sql — #1209 (scale audit H4): the pg_trgm extension + partial GIN trigram indexes on first_name / last_name (WHERE active = true), making the infix name search index-driven; transactional (NOT CONCURRENTLY, same #1196 rationale), forward-only, IF NOT EXISTS

All migrations are forward-only per ADR-016.

Fact version tables (ADR-027 / epic &56)

income_versions / asset_versions / expense_versions (migration 20260604000000, T1-3 / #671) hold the valid-time, append-only, attributed version history of the time-varying facts. Each row carries version_id (PK), fact_id (the stable cross-service handle — ADR-025; equals the legacy row id for backfilled facts), person_id, the half-open valid-time window [valid_from, valid_to), the transaction-time recorded_at + superseded_at (a correction marks the old record-version superseded; valid-time is immutable history), the attribution columns (author_type/author_id/claim_source/claim_status/origin/proposed_value JSONB), and the per-fact value columns. Since T2-6 MR8 (#687, ADR-036) the PII value columns are crypto-shred-sealed — income amount/employer_name, asset value/description, expense amount, address line_1/line_2 are now JSONB holding a SealedValue envelope under one per-fact DEK (subject_kind <fact>_version, subject_id = fact_id, wrapped in redaction_keys); the former CHECK (amount/value >= 0) constraints were dropped (the invariant moved to the application layer pre-seal — they are meaningless on ciphertext), and the structural discriminators (income_type/asset_type/expense_type, frequency, address_type/city/state/zip/county_fips) stay plaintext. Because the per-fact DEK keys on fact_id, redacting a fact (POST …/facts/{kind}/{fact_id}/redact) shreds every version’s value in one tombstone, and a correction remnant re-tile copies the sealed envelope verbatim. A non-deferrable EXCLUDE USING gist (fact_id WITH =, daterange(valid_from, valid_to, '[)') WITH &&) WHERE (superseded_at IS NULL AND claim_status LIKE 'accepted%') enforces that no two current accepted versions of a fact overlap (it keys only on fact_id + daterange, so sealing the value columns leaves it untouched).

Write path (T1-4 Slice 2 / #672): the POST /v1/persons/{id}/{income,assets,expenses}/claims endpoints append via the correction algorithm (lock → supersede-overlapping → re-tile-remnants → insert), filtered by person_id AND fact_id (ADR-027 ownership); income remove is a CLOSE (DELETE …/income/claims/{fact_id} → supersede + left-remnant-only).

Read path (T1-4 Slice 3 / #672): this corpus is the sole fact store. Every fact read (per-person GET …/{income,assets,expenses}, GET /v1/households/{id}/full?as_of, :batchGet) resolves the current-accepted version valid on as_of per fact, claim_status-filtered to determination-feeding (Proposed/Rejected excluded) and mapped to the DTO with id = fact_id + provenance. The legacy income/assets/expenses tables + the backfill_fact_versions_v1() function are dropped (20260618000000, the expand-contract CONTRACT step); the seed writes this corpus directly.

Addresses (T2-1 A1 / #683): address_versions (migration 20260623000000) brings the same valid-time shape to addresses — value columns address_type/line_1/line_2/city/state/zip/county_fips, no numeric CHECK, the same non-overlap GiST EXCLUDE + fact/person_current indexes + backfill_address_versions_v1(). The write surface flips from the flat POST …/addresses to POST/DELETE /v1/persons/{id}/addresses/claims (claim + close); GET …/addresses is an as-of-today read, while the bulk export reads all current windows (the FOIA projection dedups identical coarse rows; portability emits one row per live window). Attributed address.claimed/address.closed events carry a coarse, street-redacted value (address_type/city/state/zip/county_fips — never line_1/line_2; the full street persists only in address_versions, per ADR-004 / ADR-027 §8). Like income/assets/expenses before it, the legacy addresses table was kept frozen in A1 (no Rust read it; the backfill_address_versions_v1() function stayed so the backfill transformation was testable) and then dropped in the T2-1 CONTRACT step (20260625000000, #890), along with the backfill function. The deterministic read order is valid_from DESC, recorded_at DESC, fact_id (canopy-web takes the first row, so a stable order matters). Since E1a (#892) address_type is a native PostgreSQL enum (residential / mailing; migration 20260630120000 converts the former free-text TEXT column) — the sqlx-free wire AddressType contract is the source of truth and the store keeps an sqlx::Type mirror — so the case-detail "primary address" pick prefers the first residential over an order-arbitrary one rather than reasoning over ad-hoc strings.

Household membership (T2-1 A2 / #683): household_member_versions (migration 20260624000000) brings the same valid-time shape to household membership — version_id (PK), fact_id (the stable cross-service handle = the legacy household_members.id), person_id (FK persons), household_id (FK households), the half-open [valid_from, valid_to) window, transaction-time recorded_at + superseded_at, the attribution columns (author_type/author_id/claim_source/claim_status/origin/proposed_value JSONB), and the value column relationship. Membership is household-scoped (the ownership + correction key is household_id, not person_id), so — unlike income/assets/expenses/addresses, whose non-overlap is per-fact_id — the non-overlap GiST EXCLUDE USING gist (household_id WITH =, person_id WITH =, daterange(valid_from, valid_to, '[)') WITH &&) WHERE (superseded_at IS NULL AND claim_status LIKE 'accepted%') is per-(household_id, person_id): it is the valid-time form of the legacy UNIQUE (household_id, person_id) WHERE active = true, preserving the "no duplicate active membership per household" invariant (a person may be in two different households at once, never the same one twice; btree_gist services both UUID equality columns). The household_member_versions_valid_range CHECK (valid_to IS NULL OR valid_to > valid_from) guards the window; there is no numeric CHECK (relationship is not numeric). Three indexes back the read paths: (fact_id), partial (person_id) WHERE superseded_at IS NULL (the as-of-aware person→household JOIN), and partial (household_id) WHERE superseded_at IS NULL (the as-of household read); the deterministic read order is fact_id (UUID v7 = stable creation order that survives corrections). The write surface flips from the flat POST /v1/households/{id}/members (AddMember) + DELETE …/members/{member_id} to the authored POST/DELETE /v1/households/{id}/members/claims[/{fact_id}] (claim + close); GET /v1/households/{id} and /full are now as-of valid-time member reads, and because membership is denormalised onto every person read as Person.household_id, the person→household JOIN is now as-of-aware (it reads the version corpus valid on the read’s as_of, so Person.household_id stays consistent with the as-of household reads). The attributed household.member_claimed/household.member_closed events carry the full MemberFactValue (relationship is not PII, so — unlike the street-redacted address events — no coarse projection is applied), plus household_id and the before/after windows. The idempotent backfill_household_member_versions_v1() backfills each legacy household_members row as a system-authored v1 (active → open window; a removed/active=false row → a window closed at end_date or, absent that, the row’s updated_at). Like income/assets/expenses before it, the legacy household_members table was kept frozen in A2 (no Rust read it; the backfill function stayed so the backfill transformation was testable) and then dropped in the T2-1 CONTRACT step (20260625000000, #890), along with the backfill function (mirroring A1’s).

Edit this page · default