`src/jobs/daily_snapshot.ts`

48 lines
/**
 * Daily corridor activity snapshot.
 *
 * Run nightly (or any time — UPSERT keys on snap_date) to capture today's
 * pitch counts grouped by status / outreach_channel / dw_proximity / totals
 * into pitch_daily_snapshot. The /today.html chart reads this table.
 *
 *   $ npx tsx src/jobs/daily_snapshot.ts            # snapshot today (default)
 *   $ npx tsx src/jobs/daily_snapshot.ts 2026-05-06 # snapshot a specific date
 */
import 'dotenv/config';
import { pool, query } from '../../db/pool.ts';

const TARGET = process.argv[2] || new Date().toISOString().slice(0, 10);

async function main() {
  if (!/^\d{4}-\d{2}-\d{2}$/.test(TARGET)) {
    throw new Error(`bad date arg: ${TARGET} — expected YYYY-MM-DD`);
  }

  const r = await query(`SELECT dim, bucket, count FROM v_pitch_today_status`);
  let upserted = 0;
  for (const row of r.rows) {
    await query(
      `
      INSERT INTO pitch_daily_snapshot (snap_date, dim, bucket, count, captured_at)
      VALUES ($1, $2, $3, $4, NOW())
      ON CONFLICT (snap_date, dim, bucket)
      DO UPDATE SET count = EXCLUDED.count, captured_at = NOW()
      `,
      [TARGET, row.dim, row.bucket, row.count]
    );
    upserted++;
  }
  console.log(
    `[daily_snapshot] ${TARGET}: ${upserted} buckets upserted (${r.rows
      .filter((x) => x.dim === 'totals')
      .map((x) => `${x.bucket}=${x.count}`)
      .join(' · ')})`
  );
  await pool.end();
}

main().catch((e) => {
  console.error('[daily_snapshot] failed:', e);
  process.exit(1);
});