← back to Letsbegin
Add rw-recrawl-backfill.js: set-based DB update + idempotent sandbox Shopify variant-sku/metafield backfill for the 94 re-crawled RW murals (94/94, 0 failures)
1c651fe326446de5ee1870993ed4e9fbdfe8d05f · 2026-06-11 15:17:20 -0700 · Steve Abrams
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
Files touched
Diff
commit 1c651fe326446de5ee1870993ed4e9fbdfe8d05f
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Thu Jun 11 15:17:20 2026 -0700
Add rw-recrawl-backfill.js: set-based DB update + idempotent sandbox Shopify variant-sku/metafield backfill for the 94 re-crawled RW murals (94/94, 0 failures)
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
---
rw-recrawl-backfill.js | 202 +++++++++++++++++++++++++++++++++++++++++++++++++
1 file changed, 202 insertions(+)
diff --git a/rw-recrawl-backfill.js b/rw-recrawl-backfill.js
new file mode 100644
index 0000000..4e6243f
--- /dev/null
+++ b/rw-recrawl-backfill.js
@@ -0,0 +1,202 @@
+#!/usr/bin/env node
+/**
+ * rw-recrawl-backfill.js — backfill the 94 re-crawled Rebel Walls mural products.
+ *
+ * Reads the mapping built by rw-catalog-recrawl.js (PG table rw_recrawl_match_94:
+ * shopify_id, title, mfr_sku R#, dw_sku DWRW-#, product_url). Same backfill
+ * pattern as Letsbegin/rw-sku-backfill.js:
+ * --db set-based UPDATE of shopify_products (mfr_sku, dw_sku, sku=dw_sku, vendor_prefix)
+ * --shopify per-product live writes on the SANDBOX store:
+ * - main (non-sample) variant sku = dw_sku
+ * - -Sample variant sku = dw_sku + '-Sample'
+ * - metafields custom.mfr_sku, custom.rw_product_url
+ * Idempotent (skips if already set). Rate-limited ~2 req/s.
+ *
+ * SANDBOX ONLY. No Shopify product PUSH, no cost/price writes.
+ *
+ * Usage:
+ * node rw-recrawl-backfill.js --db
+ * node rw-recrawl-backfill.js --shopify --limit 3
+ * node rw-recrawl-backfill.js --shopify
+ *
+ * Env: SHOPIFY_ADMIN_TOKEN required for --shopify (auto-loaded from
+ * ~/Projects/secrets-manager/.env if not set). PG* optional.
+ */
+const https = require('https');
+const fs = require('fs');
+const path = require('path');
+const { Client } = require('pg');
+
+// Load SHOPIFY_ADMIN_TOKEN / SHOPIFY_STORE from secrets-manager .env if not in env.
+(function loadSecrets() {
+ if (process.env.SHOPIFY_ADMIN_TOKEN) return;
+ const envPath = path.join(process.env.HOME, 'Projects', 'secrets-manager', '.env');
+ try {
+ for (const line of fs.readFileSync(envPath, 'utf8').split('\n')) {
+ const m = line.match(/^\s*(SHOPIFY_ADMIN_TOKEN|SHOPIFY_STORE)\s*=\s*(.+?)\s*$/);
+ if (m && !process.env[m[1]]) process.env[m[1]] = m[2].replace(/^['"]|['"]$/g, '');
+ }
+ } catch { /* ignore */ }
+})();
+
+const STORE = process.env.SHOPIFY_STORE || 'designer-laboratory-sandbox.myshopify.com';
+const TOKEN = process.env.SHOPIFY_ADMIN_TOKEN || process.env.SHOPIFY_PRODUCT_TOKEN;
+const API = '/admin/api/2024-10/graphql.json';
+
+const args = process.argv.slice(2);
+const DO_DB = args.includes('--db');
+const DO_SHOPIFY = args.includes('--shopify');
+const limIdx = args.indexOf('--limit');
+const LIMIT = limIdx >= 0 ? parseInt(args[limIdx + 1], 10) : null;
+const onlyIdx = args.indexOf('--only');
+const ONLY = onlyIdx >= 0 ? args[onlyIdx + 1] : null;
+if (!DO_DB && !DO_SHOPIFY) { console.error('Usage: --db | --shopify [--limit N] [--only gid]'); process.exit(2); }
+if (DO_SHOPIFY && !TOKEN) { console.error('FATAL: SHOPIFY_ADMIN_TOKEN required for --shopify'); process.exit(1); }
+if (DO_SHOPIFY && !/sandbox/i.test(STORE)) { console.error(`FATAL: refusing to write to non-sandbox store ${STORE}`); process.exit(1); }
+
+const sleep = (ms) => new Promise((r) => setTimeout(r, ms));
+
+function gqlRaw(body) {
+ return new Promise((resolve, reject) => {
+ const data = JSON.stringify(body);
+ const req = https.request({ hostname: STORE, path: API, method: 'POST',
+ headers: { 'X-Shopify-Access-Token': TOKEN, 'Content-Type': 'application/json', 'Content-Length': Buffer.byteLength(data) },
+ }, (res) => { let c = ''; res.on('data', (d) => (c += d)); res.on('end', () => { try { resolve(JSON.parse(c)); } catch { resolve({ error: c.slice(0, 300) }); } }); });
+ req.on('error', reject);
+ req.setTimeout(60000, () => { req.destroy(); reject(new Error('timeout')); });
+ req.write(data); req.end();
+ });
+}
+async function gql(body, retries = 5) {
+ for (let attempt = 1; attempt <= retries; attempt++) {
+ try {
+ const result = await gqlRaw(body);
+ const throttled = result?.errors?.some((e) => /Throttled/i.test(e.message || ''));
+ const lowBudget = (result?.extensions?.cost?.throttleStatus?.currentlyAvailable || 9999) < 200;
+ if (throttled) { await sleep(3000); continue; }
+ if (lowBudget) await sleep(1500);
+ return result;
+ } catch (e) { if (attempt < retries) { await sleep(attempt * 2000); continue; } throw e; }
+ }
+}
+
+const PRODUCT_QUERY = (id) => ({ query: `{
+ product(id:"${id}") {
+ id title
+ variants(first:10){ edges{ node{ id sku } } }
+ metafields(namespace:"custom", first:50){ edges{ node{ key value } } }
+ }
+}` });
+const MF_MUTATION = 'mutation metafieldsSet($m: [MetafieldsSetInput!]!) { metafieldsSet(metafields: $m) { metafields { key } userErrors { message field } } }';
+const VARIANTS_BULK = 'mutation bulk($pid: ID!, $v: [ProductVariantsBulkInput!]!) { productVariantsBulkUpdate(productId: $pid, variants: $v) { productVariants { id sku } userErrors { message field } } }';
+
+function pgConnect() {
+ const pg = new Client({ user: process.env.PGUSER || 'stevestudio2', database: process.env.PGDATABASE || 'dw_unified', host: process.env.PGHOST || '/tmp' });
+ return pg.connect().then(() => pg);
+}
+
+async function runDbUpdate() {
+ const pg = await pgConnect();
+ try {
+ const res = await pg.query(`
+ UPDATE shopify_products sp
+ SET mfr_sku = m.mfr_sku,
+ dw_sku = m.dw_sku,
+ sku = m.dw_sku,
+ vendor_prefix = 'DWRW'
+ FROM rw_recrawl_match_94 m
+ WHERE sp.shopify_id = m.shopify_id`);
+ console.log(`[db] shopify_products rows updated: ${res.rowCount}`);
+ const chk = await pg.query(`SELECT count(*) c FROM shopify_products sp JOIN rw_recrawl_match_94 m ON sp.shopify_id=m.shopify_id WHERE sp.dw_sku=m.dw_sku AND sp.sku=m.dw_sku AND sp.mfr_sku=m.mfr_sku AND sp.vendor_prefix='DWRW'`);
+ console.log(`[db] verified rows (dw_sku/sku/mfr_sku/prefix all set): ${chk.rows[0].c}`);
+ } finally { await pg.end(); }
+}
+
+async function backfillOne(row) {
+ const PID = row.shopify_id;
+ const fr = await gql(PRODUCT_QUERY(PID));
+ const p = fr?.data?.product;
+ if (!p) return { ok: false, err: `product not found (${JSON.stringify(fr).slice(0, 150)})` };
+
+ const vEdges = p.variants?.edges || [];
+ const mainV = vEdges.find((e) => !/-sample$/i.test(e.node.sku || ''))?.node || vEdges[0]?.node;
+ const sampleV = vEdges.find((e) => /-sample$/i.test(e.node.sku || ''))?.node || null;
+ if (!mainV) return { ok: false, err: 'no variants' };
+
+ const mf = {}; for (const e of (p.metafields?.edges || [])) mf[e.node.key] = e.node.value;
+ const skuOk = (mainV.sku || '') === row.dw_sku;
+ const mfrOk = mf['mfr_sku'] === row.mfr_sku;
+ const urlOk = (mf['rw_product_url'] || '') === (row.product_url || '');
+
+ let didVariant = false, didMf = false;
+
+ if (!skuOk || (sampleV && (sampleV.sku || '') !== `${row.dw_sku}-Sample`)) {
+ const vIn = [{ id: mainV.id, inventoryItem: { sku: row.dw_sku } }];
+ if (sampleV) vIn.push({ id: sampleV.id, inventoryItem: { sku: `${row.dw_sku}-Sample` } });
+ const r = await gql({ query: VARIANTS_BULK, variables: { pid: PID, v: vIn } });
+ const errs = r?.data?.productVariantsBulkUpdate?.userErrors || [];
+ if (errs.length) return { ok: false, err: `variant: ${errs.map((e) => e.message).join('; ')}` };
+ didVariant = true;
+ await sleep(500);
+ }
+
+ if (!mfrOk || !urlOk) {
+ const m = [{ ownerId: PID, namespace: 'custom', key: 'mfr_sku', value: row.mfr_sku, type: 'single_line_text_field' }];
+ if (row.product_url) m.push({ ownerId: PID, namespace: 'custom', key: 'rw_product_url', value: row.product_url, type: 'single_line_text_field' });
+ const r = await gql({ query: MF_MUTATION, variables: { m } });
+ const errs = r?.data?.metafieldsSet?.userErrors || [];
+ if (errs.length) return { ok: false, err: `metafield: ${errs.map((e) => e.message).join('; ')}` };
+ didMf = true;
+ await sleep(500);
+ }
+
+ await sleep(250);
+ const vr = await gql(PRODUCT_QUERY(PID));
+ const vp = vr?.data?.product;
+ const vMain = (vp?.variants?.edges || []).find((e) => !/-sample$/i.test(e.node.sku || ''))?.node;
+ const vmf = {}; for (const e of (vp?.metafields?.edges || [])) vmf[e.node.key] = e.node.value;
+ const ok = vMain && vMain.sku === row.dw_sku && vmf['mfr_sku'] === row.mfr_sku && (!row.product_url || vmf['rw_product_url'] === row.product_url);
+ return { ok, didVariant, didMf, verifiedSku: vMain?.sku, verifiedMfr: vmf['mfr_sku'], err: ok ? null : 'verify mismatch' };
+}
+
+async function runShopify() {
+ const pg = await pgConnect();
+ let rows;
+ try {
+ let q = `SELECT shopify_id, title, mfr_sku, dw_sku, product_url, shopify_done FROM rw_recrawl_match_94 WHERE coalesce(shopify_done,false)=false`;
+ const params = [];
+ if (ONLY) { q += ` AND shopify_id = $1`; params.push(ONLY); }
+ q += ` ORDER BY shopify_id`;
+ if (LIMIT) q += ` LIMIT ${LIMIT}`;
+ rows = (await pg.query(q, params)).rows;
+ } finally { await pg.end(); }
+
+ console.log(`[shopify] store=${STORE} :: ${rows.length} matched products to process${LIMIT ? ` (limit ${LIMIT})` : ''}${ONLY ? ` (only ${ONLY})` : ''}`);
+ let done = 0, failed = 0;
+ const failures = [];
+ for (let i = 0; i < rows.length; i++) {
+ const row = rows[i];
+ let res;
+ try { res = await backfillOne(row); } catch (e) { res = { ok: false, err: e.message }; }
+ if (res.ok) {
+ done++;
+ const pg2 = await pgConnect();
+ try { await pg2.query(`UPDATE rw_recrawl_match_94 SET shopify_done=true, shopify_error=null WHERE shopify_id=$1`, [row.shopify_id]); } finally { await pg2.end(); }
+ console.log(` [${i + 1}/${rows.length}] OK ${row.shopify_id} "${row.title}" -> sku=${res.verifiedSku} mfr=${res.verifiedMfr}${res.didVariant ? '' : ' (sku already set)'}${res.didMf ? '' : ' (mf already set)'}`);
+ } else {
+ failed++;
+ failures.push({ id: row.shopify_id, title: row.title, err: res.err });
+ const pg2 = await pgConnect();
+ try { await pg2.query(`UPDATE rw_recrawl_match_94 SET shopify_error=$2 WHERE shopify_id=$1`, [row.shopify_id, String(res.err).slice(0, 400)]); } finally { await pg2.end(); }
+ console.log(` [${i + 1}/${rows.length}] FAIL ${row.shopify_id} "${row.title}" :: ${res.err}`);
+ }
+ await sleep(250);
+ }
+ console.log(`\n[shopify] done=${done} failed=${failed}`);
+ if (failures.length) console.log('FAILURES:\n' + failures.map((f) => ` ${f.id} "${f.title}" :: ${f.err}`).join('\n'));
+}
+
+(async () => {
+ if (DO_DB) await runDbUpdate();
+ if (DO_SHOPIFY) await runShopify();
+})().catch((e) => { console.error(`FATAL: ${e.message}\n${e.stack}`); process.exit(1); });
← 7f50750 Add rw-catalog-recrawl.js: feed-first RW re-crawl into vendo
·
back to Letsbegin
·
codehealth: add sandbox guards to 3 env-default Shopify writ 19a2714 →