docs(homesonspec): STEP 1 APPLIED on prod — record actual method + 2 gotchas (owner-role DDL, deploy over-applies add_inventory_specs) + flag 24-day pending migration (TK-10878)

7312ce4b816db26945568f2ab07ce857ca092915 · 2026-08-26 10:40:08 -0700 · Steve
Files touched

M ops/db-reclaim-runbook.md
Diff

commit 7312ce4b816db26945568f2ab07ce857ca092915
Author: Steve <steve@designerwallcoverings.com>
Date:   Wed Aug 26 10:40:08 2026 -0700

    docs(homesonspec): STEP 1 APPLIED on prod — record actual method + 2 gotchas (owner-role DDL, deploy over-applies add_inventory_specs) + flag 24-day pending migration (TK-10878)
---
 ops/db-reclaim-runbook.md | 33 ++++++++++++++++++++-------------
 1 file changed, 20 insertions(+), 13 deletions(-)

diff --git a/ops/db-reclaim-runbook.md b/ops/db-reclaim-runbook.md
index 63ece975..417519cb 100644
--- a/ops/db-reclaim-runbook.md
+++ b/ops/db-reclaim-runbook.md
@@ -9,23 +9,30 @@
 
 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).
+### STEP 1 — ✅ APPLIED 2026-08-26 (Steve-approved) — dropped unused `ValidationEvent(ruleId,passed)` index
+Migration `20260826165959_drop_unused_validationevent_ruleid_passed_idx` (commit `bd19db5`, hardened `63fa118`). Result: DB **80.09 → 79.59 GB** (~509 MB); index absent; hot `ValidationEvent_stagedRecordId_idx` retained; recorded in `_prisma_migrations` (checksum `44ec2c16…`). Undo in the executed-reversible ledger.
 
-**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.
+**Two gotchas the live apply exposed — read before repeating this pattern:**
+1. **Do NOT use `prisma migrate deploy` here.** (a) It applies ALL pending migrations — `20260802203133_add_inventory_specs` was *also* pending on prod (unapplied 24 days) and would have gone in too. (b) The correct workspace invocation is `pnpm --filter @homesonspec/database db:deploy` / `… exec prisma …` (there is no `prisma` script, so `pnpm --filter … prisma …` errors).
+2. **The `.env` `DATABASE_URL` role (`homesonspec`) can't run DDL** — `ValidationEvent` is owned by `postgres`. DDL must run as owner: `sudo -u postgres psql -d homesonspec`.
 
+**What was actually run on the box** (surgical: applies ONLY this change, leaves other pendings alone):
 ```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\"');"
+# on Kamatera /root/Projects/homesonspec, migration dir rsynced in, DB=homesonspec:
+sudo -u postgres psql -d homesonspec -v ON_ERROR_STOP=1 <<'SQL'
+BEGIN;
+SET LOCAL lock_timeout='3s';
+DROP INDEX "ValidationEvent_ruleId_passed_idx";
+INSERT INTO _prisma_migrations (id,checksum,finished_at,migration_name,logs,rolled_back_at,started_at,applied_steps_count)
+SELECT gen_random_uuid()::text,'<sha256 of migration.sql>',now(),
+       '20260826165959_drop_unused_validationevent_ruleid_passed_idx',NULL,NULL,now(),1
+WHERE NOT EXISTS (SELECT 1 FROM _prisma_migrations WHERE migration_name='20260826165959_drop_unused_validationevent_ruleid_passed_idx');
+COMMIT;
+SQL
+# verify: SELECT to_regclass('public."ValidationEvent_ruleId_passed_idx"');  -- expect null
 ```
+
+> **⚠️ SEPARATE FINDING — `20260802203133_add_inventory_specs` is UNAPPLIED on prod (24 days).** Not touched by step 1. Decide whether prod schema is intentionally behind or this needs applying (owner `postgres`); its own review before any `db:deploy` runs on this box.
 **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");
← 019ab4e8 docs(homesonspec): O3 partition + add-disk plan (Steve-chose · back to Homesonspec · pm2(homesonspec): node-direct next start so pm2 owns the por 7d5c17e6 →