← back to Designer Wallcoverings
Schumacher pillow reprice script + verified live pilot (3 pillows: DWPIL-5054/2054/2064 -> priced Pillow variant, sample dropped, mfr_sku fixed)
f208ebe6cc82bc95eaad63612a564c33ac46832f · 2026-07-10 17:25:23 -0700 · Steve Abrams
Files touched
A shopify/scripts/schu-pillow-reprice.js
Diff
commit f208ebe6cc82bc95eaad63612a564c33ac46832f
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Fri Jul 10 17:25:23 2026 -0700
Schumacher pillow reprice script + verified live pilot (3 pillows: DWPIL-5054/2054/2064 -> priced Pillow variant, sample dropped, mfr_sku fixed)
---
shopify/scripts/schu-pillow-reprice.js | 286 +++++++++++++++++++++++++++++++++
1 file changed, 286 insertions(+)
diff --git a/shopify/scripts/schu-pillow-reprice.js b/shopify/scripts/schu-pillow-reprice.js
new file mode 100644
index 00000000..197a103b
--- /dev/null
+++ b/shopify/scripts/schu-pillow-reprice.js
@@ -0,0 +1,286 @@
+#!/usr/bin/env node
+/**
+ * schu-pillow-reprice.js (2026-07-10) — owner: vp-dw-commerce — Steve APPROVED (LIVE)
+ *
+ * Resolves the 471 ACTIVE sample-only Schumacher DWPIL pillows.
+ * - 349 REPRICEABLE: image SO code -> schumacher_catalog priced row.
+ * Convert the single "Size: Sample" @ $4.25 variant INTO a real priced
+ * finished-good "Pillow" variant at DW retail (cost/0.65/0.85):
+ * * rename the option value Sample -> Pillow (single-value option)
+ * * price = dw_retail
+ * * SKU = base (drop the -Sample suffix) via inventoryItem.sku
+ * * inventoryPolicy = CONTINUE, stop inventory tracking (made-to-order)
+ * DROP the sample (DTD verdict B 2026-07-10: honor Steve's explicit
+ * "no samples for pillows" directive; the 89 legacy priced pillows are
+ * legacy exceptions to normalize later, not the governing standard).
+ * Fix custom.manufacturer_sku + dwc.manufacturer_sku -> real SO code.
+ * Keep product ACTIVE, title/images/specs untouched. Remove
+ * Sample-Only-Pillow + Needs-Price tags. NEVER remove display_variant.
+ * - 119 DISCONTINUED (404 at Schumacher, SO codes in schu-pillow-discontinued-118.txt):
+ * ARCHIVE (DW rule: discontinued = archive).
+ * - 3 NO SO code: leave as-is, tag Needs-Mfr-SKU.
+ *
+ * ORDER OF OPS (PostgreSQL BEFORE Shopify): each successful Shopify write is
+ * mirrored back into dw_unified.shopify_products (price, has_product_variant,
+ * has_sample_variant, mfr_sku, status).
+ *
+ * Reuses the proven gql/gqlR client from bucketB-schumacher-roll-variants.js.
+ *
+ * node schu-pillow-reprice.js --pilot # DRY-RUN, pilot 3 only
+ * node schu-pillow-reprice.js --pilot --apply # LIVE, pilot 3 only
+ * node schu-pillow-reprice.js --apply # LIVE, full batch
+ * node schu-pillow-reprice.js --apply --report data/schu-pillow-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 PILOT = args.includes('--pilot');
+const strArg = (flag, dflt) => { const i = args.indexOf(flag); return i >= 0 ? args[i + 1] : dflt; };
+const numArg = (flag, dflt) => { const i = args.indexOf(flag); return i >= 0 ? parseInt(args[i + 1], 10) : dflt; };
+const REPORT = strArg('--report', null);
+const BATCH_SIZE = numArg('--batch-size', 40);
+const BATCH_GAP_MS = numArg('--batch-gap', 90000); // Steve rule: >=90s between bulk batches
+
+// pilot targets: the Steve-linked product + 2 more repriceable
+const PILOT_IDS = ['6609185538099']; // dwpil-5023 Mixco Pillow Jewel; +2 auto-picked below
+
+const DISCO_FILE = path.join(os.homedir(), '.claude/yolo-queue/pending-approval/schu-pillow-discontinued-118.txt');
+
+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; }
+
+function psql(sql) {
+ return execFileSync('psql', ['-d', 'dw_unified', '-t', '-A', '-F', '\t', '-c', sql],
+ { input: '', encoding: 'utf8', maxBuffer: 1 << 26 }).trim();
+}
+function psqlIn(sql) { // for writes
+ return execFileSync('psql', ['-d', 'dw_unified', '-v', 'ON_ERROR_STOP=1', '-q', '-c', sql],
+ { input: '', encoding: 'utf8', maxBuffer: 1 << 24 }).trim();
+}
+function sqlLit(s) { return "'" + String(s).replace(/'/g, "''") + "'"; }
+// shopify_id in the mirror is stored as a full gid; normalize both ways.
+function numId(v) { return String(v || '').replace(/^gid:\/\/shopify\/Product\//, ''); }
+function gidOf(v) { const n = numId(v); return `gid://shopify/Product/${n}`; }
+
+// ---- Shopify GraphQL (from bucketB) ----------------------------------------
+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();
+ });
+}
+function _isThrottled(errs) {
+ if (!errs) return false;
+ const arr = Array.isArray(errs) ? errs : [errs];
+ return arr.some(e => /throttl/i.test(typeof e === 'string' ? e : JSON.stringify(e)));
+}
+async function gqlR(q, v, tries = 5) {
+ let last = null;
+ for (let i = 0; i < tries; i++) {
+ let r;
+ try { r = await gql(q, v); }
+ catch (e) {
+ last = { errors: [{ message: 'gql-reject', detail: typeof e === 'string' ? e.slice(0, 200) : String(e && e.message || e) }] };
+ await sleep(1500 * (i + 1)); continue;
+ }
+ last = r;
+ if (r && !r.errors) return r;
+ if (!_isThrottled(r && r.errors)) return r;
+ await sleep(1500 * (i + 1));
+ }
+ try { return await gql(q, v); } catch (e) { return last || { errors: [{ message: 'gql-exhausted' }] }; }
+}
+
+const Q_PRODUCT = `query($id:ID!){product(id:$id){id title status handle tags
+ options{id name values optionValues{id name}}
+ variants(first:30){nodes{id title price inventoryPolicy sku selectedOptions{name value} inventoryItem{id tracked}}}}}`;
+const M_VAR_UPDATE = `mutation($pid:ID!,$variants:[ProductVariantsBulkInput!]!){
+ productVariantsBulkUpdate(productId:$pid,variants:$variants){
+ productVariants{id sku price inventoryPolicy} userErrors{field message}}}`;
+const M_INV_TRACK = `mutation($id:ID!,$input:InventoryItemUpdateInput!){
+ inventoryItemUpdate(id:$id,input:$input){inventoryItem{id tracked} userErrors{field message}}}`;
+const M_MF = `mutation($mf:[MetafieldsSetInput!]!){metafieldsSet(metafields:$mf){metafields{id} userErrors{field message}}}`;
+const M_TAGS_ADD = `mutation($id:ID!,$tags:[String!]!){tagsAdd(id:$id,tags:$tags){userErrors{field message}}}`;
+const M_TAGS_REMOVE = `mutation($id:ID!,$tags:[String!]!){tagsRemove(id:$id,tags:$tags){userErrors{field message}}}`;
+const M_ARCHIVE = `mutation($input:ProductInput!){productUpdate(input:$input){product{id status} userErrors{field message}}}`;
+
+// ---- worklist from PG ------------------------------------------------------
+// Buckets computed identically to the memo's SQL.
+function loadBuckets() {
+ const disco = fs.readFileSync(DISCO_FILE, 'utf8').split('\n').map(s => s.trim()).filter(Boolean);
+ const discoSet = new Set(disco);
+
+ // repriceable: join to priced catalog
+ const rep = psql(`
+ SELECT sp.shopify_id, sp.handle, sp.dw_sku, sp.sku, sp.variant_id, sp.title,
+ (regexp_match(sp.image_url,'SO[0-9]+'))[1] AS so_code,
+ sc.retail_price AS net_cost,
+ round((sc.retail_price/0.65/0.85)::numeric,2) AS dw_retail
+ FROM shopify_products sp
+ JOIN schumacher_catalog sc ON sc.mfr_sku=(regexp_match(sp.image_url,'SO[0-9]+'))[1]
+ AND sc.product_type='pillow' AND sc.retail_price>0
+ WHERE (sp.dw_sku ILIKE 'DWPIL%' OR sp.sku ILIKE 'DWPIL%') AND sp.status='ACTIVE'
+ AND sp.has_sample_variant AND NOT sp.has_product_variant
+ ORDER BY sp.dw_sku;
+ `).split('\n').filter(Boolean).map(l => {
+ const c = l.split('\t');
+ return { shopify_id: c[0], handle: c[1], dw_sku: c[2], sku: c[3], variant_id: c[4],
+ title: c[5], so_code: c[6], net_cost: parseFloat(c[7]), dw_retail: parseFloat(c[8]) };
+ });
+
+ // all target rows (for disco + no-so classification)
+ const all = psql(`
+ SELECT sp.shopify_id, sp.handle, sp.dw_sku, sp.sku, sp.title,
+ (regexp_match(sp.image_url,'SO[0-9]+'))[1] AS so_code
+ FROM shopify_products sp
+ WHERE (sp.dw_sku ILIKE 'DWPIL%' OR sp.sku ILIKE 'DWPIL%') AND sp.status='ACTIVE'
+ AND sp.has_sample_variant AND NOT sp.has_product_variant;
+ `).split('\n').filter(Boolean).map(l => {
+ const c = l.split('\t');
+ return { shopify_id: c[0], handle: c[1], dw_sku: c[2], sku: c[3], title: c[4], so_code: c[5] || null };
+ });
+
+ const repIds = new Set(rep.map(r => r.shopify_id));
+ const discoRows = all.filter(r => r.so_code && discoSet.has(r.so_code) && !repIds.has(r.shopify_id));
+ const noso = all.filter(r => !r.so_code);
+ return { rep, disco: discoRows, noso, all };
+}
+
+// derive the base (non-sample) SKU from the sample SKU
+function baseSku(sampleSku) {
+ return String(sampleSku || '').replace(/-Sample$/i, '');
+}
+
+// ---- REPRICE one pillow ----------------------------------------------------
+async function repriceOne(r, log) {
+ const pid = gidOf(r.shopify_id);
+ const pr = await gqlR(Q_PRODUCT, { id: pid });
+ const p = pr.data && pr.data.product;
+ if (!p) { log.push({ sku: r.dw_sku, action: 'reprice', status: 'ERR', reason: 'product not found', detail: JSON.stringify(pr.errors || {}) }); return 'err'; }
+ if (p.status !== 'ACTIVE') { log.push({ sku: r.dw_sku, action: 'reprice', status: 'SKIP', reason: `status=${p.status}` }); return 'skip'; }
+
+ // Idempotency: if a non-sample priced variant already exists, skip the variant write.
+ const vars = p.variants.nodes;
+ const sampleVar = vars.find(v => /-Sample$/i.test(v.sku) || v.selectedOptions.some(o => /sample/i.test(o.value)));
+ const nonSample = vars.find(v => v !== sampleVar && parseFloat(v.price) > 4.25);
+ const targetSku = baseSku(r.sku || (sampleVar && sampleVar.sku) || `${r.dw_sku}`);
+ const optName = (p.options[0] && p.options[0].name) || 'Title';
+
+ if (!sampleVar) { log.push({ sku: r.dw_sku, action: 'reprice', status: 'SKIP', reason: 'no sample variant found' }); return 'skip'; }
+ if (nonSample) { log.push({ sku: r.dw_sku, action: 'reprice', status: 'SKIP', reason: 'already has priced variant', existing: nonSample.sku }); return 'skip'; }
+
+ const price = round2(r.dw_retail);
+ if (!(price > 0)) { log.push({ sku: r.dw_sku, action: 'reprice', status: 'ERR', reason: 'no valid price' }); return 'err'; }
+
+ const plan = {
+ variant_id: sampleVar.id, from_sku: sampleVar.sku, to_sku: targetSku,
+ from_price: sampleVar.price, to_price: price, so_code: r.so_code,
+ from_option: sampleVar.selectedOptions.map(o => `${o.name}:${o.value}`).join(','),
+ };
+
+ if (!APPLY) { log.push({ sku: r.dw_sku, action: 'reprice', status: 'DRY', plan }); return 'dry'; }
+
+ // 1) PG FIRST — write price + topology to the mirror before Shopify.
+ psqlIn(`UPDATE shopify_products
+ SET retail_price=${price}, price=${price},
+ has_product_variant=true, has_sample_variant=false,
+ mfr_sku=${sqlLit(r.so_code)}
+ WHERE shopify_id=${sqlLit(r.shopify_id)};`);
+
+ // 2) Update the existing variant: rename option value Sample->Pillow, set price,
+ // SKU (inventoryItem.sku), inventoryPolicy=CONTINUE.
+ const up = await gqlR(M_VAR_UPDATE, { pid, variants: [{
+ id: sampleVar.id,
+ price: String(price),
+ inventoryPolicy: 'CONTINUE',
+ inventoryItem: { sku: targetSku, tracked: false },
+ optionValues: [{ optionName: optName, name: 'Pillow' }],
+ }] });
+ const ue = (up.data && up.data.productVariantsBulkUpdate && up.data.productVariantsBulkUpdate.userErrors) || up.errors || [];
+ if (ue.length) { log.push({ sku: r.dw_sku, action: 'reprice', status: 'ERR', reason: 'variant update', detail: JSON.stringify(ue) }); return 'err'; }
+
+ // 3) Ensure inventory tracking off (belt+suspenders; inventoryItem.tracked above should do it)
+ if (sampleVar.inventoryItem && sampleVar.inventoryItem.tracked) {
+ await gqlR(M_INV_TRACK, { id: sampleVar.inventoryItem.id, input: { tracked: false } });
+ }
+
+ // 4) Fix corrupted mfr_sku metafields -> real SO code.
+ const mf = await gqlR(M_MF, { mf: [
+ { ownerId: pid, namespace: 'custom', key: 'manufacturer_sku', type: 'single_line_text_field', value: r.so_code },
+ { ownerId: pid, namespace: 'dwc', key: 'manufacturer_sku', type: 'single_line_text_field', value: r.so_code },
+ ] });
+ const me = (mf.data && mf.data.metafieldsSet && mf.data.metafieldsSet.userErrors) || [];
+ if (me.length) log.push({ sku: r.dw_sku, action: 'reprice', status: 'WARN', reason: 'metafield', detail: JSON.stringify(me) });
+
+ // 5) Remove sample-only / needs-price tags (NEVER touch display_variant).
+ await gqlR(M_TAGS_REMOVE, { id: pid, tags: ['Sample-Only-Pillow', 'Needs-Price'] });
+
+ log.push({ sku: r.dw_sku, action: 'reprice', status: 'OK', plan });
+ return 'ok';
+}
+
+// ---- ARCHIVE one discontinued pillow ---------------------------------------
+async function archiveOne(r, log) {
+ const pid = gidOf(r.shopify_id);
+ if (!APPLY) { log.push({ sku: r.dw_sku, action: 'archive', status: 'DRY', so_code: r.so_code }); return 'dry'; }
+ // PG first
+ psqlIn(`UPDATE shopify_products SET status='ARCHIVED' WHERE shopify_id=${sqlLit(r.shopify_id)};`);
+ const ar = await gqlR(M_ARCHIVE, { input: { id: pid, status: 'ARCHIVED' } });
+ const ue = (ar.data && ar.data.productUpdate && ar.data.productUpdate.userErrors) || ar.errors || [];
+ if (ue.length) { log.push({ sku: r.dw_sku, action: 'archive', status: 'ERR', detail: JSON.stringify(ue) }); return 'err'; }
+ log.push({ sku: r.dw_sku, action: 'archive', status: 'OK', so_code: r.so_code });
+ return 'ok';
+}
+
+// ---- FLAG the 3 no-SO pillows ----------------------------------------------
+async function flagOne(r, log) {
+ const pid = gidOf(r.shopify_id);
+ if (!APPLY) { log.push({ sku: r.dw_sku, action: 'flag', status: 'DRY' }); return 'dry'; }
+ await gqlR(M_TAGS_ADD, { id: pid, tags: ['Needs-Mfr-SKU'] });
+ log.push({ sku: r.dw_sku, action: 'flag', status: 'OK' });
+ return 'ok';
+}
+
+(async () => {
+ const { rep, disco, noso } = loadBuckets();
+ console.error(`[buckets] repriceable=${rep.length} discontinued=${disco.length} no-so=${noso.length} (apply=${APPLY} pilot=${PILOT})`);
+
+ const log = [];
+ const tally = { reprice: {}, archive: {}, flag: {} };
+ const bump = (k, s) => { tally[k][s] = (tally[k][s] || 0) + 1; };
+
+ if (PILOT) {
+ // pilot: the Steve-linked product + 2 more repriceable
+ const pilotSet = [];
+ const linked = rep.find(r => PILOT_IDS.includes(numId(r.shopify_id)));
+ if (linked) pilotSet.push(linked);
+ for (const r of rep) { if (pilotSet.length >= 3) break; if (!pilotSet.includes(r)) pilotSet.push(r); }
+ for (const r of pilotSet) { bump('reprice', await repriceOne(r, log)); await sleep(500); }
+ } else {
+ // FULL: reprice all, batched with 90s gaps; then archive; then flag.
+ let n = 0;
+ for (const r of rep) {
+ bump('reprice', await repriceOne(r, log)); await sleep(400);
+ if (++n % BATCH_SIZE === 0 && n < rep.length) { console.error(`[batch] ${n}/${rep.length} repriced; sleeping ${BATCH_GAP_MS/1000}s`); await sleep(BATCH_GAP_MS); }
+ }
+ for (const r of disco) { bump('archive', await archiveOne(r, log)); await sleep(300); }
+ for (const r of noso) { bump('flag', await flagOne(r, log)); await sleep(300); }
+ }
+
+ console.error('[tally]', JSON.stringify(tally));
+ const out = { at: new Date().toISOString(), apply: APPLY, pilot: PILOT, tally,
+ counts: { repriceable: rep.length, discontinued: disco.length, no_so: noso.length }, log };
+ if (REPORT) { fs.mkdirSync(path.dirname(REPORT), { recursive: true }); fs.writeFileSync(REPORT, JSON.stringify(out, null, 2)); console.error(`[report] ${REPORT}`); }
+ else process.stdout.write(JSON.stringify(out, null, 2) + '\n');
+})();
← 0259e13a auto-save: 2026-07-10T17:09:33 (4 files) — pending-approval/
·
back to Designer Wallcoverings
·
Schumacher pillow reprice full batch report: 349 repriced, 1 a28da72d →