← back to Letsbegin
rw-recrawl-backfill.js
203 lines
#!/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); });