← back to Letsbegin
rw-sku-backfill.js
202 lines
#!/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); }
// HARD GUARD: sandbox store only (matches rw-cost-write.js / rw-recrawl-backfill.js).
if (STORE !== 'designer-laboratory-sandbox.myshopify.com') {
console.error(`FATAL: refusing to write to non-sandbox store "${STORE}"`); 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); });