← back to Dw Pairs Well
WS-3: /api/colorways + /api/collection-grouped (interim T2 grouping key, prod pattern_id ready)
901bbdd30a4c739143e6b1d4e4dfe73e300118c1 · 2026-06-26 00:13:54 -0700 · Steve Abrams
Files touched
Diff
commit 901bbdd30a4c739143e6b1d4e4dfe73e300118c1
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Fri Jun 26 00:13:54 2026 -0700
WS-3: /api/colorways + /api/collection-grouped (interim T2 grouping key, prod pattern_id ready)
---
server.js | 309 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 file changed, 309 insertions(+)
diff --git a/server.js b/server.js
index 82ab90b..a668e51 100644
--- a/server.js
+++ b/server.js
@@ -7,6 +7,8 @@ const { hexToLab, deltaE2000, safeParseHexArray } = require('./lib/color');
const { nearestPaintsBatch } = require('./lib/paint');
const { generateCoordinates, renderCustom } = require('./lib/ai-stripes');
+const { COLORS } = require('./lib/classify');
+
const PORT = Number(process.env.PORT || 9799);
const DATABASE_URL = process.env.DATABASE_URL;
const CORS_ORIGINS = (process.env.CORS_ORIGIN || '*')
@@ -49,6 +51,9 @@ app.get('/healthz', async (_req, res) => {
});
const SAFE_KEY = /^[A-Za-z0-9._\-]{1,128}$/;
+// pattern_id grouping keys embed "::" segments (vendorSlug::source::root), so they
+// need a wider charset than SAFE_KEY (which gates handles/SKUs).
+const SAFE_PATTERN_ID = /^[A-Za-z0-9._:∅\- ]{1,200}$/;
const HEX_RE = /^#?[0-9a-fA-F]{3}([0-9a-fA-F]{3})?$/;
// Parse a `palette=` query — comma-separated hex codes. Used by external sources
@@ -379,6 +384,310 @@ function pickSimilar(source, candidates, k = 12) {
return out;
}
+// ============================================================
+// WS-3 — COLORWAYS BACKBONE (collapse colorways → one tile per pattern)
+//
+// In PRODUCTION the grouping key is the Shopify metafield `custom.pattern_id`
+// written by tools/pattern-id-backfill.js (GATED). That metafield is NOT in
+// the read-only mirror, so for LOCAL exercisability TODAY these endpoints
+// derive an INTERIM grouping key with the SAME T2-title-base + series-aware
+// mfr_root logic used in pattern-id-backfill.js. When a `pattern_id` column
+// later exists in the mirror (or metafields jsonb carries custom.pattern_id),
+// groupKeyForRow() reads it first and the interim derivation is the fallback.
+//
+// Key precedence per row (mirrors pattern-id-backfill.js):
+// 1. custom.pattern_id metafield (prod truth) — read from metafields jsonb
+// 2. series-aware mfr_root over mirror.mfr_sku → `${vendorSlug}::mfr::${root}`
+// 3. T2 normalized title minus color words → `${vendorSlug}::t2::${t2}`
+// 4. pattern_name last resort → `${vendorSlug}::pn::${key}`
+// ============================================================
+
+const GROUP_NOISE = [
+ 'wallcovering','wallcoverings','wallpaper','wall covering','fabric','fabrics',
+ 'durable vinyl','vinyl','grasscloth','print','commercial','residential',
+ 'type ii','type iii','mural','wall mural','peel and stick','peel-and-stick',
+ 'self adhesive','self-adhesive','by phillipe romano','phillipe romano'
+];
+const GROUP_COLOR_WORDS = new Set([...COLORS,
+ 'light','dark','deep','pale','soft','warm','cool','muted','bright','antique',
+ 'metallic','natural','neutral','multi','multicolor','multicolour'
+]);
+
+function gStripVendorSuffix(title) {
+ const i = (title || '').indexOf('|');
+ return (i === -1 ? (title || '') : title.slice(0, i)).trim();
+}
+function gNormTitle(title) {
+ let s = gStripVendorSuffix(title).toLowerCase();
+ for (const n of GROUP_NOISE) s = s.split(n).join(' ');
+ s = s.replace(/[^a-z0-9 ]+/g, ' ').replace(/\s+/g, ' ').trim();
+ return s;
+}
+function gStripColors(normalized) {
+ const kept = normalized.split(' ').filter(w => w && !GROUP_COLOR_WORDS.has(w));
+ return kept.join(' ').trim() || normalized;
+}
+// series-aware mfr_sku ROOT — drop a TRAILING colorway suffix, keep series root.
+// TAK-AA04-01 -> TAK-AA04 · FRL5226/01 -> FRL5226 · W7008-01 -> W7008 · AT9604 -> AT9604
+function gMfrRoot(raw) {
+ if (!raw) return null;
+ let s = String(raw).trim().toUpperCase();
+ if (!s) return null;
+ const tokens = s.split(/[-/_.\s]+/).filter(Boolean);
+ if (tokens.length >= 2) {
+ const last = tokens[tokens.length - 1];
+ if (/^\d{1,3}[A-Z]?$/.test(last)) return tokens.slice(0, -1).join('-');
+ }
+ return tokens.join('-');
+}
+function gVendorSlug(v) {
+ return (v || '∅').toLowerCase().replace(/[^a-z0-9]+/g, '-').replace(/^-|-$/g, '') || 'no-vendor';
+}
+// Read custom.pattern_id from the metafields jsonb if present (prod truth path).
+function patternIdFromMetafields(metafields) {
+ if (!metafields) return null;
+ let mf = metafields;
+ try { if (typeof mf === 'string') mf = JSON.parse(mf); } catch (_) { return null; }
+ if (!mf || typeof mf !== 'object') return null;
+ // accept {custom:{pattern_id:...}} or {"custom.pattern_id":...} or [{namespace,key,value}]
+ if (mf.custom && mf.custom.pattern_id) return String(mf.custom.pattern_id);
+ if (mf['custom.pattern_id']) return String(mf['custom.pattern_id']);
+ if (Array.isArray(mf)) {
+ const hit = mf.find(m => m && m.namespace === 'custom' && m.key === 'pattern_id' && m.value);
+ if (hit) return String(hit.value);
+ }
+ return null;
+}
+// THE canonical grouping key for a product row. Prod pattern_id first, else interim.
+function groupKeyForRow(row) {
+ const prod = patternIdFromMetafields(row.metafields);
+ if (prod) return prod;
+ const vslug = gVendorSlug(row.vendor);
+ const root = gMfrRoot(row.mfr_sku);
+ if (root) return `${vslug}::mfr::${root}`;
+ const t2 = gStripColors(gNormTitle(row.title));
+ if (t2) return `${vslug}::t2::${t2}`;
+ const pn = (row.pattern_name || '').toLowerCase().trim();
+ return `${vslug}::pn::${pn || 'ungrouped'}`;
+}
+
+// Extract the REAL (scraped) colorway NAME for a card — never an AI color, never "Unknown".
+// DW title shape is `Pattern RealColor[ - AIColorDescriptor Type] | Brand`. The real color
+// is the words AFTER the pattern base and BEFORE any " - " AI descriptor. We compute the
+// pattern base from the mfr_root group members' shared title prefix is overkill here, so we
+// approximate: take the part before " - " (drops the AI descriptor), strip pattern/noise/AI
+// words, and what remains is the manufacturer colorway name.
+// Sources, in order:
+// 1. real colorway phrase recovered from the title's pre-dash segment
+// 2. enrichment color_family (tag-grade fallback)
+// 3. the bare title base (never "Unknown")
+function colorNameForRow(row, patternBaseWords) {
+ const base = gStripVendorSuffix(row.title); // "Zendo Toffee - Beige Fabric"
+ const preDash = base.split(' - ')[0].trim(); // "Zendo Toffee"
+ // normalize the pre-dash segment, then remove pattern words + noise + AI color words
+ let norm = preDash.toLowerCase();
+ for (const n of GROUP_NOISE) norm = norm.split(n).join(' ');
+ norm = norm.replace(/[^a-z0-9 ]+/g, ' ').replace(/\s+/g, ' ').trim();
+ const patSet = new Set(patternBaseWords || []);
+ const colorWords = norm.split(' ').filter(w => w && !patSet.has(w) && !GROUP_COLOR_WORDS.has(w));
+ if (colorWords.length) {
+ return colorWords.map(w => w.charAt(0).toUpperCase() + w.slice(1)).join(' ');
+ }
+ // if removing pattern words left nothing real, try the words present before-dash minus pattern only
+ const colorWords2 = norm.split(' ').filter(w => w && !patSet.has(w));
+ if (colorWords2.length) {
+ return colorWords2.map(w => w.charAt(0).toUpperCase() + w.slice(1)).join(' ');
+ }
+ if (row.color_family) {
+ const cf = String(row.color_family).trim();
+ if (cf) return cf.charAt(0).toUpperCase() + cf.slice(1);
+ }
+ return base || null;
+}
+
+// Resolve a single product to its group key (used when caller passes handle/dw_sku).
+async function resolveGroupKey({ dw_sku, handle, pattern_id }) {
+ if (pattern_id) return pattern_id;
+ const sel = `dw_sku, handle, title, vendor, mfr_sku, pattern_name, metafields`;
+ let q, params;
+ if (dw_sku) { q = `SELECT ${sel} FROM shopify_products WHERE dw_sku = $1 LIMIT 1`; params = [dw_sku]; }
+ else if (handle) { q = `SELECT ${sel} FROM shopify_products WHERE handle = $1 LIMIT 1`; params = [handle]; }
+ else return null;
+ const r = await pool.query(q, params);
+ if (!r.rows[0]) return null;
+ return groupKeyForRow(r.rows[0]);
+}
+
+// GET /api/colorways?pattern_id=… | ?handle=… | ?dw_sku=…
+// → { pattern_id, pattern, vendor, count, colorways:[{dw_sku,handle,title,color_name,color_hex,image_url}] }
+app.get('/api/colorways', async (req, res) => {
+ try {
+ const pattern_id = req.query.pattern_id && SAFE_PATTERN_ID.test(req.query.pattern_id) ? req.query.pattern_id : null;
+ const dw_sku = req.query.dw_sku && SAFE_KEY.test(req.query.dw_sku) ? req.query.dw_sku : null;
+ const handle = req.query.handle && SAFE_KEY.test(req.query.handle) ? req.query.handle : null;
+ if (!pattern_id && !dw_sku && !handle) {
+ return res.status(400).json({ ok: false, error: 'pass ?pattern_id=…, ?handle=…, or ?dw_sku=…' });
+ }
+
+ const key = await resolveGroupKey({ dw_sku, handle, pattern_id });
+ if (!key) return res.status(404).json({ ok: false, error: 'group not found for that handle/sku' });
+
+ // Pull all ACTIVE products in this vendor's space, group in JS by groupKeyForRow,
+ // and return the members of the matched key. Vendor-scoped narrows the scan since
+ // the key embeds the vendor slug as its first segment.
+ const vendorSlug = key.split('::')[0];
+ const r = await pool.query(`
+ SELECT sp.dw_sku, sp.handle, sp.title, sp.vendor, sp.mfr_sku, sp.pattern_name,
+ sp.image_url, sp.metafields, sce.color_family, sce.dominant_hex, sce.background_hex
+ FROM shopify_products sp
+ LEFT JOIN shopify_color_enrichment sce ON sce.shopify_id = sp.shopify_id
+ WHERE sp.status = 'ACTIVE' AND sp.title IS NOT NULL AND sp.handle IS NOT NULL
+ AND sp.dw_sku IS NOT NULL
+ AND lower(regexp_replace(coalesce(sp.vendor,'∅'),'[^a-zA-Z0-9]+','-','g')) LIKE $1
+ `, [vendorSlug.replace(/[%_]/g, '\\$&') + '%']);
+
+ const members = r.rows.filter(row => groupKeyForRow(row) === key);
+ if (!members.length) return res.status(404).json({ ok: false, error: 'group empty' });
+
+ // Pattern label = the WORDS COMMON to every member's pre-dash title base (the real
+ // shared pattern name), Title-Cased. Common-prefix is more robust than one member's T2.
+ const memberBases = members.map(m => {
+ let n = gStripVendorSuffix(m.title).split(' - ')[0].toLowerCase();
+ for (const x of GROUP_NOISE) n = n.split(x).join(' ');
+ return n.replace(/[^a-z0-9 ]+/g, ' ').replace(/\s+/g, ' ').trim().split(' ').filter(Boolean);
+ });
+ // intersection of word-sets across members = the shared pattern words
+ let patternBaseWords = memberBases.length ? memberBases[0].slice() : [];
+ for (const wb of memberBases.slice(1)) {
+ const set = new Set(wb);
+ patternBaseWords = patternBaseWords.filter(w => set.has(w));
+ }
+ // if a single-member group, the pattern base = its T2 stripped title
+ if (members.length === 1) {
+ patternBaseWords = (gStripColors(gNormTitle(members[0].title)) || '').split(' ').filter(Boolean);
+ }
+ const labelBase = patternBaseWords.length
+ ? patternBaseWords.join(' ')
+ : (gStripColors(gNormTitle(members[0].title)) || members[0].pattern_name || '');
+ const pattern = labelBase
+ ? labelBase.replace(/\b\w/g, c => c.toUpperCase())
+ : (members[0].pattern_name || members[0].dw_sku);
+ const patSetWords = new Set(patternBaseWords);
+
+ const colorways = members
+ .map(row => ({
+ dw_sku: row.dw_sku,
+ handle: row.handle,
+ title: row.title,
+ color_name: colorNameForRow(row, patSetWords) || row.dw_sku,
+ color_hex: row.dominant_hex || row.background_hex || null,
+ image_url: row.image_url || null,
+ url: `/products/${row.handle}`
+ }))
+ .sort((a, b) => String(a.color_name).localeCompare(String(b.color_name)));
+
+ res.set('Cache-Control', 'public, max-age=300');
+ res.json({
+ ok: true,
+ pattern_id: key,
+ pattern,
+ vendor: members[0].vendor || null,
+ count: colorways.length,
+ colorways
+ });
+ } catch (e) {
+ console.error('colorways error', e);
+ res.status(500).json({ ok: false, error: e.message });
+ }
+});
+
+// GET /api/collection-grouped?handle=<collection-handle>&page=N&per=48
+// The WS-2 grouped-grid feed. Returns ONE tile per pattern_id for the named
+// collection, with a colorway count per tile, correct across pagination
+// because grouping happens server-side over the WHOLE collection before paging.
+//
+// NOTE: the read-only mirror has no collection-membership table, so for LOCAL
+// testing this accepts ?vendor=<name> (group within a vendor) or ?all=1 (whole
+// active catalog) as the collection proxy. In PRODUCTION the theme passes the
+// collection's product handles (or this reads a collection_products join).
+app.get('/api/collection-grouped', async (req, res) => {
+ try {
+ const vendor = req.query.vendor ? String(req.query.vendor).slice(0, 120) : null;
+ const all = req.query.all === '1';
+ const page = Math.max(1, parseInt(req.query.page, 10) || 1);
+ const per = Math.max(1, Math.min(96, parseInt(req.query.per, 10) || 48));
+ if (!vendor && !all) {
+ return res.status(400).json({ ok: false, error: 'pass ?vendor=<name> or ?all=1 (collection proxy for local testing)' });
+ }
+
+ const params = [];
+ let where = `sp.status = 'ACTIVE' AND sp.title IS NOT NULL AND sp.handle IS NOT NULL AND sp.dw_sku IS NOT NULL AND sp.image_url IS NOT NULL`;
+ if (vendor) { params.push(vendor); where += ` AND sp.vendor = $${params.length}`; }
+
+ const r = await pool.query(`
+ SELECT sp.dw_sku, sp.handle, sp.title, sp.vendor, sp.mfr_sku, sp.pattern_name,
+ sp.image_url, sp.metafields, sp.synced_at, sce.color_family, sce.dominant_hex
+ FROM shopify_products sp
+ LEFT JOIN shopify_color_enrichment sce ON sce.shopify_id = sp.shopify_id
+ WHERE ${where}
+ ORDER BY sp.synced_at DESC NULLS LAST
+ ${all && !vendor ? 'LIMIT 8000' : ''}
+ `, params);
+
+ // group server-side over the FULL set → counts are correct regardless of paging
+ const groups = new Map();
+ for (const row of r.rows) {
+ const key = groupKeyForRow(row);
+ let g = groups.get(key);
+ if (!g) {
+ const labelBase = gStripColors(gNormTitle(row.title)) || (row.pattern_name || '');
+ g = {
+ pattern_id: key,
+ pattern: labelBase ? labelBase.replace(/\b\w/g, c => c.toUpperCase()) : (row.pattern_name || row.dw_sku),
+ vendor: row.vendor || null,
+ // representative tile = first (newest) member
+ rep_handle: row.handle, rep_dw_sku: row.dw_sku, rep_image_url: row.image_url,
+ count: 0, swatches: []
+ };
+ groups.set(key, g);
+ }
+ g.count++;
+ if (g.swatches.length < 6 && (row.dominant_hex)) g.swatches.push(row.dominant_hex);
+ }
+
+ const arr = [...groups.values()];
+ const total_tiles = arr.length;
+ const start = (page - 1) * per;
+ const slice = arr.slice(start, start + per);
+
+ res.set('Cache-Control', 'public, max-age=180');
+ res.json({
+ ok: true,
+ collection: vendor ? { vendor } : { all: true },
+ page, per,
+ total_tiles,
+ total_pages: Math.ceil(total_tiles / per),
+ has_next: start + per < total_tiles,
+ tiles: slice.map(g => ({
+ pattern_id: g.pattern_id,
+ pattern: g.pattern,
+ vendor: g.vendor,
+ count: g.count,
+ colors_label: g.count > 1 ? `${g.count} Colors` : '1 Color',
+ image_url: g.rep_image_url,
+ handle: g.rep_handle,
+ dw_sku: g.rep_dw_sku,
+ swatches: g.swatches,
+ pattern_url: `/pages/pattern?pattern_id=${encodeURIComponent(g.pattern_id)}`,
+ product_url: `/products/${g.rep_handle}`
+ }))
+ });
+ } catch (e) {
+ console.error('collection-grouped error', e);
+ res.status(500).json({ ok: false, error: e.message });
+ }
+});
+
app.get('/api/similar', async (req, res) => {
try {
const dw_sku = req.query.dw_sku && SAFE_KEY.test(req.query.dw_sku) ? req.query.dw_sku : null;
← 90dbcaf Add pattern-id-backfill dry-run (Option-A hybrid) + texture-
·
back to Dw Pairs Well
·
collection-grouped: real sort (hue/style/colorways/az) in fe 99f1752 →