all.dw grid: manufacturer-SKU search — backfill mfr_sku from *_catalog tables (sync leaves shopify_products.mfr_sku empty for new lines); feeds mfr_number display + search haystack

75328f4ddf5dbebd8085515a6d6583280bb7e915 · 2026-07-08 12:24:32 -0700 · steve@designerwallcoverings.com
Files touched

M server.js
Diff

commit 75328f4ddf5dbebd8085515a6d6583280bb7e915
Author: steve@designerwallcoverings.com <steve@designerwallcoverings.com>
Date:   Wed Jul 8 12:24:32 2026 -0700

    all.dw grid: manufacturer-SKU search — backfill mfr_sku from *_catalog tables (sync leaves shopify_products.mfr_sku empty for new lines); feeds mfr_number display + search haystack
---
 server.js | 37 ++++++++++++++++++++++++++++++++++---
 1 file changed, 34 insertions(+), 3 deletions(-)

diff --git a/server.js b/server.js
index 7500ca6..09ca616 100644
--- a/server.js
+++ b/server.js
@@ -193,6 +193,7 @@ let FM = new Map();             // fmKey(dw_sku) → FileMaker WALLPAPER mirror
 let FM_MATCHED = new Set();     // fmKeys that joined a shopify_products row this snapshot
 let FM_STATS = { rows: 0, matched: 0, discontinued_hidden: 0, mismatch: 0, fm_only_live: 0 };
 let STOCK = new Map();          // upper(sku) → public-safe stored stock snapshot (the /livestock FREE path)
+let MFR = new Map();            // upper(bare dw_sku) → manufacturer SKU, backfilled from *_catalog tables
 let VENDOR_VIEWER = new Map();  // slugify(vendor) / slug → internal line-viewer URL (the vendor's live microsite)
 let VENDOR_ITEM = new Map();    // slugify(vendor) / slug → { url, handles:Set } — the microsite's FULL handle set;
                                // "This Item" deep-links resolve ONLY when the row's handle is IN that set.
@@ -239,6 +240,31 @@ async function loadStock() {
   STOCK = map;
 }
 
+// Manufacturer-SKU backfill — upper(bare dw_sku) → mfr_sku, from every *_catalog table.
+// The Shopify sync populates shopify_products from variant SKUs, NOT the manufacturer_sku
+// metafield, so newer lines (e.g. Pierre Frey) have an empty mfr_sku and their manufacturer
+// SKU (BP327001) is invisible to grid search. This backfills it from the dw_unified staging
+// catalogs so mfr-SKU search + the mfr_number display work for every catalog-backed vendor.
+async function loadMfr() {
+  const map = new Map();
+  const { rows: cols } = await pool.query(
+    `SELECT table_name, column_name FROM information_schema.columns
+      WHERE table_schema='public' AND table_name ~ '^[a-z0-9_]+_catalog$'
+        AND column_name IN ('dw_sku','mfr_sku')`);
+  const byTab = {};
+  for (const c of cols) (byTab[c.table_name] ||= new Set()).add(c.column_name);
+  const parts = [];
+  for (const [t, set] of Object.entries(byTab)) {
+    if (set.has('dw_sku') && set.has('mfr_sku'))
+      parts.push(`SELECT dw_sku, mfr_sku FROM "${t}" WHERE dw_sku IS NOT NULL AND mfr_sku IS NOT NULL`);
+  }
+  if (parts.length) {
+    const { rows } = await pool.query(parts.join(' UNION ALL '));
+    for (const r of rows) { const k = String(r.dw_sku).toUpperCase(); if (!map.has(k)) map.set(k, r.mfr_sku); }
+  }
+  MFR = map;
+}
+
 // FileMaker WALLPAPER mirror (dw_unified.filemaker_wallpaper, written by
 // scripts/fm-wallpaper-sync.mjs). FMPro is Steve's SOURCE OF TRUTH: "is this live?"
 // (Date Discontinued), the authoritative mfr number, width + specs, and the source for
@@ -310,7 +336,11 @@ function deriveRow(r) {
   const fmk = fmKey(r.dw_sku || r.variant_sku || r.sku);
   const fm = (fmk && FM.get(fmk)) || null;
   if (fm) FM_MATCHED.add(fmk);
-  const shopMfr = r.mfr_sku || null;
+  // manufacturer SKU: prefer the synced shopify_products.mfr_sku; else backfill from the
+  // *_catalog staging tables by bare dw_sku (the sync doesn't populate mfr_sku for newer
+  // lines like Pierre Frey → BP327001 would otherwise be invisible to search).
+  const bareDw = String(r.dw_sku || r.variant_sku || r.sku || '').replace(/-sample$/i, '').toUpperCase();
+  const shopMfr = r.mfr_sku || (bareDw && MFR.get(bareDw)) || null;
   const fmMfr = fm ? (fm.mfr_number || null) : null;
   // mfr_mismatch: both sides present AND their normalized tokens disagree → the grid flags it
   // (the mfr-number verification workflow Steve does by hand in FileMaker's "verification" layouts).
@@ -326,7 +356,7 @@ function deriveRow(r) {
     // Manufacturer identifiers — admin curation aid (Steve rule: new-SKU go-live gate needs a real
     // mfr NAME + mfr SKU number). mfr_number is the clean, ~93%-populated field; mfr_name comes from
     // supplier_name and is rendered only when present (it's blank/inconsistent for some private labels).
-    mfr_number: r.mfr_sku || null,
+    mfr_number: shopMfr,
     mfr_name: r.supplier_name || null,
     // ── FileMaker source-of-truth enrichment (null when the SKU isn't in the WALLPAPER file) ──
     fm_present: !!fm,
@@ -375,7 +405,7 @@ function deriveRow(r) {
     created: r.created_at_shopify ? new Date(r.created_at_shopify).getTime() : 0,
     updated: r.updated_at_shopify ? new Date(r.updated_at_shopify).getTime() : 0,
     // one lowercase haystack per row so search is a single .includes() pass
-    hay: [r.dw_sku, r.variant_sku, r.sku, r.mfr_sku, r.supplier_name, r.title, r.vendor, r.product_type, r.pattern_name, r.tags].filter(Boolean).join(' ').toLowerCase(),
+    hay: [r.dw_sku, r.variant_sku, r.sku, r.mfr_sku, shopMfr, r.supplier_name, r.title, r.vendor, r.product_type, r.pattern_name, r.tags].filter(Boolean).join(' ').toLowerCase(),
   };
 }
 
@@ -387,6 +417,7 @@ async function loadSnapshot() {
   buildVendorViewer();
   loadCoverage();
   try { await loadStock(); } catch (e) { console.error('stock load failed (non-fatal):', e.message); }
+  try { await loadMfr(); } catch (e) { console.error('mfr load failed (non-fatal):', e.message); }
   try { await loadFileMaker(); } catch (e) { console.error('filemaker load failed (non-fatal):', e.message); }
   FM_MATCHED = new Set();       // repopulated by deriveRow as it joins each row this cycle
   const res = await pool.query(await snapSql());
← de111d4 shopify-push: portable Shopify-lib resolution (env/mac3/co-l · back to All Designerwallcoverings · Full Shopify image galleries: bulk-sync product_images + ser 08c6237 →