← back to Designer Wallcoverings
Add fix-24h-new-skus.js: title-case + cost/price + price_updated_at for last-72h new SKUs
0f0ded51d07b88ef2c8fedf57687430d506337df · 2026-06-11 08:39:40 -0700 · SteveStudio2
Title-case pass committed 171 titles live (Schumacher/Thibaut all-caps fixed,
& decoded, hyphen/slash/acronym/SKU-code safe; Codex-reviewed). Price pass
gated on cost data (only ~1/924 has recoverable cost — vendor catalogs are
cost-empty except Thibaut). Bulk Rebel Walls launchd push disabled per Steve.
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
Files touched
A shopify/scripts/fix-24h-new-skus.js
Diff
commit 0f0ded51d07b88ef2c8fedf57687430d506337df
Author: SteveStudio2 <stevestudio2@SteveStacStudio.lan>
Date: Thu Jun 11 08:39:40 2026 -0700
Add fix-24h-new-skus.js: title-case + cost/price + price_updated_at for last-72h new SKUs
Title-case pass committed 171 titles live (Schumacher/Thibaut all-caps fixed,
& decoded, hyphen/slash/acronym/SKU-code safe; Codex-reviewed). Price pass
gated on cost data (only ~1/924 has recoverable cost — vendor catalogs are
cost-empty except Thibaut). Bulk Rebel Walls launchd push disabled per Steve.
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
---
shopify/scripts/fix-24h-new-skus.js | 266 ++++++++++++++++++++++++++++++++++++
1 file changed, 266 insertions(+)
diff --git a/shopify/scripts/fix-24h-new-skus.js b/shopify/scripts/fix-24h-new-skus.js
new file mode 100644
index 00000000..af6b1511
--- /dev/null
+++ b/shopify/scripts/fix-24h-new-skus.js
@@ -0,0 +1,266 @@
+#!/usr/bin/env node
+/**
+ * fix-24h-new-skus.js — reprice + title-case + cost-metafield for the last-N-hours
+ * of newly-created DW Shopify products.
+ *
+ * Scope: products with created_at > now - WINDOW_HOURS (default 72), LIVE Shopify.
+ * Cost: trusted from dw_unified.shopify_products.cost (NO catalog re-derivation).
+ * Retail: round(cost / 0.65 / 0.85, 2). Fixed divisors, all vendors.
+ * Missing: cost NULL/0 -> SKIP (DTD verdict A) + log to exceptions CSV.
+ * Order: write dw_unified FIRST, then push to Shopify (Steve's hard rule).
+ *
+ * Phases:
+ * node fix-24h-new-skus.js build worklist (read-only) -> worklist + exceptions
+ * node fix-24h-new-skus.js --log-db also UPDATE dw_unified (price/retail/title)
+ * node fix-24h-new-skus.js --push dry-run Shopify push (no writes to Shopify)
+ * node fix-24h-new-skus.js --push --commit COMMIT pushes to Shopify (titles, custom.cost, variant price)
+ *
+ * --window <hours> override the 72h window.
+ */
+const https = require('https');
+const fs = require('fs');
+const os = require('os');
+const { execFileSync } = require('child_process');
+
+// ---- config / args ----
+const args = process.argv.slice(2);
+const LOG_DB = args.includes('--log-db');
+const PUSH = args.includes('--push'); // price + cost-metafield pass (priceable only)
+const TITLES = args.includes('--titles'); // title-case pass (ALL products)
+const COMMIT = args.includes('--commit');
+const TODAY = new Date().toISOString().slice(0, 10); // YYYY-MM-DD for price_updated_at
+const wIdx = args.indexOf('--window');
+const WINDOW_HOURS = wIdx >= 0 ? parseFloat(args[wIdx + 1]) : 72;
+const RETAIL = cost => Math.round((cost / 0.65 / 0.85) * 100) / 100;
+
+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';
+const API = '2024-10';
+const OUTDIR = __dirname;
+const WORKLIST = `${OUTDIR}/worklist-72h.jsonl`;
+const EXCEPTIONS = `${OUTDIR}/exceptions-72h.csv`;
+const MANIFEST = `${OUTDIR}/push-manifest-72h.json`;
+
+const sleep = ms => new Promise(r => setTimeout(r, ms));
+const numId = gid => String(gid).replace(/.*\//, '');
+
+// ---- title-case (preserve DW "Pattern Color | Type | Brand" format) ----
+// Small words stay lowercase mid-title; first/last word always capped.
+const SMALL = new Set(['a','an','and','as','at','but','by','de','del','for','if','in','la','le','nor','of','on','or','per','the','to','und','via','vs','with']);
+// Known acronyms / brand codes that must stay all-caps. NO roman numerals here —
+// they collide with real names (e.g. "Xi", "Vi"). Add codes explicitly as needed.
+const KEEP_UPPER = new Set(['usa','uk','ny','nyc','sf','clj','led','pvc','uv','3d','dna']);
+function decodeEntities(s) {
+ return s.replace(/&/gi, '&').replace(/"/gi, '"').replace(/'|'/gi, "'")
+ .replace(/ /gi, ' ').replace(/</gi, '<').replace(/>/gi, '>');
+}
+const capRuns = w => w.replace(/[A-Za-z]+/g, m => m.charAt(0).toUpperCase() + m.slice(1).toLowerCase());
+function tcWord(w, isFirst, isLast) {
+ if (!w) return w;
+ if (/\d/.test(w)) return w; // product/SKU codes (CJS6320, 5017710) — leave as-is
+ const low = w.replace(/[^A-Za-z]/g, '').toLowerCase();
+ if (KEEP_UPPER.has(low)) return w.toUpperCase();
+ // small words and trailing single letters (collab "x") stay lowercase even if first/last is NOT forced
+ if (SMALL.has(low) && !isFirst) return w.toLowerCase();
+ if (isLast && low.length === 1) return w.toLowerCase();
+ // capitalize the first letter of EACH alpha run so hyphen/slash compounds survive
+ return capRuns(w); // Hex-A-Gone, Grey/Black, FEATHER -> Feather
+}
+function titleCaseSegment(seg) {
+ const words = decodeEntities(seg).trim().split(/\s+/);
+ return words.map((w, i) => tcWord(w, i === 0, i === words.length - 1)).join(' ');
+}
+function titleCase(title) {
+ // split on "|" — title-case ONLY the first segment (pattern+color);
+ // type + brand segments are already canonical, but still entity-decode them.
+ const parts = title.split('|').map(s => s.trim());
+ if (parts.length === 0) return title;
+ parts[0] = titleCaseSegment(parts[0]);
+ for (let i = 1; i < parts.length; i++) parts[i] = decodeEntities(parts[i]);
+ return parts.join(' | ');
+}
+
+// ---- shopify api ----
+function gql(query, variables) {
+ return new Promise(res => {
+ const data = JSON.stringify({ query, variables });
+ const req = https.request({ host: STORE, path: `/admin/api/${API}/graphql.json`, method: 'POST',
+ headers: { 'X-Shopify-Access-Token': TOKEN, 'Content-Type': 'application/json', 'Content-Length': Buffer.byteLength(data) } },
+ r => { let d = ''; r.on('data', c => d += c); r.on('end', () => { try { res({ status: r.statusCode, json: JSON.parse(d) }); } catch { res({ status: r.statusCode, raw: d.slice(0, 400) }); } }); });
+ req.on('error', e => res({ status: 0, err: e.message }));
+ req.write(data); req.end();
+ });
+}
+async function gqlRetry(query, variables) {
+ for (let a = 0; a < 5; a++) {
+ const r = await gql(query, variables);
+ const throttled = r.json && r.json.errors && JSON.stringify(r.json.errors).includes('THROTTLED');
+ if (r.status === 429 || throttled) { await sleep(2000 * (a + 1)); continue; }
+ await sleep(550);
+ return r;
+ }
+ return { status: 429, raw: 'throttled after retries' };
+}
+
+// ---- dw_unified cost lookup ----
+function fetchCosts(gids) {
+ // returns map shopify_id(numeric) -> {cost, dw_sku, title}
+ const ids = gids.map(numId).filter(x => /^\d+$/.test(x));
+ if (!ids.length) return {};
+ const inList = ids.map(x => `'${x}'`).join(',');
+ const sql = `SELECT shopify_id, cost, dw_sku, title FROM shopify_products WHERE shopify_id IN (${inList});`;
+ const out = execFileSync('psql', ['-At', '-F', '\t', '-d', 'dw_unified', '-c', sql], { encoding: 'utf8', maxBuffer: 1 << 28 }).trim();
+ const map = {};
+ if (out) for (const line of out.split('\n')) {
+ const [sid, cost, dw_sku, title] = line.split('\t');
+ map[sid] = { cost: cost === '' ? null : parseFloat(cost), dw_sku, title };
+ }
+ return map;
+}
+
+// ---- step 0+1: build worklist ----
+async function buildWorklist() {
+ const since = new Date(Date.now() - WINDOW_HOURS * 3600 * 1000).toISOString();
+ console.log(`window: created_at > ${since} (${WINDOW_HOURS}h)`);
+ const q = `query($q:String!,$after:String){
+ products(first:100,query:$q,after:$after,sortKey:CREATED_AT,reverse:true){
+ pageInfo{hasNextPage endCursor}
+ edges{node{
+ id title vendor createdAt
+ costMf: metafield(namespace:"custom",key:"cost"){value}
+ variants(first:50){edges{node{id sku price}}}
+ }}
+ }}`;
+ let after = null, pages = 0;
+ const nodes = [];
+ do {
+ const r = await gqlRetry(q, { q: `created_at:>${since}`, after });
+ if (r.status !== 200 || !r.json || !r.json.data) { console.error('FETCH FAIL', r.status, r.raw || JSON.stringify(r.json).slice(0, 300)); process.exit(1); }
+ const p = r.json.data.products;
+ for (const e of p.edges) nodes.push(e.node);
+ after = p.pageInfo.hasNextPage ? p.pageInfo.endCursor : null;
+ pages++;
+ process.stdout.write(`\r fetched ${nodes.length} products (page ${pages})…`);
+ } while (after && pages < 50);
+ console.log('');
+
+ // batch cost lookup
+ const costMap = {};
+ for (let i = 0; i < nodes.length; i += 200) {
+ Object.assign(costMap, fetchCosts(nodes.slice(i, i + 200).map(n => n.id)));
+ }
+
+ const work = []; // ALL 924 — carries title work for every product
+ const exceptions = []; // subset with no usable cost (price work blocked)
+ for (const n of nodes) {
+ const nid = numId(n.id);
+ const rec = costMap[nid] || {};
+ const cost = rec.cost;
+ const newTitle = titleCase(n.title);
+ const priceable = !(cost == null || cost <= 0);
+ const base = { gid: n.id, nid, vendor: n.vendor, dw_sku: rec.dw_sku || null,
+ old_title: n.title, new_title: newTitle, title_changed: newTitle !== n.title,
+ cost: priceable ? cost : null, priceable,
+ retail: priceable ? RETAIL(cost) : null,
+ variants: n.variants.edges.map(v => ({ gid: v.node.id, sku: v.node.sku, price: v.node.price })) };
+ work.push(base);
+ if (!priceable) exceptions.push(base);
+ }
+
+ fs.writeFileSync(WORKLIST, work.map(w => JSON.stringify(w)).join('\n') + '\n');
+ const exHeader = 'nid,dw_sku,vendor,cost,old_title\n';
+ fs.writeFileSync(EXCEPTIONS, exHeader + exceptions.map(e =>
+ `${e.nid},${e.dw_sku || ''},"${(e.vendor || '').replace(/"/g, '""')}",${e.cost == null ? 'NULL' : e.cost},"${e.old_title.replace(/"/g, '""')}"`).join('\n') + '\n');
+
+ const priceable = work.filter(w => w.priceable);
+ const tc = work.filter(w => w.title_changed).length;
+ console.log(`\nWORKLIST: ${work.length} products -> ${WORKLIST}`);
+ console.log(` title changes: ${tc}/${work.length}`);
+ console.log(` priceable (real cost): ${priceable.length}`);
+ console.log(` NOT priceable (no cost): ${exceptions.length} -> ${EXCEPTIONS}`);
+ console.log('\nsample title changes (first 6):');
+ for (const w of work.filter(w => w.title_changed).slice(0, 6)) console.log(` ${w.old_title.slice(0, 40)} => ${w.new_title.slice(0, 50)}`);
+ if (priceable.length) { console.log('\nsample priceable (first 6):'); for (const w of priceable.slice(0, 6)) console.log(` cost $${w.cost} -> retail $${w.retail} | ${w.new_title.slice(0, 44)}`); }
+ return { work, exceptions };
+}
+
+// ---- step 2: log to dw_unified FIRST ----
+function logToDb(work) {
+ console.log(`\n[--log-db] writing ${work.length} rows to dw_unified.shopify_products…`);
+ // build a VALUES list and a single UPDATE FROM for speed + atomicity
+ const rows = work.map(w => `('${w.nid}', ${w.retail}, $$${w.new_title.replace(/\$/g, '')}$$)`).join(',\n');
+ const sql = `
+ UPDATE shopify_products sp
+ SET retail_price = v.retail, price = v.retail, title = v.title
+ FROM (VALUES ${rows}) AS v(shopify_id, retail, title)
+ WHERE sp.shopify_id = v.shopify_id;`;
+ const tmp = `${os.tmpdir()}/fix72-logdb.sql`;
+ fs.writeFileSync(tmp, sql);
+ const out = execFileSync('psql', ['-v', 'ON_ERROR_STOP=1', '-d', 'dw_unified', '-f', tmp], { encoding: 'utf8', maxBuffer: 1 << 28 });
+ console.log(' ' + out.trim());
+}
+
+const mUpdate = `mutation($input:ProductInput!){productUpdate(input:$input){product{id title} userErrors{field message}}}`;
+const mMeta = `mutation($mf:[MetafieldsSetInput!]!){metafieldsSet(metafields:$mf){userErrors{field message}}}`;
+const mVariant = `mutation($input:ProductVariantsBulkInput!,$pid:ID!){productVariantsBulkUpdate(productId:$pid,variants:[$input]){userErrors{field message}}}`;
+const errs = a => { const v = Object.values(a)[0]; return Array.isArray(v) && v.length ? v : null; };
+
+// ---- titles pass: ALL products (cost-independent, reversible) ----
+async function pushTitles(work) {
+ const todo = work.filter(w => w.title_changed);
+ console.log(`\n[--titles${COMMIT ? ' --commit' : ' DRY-RUN'}] ${todo.length} title changes (of ${work.length})`);
+ if (!COMMIT) { todo.slice(0, 10).forEach(w => console.log(` would: ${w.old_title.slice(0,42)} => ${w.new_title.slice(0,52)}`)); console.log(` …(${todo.length} total)`); return; }
+ let ok = 0, fail = 0; const manifest = [];
+ for (const w of todo) {
+ const r = await gqlRetry(mUpdate, { input: { id: w.gid, title: w.new_title } });
+ const ue = r.json?.data?.productUpdate?.userErrors;
+ if (ue && ue.length) { fail++; console.log(` ✗ ${w.nid} ${JSON.stringify(ue).slice(0,140)}`); }
+ else { ok++; if (ok % 50 === 0) process.stdout.write(`\r titled ${ok}/${todo.length}…`); }
+ manifest.push({ nid: w.nid, new_title: w.new_title, err: ue && ue.length ? ue : null });
+ }
+ fs.writeFileSync(`${OUTDIR}/titles-manifest-72h.json`, JSON.stringify(manifest, null, 1));
+ console.log(`\nTITLES done. ok=${ok} fail=${fail}`);
+}
+
+// ---- price pass: priceable only (cost present) ----
+async function push(work) {
+ const todo = work.filter(w => w.priceable);
+ console.log(`\n[--push${COMMIT ? ' --commit' : ' DRY-RUN'}] ${todo.length} priceable products (price_updated_at=${TODAY})`);
+ const manifest = []; let ok = 0, fail = 0;
+ for (const w of todo) {
+ if (!COMMIT) { manifest.push({ nid: w.nid, would: { cost_mf: w.cost, retail: w.retail, variants: w.variants.length } }); continue; }
+ const actions = [];
+ // custom.cost + custom.price_updated_at (30-day audit stamp)
+ const rm = await gqlRetry(mMeta, { mf: [
+ { ownerId: w.gid, namespace: 'custom', key: 'cost', type: 'number_decimal', value: String(w.cost) },
+ { ownerId: w.gid, namespace: 'custom', key: 'price_updated_at', type: 'date', value: TODAY },
+ ] });
+ actions.push({ meta: rm.json?.data?.metafieldsSet?.userErrors || 'ok' });
+ // variant price (per-roll)
+ for (const v of w.variants) {
+ const rv = await gqlRetry(mVariant, { pid: w.gid, input: { id: v.gid, price: String(w.retail) } });
+ actions.push({ variant: v.sku, price: rv.json?.data?.productVariantsBulkUpdate?.userErrors || w.retail });
+ }
+ const bad = actions.some(errs);
+ if (bad) { fail++; console.log(` ✗ ${w.nid} ${JSON.stringify(actions).slice(0, 160)}`); }
+ else { ok++; if (ok % 25 === 0) process.stdout.write(`\r priced ${ok}…`); }
+ manifest.push({ nid: w.nid, actions });
+ }
+ fs.writeFileSync(MANIFEST, JSON.stringify(manifest, null, 1));
+ console.log(`\nPRICE PUSH done. ok=${ok} fail=${fail} manifest=${MANIFEST}`);
+}
+
+(async () => {
+ if (!TOKEN) { console.error('no SHOPIFY_ADMIN_TOKEN'); process.exit(1); }
+ let work;
+ if ((PUSH || TITLES) && fs.existsSync(WORKLIST)) {
+ work = fs.readFileSync(WORKLIST, 'utf8').trim().split('\n').filter(Boolean).map(l => JSON.parse(l));
+ console.log(`loaded ${work.length} from existing worklist`);
+ } else {
+ ({ work } = await buildWorklist());
+ }
+ if (LOG_DB) logToDb(work);
+ if (TITLES) await pushTitles(work);
+ if (PUSH) await push(work);
+})();
← 426995c5 PJ scraper pass 2: fix detail--row regex (ng-if attrs hid Fi
·
back to Designer Wallcoverings
·
Add gated daily cadence importer (dry-run first) 6bec4bde →