Restore orphan-publish-guard (silent ~1wk death): recover script from git, add observability heartbeat, fix launchd psql PATH crash — detect-only, --enforce stays gated

f55e542fafe3e95ae5bd0f7648868505931e860b · 2026-08-06 12:14:31 -0700 · Steve Abrams
Files touched

A orphan-publish-guard.mjs
Diff

commit f55e542fafe3e95ae5bd0f7648868505931e860b
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Thu Aug 6 12:14:31 2026 -0700

    Restore orphan-publish-guard (silent ~1wk death): recover script from git, add observability heartbeat, fix launchd psql PATH crash — detect-only, --enforce stays gated
---
 orphan-publish-guard.mjs | 87 ++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 87 insertions(+)

diff --git a/orphan-publish-guard.mjs b/orphan-publish-guard.mjs
new file mode 100644
index 0000000..a346a57
--- /dev/null
+++ b/orphan-publish-guard.mjs
@@ -0,0 +1,87 @@
+#!/usr/bin/env node
+// ORPHAN PUBLISH GUARD — prevents the "slug-SKU sample-only orphan" class from staying live.
+// Born 2026-07-16 after venice-beach-los-angeles (+577 siblings) were found ACTIVE on the
+// Online Store with mfr_sku=upper(handle), no sellable variant, no price.
+//
+// There is no single publish chokepoint (products reach ACTIVE+online_store via the drain,
+// vendor importers, and manual Shopify-admin edits), so this guard runs as a sweep over the
+// dw_unified mirror (importers write PG-first, so it catches them at the source) and flags — or
+// with --enforce, auto-DRAFTs — any product matching the orphan fingerprint:
+//     status=ACTIVE  AND  online_store_published  AND  mfr_sku = upper(handle)
+//     AND  has_product_variant IS NOT TRUE           (only a sample/placeholder variant)
+//
+//   node orphan-publish-guard.mjs            # REPORT only (default, no writes)
+//   node orphan-publish-guard.mjs --enforce  # auto-DRAFT offenders (reversible, ledgered)
+//
+// Reuses the reversible-ledger + Shopify productUpdate(status:DRAFT) mechanism from
+// unpublish-broken-orphans.mjs. Idempotent; safe to run on a schedule.
+import fs from 'node:fs';
+import { execFileSync } from 'node:child_process';
+
+// launchd runs with a minimal PATH that omits homebrew, so `psql` was ENOENT under the scheduled job
+// (pid:0 crash) even though it resolved in an interactive shell. Prepend the homebrew bins so the two
+// execFileSync('psql', ...) calls resolve identically under launchd and in a terminal. (fix 2026-08-06)
+process.env.PATH = `/opt/homebrew/bin:/opt/homebrew/opt/postgresql@14/bin:/usr/local/bin:${process.env.PATH || ''}`;
+
+const ENFORCE = process.argv.includes('--enforce');
+const DIR = '/Users/macstudio3/Projects/designerwallcoverings/data/orphan-cleanup-20260716';
+const LEDGER = `${DIR}/guard-enforced-ledger.jsonl`;
+// Observability heartbeat (added 2026-08-06): written on every COMPLETED run so the meta-watchdog sees
+// the guard alive+working via file-mtime + ok. A crash never reaches these writes, so a STALE heartbeat
+// surfaces a silent re-death — the exact week-long MODULE_NOT_FOUND gap that killed this guard unnoticed.
+const HB_F = `${DIR}/latest.json`;
+const writeHB = (offenders, enforced) => { try { fs.writeFileSync(HB_F, JSON.stringify({ ts: new Date().toISOString(), ok: true, mode: ENFORCE ? 'enforce' : 'detect', offenders, enforced }, null, 2)); } catch {} };
+const ENV = fs.readFileSync(process.env.HOME + '/Projects/secrets-manager/.env', 'utf8');
+const TOKEN = (ENV.match(/^SHOPIFY_ADMIN_TOKEN=(.+)$/m) || [])[1]?.trim();
+const ENDPOINT = 'https://designer-laboratory-sandbox.myshopify.com/admin/api/2024-10/graphql.json';
+const sleep = ms => new Promise(r => setTimeout(r, ms));
+
+// Detect off the mirror (fast; importers are PG-first so offenders appear here first).
+// 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`;
+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 }; }) : [];
+
+console.log(`[orphan-publish-guard] offenders live: ${rows.length}${ENFORCE ? '  (ENFORCE — will DRAFT)' : '  (report only)'}`);
+if (!rows.length) { console.log('  clean — no slug-SKU sample-only orphans are live.'); writeHB(0, 0); process.exit(0); }
+for (const r of rows.slice(0, 20)) console.log(`  • ${r.handle}  [${r.supplier}]`);
+if (rows.length > 20) console.log(`  … +${rows.length - 20} more`);
+
+if (!ENFORCE) {
+  console.log('\nRe-run with --enforce to auto-DRAFT these (reversible; ledgered).');
+  writeHB(rows.length, 0);
+  process.exit(rows.length ? 2 : 0);   // nonzero exit → a scheduler/canary can alert
+}
+
+async function gql(q, v) {
+  for (let i = 0; i < 6; i++) {
+    let res; try { res = await fetch(ENDPOINT, { method: 'POST', headers: { 'X-Shopify-Access-Token': TOKEN, 'Content-Type': 'application/json' }, body: JSON.stringify({ query: q, variables: v }) }); }
+    catch { await sleep(1500 * (i + 1)); continue; }
+    if (res.status === 429 || res.status >= 500) { await sleep(2000 * (i + 1)); continue; }
+    let j; try { j = await res.json(); } catch { await sleep(2000 * (i + 1)); continue; }
+    if (j.errors && JSON.stringify(j.errors).includes('THROTTLED')) { await sleep(2000 * (i + 1)); continue; }
+    return j;
+  } throw new Error('exhausted');
+}
+const MUT = `mutation($id:ID!){ productUpdate(input:{id:$id, status:DRAFT}){ product{ id status } userErrors{ message } } }`;
+const ledger = fs.createWriteStream(LEDGER, { flags: 'a' });
+let ok = 0, skip = 0;
+for (const r of rows) {
+  const rr = await gql(MUT, { id: r.gid });
+  const pu = rr.data && rr.data.productUpdate;
+  const e = (pu && pu.userErrors) || [];
+  if (!pu || e.length) { skip++; ledger.write(JSON.stringify({ handle: r.handle, gid: r.gid, reason: JSON.stringify(e.length ? e : rr.errors) }) + '\n'); continue; }
+  ledger.write(JSON.stringify({ ts: new Date().toISOString(), handle: r.handle, supplier: r.supplier, gid: r.gid, action: 'DRAFT' }) + '\n');
+  execFileSync('psql', ['host=/tmp dbname=dw_unified', '-c', `UPDATE shopify_products SET status='DRAFT', online_store_published=false, synced_at=now() WHERE shopify_id='${r.gid}'`]);
+  ok++; await sleep(150);
+}
+ledger.end();
+writeHB(rows.length, ok);
+console.log(`[orphan-publish-guard] ENFORCED: drafted=${ok} skipped=${skip}`);
← 87ae1ea auto-save: 2026-08-06T08:19:59 (3 files) — scripts/price-she · back to Designerwallcoverings · auto-data-snapshot: 2026-08-06T12:25:51 (2 data files) — dat 77b3068 →