backfill-malibu-mfr: recover real SKU+mfr+vendor for 61 held Malibu orphans from wallquest_catalog (61/61 written)

51edfffd122a229d69b2a4682e5651fafd3eca9b · 2026-07-06 20:32:52 -0700 · Steve
Files touched

A scripts/backfill-malibu-mfr.js
Diff

commit 51edfffd122a229d69b2a4682e5651fafd3eca9b
Author: Steve <steve@designerwallcoverings.com>
Date:   Mon Jul 6 20:32:52 2026 -0700

    backfill-malibu-mfr: recover real SKU+mfr+vendor for 61 held Malibu orphans from wallquest_catalog (61/61 written)
---
 scripts/backfill-malibu-mfr.js | 89 ++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 89 insertions(+)

diff --git a/scripts/backfill-malibu-mfr.js b/scripts/backfill-malibu-mfr.js
new file mode 100644
index 00000000..3fa78a8d
--- /dev/null
+++ b/scripts/backfill-malibu-mfr.js
@@ -0,0 +1,89 @@
+#!/usr/bin/env node
+/**
+ * backfill-malibu-mfr.js — Phase 1 of the go-live-gate remediation.
+ *
+ * The 61 held Malibu Wallpaper (=WallQuest) products have their REAL identity in
+ * dw_unified.wallquest_catalog (dw_sku like DWQW-59424, mfr_sku like 2231100) — it
+ * just never got written to Shopify. This recovers it (free/local) and writes back:
+ *   1. the variant SKU  = dw_sku            (fills the blank SKU)
+ *   2. metafield private_label.real_mfr_sku = mfr_sku (the real WallQuest code)
+ * plus mirrors both into dw_unified.shopify_products. After this the products satisfy
+ * Steve's go-live rule (real vendor + real mfr number) and are republish-eligible.
+ *
+ * Input:  ~/Projects/dw-golive-gate/malibu-backfill.csv (shopify_id,num,dw_sku,mfr_sku,handle)
+ * Ledger: ~/Projects/dw-golive-gate/backfill-results.jsonl
+ *
+ * Usage:  node scripts/backfill-malibu-mfr.js --dry [--limit N]
+ *         node scripts/backfill-malibu-mfr.js --limit 1
+ *         node scripts/backfill-malibu-mfr.js
+ */
+const fs = require('fs');
+const os = require('os');
+const path = require('path');
+const https = require('https');
+const { Client } = require('pg');
+require('dotenv').config({ path: path.join(__dirname, '..', '.env') });
+
+const SHOPIFY_DOMAIN = 'designer-laboratory-sandbox.myshopify.com';
+const TOKEN = process.env.SHOPIFY_ADMIN_TOKEN;
+const API = '2024-10';
+const CSV = path.join(os.homedir(), 'Projects/dw-golive-gate/malibu-backfill.csv');
+const LEDGER = path.join(os.homedir(), 'Projects/dw-golive-gate/backfill-results.jsonl');
+const DB = process.env.DATABASE_URL || 'postgresql://dw_admin@127.0.0.1:5432/dw_unified';
+
+const DRY = process.argv.includes('--dry');
+const li = process.argv.indexOf('--limit');
+const LIMIT = li > -1 ? parseInt(process.argv[li + 1], 10) : Infinity;
+if (!TOKEN) { console.error('FATAL: SHOPIFY_ADMIN_TOKEN missing'); process.exit(1); }
+
+const sleep = (ms) => new Promise(r => setTimeout(r, ms));
+function req(method, endpoint, body) {
+  return new Promise((resolve, reject) => {
+    const data = body ? JSON.stringify(body) : null;
+    const r = https.request({ hostname: SHOPIFY_DOMAIN, path: `/admin/api/${API}${endpoint}`, method,
+      headers: { 'X-Shopify-Access-Token': TOKEN, 'Content-Type': 'application/json', ...(data ? { 'Content-Length': Buffer.byteLength(data) } : {}) } },
+      (res) => { let b = ''; res.on('data', c => b += c); res.on('end', () => { let j; try { j = JSON.parse(b); } catch { j = b; } resolve({ status: res.statusCode, data: j }); }); });
+    r.on('error', reject); if (data) r.write(data); r.end();
+  });
+}
+async function call(m, e, b) { for (let a = 1; a <= 5; a++) { const r = await req(m, e, b); if (r.status !== 429) return r; await sleep(1000 * a); } return { status: 429 }; }
+
+function rows() {
+  return fs.readFileSync(CSV, 'utf8').trim().split('\n').slice(1).map(l => {
+    const c = l.split(','); return { gid: c[0], id: c[1], dw_sku: c[2], mfr_sku: c[3], handle: c[4] };
+  }).filter(r => r.id && r.mfr_sku);
+}
+
+(async () => {
+  const pg = new Client({ connectionString: DB }); await pg.connect();
+  const targets = rows().slice(0, LIMIT);
+  console.log(`${DRY ? '🔎 DRY' : '✍️  BACKFILL'} — ${targets.length} Malibu product(s) | token …${TOKEN.slice(-4)}`);
+  let ok = 0, fail = 0;
+  for (let i = 0; i < targets.length; i++) {
+    const t = targets[i]; const tag = `[${i + 1}/${targets.length}] ${t.id} ${t.dw_sku} mfr=${t.mfr_sku}`;
+    // fetch first variant id (to set SKU) + existing metafields check
+    const p = await call('GET', `/products/${t.id}.json?fields=id,variants`);
+    const variant = p.data?.product?.variants?.[0];
+    if (!variant) { console.log(`  ⚠️  ${tag} — no variant/404`); fail++; await sleep(300); continue; }
+    if (DRY) { console.log(`  🔎 ${tag} — would set variant ${variant.id} sku=${t.dw_sku} + metafield private_label.real_mfr_sku=${t.mfr_sku}`); await sleep(250); continue; }
+    // 1) variant SKU
+    const vr = await call('PUT', `/variants/${variant.id}.json`, { variant: { id: variant.id, sku: t.dw_sku } });
+    // 2) metafield real_mfr_sku
+    const mr = await call('POST', `/products/${t.id}/metafields.json`, { metafield: { namespace: 'private_label', key: 'real_mfr_sku', value: String(t.mfr_sku), type: 'single_line_text_field' } });
+    const good = vr.status === 200 && (mr.status === 201 || mr.status === 200);
+    if (good) {
+      ok++;
+      // mirror into dw_unified
+      await pg.query(`UPDATE shopify_products SET sku=$2, mfr_sku=$3 WHERE shopify_id=$1`, [t.gid, t.dw_sku, t.mfr_sku]);
+      console.log(`  ✅ ${tag} — SKU + real_mfr_sku written`);
+      fs.appendFileSync(LEDGER, JSON.stringify({ ts: new Date().toISOString(), id: t.id, dw_sku: t.dw_sku, mfr_sku: t.mfr_sku, action: 'backfilled' }) + '\n');
+    } else {
+      fail++;
+      console.log(`  ❌ ${tag} — variant:${vr.status} metafield:${mr.status} ${JSON.stringify(mr.data).slice(0,120)}`);
+      fs.appendFileSync(LEDGER, JSON.stringify({ ts: new Date().toISOString(), id: t.id, action: 'failed', v: vr.status, m: mr.status }) + '\n');
+    }
+    await sleep(500);
+  }
+  await pg.end();
+  console.log(`\n── ${DRY ? 'DRY' : 'BACKFILL'} complete ── ok=${ok} fail=${fail}  ($0 — Shopify Admin API free, local PG mirror free)`);
+})();
← c6296c08 hold-orphan-438: reversible unpublish->draft for go-live-gat · back to Designer Wallcoverings · thibaut-refresh: reference vendor refresh wiring for the rec df78838a →