[object Object]

← back to Designer Wallcoverings

WallQuest real-textile onboard pipeline: normalize + load + beach-city naming

3483fed8dc14703b95063cdbfdd83fa53922961d · 2026-07-09 23:02:03 -0700 · Steve

Parameterized downstream for Lillian August x2 + Daisy Bennett Vol 2 (187 priced SKUs):
- normalize-book.cjs: per-yard textile pricing (bolt retail/8, min/step 8, Priced Per Yard)
- load-book.cjs: per-book dw_unified staging table (lillian_august_catalog, daisy_bennett_v2_catalog)
- assign-names.cjs: beach-city {City}{Material} naming (Phillipe Romano), hides ALL vendor
  names (Lillian August/Daisy/WallQuest), leak-checked (0 leaks across 187)

187 scraped->normalized->per-yard->DB->named-vendor-safe. Remaining: local enrich, settlement
gate (Daisy florals), patch-quilt rooms, stage->gated push.

Files touched

Diff

commit 3483fed8dc14703b95063cdbfdd83fa53922961d
Author: Steve <steve@designerwallcoverings.com>
Date:   Thu Jul 9 23:02:03 2026 -0700

    WallQuest real-textile onboard pipeline: normalize + load + beach-city naming
    
    Parameterized downstream for Lillian August x2 + Daisy Bennett Vol 2 (187 priced SKUs):
    - normalize-book.cjs: per-yard textile pricing (bolt retail/8, min/step 8, Priced Per Yard)
    - load-book.cjs: per-book dw_unified staging table (lillian_august_catalog, daisy_bennett_v2_catalog)
    - assign-names.cjs: beach-city {City}{Material} naming (Phillipe Romano), hides ALL vendor
      names (Lillian August/Daisy/WallQuest), leak-checked (0 leaks across 187)
    
    187 scraped->normalized->per-yard->DB->named-vendor-safe. Remaining: local enrich, settlement
    gate (Daisy florals), patch-quilt rooms, stage->gated push.
---
 scripts/wallquest-refresh/assign-names.cjs   | 39 ++++++++++++++++++++++++++++
 scripts/wallquest-refresh/load-book.cjs      | 39 ++++++++++++++++++++++++++++
 scripts/wallquest-refresh/normalize-book.cjs | 39 ++++++++++++++++++++++++++++
 3 files changed, 117 insertions(+)

diff --git a/scripts/wallquest-refresh/assign-names.cjs b/scripts/wallquest-refresh/assign-names.cjs
new file mode 100644
index 00000000..819026cb
--- /dev/null
+++ b/scripts/wallquest-refresh/assign-names.cjs
@@ -0,0 +1,39 @@
+// Beach-city naming for a WallQuest real-textile book (Phillipe Romano private label).
+// Detects material -> maps to a beach city -> pattern_name={City} {Material},
+// title={City} {Material} {Color} Wallcovering | Phillipe Romano. Hides ALL vendor names.
+// Leak-checks customer-facing fields. Env: TABLE.
+const { execSync } = require('child_process');
+const TABLE = process.env.TABLE;
+const CONN = process.env.DATABASE_URL || 'postgresql://dw_admin@127.0.0.1:5432/dw_unified';
+const psql = sql => execSync(`psql "${CONN}" -tAF'|' -c "${sql.replace(/"/g,'\\"')}"`, { encoding: 'utf8' });
+
+const CITY = { grasscloth:'Nantucket', paperweave:'Montauk', raffia:'Newport', netting:'Southampton',
+  bamboo:'Cape May', sisal:'Sag Harbor', cork:'Kennebunkport', fiber:'Rockport', jute:'Chatham',
+  mica:'Bar Harbor', nonwoven:'Provincetown', silk:'Watch Hill', 'wood veneer':'Edgartown',
+  linen:"Martha's Vineyard", seagrass:'Block Island', hemp:'Marblehead', abaca:'Ogunquit',
+  viscose:'Rockport', rayon:'Rockport', cotton:'Osterville', arrowroot:'Wellfleet', string:'Truro' };
+const MATS = ['grasscloth','paperweave','raffia','netting','bamboo','sisal','cork','jute','mica',
+  'wood veneer','silk','linen','seagrass','hemp','abaca','viscose','rayon','cotton','arrowroot','string','fiber'];
+const tc = s => (s||'').split(/\s+/).map(w => w ? w[0].toUpperCase()+w.slice(1).toLowerCase() : w).join(' ').trim();
+const FORBID = /wallquest|lillian\s*august|carl\s*robinson|daisy\s*bennett|chesapeake|brewster|nextwall|seabrook/i;
+const clean = s => (s||'').replace(/\bwallpapers?\b/gi,'Wallcovering');
+
+const rows = psql(`SELECT id, COALESCE(src_pattern,''), COALESCE(color_name,''), COALESCE(material,''), COALESCE(specs::text,'{}') FROM ${TABLE}`).trim().split('\n').filter(Boolean);
+let leaks = 0, done = 0; const sqls = [];
+for (const line of rows) {
+  const [id, pat, color, mat, specsRaw] = line.split('|');
+  const hay = `${pat} ${mat} ${specsRaw}`.toLowerCase();
+  const material = MATS.find(m => hay.includes(m)) || 'grasscloth';
+  const city = CITY[material] || 'Hamptons';
+  const matTitle = tc(material);
+  const pattern_name = `${city} ${matTitle}`;
+  const colorTitle = tc(color);
+  const title = clean(`${pattern_name}${colorTitle ? ' ' + colorTitle : ''} Wallcovering | Phillipe Romano`);
+  const cust = `${pattern_name} | ${title} | ${colorTitle}`;
+  if (FORBID.test(cust)) { leaks++; console.error('LEAK', id, cust.match(FORBID)[0]); continue; }
+  sqls.push(`UPDATE ${TABLE} SET pattern_name='${pattern_name.replace(/'/g,"''")}', material='${matTitle}', updated_at=now() WHERE id=${id};`);
+  done++;
+}
+require('fs').writeFileSync('/tmp/_names.sql', sqls.join('\n'));
+execSync(`psql "${CONN}" -q -f /tmp/_names.sql`, { stdio: ['pipe','pipe','pipe'] });
+console.log(`${TABLE}: named ${done}, leaks ${leaks}`);
diff --git a/scripts/wallquest-refresh/load-book.cjs b/scripts/wallquest-refresh/load-book.cjs
new file mode 100644
index 00000000..7c08aa68
--- /dev/null
+++ b/scripts/wallquest-refresh/load-book.cjs
@@ -0,0 +1,39 @@
+// Load a normalized WallQuest real-textile book into a per-book dw_unified staging table.
+// Env: NORM (json), TABLE (catalog table). Creates table if absent, upserts on mfr_sku.
+const fs = require('fs'), { execSync } = require('child_process');
+const NORM = process.env.NORM, TABLE = process.env.TABLE;
+const CONN = process.env.DATABASE_URL || 'postgresql://dw_admin@127.0.0.1:5432/dw_unified';
+const q = sql => execSync(`psql "${CONN}" -v ON_ERROR_STOP=1 -q -c "${sql.replace(/"/g,'\\"')}"`, { stdio: ['pipe','pipe','pipe'] });
+const recs = JSON.parse(fs.readFileSync(NORM, 'utf8'));
+
+q(`CREATE TABLE IF NOT EXISTS ${TABLE} (
+  id serial PRIMARY KEY, mfr_sku text UNIQUE, dw_sku text,
+  src_pattern text, color_name text, material text, cost numeric, bolt_retail numeric,
+  our_price numeric, unit_of_measure text, min_order int, order_step int, bolt_yards int,
+  width text, roll_length text, repeat_v text, match_type text, backing text,
+  specs jsonb, image_url text, all_images jsonb, local_image_path jsonb,
+  room_setting_images jsonb, ai_description text, ai_colors jsonb, ai_styles jsonb,
+  color_hex text, pattern_name text, private_label_vendor text default 'Phillipe Romano',
+  real_vendor_name text default 'WallQuest', source_book text,
+  shopify_product_id bigint, on_shopify boolean default false,
+  product_url text, created_at timestamptz default now(), updated_at timestamptz default now());`);
+
+const esc = s => s == null ? 'NULL' : `'${String(s).replace(/'/g, "''")}'`;
+const jesc = o => `'${JSON.stringify(o || null).replace(/'/g, "''")}'::jsonb`;
+let n = 0; const sqls = [];
+for (const r of recs) {
+  sqls.push(`INSERT INTO ${TABLE} (mfr_sku,dw_sku,src_pattern,color_name,material,cost,bolt_retail,our_price,
+    unit_of_measure,min_order,order_step,bolt_yards,width,roll_length,repeat_v,match_type,backing,specs,
+    image_url,all_images,source_book,product_url)
+    VALUES (${esc(r.mfr_sku)},${esc(r.dw_sku)},${esc(r.pattern)},${esc(r.color)},${esc(r.material)},
+    ${r.cost||'NULL'},${r.bolt_retail||'NULL'},${r.our_price||'NULL'},${esc(r.unit_of_measure)},
+    ${r.min_order||8},${r.order_step||8},${r.bolt_yards||8},${esc(r.width)},${esc(r.length)},${esc(r.repeat)},
+    ${esc(r.match)},${esc(r.backing)},${jesc(r.specs)},${esc((r.images||[])[0]||'')},${jesc(r.images)},
+    ${esc(process.env.BOOK||'')},${esc(r.product_url)})
+    ON CONFLICT (mfr_sku) DO UPDATE SET dw_sku=EXCLUDED.dw_sku, our_price=EXCLUDED.our_price,
+    cost=EXCLUDED.cost, bolt_retail=EXCLUDED.bolt_retail, all_images=EXCLUDED.all_images, updated_at=now();`);
+  n++;
+}
+fs.writeFileSync('/tmp/_load.sql', sqls.join('\n'));
+execSync(`psql "${CONN}" -q -f /tmp/_load.sql`, { stdio: ['pipe','pipe','pipe'] });
+console.log(`${TABLE}: loaded ${n} rows`);
diff --git a/scripts/wallquest-refresh/normalize-book.cjs b/scripts/wallquest-refresh/normalize-book.cjs
new file mode 100644
index 00000000..7c7fd1e6
--- /dev/null
+++ b/scripts/wallquest-refresh/normalize-book.cjs
@@ -0,0 +1,39 @@
+// Parameterized normalize for a WallQuest real-textile book → structured records.
+// Per-yard textile pricing (WQ real-textile rule): bolt cost -> bolt retail (cost/0.65/0.85),
+// per-yard = bolt_retail/8, min/step 8. Env: IN (scraped jsonl), OUT (json), PREFIX (dw sku prefix).
+const fs = require('fs');
+const IN = process.env.IN, OUT = process.env.OUT, PREFIX = process.env.PREFIX || 'DWLA';
+const rows = fs.readFileSync(IN, 'utf8').trim().split('\n').filter(Boolean).map(l => { try { return JSON.parse(l); } catch { return null; } }).filter(Boolean);
+// dedup by mfr_sku, prefer priced+named row
+const byKey = {};
+for (const r of rows) {
+  const k = r.mfr_sku || (r.url || '').split('/').pop().toUpperCase();
+  if (!k) continue;
+  if (!byKey[k] || (r.priceValue && !byKey[k].priceValue) || (r.name && !byKey[k].name)) byKey[k] = r;
+}
+const rec = [];
+for (const [mfr, r] of Object.entries(byKey)) {
+  if (!r.priceValue) continue;                    // keep only priced
+  const numTail = (mfr.match(/\d+/) || ['0'])[0];
+  const dw_sku = `${PREFIX}-${numTail}`;
+  const s = r.specs || {};
+  const title = r.name || '';
+  const pattern = (s['Design Name'] || s['Pattern'] || title.split(' - ')[0] || '').trim();
+  const color = (s['Colorway Name'] || s['Color'] || title.split(' - ')[1] || '').trim();
+  const cost = parseFloat(String(r.priceValue).replace(/,/g, ''));
+  const boltRetail = +(cost / 0.65 / 0.85).toFixed(2);   // 8-yard bolt retail
+  const perYard = +(boltRetail / 8).toFixed(2);          // real-textile: sold per yard
+  rec.push({
+    mfr_sku: mfr, dw_sku, src_title: title, pattern, color,
+    cost, bolt_retail: boltRetail, our_price: perYard,   // our_price = per-yard sell
+    unit_of_measure: 'Priced Per Yard', min_order: 8, order_step: 8, bolt_yards: 8,
+    material: s['Material'] || '', backing: s['Backing Material'] || s['Backing'] || '',
+    width: s['Roll Width'] || s['Width'] || '', length: s['Roll Length'] || s['Length'] || '',
+    repeat: s['Design Repeat'] || s['Vertical Repeat'] || '', match: s['Match'] || s['Design Match'] || '',
+    specs: s, images: r.imgs || [], product_url: r.url,
+  });
+}
+fs.writeFileSync(OUT, JSON.stringify(rec, null, 2));
+const pats = {}; for (const x of rec) (pats[x.pattern] = pats[x.pattern] || []).push(x.color);
+console.log(`${IN.split('/').pop()}: normalized ${rec.length} priced rows → ${OUT}`);
+console.log(`  distinct patterns: ${Object.keys(pats).length}; sample: ${Object.keys(pats).slice(0,6).join(' | ')}`);

← e7041981 auto-save: 2026-07-09T22:42:31 (3 files) — pending-approval/  ·  back to Designer Wallcoverings  ·  WallQuest onboard: swatch DL + local qwen2.5vl enrich + room 8de32f95 →