← back to Designer Wallcoverings
Add Anna French staging anomaly fixer (AT<->AF type/prefix/dw_sku correction from live truth)
449e1cc634b2914a1deeb4e05a7099e996f3895b · 2026-06-25 11:22:32 -0700 · Steve Abrams
Files touched
A shopify/scripts/anna-french-fix-staging-anomalies.mjs
Diff
commit 449e1cc634b2914a1deeb4e05a7099e996f3895b
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Thu Jun 25 11:22:32 2026 -0700
Add Anna French staging anomaly fixer (AT<->AF type/prefix/dw_sku correction from live truth)
---
.../scripts/anna-french-fix-staging-anomalies.mjs | 421 +++++++++++++++++++++
1 file changed, 421 insertions(+)
diff --git a/shopify/scripts/anna-french-fix-staging-anomalies.mjs b/shopify/scripts/anna-french-fix-staging-anomalies.mjs
new file mode 100644
index 00000000..312c48e7
--- /dev/null
+++ b/shopify/scripts/anna-french-fix-staging-anomalies.mjs
@@ -0,0 +1,421 @@
+#!/usr/bin/env node
+/**
+ * anna-french-fix-staging-anomalies.mjs
+ * -----------------------------------------------------------------------------
+ * REVERSIBLE, LOCAL staging-table correction for the Anna French re-onboard.
+ *
+ * Problem (PHASE 2b task #5): the repair dry-run flagged ~144 anomalies. The
+ * biggest bucket is staging rows where anna_french_catalog says mfr_sku AT#####
+ * (-> classified Wallcovering -> DWAT) but the LIVE Shopify product is genuinely
+ * an AF##### FABRIC. Live is ground truth (verified: staging AT23100 vs live
+ * laura-fabrics-af23100, variants DWAF-74674, productType Fabric, title "...Fabric").
+ *
+ * This script corrects ONLY the anna_french_catalog STAGING TABLE — it does NOT
+ * touch shopify_products (the canonical mirror) and NEVER writes to Shopify.
+ * For each anomaly row with UNAMBIGUOUS live evidence it:
+ * - flips mfr_sku (AT<->AF) to the real manufacturer code
+ * - sets product_type (Fabric / Wallcovering) to live truth
+ * - re-derives dw_prefix (Fabric->DWAF, Wallcovering->DWAT)
+ * - reassigns dw_sku, REUSING the live counterpart's number where one exists
+ * (collision-checked vs dw_sku_registry + shopify_products + intra-batch);
+ * else mints a fresh number above the series max.
+ *
+ * GROUND TRUTH (per task): determined by LIVE Shopify GraphQL for each anomaly
+ * candidate GID (the same method the repair tool uses) — NOT the stale mirror.
+ * A row is corrected ONLY when live signals are unanimous & unambiguous:
+ * - exactly ONE canonical live counterpart (ACTIVE, real af/at handle slug), OR
+ * - all live candidates agree on type and a real af/at handle code is present.
+ * Genuinely ambiguous rows (same-type multi-match with no clean canonical, pure
+ * multi-ghost garbled handles, missing/stale GIDs) are LEFT untouched.
+ *
+ * Idempotent: re-running re-detects already-correct rows as NOOP.
+ *
+ * Usage:
+ * node shopify/scripts/anna-french-fix-staging-anomalies.mjs # DRY-RUN (default, no DB write)
+ * node shopify/scripts/anna-french-fix-staging-anomalies.mjs --apply # write staging corrections (LOCAL, reversible)
+ * node shopify/scripts/anna-french-fix-staging-anomalies.mjs --plan <path>
+ * node shopify/scripts/anna-french-fix-staging-anomalies.mjs --out <auditpath>
+ *
+ * No --apply = no DB writes. --apply only mutates anna_french_catalog (local PG).
+ */
+import pg from 'pg';
+import fs from 'node:fs';
+import path from 'node:path';
+
+const SHOP = 'designer-laboratory-sandbox.myshopify.com';
+const VER = '2024-10';
+const TOKEN =
+ process.env.SHOPIFY_ADMIN_ACCESS_TOKEN ||
+ process.env.SHOPIFY_ADMIN_TOKEN ||
+ process.env.AF_REPAIR_TOKEN;
+const GQL = `https://${SHOP}/admin/api/${VER}/graphql.json`;
+
+const APPLY = process.argv.includes('--apply');
+const pi = process.argv.indexOf('--plan');
+const PLAN =
+ pi >= 0
+ ? path.resolve(process.argv[pi + 1])
+ : path.resolve(
+ process.cwd(),
+ 'shopify/scripts/cadence/data/anna-french/anna-french-repair-plan-2026-06-25.json'
+ );
+const oi = process.argv.indexOf('--out');
+const OUT =
+ oi >= 0
+ ? path.resolve(process.argv[oi + 1])
+ : path.resolve(
+ process.cwd(),
+ `shopify/scripts/cadence/data/anna-french/anna-french-staging-fix-audit-${new Date()
+ .toISOString()
+ .slice(0, 10)}.json`
+ );
+
+// ---------------------------------------------------------------------------
+const pool = process.env.DATABASE_URL
+ ? new pg.Pool({ connectionString: process.env.DATABASE_URL })
+ : new pg.Pool({ host: '/tmp', database: 'dw_unified', user: process.env.PGUSER || process.env.USER });
+
+const sleep = (ms) => new Promise((r) => setTimeout(r, ms));
+const liveCache = new Map();
+async function gql(query, variables) {
+ for (let attempt = 0; attempt < 6; attempt++) {
+ const res = await fetch(GQL, {
+ method: 'POST',
+ headers: { 'X-Shopify-Access-Token': TOKEN, 'Content-Type': 'application/json' },
+ body: JSON.stringify({ query, variables }),
+ });
+ if (res.status === 429) {
+ const ra = Number(res.headers.get('Retry-After') || 2);
+ await sleep((ra + 0.3) * 1000);
+ continue;
+ }
+ const json = await res.json().catch(() => null);
+ const cost = json?.extensions?.cost?.throttleStatus;
+ if (cost && cost.currentlyAvailable < 300) await sleep(700);
+ if (json?.errors) return { ok: false, errors: json.errors, data: json.data };
+ return { ok: true, data: json?.data };
+ }
+ return { ok: false, errors: [{ message: 'retries exhausted' }] };
+}
+const PRODUCT_Q = `
+query($id: ID!) {
+ product(id: $id) {
+ id handle title status productType vendor
+ variants(first: 20) { edges { node { sku title } } }
+ }
+}`;
+async function readLive(gid) {
+ if (liveCache.has(gid)) return liveCache.get(gid);
+ const r = await gql(PRODUCT_Q, { id: gid });
+ const node = r?.data?.product || null;
+ liveCache.set(gid, node);
+ await sleep(110);
+ return node;
+}
+
+// ---------------------------------------------------------------------------
+function vRoot(sku) {
+ const m = String(sku || '').toUpperCase().match(/^(DW[A-Z]{2})-(\d+)/);
+ return m ? { prefix: m[1], num: m[2] } : null;
+}
+function handleCode(handle) {
+ const m = String(handle || '').toLowerCase().match(/\b(a[ft])(\d{3,})\b/);
+ return m ? { letters: m[1].toUpperCase(), num: m[2] } : null;
+}
+function prefixToType(prefix) {
+ if (prefix === 'DWAF') return 'Fabric';
+ if (prefix === 'DWAT' || prefix === 'DWTA') return 'Wallcovering'; // DWTA = transposed DWAT
+ return null; // DWTT (Thibaut leak) etc. = type-agnostic
+}
+function typeToPrefix(t) {
+ return t === 'Fabric' ? 'DWAF' : 'DWAT';
+}
+function typeToLetters(t) {
+ return t === 'Fabric' ? 'AF' : 'AT';
+}
+function numericStem(mfr) {
+ return String(mfr || '').toLowerCase().replace(/^a[ft]/, '');
+}
+
+/**
+ * Decide live truth for one anomaly's candidate set.
+ * Returns { decision:'flip'|'ambiguous', reason, type, true_mfr, reuse_num, canonical_gid }
+ * UNAMBIGUOUS rule:
+ * - Collect live nodes (skip nulls / stale GIDs).
+ * - Each node votes a type from: productType field, variant prefix, handle code.
+ * - Require ALL non-null type votes across ALL nodes to agree on ONE type.
+ * - Pick canonical = the ACTIVE node with a real af/at handle slug; else the
+ * ACTIVE node with a parseable DWAF/DWAT/DWTA/DWTT variant number; else any
+ * node with a real af/at handle slug.
+ * - true_mfr = canonical handle code (real Thibaut slug) if present;
+ * else letters(type)+numericStem(stagingMfr).
+ * - reuse_num = canonical variant number (its own live SKU number) — reused so
+ * staging dw_sku == the live product's actual dw_sku number.
+ */
+async function decide(stagingMfr, stagingType, candidates) {
+ const nodes = [];
+ for (const c of candidates) {
+ const live = await readLive(c.gid);
+ if (!live) continue;
+ const v0 = (live.variants?.edges || []).map((e) => e.node).find((n) => vRoot(n.sku));
+ nodes.push({
+ gid: live.id,
+ status: live.status,
+ type: live.productType || null,
+ handle: live.handle,
+ hcode: handleCode(live.handle),
+ vroot: vRoot(v0?.sku),
+ title: live.title || '',
+ });
+ }
+ if (!nodes.length) return { decision: 'ambiguous', reason: 'no live node (all GIDs stale/deleted)' };
+
+ // collect type votes
+ const votes = new Set();
+ for (const n of nodes) {
+ if (n.type === 'Fabric' || n.type === 'Wallcovering') votes.add(n.type);
+ if (n.hcode) votes.add(n.hcode.letters === 'AF' ? 'Fabric' : 'Wallcovering');
+ const pt = n.vroot && prefixToType(n.vroot.prefix);
+ if (pt) votes.add(pt);
+ }
+ if (votes.size !== 1) {
+ return { decision: 'ambiguous', reason: `live type votes not unanimous: {${[...votes].join(',')}}`, nodes };
+ }
+ const liveType = [...votes][0];
+
+ // canonical node selection
+ const active = nodes.filter((n) => n.status === 'ACTIVE');
+ const pickReal = (arr) => arr.find((n) => n.hcode);
+ let canonical =
+ pickReal(active) ||
+ active.find((n) => n.vroot) ||
+ pickReal(nodes) ||
+ nodes.find((n) => n.vroot) ||
+ nodes[0];
+
+ // if multiple ACTIVE real-handle nodes exist with DIFFERENT handle codes -> ambiguous
+ const activeRealCodes = new Set(
+ active.filter((n) => n.hcode).map((n) => n.hcode.letters + n.hcode.num)
+ );
+ if (activeRealCodes.size > 1) {
+ return { decision: 'ambiguous', reason: `multiple ACTIVE products with distinct mfr handle codes: {${[...activeRealCodes].join(',')}}`, nodes };
+ }
+
+ const true_mfr = canonical.hcode
+ ? canonical.hcode.letters + canonical.hcode.num
+ : typeToLetters(liveType) + numericStem(stagingMfr);
+ const reuse_num = canonical.vroot ? canonical.vroot.num : null;
+
+ // sanity: if a real handle code exists, its numeric stem should equal staging stem (same product, just AT/AF letter)
+ if (canonical.hcode && numericStem(true_mfr) !== numericStem(stagingMfr)) {
+ return {
+ decision: 'ambiguous',
+ reason: `canonical handle code ${true_mfr} numeric stem != staging ${stagingMfr} stem`,
+ nodes,
+ };
+ }
+
+ return { decision: 'flip', reason: 'unanimous live type', type: liveType, true_mfr, reuse_num, canonical_gid: canonical.gid, nodes };
+}
+
+// ---------------------------------------------------------------------------
+async function run() {
+ if (!TOKEN) {
+ console.error('No Shopify token. Aborting.');
+ process.exit(1);
+ }
+ const plan = JSON.parse(fs.readFileSync(PLAN, 'utf8'));
+ const anomalies = plan.anomalies || [];
+
+ // dedup sources for reuse/mint collision checks
+ const reg = await pool.query(`SELECT upper(dw_sku) AS s FROM dw_sku_registry WHERE dw_sku IS NOT NULL`);
+ const regSet = new Set(reg.rows.map((r) => r.s));
+ // shopify_products SKUs that belong to OTHER products (exclude Anna French so we can reuse AF's own numbers)
+ const sp = await pool.query(
+ `SELECT upper(variant_sku) AS v, upper(dw_sku) AS d, vendor FROM shopify_products WHERE variant_sku IS NOT NULL OR dw_sku IS NOT NULL`
+ );
+ const foreignSet = new Set();
+ for (const r of sp.rows) {
+ if (r.vendor === 'Anna French') continue;
+ const vr = vRoot(r.v); if (vr) foreignSet.add(vr.prefix + '-' + vr.num);
+ const dr = vRoot(r.d); if (dr) foreignSet.add(dr.prefix + '-' + dr.num);
+ }
+ // max numbers for fallback mint
+ const maxQ = await pool.query(
+ `SELECT 'DWAF' p, max((regexp_replace(dw_sku,'[^0-9]','','g'))::int) m FROM anna_french_catalog WHERE dw_sku LIKE 'DWAF-%'
+ UNION ALL SELECT 'DWAT', max((regexp_replace(dw_sku,'[^0-9]','','g'))::int) FROM anna_french_catalog WHERE dw_sku LIKE 'DWAT-%'
+ UNION ALL SELECT 'DWAF_reg', max((regexp_replace(dw_sku,'[^0-9]','','g'))::int) FROM dw_sku_registry WHERE dw_sku LIKE 'DWAF-%'
+ UNION ALL SELECT 'DWAT_reg', max((regexp_replace(dw_sku,'[^0-9]','','g'))::int) FROM dw_sku_registry WHERE dw_sku LIKE 'DWAT-%'`
+ );
+ const maxes = {};
+ for (const r of maxQ.rows) maxes[r.p] = Number(r.m || 0);
+ let nextDWAF = Math.max(maxes.DWAF || 0, maxes.DWAF_reg || 0) + 1;
+ let nextDWAT = Math.max(maxes.DWAT || 0, maxes.DWAT_reg || 0) + 1;
+
+ const intraBatch = new Set(); // dw_sku roots claimed this run
+
+ function claimSku(prefix, reuseNum, selfGid) {
+ // try reuse the live counterpart number
+ if (reuseNum) {
+ const root = `${prefix}-${reuseNum}`;
+ const collidesForeign = foreignSet.has(root);
+ const collidesIntra = intraBatch.has(root);
+ // reuse is fine if it isn't a FOREIGN (other-product) sku and not already claimed this batch.
+ // registry collisions are allowed only if it's THIS product's own registry entry — but to stay
+ // strictly safe we treat any registry hit as foreign UNLESS the number is the live counterpart's.
+ if (!collidesForeign && !collidesIntra) {
+ intraBatch.add(root);
+ return { dw_sku: root, mode: 'reuse-live-counterpart' };
+ }
+ }
+ // mint fresh above series max, skipping any collision
+ const bump = () => (prefix === 'DWAF' ? nextDWAF++ : nextDWAT++);
+ let n = bump();
+ let root = `${prefix}-${n}`;
+ while (regSet.has(root) || foreignSet.has(root) || intraBatch.has(root)) {
+ n = bump();
+ root = `${prefix}-${n}`;
+ }
+ intraBatch.add(root);
+ return { dw_sku: root, mode: 'mint-fresh' };
+ }
+
+ const audit = {
+ generated: new Date().toISOString(),
+ mode: APPLY ? 'APPLY (staging write)' : 'DRY-RUN (no write)',
+ table: 'anna_french_catalog',
+ plan_source: PLAN,
+ anomalies_in: anomalies.length,
+ corrections: [],
+ left_as_anomaly: [],
+ noop_already_correct: [],
+ not_in_staging: [],
+ tallies: {},
+ };
+
+ // index staging rows by upper(mfr_sku)
+ const { rows: stageRows } = await pool.query(
+ `SELECT id, mfr_sku, dw_sku, dw_prefix, product_type, color_name FROM anna_french_catalog`
+ );
+ const stageByMfr = new Map();
+ for (const r of stageRows) {
+ const k = String(r.mfr_sku || '').toUpperCase();
+ if (!stageByMfr.has(k)) stageByMfr.set(k, []);
+ stageByMfr.get(k).push(r);
+ }
+
+ let i = 0;
+ for (const a of anomalies) {
+ i++;
+ const candidates = a.candidates
+ ? a.candidates
+ : a.gids
+ ? a.gids.map((g) => ({ gid: g }))
+ : a.gid
+ ? [{ gid: a.gid }]
+ : [];
+ if (!candidates.length) {
+ audit.left_as_anomaly.push({ mfr_sku: a.mfr_sku, reason: a.reason, why: 'no candidate gids' });
+ continue;
+ }
+
+ const stagingMfr = String(a.mfr_sku || '').toUpperCase();
+ const stageMatches = stageByMfr.get(stagingMfr) || [];
+ if (!stageMatches.length) {
+ audit.not_in_staging.push({ mfr_sku: a.mfr_sku, reason: a.reason });
+ continue;
+ }
+
+ const d = await decide(stagingMfr, stageMatches[0].product_type, candidates);
+ if (d.decision !== 'flip') {
+ audit.left_as_anomaly.push({ mfr_sku: a.mfr_sku, reason: a.reason, why: d.reason });
+ continue;
+ }
+
+ const newType = d.type;
+ const newPrefix = typeToPrefix(newType);
+ const newMfr = d.true_mfr;
+
+ for (const row of stageMatches) {
+ const curType = row.product_type;
+ const curMfr = String(row.mfr_sku || '').toUpperCase();
+ const curPrefix = (row.dw_prefix || '').toUpperCase();
+ const alreadyCorrect =
+ curType === newType && curMfr === newMfr.toUpperCase() && curPrefix === newPrefix;
+ if (alreadyCorrect) {
+ audit.noop_already_correct.push({ id: row.id, mfr_sku: row.mfr_sku });
+ continue;
+ }
+ // assign dw_sku: keep current root if prefix unchanged AND number matches reuse; else (re)claim
+ let newDwSku = row.dw_sku;
+ const prefixChanged = curPrefix !== newPrefix;
+ const curRoot = vRoot(row.dw_sku);
+ const wantNum = d.reuse_num;
+ const needSku =
+ prefixChanged ||
+ !curRoot ||
+ (wantNum && curRoot.num !== wantNum);
+ let skuMode = 'kept';
+ if (needSku) {
+ const claim = claimSku(newPrefix, wantNum, d.canonical_gid);
+ newDwSku = claim.dw_sku;
+ skuMode = claim.mode;
+ }
+
+ audit.corrections.push({
+ id: row.id,
+ old: { mfr_sku: row.mfr_sku, product_type: curType, dw_prefix: row.dw_prefix, dw_sku: row.dw_sku },
+ new: { mfr_sku: newMfr, product_type: newType, dw_prefix: newPrefix, dw_sku: newDwSku },
+ evidence: {
+ reason: d.reason,
+ canonical_gid: d.canonical_gid,
+ live_nodes: (d.nodes || []).map((n) => ({ gid: n.gid, status: n.status, type: n.type, handle: n.handle, sku: n.vroot ? n.vroot.prefix + '-' + n.vroot.num : null })),
+ },
+ sku_mode: skuMode,
+ at_af_flip: curMfr[1] !== newMfr[1] ? `${curMfr.slice(0, 2)}->${newMfr.slice(0, 2)}` : 'same-letters',
+ prefix_flip: prefixChanged ? `${curPrefix}->${newPrefix}` : 'same-prefix',
+ type_flip: curType !== newType ? `${curType}->${newType}` : 'same-type',
+ });
+
+ if (APPLY) {
+ await pool.query(
+ `UPDATE anna_french_catalog
+ SET mfr_sku=$2, product_type=$3, dw_prefix=$4, dw_sku=$5, updated_at=now()
+ WHERE id=$1`,
+ [row.id, newMfr, newType, newPrefix, newDwSku]
+ );
+ }
+ }
+ if (i % 25 === 0) console.log(`...processed ${i}/${anomalies.length} anomalies (corrections=${audit.corrections.length})`);
+ }
+
+ // tallies
+ const t = {
+ anomalies_in: anomalies.length,
+ rows_corrected: audit.corrections.length,
+ at_to_af: audit.corrections.filter((c) => c.at_af_flip === 'AT->AF').length,
+ af_to_at: audit.corrections.filter((c) => c.at_af_flip === 'AF->AT').length,
+ type_to_fabric: audit.corrections.filter((c) => c.new.product_type === 'Fabric' && c.type_flip !== 'same-type').length,
+ type_to_wallcovering: audit.corrections.filter((c) => c.new.product_type === 'Wallcovering' && c.type_flip !== 'same-type').length,
+ prefix_to_dwaf: audit.corrections.filter((c) => c.prefix_flip === 'DWAT->DWAF' || c.prefix_flip === 'DWTA->DWAF').length,
+ prefix_to_dwat: audit.corrections.filter((c) => c.prefix_flip.endsWith('->DWAT')).length,
+ sku_reused_live_counterpart: audit.corrections.filter((c) => c.sku_mode === 'reuse-live-counterpart').length,
+ sku_minted_fresh: audit.corrections.filter((c) => c.sku_mode === 'mint-fresh').length,
+ sku_kept: audit.corrections.filter((c) => c.sku_mode === 'kept').length,
+ noop_already_correct: audit.noop_already_correct.length,
+ left_as_anomaly: audit.left_as_anomaly.length,
+ not_in_staging: audit.not_in_staging.length,
+ };
+ audit.tallies = t;
+
+ fs.mkdirSync(path.dirname(OUT), { recursive: true });
+ fs.writeFileSync(OUT, JSON.stringify(audit, null, 2));
+ console.log('\n=== Anna French staging anomaly fix — ' + audit.mode + ' ===');
+ console.log(JSON.stringify(t, null, 2));
+ console.log('\naudit ->', OUT);
+ await pool.end();
+}
+run().catch((e) => {
+ console.error('FATAL', e);
+ process.exit(1);
+});
← 72746d81 Add Anna French in-place repair tool (dry-run); 489 matched
·
back to Designer Wallcoverings
·
AF staging fixer: route mfr_sku UNIQUE collisions (phantom A 91749a79 →