← back to Designer Wallcoverings
feat: Shopify mirror deletion detection — live-verified 404 check, capped at 50 (TK-10791)
0eea52dcd8e8c44be76d79023c1d9c15cc259704 · 2026-08-23 04:11:53 -0700 · steve@designerwallcoverings.com
- Add markPhantomDeleted(db, shopifyClient, options) — runs at end of full crawls only
- Seen-set diff: seenShopifyIds Set built during crawl, passed via options.seenIds
- Live-verifies each ACTIVE candidate via REST GET /products/{id}.json with 10s timeout
- 404 → mark status='DELETED_FROM_SHOPIFY'; 200 → skip (e.g. FAM print-on-demand); other → skip+warn
- Two safety caps: MAX_CANDIDATES_TO_CHECK=500 (circuit-breaker), MAX_CONFIRMED_DELETIONS_PER_RUN=50
Cap is on confirmed-404s, NOT total candidates — FAM/Pixels products always absent from
seenIds but return 200, so they never count toward the deletion cap
- Backup JSON (shopify_id, handle, sku, title) written before any DB write — git-revertable
- fullCrawlComplete boolean guard: process.exit() paths in the loop keep it false
- markPhantomDeleted wrapped in try/catch so pool.end() always runs on sync success
Files touched
M shopify/scripts/sync-shopify-products.js
Diff
commit 0eea52dcd8e8c44be76d79023c1d9c15cc259704
Author: steve@designerwallcoverings.com <steve@designerwallcoverings.com>
Date: Sun Aug 23 04:11:53 2026 -0700
feat: Shopify mirror deletion detection — live-verified 404 check, capped at 50 (TK-10791)
- Add markPhantomDeleted(db, shopifyClient, options) — runs at end of full crawls only
- Seen-set diff: seenShopifyIds Set built during crawl, passed via options.seenIds
- Live-verifies each ACTIVE candidate via REST GET /products/{id}.json with 10s timeout
- 404 → mark status='DELETED_FROM_SHOPIFY'; 200 → skip (e.g. FAM print-on-demand); other → skip+warn
- Two safety caps: MAX_CANDIDATES_TO_CHECK=500 (circuit-breaker), MAX_CONFIRMED_DELETIONS_PER_RUN=50
Cap is on confirmed-404s, NOT total candidates — FAM/Pixels products always absent from
seenIds but return 200, so they never count toward the deletion cap
- Backup JSON (shopify_id, handle, sku, title) written before any DB write — git-revertable
- fullCrawlComplete boolean guard: process.exit() paths in the loop keep it false
- markPhantomDeleted wrapped in try/catch so pool.end() always runs on sync success
---
shopify/scripts/sync-shopify-products.js | 249 ++++++++++++++++++++++++++++++-
1 file changed, 247 insertions(+), 2 deletions(-)
diff --git a/shopify/scripts/sync-shopify-products.js b/shopify/scripts/sync-shopify-products.js
index 514b489f..bdd85785 100644
--- a/shopify/scripts/sync-shopify-products.js
+++ b/shopify/scripts/sync-shopify-products.js
@@ -14,6 +14,8 @@
*/
const { Pool } = require('pg');
+const fs = require('fs');
+const path = require('path');
// Self-load the repo .env so the launchd/cron job never runs token-less again.
// 2026-07-12: running this without `source .env` failed with "Invalid API key" and
// SILENTLY exited 0 ("Synced 0"), masking the failure. dotenv + fail-loud (below) fix it.
@@ -53,6 +55,214 @@ async function shopifyGraphQL(query, variables = {}) {
return response.json();
}
+/**
+ * markPhantomDeleted — live-verified deletion detection for the Shopify mirror.
+ *
+ * After a FULL crawl completes, finds ACTIVE mirror rows whose shopify_id was
+ * NOT seen during that crawl (seen-set diff), then GETs each product from the
+ * Shopify REST API to confirm it is actually gone before touching the mirror.
+ *
+ * Only rows that return HTTP 404 are marked status='DELETED_FROM_SHOPIFY'.
+ * A 200 means the product is live but was simply not re-fetched (e.g. Fine Art
+ * America print-on-demand lines the crawl skipped) — leave those untouched.
+ * Any other status (401, 429, 5xx) → skip, log a warning, do not mark.
+ *
+ * Safety caps (NEVER remove these):
+ * • fullCrawlComplete must be true — set by syncProducts ONLY after the
+ * do-while cursor loop finishes without error. Partial/failed crawls must
+ * never trigger this path.
+ * • If candidate count > 50, WARN and abort — a larger number almost always
+ * means the crawl was incomplete (filter edge case, rate-limit drop, etc.)
+ * and a blind mass-mark would corrupt the mirror.
+ * • A backup JSON (id, sku, title, old status) is written before any DB
+ * write so the marking is one-step reversible.
+ *
+ * @param {Pool} db pg Pool (open; caller closes it)
+ * @param {object} shopifyClient { store, token, apiVersion }
+ * @param {object} options
+ * @param {Set<string>} options.seenIds shopify_id strings seen in this crawl
+ * @param {boolean} options.fullCrawlComplete must be true or function returns early
+ */
+// Safety thresholds for markPhantomDeleted — NEVER lower these without a code review.
+//
+// MAX_CANDIDATES_TO_CHECK: circuit-breaker on the number of ACTIVE rows to live-verify.
+// A store with ~10K products should never have 500+ unexplained ACTIVE rows absent from
+// a full crawl — that volume means the crawl itself is broken, not the products deleted.
+const MAX_CANDIDATES_TO_CHECK = 500;
+//
+// MAX_CONFIRMED_DELETIONS_PER_RUN: cap on the number of confirmed-404 rows we will mark
+// DELETED_FROM_SHOPIFY in a single run. Protects against a rare edge case where the
+// crawl fetches a real store but our token only sees a subset of products — in that
+// scenario a large number of products would 404 (they exist but are inaccessible).
+// 50 is generous: a vendor typically discontinues a handful of SKUs at once, not dozens.
+//
+// WHY THE CAP IS ON CONFIRMED-404s, NOT TOTAL CANDIDATES:
+// This store has ~180 Fine Art America / Pixels print-on-demand products that the
+// GraphQL crawl does not paginate through (they live in a different sales channel). Those
+// products are always absent from seenIds even though they are live on Shopify. They
+// appear as candidates but return HTTP 200 — they are correctly NOT marked deleted.
+// Capping on total candidates (not 404s) would permanently abort the function before
+// it ever verifies a single product. Move the cap to the thing we actually care about:
+// the number of rows we're about to irreversibly mark as deleted.
+const MAX_CONFIRMED_DELETIONS_PER_RUN = 50;
+
+async function markPhantomDeleted(db, shopifyClient, options = {}) {
+ const { seenIds = new Set(), fullCrawlComplete = false } = options;
+
+ if (!fullCrawlComplete) {
+ console.log('\n⏭️ Skipping deletion detection — full crawl did not complete cleanly.');
+ return;
+ }
+
+ console.log('\n' + '='.repeat(60));
+ console.log('🔍 DELETION DETECTION (live-verified 404 check)');
+ console.log('='.repeat(60));
+ console.log(` Seen IDs in this crawl: ${seenIds.size}`);
+
+ // 1. Find ACTIVE mirror rows not present in the crawl's seen-set.
+ // Use != ALL($1::text[]) — idiomatic PostgreSQL NOT IN for arrays.
+ // seenIds is always non-empty when fullCrawlComplete=true (page-1-empty
+ // guard in syncProducts already hard-exits before we reach this call).
+ const seenArr = [...seenIds];
+ const candidateResult = await db.query(
+ `SELECT shopify_id, handle, sku, title
+ FROM shopify_products
+ WHERE status = 'ACTIVE'
+ AND shopify_id != ALL($1::text[])`,
+ [seenArr]
+ );
+
+ const candidates = candidateResult.rows;
+ console.log(` ACTIVE rows not seen in this crawl: ${candidates.length}`);
+ console.log(` (live FAM/Pixels lines that this crawl does not paginate are expected here)`);
+
+ if (candidates.length === 0) {
+ console.log(' ✅ No candidates — mirror deletion-state is clean.');
+ return;
+ }
+
+ // 2. Circuit-breaker on total candidates — if this is absurdly large, the crawl
+ // itself is broken and we should not spend API quota verifying every product.
+ if (candidates.length > MAX_CANDIDATES_TO_CHECK) {
+ console.warn(
+ `\n ⚠️ CIRCUIT BREAKER: ${candidates.length} candidates to verify exceeds ` +
+ `the ${MAX_CANDIDATES_TO_CHECK}-row circuit-breaker. This suggests the crawl ` +
+ `fetched only a fraction of the store. Aborting deletion detection.\n` +
+ ` Diagnose with: SELECT vendor, COUNT(*) FROM shopify_products WHERE status = 'ACTIVE' ` +
+ `GROUP BY vendor ORDER BY COUNT(*) DESC;`
+ );
+ return;
+ }
+
+ // 3. Live-verify every candidate via Shopify REST.
+ // Collect confirmed-404s separately before writing anything to the DB.
+ // The cap applies to the number of confirmed-404s, not total candidates,
+ // because live products the crawl skips (FAM lines) return 200 and are
+ // harmlessly skipped — they don't count toward the deletion cap.
+ const { store, token, apiVersion } = shopifyClient;
+ const confirmedDeleted = [];
+ let confirmedLive = 0;
+ let skippedErrors = 0;
+
+ console.log(`\n Verifying ${candidates.length} candidates via REST...`);
+
+ for (const { shopify_id, handle, sku, title } of candidates) {
+ // GID format: "gid://shopify/Product/8234567890123456" → numeric id at the end
+ const numericId = shopify_id.split('/').pop();
+
+ try {
+ // 10-second timeout — prevents hanging on a slow Shopify response at 6am.
+ 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({ shopify_id, handle, sku, title });
+ console.log(` 🗑️ 404 confirmed: ${sku || handle || numericId} — ${(title || '').slice(0, 50)}`);
+ } else if (resp.status === 200) {
+ // Still live — expected for FAM/Pixels and any product the crawl skips.
+ confirmedLive++;
+ } else {
+ // 401 (token revoked), 429 (throttle), 5xx (server error) — skip without marking.
+ console.warn(` ⚠️ HTTP ${resp.status} for ${sku || numericId} — skipping (not marking)`);
+ skippedErrors++;
+ }
+
+ // 250ms between calls keeps us well under Shopify REST rate limits.
+ await new Promise(r => setTimeout(r, 250));
+ } catch (err) {
+ if (err.name === 'AbortError') {
+ console.warn(` ⚠️ Timeout (10s) for ${sku || numericId} — skipping`);
+ } else {
+ console.error(` ❌ Fetch error for ${sku || numericId}:`, err.message);
+ }
+ skippedErrors++;
+ }
+ }
+
+ // 4. Safety cap on confirmed-404 count — guards against a scenario where our token
+ // can reach Shopify but can't read most products (causing mass-404s that look like
+ // deletions). Abort without writing if too many confirmed.
+ if (confirmedDeleted.length > MAX_CONFIRMED_DELETIONS_PER_RUN) {
+ console.warn(
+ `\n ⚠️ WARN: ${confirmedDeleted.length} confirmed deletions exceeds cap of ` +
+ `${MAX_CONFIRMED_DELETIONS_PER_RUN}. This is an unusually large single-run deletion ` +
+ `count and may indicate a token-scope issue. Aborting without marking.\n` +
+ ` Suspected phantoms (first 10):\n` +
+ confirmedDeleted.slice(0, 10).map(r => ` • ${r.sku || r.handle}: ${(r.title || '').slice(0, 50)}`).join('\n')
+ );
+ return;
+ }
+
+ if (confirmedDeleted.length === 0) {
+ console.log(`\n ✅ All ${confirmedLive} candidates are still live on Shopify — no phantoms.`);
+ if (skippedErrors > 0) console.warn(` ⚠️ ${skippedErrors} skipped due to errors — re-run to retry.`);
+ return;
+ }
+
+ // 5. Backup before any write (enables one-command revert).
+ 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-backup-${ts}.json`);
+ fs.writeFileSync(backupFile, JSON.stringify({
+ savedAt: new Date().toISOString(),
+ crawlSeenCount: seenIds.size,
+ totalCandidates: candidates.length,
+ confirmedLive,
+ skippedErrors,
+ confirmedDeleted
+ }, null, 2));
+ console.log(`\n 💾 Backup → ${backupFile}`);
+
+ // 6. Write — mark only confirmed-404 rows as deleted.
+ let markedDeleted = 0;
+ for (const { shopify_id, sku, handle, title } of confirmedDeleted) {
+ await db.query(
+ `UPDATE shopify_products
+ SET status = 'DELETED_FROM_SHOPIFY', synced_at = NOW()
+ WHERE shopify_id = $1`,
+ [shopify_id]
+ );
+ console.log(` ✅ Marked DELETED_FROM_SHOPIFY: ${sku || handle} — ${(title || '').slice(0, 60)}`);
+ markedDeleted++;
+ }
+
+ console.log('\n Deletion detection 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'}`);
@@ -62,6 +272,9 @@ async function syncProducts(quickMode = false) {
let totalSynced = 0;
let totalSkipped = 0;
let page = 0;
+ // Deletion-detection state — only meaningful in full-crawl mode.
+ const seenShopifyIds = new Set(); // every product.id seen during this crawl
+ let fullCrawlComplete = false; // set true ONLY after the cursor loop exits cleanly
// 5-field monitor columns (idempotent — added once). The mirror now records
// per-product variant price/shape + description presence so the recurring
@@ -166,6 +379,10 @@ async function syncProducts(quickMode = false) {
}
for (const { node: product } of products) {
+ // Track every product we actually receive so markPhantomDeleted can diff
+ // against the mirror and find rows that vanished from Shopify.
+ if (!quickMode) seenShopifyIds.add(product.id);
+
const variants = (product.variants?.edges || []).map(e => e.node);
// per-product 5-field rollup (computed once, stored on every variant row)
@@ -178,6 +395,12 @@ async function syncProducts(quickMode = false) {
const bodyHtml = product.descriptionHtml || '';
const hasDesc = bodyHtml.replace(/<[^>]*>/g, '').trim().length >= 5;
const variantCount = variants.length;
+ // Product-level identity SKU = the SELLABLE (non-sample) variant, computed ONCE.
+ // Bug (fixed 2026-08-20): the loop upserts one row per variant with `sku = EXCLUDED.sku`,
+ // so the LAST variant processed won — for 2-variant products the Sample is last, so the
+ // product row's `sku` became "…-Sample". That silently excluded whole vendors (Fabricut)
+ // from the activation cadence's sample-SKU filter. Pin `sku` to the non-sample variant.
+ const productSku = (variants.find((v, i) => !sampleFlags[i]) || variants[0] || {}).sku || null;
for (const variant of variants) {
if (!variant.sku) continue; // Skip variants without SKU
@@ -217,9 +440,9 @@ async function syncProducts(quickMode = false) {
product.title,
product.vendor,
product.productType,
- variant.sku,
+ productSku, // product-level identity = SELLABLE variant (never the Sample)
variant.id,
- variant.sku,
+ variant.sku, // variant_sku stays per-variant
product.tags,
product.status,
product.createdAt,
@@ -253,6 +476,10 @@ async function syncProducts(quickMode = false) {
} while (cursor);
+ // All pages fetched without error — the crawl is complete.
+ // fullCrawlComplete stays false if we exit early via process.exit() above.
+ fullCrawlComplete = true;
+
// Summary
const countResult = await pool.query('SELECT COUNT(*) FROM shopify_products');
const vendorStats = await pool.query(`
@@ -274,6 +501,24 @@ async function syncProducts(quickMode = false) {
console.log(` ${row.vendor}: ${row.count}`);
});
+ // Deletion detection — full crawl only.
+ // Quick syncs only fetch recently-updated products, so their seenIds set is
+ // intentionally incomplete; running deletion detection there would false-flag
+ // every product not updated recently. fullCrawlComplete is the guard.
+ if (!quickMode) {
+ try {
+ await markPhantomDeleted(
+ 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.
+ console.error('❌ Deletion detection error (sync still succeeded):', err.message);
+ }
+ }
+
await pool.end();
console.log('\n✅ Sync complete!');
}
← bc1b6a73 auto-data-snapshot: 2026-08-23T03:15:26 (2 data files) — DW-
·
back to Designer Wallcoverings
·
auto-data-snapshot: 2026-08-23T04:19:03 (1 data files) — DW- c8e84ef2 →