`scripts/scraper-contract-test/scraper-contract-test.mjs`

86 lines
// scraper-contract-test — DB-side field-depth contract test for the top-revenue
// scrapers. Complements dw-scraper-canary (which checks output-side count/leak/image
// from the PUBLIC feed and explicitly defers field-depth + price to "a dw_unified DB
// canary (future)"). This is that DB canary.
//
// Per top vendor, asserts the contract against the local dw_unified mirror:
//   FAIL  active_count == 0                 (silent stop — scraper produced nothing)
//   FAIL  image coverage  < 90%             (scraper dropping images)
//   WARN  sku coverage    < 95%             (missing mfr_sku)
//   WARN  title coverage  < 99%             (missing/blank titles)
//   FAIL  leak: any title contains an upstream-only maker name that must never appear
//   INFO  price coverage  (% with a real price >$5 in price OR retail_price) — INFO only
//         because price columns are inconsistently mirrored (variant-only for some
//         vendors), so a price FAIL would cry-wolf. Visibility, not a gate.
//
// READ-ONLY local psql. Writes only the report. Cost: $0. No live vendor fetches/creds.
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 OUT = `${process.env.HOME}/.claude/yolo-queue/scraper-contract-test-2026-06-16.json`;
const MD = `${process.env.HOME}/.claude/yolo-queue/scraper-contract-test-2026-06-16.md`;

// Top-revenue vendors (by ACTIVE count) — the highest-impact ingest paths.
const VENDORS = ['Phillipe Romano','Rebel Walls','Thibaut','Hollywood Wallcoverings','Kravet',
  'Malibu Wallpaper','China Seas','Schumacher','Los Angeles Fabrics','Brunschwig & Fils'];

// Upstream-only maker names that must NEVER appear in a DW title (private-label leak).
// NB: 'chesapeake' deliberately EXCLUDED — it's also a legit Schumacher pattern name
// ("Chesapeake - Topaz"), so it false-positives. Keep only uniquely-upstream tokens.
const LEAK = ['wallquest','command54','nextwall','seabrook'];

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 esc = (s) => s.replace(/'/g, "''");

const results = [];
for (const v of VENDORS) {
  const row = q(`select
      count(*) total,
      count(*) filter (where image_url is not null and image_url<>'') img,
      count(*) filter (where mfr_sku is not null and mfr_sku<>'') sku,
      count(*) filter (where title is not null and title<>'') ttl,
      count(*) filter (where price::numeric>5 or retail_price::numeric>5) priced,
      count(*) filter (where lower(title) ~ '${LEAK.join('|')}') leak
    from shopify_products where status='ACTIVE' and vendor='${esc(v)}'`)[0];
  const [total, img, sku, ttl, priced, leak] = row.map(Number);
  const pct = (n) => total ? +(100 * n / total).toFixed(1) : 0;
  const checks = [];
  if (total === 0) checks.push({ sev: 'FAIL', m: 'silent stop: 0 active products' });
  if (total > 0 && pct(img) < 90) checks.push({ sev: 'FAIL', m: `image coverage ${pct(img)}% (<90%)` });
  if (total > 0 && pct(sku) < 95) checks.push({ sev: 'WARN', m: `sku coverage ${pct(sku)}% (<95%)` });
  if (total > 0 && pct(ttl) < 99) checks.push({ sev: 'WARN', m: `title coverage ${pct(ttl)}% (<99%)` });
  if (leak > 0) checks.push({ sev: 'FAIL', m: `${leak} title(s) leak an upstream maker name` });
  const sev = checks.some(c => c.sev === 'FAIL') ? 'FAIL' : checks.some(c => c.sev === 'WARN') ? 'WARN' : 'PASS';
  results.push({ vendor: v, total, img_pct: pct(img), sku_pct: pct(sku), title_pct: pct(ttl),
    price_coverage_pct: pct(priced), leak, verdict: sev, checks });
}

const summary = results.reduce((m, r) => (m[r.verdict] = (m[r.verdict]||0)+1, m), {});
const out = { generated_at: new Date().toISOString(), vendors: VENDORS.length, summary, results };
fs.writeFileSync(OUT, JSON.stringify(out, null, 2));

let md = `# Scraper contract test (DB-side, top-revenue vendors) — 2026-06-16\n\n`;
md += `Complements dw-scraper-canary (output-side count/leak/image). This adds DB-side field-depth + price-coverage. **READ-ONLY, $0.**\n\n`;
md += `Summary: ${Object.entries(summary).map(([k,v])=>`${k}=${v}`).join(' · ')}\n\n`;
md += `| Vendor | Active | img% | sku% | title% | priced% | leak | Verdict |\n|---|---:|---:|---:|---:|---:|---:|---|\n`;
for (const r of results) {
  const tag = { FAIL:'🔴', WARN:'🟠', PASS:'🟢' }[r.verdict];
  md += `| ${r.vendor} | ${r.total} | ${r.img_pct} | ${r.sku_pct} | ${r.title_pct} | ${r.price_coverage_pct} | ${r.leak} | ${tag} ${r.verdict} |\n`;
}
md += `\n## Findings\n`;
for (const r of results.filter(r => r.checks.length)) {
  md += `- **${r.vendor}**: ${r.checks.map(c => `${c.sev==='FAIL'?'🔴':'🟠'} ${c.m}`).join('; ')}\n`;
}
md += `\n_Note: price coverage is INFO-only — dw_unified price columns are inconsistently mirrored (variant-only for some vendors like Rebel Walls), so a price FAIL would cry-wolf. The output-side dw-scraper-canary owns silent-stop-vs-baseline + public-feed leak/image; this owns DB field-depth._\n`;
fs.writeFileSync(MD, md);

console.log(`[scraper-contract-test] ${Object.entries(summary).map(([k,v])=>`${k}=${v}`).join(' ')}`);
for (const r of results) console.log(`  ${{FAIL:'🔴',WARN:'🟠',PASS:'🟢'}[r.verdict]} ${r.vendor.padEnd(24)} n=${String(r.total).padStart(5)} img=${r.img_pct}% sku=${r.sku_pct}% priced=${r.price_coverage_pct}%${r.checks.length?'  ← '+r.checks.map(c=>c.m).join('; '):''}`);
console.log(`Report: ${MD}`);