← back to Council 0809 Builds
TK-10385 Path A: read-only dry-run audit tooling (PG per-vendor + collision) + AUDIT_DUMP dry-run reporting — found normalizer out-of-range + 455 dangerous cross-vendor collisions; --apply BLOCKED pending fix
7f0e8d6d0ada33fa4c207bc6766f92a0ea5dfc33 · 2026-08-10 12:45:39 -0700 · steve
Files touched
A rolls-ar-deeplink/audit-collision-split.jsA rolls-ar-deeplink/audit-pathA-pg.js
Diff
commit 7f0e8d6d0ada33fa4c207bc6766f92a0ea5dfc33
Author: steve <steve@designerwallcoverings.com>
Date: Mon Aug 10 12:45:39 2026 -0700
TK-10385 Path A: read-only dry-run audit tooling (PG per-vendor + collision) + AUDIT_DUMP dry-run reporting — found normalizer out-of-range + 455 dangerous cross-vendor collisions; --apply BLOCKED pending fix
---
rolls-ar-deeplink/audit-collision-split.js | 23 +++++++++++
rolls-ar-deeplink/audit-pathA-pg.js | 66 ++++++++++++++++++++++++++++++
2 files changed, 89 insertions(+)
diff --git a/rolls-ar-deeplink/audit-collision-split.js b/rolls-ar-deeplink/audit-collision-split.js
new file mode 100644
index 0000000..c4dead3
--- /dev/null
+++ b/rolls-ar-deeplink/audit-collision-split.js
@@ -0,0 +1,23 @@
+#!/usr/bin/env node
+// read-only: split the divergent cross-vendor collisions into dangerous vs benign-rounding.
+'use strict';
+const { execFileSync } = require('child_process');
+const psql = s => execFileSync('psql', ['-h', '/tmp', '-d', 'dw_unified', '-tAF\t', '-X', '-c', s], { encoding: 'utf8', maxBuffer: 1 << 28 });
+const toIn = v => { const m = String(v).match(/[0-9]+(\.[0-9]+)?/); if (!m) return null; const n = parseFloat(m[0]); return n > 0 ? Math.round(n * 100) / 100 : null; };
+const tabs = psql("select table_name from information_schema.columns where table_schema='public' and column_name='repeat_v' and table_name like '%_catalog' and table_name not like '%_bak%' and table_name not like '_bak%'").trim().split('\n').map(s => s.trim());
+const km = new Map();
+for (const t of tabs) {
+ const cols = psql(`select column_name from information_schema.columns where table_schema='public' and table_name='${t}'`).trim().split('\n').map(s => s.trim());
+ const sk = ['mfr_sku', 'manufacturer_sku', 'sku', 'pattern_number', 'product_code', 'style_number'].find(c => cols.includes(c));
+ if (!sk) continue;
+ let rows = []; try { rows = psql(`select ${sk}::text,repeat_v::text from ${t} where repeat_v is not null`).trim().split('\n').filter(Boolean); } catch { continue; }
+ for (const l of rows) { const [s, rv] = l.split('\t'); const n = toIn(rv); if (!s || n == null) continue; const k = s.trim().toUpperCase(); if (!km.has(k)) km.set(k, []); km.get(k).push({ v: n, t: t.replace('_catalog', '') }); }
+}
+let benign = 0, danger = 0; const dex = [];
+for (const [k, a] of km) {
+ const vals = a.map(x => x.v); const u = [...new Set(vals)]; if (u.length < 2) continue;
+ const mn = Math.min(...vals), mx = Math.max(...vals);
+ if (mx / mn > 1.5 || mx - mn > 3) { danger++; if (dex.length < 15) dex.push(`${k}: ${[...new Set(a.map(x => x.t + '=' + x.v))].join(' / ')}`); }
+ else benign++;
+}
+console.log(JSON.stringify({ divergent_total: benign + danger, benign_rounding: benign, DANGEROUS_material: danger, examples: dex }, null, 2));
diff --git a/rolls-ar-deeplink/audit-pathA-pg.js b/rolls-ar-deeplink/audit-pathA-pg.js
new file mode 100644
index 0000000..28f74a5
--- /dev/null
+++ b/rolls-ar-deeplink/audit-pathA-pg.js
@@ -0,0 +1,66 @@
+#!/usr/bin/env node
+// READ-ONLY Path A audit (PG side only — no Shopify, no writes). Reuses the backfill's
+// OWN toInches normalizer + supply logic, but instead of first-writer-wins it DETECTS
+// cross-vendor SKU collisions + per-vendor value distribution + out-of-range (unit errors).
+'use strict';
+const { execFileSync } = require('child_process');
+function psql(sql) { return execFileSync('psql', ['-h', '/tmp', '-d', 'dw_unified', '-tAF\t', '-X', '-c', sql], { encoding: 'utf8', maxBuffer: 1 << 28 }); }
+function toInches(v) { if (v == null) return null; const m = String(v).match(/[0-9]+(\.[0-9]+)?/); if (!m) return null; const n = parseFloat(m[0]); return Number.isFinite(n) && n > 0 ? Math.round(n * 100) / 100 : null; }
+const median = (a) => { if (!a.length) return null; const s = [...a].sort((x, y) => x - y); const m = s.length >> 1; return s.length % 2 ? s[m] : Math.round((s[m - 1] + s[m]) / 2 * 100) / 100; };
+
+const tables = psql("select table_name from information_schema.columns where table_schema='public' and column_name='repeat_v' and table_name like '%_catalog' and table_name not like '%_bak%' and table_name not like '_bak%'").trim().split('\n').filter(Boolean).map(s => s.trim());
+
+const perVendor = [];
+const keyMap = new Map(); // upper(sku) -> [{vendor, val}]
+let totalRows = 0, totalNumeric = 0;
+
+for (const t of tables) {
+ const cols = psql(`select column_name from information_schema.columns where table_schema='public' and table_name='${t}'`).trim().split('\n').map(s => s.trim());
+ const skuCol = ['mfr_sku', 'manufacturer_sku', 'sku', 'pattern_number', 'product_code', 'style_number'].find(c => cols.includes(c));
+ if (!skuCol) { perVendor.push({ t, skuCol: null, rows: 0, numeric: 0, note: 'NO sku column — contributes nothing' }); continue; }
+ let rows = [];
+ try { rows = psql(`select ${skuCol}::text, repeat_v::text from ${t} where repeat_v is not null`).trim().split('\n').filter(Boolean); } catch { perVendor.push({ t, skuCol, rows: 0, numeric: 0, note: 'query failed' }); continue; }
+ const vals = []; let oor = 0;
+ for (const line of rows) {
+ const [sku, rv] = line.split('\t');
+ const n = toInches(rv);
+ if (!sku || n == null) continue;
+ vals.push(n);
+ if (n > 36) oor++; // >36in = likely a cm value stored as-is / unit error
+ const key = sku.trim().toUpperCase();
+ if (!keyMap.has(key)) keyMap.set(key, []);
+ keyMap.get(key).push({ vendor: t, val: n });
+ }
+ totalRows += rows.length; totalNumeric += vals.length;
+ perVendor.push({ t, skuCol, rows: rows.length, numeric: vals.length,
+ min: vals.length ? Math.min(...vals) : null, med: median(vals), max: vals.length ? Math.max(...vals) : null,
+ out_of_range_gt36: oor });
+}
+
+// cross-vendor collisions: same key in >=2 DISTINCT vendors
+let collisionKeys = 0, divergentKeys = 0; const divergentExamples = [];
+for (const [key, arr] of keyMap) {
+ const vendors = new Set(arr.map(a => a.vendor));
+ if (vendors.size < 2) continue;
+ collisionKeys++;
+ const distinctVals = new Set(arr.map(a => a.val));
+ if (distinctVals.size > 1) {
+ divergentKeys++;
+ if (divergentExamples.length < 15) divergentExamples.push({ key, spread: [...arr.map(a => `${a.vendor.replace('_catalog','')}=${a.val}"`)] });
+ }
+}
+
+const worstOOR = perVendor.filter(v => v.out_of_range_gt36 > 0).sort((a, b) => b.out_of_range_gt36 - a.out_of_range_gt36).slice(0, 12);
+const noSku = perVendor.filter(v => v.skuCol === null).map(v => v.t);
+
+console.log(JSON.stringify({
+ catalogs: tables.length,
+ supply_rows_with_repeat: totalRows,
+ supply_numeric_parseable: totalNumeric,
+ distinct_supply_keys: keyMap.size,
+ cross_vendor_collision_keys: collisionKeys,
+ DIVERGENT_collision_keys: divergentKeys, // ← the hazard: first-writer-wins mis-prefills the losers
+ divergent_examples: divergentExamples,
+ vendors_no_sku_col: noSku,
+ worst_out_of_range_vendors: worstOOR.map(v => ({ vendor: v.t, gt36: v.out_of_range_gt36, of_numeric: v.numeric, max: v.max })),
+}, null, 2));
← 847f3e6 add read-only AUDIT_DUMP reporting to Path A backfill dry-ru
·
back to Council 0809 Builds
·
TK-10385 Path A FIX: cm-handling + 0-40in range clamp in toI 5361e8c →