ADR-040: Federal-Export Data Collection and Emit Architecture

The case-level source data is partly uncaptured. Child date-of-birth is not collected at intake (ChildEntry has no DOB → NCANDS age unsourceable); per-allegation disposition is never written (allegations.disposition always NULL → NCANDS maltreatment disposition unsourceable). Investigation-level disposition is captured.

The curated child demographics are plaintext; only the raw intake snapshot is encrypted. The as-reported intake payload (reports.children, Plan B §D2) is application-layer-encrypted JSONB. But the curated person record — persons.gender/race/ethnicity/date_of_birth — is plaintext structured columns (only ssn_last_four is encrypted), and every allegation links to its victim via a non-null allegation.victim_id FK. So the authoritative demographics are reachable as a plaintext cross-entity assembly; decrypting the intake JSONB is unnecessary. (Original premise — corrected during D1; see the Status amendment + issue #671.)

Capture-first sequencing. Add the missing data capture (intake child DOB; per-allegation disposition) before building the export. The export is the last phase, on real data — not the first. An export built on absent data would emit a mostly-empty file in exactly the fields that carry NCANDS value.

Plaintext cross-entity assembly for collection (no decrypt). A purpose-built cases endpoint (GET /v1/cases/ncands-export?ffy=YYYY) assembles the export rows from the plaintext domain entities, rooted on screened-in referrals received in the FFY → their allegations + investigations → the victim persons record (via allegation.victim_id), returning a typed DTO. No decryption is required: the NCANDS fields are plaintext (demographics on persons; reporter type / admin unit / receipt date on the referral). Rooting on referrals (rather than reports) also captures referrals created directly, not only those converted from an intake report. Rejected: decrypting reports.children (unnecessary, and it reads the as-reported snapshot rather than the curated person record); a store-level SQL join across the encrypted JSONB; an event-sourced read model (premature for a yearly/periodic export). Report date is referrals.received_at, which carries the originating report’s receipt timestamp onto referrals converted from an intake report (#672) — so FFY bucketing uses the true report date, not the conversion instant. Directly-created referrals (no originating report) keep the column DEFAULT now().

Per-row materialization with snapshot. generate writes one ncands_child_rows row per emitted child, materializing the jurisdiction/FFY-derived fields (#1 submission year, #2 state) into the row, so a later re-export is byte-stable even if the active bundle/jurisdiction changes. record_count and child_fatalities derive from the rows, never hardcoded.

Literal federal codes as pure functions in craig-reference, returning Option. The enum→code mapping lives in craig-reference (mirroring the afcars codecs) as pure functions that return Option<u8> (or total u8). This keeps the verified, codebook-cited mapping in one tested place. The existing internal-mnemonic *_to_ncands functions (ADR-precedent #640) remain; the new functions are named for the federal variable (*_to_rptsrc, *_to_chmal) to avoid confusion.

Leave-unpopulated fidelity policy. Where a CRAIG domain value has no faithful federal code (e.g. ReporterType::Mandated is a legal status, not an NCANDS source category; Disposition::Inconclusive has no RptDisp equivalent), the mapping returns None → a NULL column → an empty cell. CRAIG never emits a guessed/wrong federal code. The faithful fix (capturing a finer source/disposition at intake) is a tracked follow-up, not an inline guess. No domain enum is enriched as a side effect of the export.

FFY semantics + stable surrogate export IDs. reporting_year is the Federal Fiscal Year (Oct 1 – Sep 30). Federal record/child identifiers in the emitted file are stable surrogate keys, never raw internal UUIDs — internal join keys are not leaked into an external file.