← back to Dw Validator Debug TK11314
TK-11046 WS-B: one-time frozen-delete reconcile script (snapshot+ledger)
ed856136625c1f0202c7290ae0399691cdcba823 · 2026-09-03 12:40:12 -0700 · Steve
Live-404-verifies stale DRAFT(>2d)/ARCHIVED(>3d) mirror rows; 404 -> mark
DELETED_FROM_SHOPIFY (concurrency-safe AND status=old), 200 -> leave, err -> skip.
Snapshots all candidates before write + ledgers to executed-reversible + prints
summary + audits DUPLICATE_IMPORT read-only. Applied run: 853 verified, 733
marked, 120 left live, 0 skipped; DUPLICATE_IMPORT 404-rate 0% (all live -> not
touched, audit-only default validated). Reversible via --revert <snapshot>.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01BbqKZLbd8emwVkNrxwtG2X
Files touched
M .gitignoreA shopify/scripts/reconcile-frozen-deletes-TK-11046.mjs
Diff
commit ed856136625c1f0202c7290ae0399691cdcba823
Author: Steve <steve@designerwallcoverings.com>
Date: Thu Sep 3 12:40:12 2026 -0700
TK-11046 WS-B: one-time frozen-delete reconcile script (snapshot+ledger)
Live-404-verifies stale DRAFT(>2d)/ARCHIVED(>3d) mirror rows; 404 -> mark
DELETED_FROM_SHOPIFY (concurrency-safe AND status=old), 200 -> leave, err -> skip.
Snapshots all candidates before write + ledgers to executed-reversible + prints
summary + audits DUPLICATE_IMPORT read-only. Applied run: 853 verified, 733
marked, 120 left live, 0 skipped; DUPLICATE_IMPORT 404-rate 0% (all live -> not
touched, audit-only default validated). Reversible via --revert <snapshot>.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01BbqKZLbd8emwVkNrxwtG2X
---
.gitignore | 2 +
.../scripts/reconcile-frozen-deletes-TK-11046.mjs | 234 +++++++++++++++++++++
2 files changed, 236 insertions(+)
diff --git a/.gitignore b/.gitignore
index 72410df4..c7953d2c 100644
--- a/.gitignore
+++ b/.gitignore
@@ -115,3 +115,5 @@ onboarding/graduate-new-2026/shopify-upload/*.jpg
onboarding/graduate-new-2026/shopify-upload/*.png
**/*-images-RESTORE-*.json
**/*-images-RESULT-*.json
+
+shopify/scripts/out/
diff --git a/shopify/scripts/reconcile-frozen-deletes-TK-11046.mjs b/shopify/scripts/reconcile-frozen-deletes-TK-11046.mjs
new file mode 100644
index 00000000..80553e5b
--- /dev/null
+++ b/shopify/scripts/reconcile-frozen-deletes-TK-11046.mjs
@@ -0,0 +1,234 @@
+#!/usr/bin/env node
+/**
+ * reconcile-frozen-deletes-TK-11046.mjs — ONE-TIME frozen-delete reconcile.
+ *
+ * Root cause (see sync-shopify-products.js markPhantomDeleted): deletion detection only
+ * ever ran on status='ACTIVE' rows, so products deleted while DRAFT or ARCHIVED froze at
+ * their last-known non-ACTIVE status forever instead of becoming DELETED_FROM_SHOPIFY.
+ *
+ * This script drains the accumulated backlog ONCE. For every MAC2-LOCAL mirror row in
+ * status IN ('DRAFT','ARCHIVED') with a stale synced_at (DRAFT >2d, ARCHIVED >3d) AND a
+ * shopify_id present, it GETs the product live from Shopify (250ms spacing):
+ * • HTTP 404 → mark status='DELETED_FROM_SHOPIFY', synced_at=NOW()
+ * • HTTP 200 → LEAVE UNTOUCHED (genuinely still live)
+ * • 000/429/5xx/timeout → SKIP (do not mark), log it (fail-safe)
+ *
+ * This is a LOCAL derived-table correction to make the mirror match Shopify truth. It is
+ * NOT a Shopify / Kamatera / customer-facing write.
+ *
+ * RAILS:
+ * 1. Snapshots every candidate row to out/reconcile-frozen-SNAPSHOT-<ts>.json BEFORE
+ * any UPDATE (one-step reversible via --revert <snapshot>).
+ * 2. Ledgers to ~/.claude/yolo-queue/executed-reversible/ledger.jsonl.
+ * 3. Prints a summary (candidates / 404-marked / 200-left / skipped).
+ *
+ * DUPLICATE_IMPORT rows are AUDIT-ONLY this pass: a ~40-row 404-rate sample is reported,
+ * NOT blanket-marked (provenance unaudited).
+ *
+ * Usage:
+ * node reconcile-frozen-deletes-TK-11046.mjs # dry-run (verify + report, NO writes)
+ * node reconcile-frozen-deletes-TK-11046.mjs --apply # perform the marking
+ * node reconcile-frozen-deletes-TK-11046.mjs --revert out/reconcile-frozen-SNAPSHOT-<ts>.json
+ */
+
+import pg from 'pg';
+import fs from 'fs';
+import path from 'path';
+import os from 'os';
+import { fileURLToPath } from 'url';
+
+const __dirname = path.dirname(fileURLToPath(import.meta.url));
+const OUT_DIR = path.join(__dirname, 'out');
+const LEDGER = path.join(os.homedir(), '.claude', 'yolo-queue', 'executed-reversible', 'ledger.jsonl');
+const SHOPIFY_STORE = process.env.SHOPIFY_STORE_DOMAIN || 'designer-laboratory-sandbox.myshopify.com';
+const API_VERSION = process.env.SHOPIFY_ADMIN_API_VERSION || '2024-10';
+
+const APPLY = process.argv.includes('--apply');
+const REVERT_IDX = process.argv.indexOf('--revert');
+const REVERT_FILE = REVERT_IDX >= 0 ? process.argv[REVERT_IDX + 1] : null;
+
+// ── token (secrets-manager/.env, then repo .env, then env) ────────────────────
+function readToken() {
+ if (process.env.SHOPIFY_ADMIN_TOKEN) return process.env.SHOPIFY_ADMIN_TOKEN;
+ const files = [
+ path.join(os.homedir(), 'Projects', 'secrets-manager', '.env'),
+ path.join(os.homedir(), 'Projects', 'designerwallcoverings', '.env'),
+ path.join(__dirname, '..', '..', '.env'),
+ ];
+ for (const f of files) {
+ try {
+ const m = fs.readFileSync(f, 'utf8').match(/^SHOPIFY_ADMIN_TOKEN=(.+)$/m);
+ if (m) return m[1].trim().replace(/^["']|["']$/g, '');
+ } catch { /* next */ }
+ }
+ throw new Error('SHOPIFY_ADMIN_TOKEN not found in env or secrets-manager/.env');
+}
+const TOKEN = readToken();
+
+const pool = new pg.Pool({ connectionString: 'postgresql://dw_admin@/dw_unified?host=/tmp' });
+const sleep = (ms) => new Promise(r => setTimeout(r, ms));
+
+// Live 404-verify one product. Returns 404 | 200 | 'ERR'.
+async function verify(shopifyId) {
+ const numericId = String(shopifyId).split('/').pop();
+ const controller = new AbortController();
+ const timer = setTimeout(() => controller.abort(), 10_000);
+ try {
+ const resp = await fetch(
+ `https://${SHOPIFY_STORE}/admin/api/${API_VERSION}/products/${numericId}.json`,
+ { headers: { 'X-Shopify-Access-Token': TOKEN }, signal: controller.signal }
+ );
+ if (resp.status === 404) return 404;
+ if (resp.status === 200) return 200;
+ return { err: resp.status };
+ } catch (e) {
+ return { err: e.name === 'AbortError' ? 'timeout' : (e.message || 'fetch-error') };
+ } finally {
+ clearTimeout(timer);
+ }
+}
+
+// ── revert path ───────────────────────────────────────────────────────────────
+async function revert(snapshotPath) {
+ const snap = JSON.parse(fs.readFileSync(snapshotPath, 'utf8'));
+ const marked = (snap.marked || []).filter(r => r.shopify_id);
+ console.log(`↩️ Reverting ${marked.length} rows from ${snapshotPath} to their old status...`);
+ let restored = 0;
+ for (const r of marked) {
+ const res = await pool.query(
+ `UPDATE shopify_products SET status=$1 WHERE shopify_id=$2 AND status='DELETED_FROM_SHOPIFY'`,
+ [r.old_status, r.shopify_id]
+ );
+ restored += res.rowCount;
+ }
+ console.log(`✅ Restored ${restored} rows.`);
+ await pool.end();
+}
+
+// ── DUPLICATE_IMPORT audit-only (sample ~40, report 404-rate) ─────────────────
+async function auditDuplicateImport() {
+ const { rows } = await pool.query(
+ `SELECT shopify_id, sku, title
+ FROM shopify_products
+ WHERE status='DUPLICATE_IMPORT' AND coalesce(shopify_id,'')<>''
+ ORDER BY random() LIMIT 40`
+ );
+ let n404 = 0, n200 = 0, nErr = 0;
+ for (const r of rows) {
+ const v = await verify(r.shopify_id);
+ if (v === 404) n404++;
+ else if (v === 200) n200++;
+ else nErr++;
+ await sleep(250);
+ }
+ const checked = n404 + n200;
+ return { sampled: rows.length, n404, n200, nErr,
+ pct404: checked ? Math.round((n404 / checked) * 100) : null };
+}
+
+async function main() {
+ if (REVERT_FILE) return revert(REVERT_FILE);
+
+ if (!fs.existsSync(OUT_DIR)) fs.mkdirSync(OUT_DIR, { recursive: true });
+ const ts = new Date().toISOString().replace(/[:.]/g, '-');
+
+ console.log(`\n🔧 TK-11046 frozen-delete reconcile — ${APPLY ? 'APPLY' : 'DRY-RUN'} — ${new Date().toISOString()}`);
+ console.log(` Store: ${SHOPIFY_STORE} API: ${API_VERSION} token …${TOKEN.slice(-4)}`);
+
+ // 1. Candidates: DRAFT >2d, ARCHIVED >3d, stale, with gid.
+ const { rows: candidates } = await pool.query(
+ `SELECT id, sku, dw_sku, shopify_id, status AS old_status, title, handle
+ FROM shopify_products
+ WHERE ( (status='DRAFT' AND synced_at < now() - interval '2 days')
+ OR (status='ARCHIVED' AND synced_at < now() - interval '3 days') )
+ AND coalesce(shopify_id,'') <> ''
+ ORDER BY status, synced_at ASC`
+ );
+ console.log(` Candidates (DRAFT>2d + ARCHIVED>3d, gid present): ${candidates.length}`);
+
+ // Snapshot ALL candidates BEFORE any write.
+ const snapshotFile = path.join(OUT_DIR, `reconcile-frozen-SNAPSHOT-${ts}.json`);
+ fs.writeFileSync(snapshotFile, JSON.stringify({
+ savedAt: new Date().toISOString(), apply: APPLY, store: SHOPIFY_STORE,
+ candidateCount: candidates.length,
+ candidates: candidates.map(c => ({ id: c.id, sku: c.sku, dw_sku: c.dw_sku,
+ shopify_id: c.shopify_id, old_status: c.old_status, title: c.title })),
+ marked: [] // filled in below with rows actually flipped (revert reads this)
+ }, null, 2));
+ console.log(` 💾 Snapshot → ${snapshotFile}`);
+
+ // 2. Live-verify each; collect 404s, count 200s, log skips.
+ let n404 = 0, n200 = 0, nSkip = 0;
+ const toMark = [];
+ const skips = [];
+ let i = 0;
+ for (const c of candidates) {
+ i++;
+ const v = await verify(c.shopify_id);
+ if (v === 404) {
+ n404++;
+ toMark.push(c);
+ if (n404 <= 15 || n404 % 50 === 0) console.log(` 🗑️ 404 [${c.old_status}] ${c.dw_sku || c.sku || c.handle} — ${(c.title || '').slice(0, 45)}`);
+ } else if (v === 200) {
+ n200++;
+ } else {
+ nSkip++;
+ skips.push({ shopify_id: c.shopify_id, sku: c.sku, err: v.err });
+ console.warn(` ⚠️ SKIP (${v.err}) ${c.sku || c.shopify_id}`);
+ }
+ if (i % 100 === 0) console.log(` … ${i}/${candidates.length} verified (404:${n404} 200:${n200} skip:${nSkip})`);
+ await sleep(250);
+ }
+
+ // 3. Write (only with --apply). Update snapshot.marked for reversibility.
+ let marked = 0;
+ if (APPLY && toMark.length) {
+ for (const c of toMark) {
+ const res = await pool.query(
+ `UPDATE shopify_products SET status='DELETED_FROM_SHOPIFY', synced_at=NOW()
+ WHERE shopify_id=$1 AND status=$2`,
+ [c.shopify_id, c.old_status]
+ );
+ marked += res.rowCount;
+ }
+ const snap = JSON.parse(fs.readFileSync(snapshotFile, 'utf8'));
+ snap.marked = toMark.map(c => ({ id: c.id, sku: c.sku, dw_sku: c.dw_sku,
+ shopify_id: c.shopify_id, old_status: c.old_status }));
+ fs.writeFileSync(snapshotFile, JSON.stringify(snap, null, 2));
+ }
+
+ // 4. DUPLICATE_IMPORT audit (always; read-only, never marks).
+ console.log(`\n Auditing DUPLICATE_IMPORT (sample 40, read-only)...`);
+ const dupAudit = await auditDuplicateImport();
+
+ // 5. Ledger (only on apply-with-writes).
+ if (APPLY && marked > 0) {
+ fs.mkdirSync(path.dirname(LEDGER), { recursive: true });
+ fs.appendFileSync(LEDGER, JSON.stringify({
+ ts: new Date().toISOString(), agent: 'vp-dw-commerce', ticket: 'TK-11046',
+ action: `reconcile frozen DRAFT/ARCHIVED deletes → DELETED_FROM_SHOPIFY (local mirror, live-404-verified)`,
+ blast_radius: marked,
+ undo_cmd: `node ${path.relative(os.homedir(), path.join(__dirname, 'reconcile-frozen-deletes-TK-11046.mjs'))} --revert ${snapshotFile}`,
+ verify: `psql "host=/tmp dbname=dw_unified" -tc "SELECT count(*) FROM shopify_products WHERE status='DELETED_FROM_SHOPIFY'"`
+ }) + '\n');
+ console.log(` 🧾 Ledgered → ${LEDGER}`);
+ }
+
+ // 6. Summary.
+ console.log('\n' + '='.repeat(60));
+ console.log(`📊 RECONCILE SUMMARY (${APPLY ? 'APPLIED' : 'DRY-RUN — no writes'})`);
+ console.log('='.repeat(60));
+ console.log(` Candidates verified : ${candidates.length}`);
+ console.log(` 404 (would mark/marked): ${n404}${APPLY ? ` (rows flipped: ${marked})` : ''}`);
+ console.log(` 200 (left live) : ${n200}`);
+ console.log(` Skipped (err/timeout) : ${nSkip}`);
+ console.log(` Snapshot : ${snapshotFile}`);
+ console.log(`\n DUPLICATE_IMPORT audit (AUDIT-ONLY, not marked):`);
+ console.log(` sampled ${dupAudit.sampled} · 404:${dupAudit.n404} · 200:${dupAudit.n200} · err:${dupAudit.nErr} · 404-rate: ${dupAudit.pct404 == null ? 'n/a' : dupAudit.pct404 + '%'}`);
+ if (nSkip) console.log(` Skips: ${JSON.stringify(skips.slice(0, 10))}${skips.length > 10 ? ' …' : ''}`);
+ if (!APPLY) console.log(`\n ▶ Re-run with --apply to perform the marking.`);
+
+ await pool.end();
+}
+
+main().catch(async (e) => { console.error('FATAL:', e); try { await pool.end(); } catch {} process.exit(1); });
← a6231040 auto-data-snapshot: 2026-09-03T12:32:21 (1 data files) — sho
·
back to Dw Validator Debug TK11314
·
auto-data-snapshot: 2026-09-03T13:07:42 (1 data files) — sho 37b817a4 →