feat: best-ID identify-first against unified catalog — identifyUnified(code) resolves a scanned code via dw_sku_crossref (267k, shares FileMaker's Mfr Pattern/combo sku vocabulary) to canonical {internal_sku, mfr_sku, vendor} + best-effort image/title from shopify_products. New GET /api/identify-unified. Verified: TR2581→RHT264/Seabrook, 83615→VIC261B w/ image.

a985673bd3ec9e2871846427b16409e73c62fe14 · 2026-07-06 17:02:51 -0700 · Steve Abrams

Files touched

M server.js

Diff

commit a985673bd3ec9e2871846427b16409e73c62fe14
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Mon Jul 6 17:02:51 2026 -0700

    feat: best-ID identify-first against unified catalog — identifyUnified(code) resolves a scanned code via dw_sku_crossref (267k, shares FileMaker's Mfr Pattern/combo sku vocabulary) to canonical {internal_sku, mfr_sku, vendor} + best-effort image/title from shopify_products. New GET /api/identify-unified. Verified: TR2581→RHT264/Seabrook, 83615→VIC261B w/ image.
---
 server.js | 51 +++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 51 insertions(+)

diff --git a/server.js b/server.js
index 84c110f..ad77206 100644
--- a/server.js
+++ b/server.js
@@ -1022,6 +1022,16 @@ const appHandler = (req, res) => {
     return;
   }
 
+  // "BEST ID": identify a scanned code against the whole unified catalog (crossref 267k +
+  // images) BEFORE Shopify/FileMaker. Returns the canonical identity + FileMaker search keys.
+  if (u.pathname === '/api/identify-unified' && req.method === 'GET') {
+    const code = (u.searchParams.get('code') || '').trim();
+    if (!code) return send(res, 400, { err: 'code required' });
+    identifyUnified(code).then(id => send(res, 200, { ok: !!id, found: !!id, identity: id }))
+      .catch(e => send(res, 200, { ok: false, found: false, err: e.message }));
+    return;
+  }
+
   // Ask a question about a SKU — local Ollama answers from the product's own Shopify facts. $0.
   if (u.pathname === '/api/ask' && req.method === 'POST') {
     let body = '';
@@ -1568,6 +1578,47 @@ function unifiedSimilar(terms) {
   });
 }
 
+// ── "BEST ID": identify a scanned code against the WHOLE unified catalog BEFORE any Shopify or
+// FileMaker lookup. dw_sku_crossref (267k) is the key — it shares FileMaker's code vocabulary
+// (crossref.mfr_sku == FMP "Mfr Pattern", crossref.internal_sku == FMP "combo sku"), so the
+// identity it returns is also the FileMaker search key. Image/pattern enrich best-effort from
+// the 173k shopify_products (the vendor_catalog dw_sku namespace doesn't join cleanly). $0 local psql.
+function identifyUnified(code) {
+  return new Promise(resolve => {
+    const norm = String(code || '').toUpperCase().replace(/[^A-Z0-9]/g, '');   // alnum-only → injection-safe
+    if (norm.length < 3) return resolve(null);
+    // CTE resolves ONE crossref row first, then a single lateral image lookup (no per-row blowup).
+    const SQL = `with m as (
+        select internal_sku, internal_sku_dash, mfr_sku, vendor_code, coalesce(vendor_name,'') vendor_name,
+               upper(regexp_replace(split_part(mfr_sku,' ',1),'[^A-Za-z0-9]','','g')) mfr_norm
+        from dw_sku_crossref
+        where upper(regexp_replace(split_part(mfr_sku,' ',1),'[^A-Za-z0-9]','','g'))='${norm}'
+           or upper(regexp_replace(coalesce(internal_sku,''),'[^A-Za-z0-9]','','g'))='${norm}'
+           or upper(regexp_replace(coalesce(internal_sku_dash,''),'[^A-Za-z0-9]','','g'))='${norm}'
+        order by (upper(regexp_replace(split_part(mfr_sku,' ',1),'[^A-Za-z0-9]','','g'))='${norm}') desc
+        limit 1)
+      select m.internal_sku, m.internal_sku_dash, m.mfr_sku, m.vendor_code, m.vendor_name,
+             coalesce(sp.title,''), coalesce(sp.image_url,''), coalesce(sp.pattern_name,'')
+      from m left join lateral (
+        select title, image_url, pattern_name from shopify_products
+        where upper(regexp_replace(coalesce(mfr_sku,''),'[^A-Za-z0-9]','','g'))=m.mfr_norm
+          and image_url is not null and image_url<>'' limit 1) sp on true`;
+    execFile(PSQL, ['-d', DW_DB, '-tAF', '\t', '-c', SQL],
+      { timeout: 8000, maxBuffer: 2 * 1024 * 1024 }, (err, stdout) => {
+        if (err) return resolve(null);
+        const line = (stdout || '').split('\n').filter(Boolean)[0];
+        if (!line) return resolve(null);
+        const [internal_sku, internal_dash, mfr_sku, vendor_code, vendor, title, image, pattern] = line.split('\t');
+        resolve({
+          internal_sku: internal_sku || null, internal_sku_dash: internal_dash || null,
+          mfr_sku: mfr_sku || null, mfr_code: (mfr_sku || '').split(' ')[0] || null,
+          vendor_code: vendor_code || null, vendor: vendor || null,
+          title: title || null, image: image || null, pattern: pattern || null, matched: norm
+        });
+      });
+  });
+}
+
 server.listen(PORT, '0.0.0.0', () => {
   console.log(`DW Photo Capture on http://0.0.0.0:${PORT}  (Shopify push: ${TOKEN ? 'ON' : 'OFF — no token'}, sheet GRS: ${SHEET.length})`);
   rebuildIndex();              // sheet-only items searchable immediately

← 5483a67 feat: hone in on the text — OCR now reads a centered, upscal · back to Dw Photo Capture · feat: FileMaker sample-request lookup + print-sticker handof 31dbd12 →