← back to Designer Wallcoverings
Romo per-unit pricing: repair 57 corrupt total_price_per_roll, harden cost expr (GREATEST guard), add roll-variant push script
75964c0c432013cf1ba9e20c0ac69c05303f2f05 · 2026-06-11 11:02:29 -0700 · SteveStudio2
Files touched
M shopify/scripts/cadence/vendors.jsA shopify/scripts/romo-add-roll-pricing.js
Diff
commit 75964c0c432013cf1ba9e20c0ac69c05303f2f05
Author: SteveStudio2 <steve@designerwallcoverings.com>
Date: Thu Jun 11 11:02:29 2026 -0700
Romo per-unit pricing: repair 57 corrupt total_price_per_roll, harden cost expr (GREATEST guard), add roll-variant push script
---
shopify/scripts/cadence/vendors.js | 5 +-
shopify/scripts/romo-add-roll-pricing.js | 130 +++++++++++++++++++++++++++++++
2 files changed, 134 insertions(+), 1 deletion(-)
diff --git a/shopify/scripts/cadence/vendors.js b/shopify/scripts/cadence/vendors.js
index af4d070a..f22eb730 100644
--- a/shopify/scripts/cadence/vendors.js
+++ b/shopify/scripts/cadence/vendors.js
@@ -12,7 +12,10 @@
module.exports = {
// direct cost column
- 'Romo': { table: 'romo_catalog', costExpr: 'COALESCE(NULLIF(total_price_per_roll,0), cost*1.10)', soldBy: 'Single Roll', productType: 'Wallcovering', note: 'DTD-B 2026-06-11: landed cost incl 10% tariff; confirm tariff treatment w/ Steve before bulk import' },
+ // 2026-06-11: total_price_per_roll was corrupt for 57 rows (computed off the bogus
+ // `price`/per-yard cols, came out ~10x low e.g. Jali total=13.12 vs trade=128). GREATEST
+ // guards against that: never trust a stored total below the trade-based landed cost.
+ 'Romo': { table: 'romo_catalog', costExpr: 'GREATEST(COALESCE(NULLIF(total_price_per_roll,0),0), COALESCE(NULLIF(trade_price_per_roll,0),0)*(1+COALESCE(tariff_pct,0)/100), COALESCE(cost,0)*1.10)', soldBy: 'Single Roll', productType: 'Wallcovering', note: 'DTD-B 2026-06-11: landed cost = trade_price_per_roll * (1+tariff); confirm tariff treatment w/ Steve before bulk import' },
'Thibaut': { table: 'thibaut_catalog', costExpr: 'your_cost', soldBy: 'Single Roll', productType: 'Wallcovering' },
'Osborne & Little': { table: 'osborne_catalog', costExpr: 'price_trade', soldBy: 'Single Roll', productType: 'Wallcovering' },
// Designtex: price_trade is EMPTY; the designtex catalog price (price_retail) IS our cost
diff --git a/shopify/scripts/romo-add-roll-pricing.js b/shopify/scripts/romo-add-roll-pricing.js
new file mode 100644
index 00000000..17e024a4
--- /dev/null
+++ b/shopify/scripts/romo-add-roll-pricing.js
@@ -0,0 +1,130 @@
+#!/usr/bin/env node
+/**
+ * romo-add-roll-pricing.js (2026-06-11)
+ *
+ * Steve: "All romo must have prices per unit." The legacy Romo import created
+ * ACTIVE products with ONLY a $4.25 Sample variant and no Single Roll price.
+ * This adds the missing Single Roll variant (with real cost) to EXISTING Romo
+ * products that have a confirmed roll cost in romo_catalog.
+ *
+ * Cost source (cadence/vendors.js, DTD-B verified): GREATEST(total_price_per_roll,
+ * trade_price_per_roll*(1+tariff), cost*1.10). retail = round(cost/0.65/0.85, 2).
+ * (57 corrupt total_price_per_roll rows were repaired in dw_unified first.)
+ *
+ * Reads /tmp/romo_push.csv: shopify_id,dw_sku,mfr_sku,pattern,color,cost,retail
+ * Per product: bulk-create Single Roll variant (cost on inventoryItem), reorder it
+ * first, set custom.cost + custom.price_updated_at + global.unit_of_measure, tag it.
+ * Idempotent: skips any product that already has a non-Sample variant.
+ *
+ * node romo-add-roll-pricing.js # DRY-RUN
+ * node romo-add-roll-pricing.js --apply # LIVE
+ * node romo-add-roll-pricing.js --apply --limit 5
+ */
+const https = require('https');
+const fs = require('fs');
+const os = require('os');
+
+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 CSV = '/tmp/romo_push.csv';
+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));
+
+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 tags
+ options{name values}
+ variants(first:20){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}}}`;
+
+function parseCsv(p) {
+ const [head, ...lines] = fs.readFileSync(p, 'utf8').trim().split('\n');
+ const cols = head.split(',');
+ return lines.map(l => { const v = l.split(','); const o = {}; cols.forEach((c, i) => o[c] = v[i]); return o; });
+}
+
+(async () => {
+ const rows = parseCsv(CSV).slice(0, LIMIT === Infinity ? undefined : LIMIT);
+ console.log(`romo-add-roll-pricing — ${rows.length} products — ${APPLY ? '🔴 LIVE' : 'DRY-RUN'}\n`);
+ let done = 0, skipped = 0, failed = 0;
+ for (const row of rows) {
+ const pid = `gid://shopify/Product/${row.shopify_id}`;
+ const cost = parseFloat(row.cost), retail = parseFloat(row.retail);
+ if (!(cost > 0) || !(retail > 0)) { console.log(`⏭ ${row.shopify_id} bad numbers cost=${row.cost} retail=${row.retail}`); skipped++; continue; }
+
+ const pr = await gqlR(Q_PRODUCT, { id: pid });
+ const p = pr.data && pr.data.product;
+ if (!p) { console.log(`❌ ${row.shopify_id} not found`); failed++; 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} already has roll variant (${roll.sku} $${roll.title})`); skipped++; continue; }
+ const skuBase = (sample && sample.sku ? sample.sku.replace(/-sample$/i, '') : row.dw_sku);
+
+ console.log(`${APPLY ? '➕' : '·'} ${p.title.slice(0, 50).padEnd(50)} roll $${retail} (cost $${cost}) sku ${skuBase}`);
+ if (!APPLY) { done++; continue; }
+
+ // 1) create Single Roll
+ const cr = await gqlR(M_VAR_CREATE, { pid, variants: [{
+ optionValues: [{ optionName: 'Title', name: 'Single Roll' }],
+ price: String(retail), taxable: true, inventoryPolicy: 'CONTINUE',
+ inventoryItem: { sku: skuBase, cost: cost.toFixed(2), tracked: true },
+ }] });
+ const ue = cr.data?.productVariantsBulkCreate?.userErrors || [];
+ if (ue.length) { console.log(` ❌ create: ${JSON.stringify(ue)}`); failed++; continue; }
+ const newId = cr.data.productVariantsBulkCreate.productVariants[0].id;
+
+ // 2) Single Roll first, Sample second
+ 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)}`);
+ }
+
+ // 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: 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)}`);
+
+ // 4) tag
+ if (!(p.tags || []).includes('Priced Per Single Roll')) {
+ await gqlR(M_TAGS, { id: pid, tags: ['Priced Per Single Roll'] });
+ }
+ done++;
+ await sleep(180);
+ }
+ console.log(`\nDone. created=${done} skipped=${skipped} failed=${failed}`);
+})();
← bccac2fd cadence-import: recompute price-coverage at end of run (feed
·
back to Designer Wallcoverings
·
Romo push: read durable worklist CSV; add run wrapper; CNCP- fe9e383a →