japan: loader ALTER safety — skip if use_for_ai exists (no lock), else lock_timeout=4s so a busy vendor_registry can't hang/queue-block prod; fail-fast with a maintenance-window hint

c9bf0f6f018ffdae59f30d177ed62b2023a89d76 · 2026-07-06 17:33:47 -0700 · Steve

Files touched

M db/load-distributor-lines.js

Diff

commit c9bf0f6f018ffdae59f30d177ed62b2023a89d76
Author: Steve <steve@designerwallcoverings.com>
Date:   Mon Jul 6 17:33:47 2026 -0700

    japan: loader ALTER safety — skip if use_for_ai exists (no lock), else lock_timeout=4s so a busy vendor_registry can't hang/queue-block prod; fail-fast with a maintenance-window hint
---
 db/load-distributor-lines.js | 14 +++++++++++++-
 1 file changed, 13 insertions(+), 1 deletion(-)

diff --git a/db/load-distributor-lines.js b/db/load-distributor-lines.js
index 57c31fd..ca2d456 100644
--- a/db/load-distributor-lines.js
+++ b/db/load-distributor-lines.js
@@ -21,7 +21,19 @@ const ALLOWED = 'ACEGHIJKLOPQRTUWXYZ'.split(''); // no B,D,S,F,M,N,V (hard to he
 (async () => {
   const db = new Client({ connectionString: process.env.DATABASE_URL });
   await db.connect();
-  await db.query('ALTER TABLE vendor_registry ADD COLUMN IF NOT EXISTS use_for_ai boolean DEFAULT false');
+  // vendor_registry is a BUSY prod table — never let an ALTER hang/queue behind a lock.
+  // Skip the ALTER entirely if the column already exists (no lock needed); otherwise add it
+  // with a short lock_timeout so it fails fast instead of blocking the table.
+  const col = await db.query("select 1 from information_schema.columns where table_name='vendor_registry' and column_name='use_for_ai'");
+  if (!col.rows.length) {
+    await db.query("SET lock_timeout='4s'");
+    try { await db.query('ALTER TABLE vendor_registry ADD COLUMN use_for_ai boolean DEFAULT false'); }
+    catch (e) {
+      console.error(`ALTER add use_for_ai failed (${e.message}). vendor_registry was locked/busy — add the column in a quiet moment:\n  psql "$DATABASE_URL" -c "SET lock_timeout='10s'; ALTER TABLE vendor_registry ADD COLUMN use_for_ai boolean DEFAULT false;"\nthen re-run this loader.`);
+      await db.end(); process.exit(2);
+    }
+  }
+  await db.query("SET lock_timeout='4s'");   // keep row-level writes from hanging too
 
   // fleet-wide unique prefix pool + current range high-water mark
   const used = new Set((await db.query("select distinct upper(sku_prefix) p from vendor_registry where sku_prefix is not null")).rows.map((r) => r.p));

← 32cb9e4 japan: rewrite nginx setup — separate conf via scp (server_n · back to Japan Enrich · japan: clear-hung-alter.sh (cancel a stuck vendor_registry A ffe7b6f →