← back to Designerwallcoverings
TK-11061: reprice ~457 real-roll variants with leaked $4.25 sample price
37330673905a5c8bdf5d42a0274183564bc8dead · 2026-09-04 12:42:39 -0700 · Steve Abrams
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01STupyqBoLfTPHaTr1Gi42b
Files touched
A shopify/scripts/fixA-sample-leak-reprice.mjs
Diff
commit 37330673905a5c8bdf5d42a0274183564bc8dead
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Fri Sep 4 12:42:39 2026 -0700
TK-11061: reprice ~457 real-roll variants with leaked $4.25 sample price
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01STupyqBoLfTPHaTr1Gi42b
---
shopify/scripts/fixA-sample-leak-reprice.mjs | 199 +++++++++++++++++++++++++++
1 file changed, 199 insertions(+)
diff --git a/shopify/scripts/fixA-sample-leak-reprice.mjs b/shopify/scripts/fixA-sample-leak-reprice.mjs
new file mode 100644
index 0000000..0435357
--- /dev/null
+++ b/shopify/scripts/fixA-sample-leak-reprice.mjs
@@ -0,0 +1,199 @@
+#!/usr/bin/env node
+/**
+ * TK-11061 Fix A — reprice the ~457 GENUINELY mispriced real-roll variants
+ * (real-roll products on Google whose NON-sample variant is priced <= $4.25 —
+ * the sample price leaked onto the roll, mostly Fentucci Naturals at $0).
+ *
+ * Uses the AUTHORITATIVE variant-based classifier from google-merchant-agent/check.mjs
+ * (isSampleVariant on SKU suffix, NOT price). Scans LIVE Shopify (authoritative,
+ * customer-facing). Lean scan: first:150, NO expensive per-product metafield fetch
+ * (matches check.mjs query cost so it finishes in ~7-8 min, not throttle-death).
+ *
+ * Stages:
+ * --scan (default) : LIVE scan -> write leak set json. NOTHING written to Shopify.
+ * --resolve <found> : batched per-vendor price resolution over a found.json -> resolved.json + restore-map
+ * --apply <resolved>: gated Shopify price write from a resolved.json. Records restore map + ledger.
+ */
+import fs from 'node:fs';
+import { execSync } from 'node:child_process';
+
+const MODE = process.argv.includes('--apply') ? 'apply' : process.argv.includes('--resolve') ? 'resolve' : 'scan';
+const argAfter = f => { const i = process.argv.indexOf(f); return i >= 0 ? process.argv[i + 1] : null; };
+const LIMIT = (() => { const v = argAfter('--limit'); return v ? parseInt(v) : Infinity; })();
+const T = (fs.readFileSync(process.env.HOME + '/Projects/secrets-manager/.env', 'utf8').match(/^SHOPIFY_ADMIN_TOKEN=(.+)$/m) || [])[1];
+const PSQL = '/opt/homebrew/Cellar/postgresql@14/14.23/bin/psql';
+const SHOP = 'designer-laboratory-sandbox.myshopify.com';
+const API = '2024-10';
+const GOOG_PUB = 'gid://shopify/Publication/29646651457';
+const DIR = '/Users/macstudio3/Projects/designerwallcoverings/shopify/scripts/data/fixA-sample-leak';
+fs.mkdirSync(DIR, { recursive: true });
+const STAMP = new Date().toISOString().replace(/[:.]/g, '-');
+
+const sleep = ms => new Promise(r => setTimeout(r, ms));
+async function gql(query, variables) {
+ for (let a = 0; a < 8; a++) {
+ try {
+ const r = await fetch(`https://${SHOP}/admin/api/${API}/graphql.json`, { method: 'POST',
+ headers: { 'X-Shopify-Access-Token': T, 'Content-Type': 'application/json' },
+ body: JSON.stringify({ query, variables }), signal: AbortSignal.timeout(25000) });
+ if (r.status === 429 || r.status >= 500) { await sleep(2500 * (a + 1)); continue; }
+ const j = await r.json();
+ if (j.errors && JSON.stringify(j.errors).match(/THROTTLED/)) { await sleep(2500 * (a + 1)); continue; }
+ const th = j?.extensions?.cost?.throttleStatus; if (th && th.currentlyAvailable < 500) await sleep(900);
+ return j;
+ } catch (e) { await sleep(2000 * (a + 1)); }
+ }
+ throw new Error('gql retries exhausted');
+}
+
+// Authoritative sample-variant classifier — verbatim from google-merchant-agent/check.mjs (TK-10702).
+const isSampleVariant = v => /-(sample|memo|swatch)\b|(^|[^a-z])(sample|memo|swatch)([^a-z]|$)/i
+ .test((v.sku || '') + ' ' + (v.title || '') + ' ' + (v.selectedOptions || []).map(o => o.value).join(' '));
+
+// ─────────────────────────────── SCAN ───────────────────────────────
+async function scan() {
+ const leaks = [];
+ let cur = null, active = 0, onGoogle = 0, pages = 0;
+ const Q = `query($c:String){products(first:150,after:$c,query:"status:active"){pageInfo{hasNextPage endCursor} nodes{id title vendor onG:publishedOnPublication(publicationId:"${GOOG_PUB}") variants(first:60){nodes{id sku title price selectedOptions{value}}}}}}`;
+ do {
+ const d = await gql(Q, { c: cur });
+ const pg = d?.data?.products;
+ if (!pg) { console.error('ERR', JSON.stringify(d).slice(0, 400)); break; }
+ for (const p of pg.nodes) {
+ active++;
+ if (p.onG) onGoogle++;
+ if (!p.onG) continue;
+ const nonSample = p.variants.nodes.filter(v => !isSampleVariant(v) && !isNaN(parseFloat(v.price)));
+ if (nonSample.length === 0) continue; // sample-only, legit
+ const minP = Math.min(...nonSample.map(v => parseFloat(v.price)));
+ if (minP <= 4.25) {
+ const bad = nonSample.filter(v => parseFloat(v.price) <= 4.25);
+ leaks.push({ product_id: p.id, title: p.title, vendor: p.vendor,
+ variants: bad.map(v => ({ variant_id: v.id, sku: v.sku, variantTitle: v.title, price: parseFloat(v.price) })) });
+ }
+ }
+ pages++;
+ if (pages % 50 === 0) console.error(` ...scanned ${active} active, ${leaks.length} leak-products so far`);
+ cur = pg.pageInfo.hasNextPage ? pg.pageInfo.endCursor : null;
+ } while (cur);
+ const byVendor = {};
+ for (const l of leaks) byVendor[l.vendor] = (byVendor[l.vendor] || 0) + 1;
+ const out = { ts: STAMP, ticket: 'TK-11061-fixA', stage: 'scan', scanned_active: active, on_google: onGoogle,
+ leak_products: leaks.length, leak_variants: leaks.reduce((s, l) => s + l.variants.length, 0), by_vendor: byVendor, leaks };
+ const f = `${DIR}/fixA-found-${STAMP}.json`;
+ fs.writeFileSync(f, JSON.stringify(out, null, 2));
+ console.log(`SCAN done: active=${active} onGoogle=${onGoogle} leak_products=${leaks.length} leak_variants=${out.leak_variants}`);
+ console.log('by_vendor:', JSON.stringify(byVendor));
+ console.log(`found: ${f}`);
+}
+
+// ─────────────────────────── RESOLVE (batched) ───────────────────────────
+const q = s => (s == null ? '' : String(s).replace(/'/g, "''"));
+function psqlRows(sql) { try { return execSync(`${PSQL} -h /tmp -d dw_unified -tAF$'\\t' -c "${sql.replace(/"/g, '\\"')}"`).toString().trim().split('\n').filter(Boolean).map(r => r.split('\t')); } catch { return []; } }
+const KRAVET_FAMILY = new Set(['Kravet','Kravet Couture','Kravet Design','Kravet Contract','Kravet Basics',
+ 'Lee Jofa','Lee Jofa Modern','Groundworks','Brunschwig & Fils','Cole & Son','GP & J Baker',
+ 'Colefax and Fowler','Colefax & Fowler','Clarke & Clarke','Mulberry','Threads','Baker Lifestyle',
+ 'Andrew Martin','Nicolette Mayer','Aerin','Barclay Butera','Thom Filicia'].map(s => s.toLowerCase()));
+
+function resolve(foundPath) {
+ const found = JSON.parse(fs.readFileSync(foundPath, 'utf8'));
+ // Build a base-SKU list (strip -Sample/-unit) for every leaked variant.
+ const items = [];
+ for (const l of found.leaks) for (const v of l.variants) {
+ const base = (v.sku || '').replace(/-(sample|memo|swatch|unit|\d+-sample)$/i, '').replace(/-\d+$/,'').trim();
+ items.push({ product_id: l.product_id, vendor: l.vendor, variant_id: v.variant_id, sku: v.sku, base, price: v.price, correct_price: null, source: null });
+ }
+ // Catalog metadata once.
+ const CATALOG_TABLES = psqlRows(`select table_name from information_schema.tables where table_schema='public' and table_name like '%_catalog' order by table_name`).map(r => r[0]);
+ const TABLE_COLS = {};
+ for (const t of CATALOG_TABLES) TABLE_COLS[t] = psqlRows(`select string_agg(column_name,',') from information_schema.columns where table_name='${t}'`)[0][0].split(',');
+
+ // Batched lookups: for each catalog table, one query pulling (key, retail) for all needed SKUs.
+ const allSkus = [...new Set(items.flatMap(i => [i.sku, i.base]).filter(Boolean))];
+ const inList = allSkus.map(s => `'${q(s)}'`).join(',') || "''";
+ // priceMap[sku] = {price, source}
+ const priceMap = {};
+ for (const t of CATALOG_TABLES) {
+ const cols = TABLE_COLS[t];
+ const retailCol = cols.includes('price_retail') ? 'price_retail' : (cols.includes('price') ? 'price' : null);
+ if (!retailCol) continue;
+ for (const keyCol of ['dw_sku', 'sku', 'mfr_sku', 'real_sku']) {
+ if (!cols.includes(keyCol)) continue;
+ const rows = psqlRows(`select ${keyCol}, ${retailCol} from ${t} where ${keyCol} in (${inList}) and ${retailCol} is not null and ${retailCol} > 4.25`);
+ for (const [k, pr] of rows) { if (!priceMap[k]) priceMap[k] = { price: +pr, source: `${t}.${retailCol} via ${keyCol}` }; }
+ }
+ }
+ // Kravet MAP tables (keyed by mfr_sku — we only have dw_sku/base here; try both)
+ const kravetMap = {};
+ const kAuth = psqlRows(`select mfr_sku, new_map from kravet_authoritative_pricing where mfr_sku in (${inList}) and new_map is not null and new_map>4.25`);
+ for (const [k, m] of kAuth) kravetMap[k] = { price: +m, source: 'kravet_authoritative_pricing.new_map' };
+ const kMaster = psqlRows(`select mfr_sku, map_price from kravet_master_price where mfr_sku in (${inList}) and map_price is not null and map_price>4.25`);
+ for (const [k, m] of kMaster) if (!kravetMap[k]) kravetMap[k] = { price: +m, source: 'kravet_master_price.map_price' };
+
+ for (const i of items) {
+ if (KRAVET_FAMILY.has((i.vendor || '').toLowerCase())) {
+ const hit = kravetMap[i.sku] || kravetMap[i.base] || priceMap[i.sku] || priceMap[i.base];
+ if (hit) { i.correct_price = hit.price; i.source = hit.source; } else i.source = 'kravet-no-map-SKIP';
+ } else {
+ const hit = priceMap[i.sku] || priceMap[i.base];
+ if (hit) { i.correct_price = hit.price; i.source = hit.source; } else i.source = 'no-catalog-retail-SKIP';
+ }
+ }
+ const resolved = items.filter(i => i.correct_price != null).length;
+ const rf = `${DIR}/fixA-resolved-${STAMP}.json`;
+ const restore = items.map(i => ({ product_id: i.product_id, variant_id: i.variant_id, sku: i.sku, vendor: i.vendor, prior_price: i.price, intended_new: i.correct_price, source: i.source }));
+ const rmf = `${DIR}/fixA-restore-map-${STAMP}.json`;
+ fs.writeFileSync(rf, JSON.stringify({ ts: STAMP, ticket: 'TK-11061-fixA', stage: 'resolve', total: items.length, resolved, unresolved: items.length - resolved, items }, null, 2));
+ fs.writeFileSync(rmf, JSON.stringify({ ts: STAMP, ticket: 'TK-11061-fixA', note: 'restore map — set price back to prior_price to undo', entries: restore }, null, 2));
+ console.log(`RESOLVE: total_variants=${items.length} resolved=${resolved} unresolved(SKIP)=${items.length - resolved}`);
+ const byVendor = {}; for (const i of items) { byVendor[i.vendor] = byVendor[i.vendor] || { total: 0, resolved: 0 }; byVendor[i.vendor].total++; if (i.correct_price != null) byVendor[i.vendor].resolved++; }
+ console.log('by_vendor(resolved/total):', Object.entries(byVendor).map(([v, o]) => `${v}:${o.resolved}/${o.total}`).join(' '));
+ console.log('SAMPLE old->new:');
+ items.slice(0, 15).forEach(i => console.log(` ${i.vendor} | ${i.sku} | $${i.price} -> ${i.correct_price != null ? '$' + i.correct_price + ' (' + i.source + ')' : 'SKIP (' + i.source + ')'}`));
+ console.log(`resolved: ${rf}`);
+ console.log(`restore-map (pre-written): ${rmf}`);
+}
+
+// ─────────────────────────────── APPLY (gated) ───────────────────────────────
+async function apply(resolvedPath) {
+ const R = JSON.parse(fs.readFileSync(resolvedPath, 'utf8'));
+ const items = R.items.filter(i => i.correct_price != null);
+ // group by product for a single mutation per product
+ const byProduct = {};
+ for (const i of items) (byProduct[i.product_id] = byProduct[i.product_id] || []).push(i);
+ const MUT = `mutation($productId:ID!,$variants:[ProductVariantsBulkInput!]!){productVariantsBulkUpdate(productId:$productId,variants:$variants){productVariants{id price}userErrors{field message}}}`;
+ const restore = [], ledger = [];
+ let fixed = 0, failed = 0, capped = 0;
+ for (const [pid, vs] of Object.entries(byProduct)) {
+ if (fixed >= LIMIT) { capped += vs.length; continue; }
+ try {
+ const res = await gql(MUT, { productId: pid, variants: vs.map(v => ({ id: v.variant_id, price: v.correct_price.toFixed(2) })) });
+ const ue = res?.data?.productVariantsBulkUpdate?.userErrors || [];
+ const got = res?.data?.productVariantsBulkUpdate?.productVariants || [];
+ if (ue.length === 0) {
+ for (const v of vs) {
+ const np = got.find(g => g.id === v.variant_id)?.price;
+ if (np && Math.abs(parseFloat(np) - v.correct_price) <= 0.01) {
+ fixed++;
+ restore.push({ product_id: pid, variant_id: v.variant_id, sku: v.sku, vendor: v.vendor, prior_price: v.price, new_price: v.correct_price, source: v.source });
+ ledger.push({ vendor: v.vendor, sku: v.sku, action: 'FIXED', prior: v.price, new: v.correct_price, source: v.source });
+ } else { failed++; ledger.push({ vendor: v.vendor, sku: v.sku, action: 'FAIL_VERIFY', np }); }
+ }
+ } else { failed += vs.length; ledger.push({ product_id: pid, action: 'FAIL', userErrors: ue }); }
+ } catch (err) { failed += vs.length; ledger.push({ product_id: pid, action: 'ERROR', error: String(err) }); }
+ await sleep(120);
+ }
+ const rmf = `${DIR}/fixA-restore-map-APPLIED-${STAMP}.json`;
+ const lf = `${DIR}/fixA-apply-ledger-${STAMP}.json`;
+ fs.writeFileSync(rmf, JSON.stringify({ ts: STAMP, ticket: 'TK-11061-fixA', note: 'APPLIED restore map — set price back to prior_price to undo', entries: restore }, null, 2));
+ fs.writeFileSync(lf, JSON.stringify({ ts: STAMP, ticket: 'TK-11061-fixA', limit: LIMIT, counts: { candidates: items.length, fixed, failed, capped }, results: ledger }, null, 2));
+ console.log(`APPLY done. fixed=${fixed} failed=${failed} capped=${capped}`);
+ console.log(`restore-map: ${rmf}`);
+ console.log(`ledger: ${lf}`);
+}
+
+(async () => {
+ if (MODE === 'scan') await scan();
+ else if (MODE === 'resolve') resolve(argAfter('--resolve'));
+ else if (MODE === 'apply') await apply(argAfter('--apply'));
+})().catch(e => { console.error('FATAL', e); process.exit(1); });
← f32a9c1 TK-11046: Sanderson Dalmatians activate + dup-draft cleanup
·
back to Designerwallcoverings
·
Guard manufacturer rollback against observed value drift 5c82211 →