queue-slo-snapshot: staleness-aware Shopify write-queue SLO probe (Mac2 mirror frozen at Apr-14; refuses fake-live numbers)

7960af2d100f5ef53bf4aea9a13265d07fbe486c · 2026-06-16 06:52:32 -0700 · Steve Abrams
Files touched

A scripts/queue-slo-snapshot/queue-slo-snapshot.mjs
Diff

commit 7960af2d100f5ef53bf4aea9a13265d07fbe486c
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Tue Jun 16 06:52:32 2026 -0700

    queue-slo-snapshot: staleness-aware Shopify write-queue SLO probe (Mac2 mirror frozen at Apr-14; refuses fake-live numbers)
---
 scripts/queue-slo-snapshot/queue-slo-snapshot.mjs | 81 +++++++++++++++++++++++
 1 file changed, 81 insertions(+)

diff --git a/scripts/queue-slo-snapshot/queue-slo-snapshot.mjs b/scripts/queue-slo-snapshot/queue-slo-snapshot.mjs
new file mode 100644
index 0000000..5e764c7
--- /dev/null
+++ b/scripts/queue-slo-snapshot/queue-slo-snapshot.mjs
@@ -0,0 +1,81 @@
+// queue-slo-snapshot — read-only SLO snapshot of the Shopify prod write queue
+// (shopify_api_queue). Every DW prod write (product create/update/archive,
+// GMC-exclude, April restore, draft reactivation, body-leak strip) drains
+// through this queue via the shopify-queue-worker on Kamatera. Before Steve
+// fires any ≤1k/day batched apply, he needs to know the live queue is healthy
+// and not already backed up.
+//
+// ⚠️ STALENESS GATE (cycle-21 lesson: the instrument must detect its own
+// blindness). The Mac2 mirror copy of shopify_api_queue is NOT in the 2-table
+// logical-replication set (see memory dw-unified-kamatera-canonical), so on Mac2
+// it is a FROZEN snapshot — empirically last_completed froze 2026-04-14. This
+// script REFUSES to report SLO numbers as "live" when the newest completed_at is
+// older than --max-stale-hours (default 6). On Mac2 it will (correctly) report
+// STALE / UNRELIABLE. To get a real read, point DW_UNIFIED_URL at the canonical
+// Kamatera DB (a read-only SELECT; prod-DB read is Steve-gated from Mac2).
+//
+//   node queue-slo-snapshot.mjs [--max-stale-hours N]
+// READ-ONLY. $0. No writes.
+import { execFileSync } from 'node:child_process';
+import fs from 'node:fs';
+
+const PSQL = [ '/opt/homebrew/opt/postgresql@14/bin/psql', '/usr/local/opt/postgresql@14/bin/psql', 'psql' ]
+  .find(p => { try { execFileSync(p, ['--version'], { stdio: 'ignore' }); return true; } catch { return false; } }) || 'psql';
+const DB = process.env.DW_UNIFIED_URL || 'postgresql:///dw_unified?host=/tmp';
+const args = process.argv.slice(2);
+const MAX_STALE_H = parseFloat(args.find((_,i,a)=>a[i-1]==='--max-stale-hours') || '6') || 6;
+const OUT = `${process.env.HOME}/.claude/yolo-queue/queue-slo-snapshot-2026-06-16.json`;
+const MD  = `${process.env.HOME}/.claude/yolo-queue/queue-slo-snapshot-2026-06-16.md`;
+
+function q(sql) {
+  const out = execFileSync(PSQL, [DB, '-At', '-F', '|', '-c', sql], { encoding: 'utf8', maxBuffer: 64*1024*1024 });
+  return out.trim() ? out.trim().split('\n').map(r => r.split('|')) : [];
+}
+
+// freshness probe
+const fresh = q(`select extract(epoch from (now()-max(completed_at)))/3600.0,
+                        max(completed_at), max(created_at),
+                        count(*) filter (where completed_at > now()-interval '24 hours')
+                 from shopify_api_queue`)[0] || [];
+const hoursSinceCompleted = fresh[0] === '' || fresh[0] == null ? null : parseFloat(fresh[0]);
+const lastCompleted = fresh[1] || '(none)';
+const lastCreated   = fresh[2] || '(none)';
+const tput24 = parseInt(fresh[3] || '0', 10);
+const STALE = hoursSinceCompleted == null || hoursSinceCompleted > MAX_STALE_H;
+
+const byStatus = q(`select status, count(*) from shopify_api_queue group by status order by 2 desc`);
+const pendingAge = q(`select min(created_at), extract(epoch from (now()-min(created_at)))/3600.0
+                      from shopify_api_queue where status in ('pending','queued','pending_product')`)[0] || [];
+const errAgg = q(`select count(*) filter (where error is not null),
+                         count(*) filter (where retries>0),
+                         count(*) filter (where retries>=max_retries and max_retries>0)
+                  from shopify_api_queue`)[0] || [];
+
+const report = {
+  generated_at: new Date().toISOString(), db: DB.replace(/:[^:@/]*@/, ':***@'),
+  freshness: { hours_since_last_completed: hoursSinceCompleted, last_completed: lastCompleted,
+    last_created: lastCreated, throughput_24h: tput24, max_stale_hours: MAX_STALE_H, stale: STALE },
+  by_status: Object.fromEntries(byStatus.map(r => [r[0], parseInt(r[1],10)])),
+  oldest_pending: pendingAge[0] || null, oldest_pending_age_h: pendingAge[1] ? parseFloat(pendingAge[1]) : null,
+  errors: { with_error: parseInt(errAgg[0]||'0',10), retried: parseInt(errAgg[1]||'0',10), exhausted: parseInt(errAgg[2]||'0',10) },
+};
+fs.writeFileSync(OUT, JSON.stringify(report, null, 2));
+
+let md = `# Shopify write-queue SLO snapshot — ${new Date().toISOString().slice(0,16)}\n\n`;
+md += `DB: \`${report.db}\` · **READ-ONLY, $0.**\n\n`;
+if (STALE) {
+  md += `## 🔴 STALE / UNRELIABLE — do NOT treat these numbers as live\n`;
+  md += `Newest \`completed_at\` is **${hoursSinceCompleted==null?'unknown':hoursSinceCompleted.toFixed(1)+'h'} old** (last: ${lastCompleted}), throughput-24h = **${tput24}**.\n`;
+  md += `This copy is not being drained — on Mac2 the queue table is a frozen snapshot (not in the 2-table logical-replication set, per \`dw-unified-kamatera-canonical\`). `;
+  md += `**A real SLO read must run against the canonical Kamatera DB.** Below is the frozen-snapshot content for reference only.\n\n`;
+} else {
+  md += `## 🟢 LIVE — drained within ${MAX_STALE_H}h\nLast completed ${hoursSinceCompleted.toFixed(2)}h ago · throughput-24h = ${tput24}.\n\n`;
+}
+md += `**Status distribution:** ${byStatus.map(r=>`${r[0]}=${r[1]}`).join(' · ')}\n\n`;
+md += `**Oldest pending:** ${report.oldest_pending||'—'} (${report.oldest_pending_age_h?report.oldest_pending_age_h.toFixed(1)+'h':'—'})\n\n`;
+md += `**Errors:** with_error=${report.errors.with_error} · retried=${report.errors.retried} · retry-exhausted=${report.errors.exhausted}\n`;
+fs.writeFileSync(MD, md);
+
+console.log(`[queue-slo] ${STALE?'🔴 STALE':'🟢 LIVE'} · last_completed=${lastCompleted} · tput24=${tput24} · pending=${report.by_status.pending||0}`);
+console.log(`Report: ${MD}`);
+process.exit(STALE ? 2 : 0);
← 0607790 Availability-drift canary: built, tested, found INVALID-as-i · back to Dw Yolo Loop · handle-freshness canary: storefront 404 check on mirror-ACTI 781635c →