ops: DB reclaim runbook (TK-10878) — O1 dedup + O3 partition + revised-O2 truncate; O2-null rejected (public page renders the columns); Cody disk-abort gate

5f8d356ce1939d7f5b84c8c3293af14edfe0e53a · 2026-08-26 09:52:36 -0700 · Steve
Files touched

A ops/db-reclaim-runbook.md
Diff

commit 5f8d356ce1939d7f5b84c8c3293af14edfe0e53a
Author: Steve <steve@designerwallcoverings.com>
Date:   Wed Aug 26 09:52:36 2026 -0700

    ops: DB reclaim runbook (TK-10878) — O1 dedup + O3 partition + revised-O2 truncate; O2-null rejected (public page renders the columns); Cody disk-abort gate
---
 ops/db-reclaim-runbook.md | 68 +++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 68 insertions(+)

diff --git a/ops/db-reclaim-runbook.md b/ops/db-reclaim-runbook.md
new file mode 100644
index 00000000..d24a5a6b
--- /dev/null
+++ b/ops/db-reclaim-runbook.md
@@ -0,0 +1,68 @@
+# homesonspec DB reclaim runbook — O1 dedup + O3 partition + revised-O2 truncate
+
+**Target:** 80 GB → ~25 GB · **DB:** homesonspec (Kamatera prod) · **Ticket:** TK-10878
+**⚠️ MAINTENANCE-WINDOW ONLY. REVIEW EVERY STATEMENT. Prod-destructive steps run via a human `!` shell — the harness blocks autonomous prod DELETE/repack.**
+
+## Why the original O2 was rejected (code-verified 2026-08-26)
+The public home-detail page renders `row.evidenceText` (in an `<em>`), and `rawValue`/`normalizedValue` are also rendered in app source. **Nulling those columns blanks live customer content.** O2 is therefore REVISED to *truncate oversized* values, not null them.
+
+## Cody's HARD GATES (non-negotiable)
+- Archive to **Henry (`/Volumes/Henry`), never local**, before any delete.
+- **ABORT if prod free disk < 75 GB** before `pg_repack` (needs ~55 GB scratch; box has ~68 GB). Add disk FIRST if under.
+- Batched deletes with WAL throttle (`pg_sleep` between batches). Verify archive restores before deleting.
+- FK order: `SourceEvidence`/`ValidationEvent`/`ReviewItem` → `StagedRecord`.
+- This work is `PROD_DELETE_REQUIRED` → exempt from the 3h/<$5 aged-gate auto-run. Never auto-fires.
+
+---
+
+## STEP 0 — Preflight (read-only, safe)
+```sql
+-- measure the O1 dedup fraction (run in maintenance window; times out under load)
+SELECT count(*) total, count(DISTINCT ("stagedRecordId","field","contentHash")) distinct_evidence,
+       round(100.0*(count(*)-count(DISTINCT ("stagedRecordId","field","contentHash")))/count(*),1) pct_dup
+FROM "SourceEvidence";
+-- measure O2 truncate lever: how much TOAST is oversized evidence text?
+SELECT pg_size_pretty(sum(length("evidenceText"))) evtext,
+       pg_size_pretty(sum(length("rawValue"))) rawv,
+       count(*) FILTER (WHERE length("evidenceText")>2000) AS long_rows
+FROM "SourceEvidence" TABLESAMPLE SYSTEM (1);
+```
+```sh
+ssh root@45.61.58.125 'df -h / | tail -1'   # confirm >=75GB free before any repack
+```
+
+## STEP 1 — O1 dedup (biggest lever; destructive → maintenance window)
+```sql
+-- archive first (dumps to a file that then rsyncs to Henry)
+-- \copy (SELECT * FROM "SourceEvidence") TO '/root/backups/reclaim/sourceevidence_predup.dump' ...
+-- collapse duplicate (stagedRecordId, field, contentHash), keep newest row:
+WITH d AS (
+  SELECT id, row_number() OVER (PARTITION BY "stagedRecordId","field","contentHash"
+                                ORDER BY "createdAt" DESC) rn
+  FROM "SourceEvidence")
+DELETE FROM "SourceEvidence" se USING d
+WHERE se.id=d.id AND d.rn>1;   -- run BATCHED (add: AND se.id IN (... limit)) with pg_sleep
+-- then enforce going forward:
+CREATE UNIQUE INDEX CONCURRENTLY ux_srcev_dedup
+  ON "SourceEvidence" ("stagedRecordId","field","contentHash");
+```
+**Pipeline change (prevents regrowth):** in `apps/workers` + `packages/publisher`, change the SourceEvidence insert to `ON CONFLICT ("stagedRecordId","field","contentHash") DO NOTHING`.
+
+## STEP 2 — revised O2 truncate (only if STEP 0 shows oversized text dominates TOAST)
+```sql
+-- keep short displayed values; cap pathological ones. Confirm the page tolerates truncation.
+UPDATE "SourceEvidence" SET "evidenceText"=left("evidenceText",2000)
+WHERE length("evidenceText")>2000;   -- batched
+```
+
+## STEP 3 — O3 partition going forward (pg_partman)
+Convert `SourceEvidence` + `ValidationEvent` to monthly range partitions on `createdAt`/`runAt` via pg_partman (online, background workers). Future reclaim = `DROP PARTITION` (zero WAL). Growth (~16 GB/wk) becomes bounded + operationally trivial.
+
+## STEP 4 — reclaim space to OS
+```sh
+# ONLY after STEP 0 confirms >=75GB free:
+pg_repack -d homesonspec -t SourceEvidence -t ValidationEvent   # online, no long lock
+```
+
+## STEP 5 — add free-space canary
+Wire a homesonspec-DB-size canary into fleet-health-rollup so a regrowth to 80 GB re-alerts.
← 67563c20 auto-data-snapshot: 2026-08-24T04:45:01 (6 data files) — col · back to Homesonspec · db(homesonspec): drop unused ValidationEvent(ruleId,passed) bd19db5f →