← back to Homesonspec

TK-10878: monthly SourceEvidence VACUUM/REINDEX maintenance (ready+gated) + plan PHASE 3 delivered

b9a44831508eb0e28db78eb508328689aa39969e · 2026-08-31 14:50:14 -0700 · Steve

Files touched

• M ops/o3-partition-and-disk-plan.md
• A ops/phase2-drafts/preflight.sh
• A ops/sourceevidence-maintenance.sh
• A ops/sourceevidence-maintenance.sql

Diff

commit b9a44831508eb0e28db78eb508328689aa39969e
Author: Steve <steve@designerwallcoverings.com>
Date:   Mon Aug 31 14:50:14 2026 -0700

    TK-10878: monthly SourceEvidence VACUUM/REINDEX maintenance (ready+gated) + plan PHASE 3 delivered
---
 ops/o3-partition-and-disk-plan.md  |  40 +++++++++++++--
 ops/phase2-drafts/preflight.sh     |  36 +++++++++++++
 ops/sourceevidence-maintenance.sh  |  78 ++++++++++++++++++++++++++++
 ops/sourceevidence-maintenance.sql | 101 +++++++++++++++++++++++++++++++++++++
 4 files changed, 251 insertions(+), 4 deletions(-)

diff --git a/ops/o3-partition-and-disk-plan.md b/ops/o3-partition-and-disk-plan.md
index ee406603..0d6e3b56 100644
--- a/ops/o3-partition-and-disk-plan.md
+++ b/ops/o3-partition-and-disk-plan.md
@@ -13,6 +13,22 @@ pg_partman's conversion of an **existing** large table (`partition_data_proc`) r
 - Verify after grow: `df -h /` shows ≥75 GB free before any conversion (also unblocks the pg_repack gate if ever needed).
 
 ### PHASE 2 — pg_partman monthly range partitions + retention (gated: prod DDL, maintenance window)
+
+> **STATUS 2026-09-01 — DEFERRED (runbook-ready), per DTD verdict C (7/8).** After the disk grew,
+> the panel ruled AGAINST running the in-place conversion now. Rationale: retention is
+> provenance-DISABLED, so partitioning reclaims **zero** space — the crisis (box refilling) was
+> already solved by the disk grow, leaving only an operability benefit that is **not biting** at
+> ~55 GB today, bought at the cost of an irreversible rename→DROP window on the one legal-provenance
+> table we cannot corrupt. Dissent: Codex (A — "prep is staged, fire it now"); even the contrarian
+> tasked to defend running it concluded FIX-FIRST. **The hardened migration drafts stay as the
+> on-demand RUNBOOK, not cancelled.** Instead: (1) a size/growth canary + (2) monthly index/vacuum
+> maintenance (both below / in phase2-drafts).
+>
+> **PULL-THE-RUNBOOK TRIGGERS (any one justifies running Phase 2 later):** autovacuum can't keep up
+> on SourceEvidence · index bloat >~30% · a measured read-latency regression traceable to table size
+> · retention gets turned ON (the only thing that makes partitioning reclaim space). Runbook +
+> preflight: `ops/phase2-drafts/` (migration.sql · retention · README.md · preflight.sh).
+
 Targets: `SourceEvidence` on `createdAt`, `ValidationEvent` on `runAt` (monthly RANGE).
 **DRAFTS READY (not applied):** `ops/phase2-drafts/` —
 - `20260901000000_partition_sourceevidence_by_month/migration.sql` — the pg_partman conversion.
@@ -37,13 +53,29 @@ Notes:
 6. [ ] **Post-cutover verify:** `count(*)` on the new partitioned parent **==** the pre-conversion source count, `SourceEvidence_old` count **== 0**, and `partman.check_default(...)` shows a contiguous partition set with no gap at `now()` — BEFORE the gated `DROP TABLE "SourceEvidence_old"`.
 7. [ ] **Prisma:** `prisma migrate resolve --applied` recorded so drift-detection won't revert it.
 
-### PHASE 3 — STEP 5 size canary (gated on Steve provisioning prod-read connectivity)
-homesonspec DB-size canary → fleet-health-rollup (PASS/WARN/FAIL, `data/latest.json`), WARN on regrowth past a threshold. Mac2 is classifier-blocked from prod, so it must either run on Kamatera or use a Steve-provisioned read-only conn. Author when connectivity exists.
+### PHASE 3 — the two standing guards (DELIVERED 2026-09-01, per DTD verdict C)
+
+**(A) Size/growth canary — BUILT.** `~/.claude/skills/homesonspec-size-canary/` watches
+`SourceEvidence` size, total DB size, and week-over-week growth, projects the free-space
+RUNWAY vs the grown headroom, and WARNs when runway < 8 weeks (configurable) or a size
+ceiling is crossed. Emits fleet-health-rollup PASS/WARN/FAIL to `data/latest.json`. Mac2
+is classifier-blocked from prod SSH unattended, so it reads size from EITHER
+`HOS_SIZE_PG_URL` (a Steve-provisioned read-only DSN — preferred) OR opt-in `HOS_SIZE_SSH=1`;
+with NEITHER it emits **UNKNOWN / status WARN "prod-read connectivity not provisioned"** —
+never a false PASS. **Steve action to arm it:** provision a read-only DSN into the skill's
+`.env` (or a Kamatera-side schedule), then install the weekly launchd/cron job.
+
+**(B) Monthly index/vacuum maintenance — DRAFTED (gated).** `VACUUM (ANALYZE)` + heaviest-index
+`REINDEX INDEX CONCURRENTLY` (no FULL), the in-place operability maintenance that replaces the
+deferred partitioning. Ready script + wrapper: `ops/sourceevidence-maintenance.sql` +
+`ops/sourceevidence-maintenance.sh`. Runs on prod → GATED: install command (systemd timer /
+cron) + the DRY_RUN preflight that identifies the heaviest indexes are in
+`pending-approval/2026-09-01-TK-10878-sourceevidence-monthly-maintenance.md`.
 
 ## Sequencing summary
 1. **Now (gated, ready):** apply STEP 1 index-drop (~508 MB) — `pending-approval/2026-08-26-TK-10878-step1-index-drop-APPLY.md`.
 2. **Next (gated):** PHASE 1 add disk (+≥100 GB).
-3. **Then (gated, maintenance window):** PHASE 2 pg_partman conversion.
-4. **After connectivity:** PHASE 3 size canary.
+3. **Then (gated, maintenance window):** PHASE 2 pg_partman conversion (deferred; runbook-ready).
+4. **Standing (DELIVERED):** PHASE 3 size canary (arm with prod-read conn) + monthly VACUUM/REINDEX maintenance (gated install memo).
 
 Unused-PKEY drop (~10.4 GB, @id data-model change) is explicitly NOT on this path (Steve chose partition+disk over the higher-risk pkey change).
diff --git a/ops/phase2-drafts/preflight.sh b/ops/phase2-drafts/preflight.sh
new file mode 100644
index 00000000..9b68da4b
--- /dev/null
+++ b/ops/phase2-drafts/preflight.sh
@@ -0,0 +1,36 @@
+#!/usr/bin/env bash
+# TK-10878 Phase 2 preflight — READ-ONLY. Gathers the facts that finalize the migration.
+# Run: ssh root@45.61.58.125 'bash -s' < preflight.sh
+set -uo pipefail
+echo "==================== 1. DISK (need >=75 GB free) ===================="
+df -h / | tail -1
+echo
+echo "==================== 2. DB holding SourceEvidence ===================="
+DB=$(sudo -u postgres psql -Atqc "SELECT datname FROM pg_database WHERE datistemplate=false AND datname<>'postgres'" \
+  | while read -r d; do
+      sudo -u postgres psql -Atqc "SELECT to_regclass('public.\"SourceEvidence\"') IS NOT NULL" -d "$d" 2>/dev/null \
+        | grep -qx t && { echo "$d"; break; }
+    done)
+echo "DB = ${DB:-<not found>}"
+[ -z "${DB:-}" ] && { echo "!! Could not locate the SourceEvidence table — stop and tell Steve the DB name."; exit 1; }
+echo
+echo "==================== 3. pg_partman version (sets p_type) ===================="
+echo "   5.x -> keep p_type 'range' (default) | 4.x -> change to 'partman' | empty -> extension not installed yet"
+sudo -u postgres psql -d "$DB" -c "SELECT extversion AS pg_partman_version FROM pg_extension WHERE extname='pg_partman'"
+echo
+echo "==================== 4. current PK (draft widens (id) -> (id,createdAt)) ===================="
+sudo -u postgres psql -d "$DB" -c "SELECT conname, pg_get_constraintdef(oid) AS current_pk FROM pg_constraint WHERE conrelid='public.\"SourceEvidence\"'::regclass AND contype='p'"
+echo "   (confirm nothing FK-references SourceEvidence:)"
+sudo -u postgres psql -d "$DB" -c "SELECT conrelid::regclass AS referencing_table, conname FROM pg_constraint WHERE confrelid='public.\"SourceEvidence\"'::regclass AND contype='f'"
+echo
+echo "==================== 5. p_start_partition + total rows ===================="
+sudo -u postgres psql -d "$DB" -c "SELECT date_trunc('month',min(\"createdAt\"))::date AS p_start_partition, count(*) AS total_rows, pg_size_pretty(pg_total_relation_size('public.\"SourceEvidence\"')) AS table_size FROM public.\"SourceEvidence\""
+echo
+echo "==================== 6. import-sweep activity (should be ~0 in the window) ===================="
+sudo -u postgres psql -d "$DB" -c "SELECT count(*) AS inserts_last_2min FROM public.\"SourceEvidence\" WHERE \"createdAt\" > now() - interval '2 minutes'"
+echo
+echo "==================== 7. newest backup (this IS the rollback) ===================="
+ls -lt /var/backups/*homesonspec* /root/backups/* /var/lib/postgresql/backups/* 2>/dev/null | head -3 \
+  || echo "   no obvious dump found in the usual dirs — confirm where the pg_dump lands + that one is fresh since the last import"
+echo
+echo "==================== PREFLIGHT DONE — paste all of the above back ===================="
diff --git a/ops/sourceevidence-maintenance.sh b/ops/sourceevidence-maintenance.sh
new file mode 100755
index 00000000..cc91c054
--- /dev/null
+++ b/ops/sourceevidence-maintenance.sh
@@ -0,0 +1,78 @@
+#!/bin/bash
+# ============================================================================
+# TK-10878 — Monthly SourceEvidence maintenance wrapper. GATED (runs on PROD).
+# Intended to run ON Kamatera (prod-local psql), driven by launchd/cron in a
+# low-traffic window. NOT installed by this repo — Steve installs it (see the
+# pending-approval memo). This wrapper is a SAFE HARNESS around
+# sourceevidence-maintenance.sql: it logs, guards, and cleans up an interrupted
+# CONCURRENTLY rebuild; it never runs VACUUM FULL and never drops a valid index.
+# ============================================================================
+set -uo pipefail
+HERE="$(cd "$(dirname "$0")" && pwd)"
+DB="${HOS_DB:-homesonspec}"
+PSQL_CONN="${HOS_PSQL_CONN:--d $DB}"     # e.g. HOS_PSQL_CONN="-d homesonspec -U postgres"
+LOGDIR="${HOS_LOGDIR:-$HERE/maintenance-logs}"; mkdir -p "$LOGDIR"
+TS="$(date +%Y%m%d-%H%M%S)"; LOG="$LOGDIR/maint-$TS.log"
+DRY_RUN="${DRY_RUN:-0}"
+log(){ echo "[$(date -Iseconds)] $1" | tee -a "$LOG"; }
+
+log "SourceEvidence monthly maintenance starting (db=$DB, dry_run=$DRY_RUN)"
+
+# ── Guard 1: table exists ───────────────────────────────────────────────────
+EXISTS=$(psql $PSQL_CONN -tA -c "SELECT to_regclass('public.\"SourceEvidence\"') IS NOT NULL;" 2>>"$LOG")
+if [ "$EXISTS" != "t" ]; then
+  log "ABORT: SourceEvidence not found (partitioned/renamed?). If Phase 2 ran, retarget the parent."
+  exit 2
+fi
+
+# ── Guard 2: clean up any INVALID index leftover from a prior interrupted run ──
+# An interrupted REINDEX ... CONCURRENTLY leaves an invalid "*_ccnew" index that
+# wastes space and would trip a fresh REINDEX. Drop only INVALID ones (never a
+# valid index) BEFORE this run.
+INVALID=$(psql $PSQL_CONN -tA -c \
+  "SELECT c.relname FROM pg_index i JOIN pg_class c ON c.oid=i.indexrelid JOIN pg_class t ON t.oid=i.indrelid WHERE t.relname='SourceEvidence' AND NOT i.indisvalid;" 2>>"$LOG")
+if [ -n "$INVALID" ]; then
+  log "found leftover INVALID indexes from a prior interrupted run: $(echo "$INVALID"|tr '\n' ' ')"
+  if [ "$DRY_RUN" = "1" ]; then
+    log "DRY_RUN: would DROP INDEX CONCURRENTLY each invalid index above"
+  else
+    while IFS= read -r idx; do
+      [ -z "$idx" ] && continue
+      log "DROP INDEX CONCURRENTLY \"$idx\" (invalid leftover)"
+      psql $PSQL_CONN -c "DROP INDEX CONCURRENTLY IF EXISTS \"$idx\";" >>"$LOG" 2>&1
+    done <<< "$INVALID"
+  fi
+fi
+
+# ── Run the maintenance SQL ─────────────────────────────────────────────────
+if [ "$DRY_RUN" = "1" ]; then
+  log "DRY_RUN: would run $HERE/sourceevidence-maintenance.sql (Step 0 preflight, then VACUUM ANALYZE + REINDEX)"
+  log "DRY_RUN: printing Step 0 index inventory only (read-only) —"
+  psql $PSQL_CONN -f <(sed -n '/STEP 0/,/STEP 1/p' "$HERE/sourceevidence-maintenance.sql") 2>&1 | tee -a "$LOG"
+  log "DRY_RUN complete — no VACUUM/REINDEX executed."
+  exit 0
+fi
+
+log "running sourceevidence-maintenance.sql"
+psql $PSQL_CONN -f "$HERE/sourceevidence-maintenance.sql" 2>&1 | tee -a "$LOG"
+RC=${PIPESTATUS[0]}
+# ON_ERROR_STOP is off (so one failed REINDEX doesn't abort the rest) — that means
+# psql's exit code can be 0 even if a statement errored. Scan the log for ERROR
+# lines and elevate rc so a partial-maintenance failure is surfaced, not silent
+# (codex-flagged). The Step-3 invalid-index check is the second backstop.
+if grep -qE '^psql:.*ERROR|^ERROR:' "$LOG"; then
+  log "WARN: psql reported ERROR line(s) — maintenance was PARTIAL. Review $LOG:"
+  grep -nE '^psql:.*ERROR|^ERROR:' "$LOG" | tee -a "$LOG"
+  [ "$RC" -eq 0 ] && RC=1
+fi
+
+# ── Post-check: fail loudly if an index was left INVALID by this run ─────────
+INVALID_AFTER=$(psql $PSQL_CONN -tA -c \
+  "SELECT c.relname FROM pg_index i JOIN pg_class c ON c.oid=i.indexrelid JOIN pg_class t ON t.oid=i.indrelid WHERE t.relname='SourceEvidence' AND NOT i.indisvalid;" 2>>"$LOG")
+if [ -n "$INVALID_AFTER" ]; then
+  log "WARN: run left INVALID index(es): $(echo "$INVALID_AFTER"|tr '\n' ' ') — next run's Guard 2 will DROP + a manual re-REINDEX is needed."
+  RC=1
+fi
+
+log "SourceEvidence maintenance finished (rc=$RC). Log: $LOG"
+exit "$RC"
diff --git a/ops/sourceevidence-maintenance.sql b/ops/sourceevidence-maintenance.sql
new file mode 100644
index 00000000..208a92e9
--- /dev/null
+++ b/ops/sourceevidence-maintenance.sql
@@ -0,0 +1,101 @@
+-- ============================================================================
+-- TK-10878 — Monthly index/vacuum maintenance for "SourceEvidence"
+-- READY SCRIPT — GATED. Runs on PROD Postgres (Kamatera 45.61.58.125). NOT auto-run.
+-- ============================================================================
+--
+-- WHY: DTD verdict C deferred the pg_partman conversion, so SourceEvidence stays
+-- ONE ~55 GB / ~122M-row insert-only heap. Insert-only + provenance-DISABLED
+-- retention means autovacuum rarely gets a compelling trigger (few dead tuples),
+-- yet the table still needs periodic ANALYZE (keep the planner's stats honest on a
+-- table that grows ~16 GB/wk) and its indexes still bloat over time. This is the
+-- lightweight, in-place operability maintenance that replaces the deferred
+-- partitioning — run monthly in a low-traffic window.
+--
+-- SCOPE: SourceEvidence only. ValidationEvent (rebuildable) can be added later if
+-- its stats/bloat warrant it; not included here to keep the window short.
+--
+-- SAFETY / DESIGN:
+--   * VACUUM (ANALYZE) — NO FULL. Plain VACUUM never takes ACCESS EXCLUSIVE and
+--     does NOT rewrite the table, so imports keep running. VACUUM FULL would
+--     rewrite + lock the whole 55 GB table — NEVER use it here.
+--   * REINDEX INDEX CONCURRENTLY — rebuilds each index WITHOUT an ACCESS EXCLUSIVE
+--     lock on the table (PG12+). It needs transient scratch ≈ the index size and
+--     will fail (leaving an INVALID _ccnew index) if it can't get a brief lock or
+--     is killed — the runbook/wrapper handles cleanup of any leftover invalid index.
+--   * This file does NOT hardcode index names. PostgreSQL auto-names Prisma
+--     indexes ("SourceEvidence_entityType_entityId_idx",
+--     "SourceEvidence_stagedRecordId_idx", PK "SourceEvidence_pkey"), but which are
+--     "heaviest" (most bloated / largest) is a PROD FACT the operator must read
+--     first (Step 0). Rebuilding ALL of them blindly is wasteful; rebuild the ones
+--     the preflight names.
+--
+-- HOW TO RUN: this file is written to be sourced AFTER the operator has run Step 0
+-- and knows which indexes to rebuild. Each REINDEX is its OWN statement so a failure
+-- on one does not abort the rest. Do NOT wrap the REINDEXes in a transaction —
+-- REINDEX ... CONCURRENTLY cannot run inside a transaction block.
+-- ============================================================================
+
+\set ON_ERROR_STOP off
+\timing on
+
+-- ---------------------------------------------------------------------------
+-- STEP 0 (PREFLIGHT — run this FIRST, read the output, THEN edit Step 2 below).
+-- Identify the SourceEvidence indexes and rank them by size + estimated bloat, so
+-- the REINDEX list is evidence-driven, not guessed. (Bloat estimate = leaf density
+-- proxy; the pgstattuple extension gives an exact number if it's installed.)
+-- ---------------------------------------------------------------------------
+\echo '=== STEP 0: SourceEvidence indexes by size (rebuild the biggest / most-bloated) ==='
+SELECT
+  i.indexrelid::regclass                              AS index_name,
+  pg_size_pretty(pg_relation_size(i.indexrelid))      AS index_size,
+  idx.idx_scan                                        AS index_scans,
+  i.indisprimary                                      AS is_pk
+FROM pg_index i
+JOIN pg_class c              ON c.oid = i.indrelid
+JOIN pg_stat_user_indexes idx ON idx.indexrelid = i.indexrelid
+WHERE c.relname = 'SourceEvidence'
+ORDER BY pg_relation_size(i.indexrelid) DESC;
+
+-- OPTIONAL exact bloat (only if pgstattuple is available on prod):
+--   CREATE EXTENSION IF NOT EXISTS pgstattuple;
+--   SELECT 'SourceEvidence_entityType_entityId_idx' AS idx,
+--          (SELECT avg_leaf_density FROM pgstatindex('"SourceEvidence_entityType_entityId_idx"'));
+-- A low avg_leaf_density (e.g. < 70) is the real "this index is bloated, rebuild it" signal.
+
+-- ---------------------------------------------------------------------------
+-- STEP 1 — VACUUM (ANALYZE). Reclaims any dead tuples + refreshes planner stats.
+-- Non-blocking (no ACCESS EXCLUSIVE); safe to run with imports live, though the
+-- low-traffic window keeps it fast. VERBOSE so the log shows what it did.
+-- ---------------------------------------------------------------------------
+\echo '=== STEP 1: VACUUM (ANALYZE) SourceEvidence ==='
+VACUUM (ANALYZE, VERBOSE) "SourceEvidence";
+
+-- ---------------------------------------------------------------------------
+-- STEP 2 — REINDEX the heaviest indexes CONCURRENTLY.
+-- EDIT THIS LIST based on Step 0's output. The three lines below are the
+-- CURRENT full index set for SourceEvidence (per schema.prisma at authoring
+-- time) — KEEP the ones Step 0 flagged as large/bloated, COMMENT OUT the rest.
+-- REINDEX ... CONCURRENTLY must each be its own top-level statement (cannot be in
+-- a transaction), which is why they are listed individually.
+-- ---------------------------------------------------------------------------
+\echo '=== STEP 2: REINDEX INDEX CONCURRENTLY (edit the list per Step 0) ==='
+REINDEX INDEX CONCURRENTLY "SourceEvidence_entityType_entityId_idx";
+REINDEX INDEX CONCURRENTLY "SourceEvidence_stagedRecordId_idx";
+-- The PK is usually the largest index on this table. Rebuild it only if Step 0
+-- shows it bloated — a cuid PK on an insert-only table bloats slowly:
+-- REINDEX INDEX CONCURRENTLY "SourceEvidence_pkey";
+
+-- ---------------------------------------------------------------------------
+-- STEP 3 — POST-CHECK. Confirm no index was left INVALID by an interrupted
+-- CONCURRENTLY rebuild (an INVALID index still exists but isn't used by the
+-- planner and wastes space). If any row returns, DROP the leftover _ccnew index
+-- (see the wrapper / memo) and re-run that one REINDEX.
+-- ---------------------------------------------------------------------------
+\echo '=== STEP 3: any INVALID indexes left behind? (should be zero rows) ==='
+SELECT c.relname AS invalid_index
+FROM pg_index i
+JOIN pg_class c   ON c.oid = i.indexrelid
+JOIN pg_class t   ON t.oid = i.indrelid
+WHERE t.relname = 'SourceEvidence' AND NOT i.indisvalid;
+
+\echo '=== SourceEvidence maintenance complete ==='

← ddb43e67 TK-10878 Phase 2 drafts: fix 3 fatal + 2 hardening review fi  ·  back to Homesonspec  ·  defer SourceEvidence reindex pending bloat evidence c97856c6 →