O3b: ValidationEvent partition conversion script (corrected pattern)

3e4d499fd37b0423a02594a0977ba9b9f9825f63 · 2026-09-10 12:29:35 -0700 · Steve

Second half of O3. Same ATTACH path as SourceEvidence but built on the
corrected pattern from today's run rather than repeating its mistakes:

- per-partition FKs, never a parent FK before ATTACH (that forced a 170M-row
  revalidation under ACCESS EXCLUSIVE and stalled the site for ~7 minutes)
- GRANTs on the parent and every partition, and the run aborts if any partition
  is left ungranted (a postgres-created table has no app-role ACL, which 500'd
  the public page with 42501)
- verification includes an app-role query in the exact shape the admin pages
  use (SET ROLE homesonspec; WHERE passed=false ORDER BY runAt DESC LIMIT 100)

runAt is timestamp(3) without time zone, so bounds use TIMESTAMP literals
rather than the ::timestamptz casts used for SourceEvidence.

Scratch-tested: rows preserved, new rows route to the correct monthly
partition, default partition stays empty, admin query shape returns rows.

Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01HpKbjp2febJ1r8BNTyZwvP

Files touched

A ops/o3b-apply-validationevent.sh

Diff

commit 3e4d499fd37b0423a02594a0977ba9b9f9825f63
Author: Steve <steve@designerwallcoverings.com>
Date:   Thu Sep 10 12:29:35 2026 -0700

    O3b: ValidationEvent partition conversion script (corrected pattern)
    
    Second half of O3. Same ATTACH path as SourceEvidence but built on the
    corrected pattern from today's run rather than repeating its mistakes:
    
    - per-partition FKs, never a parent FK before ATTACH (that forced a 170M-row
      revalidation under ACCESS EXCLUSIVE and stalled the site for ~7 minutes)
    - GRANTs on the parent and every partition, and the run aborts if any partition
      is left ungranted (a postgres-created table has no app-role ACL, which 500'd
      the public page with 42501)
    - verification includes an app-role query in the exact shape the admin pages
      use (SET ROLE homesonspec; WHERE passed=false ORDER BY runAt DESC LIMIT 100)
    
    runAt is timestamp(3) without time zone, so bounds use TIMESTAMP literals
    rather than the ::timestamptz casts used for SourceEvidence.
    
    Scratch-tested: rows preserved, new rows route to the correct monthly
    partition, default partition stays empty, admin query shape returns rows.
    
    Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
    Claude-Session: https://claude.ai/code/session_01HpKbjp2febJ1r8BNTyZwvP
---
 ops/o3b-apply-validationevent.sh | 88 ++++++++++++++++++++++++++++++++++++++++
 1 file changed, 88 insertions(+)

diff --git a/ops/o3b-apply-validationevent.sh b/ops/o3b-apply-validationevent.sh
new file mode 100755
index 00000000..d29f5b8a
--- /dev/null
+++ b/ops/o3b-apply-validationevent.sh
@@ -0,0 +1,88 @@
+#!/usr/bin/env bash
+# O3b — ValidationEvent partition conversion (ATTACH path, no table rewrite).
+# TK-11363. Uses the CORRECTED pattern from the 2026-09-10 SourceEvidence run:
+#   - per-partition FKs (a parent FK forces a full revalidation under ACCESS EXCLUSIVE)
+#   - GRANTs on parent + every partition (postgres-created tables have no app-role ACL)
+#   - an app-level assertion, not just database checks
+set -euo pipefail
+PSQL="sudo -u postgres psql -d homesonspec -v ON_ERROR_STOP=1"
+Q() { sudo -u postgres psql -d homesonspec -tAc "$1"; }
+say() { printf '\n\033[1;36m=== %s ===\033[0m\n' "$*"; }
+
+say "PREFLIGHT"
+DUMP=$(ls -t /root/backups/db/homesonspec_*.dump 2>/dev/null | head -1 || true)
+[ -n "$DUMP" ] || { echo "ABORT: no homesonspec dump"; exit 1; }
+AGE=$(( ( $(date +%s) - $(stat -c %Y "$DUMP") ) / 3600 ))
+echo "backup: $(basename "$DUMP") ${AGE}h old"
+[ "$AGE" -lt 48 ] || { echo "ABORT: dump ${AGE}h old"; exit 1; }
+FREE=$(df -BG --output=avail / | tail -1 | tr -dc '0-9')
+echo "disk free: ${FREE}G"
+[ "$FREE" -ge 15 ] || { echo "ABORT: <15G free"; exit 1; }
+BEFORE=$(Q 'SELECT count(*) FROM "ValidationEvent";')
+echo "ValidationEvent rows: $BEFORE"
+echo "max runAt: $(Q 'SELECT max("runAt") FROM "ValidationEvent";')"
+
+say "STEP 1 — bound constraint + VALIDATE (online, SHARE UPDATE EXCLUSIVE, does NOT block reads/writes)"
+$PSQL -c 'ALTER TABLE "ValidationEvent" DROP CONSTRAINT IF EXISTS ve_legacy_bound;'
+$PSQL -c "ALTER TABLE \"ValidationEvent\" ADD CONSTRAINT ve_legacy_bound CHECK (\"runAt\" < TIMESTAMP '2026-10-01 00:00:00') NOT VALID;"
+time $PSQL -c 'ALTER TABLE "ValidationEvent" VALIDATE CONSTRAINT ve_legacy_bound;'
+[ "$(Q "SELECT convalidated FROM pg_constraint WHERE conname='ve_legacy_bound';")" = "t" ] \
+  || { echo "ABORT: not validated"; exit 1; }
+echo "STEP 1 OK (rollback here: ALTER TABLE \"ValidationEvent\" DROP CONSTRAINT ve_legacy_bound;)"
+
+say "STEP 2 — swap in partitioned parent + ATTACH (catalog-only; NO parent FK => no revalidation)"
+$PSQL <<'SQL'
+BEGIN;
+SET LOCAL lock_timeout = '10s';
+ALTER TABLE "ValidationEvent" RENAME TO "ValidationEvent_p_legacy";
+ALTER INDEX "ValidationEvent_pkey"                RENAME TO "ValidationEvent_p_legacy_pkey";
+ALTER INDEX "ValidationEvent_stagedRecordId_idx"  RENAME TO "VE_p_legacy_staged_idx";
+
+-- PK-less parent: keeps the legacy partition's local PK, avoids a composite-PK change.
+CREATE TABLE "ValidationEvent" (
+  LIKE "ValidationEvent_p_legacy" INCLUDING DEFAULTS INCLUDING STORAGE
+) PARTITION BY RANGE ("runAt");
+CREATE INDEX "ValidationEvent_stagedRecordId_idx" ON "ValidationEvent" ("stagedRecordId");
+
+ALTER TABLE "ValidationEvent" ATTACH PARTITION "ValidationEvent_p_legacy"
+  FOR VALUES FROM (MINVALUE) TO (TIMESTAMP '2026-10-01 00:00:00');
+
+CREATE TABLE "ValidationEvent_p_default" PARTITION OF "ValidationEvent" DEFAULT;
+ALTER TABLE "ValidationEvent_p_default"
+  ADD CONSTRAINT "VE_p_default_stagedRecordId_fkey"
+  FOREIGN KEY ("stagedRecordId") REFERENCES "StagedRecord"(id);
+
+GRANT ALL PRIVILEGES ON TABLE "ValidationEvent"           TO homesonspec;
+GRANT ALL PRIVILEGES ON TABLE "ValidationEvent_p_default" TO homesonspec;
+COMMIT;
+SQL
+
+say "STEP 3 — forward monthly partitions through 2027-12 (+ FK + GRANT each)"
+for y in 2026 2027; do
+  for m in 01 02 03 04 05 06 07 08 09 10 11 12; do
+    s="$y-$m-01"
+    [ "$s" \< "2026-10-01" ] && continue
+    n=$(date -d "$s +1 month" +%Y-%m-01)
+    T="ValidationEvent_p_${y}_${m}"
+    $PSQL -c "CREATE TABLE IF NOT EXISTS \"$T\" PARTITION OF \"ValidationEvent\" FOR VALUES FROM (TIMESTAMP '$s 00:00:00') TO (TIMESTAMP '$n 00:00:00');" >/dev/null
+    $PSQL -c "ALTER TABLE \"$T\" ADD CONSTRAINT \"${T}_sr_fkey\" FOREIGN KEY (\"stagedRecordId\") REFERENCES \"StagedRecord\"(id);" >/dev/null 2>&1 || true
+    $PSQL -c "GRANT ALL PRIVILEGES ON TABLE \"$T\" TO homesonspec;" >/dev/null
+  done
+done
+echo "forward partitions ready"
+
+say "VERIFY"
+AFTER=$(Q 'SELECT count(*) FROM "ValidationEvent";')
+echo "rows before: $BEFORE / after: $AFTER"
+[ "$AFTER" -ge "$BEFORE" ] || { echo "*** ABORT: ROW LOSS ***"; exit 1; }
+UNGRANTED=$(Q "SELECT count(*) FROM pg_class c WHERE (c.relname='ValidationEvent' OR c.oid IN (SELECT inhrelid FROM pg_inherits WHERE inhparent=(SELECT oid FROM pg_class WHERE relname='ValidationEvent'))) AND (c.relacl IS NULL OR NOT c.relacl::text LIKE '%homesonspec%');")
+echo "partitions WITHOUT app grant (want 0): $UNGRANTED"
+[ "$UNGRANTED" = "0" ] || { echo "*** ABORT: ungranted partitions -> app will 42501 ***"; exit 1; }
+echo "rows in DEFAULT (want 0): $(Q 'SELECT count(*) FROM "ValidationEvent_p_default";')"
+echo "--- FK enforced? (expect ERROR) ---"
+sudo -u postgres psql -d homesonspec -c "INSERT INTO \"ValidationEvent\" (id,\"stagedRecordId\",\"ruleId\",severity,passed,\"validatorVersion\",\"runAt\") VALUES ('o3bprobe','__NO_SR__','r','error',false,'v',now());" 2>&1 | head -2
+echo "--- APP-ROLE query test: the exact admin page shape, as the app user ---"
+sudo -u postgres psql -d homesonspec -c "SET ROLE homesonspec; SELECT count(*) AS admin_rows FROM (SELECT id FROM \"ValidationEvent\" WHERE passed=false ORDER BY \"runAt\" DESC LIMIT 100) t;" 2>&1 | head -4
+echo "--- partition count ---"
+$PSQL -c "SELECT count(*) AS partitions FROM pg_inherits WHERE inhparent='\"ValidationEvent\"'::regclass;"
+say "DONE — ValidationEvent partitioned. No data copied, no data deleted."

← 0f94115d O3: add grant script so future partitions inherit the app-ro · back to Homesonspec · TK-11364: version + golden-lock the TK-11125 guard's canonic 31581ff8 →