usre: pull non-concurrent idx_broker_website_todo out of migration 017 into out-of-band CONCURRENTLY build + gate code restart on asset_class column existence (Cody gate, TK-10535)

11157a8693c4b015d4358f821d93a1afab7f8f51 · 2026-08-14 08:03:42 -0700 · Steve Abrams

Files touched

M db/migrations/017_broker_website.sql
M docs/deploy-asset-class.md

Diff

commit 11157a8693c4b015d4358f821d93a1afab7f8f51
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Fri Aug 14 08:03:42 2026 -0700

    usre: pull non-concurrent idx_broker_website_todo out of migration 017 into out-of-band CONCURRENTLY build + gate code restart on asset_class column existence (Cody gate, TK-10535)
---
 db/migrations/017_broker_website.sql | 10 ++++++++--
 docs/deploy-asset-class.md           | 11 ++++++++++-
 2 files changed, 18 insertions(+), 3 deletions(-)

diff --git a/db/migrations/017_broker_website.sql b/db/migrations/017_broker_website.sql
index ba9dda0..093641a 100644
--- a/db/migrations/017_broker_website.sql
+++ b/db/migrations/017_broker_website.sql
@@ -15,5 +15,11 @@ ALTER TABLE broker
   ADD COLUMN IF NOT EXISTS phone_source TEXT;            -- provenance of broker.phone (site-extract | registry) once populated
 
 -- Cheap "not yet attempted" lookup so the sweep advances down its priority queue.
-CREATE INDEX IF NOT EXISTS idx_broker_website_todo
-  ON broker (id) WHERE website_status IS NULL;
+-- MOVED OUT OF THIS FILE (TK-10535, Cody gate): a NON-concurrent CREATE INDEX inside the
+-- runner's transaction takes an ACCESS EXCLUSIVE lock on the ~2M-row broker table, and on a
+-- freshly-added `website_status` column the `IS NULL` predicate matches EVERY row (= a cold
+-- full-table index build) — that would take the live RENTV desk dark for minutes mid-migration.
+-- It is now built CONCURRENTLY, out-of-band, from docs/deploy-asset-class.md (same pattern as
+-- the asset_class indexes). Left here as documentation only:
+--   CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_broker_website_todo
+--     ON broker (id) WHERE website_status IS NULL;
diff --git a/docs/deploy-asset-class.md b/docs/deploy-asset-class.md
index d5cc8d1..a54f586 100644
--- a/docs/deploy-asset-class.md
+++ b/docs/deploy-asset-class.md
@@ -32,13 +32,22 @@ npx tsx db/migrate.ts
 #    lock on the ~2M-row broker table that would stall the live desk).
 psql usre -c "CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_firm_asset_class   ON firm   (asset_class);"
 psql usre -c "CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_broker_asset_class ON broker (asset_class);"
+# c2. The broker-website-todo index was PULLED OUT of migration 017 (TK-10535, Cody gate) because a
+#     non-concurrent build on the freshly-NULL website_status column matches all ~2M rows and would
+#     ACCESS-EXCLUSIVE-lock the live desk mid-migration. Build it CONCURRENTLY here (IF NOT EXISTS =
+#     no-op if the surgical-017 fix already created it on prod):
+psql usre -c "CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_broker_website_todo ON broker (id) WHERE website_status IS NULL;"
 
 # d. Backfill the tag — off-peak. Single UPDATE over ~2M broker rows; ~2 min locally. It takes a
 #    ROW-share lock (not a table lock) but is write-heavy — run when desk traffic is low.
 #    Idempotent + re-runnable; never overwrites asset_class_source='manual'.
 psql usre -f scripts/classify-asset-class.sql   # prints the firm/broker class tallies
 
-# e. Deploy usre code + restart. Expect ~4,100 commercial firms / ~18,400 commercial brokers.
+# e. GATE the code restart on the column actually existing (TK-10535, Cody HOLE 3) — usre/RENTV/CRCP
+#    all read firm.asset_class; if code restarts before 020 landed, /api/firm/:id 404s the desk blank.
+psql usre -tAc "SELECT 1 FROM information_schema.columns WHERE table_name='firm' AND column_name='asset_class';" \
+  | grep -q 1 && echo "column present -> safe to restart" || { echo "ABORT: asset_class column missing — do NOT restart"; exit 1; }
+# Deploy usre code + restart. Expect ~4,100 commercial firms / ~18,400 commercial brokers.
 pm2 restart usrealestate
 ```

← 3e868e0 nav-agent: opt-out listings.html — VERIFIED (Cycle 4) CrcpGr · back to Nationalrealestate · auto-data-snapshot: 2026-08-14T09:51:27 (4 data files) — dat 2e5a7a1 →