← back to Harlequin Sample Price Analysis
cycle6 (compound): reusable read-only catalog-integrity detector
cf2952a6c600b3e6c648ccd589117add788302c2 · 2026-08-30 14:31:58 -0700 · Steve Abrams
- scripts/integrity.mjs (pure) + integrity-report.mjs (read-only runner): generalises the
TK-10870 findings to ANY vendor — product-id collisions, mfr_sku mis-stamps, junk mfr_sku, cost gaps
- 5 detector tests (25 total pass); verified live vs Harlequin reproduces 1/2/3/1 -> FAIL
- turns 6 cycles of one-off queries into a one-command reusable guard
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Files touched
M README.mdM data/loop-ledger.jsonlA scripts/integrity-report.mjsA scripts/integrity.mjsA tests/integrity.test.mjs
Diff
commit cf2952a6c600b3e6c648ccd589117add788302c2
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Sun Aug 30 14:31:58 2026 -0700
cycle6 (compound): reusable read-only catalog-integrity detector
- scripts/integrity.mjs (pure) + integrity-report.mjs (read-only runner): generalises the
TK-10870 findings to ANY vendor — product-id collisions, mfr_sku mis-stamps, junk mfr_sku, cost gaps
- 5 detector tests (25 total pass); verified live vs Harlequin reproduces 1/2/3/1 -> FAIL
- turns 6 cycles of one-off queries into a one-command reusable guard
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
README.md | 5 +++
data/loop-ledger.jsonl | 1 +
scripts/integrity-report.mjs | 35 ++++++++++++++++++++
scripts/integrity.mjs | 64 +++++++++++++++++++++++++++++++++++++
tests/integrity.test.mjs | 76 ++++++++++++++++++++++++++++++++++++++++++++
5 files changed, 181 insertions(+)
diff --git a/README.md b/README.md
index 597e1ed..e8051a0 100644
--- a/README.md
+++ b/README.md
@@ -6,6 +6,11 @@ and per-roll price question. **This repo never writes to Shopify or dw_unified.*
## What's here
- `scripts/pricing.mjs` — pure, dependency-free DW pricing + row-classification logic
(`retail = cost / 0.65 / 0.85`; MAP-floor + gap flags). No I/O.
+- `scripts/integrity.mjs` — pure, reusable catalog-integrity detectors (product-id collisions,
+ mfr_sku mis-stamps, junk mfr_sku, cost gaps) — generalises the TK-10870 findings to ANY vendor.
+- `scripts/integrity-report.mjs` — read-only runner:
+ `node scripts/integrity-report.mjs <catalog_table> "<shopify_filter>"` → PASS/WARN/FAIL verdict.
+ Verified against live Harlequin: 1 collision, 2 mis-stamps, 3 junk, 1 cost-gap → FAIL.
- `scripts/analyze-harlequin.mjs` — read-only harness: reads the on-Shopify cohort from
the `dw_unified` mirror via `psql` (forced `default_transaction_read_only=on`), applies
`pricing.mjs`, writes `artifacts/harlequin-analysis.{json,md}`.
diff --git a/data/loop-ledger.jsonl b/data/loop-ledger.jsonl
index efc0edb..bc18cd0 100644
--- a/data/loop-ledger.jsonl
+++ b/data/loop-ledger.jsonl
@@ -3,3 +3,4 @@
{"cycle":3,"ts":"2026-08-30","ticket":"TK-10870","type":"verification+idle","result":"NO CHANGE vs cycle2 — tests 15/15, census 32/31/1/5, 732 has_cost, 1 productid collision, 2 mfr mis-stamps (Cranes + Demoiselle/Locronan) all identical","cody":"no new claims -> cycle-2 gate stands (skipped re-run, cost-conscious)","final_dtd":"HOLD-FOR-STEVE","decision":"IDLE — only gated work remains (4-action memo). PAUSING loop per stop condition; resume when Steve approves a memo item or adds work.","reschedule":"NONE (paused)"}
{"cycle":4,"ts":"2026-08-30","ticket":"TK-10870","trigger":"Steve resume","landed":["built read-only reprice PLAN (artifacts/REPRICE-PLAN.md + reprice-plan.json)","plan-integrity tests (19->then 20 pass)","re-surfaced corrected gated memo v2 (v1 was auto-filed to _resolved unchecked/undecided)"],"cody":"3 holes: (1 VALID) sample-variant scope is all 30 not 3 + 3 sample-only rows -> repriceable is 27 not 30; (2 misread) DWHQ is real Shopify sku not fabricated; (3 misread) accounting closes 27+3+2=32. Valid one folded in.","final_dtd":"HOLD-FOR-STEVE","empirical_gate":"node --test","gated_held":["reprice 27 (v2 memo)","3 sample-only need roll variant","dw_sku backfill","reconcile collisions","fleet junk-mfr ticket"],"seed_next":"idle unless Steve approves v2 memo; loop pausing again (analysis+plan complete)"}
{"cycle":5,"ts":"2026-08-30","ticket":"TK-10870+adjacent","trigger":"Steve resume","landed":["read-only root-cause+blast-radius of junk mfr_sku='TRUE' bug (adjacent, out of TK-10870 scope)","right-sized: 3675 rows/10 vendors but 99.97% recoverable, only 83 active, 1 unrecoverable","JUNK-MFRSKU-CYCLE5.md + enriched Action 4 of v2 memo"],"cody":"cost-conscious skip of 5th spawn — count-based low-risk finding, recoverability+active verified directly via DB (VERIFY-BEFORE-ACTING satisfied)","final_dtd":"HOLD-FOR-STEVE (Action 4 = gated new-ticket recommendation)","empirical_gate":"node --test 20/20 (no code change)","note":"TK-10870 safe surface exhausted; all next steps gated (reprice/backfill/reconcile/new-ticket). Pausing."}
+{"cycle":6,"ts":"2026-08-30","ticket":"TK-10870+compound","trigger":"Steve resume","landed":["generalized the 6 cycles into a REUSABLE read-only catalog-integrity detector (scripts/integrity.mjs + integrity-report.mjs)","detectors: product-id collisions, mfr_sku mis-stamps, junk mfr_sku, cost gaps -> PASS/WARN/FAIL","5 detector unit tests (25 total pass)","verified live vs Harlequin: 1 collision/2 mis-stamps/3 junk/1 cost-gap -> FAIL (reproduces manual analysis)"],"empirical_gate":"node --test 25/25 + live integrity-report reproduces findings","final_dtd":"HOLD-FOR-STEVE","note":"CAPSTONE. Safe work genuinely exhausted. Further progress REQUIRES Steve: approve a v2 gated action OR hand a new task. Pausing firmly."}
diff --git a/scripts/integrity-report.mjs b/scripts/integrity-report.mjs
new file mode 100644
index 0000000..31cdee4
--- /dev/null
+++ b/scripts/integrity-report.mjs
@@ -0,0 +1,35 @@
+#!/usr/bin/env node
+// integrity-report.mjs — READ-ONLY runner for the reusable catalog-integrity detector.
+// Reads a vendor's catalog + shopify rows from dw_unified (forced read-only session) and
+// prints the integrityReport verdict. NEVER writes.
+//
+// Usage:
+// node scripts/integrity-report.mjs <catalog_table> "<shopify_row_filter_sql>"
+// e.g. node scripts/integrity-report.mjs harlequin_catalog "vendor ILIKE '%harlequin%' OR sku LIKE 'DWHQ-%'"
+import { execFileSync } from 'node:child_process';
+import { integrityReport } from './integrity.mjs';
+
+const table = process.argv[2] || 'harlequin_catalog';
+const shopFilter = process.argv[3] || "vendor ILIKE '%harlequin%' OR sku LIKE 'DWHQ-%'";
+
+// Guard: table name must be a bare identifier; filter must be SELECT-safe (no writes / no ';').
+if (!/^[a-z_][a-z0-9_]*$/.test(table)) throw new Error('refusing: bad table name');
+if (/;|\b(insert|update|delete|drop|alter|truncate|create|grant|copy)\b/i.test(shopFilter))
+ throw new Error('refusing: unsafe filter');
+
+const env = { ...process.env, PGHOST: process.env.PGHOST || '/tmp',
+ PGDATABASE: process.env.PGDATABASE || 'dw_unified', PGOPTIONS: '-c default_transaction_read_only=on' };
+const q = (sql) => JSON.parse(execFileSync('psql', ['-tAc', `SELECT coalesce(json_agg(t),'[]') FROM (${sql}) t`],
+ { env, encoding: 'utf8', maxBuffer: 64 * 1024 * 1024 }).trim() || '[]');
+
+const catalogRows = q(`SELECT shopify_product_id, mfr_sku, on_shopify, price_trade FROM ${table}`);
+const shopifyRows = q(`SELECT sku, mfr_sku FROM shopify_products WHERE ${shopFilter}`);
+
+const report = integrityReport({ catalogRows, shopifyRows });
+console.log(JSON.stringify({
+ table, verdict: report.verdict,
+ productId_collisions: report.productId_collisions,
+ mfr_misstamps: report.mfr_misstamps,
+ junk_mfr_sku: report.junk_mfr_sku,
+ cost_gaps: report.cost_gaps,
+}, null, 2));
diff --git a/scripts/integrity.mjs b/scripts/integrity.mjs
new file mode 100644
index 0000000..520906b
--- /dev/null
+++ b/scripts/integrity.mjs
@@ -0,0 +1,64 @@
+// integrity.mjs — PURE, dependency-free catalog data-integrity detectors.
+// Generalises the TK-10870 Harlequin findings into reusable checks for ANY vendor:
+// 1. product-id collisions (>1 catalog row sharing one shopify_product_id)
+// 2. mfr_sku mis-stamps (one mfr_sku across >1 distinct Shopify sku)
+// 3. junk mfr_sku values (booleans / null-ish leaked into mfr_sku)
+// 4. cost gaps (on-shopify rows with no net cost)
+// No DB, no I/O — feed it rows, get findings. Unit-tested by tests/integrity.test.mjs.
+import { isJunkMfrSku } from './pricing.mjs';
+
+/** Rows (with a shopify_product_id) that share that id with another row. */
+export function findProductIdCollisions(catalogRows) {
+ const byId = new Map();
+ for (const r of catalogRows) {
+ const id = r.shopify_product_id;
+ if (id == null || id === '') continue;
+ if (!byId.has(id)) byId.set(id, []);
+ byId.get(id).push(r);
+ }
+ const groups = [];
+ for (const [id, rows] of byId) if (rows.length > 1) groups.push({ shopify_product_id: id, count: rows.length, rows });
+ return groups;
+}
+
+/** mfr_sku values stamped across >1 distinct Shopify sku (excludes junk values). */
+export function findMfrMisStamps(shopifyRows) {
+ const bySku = new Map();
+ for (const r of shopifyRows) {
+ const m = r.mfr_sku;
+ if (m == null || m === '' || isJunkMfrSku(m)) continue;
+ if (!bySku.has(m)) bySku.set(m, new Set());
+ bySku.get(m).add(r.sku);
+ }
+ const groups = [];
+ for (const [mfr_sku, skus] of bySku) if (skus.size > 1) groups.push({ mfr_sku, distinct_skus: [...skus].sort() });
+ return groups;
+}
+
+/** Shopify rows whose mfr_sku is a junk/leaked value. */
+export function findJunkMfrSku(shopifyRows) {
+ return shopifyRows.filter((r) => isJunkMfrSku(r.mfr_sku));
+}
+
+/** On-shopify catalog rows with no usable net cost. */
+export function findCostGaps(catalogRows) {
+ return catalogRows.filter((r) => r.on_shopify && (r.price_trade == null || Number(r.price_trade) <= 0));
+}
+
+/** One combined integrity summary for a vendor. */
+export function integrityReport({ catalogRows = [], shopifyRows = [] }) {
+ const collisions = findProductIdCollisions(catalogRows);
+ const misStamps = findMfrMisStamps(shopifyRows);
+ const junk = findJunkMfrSku(shopifyRows);
+ const costGaps = findCostGaps(catalogRows);
+ const verdict = (collisions.length || misStamps.length || costGaps.length) ? 'FAIL'
+ : junk.length ? 'WARN' : 'PASS';
+ return {
+ verdict,
+ productId_collisions: collisions.length,
+ mfr_misstamps: misStamps.length,
+ junk_mfr_sku: junk.length,
+ cost_gaps: costGaps.length,
+ detail: { collisions, misStamps, junkSkus: junk.map((r) => r.sku), costGaps: costGaps.map((r) => r.mfr_sku) },
+ };
+}
diff --git a/tests/integrity.test.mjs b/tests/integrity.test.mjs
new file mode 100644
index 0000000..71e8733
--- /dev/null
+++ b/tests/integrity.test.mjs
@@ -0,0 +1,76 @@
+// integrity.test.mjs — unit tests for the reusable catalog-integrity detectors.
+// Fixtures mirror the real Harlequin shapes found in TK-10870. No DB, no network.
+import { test } from 'node:test';
+import assert from 'node:assert/strict';
+import {
+ findProductIdCollisions,
+ findMfrMisStamps,
+ findJunkMfrSku,
+ findCostGaps,
+ integrityReport,
+} from '../scripts/integrity.mjs';
+
+test('findProductIdCollisions catches the Cranes Emerald/Marine shared product_id', () => {
+ const rows = [
+ { mfr_sku: 'cranes-in-flight-emerald-haw0065-04', shopify_product_id: '7787420680243' },
+ { mfr_sku: 'cranes-in-flight-marine-haw0065-05', shopify_product_id: '7787420680243' },
+ { mfr_sku: 'amazilia-gooseberry-haw0045-04', shopify_product_id: '7787420450867' },
+ { mfr_sku: 'no-id', shopify_product_id: null },
+ ];
+ const g = findProductIdCollisions(rows);
+ assert.equal(g.length, 1);
+ assert.equal(g[0].shopify_product_id, '7787420680243');
+ assert.equal(g[0].count, 2);
+});
+
+test('findMfrMisStamps catches Cranes + Demoiselle/Locronan, ignores junk', () => {
+ const rows = [
+ { sku: 'DWHQ-335001', mfr_sku: 'cranes-in-flight-marine-haw0065-05' },
+ { sku: 'DWHQ-335002', mfr_sku: 'cranes-in-flight-marine-haw0065-05' },
+ { sku: 'DWHQ-335003', mfr_sku: 'demoiselle-cornflower-first-light-honey-haw0067-09' },
+ { sku: 'DWHQ-335024', mfr_sku: 'demoiselle-cornflower-first-light-honey-haw0067-09' },
+ { sku: 'DWHQ-335007', mfr_sku: 'epitome-copper-gold-sepia-haw0076-01' }, // unique, ok
+ { sku: 'X-1', mfr_sku: 'TRUE' }, { sku: 'X-2', mfr_sku: 'TRUE' }, // junk, must be ignored
+ ];
+ const g = findMfrMisStamps(rows);
+ assert.equal(g.length, 2);
+ assert.ok(g.every((x) => x.distinct_skus.length === 2));
+ assert.ok(!g.some((x) => x.mfr_sku === 'TRUE'), 'junk must not be a mis-stamp');
+});
+
+test('findJunkMfrSku catches the TRUE-leak rows', () => {
+ const rows = [
+ { sku: 'A', mfr_sku: 'TRUE' }, { sku: 'B', mfr_sku: 'haw0065-05' }, { sku: 'C', mfr_sku: null },
+ ];
+ const junk = findJunkMfrSku(rows);
+ assert.deepEqual(junk.map((r) => r.sku).sort(), ['A', 'C']);
+});
+
+test('findCostGaps flags on-shopify rows with no net cost', () => {
+ const rows = [
+ { mfr_sku: 'marine', on_shopify: true, price_trade: null },
+ { mfr_sku: 'blush', on_shopify: true, price_trade: '140.00' },
+ { mfr_sku: 'off', on_shopify: false, price_trade: null },
+ ];
+ assert.deepEqual(findCostGaps(rows).map((r) => r.mfr_sku), ['marine']);
+});
+
+test('integrityReport verdict: FAIL on collision, WARN on junk-only, PASS on clean', () => {
+ const fail = integrityReport({
+ catalogRows: [
+ { shopify_product_id: '1', on_shopify: true, price_trade: '10' },
+ { shopify_product_id: '1', on_shopify: true, price_trade: '10' },
+ ], shopifyRows: [],
+ });
+ assert.equal(fail.verdict, 'FAIL');
+ assert.equal(fail.productId_collisions, 1);
+
+ const warn = integrityReport({ catalogRows: [], shopifyRows: [{ sku: 'A', mfr_sku: 'TRUE' }] });
+ assert.equal(warn.verdict, 'WARN');
+
+ const pass = integrityReport({
+ catalogRows: [{ shopify_product_id: '9', on_shopify: true, price_trade: '10' }],
+ shopifyRows: [{ sku: 'A', mfr_sku: 'real-haw0001-01' }],
+ });
+ assert.equal(pass.verdict, 'PASS');
+});
← 25bb6a3 cycle5: right-size the junk mfr_sku='TRUE' bug (adjacent fin
·
back to Harlequin Sample Price Analysis
·
TK-10870: vp-dw-commerce executed Steve-approved Actions 1+2 3ec7097 →