server/dist/routes/analytics.js

import { Router } from 'express';
import { getDb } from '../db/index.js';
import { FALLBACK_INPUT_PER_M, FALLBACK_OUTPUT_PER_M } from '../db/model-pricing.js';
export const analyticsRouter = Router();
// Format UTC timestamps the same way SQLite stores created_at text values.
const toSqliteDateTime = (timestamp) => new Date(timestamp).toISOString().slice(0, 19).replace('T', ' ');
// Return the rolling cutoff timestamp for the selected analytics range.
function getSinceTimestamp(range) {
    const now = Date.now();
    switch (range) {
        case '24h':
            return toSqliteDateTime(now - 24 * 60 * 60 * 1000);
        case '30d':
            return toSqliteDateTime(now - 30 * 24 * 60 * 60 * 1000);
        case '7d':
        default:
            return toSqliteDateTime(now - 7 * 24 * 60 * 60 * 1000);
    }
}
// Summary stats
analyticsRouter.get('/summary', (req, res) => {
    const range = req.query.range ?? '7d';
    const since = getSinceTimestamp(range);
    const db = getDb();
    // Savings are priced per request at the served model's paid-equivalent
    // rate (models.paid_input_per_m / paid_output_per_m — see db/model-pricing.ts),
    // with a modest fallback for custom/unmapped models, and only count
    // successful requests. This is "what the same tokens would have cost on
    // paid APIs", not a GPT-4o fantasy number.
    const stats = db.prepare(`
    SELECT
      COUNT(*) as total_requests,
      SUM(CASE WHEN r.status = 'success' THEN 1 ELSE 0 END) as success_count,
      SUM(r.input_tokens) as total_input_tokens,
      SUM(r.output_tokens) as total_output_tokens,
      AVG(r.latency_ms) as avg_latency_ms,
      MIN(r.created_at) as first_request_at,
      SUM(CASE WHEN r.requested_model IS NOT NULL THEN 1 ELSE 0 END) as pinned_count,
      SUM(CASE WHEN r.requested_model = r.model_id THEN 1 ELSE 0 END) as pin_honored_count,
      SUM(CASE WHEN r.status = 'success' THEN
        r.input_tokens  * COALESCE(m.paid_input_per_m,  ?) / 1000000.0 +
        r.output_tokens * COALESCE(m.paid_output_per_m, ?) / 1000000.0
      ELSE 0 END) as est_savings
    FROM requests r
    LEFT JOIN models m ON m.platform = r.platform AND m.model_id = r.model_id
    WHERE r.created_at >= ?
  `).get(FALLBACK_INPUT_PER_M, FALLBACK_OUTPUT_PER_M, since);
    const totalRequests = stats.total_requests ?? 0;
    const successRate = totalRequests > 0 ? (stats.success_count / totalRequests) * 100 : 0;
    res.json({
        totalRequests,
        successRate: Math.round(successRate * 10) / 10,
        totalInputTokens: stats.total_input_tokens ?? 0,
        totalOutputTokens: stats.total_output_tokens ?? 0,
        avgLatencyMs: Math.round(stats.avg_latency_ms ?? 0),
        estimatedCostSavings: Math.round((stats.est_savings ?? 0) * 100) / 100,
        // Pinned = requests where the client named a specific model (not 'auto').
        // Honored = the pinned model actually served it; the difference is
        // failovers that overrode the pin.
        pinnedRequests: stats.pinned_count ?? 0,
        pinHonoredRequests: stats.pin_honored_count ?? 0,
        // Lets the client project savings from the ACTUAL data span (a 2-day-old
        // install shouldn't extrapolate as if the whole range had traffic).
        firstRequestAt: stats.first_request_at ?? null,
    });
});
// Stats grouped by model
analyticsRouter.get('/by-model', (req, res) => {
    const range = req.query.range ?? '7d';
    const since = getSinceTimestamp(range);
    const db = getDb();
    const rows = db.prepare(`
    SELECT
      r.platform,
      r.model_id,
      m.display_name,
      COUNT(*) as requests,
      SUM(CASE WHEN r.status = 'success' THEN 1 ELSE 0 END) * 100.0 / COUNT(*) as success_rate,
      AVG(r.latency_ms) as avg_latency_ms,
      SUM(r.input_tokens) as total_input_tokens,
      SUM(r.output_tokens) as total_output_tokens,
      SUM(CASE WHEN r.requested_model = r.model_id THEN 1 ELSE 0 END) as pinned_requests,
      SUM(CASE WHEN r.status = 'success' THEN
        r.input_tokens  * COALESCE(m.paid_input_per_m,  ?) / 1000000.0 +
        r.output_tokens * COALESCE(m.paid_output_per_m, ?) / 1000000.0
      ELSE 0 END) as est_cost
    FROM requests r
    LEFT JOIN models m ON m.platform = r.platform AND m.model_id = r.model_id
    WHERE r.created_at >= ?
    GROUP BY r.platform, r.model_id
    ORDER BY requests DESC
  `).all(FALLBACK_INPUT_PER_M, FALLBACK_OUTPUT_PER_M, since);
    res.json(rows.map(r => ({
        platform: r.platform,
        modelId: r.model_id,
        displayName: r.display_name ?? r.model_id,
        requests: r.requests,
        successRate: Math.round(r.success_rate * 10) / 10,
        avgLatencyMs: Math.round(r.avg_latency_ms),
        totalInputTokens: r.total_input_tokens ?? 0,
        totalOutputTokens: r.total_output_tokens ?? 0,
        // Requests this model served because the client pinned it by name.
        pinnedRequests: r.pinned_requests ?? 0,
        estimatedCost: Math.round((r.est_cost ?? 0) * 100) / 100,
    })));
});
// Stats grouped by platform
analyticsRouter.get('/by-platform', (req, res) => {
    const range = req.query.range ?? '7d';
    const since = getSinceTimestamp(range);
    const db = getDb();
    const rows = db.prepare(`
    SELECT
      platform,
      COUNT(*) as requests,
      SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) * 100.0 / COUNT(*) as success_rate,
      AVG(latency_ms) as avg_latency_ms,
      SUM(input_tokens) as total_input_tokens,
      SUM(output_tokens) as total_output_tokens
    FROM requests
    WHERE created_at >= ?
    GROUP BY platform
    ORDER BY requests DESC
  `).all(since);
    res.json(rows.map(r => ({
        platform: r.platform,
        requests: r.requests,
        successRate: Math.round(r.success_rate * 10) / 10,
        avgLatencyMs: Math.round(r.avg_latency_ms),
        totalInputTokens: r.total_input_tokens ?? 0,
        totalOutputTokens: r.total_output_tokens ?? 0,
    })));
});
// Timeline data
analyticsRouter.get('/timeline', (req, res) => {
    const range = req.query.range ?? '7d';
    const interval = req.query.interval ?? (range === '24h' ? 'hour' : 'day');
    const since = getSinceTimestamp(range);
    const db = getDb();
    // dateFormat is a hardcoded whitelist — never user-controlled.
    const dateFormat = interval === 'hour' ? '%Y-%m-%dT%H:00:00' : '%Y-%m-%d';
    const rows = db.prepare(`
    SELECT
      strftime('${dateFormat}', created_at) as timestamp,
      COUNT(*) as requests,
      SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) as success_count,
      SUM(CASE WHEN status = 'error' THEN 1 ELSE 0 END) as failure_count
    FROM requests
    WHERE created_at >= ?
    GROUP BY strftime('${dateFormat}', created_at)
    ORDER BY timestamp ASC
  `).all(since);
    res.json(rows.map(r => ({
        timestamp: r.timestamp,
        requests: r.requests,
        successCount: r.success_count,
        failureCount: r.failure_count,
    })));
});
// Error distribution (grouped by error type and platform)
analyticsRouter.get('/error-distribution', (req, res) => {
    const range = req.query.range ?? '7d';
    const since = getSinceTimestamp(range);
    const db = getDb();
    // Group errors by category (extract the key part of the error message)
    const rows = db.prepare(`
    SELECT
      platform,
      model_id,
      CASE
        WHEN error LIKE '%429%' OR error LIKE '%rate limit%' OR error LIKE '%too many%' OR error LIKE '%quota%' THEN 'Rate Limited (429)'
        WHEN error LIKE '%401%' OR error LIKE '%unauthorized%' OR error LIKE '%invalid.*key%' THEN 'Auth Error (401)'
        WHEN error LIKE '%403%' OR error LIKE '%forbidden%' THEN 'Forbidden (403)'
        WHEN error LIKE '%404%' OR error LIKE '%not found%' THEN 'Not Found (404)'
        WHEN error LIKE '%timeout%' OR error LIKE '%ETIMEDOUT%' OR error LIKE '%ECONNREFUSED%' THEN 'Timeout/Connection'
        WHEN error LIKE '%500%' OR error LIKE '%internal server%' THEN 'Server Error (500)'
        WHEN error LIKE '%503%' OR error LIKE '%unavailable%' THEN 'Unavailable (503)'
        ELSE 'Other'
      END as error_category,
      COUNT(*) as count
    FROM requests
    WHERE status = 'error' AND created_at >= ?
    GROUP BY platform, error_category
    ORDER BY count DESC
  `).all(since);
    // Also get totals by category
    const byCategory = db.prepare(`
    SELECT
      CASE
        WHEN error LIKE '%429%' OR error LIKE '%rate limit%' OR error LIKE '%too many%' OR error LIKE '%quota%' THEN 'Rate Limited (429)'
        WHEN error LIKE '%401%' OR error LIKE '%unauthorized%' OR error LIKE '%invalid.*key%' THEN 'Auth Error (401)'
        WHEN error LIKE '%403%' OR error LIKE '%forbidden%' THEN 'Forbidden (403)'
        WHEN error LIKE '%404%' OR error LIKE '%not found%' THEN 'Not Found (404)'
        WHEN error LIKE '%timeout%' OR error LIKE '%ETIMEDOUT%' OR error LIKE '%ECONNREFUSED%' THEN 'Timeout/Connection'
        WHEN error LIKE '%500%' OR error LIKE '%internal server%' THEN 'Server Error (500)'
        WHEN error LIKE '%503%' OR error LIKE '%unavailable%' THEN 'Unavailable (503)'
        ELSE 'Other'
      END as category,
      COUNT(*) as count
    FROM requests
    WHERE status = 'error' AND created_at >= ?
    GROUP BY category
    ORDER BY count DESC
  `).all(since);
    // Errors by platform
    const byPlatform = db.prepare(`
    SELECT platform, COUNT(*) as count
    FROM requests
    WHERE status = 'error' AND created_at >= ?
    GROUP BY platform
    ORDER BY count DESC
  `).all(since);
    res.json({
        byCategory,
        byPlatform,
        detailed: rows,
    });
});
// Recent errors
analyticsRouter.get('/errors', (req, res) => {
    const range = req.query.range ?? '7d';
    const since = getSinceTimestamp(range);
    const db = getDb();
    const rows = db.prepare(`
    SELECT id, platform, model_id, error, latency_ms, created_at
    FROM requests
    WHERE status = 'error' AND created_at >= ?
    ORDER BY created_at DESC
    LIMIT 50
  `).all(since);
    res.json(rows.map(r => ({
        id: r.id,
        platform: r.platform,
        modelId: r.model_id,
        error: r.error,
        latencyMs: r.latency_ms,
        createdAt: r.created_at,
    })));
});
//# sourceMappingURL=analytics.js.map