← back to Designer Wallcoverings
Thibaut/AF wave-2 scrape backfill: PDP SingleRollLength -> local mirror with source_url+timestamp, discontinued-redirect + conflict exception logging (pilot 14/15 ok)
1fc5c567a9a09d0e4dcec56cadb89d391ad445bb · 2026-07-02 09:44:19 -0700 · Steve Abrams
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
Files touched
A shopify/scripts/thibaut-single-roll-length-backfill.js
Diff
commit 1fc5c567a9a09d0e4dcec56cadb89d391ad445bb
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Thu Jul 2 09:44:19 2026 -0700
Thibaut/AF wave-2 scrape backfill: PDP SingleRollLength -> local mirror with source_url+timestamp, discontinued-redirect + conflict exception logging (pilot 14/15 ok)
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
---
.../scripts/thibaut-single-roll-length-backfill.js | 196 +++++++++++++++++++++
1 file changed, 196 insertions(+)
diff --git a/shopify/scripts/thibaut-single-roll-length-backfill.js b/shopify/scripts/thibaut-single-roll-length-backfill.js
new file mode 100644
index 00000000..b8189101
--- /dev/null
+++ b/shopify/scripts/thibaut-single-roll-length-backfill.js
@@ -0,0 +1,196 @@
+#!/usr/bin/env node
+/**
+ * WAVE-2 SCRAPE BACKFILL — single-roll dimensions from thibautdesign.com PDPs
+ * into the LOCAL dw_unified mirror (thibaut_catalog + anna_french_catalog).
+ *
+ * DTD verdict (2026-07-02, Approach C HYBRID): scrape the missing rows; only
+ * PDP-confirmed values, stored WITH source_url + scraped_at timestamp.
+ * Kamatera-canonical write-back is GATED — this touches the LOCAL mirror only.
+ *
+ * PDP mechanism (verified 2026-07-02 on T4018): Hydrogen/React-Router app,
+ * specs live in triple-escaped JSON in the SSR stream:
+ * \\\"SingleRollLength\\\":4.5 (single-roll yards — the customer fact)
+ * \\\"single_roll_length\\\":\\\"9\\\" (item-master string, put-up; CONFLICT-CHECKED)
+ * \\\"width\\\":27 (inches)
+ * \\\"match\\\":\\\"Half Drop Match\\\", \\\"vertical_repeat\\\":25.25
+ * Discontinued SKUs 301-redirect to /category/wallcoverings — logged as
+ * pdp_check=discontinued_redirect, NEVER auto-archived (that action is gated).
+ *
+ * Writes (LOCAL ONLY):
+ * thibaut_catalog: specs.single_roll_length ("X yd (Y m)"), specs.width
+ * ("W in (C cm)") if missing, specs.source_url,
+ * specs.scraped_at, roll_length col, width col if empty
+ * anna_french_catalog: length ("X Feet"), width ("W Inches") if empty,
+ * last_scraped
+ * Exceptions -> audits/thibaut-backfill-exceptions.jsonl (QA guardrail).
+ *
+ * Usage: node thibaut-single-roll-length-backfill.js [--dry-run] [--limit N] [--af-only|--thibaut-only]
+ * Cost: $0 (local + polite public-page fetches).
+ */
+const { Pool } = require('pg');
+const https = require('https');
+const fs = require('fs');
+const path = require('path');
+
+const pool = new Pool({ connectionString: 'postgresql:///dw_unified?host=/tmp' });
+const args = process.argv.slice(2);
+const DRY = args.includes('--dry-run');
+const limitArg = args.indexOf('--limit');
+const LIMIT = limitArg >= 0 ? parseInt(args[limitArg + 1]) || 0 : 0;
+const AF_ONLY = args.includes('--af-only');
+const TH_ONLY = args.includes('--thibaut-only');
+const BATCH = 5, DELAY_MS = 1500;
+const AUDIT_DIR = path.join(__dirname, 'audits');
+const EXC_PATH = path.join(AUDIT_DIR, 'thibaut-backfill-exceptions.jsonl');
+const UA = 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36';
+
+fs.mkdirSync(AUDIT_DIR, { recursive: true });
+const logExc = (o) => fs.appendFileSync(EXC_PATH, JSON.stringify({ ts: new Date().toISOString(), ...o }) + '\n');
+
+function fetchPage(url, hops = 0) {
+ return new Promise((resolve, reject) => {
+ if (hops > 4) return resolve({ redirectedTo: url });
+ const timeout = setTimeout(() => reject(new Error('timeout')), 25000);
+ const req = https.get(url, { headers: { 'User-Agent': UA } }, (res) => {
+ if (res.statusCode >= 300 && res.statusCode < 400 && res.headers.location) {
+ clearTimeout(timeout);
+ res.resume();
+ const loc = new URL(res.headers.location, url).href;
+ if (loc.includes('/category/')) return resolve({ redirectedTo: loc });
+ return resolve(fetchPage(loc, hops + 1));
+ }
+ let data = '';
+ res.on('data', c => data += c);
+ res.on('end', () => { clearTimeout(timeout); resolve({ html: data, status: res.statusCode, finalUrl: url }); });
+ });
+ req.on('error', (e) => { clearTimeout(timeout); reject(e); });
+ });
+}
+
+function extractSpecs(html) {
+ const num = (re) => { const m = html.match(re); return m ? parseFloat(m[1]) : null; };
+ const str = (re) => { const m = html.match(re); return m ? m[1].trim() : null; };
+ const singleRollYd = num(/\\+"SingleRollLength\\+":\s*([\d.]+)/);
+ const rawSingleRoll = str(/\\+"single_roll_length\\+":\\+"([\d.]+)\\+"/);
+ const widthIn = num(/\\+"width\\+":\s*([\d.]+)/);
+ const match = str(/\\+"match\\+":\\+"([A-Z][^\\]+)/);
+ const vRepeat = num(/\\+"vertical_repeat\\+":\s*([\d.]+)/);
+ return { singleRollYd, rawSingleRoll, widthIn, match, vRepeat };
+}
+
+const fmtYd = yd => `${yd} yd (${(yd * 0.9144).toFixed(2)} m)`;
+const fmtIn = w => `${w} in (${(w * 2.54).toFixed(2)} cm)`;
+const fmtFt = yd => `${(yd * 3) % 1 === 0 ? yd * 3 : (yd * 3).toFixed(1)} Feet`;
+const fmtInAF = w => `${w} Inches`;
+
+const sleep = ms => new Promise(r => setTimeout(r, ms));
+
+async function loadWork() {
+ const work = [];
+ if (!AF_ONLY) {
+ const { rows } = await pool.query(`
+ SELECT id, mfr_sku, product_url, width, on_shopify
+ FROM thibaut_catalog
+ WHERE COALESCE(NULLIF(specs->>'single_roll_length',''), roll_length, '') = ''
+ AND COALESCE(discontinued,false) = false
+ ORDER BY on_shopify DESC, id`);
+ for (const r of rows) work.push({
+ table: 'thibaut', id: r.id, mfr: r.mfr_sku, hasWidth: !!(r.width || '').trim(),
+ url: (r.product_url || '').trim() || `https://www.thibautdesign.com/products/wallcoverings/${r.mfr_sku}`,
+ });
+ }
+ if (!TH_ONLY) {
+ const { rows } = await pool.query(`
+ SELECT id, mfr_sku, product_url, width, on_shopify
+ FROM anna_french_catalog
+ WHERE mfr_sku LIKE 'AT%' AND COALESCE(length,'') = ''
+ AND COALESCE(discontinued,false) = false
+ ORDER BY on_shopify DESC, id`);
+ for (const r of rows) work.push({
+ table: 'af', id: r.id, mfr: r.mfr_sku, hasWidth: !!(r.width || '').trim(),
+ url: (r.product_url || '').trim() || `https://www.thibautdesign.com/products/wallcoverings/${r.mfr_sku}`,
+ });
+ }
+ return LIMIT ? work.slice(0, LIMIT) : work;
+}
+
+async function processOne(w, stats) {
+ let page;
+ try { page = await fetchPage(w.url); }
+ catch (e) { stats.fetchErr++; logExc({ mfr: w.mfr, table: w.table, reason: 'fetch_error', err: String(e.message) }); return; }
+
+ if (page.redirectedTo) {
+ stats.redirected++;
+ logExc({ mfr: w.mfr, table: w.table, reason: 'discontinued_redirect', redirectedTo: page.redirectedTo, note: 'archive decision is GATED — review' });
+ if (!DRY) {
+ if (w.table === 'thibaut')
+ await pool.query(`UPDATE thibaut_catalog SET specs = specs || jsonb_build_object('pdp_check','discontinued_redirect','pdp_checked_at', to_char(now(),'YYYY-MM-DD"T"HH24:MI:SS')), updated_at = now() WHERE id=$1`, [w.id]);
+ }
+ return;
+ }
+ if (page.status !== 200 || !page.html) {
+ stats.fetchErr++;
+ logExc({ mfr: w.mfr, table: w.table, reason: 'bad_status', status: page.status });
+ return;
+ }
+ const s = extractSpecs(page.html);
+ if (s.singleRollYd == null) {
+ stats.noLength++;
+ logExc({ mfr: w.mfr, table: w.table, reason: 'no_single_roll_length_on_pdp' });
+ return;
+ }
+ // conflict guardrail: item-master string vs SingleRollLength (expect 2x put-up or equal)
+ if (s.rawSingleRoll != null) {
+ const raw = parseFloat(s.rawSingleRoll);
+ if (raw !== s.singleRollYd && raw !== s.singleRollYd * 2) {
+ stats.conflict++;
+ logExc({ mfr: w.mfr, table: w.table, reason: 'length_field_conflict', SingleRollLength: s.singleRollYd, raw_single_roll_length: raw, note: 'stored SingleRollLength; flagged for review' });
+ }
+ }
+ stats.scraped++;
+ if (DRY) { stats.wouldWrite++; return; }
+ const nowIso = new Date().toISOString();
+ if (w.table === 'thibaut') {
+ const specsPatch = {
+ single_roll_length: fmtYd(s.singleRollYd),
+ source_url: page.finalUrl, scraped_at: nowIso,
+ pdp_single_roll_yd: s.singleRollYd,
+ };
+ if (s.widthIn != null) specsPatch.width = fmtIn(s.widthIn);
+ if (s.match) specsPatch.match = s.match;
+ if (s.vRepeat != null) specsPatch.vertical_repeat = String(s.vRepeat);
+ await pool.query(
+ `UPDATE thibaut_catalog SET
+ specs = specs || $2::jsonb,
+ roll_length = $3,
+ width = CASE WHEN COALESCE(width,'')='' THEN $4 ELSE width END,
+ updated_at = now()
+ WHERE id = $1`,
+ [w.id, JSON.stringify(specsPatch), fmtYd(s.singleRollYd), s.widthIn != null ? fmtIn(s.widthIn) : null]);
+ } else {
+ await pool.query(
+ `UPDATE anna_french_catalog SET
+ length = $2,
+ width = CASE WHEN COALESCE(width,'')='' THEN $3 ELSE width END,
+ product_url = COALESCE(NULLIF(product_url,''), $4),
+ last_scraped = now(), updated_at = now()
+ WHERE id = $1`,
+ [w.id, fmtFt(s.singleRollYd), s.widthIn != null ? fmtInAF(s.widthIn) : null, page.finalUrl]);
+ }
+ stats.updated++;
+}
+
+(async () => {
+ const work = await loadWork();
+ console.log(`${DRY ? '[DRY-RUN] ' : ''}Backfill queue: ${work.length} rows (thibaut=${work.filter(x=>x.table==='thibaut').length}, af=${work.filter(x=>x.table==='af').length})`);
+ const stats = { scraped: 0, updated: 0, wouldWrite: 0, redirected: 0, fetchErr: 0, noLength: 0, conflict: 0 };
+ for (let i = 0; i < work.length; i += BATCH) {
+ await Promise.all(work.slice(i, i + BATCH).map(w => processOne(w, stats).catch(e => { stats.fetchErr++; logExc({ mfr: w.mfr, reason: 'unhandled', err: String(e) }); })));
+ process.stdout.write(`\r ${Math.min(i + BATCH, work.length)}/${work.length} ok=${stats.scraped} upd=${stats.updated} disco=${stats.redirected} err=${stats.fetchErr} nolen=${stats.noLength} conflict=${stats.conflict}`);
+ await sleep(DELAY_MS);
+ }
+ console.log('\nDONE', JSON.stringify(stats));
+ const summary = { finished_at: new Date().toISOString(), dry_run: DRY, queue: work.length, ...stats };
+ fs.writeFileSync(path.join(AUDIT_DIR, 'thibaut-backfill-summary.json'), JSON.stringify(summary, null, 1));
+ await pool.end();
+})();
← 68d42e53 DWTT/AF single-roll dimensions: read-only spec audit + wave-
·
back to Designer Wallcoverings
·
auto-save: 2026-07-02T09:46:55 (9 files) — pending-approval/ d10d9eba →