← back to Dw Photo Capture
Add manufacturer-SKU resolver: scanned vendor codes resolve to DW house SKUs
47fbc5ae03a8d89eb72b24c732df2482f72bbca8 · 2026-06-25 17:29:56 -0700 · Steve
- build_mfr_index.py: Shopify bulk-op export of every product's manufacturer_sku
(custom + dwc) -> normalized vendor-code index (131,516 products, 224,704 keys)
- server.js: load data/mfr_index.json; shopifySearch() rewrites a single code-like
query to its DW house SKU via normalized lookup (handles dashes/dots/compound) +
tight ±1-char fuzzy for DW's dropped-trailing-char codes
- fixes scans of vendor codes (WDW-2290 -> CHC-217000, WHF3846.WT.0 -> DWKK-156194)
that Shopify can't search because the code lives only in a metafield
- mfr_index.json (38MB, regenerable) gitignored
Files touched
M .gitignoreA build_mfr_index.pyM server.js
Diff
commit 47fbc5ae03a8d89eb72b24c732df2482f72bbca8
Author: Steve <steve@designerwallcoverings.com>
Date: Thu Jun 25 17:29:56 2026 -0700
Add manufacturer-SKU resolver: scanned vendor codes resolve to DW house SKUs
- build_mfr_index.py: Shopify bulk-op export of every product's manufacturer_sku
(custom + dwc) -> normalized vendor-code index (131,516 products, 224,704 keys)
- server.js: load data/mfr_index.json; shopifySearch() rewrites a single code-like
query to its DW house SKU via normalized lookup (handles dashes/dots/compound) +
tight ±1-char fuzzy for DW's dropped-trailing-char codes
- fixes scans of vendor codes (WDW-2290 -> CHC-217000, WHF3846.WT.0 -> DWKK-156194)
that Shopify can't search because the code lives only in a metafield
- mfr_index.json (38MB, regenerable) gitignored
---
.gitignore | 1 +
build_mfr_index.py | 110 +++++++++++++++++++++++++++++++++++++++++++++++++++++
server.js | 30 +++++++++++++++
3 files changed, 141 insertions(+)
diff --git a/.gitignore b/.gitignore
index 3cbca28..abc9d57 100644
--- a/.gitignore
+++ b/.gitignore
@@ -5,3 +5,4 @@ data/progress.json
*.log
.DS_Store
certs/
+data/mfr_index.json
diff --git a/build_mfr_index.py b/build_mfr_index.py
new file mode 100644
index 0000000..0a03f21
--- /dev/null
+++ b/build_mfr_index.py
@@ -0,0 +1,110 @@
+#!/usr/bin/env python3
+"""Build data/mfr_index.json — a normalized manufacturer_sku -> DW house-SKU index for
+the whole Shopify catalog, so scanning a vendor's printed code (WDW2310, WHF3846.WT.0,
+etc.) resolves to the DW product even though DW files it under a house SKU (CHC-/DWKK-/…)
+with the vendor code tucked in a metafield Shopify can't search by value.
+
+Uses a Bulk Operation (server-side full-catalog export -> one JSONL) rather than paging
+~160k products by hand. Read-only. $0 (local + Shopify read)."""
+import json, os, re, time, urllib.request, sys
+
+SHOP = "designer-laboratory-sandbox.myshopify.com"; API = "2024-10"
+ROOT = os.path.dirname(os.path.abspath(__file__))
+OUT = os.path.join(ROOT, "data", "mfr_index.json")
+TOKEN = os.environ.get("SHOPIFY_ADMIN_TOKEN", "")
+if not TOKEN:
+ for line in open(os.path.expanduser("~/Projects/secrets-manager/.env")):
+ if line.startswith("SHOPIFY_ADMIN_TOKEN="):
+ TOKEN = line.strip().split("=", 1)[1]; break
+assert TOKEN, "no SHOPIFY_ADMIN_TOKEN"
+
+def gql(query):
+ req = urllib.request.Request(f"https://{SHOP}/admin/api/{API}/graphql.json",
+ data=json.dumps({"query": query}).encode(),
+ headers={"X-Shopify-Access-Token": TOKEN, "Content-Type": "application/json"})
+ return json.load(urllib.request.urlopen(req, timeout=90))
+
+def norm(s):
+ return re.sub(r'[^A-Z0-9]', '', (s or '').upper())
+
+# A vendor code field can be compound: "WDW-2290; Diamond WMT5001" -> ["WDW-2290","Diamond WMT5001"]
+def parts(val):
+ return [p for p in re.split(r'[;,/|]+', val or '') if p.strip()]
+
+# ── 1) kick off the bulk export ──────────────────────────────────────────────
+BULK_Q = '''
+{
+ products {
+ edges { node {
+ id handle status title
+ mf: metafield(namespace:"custom", key:"manufacturer_sku"){ value }
+ mf2: metafield(namespace:"dwc", key:"manufacturer_sku"){ value }
+ variants { edges { node { id sku } } }
+ }}
+ }
+}'''
+start = gql('mutation { bulkOperationRunQuery(query: """%s""") { bulkOperation{ id status } userErrors{ field message } } }' % BULK_Q)
+ue = start.get("data", {}).get("bulkOperationRunQuery", {}).get("userErrors", [])
+if ue:
+ print("bulk start errors:", ue)
+ # a stale running op blocks new ones — surface it
+ cur = gql('{ currentBulkOperation { id status } }')["data"]["currentBulkOperation"]
+ print("currentBulkOperation:", cur); sys.exit(1)
+print("bulk op started, polling…")
+
+# ── 2) poll to completion ────────────────────────────────────────────────────
+url = None
+for _ in range(180): # up to ~15 min
+ time.sleep(5)
+ cur = gql('{ currentBulkOperation { id status errorCode objectCount url } }')["data"]["currentBulkOperation"]
+ print(f" status={cur['status']} objects={cur.get('objectCount')}")
+ if cur["status"] == "COMPLETED":
+ url = cur["url"]; break
+ if cur["status"] in ("FAILED", "CANCELED"):
+ print("bulk failed:", cur); sys.exit(1)
+assert url, "bulk op did not complete in time"
+
+# ── 3) download JSONL + parse (products + their variant child lines) ──────────
+data = urllib.request.urlopen(url, timeout=300).read().decode()
+prods = {} # gid -> {handle,status,title,mfrs:[...]}
+vskus = {} # parentGid -> [sku,...]
+for line in data.splitlines():
+ if not line.strip(): continue
+ o = json.loads(line)
+ if "__parentId" in o and "sku" in o: # variant child line
+ vskus.setdefault(o["__parentId"], []).append(o.get("sku") or "")
+ elif "handle" in o: # product line
+ mfrs = []
+ for k in ("mf", "mf2"):
+ v = (o.get(k) or {}).get("value")
+ if v: mfrs += parts(v)
+ prods[o["id"]] = {"handle": o["handle"], "status": o.get("status"),
+ "title": o.get("title", ""), "mfrs": mfrs}
+
+# ── 4) build normalized index: norm(vendor code) -> {sku,title,status,handle} ─
+def house_sku(skus):
+ real = [s for s in skus if s and not s.lower().endswith("-sample")]
+ return (real or [s for s in skus if s] or [""])[0]
+
+index = {}
+collisions = 0
+for gid, p in prods.items():
+ sku = house_sku(vskus.get(gid, []))
+ if not sku: continue
+ keys = set()
+ for m in p["mfrs"]:
+ n = norm(m)
+ if len(n) >= 4: keys.add(n) # skip junk like "0" / "WT"
+ # also index the house SKU itself (so scanning the house code resolves too)
+ if len(norm(sku)) >= 4: keys.add(norm(sku))
+ for n in keys:
+ if n in index and index[n]["sku"] != sku: collisions += 1
+ index.setdefault(n, {"sku": sku, "title": p["title"][:80],
+ "status": p["status"], "handle": p["handle"]})
+
+meta = {"_meta": {"products": len(prods), "keys": len(index), "collisions": collisions}}
+json.dump({**meta, **index}, open(OUT, "w"))
+print(f"\nindexed {len(prods)} products -> {len(index)} normalized keys ({collisions} collisions) -> {OUT}")
+# spot-checks
+for probe in ("WDW2290", "WDW2310", "CHC217000", "WHF3846WT0"):
+ print(f" {probe} -> {index.get(probe, 'NOT FOUND')}")
diff --git a/server.js b/server.js
index 5b33d6e..383db01 100644
--- a/server.js
+++ b/server.js
@@ -708,6 +708,28 @@ let SHEET = loadJSON(path.join(DATA, 'sheet_grs.json'), []);
const SHEET_BY_GRS = {};
SHEET.forEach(x => { if (x.grs) SHEET_BY_GRS[x.grs.toUpperCase()] = x; });
+// Manufacturer-SKU index: normalized vendor code -> { sku (DW house SKU), title, ... }.
+// Lets a scanned vendor code (WDW2310, WHF3846.WT.0) resolve to the DW product that
+// stores that code only in a metafield. Built by build_mfr_index.py. Keys are normalized.
+const MFR_INDEX = loadJSON(path.join(DATA, 'mfr_index.json'), {});
+const mfrNorm = s => String(s || '').toUpperCase().replace(/[^A-Z0-9]/g, '');
+// Resolve a scanned/typed code to a DW house SKU via the index. Tolerates DW's messy codes:
+// exact normalized hit first, then a dropped-trailing-char (prefix) match either direction.
+function mfrToHouseSku(q) {
+ const n = mfrNorm(q);
+ if (n.length < 4) return null;
+ if (MFR_INDEX[n] && MFR_INDEX[n].sku) return MFR_INDEX[n].sku; // exact (normalized) — common case
+ // fuzzy: DW sometimes drops/adds ONE trailing char. Only accept a key within 1 char
+ // where the shorter is a prefix of the longer — tight enough to avoid wild mismatches.
+ if (n.length >= 6) {
+ for (const k of Object.keys(MFR_INDEX)) {
+ if (Math.abs(k.length - n.length) <= 1 && k.length >= 6 && MFR_INDEX[k].sku &&
+ (k.startsWith(n) || n.startsWith(k))) return MFR_INDEX[k].sku;
+ }
+ }
+ return null;
+}
+
function sheetTitle(s) {
const name = (s.name || '').trim(), color = (s.color || '').trim();
// drop a redundant color when the "name" already IS the color (some sheet rows duplicate them)
@@ -847,6 +869,14 @@ const _PNODE = `legacyResourceId title status vendor
mf2:metafield(namespace:"dwc",key:"manufacturer_sku"){value}
v:variants(first:5){edges{node{sku title price}}}`;
async function shopifySearch(q) {
+ // If the query is a single code-like token (has a digit), try the manufacturer-SKU
+ // index FIRST — a scanned vendor code (WDW2310) resolves to its DW house SKU (CHC-…)
+ // and we search by that, since Shopify can't search the mfr# metafield by value.
+ const one = q.trim();
+ if (!/\s/.test(one) && /\d/.test(one)) {
+ const house = mfrToHouseSku(one);
+ if (house && mfrNorm(house) !== mfrNorm(one)) q = house; // rewrite to the house SKU
+ }
const terms = q.split(/\s+/).filter(Boolean).slice(0, 4)
.map(t => t.replace(/["\\():*]/g, '')).filter(Boolean);
if (!terms.length) return [];
← 8ddb73e Scan: code numbers ONLY — drop name/prose candidates
·
back to Dw Photo Capture
·
auto-save: 2026-06-25T17:36:07 (2 files) — build_mfr_index.p dcb3fda →