wallpaper: prefix-agnostic UNIQUE-number mfr fallback so Shopify patterns always resolve their mfr

d32c199eadb107cb6a181a4c7ab7a3e690a81fb1 · 2026-08-04 11:23:25 -0700 · Steve

Root cause: a private-label pattern is sold on Shopify under one series prefix
(DWLA-436701) but its mfr number is filed in dw_unified under the real vendor's prefix
(DWPR-436701) with the same numeric tail — so the exact/normalized-SKU lookup misses and
the line gets a blank mfr + a FLAGGED-SKU warning. sourceFor now falls back to matching on
the trailing pattern number across shopify_products/vendor_catalog/dw_sku_registry, but
accepts it ONLY when the number maps to exactly ONE mfr (ambiguous like 46333 -> two mfrs,
and net-new, still fall through to review — never a guessed/wrong mfr). Read-only resolve.
Proven on 4 unique + 2 ambiguous SKUs (scripts/smoke-mfr-number-fallback.mjs).
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

Files touched

M lib/wallpaper.js
A scripts/smoke-mfr-number-fallback.mjs

Diff

commit d32c199eadb107cb6a181a4c7ab7a3e690a81fb1
Author: Steve <steve@designerwallcoverings.com>
Date:   Tue Aug 4 11:23:25 2026 -0700

    wallpaper: prefix-agnostic UNIQUE-number mfr fallback so Shopify patterns always resolve their mfr
    
    Root cause: a private-label pattern is sold on Shopify under one series prefix
    (DWLA-436701) but its mfr number is filed in dw_unified under the real vendor's prefix
    (DWPR-436701) with the same numeric tail — so the exact/normalized-SKU lookup misses and
    the line gets a blank mfr + a FLAGGED-SKU warning. sourceFor now falls back to matching on
    the trailing pattern number across shopify_products/vendor_catalog/dw_sku_registry, but
    accepts it ONLY when the number maps to exactly ONE mfr (ambiguous like 46333 -> two mfrs,
    and net-new, still fall through to review — never a guessed/wrong mfr). Read-only resolve.
    Proven on 4 unique + 2 ambiguous SKUs (scripts/smoke-mfr-number-fallback.mjs).
    Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
 lib/wallpaper.js                      | 45 +++++++++++++++++++++++++++++++++--
 scripts/smoke-mfr-number-fallback.mjs |  9 +++++++
 2 files changed, 52 insertions(+), 2 deletions(-)

diff --git a/lib/wallpaper.js b/lib/wallpaper.js
index ee9d8ed..816939f 100644
--- a/lib/wallpaper.js
+++ b/lib/wallpaper.js
@@ -264,7 +264,48 @@ function sourceFor(dashSku, key) {
   if (row && row.mfr) return { ...row, src: 'dw_sku_registry' };
   const c = sql(`SELECT json_build_object('mfr', NULLIF(mfr_sku,''), 'supplier', vendor, 'color', color_primary)
     FROM connie_our_catalog WHERE dw_sku ILIKE '${esc}%' LIMIT 1`);
-  return c ? { ...c, src: 'connie_our_catalog' } : null;
+  if (c && c.mfr) return { ...c, src: 'connie_our_catalog' };
+  // LAST RESORT — prefix-agnostic UNIQUE-number match. A private-label pattern is sold on
+  // Shopify under one series prefix (DWLA-436701) but its mfr number is filed in dw_unified
+  // under the real vendor's prefix (DWPR-436701) with the SAME numeric pattern tail. Match
+  // on the number; accept ONLY when it resolves to exactly ONE mfr across sources — an
+  // ambiguous number (46333 -> PSW1625RL AND DB46333) falls through to review, never a guess.
+  const byNum = mfrByNumber(dashSku);
+  if (byNum && byNum.mfr) return byNum;
+  return c || null;
+}
+
+// Prefix-agnostic mfr resolver: match on the trailing pattern NUMBER (>=3 digits) across
+// shopify_products / vendor_catalog / dw_sku_registry, returning a source row ONLY when the
+// number maps to exactly ONE distinct mfr. Zero matches (net-new) or >1 (ambiguous) -> null,
+// so the caller flags it for review instead of writing a wrong/blank mfr. Read-only.
+function mfrByNumber(dashSku) {
+  const num = (String(dashSku || '').match(/(\d{3,})\s*$/) || [])[1];
+  if (!num) return null;
+  const n = num.replace(/'/g, "''");
+  const spRe = `(^|[^0-9])${n}(-SAMPLE)?$`, vRe = `(^|[^0-9])${n}$`;
+  const mfrs = sql(`SELECT json_agg(DISTINCT mfr) FROM (
+      SELECT NULLIF(mfr_sku,'') mfr FROM shopify_products WHERE dw_sku ~* '${spRe}' AND NULLIF(mfr_sku,'') IS NOT NULL
+      UNION SELECT NULLIF(mfr_sku,'') FROM vendor_catalog   WHERE dw_sku ~* '${vRe}'  AND NULLIF(mfr_sku,'') IS NOT NULL
+      UNION SELECT NULLIF(mfr_sku,'') FROM dw_sku_registry  WHERE dw_sku ~* '${vRe}'  AND NULLIF(mfr_sku,'') IS NOT NULL
+    ) t`);
+  if (!Array.isArray(mfrs) || mfrs.length !== 1) return null; // 0 = net-new; >1 = ambiguous
+  const e = String(mfrs[0]).replace(/'/g, "''");
+  const row = sql(`SELECT json_build_object(
+      'mfr', '${e}',
+      'supplier', COALESCE(
+        (SELECT COALESCE(NULLIF(reg.vendor_name,''), NULLIF(vc.original_vendor_name,'')) FROM vendor_catalog vc
+           LEFT JOIN dw_sku_registry reg ON reg.dw_sku = vc.dw_sku WHERE vc.mfr_sku='${e}' AND vc.dw_sku ~* '${vRe}' LIMIT 1),
+        (SELECT COALESCE(NULLIF(supplier_name,''), vendor) FROM shopify_products WHERE mfr_sku='${e}' AND dw_sku ~* '${spRe}' LIMIT 1)),
+      'pattern', COALESCE(
+        (SELECT NULLIF(pattern_name,'') FROM vendor_catalog WHERE mfr_sku='${e}' AND dw_sku ~* '${vRe}' LIMIT 1),
+        (SELECT NULLIF(pattern_name,'') FROM shopify_products WHERE mfr_sku='${e}' AND dw_sku ~* '${spRe}' LIMIT 1)),
+      'color', (SELECT COALESCE(NULLIF(color_name,''), NULLIF(color_primary,'')) FROM vendor_catalog WHERE mfr_sku='${e}' AND dw_sku ~* '${vRe}' LIMIT 1),
+      'width', (SELECT COALESCE(NULLIF(width,''), NULLIF(width_inches::text,'')) FROM vendor_catalog WHERE mfr_sku='${e}' AND dw_sku ~* '${vRe}' LIMIT 1),
+      'canonical', COALESCE(
+        (SELECT dw_sku FROM shopify_products WHERE mfr_sku='${e}' AND dw_sku ~* '${spRe}' LIMIT 1),
+        (SELECT dw_sku FROM vendor_catalog WHERE mfr_sku='${e}' AND dw_sku ~* '${vRe}' LIMIT 1)))`);
+  return (row && row.mfr) ? { ...row, src: 'number-match(unique)' } : null;
 }
 
 // LAST-RESORT vid guess for a genuinely-new SKU: the most common vid among existing
@@ -372,4 +413,4 @@ export async function ensureWallpaper(combo) {
 
 // Internal helpers exported for the TK-10083 live-proof dry-run so it exercises the ACTUAL
 // committed logic (not a re-implementation). findExistingMaster is READ-ONLY (only fm.find).
-export const _internals = { parseCombo, normalizeSku, findExistingMaster, canonicalDashFor, splitCandidates };
+export const _internals = { parseCombo, normalizeSku, findExistingMaster, canonicalDashFor, splitCandidates, sourceFor, mfrByNumber };
diff --git a/scripts/smoke-mfr-number-fallback.mjs b/scripts/smoke-mfr-number-fallback.mjs
new file mode 100644
index 0000000..475d618
--- /dev/null
+++ b/scripts/smoke-mfr-number-fallback.mjs
@@ -0,0 +1,9 @@
+import { _internals } from '../lib/wallpaper.js';
+const { sourceFor, normalizeSku } = _internals;
+const skus = ['DWLA-436701','DWLA-436713','DWYG-292570','DWYG-293650','DWDB2-46333','403247','OP-ART-DECO-WAVES'];
+console.log('SKU'.padEnd(20), 'RESULT');
+for (const s of skus) {
+  const r = sourceFor(s, normalizeSku(s));
+  if (r && r.mfr) console.log(s.padEnd(20), `mfr=${r.mfr}  via=${r.src}  canon=${r.canonical||'-'}`);
+  else console.log(s.padEnd(20), 'no mfr → still flagged (net-new or ambiguous → review)');
+}

← 35cf4d3 test: smoke-invoice-b — dry-run regression for SKU-on-line + · back to Filemaker Mcp · Backfill blank Name/Color of Pattern on WallQuest GRS grassc dd37d30 →