← back to Letsbegin
Add rw-catalog-recrawl.js: feed-first RW re-crawl into vendor_catalog to recover real R##### skus for the 94 unmatched mural cohort + build backfill match table
7f5075017d52b0e556d68370fc7f3793224e622c · 2026-06-11 15:11:02 -0700 · Steve Abrams
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
Files touched
Diff
commit 7f5075017d52b0e556d68370fc7f3793224e622c
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Thu Jun 11 15:11:02 2026 -0700
Add rw-catalog-recrawl.js: feed-first RW re-crawl into vendor_catalog to recover real R##### skus for the 94 unmatched mural cohort + build backfill match table
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
---
rw-catalog-recrawl.js | 300 ++++++++++++++++++++++++++++++++++++++++++++++++++
1 file changed, 300 insertions(+)
diff --git a/rw-catalog-recrawl.js b/rw-catalog-recrawl.js
new file mode 100644
index 0000000..85cf241
--- /dev/null
+++ b/rw-catalog-recrawl.js
@@ -0,0 +1,300 @@
+#!/usr/bin/env node
+/**
+ * rw-catalog-recrawl.js — feed-first re-crawl of Rebel Walls (rebelwalls.com)
+ * into the `vendor_catalog` table to recover the real R##### mfr_skus for the
+ * cohort of newer RW mural products that have no catalog match, then write the
+ * 94→sku mapping for the Shopify backfill stage.
+ *
+ * SCOPE: catalog crawl into vendor_catalog ONLY. No Shopify product PUSH, no
+ * cost/price writes. (The RW Shopify bulk-PUSH that Steve stopped 2026-06-11 is
+ * a different thing — this only ingests current designs into our catalog.)
+ *
+ * rebelwalls.com runs Gimmersta "Cronos": every product page carries a
+ * JSON-LD <script type=application/ld+json> @type:Product with the real
+ * name ("Abstract Arches, Neutral"), sku (R19536), image, offers.price.
+ * Product slugs are root-level kebab-case. We derive each of the 94 cohort
+ * products' slug from its Shopify title, fetch the PDP, parse JSON-LD, and also
+ * harvest the page's sibling colorways (broadens the crawl). Everything
+ * harvested is upserted into vendor_catalog on the (vendor_code, mfr_sku)
+ * unique key; new mfr_skus get a fresh DWRW-###### dw_sku from the existing
+ * series max (never reassigns an mfr_sku that already has a dw_sku).
+ *
+ * Usage:
+ * node rw-catalog-recrawl.js --crawl # crawl + upsert vendor_catalog + build match table
+ * node rw-catalog-recrawl.js --crawl --dry # crawl, report, NO db writes
+ *
+ * Env: PG* optional (defaults user stevestudio2, db dw_unified, host /tmp).
+ */
+const https = require('https');
+const { Client } = require('pg');
+
+const VENDOR_CODE = 'rebel_walls';
+const BASE = 'https://rebelwalls.com';
+const UA = 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36';
+
+const args = process.argv.slice(2);
+const DRY = args.includes('--dry');
+const DO_CRAWL = args.includes('--crawl');
+if (!DO_CRAWL) { console.error('Usage: node rw-catalog-recrawl.js --crawl [--dry]'); process.exit(2); }
+
+const sleep = (ms) => new Promise((r) => setTimeout(r, ms));
+
+function pgConnect() {
+ const pg = new Client({
+ user: process.env.PGUSER || 'stevestudio2',
+ database: process.env.PGDATABASE || 'dw_unified',
+ host: process.env.PGHOST || '/tmp',
+ });
+ return pg.connect().then(() => pg);
+}
+
+function fetchHtml(url) {
+ return new Promise((resolve, reject) => {
+ const req = https.request(url, { method: 'GET', headers: { 'User-Agent': UA, Accept: 'text/html' } }, (res) => {
+ if (res.statusCode === 404) { res.resume(); return resolve(null); }
+ if (res.statusCode >= 300 && res.statusCode < 400 && res.headers.location) {
+ res.resume();
+ const next = res.headers.location.startsWith('http') ? res.headers.location : BASE + res.headers.location;
+ return fetchHtml(next).then(resolve, reject);
+ }
+ if (res.statusCode !== 200) { res.resume(); return reject(new Error(`HTTP ${res.statusCode} ${url}`)); }
+ let c = '';
+ res.on('data', (d) => (c += d));
+ res.on('end', () => resolve(c));
+ });
+ req.on('error', reject);
+ req.setTimeout(25000, () => { req.destroy(); reject(new Error('timeout ' + url)); });
+ req.end();
+ });
+}
+
+function decodeEntities(s) {
+ return String(s || '')
+ .replace(/"/g, '"').replace(/�?39;/g, "'").replace(/'/g, "'")
+ .replace(/&/g, '&').replace(/</g, '<').replace(/>/g, '>');
+}
+
+// Derive candidate product slugs from a Shopify title.
+// "Abstract Fields, Green & Red Wallcoverings | Rebel Walls" -> abstract-fields-green-red
+function slugVariants(title) {
+ let t = decodeEntities(title)
+ .replace(/\s*Wallcoverings\s*\|\s*Rebel Walls\s*$/i, '')
+ .replace(/\s*\|\s*Rebel Walls\s*$/i, '');
+ const base = (repAmp) => {
+ let s = t.replace(/&/g, repAmp).replace(/,/g, ' ').toLowerCase();
+ s = s.replace(/[^a-z0-9]+/g, '-').replace(/^-+|-+$/g, '');
+ return s;
+ };
+ const set = new Set();
+ // RW drops '&' entirely (green & red -> green-red); also try 'and' just in case
+ for (const amp of [' ', ' and ']) {
+ const s = base(amp);
+ set.add(s);
+ // gray -> grey fallback (RW uses British spelling)
+ if (/(^|-)gray(-|$)/.test(s)) set.add(s.replace(/(^|-)gray(-|$)/g, '$1grey$2'));
+ }
+ return [...set];
+}
+
+function parseProductLd(html) {
+ const re = /<script type="application\/ld\+json">([\s\S]*?)<\/script>/g;
+ let m;
+ while ((m = re.exec(html))) {
+ let d;
+ try { d = JSON.parse(m[1]); } catch { continue; }
+ const items = Array.isArray(d) ? d : (d['@graph'] || [d]);
+ for (const it of items) {
+ if (it && it['@type'] === 'Product') {
+ let img = it.image;
+ if (Array.isArray(img)) img = img[0];
+ let offers = it.offers;
+ if (Array.isArray(offers)) offers = offers[0];
+ return {
+ name: decodeEntities(it.name || '').trim(),
+ sku: (it.sku || '').trim(),
+ image: typeof img === 'string' ? img : '',
+ price: offers && offers.price != null ? Number(offers.price) : null,
+ };
+ }
+ }
+ }
+ return null;
+}
+
+// Harvest sibling colorways from the inline state blob (broadens the crawl).
+// colorways[] entries look like {"no":"R12345","name":"Blue","link":"/slug",...}
+function parseColorways(html, baseName) {
+ const out = [];
+ const re = /"no"\s*:\s*"(R\d+)"\s*,\s*"name"\s*:\s*"([^"]*)"[^}]*?"link"\s*:\s*"([^"]*)"/g;
+ let m;
+ const seen = new Set();
+ while ((m = re.exec(html))) {
+ const sku = m[1];
+ if (seen.has(sku)) continue;
+ seen.add(sku);
+ const color = decodeEntities(m[2]).trim();
+ let link = decodeEntities(m[3]).trim();
+ if (link && !link.startsWith('http')) link = BASE + (link.startsWith('/') ? '' : '/') + link;
+ out.push({ sku, pattern_name: baseName, color_name: color, product_url: link });
+ }
+ return out;
+}
+
+// "Abstract Arches, Neutral" -> {pattern:"Abstract Arches", color:"Neutral"}
+function splitName(name) {
+ const ci = name.lastIndexOf(',');
+ if (ci > 0) return { pattern: name.slice(0, ci).trim(), color: name.slice(ci + 1).trim() };
+ return { pattern: name.trim(), color: '' };
+}
+
+async function nextDwSeq(pg) {
+ const r = await pg.query(
+ `SELECT coalesce(max((regexp_replace(dw_sku,'\\D','','g'))::bigint),0) AS mx
+ FROM vendor_catalog WHERE vendor_code=$1 AND dw_sku ~ '^DWRW-[0-9]+$'`, [VENDOR_CODE]);
+ return Number(r.rows[0].mx);
+}
+
+async function main() {
+ const pg = await pgConnect();
+ try {
+ // 1. Load the 94 cohort
+ const cohort = (await pg.query(`
+ SELECT shopify_id, title
+ FROM shopify_products
+ WHERE created_at_shopify >= now() - interval '20 days'
+ AND vendor='Rebel Walls'
+ AND coalesce(dw_sku,'')=''
+ AND NOT EXISTS (
+ SELECT 1 FROM vendor_catalog vc
+ WHERE vc.vendor_code IN ('rebel_walls','rebelwalls')
+ AND lower(trim(vc.pattern_name)) = lower(trim(split_part(regexp_replace(title,'\\s*Wallcoverings.*$','','i'),',',1)))
+ )
+ ORDER BY title`)).rows;
+ console.log(`[crawl] cohort to recover: ${cohort.length}`);
+
+ // 2. Crawl each cohort product page -> real JSON-LD sku, + harvest colorways
+ const harvested = new Map(); // mfr_sku(UPPER) -> design row
+ const cohortMatch = []; // {shopify_id, title, mfr_sku, image, product_url, pattern, color}
+ const unresolved = [];
+
+ let i = 0;
+ for (const row of cohort) {
+ i++;
+ const variants = slugVariants(row.title);
+ let pdp = null, usedUrl = null, html = null;
+ for (const slug of variants) {
+ const url = `${BASE}/${slug}`;
+ try { html = await fetchHtml(url); } catch (e) { html = null; }
+ if (html) {
+ const ld = parseProductLd(html);
+ if (ld && ld.sku) { pdp = ld; usedUrl = url; break; }
+ }
+ await sleep(200);
+ }
+ if (!pdp) {
+ unresolved.push({ shopify_id: row.shopify_id, title: row.title, tried: variants });
+ console.log(` [${i}/${cohort.length}] UNRESOLVED "${row.title}" (tried ${variants.join(', ')})`);
+ await sleep(900);
+ continue;
+ }
+ const { pattern, color } = splitName(pdp.name);
+ const mfrU = pdp.sku.toUpperCase();
+ harvested.set(mfrU, { mfr_sku: pdp.sku, pattern_name: pattern, color_name: color, image_url: pdp.image, product_url: usedUrl, price: pdp.price });
+ cohortMatch.push({ shopify_id: row.shopify_id, title: row.title, mfr_sku: pdp.sku, product_url: usedUrl, pattern, color });
+
+ // sibling colorways broaden the catalog
+ for (const cw of parseColorways(html, pattern)) {
+ const u = cw.sku.toUpperCase();
+ if (!harvested.has(u)) harvested.set(u, { mfr_sku: cw.sku, pattern_name: cw.pattern_name, color_name: cw.color_name, image_url: '', product_url: cw.product_url, price: null });
+ }
+ console.log(` [${i}/${cohort.length}] OK "${row.title}" -> ${pdp.sku} (${pattern}, ${color})`);
+ await sleep(900); // ~1 req/s, gentle on the small vendor
+ }
+
+ console.log(`\n[crawl] cohort resolved: ${cohortMatch.length}/${cohort.length}, unresolved: ${unresolved.length}`);
+ console.log(`[crawl] distinct designs harvested (cohort + sibling colorways): ${harvested.size}`);
+
+ if (DRY) {
+ console.log('[dry] no db writes. Unresolved:');
+ unresolved.forEach((u) => console.log(' ' + u.title));
+ return;
+ }
+
+ // 3. Upsert harvested designs into vendor_catalog, assigning DWRW dw_sku for NEW mfr_skus only.
+ // Dedup case-insensitively against existing rows; never reassign an existing dw_sku.
+ const existing = (await pg.query(
+ `SELECT upper(trim(mfr_sku)) AS u, mfr_sku, dw_sku FROM vendor_catalog WHERE vendor_code=$1`, [VENDOR_CODE])).rows;
+ const existingByUpper = new Map(existing.map((r) => [r.u, r]));
+ let seq = await nextDwSeq(pg);
+
+ let inserted = 0, updated = 0;
+ for (const [u, d] of harvested) {
+ const ex = existingByUpper.get(u);
+ if (ex) {
+ // already present — refresh descriptive fields only, keep its dw_sku
+ const r = await pg.query(`
+ UPDATE vendor_catalog
+ SET pattern_name = COALESCE(NULLIF($3,''), pattern_name),
+ color_name = COALESCE(NULLIF($4,''), color_name),
+ product_url = COALESCE(NULLIF($5,''), product_url),
+ image_url = COALESCE(NULLIF($6,''), image_url),
+ last_scraped_at = now(),
+ updated_at = now()
+ WHERE vendor_code=$1 AND upper(trim(mfr_sku))=$2`,
+ [VENDOR_CODE, u, d.pattern_name, d.color_name, d.product_url, d.image_url]);
+ updated += r.rowCount;
+ } else {
+ seq += 1;
+ const dw = `DWRW-${seq}`;
+ await pg.query(`
+ INSERT INTO vendor_catalog
+ (vendor_code, mfr_sku, dw_sku, pattern_name, color_name, product_type,
+ image_url, product_url, sync_status, first_seen_at, last_scraped_at, updated_at)
+ VALUES ($1,$2,$3,$4,$5,'Mural',$6,$7,'new',now(),now(),now())
+ ON CONFLICT (vendor_code, mfr_sku) DO NOTHING`,
+ [VENDOR_CODE, d.mfr_sku, dw, d.pattern_name, d.color_name, d.image_url, d.product_url]);
+ existingByUpper.set(u, { u, mfr_sku: d.mfr_sku, dw_sku: dw });
+ inserted += 1;
+ }
+ }
+ console.log(`[vendor_catalog] inserted (new designs): ${inserted}, refreshed (already present): ${updated}`);
+
+ // 4. Build the cohort match table for the Shopify backfill stage.
+ // Each cohort product -> its real dw_sku (looked up by mfr_sku, freshly inserted or pre-existing).
+ await pg.query(`
+ CREATE TABLE IF NOT EXISTS rw_recrawl_match_94 (
+ shopify_id text PRIMARY KEY,
+ title text,
+ mfr_sku text,
+ dw_sku text,
+ product_url text,
+ match_kind text DEFAULT 'recrawl-jsonld',
+ shopify_done boolean DEFAULT false,
+ shopify_error text,
+ created_at timestamptz DEFAULT now()
+ )`);
+ await pg.query('TRUNCATE rw_recrawl_match_94');
+ let mapped = 0, nodw = 0;
+ for (const c of cohortMatch) {
+ const ex = existingByUpper.get(c.mfr_sku.toUpperCase());
+ const dw = ex && ex.dw_sku;
+ if (!dw) { nodw++; continue; }
+ await pg.query(
+ `INSERT INTO rw_recrawl_match_94 (shopify_id,title,mfr_sku,dw_sku,product_url)
+ VALUES ($1,$2,$3,$4,$5)
+ ON CONFLICT (shopify_id) DO UPDATE SET mfr_sku=excluded.mfr_sku, dw_sku=excluded.dw_sku, product_url=excluded.product_url`,
+ [c.shopify_id, c.title, ex.mfr_sku, dw, c.product_url]);
+ mapped++;
+ }
+ console.log(`[match] rw_recrawl_match_94 rows: ${mapped} (cohort products with a resolved dw_sku; ${nodw} had no dw_sku)`);
+
+ if (unresolved.length) {
+ console.log('\n[UNRESOLVED — still absent from rebelwalls.com]:');
+ unresolved.forEach((u) => console.log(' ' + u.title));
+ }
+ } finally {
+ await pg.end();
+ }
+}
+
+main().catch((e) => { console.error('FATAL', e.message, '\n', e.stack); process.exit(1); });
← df2dbbf Add 3-material-options priced metafield (Standard/P&S/Commer
·
back to Letsbegin
·
Add rw-recrawl-backfill.js: set-based DB update + idempotent 1c651fe →