← back to All Designerwallcoverings
server.js
1642 lines
// all.designerwallcoverings.com — the WHOLE catalog, searchable in one place.
// Pattern: japan-staging-viewer (in-memory rows + paginated /api/products +
// drill-down /api/facets + chip-toggle filters + sort-any-field + infinite scroll).
// Data: dw_unified.shopify_products loaded into RAM at boot, refreshed every 10 min —
// the request path never touches PG, so search over the full catalog is instant and
// the canonical DB needs no new indexes. Public surface: cost/net/wholesale never
// leave the server; only retail price ships.
const http = require('http');
const zlib = require('zlib');
const fs = require('fs');
const os = require('os');
const path = require('path');
const { execFile } = require('child_process');
const { Pool } = require('pg');
try { require('dotenv').config(); } catch {}
const { crawlMicrosites, OUT: MICROSITES } = require('./scripts/crawl-microsites');
const PORT = process.env.PORT || 9958;
const PUB = path.join(__dirname, 'public');
const DATA = path.join(__dirname, 'data', 'vendors.json'); // legacy vendor directory (kept at /vendors.html)
const REFRESH_MS = 10 * 60 * 1000;
const DSN = process.env.DATABASE_URL || 'postgresql:///dw_unified?host=/tmp';
const MIME = {
'.html': 'text/html; charset=utf-8', '.js': 'text/javascript; charset=utf-8',
'.css': 'text/css; charset=utf-8', '.json': 'application/json; charset=utf-8',
'.png': 'image/png', '.jpg': 'image/jpeg', '.svg': 'image/svg+xml',
'.ico': 'image/x-icon', '.webp': 'image/webp',
};
// ── tag lexicons → derived facets ────────────────────────────────────────────────
// Tags are ", "-separated words like "Blue, Coastal, Grasscloth, color:Ecru, …".
// Color families follow the interior-designer wheel order; style + material use the
// working DW tag vocabulary. A tag must match exactly (case-insensitive).
const FAMILY_ORDER = ['Red', 'Orange', 'Brown', 'Yellow', 'Gold', 'Green', 'Teal', 'Blue', 'Purple', 'Pink', 'Beige', 'Cream', 'Black', 'Gray', 'Grey', 'White', 'Silver', 'Metallic', 'Multi'];
const COLOR_TAGS = new Set(FAMILY_ORDER.concat(['Light Blue', 'Dark Blue', 'Navy', 'Light Gray', 'Dark Gray', 'Charcoal', 'Ivory', 'Ecru', 'Taupe', 'Tan', 'Rust', 'Burgundy', 'Coral', 'Lavender', 'Mint', 'Sage', 'Olive', 'Turquoise', 'Aqua', 'Bronze', 'Copper', 'Neutral']).map((s) => s.toLowerCase()));
const STYLE_TAGS = new Set(['Traditional', 'Modern', 'Contemporary', 'Transitional', 'Floral', 'Damask', 'Geometric', 'Stripe', 'Stripes', 'Striped', 'Toile', 'Ikat', 'Chinoiserie', 'Coastal', 'Tropical', 'Botanical', 'Art Deco', 'Mid-Century', 'Minimalist', 'Rustic', 'Vintage', 'Abstract', 'Animal', 'Bird', 'Paisley', 'Plaid', 'Trellis', 'Lattice', 'Medallion', 'Ombre', 'Mural', 'Scenic', 'Texture', 'Textured', 'Solid', 'Novelty', 'Kids', 'Basketweave', 'Herringbone', 'Chevron', 'Moroccan', 'Asian', 'French', 'English', 'Farmhouse', 'Glam', 'Bohemian', 'Industrial'].map((s) => s.toLowerCase()));
const MATERIAL_TAGS = new Set(['Grasscloth', 'Cork', 'Silk', 'Linen', 'Vinyl', 'Paper', 'Non-Woven', 'Nonwoven', 'Fabric', 'Textile', 'Sisal', 'Jute', 'Raffia', 'Hemp', 'Bamboo', 'Wood', 'Leather', 'Suede', 'Velvet', 'Flocked', 'Foil', 'Mylar', 'Mica', 'Glass Beads', 'Glass Beaded', 'Metal', 'Natural', 'Naturals', 'Paperweave', 'Arrowroot', 'Wool', 'Felt', 'Acoustic', 'Type II', 'Type I', 'Peel and Stick', 'Peel & Stick', 'Removable'].map((s) => s.toLowerCase()));
const PRICE_BANDS = [
{ key: '≤$50', test: (p) => p <= 50 },
{ key: '$50–100', test: (p) => p > 50 && p <= 100 },
{ key: '$100–200', test: (p) => p > 100 && p <= 200 },
{ key: '$200–400', test: (p) => p > 200 && p <= 400 },
{ key: '$400+', test: (p) => p > 400 },
];
const PRICE_ORDER = PRICE_BANDS.map((b) => b.key);
// Lifecycle Steve lists on this surface, ordered live → not-yet-live → internal-only → gone.
// 'Internal' = deliberately never front-facing (permanent), distinct from 'Staged' (a candidate
// to go live). Internal lines STAY on this internal grid + facets; they are excluded only from
// the genuinely front-facing paths (public storefront URL + Google feed) — see isInternalRow.
const LIFE_ORDER = ['Active on site', 'Staged', 'Internal', 'Archived'];
const priceBand = (p) => { if (!p || p <= 0) return null; const b = PRICE_BANDS.find((x) => x.test(p)); return b ? b.key : null; };
const titleCase = (s) => s.replace(/\w\S*/g, (t) => t[0].toUpperCase() + t.slice(1));
// Public storefront (custom domain) + internal Shopify admin store handle. The internal
// per-SKU page is the Shopify admin product view, keyed by the product's numeric id
// (extracted from the shopify_id GID). Public surface still ships only public-safe cols.
const STOREFRONT = 'https://designerwallcoverings.com';
const ADMIN_STORE = process.env.SHOPIFY_ADMIN_STORE || 'designer-laboratory-sandbox';
// DW SKU series/book code = the leading alpha run of the SKU (DWTT, RML, GRS, XJG …).
// Deterministic, in-RAM, and never leaks an upstream/private-label name (it's a house code).
const seriesOf = (sku) => { const m = (sku || '').match(/^[A-Za-z]{2,}/); return m ? m[0].toUpperCase() : null; };
const adminUrl = (gid) => { const m = (gid || '').match(/(\d+)\s*$/); return m ? `https://admin.shopify.com/store/${ADMIN_STORE}/products/${m[1]}` : null; };
// Same slugify the crawler uses — a vendor's live microsite host is its INTERNAL line viewer.
const slugify = (v) => String(v || '').toLowerCase().normalize('NFD')
.replace(/[̀-ͯ]/g, '').replace(/&/g, ' and ')
.replace(/[^a-z0-9]+/g, '-').replace(/^-+|-+$/g, '');
// ── FileMaker WALLPAPER mirror join (source-of-truth enrichment) ──────────────────
// FMPro's `comboskuwithdash` (bare, e.g. "DWSW-5000270") matches the Shopify dw_sku once
// the "-SAMPLE" variant suffix is stripped; case-insensitive. That's the single join key
// between the FileMaker master and this catalog (verified: modern traditional-vendor lines
// overlap; modern material series are Shopify-only; the legacy tail is FileMaker-only).
const fmKey = (sku) => String(sku || '').toUpperCase().replace(/-SAMPLE$/, '').trim();
// Normalize an mfr number for MISMATCH comparison only — strip the human notes FMPro appends
// ("SBG3207 -- MUST USE QUOTE #G45" → "SBG3207") plus punctuation/case, so the verification
// flag fires on real identity differences, not formatting noise. Display keeps the raw value.
const cleanMfr = (raw) => String(raw || '').split(/--|·|;|\bMUST\b|\bQUOTE\b/i)[0]
.replace(/[^A-Za-z0-9./-]+/g, '').toUpperCase();
// HARD front-facing rule (same list build-data.py enforced for the vendor directory):
// banned upstream/private-label source names never ship on this public surface — rows
// whose vendor OR title leaks one are excluded (their products surface under the label).
const BANNED = /brewster|york|wallquest|wall\s*quest|newwall|new\s*wall\b|command\s*54|justin\s*david|nicolette\s*mayer|seabrook|chesapeake|nextwall|desima|carlsten/i;
// ── INTERNAL lines — deliberately NEVER front-facing (permanent), distinct from Staged ────────────
// The durable registry lives in the shopify repo (config/internal-lines.json). A product whose
// VENDOR is in that registry, OR that carries the `internal` tag, is INTERNAL: it STAYS on this
// internal grid + facets (all.dw is an internal tool) with lifecycle 'Internal', but is EXCLUDED
// from every genuinely front-facing output (public storefront URL + Google feed). Reused via the
// shopify repo's config; falls back to the four known luxury lines if the file can't be read.
const INTERNAL_REGISTRY_PATHS = [
path.join(os.homedir(), 'Projects', 'Designer-Wallcoverings', 'shopify', 'config', 'internal-lines.json'),
'/root/Projects/Designer-Wallcoverings/shopify/config/internal-lines.json', // Kamatera path
];
let INTERNAL_VENDORS = new Set(['fromental', 'gracie', 'zuber', 'crezana design']);
let INTERNAL_TAG = 'internal';
(function loadInternalRegistry() {
for (const p of INTERNAL_REGISTRY_PATHS) {
try {
const j = JSON.parse(fs.readFileSync(p, 'utf8'));
if (Array.isArray(j.vendors) && j.vendors.length) INTERNAL_VENDORS = new Set(j.vendors.map((v) => String(v).toLowerCase().trim()));
if (typeof j.tag === 'string' && j.tag.trim()) INTERNAL_TAG = j.tag.trim().toLowerCase();
console.log(`internal-lines registry loaded from ${p}: ${[...INTERNAL_VENDORS].join(', ')}`);
return;
} catch { /* try next path */ }
}
console.log('internal-lines registry not found — using built-in fallback (fromental/gracie/zuber/crezana design)');
})();
// True when a DB/microsite row is an INTERNAL line — vendor in the registry OR the `internal` tag.
function isInternalRow(r) {
const v = String(r.vendor || '').toLowerCase().trim();
if (v && INTERNAL_VENDORS.has(v)) return true;
const tags = String(r.tags || '').toLowerCase();
if (tags && new RegExp(`(^|,)\\s*${INTERNAL_TAG}\\s*($|,)`).test(tags)) return true;
return false;
}
// ── INTERNAL full-catalog feed sanitizer (/api/catalog-full) ──────────────────────
// The internal feed (auth-gated) INCLUDES private-label products so the substitute + photo
// tools see EVERY SKU — but the real upstream manufacturer name must NEVER be emitted, even
// into a client letter or a public photo page (Steve HARD rule, dw-leak-scanner). So instead
// of BLUNT-excluding those rows like the public /api/products does, we map each genuine upstream
// brand to the private label DW actually sells it under, then strip any residual upstream token.
// Confident label maps only (memory: WallQuest-family→Malibu, Command54/JustinDavid→Phillipe Romano).
// NB: bare "york" is deliberately NOT a strip token — "New York" in a legit title is not a leak.
const LABEL_MAP = [
[/brewster\s*&?\s*york|brewster|wall\s*quest|wallquest|chesapeake|nextwall|new\s*wall\b|seabrook/gi, 'Malibu Wallpaper'],
[/command\s*54|justin\s*david/gi, 'Phillipe Romano'],
];
const LEAK_TOKENS = /\b(brewster|wallquest|wall\s*quest|chesapeake|nextwall|newwall|new\s*wall|seabrook|command\s*54|justin\s*david|nicolette\s*mayer|desima|carlsten)\b/gi;
function sanitizeName(s) {
if (!s) return s;
let out = String(s);
for (const [re, label] of LABEL_MAP) out = out.replace(re, label);
out = out.replace(LEAK_TOKENS, '').replace(/\s{2,}/g, ' ').replace(/\s*&\s*$/, '').trim();
return out || null;
}
// ── LIVE stock (metered vendor-portal scrape) — the paid /livestock --live path ────
// Distinct from the FREE stored STOCK snapshot. SELF-GATE: a display-vendor can fire live ONLY
// when data/live-stock-coverage.json classifies it live_capable with a wired adapter. That file
// (built by scripts/build-coverage.js) is the single source of truth for enable/dim AND per-tier
// dim reason — no vendor is hardcoded here. Adapters: wallquest (WallQuest-family portal, surfaced
// as "Malibu Wallpaper"/"PS Removable Wallpaper"/…), romo (Romo trade portal), shopify-public
// (public Shopify .js stock), woo-public (wired, kept dim until per-site verified).
const COVERAGE_FILE = path.join(__dirname, 'data', 'live-stock-coverage.json');
let COVERAGE = new Map(); // lower(vendor) → coverage entry
function loadCoverage() {
try {
const d = JSON.parse(fs.readFileSync(COVERAGE_FILE, 'utf8'));
const m = new Map();
for (const [vendor, e] of Object.entries(d.vendors || {})) m.set(String(vendor).toLowerCase(), e);
COVERAGE = m;
console.log(`coverage: ${m.size} display-vendors (live-capable ${(d.counts && d.counts.live_capable) || 0})`);
} catch (e) { console.error('coverage load failed (non-fatal — live checks dim):', e.message); }
}
// Hot-reload: build-coverage.js regenerates the file on a schedule (and after deploys) —
// re-read on mtime change so new live_capable flags flow to /api/products without a restart.
let COVERAGE_MTIME = 0;
setInterval(() => {
fs.stat(COVERAGE_FILE, (err, st) => {
if (err) return;
if (COVERAGE_MTIME && st.mtimeMs !== COVERAGE_MTIME) loadCoverage();
COVERAGE_MTIME = st.mtimeMs;
});
}, 10 * 60 * 1000).unref();
const coverageFor = (vendor) => COVERAGE.get(String(vendor || '').trim().toLowerCase()) || null;
const liveAdapter = (vendor) => { const e = coverageFor(vendor); return (e && e.live_capable && e.adapter) ? e.adapter : null; };
const LIVE_WORKER = process.env.LIVE_WORKER || path.join(__dirname, 'scripts', 'live-scrape.js');
const LIVE_CACHE_MS = 10 * 60 * 1000; // repeat clicks within 10 min = cached, $0
const LIVE_MAX_INFLIGHT = 2; // global burst cap on concurrent portal scrapes
const LIVE_IP_MAX = 6, LIVE_IP_WIN_MS = 60 * 1000; // per-IP: ≤6 live calls / 60s
// Worker hard-kill for a hung/slow portal scrape. Was 52s to stay UNDER the ~60s Cloudflare/
// nginx proxy cap (2026-07-15: a ~63s scrape 504'd through the public URL). That cap no longer
// binds the worker — the async job+poll path (2026-07-27) decouples the HTTP request from the
// scrape, so the initiating request returns instantly and the client polls. Raised to 75s (DTD
// 5/5, 2026-07-27) so a genuine ~63s WallQuest/Romo scrape actually COMPLETES and returns a live
// answer instead of being killed at 52s and falsely surfaced as "timed out — use Email Vendor".
// Cost stays bounded: only the slowest scrapes use the extra ~20s (~$0.03 more at $0.15/session-
// min), under the $5/day kill-switch + the global burst cap of 2. The client poll budget (30×3s
// = ~90s) already exceeds 75s, so a completing scrape resolves before the poll loop gives up.
const LIVE_TIMEOUT_MS = 75 * 1000; // hard kill a hung/slow portal scrape (async/poll decoupled from proxy cap)
const BB_RATE_PER_MIN = 0.15; // pricing.json apis.browserbase.rates.session_min
const BB_MIN_SESSION_MIN = 0.2; // minimum billable Browserbase session (minutes)
const COST_LOG = path.join(os.homedir(), '.claude', 'skills', 'cost-tracker', 'scripts', 'log.js');
const liveCache = new Map(); // UPPER(sku) → { result, at }
const liveInflight = new Map(); // UPPER(sku) → Promise (coalesces concurrent clicks)
const liveIpHits = new Map(); // ip → [timestamps] (per-IP rate window)
// ── DAILY $ KILL-SWITCH ────────────────────────────────────────────────────────────
// A GLOBAL per-calendar-day live-scrape spend ceiling that sits ON TOP of the per-IP +
// concurrent-burst caps (those bound RATE, not the day's TOTAL). Every billed live scrape's
// cost_usd is summed per UTC day and persisted to data/live-spend.json so the tally survives a
// pm2 restart. Once the day's total reaches DAILY_LIVE_CAP_USD the live path HARD-STOPS BEFORE
// any Browserbase session opens — returning a graceful {live_available:false, reason, cost_usd:0}
// degrade the UI dims like any other. Cached/coalesced hits ($0, session already paid) still serve.
const DAILY_LIVE_CAP_USD = Math.max(0.01, +(process.env.DAILY_LIVE_CAP_USD || 5) || 5);
const SPEND_FILE = path.join(__dirname, 'data', 'live-spend.json');
const spendDayKey = () => new Date().toISOString().slice(0, 10); // UTC calendar day
function readSpendMap() { try { return JSON.parse(fs.readFileSync(SPEND_FILE, 'utf8')) || {}; } catch { return {}; } }
function daySpendUSD() { return +(readSpendMap()[spendDayKey()] || 0); }
function addDaySpend(usd) {
if (!(usd > 0)) return daySpendUSD();
const s = readSpendMap(), k = spendDayKey();
s[k] = +((+(s[k] || 0)) + usd).toFixed(4);
// prune keys older than ~14 days so the ledger file stays tiny
const cutoff = new Date(Date.now() - 14 * 864e5).toISOString().slice(0, 10);
for (const key of Object.keys(s)) if (key < cutoff) delete s[key];
try { fs.writeFileSync(SPEND_FILE, JSON.stringify(s)); } catch (e) { console.error('spend persist failed:', e.message); }
console.log(`live-spend: day ${k} → $${s[k].toFixed(4)} / cap $${DAILY_LIVE_CAP_USD.toFixed(2)}`);
return s[k];
}
// ── snapshot load ────────────────────────────────────────────────────────────────
// Public-safe columns ONLY — cost_price / net_price / cost never selected.
// The Mac2 mirror carries rollup columns (min_variant_price, online_store_published)
// that the Kamatera canonical doesn't — probe once and build a portable SELECT.
async function snapSql() {
const { rows } = await pool.query(
`SELECT column_name FROM information_schema.columns
WHERE table_name='shopify_products' AND column_name = ANY($1)`,
[['min_variant_price', 'online_store_published']]);
const has = new Set(rows.map((r) => r.column_name));
const priceExpr = has.has('min_variant_price') ? 'coalesce(price, min_variant_price)' : 'price';
const pubExpr = has.has('online_store_published') ? 'online_store_published' : "(upper(coalesce(status,'')) = 'ACTIVE')";
return `
SELECT id, shopify_id, handle, title, vendor, product_type, dw_sku, variant_sku, sku, pattern_name,
mfr_sku, supplier_name,
tags, upper(coalesce(status,'')) AS status, image_url,
${priceExpr} AS price,
${pubExpr} AS online_store_published,
created_at_shopify, updated_at_shopify
FROM shopify_products`;
// NOTE: DELETED_FROM_SHOPIFY rows are now LOADED and mapped to the 'Archived'
// lifecycle (Steve 2026-07-08: "they should be archived"), not excluded outright —
// so they're classified honestly and reachable only under the Archived filter.
}
let ROWS = [];
let ROWS_INTERNAL = []; // internal full-catalog feed (every non-archived SKU, leak-sanitized)
let MICROSITE_ROWS = []; // extra searchable rows sourced from DW-family microsite full-feeds
// (customer-facing luxury lines NOT in shopify_products — e.g. Fromental,
// Gracie, Zuber, Crezana Design). source='microsite'. See loadMicrositeExtras().
let LOADED_AT = null;
let pool = null;
let FM = new Map(); // fmKey(dw_sku) → FileMaker WALLPAPER mirror row (source of truth)
let FM_MATCHED = new Set(); // fmKeys that joined a shopify_products row this snapshot
let FM_STATS = { rows: 0, matched: 0, discontinued_hidden: 0, mismatch: 0, fm_only_live: 0 };
let IMAGES = new Map(); // product_id → [image urls] full Shopify gallery (from product_images sync)
let STOCK = new Map(); // upper(sku) → public-safe stored stock snapshot (the /livestock FREE path)
let MFR = new Map(); // upper(bare dw_sku) → manufacturer SKU, backfilled from *_catalog tables
let VENDOR_VIEWER = new Map(); // slugify(vendor) / slug → internal line-viewer URL (the vendor's live microsite)
let VENDOR_ITEM = new Map(); // slugify(vendor) / slug → { url, handles:Set } — the microsite's FULL handle set;
// "This Item" deep-links resolve ONLY when the row's handle is IN that set.
// Stored stock snapshot — the FREE /livestock path. PUBLIC-SAFE availability ONLY
// (in_stock / quantity / lead_time / as-of); cost & price columns are NEVER selected.
// Sources: the dedicated stock_checks cache ∪ every *_catalog table's in_stock/last_scraped.
// Loaded into RAM on the snapshot cycle so a card-click never scrapes and never hits a portal.
async function loadStock() {
const map = new Map();
const put = (key, val) => {
if (!key) return;
const k = String(key).toUpperCase();
const prev = map.get(k);
if (!prev || (val.as_of || '') > (prev.as_of || '')) map.set(k, val); // newest reading wins
};
// 1. per-vendor catalog snapshots (broad coverage) — table names come from the DB
// catalog (not user input) and are allow-listed to ^[a-z0-9_]+_catalog$.
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$'
AND column_name IN ('dw_sku','in_stock','last_scraped')`);
const byTab = {};
for (const c of cols) (byTab[c.table_name] ||= new Set()).add(c.column_name);
const parts = [];
for (const [t, set] of Object.entries(byTab)) {
if (!set.has('dw_sku') || !set.has('in_stock')) continue;
const asof = set.has('last_scraped') ? 'last_scraped::text' : 'NULL::text';
parts.push(`SELECT dw_sku, in_stock::text AS in_stock, ${asof} AS as_of FROM "${t}" WHERE in_stock IS NOT NULL AND dw_sku IS NOT NULL`);
}
if (parts.length) {
const { rows } = await pool.query(parts.join(' UNION ALL '));
for (const r of rows) put(r.dw_sku, { in_stock: r.in_stock === 'true' || r.in_stock === 't', as_of: r.as_of ? r.as_of.slice(0, 10) : null, source: 'catalog' });
}
// 2. the dedicated stock-check cache (also carries quantity + lead time), keyed both ways
try {
const { rows } = await pool.query(
`SELECT dw_sku, mfr_sku, in_stock, quantity, lead_time, checked_at::text AS as_of, source FROM stock_checks`);
for (const r of rows) {
const val = { in_stock: r.in_stock, quantity: r.quantity, lead_time: r.lead_time || null, as_of: r.as_of ? r.as_of.slice(0, 10) : null, source: r.source || 'stock_check' };
put(r.dw_sku, val); put(r.mfr_sku, val);
}
} catch { /* stock_checks optional */ }
STOCK = map;
}
// Manufacturer-SKU backfill — upper(bare dw_sku) → mfr_sku, from every *_catalog table.
// The Shopify sync populates shopify_products from variant SKUs, NOT the manufacturer_sku
// metafield, so newer lines (e.g. Pierre Frey) have an empty mfr_sku and their manufacturer
// SKU (BP327001) is invisible to grid search. This backfills it from the dw_unified staging
// catalogs so mfr-SKU search + the mfr_number display work for every catalog-backed vendor.
async function loadMfr() {
const map = new Map();
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$'
AND column_name IN ('dw_sku','mfr_sku')`);
const byTab = {};
for (const c of cols) (byTab[c.table_name] ||= new Set()).add(c.column_name);
const parts = [];
for (const [t, set] of Object.entries(byTab)) {
if (set.has('dw_sku') && set.has('mfr_sku'))
parts.push(`SELECT dw_sku, mfr_sku FROM "${t}" WHERE dw_sku IS NOT NULL AND mfr_sku IS NOT NULL`);
}
if (parts.length) {
const { rows } = await pool.query(parts.join(' UNION ALL '));
for (const r of rows) { const k = String(r.dw_sku).toUpperCase(); if (!map.has(k)) map.set(k, r.mfr_sku); }
}
// Curated Kravet-family backfill (scripts/backfill-kravet-mfr.js — title/price-consensus/
// spec/local-model tiers) OVERRIDES the generic catalog map for its SKUs. Table optional.
try {
const { rows } = await pool.query('SELECT base_sku, mfr_sku FROM kravet_mfr_backfill');
for (const r of rows) map.set(String(r.base_sku).toUpperCase(), r.mfr_sku);
} catch { /* backfill table not present — fine */ }
MFR = map;
}
// FileMaker WALLPAPER mirror (dw_unified.filemaker_wallpaper, written by
// scripts/fm-wallpaper-sync.mjs). FMPro is Steve's SOURCE OF TRUTH: "is this live?"
// (Date Discontinued), the authoritative mfr number, width + specs, and the source for
// the FileMaker→Shopify push. Loaded into RAM on the snapshot cycle; a missing/empty
// table just means no FM enrichment (fully graceful — the grid is unchanged).
async function loadFileMaker() {
const map = new Map();
try {
const { rows } = await pool.query(
`SELECT dw_sku, mfr_number, mfr_number_clean, name_of_pattern, color_of_pattern,
title, series, vid, is_discontinued, date_discontinued, width, specs,
shopify_handle, dw_retail_price, record_type
FROM filemaker_wallpaper`);
for (const r of rows) { const k = fmKey(r.dw_sku); if (k) map.set(k, r); }
} catch (e) { console.error('filemaker mirror load failed (non-fatal — no FM enrichment):', e.message); }
FM = map;
FM_STATS.rows = map.size;
}
// Full Shopify galleries (dw_unified.product_images, written by scripts/shopify-images-sync.mjs).
// Steve: every non-archived/non-disco product should carry ALL its images across all./substitute/new.
// Keyed by numeric product_id (the shopify_id tail) so the join needs no SKU normalization. A missing/
// empty table just means single-image behavior (fully graceful).
async function loadImages() {
const map = new Map();
try {
const { rows } = await pool.query(
`SELECT product_id, image_url FROM product_images ORDER BY product_id, position`);
for (const r of rows) { const a = map.get(r.product_id); if (a) a.push(r.image_url); else map.set(r.product_id, [r.image_url]); }
} catch (e) { console.error('product_images load failed (non-fatal — single image only):', e.message); }
IMAGES = map;
}
// Internal line viewer per product line = the vendor's live microsite (astek-landing style).
// Resolved from the crawl artifact so ANY microsite that exists resolves with no code change;
// vendors without a live line viewer degrade gracefully (the control dims).
function buildVendorViewer() {
const map = new Map();
const item = new Map(); // slug → { url, handles:Set } for per-PRODUCT deep-link membership
try {
const d = JSON.parse(fs.readFileSync(MICROSITES, 'utf8'));
for (const s of (d.sites || [])) {
if (!s.up || !s.host || BANNED.test(s.host)) continue;
const url = `https://${s.host}/`;
// "This Item" deep-links to /product/<handle>. Since microsite handle == dw_unified.handle,
// the link is guaranteed live ONLY when THIS product's handle is actually in the microsite's
// FULL handle set (captured by the crawl). We never trust a 24-sample or a bare vendor match:
// • gated (401) sites → handles:[] (feed not fetchable) → dims, never a dead-end
// • broken/empty vhosts → handles:[] (up-200-but-no-feed, e.g. daisybennett) → dims
// • a dw product not carried by → handle ∉ set → dims (the ~15-25% dead-end this fixes)
// that particular microsite
const handles = Array.isArray(s.handles) ? s.handles : [];
const entry = handles.length ? { url, handles: new Set(handles) } : null;
const setBoth = (k) => { map.set(k, url); if (entry) item.set(k, entry); };
if (s.vendor && !BANNED.test(s.vendor)) setBoth(slugify(s.vendor));
if (s.slug) setBoth(String(s.slug).toLowerCase());
}
} catch { /* crawl artifact not ready yet — viewers resolve after the first crawl lands */ }
VENDOR_VIEWER = map;
VENDOR_ITEM = item;
}
function deriveRow(r) {
const tags = (r.tags || '').split(',').map((t) => t.trim()).filter(Boolean);
const colors = [], styles = [], materials = [];
for (const t of tags) {
const lc = t.toLowerCase();
const bare = lc.startsWith('color:') ? lc.slice(6).trim() : lc;
if (COLOR_TAGS.has(bare)) colors.push(titleCase(bare));
if (STYLE_TAGS.has(lc)) styles.push(titleCase(lc));
if (MATERIAL_TAGS.has(lc)) materials.push(titleCase(lc));
}
const price = r.price != null ? parseFloat(r.price) : null;
// "Active on site" only when the ACTIVE product is actually published to the Online
// Store — Shopify status and sales-channel publish state are orthogonal, so an
// ACTIVE-but-unpublished SKU (never went live) reads as Staged, same as a DRAFT.
// INTERNAL is checked FIRST (before Staged/Active): a registry vendor or `internal`-tagged
// product is deliberately never front-facing, so it reads 'Internal' regardless of Shopify
// status — EXCEPT still honor 'Archived' (a dead internal line is gone, not internal-live).
const lifecycle = (r.status === 'ARCHIVED' || r.status === 'DELETED_FROM_SHOPIFY') ? 'Archived'
: isInternalRow(r) ? 'Internal'
: r.status === 'DRAFT' ? 'Staged'
: (r.status === 'ACTIVE' && r.online_store_published) ? 'Active on site'
: 'Staged';
const sku = r.dw_sku || r.variant_sku || r.sku || null;
// FileMaker WALLPAPER join (source of truth). Match on the bare, -SAMPLE-stripped dw_sku.
const fmk = fmKey(r.dw_sku || r.variant_sku || r.sku);
const fm = (fmk && FM.get(fmk)) || null;
if (fm) FM_MATCHED.add(fmk);
// manufacturer SKU: prefer the synced shopify_products.mfr_sku; else backfill from the
// *_catalog staging tables by bare dw_sku (the sync doesn't populate mfr_sku for newer
// lines like Pierre Frey → BP327001 would otherwise be invisible to search).
const bareDw = String(r.dw_sku || r.variant_sku || r.sku || '').replace(/-sample$/i, '').toUpperCase();
const shopMfr = r.mfr_sku || (bareDw && MFR.get(bareDw)) || null;
const fmMfr = fm ? (fm.mfr_number || null) : null;
// mfr_mismatch: both sides present AND their normalized tokens disagree → the grid flags it
// (the mfr-number verification workflow Steve does by hand in FileMaker's "verification" layouts).
const mfrMismatch = !!(fm && fmMfr && shopMfr && cleanMfr(fmMfr) !== cleanMfr(shopMfr));
// Full Shopify gallery (joined by numeric product_id). The primary is the mirror's featured
// image_url, gap-filled from the gallery when the featured is missing; `images` is the deduped
// set (primary first) so every surface can show all images.
const pid = (String(r.shopify_id || '').match(/(\d+)\s*$/) || [])[1] || null;
const gallery = (pid && IMAGES.get(pid)) || [];
const primaryImg = r.image_url || gallery[0] || null;
const images = primaryImg ? [primaryImg, ...gallery.filter((u) => u && u !== primaryImg)] : [];
return {
id: r.id,
sku,
series: seriesOf(sku),
title: r.title || '',
vendor: r.vendor || null,
type: r.product_type || null,
pattern: r.pattern_name || null,
// Manufacturer identifiers — admin curation aid (Steve rule: new-SKU go-live gate needs a real
// mfr NAME + mfr SKU number). mfr_number is the clean, ~93%-populated field; mfr_name comes from
// supplier_name and is rendered only when present (it's blank/inconsistent for some private labels).
mfr_number: shopMfr,
mfr_name: r.supplier_name || null,
// ── FileMaker source-of-truth enrichment (null when the SKU isn't in the WALLPAPER file) ──
fm_present: !!fm,
fm_mfr_number: fmMfr, // what FMPro shows as the mfr number
fm_discontinued: !!(fm && fm.is_discontinued), // FMPro Date Discontinued present → hidden by default
fm_disco_date: fm ? (fm.date_discontinued || null) : null,
fm_name: fm ? (fm.name_of_pattern || null) : null,
fm_width: fm ? (fm.width || null) : null,
fm_specs: fm ? (fm.specs || null) : null,
fm_handle: fm ? (fm.shopify_handle || null) : null,
mfr_mismatch: mfrMismatch,
status: r.status || null,
lifecycle,
image: primaryImg,
images,
image_count: images.length,
price: price && price > 0 ? price : null,
price_band: priceBand(price),
colors: [...new Set(colors)],
styles: [...new Set(styles)],
materials: [...new Set(materials)],
image_state: primaryImg ? 'Has image' : 'No image',
// Website = customer-facing storefront page (only when actually live on the Online Store).
// Internal lines are deliberately never front-facing → never emit a public storefront URL,
// even in the impossible case one is published (defense-in-depth behind the activation gate).
url: (r.online_store_published && r.handle && !isInternalRow(r)) ? `${STOREFRONT}/products/${r.handle}` : null,
// Internal cluster — three destinations per SKU, each resolves or the control dims:
// Admin = Shopify admin product view (any row with a shopify_id GID),
// Line Viewer = the vendor's live internal microsite (astek-landing style),
// stock = the FREE stored /livestock snapshot (public-safe availability only).
internal_url: adminUrl(r.shopify_id),
line_viewer_url: r.vendor ? (VENDOR_VIEWER.get(slugify(r.vendor)) || null) : null,
// This Item = the SAME internal microsite, deep-linked to THIS product's /product/<handle>
// page (one item, not the whole line). Resolves ONLY when the row's handle is actually IN
// the microsite's full handle set — since microsite handle == dw_unified.handle, that makes
// the link guaranteed-live. Handle absent (site doesn't carry this product, gated, or broken)
// → null, so the control dims rather than dead-ending on the SPA's client-side "not found".
internal_product_url: (function () {
if (!r.handle || !r.vendor) return null;
const entry = VENDOR_ITEM.get(slugify(r.vendor));
return (entry && entry.handles.has(r.handle)) ? `${entry.url}product/${encodeURIComponent(r.handle)}` : null;
})(),
// Grid SKUs may carry a variant suffix (…-Sample) while STOCK is keyed by bare dw_sku —
// fall back to the stripped key so sample-variant rows still surface the snapshot.
stock: (sku && (STOCK.get(String(sku).toUpperCase())
|| STOCK.get(String(sku).toUpperCase().replace(/-SAMPLE$/, '')))) || null,
// live_capable + live_tier + live_reason come from the committed coverage audit. They drive the
// card's "Check Live 🔄" enable/dim AND the per-tier dim reason (no-creds vs quote-only vs no-stock).
// The endpoint self-gates again server-side against the same coverage entry.
live_capable: !!liveAdapter(r.vendor),
live_tier: (coverageFor(r.vendor) || {}).tier || null,
live_reason: (coverageFor(r.vendor) || {}).reason || 'live check unavailable for this vendor',
created: r.created_at_shopify ? new Date(r.created_at_shopify).getTime() : 0,
updated: r.updated_at_shopify ? new Date(r.updated_at_shopify).getTime() : 0,
// one lowercase haystack per row so search is a single .includes() pass
hay: [r.dw_sku, r.variant_sku, r.sku, r.mfr_sku, shopMfr, r.supplier_name, r.title, r.vendor, r.product_type, r.pattern_name, r.tags].filter(Boolean).join(' ').toLowerCase(),
};
}
// ── DW-family microsite full-feed ingest (customer-facing luxury lines) ────────────
// A handful of luxury vendors (Fromental, Gracie, Zuber, Crezana Design) live ONLY in
// their dw_unified staging tables + their own co-located microsites — they were never
// Shopify-imported, so shopify_products has no rows and the main grid can't see them.
// Steve authorized surfacing them INTERNALLY (no Shopify import, no canonical dw_unified
// write) by pulling each microsite's FULL /api/products feed over localhost (bypassing the
// public 401), normalizing to the grid row shape, and merging into the searchable set.
// These are CUSTOMER-FACING brands (not private-label), so their real vendor names are fine.
// Fully additive + guarded: an unreachable feed is skipped, never breaks the grid.
// Each line's co-located internal viewer serves its FULL catalog at /api/products on a localhost
// port. The port DIFFERS per environment (Mac2 dev vs Kamatera prod), so it's env-overridable:
// set MICROSITE_FEED_PORTS="fromental=PORT,gracie=PORT,zuber=PORT,crezana=PORT" to point at prod's
// ports. Defaults are the Mac2 dev ports. An unset/partial override falls back to the default port.
const MICROSITE_DEFAULT_PORTS = { fromental: 10071, gracie: 10073, zuber: 10074, crezana: 10072 };
const MICROSITE_PORT_OVERRIDE = (() => {
const m = {};
for (const pair of String(process.env.MICROSITE_FEED_PORTS || '').split(',')) {
const [k, v] = pair.split('='); const port = parseInt((v || '').trim(), 10);
if (k && port) m[k.trim().toLowerCase()] = port;
}
return m;
})();
const micrositePort = (slug) => MICROSITE_PORT_OVERRIDE[slug] || MICROSITE_DEFAULT_PORTS[slug];
const micrositeFeedUrl = (slug) => `http://127.0.0.1:${micrositePort(slug)}/api/products?limit=100000`;
const MICROSITE_FEEDS = [
{ vendor: 'Fromental', slug: 'fromental', url: micrositeFeedUrl('fromental'), type: 'Wallcovering' },
{ vendor: 'Gracie', slug: 'gracie', url: micrositeFeedUrl('gracie'), type: 'Wallcovering' },
{ vendor: 'Zuber', slug: 'zuber', url: micrositeFeedUrl('zuber'), type: 'Wallcovering' },
{ vendor: 'Crezana Design', slug: 'crezana', url: micrositeFeedUrl('crezana'), type: 'Fabric' },
];
const MICROSITE_PER_SITE_CAP = 800; // full catalog per site is ≤735 — well under this
// Shared internal cred so the localhost feeds (Basic-Auth gated, the 401 is the healthy signal)
// are readable. Same override the crawler uses; defaults to the standard admin cred.
const MICROSITE_FEED_AUTH = process.env.MICROSITE_BASIC_AUTH || 'admin:DW2024!';
const MICROSITE_FEED_HDR = 'Basic ' + Buffer.from(MICROSITE_FEED_AUTH).toString('base64');
// CF Access service-token headers for astek (Zero-Trust pilot — memo 260727A / TK-11).
// When astek moves behind Cloudflare Access, the on-demand feed proxy needs the service
// token too. Scoped to the astek host ONLY (per-application token). Inert + byte-identical
// (Basic-Auth-only, as today) whenever CF_ACCESS_ASTEK_ID / CF_ACCESS_ASTEK_SECRET are unset.
const CF_ACCESS_ASTEK_ID = process.env.CF_ACCESS_ASTEK_ID || '';
const CF_ACCESS_ASTEK_SECRET = process.env.CF_ACCESS_ASTEK_SECRET || '';
const isAstekHost = (u) => { try { return new URL(u).hostname.toLowerCase() === 'astek.designerwallcoverings.com'; } catch { return false; } };
const MICROSITE_FEED_TIMEOUT_MS = 12000;
// Map a heterogeneous microsite feed row (each of the 4 feeds has a slightly different
// shape) onto the SAME row shape the grid's deriveRow emits, so it's searchable + facetable
// with zero frontend change. Colors/styles/materials run through the SAME tag lexicons the
// DB rows use, seeded from the feed's own AI color/style/tag fields.
function deriveMicrositeRow(p, feed) {
const sku = p.sku || p.dw_sku || p.mfr_sku || null;
const handle = p.handle || null;
const title = [p.pattern || p.pattern_name, (p.color || p.color_name)].filter(Boolean).join(' — ')
|| p.pattern || p.pattern_name || p.title || '';
// gather candidate facet terms from every tag-ish field the feed carries
const rawColorNames = []
.concat(Array.isArray(p.colors) ? p.colors.map((c) => (typeof c === 'string' ? c : c && c.name)) : [])
.concat(Array.isArray(p.ai_colors) ? p.ai_colors.map((c) => (typeof c === 'string' ? c : c && c.name)) : [])
.concat(p.color || p.color_name || []);
const rawStyleNames = []
.concat(Array.isArray(p.styles) ? p.styles : [])
.concat(Array.isArray(p.ai_styles) ? p.ai_styles : []);
const rawTagNames = []
.concat(Array.isArray(p.tags) ? p.tags : [])
.concat(Array.isArray(p.aiTags) ? p.aiTags : [])
.concat(Array.isArray(p.ai_tags) ? p.ai_tags : [])
.concat(p.material ? [p.material] : []);
const colors = [], styles = [], materials = [];
for (const c of rawColorNames) { const lc = String(c || '').toLowerCase().trim(); if (COLOR_TAGS.has(lc)) colors.push(titleCase(lc)); }
for (const s of rawStyleNames) { const lc = String(s || '').toLowerCase().trim(); if (STYLE_TAGS.has(lc)) styles.push(titleCase(lc)); }
// styles + tags can both name a material or a style — match each against both lexicons
for (const t of rawStyleNames.concat(rawTagNames)) {
const lc = String(t || '').toLowerCase().trim();
if (STYLE_TAGS.has(lc)) styles.push(titleCase(lc));
if (MATERIAL_TAGS.has(lc)) materials.push(titleCase(lc));
}
const price = (p.quote === true) ? null : (p.price != null ? parseFloat(p.price) : null);
const gallery = Array.isArray(p.images) ? p.images : Array.isArray(p.gallery) ? p.gallery : [];
const primaryImg = p.image || p.image_url || gallery[0] || null;
const images = primaryImg ? [primaryImg, ...gallery.filter((u) => u && u !== primaryImg)] : [];
const created = p.createdAt || p.created_at || null;
const heur = [sku, p.mfr_sku, title, p.pattern, p.pattern_name, p.collection, feed.vendor,
...rawColorNames, ...rawStyleNames, ...rawTagNames].filter(Boolean).join(' ').toLowerCase();
return {
id: `ms:${feed.slug}:${handle || sku || heur.slice(0, 40)}`,
source: 'microsite',
sku,
series: seriesOf(sku),
title,
vendor: feed.vendor,
type: p.product_type || p.type || feed.type || null,
pattern: p.pattern || p.pattern_name || null,
mfr_number: p.mfr_sku || null,
mfr_name: feed.vendor,
// FileMaker enrichment fields are all null (these lines aren't in the WALLPAPER file)
fm_present: false, fm_mfr_number: null, fm_discontinued: false, fm_disco_date: null,
fm_name: null, fm_width: null, fm_specs: null, fm_handle: null, mfr_mismatch: false,
status: 'STAGED',
// The 4 microsite lines (Fromental/Gracie/Zuber/Crezana) ARE the internal registry vendors,
// so they read 'Internal' when registered (never front-facing), else fall back to 'Staged'.
lifecycle: isInternalRow({ vendor: feed.vendor }) ? 'Internal' : 'Staged',
image: primaryImg,
images,
image_count: images.length,
price: price && price > 0 ? price : null,
price_band: priceBand(price),
colors: [...new Set(colors)],
styles: [...new Set(styles)],
materials: [...new Set(materials)],
image_state: primaryImg ? 'Has image' : 'No image',
// Website = the vendor's own product page when the feed carries an absolute one.
// INTERNAL lines never emit an external front-facing URL (Steve 2026-07-09).
url: (!isInternalRow({ vendor: feed.vendor }) && typeof (p.productUrl || p.product_url) === 'string' && /^https?:\/\//.test(p.productUrl || p.product_url)) ? (p.productUrl || p.product_url) : null,
internal_url: null,
// Line Viewer = the vendor's live internal microsite (resolves via the same crawl artifact).
line_viewer_url: VENDOR_VIEWER.get(slugify(feed.vendor)) || VENDOR_VIEWER.get(feed.slug) || null,
internal_product_url: (function () {
if (!handle) return null;
const entry = VENDOR_ITEM.get(slugify(feed.vendor)) || VENDOR_ITEM.get(feed.slug);
return (entry && entry.handles.has(handle)) ? `${entry.url}product/${encodeURIComponent(handle)}` : null;
})(),
stock: null,
live_capable: false, live_tier: null, live_reason: 'live check unavailable for this line',
created: created ? new Date(created).getTime() || 0 : 0,
updated: created ? new Date(created).getTime() || 0 : 0,
hay: heur,
};
}
async function fetchMicrositeFeed(feed) {
const headers = { 'user-agent': 'all-dw/1.0', Authorization: MICROSITE_FEED_HDR };
if (CF_ACCESS_ASTEK_ID && CF_ACCESS_ASTEK_SECRET && isAstekHost(feed.url)) {
headers['CF-Access-Client-Id'] = CF_ACCESS_ASTEK_ID;
headers['CF-Access-Client-Secret'] = CF_ACCESS_ASTEK_SECRET;
}
const r = await fetch(feed.url, {
redirect: 'follow', signal: AbortSignal.timeout(MICROSITE_FEED_TIMEOUT_MS),
headers,
});
if (r.status !== 200) throw new Error(`HTTP ${r.status}`);
const j = JSON.parse(await r.text());
const arr = Array.isArray(j.rows) ? j.rows : Array.isArray(j.products) ? j.products : [];
return arr;
}
// Pull all 4 DW-family microsite full-feeds, normalize + dedupe (by vendor+handle, then
// vendor+sku), and populate MICROSITE_ROWS. Never throws — a failed feed just contributes
// nothing this cycle (the grid keeps whatever the DB provided). Runs on the snapshot cycle.
async function loadMicrositeExtras() {
const perSite = {};
// Per-site results keyed by vendor; on a per-site failure we KEEP the prior cycle's rows for
// that vendor (a transient feed blip must not silently vanish an entire luxury line).
const prevByVendor = {};
for (const r of MICROSITE_ROWS) (prevByVendor[r.vendor] ||= []).push(r);
const nextByVendor = {};
await Promise.all(MICROSITE_FEEDS.map(async (feed) => {
try {
const arr = await fetchMicrositeFeed(feed);
const seen = new Set();
const rows = [];
for (const p of arr) {
if (rows.length >= MICROSITE_PER_SITE_CAP) break;
// leak-safety: these are customer-facing lines, but still scrub the standing banned set
if (BANNED.test(feed.vendor) || BANNED.test(p.title || '') || BANNED.test(p.pattern || p.pattern_name || '')) continue;
const row = deriveMicrositeRow(p, feed);
const dk = `${feed.slug}:${(row.sku || '').toLowerCase()}|${(p.handle || '').toLowerCase()}`;
if (seen.has(dk)) continue;
seen.add(dk);
rows.push(row);
}
nextByVendor[feed.vendor] = rows;
perSite[feed.vendor] = rows.length;
} catch (e) {
// keep prior rows for this vendor (may be empty on first boot)
nextByVendor[feed.vendor] = prevByVendor[feed.vendor] || [];
perSite[feed.vendor] = `kept ${nextByVendor[feed.vendor].length} (feed ${e.message})`;
console.error(`microsite extras: ${feed.vendor} feed unreachable (non-fatal, kept prior):`, e.message);
}
}));
MICROSITE_ROWS = MICROSITE_FEEDS.flatMap((f) => nextByVendor[f.vendor] || []);
console.log(`microsite extras: ${MICROSITE_ROWS.length} rows merged — ` +
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();
// Resolve the two side-channels BEFORE mapping rows (both non-fatal — a slow/empty
// stock or viewer map just dims those controls, it never takes the grid down).
buildVendorViewer();
loadCoverage();
try { await loadStock(); } catch (e) { console.error('stock load failed (non-fatal):', e.message); }
try { await loadMfr(); } catch (e) { console.error('mfr load failed (non-fatal):', e.message); }
try { await loadFileMaker(); } catch (e) { console.error('filemaker load failed (non-fatal):', e.message); }
try { await loadImages(); } catch (e) { console.error('images load failed (non-fatal):', e.message); }
// DW-family microsite full-feeds (Fromental/Gracie/Zuber/Crezana) — additive searchable rows
// for the customer-facing luxury lines that are NOT in shopify_products. Non-fatal: on any
// failure MICROSITE_ROWS just stays whatever it was (or empty on first boot) — grid unaffected.
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)
// ...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/
// color/style re-derivation). Archived excluded per the internal-tools scope.
// INTERNAL feed also honors FMPro's discontinued truth: a SKU FMPro marks discontinued is
// withheld from substitute + photo (they must never surface a dead product), same as Archived.
let discoHidden = 0;
ROWS_INTERNAL = res.rows
.map((r) => {
const d = deriveRow(r);
if (d.lifecycle === 'Archived') return null;
if (d.fm_discontinued) { discoHidden++; return null; }
const { hay, ...rest } = d;
const pid = (String(r.shopify_id || '').match(/(\d+)\s*$/) || [])[1] || null;
return {
...rest, product_id: pid, handle: r.handle || null, tags: r.tags || '',
vendor: sanitizeName(rest.vendor), title: sanitizeName(rest.title), pattern: sanitizeName(rest.pattern),
};
})
.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;
FM_STATS.mismatch = ROWS.filter((r) => r.mfr_mismatch).length;
FM_STATS.discontinued_hidden = discoHidden;
let fmOnly = 0;
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 + ${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`);
}
// ── filtering / sorting (japan-viewer pattern) ──────────────────────────────────
function parseFilters(u) {
const csv = (k) => new Set((u.searchParams.get(k) || '').split(',').map((s) => s.trim()).filter(Boolean));
return {
q: (u.searchParams.get('q') || '').trim().toLowerCase(),
vendors: csv('vendors'), types: csv('types'), statuses: csv('statuses'), lifecycles: csv('lifecycles'),
series: csv('series'),
colors: csv('colors'), styles: csv('styles'), materials: csv('materials'),
prices: csv('prices'), images: csv('images'),
// FileMaker verification views: fm_disco=1 shows the FMPro-discontinued rows normally hidden;
// mismatch=1 narrows to rows whose FMPro mfr# disagrees with Shopify's (the audit set).
fmDisco: u.searchParams.get('fm_disco') === '1',
mfrMismatch: u.searchParams.get('mismatch') === '1',
};
}
// Default scope = EVERYTHING EXCEPT ARCHIVED. all.dw is the INTERNAL "find any item in
// the market" tool (Steve 2026-07-08) — it must surface every product that exists,
// incl. Staged/DRAFT that DW doesn't publish on the customer store; only Archived
// (which now includes DELETED_FROM_SHOPIFY) is hidden by default.
function applyFilters(rows, f, opts = {}) {
let out = rows;
if (opts.skip !== 'lifecycle') {
out = f.lifecycles.size ? out.filter((r) => f.lifecycles.has(r.lifecycle))
: out.filter((r) => r.lifecycle !== 'Archived');
}
// FMPro is the source of truth for "discontinued": hide matched rows FMPro marks with a
// Date Discontinued (union — rows not in the WALLPAPER file have fm_discontinued=false and
// are unaffected). The verification view (?fm_disco=1) opts them back in.
if (!f.fmDisco) out = out.filter((r) => !r.fm_discontinued);
if (f.mfrMismatch) out = out.filter((r) => r.mfr_mismatch);
if (f.q) {
// word-START match per term so "red" hits "Red"/"redwood" but not "weatheRED"/"bordeRED"
const terms = f.q.split(/\s+/).filter(Boolean)
.map((t) => new RegExp('\\b' + t.replace(/[.*+?^${}()|[\]\\]/g, '\\$&')));
out = out.filter((r) => terms.every((re) => re.test(r.hay)));
}
if (opts.skip !== 'vendor' && f.vendors.size) out = out.filter((r) => r.vendor && f.vendors.has(r.vendor));
if (opts.skip !== 'type' && f.types.size) out = out.filter((r) => r.type && f.types.has(r.type));
if (opts.skip !== 'series' && f.series.size) out = out.filter((r) => r.series && f.series.has(r.series));
if (opts.skip !== 'color' && f.colors.size) out = out.filter((r) => r.colors.some((c) => f.colors.has(c)));
if (opts.skip !== 'style' && f.styles.size) out = out.filter((r) => r.styles.some((c) => f.styles.has(c)));
if (opts.skip !== 'material' && f.materials.size) out = out.filter((r) => r.materials.some((c) => f.materials.has(c)));
if (opts.skip !== 'price' && f.prices.size) out = out.filter((r) => r.price_band && f.prices.has(r.price_band));
if (opts.skip !== 'image' && f.images.size) out = out.filter((r) => f.images.has(r.image_state));
return out;
}
const str = (v) => String(v == null ? '' : v); // nulls sink to the end on A→Z sorts
const sorters = {
newest: (a, b) => b.created - a.created,
oldest: (a, b) => a.created - b.created,
updated: (a, b) => b.updated - a.updated,
title: (a, b) => str(a.title).localeCompare(str(b.title)),
vendor: (a, b) => str(a.vendor).localeCompare(str(b.vendor)) || str(a.title).localeCompare(str(b.title)),
type: (a, b) => str(a.type).localeCompare(str(b.type)) || str(a.title).localeCompare(str(b.title)),
series: (a, b) => str(a.series).localeCompare(str(b.series)) || str(a.sku).localeCompare(str(b.sku)),
sku: (a, b) => str(a.sku).localeCompare(str(b.sku)),
pattern: (a, b) => str(a.pattern).localeCompare(str(b.pattern)),
status: (a, b) => str(a.status).localeCompare(str(b.status)),
lifecycle: (a, b) => str(a.lifecycle).localeCompare(str(b.lifecycle)) || str(a.sku).localeCompare(str(b.sku)),
created: (a, b) => a.created - b.created, // Date column: ▲ asc = oldest→newest, dir=desc flips to newest-first
price_asc: (a, b) => (a.price ?? 1e12) - (b.price ?? 1e12),
price_desc: (a, b) => (b.price ?? -1) - (a.price ?? -1),
color: (a, b) => { const i = (x) => { const c = x.colors[0]; const k = c ? FAMILY_ORDER.indexOf(c) : -1; return k < 0 ? 99 : k; }; return i(a) - i(b) || str(a.sku).localeCompare(str(b.sku)); },
style: (a, b) => str(a.styles[0]).localeCompare(str(b.styles[0])) || str(a.sku).localeCompare(str(b.sku)),
material: (a, b) => str(a.materials[0]).localeCompare(str(b.materials[0])) || str(a.sku).localeCompare(str(b.sku)),
price: (a, b) => (a.price ?? 1e12) - (b.price ?? 1e12), // generic; pair with dir=desc for high→low
};
// per-sort "row actually has this value" — used to keep empties last on dir=desc
const HAS_VALUE = {
sku: (r) => r.sku != null && r.sku !== '', title: (r) => !!r.title, vendor: (r) => !!r.vendor,
type: (r) => !!r.type, series: (r) => !!r.series, pattern: (r) => !!r.pattern, status: (r) => !!r.status, lifecycle: (r) => !!r.lifecycle,
price: (r) => r.price != null, price_asc: (r) => r.price != null, price_desc: (r) => r.price != null,
color: (r) => r.colors.length > 0, style: (r) => r.styles.length > 0, material: (r) => r.materials.length > 0,
created: (r) => r.created > 0,
};
const tally = (rows, key) => { const m = {}; for (const r of rows) { const v = r[key]; if (v) m[v] = (m[v] || 0) + 1; } return m; };
const tallyArr = (rows, key) => { const m = {}; for (const r of rows) { for (const v of r[key]) m[v] = (m[v] || 0) + 1; } return m; };
let vendorsCache = null, vendorsMtime = 0;
function loadVendors() {
const st = fs.statSync(DATA);
if (!vendorsCache || st.mtimeMs !== vendorsMtime) { vendorsCache = fs.readFileSync(DATA); vendorsMtime = st.mtimeMs; }
return vendorsCache;
}
let micrositesCache = null, micrositesMtime = 0;
function loadMicrosites() {
const st = fs.statSync(MICROSITES);
if (!micrositesCache || st.mtimeMs !== micrositesMtime) { micrositesCache = fs.readFileSync(MICROSITES); micrositesMtime = st.mtimeMs; }
return micrositesCache;
}
// Internal full-catalog payload — serialized ONCE per snapshot (keyed on LOADED_AT) and gzipped
// lazily, so serving the ~82k-row feed to the photo/substitute apps costs ~nothing on repeat pulls.
let INTERNAL_CACHE = { at: null, json: null, gz: null };
function internalPayload() {
if (INTERNAL_CACHE.at !== LOADED_AT) {
INTERNAL_CACHE = {
at: LOADED_AT,
json: Buffer.from(JSON.stringify({ total: ROWS_INTERNAL.length, loaded_at: LOADED_AT, rows: ROWS_INTERNAL })),
gz: null,
};
}
return INTERNAL_CACHE;
}
// ── LIVE stock endpoint — /api/livestock?sku=<sku>[&live=1] ─────────────────────────
// FREE default: the stored STOCK snapshot ($0, never a portal hit). live=1: the METERED
// vendor-portal scrape (spawns scripts/live-scrape.js). Self-gates on a wired adapter,
// caches each SKU's live result 10 min, coalesces concurrent clicks, per-IP + global-burst
// rate-limited. PUBLIC-SAFE: only availability + our RETAIL price ship — never cost/net.
const sendJSON = (res, code, obj) => { res.writeHead(code, { 'Content-Type': 'application/json' }); res.end(JSON.stringify(obj)); };
// Match on ANY identity the grid may pass — the products API serializes sku = dw_sku||variant_sku||sku,
// so the live check can arrive with any of the three; match them all (else the adapter gate mis-dims).
const findRowBySku = (sku) => {
if (!sku) return null; const k = String(sku).toUpperCase();
return ROWS.find((r) => (r.dw_sku || '').toUpperCase() === k || (r.variant_sku || '').toUpperCase() === k || (r.sku || '').toUpperCase() === k) || null;
};
function logBrowserbase(min, sku) {
try {
execFile('node', [COST_LOG, '--api', 'browserbase', '--units', `${min.toFixed(2)}:session_min`,
'--app', 'all-dw-livestock', '--note', `live stock scrape ${sku}`], () => {});
} catch { /* ledger is best-effort — never block the response */ }
}
// Spawn the live-scrape worker for ONE SKU via its resolved adapter; measure the session,
// estimate + LOG the Browserbase cost, and return a PUBLIC-SAFE result. Never throws.
function runLiveScrape(sku, adapterKey, catalogTable) {
return new Promise((resolve) => {
const t0 = Date.now();
const args = [LIVE_WORKER, sku, adapterKey || '', catalogTable || ''];
execFile('node', args, { timeout: LIVE_TIMEOUT_MS, maxBuffer: 4 * 1024 * 1024, env: process.env },
(err, stdout) => {
const elapsedMin = Math.max(BB_MIN_SESSION_MIN, (Date.now() - t0) / 60000);
let parsed = null;
try { parsed = JSON.parse((stdout || '').trim().split('\n').filter(Boolean).pop()); } catch { /* no JSON */ }
if (!parsed) {
const reason = err
? (err.killed ? 'live check timed out — use Email Vendor to confirm stock' : 'live check error — use Email Vendor')
: 'no result from live worker';
return resolve({ ok: false, live: true, live_available: true, sku, reason, cost_usd: 0 });
}
if (parsed.available === false) {
// A Browserbase session may have opened before failing → that IS billable, so log it.
if (parsed.session_opened) {
const cost = +(elapsedMin * BB_RATE_PER_MIN).toFixed(4);
logBrowserbase(elapsedMin, sku);
return resolve({ ok: false, live: true, live_available: true, sku, reason: parsed.reason || 'live scrape incomplete', cost_usd: cost, session_min: +elapsedMin.toFixed(2) });
}
return resolve({ ok: false, live: true, live_available: false, sku, reason: parsed.reason || 'live check unavailable for this vendor', cost_usd: 0 });
}
// $0 DB-adapter path (kravet-db etc.): no Browserbase session ever opened → no cost,
// no ledger entry, no live-spend.json increment. Only session-opened scrapes bill.
const opened = parsed.session_opened !== false;
const cost = opened ? +(elapsedMin * BB_RATE_PER_MIN).toFixed(4) : 0;
if (opened) logBrowserbase(elapsedMin, sku);
resolve({
ok: true, live: true, cached: false, sku, vendor: parsed.vendor || null,
in_stock: parsed.in_stock ?? null, lead_time: parsed.lead_time || null,
price: parsed.price ?? null, availability: parsed.raw_stock_label || null,
as_of: parsed.as_of || new Date().toISOString(), source: parsed.source || 'vendor-live',
cost_usd: cost, session_min: opened ? +elapsedMin.toFixed(2) : 0,
});
});
});
}
// ── FileMaker → Shopify push (name/mfr/width/all specs) ──────────────────────────
// Spawns scripts/shopify-push.mjs for ONE SKU (same worker pattern as live-scrape). DRY-RUN
// by default — returns the FM→Shopify diff. A live write requires BOTH ?commit=1 on the request
// AND SHOPIFY_PUSH_ENABLED=1 in the server env (the Steve-gated switch the worker re-checks).
const PUSH_WORKER = path.join(__dirname, 'scripts', 'shopify-push.mjs');
const PUSH_ENABLED = process.env.SHOPIFY_PUSH_ENABLED === '1';
function runPush(sku, commit) {
return new Promise((resolve) => {
const args = [PUSH_WORKER, sku];
if (commit) args.push('--commit');
execFile('node', args, { timeout: 90 * 1000, maxBuffer: 4 * 1024 * 1024, env: process.env }, (err, stdout) => {
let parsed = null;
try { parsed = JSON.parse((stdout || '').trim().split('\n').filter(Boolean).pop()); } catch { /* no JSON */ }
if (!parsed) return resolve({ ok: false, sku, reason: err ? ('push worker ' + (err.killed ? 'timed out' : 'error')) : 'no result from push worker' });
resolve(parsed);
});
});
}
// POST /api/shopify-push?sku=<sku>[&commit=1] — one product. Dry-run unless commit=1 AND server-enabled.
async function handleShopifyPush(req, res, u) {
const sku = (u.searchParams.get('sku') || '').trim();
const commit = u.searchParams.get('commit') === '1';
if (!sku) return sendJSON(res, 400, { ok: false, reason: 'sku required' });
if (commit && !PUSH_ENABLED) return sendJSON(res, 200, { ok: false, sku, blocked: true,
reason: 'live writes are gated — SHOPIFY_PUSH_ENABLED is off on this server (Steve-gated switch)' });
const result = await runPush(sku, commit && PUSH_ENABLED);
return sendJSON(res, 200, { ...result, push_enabled: PUSH_ENABLED });
}
// POST /api/shopify-push-bulk body { skus:[...] } — dry-run diff over a capped batch (≤100),
// aggregated. Live commit is intentionally NOT offered here — bulk is a review report only.
function readBody(req, cap = 1e6) {
return new Promise((resolve) => { let b = ''; req.on('data', (c) => { b += c; if (b.length > cap) req.destroy(); }); req.on('end', () => resolve(b)); req.on('error', () => resolve('')); });
}
async function handleShopifyPushBulk(req, res) {
const body = await readBody(req);
let skus = [];
try { skus = (JSON.parse(body || '{}').skus || []).map((s) => String(s).trim()).filter(Boolean); } catch { /* bad body */ }
skus = [...new Set(skus)].slice(0, 100);
if (!skus.length) return sendJSON(res, 400, { ok: false, reason: 'skus[] required (≤100 per call)' });
// limited concurrency so we don't hammer the Shopify Admin API
const results = []; let i = 0;
async function worker() { while (i < skus.length) { const k = skus[i++]; results.push(await runPush(k, false)); } }
await Promise.all([worker(), worker(), worker()]);
const withChanges = results.filter((r) => r.ok && r.change_count > 0);
const totalChanges = withChanges.reduce((n, r) => n + (r.change_count || 0), 0);
return sendJSON(res, 200, {
ok: true, requested: skus.length, evaluated: results.length,
products_with_changes: withChanges.length, total_field_changes: totalChanges,
push_enabled: PUSH_ENABLED, results,
});
}
async function handleLiveStock(req, res, u) {
const sku = (u.searchParams.get('sku') || '').trim();
const wantLive = u.searchParams.get('live') === '1';
if (!sku) return sendJSON(res, 400, { ok: false, reason: 'sku required' });
const row = findRowBySku(sku);
const stored = STOCK.get(sku.toUpperCase()) || (row && row.stock) || null;
// FREE stored-snapshot path (default) — never a portal hit, $0.
if (!wantLive) {
return sendJSON(res, 200, {
ok: true, sku, live: false, cached: false, source: stored ? (stored.source || 'stored snapshot') : 'stored snapshot',
in_stock: stored ? stored.in_stock : null, quantity: stored ? (stored.quantity ?? null) : null,
lead_time: stored ? (stored.lead_time || null) : null, as_of: stored ? (stored.as_of || null) : null,
has_snapshot: !!stored, cost_usd: 0,
});
}
// ── LIVE (metered) path — self-gate against the coverage audit (same source as the UI dim) ──
const cov = coverageFor(row ? row.vendor : null);
const adapter = (cov && cov.live_capable && cov.adapter) ? cov.adapter : null;
if (!adapter) {
return sendJSON(res, 200, { ok: false, live: true, live_available: false, sku,
vendor: row ? row.vendor : null, tier: cov ? cov.tier : null,
reason: (cov && cov.reason) || 'live check unavailable for this vendor', cost_usd: 0 });
}
const key = sku.toUpperCase();
const isPoll = u.searchParams.get('poll') === '1';
// cache: repeat within the 10-min window returns the cached live result, $0.
// (Applies to BOTH tiers and BOTH initiate + poll — a fresh cache hit is always "done".)
const hit = liveCache.get(key);
if (hit && (Date.now() - hit.at) < (hit.ttl || LIVE_CACHE_MS)) {
return sendJSON(res, 200, { ...hit.result, status: 'done', cached: true, cost_usd: 0, cache_age_s: Math.round((Date.now() - hit.at) / 1000) });
}
// ── DB_AUTHORITATIVE tier stays fully SYNCHRONOUS ──────────────────────────
// It's an instant local Postgres read (no Browserbase session, $0) — zero proxy risk, so we
// keep the original await-inline behavior and return the full result with status:'done'.
// The async job+poll machinery below is only for the SLOW (Browserbase) tiers that can 504.
if (cov.tier === 'DB_AUTHORITATIVE') {
// coalesce: a read already running for this SKU → ride it (no second call).
if (liveInflight.has(key)) {
try { const r = await liveInflight.get(key); return sendJSON(res, 200, { ...r, status: 'done', cached: true, coalesced: true, cost_usd: 0 }); }
catch { return sendJSON(res, 200, { ok: false, live: true, live_available: true, sku, reason: 'live scrape failed', cost_usd: 0, status: 'done' }); }
}
// per-IP rate window (DB tier is $-cap exempt but still rate-limited)
const ipDb = (String(req.headers['x-forwarded-for'] || req.socket.remoteAddress || '')).split(',')[0].trim();
const nowDb = Date.now();
const hitsDb = (liveIpHits.get(ipDb) || []).filter((t) => nowDb - t < LIVE_IP_WIN_MS);
if (hitsDb.length >= LIVE_IP_MAX) {
return sendJSON(res, 429, { ok: false, live: true, rate_limited: true, sku,
reason: 'too many live checks — wait a moment', retry_after_s: Math.ceil((LIVE_IP_WIN_MS - (nowDb - hitsDb[0])) / 1000), cost_usd: 0 });
}
if (liveInflight.size >= LIVE_MAX_INFLIGHT) {
return sendJSON(res, 429, { ok: false, live: true, rate_limited: true, sku,
reason: 'live-check queue full — try again shortly', retry_after_s: 8, cost_usd: 0 });
}
hitsDb.push(nowDb); liveIpHits.set(ipDb, hitsDb);
const pDb = runLiveScrape(sku, adapter, cov.catalog_table).finally(() => liveInflight.delete(key));
liveInflight.set(key, pDb);
let resultDb;
try { resultDb = await pDb; } catch (e) { return sendJSON(res, 200, { ok: false, live: true, live_available: true, sku, reason: 'live scrape error: ' + e.message, cost_usd: 0, status: 'done' }); }
// DB tier opens no billable session (cost_usd stays 0) — still surface the running day total.
if (resultDb.cost_usd > 0) addDaySpend(resultDb.cost_usd);
resultDb.day_spend_usd = +daySpendUSD().toFixed(4);
resultDb.day_cap_usd = DAILY_LIVE_CAP_USD;
resultDb.status = 'done';
if (resultDb.ok) liveCache.set(key, { result: resultDb, at: Date.now() });
return sendJSON(res, 200, resultDb);
}
// ── Browserbase tiers — ASYNC job + poll (never block the proxy) ───────────
// The scrape runs in the background; the initiating request returns immediately with
// status:'pending'. The client polls with &poll=1 until status:'done' (or gives up).
// POLL request: report on an in-flight scrape without starting a new one.
if (isPoll) {
if (liveInflight.has(key)) return sendJSON(res, 200, { status: 'pending', sku, poll_after_ms: 3000, live: true });
// no fresh cache (handled above) and nothing in flight → the result is gone; ask again.
return sendJSON(res, 200, { status: 'gone', sku, reason: 'no live result — click Check Live again', live: true });
}
// INITIATE request.
// coalesce: a scrape already running for this SKU → tell the client to poll (no new bill).
if (liveInflight.has(key)) {
return sendJSON(res, 200, { status: 'pending', sku, poll_after_ms: 3000, live: true });
}
// ── DAILY $ KILL-SWITCH — the TOTAL ceiling the rate limits don't provide (TERMINAL) ──
// Checked BEFORE any new billable session. When the day's summed spend hits the cap,
// hard-stop at $0 — no Browserbase session is ever opened.
const daySpend = daySpendUSD();
if (daySpend >= DAILY_LIVE_CAP_USD) {
return sendJSON(res, 200, { ok: false, live: true, live_available: false, sku,
vendor: row ? row.vendor : null, tier: cov ? cov.tier : null,
reason: `daily live-stock budget reached ($${daySpend.toFixed(2)}/$${DAILY_LIVE_CAP_USD.toFixed(2)})`,
day_spend_usd: +daySpend.toFixed(4), day_cap_usd: DAILY_LIVE_CAP_USD, cost_usd: 0 });
}
// per-IP rate window (TERMINAL)
const ip = (String(req.headers['x-forwarded-for'] || req.socket.remoteAddress || '')).split(',')[0].trim();
const now = Date.now();
const hits = (liveIpHits.get(ip) || []).filter((t) => now - t < LIVE_IP_WIN_MS);
if (hits.length >= LIVE_IP_MAX) {
return sendJSON(res, 429, { ok: false, live: true, rate_limited: true, sku,
reason: 'too many live checks — wait a moment', retry_after_s: Math.ceil((LIVE_IP_WIN_MS - (now - hits[0])) / 1000), cost_usd: 0 });
}
// global burst cap on concurrent portal sessions (protects spend + the vendor portal) (TERMINAL)
if (liveInflight.size >= LIVE_MAX_INFLIGHT) {
return sendJSON(res, 429, { ok: false, live: true, rate_limited: true, sku,
reason: 'live-check queue full — try again shortly', retry_after_s: 8, cost_usd: 0 });
}
hits.push(now); liveIpHits.set(ip, hits);
// Start the scrape but DO NOT await it in the handler — the request returns immediately so it
// can never 504 through the ~60s proxy cap. Spend-accrual + caching happen in the background
// .then() (moved out of the request path). The .catch caches an error result so a later poll
// resolves cleanly, and guarantees no unhandled promise rejection.
const p = runLiveScrape(sku, adapter, cov.catalog_table)
.then((r) => {
if (r.cost_usd > 0) addDaySpend(r.cost_usd);
r.day_spend_usd = +daySpendUSD().toFixed(4); r.day_cap_usd = DAILY_LIVE_CAP_USD;
// Cache a real answer (ok, incl. a genuine negative availability) for the full window;
// a TRANSIENT failure (timeout / worker error, !ok) only briefly — so the in-flight poll
// still renders its specific reason, but the SKU is NOT retry-locked for 10 min (the bug a
// blanket cache-of-failure would create — contrarian-gated 2026-07-27; matches the DB
// tier's success-only rule at ~line 1143).
liveCache.set(key, { result: { ...r, status: 'done' }, at: Date.now(), ttl: r.ok ? LIVE_CACHE_MS : 30 * 1000 });
return r;
})
.catch((e) => {
liveCache.set(key, { result: { ok: false, live: true, live_available: true, sku, reason: 'live scrape error: ' + e.message, cost_usd: 0, status: 'done' }, at: Date.now(), ttl: 30 * 1000 });
})
.finally(() => { liveInflight.delete(key); });
liveInflight.set(key, p);
return sendJSON(res, 200, { status: 'pending', sku, poll_after_ms: 3000, live: true });
}
// ── /api/discontinued?sku=<sku> — resolve a dead/archived SKU + link its live successor ──
// Ported from ~/.claude/skills/substitutefinder/find.py (resolve_successor + target query).
// Given ANY sku (dash-insensitive, -SAMPLE-tolerant), returns:
// { found, sku, title, status, discontinued, successor:{sku,title,handle,url}|null }
// This queries the DB directly (NOT the in-RAM snapshot) so it can see DELETED_FROM_SHOPIFY /
// ARCHIVED rows the snapshot's public grid hides. Leak-safe: successor comes from a live ACTIVE
// product (already-clean title). Never emits cost/net/wholesale.
const SUCC_BASE = 'https://designerwallcoverings.com/products/';
// _succ_name — strip a COPY-OF-<name> mfr code (or a dead title) down to its bare pattern name.
function succName(s) {
s = (s || '').replace(/copy[\s_-]*of[\s_-]*/ig, ''); // drop COPY-OF-
s = s.replace(/[\s_-]+\d+$/, ''); // drop trailing -1..-8
s = s.replace(/[_-]+/g, ' ').trim(); // dashes/underscores -> spaces
return s.replace(/\s*(wall\s?paper|wall\s?covering)\s*$/i, '').trim(); // drop generic suffix
}
// resolve_successor — a discontinued item's LIVE successor = an ACTIVE product whose name matches
// the dead item's real pattern. The clue is often a 'COPY-OF-<successor>' mfr code — sometimes on
// the typed SKU, sometimes only on a SIBLING sharing the same dead title.
async function resolveSuccessor(mfr, title) {
const names = [succName(mfr)];
const baseTitle = (title || '').replace(/\s*\|.*$/, '').trim();
if (baseTitle.length >= 4) {
// harvest COPY-OF-<x> hints from any row sharing this dead title
const { rows } = await pool.query(
`select coalesce(mfr_sku,'') mfr from shopify_products
where title ilike '%'||$1||'%' and mfr_sku ~* 'copy' limit 10`, [baseTitle]);
for (const r of rows) names.push(succName(r.mfr));
names.push(baseTitle);
}
const seen = new Set(); const ordered = [];
for (let n of names) {
n = (n || '').trim();
if (n.length >= 4 && !seen.has(n.toLowerCase())) { seen.add(n.toLowerCase()); ordered.push(n); }
}
for (const name of ordered) {
const { rows } = await pool.query(
`select dw_sku, coalesce(title,'') title, coalesce(handle,'') handle
from shopify_products
where lower(status) in ('active','draft') and image_url is not null
and dw_sku is not null and dw_sku <> '' and title ilike '%'||$1||'%'
order by (title !~* 'memo|sample') desc,
(lower(title) like lower($1)||'%') desc, dw_sku
limit 1`, [name]);
if (rows.length) {
const r = rows[0];
const handle = (r.handle || '').replace('�', '').trim();
return { sku: r.dw_sku, title: (r.title || '').replace(/�/g, '').trim(),
handle, url: handle ? SUCC_BASE + handle : '' };
}
}
return null;
}
async function handleDiscontinued(req, res, u) {
const raw = (u.searchParams.get('sku') || '').trim();
if (!raw) return sendJSON(res, 400, { found: false, error: 'sku required' });
if (!pool) return sendJSON(res, 503, { found: false, error: 'db not ready' });
// normalize: strip a trailing -SAMPLE so a typed CHC-216830 also finds its CHC-216830-SAMPLE row.
const qbase = raw.replace(/-sample$/i, '');
try {
// dash/punct-insensitive base match (regexp_replace '[^a-z0-9]' -> ''), plus the -SAMPLE-suffix
// catch via the de-dashed PREFIX. Include archived/deleted (this endpoint's whole point).
const { rows } = await pool.query(
`select dw_sku, coalesce(title,'') title, coalesce(status,'') status,
coalesce(handle,'') handle, coalesce(mfr_sku,'') mfr
from shopify_products
where regexp_replace(lower(dw_sku),'[^a-z0-9]','','g') like regexp_replace(lower($1),'[^a-z0-9]','','g')||'%'
or dw_sku ilike $1 or sku ilike $1
or handle ilike ('%'||$1||'%') or mfr_sku ilike $1
order by (dw_sku ilike $1) desc,
(lower(status) in ('active','draft')) desc,
(regexp_replace(lower(dw_sku),'[^a-z0-9]','','g') = regexp_replace(lower($1),'[^a-z0-9]','','g')) desc,
(lower(status)='archived') desc
limit 1`, [qbase]);
if (!rows.length) return sendJSON(res, 200, { found: false, sku: raw });
const t = rows[0];
const title = (t.title || '').replace(/�/g, '').trim();
const status = (t.status || '').toUpperCase();
// discontinued = not currently sellable (archived OR hard-deleted from Shopify)
const discontinued = !['active', 'draft'].includes((t.status || '').trim().toLowerCase());
let successor = null;
if (discontinued) successor = await resolveSuccessor(t.mfr, t.title);
return sendJSON(res, 200, { found: true, sku: t.dw_sku || raw, title, status, discontinued, successor });
} catch (e) {
return sendJSON(res, 500, { found: false, error: e.message });
}
}
// ── /api/similar?sku=<dead sku>&limit=N — attribute-scored live substitutes ──────────
// Powers the discontinued banner's "Search the catalog for a similar pattern" button.
// Scores the in-RAM snapshot against the dead SKU's own row (Archived rows keep their
// full color/style/material tags, so the reference attributes are already derived).
// Falls back to a DB title lookup when the dead SKU predates the mirror snapshot.
// Returns rows in the exact /api/products row shape so the client card renderer works
// unchanged. Leak-safe: only ROWS (public-safe columns) ever leave the server.
const SIM_STOP = new Set(['wallpaper', 'wallcovering', 'wallcoverings', 'fabric', 'sample',
'memo', 'the', 'and', 'with', 'collection', 'designer', 'wallcoverings|']);
function simTokens(title) {
return [...new Set(String(title || '').replace(/\|.*$/, '').toLowerCase().split(/[^a-z0-9]+/)
.filter((t) => t.length >= 4 && !SIM_STOP.has(t)))];
}
function simKey(s) { return String(s || '').toLowerCase().replace(/-sample$/, '').replace(/[^a-z0-9]/g, ''); }
async function handleSimilar(req, res, u) {
const raw = (u.searchParams.get('sku') || '').trim();
if (!raw) return sendJSON(res, 400, { error: 'sku required' });
const limit = Math.min(parseInt(u.searchParams.get('limit') || '12', 10) || 12, 50);
const key = simKey(raw);
// the reference (dead) row: prefer the snapshot row; else DB title fallback
let ref = ROWS.find((r) => simKey(r.sku) === key || (r.mfr_number && simKey(r.mfr_number) === key));
if (!ref && pool) {
try {
const { rows } = await pool.query(
`select coalesce(title,'') title, coalesce(vendor,'') vendor, coalesce(product_type,'') type
from shopify_products
where regexp_replace(lower(dw_sku),'[^a-z0-9]','','g') = $1
or regexp_replace(lower(mfr_sku),'[^a-z0-9]','','g') = $1
limit 1`, [key]);
if (rows.length) {
ref = { sku: raw, title: rows[0].title, vendor: rows[0].vendor, type: rows[0].type,
pattern: null, colors: [], styles: [], materials: [] };
}
} catch { /* fall through to not-found */ }
}
if (!ref) return sendJSON(res, 200, { found: false, sku: raw, rows: [] });
const toks = simTokens(ref.title);
const colors = new Set(ref.colors || []), styles = new Set(ref.styles || []), materials = new Set(ref.materials || []);
const scored = [];
for (const r of ROWS) {
if (r.lifecycle !== 'Active on site' || !r.image) continue; // substitutes must be live + shoppable
if (simKey(r.sku) === key) continue;
let s = 0;
for (const m of r.materials || []) if (materials.has(m)) s += 3;
for (const st of r.styles || []) if (styles.has(st)) s += 2;
for (const c of r.colors || []) if (colors.has(c)) s += 1.5;
if (ref.type && r.type === ref.type) s += 2;
if (ref.vendor && r.vendor === ref.vendor) s += 1;
if (ref.pattern && r.pattern && r.pattern.toLowerCase() === ref.pattern.toLowerCase()) s += 4;
if (toks.length) { const h = r.hay || String(r.title || '').toLowerCase(); for (const t of toks) if (h.includes(t)) s += 1; }
if (s > 0) scored.push([s, r]);
}
scored.sort((a, b) => b[0] - a[0] || String(a[1].sku).localeCompare(String(b[1].sku)));
return sendJSON(res, 200, {
found: true, sku: ref.sku, title: ref.title || '',
rows: scored.slice(0, limit).map(([score, { hay, ...r }]) => ({ ...r, similarity: score })),
});
}
// ── Vendor stock & price INQUIRY (the email path) ────────────────────────────────
// Steve's standing rule (2026-07-15): EVERY display vendor gets a working "check stock
// and price" action. live_capable vendors scrape their portal; everyone else (and live
// ones too, as a confirmation path) gets an EMAIL to the vendor rep — composed here,
// saved as a Gmail DRAFT via George. DRAFT ONLY, never an auto-send ($0, reversible).
// GET /api/vendor-contact?vendor=<name> → stored rep email + last draft (case-insens.)
// POST /api/vendor-inquiry {vendor,to,cc?,subject,body,sku}
// → UPSERTs vendor_contacts (coverage grows organically as Steve uses it), then
// creates the Gmail draft. George unreachable/unconfigured → the contact still
// saves and {drafted:false, reason, mailto} lets the UI fall back to mailto:.
// LEAK-SAFE: the payload carries only what the caller typed — no cost/net/wholesale ever
// enters the compose (the whole point is asking the VENDOR for THEIR current price).
const GEORGE = process.env.GEORGE_URL || 'http://127.0.0.1:9850';
const GEORGE_ENV = process.env.GEORGE_ENV_PATH || path.join(os.homedir(), 'Projects', 'george-gmail', '.env');
const SECRETS_ENV = process.env.SECRETS_ENV_PATH || path.join(os.homedir(), 'Projects', 'secrets-manager', '.env');
// Drafts land in the steve-office (steve@designerwallcoverings.com) Gmail by default;
// override with GEORGE_DRAFT_ACCOUNT=info to draft from info@ instead.
const GEORGE_DRAFT_ACCOUNT = process.env.GEORGE_DRAFT_ACCOUNT || 'steve-office';
function envFrom(file, key) {
try { const m = fs.readFileSync(file, 'utf8').match(new RegExp('^' + key + '=(.+)$', 'm')); return m ? m[1].trim().replace(/^["']|["']$/g, '') : ''; } catch { return ''; }
}
// Same resolution order dw-pitch-followup uses: canonical GEORGE_AUTH ("user:pass") from
// process.env → secrets-manager master → george-gmail/.env → legacy PASS-only key.
// Null (no cred found) is fine — the inquiry degrades to drafted:false + mailto.
function resolveGeorgeAuth() {
const direct = process.env.GEORGE_AUTH || envFrom(SECRETS_ENV, 'GEORGE_AUTH') || envFrom(GEORGE_ENV, 'GEORGE_AUTH');
if (direct) return direct.startsWith('Basic ') ? direct : ('Basic ' + Buffer.from(direct.includes(':') ? direct : ('admin:' + direct)).toString('base64'));
const pass = process.env.GEORGE_BASIC_AUTH_PASS || envFrom(GEORGE_ENV, 'GEORGE_BASIC_AUTH_PASS');
return pass ? 'Basic ' + Buffer.from('admin:' + pass).toString('base64') : null;
}
const GEORGE_AUTH = resolveGeorgeAuth();
const EMAIL_RE = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
const mailtoUrl = (to, subject, body) =>
`mailto:${encodeURIComponent(to)}?subject=${encodeURIComponent(subject)}&body=${encodeURIComponent(body)}`;
// vendor_contacts may not exist on a fresh DB (it was born on the mac3 mirror) — without
// it every save degrades to contact_saved:false. Self-heal: create-if-missing, once.
let CONTACTS_DDL_DONE = false;
async function ensureVendorContactsTable() {
if (CONTACTS_DDL_DONE || !pool) return;
try {
await pool.query(`CREATE TABLE IF NOT EXISTS vendor_contacts (
vendor text PRIMARY KEY,
email text,
updated_at timestamptz,
draft_subject text,
draft_body text,
draft_saved_at timestamptz,
sheet_url text,
sheet_created_at timestamptz
)`);
CONTACTS_DDL_DONE = true;
} catch (e) { console.error('vendor_contacts init failed (will retry next request):', e.message); }
}
async function handleVendorContact(req, res, u) {
const vendor = (u.searchParams.get('vendor') || '').trim();
if (!vendor) return sendJSON(res, 400, { ok: false, reason: 'vendor required' });
if (!pool) return sendJSON(res, 503, { ok: false, reason: 'db not ready' });
await ensureVendorContactsTable();
try {
const { rows } = await pool.query(
`SELECT vendor, email, draft_subject, draft_body FROM vendor_contacts
WHERE lower(vendor) = lower($1) LIMIT 1`, [vendor]);
const r = rows[0] || null;
return sendJSON(res, 200, {
ok: true, vendor, known: !!r,
email: (r && r.email) ? r.email : null,
draft_subject: r ? (r.draft_subject || null) : null,
draft_body: r ? (r.draft_body || null) : null,
});
} catch (e) { return sendJSON(res, 500, { ok: false, reason: e.message }); }
}
async function handleVendorInquiry(req, res) {
let b = {};
try { b = JSON.parse(await readBody(req) || '{}'); } catch { /* bad body → field checks reject below */ }
const vendor = String(b.vendor || '').trim();
const to = String(b.to || '').trim();
const cc = String(b.cc || '').trim();
const subject = String(b.subject || '').trim();
const body = String(b.body || '').trim();
const sku = String(b.sku || '').trim();
if (!vendor) return sendJSON(res, 400, { ok: false, reason: 'vendor required' });
if (!EMAIL_RE.test(to)) return sendJSON(res, 400, { ok: false, reason: 'to= must be a valid email address' });
if (cc && !EMAIL_RE.test(cc)) return sendJSON(res, 400, { ok: false, reason: 'cc= must be a valid email address' });
if (!subject || !body) return sendJSON(res, 400, { ok: false, reason: 'subject and body required' });
const mailto = mailtoUrl(to, subject, body);
// 1. UPSERT the contact — coverage grows organically as Steve uses the compose. Update is
// case-insensitive against the existing vendor key so casing variants never dupe rows.
let saved = false;
if (pool) {
try {
await ensureVendorContactsTable();
const upd = await pool.query(
`UPDATE vendor_contacts SET email=$2, draft_subject=$3, draft_body=$4,
draft_saved_at=now(), updated_at=now()
WHERE lower(vendor) = lower($1)`, [vendor, to, subject, body]);
if (upd.rowCount === 0) {
await pool.query(
`INSERT INTO vendor_contacts (vendor, email, draft_subject, draft_body, draft_saved_at, updated_at)
VALUES ($1, $2, $3, $4, now(), now())`, [vendor, to, subject, body]);
}
saved = true;
} catch (e) { console.error('vendor_contacts upsert failed (non-fatal):', e.message); }
}
// 2. Gmail DRAFT via George — POST /api/drafts (NEVER /api/send). Any failure degrades
// gracefully: the contact is already saved and the UI gets the mailto: fallback.
if (!GEORGE_AUTH) {
return sendJSON(res, 200, { ok: true, drafted: false, contact_saved: saved,
reason: 'George credentials not configured on this host', mailto });
}
try {
const r = await fetch(`${GEORGE}/api/drafts`, {
method: 'POST', signal: AbortSignal.timeout(12000),
headers: { 'Content-Type': 'application/json', Authorization: GEORGE_AUTH },
body: JSON.stringify({ to, ...(cc ? { cc } : {}), subject, body,
account: GEORGE_DRAFT_ACCOUNT, source: 'all-dw vendor inquiry' + (sku ? ' · ' + sku : '') }),
});
const d = await r.json().catch(() => ({}));
if (r.status === 200 && d.success) {
console.log(`vendor-inquiry: Gmail draft ${d.draftId} → ${to} (${vendor}${sku ? ' · ' + sku : ''}) [$0 — draft only, never sent]`);
return sendJSON(res, 200, { ok: true, drafted: true, gmail_draft_id: d.draftId,
account: d.account || GEORGE_DRAFT_ACCOUNT, contact_saved: saved, mailto });
}
return sendJSON(res, 200, { ok: true, drafted: false, contact_saved: saved,
reason: (d && d.error) || `George HTTP ${r.status}`, mailto });
} catch (e) {
return sendJSON(res, 200, { ok: true, drafted: false, contact_saved: saved,
reason: 'George unreachable: ' + e.message, mailto });
}
}
// Basic Auth gate (Steve 2026-07-02: all. is published but un/pw-gated).
// Override with BASIC_AUTH=user:pass (or BASIC_AUTH_USER / BASIC_AUTH_PASS) in .env;
// default stays admin:DW2024! so behavior is byte-identical when unset. /healthz open.
const BASIC_AUTH = process.env.BASIC_AUTH
|| (process.env.BASIC_AUTH_USER && process.env.BASIC_AUTH_PASS
? `${process.env.BASIC_AUTH_USER}:${process.env.BASIC_AUTH_PASS}`
: 'admin:DW2024!');
const AUTH_OK = 'Basic ' + Buffer.from(BASIC_AUTH).toString('base64');
const server = http.createServer((req, res) => {
const u = new URL(req.url, `http://localhost:${PORT}`);
if (u.pathname !== '/healthz' && req.headers.authorization !== AUTH_OK) {
res.writeHead(401, { 'WWW-Authenticate': 'Basic realm="Designer Wallcoverings - All Products"', 'Content-Type': 'text/plain' });
return res.end('Authentication required');
}
if (u.pathname === '/api/products') {
const f = parseFilters(u);
const sort = u.searchParams.get('sort') || 'newest';
const offset = parseInt(u.searchParams.get('offset') || '0', 10) || 0;
const limit = Math.min(parseInt(u.searchParams.get('limit') || '120', 10) || 120, 500);
let rows = applyFilters(ROWS, f);
if (sorters[sort]) {
rows = [...rows].sort(sorters[sort]);
// dir=desc flips ANY sort key so every list-view column sorts both ways.
// Reversing would surface the null-sentinel rows first, so after the flip
// we stable-partition rows lacking the sort value back to the end.
if (u.searchParams.get('dir') === 'desc') {
rows.reverse();
const has = HAS_VALUE[sort];
if (has) {
const withV = [], without = [];
for (const r of rows) (has(r) ? withV : without).push(r);
rows = withV.concat(without);
}
}
}
res.writeHead(200, { 'Content-Type': 'application/json' });
res.end(JSON.stringify({
total: rows.length, catalog: ROWS.length, loaded_at: LOADED_AT, offset, limit,
// keep created/updated (epoch ms) so the card/list can show the active-or-staged date; only `hay` is internal
rows: rows.slice(offset, offset + limit).map(({ hay, ...r }) => r),
}));
return;
}
// ── INTERNAL full-catalog feed ─────────────────────────────────────────────────
// Auth-gated (the whole server is). The single source of truth the substitute + photo
// apps consume so they see EVERY non-archived SKU in dw_unified — not just their own line —
// with the same enrichment (colors/styles/materials/price_band/lifecycle/stock) plus the
// internal-only product_id/handle/tags. Private-label names are leak-sanitized upstream.
if (u.pathname === '/api/catalog-full') {
const c = internalPayload();
const wantsGzip = /\bgzip\b/.test(req.headers['accept-encoding'] || '');
if (wantsGzip) {
if (!c.gz) { try { c.gz = zlib.gzipSync(c.json); } catch { c.gz = null; } }
if (c.gz) {
res.writeHead(200, { 'Content-Type': 'application/json; charset=utf-8', 'Content-Encoding': 'gzip' });
return res.end(c.gz);
}
}
res.writeHead(200, { 'Content-Type': 'application/json; charset=utf-8' });
return res.end(c.json);
}
if (u.pathname === '/api/facets') {
const f = parseFilters(u);
res.writeHead(200, { 'Content-Type': 'application/json' });
res.end(JSON.stringify({
vendor: tally(applyFilters(ROWS, f, { skip: 'vendor' }), 'vendor'),
type: tally(applyFilters(ROWS, f, { skip: 'type' }), 'type'),
series: tally(applyFilters(ROWS, f, { skip: 'series' }), 'series'),
lifecycle: tally(applyFilters(ROWS, f, { skip: 'lifecycle' }), 'lifecycle'),
status: tally(applyFilters(ROWS, f, { skip: 'lifecycle' }), 'status'),
color: tallyArr(applyFilters(ROWS, f, { skip: 'color' }), 'colors'),
style: tallyArr(applyFilters(ROWS, f, { skip: 'style' }), 'styles'),
material: tallyArr(applyFilters(ROWS, f, { skip: 'material' }), 'materials'),
price_band: tally(applyFilters(ROWS, f, { skip: 'price' }), 'price_band'),
image_state: tally(applyFilters(ROWS, f, { skip: 'image' }), 'image_state'),
price_order: PRICE_ORDER, family_order: FAMILY_ORDER, lifecycle_order: LIFE_ORDER,
total: applyFilters(ROWS, f).length,
filemaker: FM_STATS,
}));
return;
}
if (u.pathname === '/api/discontinued') { handleDiscontinued(req, res, u); return; }
if (u.pathname === '/api/similar') { handleSimilar(req, res, u); return; }
if (u.pathname === '/api/livestock') { handleLiveStock(req, res, u); return; }
if (u.pathname === '/api/vendor-contact') { handleVendorContact(req, res, u); return; }
if (u.pathname === '/api/vendor-inquiry' && req.method === 'POST') { handleVendorInquiry(req, res); return; }
if (u.pathname === '/api/shopify-push') { handleShopifyPush(req, res, u); return; }
if (u.pathname === '/api/shopify-push-bulk') { handleShopifyPushBulk(req, res); return; }
if (u.pathname === '/api/vendors') { // legacy vendor directory API (vendors.html)
try {
res.writeHead(200, { 'Content-Type': 'application/json; charset=utf-8', 'Cache-Control': 'private, no-store' });
return res.end(loadVendors());
} catch { res.writeHead(500, { 'Content-Type': 'application/json' }); return res.end('{"error":"data unavailable"}'); }
}
if (u.pathname === '/api/microsites') {
try {
res.writeHead(200, { 'Content-Type': 'application/json; charset=utf-8', 'Cache-Control': 'private, no-store' });
return res.end(loadMicrosites());
} catch { res.writeHead(503, { 'Content-Type': 'application/json' }); return res.end('{"error":"crawl snapshot not ready"}'); }
}
if (u.pathname === '/healthz') {
res.writeHead(200, { 'Content-Type': 'application/json' });
return res.end(JSON.stringify({ ok: ROWS.length > 0, products: ROWS.length, loaded_at: LOADED_AT, filemaker: FM_STATS }));
}
// static, path-traversal-safe
const p = u.pathname === '/' ? '/index.html' : u.pathname;
const file = path.join(PUB, path.normalize(p).replace(/^(\.\.[/\\])+/, ''));
if (!file.startsWith(PUB) || !fs.existsSync(file) || !fs.statSync(file).isFile()) { res.writeHead(404); return res.end('not found'); }
res.writeHead(200, { 'Content-Type': MIME[path.extname(file)] || 'application/octet-stream' });
res.end(fs.readFileSync(file));
});
function pollMicrosites() {
crawlMicrosites().catch((e) => console.error('microsite crawl failed:', e.message));
}
loadSnapshot()
.then(() => {
server.listen(PORT, () => console.log(`all. catalog → http://127.0.0.1:${PORT} (${ROWS.length.toLocaleString()} products)`));
setInterval(() => loadSnapshot().catch((e) => console.error('snapshot refresh failed:', e.message)), REFRESH_MS);
pollMicrosites();
setTimeout(() => setInterval(pollMicrosites, REFRESH_MS), 30 * 1000);
})
.catch((e) => { console.error('FATAL: initial snapshot load failed:', e.message); process.exit(1); });