Data Model: Case Management

reports

The public-report lifecycle rows relocated from craig-intake (ADR-017 Step 3) — the stateless edge forwards here; screened via screening_decisions, converted into referrals

screening_decisions

Report screening decisions (single-parent supersession chains; the effective leaf drives convert — #767 write-side prevention)

disposition_follow_ups

Report disposition follow-ups (the ADR-017 Step 3 trio’s third table): caseworker actions attached to non-terminal dispositions (screened-out-and-referred, I&R)

report_persons

ADR-019 report-entry ↔ person links — evidence-grade rows recording each deliberate link (auto-link at convert or worker confirmation) between reports.children/adults JSONB entries and persons; rejections write no row

report_attachments

Supporting documents for reports (mirrors contact_attachments: soft-delete, uploader provenance, object-store keys; moved from craig-intake per ADR-018 Step 3)

jws_seen_jti

Mandated-reporter JWS replay-defense ledger (Plan C F-005): seen jti values with TTL pruning — lives here, not in the stateless intake edge

crypto_key_lineage

ADR-048 §D3 key↔data lineage anchor (singleton: KCV + canary) — what the cases boot verify checks the mounted field key against

crypto_field_lineage

#1025/#1395 per-field scheme lineage — the authoritative record of which at-rest scheme each allowlisted field’s data was written under; insert/equal-only, written by the migrate gate’s post_apply seam

persons

Shared person registry — children, parents, reporters, perpetrators

referrals

Hotline intakes — screening decisions, priority, county assignment

allegations

Specific abuse/neglect allegations linked to a referral, victim, and perpetrator

investigations

Field investigations opened from screened-in referrals

safety_assessments

Structured safety decisions produced by the rules engine during investigation

cases

Ongoing case management records opened after substantiated investigation

case_household

Household composition — persons linked to a case with role and caregiver flag. SSA-screening note (#1466 B5 + #1467 B6b, ADR-065 § Amendment #1479 D16): every runtime mutation that changes what a screening cohort would contain bumps cases.screening_cohort_revision through the ONE screening_revision seam — household add (P1), update_person digest transitions incl. the keyless fallback arm (P2), clear_ssn (P3), manual-link promotion (P4), §G4 auto-link promotion (P5) — and the seam stages the case.screening_cohort_changed event in the same tx (#1467 B6b), so a bump and its event can never disagree. P7/P8 contract (pinned here, deliberately not as dead code): no surface removes a household member or merges persons today; whoever builds either MUST bump through the same seam — an unbumped removal/merge would leave a stale screening run witnessing a cohort that no longer exists.

case_plans

Permanency plans with goals, strengths/needs assessment, and approval tracking

case_plan_tasks

Action items within a case plan assigned to workers or families

contacts

Visitation and contact logs with narrative documentation

contact_attachments

Files attached to contacts — stored in object storage, metadata in DB

court_orders

Legal orders with findings, hearing dates, and uploaded documents

ssa_benefit_facts

SSA benefit assertions (#1466 B5-U2, ADR-065 §D4 — provenance class, INSERT-only, indefinite-hold pending DFCS). Row-per-benefit pointer-forward supersession chain: exactly-one-current per (person × kind × source × beneficiary_role) is STRUCTURAL (one chain root + at most one successor per row — partial uniques, never a status column); corrections/terminations/re-grants are successor rows. Keyed by SsnDigest provenance pointers, never by SSN. Read surface: the D4 (#1549) case-scoped DTOs only — the store row is deliberately not Serialize (ADR-065 §D3).

ssa_screening_outcomes

One row per screened member (#1466 B5-U2 — the D20 projection): outcome category + fact_ids, the replay truth post-prune redeliveries re-derive from (claims store no response bodies). Insert-only, provenance class, no digests. Served via the D4 (#1549) household-scoped outcome list.

ive_determinations

#1557 (D3, ADR-066 §D7): the IV-E determination of record — the fleet’s first persisted eligibility determination. Denormalized decision outputs (as-of doctrine) + rule_set_revision/evaluation_id joining the rules-side #1130 single copy; the worker-ATTESTED input document (provenance-labeled); the witness block (run, as-of, cohort revision, the byte-verified cohort_hash, consulted fact_ids, the server-derived evidence token). Written only by the assembly endpoint’s claimed tx. Keep-forever-hot audit class.

screening_review_worklist

#1557 (UD5): notify-never-re-evaluate — ONE open item per case (partial unique WHERE not dismissed; the #1172 standing-alert ON CONFLICT bump), opened post-determination only, by three same-tx writers serialized on the case row lock (facts fan-out / cohort seam / the run-invalidation consumer). Dismissal records who/when/why in-row AND stages case.screening_review_dismissed.

screening_run_invalidations

#1557: the cases-local run-invalidation marker (per-run, first-write-wins), written by the widened inbox consumer’s claim tx; the determination tx’s in-tx witness re-check consults it — delivered invalidations refuse, undelivered ones surface via the worklist on eventual delivery.

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: contact_attachment / report_attachment / court_order_document (the replace kind — court_orders.current_attempt_id is its fenced pointer). 31 d terminal retention (reconciler-pruned); no FK — target_id is polymorphic by design.