← back to Letsbegin
Add 3-material-options priced metafield (Standard/P&S/Commercial, per-m2 retail+cost) on 1000 RW murals
df2dbbf909a0dfd2790c71a356a5105d1330523a · 2026-06-11 15:06:27 -0700 · Steve Abrams
Files touched
M .gitignoreA rw-material-options.js
Diff
commit df2dbbf909a0dfd2790c71a356a5105d1330523a
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Thu Jun 11 15:06:27 2026 -0700
Add 3-material-options priced metafield (Standard/P&S/Commercial, per-m2 retail+cost) on 1000 RW murals
---
.gitignore | 2 +
rw-material-options.js | 270 +++++++++++++++++++++++++++++++++++++++++++++++++
2 files changed, 272 insertions(+)
diff --git a/.gitignore b/.gitignore
index e234a0e..17f638b 100644
--- a/.gitignore
+++ b/.gitignore
@@ -42,3 +42,5 @@ coverage/
tmp/
*.log
*.bak
+rw-material-options.progress.jsonl
+rw-material-options.run.log
diff --git a/rw-material-options.js b/rw-material-options.js
new file mode 100644
index 0000000..7fc3209
--- /dev/null
+++ b/rw-material-options.js
@@ -0,0 +1,270 @@
+#!/usr/bin/env node
+/**
+ * rw-material-options.js — write the "3 material options, priced out" metafield
+ * to every Rebel Walls mural product in the 20-day cohort on the SANDBOX store.
+ *
+ * Steve-approved 2026-06-11: "offer all 3 options and priced out".
+ *
+ * For each product in:
+ * shopify_products WHERE created_at_shopify >= now()-interval '20 days'
+ * AND vendor='Rebel Walls' (1,000 RW murals)
+ * write:
+ * - custom.material_options (type json) = stringified array of 3 RW material
+ * choices for the standard C3 price group, priced PER M² (USD):
+ * retail = DW sell price (RW RRP), cost = DW net.
+ * - custom.materials_available (single_line_text) = "Non-Woven; Peel & Stick; Commercial Grade"
+ *
+ * LEAVES AS-IS (never touched): custom.cost_per_m2 (44.5), custom.price_group (C3),
+ * custom.cost_currency (USD), and every color/title/tag metafield, variant SKUs,
+ * and Shopify variant prices (price computes from wall size at order).
+ *
+ * Guardrails:
+ * - Sandbox only (designer-laboratory-sandbox.myshopify.com).
+ * - Idempotent / resumable: a progress ledger (rw-material-options.progress.jsonl)
+ * records every DONE shopify_id; a resumed run skips them. The write itself is
+ * also naturally idempotent (metafieldsSet overwrites with the same value).
+ * - ~2 req/s with exponential backoff on throttle.
+ * - Touches ONLY custom.material_options + custom.materials_available. A
+ * concurrent SKU-backfill job writing variant SKUs / other metafields is a
+ * different field and is not disturbed.
+ *
+ * Usage:
+ * node rw-material-options.js --limit 3 # validate on first 3
+ * node rw-material-options.js # run all 1,000 (resumable)
+ * node rw-material-options.js --ids gid://...,gid://... # specific ids
+ * node rw-material-options.js --requery N # re-QA: print material_options
+ * # + cost_per_m2 for N done rows
+ *
+ * Env: SHOPIFY_ADMIN_TOKEN. PG* optional (defaults user stevestudio2, db dw_unified).
+ */
+
+const https = require('https');
+const fs = require('fs');
+const path = require('path');
+const { Client } = require('pg');
+
+const STORE = process.env.SHOPIFY_STORE || 'designer-laboratory-sandbox.myshopify.com';
+const TOKEN = process.env.SHOPIFY_ADMIN_TOKEN || process.env.SHOPIFY_PRODUCT_TOKEN;
+const API = '/admin/api/2024-10/graphql.json';
+if (!TOKEN) { console.error('FATAL: SHOPIFY_ADMIN_TOKEN env var required'); process.exit(1); }
+
+// ---- guardrail: sandbox only ----
+if (!/sandbox/i.test(STORE)) {
+ console.error(`FATAL: refusing to run against non-sandbox store "${STORE}"`);
+ process.exit(1);
+}
+
+const PROGRESS_FILE = path.join(__dirname, 'rw-material-options.progress.jsonl');
+
+// ---- the C3-standard 3-material payload (per m², USD) ----
+// retail = DW sell price (RW RRP); cost = DW net.
+// Non-Woven cost is portal-confirmed; P&S + Commercial are extrapolated
+// (RRP × 0.5825, the confirmed C3-standard Net/RRP ratio) pending portal Net.
+const MATERIAL_OPTIONS = [
+ { material: 'Non-Woven (Standard)', retail_per_m2: 76.40, cost_per_m2: 44.50, cost_confirmed: true, source: 'partner.gimmersta.com portal Net 2026-06' },
+ { material: 'Peel & Stick', retail_per_m2: 91.70, cost_per_m2: 53.41, cost_confirmed: false, source: 'extrapolated: RRP x 0.5825 (confirmed C3-standard Net/RRP ratio); confirm via portal' },
+ { material: 'Commercial Grade', retail_per_m2: 107.00, cost_per_m2: 62.32, cost_confirmed: false, source: 'extrapolated: RRP x 0.5825; confirm via portal' },
+];
+const MATERIALS_AVAILABLE = 'Non-Woven; Peel & Stick; Commercial Grade';
+
+// value must be a STRING containing valid JSON for Shopify json type.
+const MATERIAL_OPTIONS_JSON = JSON.stringify(MATERIAL_OPTIONS);
+// validate it parses (fail fast if the const above is ever malformed)
+try {
+ const parsed = JSON.parse(MATERIAL_OPTIONS_JSON);
+ if (!Array.isArray(parsed) || parsed.length !== 3) throw new Error('expected 3-entry array');
+} catch (e) { console.error(`FATAL: MATERIAL_OPTIONS_JSON invalid: ${e.message}`); process.exit(1); }
+
+function sleep(ms) { return new Promise(r => setTimeout(r, ms)); }
+
+// ---------- Shopify GraphQL (gql/metafieldsSet pattern from enrich-write.js) ----------
+function gqlRaw(body) {
+ return new Promise((resolve, reject) => {
+ const data = JSON.stringify(body);
+ const req = https.request({
+ hostname: STORE, path: API, method: 'POST',
+ headers: { 'X-Shopify-Access-Token': TOKEN, 'Content-Type': 'application/json', 'Content-Length': Buffer.byteLength(data) },
+ }, res => { let c = ''; res.on('data', d => c += d); res.on('end', () => { try { resolve(JSON.parse(c)); } catch { resolve({ error: c.slice(0, 300) }); } }); });
+ req.on('error', reject);
+ req.setTimeout(60000, () => { req.destroy(); reject(new Error('timeout')); });
+ req.write(data); req.end();
+ });
+}
+async function gql(body, retries = 5) {
+ for (let attempt = 1; attempt <= retries; attempt++) {
+ try {
+ const result = await gqlRaw(body);
+ const throttled = result?.errors?.some(e => /Throttled/i.test(e.message || ''));
+ const lowBudget = (result?.extensions?.cost?.throttleStatus?.currentlyAvailable || 9999) < 200;
+ if (throttled) { await sleep(2500 * attempt); continue; }
+ if (lowBudget) await sleep(1500);
+ return result;
+ } catch (e) {
+ if (attempt < retries) { await sleep(attempt * 2000); continue; }
+ throw e;
+ }
+ }
+}
+
+const MF_MUTATION = 'mutation metafieldsSet($m: [MetafieldsSetInput!]!) { metafieldsSet(metafields: $m) { metafields { key } userErrors { message field } } }';
+
+// ---------- progress ledger ----------
+function loadDone() {
+ const done = new Set();
+ if (fs.existsSync(PROGRESS_FILE)) {
+ for (const line of fs.readFileSync(PROGRESS_FILE, 'utf8').split('\n')) {
+ if (!line.trim()) continue;
+ try { const o = JSON.parse(line); if (o.status === 'done' && o.id) done.add(o.id); } catch {}
+ }
+ }
+ return done;
+}
+function markDone(id) {
+ fs.appendFileSync(PROGRESS_FILE, JSON.stringify({ id, status: 'done', at: new Date().toISOString() }) + '\n');
+}
+
+// ---------- cohort from PG ----------
+async function loadCohort() {
+ const pg = new Client({ user: process.env.PGUSER || 'stevestudio2', database: process.env.PGDATABASE || 'dw_unified', host: process.env.PGHOST || '/tmp' });
+ await pg.connect();
+ try {
+ const res = await pg.query(
+ `SELECT shopify_id FROM shopify_products
+ WHERE created_at_shopify >= now() - interval '20 days'
+ AND vendor = 'Rebel Walls'
+ AND shopify_id IS NOT NULL
+ ORDER BY shopify_id`
+ );
+ return res.rows.map(r => r.shopify_id);
+ } finally { try { await pg.end(); } catch {} }
+}
+
+// ---------- write the 2 metafields to one product ----------
+async function writeProduct(PID) {
+ const mf = [
+ { ownerId: PID, namespace: 'custom', key: 'material_options', value: MATERIAL_OPTIONS_JSON, type: 'json' },
+ { ownerId: PID, namespace: 'custom', key: 'materials_available', value: MATERIALS_AVAILABLE, type: 'single_line_text_field' },
+ ];
+ const r = await gql({ query: MF_MUTATION, variables: { m: mf } });
+ const errs = r?.data?.metafieldsSet?.userErrors || [];
+ if (errs.length) return { ok: false, err: errs.map(e => `${(e.field || []).join('.')}: ${e.message}`).join('; ') };
+ if (r?.errors?.length) return { ok: false, err: r.errors.map(e => e.message).join('; ') };
+ return { ok: true };
+}
+
+// ---------- re-fetch + print material_options for verification ----------
+async function fetchVerify(PID) {
+ const vr = await gql({ query: `{ product(id:"${PID}") { title metafields(first:100){ edges{ node{ namespace key value type } } } } }` });
+ const p = vr?.data?.product;
+ if (!p) return null;
+ const mfs = {};
+ for (const e of (p.metafields?.edges || [])) { if (e.node.namespace === 'custom') mfs[e.node.key] = { value: e.node.value, type: e.node.type }; }
+ return { title: p.title, mfs };
+}
+
+// ---------- modes ----------
+async function runRequery(n) {
+ const done = [...loadDone()];
+ const sample = done.slice(0, n);
+ if (!sample.length) { console.log('No DONE rows in progress ledger to re-QA.'); return; }
+ console.log(`Re-QA on ${sample.length} done products:\n`);
+ let pass = 0, fail = 0;
+ for (const PID of sample) {
+ const v = await fetchVerify(PID);
+ await sleep(450);
+ if (!v) { console.log(` ${PID} :: NOT FOUND`); fail++; continue; }
+ const mo = v.mfs['material_options'];
+ const cost = v.mfs['cost_per_m2'];
+ let parsed = null, parseOk = false, len = 0;
+ if (mo) { try { parsed = JSON.parse(mo.value); parseOk = Array.isArray(parsed); len = parseOk ? parsed.length : 0; } catch {} }
+ const moOk = !!mo && mo.type === 'json' && parseOk && len === 3;
+ const costOk = !!cost && String(cost.value) === '44.5';
+ if (moOk && costOk) pass++; else fail++;
+ console.log(` ${PID} | "${v.title}"`);
+ console.log(` material_options: ${moOk ? `OK (json, ${len} entries)` : 'FAIL'} | cost_per_m2=${cost ? cost.value : '(none)'} ${costOk ? 'OK (untouched)' : 'CHANGED!'}`);
+ }
+ console.log(`\nRe-QA result: ${pass} pass, ${fail} fail (of ${sample.length}).`);
+}
+
+async function runWrite({ limit, ids }) {
+ let cohort;
+ if (ids) {
+ cohort = ids;
+ console.log(`Mode: explicit ids (${cohort.length}).`);
+ } else {
+ cohort = await loadCohort();
+ console.log(`Cohort from PG: ${cohort.length} RW murals (20-day window).`);
+ if (limit) { cohort = cohort.slice(0, limit); console.log(`--limit ${limit} → processing first ${cohort.length}.`); }
+ }
+
+ const done = loadDone();
+ const todo = cohort.filter(id => !done.has(id));
+ console.log(`Already done (ledger): ${cohort.length - todo.length}. To process: ${todo.length}.\n`);
+
+ let written = 0, skipped = 0, failed = 0;
+ const errorRows = [];
+ const isValidation = !!limit || !!ids;
+
+ for (let i = 0; i < todo.length; i++) {
+ const PID = todo[i];
+ const res = await writeProduct(PID);
+ if (!res.ok) {
+ failed++; errorRows.push({ PID, err: res.err });
+ console.log(` [${i + 1}/${todo.length}] FAIL ${PID} :: ${res.err}`);
+ await sleep(600);
+ continue;
+ }
+ // verify-on-write for validation runs (and a light verify every 100 in bulk)
+ if (isValidation || (i % 100 === 0)) {
+ await sleep(350);
+ const v = await fetchVerify(PID);
+ const mo = v?.mfs['material_options'];
+ let parseOk = false, len = 0, parsed = null;
+ if (mo) { try { parsed = JSON.parse(mo.value); parseOk = Array.isArray(parsed); len = parsed.length; } catch {} }
+ const cost = v?.mfs['cost_per_m2'];
+ const ok = mo && mo.type === 'json' && parseOk && len === 3;
+ if (!ok) {
+ failed++; errorRows.push({ PID, err: 'verify failed: material_options not valid json/3-entries' });
+ console.log(` [${i + 1}/${todo.length}] VERIFY-FAIL ${PID}`);
+ await sleep(500);
+ continue;
+ }
+ markDone(PID); written++;
+ console.log(` [${i + 1}/${todo.length}] OK ${PID} | "${v.title}"`);
+ console.log(` custom.material_options = ${mo.value}`);
+ console.log(` custom.materials_available present | cost_per_m2=${cost ? cost.value : '(none)'} (untouched)`);
+ } else {
+ markDone(PID); written++;
+ if (i % 50 === 0) console.log(` [${i + 1}/${todo.length}] OK ${PID}`);
+ }
+ await sleep(500); // ~2 req/s
+ }
+
+ console.log(`\n==== SUMMARY ====`);
+ console.log(`written: ${written} | failed: ${failed} | total in this run: ${todo.length}`);
+ if (errorRows.length) {
+ console.log(`errors:`);
+ for (const e of errorRows) console.log(` ${e.PID} :: ${e.err}`);
+ }
+ const totalDone = loadDone().size;
+ console.log(`progress ledger total DONE: ${totalDone}`);
+ if (failed > 0) process.exit(1);
+}
+
+// ---------- arg parse ----------
+function parseArgs() {
+ const a = process.argv.slice(2);
+ const out = { limit: null, ids: null, requery: null };
+ for (let i = 0; i < a.length; i++) {
+ if (a[i] === '--limit') out.limit = parseInt(a[++i], 10);
+ else if (a[i] === '--ids') out.ids = a[++i].split(',').map(s => s.trim()).filter(Boolean).map(s => /^gid:\/\//.test(s) ? s : `gid://shopify/Product/${s.replace(/\D/g, '')}`);
+ else if (a[i] === '--requery') out.requery = parseInt(a[++i], 10) || 15;
+ }
+ return out;
+}
+
+(async () => {
+ const args = parseArgs();
+ if (args.requery) { await runRequery(args.requery); return; }
+ await runWrite(args);
+})().catch(e => { console.error(`FATAL: ${e.message}\n${e.stack}`); process.exit(1); });
← 3c0c7fa Backfill SKUs (mfr_sku/dw_sku/variant sku + rw url metafield
·
back to Letsbegin
·
Add rw-catalog-recrawl.js: feed-first RW re-crawl into vendo 7f50750 →