← back to All Designerwallcoverings
auto-save: 2026-07-23T12:20:57 (1 files) — server.js
fde98d3919c29b6923f515b7b55df2fb63e1bd44 · 2026-07-23 12:20:58 -0700 · Steve Abrams
Files touched
Diff
commit fde98d3919c29b6923f515b7b55df2fb63e1bd44
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Thu Jul 23 12:20:58 2026 -0700
auto-save: 2026-07-23T12:20:57 (1 files) — server.js
---
server.js | 121 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
1 file changed, 118 insertions(+), 3 deletions(-)
diff --git a/server.js b/server.js
index e4e2a72..2eafea5 100644
--- a/server.js
+++ b/server.js
@@ -673,6 +673,107 @@ async function loadMicrositeExtras() {
MICROSITE_FEEDS.map((f) => `${f.vendor}=${perSite[f.vendor] ?? 0}`).join(' · '));
}
+// ── catalog-vendor extras — EVERY genuine vendor line into all.dw ─────────────────
+// Steve 2026-07-23 ("all vendors must add to all"): the ~90 dw_unified <vendor>_catalog
+// tables that are NOT already on Shopify become additive searchable rows, so all.dw is
+// truly "every vendor". Same row shape as the microsite feeds (deriveMicrositeRow).
+// HARD RULES honored:
+// • EXCLUDE the mega non-vendor dumps (vendor_catalog / connie×2 / spoonflower) + dups/drafts.
+// • Skip any row whose vendor is ALREADY a live Shopify vendor (no double-count).
+// • Public feed drops BANNED private-label mills (same as Shopify rows); the internal
+// auth-gated feed keeps them (sanitized), exactly like ROWS_INTERNAL already does.
+// • Public-safe columns ONLY — cost/net/wholesale are never selected; retail price is
+// deliberately dropped to null (scraped catalog prices incl. the $4.25 sample trap are
+// unreliable — these render as inquire/quote lines).
+let CATALOG_ROWS = [];
+const CATALOG_EXCLUDE = new Set([
+ 'vendor_catalog', 'connie_our_catalog', 'connie_competitor_catalog', 'spoonflower_catalog',
+ 'rebelwalls_catalog', 'greenland_full_catalog', 'pj_draft_catalog',
+ 'daisy_bennett_v2_catalog', 'daisy_bennett_printed_catalog', 'twil_karin_catalog',
+ 'quadrille_house_catalog', 'sanderson_trade_catalog', 'york_contract_catalog',
+ 'pierre_frey_fabric_catalog',
+ // already loaded as dedicated microsite feeds
+ 'fromental_catalog', 'gracie_catalog', 'zuber_catalog', 'crezana_catalog',
+]);
+const CAT_CAND = {
+ title: ['pattern_name', 'product_name', 'title', 'name', 'pattern', 'design_name', 'style_name'],
+ sku: ['dw_sku', 'sku', 'mfr_sku', 'item_number', 'product_code', 'sku_code', 'pattern_number', 'handle'],
+ image: ['image_url', 'image', 'img_url', 'image_src', 'thumbnail', 'thumbnail_url', 'main_image', 'primary_image', 'featured_image'],
+ color: ['colorway', 'color', 'colour', 'color_name'],
+ type: ['product_type', 'type', 'category', 'material_type'],
+ pattern: ['pattern_name', 'pattern', 'design_name', 'collection'],
+ mfr: ['mfr_sku', 'manufacturer_sku', 'item_number', 'product_code', 'pattern_number'],
+ material:['material', 'substrate', 'composition'],
+ created: ['created_at', 'scraped_at', 'imported_at', 'inserted_at', 'updated_at'],
+ vendor: ['vendor', 'brand', 'supplier_name', 'manufacturer', 'maker', 'vendor_name'],
+};
+const CAT_PER_TABLE_CAP = 50000; // backstop; the largest included line is ~14k
+const normKey = (s) => String(s || '').toLowerCase().replace(/[^a-z0-9]/g, '');
+const qi = (id) => '"' + String(id).replace(/"/g, '""') + '"';
+
+async function loadCatalogVendors(liveVendorKeys) {
+ // one pass over information_schema → per-table column list
+ const { rows: cols } = await pool.query(
+ `select table_name, column_name from information_schema.columns
+ where table_schema='public' and table_name ~ '^[a-z0-9_]+_catalog$'`);
+ const byTable = new Map();
+ for (const c of cols) { (byTable.get(c.table_name) || byTable.set(c.table_name, new Set()).get(c.table_name)).add(c.column_name); }
+
+ const out = [];
+ let tablesUsed = 0, tablesSkipped = 0;
+ for (const [table, colset] of byTable) {
+ if (CATALOG_EXCLUDE.has(table)) continue;
+ // The table NAME is the authoritative vendor identity (marburg_catalog → "Marburg").
+ // A per-row vendor column tends to hold distributor/sub-brand noise that misattributes
+ // and drops rows, so we attribute the whole table to its line name.
+ const base = table.replace(/_catalog$/, '');
+ const tableVendor = titleCase(base.replace(/_/g, ' '));
+ if (liveVendorKeys.has(normKey(tableVendor))) { tablesSkipped++; continue; } // already on Shopify
+ const pick = {};
+ for (const [role, cands] of Object.entries(CAT_CAND)) {
+ if (role === 'vendor') continue; // ignore in-table vendor col (see above)
+ const hit = cands.find((c) => colset.has(c)); if (hit) pick[role] = hit;
+ }
+ if (!pick.title && !pick.sku) continue; // nothing renderable
+ const sel = Object.entries(pick).map(([role, col]) => `${qi(col)} as ${role}`).join(', ');
+ let rows;
+ try { ({ rows } = await pool.query(`select ${sel} from ${qi(table)} limit ${CAT_PER_TABLE_CAP}`)); }
+ catch (e) { console.error(`catalog ${table} read failed (skip):`, e.message); continue; }
+ const feedBase = { vendor: tableVendor, slug: slugify(tableVendor) };
+ let used = 0;
+ const seen = new Set();
+ for (const r of rows) {
+ const sku = r.sku || r.mfr || null;
+ // dedup within the table on sku+colorway so distinct colorways survive but exact dups don't.
+ const dk = normKey(sku || r.title || '') + '|' + normKey(r.color || '');
+ if (seen.has(dk)) continue; seen.add(dk);
+ const p = {
+ pattern_name: r.title || r.pattern || null, pattern: r.pattern || null,
+ color_name: r.color || null, sku, mfr_sku: r.mfr || null,
+ image_url: r.image || null, product_type: r.type || null, material: r.material || null,
+ created_at: r.created || null, handle: null, // price intentionally omitted → null
+ };
+ const row = deriveMicrositeRow(p, { ...feedBase, type: r.type || null });
+ row.source = 'catalog';
+ row.id = `cat:${base}:${sku || used}`;
+ out.push(row);
+ if (++used >= CAT_PER_TABLE_CAP) break;
+ }
+ if (used) tablesUsed++;
+ }
+ CATALOG_ROWS = out;
+ console.log(`catalog vendors: ${out.length.toLocaleString()} rows from ${tablesUsed} tables (${tablesSkipped} tables skipped as live-Shopify dups)`);
+}
+
+// Map a catalog/microsite public row into the ROWS_INTERNAL shape (leak-sanitized, no shopify ids).
+function toInternalRow(d) {
+ const { hay, ...rest } = d;
+ return {
+ ...rest, product_id: null, handle: null, tags: '',
+ vendor: sanitizeName(rest.vendor), title: sanitizeName(rest.title), pattern: sanitizeName(rest.pattern),
+ };
+}
+
async function loadSnapshot() {
if (!pool) pool = new Pool({ connectionString: DSN, max: 2 });
const t0 = Date.now();
@@ -690,11 +791,23 @@ async function loadSnapshot() {
try { await loadMicrositeExtras(); } catch (e) { console.error('microsite extras load failed (non-fatal):', e.message); }
FM_MATCHED = new Set(); // repopulated by deriveRow as it joins each row this cycle
const res = await pool.query(await snapSql());
+ // Live Shopify vendor keys (non-archived) — the dedup guard so catalog lines already on
+ // Shopify don't double-count. Normalized (alnum-only) so "Cowtan & Tout" == "cowtan_tout".
+ const liveVendorKeys = new Set();
+ for (const r of res.rows) {
+ const st = String(r.status || '').toUpperCase();
+ if (r.vendor && st !== 'ARCHIVED' && st !== 'DELETED_FROM_SHOPIFY') liveVendorKeys.add(normKey(r.vendor));
+ }
+ // Every genuine catalog-only vendor line → additive searchable rows. Non-fatal: on failure
+ // CATALOG_ROWS keeps its prior value (or empty on first boot) — the grid is unaffected.
+ try { await loadCatalogVendors(liveVendorKeys); } catch (e) { console.error('catalog vendors load failed (non-fatal):', e.message); }
ROWS = res.rows
.filter((r) => !BANNED.test(r.vendor || '') && !BANNED.test(r.title || ''))
.map(deriveRow)
// ...then append the microsite-sourced rows so they're searchable + facetable in the same grid.
- .concat(MICROSITE_ROWS);
+ .concat(MICROSITE_ROWS)
+ // ...and every genuine catalog-only vendor line, minus the banned private-label mills.
+ .concat(CATALOG_ROWS.filter((r) => !BANNED.test(r.vendor || '') && !BANNED.test(r.title || '')));
// INTERNAL feed — EVERY non-archived SKU (incl. private-label, NO banned-exclusion), leak-sanitized,
// plus the extra fields internal consumers (photo + substitute apps) need but the public API omits:
// product_id (numeric, for Shopify photo push), handle (for product-page URLs), tags (for material/
@@ -714,7 +827,9 @@ async function loadSnapshot() {
vendor: sanitizeName(rest.vendor), title: sanitizeName(rest.title), pattern: sanitizeName(rest.pattern),
};
})
- .filter(Boolean);
+ .filter(Boolean)
+ // catalog vendors join the internal feed too (ALL of them, incl. private-label mills — sanitized).
+ .concat(CATALOG_ROWS.filter((r) => r.lifecycle !== 'Archived').map(toInternalRow));
// FM stats (for the grid header + /healthz). matched/mismatch counted over the PUBLIC rows;
// fm_only_live = FM live Masters that never joined a Shopify row (the opt-in ?fm_only=1 set).
FM_STATS.matched = ROWS.filter((r) => r.fm_present).length;
@@ -724,7 +839,7 @@ async function loadSnapshot() {
for (const [k, fm] of FM) { if (!fm.is_discontinued && (!fm.record_type || fm.record_type === 'Master') && !FM_MATCHED.has(k)) fmOnly++; }
FM_STATS.fm_only_live = fmOnly;
LOADED_AT = new Date().toISOString();
- console.log(`snapshot: ${ROWS.length.toLocaleString()} public (incl. ${MICROSITE_ROWS.length.toLocaleString()} microsite) + ${ROWS_INTERNAL.length.toLocaleString()} internal products in ${Date.now() - t0}ms (stock ${STOCK.size.toLocaleString()} · viewers ${VENDOR_VIEWER.size})`);
+ console.log(`snapshot: ${ROWS.length.toLocaleString()} public (incl. ${MICROSITE_ROWS.length.toLocaleString()} microsite + ${CATALOG_ROWS.length.toLocaleString()} catalog) + ${ROWS_INTERNAL.length.toLocaleString()} internal products in ${Date.now() - t0}ms (stock ${STOCK.size.toLocaleString()} · viewers ${VENDOR_VIEWER.size})`);
console.log(` filemaker: ${FM_STATS.rows.toLocaleString()} mirror rows · ${FM_STATS.matched.toLocaleString()} joined · ${FM_STATS.mismatch.toLocaleString()} mfr# mismatches · ${discoHidden.toLocaleString()} disco-hidden · ${fmOnly.toLocaleString()} FM-only live`);
}
← eb95b2f auto-save: 2026-07-16T17:13:50 (1 files) — package-lock.json
·
back to All Designerwallcoverings
·
all.dw: async job+poll for metered live check (never 504s th ad479fd →