← back to Ventura Corridor

src/jobs/coverage_snapshot.ts

39 lines

// Captures a single coverage snapshot per run. Daily launchd at 11:55 PM
// gives us a sharp end-of-day count; over weeks/months we get trend lines.
//
// Run manually:  npm run magazine:coverage-snapshot

import { Pool } from 'pg';

const pool = new Pool({ host: '/tmp', database: 'ventura_corridor' });

async function main() {
  const r = await pool.query(`
    INSERT INTO coverage_snapshots (
      total_biz, biz_with_features, total_features,
      drafts, reviewed, published, spiked,
      feedback_total, feedback_unread,
      inquiries_total, inquiries_open
    )
    SELECT
      (SELECT count(*) FROM businesses),
      (SELECT count(DISTINCT business_id) FROM magazine_features),
      (SELECT count(*) FROM magazine_features),
      (SELECT count(*) FROM magazine_features WHERE status='draft'),
      (SELECT count(*) FROM magazine_features WHERE status='reviewed'),
      (SELECT count(*) FROM magazine_features WHERE status='published'),
      (SELECT count(*) FROM magazine_features WHERE status='spiked'),
      (SELECT count(*) FROM reader_feedback),
      (SELECT count(*) FROM reader_feedback WHERE NOT reviewed),
      (SELECT count(*) FROM sponsor_inquiries),
      (SELECT count(*) FROM sponsor_inquiries WHERE status='new')
    RETURNING id, taken_at, total_biz, biz_with_features, total_features, published
  `);
  const row = r.rows[0];
  console.log(`[coverage-snapshot] #${row.id} @ ${new Date(row.taken_at).toISOString()}`);
  console.log(`  ${row.total_biz} biz · ${row.biz_with_features} covered · ${row.total_features} features (${row.published} published)`);
  await pool.end();
}

main().catch(e => { console.error('[coverage-snapshot]', e); process.exit(1); });