Add by-sku reconcile; confirmed missing Schumacher SKUs genuinely absent (import throttle no-op'd ~2558, not dup risk)

70d046b04962cb6358a78e80d582a93944a28fd1 · 2026-06-11 13:40:03 -0700 · SteveStudio2

Files touched

A shopify/scripts/schu-reconcile-bysku.js

Diff

commit 70d046b04962cb6358a78e80d582a93944a28fd1
Author: SteveStudio2 <stevestudio2@SteveStudio2s-Mac-Studio.local>
Date:   Thu Jun 11 13:40:03 2026 -0700

    Add by-sku reconcile; confirmed missing Schumacher SKUs genuinely absent (import throttle no-op'd ~2558, not dup risk)
---
 shopify/scripts/schu-reconcile-bysku.js | 50 +++++++++++++++++++++++++++++++++
 1 file changed, 50 insertions(+)

diff --git a/shopify/scripts/schu-reconcile-bysku.js b/shopify/scripts/schu-reconcile-bysku.js
new file mode 100644
index 00000000..99501682
--- /dev/null
+++ b/shopify/scripts/schu-reconcile-bysku.js
@@ -0,0 +1,50 @@
+#!/usr/bin/env node
+/**
+ * Deterministic Schumacher pid reconcile — by SKU.
+ *
+ * The vendor:Schumacher scan is stuck at ~2,200 (search-index lag/cap), but direct
+ * sku: lookups index immediately. For every catalog row missing a shopify_product_id,
+ * query Shopify `sku:<dw_sku>` and backfill the product id. Read-only on Shopify; only
+ * writes shopify_product_id in dw_unified.
+ *   node schu-reconcile-bysku.js           # dry (first 20)
+ *   node schu-reconcile-bysku.js --commit
+ */
+const https = require('https'); const fs = require('fs'); const os = require('os');
+const { execFileSync } = require('child_process');
+const COMMIT = process.argv.includes('--commit');
+const TOKEN = (fs.readFileSync(os.homedir() + '/Projects/secrets-manager/.env', 'utf8').match(/^SHOPIFY_ADMIN_TOKEN=(.*)$/m) || [])[1].replace(/['"]/g, '').trim();
+const STORE = 'designer-laboratory-sandbox.myshopify.com', API = '2024-10';
+const sleep = ms => new Promise(r => setTimeout(r, ms));
+function psql(s) { return execFileSync('psql', ['-At', '-F', '\t', '-d', 'dw_unified', '-c', s], { encoding: 'utf8', maxBuffer: 1 << 28 }).trim(); }
+const q = s => "'" + String(s).replace(/'/g, "''") + "'";
+
+function gql(query, variables) {
+  return new Promise(res => { const data = JSON.stringify({ query, variables });
+    const req = https.request({ host: STORE, path: `/admin/api/${API}/graphql.json`, method: 'POST', headers: { 'X-Shopify-Access-Token': TOKEN, 'Content-Type': 'application/json', 'Content-Length': Buffer.byteLength(data) } },
+      r => { let d = ''; r.on('data', c => d += c); r.on('end', () => { try { res(JSON.parse(d)); } catch { res({}); } }); });
+    req.on('error', () => res({})); req.write(data); req.end(); });
+}
+async function gqlRetry(query, v) { for (let a = 0; a < 6; a++) { const r = await gql(query, v); if (r.errors && JSON.stringify(r.errors).includes('THROTTLED')) { await sleep(2500 * (a + 1)); continue; } return r; } return {}; }
+
+(async () => {
+  if (!TOKEN) { console.error('no token'); process.exit(1); }
+  const missing = psql(`SELECT dw_sku FROM schumacher_catalog WHERE coalesce(shopify_product_id::text,'')='' AND cost>0 AND NOT coalesce(excluded,false) AND dw_sku IS NOT NULL AND dw_sku<>'' ORDER BY dw_sku;`).split('\n').filter(Boolean);
+  console.log(`catalog rows missing pid: ${missing.length} | mode: ${COMMIT ? 'COMMIT' : 'DRY (first 20)'}`);
+  const Q = `query($q:String!){products(first:1,query:$q){edges{node{id}}}}`;
+  let found = 0, nf = 0, done = 0;
+  const list = COMMIT ? missing : missing.slice(0, 20);
+  for (const sku of list) {
+    const r = await gqlRetry(Q, { q: `sku:${sku}` });
+    const edge = r?.data?.products?.edges?.[0];
+    if (edge) {
+      const pid = String(edge.node.id).replace(/.*\//, '');
+      found++;
+      if (COMMIT) psql(`UPDATE schumacher_catalog SET shopify_product_id=${q(pid)} WHERE dw_sku=${q(sku)};`);
+      else if (found <= 8) console.log(`  ${sku} → ${pid}`);
+    } else { nf++; }
+    done++;
+    if (COMMIT && done % 100 === 0) process.stdout.write(`\r  checked ${done}/${list.length} | linked ${found} | not-found ${nf}…`);
+    await sleep(120);
+  }
+  console.log(`\n${COMMIT ? 'DONE' : 'DRY'}: found-on-shopify ${found}, not-found ${nf} of ${list.length} checked.`);
+})();

← a5291a3f Add daily vendor sweep (com.steve.cadence-daily-sweep) — Kra · back to Designer Wallcoverings · Fix importer throttle bug (require real pid for success) + b fd28444b →