Plan: Gateway ACF-199 Data Export (#161)

Registry (crates/craig-cases-fields/src/persons.rs): field ssn — FieldScheme::BlindIndex, HKDF field_domain = "ssn_full" v1, Canon::AsIs over the pre-canonicalized digits; sibling ssn_full_hmac. Migration: ssn TEXT (ciphertext) + ssn_full_hmac TEXT + partial index
verification columns + the CHECK below. ssn is excluded from the persons search descriptors in MR1 — full-SSN equality search would be a confirmation oracle for realm-flat caseworkers; the HMAC
index exist for write-time duplicate detection and #162’s future matching, not search capability (stated in the registry doc-comment).

Row machinery is explicit work: extend impl_encryptable_row! (crates/craig-search/src/row.rs:101-135, single-sibling today) to multiple HMAC siblings (or hand-write impls); update CreatePersonParams/UpdatePersonParams/Person (services/craig-cases/src/api/encryption/rows.rs) + the seeder Person shape (tools/craig-seed/src/encrypt.rs). Missing a shape hard-errors RowMissingHmacSlot("ssn_full_hmac") on person writes. The ADR-048 boot verify scan handles the new nullable encrypted column automatically; nothing else does.

DTO ssn: Option<String>: [garde(length(max = 11))] + [schema(max_length = 11)] (#492 cap-sync; garde ships without the regex validator). Service-side canonicalization accepts ONLY digits with space/dash separators (letter-embedded input rejected — 123x45x6789 is not an SSN with decoration), then exactly-9 digit shape AND SSN range validation (area ∉ {000, 666, 900–999}, group ≠ 00, serial ≠ 0000) → 400 bad_request_typed(INVALID_SSN) (new craig-common constant).

Write-only: #[serde(skip_serializing)] on Person.ssn + Person.ssn_full_hmac (the ssn_hmac precedent — person reads return the store model; there is no read-DTO layer). Reads expose last-four only. Not clearable via update — correction is the audited clear operation below.

Atomic writes: the update tx takes SELECT … FOR UPDATE on the person row; CASE-arm SQL (the store’s update_person is COALESCE-only today, store/persons.rs:80-94) writes ssn
ssn_full_hmac + derived ssn_last_four + ssn_hmac and resets ALL verification columns on EVERY ssn write, same-value included — deliberate policy: re-entry never re-verifies, even though the deterministic ssn_full_hmac would make equality knowable in SQL; do not "optimize" the reset away. The SSN_LAST_FOUR_MANAGED on-file check runs INSIDE the same locking tx. Create-with-ssn derives the same four columns + verification-false. Direct ssn_last_four entry while a full ssn is on file — or both fields in one request — → 400 bad_request_typed(SSN_LAST_FOUR_MANAGED) (new constant). New full-SSN writes carry the digit-shape guarantee (#1023’s DTO-boundary fix for standalone last-four stays that issue’s scope).

Columns ssn_verified BOOLEAN NOT NULL DEFAULT false, ssn_verified_at TIMESTAMPTZ, ssn_verified_by TEXT, ssn_verification_method TEXT (evidence source). DB CHECK: ssn_verified ⇒ (verified_at, verified_by, method) NOT NULL and NOT ssn_verified ⇒ all three NULL — method is nulled wherever the other two are (reset-on-write, revocation, clear).

Digest acquisition GET /v1/cases/persons/{id}/ssn-verification → { digest: Option<String> (base64 `ssn_full_hmac, matching Codec::Base64), ssn_present, ssn_verified, verified_at, verified_by, method }` — same authz as attestation (Action::Approve) + in-handler service denial. The keyed HMAC is non-reversible (HKDF field key held only by cases), so exposing it to attesters is safe; ADR-051 argues this. Without this surface the value-bound flow is unimplementable (ssn_full_hmac is skip-serialized on every other read path).

Attestation POST /v1/cases/persons/{id}/ssn-verification: body carries the digest fetched above + method. The attestation write is itself a single guarded statement (no validate-then-write window): UPDATE persons SET ssn_verified=true, … WHERE id=$1 AND ssn_full_hmac=$2 AND ssn IS NOT NULL RETURNING *; zero rows → re-read to disambiguate: person missing → 404; ssn NULL → 409 conflict_typed(MISSING_SSN); hmac mismatch (SSN changed since the attester fetched) → 409 conflict_typed(STALE_ATTESTATION) (all 409s via conflict_typed, never bad_request_typed). DB CHECK additionally enforces ssn_verified ⇒ ssn IS NOT NULL. Attribution claims.acting_worker(). Authz authz.check(…​, ResourceType::Person, Action::Approve) — GA: admin+supervisor already allowed (allow-all rows; no ruleset change, pinned by tests); TX: the person ResourceRef carries no supervisor linkage (persons.rs:35-44) so i_supervises never matches → TX attestation = admin/regional only, deliberate and pinned. In-handler PURE-service-caller denial — claims.acting_worker().is_service() → 403 (the Plan E §9b predicate; composition precedent at write_support.rs:212). NOT the literal claims.is_service(), which would 403 craig-web’s own BFF calls (service token + lifted X-Craig-Actor — the sole entry surface). Test pins split accordingly: pure service token 403 (evil) vs craig-web-style service token + verified actor → 200 with acting-worker attribution (happy). The person rulesets grant service principals allow-all and changing that is out of scope here. This predicate applies to ALL FOUR verification endpoints (digest GET, attest, revoke, clear).

Revocation DELETE /v1/cases/persons/{id}/ssn-verification (reason required): same authz + service denial, nulls all verification columns, audited.

Audited clear DELETE /v1/cases/persons/{id}/ssn (body { reason }, required): clears all four SSN columns — ssn, ssn_full_hmac, ssn_last_four, ssn_hmac — plus all verification columns. (After any full-SSN write the stored last-four IS derived from it, so retaining it would keep the wrong person’s PII residue; a still-valid standalone last-four is simply re-entered through the normal path afterward, which is permitted once no full ssn is on file.) The wrong-person/merge/data-subject remedy. Authz: authz.check(…​, Action::Approve) PLUS an in-handler admin-role gate (a ruleset-routed check alone cannot be admin-only — GA supervisor rows are allow-all) and the same pure-service denial. ApiDoc-registered with the other three; evil-axis tests.

Attestation/revocation/clear each stage person events on the transactional outbox (PII-free payloads, the publish_person_updated pattern — services/craig-cases/src/events.rs).

Request { person_ids: Vec<Uuid>, include_full_ssn: bool (default false) }; >5,000 ids → manual length check → 400 bad_request_typed(BATCH_TOO_LARGE) (existing constant; garde caps cannot emit typed problems). Consumers chunk at the cap.

Response rows: demographics + ssn_last_four + ssn_present + ssn_verified
ssn_digest: Option<String> (base64 ssn_full_hmac — the value the blocked transmit re-affirmation compares; reporting cannot compute it, the HKDF key lives in cases); full ssn populated ONLY when include_full_ssn = true AND the row is ssn_verified — unverified SSNs never leave cases via THIS purpose. Cache-Control: no-store. Unknown ids absent from the response.

Each call stages a PII-export audit event (requestor service id, the person-id list, include_full_ssn, purpose string) on the cases outbox — the repo’s first read-audit, and it must answer "whose SSNs left the service" in an incident (UUIDs are not themselves PII); reaches audit_log via the # subscriber. Doc-comment cites ADR-051: this is the only full-SSN-emitting surface today; #162’s SOLQ verification will need a distinct verification-purpose mode (separate allowlist entry, UNVERIFIED emission permitted — verification is the point — same audit event with a purpose discriminator), specced at #162 pickup; ADR-051 anticipates that exception explicitly.

Data sources: payments generated from subsidy agreements (&70 #1068) — monthly (SG/NRSG start the 1st of the following month; ERR is effective from the placement date, so the FIRST month may be partial — confirm proration in &70 planning), uas_code denormalized from the agreement; the financial export endpoint filters status IN ('issued','cleared') and attributes each payment to exactly one quarter by period_end within the half-open FFY-quarter window, where quarter WINDOWS are half-open but payment period_end is the inclusive last covered day (the existing financial convention — compute_period/inclusive_day_count + the period_end >= period_start CHECK; &70 #1068’s generator must keep it, named in the re-validation checklist — a half-open period_end would silently shift every quarter-final month into the next quarter). Keyset pagination + max-span bound + supporting index. Unclassified-candidate predicate (pinned): the financial endpoint ALSO returns rows matching uas_code IS NULL AND placement/payment_type ∈ the jurisdiction’s relative-placement set (effective-dated reference data, not constants — financial evaluates the predicate; reporting materializes each as a validation failure). Never silently filtered.

Quarters: YYYY-Qn = federal fiscal quarter of FFY YYYY (Q1 = Oct–Dec of YYYY−1), parsed by a garde-custom validator in craig-reporting-contracts with half-open window derivation (2026-Q2 → [2026-01-01, 2026-04-01)), pinned test + never-panics proptest, half-open everywhere including the financial endpoint contract.

Accounting basis: each submission captures an as-of cutoff timestamp at generate, applied as issued_at ⇐ cutoff in the financial endpoint contract (post-cutoff status changes — voids, clears — are caught only by supersession, stated); rows snapshot everything except the full SSN (demographics, amounts, uas_code, bundle version, ssn_last_four, ssn_present/ssn_verified
the ssn_digest from D4’s export row for transmit re-affirmation); history is never re-derived from current bundle contents. Late corrections → a NEW superseding submission (below). Late-arrival detection (the supersession trigger): generate additionally surfaces issued/cleared payments whose period_end falls in a PRIOR period with a transmitted submission and whose issuance postdates that submission’s cutoff, as a quality issue naming the stale period — otherwise dollars are silently omitted during the 45 CFR 265.8(c) penalty-free correction window with zero operator signal.

One active per period: partial unique index on (reporting_period) over non-terminal statuses
superseded_by column; regenerating a period supersedes-then-creates (case_plans lock-supersede-activate precedent) — supersession is a status flip to a new terminal Superseded variant (vacating the index slot; a bare superseded_by stamp would leave the old row non-terminal and the create would 23505). UNIQUE (reporting_period, revision) makes the idempotency key real. The supersede CAS’s expected-status set EXCLUDES transmitting — an in-flight delivery must terminalize first (no successor revision can race a mid-POST worker into a double-submission under a different key).

Counts: aligned with the NCANDS precedent — record_count = COUNT() (total rows), validation_errors = COUNT() FILTER (WHERE NOT valid) (subset; ncands_child_rows.rs:287-289 form) so the shared column names mean the same thing across programs — DB-authoritative in the generate tx. Zero rows ⇒ validated_with_errors (terminal; exact NCANDS store semantics, store/ncands.rs:177-181) — which makes a legitimately EMPTY quarter untransmittable; whether Gateway needs an explicit empty-period signal is a #1074 contract question (named in the re-validation list). Missing-person rows: nullable name columns + valid=false + notes (sentinel-free). The verified-SSN hard gate is DELIBERATELY stricter than federal (ACF element #69 permits a temporary 000000000 for an unobtainable child SSN with replace-later duty) — recorded choice with deadline exposure; placeholder handling is also a #1074 question.

Lifecycle: the shared SubmissionStatus enum gains Transmitting and Superseded variants, and the transition matrix becomes per-program (gateway-199’s chain adds approved → transmitting → transmitted, the failure back-edge transmitting → approved, and supersession edges; the AFCARS/NCANDS matrices are pinned UNCHANGED by tests — the shared matrix today is a strict forward chain and must not silently loosen). ALL transitions are CAS single-statements (WHERE id=$1 AND status='<expected>' RETURNING *) with the financial disambiguation protocol (404 / 400 INVALID_STATE_TRANSITION / 409 CONCURRENT_MODIFICATION). Actor stamps generated_by/reviewed_by/approved_by/transmitted_by via claims.acting_worker(); approve and transmit must be distinct actors — enforced IN THE TRANSMIT CAS PREDICATE (… AND status='approved' AND approved_by IS DISTINCT FROM $actor) with a typed 403 + test, NOT in rulesets (per-jurisdiction fixtures are replaceable; a federal-integrity invariant must not be silently repealable). Rulesets stay role-gating only. (Deviation from AFCARS noted.)

Transmit: no stub mode. URL unset → typed 503 TRANSMIT_TARGET_UNCONFIGURED, zero state change; devstack/e2e set the URL to craig-mock-server. When configured: the transmit tx CAS-flips approved → transmitting and stages a delivery job (send-jobs pattern: payload digest, correlation UUID UNIQUE, attempts, last_error, receipt column; UNIQUE(submission_id) WHERE job non-terminal — at most one live job per submission); a worker claims via FOR UPDATE SKIP LOCKED, re-fetches SSNs (include_full_ssn=true, verified-only) and re-affirms each row’s ssn_digest
verified state (any regression → job terminalizes AND the submission CAS-flips back to approved in the same tx; 409 conflict_typed(STALE_SUBMISSION) surfaced on inspection), POSTs once per attempt with bounded timeout, redirect(Policy::none()), https-required URL + host allowlist (relate #771), Idempotency-Key: <submission_id>:<revision>; stores the response; success CAS-flips transmitting → transmitted + stages the event. Terminal HTTP failure (attempt cap, send-jobs MAX_ATTEMPTS/backoff schedule) → job terminalizes + submission CAS transmitting → approved in the same tx — never wedged in transmitting (the one status the supersede CAS excludes); a bootstrap recovery sweep (send-jobs precedent) flips stale in-flight jobs. Full SSNs exist only in the outbound request body — never in reporting’s DB, the object store, logs, or events.

Export artifact (review aid, not "PII-free" — it carries demographics + last-four): object-store TSV without full SSNs; retention/download-audit posture recorded in the design when implemented.

Quality issues: generate writes data_quality_issues with the tx-aware create_issue (PgExecutor), full field set (field_name/description/severity), source_service = data-owning service, paired reporting.quality_issue_detected events; dedup natural key (source_service, source_record_id, issue_type, field_name) unresolved-unique so regeneration cannot duplicate; issues auto-resolve when a later generate finds the row valid.

Authz: new ResourceType::Gateway199Submission (snake_case rendering pinned); per-jurisdiction rulesets, service callers DENIED in both (GA: admin+supervisor full, readonly read+list; TX: modeled on TX afcars — admin/regional/supervisor-with-supervises/readonly — NOT a GA clone). A compromised peer service must not be able to drive generate→transmit.

Row model → ACF-199 mapping: deferred to the real contract (#1074); known limitation recorded — ACF-199 race/ethnicity are six independent elements vs CRAIG’s single-valued persons.race; family/person record structure is Gateway’s derivation unless the contract says otherwise.

Module layout: flat api/gateway_199*.rs files each honestly < 500 lines (B1 is non-recursive; subdirectories evade rather than satisfy it). Contracts DTOs in craig-reporting-contracts; typed test-lib client methods (B7 flat); FEATURE_MATRIX_CRATES + compose env when financial gains bundle features (&70 #1072).