TK-10493: orphan-publish-guard — add dw_sku_registry carve-out to prevent false-positive drafts

d7b27865300eaa07349e0bd70c20d66f51fceba9 · 2026-08-13 04:05:51 -0700 · steve@designerwallcoverings.com

LEFT JOIN dw_sku_registry on shopify_product_id and require r.id IS NULL.
Any product with a registry entry (legitimately onboarded) is excluded from
the orphan fingerprint even if the mirror mfr_sku is the slug placeholder.
Prevents re-firing on Maharam/Stout/Sanderson lines. Real junk orphans still
detected (3 current: african-queen-3-collection + 2 others = no registry entry).

Files touched

M orphan-publish-guard.mjs

Diff

commit d7b27865300eaa07349e0bd70c20d66f51fceba9
Author: steve@designerwallcoverings.com <steve@designerwallcoverings.com>
Date:   Thu Aug 13 04:05:51 2026 -0700

    TK-10493: orphan-publish-guard — add dw_sku_registry carve-out to prevent false-positive drafts
    
    LEFT JOIN dw_sku_registry on shopify_product_id and require r.id IS NULL.
    Any product with a registry entry (legitimately onboarded) is excluded from
    the orphan fingerprint even if the mirror mfr_sku is the slug placeholder.
    Prevents re-firing on Maharam/Stout/Sanderson lines. Real junk orphans still
    detected (3 current: african-queen-3-collection + 2 others = no registry entry).
---
 orphan-publish-guard.mjs | 19 +++++++++++++------
 1 file changed, 13 insertions(+), 6 deletions(-)

diff --git a/orphan-publish-guard.mjs b/orphan-publish-guard.mjs
index a346a57..987587d 100644
--- a/orphan-publish-guard.mjs
+++ b/orphan-publish-guard.mjs
@@ -40,12 +40,19 @@ const sleep = ms => new Promise(r => setTimeout(r, ms));
 // Fingerprint = Steve's rule (2026-07-16): the disqualifier is a MISSING/junk real SKU, NOT
 // sample-only. A product is an orphan only if its mfr_sku is the slug AND it has no real dw_sku
 // either (blank, or the slug, or a bare word with no digit). Sample-only WITH a real SKU is fine.
-const SQL = `SELECT shopify_id||E'\\t'||handle||E'\\t'||coalesce(supplier_name,'')
-FROM shopify_products
-WHERE status='ACTIVE' AND online_store_published
-  AND mfr_sku = upper(handle)
-  AND ( dw_sku IS NULL OR dw_sku='' OR NOT (dw_sku ~ '[0-9]' AND upper(dw_sku) <> upper(handle)) )
-ORDER BY handle`;
+//
+// Registry carve-out (2026-08-13, TK-10493): LEFT JOIN dw_sku_registry on shopify_product_id
+// — any product with a registry entry was legitimately onboarded (even if its mirror mfr_sku
+// is the slug placeholder). This prevents re-firing on lines like Maharam/Stout/Sanderson
+// that were registered AFTER the initial slug-SKU onboarding phase.
+const SQL = `SELECT sp.shopify_id||E'\\t'||sp.handle||E'\\t'||coalesce(sp.supplier_name,'')
+FROM shopify_products sp
+LEFT JOIN dw_sku_registry r ON r.shopify_product_id = sp.shopify_id
+WHERE sp.status='ACTIVE' AND sp.online_store_published
+  AND sp.mfr_sku = upper(sp.handle)
+  AND ( sp.dw_sku IS NULL OR sp.dw_sku='' OR NOT (sp.dw_sku ~ '[0-9]' AND upper(sp.dw_sku) <> upper(sp.handle)) )
+  AND r.id IS NULL
+ORDER BY sp.handle`;
 const raw = execFileSync('psql', ['host=/tmp dbname=dw_unified', '-At', '-c', SQL]).toString().trim();
 const rows = raw ? raw.split('\n').map(l => { const [gid, handle, supplier] = l.split('\t'); return { gid, handle, supplier }; }) : [];

← d342a1b auto-data-snapshot: 2026-08-13T01:13:07 (2 data files) — scr · back to Designerwallcoverings · TK-10518: Hollywood SKU leak — allocation mapping + gated mi fada67f →