Data Model: Placement
On this page
Overview
The placement service manages foster homes, child placements, kinship evaluations, and sibling group tracking.
Foster home occupancy is derived automatically by a database trigger (#761): a home’s current_occupancy
always equals the count of its placements in active status, recomputed on every placement insert/delete and
on any update that changes foster_home_id or status (#1161 — an update changing neither is a provable
no-op for the count, so the WHEN guard skips the recompute and its home-row lock instead of serializing
unrelated placement writes behind open same-home transactions).
It therefore stays correct across all transitions — create, planned → active, and end — and cannot drift.
The trigger locks the home row (PERFORM … FOR UPDATE) in its own statement before the recount (#1117):
under READ COMMITTED, a recompute UPDATE that instead blocked on the home row directly would resume via
EvalPlanQual with its original statement snapshot — writing a COUNT that predates the very commit it waited
for (the #1056 stale-clobber forensics: a third transaction re-wrote occupancy 2 after two endings had
committed). Lock-first forces the wait before the counting statement begins, so the recount’s snapshot
always postdates the peer’s commit. Any future COUNT-maintaining trigger must follow this lock-first shape
(deterministic pin: tests/constraints/occupancy_recompute_race.rs).
Activation (create-active or planned → active) is capacity-guarded under a SELECT … FOR UPDATE lock.
As a DB-level backstop (#861), CHECK (current_occupancy >= 0 AND current_occupancy ⇐ max_capacity) and
CHECK (max_capacity >= 0) reject any write — from any present or future writer — that would drive occupancy
negative or past licensed capacity.
Cross-service references to cases.cases(id) and cases.persons(id) are stored as plain UUIDs — no foreign key constraints across databases.
Tables
| Table | Purpose |
|---|---|
|
Licensed foster care providers with capacity, age range, and ICWA compliance tracking |
|
Training records for foster parents (pre-service, CPR, trauma-informed, etc.) |
|
Child placement records with AFCARS-required fields (removal, CTW, reasonable efforts) |
|
Relative/kinship placements evaluated during the matching process |
|
Tracks whether siblings are placed together or separated |
|
Educational enrollment and IEP/Section 504 tracking per child (Phase 4 expansion) |
|
Health visits, immunizations, diagnoses, and overdue-appointment tracking per child (Phase 4 expansion) |
|
Uploaded documents (licenses, home-study reports, background checks) per foster home, stored in object storage via |
|
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 |
|
#1213: the event-projected authoritative case assignment
( |
Key Indexes
-
idx_placements_case— find placements by case -
idx_placements_child— find placements by child -
idx_placements_foster_home— find placements by home -
idx_placements_status— filter by placement status -
idx_foster_homes_admin_unit— admin-unit-based home search (county/tribal region) -
idx_foster_homes_status— license status filtering (matching algorithm) -
idx_kinship_options_case— kinship options by case -
idx_education_records_child— educational history by child -
idx_health_records_child— health visit history by child -
idx_health_records_next_due— partial index onnext_due_date < now()for the overdue-appointments endpoint -
idx_home_documents_foster_home— documents by foster home