Data Model: Data Exchange & ICPC

On this page

Overview

The exchange service manages inter-agency data sharing (partners, agreements, transactions) and Interstate Compact on the Placement of Children (ICPC) requests with home studies and attachments.

Partners define external systems to exchange data with. Agreements establish the legal basis. Transactions record individual data exchange operations. ICPC requests follow a multi-step workflow from draft through home study to completion.

The fleet-standard platform tables also live in this schema and are documented centrally: event_outbox / event_inbox / event_inbox_collisions (ADR-022 and the ADR-062 §A attempt machine), request_claims (ADR-062 §B), and schema_compat_floor (ADR-063).

Tables

Table Purpose

ssa_cohort_watermarks

#1466 B5 (ADR-065/066 §Amendment #1479) SSA screening run-lifecycle substrate: per-case cohort watermarks backing stale-run invalidation delivery

exchange_partners

External agencies/systems configured for data exchange

data_sharing_agreements

Legal agreements governing data exchange with a partner

exchange_transactions

Individual send/receive operations with payload and status tracking

exchange_send_jobs

Async partner-send queue behind the 202-Accepted handler (platform-stab-2 §D5; #1185 lease + fencing)

icpc_requests

Interstate placement requests with multi-step status workflow. Carries the (assigned_worker_sub, supervisor_sub, assignment_revision) denorm triple (#1312 revision guard; the supervisor half landed with #1367) — applied by the case.assignment_changed inbox, consulted by record-level authz refs and the worker/supervisor list-scope filters

icpc_home_studies

Structured home study evaluations for ICPC requests (one-to-one); carries the same denorm triple via the icpc_requests JOIN legs

icpc_attachments

Documents attached to ICPC requests — stored in object storage; carries the same denorm triple via the icpc_requests JOIN legs

upload_attempts

ADR-062 §U upload-attempt tombstones (SU2 #1248): one immutable row per client upload operation, id = the client-held request id; generation-fenced CAS state machine + replay decision table live in craig-store::attempts (shared crates); kinds here: icpc_attachment. 31 d terminal retention (reconciler-pruned); no FK — target_id is polymorphic by design.

ssa_screening_runs

SSA SOLQ screening runs (#1462, plan Plan::SSA B1; ADR-066 §D5): one row per household screening request, carrying the H1 cohort binding (cohort_hash
as_of + the one-way invalidation marker). Provenance class (ADR-065 §D4) — never transport-pruned; archive-eligible only once completed AND past the 12-month quiescence horizon. Carries the UD10 legal_hold flag (#1566): an eligibility-predicate column every destructive retention statement selects on AND re-asserts, transitively shielding the run’s whole family (members/jobs/transactions/handoffs); set/cleared by the human-admin hold endpoints, each transition audited. One ACTIVE (pending, not-invalidated) run per case by partial unique index.

ssa_screening_members

One row per screened household member: the A4 SsnDigest canonical string (tagged single TEXT, grammar-CHECKed), per-run ordinal, and the txn↔member one-to-one link (transaction_id is a logical fk — SSA transaction rows are prunable transport, provenance must outlive them). Terminal statuses include ssn_stale (the V2/B2 custody-mismatch class). Provenance class.

ssa_screening_jobs

The typed job queue the SsaWorker (#1465) drains — deliberately NO payload column (H18/SD14: the wire payload is built at send time from the member row + the value-bound custody release, never persisted). exchange_send_jobs-shaped lease (claimed_at + claim_generation); paused = the H16 class (categorical last_error: contract-pending / gate-disabled / partner-unavailable / dsa-not-active / egress-refused), re-admitted ONLY by the #1469 fork-6 resume endpoint (POST /v1/exchange/ssa-screening-jobs/resume — human-admin, live precondition re-check, attempts reset with the pre-reset count on the ssa.jobs_resumed audit event; never automatic on config change). Terminal failed classes incl. ssn-stale (custody digest refusal → the member goes ssn_stale). Transport class — pruned at the UD4 window (default/max 90 d, CRAIG_EXCHANGESSA_SCREENINGTRANSPORT_RETENTION_DAYS) by the E3 handoff-then-prune sweep (#1566): each batch snapshots the transport facts into ssa_transport_handoffs and deletes the job + its SSA exchange_transactions row in ONE transaction — the jobs delete re-asserts the full eligibility predicate plus a snapshot-exists guard, the transactions delete the hold + type legs — so a UD10 hold landing mid-batch (or flapping across the statements) rolls the whole batch back.

ssa_transport_handoffs

E3 (#1566): the UD4 provenance handoff relation — one insert-only row per member, written INSIDE the transport prune transaction, snapshotting the facts only the pruned job/transaction rows held (correlation token, partner, attempts, final status, categorical last_error, timing). FK direction is the recorded rule: the handoff FKs the MEMBER (ON DELETE RESTRICT) — provenance never FK-depends on transport — which also forces the archive deletion order handoffs → members → runs. Provenance class; archives with the family via the exchange engine.

archive_ledger

E3 (#1566): craig-exchange’s durable archive ledger (the craig-rules shape verbatim; ADR-058 D8) — one row per archived-and-pruned provenance batch, written inside the prune transaction; cargo xtask archive-fetch craig-exchange <table> resolves through its [min,max] id ranges (D17). No legal_hold column — exchange ships no purge tooling until DFCS names a schedule (#1480); the purge tooling that lands then must add it.

ER Diagram

exchange_partnersuuididPKtextpartner_nameUKtextpartner_typetextdirectiontextendpoint_urltextauth_typejsonbauth_configwrite-only; not returned in API responses (#757)textexchange_formatbooleanactivesoft-delete flaguuiddata_sharing_agreement_idFKnullablebigintversionIf-Match CAS counter, bumped by DELETE too (ADR-062 §G2)timestamptzcreated_attimestamptzupdated_atdata_sharing_agreementsuuididPKuuidpartner_idFKtextagreement_titledateeffective_datedateexpiration_datejsonbdata_elementstextlegal_authoritytextstatusdraft|active|expired|terminatedtextobject_keytextapproved_bytimestamptzapproved_attimestamptzcreated_atexchange_transactionsuuididPKuuidpartner_idFKtextdirectiontextexchange_typetextstatuspending|success|failed|retryjsonbrequest_payloadjsonbresponse_payloadtexterror_messageintegerrecord_counttimestamptzinitiated_attimestamptzcompleted_attextinitiated_byicpc_requestsuuididPKuuidcase_idcross-service FKuuidchild_idcross-service FKtextdirectiontextsending_statetextreceiving_statetextrequest_typetextstatusdraft|submitted|received|home_study_in_progress|approved|conditional|denied|completed|withdrawntimestamptzsubmitted_atauto-set on submittimestamptzdeadline_atauto-set: received + 84 daystimestamptzcompleted_atauto-set on completiontextcreated_bytimestamptzcreated_attimestamptzupdated_aticpc_home_studiesuuididPKuuidicpc_request_idFKtexthome_safetypending|pass|failtextbackground_checkspending|pass|failtextreferences_checkpending|pass|failtextspace_capacitypending|pass|failtextsupport_systemspending|pass|failtexteducation_accesspending|pass|failtextmedical_accesspending|pass|failtextrules_recommendationtextcoordinator_decisiontextoverride_justificationtimestamptzcompleted_attextassessed_byicpc_attachmentsuuididPKuuidicpc_request_idFKtextattachment_typetextobject_keytimestamptzuploaded_attextuploaded_byssa_screening_runsuuididPKuuidcase_idcross-service logical fktextstatuspending|completedchar_64cohort_hashsha256 hex, H1 cohort bindingdateas_ofbusiness date the cohort was bounduuidcreated_byrequesting caseworker subtimestamptzcreated_attimestamptzcompleted_atbiconditional with status=completedtimestamptzinvalidated_atone-way H1 marker, orthogonal to statustextinvalidation_reasoncohort_changed; paired with invalidated_atbooleanlegal_holdUD10 operator hold; shields the whole family (#1566)ssa_screening_membersuuididPKuuidrun_idFKON DELETE RESTRICTuuidperson_idcross-service logical fkintordinalunique per run; H5 per-ordinal determinismtextssn_digestSsnDigest canonical v{n}:{base64}, grammar-CHECKedtextstatuspending|screened|ssn_stale|faileduuidtransaction_idUKlogical fk - txn rows are prunable transporttimestamptzcreated_attimestamptzcompleted_atbiconditional with terminal statusssa_screening_jobsuuididPKuuidmember_idFKON DELETE RESTRICT; unique - one job per memberuuidcorrelation_idUKpartner-side idempotency tokentextstatuspending|in_flight|paused|sent|failedintattemptstextlast_errorcategorical only (ADR-065 D1)timestamptznext_attempt_attimestamptzclaimed_atclaim clock (1185 lease shape)bigintclaim_generationfencing tokentimestamptzcreated_attimestamptzcompleted_atbiconditional with sent|failedssa_transport_handoffsuuidmember_idPKFK ON DELETE RESTRICT — provenance never FKs transportuuidrun_idFKON DELETE RESTRICTuuidjob_idvalue snapshot of the pruned jobuuidtransaction_idvalue snapshotuuidcorrelation_idthe partner-side token, preserved past the pruneuuidpartner_idvalue snapshotintattemptstextfinal_statussent|failedtextlast_errorcategorical class (ADR-065 D1)timestamptzjob_created_attimestamptzcompleted_atthe family's shared archive age clocktimestamptzhanded_off_atgoverned byactive agreementtransacts withevaluated indocumented byscreensdelivered bytransport facts preserved by (#1566)

exchange_send_jobs

The send worker’s queue: one row per staged partner send, drained by ExchangeSendWorker (claim → adapter POST → fenced finalize). Statuses: pending / in_flight / sent / failed.

Column Type Notes

id

UUID

PK (uuidv7)

transaction_id

UUID

FK → exchange_transactions (ON DELETE CASCADE)

partner_id

UUID

Target partner

correlation_id

UUID

UNIQUE — propagated as craig_correlation_id, the partner-side idempotency token

payload

JSONB

Outbound payload staged verbatim

status

TEXT

CHECK pending / in_flight / sent / failed

attempts

INT

Incremented on EVERY claim (incl. lease reclaims); adapter errors past MAX_ATTEMPTS (6) go terminal

last_error

TEXT

Latest adapter/dispatch error

next_attempt_at

TIMESTAMPTZ

Scheduled ELIGIBILITY (backoff target) — deliberately untouched at claim; never a claim clock

claimed_at

TIMESTAMPTZ

#1185: the REAL claim clock. NULL (never claimed / legacy / crash residue) counts as an expired lease

claim_generation

BIGINT

#1185: fencing token — every claim increments it; every terminal/retry transition carries a status='in_flight' AND claim_generation = $n CAS, so a superseded worker’s stamp is a no-op (never a sent↔failed overwrite, never conflicting events)

created_at / completed_at

TIMESTAMPTZ

Stage / terminal stamps

Lease semantics (#1185): the claim query itself re-admits in_flight rows whose claimed_at is NULL or older than the worker’s CLAIM_LEASE_SECS (90s = 3× the 30s transport cap) — recovery is continuous on the 1s poll; there is no recovery sweep. A crash after the partner accepted but before finalize re-sends after lease expiry: at-least-once by design (ADR-022 #1185 amendment), deduped partner-side by craig_correlation_id.

Key Indexes

  • idx_exchange_partners_type — filter by partner type

  • idx_exchange_partners_active — active partner lookup

  • idx_agreements_partner — agreements by partner

  • idx_agreements_status — agreement status filtering

  • idx_transactions_partner — transactions by partner

  • idx_transactions_status — transaction status filtering

  • idx_transactions_initiated_at — time-range queries

  • idx_icpc_case — ICPC requests by case

  • idx_icpc_child — ICPC requests by child

  • idx_icpc_status — ICPC status filtering

  • idx_icpc_direction — inbound vs outbound ICPC

  • ssa_screening_runs_one_active_idx — UNIQUE (case_id) WHERE pending AND not invalidated: the one-ACTIVE-run-per-case safety net (#1462)

  • ssa_screening_runs_case_idx — run history by case (D3 freshness checks)

  • ssa_screening_members_person_idx / _ordinal_idx — UNIQUE per run

  • ssa_screening_members_transaction_idx — UNIQUE fleet-wide (txn↔member 1:1)

  • ssa_screening_jobs_member_idx / _correlation_idx — UNIQUE

  • ssa_screening_jobs_drainable_idx / _lease_idx — the B4 worker’s claim
    lease-recovery partials (the exchange_send_jobs shape)

  • ssa_screening_runs_held_idx — partial on (id) WHERE legal_hold (#1566): the held-runs gauge + hold-shield predicates; held rows are rare by design

  • ssa_transport_handoffs_completed_idx — the archive spec’s (ts, id) paging key (#1566; the family’s shared age clock)

  • idx_archive_ledger_id_range — (first_record_id, last_record_id): the D17 archive-fetch batch resolution (#1566)

  • idx_icpc_home_study_request — home study by ICPC request

  • idx_icpc_attachments_request — attachments by ICPC request

  • idx_exchange_send_jobs_drainable — partial on (next_attempt_at) WHERE status = 'pending'; the claim query’s due-pending arm

  • idx_exchange_send_jobs_lease — partial on (claimed_at) WHERE status = 'in_flight' (#1185); the claim query’s expired-lease arm (replaced the wrong-clock stuck_in_flight index)

  • idx_exchange_send_jobs_correlation — UNIQUE on correlation_id

Edit this page · latest