← back to Big Red
feat(big-red/tick3): SKU resolver fallback into sister catalogs
6688ef1fe4007e9ba59c3ffc8c5882936de22dce · 2026-05-12 06:39:51 -0700 · Steve Abrams
Files touched
Diff
commit 6688ef1fe4007e9ba59c3ffc8c5882936de22dce
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Tue May 12 06:39:51 2026 -0700
feat(big-red/tick3): SKU resolver fallback into sister catalogs
---
server.js | 111 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++---
1 file changed, 107 insertions(+), 4 deletions(-)
diff --git a/server.js b/server.js
index 03c7fa8..891bf73 100644
--- a/server.js
+++ b/server.js
@@ -61,22 +61,125 @@ function detectSku(text) {
return m ? m[0].toUpperCase().replace(/(DW[A-Z]{2})(\d)/, '$1-$2') : null;
}
+// Per-catalog fallback queries — each returns a normalized row when the SKU
+// is in that table. Tried in order after shopify_products misses. Schemas
+// vary widely across catalogs (some have title, others have pattern_name +
+// color_name; some have price_retail, others retail_price) so each catalog
+// gets its own SELECT with hand-rolled normalization.
+const SISTER_CATALOG_QUERIES = [
+ {
+ source: 'scalamandre_pillows',
+ sql: `SELECT
+ dw_sku AS sku,
+ shopify_product_id::text AS shopify_id,
+ NULL::text AS handle,
+ pattern_name || COALESCE(' · ' || color_name, '') AS title,
+ 'Scalamandre'::text AS vendor,
+ CASE WHEN discontinued THEN 'archived' WHEN in_stock THEN 'active' ELSE 'inactive' END AS status,
+ image_url,
+ product_url,
+ price_retail::float AS retail_price,
+ last_scraped AS synced_at
+ FROM scalamandre_pillows WHERE dw_sku = $1 LIMIT 1`,
+ },
+ {
+ source: 'scalamandre_catalog',
+ sql: `SELECT
+ dw_sku AS sku,
+ shopify_product_id::text AS shopify_id,
+ NULL::text AS handle,
+ pattern_name || COALESCE(' · ' || color_name, '') AS title,
+ 'Scalamandre'::text AS vendor,
+ CASE WHEN discontinued THEN 'archived' WHEN in_stock THEN 'active' ELSE 'inactive' END AS status,
+ image_url,
+ product_url,
+ price_retail::float AS retail_price,
+ last_scraped AS synced_at
+ FROM scalamandre_catalog WHERE dw_sku = $1 LIMIT 1`,
+ },
+ {
+ source: 'kravet_catalog',
+ sql: `SELECT
+ dw_sku AS sku,
+ NULL::text AS shopify_id,
+ NULL::text AS handle,
+ pattern_name || COALESCE(' · ' || color_name, '') AS title,
+ 'Kravet'::text AS vendor,
+ COALESCE(status, 'unknown') AS status,
+ image_url,
+ NULL::text AS product_url,
+ price_retail::float AS retail_price,
+ last_scraped AS synced_at
+ FROM kravet_catalog WHERE dw_sku = $1 LIMIT 1`,
+ },
+ {
+ source: 'schumacher_catalog',
+ sql: `SELECT
+ dw_sku AS sku,
+ NULL::text AS shopify_id,
+ NULL::text AS handle,
+ pattern_name || COALESCE(' · ' || color_name, '') AS title,
+ 'Schumacher'::text AS vendor,
+ 'unknown'::text AS status,
+ image_url,
+ NULL::text AS product_url,
+ retail_price::float AS retail_price,
+ price_fetched_at AS synced_at
+ FROM schumacher_catalog WHERE dw_sku = $1 LIMIT 1`,
+ },
+ {
+ source: 'sisterparish_catalog',
+ sql: `SELECT
+ handle AS sku,
+ shopify_product_id::text AS shopify_id,
+ shopify_handle AS handle,
+ title,
+ vendor,
+ 'unknown'::text AS status,
+ primary_image AS image_url,
+ source_url AS product_url,
+ NULLIF(variants->0->>'dw_retail', '')::float AS retail_price,
+ updated_at AS synced_at
+ FROM sisterparish_catalog
+ WHERE handle = $1 OR shopify_handle = $1 LIMIT 1`,
+ },
+];
+
async function lookupSkuInPg(sku) {
if (!dwPool) return null;
+ // 1. Primary: shopify_products (the master synced table). Most DW SKUs
+ // that have been synced to Shopify live here with rich metadata.
try {
const r = await dwPool.query(
`SELECT shopify_id, handle, title, vendor, status, image_url,
- created_at_shopify, updated_at_shopify, synced_at, tags
+ created_at_shopify, updated_at_shopify, synced_at, tags,
+ 'shopify_products'::text AS _source
FROM shopify_products
WHERE sku = $1 OR variant_sku = $1 OR dw_sku = $1
LIMIT 1`,
[sku]
);
- return r.rows[0] || null;
+ if (r.rows[0]) return r.rows[0];
} catch (e) {
- console.error('[dw pg]', e.message);
- return null;
+ console.error('[dw pg/shopify_products]', e.message);
+ }
+ // 2. Fallback: sister catalogs in priority order. The DWSC-700* pillows
+ // Steve worked through earlier in the day live in scalamandre_pillows,
+ // NOT in shopify_products — without this fallback the lookup returns
+ // null and Big Red has no facts to phrase from.
+ for (const q of SISTER_CATALOG_QUERIES) {
+ try {
+ const r = await dwPool.query(q.sql, [sku]);
+ if (r.rows[0]) {
+ return { ...r.rows[0], _source: q.source };
+ }
+ } catch (e) {
+ // A single bad catalog (e.g. schema drift, missing column) shouldn't
+ // kill the whole fallback chain — log and continue.
+ console.error(`[dw pg/${q.source}]`, e.message);
+ }
}
+ return null;
}
// In-memory cache for live Shopify SKU lookups. 60s TTL — long enough to
← 8379170 feat(big-red/tick2): Shopify lookup 60s TTL cache (next: SKU
·
back to Big Red
·
snapshot: 4 file(s) changed, +2 new, ~2 modified 95b5ea0 →