← back to All Designerwallcoverings
adapters: shared grid-SKU→catalog resolver (bridge via shopify_products mfr_sku/dw_sku) so shopify-public/romo/woo actually resolve display SKUs
5d8857a7dfc3ba16c853d620e8f9bd7208078284 · 2026-07-07 09:34:37 -0700 · Steve
Files touched
A scripts/adapters/resolve.jsM scripts/adapters/romo.jsM scripts/adapters/shopify-public.jsM scripts/adapters/woo-public.js
Diff
commit 5d8857a7dfc3ba16c853d620e8f9bd7208078284
Author: Steve <steve@designerwallcoverings.com>
Date: Tue Jul 7 09:34:37 2026 -0700
adapters: shared grid-SKU→catalog resolver (bridge via shopify_products mfr_sku/dw_sku) so shopify-public/romo/woo actually resolve display SKUs
---
scripts/adapters/resolve.js | 41 ++++++++++++++++++++++++++++++++++++++
scripts/adapters/romo.js | 16 +++++++--------
scripts/adapters/shopify-public.js | 29 ++++++---------------------
scripts/adapters/woo-public.js | 20 +++++--------------
4 files changed, 59 insertions(+), 47 deletions(-)
diff --git a/scripts/adapters/resolve.js b/scripts/adapters/resolve.js
new file mode 100644
index 0000000..604848d
--- /dev/null
+++ b/scripts/adapters/resolve.js
@@ -0,0 +1,41 @@
+// adapters/resolve.js — shared grid-SKU → vendor-catalog product_url resolver.
+//
+// The grid passes a DISPLAY sku (e.g. "ROP-73347-Sample"). The vendor catalog is keyed by its own
+// dw_sku (e.g. "DWRO-29318") and mfr_sku (e.g. "W472/04") — the display string alone often matches
+// neither. So we bridge through shopify_products: look up the display row to recover its dw_sku +
+// mfr_sku, then match the catalog on ANY of {display sku, its dw_sku, its mfr_sku}. This is exactly
+// the join the coverage resolvability gate measures, so "wired & enabled" == "actually resolves".
+async function catCols(pool, table) {
+ const { rows } = await pool.query(
+ `SELECT column_name FROM information_schema.columns WHERE table_schema='public' AND table_name=$1`, [table]);
+ return new Set(rows.map((r) => r.column_name));
+}
+
+async function resolveCatalogUrl(pool, table, gridSku) {
+ if (!table || !/^[a-z0-9_]+$/.test(table)) return { error: 'no catalog table for this vendor' };
+ const cols = await catCols(pool, table);
+ if (!cols.has('product_url')) return { error: 'catalog has no product_url' };
+
+ // recover the display row's dw_sku + mfr_sku from the grid sku
+ const keys = new Set([String(gridSku).toUpperCase()]);
+ try {
+ const { rows } = await pool.query(
+ `SELECT dw_sku, mfr_sku FROM shopify_products
+ WHERE upper(coalesce(dw_sku,''))=upper($1) OR upper(coalesce(variant_sku,''))=upper($1) OR upper(coalesce(sku,''))=upper($1)
+ LIMIT 1`, [gridSku]);
+ if (rows[0]) { if (rows[0].dw_sku) keys.add(String(rows[0].dw_sku).toUpperCase()); if (rows[0].mfr_sku) keys.add(String(rows[0].mfr_sku).toUpperCase()); }
+ } catch { /* shopify_products lookup optional */ }
+
+ const keyArr = [...keys];
+ const wh = [];
+ if (cols.has('dw_sku')) wh.push('upper(dw_sku)=ANY($1)');
+ if (cols.has('mfr_sku')) wh.push('upper(mfr_sku)=ANY($1)');
+ if (!wh.length) return { error: 'catalog has no matchable sku column' };
+ const sel = ['product_url', cols.has('mfr_sku') ? 'mfr_sku' : "'' AS mfr_sku", cols.has('handle') ? 'handle' : "'' AS handle"].join(', ');
+ const { rows } = await pool.query(
+ `SELECT ${sel} FROM "${table}" WHERE (${wh.join(' OR ')}) AND product_url IS NOT NULL AND product_url<>'' LIMIT 1`, [keyArr]);
+ if (!rows.length) return { error: 'SKU not found in this vendor catalog' };
+ return { product_url: rows[0].product_url, mfr_sku: rows[0].mfr_sku || null, handle: rows[0].handle || null };
+}
+
+module.exports = { resolveCatalogUrl };
diff --git a/scripts/adapters/romo.js b/scripts/adapters/romo.js
index dff90dc..ed1b59a 100644
--- a/scripts/adapters/romo.js
+++ b/scripts/adapters/romo.js
@@ -7,17 +7,15 @@
//
// PUBLIC-SAFE: emits availability only. Romo's portal price is the NET/TRADE (cost) price with no
// known retail transform in this context, so `price` is always null — never emitted.
-async function run({ sku, pool, newSession, romoLogin, log }) {
+const { resolveCatalogUrl } = require('./resolve');
+
+async function run({ sku, pool, newSession, romoLogin, catalogTable, log }) {
let sessionOpened = false, browser = null, session = null, bb = null;
try {
- // 1. Resolve the SKU → its Romo portal product page.
- const { rows } = await pool.query(
- `SELECT dw_sku, mfr_sku, product_url, pattern_name, color_name
- FROM romo_catalog
- WHERE (upper(dw_sku)=upper($1) OR upper(mfr_sku)=upper($1)) AND product_url IS NOT NULL AND product_url<>''
- LIMIT 1`, [sku]);
- if (!rows.length) return { available: false, reason: 'SKU not found in Romo catalog (or no product URL)' };
- const url = rows[0].product_url;
+ // 1. Resolve the grid SKU → its Romo portal product page (bridged via shopify_products).
+ const r = await resolveCatalogUrl(pool, catalogTable || 'romo_catalog', sku);
+ if (r.error) return { available: false, reason: r.error };
+ const url = r.product_url;
// 2. Metered Browserbase session + Romo portal login (documented mechanics from romo-lib).
const sess = await newSession();
diff --git a/scripts/adapters/shopify-public.js b/scripts/adapters/shopify-public.js
index e087348..04cbc0a 100644
--- a/scripts/adapters/shopify-public.js
+++ b/scripts/adapters/shopify-public.js
@@ -7,33 +7,16 @@
//
// PUBLIC-SAFE: emits availability only. `price` is null — the storefront price is the vendor's DTC
// retail, NOT our retail, so we never surface it (avoids implying it's our price).
-const { Pool } = require('pg');
-
-// which of {dw_sku, mfr_sku, handle} a given catalog table actually carries
-async function catCols(pool, table) {
- const { rows } = await pool.query(
- `SELECT column_name FROM information_schema.columns WHERE table_schema='public' AND table_name=$1`, [table]);
- return new Set(rows.map((r) => r.column_name));
-}
+const { resolveCatalogUrl } = require('./resolve');
async function run({ sku, pool, newSession, catalogTable, log }) {
let sessionOpened = false, browser = null, session = null, bb = null;
try {
- const table = catalogTable;
- if (!table || !/^[a-z0-9_]+$/.test(table)) return { available: false, reason: 'no catalog table for this vendor' };
- const cols = await catCols(pool, table);
- if (!cols.has('product_url')) return { available: false, reason: 'catalog has no product_url' };
-
- // Resolve the SKU → its Shopify product URL (+ mfr_sku for variant matching).
- const wh = [];
- if (cols.has('dw_sku')) wh.push('upper(dw_sku)=upper($1)');
- if (cols.has('mfr_sku')) wh.push('upper(mfr_sku)=upper($1)');
- if (!wh.length) return { available: false, reason: 'catalog has no matchable sku column' };
- const sel = ['product_url', cols.has('mfr_sku') ? 'mfr_sku' : "'' AS mfr_sku", cols.has('handle') ? 'handle' : "'' AS handle"].join(', ');
- const { rows } = await pool.query(
- `SELECT ${sel} FROM "${table}" WHERE (${wh.join(' OR ')}) AND product_url IS NOT NULL AND product_url<>'' LIMIT 1`, [sku]);
- if (!rows.length) return { available: false, reason: 'SKU not found in this vendor catalog' };
- const { product_url, mfr_sku } = rows[0];
+ // Resolve the grid SKU → its Shopify product URL (+ mfr_sku for variant matching), bridging
+ // through shopify_products so the display SKU aligns with the catalog's own keys.
+ const r = await resolveCatalogUrl(pool, catalogTable, sku);
+ if (r.error) return { available: false, reason: r.error };
+ const { product_url, mfr_sku } = r;
const jsUrl = String(product_url).replace(/[?#].*$/, '').replace(/\/+$/, '') + '.js';
// Metered Browserbase session — read the .js feed in-page (uniform mechanism; redirects followed).
diff --git a/scripts/adapters/woo-public.js b/scripts/adapters/woo-public.js
index 1e33d69..592a9f7 100644
--- a/scripts/adapters/woo-public.js
+++ b/scripts/adapters/woo-public.js
@@ -5,24 +5,14 @@
// Reads the WooCommerce Store API (`/wp-json/wc/store/products?slug=<slug>`) in-page via the same
// Browserbase mechanism; `stock_status` = 'instock' | 'outofstock' | 'onbackorder'.
// PUBLIC-SAFE: availability only; price:null (vendor DTC, not our retail).
+const { resolveCatalogUrl } = require('./resolve');
+
async function run({ sku, pool, newSession, catalogTable, log }) {
let sessionOpened = false, browser = null, session = null, bb = null;
try {
- const table = catalogTable;
- if (!table || !/^[a-z0-9_]+$/.test(table)) return { available: false, reason: 'no catalog table for this vendor' };
- const { rows: colRows } = await pool.query(
- `SELECT column_name FROM information_schema.columns WHERE table_schema='public' AND table_name=$1`, [table]);
- const cols = new Set(colRows.map((r) => r.column_name));
- if (!cols.has('product_url')) return { available: false, reason: 'catalog has no product_url' };
- const wh = [];
- if (cols.has('dw_sku')) wh.push('upper(dw_sku)=upper($1)');
- if (cols.has('mfr_sku')) wh.push('upper(mfr_sku)=upper($1)');
- if (!wh.length) return { available: false, reason: 'catalog has no matchable sku column' };
- const { rows } = await pool.query(
- `SELECT product_url FROM "${table}" WHERE (${wh.join(' OR ')}) AND product_url IS NOT NULL AND product_url<>'' LIMIT 1`, [sku]);
- if (!rows.length) return { available: false, reason: 'SKU not found in this vendor catalog' };
-
- const url = rows[0].product_url;
+ const r = await resolveCatalogUrl(pool, catalogTable, sku);
+ if (r.error) return { available: false, reason: r.error };
+ const url = r.product_url;
const m = /^https?:\/\/([^/]+)\/product\/([^/?#]+)/i.exec(url);
if (!m) return { available: false, reason: 'not a WooCommerce product URL' };
const [, host, slug] = m;
← 803861a coverage: resolvability gate — only ENABLE live-capable vend
·
back to All Designerwallcoverings
·
live-stock verification: 4 metered scrapes ($0.234 total) pr 875d338 →