Osborne width backfill tool — found catalog overlap exhausted: 896 live gaps have no osborne_catalog source (need re-scrape, not metafield push)

066739934f53f5f7ab4deae58c2eec9c670f9195 · 2026-06-20 11:39:33 -0700 · Steve
Files touched

A shopify/scripts/cadence/backfill-osborne-width.js
Diff

commit 066739934f53f5f7ab4deae58c2eec9c670f9195
Author: Steve <steve@designerwallcoverings.com>
Date:   Sat Jun 20 11:39:33 2026 -0700

    Osborne width backfill tool — found catalog overlap exhausted: 896 live gaps have no osborne_catalog source (need re-scrape, not metafield push)
---
 shopify/scripts/cadence/backfill-osborne-width.js | 54 +++++++++++++++++++++++
 1 file changed, 54 insertions(+)

diff --git a/shopify/scripts/cadence/backfill-osborne-width.js b/shopify/scripts/cadence/backfill-osborne-width.js
new file mode 100644
index 00000000..872612c2
--- /dev/null
+++ b/shopify/scripts/cadence/backfill-osborne-width.js
@@ -0,0 +1,54 @@
+'use strict';
+// Backfill width (+ mfr_sku) metafields on ALL Osborne ACTIVE products missing them,
+// sourced from osborne_catalog.width_inches via mfr_sku. Skips material (data quality).
+// DRY-RUN default; --commit writes. Idempotent (helper skips existing). $0 (Shopify API).
+const fs = require('fs'), path = require('path'), { execFileSync } = require('child_process');
+for (const p of [path.join(__dirname, '..', '..', '.env'), path.join(__dirname, '..', '.env')])
+  if (fs.existsSync(p)) for (const l of fs.readFileSync(p, 'utf8').split('\n')) { const m = l.match(/^\s*([A-Z0-9_]+)\s*=\s*(.*)\s*$/); if (m && !process.env[m[1]]) process.env[m[1]] = m[2].replace(/^["']|["']$/g, ''); }
+const { pushSpecMetafields } = require('../lib/push-spec-metafields');
+const STORE = 'designer-laboratory-sandbox.myshopify.com', TOKEN = process.env.SHOPIFY_ADMIN_TOKEN, VER = '2024-10';
+const COMMIT = process.argv.includes('--commit');
+const sleep = ms => new Promise(r => setTimeout(r, ms));
+async function gqlRetry(q, v) { for (let a = 0; a < 4; a++) { const r = await fetch(`https://${STORE}/admin/api/${VER}/graphql.json`, { method: 'POST', headers: { 'X-Shopify-Access-Token': TOKEN, 'Content-Type': 'application/json' }, body: JSON.stringify({ query: q, variables: v }) }); if (r.status === 429) { await sleep(2000 * (a + 1)); continue; } return { json: await r.json() }; } return { json: {} }; }
+const widthMf = mfs => (mfs || []).some(m => /width/i.test(m.key) && m.value && m.value.trim());
+
+(async () => {
+  // 1. preload osborne_catalog mfr_sku -> normalized width (one query)
+  const cat = new Map();
+  execFileSync('psql', ['-d', 'dw_unified', '-F\t', '-tAc', `SELECT upper(mfr_sku), width_inches::text FROM osborne_catalog WHERE width_inches IS NOT NULL AND mfr_sku IS NOT NULL`], { encoding: 'utf8' })
+    .trim().split('\n').forEach(l => { const [m, w] = l.split('\t'); if (m && w) cat.set(m, `${parseFloat(w).toFixed(2)} Inches`); });
+  console.log(`catalog width-map: ${cat.size} Osborne SKUs\n`);
+
+  // 2. paginate all Osborne actives
+  const Q = `query($cur:String){ products(first:200, query:"status:active vendor:'Osborne & Little'", after:$cur){ pageInfo{hasNextPage endCursor} nodes{ id variants(first:4){nodes{sku}} metafields(first:50){nodes{namespace key value}} } } }`;
+  let cur = null, total = 0, missing = 0, linkable = 0, pushed = 0, noCat = 0, errors = 0;
+  for (let page = 0; page < 30; page++) {
+    const pg = (await gqlRetry(Q, { cur })).json.data.products;
+    for (const n of pg.nodes) {
+      total++;
+      if (widthMf(n.metafields.nodes)) continue;
+      missing++;
+      // derive mfr from any variant sku; strip -Sample then the -OSB vendor suffix
+      const skus = n.variants.nodes.map(v => v.sku || '').filter(Boolean);
+      const baseSku = skus.find(s => /-OSB$/i.test(s)) || skus[0] || '';
+      const mfr = baseSku.replace(/-sample$/i, '').replace(/-OSB$/i, '').toUpperCase();
+      const width = cat.get(mfr);
+      if (!width) { noCat++; continue; }
+      linkable++;
+      const row = { width, material: '', roll_length: '', pattern_repeat: '', mfr_sku: mfr, dw_sku: '' };
+      const res = await pushSpecMetafields(n.id, row, n.metafields.nodes, gqlRetry, COMMIT);
+      if (res.errors) { errors++; console.log(`  ✗ ${mfr}: ${JSON.stringify(res.errors).slice(0, 80)}`); }
+      else if (COMMIT && res.pushed) { pushed++; if (pushed % 25 === 0) console.log(`  …pushed ${pushed} (latest ${mfr} = ${width})`); }
+      if (COMMIT) await sleep(120);
+    }
+    if (!pg.pageInfo.hasNextPage) break; cur = pg.pageInfo.endCursor;
+  }
+  console.log(`\n=== Osborne width backfill ${COMMIT ? '(COMMITTED)' : '(DRY-RUN)'} ===`);
+  console.log(`  active scanned    : ${total}`);
+  console.log(`  missing width     : ${missing}`);
+  console.log(`  linkable (catalog): ${linkable}`);
+  console.log(`  ${COMMIT ? 'PUSHED' : 'would push'}        : ${COMMIT ? pushed : linkable}`);
+  console.log(`  no catalog source : ${noCat}  (need re-scrape/enrich — not backfillable from catalog)`);
+  if (errors) console.log(`  errors            : ${errors}`);
+  console.log(`  cost              : $0 (Shopify API only)`);
+})().catch(e => { console.error('ERR:', e.message); process.exit(1); });
← 87886372 Live: pushed width+mfr_sku metafields to 3 Osborne actives ( · back to Designer Wallcoverings · cadence: backfill+normalize widths to DW canonical 'N Inches 84e9e2e1 →