sanderson verify: suppress deleted-tombstone false-positive orphans (TK-11046)

df3ec4b60f4a2f0208b4efec4d8ab087eadc352c · 2026-09-03 12:06:08 -0700 · Steve Abrams

The local dw_unified mirror upserts but never purges live deletions, so the -1
duplicate DRAFT rows deleted under TK-11070 stayed as stale non-ACTIVE rows and
fired a permanent ORPHANS=4 false alarm every 08:00 run. The orphan detector now
resolves each candidate's product_id from golive-done.jsonl and does a read-only
Shopify GET: 404 => deleted tombstone (skip), 200+active => stale mirror (skip),
200+non-active => genuine stuck DRAFT (flag), transient => flag "(unverified)"
fail-safe. Read-only GET, no --apply, no Shopify/data writes. ORPHANS 4 -> 0.

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

Files touched

M scripts/sanderson-onboard/verify.sh

Diff

commit df3ec4b60f4a2f0208b4efec4d8ab087eadc352c
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Thu Sep 3 12:06:08 2026 -0700

    sanderson verify: suppress deleted-tombstone false-positive orphans (TK-11046)
    
    The local dw_unified mirror upserts but never purges live deletions, so the -1
    duplicate DRAFT rows deleted under TK-11070 stayed as stale non-ACTIVE rows and
    fired a permanent ORPHANS=4 false alarm every 08:00 run. The orphan detector now
    resolves each candidate's product_id from golive-done.jsonl and does a read-only
    Shopify GET: 404 => deleted tombstone (skip), 200+active => stale mirror (skip),
    200+non-active => genuine stuck DRAFT (flag), transient => flag "(unverified)"
    fail-safe. Read-only GET, no --apply, no Shopify/data writes. ORPHANS 4 -> 0.
    
    Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
    Claude-Session: https://claude.ai/code/session_01BbqKZLbd8emwVkNrxwtG2X
---
 scripts/sanderson-onboard/verify.sh | 54 ++++++++++++++++++++++++++++++++++---
 1 file changed, 50 insertions(+), 4 deletions(-)

diff --git a/scripts/sanderson-onboard/verify.sh b/scripts/sanderson-onboard/verify.sh
index 15ed144..561ed6e 100755
--- a/scripts/sanderson-onboard/verify.sh
+++ b/scripts/sanderson-onboard/verify.sh
@@ -13,6 +13,16 @@ NOW="$(date +%Y-%m-%dT%H:%M:%S)"
 STATE="out/verify-state.json"
 LATEST="out/latest.json"
 
+# Shopify read creds (read_products is enough; a GET is not an --apply/write). Mirrors
+# run.sh — token from ../../.env then secrets-manager. Used by the orphan detector (4b)
+# to distinguish a DELETED tombstone (404) from a genuine stuck DRAFT (200 non-active). (TK-11046)
+SHOP_STORE="designer-laboratory-sandbox.myshopify.com"
+SHOP_TOKEN=""
+for f in "../../.env" "$HOME/Projects/secrets-manager/.env"; do
+  [ -n "$SHOP_TOKEN" ] && break
+  [ -f "$f" ] && SHOP_TOKEN="$(grep -hiE '^SHOPIFY_ADMIN_TOKEN=' "$f" | head -1 | cut -d= -f2- | tr -d '"'"'"' ')"
+done
+
 verdict="PASS"; reasons=""
 add(){ reasons="${reasons:+$reasons; }$1"; }
 worse(){ case "$1" in FAIL) verdict=FAIL;; WARN) [ "$verdict" = PASS ] && verdict=WARN;; esac; }
@@ -57,17 +67,53 @@ fi
 # 4b. orphan detector — a SKU marked done in golive-done.jsonl but NOT ACTIVE on the
 #     store is silently stuck (go-live won't retry, create won't recreate). This is the
 #     class that hid 4 CLEAR products in DRAFT (TK-11046). Read-only; skips if DB down.
+#
+#     The local mirror UPSERTS but never PURGES deletions, so it lags behind live
+#     deletes: a duplicate DRAFT that was DELETED off Shopify (e.g. the -1 dup drafts
+#     removed under TK-11070) stays in the mirror as a stale non-ACTIVE row and fired a
+#     permanent false-positive ORPHANS alert every run. Fix: before flagging, resolve the
+#     product_id (from the golive-done.jsonl line) and do a read-only Shopify GET.
+#       404            => DELETED tombstone  => SKIP (not an orphan).
+#       200 & active   => mirror just stale  => SKIP.
+#       200 & !active  => GENUINE stuck DRAFT => FLAG (the real bug this detector catches).
+#       000/429/5xx/TO => transient          => FLAG "(unverified)" (fail-safe: alert, don't drop).
 ORPHANS=0; ORPHAN_SKUS=""
+# read-only Shopify status probe: echoes deleted|active|orphan|unverified for a product_id
+probe_pid(){
+  local pid="$1" code body livestatus attempt=0
+  [ -z "$pid" ] && { echo unverified; return; }
+  [ -z "$SHOP_TOKEN" ] && { echo unverified; return; }
+  while [ "$attempt" -lt 2 ]; do
+    body="$(curl -s -w $'\n%{http_code}' --max-time 8 \
+      -H "X-Shopify-Access-Token: $SHOP_TOKEN" \
+      "https://$SHOP_STORE/admin/api/2024-10/products/$pid.json" 2>/dev/null)"
+    code="${body##*$'\n'}"
+    case "$code" in
+      404) echo deleted; return;;
+      200)
+        livestatus="$(printf '%s' "${body%$'\n'*}" | sed -n 's/.*"status":"\([a-z]*\)".*/\1/p' | head -1)"
+        [ "$livestatus" = "active" ] && { echo active; return; }
+        echo orphan; return;;
+    esac
+    attempt=$((attempt+1)); sleep 1
+  done
+  echo unverified
+}
 PSQL="$(command -v psql || echo /opt/homebrew/opt/postgresql@14/bin/psql)"
 if [ -x "$PSQL" ] || command -v psql >/dev/null 2>&1; then
   NONACTIVE="$("$PSQL" -h /tmp -d dw_unified -tAc "SELECT sku FROM shopify_products WHERE vendor='Sanderson' AND status<>'ACTIVE'" 2>/dev/null)"
   if [ $? -eq 0 ]; then
     for s in $NONACTIVE; do
-      if grep -q "\"$s\"" out/golive-done.jsonl 2>/dev/null || grep -q "$s" out/golive-done.jsonl 2>/dev/null; then
-        ORPHANS=$((ORPHANS+1)); ORPHAN_SKUS="${ORPHAN_SKUS:+$ORPHAN_SKUS,}$s"
-      fi
+      line="$(grep -m1 "\"$s\"" out/golive-done.jsonl 2>/dev/null || grep -m1 "$s" out/golive-done.jsonl 2>/dev/null)"
+      [ -z "$line" ] && continue   # never marked golive-done -> not an orphan
+      pid="$(printf '%s' "$line" | sed -n 's/.*"product_id":"\{0,1\}\([0-9]*\).*/\1/p')"
+      case "$(probe_pid "$pid")" in
+        deleted|active) : ;;                                  # tombstone or stale-mirror -> not an orphan
+        orphan)     ORPHANS=$((ORPHANS+1)); ORPHAN_SKUS="${ORPHAN_SKUS:+$ORPHAN_SKUS,}$s";;
+        unverified) ORPHANS=$((ORPHANS+1)); ORPHAN_SKUS="${ORPHAN_SKUS:+$ORPHAN_SKUS,}$s(unverified)";;
+      esac
     done
-    if [ "$ORPHANS" -gt 0 ]; then add "ORPHANS=$ORPHANS marked golive-done but DRAFT: $ORPHAN_SKUS"; worse WARN; fi
+    if [ "$ORPHANS" -gt 0 ]; then add "ORPHANS=$ORPHANS marked golive-done but not-active on live: $ORPHAN_SKUS"; worse WARN; fi
   else
     add "orphan-check skipped (mirror unreachable)"
   fi

← e45ffc0 TK-11186: A2 GMC-channel unpublisher (PJ-scoped, publication · back to Designerwallcoverings · TK-11186 Step D+E prep: showroom-vendor guard in New Arrival 5555d3e →