sangetsu vendor_code partition: brand-map codes at ingest time (stage-sangetsu-all applies slug+'-sg', flat 'sangetsu' would duplicate rows on re-scrape) + committed idempotent backfill script documenting the transform + greenland-sg/greenland disambiguation (TK-10084 Cody gate)

ada04535384fa0e82eb4f5ef97f25a0a99be2058 · 2026-07-31 11:25:40 -0700 · Steve
Files touched

A db/backfill-sangetsu-vendor-codes.js
M db/stage-sangetsu-all.js
Diff

commit ada04535384fa0e82eb4f5ef97f25a0a99be2058
Author: Steve <steve@designerwallcoverings.com>
Date:   Fri Jul 31 11:25:40 2026 -0700

    sangetsu vendor_code partition: brand-map codes at ingest time (stage-sangetsu-all applies slug+'-sg', flat 'sangetsu' would duplicate rows on re-scrape) + committed idempotent backfill script documenting the transform + greenland-sg/greenland disambiguation (TK-10084 Cody gate)
---
 db/backfill-sangetsu-vendor-codes.js | 55 ++++++++++++++++++++++++++++++++++++
 db/stage-sangetsu-all.js             | 14 ++++++++-
 2 files changed, 68 insertions(+), 1 deletion(-)

diff --git a/db/backfill-sangetsu-vendor-codes.js b/db/backfill-sangetsu-vendor-codes.js
new file mode 100644
index 0000000..19db7a1
--- /dev/null
+++ b/db/backfill-sangetsu-vendor-codes.js
@@ -0,0 +1,55 @@
+#!/usr/bin/env node
+/**
+ * Re-partition sangetsu_catalog.vendor_code by real brand (TK-10084, 2026-07-31).
+ *
+ * The catalog was staged with a flat vendor_code='sangetsu', which made all 13,067
+ * rows invisible to VCC's per-vendor rollups (vendor_registry sharers use '<slug>-sg'
+ * codes). This script re-codes each row from staging/sangetsu-brands.json, joining on
+ * product_url, writing vendor_code = slug + '-sg'. Rows whose URL is not in the map
+ * keep 'sangetsu' — never guessed. Idempotent: re-running is a no-op once partitioned.
+ *
+ * The '-sg' suffix IS the documented transform (brand map slugs are bare, registry
+ * codes are suffixed) — stage-sangetsu-all.js applies the same transform at ingest
+ * time so a re-scrape cannot revert or duplicate this partition.
+ *
+ * Naming notes (standing rules):
+ *  - These are SEPARATE firms scraped off the Goodrich site — never customer-face
+ *    them as "Sangetsu products" (see memory vahallan-sangetsu-separate-firms).
+ *  - 'greenland-sg' ("Greenland (Sangetsu)", the Goodrich-sourced brand) is NOT
+ *    'greenland' ("Greenland Printing", the private-label Phillipe Romano cork line
+ *    from greenlandwallcoverings.com). Distinct registry rows, keep them distinct.
+ *
+ * Backup made before the first run: sangetsu_catalog_vendorcode_bak_20260731
+ * (id, mfr_sku, vendor_code). Rollback = UPDATE ... FROM the backup table.
+ *
+ *   DATABASE_URL=postgresql:///dw_unified node db/backfill-sangetsu-vendor-codes.js
+ */
+const fs = require('fs'), path = require('path');
+const { Client } = require('pg');
+
+const BRANDS = path.join(__dirname, '..', 'staging', 'sangetsu-brands.json');
+const DSN = process.env.DATABASE_URL || 'postgresql:///dw_unified';
+
+(async () => {
+  const brandMap = JSON.parse(fs.readFileSync(BRANDS, 'utf8'));
+  const bySlug = {};
+  for (const [url, b] of Object.entries(brandMap)) {
+    if (b && b.slug) (bySlug[b.slug] = bySlug[b.slug] || []).push(url);
+  }
+  const db = new Client({ connectionString: DSN });
+  await db.connect();
+  await db.query('BEGIN');
+  let total = 0;
+  for (const [slug, urls] of Object.entries(bySlug)) {
+    const code = `${slug}-sg`;
+    const r = await db.query(
+      `UPDATE sangetsu_catalog SET vendor_code = $1, updated_at = now()
+       WHERE product_url = ANY($2) AND vendor_code = 'sangetsu'`,
+      [code, urls]);
+    if (r.rowCount) { console.log(`${code}: ${r.rowCount}`); total += r.rowCount; }
+  }
+  const left = await db.query(`SELECT COUNT(*) FROM sangetsu_catalog WHERE vendor_code = 'sangetsu'`);
+  await db.query('COMMIT');
+  await db.end();
+  console.log(`re-coded ${total} rows; ${left.rows[0].count} remain 'sangetsu' (unmapped — by design)`);
+})().catch((e) => { console.error(e); process.exit(1); });
diff --git a/db/stage-sangetsu-all.js b/db/stage-sangetsu-all.js
index d763e08..a332d99 100644
--- a/db/stage-sangetsu-all.js
+++ b/db/stage-sangetsu-all.js
@@ -12,6 +12,7 @@ const fs = require('fs'), path = require('path');
 const { Client } = require('pg');
 
 const F = path.join(__dirname, '..', 'staging', 'sangetsu-staging.jsonl');
+const BRANDS = path.join(__dirname, '..', 'staging', 'sangetsu-brands.json');
 const DSN = process.env.DATABASE_URL || 'postgresql:///dw_unified';
 const CHUNK = 500;
 
@@ -19,6 +20,17 @@ const distOf = (url) => /sangetsu-goodrich\.co\.th/.test(url || '') ? 'Goodrich
   : /sangetsu-goodrich\.vn/.test(url || '') ? 'Goodrich (Vietnam)'
   : /goodrichglobal\.com/.test(url || '') ? 'Goodrich (Singapore)' : 'Goodrich';
 
+// vendor_code per row = the brand map's slug + '-sg' (matches the vendor_registry sharer
+// codes so VCC's shared-table rollups partition the catalog by real brand). A flat
+// 'sangetsu' write here would DUPLICATE rows on re-scrape (upsert key is
+// vendor_code+mfr_sku, and the 2026-07-31 backfill already re-coded every row).
+// URL not in the map → 'sangetsu' fallback, never a guess.
+const brandMap = fs.existsSync(BRANDS) ? JSON.parse(fs.readFileSync(BRANDS, 'utf8')) : {};
+const codeOf = (url) => {
+  const b = brandMap[url];
+  return (b && b.slug) ? `${b.slug}-sg` : 'sangetsu';
+};
+
 (async () => {
   const patterns = fs.readFileSync(F, 'utf8').trim().split('\n').filter(Boolean).map((l) => JSON.parse(l));
   // flatten pattern → colorway SKU rows (mirror the viewer's Sangetsu builder).
@@ -31,7 +43,7 @@ const distOf = (url) => /sangetsu-goodrich\.co\.th/.test(url || '') ? 'Goodrich
     for (const sku of (r.colorway_skus || [])) {
       if (seen.has(sku)) { dupes++; continue; }
       seen.add(sku);
-      recs.push([ 'sangetsu', sku, r.pattern || sku, r.source_url || null, imgs[sku] || null, us ]);
+      recs.push([ codeOf(r.source_url), sku, r.pattern || sku, r.source_url || null, imgs[sku] || null, us ]);
     }
   }
   if (dupes) console.log(`skipped ${dupes} duplicate SKU(s) across patterns`);
← f931e41 auto-save: 2026-07-31T08:55:43 (1 files) — staging/onboard-r · back to Japan Enrich · chore: v1.1.1 (session close TK-10084) 1bff8b9 →