handle-freshness canary: storefront 404 check on mirror-ACTIVE handles (DTD 3/3; PASS 4.4% drift, all explained mirror-lag)

781635cadb27f11eb6dd16fc36db3a079ffbfedd · 2026-06-16 07:12:47 -0700 · Steve Abrams
Files touched

A scripts/handle-freshness/handle-freshness-canary.mjs
Diff

commit 781635cadb27f11eb6dd16fc36db3a079ffbfedd
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Tue Jun 16 07:12:47 2026 -0700

    handle-freshness canary: storefront 404 check on mirror-ACTIVE handles (DTD 3/3; PASS 4.4% drift, all explained mirror-lag)
---
 .../handle-freshness/handle-freshness-canary.mjs   | 99 ++++++++++++++++++++++
 1 file changed, 99 insertions(+)

diff --git a/scripts/handle-freshness/handle-freshness-canary.mjs b/scripts/handle-freshness/handle-freshness-canary.mjs
new file mode 100644
index 0000000..e8843d6
--- /dev/null
+++ b/scripts/handle-freshness/handle-freshness-canary.mjs
@@ -0,0 +1,99 @@
+// handle-freshness-canary — does each dw_unified ACTIVE product actually resolve
+// on the live storefront? Samples N ACTIVE handles (stratified across vendors),
+// GETs https://www.designerwallcoverings.com/products/<handle>.json, and counts
+// 404s = stale/unpublished handles a shopper literally cannot reach.
+//
+// This is the VALIDATED signal salvaged from cycle 21 (the killed availability-
+// drift canary). Unlike variant.available (by-design-false on a trade/inquiry
+// store → cry-wolf), a 404 on a mirror-ACTIVE handle is UNAMBIGUOUS: the product
+// is not shopper-reachable (stale handle, unpublished, or mirror↔live drift).
+// It directly de-risks the gated reactivation/restore applies — those set
+// products ACTIVE by id, but a stale handle means shoppers still 404.
+//
+//   node handle-freshness-canary.mjs [--n 300] [--per-vendor 0] [--warn 5] [--fail 15]
+// READ-ONLY (public product JSON + local psql). $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 BASE = 'https://www.designerwallcoverings.com';
+const args = process.argv.slice(2);
+const N = parseInt(args.find((_,i,a)=>a[i-1]==='--n') || '300', 10) || 300;
+const PER_VENDOR = parseInt(args.find((_,i,a)=>a[i-1]==='--per-vendor') || '0', 10) || 0; // 0 = flat random across all
+const WARN = parseFloat(args.find((_,i,a)=>a[i-1]==='--warn') || '5') || 5;   // %
+const FAIL = parseFloat(args.find((_,i,a)=>a[i-1]==='--fail') || '15') || 15; // %
+const OUT = `${process.env.HOME}/.claude/yolo-queue/handle-freshness-2026-06-16.json`;
+const MD  = `${process.env.HOME}/.claude/yolo-queue/handle-freshness-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('|')) : [];
+}
+const sleep = (ms) => new Promise(r => setTimeout(r, ms));
+
+// stratified or flat sample. random() ordering = representative draw (not oldest-biased like cycle 21's first run).
+let rows;
+if (PER_VENDOR > 0) {
+  rows = q(`with r as (select vendor, handle, mfr_sku, row_number() over (partition by vendor order by random()) rn
+            from shopify_products where status='ACTIVE' and handle is not null and handle<>'')
+            select vendor, handle, mfr_sku from r where rn <= ${PER_VENDOR} order by random() limit ${N}`)
+    .map(r => ({ vendor: r[0], handle: r[1], sku: r[2] }));
+} else {
+  rows = q(`select vendor, handle, mfr_sku from shopify_products
+            where status='ACTIVE' and handle is not null and handle<>'' order by random() limit ${N}`)
+    .map(r => ({ vendor: r[0], handle: r[1], sku: r[2] }));
+}
+
+async function check(p) {
+  try {
+    const res = await fetch(`${BASE}/products/${encodeURIComponent(p.handle)}.json`,
+      { headers: { 'User-Agent': 'dw-handle-freshness-canary' }, signal: AbortSignal.timeout(15000) });
+    if (res.status === 404) return { ...p, state: 'STALE_404' };
+    if (res.status === 200) return { ...p, state: 'OK' };
+    if (res.status === 429 || res.status >= 500) return { ...p, state: 'UNKNOWN', http: res.status };
+    return { ...p, state: 'UNKNOWN', http: res.status };
+  } catch (e) { return { ...p, state: 'UNKNOWN', detail: String(e.message).slice(0,40) }; }
+}
+
+(async () => {
+  const results = [];
+  for (const p of rows) { results.push(await check(p)); await sleep(120); }
+  const ok = results.filter(r => r.state==='OK').length;
+  const stale = results.filter(r => r.state==='STALE_404');
+  const unknown = results.filter(r => r.state==='UNKNOWN').length;
+  const decided = ok + stale.length; // exclude UNKNOWN from the rate denominator
+  const driftPct = decided ? (stale.length / decided) * 100 : 0;
+  // Wald 95% CI half-width on the drift proportion (bounded estimate, honest about sample size)
+  const pHat = decided ? stale.length/decided : 0;
+  const ci = decided ? 1.96 * Math.sqrt(pHat*(1-pHat)/decided) * 100 : 0;
+  const verdict = driftPct >= FAIL ? 'FAIL' : driftPct >= WARN ? 'WARN' : 'PASS';
+
+  const byVendor = {};
+  for (const r of results) { (byVendor[r.vendor] ||= {n:0,stale:0}); byVendor[r.vendor].n++; if (r.state==='STALE_404') byVendor[r.vendor].stale++; }
+  const worstVendors = Object.entries(byVendor).filter(([,v])=>v.stale>0).sort((a,b)=>b[1].stale-a[1].stale).slice(0,10);
+
+  const report = { generated_at: new Date().toISOString(), sampled: results.length, decided, ok, stale: stale.length,
+    unknown, drift_pct: +driftPct.toFixed(2), ci95_halfwidth_pct: +ci.toFixed(2), verdict, warn_at: WARN, fail_at: FAIL,
+    stale_handles: stale.map(s=>({vendor:s.vendor,sku:s.sku,handle:s.handle})),
+    worst_vendors: worstVendors.map(([v,s])=>({vendor:v, sampled:s.n, stale:s.stale})) };
+  fs.writeFileSync(OUT, JSON.stringify(report, null, 2));
+
+  const emoji = verdict==='FAIL'?'🔴':verdict==='WARN'?'🟠':'🟢';
+  let md = `# Handle-freshness canary — ${new Date().toISOString().slice(0,16)}\n\n`;
+  md += `Sampled **${results.length}** random ACTIVE handles (mirror) → GET storefront \`/products/<handle>.json\`. **READ-ONLY, $0.**\n\n`;
+  md += `## ${emoji} ${verdict} — drift ${driftPct.toFixed(1)}% ±${ci.toFixed(1)} (95% CI), thresholds WARN≥${WARN}% / FAIL≥${FAIL}%\n`;
+  md += `OK=${ok} · STALE_404=${stale.length} · UNKNOWN=${unknown} (excluded from rate) · decided n=${decided}\n\n`;
+  md += stale.length ? `### Stale (mirror-ACTIVE but storefront 404)\n| Vendor | SKU | Handle |\n|---|---|---|\n`
+    + stale.slice(0,40).map(s=>`| ${s.vendor} | ${s.sku} | ${s.handle} |`).join('\n') + '\n\n'
+    : `No stale handles in the sample — every sampled ACTIVE product resolves on the storefront. 🟢\n\n`;
+  if (worstVendors.length) { md += `### Worst vendors (by stale count in sample)\n| Vendor | Sampled | Stale |\n|---|---:|---:|\n`
+    + worstVendors.map(([v,s])=>`| ${v} | ${s.n} | ${s.stale} |`).join('\n') + '\n\n'; }
+  md += `_Random representative draw (not oldest-biased). UNKNOWN (429/5xx/timeout) excluded from the drift denominator — transient, not drift. Scale --n for tighter CI._\n`;
+  fs.writeFileSync(MD, md);
+
+  console.log(`[handle-freshness] ${emoji} ${verdict} · drift=${driftPct.toFixed(1)}% ±${ci.toFixed(1)} · OK=${ok} STALE=${stale.length} UNK=${unknown} (n=${results.length})`);
+  console.log(`Report: ${MD}`);
+  process.exit(verdict==='FAIL'?2:0);
+})().catch(e => { console.error('FATAL', e.message); process.exit(1); });
← 7960af2 queue-slo-snapshot: staleness-aware Shopify write-queue SLO · back to Dw Yolo Loop · tls-expiry canary: read-only fleet cert-expiry check (DTD 3/ 587c4e4 →