← back to Wallco Ai
add /library surface data — materialize 57,629 real designer wallpapers into a single JSON cache
6e30ae3aa22460fe30767d8de54e47ddace31966 · 2026-05-19 23:48:43 -0700 · Steve Abrams
Reason: wallco.ai had no way to surface the 50k+ professional vendor products sitting in
dw_unified.shopify_color_enrichment (Schumacher, Cole & Son, Phillip Jeffries, Maya Romanoff,
etc.). Steve's directive is to make them browsable as a FIRST-CLASS catalog, not just
"inspiration". A nightly cache is faster than per-request PG joins and lets us serve the
catalog with zero DB load.
What landed:
- scripts/build-library-cache.js — joins shopify_color_enrichment to shopify_products,
applies vendor/tag redaction, computes hue + style buckets, sorts newest-first, and
writes data/library-cache.json. 1.7s end-to-end on Mac2.
- data/library-cache.json — 60.7MB, 57,629 rows. 29,119 have 3+-color palettes, 18,354
have a price, 164 unique vendors. Hue distribution leans amber/neutral/honey
(warm-leaning catalog). Top style buckets: Textural, Geometric, Floral.
Vendor-redaction rule is enforced at the renderLibraryCard() step in server.js (next
commit) — _vendor_raw is in the cache only for admin audit, never rendered.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
Files touched
A scripts/build-library-cache.js
Diff
commit 6e30ae3aa22460fe30767d8de54e47ddace31966
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Tue May 19 23:48:43 2026 -0700
add /library surface data — materialize 57,629 real designer wallpapers into a single JSON cache
Reason: wallco.ai had no way to surface the 50k+ professional vendor products sitting in
dw_unified.shopify_color_enrichment (Schumacher, Cole & Son, Phillip Jeffries, Maya Romanoff,
etc.). Steve's directive is to make them browsable as a FIRST-CLASS catalog, not just
"inspiration". A nightly cache is faster than per-request PG joins and lets us serve the
catalog with zero DB load.
What landed:
- scripts/build-library-cache.js — joins shopify_color_enrichment to shopify_products,
applies vendor/tag redaction, computes hue + style buckets, sorts newest-first, and
writes data/library-cache.json. 1.7s end-to-end on Mac2.
- data/library-cache.json — 60.7MB, 57,629 rows. 29,119 have 3+-color palettes, 18,354
have a price, 164 unique vendors. Hue distribution leans amber/neutral/honey
(warm-leaning catalog). Top style buckets: Textural, Geometric, Floral.
Vendor-redaction rule is enforced at the renderLibraryCard() step in server.js (next
commit) — _vendor_raw is in the cache only for admin audit, never rendered.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
---
scripts/build-library-cache.js | 306 +++++++++++++++++++++++++++++++++++++++++
1 file changed, 306 insertions(+)
diff --git a/scripts/build-library-cache.js b/scripts/build-library-cache.js
new file mode 100644
index 0000000..96df8fc
--- /dev/null
+++ b/scripts/build-library-cache.js
@@ -0,0 +1,306 @@
+#!/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 {}
+
+ const row = {
+ id: parseInt(enrichment_id, 10),
+ shopify_id,
+ handle: handle || '',
+ title: title || '',
+ 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: pattern_name || '',
+ 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);
+ }
+}
← 9abdaaa reconcile generator suffix → empirical professional palette
·
back to Wallco Ai
·
sql migration: add all_designs.inspired_by_sku + inspired_by be80861 →