← back to Designer Wallcoverings
Add schu-reconcile-pids: backfill shopify_product_id from Shopify (bulk import write-back missed ~2558)
0548262de92dcf9193b1d787231f97601dfebda4 · 2026-06-11 12:40:51 -0700 · SteveStudio2
Bulk DRAFT import created 2976 products but the per-row pid write-back stopped after ~418
(psql-spawn exhaustion), leaving rows linked-on-Shopify-but-not-in-catalog → re-run dup risk.
Reconciler scans Shopify vendor:Schumacher, matches by variant SKU=dw_sku, backfills the pid.
Defeated short-term by Shopify search-index lag after bulk create; re-run once settled.
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
Files touched
A shopify/scripts/schu-reconcile-pids.js
Diff
commit 0548262de92dcf9193b1d787231f97601dfebda4
Author: SteveStudio2 <stevestudio2@SteveStudio2s-Mac-Studio.local>
Date: Thu Jun 11 12:40:51 2026 -0700
Add schu-reconcile-pids: backfill shopify_product_id from Shopify (bulk import write-back missed ~2558)
Bulk DRAFT import created 2976 products but the per-row pid write-back stopped after ~418
(psql-spawn exhaustion), leaving rows linked-on-Shopify-but-not-in-catalog → re-run dup risk.
Reconciler scans Shopify vendor:Schumacher, matches by variant SKU=dw_sku, backfills the pid.
Defeated short-term by Shopify search-index lag after bulk create; re-run once settled.
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
---
shopify/scripts/schu-reconcile-pids.js | 75 ++++++++++++++++++++++++++++++++++
1 file changed, 75 insertions(+)
diff --git a/shopify/scripts/schu-reconcile-pids.js b/shopify/scripts/schu-reconcile-pids.js
new file mode 100644
index 00000000..ab233dd5
--- /dev/null
+++ b/shopify/scripts/schu-reconcile-pids.js
@@ -0,0 +1,75 @@
+#!/usr/bin/env node
+/**
+ * Reconcile schumacher_catalog.shopify_product_id against Shopify (source of truth).
+ *
+ * The bulk DRAFT import reported CREATED 2976 but only ~1,482 rows recorded a
+ * shopify_product_id — the per-row write-back silently missed ~half, which would let a
+ * re-run DUPLICATE them. This queries Shopify for every Schumacher product, matches each
+ * by its variant SKU (= dw_sku) back to the catalog, and backfills shopify_product_id.
+ * Read-only on Shopify; only writes shopify_product_id in dw_unified. No product changes.
+ *
+ * node schu-reconcile-pids.js # report (dry)
+ * node schu-reconcile-pids.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(sql) { return execFileSync('psql', ['-At', '-F', '\t', '-d', 'dw_unified', '-c', sql], { 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; } await sleep(300); return r; } return {}; }
+
+(async () => {
+ if (!TOKEN) { console.error('no token'); process.exit(1); }
+ // known catalog dw_skus (for safe matching)
+ const known = new Set(psql(`SELECT dw_sku FROM schumacher_catalog WHERE dw_sku IS NOT NULL AND dw_sku<>'';`).split('\n').filter(Boolean));
+ console.log(`catalog dw_skus: ${known.size}`);
+
+ const Q = `query($after:String){products(first:100,query:"vendor:Schumacher",after:$after){pageInfo{hasNextPage endCursor} edges{node{id status variants(first:5){edges{node{sku}}}}}}}`;
+ let after = null, pages = 0, seen = 0;
+ const pidBySku = new Map();
+ do {
+ const r = await gqlRetry(Q, { after });
+ const p = r?.data?.products; if (!p) { console.error('fetch fail', JSON.stringify(r).slice(0, 200)); break; }
+ for (const e of p.edges) {
+ seen++;
+ const pid = String(e.node.id).replace(/.*\//, '');
+ for (const v of e.node.variants.edges) {
+ const sku = (v.node.sku || '').replace(/-Sample$/i, '');
+ if (sku && known.has(sku) && !pidBySku.has(sku)) pidBySku.set(sku, pid);
+ }
+ }
+ after = p.pageInfo.hasNextPage ? p.pageInfo.endCursor : null; pages++;
+ process.stdout.write(`\r scanned ${seen} Schumacher products (page ${pages}), matched ${pidBySku.size}…`);
+ } while (after && pages < 80);
+ console.log(`\nShopify Schumacher products seen: ${seen} | matched to catalog dw_sku: ${pidBySku.size}`);
+
+ // which matched skus currently lack a pid in the catalog
+ const missing = psql(`SELECT dw_sku FROM schumacher_catalog WHERE coalesce(shopify_product_id::text,'')='' AND dw_sku IS NOT NULL AND dw_sku<>'';`).split('\n').filter(Boolean);
+ const toFix = missing.filter(s => pidBySku.has(s));
+ console.log(`catalog rows missing pid that EXIST on Shopify (would be backfilled): ${toFix.length}`);
+
+ if (!COMMIT) { console.log('DRY — re-run with --commit to backfill shopify_product_id.'); return; }
+ let done = 0;
+ for (let i = 0; i < toFix.length; i += 500) {
+ const stmts = toFix.slice(i, i + 500).map(s => `UPDATE schumacher_catalog SET shopify_product_id=${q(pidBySku.get(s))} WHERE dw_sku=${q(s)};`).join(' ');
+ psql('BEGIN; ' + stmts + ' COMMIT;');
+ done += Math.min(500, toFix.length - i);
+ process.stdout.write(`\r backfilled ${done}/${toFix.length}…`);
+ }
+ console.log(`\nDONE: backfilled ${done} shopify_product_id links. Re-run net-new check — duplicates now prevented.`);
+})();
← b893c46c Load Graham & Brown cost from GDrive list (629 SKUs) + wire
·
back to Designer Wallcoverings
·
Sweep: fix flushInv fallback (set-per-item before activate) c3d80f3d →