← back to Letsbegin
Backfill SKUs (mfr_sku/dw_sku/variant sku + rw url metafield) on RW cohort via pattern+color rematch
3c0c7faed2f2f870c5d5a2f554b052231ebe5ab8 · 2026-06-11 14:55:52 -0700 · Steve Abrams
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
Files touched
Diff
commit 3c0c7faed2f2f870c5d5a2f554b052231ebe5ab8
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Thu Jun 11 14:55:52 2026 -0700
Backfill SKUs (mfr_sku/dw_sku/variant sku + rw url metafield) on RW cohort via pattern+color rematch
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
---
rw-sku-backfill.js | 196 +++++++++++++++++++++++++++++++++++++++++++++++++++++
1 file changed, 196 insertions(+)
diff --git a/rw-sku-backfill.js b/rw-sku-backfill.js
new file mode 100644
index 0000000..38d071a
--- /dev/null
+++ b/rw-sku-backfill.js
@@ -0,0 +1,196 @@
+#!/usr/bin/env node
+/**
+ * rw-sku-backfill.js — recover + backfill real SKUs on the SKU-less Rebel Walls
+ * cohort (1,000 products created in the last 20 days on the SANDBOX store).
+ *
+ * The match mapping was computed in SQL and persisted to PG table
+ * `rw_sku_match_20d` (shopify_id, title, mfr_sku R#, dw_sku DWRW-#, product_url,
+ * match_kind, shopify_done, shopify_error). This script:
+ * 1. (once, --db) set-based UPDATE of shopify_products from the mapping:
+ * mfr_sku, dw_sku, sku=dw_sku, vendor_prefix='DWRW'.
+ * 2. (per matched product) Live Shopify:
+ * - set the MAIN (non-sample) variant sku = dw_sku
+ * - set the -Sample variant sku = dw_sku + '-Sample' (keeps convention)
+ * - metafields custom.mfr_sku = R# (single_line_text_field)
+ * custom.rw_product_url = product_url (single_line_text_field)
+ * Idempotent (skips a product whose main variant sku already == dw_sku AND
+ * both metafields already present). Rate-limited ~2 req/s w/ enrich-write
+ * backoff. Marks rw_sku_match_20d.shopify_done=true on success.
+ *
+ * Does NOT touch color/title/tag/cost metafields, unmatched products, or non-RW.
+ *
+ * Usage:
+ * node rw-sku-backfill.js --db # run the set-based DB update only
+ * node rw-sku-backfill.js --shopify --limit 3 # write first 3 matched to Shopify (validation)
+ * node rw-sku-backfill.js --shopify # write all matched to Shopify
+ * node rw-sku-backfill.js --shopify --only gid://shopify/Product/123 # one product
+ *
+ * Env: SHOPIFY_ADMIN_TOKEN. PG* optional (defaults user stevestudio2, db dw_unified).
+ */
+const https = require('https');
+const { Client } = require('pg');
+
+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';
+if (!TOKEN) { console.error('FATAL: SHOPIFY_ADMIN_TOKEN env var required'); process.exit(1); }
+
+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); }
+
+function sleep(ms) { return 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 } } }';
+
+async function pgConnect() {
+ const pg = new Client({ user: process.env.PGUSER || 'stevestudio2', database: process.env.PGDATABASE || 'dw_unified', host: process.env.PGHOST || '/tmp' });
+ await pg.connect();
+ return 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_sku_match_20d 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_sku_match_20d 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;
+
+ // ---- variant sku update (only if needed) ----
+ 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);
+ }
+
+ // ---- metafields (only if needed) ----
+ 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);
+ }
+
+ // ---- verify ----
+ 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, match_kind, shopify_done FROM rw_sku_match_20d 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] ${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_sku_match_20d 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_sku_match_20d 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); // ~2 req/s overall with inter-op sleeps
+ }
+ 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); });
← 62605f8 Write C3 per-m2 cost ($44.50) metafield on 1000 Rebel Walls
·
back to Letsbegin
·
Add 3-material-options priced metafield (Standard/P&S/Commer df2dbbf →