Plan: NCANDS Child File Federal Export (Capture-First)

On this page

Status

Step Description Status

0

Plan + ADR-040 + epic &55 + child issues + nav entry

Done (2026-06-21) — MR !763

A1 (#644)

Verified literal NCANDS code library in craig-reference

Done (2026-06-21) — MR !764

B1 (#645)

Capture child date-of-birth at intake (validation + form)

Done (2026-06-21) — MR !765

C1 (#646)

Capture allegation disposition on create

Done (2026-06-21) — MR !766

C2 (#647)

Allegation disposition update path

Done (2026-06-21) — MR !767

D1 (#651)

Cases NCANDS export collection endpoint (plaintext cross-entity assembly, service authz, FFY cutoff)

Done (2026-06-21) — MR !768

D2 (#652)

craig-reporting CasesClient into router state; generate fetches the real record_count (503 on upstream failure)

Done (2026-06-21) — MR !769

D3 (#653)

ncands_child_rows table + store (migration, batch-insert / delete / list / derive-counts)

Done (2026-06-21) — MR !770

D4 (#654)

Row mapping: cases export DTO → NcandsChildRowInput (#1–34)

Done (2026-06-21) — MR !771

D5 (#655)

Real generate_ncands: fetch → map → materialize rows in one tx; DB-authoritative counts

Done (2026-06-21) — MR !772

D6 (#656)

Export emitter: Child File TSV + POST /ncands/{id}/export + /export/download → object store

Done (2026-06-22) — MR !773

D7 (#657)

End-to-end lifecycle test + docs close-out + program archival

Done (2026-06-22) — MR !775

Epic: &55
Issues: capture #644–#647 (done); Phase D #651–#657; fidelity follow-ups #648/#649; D1-discovered #671 (PII-encryption posture) + #672 (referral report-date precision)
Branch: feat/ncands-… (per step)

Context

A faithful NCANDS Child File export is gated on data CRAIG does not capture yet. Verified against the real model (reports → screening_decisions triage → conversion → referrals → allegations
investigations; child subjects in encrypted reports.children JSONB):

  • No child date-of-birth at intake — ChildEntry (services/craig-intake/src/api/validation.rs) has name/gender/race/ethnicity but no DOB, so NCANDS age (#14) is unsourceable.

  • No per-allegation disposition — allegations.disposition exists but is never written (CreateAllegationRequest omits it; create_allegation does not bind it), so NCANDS maltreatment disposition (#27) is unsourceable.

  • Investigation-level disposition is already captured — UpdateInvestigationRequest.disposition, the store update, the PUT route, and a supervisor-gated required-on-close UI form (services/craig-web/templates/intake/investigation.html) + investigation_closed event are all live. This is the #11 source (and #27 fallback).

  • Child demographics (race/ethnicity/gender/DOB) are plaintext structured columns on persons (only ssn_last_four is encrypted), reachable via the non-null allegation.victim_id FK — so export collection is a plaintext cross-entity assembly (no decrypt). The as-reported intake snapshot in reports.children is encrypted, but the curated person record is the authoritative demographic source. (Corrected during D1 — the original "encrypted-only / app-decrypt" premise is superseded; see ADR-040’s D1 amendment + issue #671.)

Decision (recorded with the user): capture-first, then export — add the two missing capture surfaces, then build the export pipeline on real data. CRAIG is pre-1.0 with no live federal submission; this program emits the core record (NCANDS fields #1–34), with full 151-field coverage iterative.

Related but distinct: Gateway ACF-199 Data Export (a different federal file). The partner→federal-category mapping seam (ADR-032 §2.4, issue #583) is orthogonal — it routes partner exchanges, not Child File child-rows — and stays out of scope.

Codebook provenance

Authoritative source: NDACAN NCANDS Child File Codebook, revised 2025-09-12 (NDACAN dataset ID 306, FFY 2024), Duke/Cornell University. The field code values used below are transcribed from that codebook (RptSrc p18, RptDisp p19, ChAge p23, ChSex p24, race #16–21 pp25–30, ethnicity p31, maltreatment #26/#27 pp37–38). An implementer re-verifies a code by reading the codebook PDF (the National Data Archive publishes it at ndacan.acf.hhs.gov); do not assume NCANDS codes equal the craig-reference::afcars codecs.

Scope

In scope: child-DOB capture (intake); per-allegation disposition capture; the #644 verified-code library; then the export pipeline (core record #1–34) on captured data.

Out of scope (each → a filed follow-up linked to epic &55): full 151-field coverage; report-source taxonomy enrichment (the RptSrc gap — Mandated/Professional are not NCANDS source categories → unpopulated); child-fatality capture (#34 has no source → child_fatalities is honestly 0); race "unable to determine" / "unknown" source + capturing a child’s multiple races at intake (the export already supports multi-race via Vec<Race>, but intake records a single race today); AFCARS export; ACF transmission protocol; the NCANDS Agency File (aggregate); the #583 partner-axis seam.

Decisions

  1. Leave unpopulated for semantic gaps: the #644 functions return Option<u8>; None → a NULL column → emitted empty (the codebook NULL = "Not Collected or Not Applicable"). A NULL from a semantic gap is not a validation error — only a missing required-and-capturable field is. No CRAIG enum is enriched in this program (enrichment is a later follow-up).

  2. reporting_year = Federal Fiscal Year (Oct 1 – Sep 30); the cases collection endpoint takes ?ffy=YYYY; the cutoff is unit-tested (Sep 30 vs Oct 1).

  3. Emit format = TSV mirroring build_afcars_tsv (services/craig-reporting/src/api/afcars.rs); csv::Writer, tab delimiter, QuoteStyle::Necessary. The exact ACF fixed-width submission layout + transmission are deferred.

  4. Investigation disposition is the #11 source (already captured); allegation disposition (Phase C) is the #27 source.

  5. Age from DOB: capture DOB at intake (Phase B); derive age-at-report in the D4 mapping layer. The D1 collection DTO carries DOB only (not the intake approximate-age), so #14 is DOB-only — an absent DOB → NULL. Re-adding the approximate-age to the D1 DTO as a fallback is a deferred fidelity follow-up.

  6. Export IDs are stable surrogates, never raw internal UUIDs (no internal join keys leaked to a federal file).

Design

Verified federal codes + CRAIG mapping

# Field Codebook codes CRAIG mapping

#10

RptSrc

1–13, 88, 99

ReporterType: Anonymous→13, LawEnforcement→4, SelfReport→8, ConcernedCitizen→11; Mandated/Professional→None (unpopulated)

#11

RptDisp

1 Sub · 2 Indicated · 3 AR-Victim · 4 AR-Nonvictim · 5 Unsub · 6 Unsub-False · 7 Closed-NoFinding · 88 · 99

derived = most-severe allegation disposition (Phase C), fallback investigation disposition. Disposition: Substantiated→1, Unsubstantiated→5, Inconclusive→None

#14

ChAge

0 Under-1 · 1–23 · 77 Unborn · 99 Unknown

derived age(dob, report_date): <1→0, 1–23, >23→23; DOB absent (or after the report date) → NULL (the D1 DTO carries no approximate-age fallback)

#15

ChSex

1 Male · 2 Female · 9 Unknown

Gender (exactly Male/Female): Male→1, Female→2 (total); absent source → NULL

#16–21

Race ChRacAI/As/Bl/NH/Wh/UD

each: 1 Yes · 2 No · 3 UTD · 9 Unknown

Race (exactly AIAN/Asian/Black/NHOPI/White, no UD variant): selected→1, other four→2, #21 UD→2; absent source → all six NULL

#22

CEthn

1 Yes · 2 No · 3 UTD · 9 Unknown

Ethnicity: Hispanic→1, NonHispanic→2, UnableToDetermine→3 (total)

#26/28/30/32

ChMal1–4 type

1 Physical · 2 Neglect · 3 MedicalNeglect · 4 Sexual · 5 Psych/Emotional · 6 NoAlleged · 7 SexTraffick · 8 Other · 9 Unknown

AbuseType: Physical→1, Neglect→2, MedicalNeglect→3, Sexual→4, Emotional→5 (total)

#27/29/31/33

Mal1–4 disp level

1 · 2 · 3 · 4 · 5 · 6 · 7 · 8 NoAlleged · 88

per-allegation Disposition (Phase C): Substantiated→1, Unsubstantiated→5, Inconclusive→None

#34

Maltreatment Death

per codebook

no CRAIG source → unpopulated; drives child_fatalities = 0

Identifiers/dates (#3 Report ID, #4 Child ID, #5 FIPS, #6 Report Date, #8 Investigation Start, #12 Disposition Date) carry source IDs/dates (FIPS via craig-reference FIPS); #1 Submission Year + #2 State/Territory from the FFY + jurisdiction/bundle, materialized into the row so re-export is stable.

#644 function signatures

In crates/craig-reference/src/ncands.rs, beside the existing internal-mnemonic _to_ncands functions. Per-arm // NCANDS #N … (codebook rev 2025-09-12) citations. Functions return bare u8 / Option<u8>; code newtypes (NcandsRptSrc(u8) etc.) wrap at the *call site, not the signature. Named for the federal variable (_to_rptsrc / *_to_chmal / *_to_chsex), *not another *_to_ncands, to avoid confusion with the internal-mnemonic functions (per #640).

fn reporter_type_to_rptsrc(ReporterType) -> Option<u8>;  // None: Mandated/Professional
fn disposition_to_rptdisp(Disposition)   -> Option<u8>;  // None: Inconclusive
fn abuse_type_to_chmal(AbuseType)        -> u8;          // 1..=5 (total)
fn gender_to_chsex(Gender)               -> u8;          // 1|2 (total)
fn ethnicity_to_ncands(Ethnicity)        -> u8;          // 1|2|3 (total)
fn race_to_ncands(&[Race])               -> NcandsRaceFields; // present→1, absent→2, ud→2 (multi-race; Vec<Race>)

Age derivation (child_age_code, #14) is not in craig-reference (it would add a chrono dependency to that leaf crate, and needs the report date) — it lives in the Phase D mapping layer (D4) where dates are handled.

Tests = #644 acceptance: exhaustive per-variant vs codebook + proptest range bounds + explicit None-arm pins (reporter_type_to_rptsrc(Mandated) == None, disposition_to_rptdisp(Inconclusive) == None) + age boundaries.

ncands_child_rows schema (Phase D)

New migration under services/craig-reporting/migrations/, uuidv7() PK. submission_id FK (ON DELETE CASCADE) + source_report_id / source_victim_person_id provenance (idempotent re-generate via delete-by-submission). Materialized columns: #1 submission_year, #2 state, #3 report_id_field, #4 child_id_field (surrogate, not raw UUID), #5 state_county_fips, #6 report_date, #8 investigation_start, #10 report_source_code (NULL=unpopulated), #11 report_disposition_code, #12 disposition_date, #14 child_age_code, #15 child_sex_code, #16–21 six race SMALLINT`s, #22 child_ethnicity_code, #26–33 `mal{1..4}_{type,lev} (de-duplicate maltreatment types — codebook: each type once across ChMal1–4; >4 distinct → first 4 + row_valid=false), #34 maltreatment_death, plus child_fatality BOOLEAN, row_valid BOOLEAN, validation_notes TEXT, created_at. The same migration adds export_object_key / export_generated_at to ncands_submissions (parity with afcars_submissions). Counts: record_count = COUNT(); child_fatalities = COUNT WHERE child_fatality; validation_errors = COUNT WHERE NOT row_valid. *Validation rule (minimal, as implemented in D4): a row is invalid (row_valid = false) only when more than four distinct maltreatment types are present, or when no disposition is recorded at all (every allegation disposition AND the investigation disposition absent). Semantic-gap NULLs are not errors — in particular a captured-but-Inconclusive disposition stays valid (its #11/level code is an emitted-empty NULL). Full ACF edit-checks deferred.

Phase D collection (D1 — implemented)

A purpose-built cases endpoint GET /v1/cases/ncands-export?ffy=YYYY does a plaintext cross-entity assembly (no decrypt), rooted on screened-in referrals received in the FFY → their allegations (type + disposition) + investigations (disposition + dates) → the victim persons record (demographics via allegation.victim_id), returning a typed one-row-per-victim-allegation DTO (craig-cases-contracts::ncands::NcandsExportRow). No decryption is needed — demographics are plaintext on persons; reporter type / admin unit / receipt date are plaintext on the referral (see ADR-040’s D1 amendment). Rooting on referrals also captures directly-created referrals, not only converted ones. The FFY window is half-open [Oct 1 (ffy-1), Oct 1 (ffy)) on referrals.received_at (unit-tested). Service-to-service authz: require_service_caller()
service_id == "craig-reporting" (mirrors jws_replay), not the per-row ruleset engine — annotated // authz: skip. D2 wires craig-reporting’s explicit CasesClient into router state (the authz-boot http_client is not in router state today; upstream failure → 503, never a silent 0-row file).

Phase D row mapping (D4 — design)

services/craig-reporting/src/ncands_mapping.rs maps the D1 collection DTO (NcandsExportResponse — one row per victim-allegation) into the materialized NcandsChildRowInput rows (one per child-in-report). The mapping is pure and infallible: a semantic gap degrades to a NULL code (never an error), and a structurally unusable row is flagged row_valid = false rather than dropped — so there is deliberately no mapping error type (graceful degradation is more faithful than a hard error for a best-effort federal extract; "no anyhow at a pub boundary" holds vacuously).

Grouping. The DTO is one row per victim-allegation; an NCANDS Child File record is one row per (report, child). Rows are grouped by (referral_id, victim_person_id) (deterministic BTreeMap key order, independent of collection order) → one NcandsChildRowInput per group. Report- and victim-level fields (receipt date, admin unit, reporter type, demographics, investigation dates/disposition) are identical within a group and taken from its first row.

Per-field mapping (#1–34).

  • #1 submission_year = the FFY (export.ffy); #2 state = the jurisdiction’s FIPS state code (Georgia → 13), resolved from StateBundle::jurisdiction_code() at D5 and passed in (user decision: a real federal state code, not the raw "georgia" jurisdiction string). Both materialized so re-export is stable. A jurisdiction_code with no State match → export still emits with #2 NULL + a filed fidelity follow-up. (Exact representation — 2-digit FIPS 13 vs postal GA — confirmed against the codebook + the craig-reference State accessor at implementation.)

  • #3 report_id_field / #4 child_id_field = stable opaque surrogates of referral_id / victim_person_id: hex(SHA-256(domain-tag ‖ uuid_bytes))[..24] with distinct tags (ncands-report: / ncands-child:). Deterministic + stable across re-export, never the raw internal UUID (privacy — no internal join key reaches the federal file). Adds sha2 to craig-reporting.

  • #5 state_county_fips = counties::admin_unit_by_name(state, admin_unit).fips_code, else NULL (an admin-unit name that doesn’t resolve is a semantic gap, not an error — and a candidate fidelity follow-up if seed/admin-unit names diverge from the reference dataset). #6 report_date = referral_received_at (UTC date); #8 investigation_start = investigation_started_at; #12 disposition_date = investigation_closed_at.

  • #10 report_source_code = reporter_type_to_rptsrc (Mandated/Professional → NULL).

  • #11 report_disposition_code = disposition_to_rptdisp(most_severe(…)), where most-severe ranks Substantiated > Unsubstantiated > Inconclusive across the group’s allegation dispositions, falling back to the investigation disposition. Inconclusive (no faithful federal code) → NULL — a semantic gap, not a row error.

  • #14 child_age_code = child_age_code(victim_date_of_birth, report_date) (lives in this layer, not craig-reference — it needs the report date): completed years at the report date, then <1 → 0, 1–23 → the age, >23 → 23; DOB absent or after the report date → NULL (the DTO carries no reported-age fallback). Checked arithmetic.

  • #15 child_sex_code = gender_to_chsex (absent → NULL). #16–21 race = the six race_to_ncands flags (absent → all six NULL). #22 child_ethnicity_code = ethnicity_to_ncands (absent → NULL).

  • #26–33 maltreatment 1–4: the group’s distinct abuse types (codebook: each type once across ChMal1–4), sorted by ChMal code for a deterministic slot order; each slot’s level = disposition_to_rptdisp(most-severe disposition among that type’s allegations). With 5 distinct AbuseType variants but only 4 slots, a child with 5 distinct types keeps the first 4 (by code) and sets row_valid = false.

  • #34 maltreatment_death = NULL; child_fatality = false (no CRAIG source → child_fatalities = 0).

row_valid (minimal validation). false only when a required-and-capturable field is missing: >4 distinct maltreatment types, or no disposition recorded at all (every allegation disposition AND the investigation disposition absent — a capture gap, distinct from a captured-but-Inconclusive disposition, which is a semantic-gap NULL and stays valid). child_id_field is always derivable from the always-present victim id, so that arm never trips today. validation_notes records the reason when invalid.

Tests (654 acceptance): table-driven + proptest — most-severe ranking (incl. the investigation fallback + all-Inconclusive), age boundaries (0 / 1 / 23 / >23, leap-year, DOB-after-report, absent), dedup + the 5-types→4-slots+row_valid=false case, the None/unpopulated arms (Mandated RptSrc, Inconclusive RptDisp), and the no-disposition-at-all → row_valid=false vs Inconclusive → valid distinction. The mapping functions are consumed by D5; until then a module-level [cfg_attr(not(test), expect(dead_code, …))] marks them (auto-expires when D5 wires generate_ncands).

Steps

Step 0: Plan + ADR-040 + epic + issues (this MR)

Files: this plan, docs/modules/ROOT/pages/adrs/adr-040-federal-export-collection-and-emit.adoc, docs/modules/ROOT/nav.adoc, CHANGELOG.adoc. Epic &55 + issues #644/#645/#646/#647 created.

Step A1 (#644): Verified literal NCANDS code library

Files: crates/craig-reference/src/ncands.rs (NcandsRaceFields + the functions, codebook citations
test battery; no chrono dep). Closes #644. File the deferred follow-ups (true NCANDS reporter source
disposition capture at intake — the fidelity fix for the None/unpopulated arms; child-fatality capture; full 151-field coverage).

Step B1 (#645): Child DOB capture at intake

Files: services/craig-intake/src/api/validation.rs (ChildEntry.date_of_birth: Option<String>
validate_optional_iso_date helper — Option<String> not NaiveDate because the form sends an unset <input type="date"> as "", which would 422 a parsed-date field; empty/absent = not provided), services/craig-intake/static/report.html + static/js/report-form.js (DOB date input + model), intake unit tests. The public contract children stays raw serde_json::Value (untrusted ingress); the intake→cases forwarder is pass-through, so the raw DOB flows verbatim into reports.children.

Step C1 (#646): Allegation disposition on create

Files: crates/craig-cases-contracts/src/referrals.rs (CreateAllegationRequest.disposition), services/craig-cases/src/store/referrals.rs (create_allegation binds it), services/craig-cases/src/api/referrals.rs (handler), services/craig-web/templates/intake/referral_detail.html (create-form select), cases tests. Optional + backward compatible (omitting → NULL).

Step C2 (#647): Allegation disposition update path

Files: crates/craig-cases-contracts/src/referrals.rs (UpdateAllegationRequest), services/craig-cases/src/store/referrals.rs (update_allegation), services/craig-cases/src/api/{referrals.rs,mod.rs} (PUT /v1/cases/referrals/{id}/allegations/{aid} + authz), services/craig-web/templates/intake/referral_detail.html (inline edit), cases tests.

Steps D1–D7: Export pipeline (gated on A1+B1+C1/C2)

D1 (#651, done) cases collection endpoint — plaintext cross-entity assembly rooted on screened-in referrals, service authz, FFY cutoff; the existing seed already produces the referral/allegation/investigation/person chain, so no seed change was needed. D2 (#652, done) craig-reporting CasesClient wired into router state
generate_ncands fetches the export and records the real top-line record_count (a 503 on upstream failure via the new ApiError::ServiceUnavailable; an empty FFY is an honest count-0, never a silent 0-row file). The D2/D5 boundary: D2 does fetch + top-line count; D5 adds map + per-row materialize. D3 ncands_child_rows migration + store. D4 mapping DTO → NcandsChildRow (#1–34: RptDisp most-severe derivation, age derivation, #644 codes; materialize #1/#2). D5 full generate_ncands (fetch→map→materialize in one tx → record_count/child_fatalities/validation_errors derived from the rows; empty-FFY = honest count-0; supersedes D2’s top-line count). D6 export emitter POST /ncands/{id}/export
/export/download → TSV → object store, with a byte-identity hash test fed from a hand-fixed row vector (not seed data) + stable surrogate export IDs. D7 integration tests + docs. Phase D issues #651–#657 filed.

Known fidelity limitations (documented; each filed as a follow-up)

RptSrc unpopulated for Mandated/Professional; RptDisp/Mal-level unpopulated for Inconclusive; race UD/"unknown" source not representable + intake captures a single race (the export emits Yes/No per race and supports Vec<Race>, but only one is recorded today); Gender "Unknown" (ChSex 9) not representable; unborn age (77) not representable; child_fatalities = 0 (no source). This is an honestly-partial slice, labelled as such in the CHANGELOG.

Verification

  1. A1: code-table + proptest + None-arm-pin + age-boundary tests == #644 acceptance.

  2. B1: intake unit tests — DOB format validation (valid ISO accepted / bad format 400 / empty = not provided). The round-trip into reports.children is the unchanged forwarder passthrough, exercised end-to-end by the Phase D export test.

  3. C1/C2: cases API tests — create + update an allegation disposition; assert persisted.

  4. Phase D (devstack, seeded): generate_ncands over an FFY → non-zero record_count → export → download yields a multi-row TSV; faithful columns carry codebook codes; Mandated/Professional RptSrc + Inconclusive RptDisp cells empty; age derived from DOB; maltreatment + disposition populated from C1/C2 data.

  5. Byte-identity hash test (D6) over the hand-fixed vector; BLESS_SEED_HASHES-style re-bless.

  6. Each MR: cargo xtask validate + a fresh-eyes J1–J8 review of the staged diff.

Documentation Updates

  • The new cases ncands-export endpoint + the ncands_child_rows table (Phase D) — documented in the per-service pages api/craig-cases.adoc + api/craig-reporting.adoc (regenerated, D7) and data-model-reporting.adoc (D7). services.adoc is a thin index whose rows already point to those pages; it lists no individual endpoints/tables, so it needs no edit.

  • CHANGELOG.adoc — an == Unreleased entry per MR (D0–D7).

  • ADR-040 — the architecture record (MR 0).

  • Shared Crates — Public API Surface — the #644 code functions (A1).

Edit this page · latest