docs(homesonspec): runbook reflects measured reality — index-drop is real STEP 1, corrected apply/verify/rollback (TK-10878)

26657d752e8cc6d5d397ade45ea451617f2069b7 · 2026-08-26 10:18:36 -0700 · Steve
Files touched

M ops/db-reclaim-runbook.md
Diff

commit 26657d752e8cc6d5d397ade45ea451617f2069b7
Author: Steve <steve@designerwallcoverings.com>
Date:   Wed Aug 26 10:18:36 2026 -0700

    docs(homesonspec): runbook reflects measured reality — index-drop is real STEP 1, corrected apply/verify/rollback (TK-10878)
---
 ops/db-reclaim-runbook.md | 38 ++++++++++++++++++++++++++++++++++++++
 1 file changed, 38 insertions(+)

diff --git a/ops/db-reclaim-runbook.md b/ops/db-reclaim-runbook.md
index d24a5a6b..63ece975 100644
--- a/ops/db-reclaim-runbook.md
+++ b/ops/db-reclaim-runbook.md
@@ -3,6 +3,44 @@
 **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.**
 
+---
+
+## ⭐ REVISED after measurement (2026-08-26) — what ACTUALLY reclaims
+
+Read-only forensics invalidated the row-prune premise: **O1 dedup = 0% dupes, O2 = text avg 13 B and rendered on the public page, retention = all data 5 wk old.** All three reclaim ~0. The only safe reclaim is **dropping 0-scan indexes**. `DROP INDEX` truncates the index files immediately — no `VACUUM`/`pg_repack`, no disk-scratch gate.
+
+### STEP 1 (READY NOW) — drop unused `ValidationEvent(ruleId,passed)` index (~508 MB)
+Committed migration: `20260826165959_drop_unused_validationevent_ruleid_passed_idx` (commit `bd19db5`, hardened `63fa118`: `SET lock_timeout='3s'`, non-concurrent by Prisma-txn necessity).
+
+**Gate:** prod DDL on Kamatera = `PROD_DELETE_REQUIRED`. Run on the Kamatera box (prod `DATABASE_URL` lives there, not in the repo) in a low-traffic window, via Steve's shell. Not autonomously fired.
+
+```sh
+# on Kamatera, in the deployed homesonspec repo, prod DATABASE_URL loaded:
+# 1) PRE-FLIGHT — confirm ONLY this migration is pending (exits nonzero when pending = expected)
+pnpm --filter @homesonspec/database prisma migrate status
+#    -> the ONLY "not yet applied" entry must be 20260826165959_drop_unused_validationevent_ruleid_passed_idx.
+#       If any OTHER migration is also pending, STOP and review — deploy applies ALL pending migrations.
+# 2) APPLY
+pnpm --filter @homesonspec/database prisma migrate deploy
+# 3) VERIFY — index is gone (bare `psql -c` ignores DATABASE_URL; pass it explicitly). Expect: (null)
+psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -c \
+  "SELECT to_regclass('public.\"ValidationEvent_ruleId_passed_idx\"');"
+```
+**Rollback (only if the index is unexpectedly needed — it is 0-scan):** re-add `@@index([ruleId,passed])` to the model, then recreate MANUALLY, never a plain rebuild on 51 M rows:
+```sql
+CREATE INDEX CONCURRENTLY "ValidationEvent_ruleId_passed_idx" ON "ValidationEvent" ("ruleId","passed");
+```
+
+### The rest of the reclaim (scheduled, each Steve-gated)
+- **O3 partition** (biggest long-term win) — pg_partman monthly range-partition `SourceEvidence(createdAt)` + `ValidationEvent(runAt)`; bounds the ~16 GB/wk growth, future reclaim = `DROP PARTITION` (zero WAL). Schedule as its own migration.
+- **Add disk** — the 122 M-row provenance is REAL (unique, tiny-per-value, live-read); beyond indexes it is NOT prunable. Box ~90 % full (~67 GB free). Grow the volume.
+- **Unused PKEYs ~10.4 GB** — `SourceEvidence_pkey` (7.9 GB) + `ValidationEvent_pkey` (2.5 GB) are 0-scan / no FK deps; reclaiming needs changing the Prisma `@id` strategy — a higher-risk data-model change, evaluate separately.
+- **STEP 5 canary** — homesonspec DB-size canary → fleet-health-rollup so a regrowth re-alerts (needs Steve to provision prod-read connectivity; Mac2 is classifier-blocked from prod).
+
+**DEAD — do NOT pursue (measured to ~0):** row retention-prune · O1 dedup · O2 null/truncate. The historical O1/O2/O3 SQL below is retained for provenance only.
+
+---
+
 ## 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.
← 63fa1185 db(homesonspec): guard TK-10878 index-drop with lock_timeout · back to Homesonspec · docs(homesonspec): O3 partition + add-disk plan (Steve-chose 019ab4e8 →