← back to Homesonspec

O3: fix the two bugs the live run exposed (FK-before-attach stall, missing GRANTs)

cc0a05161d46cfa05a6fff6940cd7a934f8dacda · 2026-09-10 11:39:13 -0700 · Steve

The conversion succeeded on prod but caused two problems the 20k-row scratch
test was too small to expose. Both are now fixed in the script.

1. FK-before-ATTACH stalled the live site ~7 minutes.
   Adding the foreign key to the partitioned parent BEFORE attaching makes
   Postgres re-validate that FK against every row of the incoming partition --
   170M rows joined against StagedRecord -- while holding ACCESS EXCLUSIVE.
   Peak 33 app queries blocked, oldest waiting 396s. lock_timeout did not help
   because the lock was already acquired; the cost was in the work, not the
   wait. The legacy partition already carries its own valid FK and forward
   partitions are empty, so per-partition FKs are correct and instant.

2. Missing GRANTs 500'd the public home detail page.
   A table created by postgres has no ACL for the app role, so
   prisma.sourceEvidence.findMany() failed with 42501 'permission denied for
   table SourceEvidence' on /homes/[id]. The script now grants on the parent,
   the default partition and every forward partition, and sets ALTER DEFAULT
   PRIVILEGES so future partitions inherit the grant instead of silently
   breaking writes to a new range.

Also adds an app-health assertion to VERIFY: it fetches a real published home
detail page and aborts unless it returns 200. The original script verified the
database thoroughly and never once checked that the application still worked,
which is why a 500 reached users.

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

Files touched

• M ops/o3-apply.sh

Diff

commit cc0a05161d46cfa05a6fff6940cd7a934f8dacda
Author: Steve <steve@designerwallcoverings.com>
Date:   Thu Sep 10 11:39:13 2026 -0700

    O3: fix the two bugs the live run exposed (FK-before-attach stall, missing GRANTs)
    
    The conversion succeeded on prod but caused two problems the 20k-row scratch
    test was too small to expose. Both are now fixed in the script.
    
    1. FK-before-ATTACH stalled the live site ~7 minutes.
       Adding the foreign key to the partitioned parent BEFORE attaching makes
       Postgres re-validate that FK against every row of the incoming partition --
       170M rows joined against StagedRecord -- while holding ACCESS EXCLUSIVE.
       Peak 33 app queries blocked, oldest waiting 396s. lock_timeout did not help
       because the lock was already acquired; the cost was in the work, not the
       wait. The legacy partition already carries its own valid FK and forward
       partitions are empty, so per-partition FKs are correct and instant.
    
    2. Missing GRANTs 500'd the public home detail page.
       A table created by postgres has no ACL for the app role, so
       prisma.sourceEvidence.findMany() failed with 42501 'permission denied for
       table SourceEvidence' on /homes/[id]. The script now grants on the parent,
       the default partition and every forward partition, and sets ALTER DEFAULT
       PRIVILEGES so future partitions inherit the grant instead of silently
       breaking writes to a new range.
    
    Also adds an app-health assertion to VERIFY: it fetches a real published home
    detail page and aborts unless it returns 200. The original script verified the
    database thoroughly and never once checked that the application still worked,
    which is why a 500 reached users.
    
    Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
    Claude-Session: https://claude.ai/code/session_01HpKbjp2febJ1r8BNTyZwvP
---
 ops/o3-apply.sh | 30 +++++++++++++++++++++++++++---
 1 file changed, 27 insertions(+), 3 deletions(-)

diff --git a/ops/o3-apply.sh b/ops/o3-apply.sh
index fb247e8c..23df42b9 100755
--- a/ops/o3-apply.sh
+++ b/ops/o3-apply.sh
@@ -56,9 +56,12 @@ CREATE TABLE "SourceEvidence" (
   LIKE "SourceEvidence_p_legacy" INCLUDING DEFAULTS INCLUDING STORAGE
 ) PARTITION BY RANGE ("createdAt");
 
-ALTER TABLE "SourceEvidence"
-  ADD CONSTRAINT "SourceEvidence_stagedRecordId_fkey"
-  FOREIGN KEY ("stagedRecordId") REFERENCES "StagedRecord"(id);
+-- DO NOT add the FK to the parent before ATTACH. Postgres re-validates a parent
+-- FK against EVERY row of the incoming partition (170M rows joined to
+-- StagedRecord) while holding ACCESS EXCLUSIVE -- that stalled the live site for
+-- ~7 minutes on the 2026-09-10 run. The legacy partition already carries its own
+-- valid FK, and every forward partition is empty, so per-partition FKs are both
+-- correct and instant.
 CREATE INDEX "SourceEvidence_entityType_entityId_idx" ON "SourceEvidence" ("entityType","entityId");
 CREATE INDEX "SourceEvidence_stagedRecordId_idx"      ON "SourceEvidence" ("stagedRecordId");
 
@@ -68,6 +71,20 @@ ALTER TABLE "SourceEvidence" ATTACH PARTITION "SourceEvidence_p_legacy"
 -- SAFETY NET: without a DEFAULT partition, a row whose createdAt falls outside every
 -- defined range raises "no partition of relation found" and ALL INGESTION STOPS.
 CREATE TABLE "SourceEvidence_p_default" PARTITION OF "SourceEvidence" DEFAULT;
+
+-- Per-partition FK (instant: legacy already has its own, the rest are empty).
+ALTER TABLE "SourceEvidence_p_default"
+  ADD CONSTRAINT "SourceEvidence_p_default_stagedRecordId_fkey"
+  FOREIGN KEY ("stagedRecordId") REFERENCES "StagedRecord"(id);
+
+-- CRITICAL: a table created by postgres has NO ACL for the app role, so the
+-- public page dies with 42501 "permission denied for table SourceEvidence".
+-- This is what broke /homes/[id] on the 2026-09-10 run.
+GRANT ALL PRIVILEGES ON TABLE "SourceEvidence"           TO homesonspec;
+GRANT ALL PRIVILEGES ON TABLE "SourceEvidence_p_default" TO homesonspec;
+-- and make every FUTURE partition inherit the grant automatically
+ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public
+  GRANT ALL PRIVILEGES ON TABLES TO homesonspec;
 COMMIT;
 SQL
 
@@ -77,6 +94,7 @@ for y in 2026 2027; do for m in 01 02 03 04 05 06 07 08 09 10 11 12; do
   [ "$start" \< "2026-10-01" ] && continue
   nxt=$(date -d "$start +1 month" +%Y-%m-01)
   $PSQL -c "CREATE TABLE IF NOT EXISTS \"SourceEvidence_p_${y}_${m}\" PARTITION OF \"SourceEvidence\" FOR VALUES FROM ('$start') TO ('$nxt');" >/dev/null
+  $PSQL -c "GRANT ALL PRIVILEGES ON TABLE \"SourceEvidence_p_${y}_${m}\" TO homesonspec;" >/dev/null
 done; done
 echo "forward partitions created through 2027-12"
 
@@ -94,6 +112,12 @@ $PSQL -c "SELECT count(*) AS rows_in_default FROM \"SourceEvidence_p_default\";"
 echo "--- hot path still indexed ---"
 $PSQL -c "EXPLAIN (COSTS OFF) SELECT * FROM \"SourceEvidence\" WHERE \"stagedRecordId\"='probe';" | head -8
 
+echo "--- APP HEALTH (the check the 2026-09-10 run was missing) ---"
+HID=$(Q "SELECT id FROM \"InventoryHome\" WHERE status='PUBLISHED' AND \"isDemo\"=false LIMIT 1;")
+CODE=$(curl -s -o /dev/null -w '%{http_code}' -m 25 "https://homesonspec.com/homes/$HID" || echo 000)
+echo "  /homes/$HID -> $CODE"
+[ "$CODE" = "200" ] || { echo "*** ABORT: home detail page is $CODE, not 200 -- check GRANTs (42501) ***"; exit 1; }
+
 say "DONE — O3 partition conversion complete. No data copied, no data deleted."
 cat <<'ROLL'
 ROLLBACK (catalog-only, no data movement):

← a15775f6 O3: add prod apply script for the partition conversion  ·  back to Homesonspec  ·  O3: add grant script so future partitions inherit the app-ro 0f94115d →