analytics.js

/**
 * Request Analytics + Error Monitoring
 * Logs all requests to SQLite (analytics.db)
 */

import Database from 'better-sqlite3';
import path from 'path';
import { fileURLToPath } from 'url';

const __dirname = path.dirname(fileURLToPath(import.meta.url));
const db = new Database(path.join(__dirname, 'analytics.db'));

db.pragma('journal_mode = WAL');
db.pragma('synchronous = NORMAL');

db.exec(`
  CREATE TABLE IF NOT EXISTS requests (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ts INTEGER NOT NULL,
    method TEXT NOT NULL,
    path TEXT NOT NULL,
    status INTEGER NOT NULL,
    duration_ms INTEGER NOT NULL,
    ip TEXT,
    user_agent TEXT,
    paid INTEGER DEFAULT 0,
    payment_amount TEXT
  );
  CREATE INDEX IF NOT EXISTS idx_requests_ts ON requests(ts);
  CREATE INDEX IF NOT EXISTS idx_requests_path ON requests(path);

  CREATE TABLE IF NOT EXISTS errors (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ts INTEGER NOT NULL,
    path TEXT,
    error_type TEXT,
    error_message TEXT,
    stack TEXT,
    context TEXT
  );
  CREATE INDEX IF NOT EXISTS idx_errors_ts ON errors(ts);

  CREATE TABLE IF NOT EXISTS upstream_failures (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ts INTEGER NOT NULL,
    upstream TEXT NOT NULL,
    status_code INTEGER,
    error_message TEXT
  );
`);

const insertRequest = db.prepare(`
  INSERT INTO requests (ts, method, path, status, duration_ms, ip, user_agent, paid, payment_amount)
  VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
`);

const insertError = db.prepare(`
  INSERT INTO errors (ts, path, error_type, error_message, stack, context)
  VALUES (?, ?, ?, ?, ?, ?)
`);

const insertUpstreamFailure = db.prepare(`
  INSERT INTO upstream_failures (ts, upstream, status_code, error_message)
  VALUES (?, ?, ?, ?)
`);

export function requestLogger() {
  return (req, res, next) => {
    const start = Date.now();
    const origEnd = res.end.bind(res);
    res.end = function (...args) {
      const duration = Date.now() - start;
      try {
        const paid = res.statusCode !== 402 && req.path.startsWith('/api/') && (req.headers['payment-signature'] || req.headers['x-payment']) ? 1 : 0;
        insertRequest.run(
          start,
          req.method,
          req.path,
          res.statusCode,
          duration,
          req.ip || req.headers['x-forwarded-for'] || 'unknown',
          (req.headers['user-agent'] || '').substring(0, 255),
          paid,
          null
        );
      } catch (e) { /* never break requests */ }
      return origEnd(...args);
    };
    next();
  };
}

export function logError(err, context = {}) {
  try {
    insertError.run(
      Date.now(),
      context.path || null,
      err?.name || 'Error',
      (err?.message || String(err)).substring(0, 1000),
      (err?.stack || '').substring(0, 2000),
      JSON.stringify(context).substring(0, 500)
    );
  } catch {}
}

export function logUpstreamFailure(upstream, status, message) {
  try {
    insertUpstreamFailure.run(Date.now(), upstream, status || null, (message || '').substring(0, 500));
  } catch {}
}

export function errorHandler() {
  return (err, req, res, next) => {
    logError(err, { path: req.path, method: req.method });
    if (res.headersSent) return next(err);
    res.status(500).json({ error: 'Internal server error' });
  };
}

// ============ STATS QUERIES (for /stats endpoint) ============

export function getStats() {
  const now = Date.now();
  const h1 = now - 3600_000;
  const h24 = now - 86400_000;
  const d7 = now - 7 * 86400_000;

  const q = (sql, ...params) => db.prepare(sql).get(...params);

  return {
    generatedAt: new Date().toISOString(),
    totals: {
      allTime: q('SELECT COUNT(*) AS c FROM requests').c,
      last7Days: q('SELECT COUNT(*) AS c FROM requests WHERE ts > ?', d7).c,
      last24Hours: q('SELECT COUNT(*) AS c FROM requests WHERE ts > ?', h24).c,
      lastHour: q('SELECT COUNT(*) AS c FROM requests WHERE ts > ?', h1).c,
    },
    paidRequests: {
      last24h: q('SELECT COUNT(*) AS c FROM requests WHERE paid = 1 AND ts > ?', h24).c,
      allTime: q('SELECT COUNT(*) AS c FROM requests WHERE paid = 1').c,
    },
    paywallHits_402: {
      last24h: q('SELECT COUNT(*) AS c FROM requests WHERE status = 402 AND ts > ?', h24).c,
      allTime: q('SELECT COUNT(*) AS c FROM requests WHERE status = 402').c,
    },
    topEndpoints_24h: db.prepare(`
      SELECT path, COUNT(*) AS count, AVG(duration_ms) AS avg_ms, SUM(CASE WHEN status = 402 THEN 1 ELSE 0 END) AS paywalls
      FROM requests WHERE ts > ? GROUP BY path ORDER BY count DESC LIMIT 10
    `).all(h24),
    errors_24h: q('SELECT COUNT(*) AS c FROM errors WHERE ts > ?', h24).c,
    upstreamFailures_24h: q('SELECT COUNT(*) AS c FROM upstream_failures WHERE ts > ?', h24).c,
    avgResponseTime_24h: q('SELECT AVG(duration_ms) AS avg FROM requests WHERE ts > ?', h24).avg,
  };
}

export { db };