Osborne width: API sweep populated osborne_catalog 787->14107 width rows; backfill reads fresh Kamatera catalog (gaps already closed by cadence sync, backfill now no-op)

f8f483e69c7529002afb501cb03dd36faad58a2f · 2026-06-21 14:26:12 -0700 · Steve
Files touched

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

commit f8f483e69c7529002afb501cb03dd36faad58a2f
Author: Steve <steve@designerwallcoverings.com>
Date:   Sun Jun 21 14:26:12 2026 -0700

    Osborne width: API sweep populated osborne_catalog 787->14107 width rows; backfill reads fresh Kamatera catalog (gaps already closed by cadence sync, backfill now no-op)
---
 shopify/scripts/cadence/backfill-osborne-width.js |  9 +--
 shopify/scripts/cadence/osborne-width-sweep.js    | 68 +++++++++++++++++++++++
 2 files changed, 73 insertions(+), 4 deletions(-)

diff --git a/shopify/scripts/cadence/backfill-osborne-width.js b/shopify/scripts/cadence/backfill-osborne-width.js
index 872612c2..5df90553 100644
--- a/shopify/scripts/cadence/backfill-osborne-width.js
+++ b/shopify/scripts/cadence/backfill-osborne-width.js
@@ -13,11 +13,12 @@ async function gqlRetry(q, v) { for (let a = 0; a < 4; a++) { const r = await fe
 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)
+  // 1. preload osborne_catalog mfr_sku -> normalized width from the FRESH Kamatera
+  //    canonical (the sweep wrote there; the local mirror lags). One ssh 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`);
+  execFileSync('ssh', ['my-server', `sudo -u postgres psql -d dw_unified -tAF'|' -c "select upper(mfr_sku), width_inches from osborne_catalog where width_inches is not null and mfr_sku is not null"`], { encoding: 'utf8', maxBuffer: 64 * 1024 * 1024 })
+    .trim().split('\n').forEach(l => { const [m, w] = l.split('|'); if (m && w) cat.set(m, `${parseFloat(w).toFixed(2)} Inches`); });
+  console.log(`catalog width-map (fresh from Kamatera): ${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}} } } }`;
diff --git a/shopify/scripts/cadence/osborne-width-sweep.js b/shopify/scripts/cadence/osborne-width-sweep.js
new file mode 100644
index 00000000..1a123b91
--- /dev/null
+++ b/shopify/scripts/cadence/osborne-width-sweep.js
@@ -0,0 +1,68 @@
+'use strict';
+// Sweep the Osborne trade API (now that login works) and upsert WIDTH into
+// osborne_catalog for every current product — closing the 896 width gaps at source.
+// Runs ON Kamatera (reads /root/DW-Agents/dw-price-stock/.env). Width comes straight
+// from item.material.property.width. Idempotent upsert on mfr_sku.
+require('dotenv').config({ path: '/root/DW-Agents/dw-price-stock/.env' });
+const { pool } = require('/root/DW-Agents/vendor-scrapers/scraper-utils');
+const API = 'https://api.osborneandlittle.com/v1/';
+const U = process.env.OSBORNE_USERNAME, P = process.env.OSBORNE_PASSWORD;
+const ORIGIN = 'https://tradenew.osborneandlittle.com';
+const MAXID = Number(process.env.MAXID||1600), CONC = 8;
+let TOKEN;
+
+async function login() {
+  const r = await fetch(API + 'User/login', { method: 'POST', headers: { 'Content-Type': 'application/json', 'Accept': 'application/json', 'Origin': ORIGIN }, body: JSON.stringify({ username: U, password: P, rememberme: false, countrycode: 'US', countryname: 'United States' }) });
+  if (!r.ok) throw new Error('login ' + r.status); return (await r.json()).token;
+}
+async function getCol(cid, page) {
+  const r = await fetch(API + `product/getbycollectionid?collectionid=${cid}&itemsperpage=200&currentpage=${page}`, { headers: { 'Authorization': 'Token ' + TOKEN, 'Origin': ORIGIN, 'Accept': 'application/json' } });
+  if (r.status === 401) { TOKEN = await login(); return getCol(cid, page); }
+  if (!r.ok) return null; return r.json();
+}
+// parse inches from "139.5cm (54 3/4")" / "10m x 52cm (11yds x 20 ins)" / "20.5 ins"
+function widthInches(s) {
+  if (!s) return null;
+  let m = s.match(/(\d+)\s+(\d+)\/(\d+)\s*(?:ins|inches|")/i); if (m) return (parseInt(m[1]) + parseInt(m[2]) / parseInt(m[3])).toFixed(2);
+  m = s.match(/(\d+(?:\.\d+)?)\s*(?:ins\b|inches|")/i); if (m) return parseFloat(m[1]).toFixed(2);
+  m = s.match(/(\d+(?:\.\d+)?)\s*cm/i); if (m) return (parseFloat(m[1]) / 2.54).toFixed(2);
+  return null;
+}
+
+(async () => {
+  TOKEN = await login(); console.log('login OK');
+  let seen = 0, withWidth = 0, upserted = 0, errors = 0;
+  for (let start = 1; start <= MAXID; start += CONC) {
+    const ids = []; for (let i = start; i < start + CONC && i <= MAXID; i++) ids.push(i);
+    await Promise.all(ids.map(async cid => {
+      try {
+        const d = await getCol(cid, 1); if (!d || !d.items || !d.items.length) return;
+        const items = [...d.items];
+        const tot = d.totalItems || d.items.length;
+        if (tot > 200) for (let pg = 2; pg <= Math.ceil(tot / 200); pg++) { const dp = await getCol(cid, pg); if (dp && dp.items) items.push(...dp.items); }
+        for (const it of items) {
+          seen++;
+          const code = it.code || it.productcode || it.sku; if (!code) continue;
+          const prop = (it.material && it.material.property) || {};
+          const w = (prop.width || '').trim(); if (!w) continue;
+          withWidth++;
+          const wi = widthInches(w);
+          try {
+            await pool.query(
+              `INSERT INTO osborne_catalog (mfr_sku, width, width_inches, repeat_v, last_scraped, updated_at)
+               VALUES ($1,$2,$3,$4,now(),now())
+               ON CONFLICT (mfr_sku) DO UPDATE SET
+                 width=EXCLUDED.width, width_inches=COALESCE(EXCLUDED.width_inches, osborne_catalog.width_inches),
+                 repeat_v=COALESCE(NULLIF(EXCLUDED.repeat_v,''), osborne_catalog.repeat_v),
+                 last_scraped=now(), updated_at=now()`,
+              [String(code).toUpperCase(), w, wi, (prop.repeats || '').trim() || null]);
+            upserted++;
+          } catch (e) { errors++; if (errors <= 3) console.log('  upsert err', code, e.message.slice(0, 60)); }
+        }
+      } catch (e) { /* skip dead collection ids */ }
+    }));
+    if (start % 200 === 1) console.log(`  …collections ${start}/${MAXID} · seen ${seen} · width ${withWidth} · upserted ${upserted}`);
+  }
+  console.log(`\nDONE: items seen ${seen} · with width ${withWidth} · upserted to osborne_catalog ${upserted} · errors ${errors}`);
+  await pool.end();
+})().catch(e => { console.error('SWEEP ERR:', e.message); process.exit(1); });
← fcce8e0e Zoffany colorway verification (DTD 2026-06-21): authoritativ · back to Designer Wallcoverings · sync-shopify-products: fetch variant price + descriptionHtml f4947979 →