← back to Dw Sku Integrity
TK-10900/A: content-match-gen.mjs — schema-adaptive catalog content-match recovery
0aa462b3fb83493fe8ed389c970b4758820c953e · 2026-08-31 02:08:10 -0700 · codex-10896
Recovers dw_sku for blank rows w/ no sku+no mfr_sku via title -> catalog
(pattern,color) -> real code. Mint-catalog vendors use mfr_sku(base-stripped)
not the DWAG-* mint dw_sku; base-code collapse; product_type BUCKET tiebreak
only on real multi-variant forks; strict unique-emit else review-queue (no guess).
Carnegie canary: 87.7% matched (3,439/3,921), 468 variant-forks queued, 14 parse-miss.
Verified: upholstery rows correctly pick the -upholstery S-variant. GATED draft.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Files touched
Diff
commit 0aa462b3fb83493fe8ed389c970b4758820c953e
Author: codex-10896 <steve@designerwallcoverings.com>
Date: Mon Aug 31 02:08:10 2026 -0700
TK-10900/A: content-match-gen.mjs — schema-adaptive catalog content-match recovery
Recovers dw_sku for blank rows w/ no sku+no mfr_sku via title -> catalog
(pattern,color) -> real code. Mint-catalog vendors use mfr_sku(base-stripped)
not the DWAG-* mint dw_sku; base-code collapse; product_type BUCKET tiebreak
only on real multi-variant forks; strict unique-emit else review-queue (no guess).
Carnegie canary: 87.7% matched (3,439/3,921), 468 variant-forks queued, 14 parse-miss.
Verified: upholstery rows correctly pick the -upholstery S-variant. GATED draft.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
content-match-gen.mjs | 150 ++++++++++++++++++++++++++++++++++++++++++++++++++
1 file changed, 150 insertions(+)
diff --git a/content-match-gen.mjs b/content-match-gen.mjs
new file mode 100644
index 0000000..11d24ca
--- /dev/null
+++ b/content-match-gen.mjs
@@ -0,0 +1,150 @@
+#!/usr/bin/env node
+// content-match-gen.mjs — READ-ONLY generator of GATED content-match apply plans (TK-10900, Program A).
+//
+// Recovers canonical dw_sku for blank Shopify rows that have NO sku and NO mfr_sku, by CONTENT-MATCHing
+// the Shopify title -> a vendor staging-catalog row -> that row's EXISTING real code. Recovers an
+// existing code — never mints. Cross-machine: blank rows from Kamatera (canonical shopify_products),
+// the pattern/color -> code map from the Mac2 <vendor>_catalog (staging is Mac2-canonical).
+//
+// Algorithm (see evidence/TK-10900-rescrape-workplan.md):
+// 1. code source: MINT-CATALOG vendors (carnegie/maharam/cmo_paris/stout) have greenfield-mint dw_sku
+// -> use mfr_sku instead; others use dw_sku. Greenfield-mint + reverted-ledger codes are excluded.
+// 2. base-code collapse: strip the DW type-suffix ('655788-panels-museums' -> '655788').
+// 3. key = (normPattern, normColor). Multiple catalog rows usually collapse to ONE base code.
+// 4. strict emit: write ONLY when the key maps 1:1 to exactly one base code. If >1 base code (a real
+// fabric-vs-wallcovering variant), disambiguate by product_type BUCKET; still >1 -> review queue.
+// Parse-miss / no-map / still-ambiguous -> review queue, NEVER a best guess (codex-hardened).
+//
+// Emits apply-plans-content-match/<vendor>/{apply.sql,undo.sql,restore-map.json,review-queue.json},
+// keyed on shopify_id + blank-guarded. NEVER writes a DB. Parent: TK-10900 (under TK-10896).
+//
+// Usage: node content-match-gen.mjs --vendor Carnegie --catalog carnegie_catalog \
+// [--kam 'ssh root@45.61.58.125 psql dw_unified'] [--ledger /tmp/_ledcodes.txt]
+
+import { execFileSync } from 'node:child_process';
+import { readFileSync, writeFileSync, mkdirSync, existsSync, rmSync } from 'node:fs';
+import { join, dirname } from 'node:path';
+import { fileURLToPath } from 'node:url';
+
+const HERE = dirname(fileURLToPath(import.meta.url));
+const US = '\x1f', RS = '\x1e';
+const arg = (n, d = null) => { const i = process.argv.indexOf(n); return i >= 0 ? process.argv[i + 1] : d; };
+const VENDOR = arg('--vendor', 'Carnegie');
+const CATALOG = arg('--catalog', null);
+const KAM = (arg('--kam', 'ssh root@45.61.58.125 psql dw_unified')).split(/\s+/);
+const LEDGER = arg('--ledger', '/tmp/_ledcodes.txt');
+const OUT = arg('--out', join(HERE, 'apply-plans-content-match'));
+const CODE_SHAPE = /^[A-Za-z0-9][A-Za-z0-9._/-]{0,39}$/;
+const GREENFIELD_MINT = /^DW(AG|AX|CX|ST|SC|DX|WG)/i;
+// Vendors whose catalog dw_sku is reverted greenfield-mint residue -> recover from mfr_sku instead.
+const MINT_CATALOG = new Set(['carnegie', 'maharam', 'cmo paris', 'cmo_paris', 'stout', 'stout textiles']);
+
+const sqlEscape = (v) => String(v).replace(/'/g, "''");
+const norm = (s) => (s || '').trim().toLowerCase().replace(/\s+/g, ' ');
+// Base code = token before the first DW type-suffix ('-panels', '-wallcoverings', ...). Real codes with
+// no such suffix pass through unchanged. Only applied to mint-catalog mfr_sku (dw_sku is used verbatim).
+const baseCode = (c) => (c || '').trim().split('-')[0].toUpperCase();
+// product_type semantic bucket so Shopify 'Upholstery' can match catalog 'Upholstery'/'Fabric' etc.
+const bucket = (pt) => {
+ const s = norm(pt);
+ if (/wallcover|wallpaper|mural|panel|museum|window|privacy|imo/.test(s)) return 'wall';
+ if (/upholst|fabric|textile|drapery|seat/.test(s)) return 'fabric';
+ return 'other';
+};
+
+function runPsql(cmd, sql) {
+ const out = execFileSync(cmd[0], [...cmd.slice(1), '-tA', '-F', US, '-R', RS], { input: sql, maxBuffer: 1 << 30, encoding: 'utf8' });
+ return out.split(RS).map((r) => r.replace(/\n$/, '')).filter(Boolean).map((r) => r.split(US));
+}
+const LOCAL = ['psql', '-h', '/tmp', '-d', 'dw_unified'];
+
+// 0. Resolve the catalog table + detect which columns it actually has (schema-adaptive).
+const catalog = CATALOG || `${VENDOR.toLowerCase().replace(/\s+/g, '_')}_catalog`;
+const cols = new Set(runPsql(LOCAL,
+ `SELECT column_name FROM information_schema.columns WHERE table_name='${sqlEscape(catalog)}';`).map((r) => r[0]));
+for (const need of ['dw_sku', 'mfr_sku', 'pattern_name', 'product_type']) {
+ if (!cols.has(need)) { console.error(`FATAL: ${catalog} lacks required column '${need}'. Columns: ${[...cols].join(', ')}`); process.exit(2); }
+}
+const colColumn = cols.has('color_number') ? 'color_number' : (cols.has('color_name') ? 'color_name' : null);
+if (!colColumn) { console.error(`FATAL: ${catalog} has neither color_number nor color_name.`); process.exit(2); }
+const useMfr = MINT_CATALOG.has(VENDOR.toLowerCase());
+
+// 1. Reverted-mint exclude list.
+const minted = new Set(existsSync(LEDGER) ? readFileSync(LEDGER, 'utf8').split('\n').map((s) => s.trim().toUpperCase()).filter(Boolean) : []);
+
+// 2. Build the catalog map: key "(pattern|color)" -> Map(baseCode -> Set(bucket)).
+const catRows = runPsql(LOCAL,
+ `SELECT coalesce(pattern_name,''), coalesce(${colColumn},''), coalesce(product_type,''), ` +
+ `coalesce(dw_sku,''), coalesce(mfr_sku,'') FROM ${catalog};`);
+const map = new Map();
+let catUsable = 0, catExcluded = 0;
+for (const [pat, col, ptype, dw, mfr] of catRows) {
+ const raw = useMfr ? mfr : dw;
+ if (!raw || !raw.trim()) { continue; }
+ const code = useMfr ? baseCode(mfr) : dw.trim();
+ if (!CODE_SHAPE.test(code) || GREENFIELD_MINT.test(code) || minted.has(code.toUpperCase())) { catExcluded++; continue; }
+ const key = norm(pat) + '|' + norm(col);
+ if (!norm(pat) || !norm(col)) continue;
+ if (!map.has(key)) map.set(key, new Map());
+ const codes = map.get(key);
+ if (!codes.has(code)) codes.set(code, new Set());
+ codes.get(code).add(bucket(ptype));
+ catUsable++;
+}
+
+// 3. Shopify blank rows (canonical). Parse title -> pattern + trailing color token.
+const shopRows = runPsql(KAM,
+ `SELECT coalesce(shopify_id,''), coalesce(title,''), coalesce(product_type,'') FROM shopify_products ` +
+ `WHERE lower(coalesce(status,''))='active' AND (dw_sku IS NULL OR btrim(dw_sku)='') ` +
+ `AND vendor ILIKE '${sqlEscape(VENDOR)}%';`);
+
+const vname = norm(VENDOR);
+const entries = [], review = [];
+const stat = { shopify_blank: shopRows.length, no_shopify_id: 0, parse_miss: 0, no_map: 0, ambiguous: 0, matched: 0 };
+for (const [sid, title, ptype] of shopRows) {
+ if (!sid) { stat.no_shopify_id++; continue; }
+ let body = norm(title);
+ if (body.startsWith(vname + ' ')) body = body.slice(vname.length + 1);
+ const m = body.match(/^(.*\S)\s+([0-9]+[a-z]?)$/); // "<pattern...> <colortoken>"
+ if (!m) { stat.parse_miss++; review.push({ shopify_id: sid, title, reason: 'parse_miss' }); continue; }
+ const key = norm(m[1]) + '|' + norm(m[2]);
+ const codes = map.get(key);
+ if (!codes) { stat.no_map++; review.push({ shopify_id: sid, title, key, reason: 'no_catalog_match' }); continue; }
+ let cand = [...codes.keys()];
+ if (cand.length > 1) { // real variant fork -> filter by product_type bucket
+ const want = bucket(ptype);
+ const filtered = cand.filter((c) => codes.get(c).has(want));
+ if (filtered.length === 1) cand = filtered;
+ }
+ if (cand.length !== 1) { stat.ambiguous++; review.push({ shopify_id: sid, title, key, candidates: [...codes.keys()], reason: 'ambiguous_multi_code' }); continue; }
+ entries.push({ shopify_id: sid, title, candidate: cand[0] });
+ stat.matched++;
+}
+
+// 4. Emit GATED artifacts.
+const HEADER =
+ '-- GATED -- canonical Kamatera dw_unified write. Do NOT run automatically.\n' +
+ `-- Content-match recovery (title -> ${catalog} ${useMfr ? 'mfr_sku(base)' : 'dw_sku'}, existing code, no mint). TK-10900.\n`;
+const applySql = HEADER + entries.map((e) =>
+ `UPDATE shopify_products SET dw_sku='${sqlEscape(e.candidate)}' WHERE shopify_id='${sqlEscape(e.shopify_id)}' AND (dw_sku IS NULL OR btrim(dw_sku)='');`).join('\n') + '\n';
+const undoSql = HEADER + entries.map((e) =>
+ `UPDATE shopify_products SET dw_sku=NULL WHERE shopify_id='${sqlEscape(e.shopify_id)}' AND dw_sku='${sqlEscape(e.candidate)}';`).join('\n') + '\n';
+const restoreMap = entries.map((e) => ({ shopify_id: e.shopify_id, title: e.title, column: 'dw_sku', old: null, new: e.candidate }));
+
+const dir = join(OUT, VENDOR);
+if (existsSync(dir)) rmSync(dir, { recursive: true, force: true });
+mkdirSync(dir, { recursive: true });
+writeFileSync(join(dir, 'apply.sql'), applySql);
+writeFileSync(join(dir, 'undo.sql'), undoSql);
+writeFileSync(join(dir, 'restore-map.json'), JSON.stringify(restoreMap, null, 2) + '\n');
+writeFileSync(join(dir, 'review-queue.json'), JSON.stringify(review, null, 2) + '\n');
+
+const summary = {
+ ticket: 'TK-10900', program: 'A-content-match', vendor: VENDOR, catalog, color_column: colColumn,
+ code_source: useMfr ? 'mfr_sku(base-stripped)' : 'dw_sku', catalog_usable: catUsable, catalog_excluded_mint: catExcluded,
+ ...stat, review_queue: review.length, statements: entries.length,
+ match_pct: stat.shopify_blank ? (100 * stat.matched / stat.shopify_blank).toFixed(1) + '%' : '0%',
+ note: 'NOTHING executed. apply.sql is a GATED draft; review-queue.json is NOT written.',
+};
+writeFileSync(join(OUT, `SUMMARY-${VENDOR}.json`), JSON.stringify(summary, null, 2) + '\n');
+console.log(JSON.stringify(summary, null, 2));
← 688c25d auto-data-snapshot: 2026-08-31T02:00:54 (4 data files) — app
·
back to Dw Sku Integrity
·
TK-10900/A: fix cross-class wrong-write (contrarian Defect B e47fdb5 →