fix(placement): DB-anchor home-document blobs + reap orphan blobs

Summary

craig-placement uploads foster-home documents by writing the blob to the object store first and inserting the metadata row second. If the process dies or the DB insert fails after the blob write, the blob is left in storage with no DB row referencing it — an untracked orphan that no reaper ever cleans up.

craig-cases and craig-exchange already solved this with the platform-stab-2 §D4 anchored two-phase pattern (DB pending row committed before the blob write, then promote to present, with a background AttachmentScanner reaping reverse-orphans). craig-placement is the only attachment-bearing service still on the unsafe blob-first path. This is parity cleanup: the shared machinery (craig_store::AttachmentScanner, ScanTarget, ReapPolicy) already exists and just needs wiring in, plus a schema column and a reordered handler.

This is not a security/authz hole (that is the separate #977 (closed) IDOR) — it is a storage-integrity leak: orphan blobs accumulate on partial failures and are never reclaimed.

Evidence (file:line)

  • services/craig-placement/src/api/home_documents.rs:102-105 — place_upload_object writes the blob (object_store.put(&object_key, ...)).
  • services/craig-placement/src/api/home_documents.rs:150-173 — upload_document calls place_upload_object (blob write) at 150-151, then insert_document (DB row) at 158-173. Blob-first ordering; a failure between them orphans the blob.
  • services/craig-placement/src/store/home_documents.rs:24-48 — insert_document is a single-phase INSERT with no object_status column.
  • services/craig-placement/migrations/20260323100000_add_home_documents.sql:2-16 — home_documents has no object_status (pending/present/failed) column.
  • services/craig-placement/src/main.rs:145-166 — spawn_workers spawns only the outbox worker + inbox subscriber; no AttachmentScanner.

Reference pattern already in the tree:

  • services/craig-cases/src/api/report_attachments.rs:133-194 — Phase 1 pending row + outbox event in a tx before the blob write; Phase 2 blob write + mark_object_present, with a compensating delete on failure.
  • services/craig-cases/src/main.rs:182-206 — AttachmentScanner over the three cases attachment tables.
  • services/craig-cases/migrations/20260505182150_attachment_object_status.sql — the object_status column + partial pending index.
  • crates/craig-store/src/scanner.rs:44-206 — reusable AttachmentScanner / ScanTarget / ReapPolicy.
  • services/craig-exchange/src/main.rs:186-191 — exchange's scanner wiring (second precedent).

Why it matters

  • Storage leak / cost + audit drift. Every failed upload after the blob lands (transient object-store error on the metadata insert, pod eviction, OOM, deploy restart) leaves a permanently orphaned blob. In a child-welfare system these blobs are PII (case documents), so untracked, unreapable copies are also a data-governance liability, not just wasted storage.
  • Inconsistent platform invariant. cases + exchange guarantee "every blob is anchored by a DB row"; placement silently violates it, so the org-wide invariant sweep / #318 (closed) test cannot pass for placement.
  • The fix is low-risk parity work, not a redesign — the shared scanner and the exact migration/handler shape already exist in two sibling services.

Recommended fix

  1. Schema: add an object_status TEXT NOT NULL CHECK (object_status IN ('pending','present','failed')) column to home_documents (mirror services/craig-cases/migrations/20260505182150_attachment_object_status.sql: DEFAULT 'present' to backfill legacy rows, then DROP DEFAULT), plus a partial index WHERE object_status = 'pending' on the timestamp column.
  2. Store: split insert_document into a ..._pending insert (status pending) and a mark_object_present promotion, matching craig-cases's report_attachments store.
  3. Handler: reorder upload_document (services/craig-placement/src/api/home_documents.rs) to insert the pending anchor row before object_store.put, then promote to present; on blob-write failure, compensate (delete the pending row / mark failed) and surface the error.
  4. Reaper: register home_documents as a craig_store::ScanTarget (ReapPolicy::Delete) and spawn an AttachmentScanner in services/craig-placement/src/main.rs spawn_workers, mirroring craig-cases/craig-exchange.
  5. Unblock #318 (closed): once shipped, the placement half of the #318 (closed) fault-injection test can be written.

Note: the timestamp column the scanner reads is uploaded_at for cases/exchange tables; home_documents uses created_at (see the table DDL), so either add the scanner's expected column or extend ScanTarget/scan_once (crates/craig-store/src/scanner.rs:194-198) to take the timestamp column name explicitly rather than the current hard-coded heuristic.

Acceptance criteria

  • home_documents has an object_status column (pending/present/failed) with a partial index over pending rows; existing rows backfill to present.
  • upload_document inserts the DB anchor row (pending) before writing the blob, then promotes to present; a blob-write failure leaves no present row and compensates the pending row.
  • A fault-injection test succeeds the blob put but fails/skips the DB promotion and asserts zero untracked/orphan blobs remain after the scanner runs (satisfies the placement half of #318 (closed)).
  • craig-placement spawns an AttachmentScanner with a home_documents ScanTarget; a reverse-orphan (pending row, missing blob) past the grace window is reaped, and a pending row whose blob is present is promoted.
  • The scanner reads the correct timestamp column for home_documents (no reliance on a nonexistent uploaded_at).
  • Docs updated where the upload contract/pattern is described; CHANGELOG.adoc == Unreleased entry added.

References

  • Related test (blocked on this fix): #318 (closed) — test(craig-cases/placement): object-store write succeeds, DB insert fails → no untracked blobs [Phase B Test 6].
  • Distinct handler defect (delete-ordering IDOR, do not conflate): #977 (closed).
  • Pattern precedent: platform-stab-2 §D4 as-built in services/craig-cases + services/craig-exchange; shared reaper crates/craig-store/src/scanner.rs.
  • Source: 2026-07 spaghetti-audit finding placement-orphan-blob.

Filed from the 2026-07-10 spaghetti audit; adversarially verified against the current tree before filing.