canopy-medicaid Data Model

On this page

Tables

Table Purpose

medicaid_applications

Medicaid/CHIP application intake records. One row per application from canopy-eligibility. pathway ∈ {magi, non_magi, both, chip} discriminates the evaluation track. status lifecycle starts at pending.

magi_income (PAMMS 2669)

MAGI income records for budget-group members. Carries gross + monthly-normalized amounts, is_countable / is_earned flags, the 1040-deduction set (magi_adjustment_type + magi_adjustment_amount), before-tax deductions (before_tax_deduction_type + amount), self_employment_expenses, and the child-dependent exclusion gate (is_child_dependent_excluded + child_dependent_exemption_amount) per PAMMS 2610. source ∈ {self_report, fti, fdsh, employer, collateral}. FK to medicaid_applications(id).

non_magi_factors

Non-MAGI eligibility factors for ABD COAs (FBR and non-FBR). category carries 19 COA codes spanning SSI / Pickle / DAC / Disabled Widow / Widow 60-64 / Former SSI Disabled Child / EDWP / NOW / COMP / TEFRA / Hospice / Hospital / ICWP / Nursing Home / AMN / QDWI / QMB / SLMB / QI-1. Carries age + verification, disability_status + disability_determination_date, Medicare enrollment fields, resource-test and income-test pairs (countable + limit), federal_benefit_rate for FBR COAs, medical_spend_down_amount for medically-needy / AMN, and level_of_care_met / length_of_stay_met for institutional COAs. FK to medicaid_applications(id).

fti_tax_data (IRS Pub 1075 / IRC §6103(l)(12))

FTI from IRS for Medicaid/CHIP. All access MUST be wrapped with FTI audit logging. Carries tax_year, filing_status (5 IRS categories), adjusted_gross_income, wages_salaries_tips, self_employment_income, social_security_benefits + taxable_social_security (portion included in AGI), tax_exempt_interest, foreign_earned_income — the four components of MAGI per ACA §1413. FK to medicaid_applications(id).

fdsh_results (ADR-004)

Federal Data Services Hub verification results. hub_service ∈ {verify_ssa, verify_dhs, verify_irs, verify_cms}; verification_type ∈ {income, citizenship, incarceration, ssn, mec, immigration_status, quarterly_wage}; result_status ∈ {verified, not_verified, inconsistency, unavailable}; result_data JSONB carries the structured hub response. Per ADR-004, raw FDSH stays transiently in canopy-verification; the eligibility-relevant outcome lands here.

clinical_assessments (HIPAA minimum-necessary)

HIPAA-scoped clinical data. assessment_type ∈ {disability, medical_necessity, functional_limitation, blindness, nursing_facility_level_of_care}; result ∈ {meets_criteria, does_not_meet, pending_review}. The migration explicitly forbids clinical details, diagnosis descriptions, or treatment plans — only the eligibility-relevant outcome is stored. FK to medicaid_applications(id).

medicaid_eligible_categories (PAMMS 2052)

Per-individual evaluation outcomes against each Medicaid COA. Multiple COAs may apply; the CMD cascade evaluates all of them; the EE15 hierarchy picks the most advantageous. Carries coa_code, coa_track ∈ {magi, non_magi, chip}, eligible boolean, fpl_percentage + fpl_threshold, income_amount, optional resource_amount (non-MAGI only), optional spend_down_amount (medically-needy), denial_reason, and evaluation_order for cascade position. FK to medicaid_applications(id).

chip_applications (PAMMS 2194 / CHIPRA)

CHIP-specific child enrollment records (children above Medicaid limit, below CHIP limit). chip_type ∈ {standalone, medicaid_expansion}; premium_tier carries the FPL band; monthly_premium + family_cap_premium carry the schedule values; premium_exempt + premium_exemption_reason ∈ {under_6, foster_care, ai_an}. enrollment_effective_date is first day of month after application complete + premium paid. FK to medicaid_applications(id).

peachcare_premium_schedule (PAMMS 2194)

PeachCare premium schedule rows. Loaded from jurisdiction.toml, persisted for audit trail. Each row is an FPL band (fpl_lower_bound / fpl_upper_bound) with one_child_premium + family_cap_premium, bounded by effective_date / end_date.

pathways_qualifying_activities (PAMMS 2195)

Pathways-to-Coverage qualifying-activity log. Must demonstrate 80 hours/month. activity_type ∈ 11 categories (unsubsidized_employment, subsidized_private, subsidized_public, on_the_job_training, job_readiness, community_service, vocational_training, higher_education, gvra_vocational_rehab, snap_abawd_compliance, parent_child_under_6). verification_status starts at self_attested. FK to medicaid_applications(id).

pathways_hipp_referrals (PAMMS 2195)

Pathways HIPP (Health Insurance Premium Payment) cost-effectiveness determinations. has_esi_access flags ESI availability; cost_effective is nullable while pending; esi_monthly_premium vs medicaid_capitation_rate drives the comparison; status ∈ {pending, enrolled, not_cost_effective}. FK to medicaid_applications(id).

medicaid_determinations (ADR-002)

Signed Medicaid/CHIP determinations. status ∈ {approved, denied, pending_spenddown, pending_premium}; assigned_coa is the EE15 most-advantageous COA; assigned_coa_track ∈ {magi, non_magi, chip}; benefit_type ∈ {full_medicaid, chip_standalone, qmb_supplement, family_planning_only, emergency_only}. Carries continuous_eligibility_end for 12-month CE periods (children, postpartum), denial_reason_codes text array, fmap_rate (federal matching rate for the assigned COA), the ADR-002 JWS signature, and the snapshot_hash (hex SHA-256 of the ADR-028 input snapshot, bound into the signature; one row per member determination per ADR-035, so each member’s snapshot binds to its own determination; NOT NULL since #911 — the pre-snapshot legacy rows were deleted with the ADR-028 §58 backstop). FK to medicaid_applications(id).

determination_snapshots (ADR-028)

Immutable determination input snapshots (T2-4). One row per determination — i.e. per member (PK = FK determination_id → medicaid_determinations(id), ADR-035): the typed DeterminationSnapshot as a canonical snapshot JSONB blob (the CMD cascade priority order + per-COA magi/non-MAGI/CHIP/TMA results + per-COA denial-reason evals + the EE15 hierarchy result + assigned_coa + countable_resources + member SOLQ flags, the income/expense facts + whole-household composition, resolved policy params, ruleset corpus-hash), plus denormalized corpus_hash + as_of columns and the signing_kid (ADR-028 §53 key retention). When the determination consumed SSA SOLQ data, the blob also carries a typed cross_program_inputs.solq projection — a by-value copy of every SolqRecord the verdict had access to (T2-3 #684, ADR-028 cross-program capture), distinct from the SOLQ-derived ABD booleans in program_input.member_flags (the raw source vs the resolved ruleset input). T2-3 set schema_version: 2 for a SOLQ-bearing snapshot (a snapshot without it stayed 1); with the T2-2 derivation graph (below) every Medicaid snapshot is now schema_version: 3. 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 medicaid_determinations.snapshot_hash. Medicaid is FTI-bearing — each snapshot’s creation also appends an fti_audit_log chain entry (resource_type='determination_snapshot', one per member determination, all within the single all-or-nothing determine transaction; data_elements_accessed records solq when the SOLQ projection was frozen), so the FTI-derived artifact joins the ADR-014 tamper-evident chain (§4) + §9 breach pathway. T2-2 (#679): each per-member blob also carries the self-explaining derivation_graph (the MAGI / non-MAGI / CHIP / hierarchy / TMA firings as derived-output nodes + the Rust ABD chain derive_abd_flags_from_solq, whose inputs reference cross_program_inputs.solq by id — no FTI value copied, ADR-014) — 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, and the cross-program SSA SOLQ projection) 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.

cmd_cascade_log

Audit trail of the CMD (Categorical Medicaid Determination) cascade evaluation path. One row per (person, evaluated COA). evaluation_track ∈ {abd, family}. Records eligible boolean, optional denial_reason, and evaluation_order for replay. FK to medicaid_applications(id).

tanf_tma_coverage (PAMMS 2166 / 42 CFR 435.112)

Transitional Medical Assistance coverage records. Created when canopy-medicaid receives a tanf.case_closed event with an earnings-related closure reason. Carries tanf_termination_date, tma_start_date / tma_end_date, phase (default phase_1), qrf_due_dates JSONB array, status (default active), closure_reason.

ele_consents (42 CFR 435.1102, Plan 2)

Durable applicant consent to Express Lane Eligibility. Written by the canopy-medicaid.ele-consent subscriber on application.ele_consent_recorded. consent_source ∈ {applicant_portal, worker_attestation}. A partial unique index enforces one active (non-revoked) consent per household. The express-lane grant subscriber gates on an active consent before granting.

ele_status (42 CFR 435.1102, Plan 2)

Current durable ELE snapshot per child — the 1-year flag. eligibility_tier ∈ {medicaid, peachcare}; current_status ∈ {active, lapsed, revoked, pending_redetermination}; granting_program_history is a de-duped program[] projection of the source programs that granted/extended (a second program extends, expiry unchanged). Denormalizes child_date_of_birth for the local age-out query (ADR-001). last_event_id / last_event_hash point at the tail of this child’s chain. Partial unique index enforces one active row per child. The #1219 sweep triple — sweep_claimed_at/sweep_claimed_by (bounded FOR UPDATE SKIP LOCKED lease claims so hourly sweep passes on every replica partition the due cohort; a work-sharing hint, never renew/lapse correctness) and sweep_next_attempt_at (the sub-daily retry gate a failed row sets, so it retries the same operational day without regrinding every claim) — replaces the pre-#1219 cluster advisory lock that one hung HTTP call could wedge statewide. The composite ele_status_renewal_due_idx (expires_at, id) WHERE current_status='active' serves the claim’s scan and order directly.

ele_grant_events (42 CFR 435.1102, ADR-014, Plan 2)

Append-only, SHA-256 hash-chained source-of-truth log for every ELE grant/extend/renew/lapse/revoke. previous_hash / event_hash form the tamper-evident chain (distinct advisory lock from FTI); occurred_at is stamped after the lock for monotonicity. Carries source_program + source_determination_id (SNAP) / source_application_id (TANF), eligibility_tier, granted_at / expires_at, reason_code, and the hashed payload JSONB. Verified by GET /v1/ele/chain-status. Supersedes the pre-Plan-2 express_lane_evaluations log (dropped in migration 20260606000000).

ele_deferred_approvals (42 CFR 435.1102, #649)

Deferred source-program approvals for the consent-after-approval race. When a snap/tanf.application_approved reaches the canopy-medicaid.express-lane subscriber before ELE consent is recorded, the approval is persisted here instead of being ack-and-dropped (the grant would otherwise be lost to event ordering); the canopy-medicaid.ele-consent subscriber drains + replays these — running the identical grant evaluation — when consent lands, then sets processed_at. source_program ∈ {snap, tanf} (CHECK); carries source_determination_id (SNAP) / source_application_id (TANF) for replay provenance. A partial unique index over unprocessed rows makes a redelivered/duplicate approval a no-op (idempotent per (household_id, source_program)); marking processed_at frees the slot for a later re-approval. Since #1274 the check-then-act pair (approval leg’s consent-check/defer, consent leg’s insert/drain) serializes on a per-household pg_advisory_xact_lock — under READ COMMITTED alone, a concurrently-committing consent and deferred approval could each miss the other, leaving the row unprocessed forever (a silently lost grant). An hourly heal pass drains any lingering unprocessed-deferred + active-consent households under the same lock (historical pre-lock rows; a post-deploy nonzero count is a fresh-leak tripwire).

medicaid_cmd_events (#448, lifecycle head since #1506)

Change in Circumstances (CMD) lifecycle rows: state received → requested → completed | failed (pre-#1506 rows legacy). Ingest (POST /v1/cmd/ingest) inserts the row, stamps deadline_at = ingest + [medicaid].cmd_clock_days (the PAMMS 2750 clock — jurisdiction data since #1511), and stages the determination.requested::Order in one tx; the cmd-settle consumer completes rows off the origin-echoing determination.completed, and the cmd-fail consumer (#1511) marks terminal order failures failed + failure_code off determination.order_failed — those rows surface on GET /v1/cmd/escalations and count on the (now live) canopy_medicaid_cmd_terminal_failed gauge. processed_at set on completion only; the partial idx_medicaid_cmd_events_unresolved index backs both the clock gauges and the escalation feed.

fti_audit_log (IRS Pub 1075 §4, ADR-014)

FTI access audit log. Schema identical to canopy-tanf’s. ADR-014 added previous_hash + event_hash SHA-256 columns forming an append-only tamper-evident chain.

fti_audit_log_archive

Retention archive table (LIKE fti_audit_log INCLUDING ALL). Pub 1075 AU-11 7-year retention floor (ADR-004 Amendment 2). ADR-014 chain extends across the archive boundary.

overpayment_claims (42 CFR 433.300)

Per-program overpayment claims. Byte-identical schema across all five program services. claim_basis ∈ {agency_error, inadvertent_household_error, ipv}; status ∈ {open, in_repayment, closed, written_off, void}; since #1104: pipeline provenance (appeal_id, adverse_action_id, assessment_id, source_event_id — partial UNIQUEs on the last two make event redelivery a no-op) + the void path (voided_at, void_reason).

repayment_plans

One or more repayment plans per claim. status ∈ {active, suspended, completed, defaulted}. FK to overpayment_claims(id).

recoupment_ledger

Append-only ledger of recoupment events. method ∈ {allotment_reduction, cash_payment, tax_offset, write_off, manual_adjustment}. Outstanding = claim_amount_cents + SUM(claim_adjustments.delta_cents) - SUM(amount_cents); status recompute in Rust (closed at zero; upward adjustments reopen; void/written_off sticky).

claim_adjustments (#1104)

Append-only principal corrections (signed delta_cents, reason ∈ {reallocation, void, correction, manual}, requires_ops_review on over-recovery). FK to overpayment_claims(id). Corrections never rewrite claim_amount_cents.

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-medicaid holds no Postgres-level foreign keys to other services. DB-level FKs are intra-database only: every medicaid_application_id column references medicaid_applications(id); determination_snapshots.determination_id → medicaid_determinations(id); repayment_plans.overpayment_claim_id → overpayment_claims(id); recoupment_ledger.overpayment_claim_id → overpayment_claims(id) + repayment_plan_id → repayment_plans(id). Every other UUID column referencing a foreign service — household_id / person_id / applicant_person_id / tax_filer_person_id / spouse_person_id / child_person_id and the dependents_person_ids / pregnant_member_ids / ssi_recipient_ids UUID arrays (canopy-persons), application_id on medicaid_applications (canopy-applications), overpayment_claims.determination_id (this service’s medicaid_determinations, unconstrained for back-rebill flows) — is application-level only. Note also tanf_tma_coverage, ele_consents, ele_status, ele_grant_events, and ele_deferred_approvals deliberately have no FKs back to medicaid_applications: they’re populated by cross-program event subscribers (from canopy-tanf / canopy-snap / canopy-applications) and pre-date any Medicaid application of record.

Retention

canopy-medicaid is the most data-class-dense service in the system. It holds:

  • FTI (IRC §6103(l)(12); IRS Publication 1075) in fti_tax_data. FTI audit access is logged to fti_audit_log per Pub 1075 §4; minimum 7-year retention applies per Pub 1075 AU-11 (ADR-004 Amendment 2), with rows aging into fti_audit_log_archive and the ADR-014 hash chain extending across the boundary.

  • HIPAA-scoped PHI in clinical_assessments. Minimum-necessary standard enforced at the schema level — only the eligibility-relevant outcome is stored; no clinical narrative, diagnosis, or treatment plan. HIPAA 45 CFR 164.530(j) requires 6-year retention of compliance documentation; Medicaid claim records per 42 CFR 431.17 require 3-year minimum from claim closure.

  • FDSH data in fdsh_results. Retained per the CMS data-use-agreement schedule.

The longest applicable floor governs. Archive moves and FTI-expiry purges are operator-driven; ADR-016 forbids destructive migrations.

Indexes

  • idx_medicaid_applications_{application,household,status} — application list endpoints

  • idx_magi_income_{application,person} — MAGI income lookups

  • idx_non_magi_factors_{application,person} — non-MAGI factor lookups

  • idx_fti_tax_data_{application,person} — FTI lookups (audit-wrapped)

  • idx_fdsh_results_{application,person} — FDSH lookups

  • idx_clinical_assessments_application — HIPAA-scoped assessment lookup

  • idx_medicaid_categories_{application,person,coa} — eligible-categories lookup + COA filter

  • idx_chip_applications_{medicaid,child} — CHIP enrollment lookup

  • idx_pathways_activities_application, idx_pathways_hipp_application — Pathways lookups

  • idx_medicaid_determinations_{application,person,status} — determination lookups

  • idx_medicaid_determinations_determined_at_id ((determined_at DESC, id DESC), migration 20260817000000) — the keyset cursor backing the paginated GET /v1/determinations list (#1195); serves the newest-first page (and the T-MSIS extractor’s month-scoped page-loop) as an index scan with no top-N sort

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

  • idx_cmd_cascade_{application,person} — CMD cascade audit replay

  • idx_tma_coverage_{household,person}, idx_tma_coverage_status (partial on active) — TMA listings

  • idx_ele_household — Express Lane Eligibility evaluation history (DESC by evaluated_at)

  • idx_medicaid_cmd_events_household, idx_medicaid_cmd_events_unresolved (partial, WHERE state IN ('received','requested') — the #1506 lifecycle replaced the retired _unprocessed picker index) — CMD event worklist

  • idx_fti_audit_{accessed_at,accessed_by,purpose_code} — FTI audit query shapes

  • idx_fti_audit_created_at — (created_at) for the ADR-014 §9 in-lock predecessor-hash lookup (ORDER BY created_at DESC LIMIT 1, held under the per-originating_system advisory lock on every FTI-bearing determination/ELE commit) and verify_chain’s ascending walk; #1197, migration 20260811000000. Closes the ADR-014 §9 doc/schema drift (the §9 budget asserted a `created_at DESC index that did not exist)

  • idx_fti_audit_event_hash, idx_fti_audit_archive_event_hash — ADR-014 hash-chain verification

  • overpayment_claims_status, repayment_plans_by_claim, recoupment_ledger_by_claim — overpayment lifecycle

  • 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

  • 20260326000001_create_fti_audit_log.sql — Pub 1075 §4 audit log + archive table (LIKE …​ INCLUDING ALL); identical schema to canopy-tanf’s

  • 20260407000000_create_medicaid_tables.sql — original 14-table schema (applications, MAGI household + income, non-MAGI factors, FTI tax data, FDSH results, clinical assessments, eligible categories, CHIP applications, PeachCare premium schedule, Pathways activities + HIPP referrals, determinations, CMD cascade log)

  • 20260413000000_create_tanf_tma_coverage.sql — tanf_tma_coverage (PAMMS 2166 / 42 CFR 435.112) populated by the tanf.case_closed subscriber

  • 20260417000000_create_express_lane_evaluations.sql — express_lane_evaluations (42 CFR 435.1102) evaluation log — dropped in 20260606000000 (Plan 2 MR5; superseded by the durable ele_ tables)*

  • 20260425000000_add_fti_audit_hash_chain.sql — ADR-014 previous_hash + event_hash on fti_audit_log + archive

  • 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

  • 20260510000001_create_overpayments.sql — canonical overpayment schema per 42 CFR 433.300

  • 20260727000000_claim_provenance_adjustments.sql — #1104: claim provenance + partial UNIQUEs, the void path, and claim_adjustments (canonical copy in crates/canopy-overpayments/migrations/; byte-parity crate-tested)

  • 20260512000000_create_medicaid_cmd_events.sql — #448 worker-portal-initiated CMD event ingest

  • 20261126000000_medicaid_determinations_trigger.sql — #1505 ADR-002 D9 provenance columns (trigger w/ kebab CHECK, previous_determination_id, the per-person-latest index, the one-successor-per-prior supersession unique)

  • 20261127000000_cmd_events_lifecycle.sql — #1506 CMD lifecycle (state received→requested→completed|failed|legacy, deadline_at = the PAMMS 2750 10-day clock, application_id, determination_request_id, failure_code; pre-subsystem rows explicitly legacy)

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

  • 20260605000000_create_ele_consents.sql / 20260605000001_create_ele_status.sql / 20260605000002_create_ele_grant_events.sql — Plan 2 durable Express Lane Eligibility: consent + per-child status + hash-chained grant events (42 CFR 435.1102, ADR-014)

  • 20260606000000_drop_express_lane_evaluations.sql — Plan 2 MR5 expand-contract: drop the superseded pre-Plan-2 evaluation log

  • 20260606010000_fti_audit_append_only_guard.sql — statement-level append-only guard on fti_audit_log + archive (GUC canopy.audit_maintenance)

  • 20260624000000_create_determination_snapshots.sql — medicaid_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)

  • 20260630000000_drop_magi_household_snapshots.sql — drops the dead-on-arrival magi_household_snapshots table (created in 20260407000000, never wired to a read/write path); superseded in intent by determination_snapshots (D9 #883, ADR-016 expand→contract)

  • 20260701000000_create_ele_deferred_approvals.sql — ele_deferred_approvals for the ELE consent-after-approval race: express-lane defers an approval that arrives before consent, the ele-consent subscriber replays it (#649)

  • 20260811000000_fti_audit_created_at_idx.sql — idx_fti_audit_created_at for the ADR-014 §9 in-lock predecessor lookup + verify walk (#1197, scale audit H13; closes the §9 doc/schema drift). Transactional (NOT CONCURRENTLY) for the same reason as the #1196 eligibility index migration

  • 20260817000000_medicaid_determinations_list_keyset_idx.sql — idx_medicaid_determinations_determined_at_id (determined_at DESC, id DESC) backing the keyset-paginated GET /v1/determinations list (#1195, scale audit C1). Transactional (NOT CONCURRENTLY), same rationale

  • 20260902000000_overpayment_claims_keyset_idx.sql — #1222 (scale audit M11): overpayment_claims_keyset (created_at DESC, id DESC) serving the roll-up keyset page; transactional CREATE INDEX (not CONCURRENTLY — the sqlx migrator’s advisory lock deadlocks against CONCURRENTLY’s snapshot wait); canonical copy in `crates/canopy-overpayments/migrations/, byte-parity asserted by the crate’s tests

  • 20260910000000_chain_v2_substrate.sql — #1246 MR-2 (ADR-014 Amendment 6): the dormant chain-v2 fti substrate — see the chain-v2 section below

All migrations are forward-only per ADR-016.

chain-v2 substrate (dormant, #1246 / ADR-014 Amendment 6)

Installed by 20260910000000_chain_v2_substrate.sql — the fti-family copy (canopy-medicaid is a chain SOURCE; the anchor store + C6 verification state live only in canopy_security). Dormant until the #1279 cutover.

Table Purpose

chain_instances / chain_topology / chain_epochs / chain_sources / chain_heads

The shared registry substrate (identical DDL to the canopy-security copy): instance history, active pointer, fenced epochs, the source registry chain_append_rows_fti validates its baked canopy-medicaid literal against, and pre-created heads.

fti_audit_log_v2 / fti_audit_log_archive_v2

The FTI family’s strict-from-row-one event store (same constraint set as the audit copy). Hashed business columns are DERIVED from the payload — including the row id (a hashed, routing-relevant field, never server-minted for fti); request_id / ip_address / success ride the pinned unhashed ingress.

Ownership: every object is owned by NOLOGIN canopy_chain_owner_medicaid; the canopy_medicaid_app runtime role appends ONLY through the SECURITY DEFINER function (C8). Details: ADR-014 Amendment 6.

Edit this page · default