`src/jobs/alerts_check.ts`

53 lines
/**
 * Evaluates every watchlist threshold against the latest metric value for its
 * region; fires a row into alerts_log when tripped. Re-fire suppression: skips
 * if an alert for the same watchlist+metric already fired in the last 25 days
 * (monthly cadence means one alert per period, not one per run).
 * Threshold shape: {metric:'zhvi_yoy', op:'>'|'<'|'>='|'<=', value:number}
 */
import { query, pool } from '../../db/pool.ts';

const OPS: Record<string, (a: number, b: number) => boolean> = {
  '>': (a, b) => a > b,
  '<': (a, b) => a < b,
  '>=': (a, b) => a >= b,
  '<=': (a, b) => a <= b,
};

async function main() {
  const wl = await query<{ id: number; region_id: number; label: string | null; thresholds: any }>(
    `SELECT id, region_id, label, thresholds FROM watchlist WHERE thresholds IS NOT NULL`,
  );
  let fired = 0;
  for (const w of wl.rows) {
    const t = w.thresholds;
    if (!t || !t.metric || !OPS[t.op] || typeof t.value !== 'number') continue;
    const m = await query<{ value: string; period: string }>(
      `SELECT value::text AS value, to_char(period,'YYYY-MM-DD') AS period
         FROM metric_series WHERE region_id = $1 AND metric = $2
        ORDER BY period DESC LIMIT 1`,
      [w.region_id, t.metric],
    );
    if (!m.rows.length) continue;
    const observed = Number(m.rows[0].value);
    if (!OPS[t.op](observed, t.value)) continue;
    const recent = await query(
      `SELECT 1 FROM alerts_log WHERE watchlist_id = $1 AND metric = $2
        AND fired_at > NOW() - INTERVAL '25 days' LIMIT 1`,
      [w.id, t.metric],
    );
    if (recent.rows.length) continue;
    await query(
      `INSERT INTO alerts_log (watchlist_id, metric, observed, detail)
       VALUES ($1, $2, $3, $4)`,
      [w.id, t.metric, observed, JSON.stringify({ threshold: t, period: m.rows[0].period, label: w.label })],
    );
    fired++;
    console.log(`[alerts] FIRED watchlist#${w.id} ${t.metric} ${t.op} ${t.value} (observed ${observed})`);
  }
  console.log(`[alerts] checked ${wl.rows.length} watches, fired ${fired}`);
  await pool.end();
}

main().catch((e) => { console.error(e); process.exit(1); });