[object Object]

← back to Designer Wallcoverings

TK-10002: forward-only variant-SKU collision guard (skuTakenOnActiveProduct)

f8969b83264c0452ccf2ce7cc651f48463738645 · 2026-07-28 07:16:18 -0700 · vp-dw-commerce

Adds a pre-create check that rejects a mint whose dw_sku is already carried by a
NON-ARCHIVED product, closing the gap where a drifted registry lets registerSku()
re-mint a live SKU. Wired into the new-product-import template between register and
create. Mirrored into both sku-registry libs. NOT a DB constraint (461 legacy
violations remain).

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

Files touched

Diff

commit f8969b83264c0452ccf2ce7cc651f48463738645
Author: vp-dw-commerce <steve@designerwallcoverings.com>
Date:   Tue Jul 28 07:16:18 2026 -0700

    TK-10002: forward-only variant-SKU collision guard (skuTakenOnActiveProduct)
    
    Adds a pre-create check that rejects a mint whose dw_sku is already carried by a
    NON-ARCHIVED product, closing the gap where a drifted registry lets registerSku()
    re-mint a live SKU. Wired into the new-product-import template between register and
    create. Mirrored into both sku-registry libs. NOT a DB constraint (461 legacy
    violations remain).
    
    Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
 DW-Programming/vendor-crawlers/lib/sku-registry.js | 34 ++++++++++++++++++++++
 .../templates/new-product-import-template.js       | 17 ++++++++++-
 2 files changed, 50 insertions(+), 1 deletion(-)

diff --git a/DW-Programming/vendor-crawlers/lib/sku-registry.js b/DW-Programming/vendor-crawlers/lib/sku-registry.js
index 954c252a..56538055 100644
--- a/DW-Programming/vendor-crawlers/lib/sku-registry.js
+++ b/DW-Programming/vendor-crawlers/lib/sku-registry.js
@@ -215,6 +215,39 @@ async function registerProductLine(vendorPrefix, vendorName, mfrSkus) {
   return results;
 }
 
+/**
+ * FORWARD-ONLY COLLISION GUARD (TK-10002, 2026-07-28) — mirror of the shopify/scripts
+ * lib. Verify a minted DW SKU is not already carried by a NON-ARCHIVED product before
+ * a create, closing the gap where a drifted registry lets registerSku() re-mint a
+ * live SKU. Matches base with/without variant suffix across variant_sku/sku/dw_sku.
+ * NOT a DB constraint (legacy violations still exist).
+ * @param {string} dwSku
+ * @returns {Promise<{taken:boolean, byProductId?:number, byStatus?:string, matchedSku?:string}>}
+ */
+async function skuTakenOnActiveProduct(dwSku) {
+  if (!dwSku) return { taken: false };
+  try {
+    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)
+          AND UPPER(COALESCE(status,'')) <> 'ARCHIVED'
+        LIMIT 1`,
+      [dwSku, dwSku + '-%']);
+    if (rows.length) {
+      const r = rows[0];
+      return { taken: true, byProductId: r.id, byStatus: r.status,
+               matchedSku: r.variant_sku || r.sku || r.dw_sku };
+    }
+    return { taken: false };
+  } catch (err) {
+    console.error('skuTakenOnActiveProduct error:', err.message);
+    return { taken: false, error: err.message };
+  }
+}
+
 /**
  * Update registry with Shopify product ID after creation
  * @param {string} dwSku - The DW SKU (e.g., 'DWK-030001')
@@ -290,6 +323,7 @@ module.exports = {
   getNextSkuNumber,
   registerSku,
   registerProductLine,  // For product lines with colorways (spaced by 10)
+  skuTakenOnActiveProduct,  // TK-10002: forward-only variant-SKU collision guard
   updateShopifyInfo,
   getVendorSkus,
   getStats,
diff --git a/shopify/scripts/templates/new-product-import-template.js b/shopify/scripts/templates/new-product-import-template.js
index bd886e23..f1f4e4f3 100644
--- a/shopify/scripts/templates/new-product-import-template.js
+++ b/shopify/scripts/templates/new-product-import-template.js
@@ -13,7 +13,7 @@
  */
 
 const fetch = globalThis.fetch; // node-fetch shim removed -- Node 26 global fetch
-const { checkDuplicate, registerSku, updateShopifyInfo, getStats, close } = require('../lib/sku-registry');
+const { checkDuplicate, registerSku, skuTakenOnActiveProduct, updateShopifyInfo, getStats, close } = require('../lib/sku-registry');
 
 // ==================== CONFIGURATION ====================
 // UPDATE THESE VALUES FOR YOUR VENDOR
@@ -138,6 +138,21 @@ async function importProducts() {
         continue;
       }
 
+      // ===== STEP 2b: FORWARD-ONLY VARIANT-SKU COLLISION GUARD (TK-10002) =====
+      // checkDuplicate() above only answers "has this mfr been imported?" (registry
+      // by mfr_sku). It does NOT verify the dw_sku we just minted is free as a live
+      // variant SKU — and the registry drifts from live, so registerSku() can hand
+      // back a dw_sku that already sits on an ACTIVE product. Reject the create if so
+      // rather than stamping a second product with the same variant SKU (the exact
+      // failure class Phase 1 remediated). Skip (never clobber the live listing).
+      const collision = await skuTakenOnActiveProduct(dwSku);
+      if (collision.taken) {
+        console.log(`⛔ SKIP: ${mfrSku} → minted ${dwSku} but it is already live on ` +
+          `product ${collision.byProductId} (${collision.byStatus}) as ${collision.matchedSku}`);
+        results.skipped++;
+        continue;
+      }
+
       // ===== STEP 3: CREATE IN SHOPIFY =====
       const shopifyProduct = await createShopifyProduct({
         title: `${title} | ${VENDOR_NAME}`,

← fc665d1c TK-135: add Shape: to label normalizer strip set  ·  back to Designer Wallcoverings  ·  Track shopify/scripts/lib operational source (registry+guard 6984f4c6 →