japan: use_for_ai via side table vendor_ai_flags (NO ALTER on hot vendor_registry) — ends the lock contention; inserts/updates take only row locks. Verified on local mirror

23c55145b63cb5a8af91c4ba9143dc89a6efba72 · 2026-07-06 17:43:36 -0700 · Steve
Files touched

M db/load-distributor-lines.js
Diff

commit 23c55145b63cb5a8af91c4ba9143dc89a6efba72
Author: Steve <steve@designerwallcoverings.com>
Date:   Mon Jul 6 17:43:36 2026 -0700

    japan: use_for_ai via side table vendor_ai_flags (NO ALTER on hot vendor_registry) — ends the lock contention; inserts/updates take only row locks. Verified on local mirror
---
 db/load-distributor-lines.js | 33 +++++++++++----------------------
 1 file changed, 11 insertions(+), 22 deletions(-)

diff --git a/db/load-distributor-lines.js b/db/load-distributor-lines.js
index 67776b9..f6dcb51 100644
--- a/db/load-distributor-lines.js
+++ b/db/load-distributor-lines.js
@@ -21,25 +21,11 @@ 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();
-  // 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) {
-    // busy prod table → try a few times to catch a brief lock gap (each attempt fails fast).
-    let added = false;
-    for (let i = 1; i <= 8 && !added; i++) {
-      await db.query("SET lock_timeout='2s'");
-      try { await db.query('ALTER TABLE vendor_registry ADD COLUMN use_for_ai boolean DEFAULT false'); added = true; }
-      catch (e) { console.error(`  ALTER attempt ${i}/8: ${e.message}`); await new Promise((r) => setTimeout(r, 3000)); }
-    }
-    if (!added) {
-      console.error('Could not add use_for_ai (vendor_registry stayed busy). If an OLD hung ALTER is holding it, run scripts/clear-hung-alter.sh first, then re-run.');
-      await db.end(); process.exit(2);
-    }
-    console.log('added column use_for_ai');
-  }
-  await db.query("SET lock_timeout='4s'");   // keep row-level writes from hanging too
+  await db.query("SET lock_timeout='4s'");   // row writes fail fast rather than hang
+  // use_for_ai lives in a SIDE table — NO ALTER on the hot vendor_registry (that ALTER needs
+  // an ACCESS EXCLUSIVE lock and was queue-blocking the live app). CREATE TABLE is independent.
+  await db.query(`create table if not exists vendor_ai_flags (
+    vendor_code text primary key, use_for_ai boolean default true, added_at timestamptz default now())`);
 
   // 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));
@@ -60,17 +46,20 @@ const ALLOWED = 'ACEGHIJKLOPQRTUWXYZ'.split(''); // no B,D,S,F,M,N,V (hard to he
       id = ex.rows[0].id;
       prefix = ex.rows[0].sku_prefix || nextPrefix();
       rstart = null; // keep existing range on update
-      await db.query('update vendor_registry set use_for_ai=true, is_active=false, catalog_table=$2, sku_prefix=coalesce(sku_prefix,$3), updated_at=now() where vendor_code=$1',
+      await db.query('update vendor_registry set is_active=false, catalog_table=$2, sku_prefix=coalesce(sku_prefix,$3), updated_at=now() where vendor_code=$1',
         [code, 'sangetsu_catalog', prefix]);
     } else {
       prefix = nextPrefix();
       range += 5000; rstart = range;
       const ins = await db.query(
-        `insert into vendor_registry (vendor_name,vendor_code,catalog_table,sku_prefix,sku_range_start,is_active,is_private_label,use_for_ai,display_prices,created_at,updated_at)
-         values ($1,$2,'sangetsu_catalog',$3,$4,false,false,true,false,now(),now()) returning id`,
+        `insert into vendor_registry (vendor_name,vendor_code,catalog_table,sku_prefix,sku_range_start,is_active,is_private_label,display_prices,created_at,updated_at)
+         values ($1,$2,'sangetsu_catalog',$3,$4,false,false,false,now(),now()) returning id`,
         [name, code, prefix, rstart]);
       id = ins.rows[0].id;
     }
+    // use_for_ai (side table) + keep_offline (vendor_never_on_shopify)
+    await db.query(`insert into vendor_ai_flags (vendor_code,use_for_ai) values ($1,true)
+       on conflict (vendor_code) do update set use_for_ai=true`, [code]);
     await db.query(
       `insert into vendor_never_on_shopify (vendor_code,reason,added_at)
        select $1,'internal distributor line — keep offline (Sangetsu distribution)',now()
← ffe7b6f japan: clear-hung-alter.sh (cancel a stuck vendor_registry A · back to Japan Enrich · japan: loader creates vendor_never_on_shopify if missing (ca a3d28d9 →