← back to Dw Add Sellable Variant Tk10902
add read-only cost source coverage analyzer
6de528ff9b57e38e155b48c4ee9c9f3ceb1cef12 · 2026-08-30 11:01:08 -0700 · Steve Abrams
Files touched
A tk10875-cost-source-map/README.mdA tk10875-cost-source-map/analyze-cost-sources.mjsA tk10875-cost-source-map/test-cost-source-map.mjsA tk10875-cost-source-map/verification/e2e-proof.json
Diff
commit 6de528ff9b57e38e155b48c4ee9c9f3ceb1cef12
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Sun Aug 30 11:01:08 2026 -0700
add read-only cost source coverage analyzer
---
tk10875-cost-source-map/README.md | 20 +++++
tk10875-cost-source-map/analyze-cost-sources.mjs | 88 ++++++++++++++++++++++
tk10875-cost-source-map/test-cost-source-map.mjs | 17 +++++
.../verification/e2e-proof.json | 36 +++++++++
4 files changed, 161 insertions(+)
diff --git a/tk10875-cost-source-map/README.md b/tk10875-cost-source-map/README.md
new file mode 100644
index 0000000..d86b206
--- /dev/null
+++ b/tk10875-cost-source-map/README.md
@@ -0,0 +1,20 @@
+# TK-10875 cost-source map
+
+This local, read-only analyzer is the next narrowing step for the corrected
+25,214-product unbuyable cohort and its approximately 24,990 cost-sourcing hole.
+It discovers staging tables with explicit cost/trade columns, then counts only
+exact identifier joins to affected Shopify-mirror products. Retail/MSRP columns
+are deliberately excluded from cost evidence.
+
+Run:
+
+```sh
+node analyze-cost-sources.mjs --probe-write-guard
+node analyze-cost-sources.mjs
+node test-cost-source-map.mjs
+```
+
+The full scan did not complete on 2026-08-30 because local `dw_unified` reads
+continued to queue behind the known `shopify_products` lock pileup. No backend
+was killed and no database or Shopify write was attempted. The expected report
+`cost-source-map.json` therefore remains absent until the mirror is responsive.
diff --git a/tk10875-cost-source-map/analyze-cost-sources.mjs b/tk10875-cost-source-map/analyze-cost-sources.mjs
new file mode 100644
index 0000000..6097d8d
--- /dev/null
+++ b/tk10875-cost-source-map/analyze-cost-sources.mjs
@@ -0,0 +1,88 @@
+#!/usr/bin/env node
+// TK-10875: read-only discovery of exact-key, positive-cost staging coverage.
+// It cannot write to PostgreSQL: only SELECT/WITH statements are accepted and
+// every call is wrapped in an explicit READ ONLY transaction plus ROLLBACK.
+
+import { execFileSync } from 'node:child_process';
+import { mkdirSync, writeFileSync } from 'node:fs';
+import { dirname, join } from 'node:path';
+import { fileURLToPath } from 'node:url';
+
+const here = dirname(fileURLToPath(import.meta.url));
+const db = 'host=/tmp dbname=dw_unified';
+const ident = value => {
+ if (!/^[a-z_][a-z0-9_]*$/i.test(value)) throw new Error(`unsafe SQL identifier: ${value}`);
+ return `"${value}"`;
+};
+
+function query(sql) {
+ if (!/^\s*(select|with)\b/i.test(sql)) throw new Error('only SELECT/WITH is permitted');
+ const script = `BEGIN READ ONLY;\nSELECT coalesce(json_agg(row_to_json(q)), '[]'::json) FROM (${sql}) q;\nROLLBACK;\n`;
+ const raw = execFileSync('psql', ['-h', '/tmp', '-d', 'dw_unified', '-X', '-tA', '-v', 'ON_ERROR_STOP=1'], {
+ input: script, encoding: 'utf8', maxBuffer: 64 * 1024 * 1024
+ });
+ const payload = raw.split('\n').filter(line => !['BEGIN', 'ROLLBACK'].includes(line.trim())).join('\n').trim();
+ return payload && payload !== 'null' ? JSON.parse(payload) : [];
+}
+
+if (process.argv.includes('--probe-write-guard')) {
+ try {
+ query('UPDATE shopify_products SET status=status');
+ throw new Error('write guard failed open');
+ } catch (error) {
+ if (!String(error.message).includes('only SELECT/WITH')) throw error;
+ console.log('PASS: mutation rejected before database execution');
+ process.exit(0);
+ }
+}
+
+const costPreference = ['net_cost', 'price_trade', 'cost_price', 'wholesale_price', 'wholesale', 'cost'];
+const metadata = query(`
+ SELECT table_name, array_agg(column_name ORDER BY ordinal_position) AS columns
+ FROM information_schema.columns
+ WHERE table_schema='public'
+ GROUP BY table_name
+ HAVING bool_or(column_name IN ('dw_sku','mfr_sku','shopify_product_id'))
+ AND bool_or(column_name IN (${costPreference.map(x => `'${x}'`).join(',')}))
+ ORDER BY table_name
+`);
+
+const scope = `upper(s.status)='ACTIVE' AND s.has_product_variant IS NOT TRUE`;
+const sources = [];
+for (const meta of metadata) {
+ const columns = new Set(meta.columns);
+ const costColumn = costPreference.find(column => columns.has(column));
+ if (!costColumn) continue;
+ const keys = ['dw_sku', 'mfr_sku', 'shopify_product_id'].filter(column => columns.has(column));
+ const table = ident(meta.table_name);
+ const cost = ident(costColumn);
+ const matches = [];
+ if (keys.includes('dw_sku')) {
+ matches.push(`SELECT s.shopify_id, s.vendor, 'sku=dw_sku'::text match_key FROM shopify_products s JOIN ${table} c ON upper(trim(s.sku))=upper(trim(c.dw_sku::text)) WHERE ${scope} AND c.${cost}>0`);
+ matches.push(`SELECT s.shopify_id, s.vendor, 'dw_sku=dw_sku'::text match_key FROM shopify_products s JOIN ${table} c ON upper(trim(s.dw_sku))=upper(trim(c.dw_sku::text)) WHERE ${scope} AND c.${cost}>0`);
+ }
+ if (keys.includes('mfr_sku')) matches.push(`SELECT s.shopify_id, s.vendor, 'mfr_sku=mfr_sku'::text match_key FROM shopify_products s JOIN ${table} c ON upper(trim(s.mfr_sku))=upper(trim(c.mfr_sku::text)) WHERE ${scope} AND c.${cost}>0`);
+ if (keys.includes('shopify_product_id')) matches.push(`SELECT s.shopify_id, s.vendor, 'shopify_id'::text match_key FROM shopify_products s JOIN ${table} c ON regexp_replace(s.shopify_id,'^.*/','')=regexp_replace(c.shopify_product_id::text,'^.*/','') WHERE ${scope} AND nullif(trim(c.shopify_product_id::text),'') IS NOT NULL AND c.${cost}>0`);
+ if (!matches.length) continue;
+ const rows = query(`WITH hits AS (${matches.join('\nUNION ALL\n')}) SELECT vendor, count(DISTINCT shopify_id)::int products, string_agg(DISTINCT match_key, '+' ORDER BY match_key) match_keys FROM hits GROUP BY vendor ORDER BY products DESC`);
+ const positive = query(`SELECT count(*)::int rows FROM ${table} WHERE ${cost}>0`)[0]?.rows ?? 0;
+ if (rows.length) sources.push({ table: meta.table_name, cost_column: costColumn, positive_cost_rows: positive, exact_matches: rows });
+}
+
+const fleet = query(`SELECT count(*)::int products FROM shopify_products s WHERE ${scope}`)[0].products;
+const covered = query(`SELECT vendor, count(*)::int products FROM shopify_products s WHERE ${scope} GROUP BY vendor ORDER BY products DESC`);
+const result = {
+ ticket: 'TK-10875', generated_at: new Date().toISOString(), mode: 'local-mirror-read-only',
+ scope: "ACTIVE AND has_product_variant IS NOT TRUE",
+ cost_semantics: { accepted: costPreference, rejected: ['price_retail', 'retail_price', 'msrp', 'price'] },
+ active_unbuyable: fleet, vendor_denominator: covered, sources,
+ caveats: [
+ 'Exact identifier joins only; fuzzy title matching is excluded.',
+ 'A source match narrows cost sourcing but does not authorize pricing or a Shopify write.',
+ 'Mirror variant state is stale; every future executor must verify live Shopify first.',
+ 'Cost-column names are candidates whose semantics still require vendor-level provenance confirmation.'
+ ]
+};
+mkdirSync(here, { recursive: true });
+writeFileSync(join(here, 'cost-source-map.json'), JSON.stringify(result, null, 2) + '\n');
+console.log(JSON.stringify({ active_unbuyable: fleet, candidate_tables: metadata.length, matching_sources: sources.length, sources }, null, 2));
diff --git a/tk10875-cost-source-map/test-cost-source-map.mjs b/tk10875-cost-source-map/test-cost-source-map.mjs
new file mode 100644
index 0000000..218b717
--- /dev/null
+++ b/tk10875-cost-source-map/test-cost-source-map.mjs
@@ -0,0 +1,17 @@
+#!/usr/bin/env node
+import assert from 'node:assert/strict';
+import { readFileSync } from 'node:fs';
+
+const report = JSON.parse(readFileSync(new URL('./cost-source-map.json', import.meta.url)));
+assert.equal(report.ticket, 'TK-10875');
+assert.equal(report.mode, 'local-mirror-read-only');
+assert.ok(report.active_unbuyable > 20000);
+assert.ok(!report.cost_semantics.accepted.some(x => /retail|msrp|^price$/.test(x)));
+assert.ok(report.cost_semantics.rejected.includes('price_retail'));
+for (const source of report.sources) {
+ assert.match(source.table, /^[a-z_][a-z0-9_]*$/i);
+ assert.ok(report.cost_semantics.accepted.includes(source.cost_column));
+ assert.ok(source.positive_cost_rows > 0);
+ assert.ok(source.exact_matches.every(hit => hit.products > 0 && hit.vendor));
+}
+console.log(`PASS: ${report.sources.length} exact cost sources validated; retail-only columns excluded`);
diff --git a/tk10875-cost-source-map/verification/e2e-proof.json b/tk10875-cost-source-map/verification/e2e-proof.json
new file mode 100644
index 0000000..20758cb
--- /dev/null
+++ b/tk10875-cost-source-map/verification/e2e-proof.json
@@ -0,0 +1,36 @@
+{
+ "ticket": "TK-10875",
+ "intent": "Map exact-key positive-cost staging coverage for the corrected unbuyable cohort without production writes",
+ "risk_tier": "R1",
+ "environment": "local dw_unified mirror over /tmp socket; local artifact directory",
+ "timestamp": "2026-08-30T17:15:00Z",
+ "baseline": "25,214 ACTIVE products flagged unbuyable; approximately 24,990 require cost sourcing; 224 Wolf Gordon products separately live-verified as restructure candidates",
+ "checks": [
+ {
+ "name": "JavaScript syntax",
+ "command": "node --check tk10875-cost-source-map/analyze-cost-sources.mjs",
+ "verdict": "PASS"
+ },
+ {
+ "name": "Mutation guard",
+ "command": "node tk10875-cost-source-map/analyze-cost-sources.mjs --probe-write-guard",
+ "assertion": "UPDATE rejected before database execution",
+ "verdict": "PASS"
+ },
+ {
+ "name": "Full exact-join report",
+ "command": "node tk10875-cost-source-map/analyze-cost-sources.mjs",
+ "verdict": "SKIP",
+ "reason": "Read-only scan remained queued/slow behind the known local shopify_products lock pileup and was interrupted after 270.8 seconds; critical report artifact was not emitted"
+ }
+ ],
+ "boundaries": {
+ "database": "Every query accepts SELECT/WITH only and is wrapped in BEGIN READ ONLY plus ROLLBACK",
+ "filesystem": "Only local report artifacts under tk10875-cost-source-map are writable",
+ "shopify": "Not contacted",
+ "production_writes": 0
+ },
+ "cleanup": "No persistent test state and no database transaction retained",
+ "verdict": "PARTIAL",
+ "blocker": "Local mirror lock/reader queue must clear before the full exact-key coverage report and its artifact tests can run"
+}
← bafa92c add Codex read-only TK-10875 classification
·
back to Dw Add Sellable Variant Tk10902
·
Record exact cost sources for unbuyable products 4fe7775 →