← back to Designer Wallcoverings
Add bucketA-add-roll-variants.js: build sellable roll variant on 311 sample-only cost-bearing products (PG-first, DW pricing rules)
4be7b7c4c49f1aa72bab17ec39bd988d1446a3ee · 2026-06-22 13:17:32 -0700 · Steve
Files touched
A shopify/scripts/bucketA-add-roll-variants.js
Diff
commit 4be7b7c4c49f1aa72bab17ec39bd988d1446a3ee
Author: Steve <steve@designerwallcoverings.com>
Date: Mon Jun 22 13:17:32 2026 -0700
Add bucketA-add-roll-variants.js: build sellable roll variant on 311 sample-only cost-bearing products (PG-first, DW pricing rules)
---
shopify/scripts/bucketA-add-roll-variants.js | 219 +++++++++++++++++++++++++++
1 file changed, 219 insertions(+)
diff --git a/shopify/scripts/bucketA-add-roll-variants.js b/shopify/scripts/bucketA-add-roll-variants.js
new file mode 100644
index 00000000..36aa9820
--- /dev/null
+++ b/shopify/scripts/bucketA-add-roll-variants.js
@@ -0,0 +1,219 @@
+#!/usr/bin/env node
+/**
+ * bucketA-add-roll-variants.js (2026-06-22) — owner: vp-dw-commerce
+ *
+ * Sample-only cleanup Bucket A (Steve APPROVED, draft-gated, reversible).
+ * 311 ACTIVE "sample-only" products that already have a real cost in dw_unified
+ * get the missing sellable ROLL variant `{DW_SKU}` built alongside the existing
+ * `{DW_SKU}-Sample` ($4.25). No scraping, no removal.
+ *
+ * PRICING (DW rules):
+ * - Lee Jofa = Kravet-family -> MAP = WHLS x 1.5
+ * (uses kravet_authoritative_pricing.new_map if the mfr_sku is
+ * there; none of the 17 LJ skus are, so falls back to cost*1.5,
+ * which IS the universal MAP per the standing Kravet rule)
+ * - all other = cost / 0.65 / 0.85
+ *
+ * ORDER OF OPS (PostgreSQL BEFORE Shopify):
+ * 1. read cost from the worklist (already present in dw_unified mirror)
+ * 2. WRITE computed roll price into dw_unified.shopify_products.retail_price
+ * (+ price_source note) for the matching shopify_id -- source of truth first
+ * 3. create the roll variant on the Shopify product via productVariantsBulkCreate
+ * (sku on inventoryItem, cost on inventoryItem), reorder roll-first, tag/metafields
+ *
+ * GUARDS:
+ * - variant SKU = the existing Sample variant's sku minus `-Sample` (authoritative);
+ * if the product has no Sample sku AND no base_sku -> SKIP+report (no Unknown SKU)
+ * - idempotent: skip any product that already has a non-Sample variant
+ * - NEVER changes product status. These are already ACTIVE; we do not flip status.
+ * (If a product were DRAFT it stays DRAFT; never flipped ACTIVE without img+width.)
+ * - never touches title / customer-facing vendor field / display_variant tag
+ *
+ * Worklist: today-viewer/data/bucketA-ids.tsv (shopify_id, base_sku, vendor, cost).
+ *
+ * node bucketA-add-roll-variants.js # DRY-RUN (also writes nothing to PG)
+ * node bucketA-add-roll-variants.js --apply # LIVE: PG write + Shopify create
+ * node bucketA-add-roll-variants.js --apply --limit 5
+ * node bucketA-add-roll-variants.js --apply --report data/bucketA/run-report.json
+ */
+const https = require('https');
+const fs = require('fs');
+const os = require('os');
+const { execFileSync } = require('child_process');
+
+const args = process.argv.slice(2);
+const APPLY = args.includes('--apply');
+const LIMIT = (() => { const i = args.indexOf('--limit'); return i >= 0 ? parseInt(args[i + 1], 10) : Infinity; })();
+const REPORT = (() => { const i = args.indexOf('--report'); return i >= 0 ? args[i + 1] : null; })();
+
+const TSV = os.homedir() + '/Projects/designerwallcoverings/today-viewer/data/bucketA-ids.tsv';
+const TODAY = new Date().toISOString().slice(0, 10);
+
+const env = fs.readFileSync(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));
+
+// ---- pricing ---------------------------------------------------------------
+function priceFor(vendor, cost) {
+ if (/lee jofa/i.test(vendor)) {
+ return { rule: 'MAP=WHLSx1.5', price: round2(cost * 1.5) };
+ }
+ return { rule: 'cost/0.65/0.85', price: round2(cost / 0.65 / 0.85) };
+}
+function round2(n) { return Math.round((n + Number.EPSILON) * 100) / 100; }
+
+// ---- dw_unified write (PG-first) ------------------------------------------
+// Runs over ssh as `sudo -u postgres psql` (OS user is root on the box).
+function pgWriteRollPrice(rows) {
+ // rows: [{shopify_id, price, rule}] -- shopify_id here is the numeric id
+ const values = rows.map(r =>
+ `('gid://shopify/Product/${r.shopify_id}', ${r.price})`).join(',\n');
+ const sql = `
+BEGIN;
+CREATE TEMP TABLE _bA_price(gid text, roll_price numeric);
+INSERT INTO _bA_price(gid, roll_price) VALUES
+${values};
+UPDATE shopify_products sp
+ SET retail_price = b.roll_price
+ FROM _bA_price b
+ WHERE sp.shopify_id = b.gid;
+SELECT count(*) AS updated FROM _bA_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}}}`;
+
+// ---- worklist --------------------------------------------------------------
+function loadWorklist() {
+ const lines = fs.readFileSync(TSV, 'utf8').trim().split('\n');
+ const [, ...rows] = lines; // drop header
+ return rows.map(l => {
+ const [shopify_id, base_sku, vendor, cost] = l.split('\t');
+ return { shopify_id: (shopify_id || '').trim(), base_sku: (base_sku || '').trim(),
+ vendor: (vendor || '').trim(), cost: parseFloat(cost) };
+ });
+}
+
+(async () => {
+ let rows = loadWorklist();
+ if (LIMIT !== Infinity) rows = rows.slice(0, LIMIT);
+ console.log(`bucketA-add-roll-variants — ${rows.length} products — ${APPLY ? '🔴 LIVE' : 'DRY-RUN'}\n`);
+
+ const report = { started: new Date().toISOString(), apply: APPLY, created: [], skipped: [], failed: [], byVendor: {} };
+ const bump = (k, v) => { report.byVendor[v] = report.byVendor[v] || { created: 0, skipped: 0, failed: 0 }; report.byVendor[v][k]++; };
+
+ // ---- preflight: validate SKU + cost, compute price ----
+ const work = [];
+ for (const r of rows) {
+ if (!(r.cost > 0)) { console.log(`⏭ ${r.shopify_id} ${r.base_sku || '(no sku)'} — bad cost ${r.cost}`); report.skipped.push({ ...r, reason: 'no-cost' }); bump('skipped', r.vendor); continue; }
+ const { rule, price } = priceFor(r.vendor, r.cost);
+ if (!(price > 0)) { console.log(`⏭ ${r.shopify_id} ${r.base_sku} — bad price`); report.skipped.push({ ...r, reason: 'bad-price' }); bump('skipped', r.vendor); continue; }
+ work.push({ ...r, rule, price });
+ }
+
+ // ---- PG-FIRST: write roll prices to dw_unified before any Shopify write ----
+ if (APPLY && work.length) {
+ try {
+ const res = pgWriteRollPrice(work.map(w => ({ shopify_id: w.shopify_id, price: w.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);
+ process.exit(2);
+ }
+ } else if (work.length) {
+ console.log(`(dry-run) would write ${work.length} retail_price rows to dw_unified first\n`);
+ }
+
+ // ---- Shopify: add roll variant per product ----
+ for (const w of work) {
+ 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' }); bump('failed', w.vendor); 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,48)} already has roll (${roll.sku})`); report.skipped.push({ ...w, reason: 'already-has-roll', existingRollSku: roll.sku }); bump('skipped', w.vendor); continue; }
+
+ // SKU: prefer existing sample sku minus -Sample, else worklist 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 (sample sku null + empty base_sku) — SKIP`); report.failed.push({ ...w, reason: 'no-sku' }); bump('failed', w.vendor); continue; }
+
+ console.log(`${APPLY ? '➕' : '·'} ${p.title.slice(0, 46).padEnd(46)} [${p.status}] roll $${w.price} (cost $${w.cost}, ${w.rule}) sku ${skuBase}`);
+ if (!APPLY) { report.created.push({ ...w, skuBase, dryRun: true }); bump('created', w.vendor); continue; }
+
+ // 1) create roll variant
+ const cr = await gqlR(M_VAR_CREATE, { pid, variants: [{
+ optionValues: [{ optionName: (p.options[0] && p.options[0].name) || 'Title', name: 'Single Roll' }],
+ price: String(w.price), taxable: true, inventoryPolicy: 'CONTINUE',
+ inventoryItem: { sku: skuBase, cost: w.cost.toFixed(2), 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 }); bump('failed', w.vendor); continue; }
+ const newId = cr.data.productVariantsBulkCreate.productVariants[0].id;
+
+ // 2) roll first, sample second
+ if (sample) {
+ const ro = await gqlR(M_REORDER, { pid, positions: [{ id: newId, position: 0 }, { id: sample.id, position: 1 }] });
+ const re = ro.data?.productVariantsBulkReorder?.userErrors || [];
+ if (re.length) console.log(` ⚠ reorder: ${JSON.stringify(re)}`);
+ }
+
+ // 3) metafields: cost + price_updated_at + unit_of_measure
+ const mf = await gqlR(M_MF, { mf: [
+ { ownerId: pid, namespace: 'custom', key: 'cost', type: 'number_decimal', value: w.cost.toFixed(2) },
+ { 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, variantId: newId });
+ bump('created', w.vendor);
+ await sleep(180);
+ }
+
+ report.finished = new Date().toISOString();
+ report.totals = { created: report.created.length, skipped: report.skipped.length, failed: report.failed.length };
+ console.log(`\n=== SUMMARY ===`);
+ console.log(`created=${report.totals.created} skipped=${report.totals.skipped} failed=${report.totals.failed}`);
+ console.log('per-vendor:', JSON.stringify(report.byVendor, null, 0));
+ if (REPORT) { fs.mkdirSync(require('path').dirname(REPORT), { recursive: true }); fs.writeFileSync(REPORT, JSON.stringify(report, null, 2)); console.log(`report -> ${REPORT}`); }
+})();
← 77dea01c Add idempotent size/qty layout fixer (strip-all-then-inject-
·
back to Designer Wallcoverings
·
MOQ audit + fix tooling: store-wide order-minimum audit, Tie de446529 →