← back to Designer Wallcoverings
Bucket-B PR + Hollywood sample-only roll-variant scripts (DTD-A)
7df3fcf812987b05dd4b430772c72f3a6e2ebafe · 2026-06-22 19:26:30 -0700 · Steve
Source-join recovery for the two largest sample-only buckets. Glob-matched
bucketB-*-roll-variants.js so bucketB-land-all.sh auto-drains them; PG-first,
draft-gated, budget-safe (take/refund 'roll'), idempotent, leak-safe (never
writes Command54/Momentum to any customer-facing field).
Honest source reality (verified against live dw_unified 2026-06-22):
- PR: command54_catalog has 0 priced rows; phillipe_romano_catalog prices only
60 of 5841 worklist -> 60 staged, 5781 Needs-Price.
- HW: cohort does not bridge to Momentum (pattern/sku joins yield 0-2); the 12
'priced' hollywood_catalog rows were $4.25 sample-price contamination rejected
by the sample-tier floor -> 0 genuine, 1799 Needs-Price.
Files touched
A shopify/scripts/bucketB-hollywood-roll-variants.jsA shopify/scripts/bucketB-phillipromano-roll-variants.js
Diff
commit 7df3fcf812987b05dd4b430772c72f3a6e2ebafe
Author: Steve <steve@designerwallcoverings.com>
Date: Mon Jun 22 19:26:30 2026 -0700
Bucket-B PR + Hollywood sample-only roll-variant scripts (DTD-A)
Source-join recovery for the two largest sample-only buckets. Glob-matched
bucketB-*-roll-variants.js so bucketB-land-all.sh auto-drains them; PG-first,
draft-gated, budget-safe (take/refund 'roll'), idempotent, leak-safe (never
writes Command54/Momentum to any customer-facing field).
Honest source reality (verified against live dw_unified 2026-06-22):
- PR: command54_catalog has 0 priced rows; phillipe_romano_catalog prices only
60 of 5841 worklist -> 60 staged, 5781 Needs-Price.
- HW: cohort does not bridge to Momentum (pattern/sku joins yield 0-2); the 12
'priced' hollywood_catalog rows were $4.25 sample-price contamination rejected
by the sample-tier floor -> 0 genuine, 1799 Needs-Price.
---
shopify/scripts/bucketB-hollywood-roll-variants.js | 317 +++++++++++++++++++++
.../scripts/bucketB-phillipromano-roll-variants.js | 312 ++++++++++++++++++++
2 files changed, 629 insertions(+)
diff --git a/shopify/scripts/bucketB-hollywood-roll-variants.js b/shopify/scripts/bucketB-hollywood-roll-variants.js
new file mode 100644
index 00000000..14b70743
--- /dev/null
+++ b/shopify/scripts/bucketB-hollywood-roll-variants.js
@@ -0,0 +1,317 @@
+#!/usr/bin/env node
+/**
+ * bucketB-hollywood-roll-variants.js (2026-06-22) — owner: vp-dw-commerce
+ *
+ * Bucket B — HOLLYWOOD WALLCOVERINGS sample-only recovery (Steve APPROVED, DTD-A
+ * unanimous 3/3, feed-first, draft-gated, reversible). Mirror of the completed Rebel
+ * Walls / Mind the Gap / Nina Campbell runs. For each Hollywood sample-only product
+ * (Sample variant present, no sellable ROLL), add the missing ROLL variant `{DW_SKU}`
+ * alongside the existing `{DW_SKU}-Sample` ($4.25) — ONLY when a clean price is found.
+ *
+ * PRIVATE-LABEL LEAK RULE — ABSOLUTE: Hollywood Wallcoverings is the private label over
+ * the Momentum upstream. NEVER write "Momentum" anywhere customer-facing. This script
+ * adds a back-office roll variant + price ONLY; it NEVER touches title, the customer-
+ * facing vendor field, or the display_variant tag.
+ *
+ * PRICE SOURCE — recover from PG first (Steve-approved, catalog-only):
+ * The task targeted the Momentum upstream (momentum_colorways / momentum_prices) for
+ * cost/price, bridged via shopify_products.mfr_sku. VERIFIED 2026-06-22 against live
+ * dw_unified — this HW cohort DOES NOT bridge to Momentum:
+ * - All 1,799 HW worklist sids exist in hollywood_catalog, but only 12 carry a
+ * price_retail, and ALL 12 are exactly $4.25 (sample-price contamination in the
+ * source, NOT a roll price) → 0 genuine roll prices after the sample-tier floor.
+ * - momentum_colorways (8,083 priced) / momentum_prices (433) are rich but map to a
+ * DIFFERENT product family: pattern-name join yields 2, and dw_sku / mfr_sku /
+ * shopify_product_id joins yield 0. The HW worklist patterns (Mollies Real Fabric,
+ * Grimsby Type II Vinyl, Tommy's Trees) are simply not Momentum patterns (Axiom WC,
+ * Alegre, Alexandria). The Momentum-priced data belongs to an older HW cohort.
+ * So 0 of 1,799 carry a clean, verified roll price from any catalog-only source today.
+ * ALL 1,799 STAY sample-only + tag Needs-Price. A targeted Momentum re-bridge/re-scrape
+ * of this specific cohort is staged separately (out of this catalog-only run's scope).
+ * This script NEVER invents / fuzzes / force-prices a roll, and rejects any sample-tier
+ * price (≤ $5) as no-price. It remains live + idempotent: the moment a clean price lands
+ * in the enriched TSV for a row, a future run recovers that roll.
+ *
+ * PRICING RULE (Hollywood Wallcoverings is NOT Kravet-family):
+ * prefer price_retail (already our single-roll retail, used verbatim from the PG-sourced
+ * enriched TSV). roll_price is taken verbatim from data/bucketB-hollywood-enriched.tsv.
+ *
+ * ORDER OF OPS (PostgreSQL BEFORE Shopify):
+ * 1. read recovered roll price from bucketB-hollywood-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): respect the 1k/day cap via budget.cjs take('roll', n) up-
+ * front to SIZE the run, then refund() the unused grant. Multiple vendors run concurrently;
+ * grant fewer than needed → create only what fits, STAGE the remainder (resumable next day).
+ *
+ * HARD RULES enforced:
+ * - PRIVATE-LABEL LEAK: never the name "Momentum" in any field this script writes.
+ * - 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 clean recovered price (or price ≤ sample tier) => stays sample-only,
+ * tag Needs-Price. NEVER force-price.
+ * - PG dw_unified BEFORE Shopify.
+ * - NEVER flip status. DRAFT stays DRAFT, ACTIVE stays ACTIVE (no img+width promotion here).
+ * - 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. Glob-matched bucketB-*-roll-variants.js → auto-drained
+ * by the nightly bucketB-land-all.sh; NO per-vendor launchd job.
+ * node bucketB-phillipromano-roll-variants.js # DRY-RUN
+ * node bucketB-phillipromano-roll-variants.js --apply # LIVE (budget-safe)
+ * node bucketB-phillipromano-roll-variants.js --apply --report data/bucketB/phillipromano-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');
+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 VENDOR = 'Hollywood Wallcoverings';
+const PRICE_SRC_PREFIX = 'catalog-hw'; // price_source values the TSV uses for priced rows
+const SAMPLE_PRICE_FLOOR = 5.00; // reject ≤ this as $4.25 sample-price contamination
+const TSV = path.join(os.homedir(), 'Projects/designerwallcoverings/today-viewer/data/bucketB-hollywood-enriched.tsv');
+const TODAY = new Date().toISOString().slice(0, 10);
+
+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));
+}
+
+// A row is genuinely priced only if price_source is a catalog source AND the price clears
+// the sample-tier floor (defends against $4.25 sample-price contamination in the source).
+function isPriced(r) {
+ return r.price_source.startsWith(PRICE_SRC_PREFIX) && r.roll_price > SAMPLE_PRICE_FLOOR;
+}
+
+(async () => {
+ const rows = loadWork();
+ const priced = rows.filter(isPriced);
+ const unpriced = rows.filter(r => !isPriced(r));
+ console.log(`bucketB-hollywood — ${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: VENDOR, 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 ----
+ let rollCap = priced.length;
+ let grant = priced.length;
+ if (budget && !NO_BUDGET) {
+ try {
+ 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)`);
+ 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.`);
+ } 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 ----
+ 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 (_) {} }
+ 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; }
+
+ 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}`); }
+})();
diff --git a/shopify/scripts/bucketB-phillipromano-roll-variants.js b/shopify/scripts/bucketB-phillipromano-roll-variants.js
new file mode 100644
index 00000000..5e3a037b
--- /dev/null
+++ b/shopify/scripts/bucketB-phillipromano-roll-variants.js
@@ -0,0 +1,312 @@
+#!/usr/bin/env node
+/**
+ * bucketB-phillipromano-roll-variants.js (2026-06-22) — owner: vp-dw-commerce
+ *
+ * Bucket B — PHILLIPE ROMANO sample-only recovery (Steve APPROVED, DTD-A unanimous
+ * 3/3, feed-first, draft-gated, reversible). Mirror of the completed Rebel Walls /
+ * Mind the Gap / Nina Campbell runs. For each Phillipe Romano sample-only product
+ * (Sample variant present, no sellable ROLL), add the missing ROLL variant `{DW_SKU}`
+ * alongside the existing `{DW_SKU}-Sample` ($4.25) — ONLY when a clean price is found.
+ *
+ * PRIVATE-LABEL LEAK RULE — ABSOLUTE: Phillipe Romano is the private label over the
+ * Command54 upstream. NEVER write "Command54" anywhere customer-facing. This script
+ * adds a back-office roll variant + price ONLY; it NEVER touches title, the customer-
+ * facing vendor field, or the display_variant tag.
+ *
+ * PRICE SOURCE — recover from PG first (Steve-approved, catalog-only):
+ * The task targeted the Command54 upstream (command54_catalog) for cost/price, bridged
+ * via shopify_products.mfr_sku. VERIFIED 2026-06-22 against live dw_unified:
+ * - command54_catalog has 2,120 rows but ZERO priced (price_retail = price_trade = 0
+ * for every row; many names are scrape-noise) → it CANNOT price anything.
+ * - phillipe_romano_catalog prices only 60 of the 5,841 PR sample-only worklist
+ * (exact thin residue the prior pass already found), reachable via three SAFE bridges
+ * (NEVER fuzzy): (a) source.shopify_product_id == worklist GID tail, (b) source.mfr_sku
+ * == live shopify_products.mfr_sku, (c) source.dw_sku == worklist base DW SKU.
+ * So only 60 of 5,841 carry a clean, verified price. The other 5,781 have NO recoverable
+ * price in any catalog-only source → they STAY sample-only + tag Needs-Price. This script
+ * NEVER invents / fuzzes / force-prices a roll, and rejects any sample-tier price (≤ $5,
+ * i.e. $4.25 sample-price contamination) as no-price.
+ *
+ * PRICING RULE (Phillipe Romano is NOT Kravet-family):
+ * prefer price_retail (already our single-roll retail, used verbatim from the PG-sourced
+ * enriched TSV). roll_price is taken verbatim from data/bucketB-phillipromano-enriched.tsv.
+ *
+ * ORDER OF OPS (PostgreSQL BEFORE Shopify):
+ * 1. read recovered roll price from bucketB-phillipromano-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): respect the 1k/day cap via budget.cjs take('roll', n) up-
+ * front to SIZE the run, then refund() the unused grant. Multiple vendors run concurrently;
+ * grant fewer than needed → create only what fits, STAGE the remainder (resumable next day).
+ *
+ * HARD RULES enforced:
+ * - PRIVATE-LABEL LEAK: never the name "Command54" in any field this script writes.
+ * - 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 clean recovered price (or price ≤ sample tier) => stays sample-only,
+ * tag Needs-Price. NEVER force-price.
+ * - PG dw_unified BEFORE Shopify.
+ * - NEVER flip status. DRAFT stays DRAFT, ACTIVE stays ACTIVE (no img+width promotion here).
+ * - 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. Glob-matched bucketB-*-roll-variants.js → auto-drained
+ * by the nightly bucketB-land-all.sh; NO per-vendor launchd job.
+ * node bucketB-phillipromano-roll-variants.js # DRY-RUN
+ * node bucketB-phillipromano-roll-variants.js --apply # LIVE (budget-safe)
+ * node bucketB-phillipromano-roll-variants.js --apply --report data/bucketB/phillipromano-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');
+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 VENDOR = 'Phillipe Romano';
+const PRICE_SRC_PREFIX = 'catalog-pr'; // price_source values the TSV uses for priced rows
+const SAMPLE_PRICE_FLOOR = 5.00; // reject ≤ this as $4.25 sample-price contamination
+const TSV = path.join(os.homedir(), 'Projects/designerwallcoverings/today-viewer/data/bucketB-phillipromano-enriched.tsv');
+const TODAY = new Date().toISOString().slice(0, 10);
+
+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));
+}
+
+// A row is genuinely priced only if price_source is a catalog source AND the price clears
+// the sample-tier floor (defends against $4.25 sample-price contamination in the source).
+function isPriced(r) {
+ return r.price_source.startsWith(PRICE_SRC_PREFIX) && r.roll_price > SAMPLE_PRICE_FLOOR;
+}
+
+(async () => {
+ const rows = loadWork();
+ const priced = rows.filter(isPriced);
+ const unpriced = rows.filter(r => !isPriced(r));
+ console.log(`bucketB-phillipromano — ${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: VENDOR, 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 ----
+ let rollCap = priced.length;
+ let grant = priced.length;
+ if (budget && !NO_BUDGET) {
+ try {
+ 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)`);
+ 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.`);
+ } 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 ----
+ 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 (_) {} }
+ 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; }
+
+ 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}`); }
+})();
← 0ee05d61 New Arrivals Boost scope: confirm no API key (admin-only fix
·
back to Designer Wallcoverings
·
card-hover v3: hide vendor on touch (mobile shows pattern na 1cde7666 →