← back to Designer Wallcoverings
Bucket B Mind the Gap: roll-variant recovery script (PG-first, exact mfr match, budget-aware, draft-gated)
1e04847a874ff0ebd4018139db08c0bfd968a37f · 2026-06-22 14:28:51 -0700 · vp-dw-commerce
Files touched
A shopify/scripts/bucketB-mindthegap-roll-variants.js
Diff
commit 1e04847a874ff0ebd4018139db08c0bfd968a37f
Author: vp-dw-commerce <steve@designerwallcoverings.com>
Date: Mon Jun 22 14:28:51 2026 -0700
Bucket B Mind the Gap: roll-variant recovery script (PG-first, exact mfr match, budget-aware, draft-gated)
---
.../scripts/bucketB-mindthegap-roll-variants.js | 307 +++++++++++++++++++++
1 file changed, 307 insertions(+)
diff --git a/shopify/scripts/bucketB-mindthegap-roll-variants.js b/shopify/scripts/bucketB-mindthegap-roll-variants.js
new file mode 100644
index 00000000..965cf186
--- /dev/null
+++ b/shopify/scripts/bucketB-mindthegap-roll-variants.js
@@ -0,0 +1,307 @@
+#!/usr/bin/env node
+/**
+ * bucketB-mindthegap-roll-variants.js (2026-06-22) — owner: vp-dw-commerce
+ *
+ * Bucket B — Mind the Gap sample-only recovery (Steve APPROVED, feed-first,
+ * draft-gated, reversible). Mirror of the just-completed Rebel Walls run.
+ * For the 350 Mind the Gap sample-only products (Sample variant present, no
+ * sellable ROLL), add the missing ROLL variant `{DW_SKU}` alongside the
+ * existing `{DW_SKU}-Sample` ($4.25).
+ *
+ * PRICE SOURCE — recover from PG first (DTD verdict A, 2026-06-22, 3/3 unanimous):
+ * mindthegap_catalog priced 1040/1040. We bridge each worklist product to the
+ * catalog via shopify_products.mfr_sku (the live Shopify mfr_sku) joined CASE-
+ * INSENSITIVELY to mindthegap_catalog.mfr_sku — EXACT match only. 143/350 recover
+ * a verified per-product price this way. The other 207 carry OLD name-style mfr_skus
+ * (LAKAI, LOUVRE, ARISTOCRACY-TAUPE) while the live catalog keys on WP-codes
+ * (WP30217), so they have NO exact match. A normalized pattern_name match would
+ * "recover" 164 of them BUT pattern_name is one-to-many across colorways, so the
+ * price could attach to the WRONG colorway — a customer-facing pricing error on a
+ * live sellable variant. So (exactly as Rebel Walls rejected fuzzy slug matching)
+ * the 207 STAY sample-only + tag Needs-Price; a targeted re-scrape is staged
+ * separately. This script never invents/fuzzes a price.
+ *
+ * PRICING RULE (Mind the Gap is NOT Kravet-family):
+ * prefer our_price -> price_retail (both already our single-roll retail, used
+ * verbatim); else DW retail = price_trade / 0.65 / 0.85. Enrichment is produced
+ * upstream into data/bucketB-mindthegap-enriched.tsv by the PG join (see header
+ * of that file's generator). roll_price is taken verbatim from that TSV.
+ *
+ * ORDER OF OPS (PostgreSQL BEFORE Shopify):
+ * 1. read recovered roll price from data/bucketB-mindthegap-enriched.tsv (PG-sourced)
+ * 2. WRITE the roll price into dw_unified.shopify_products.retail_price for the
+ * matching shopify_id -- source of truth first
+ * 3. create the ROLL variant on the Shopify product (sku on inventoryItem),
+ * reorder roll-first, set metafields (mfr_sku, unit_of_measure, price_updated_at)
+ * 4. for unmatched (price_source=none): tag Needs-Price (+ Needs-Width/Needs-Image
+ * where the catalog gave us no width/image) — NO variant, status untouched
+ *
+ * SHARED VARIANT BUDGET (Steve, this run): respect the 1k/day cap via budget.cjs
+ * take('roll', n) up-front to SIZE the run, then refund() the unused grant. Harlequin
+ * + Coordonné run concurrently; if the day's roll headroom is below the priced backlog,
+ * we create only what the ledger grants and STAGE the remainder (resumable next day).
+ *
+ * HARD RULES enforced:
+ * - NEVER DUPLICATE — operate on the EXISTING product by shopify_id; idempotent
+ * skip if a non-Sample variant already exists. Never create a 2nd product.
+ * - PRICE GATE — no recovered price => row stays sample-only, tag Needs-Price.
+ * - PG dw_unified BEFORE Shopify.
+ * - NEVER flip status. These are sample-only; we never set ACTIVE (no img+width
+ * promotion here) and never demote. DRAFT stays DRAFT, ACTIVE stays ACTIVE.
+ * - never touch title / customer-facing vendor field / display_variant tag.
+ * - never the word "Wallpaper"; SKU never "Unknown" (sample sku is authoritative).
+ *
+ * Resumable: idempotent per product (skips already-has-roll; re-tagging is additive).
+ *
+ * node bucketB-mindthegap-roll-variants.js # DRY-RUN
+ * node bucketB-mindthegap-roll-variants.js --apply # LIVE
+ * node bucketB-mindthegap-roll-variants.js --apply --max-create 200
+ * node bucketB-mindthegap-roll-variants.js --apply --report data/bucketB/mtg-run.json
+ */
+const https = require('https');
+const fs = require('fs');
+const os = require('os');
+const path = require('path');
+const { execFileSync } = require('child_process');
+
+const args = process.argv.slice(2);
+const APPLY = args.includes('--apply');
+const NO_BUDGET = args.includes('--no-budget'); // escape hatch; default respects the shared cap
+const numArg = (flag, dflt) => { const i = args.indexOf(flag); return i >= 0 ? parseInt(args[i + 1], 10) : dflt; };
+const strArg = (flag, dflt) => { const i = args.indexOf(flag); return i >= 0 ? args[i + 1] : dflt; };
+const MAX_CREATE = numArg('--max-create', 900);
+const BATCH_SIZE = numArg('--batch-size', 100);
+const BATCH_GAP_MS = numArg('--batch-gap', 90000);
+const REPORT = strArg('--report', null);
+
+const TSV = path.join(os.homedir(), 'Projects/designerwallcoverings/today-viewer/data/bucketB-mindthegap-enriched.tsv');
+const TODAY = new Date().toISOString().slice(0, 10);
+
+// shared daily variant budget ledger (1k/day cap; Harlequin + Coordonné run concurrently)
+let budget = null;
+try { budget = require(path.join(os.homedir(), 'Projects/designerwallcoverings/scripts/variant-budget/budget.cjs')); }
+catch (e) { console.warn('[budget] ledger not loadable — proceeding under --max-create only:', e.message); }
+
+const env = fs.readFileSync(path.join(os.homedir(), 'Projects/secrets-manager/.env'), 'utf8');
+const TOKEN = (env.match(/^SHOPIFY_ADMIN_TOKEN=(.*)$/m) || [])[1].replace(/['"]/g, '').trim();
+const STORE = 'designer-laboratory-sandbox.myshopify.com', API = '2024-10';
+const sleep = ms => new Promise(r => setTimeout(r, ms));
+function round2(n) { return Math.round((n + Number.EPSILON) * 100) / 100; }
+
+// ---- dw_unified write (PG-first) ------------------------------------------
+function pgWriteRollPrice(rows) {
+ if (!rows.length) return '0';
+ const values = rows.map(r =>
+ `('gid://shopify/Product/${r.shopify_id}', ${r.price})`).join(',\n');
+ const sql = `
+BEGIN;
+CREATE TEMP TABLE _bB_price(gid text, roll_price numeric);
+INSERT INTO _bB_price(gid, roll_price) VALUES
+${values};
+UPDATE shopify_products sp
+ SET retail_price = b.roll_price
+ FROM _bB_price b
+ WHERE sp.shopify_id = b.gid;
+SELECT count(*) AS updated FROM _bB_price b JOIN shopify_products sp ON sp.shopify_id=b.gid;
+COMMIT;`;
+ const out = execFileSync('ssh', ['my-server',
+ `sudo -u postgres psql -d dw_unified -v ON_ERROR_STOP=1 -t -A`],
+ { input: sql, encoding: 'utf8', maxBuffer: 1 << 24 });
+ return out.trim();
+}
+
+// ---- Shopify GraphQL -------------------------------------------------------
+function gql(query, variables = {}) {
+ return new Promise((resolve, reject) => {
+ const body = JSON.stringify({ query, variables });
+ const req = https.request({ host: STORE, path: `/admin/api/${API}/graphql.json`, method: 'POST',
+ headers: { 'Content-Type': 'application/json', 'X-Shopify-Access-Token': TOKEN, 'Content-Length': Buffer.byteLength(body) } },
+ r => { let d = ''; r.on('data', c => d += c); r.on('end', () => { try { resolve(JSON.parse(d)); } catch (e) { reject(d); } }); });
+ req.on('error', reject); req.write(body); req.end();
+ });
+}
+async function gqlR(q, v, tries = 4) {
+ for (let i = 0; i < tries; i++) {
+ const r = await gql(q, v);
+ if (r && !r.errors) return r;
+ const throttled = (r.errors || []).some(e => /throttl/i.test(JSON.stringify(e)));
+ if (!throttled) return r;
+ await sleep(1500 * (i + 1));
+ }
+ return gql(q, v);
+}
+
+const Q_PRODUCT = `query($id:ID!){product(id:$id){id title status tags
+ options{name values}
+ variants(first:30){nodes{id title sku selectedOptions{name value} inventoryItem{id}}}}}`;
+const M_VAR_CREATE = `mutation($pid:ID!,$variants:[ProductVariantsBulkInput!]!){
+ productVariantsBulkCreate(productId:$pid,variants:$variants){
+ productVariants{id sku} userErrors{field message}}}`;
+const M_REORDER = `mutation($pid:ID!,$positions:[ProductVariantPositionInput!]!){
+ productVariantsBulkReorder(productId:$pid,positions:$positions){userErrors{field message}}}`;
+const M_MF = `mutation($mf:[MetafieldsSetInput!]!){metafieldsSet(metafields:$mf){userErrors{field message}}}`;
+const M_TAGS = `mutation($id:ID!,$tags:[String!]!){tagsAdd(id:$id,tags:$tags){userErrors{field message}}}`;
+
+// ---- worklist (enriched) ---------------------------------------------------
+// cols: shopify_id base_sku sample_sku mfr_sku roll_price price_source cat_width cat_width_in cat_image cat_discontinued
+function loadWork() {
+ const lines = fs.readFileSync(TSV, 'utf8').replace(/\n$/, '').split('\n');
+ return lines.map(l => {
+ const c = l.split('\t');
+ return {
+ shopify_id: (c[0] || '').trim(), base_sku: (c[1] || '').trim(),
+ sample_sku: (c[2] || '').trim(), mfr_sku: (c[3] || '').trim(),
+ roll_price: c[4] ? parseFloat(c[4]) : null, price_source: (c[5] || '').trim(),
+ cat_width: (c[6] || '').trim(), cat_width_in: (c[7] || '').trim(),
+ cat_image: (c[8] || '').trim(), cat_disc: (c[9] || '').trim() === 't',
+ };
+ }).filter(r => r.shopify_id && /^\d+$/.test(r.shopify_id));
+}
+
+(async () => {
+ const rows = loadWork();
+ const priced = rows.filter(r => r.price_source.startsWith('catalog') && r.roll_price > 0);
+ const unpriced = rows.filter(r => !(r.price_source.startsWith('catalog') && r.roll_price > 0));
+ console.log(`bucketB-mindthegap — ${rows.length} products (${priced.length} priced, ${unpriced.length} no-price) — ${APPLY ? '🔴 LIVE' : 'DRY-RUN'}`);
+ console.log(`max-create=${MAX_CREATE} batch-size=${BATCH_SIZE} batch-gap=${BATCH_GAP_MS}ms\n`);
+
+ const report = { vendor: 'Mind the Gap', started: new Date().toISOString(), apply: APPLY, scrapedFresh: 0,
+ created: [], skippedAlreadyRoll: [], taggedNeedsPrice: [], failed: [],
+ byPriceSource: {}, budget: null, geminiCostUsd: 0 };
+
+ // ---- SHARED BUDGET: size this run against the 1k/day variant cap ----
+ // Each priced product creates ONE roll variant. take('roll', N) up-front, refund the
+ // unused grant at the end. If the ledger grants fewer than we need, stage the remainder.
+ let rollCap = priced.length; // how many roll variants we want to create
+ let grant = priced.length; // how many the ledger lets us create this run
+ if (budget && !NO_BUDGET) {
+ try {
+ // DRY-RUN must NOT consume the shared ledger — use the non-mutating remaining().
+ // LIVE actually take()s the grant (and refunds the unused tail at the end).
+ grant = APPLY ? budget.take('roll', priced.length)
+ : Math.min(priced.length, budget.remaining('roll'));
+ report.budget = { requested: priced.length, granted: grant, consumed: APPLY };
+ console.log(`[budget] ${APPLY ? "take" : "remaining"}('roll', ${priced.length}) -> ${grant} (shared 1k/day cap; Harlequin + Coordonné concurrent)`);
+ if (grant < priced.length) console.log(`[budget] ⚠ ${priced.length - grant} priced rows STAGED for next run (cap reached today)`);
+ if (grant === 0) console.log(`[budget] 0 granted — today's roll slice is exhausted; will only tag the unpriced rows. Re-run tomorrow to drain the priced backlog.`);
+ } catch (e) {
+ console.warn('[budget] budget call failed (fail-open) — proceeding under --max-create only:', e.message);
+ grant = priced.length;
+ }
+ }
+ rollCap = Math.min(grant, MAX_CREATE);
+
+ // ---- PG-FIRST: write recovered roll prices to dw_unified before any Shopify write ----
+ // Only write the prices for products we will actually build this run (the granted slice).
+ const buildable = priced.slice(0, rollCap);
+ if (APPLY && buildable.length) {
+ try {
+ const res = pgWriteRollPrice(buildable.map(w => ({ shopify_id: w.shopify_id, price: round2(w.roll_price) })));
+ console.log(`PG (dw_unified) retail_price written — psql updated count: ${res}\n`);
+ report.pgUpdated = res;
+ } catch (e) {
+ console.error('PG write FAILED — aborting before Shopify:', e.message || e);
+ if (budget && !NO_BUDGET) { try { budget.refund('roll', grant); } catch (_) {} } // give the grant back
+ process.exit(2);
+ }
+ } else if (buildable.length) {
+ console.log(`(dry-run) would write ${buildable.length} retail_price rows to dw_unified first\n`);
+ }
+
+ // ---- Shopify: add ROLL variant to each priced product (within the granted slice) ----
+ let createdThisRun = 0, inBatch = 0;
+ for (let idx = 0; idx < priced.length; idx++) {
+ const w = priced[idx];
+ if (createdThisRun >= rollCap) { console.log(`\n⛔ hit run cap (${rollCap}: min of budget grant ${grant} / --max-create ${MAX_CREATE}); ${priced.length - createdThisRun} priced rows deferred (resumable)`); break; }
+ const pid = `gid://shopify/Product/${w.shopify_id}`;
+ const pr = await gqlR(Q_PRODUCT, { id: pid });
+ const p = pr.data && pr.data.product;
+ if (!p) { console.log(`❌ ${w.shopify_id} ${w.base_sku} not found`); report.failed.push({ ...w, reason: 'not-found' }); continue; }
+
+ const vs = p.variants.nodes;
+ const sample = vs.find(v => /sample/i.test(v.title) || /-sample$/i.test(v.sku || ''));
+ const roll = vs.find(v => v !== sample);
+ if (roll) { console.log(`⏭ ${p.title.slice(0,46)} already has roll (${roll.sku})`); report.skippedAlreadyRoll.push({ ...w, existingRollSku: roll.sku }); continue; }
+
+ // SKU: prefer existing sample sku minus -Sample (authoritative), else base_sku. Never Unknown.
+ let skuBase = (sample && sample.sku) ? sample.sku.replace(/-sample$/i, '') : w.base_sku;
+ if (!skuBase) { console.log(`❌ ${w.shopify_id} — no derivable SKU — SKIP`); report.failed.push({ ...w, reason: 'no-sku' }); continue; }
+ const price = round2(w.roll_price);
+
+ console.log(`${APPLY ? '➕' : '·'} ${p.title.slice(0,44).padEnd(44)} [${p.status}] roll $${price} (${w.price_source}) sku ${skuBase}`);
+ report.byPriceSource[w.price_source] = (report.byPriceSource[w.price_source] || 0) + 1;
+ if (!APPLY) { report.created.push({ ...w, skuBase, price, dryRun: true }); createdThisRun++; continue; }
+
+ const cr = await gqlR(M_VAR_CREATE, { pid, variants: [{
+ optionValues: [{ optionName: (p.options[0] && p.options[0].name) || 'Title', name: 'Single Roll' }],
+ price: String(price), taxable: true, inventoryPolicy: 'CONTINUE',
+ inventoryItem: { sku: skuBase, tracked: false },
+ }] });
+ const ue = cr.data?.productVariantsBulkCreate?.userErrors || [];
+ if (ue.length) { console.log(` ❌ create: ${JSON.stringify(ue)}`); report.failed.push({ ...w, skuBase, reason: 'create-error', errors: ue }); continue; }
+ const newId = cr.data.productVariantsBulkCreate.productVariants[0].id;
+
+ if (sample) {
+ const ro = await gqlR(M_REORDER, { pid, positions: [{ id: newId, position: 1 }, { id: sample.id, position: 2 }] });
+ const re = ro.data?.productVariantsBulkReorder?.userErrors || [];
+ if (re.length) console.log(` ⚠ reorder: ${JSON.stringify(re)}`);
+ }
+
+ const mf = await gqlR(M_MF, { mf: [
+ { ownerId: pid, namespace: 'custom', key: 'manufacturer_sku', type: 'single_line_text_field', value: w.mfr_sku || skuBase },
+ { ownerId: pid, namespace: 'dwc', key: 'manufacturer_sku', type: 'single_line_text_field', value: w.mfr_sku || skuBase },
+ { ownerId: pid, namespace: 'custom', key: 'price_updated_at', type: 'date', value: TODAY },
+ { ownerId: pid, namespace: 'global', key: 'unit_of_measure', type: 'single_line_text_field', value: 'Priced Per Single Roll' },
+ ] });
+ const me = mf.data?.metafieldsSet?.userErrors || [];
+ if (me.length) console.log(` ⚠ metafields: ${JSON.stringify(me)}`);
+
+ report.created.push({ ...w, skuBase, price, variantId: newId });
+ createdThisRun++; inBatch++;
+ if (inBatch >= BATCH_SIZE) { console.log(` …batch of ${inBatch} done — sleeping ${BATCH_GAP_MS/1000}s (≥90s bulk gap)`); await sleep(BATCH_GAP_MS); inBatch = 0; }
+ else await sleep(200);
+ }
+
+ // ---- BUDGET REFUND: return the unused grant (granted − actually created) ----
+ if (budget && !NO_BUDGET && report.budget && report.budget.consumed) {
+ const actuallyCreated = report.created.filter(c => !c.dryRun).length;
+ const unused = Math.max(0, grant - actuallyCreated);
+ if (unused > 0) {
+ try { const back = budget.refund('roll', unused); report.budget.refunded = back;
+ console.log(`\n[budget] refund('roll', ${unused}) -> returned ${back} unused grant to the shared ledger`); }
+ catch (e) { console.warn('[budget] refund failed (fail-closed):', e.message); }
+ }
+ report.budget.created = actuallyCreated;
+ }
+
+ // ---- unpriced: tag Needs-Price (+ Needs-Width/Needs-Image where catalog lacked them) ----
+ console.log(`\n--- tagging ${unpriced.length} unpriced rows (Needs-Price) ---`);
+ for (const w of unpriced) {
+ const tags = ['Needs-Price'];
+ if (!w.cat_width) tags.push('Needs-Width');
+ if (!w.cat_image) tags.push('Needs-Image');
+ if (!APPLY) { report.taggedNeedsPrice.push({ ...w, tags, dryRun: true }); continue; }
+ const pid = `gid://shopify/Product/${w.shopify_id}`;
+ const tr = await gqlR(M_TAGS, { id: pid, tags });
+ const te = tr.data?.tagsAdd?.userErrors || [];
+ if (te.length) { console.log(` ⚠ tag ${w.base_sku}: ${JSON.stringify(te)}`); report.failed.push({ ...w, reason: 'tag-error', errors: te }); continue; }
+ report.taggedNeedsPrice.push({ ...w, tags });
+ await sleep(80);
+ }
+
+ report.finished = new Date().toISOString();
+ report.totals = {
+ rollVariantsCreated: report.created.filter(c => !c.dryRun).length,
+ rollVariantsWouldCreate: report.created.filter(c => c.dryRun).length,
+ stayedSampleOnly_NeedsPrice: report.taggedNeedsPrice.length,
+ skippedAlreadyHadRoll: report.skippedAlreadyRoll.length,
+ pricedBacklogStaged: Math.max(0, priced.length - report.created.length),
+ scrapedFresh: report.scrapedFresh,
+ failed: report.failed.length,
+ };
+ console.log(`\n=== SUMMARY ===`);
+ console.log(JSON.stringify(report.totals, null, 2));
+ console.log('byPriceSource:', JSON.stringify(report.byPriceSource));
+ if (report.budget) console.log('budget:', JSON.stringify(report.budget));
+ if (REPORT) { fs.mkdirSync(path.dirname(REPORT), { recursive: true }); fs.writeFileSync(REPORT, JSON.stringify(report, null, 2)); console.log(`report -> ${REPORT}`); }
+})();
← 99e77163 Tier B packaging answered via variant-title scan: per-vendor
·
back to Designer Wallcoverings
·
auto-save: 2026-06-22T14:36:53 (2 files) — shopify/scripts/d 627df739 →