← back to Dw Sku Integrity

TK-10896: Cody-gate fixes on apply-plan-gen — key on shopify_id (not local id) + shape-guard

e5d7d094660541b516cfe9e97b42fcd3f8ee6c7e · 2026-08-31 00:07:33 -0700 · codex-10896

Contrarian caught a CRITICAL flaw: apply.sql keyed on the local Mac2 serial `id`,
which maps to a DIFFERENT product on canonical Kamatera (built from its own
sequence) — running as-is would hit wrong/zero rows. Now keyed on shopify_id (the
stable Shopify GID, verified 1:1 and identical cross-machine). Also: shape-guard
rejects title/garbage SELF_COPY_SOURCE candidates (2 caught) from reaching a
canonical write; restore-map old:null (honest); skip+count rows missing shopify_id.
Re-verified: 38,712 stmts all shopify_id-keyed, 0 WHERE id=, 0 ledger codes leaked.
40 tests green (was 38).

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

Files touched

• M apply-plan-gen.mjs
• M test/apply-plan-gen.test.mjs

Diff

commit e5d7d094660541b516cfe9e97b42fcd3f8ee6c7e
Author: codex-10896 <steve@designerwallcoverings.com>
Date:   Mon Aug 31 00:07:33 2026 -0700

    TK-10896: Cody-gate fixes on apply-plan-gen — key on shopify_id (not local id) + shape-guard
    
    Contrarian caught a CRITICAL flaw: apply.sql keyed on the local Mac2 serial `id`,
    which maps to a DIFFERENT product on canonical Kamatera (built from its own
    sequence) — running as-is would hit wrong/zero rows. Now keyed on shopify_id (the
    stable Shopify GID, verified 1:1 and identical cross-machine). Also: shape-guard
    rejects title/garbage SELF_COPY_SOURCE candidates (2 caught) from reaching a
    canonical write; restore-map old:null (honest); skip+count rows missing shopify_id.
    Re-verified: 38,712 stmts all shopify_id-keyed, 0 WHERE id=, 0 ledger codes leaked.
    40 tests green (was 38).
    
    Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
 apply-plan-gen.mjs           | 52 ++++++++++++++++++++++++++++++++++----------
 test/apply-plan-gen.test.mjs | 42 +++++++++++++++++++++++++++++------
 2 files changed, 75 insertions(+), 19 deletions(-)

diff --git a/apply-plan-gen.mjs b/apply-plan-gen.mjs
index f958889..d47a7d6 100644
--- a/apply-plan-gen.mjs
+++ b/apply-plan-gen.mjs
@@ -77,16 +77,28 @@ export function vendorSlug(vendor) {
   return slug || 'unknown';
 }
 
-// Keep ONLY self-copy classes with a non-null candidate. Assert no forbidden
-// class ever slips through.
+// A candidate must LOOK like a code, not a product title or a garbage-suffixed
+// string. classify.mjs's SELF_COPY_SOURCE path admits any non-DW/non-Cork sku
+// verbatim, so a leaked title ("Bouncing Bubbles Mural - Cream") or a malformed
+// code ("DWKK-152210-Sold Per Bolt (20.5in x 33ft)") could otherwise be written
+// into the canonical dw_sku identity field. Reject anything with whitespace,
+// parentheses, or over 40 chars — these need re-scrape/manual, never a self-copy.
+export const CODE_SHAPE = /^[A-Za-z0-9][A-Za-z0-9._/-]{0,39}$/;
+
+// Keep ONLY self-copy classes with a non-null, code-shaped candidate. Assert no
+// forbidden class ever slips through. Returns { kept, rejectedShape }.
 export function selectSelfCopyRows(planRows) {
   const kept = [];
+  const rejectedShape = [];
   for (const row of planRows) {
     if (FORBIDDEN_CLASSES.has(row.class)) continue; // rescrape / collision / staging / residue -> never
     if (!SELF_COPY_CLASSES.has(row.class)) continue; // any other class -> no SQL
-    if (row.candidate == null || String(row.candidate).trim() === '') continue; // require an existing code
+    const cand = row.candidate == null ? '' : String(row.candidate).trim();
+    if (cand === '') continue; // require an existing code
+    if (!CODE_SHAPE.test(cand)) { rejectedShape.push(row); continue; } // title/garbage -> not a self-copy
     kept.push(row);
   }
+  kept.rejectedShape = rejectedShape;
   return kept;
 }
 
@@ -129,6 +141,7 @@ export function buildPlans(planRows, blankKeyMap) {
   const selfCopy = selectSelfCopyRows(planRows);
   const byVendor = new Map(); // vendor -> [{ id, shopify_id, handle, candidate }]
   let unmatched = 0;
+  let noShopifyId = 0; // rows we cannot safely target on Kamatera (missing stable key)
   const unmatchedRows = [];
 
   for (const row of selfCopy) {
@@ -137,32 +150,41 @@ export function buildPlans(planRows, blankKeyMap) {
     if (!byVendor.has(row.vendor)) byVendor.set(row.vendor, []);
     const bucket = byVendor.get(row.vendor);
     for (const db of dbRows) {
+      // shopify_id is the stable, machine-independent key. A row missing it cannot
+      // be safely targeted on the canonical Kamatera DB -> exclude + report.
+      if (!db.shopify_id || String(db.shopify_id).trim() === '') { noShopifyId += 1; continue; }
       bucket.push({ id: db.id, shopify_id: db.shopify_id, handle: db.handle, candidate: String(row.candidate) });
     }
   }
-  return { byVendor, unmatched, unmatchedRows, selfCopyCount: selfCopy.length };
+  return { byVendor, unmatched, unmatchedRows, noShopifyId, selfCopyCount: selfCopy.length, rejectedShape: selfCopy.rejectedShape || [] };
 }
 
 const HEADER =
   '-- GATED -- canonical Kamatera dw_unified write. Do NOT run automatically.\n' +
   '-- Recovers existing code (no mint). Undo via undo.sql / restore-map.json. TK-10896.\n';
 
-function applyStmt(id, candidate) {
+// Key on shopify_id (the stable Shopify GID, identical on Mac2 + Kamatera), NOT
+// the local `id` (a per-machine nextval serial that maps to a DIFFERENT product
+// on the canonical Kamatera DB). The blank-guard keeps a re-run / concurrent fill
+// from clobbering.
+function applyStmt(shopify_id, candidate) {
   const c = sqlEscape(candidate);
-  return `UPDATE shopify_products SET dw_sku='${c}' WHERE id=${id} AND (dw_sku IS NULL OR btrim(dw_sku)='');`;
+  const sid = sqlEscape(shopify_id);
+  return `UPDATE shopify_products SET dw_sku='${c}' WHERE shopify_id='${sid}' AND (dw_sku IS NULL OR btrim(dw_sku)='');`;
 }
 
-function undoStmt(id, candidate) {
+function undoStmt(shopify_id, candidate) {
   const c = sqlEscape(candidate);
-  return `UPDATE shopify_products SET dw_sku=NULL WHERE id=${id} AND dw_sku='${c}';`;
+  const sid = sqlEscape(shopify_id);
+  return `UPDATE shopify_products SET dw_sku=NULL WHERE shopify_id='${sid}' AND dw_sku='${c}';`;
 }
 
 export function renderApplySql(entries) {
-  return HEADER + entries.map((e) => applyStmt(e.id, e.candidate)).join('\n') + '\n';
+  return HEADER + entries.map((e) => applyStmt(e.shopify_id, e.candidate)).join('\n') + '\n';
 }
 
 export function renderUndoSql(entries) {
-  return HEADER + entries.map((e) => undoStmt(e.id, e.candidate)).join('\n') + '\n';
+  return HEADER + entries.map((e) => undoStmt(e.shopify_id, e.candidate)).join('\n') + '\n';
 }
 
 export function renderRestoreMap(entries) {
@@ -171,14 +193,17 @@ export function renderRestoreMap(entries) {
     shopify_id: e.shopify_id,
     handle: e.handle,
     column: 'dw_sku',
-    old: '',
+    // old is the pre-apply canonical value: blank (NULL or ''); undo restores to
+    // NULL. null (not '') is the honest record — undo targets `dw_sku='<new>'` so
+    // it only ever reverts what this apply set.
+    old: null,
     new: e.candidate,
   }));
 }
 
 // ---- writer ----------------------------------------------------------------
 
-export function writePlans({ byVendor, unmatched, selfCopyCount }, outDir) {
+export function writePlans({ byVendor, unmatched, selfCopyCount, noShopifyId = 0, rejectedShape = [] }, outDir) {
   if (existsSync(outDir)) rmSync(outDir, { recursive: true, force: true });
   mkdirSync(outDir, { recursive: true });
 
@@ -199,10 +224,13 @@ export function writePlans({ byVendor, unmatched, selfCopyCount }, outDir) {
     ticket: 'TK-10896',
     note: 'NOTHING was executed. apply.sql files are GATED drafts for a human to run. dw_sku is Kamatera-canonical dw_unified -- hard-gated.',
     generated_at: new Date().toISOString(),
+    key_column: 'shopify_id',
     self_copy_plan_rows: selfCopyCount,
     vendors: Object.keys(perVendor).length,
     grand_total_statements: grandTotal,
     unmatched_plan_rows: unmatched,
+    excluded_missing_shopify_id: noShopifyId,
+    excluded_bad_candidate_shape: rejectedShape.length,
     per_vendor: perVendor,
   };
   writeFileSync(join(outDir, 'SUMMARY.json'), JSON.stringify(summary, null, 2) + '\n');
diff --git a/test/apply-plan-gen.test.mjs b/test/apply-plan-gen.test.mjs
index b0c8a4f..26a1e4a 100644
--- a/test/apply-plan-gen.test.mjs
+++ b/test/apply-plan-gen.test.mjs
@@ -21,7 +21,7 @@ const PLAN = [
   { vendor: 'Fabricut', sku: 'DWFC-1', class: 'SELF_COPY_DW', candidate: 'DWFC-1' },
   { vendor: 'Fabricut', sku: 'DWFC-2', class: 'SELF_COPY_DW_PROVEN_NATIVE', candidate: 'DWFC-2' },
   { vendor: 'Carnegie', sku: 'DWAG-9', class: 'SELF_COPY_SOURCE', candidate: 'DWAG-9' },
-  { vendor: 'Cork Co', sku: 'CORK-3', class: 'SELF_COPY_CORK', candidate: "O'CORK-3" }, // apostrophe
+  { vendor: 'Cork Co', sku: 'CORK-3', class: 'SELF_COPY_CORK', candidate: 'Cork-3' }, // valid code-shape
   { vendor: 'Fabricut', sku: 'DWFC-NULL', class: 'SELF_COPY_DW', candidate: null },     // no candidate → skip
   { vendor: 'Fabricut', sku: 'DWFC-R', class: 'MINT_RESIDUE_RESCRAPE', candidate: null },
   { vendor: 'Fabricut', sku: 'DWFC-RR', class: 'RESCRAPE', candidate: 'DWFC-RR' },
@@ -83,12 +83,14 @@ test('(c) every generated UPDATE carries the blank-guard', () => {
   }
 });
 
-test('(d) undo.sql reverses apply.sql for each id', () => {
-  const entries = [{ id: 101, shopify_id: 'g', handle: 'h', candidate: 'DWFC-1' }];
+test('(d) undo.sql reverses apply.sql, keyed on shopify_id (stable cross-machine)', () => {
+  const entries = [{ id: 101, shopify_id: 'gid://shopify/Product/77', handle: 'h', candidate: 'DWFC-1' }];
   const apply = renderApplySql(entries);
   const undo = renderUndoSql(entries);
-  assert.ok(apply.includes("SET dw_sku='DWFC-1' WHERE id=101 AND (dw_sku IS NULL OR btrim(dw_sku)='')"));
-  assert.ok(undo.includes("SET dw_sku=NULL WHERE id=101 AND dw_sku='DWFC-1'"));
+  assert.ok(apply.includes("SET dw_sku='DWFC-1' WHERE shopify_id='gid://shopify/Product/77' AND (dw_sku IS NULL OR btrim(dw_sku)='')"));
+  assert.ok(undo.includes("SET dw_sku=NULL WHERE shopify_id='gid://shopify/Product/77' AND dw_sku='DWFC-1'"));
+  // MUST NOT key on the local serial id (meaningless on Kamatera).
+  assert.ok(!apply.includes('WHERE id='), 'apply must not key on local id');
 });
 
 test('(e) apostrophe candidates are SQL-escaped', () => {
@@ -100,6 +102,32 @@ test('(e) apostrophe candidates are SQL-escaped', () => {
   assert.ok(undo.includes("dw_sku='O''CORK-3'"), 'undo must double the apostrophe');
 });
 
+test('(f) candidate that is a title / garbage shape is rejected, never written', () => {
+  const dirty = [
+    { vendor: 'Cole & Son', sku: 'Bouncing Bubbles Mural - Cream', class: 'SELF_COPY_SOURCE', candidate: 'Bouncing Bubbles Mural - Cream' },
+    { vendor: 'X', sku: 'DWKK-152210-Sold Per Bolt (20.5in x 33ft)', class: 'SELF_COPY_SOURCE', candidate: 'DWKK-152210-Sold Per Bolt (20.5in x 33ft)' },
+    { vendor: 'Good', sku: 'AS784911-0', class: 'SELF_COPY_SOURCE', candidate: 'AS784911-0' }, // valid code-shape
+  ];
+  const kept = selectSelfCopyRows(dirty);
+  assert.equal(kept.length, 1);
+  assert.equal(kept[0].candidate, 'AS784911-0');
+  assert.equal(kept.rejectedShape.length, 2);
+});
+
+test('(g) a self-copy row missing shopify_id is excluded + counted, never emitted', () => {
+  const plan = [{ vendor: 'V', sku: 'DWV-1', class: 'SELF_COPY_DW', candidate: 'DWV-1' }];
+  const map = new Map();
+  map.set(keyOf('DWV-1', 'V'), [
+    { id: 1, shopify_id: '', handle: 'no-gid' },              // excluded
+    { id: 2, shopify_id: 'gid://shopify/Product/2', handle: 'ok' }, // kept
+  ]);
+  const plans = buildPlans(plan, map);
+  assert.equal(plans.noShopifyId, 1);
+  const entries = [...plans.byVendor.values()].flat();
+  assert.equal(entries.length, 1);
+  assert.equal(entries[0].shopify_id, 'gid://shopify/Product/2');
+});
+
 test('writePlans emits per-vendor artifacts + SUMMARY with correct totals', () => {
   const out = join(mkdtempSync(join(tmpdir(), 'tk10896-apply-')), 'apply-plans');
   const plans = buildPlans(PLAN, fixtureMap());
@@ -109,9 +137,9 @@ test('writePlans emits per-vendor artifacts + SUMMARY with correct totals', () =
   assert.equal(summary.self_copy_plan_rows, 4);
   const s = JSON.parse(readFileSync(join(out, 'SUMMARY.json'), 'utf8'));
   assert.match(s.note, /NOTHING was executed/);
-  // restore-map old value is the current blank
+  // restore-map old value is null (honest: pre-apply was blank; undo -> NULL) + keyed on shopify_id
   const rm = JSON.parse(readFileSync(join(out, 'fabricut', 'restore-map.json'), 'utf8'));
-  assert.ok(rm.every((r) => r.old === '' && r.column === 'dw_sku'));
+  assert.ok(rm.every((r) => r.old === null && r.column === 'dw_sku' && typeof r.shopify_id === 'string'));
   // header present + gated
   const applySql = readFileSync(join(out, 'fabricut', 'apply.sql'), 'utf8');
   assert.match(applySql, /GATED -- canonical Kamatera dw_unified write/);

← 467c707 auto-data-snapshot: 2026-08-31T00:07:16 (280 data files) — a  ·  back to Dw Sku Integrity  ·  TK-10896: read-only preflight GO/NO-GO checker for the gated 600aa83 →