japan: loader gives each distributor its OWN vendor id + unique compliant DW prefix + 5000-spaced range (proven on local mirror: 19 lines, forbidden-letter-safe, idempotent)

b34412acd8bdfb5d0970d4c63a23b6f8f7fbb82e · 2026-07-06 17:04:40 -0700 · Steve
Files touched

M db/load-distributor-lines.js
Diff

commit b34412acd8bdfb5d0970d4c63a23b6f8f7fbb82e
Author: Steve <steve@designerwallcoverings.com>
Date:   Mon Jul 6 17:04:40 2026 -0700

    japan: loader gives each distributor its OWN vendor id + unique compliant DW prefix + 5000-spaced range (proven on local mirror: 19 lines, forbidden-letter-safe, idempotent)
---
 db/load-distributor-lines.js | 46 ++++++++++++++++++++++++++++++++------------
 1 file changed, 34 insertions(+), 12 deletions(-)

diff --git a/db/load-distributor-lines.js b/db/load-distributor-lines.js
index d78f7ba..57c31fd 100644
--- a/db/load-distributor-lines.js
+++ b/db/load-distributor-lines.js
@@ -2,42 +2,64 @@
 /**
  * Load the 20 distributor internal LINES into canonical dw_unified from
  * staging/onboarding-manifest.json. GATED (Steve-approved 2026-07-06). Idempotent.
- *  - vendor_registry row per distributor (vendor_code=<slug>-sg to NOT clobber existing
- *    Koroseal/ColeSon/Harlequin vendors), is_active=false (offline), use_for_ai=true.
- *  - keep_offline → INSERT into vendor_never_on_shopify.
- * NEVER publishes to Shopify. Products stay in sangetsu_catalog (Tier 1 stage, on_shopify=false).
+ * EACH distributor gets its OWN vendor identity:
+ *   - vendor_registry row (auto id), vendor_code=<slug>-sg (won't clobber existing
+ *     Koroseal/ColeSon/Harlequin vendors),
+ *   - a UNIQUE compliant sku_prefix  (DW + 2 allowed letters; forbidden B/D/S/F/M/N/V),
+ *   - a sku_range_start spaced 5000 apart from the current max,
+ *   - is_active=false (offline), use_for_ai=true,
+ *   - keep_offline → INSERT into vendor_never_on_shopify.
+ * NEVER publishes to Shopify (products stay in sangetsu_catalog, on_shopify=false).
  *   DATABASE_URL=... node db/load-distributor-lines.js
  */
 const { Client } = require('pg');
 const fs = require('fs'), path = require('path');
 
 const man = JSON.parse(fs.readFileSync(path.join(__dirname, '..', 'staging', 'onboarding-manifest.json'), 'utf8'));
+const ALLOWED = 'ACEGHIJKLOPQRTUWXYZ'.split(''); // no B,D,S,F,M,N,V (hard to hear on the phone)
 
 (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');
+
+  // 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));
+  let range = Number((await db.query('select coalesce(max(sku_range_start),100000) m from vendor_registry')).rows[0].m);
+  const nextPrefix = () => {
+    for (const a of ALLOWED) for (const b of ALLOWED) { const p = 'DW' + a + b; if (!used.has(p)) { used.add(p); return p; } }
+    throw new Error('DW prefix space exhausted');
+  };
+
   let n = 0;
   for (const [dist, L] of Object.entries(man)) {
     if (dist === '(unassigned)') continue;
     const code = ((L.slug || dist.toLowerCase().replace(/[^a-z0-9]+/g, '-')).replace(/^-|-$/g, '')) + '-sg';
     const name = `${dist} (Sangetsu)`;
-    const ex = await db.query('select id from vendor_registry where vendor_code=$1', [code]);
+    const ex = await db.query('select id, sku_prefix from vendor_registry where vendor_code=$1', [code]);
+    let prefix, rstart, id;
     if (ex.rows.length) {
-      await db.query('update vendor_registry set use_for_ai=true, is_active=false, catalog_table=$2, updated_at=now() where vendor_code=$1',
-        [code, 'sangetsu_catalog']);
+      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',
+        [code, 'sangetsu_catalog', prefix]);
     } else {
-      await db.query(
-        `insert into vendor_registry (vendor_name,vendor_code,catalog_table,is_active,is_private_label,use_for_ai,display_prices,created_at,updated_at)
-         values ($1,$2,'sangetsu_catalog',false,false,true,false,now(),now())`, [name, code]);
+      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`,
+        [name, code, prefix, rstart]);
+      id = ins.rows[0].id;
     }
     await db.query(
       `insert into vendor_never_on_shopify (vendor_code,reason,added_at)
        select $1,'internal distributor line — keep offline (Sangetsu distribution)',now()
        where not exists (select 1 from vendor_never_on_shopify where vendor_code=$1)`, [code]);
     n++;
-    console.log(`  ${code.padEnd(28)} patterns=${L.pattern_count} skus=${L.colorway_count} use_for_ai=t keep_offline=t`);
+    console.log(`  id=${String(id).padStart(5)} ${code.padEnd(26)} prefix=${prefix} range=${rstart || '(kept)'} patterns=${L.pattern_count} skus=${L.colorway_count}`);
   }
   await db.end();
-  console.log(`\nloaded ${n} distributor internal lines (offline, use_for_ai=true). No Shopify publish.`);
+  console.log(`\nloaded ${n} distributor vendor lines — each with its own id + unique prefix + range (offline, use_for_ai=true). No Shopify.`);
 })().catch((e) => { console.error(e); process.exit(1); });
← 619957d japan: approved-execution runbook + load-distributor-lines.j · back to Japan Enrich · japan: viewer strips -sg subdomain suffix (koroseal-sg→Koros a6bb3a2 →