← back to Wolfgordon Crawl
Assign sequential DW SKUs to 106 NULL WG catalog rows (DWWG-535378..535483), local only
8c28e074e20f8f75e88c0f8a7fd5f753e958cd78 · 2026-06-19 09:39:10 -0700 · Steve
Files touched
A SKU-ASSIGNMENT-NOTE.mdA assign-skus.jsA price-backfill.js
Diff
commit 8c28e074e20f8f75e88c0f8a7fd5f753e958cd78
Author: Steve <steve@designerwallcoverings.com>
Date: Fri Jun 19 09:39:10 2026 -0700
Assign sequential DW SKUs to 106 NULL WG catalog rows (DWWG-535378..535483), local only
---
SKU-ASSIGNMENT-NOTE.md | 23 +++++++++++++
assign-skus.js | 92 ++++++++++++++++++++++++++++++++++++++++++++++++++
price-backfill.js | 75 ++++++++++++++++++++++++++++++++++++++++
3 files changed, 190 insertions(+)
diff --git a/SKU-ASSIGNMENT-NOTE.md b/SKU-ASSIGNMENT-NOTE.md
new file mode 100644
index 0000000..3bdcf3b
--- /dev/null
+++ b/SKU-ASSIGNMENT-NOTE.md
@@ -0,0 +1,23 @@
+# Wolf Gordon SKU assignment — 2026-06-19
+
+## What ran (SAFE, LOCAL, REVERSIBLE)
+- `assign-skus.js --apply` assigned sequential DW SKUs to the 106
+ `wolf_gordon_catalog` rows that had `dw_sku IS NULL`.
+- Range assigned: **DWWG-535378 .. DWWG-535483** (continued from prior MAX 535377).
+- Ordered by `id ASC` (deterministic, re-runnable).
+- Existing dw_skus were REUSED, never renumbered (only NULL rows touched).
+- Ran inside a transaction with a post-write duplicate-SKU guard.
+
+## Final state of wolf_gordon_catalog.dw_sku
+- 5483 rows, ALL with a conforming `DWWG-<seq>` SKU.
+- min 530001, max 535483, 0 NULL, 0 non-conforming, 0 duplicates.
+
+## Hard rule honored
+Every DW SKU = `DWWG-<sequential>`. No manufacturer code embedded. No new
+ad-hoc SKU format. Existing series + number reused where present.
+
+## NOT done here (Steve-gated)
+- No write to shopify_products or live Shopify.
+- Legacy 1,068 Shopify SKUs (DWWG-AD10336 etc.) still on the OLD embedded
+ scheme — migrating those is a destructive prod write awaiting sign-off.
+- See reconcile.js for the legacy<->catalog dry-run reconciliation.
diff --git a/assign-skus.js b/assign-skus.js
new file mode 100644
index 0000000..66731e0
--- /dev/null
+++ b/assign-skus.js
@@ -0,0 +1,92 @@
+#!/usr/bin/env node
+// ============================================================================
+// Assign sequential DW SKUs to wolf_gordon_catalog rows that have none.
+//
+// HARD RULE (Steve 2026-06-19): every DW SKU is DW<XX>-<sequential number> for
+// that vendor series. WG series = DWWG-, range start 530000. NEVER embed the
+// manufacturer code. REUSE an existing dw_sku wherever one is set (never
+// renumber). Assign the next sequential number ONLY to rows with dw_sku IS NULL.
+//
+// This is a SAFE, LOCAL, REVERSIBLE write to wolf_gordon_catalog.dw_sku only.
+// Nothing is written to shopify_products or to live Shopify.
+//
+// Usage:
+// node assign-skus.js # dry run (prints plan, writes nothing)
+// node assign-skus.js --apply # performs the UPDATE inside a transaction
+// ============================================================================
+
+const { pool } = require('./scraper-utils');
+const APPLY = process.argv.includes('--apply');
+
+async function main() {
+ // Next sequential number = max existing DWWG-<n> + 1.
+ const { rows: mx } = await pool.query(`
+ SELECT COALESCE(MAX((regexp_replace(dw_sku,'^DWWG-',''))::int), 530000) AS maxseq
+ FROM wolf_gordon_catalog WHERE dw_sku ~ '^DWWG-[0-9]+$'
+ `);
+ let next = mx[0].maxseq + 1;
+
+ // Rows needing a SKU, deterministic order (id ASC) so re-runs are stable.
+ const { rows } = await pool.query(`
+ SELECT id, mfr_sku, pattern_name, color_name
+ FROM wolf_gordon_catalog
+ WHERE dw_sku IS NULL
+ ORDER BY id ASC
+ `);
+
+ console.log('='.repeat(70));
+ console.log(` WG SKU ASSIGNMENT (${APPLY ? 'APPLY' : 'DRY RUN'})`);
+ console.log('='.repeat(70));
+ console.log(` Rows needing a dw_sku: ${rows.length}`);
+ console.log(` Next sequential start: DWWG-${next}`);
+ if (rows.length === 0) { console.log(' Nothing to do.'); await pool.end(); return; }
+
+ const plan = rows.map(r => ({ id: r.id, dw_sku: `DWWG-${next++}`, r }));
+ console.log(` Range to assign: ${plan[0].dw_sku} .. ${plan[plan.length-1].dw_sku}`);
+ console.log('-'.repeat(70));
+ for (const p of plan.slice(0, 8)) {
+ console.log(` id=${String(p.id).padEnd(6)} ${p.dw_sku} <- ${p.r.mfr_sku} | ${p.r.pattern_name||'(no pat)'} | ${p.r.color_name||'(no color)'}`);
+ }
+ if (plan.length > 8) console.log(` ... and ${plan.length - 8} more`);
+ console.log('-'.repeat(70));
+
+ if (!APPLY) {
+ console.log(' DRY RUN — no writes. Re-run with --apply to commit.');
+ await pool.end();
+ return;
+ }
+
+ // Apply inside a transaction. Guard: each UPDATE only fires if dw_sku is
+ // still NULL (idempotent against a concurrent crawl insert).
+ const client = await pool.connect();
+ let updated = 0;
+ try {
+ await client.query('BEGIN');
+ for (const p of plan) {
+ const res = await client.query(
+ `UPDATE wolf_gordon_catalog SET dw_sku = $1, updated_at = NOW()
+ WHERE id = $2 AND dw_sku IS NULL`,
+ [p.dw_sku, p.id]
+ );
+ updated += res.rowCount;
+ }
+ // Safety: no duplicate DWWG sequential numbers exist after the write.
+ const dup = await client.query(`
+ SELECT dw_sku, count(*) c FROM wolf_gordon_catalog
+ WHERE dw_sku ~ '^DWWG-[0-9]+$' GROUP BY dw_sku HAVING count(*) > 1`);
+ if (dup.rows.length) {
+ throw new Error(`ABORT: duplicate dw_sku detected: ${dup.rows.map(d=>d.dw_sku).join(', ')}`);
+ }
+ await client.query('COMMIT');
+ console.log(` COMMITTED. Rows updated: ${updated}`);
+ } catch (e) {
+ await client.query('ROLLBACK');
+ console.error(' ROLLED BACK:', e.message);
+ process.exitCode = 1;
+ } finally {
+ client.release();
+ await pool.end();
+ }
+}
+
+main().catch(e => { console.error(e); process.exit(1); });
diff --git a/price-backfill.js b/price-backfill.js
new file mode 100644
index 0000000..8030c2c
--- /dev/null
+++ b/price-backfill.js
@@ -0,0 +1,75 @@
+#!/usr/bin/env node
+// ============================================================================
+// Wolf Gordon price backfill.
+// WG publishes a per-linear-yard price on each public product page
+// ("$31.25 per linear yard"). That price is the COST input to the DW formula:
+// price_trade = cost (scraped per-yard price)
+// price_retail = round(cost / 0.65 / 0.85, 2) [Steve, 2026-06-19]
+// Price is per-PATTERN (identical across colorways), so we fetch one
+// representative page per pattern and apply to every colorway of that pattern.
+// Local dw_unified only. $0 (public HTTP + local DB).
+// ============================================================================
+const https = require('https');
+const { pool, wait, closePool } = require('./scraper-utils');
+
+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';
+
+function fetchPage(url, redirects = 5) {
+ return new Promise((resolve, reject) => {
+ https.get(url, { timeout: 25000, headers: { 'User-Agent': UA, 'Accept': 'text/html' } }, (res) => {
+ if ((res.statusCode === 301 || res.statusCode === 302) && res.headers.location && redirects > 0) {
+ let loc = res.headers.location;
+ if (loc.startsWith('/')) loc = 'https://www.wolfgordon.com' + loc;
+ return fetchPage(loc, redirects - 1).then(resolve).catch(reject);
+ }
+ let d = ''; res.on('data', c => d += c); res.on('end', () => resolve({ html: d, status: res.statusCode }));
+ }).on('error', reject).on('timeout', function () { this.destroy(); reject(new Error('timeout')); });
+ });
+}
+
+function parseCost(html) {
+ const m = html.match(/\$\s*([0-9]+(?:\.[0-9]{2})?)\s*per\s*linear\s*yard/i);
+ return m ? parseFloat(m[1]) : null;
+}
+const retailFrom = cost => Math.round((cost / 0.65 / 0.85) * 100) / 100;
+
+async function main() {
+ // one representative product_url per pattern
+ const { rows } = await pool.query(`
+ SELECT pattern_name, MIN(product_url) AS url, COUNT(*) AS colorways
+ FROM wolf_gordon_catalog
+ WHERE pattern_name IS NOT NULL AND product_url IS NOT NULL
+ GROUP BY pattern_name ORDER BY pattern_name`);
+ console.log(`Patterns to price: ${rows.length}`);
+
+ let priced = 0, rowsUpdated = 0, noPrice = 0, errors = 0, i = 0;
+ const concurrency = 4;
+
+ async function worker() {
+ while (i < rows.length) {
+ const r = rows[i++];
+ try {
+ const { html, status } = await fetchPage(r.url);
+ if (status !== 200) { noPrice++; continue; }
+ const cost = parseCost(html);
+ if (!cost) { noPrice++; continue; }
+ const retail = retailFrom(cost);
+ const upd = await pool.query(
+ `UPDATE wolf_gordon_catalog SET price_trade=$1, price_retail=$2, updated_at=NOW()
+ WHERE pattern_name=$3`, [cost, retail, r.pattern_name]);
+ priced++; rowsUpdated += upd.rowCount;
+ if (priced <= 10) console.log(` ${r.pattern_name.padEnd(24)} cost $${cost} -> retail $${retail} (${upd.rowCount} colorways)`);
+ if (priced % 50 === 0) console.log(` ...${priced}/${rows.length} patterns priced (${rowsUpdated} rows)`);
+ await wait(250, 150);
+ } catch (e) { errors++; if (errors <= 5) console.error(` err ${r.pattern_name}: ${e.message}`); await wait(500, 300); }
+ }
+ }
+ await Promise.all(Array.from({ length: concurrency }, worker));
+
+ const cov = await pool.query(`SELECT count(*) total, count(*) FILTER(WHERE price_retail IS NOT NULL) withprice FROM wolf_gordon_catalog`);
+ console.log('\n=== PRICE BACKFILL DONE ===');
+ console.log(` patterns priced: ${priced} | no-price: ${noPrice} | errors: ${errors}`);
+ console.log(` catalog rows with price: ${cov.rows[0].withprice}/${cov.rows[0].total}`);
+ await closePool();
+}
+main().catch(e => { console.error('FATAL', e); closePool().then(() => process.exit(1)); });
← 9f1964c Add WG re-onboarding reconciliation (dry-run, normalized-SKU
·
back to Wolfgordon Crawl
·
Add unmatched-legacy analysis (174 weak-data, 614 discontinu 5bbaafe →