Sanderson: harvest product width from public retail API ($0, no auth) into sanderson_catalog.width

fa05ca1b86680f6fa12015bcf2ed5438c5ee74a0 · 2026-08-10 10:08:51 -0700 · Steve Abrams

The trade portal carries price but not width (original TK-00058 Tier-1 blocker).
Width lives on www.sanderson.design product records; one call per SKU via
/api/n/find?type=product&filter={sku} → sdb_usable_width_inches/sdb_useable_width,
stored in the DW sibling convention '20.47 in  (52 cm)'.

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

Files touched

A scripts/harvest_width.js

Diff

commit fa05ca1b86680f6fa12015bcf2ed5438c5ee74a0
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Mon Aug 10 10:08:51 2026 -0700

    Sanderson: harvest product width from public retail API ($0, no auth) into sanderson_catalog.width
    
    The trade portal carries price but not width (original TK-00058 Tier-1 blocker).
    Width lives on www.sanderson.design product records; one call per SKU via
    /api/n/find?type=product&filter={sku} → sdb_usable_width_inches/sdb_useable_width,
    stored in the DW sibling convention '20.47 in  (52 cm)'.
    
    Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
 scripts/harvest_width.js | 57 ++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 57 insertions(+)

diff --git a/scripts/harvest_width.js b/scripts/harvest_width.js
new file mode 100644
index 0000000..85f523a
--- /dev/null
+++ b/scripts/harvest_width.js
@@ -0,0 +1,57 @@
+#!/usr/bin/env node
+// Harvest Sanderson product WIDTH from the PUBLIC retail API ($0, no auth) into
+// sanderson_catalog.width. The trade portal carries price but NOT width; width
+// lives on www.sanderson.design's product record (sdb_usable_width_inches / sdb_useable_width).
+//
+// Endpoint (proven via network capture): one call per SKU, keyed by sku —
+//   GET /api/n/find?type=product&verbosity=3&filter={"sku":"<SKU>"}&limit=1
+//   → .catalog[] entry where .sku===SKU has sdb_usable_width_inches ("20.47 in")
+//     and sdb_useable_width ("52 cm").
+// Stored in the DW sibling convention: "20.47 in  (52 cm)".
+//
+// Usage: node scripts/harvest_width.js            (all null-width rows)
+//        node scripts/harvest_width.js --all      (re-harvest every row)
+const https = require('https');
+const { execFileSync } = require('child_process');
+
+const ALL = process.argv.includes('--all');
+const sleep = ms => new Promise(r => setTimeout(r, ms));
+const UA = 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36';
+
+function get(url) {
+  return new Promise((resolve) => {
+    https.get(url, { headers: { 'User-Agent': UA, 'Accept': 'application/json' } }, res => {
+      let d = ''; res.on('data', c => d += c); res.on('end', () => { try { resolve(JSON.parse(d)); } catch { resolve(null); } });
+    }).on('error', () => resolve(null));
+  });
+}
+const psql = sql => execFileSync('psql', ['-h', '/tmp', '-d', 'dw_unified', '-tAc', sql], { encoding: 'utf8' }).trim();
+const psqlFile = f => execFileSync('psql', ['-h', '/tmp', '-d', 'dw_unified', '-q', '-v', 'ON_ERROR_STOP=1', '-f', f], { encoding: 'utf8' });
+const q = s => "'" + String(s).replace(/'/g, "''") + "'";
+const numFrom = s => { const m = String(s == null ? '' : s).match(/-?\d+(\.\d+)?/); return m ? m[0] : null; };
+
+(async () => {
+  const where = ALL ? "mfr_sku is not null" : "mfr_sku is not null and width is null";
+  const skus = psql(`select mfr_sku from sanderson_catalog where ${where} order by mfr_sku`).split('\n').filter(Boolean);
+  console.log(`[width] ${skus.length} SKUs to harvest (${ALL ? 'all' : 'null-width only'})`);
+  const updates = []; let hit = 0, miss = 0;
+  for (let i = 0; i < skus.length; i++) {
+    const sku = skus[i];
+    const filter = encodeURIComponent(JSON.stringify({ sku }));
+    const url = `https://www.sanderson.design/api/n/find?type=product&verbosity=3&filter=${filter}&limit=1`;
+    const d = await get(url);
+    const cat = (d && Array.isArray(d.catalog)) ? d.catalog.find(c => c.sku === sku) || d.catalog[0] : null;
+    const inch = cat && numFrom(cat.sdb_usable_width_inches);
+    const cm = cat && numFrom(cat.sdb_useable_width);
+    if (inch) { const w = cm ? `${inch} in  (${cm} cm)` : `${inch} in`; updates.push(`update sanderson_catalog set width=${q(w)} where mfr_sku=${q(sku)};`); hit++; }
+    else miss++;
+    if ((i + 1) % 25 === 0 || i === skus.length - 1) console.log(`[width] ${i + 1}/${skus.length}  hit=${hit} miss=${miss}`);
+    await sleep(150);
+  }
+  if (updates.length) {
+    const fs = require('fs'), tmp = '/tmp/sanderson_width_updates.sql';
+    fs.writeFileSync(tmp, 'begin;\n' + updates.join('\n') + '\ncommit;\n');
+    psqlFile(tmp);
+  }
+  console.log(`[width] DONE: wrote ${hit} widths, ${miss} misses. Fill now: ${psql("select count(width)||'/'||count(*) from sanderson_catalog")}`);
+})();

← c895b56 TK-10400 Cody-gate fixes: reauth->openclaw, tab-race, freshn · back to Sanderson Onboard · chore: harvest_width.js refactor (hoist fs require) + v1.0.1 e9c105a →