TK-11046 WS-C: exclude DELETED_FROM_SHOPIFY from net-new anti-join + dedup set

bc6681c7ad030c95c2f4152fd6d5b40fe7efceda · 2026-09-03 12:40:30 -0700 · Steve

A deleted product's tombstone row (same mfr_sku) must not suppress its own legit
re-import. Both the NETNEW anti-join and the stage dedup CTE now exclude
DELETED_FROM_SHOPIFY (null-safe).

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01BbqKZLbd8emwVkNrxwtG2X

Files touched

M server.js

Diff

commit bc6681c7ad030c95c2f4152fd6d5b40fe7efceda
Author: Steve <steve@designerwallcoverings.com>
Date:   Thu Sep 3 12:40:30 2026 -0700

    TK-11046 WS-C: exclude DELETED_FROM_SHOPIFY from net-new anti-join + dedup set
    
    A deleted product's tombstone row (same mfr_sku) must not suppress its own legit
    re-import. Both the NETNEW anti-join and the stage dedup CTE now exclude
    DELETED_FROM_SHOPIFY (null-safe).
    
    Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
    Claude-Session: https://claude.ai/code/session_01BbqKZLbd8emwVkNrxwtG2X
---
 server.js | 7 +++++--
 1 file changed, 5 insertions(+), 2 deletions(-)

diff --git a/server.js b/server.js
index 3dc76a4..efa3d6d 100644
--- a/server.js
+++ b/server.js
@@ -121,8 +121,11 @@ const NEW_PRED = "(vc.sync_status='new' OR ((vc.on_shopify IS NOT TRUE) AND vc.s
 // Match normalized upper(trim()), hyphens preserved. idx_sp_mfr_upper_trim +
 // idx_vc_mfr_upper_trim cover the shopify side; a functional registry index on
 // upper(trim(mfr_sku)) would speed the registry NOT EXISTS (recommend adding).
+// TK-11046: exclude DELETED_FROM_SHOPIFY rows from the "already on Shopify" anti-join —
+// a product deleted from Shopify must NOT suppress its own legit re-import via a leftover
+// tombstone row carrying the same mfr_sku.
 const NETNEW_PRED = `(${NEW_PRED} AND vc.mfr_sku IS NOT NULL AND vc.mfr_sku <> ''
-  AND NOT EXISTS (SELECT 1 FROM shopify_products sx WHERE upper(trim(sx.mfr_sku)) = upper(trim(vc.mfr_sku)))
+  AND NOT EXISTS (SELECT 1 FROM shopify_products sx WHERE upper(trim(sx.mfr_sku)) = upper(trim(vc.mfr_sku)) AND sx.status IS DISTINCT FROM 'DELETED_FROM_SHOPIFY')
   AND NOT EXISTS (SELECT 1 FROM dw_sku_registry rx WHERE upper(trim(rx.mfr_sku)) = upper(trim(vc.mfr_sku))))`;
 // Resolve one shopify_products row per vendor_catalog row. shopify_id is a
 // gid://shopify/Product/<n> string (UNIQUE index); vc.shopify_product_id is the
@@ -373,7 +376,7 @@ function dedupFilterIds(ids) {
   const dup = q(`WITH cand AS (
                    SELECT vc.id, upper(trim(vc.mfr_sku)) AS k FROM vendor_catalog vc
                    WHERE vc.id IN (${idList}) AND vc.mfr_sku IS NOT NULL AND vc.mfr_sku <> ''),
-                 shop AS (SELECT DISTINCT upper(trim(mfr_sku)) k FROM shopify_products WHERE mfr_sku IS NOT NULL),
+                 shop AS (SELECT DISTINCT upper(trim(mfr_sku)) k FROM shopify_products WHERE mfr_sku IS NOT NULL AND status IS DISTINCT FROM 'DELETED_FROM_SHOPIFY'),
                  reg  AS (SELECT DISTINCT upper(trim(mfr_sku)) k FROM dw_sku_registry WHERE mfr_sku IS NOT NULL)
                  SELECT c.id FROM cand c
                  WHERE c.k IN (SELECT k FROM shop) OR c.k IN (SELECT k FROM reg)`);

← 4bb29d4 creds-safe fetch guard: resolve relative fetch vs credential · back to New Import Viewer · (newest)