← back to Dw Validator Debug TK11314
TK-11046 WS-A: sweep DRAFT/ARCHIVED for phantom deletes (root fix)
6f26210b0881e4fcfb5937f68386d8c666d2f3c3 · 2026-09-03 12:31:32 -0700 · Steve
markPhantomDeleted only 404-verified status='ACTIVE' rows, so a product
deleted while DRAFT or ARCHIVED froze at its last non-ACTIVE status forever
instead of becoming DELETED_FROM_SHOPIFY. Add markPhantomDeletedNonActive on a
SEPARATE budget (own candidate cap 300/run, own confirmed cap 250/run, own
broken-crawl guard) so the non-active backlog drains over several nightly runs
and can never trip the ACTIVE 500-candidate circuit-breaker. ACTIVE sweep is
byte-for-byte unchanged. Concurrency-safe UPDATE (AND status=old) per Cody.
DUPLICATE_IMPORT opt-in only (provenance unaudited).
Cody /contrarian: 4x SHIP IT / 1x FIX FIRST -> folded in the status guard.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01BbqKZLbd8emwVkNrxwtG2X
Files touched
M shopify/scripts/sync-shopify-products.js
Diff
commit 6f26210b0881e4fcfb5937f68386d8c666d2f3c3
Author: Steve <steve@designerwallcoverings.com>
Date: Thu Sep 3 12:31:32 2026 -0700
TK-11046 WS-A: sweep DRAFT/ARCHIVED for phantom deletes (root fix)
markPhantomDeleted only 404-verified status='ACTIVE' rows, so a product
deleted while DRAFT or ARCHIVED froze at its last non-ACTIVE status forever
instead of becoming DELETED_FROM_SHOPIFY. Add markPhantomDeletedNonActive on a
SEPARATE budget (own candidate cap 300/run, own confirmed cap 250/run, own
broken-crawl guard) so the non-active backlog drains over several nightly runs
and can never trip the ACTIVE 500-candidate circuit-breaker. ACTIVE sweep is
byte-for-byte unchanged. Concurrency-safe UPDATE (AND status=old) per Cody.
DUPLICATE_IMPORT opt-in only (provenance unaudited).
Cody /contrarian: 4x SHIP IT / 1x FIX FIRST -> folded in the status guard.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01BbqKZLbd8emwVkNrxwtG2X
---
shopify/scripts/sync-shopify-products.js | 221 +++++++++++++++++++++++++++++++
1 file changed, 221 insertions(+)
diff --git a/shopify/scripts/sync-shopify-products.js b/shopify/scripts/sync-shopify-products.js
index a5864b2f..e5f2af0e 100644
--- a/shopify/scripts/sync-shopify-products.js
+++ b/shopify/scripts/sync-shopify-products.js
@@ -106,6 +106,36 @@ const MAX_CANDIDATES_TO_CHECK = 500;
// the number of rows we're about to irreversibly mark as deleted.
const MAX_CONFIRMED_DELETIONS_PER_RUN = 50;
+// ─────────────────────────────────────────────────────────────────────────────
+// NON-ACTIVE deletion sweep (DRAFT / ARCHIVED) — TK-11046.
+// ROOT CAUSE fixed: markPhantomDeleted only ever considered status='ACTIVE' rows
+// as deletion candidates, so a product deleted while DRAFT or ARCHIVED froze at its
+// last-known non-ACTIVE status forever instead of becoming DELETED_FROM_SHOPIFY.
+// This sweep 404-verifies non-active candidates too, but on a SEPARATE budget so the
+// (larger) non-active backlog can never trip the ACTIVE circuit-breaker above, and a
+// broken/partial crawl can never mass-corrupt the mirror.
+//
+// Why this is safe even against a "crawl returned only active" bug:
+// 1. BROKEN-CRAWL GUARD — a HEALTHY full crawl re-touches ~all non-active rows
+// (empirically ~99.6% ARCHIVED / ~96.8% DRAFT). If >NONACTIVE_ABSENT_RATIO_ABORT
+// of non-active rows are ABSENT from this crawl's seen-set, the crawl did not
+// paginate them → skip the sweep entirely rather than 404-storm the whole backlog.
+// 2. PER-RUN CANDIDATE CAP — verify at most N (oldest-first) per run, so even a real
+// backlog drains over several nightly runs, never one giant burst.
+// 3. LIVE 404-VERIFY — every candidate is GET-checked live; a product that still
+// exists (even ARCHIVED, which is NOT deleted) returns 200 and is left untouched.
+// 4. PER-RUN CONFIRMED CAP — a final backstop on marks/run against a token-scope
+// failure that could make many live products 404.
+// 5. BACKUP-BEFORE-WRITE — old_status recorded to a JSON before any UPDATE.
+const NONACTIVE_STATUSES = ['DRAFT', 'ARCHIVED']; // DUPLICATE_IMPORT is opt-in (below)
+const NONACTIVE_STALE = { DRAFT: '2 days', ARCHIVED: '3 days', DUPLICATE_IMPORT: '3 days' };
+const NONACTIVE_MAX_CANDIDATES_PER_RUN = 300; // 404-verify at most N non-active rows/run
+const NONACTIVE_MAX_CONFIRMED_DELETIONS_PER_RUN = 250; // backstop cap on marks/run
+const NONACTIVE_ABSENT_RATIO_ABORT = 0.5; // >50% non-active absent ⇒ crawl broken ⇒ skip
+// DUPLICATE_IMPORT is NOT swept by default: those rows are a dedup artifact the crawl
+// never touches (provenance unaudited), so blanket-marking them is unsafe. Opt in with
+// SWEEP_DUPLICATE_IMPORT=1 only after their provenance is understood (see TK-11046 WS-B audit).
+
async function markPhantomDeleted(db, shopifyClient, options = {}) {
const { seenIds = new Set(), fullCrawlComplete = false } = options;
@@ -263,6 +293,188 @@ async function markPhantomDeleted(db, shopifyClient, options = {}) {
console.log(` 💾 Backup : ${backupFile}`);
}
+/**
+ * markPhantomDeletedNonActive — the DRAFT/ARCHIVED half of deletion detection (TK-11046).
+ *
+ * markPhantomDeleted (above) only ever considered status='ACTIVE' rows, so a product
+ * deleted while DRAFT or ARCHIVED never became DELETED_FROM_SHOPIFY. This function does
+ * the identical live-404-verify for the non-active statuses, on a SEPARATE budget so it
+ * can never trip the ACTIVE circuit-breaker and never mass-corrupt the mirror.
+ *
+ * Purely additive: it does NOT touch the ACTIVE sweep or its safety constants.
+ *
+ * @param {Pool} db
+ * @param {object} shopifyClient { store, token, apiVersion }
+ * @param {object} options { seenIds:Set<string>, fullCrawlComplete:boolean }
+ */
+async function markPhantomDeletedNonActive(db, shopifyClient, options = {}) {
+ const { seenIds = new Set(), fullCrawlComplete = false } = options;
+
+ if (!fullCrawlComplete) {
+ console.log('\n⏭️ Skipping non-active deletion detection — full crawl did not complete cleanly.');
+ return;
+ }
+
+ const sweepStatuses = [...NONACTIVE_STATUSES];
+ if (process.env.SWEEP_DUPLICATE_IMPORT === '1') sweepStatuses.push('DUPLICATE_IMPORT');
+
+ console.log('\n' + '='.repeat(60));
+ console.log('🔍 NON-ACTIVE DELETION DETECTION (DRAFT/ARCHIVED backlog drain)');
+ console.log('='.repeat(60));
+ console.log(` Statuses swept: ${sweepStatuses.join(', ')}`);
+
+ const seenArr = [...seenIds];
+
+ // BROKEN-CRAWL GUARD — a healthy full crawl re-touches ~all non-active rows. If most
+ // non-active rows are ABSENT from this crawl's seen-set, the crawl did not paginate
+ // them (a status filter regressed, an early break, a rate-limit drop) — skip rather
+ // than 404-storm the entire non-active backlog. DUPLICATE_IMPORT is excluded from the
+ // ratio because it is never crawl-touched by design (always absent, would skew it).
+ const ratioRes = await db.query(
+ `SELECT count(*)::int AS total,
+ count(*) FILTER (WHERE shopify_id != ALL($1::text[]))::int AS absent
+ FROM shopify_products
+ WHERE status = ANY($2::text[])`,
+ [seenArr, NONACTIVE_STATUSES]
+ );
+ const { total, absent } = ratioRes.rows[0];
+ if (total > 0 && absent / total > NONACTIVE_ABSENT_RATIO_ABORT) {
+ console.warn(
+ `\n ⚠️ BROKEN-CRAWL GUARD: ${absent}/${total} non-active rows absent from this crawl ` +
+ `(> ${Math.round(NONACTIVE_ABSENT_RATIO_ABORT * 100)}%). A healthy full crawl re-touches ~all ` +
+ `non-active rows, so this suggests the crawl did not fetch them. Skipping non-active sweep.`
+ );
+ return;
+ }
+ console.log(` Non-active rows absent from crawl: ${absent}/${total} (guard threshold ${Math.round(NONACTIVE_ABSENT_RATIO_ABORT * 100)}%)`);
+
+ // Candidate query: absent from the seen-set AND stale (per-status floor) AND has a
+ // gid. Oldest synced_at first so the oldest backlog drains first; capped per run.
+ const staleClauses = sweepStatuses
+ .map(s => `(status = '${s}' AND synced_at < now() - interval '${NONACTIVE_STALE[s]}')`)
+ .join(' OR ');
+ const candidateResult = await db.query(
+ `SELECT shopify_id, handle, sku, title, status
+ FROM shopify_products
+ WHERE (${staleClauses})
+ AND coalesce(shopify_id, '') <> ''
+ AND shopify_id != ALL($1::text[])
+ ORDER BY synced_at ASC
+ LIMIT ${NONACTIVE_MAX_CANDIDATES_PER_RUN}`,
+ [seenArr]
+ );
+ const candidates = candidateResult.rows;
+ console.log(` Candidates this run (cap ${NONACTIVE_MAX_CANDIDATES_PER_RUN}): ${candidates.length}`);
+
+ if (candidates.length === 0) {
+ console.log(' ✅ No non-active candidates — backlog drained.');
+ return;
+ }
+
+ // Live-verify every candidate via Shopify REST (same 250ms spacing + 10s timeout).
+ const { store, token, apiVersion } = shopifyClient;
+ const confirmedDeleted = [];
+ let confirmedLive = 0;
+ let skippedErrors = 0;
+
+ console.log(`\n Verifying ${candidates.length} non-active candidates via REST...`);
+
+ for (const row of candidates) {
+ const numericId = row.shopify_id.split('/').pop();
+ try {
+ const controller = new AbortController();
+ const timer = setTimeout(() => controller.abort(), 10_000);
+ let resp;
+ try {
+ resp = await fetch(
+ `https://${store}/admin/api/${apiVersion}/products/${numericId}.json`,
+ { headers: { 'X-Shopify-Access-Token': token }, signal: controller.signal }
+ );
+ } finally {
+ clearTimeout(timer);
+ }
+
+ if (resp.status === 404) {
+ confirmedDeleted.push(row);
+ console.log(` 🗑️ 404 [${row.status}]: ${row.sku || row.handle || numericId} — ${(row.title || '').slice(0, 45)}`);
+ } else if (resp.status === 200) {
+ confirmedLive++;
+ } else {
+ console.warn(` ⚠️ HTTP ${resp.status} for ${row.sku || numericId} — skipping (not marking)`);
+ skippedErrors++;
+ }
+
+ await new Promise(r => setTimeout(r, 250));
+ } catch (err) {
+ if (err.name === 'AbortError') {
+ console.warn(` ⚠️ Timeout (10s) for ${row.sku || numericId} — skipping`);
+ } else {
+ console.error(` ❌ Fetch error for ${row.sku || numericId}:`, err.message);
+ }
+ skippedErrors++;
+ }
+ }
+
+ // PER-RUN CONFIRMED CAP — backstop against a token-scope failure that makes many live
+ // products 404. Abort without writing if we somehow confirmed more than the cap.
+ if (confirmedDeleted.length > NONACTIVE_MAX_CONFIRMED_DELETIONS_PER_RUN) {
+ console.warn(
+ `\n ⚠️ WARN: ${confirmedDeleted.length} confirmed non-active deletions exceeds per-run cap ` +
+ `${NONACTIVE_MAX_CONFIRMED_DELETIONS_PER_RUN}. Aborting without marking (possible token-scope issue).`
+ );
+ return;
+ }
+
+ if (confirmedDeleted.length === 0) {
+ console.log(`\n ✅ All ${confirmedLive} non-active candidates are still live — none marked.`);
+ if (skippedErrors > 0) console.warn(` ⚠️ ${skippedErrors} skipped due to errors — re-run to retry.`);
+ return;
+ }
+
+ // BACKUP-BEFORE-WRITE — records old_status so the marking is one-command reversible.
+ const backupDir = path.join(__dirname, '..', '..', 'data');
+ if (!fs.existsSync(backupDir)) fs.mkdirSync(backupDir, { recursive: true });
+ const ts = new Date().toISOString().replace(/[:.]/g, '-');
+ const backupFile = path.join(backupDir, `phantom-deletion-nonactive-backup-${ts}.json`);
+ fs.writeFileSync(backupFile, JSON.stringify({
+ savedAt: new Date().toISOString(),
+ crawlSeenCount: seenIds.size,
+ totalCandidates: candidates.length,
+ confirmedLive,
+ skippedErrors,
+ confirmedDeleted: confirmedDeleted.map(r => ({
+ shopify_id: r.shopify_id, sku: r.sku, handle: r.handle,
+ title: r.title, old_status: r.status
+ }))
+ }, null, 2));
+ console.log(`\n 💾 Backup → ${backupFile}`);
+
+ // Write — mark only confirmed-404 rows deleted. The `AND status = $2` guard (Cody
+ // TK-11046) makes this concurrency-safe: if a --quick sync flipped this row's status
+ // between the 404-verify and now, the UPDATE is a no-op rather than a clobber.
+ let markedDeleted = 0;
+ for (const row of confirmedDeleted) {
+ const res = await db.query(
+ `UPDATE shopify_products
+ SET status = 'DELETED_FROM_SHOPIFY', synced_at = NOW()
+ WHERE shopify_id = $1 AND status = $2`,
+ [row.shopify_id, row.status]
+ );
+ if (res.rowCount > 0) {
+ console.log(` ✅ Marked DELETED_FROM_SHOPIFY [was ${row.status}]: ${row.sku || row.handle}`);
+ markedDeleted++;
+ } else {
+ console.log(` ⏭️ Skipped (status changed since verify): ${row.sku || row.handle}`);
+ }
+ }
+
+ console.log('\n Non-active deletion summary:');
+ console.log(` 🗑️ Marked DELETED_FROM_SHOPIFY : ${markedDeleted}`);
+ console.log(` ✅ Confirmed still live (skipped) : ${confirmedLive}`);
+ console.log(` ⚠️ Skipped on error/timeout : ${skippedErrors}`);
+ console.log(` 💾 Backup : ${backupFile}`);
+}
+
async function syncProducts(quickMode = false) {
console.log(`\n🔄 Shopify Products Sync - ${new Date().toISOString()}`);
console.log(` Mode: ${quickMode ? 'Quick (new only)' : 'Full sync'}`);
@@ -515,6 +727,15 @@ async function syncProducts(quickMode = false) {
{ store: SHOPIFY_STORE, token: SHOPIFY_TOKEN, apiVersion: API_VERSION },
{ seenIds: seenShopifyIds, fullCrawlComplete }
);
+ // TK-11046: also sweep DRAFT/ARCHIVED (previously frozen at their last non-ACTIVE
+ // status forever). Separate budget — see markPhantomDeletedNonActive. This runs
+ // AFTER the ACTIVE sweep; its own broken-crawl guard independently protects it if
+ // the crawl was partial (the ACTIVE circuit-breaker returns early on a broken crawl).
+ await markPhantomDeletedNonActive(
+ pool,
+ { store: SHOPIFY_STORE, token: SHOPIFY_TOKEN, apiVersion: API_VERSION },
+ { seenIds: seenShopifyIds, fullCrawlComplete }
+ );
} catch (err) {
// Deletion detection failing must not prevent the pool from closing or the
// sync from reporting success — the main sync already completed cleanly.
← c6e825b1 git-bloat Layer 1: gitignore broad room-render/snapshot patt
·
back to Dw Validator Debug TK11314
·
auto-data-snapshot: 2026-09-03T12:32:21 (1 data files) — sho a6231040 →