← back to Wallco Ai
scripts/build-library-cache.js
325 lines
#!/usr/bin/env node
/**
* build-library-cache.js
*
* One-shot cache builder for the wallco.ai /library surface — the new browsable
* view onto the 50k+ real professional vendor products that live in
* dw_unified.shopify_color_enrichment + shopify_products.
*
* Why:
* - /library on wallco.ai should browse REAL designer wallpapers (Schumacher,
* Cole & Son, Phillip Jeffries, Maya Romanoff, etc.) alongside the AI-
* generated ones — Steve's tens-of-thousands-in-postgres directive.
* - We can't query PG on every request (latency + cost) so we materialize the
* joined+redacted view into data/library-cache.json at boot.
*
* Output:
* data/library-cache.json — array of { id, sku, title, image_url, palette,
* hex_dominant, color_family, styles, patterns, material, design_era,
* mood, tags_redacted, price, product_type, _vendor_raw } records.
*
* IMPORTANT — vendor redaction:
* The on-disk cache keeps _vendor_raw so admin surfaces can audit, BUT every
* customer-facing surface MUST strip it before rendering. See server.js
* redactLibraryRow() — single source of truth for stripping.
*
* Settlement note (per Steve's brief):
* These are real commercial products that already passed legal at the vendor
* level. We don't run the wallco settlement gate on them — that's for our
* AI-generated patterns. If a future audit is needed, key off _vendor_raw
* + sku.
*
* Run:
* node scripts/build-library-cache.js
*/
'use strict';
const fs = require('fs');
const path = require('path');
const { execFileSync } = require('child_process');
const DB = process.env.WALLCO_DB || 'dw_unified';
const PSQL = process.env.PSQL_BIN || 'psql';
const OUT = path.join(__dirname, '..', 'data', 'library-cache.json');
// Product-type allowlist — wallcoverings + close cousins. Excludes one-offs
// like Mugs, Puzzles, Spiral Notebooks that ended up in the products table.
const PRODUCT_TYPE_ALLOW = [
'Wallcovering',
'Wallcoverings',
'Commercial Wallcovering',
'Natural Wallcovering',
'Fabric',
'Fabrics',
'Commercial Fabric',
'Others'
];
// Vendor allowlist — the 30+ professional houses we know are wallcoverings/
// fabric brands. (We DO carry their data, but we never SHOW their names.)
// Anything outside this list still flows in IF the product_type matches, but
// gets _vendor_raw set so we can audit. The redactLibraryRow() in server.js
// is what hides the brand from customer surfaces — this list is just for
// our own bookkeeping.
const KNOWN_VENDORS = new Set([
'Schumacher', 'Maya Romanoff', 'Thibaut Wallpaper', 'Thibaut',
'Arte International', 'Cole & Son', 'Brunschwig & Fils', 'Scalamandre Wallpaper',
'Phillip Jeffries', 'Koroseal', 'Kravet', 'Romo', 'Pierre Frey',
'Designers Guild', 'Osborne & Little', 'Ralph Lauren', 'Donghia',
'William Morris', '1838 Wallcoverings', 'Lee Jofa', 'Anna French',
'Harlequin', 'Nina Campbell', 'Elitis', 'Sandberg', 'Wolf Gordon',
'Versace', 'Mind the Gap', 'Coordonné', 'China Seas', 'Rebel Walls',
'Newmor Wallcoverings', 'Versa Designed Surfaces', 'Architectural Fabrics',
'Architectural Wallcoverings', 'York Wallcoverings', 'Brewster',
'Phillipe Romano', 'Jeffrey Stevens', 'Hollywood Wallcoverings',
'Malibu Wallpaper', 'Surface Stick', 'LA Walls', 'Designer Wallcoverings',
'DW Bespoke Studio'
]);
// SKU resolution — prefer dw_sku, fall back to sku, then mfr_sku, then a
// derived DWLB-NNNNNN. The customer never sees vendor name — only the SKU —
// so this MUST always be present.
function resolveSku(row) {
if (row.dw_sku && /^[A-Z0-9-]+$/i.test(row.dw_sku)) return row.dw_sku;
if (row.sku) return row.sku;
if (row.mfr_sku) return row.mfr_sku;
return `DWLB-${String(row.id).padStart(6, '0')}`;
}
// Tag redaction — strips any tag that looks like a vendor brand name OR
// internal-only tags (Made In X, NFPA codes, Commercial Grade, contract).
// Anything that's a clean adjective/category stays.
function redactTags(tagString, vendor) {
if (!tagString) return [];
const VENDOR_LIKE = new Set(
Array.from(KNOWN_VENDORS).map(v => v.toLowerCase())
);
if (vendor) VENDOR_LIKE.add(vendor.toLowerCase());
const INTERNAL = /^(made in|nfpa|fire rated|contract grade|commercial grade|wholesale|trade only|stock code|sku:?|color:?)/i;
return tagString.split(',')
.map(t => t.trim())
.filter(t => t.length > 0 && t.length < 40)
.filter(t => !VENDOR_LIKE.has(t.toLowerCase()))
.filter(t => !INTERNAL.test(t))
.slice(0, 12);
}
// Saturation buckets — same hue logic as /designs uses, for the "Color" sort.
function hueBucketOf(hex) {
if (!hex || !/^#[0-9a-fA-F]{6}$/.test(hex)) return null;
const r = parseInt(hex.slice(1,3),16)/255;
const g = parseInt(hex.slice(3,5),16)/255;
const b = parseInt(hex.slice(5,7),16)/255;
const max = Math.max(r,g,b), min = Math.min(r,g,b);
if (max === min) return 'neutral';
let h = 0; const d = max - min;
if (max === r) h = (g-b)/d + (g<b?6:0);
else if (max === g) h = (b-r)/d + 2;
else h = (r-g)/d + 4;
h = (h * 60 + 360) % 360;
const l = (max + min) / 2;
const s = (max - min) / (l > 0.5 ? 2 - max - min : max + min);
if (s < 0.1) return 'neutral';
if (h < 15 || h >= 345) return 'rose';
if (h < 40) return 'amber';
if (h < 60) return 'honey';
if (h < 90) return 'olive';
if (h < 160) return 'sage';
if (h < 200) return 'marine';
if (h < 250) return 'sapphire';
if (h < 290) return 'mauve';
return 'plum';
}
function styleBucketOf(styles, patterns, designEra) {
// Distill the styles/patterns arrays into a single "style" label
// that the Sort=Style option can group by. Defaults to "Mixed".
const all = [...(styles||[]), ...(patterns||[])].map(s => String(s||'').toLowerCase());
const era = String(designEra||'').toLowerCase();
if (all.some(s => /damask/.test(s))) return 'Damask';
if (all.some(s => /floral|botanic|flora/.test(s))) return 'Floral';
if (all.some(s => /geometric|grid|striped|stripe|chevron/.test(s))) return 'Geometric';
if (all.some(s => /chinoiserie/.test(s))) return 'Chinoiserie';
if (all.some(s => /mural|scenic|landscape/.test(s))) return 'Mural';
if (all.some(s => /grass|textur|linen|silk|natural/.test(s))) return 'Textural';
if (all.some(s => /trad/.test(s)) || /classic|victorian|edwardian/.test(era)) return 'Traditional';
if (all.some(s => /modern|contemp|minimal/.test(s)) || /modern|contemp/.test(era)) return 'Modern';
if (all.some(s => /art deco|deco/.test(s)) || /deco/.test(era)) return 'Deco';
return 'Mixed';
}
function runQuery(sql) {
// Use psql -A (unaligned), -t (tuples only), --field-separator='\x1f' for
// a separator that never appears in the data. Faster than -X --csv for
// jsonb columns since we don't have to parse CSV quoting.
const args = ['-d', DB, '-A', '-t', '-F', '\x1f', '-c', sql];
const buf = execFileSync(PSQL, args, { maxBuffer: 1024 * 1024 * 1024 }); // 1GB
return buf.toString('utf8');
}
function main() {
const t0 = Date.now();
console.log('[build-library-cache] connecting to dw_unified…');
const productTypesIn = PRODUCT_TYPE_ALLOW.map(t => `'${t.replace(/'/g, "''")}'`).join(',');
// SELECT JSON aggregates per row so we don't have to parse arrays manually.
// jsonb -> text -> JS via JSON.parse. shopify_id is a stable key.
const sql = `
SELECT
sce.id::text AS enrichment_id,
sce.shopify_id,
sce.handle,
sce.title,
sce.vendor,
sce.image_url,
sce.dominant_hex,
sce.color_family,
COALESCE(sce.hex_codes::text, '[]') AS hex_codes,
COALESCE(sce.colors::text, '[]') AS colors,
COALESCE(sce.styles::text, '[]') AS styles,
COALESCE(sce.patterns::text, '[]') AS patterns,
sce.material,
sce.design_era,
sce.mood,
COALESCE(sce.ai_tags::text, '[]') AS ai_tags,
sp.sku,
sp.dw_sku,
sp.mfr_sku,
sp.tags,
sp.product_type,
sp.price,
sp.retail_price,
sp.pattern_name,
sp.created_at_shopify
FROM shopify_color_enrichment sce
LEFT JOIN shopify_products sp ON sce.shopify_id = sp.shopify_id
WHERE sce.image_url IS NOT NULL
AND sce.hex_codes IS NOT NULL
AND (sp.product_type IS NULL OR sp.product_type IN (${productTypesIn}))
AND sce.title IS NOT NULL
ORDER BY sce.id ASC;
`;
const raw = runQuery(sql);
const lines = raw.split('\n').filter(L => L.trim().length > 0);
console.log(`[build-library-cache] got ${lines.length} rows from PG`);
const out = [];
let skipped = 0;
for (const line of lines) {
const parts = line.split('\x1f');
if (parts.length < 22) { skipped++; continue; }
const [
enrichment_id, shopify_id, handle, title, vendor, image_url,
dominant_hex, color_family, hex_codes_j, colors_j, styles_j, patterns_j,
material, design_era, mood, ai_tags_j,
sku, dw_sku, mfr_sku, tags, product_type, price, retail_price,
pattern_name, created_at_shopify
] = parts;
let palette = [];
try { palette = JSON.parse(hex_codes_j || '[]'); } catch {}
let stylesArr = [];
try { stylesArr = JSON.parse(styles_j || '[]'); } catch {}
let patternsArr = [];
try { patternsArr = JSON.parse(patterns_j || '[]'); } catch {}
let aiTagsArr = [];
try { aiTagsArr = JSON.parse(ai_tags_j || '[]'); } catch {}
// Pre-scrub vendor from title + pattern_name at cache-build time.
// The server-side scrub runs again as a safety net, but pre-scrubbing
// here makes the cache self-protecting — even an admin-only JSON dump
// of the raw cache wouldn't leak the vendor name in the title field.
function scrubVendorInline(s, v) {
if (!s || !v) return s || '';
const vEsc = String(v).replace(/[.*+?^${}()|[\]\\]/g, '\\$&');
// Match " | <vendor>", " - <vendor>", " by <vendor>", "(<vendor>)",
// " (<vendor>)" or a standalone trailing <vendor>.
const re = new RegExp(
`\\s*[|·\\-–—]\\s*${vEsc}\\b|\\s+by\\s+${vEsc}\\b|\\(\\s*${vEsc}\\s*\\)|\\s+${vEsc}\\b\\s*$|^${vEsc}\\s*[|·\\-–—]\\s*`,
'gi'
);
return String(s).replace(re, '').replace(/\s{2,}/g, ' ').replace(/\s*[|·\-–—]\s*$/, '').trim();
}
const titleScrubbed = scrubVendorInline(title, vendor);
const patternScrubbed = scrubVendorInline(pattern_name, vendor);
const row = {
id: parseInt(enrichment_id, 10),
shopify_id,
handle: handle || '',
title: titleScrubbed || '',
image_url,
// dominant_hex normalized to #RRGGBB; fall back to first palette color.
dominant_hex: (dominant_hex && /^#[0-9a-fA-F]{6}$/.test(dominant_hex))
? dominant_hex.toUpperCase()
: (palette[0] || '#888888'),
palette: Array.isArray(palette) ? palette.slice(0, 6) : [],
color_family: color_family || '',
hue_bucket: '',
style_bucket: '',
styles: Array.isArray(stylesArr) ? stylesArr : [],
patterns: Array.isArray(patternsArr) ? patternsArr : [],
ai_tags: Array.isArray(aiTagsArr) ? aiTagsArr.slice(0, 12) : [],
material: material || '',
design_era: design_era || '',
mood: mood || '',
tags_redacted: redactTags(tags, vendor),
product_type: product_type || '',
sku: '',
price: (price && !isNaN(parseFloat(price))) ? parseFloat(price) : null,
retail_price: (retail_price && !isNaN(parseFloat(retail_price)))
? parseFloat(retail_price) : null,
pattern_name: patternScrubbed || '',
created_at: created_at_shopify || '',
// Audit-only field — NEVER render to customer surface.
_vendor_raw: vendor || ''
};
row.sku = resolveSku({ id: row.id, dw_sku, sku, mfr_sku });
row.hue_bucket = hueBucketOf(row.dominant_hex) || 'neutral';
row.style_bucket = styleBucketOf(row.styles, row.patterns, row.design_era);
out.push(row);
}
console.log(`[build-library-cache] kept ${out.length} rows, skipped ${skipped} malformed lines`);
// Sort by created_at desc so the default "Newest" sort is fast (just slice
// the head of the array, no resort needed).
out.sort((a, b) => {
const ta = Date.parse(a.created_at) || 0;
const tb = Date.parse(b.created_at) || 0;
return tb - ta;
});
// Stats banner — palette coverage, sku coverage, vendor distribution.
const stats = {
rows: out.length,
with_palette_3plus: out.filter(r => r.palette.length >= 3).length,
with_price: out.filter(r => r.price != null).length,
with_room_era: out.filter(r => r.design_era).length,
unique_vendors: new Set(out.map(r => r._vendor_raw).filter(Boolean)).size,
hue_buckets: {},
style_buckets: {}
};
for (const r of out) {
stats.hue_buckets[r.hue_bucket] = (stats.hue_buckets[r.hue_bucket]||0) + 1;
stats.style_buckets[r.style_bucket] = (stats.style_buckets[r.style_bucket]||0) + 1;
}
console.log('[build-library-cache] stats:', JSON.stringify(stats, null, 2));
fs.writeFileSync(OUT, JSON.stringify({ generated_at: new Date().toISOString(), stats, rows: out }));
const sz = fs.statSync(OUT).size;
console.log(`[build-library-cache] wrote ${OUT} (${(sz/1024/1024).toFixed(1)} MB) in ${Date.now()-t0}ms`);
}
if (require.main === module) {
try { main(); }
catch (err) {
console.error('[build-library-cache] FATAL:', err && err.stack || err);
process.exit(1);
}
}