`scripts/handle-freshness/handle-freshness-canary.mjs`

111 lines
// 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 today = new Date().toISOString().slice(0,10); // dynamic — a scheduled run must not clobber a prior day's report
const OUT = `${process.env.HOME}/.claude/yolo-queue/handle-freshness-${today}.json`;
const MD  = `${process.env.HOME}/.claude/yolo-queue/handle-freshness-${today}.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));

// best-effort local CNCP alert — timeout-bounded, NEVER throws (canary must still finish).
async function alertCNCP(note) {
  try { const ac = new AbortController(); const t = setTimeout(()=>ac.abort(), 6000);
    const r = await fetch(`${process.env.CNCP_URL||'http://localhost:3333'}/api/parking-lot`,
      { method:'POST', headers:{'Content-Type':'application/json'},
        body: JSON.stringify({ url:'file://handle-freshness-canary', note }), signal: ac.signal });
    clearTimeout(t); return r.ok; } catch { return false; }
}

// 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}`);
  if (verdict === 'FAIL') await alertCNCP(`[HANDLE-FRESHNESS 🔴 FAIL] storefront drift ${driftPct.toFixed(1)}% (${stale.length}/${decided}) — mirror-ACTIVE handles returning 404; shoppers can't reach them`);
  process.exit(verdict==='FAIL'?2:0);
})().catch(e => { console.error('FATAL', e.message); process.exit(1); });