TK-10002: harden skuTakenOnActiveProduct — fail-closed on error + case-fold comparison (both lib copies)

de4cd0e3e999e557db0cfa4a81729e039af6aa9f · 2026-07-28 11:56:25 -0700 · vp-dw-commerce
Files touched

M DW-Programming/vendor-crawlers/lib/sku-registry.js
M shopify/scripts/lib/sku-registry.js
Diff

commit de4cd0e3e999e557db0cfa4a81729e039af6aa9f
Author: vp-dw-commerce <steve@designerwallcoverings.com>
Date:   Tue Jul 28 11:56:25 2026 -0700

    TK-10002: harden skuTakenOnActiveProduct — fail-closed on error + case-fold comparison (both lib copies)
---
 DW-Programming/vendor-crawlers/lib/sku-registry.js | 17 ++++++++++++-----
 shopify/scripts/lib/sku-registry.js                | 20 ++++++++++++--------
 2 files changed, 24 insertions(+), 13 deletions(-)

diff --git a/DW-Programming/vendor-crawlers/lib/sku-registry.js b/DW-Programming/vendor-crawlers/lib/sku-registry.js
index 56538055..6df4c84e 100644
--- a/DW-Programming/vendor-crawlers/lib/sku-registry.js
+++ b/DW-Programming/vendor-crawlers/lib/sku-registry.js
@@ -227,12 +227,15 @@ async function registerProductLine(vendorPrefix, vendorName, mfrSkus) {
 async function skuTakenOnActiveProduct(dwSku) {
   if (!dwSku) return { taken: false };
   try {
+    // TK-10002 hardening (2026-07-28): case-fold the comparison so `DWAT-65039`
+    // and `dwat-65039` are treated as the SAME sku (a case-difference is how the
+    // memo-sample twins slipped a duplicate `-Sample` past the old `=` check).
     const { rows } = await pool.query(
       `SELECT id, status, variant_sku, sku, dw_sku
          FROM shopify_products
-        WHERE (variant_sku = $1 OR variant_sku LIKE $2
-            OR sku         = $1 OR sku         LIKE $2
-            OR dw_sku      = $1 OR dw_sku      LIKE $2)
+        WHERE (UPPER(variant_sku) = UPPER($1) OR UPPER(variant_sku) LIKE UPPER($2)
+            OR UPPER(sku)         = UPPER($1) OR UPPER(sku)         LIKE UPPER($2)
+            OR UPPER(dw_sku)      = UPPER($1) OR UPPER(dw_sku)      LIKE UPPER($2))
           AND UPPER(COALESCE(status,'')) <> 'ARCHIVED'
         LIMIT 1`,
       [dwSku, dwSku + '-%']);
@@ -243,8 +246,12 @@ async function skuTakenOnActiveProduct(dwSku) {
     }
     return { taken: false };
   } catch (err) {
-    console.error('skuTakenOnActiveProduct error:', err.message);
-    return { taken: false, error: err.message };
+    // TK-10002 hardening (2026-07-28): FAIL CLOSED. The old catch returned
+    // {taken:false}, so a DB blip / lock-storm (we had one 2026-07-21) would
+    // SILENTLY admit a colliding SKU. On error we now report taken:true so the
+    // caller BLOCKS the import rather than minting a live duplicate.
+    console.error('skuTakenOnActiveProduct error (failing CLOSED):', err.message);
+    return { taken: true, error: err.message };
   }
 }
 
diff --git a/shopify/scripts/lib/sku-registry.js b/shopify/scripts/lib/sku-registry.js
index 57878213..fbf264c4 100644
--- a/shopify/scripts/lib/sku-registry.js
+++ b/shopify/scripts/lib/sku-registry.js
@@ -280,12 +280,15 @@ async function registerProductLine(vendorPrefix, vendorName, mfrSkus) {
 async function skuTakenOnActiveProduct(dwSku) {
   if (!dwSku) return { taken: false };
   try {
+    // TK-10002 hardening (2026-07-28): case-fold the comparison so `DWAT-65039`
+    // and `dwat-65039` are treated as the SAME sku (a case-difference is how the
+    // memo-sample twins carried a duplicate `-Sample` past the old `=` check).
     const { rows } = await pool.query(
       `SELECT id, status, variant_sku, sku, dw_sku
          FROM shopify_products
-        WHERE (variant_sku = $1 OR variant_sku LIKE $2
-            OR sku         = $1 OR sku         LIKE $2
-            OR dw_sku      = $1 OR dw_sku      LIKE $2)
+        WHERE (UPPER(variant_sku) = UPPER($1) OR UPPER(variant_sku) LIKE UPPER($2)
+            OR UPPER(sku)         = UPPER($1) OR UPPER(sku)         LIKE UPPER($2)
+            OR UPPER(dw_sku)      = UPPER($1) OR UPPER(dw_sku)      LIKE UPPER($2))
           AND UPPER(COALESCE(status,'')) <> 'ARCHIVED'
         LIMIT 1`,
       [dwSku, dwSku + '-%']);
@@ -296,11 +299,12 @@ async function skuTakenOnActiveProduct(dwSku) {
     }
     return { taken: false };
   } catch (err) {
-    // Fail OPEN would allow a collision; fail CLOSED (report taken) would block valid
-    // imports on a DB hiccup. Surface the error so the caller can decide; default to
-    // "unknown = don't block" but log loudly.
-    console.error('skuTakenOnActiveProduct error:', err.message);
-    return { taken: false, error: err.message };
+    // TK-10002 hardening (2026-07-28): FAIL CLOSED. Fail-open silently ADMITS a
+    // colliding SKU on a DB blip / lock-storm (we had one 2026-07-21) — the exact
+    // failure mode that mints live duplicates. Report taken:true so the caller
+    // BLOCKS the import; a blocked import is recoverable, a minted duplicate is not.
+    console.error('skuTakenOnActiveProduct error (failing CLOSED):', err.message);
+    return { taken: true, error: err.message };
   }
 }
← 7ccd2e42 TK-10002 Phase-2a: null-Sample + low-risk collision remediat · back to Designer Wallcoverings · auto-save: 2026-07-28T12:00:18 (19 files) — DW-Programming/1 dda80057 →