7.4 billion tokens per month. 34 free LLM providers. 635 free model endpoints. All behind one /v1 endpoint, plus any custom OpenAI-compatible endpoint. Smart routing, automatic failover, encrypted keys. Personal experimentation only.
TypeScript
import { Router } from 'express';
import type { Request, Response } from 'express';
import { getDb } from '../db/index.js';
import { FALLBACK_INPUT_PER_M, FALLBACK_OUTPUT_PER_M } from '../db/model-pricing.js';
import { providerIdFor, providerDisplayName } from '../lib/provider-identity.js';
import { normalizeBaseUrl } from '../lib/endpoint-scope.js';
import { searchAnalytics, type SearchAnalyticsRange } from '../services/search.js';
export const analyticsRouter = Router();
// The endpoint identity of a request, in SQL: the serving key's base_url, ''
// when the key is gone or carries none (every catalog key). Custom endpoints
// all share the platform id 'custom' (services/custom-endpoint.ts), so any
// view that groups by `platform` alone collapses every relay into one row and
// the operator cannot tell which endpoint did what (#889) — this expression is
// the second half of the grouping key everywhere that matters.
//
// rtrim/trim mirror lib/endpoint-scope.normalizeBaseUrl so the SQL side agrees
// with the ids providerIdFor() builds: keys.ts normalizes base_url on write,
// but rows stored before it did would otherwise split one endpoint in two.
// Requires the query to LEFT JOIN api_keys as `k`.
const ENDPOINT_ID_SQL = "COALESCE(rtrim(trim(k.base_url), '/'), '')";
// Format UTC timestamps the same way SQLite stores created_at text values.
const toSqliteDateTime = (timestamp: number) =>
new Date(timestamp).toISOString().slice(0, 19).replace('T', ' ');
// First value only — Express hands back an array for a repeated query param,
// and a repeated filter is a client bug, not two filters. Same contract as
// routes/logs.ts.
function firstParam(value: unknown): string | undefined {
if (typeof value === 'string') return value;
if (Array.isArray(value) && typeof value[0] === 'string') return value[0];
return undefined;
}
// Return the rolling cutoff timestamp for the selected analytics range.
function getSinceTimestamp(range: string): string {
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 '90d':
return toSqliteDateTime(now - 90 * 24 * 60 * 60 * 1000);
case '7d':
default:
return toSqliteDateTime(now - 7 * 24 * 60 * 60 * 1000);
}
}
// Range-based window read from the durable `request_hourly` aggregate. The raw
// `requests` table is pruned by REQUEST_ANALYTICS_MAX_ROWS, so any analytics
// count that depends on a >=7d window must read from the hourly table to stay
// accurate. Hourly resolution is fine for any UI range the dashboard exposes.
function readAggregateSince(since: string) {
const db = getDb();
// Hour keys are created_at truncated to the hour, so they share SQLite's
// canonical 'YYYY-MM-DD HH:00:00' text (space separator). The range cutoff is
// already in that format — floor it to the hour and compare the strings
// directly. No separator conversion: the writer (logRequest) and the timeline
// reader both compare on the space form, so this must too.
const aggregateSince = since.slice(0, 13) + ':00:00';
const rows = db.prepare(`
SELECT
COALESCE(SUM(total_requests), 0) as total_requests,
COALESCE(SUM(success_count), 0) as success_count,
COALESCE(SUM(error_count), 0) as error_count,
COALESCE(SUM(input_tokens), 0) as total_input_tokens,
COALESCE(SUM(output_tokens), 0) as total_output_tokens,
MIN(hour) as first_request_at
FROM request_hourly
WHERE hour >= ?
`).get(aggregateSince) as {
total_requests: number;
success_count: number;
error_count: number;
total_input_tokens: number;
total_output_tokens: number;
first_request_at: string | null;
};
return rows;
}
function readLifetimeSettings() {
const db = getDb();
const row = db.prepare(`
SELECT value FROM settings WHERE key = 'first_request_at'
`).get() as { value: string } | undefined;
return row?.value ?? null;
}
// Summary stats
// Web searches (#1174) live in their own log (search_requests), so they never
// show up in the chat totals above; the dashboard's search cards read this.
const SEARCH_RANGES: readonly SearchAnalyticsRange[] = ['24h', '7d', '30d', '90d', 'all'];
analyticsRouter.get('/search', (req: Request, res: Response) => {
const raw = typeof req.query.range === 'string' ? req.query.range : '7d';
const range = (SEARCH_RANGES as readonly string[]).includes(raw) ? raw as SearchAnalyticsRange : '7d';
res.json(searchAnalytics(range));
});
analyticsRouter.get('/summary', (req: Request, res: Response) => {
const range = (req.query.range as string) ?? '7d';
const since = getSinceTimestamp(range);
const db = getDb();
// Totals (request count, token sums, success rate, lifetime first_request_at)
// come from the durable `request_hourly` aggregate so they stay accurate even
// after the raw `requests` table is pruned. Per-model pin-honor rate and
// estimated savings still need the raw table because they're broken down by
// (platform, model_id); for those we fall back to the raw rows but they're
// only reported for ranges where recent activity still exists. The aggregate
// is the source of truth for headline numbers.
const aggregate = readAggregateSince(since);
const totalRequests = aggregate.total_requests ?? 0;
// Success rate over success+error only: a 'canceled' request (#752 — client
// hung up) still counts in the totals but is neither a success nor a
// failure, so it must not dilute the rate.
const decidedRequests = (aggregate.success_count ?? 0) + (aggregate.error_count ?? 0);
const successRate = decidedRequests > 0 ? (aggregate.success_count / decidedRequests) * 100 : 0;
// Avg latency is only meaningful at the raw row level; the hourly bucket
// doesn't preserve it. Fall back to a 0/null when no recent raw rows exist.
const latencyRow = db.prepare(`
SELECT AVG(latency_ms) as avg_latency_ms FROM requests WHERE created_at >= ?
`).get(since) as { avg_latency_ms: number | null } | undefined;
// Estimated savings is a per-request priced value, so it lives on the raw
// rows. For ranges where the raw table is empty we report 0 (no recent
// activity to price).
const savings = db.prepare(`
SELECT COALESCE(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
), 0) 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) as { est_savings: number };
// Pin-honor stats are also raw-row scoped. We still report them when present
// (typically 24h/7d) and gracefully drop them when the raw window is empty.
const pinRow = db.prepare(`
SELECT
SUM(CASE WHEN requested_model IS NOT NULL THEN 1 ELSE 0 END) as pinned_count,
SUM(CASE WHEN requested_model = model_id THEN 1 ELSE 0 END) as pin_honored_count
FROM requests WHERE created_at >= ?
`).get(since) as { pinned_count: number | null; pin_honored_count: number | null };
// Latency percentiles, time-to-first-token, and the chat/embedding split all
// live on the raw rows (the hourly aggregate keeps neither latency nor a
// per-type breakdown). When the raw window is empty (older than the prune
// horizon) we report null, not 0, so the UI can render a placeholder instead
// of a misleading zero. Percentiles use nearest-rank via ORDER BY/OFFSET.
// Only rows that actually recorded a latency participate in the percentile.
// The IS NOT NULL guard must be on BOTH the offset-denominator count and the
// ordered selection so they range over the same set: a NULL sorts first under
// ORDER BY latency_ms ASC, so if it were counted but not filtered the offset
// math would shift and a NULL could be selected (rendered as 0).
const rawCount = (db.prepare(
`SELECT COUNT(*) as c FROM requests WHERE created_at >= ? AND latency_ms IS NOT NULL`
).get(since) as { c: number }).c;
const percentileAt = (fraction: number): number | null => {
if (rawCount === 0) return null;
const offset = Math.floor((rawCount - 1) * fraction);
const row = db.prepare(`
SELECT latency_ms FROM requests
WHERE created_at >= ? AND latency_ms IS NOT NULL
ORDER BY latency_ms ASC
LIMIT 1 OFFSET ?
`).get(since, offset) as { latency_ms: number } | undefined;
return row ? Math.round(row.latency_ms) : null;
};
const p50LatencyMs = percentileAt(0.5);
const p95LatencyMs = percentileAt(0.95);
const ttfbRow = db.prepare(`
SELECT AVG(ttfb_ms) as avg_ttfb_ms FROM requests
WHERE created_at >= ? AND ttfb_ms IS NOT NULL
`).get(since) as { avg_ttfb_ms: number | null } | undefined;
const avgTtfbMs = ttfbRow?.avg_ttfb_ms != null ? Math.round(ttfbRow.avg_ttfb_ms) : null;
const typeRows = db.prepare(`
SELECT request_type, COUNT(*) as count FROM requests
WHERE created_at >= ?
GROUP BY request_type
`).all(since) as Array<{ request_type: string; count: number }>;
const requestTypeCounts = { chat: 0, embedding: 0 };
for (const row of typeRows) {
if (row.request_type === 'embedding') requestTypeCounts.embedding = row.count;
else if (row.request_type === 'chat') requestTypeCounts.chat = row.count;
}
const lifetimeFirst = readLifetimeSettings();
res.json({
totalRequests,
successRate: Math.round(successRate * 10) / 10,
totalInputTokens: aggregate.total_input_tokens ?? 0,
totalOutputTokens: aggregate.total_output_tokens ?? 0,
avgLatencyMs: Math.round(latencyRow?.avg_latency_ms ?? 0),
// Latency spread (raw rows): p50 typical, p95 tail. Null when the raw
// window is empty.
p50LatencyMs,
p95LatencyMs,
// Average streaming time-to-first-token over rows that recorded it; null
// when none did (non-streaming traffic or pruned window).
avgTtfbMs,
// Chat vs embedding request split for the selected window.
requestTypeCounts,
estimatedCostSavings: Math.round((savings.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: pinRow.pinned_count ?? 0,
pinHonoredRequests: pinRow.pin_honored_count ?? 0,
// First-ever request timestamp (lifetime, never pruned). Falls back to
// the oldest hour in the current window when lifetime is not yet seeded.
firstRequestAt: lifetimeFirst ?? aggregate.first_request_at ?? null,
// Lifetime total since install — useful when the user wants to see "all
// time" alongside the selected range window. Sourced from settings so it
// survives the raw-row prune entirely.
lifetimeTotalRequests: Number((db.prepare(`SELECT value FROM settings WHERE key='total_requests'`).get() as { value?: string } | undefined)?.value ?? 0) || 0,
});
});
// Stats grouped by model.
//
// The grouping key is (platform, endpoint, model_id), not (platform, model_id):
// the same model id served by two different custom relays is two different
// things — different latency, different failure modes — and merging them into
// one row labelled "custom" is the #889 collision in its most misleading form,
// because the merged row's numbers describe neither endpoint.
//
// The models join is endpoint-scoped for the same reason. `models` is unique on
// (platform, model_id, endpoint_scope) since #651, so joining on
// (platform, model_id) alone matches ONE row per relay that registered the
// model and multiplies every request row by that count. Adding endpoint_scope
// to the ON clause picks the row belonging to the endpoint that actually served
// the request — the only one whose display name and pricing apply.
analyticsRouter.get('/by-model', (req: Request, res: Response) => {
const range = (req.query.range as string) ?? '7d';
const since = getSinceTimestamp(range);
const db = getDb();
const rows = db.prepare(`
SELECT
r.platform,
${ENDPOINT_ID_SQL} as base_url,
r.model_id,
m.display_name,
COUNT(*) as requests,
-- Rate over success+error only: 'canceled' (#752) is neither.
SUM(CASE WHEN r.status = 'success' THEN 1 ELSE 0 END) * 100.0 / NULLIF(SUM(CASE WHEN r.status <> 'canceled' THEN 1 ELSE 0 END), 0) 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 api_keys k ON k.id = r.key_id
LEFT JOIN models m
ON m.platform = r.platform AND m.model_id = r.model_id
AND m.endpoint_scope = ${ENDPOINT_ID_SQL}
WHERE r.created_at >= ?
GROUP BY r.platform, ${ENDPOINT_ID_SQL}, r.model_id
ORDER BY requests DESC
`).all(FALLBACK_INPUT_PER_M, FALLBACK_OUTPUT_PER_M, since) as any[];
res.json(rows.map(r => ({
platform: r.platform,
// Same row identity as /by-platform, so a model row and a provider row for
// one endpoint carry the same id and the same operator-readable name.
providerId: providerIdFor(r.platform, r.base_url || null),
endpoint: providerDisplayName(r.platform, r.base_url || null),
modelId: r.model_id,
displayName: r.display_name ?? r.model_id,
requests: r.requests,
// success_rate is NULL when every row in the group was canceled.
successRate: Math.round((r.success_rate ?? 0) * 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.
//
// Custom endpoints all share the platform id 'custom' (services/custom-
// endpoint.ts), so grouping by `platform` alone would collapse every custom
// relay into one row and the operator could not tell which endpoint did what
// (#889). We therefore also group by the serving key's base_url — the canonical
// endpoint identity (custom-endpoint.ts pools credentials by base_url, and the
// router treats every key sharing a base_url as one endpoint). Non-custom keys
// carry no base_url, so COALESCE(base_url,'') keeps each of them in a single
// per-platform group exactly as before.
analyticsRouter.get('/by-platform', (req: Request, res: Response) => {
const range = (req.query.range as string) ?? '7d';
const since = getSinceTimestamp(range);
const db = getDb();
const rows = db.prepare(`
SELECT
r.platform,
${ENDPOINT_ID_SQL} as base_url,
COUNT(*) as requests,
COUNT(r.latency_ms) as latency_count,
SUM(CASE WHEN r.status = 'success' THEN 1 ELSE 0 END) * 100.0 / NULLIF(SUM(CASE WHEN r.status <> 'canceled' THEN 1 ELSE 0 END), 0) as success_rate,
AVG(r.latency_ms) as avg_latency_ms,
AVG(r.ttfb_ms) as avg_ttfb_ms,
SUM(CASE WHEN r.status = 'error' THEN 1 ELSE 0 END) as error_count,
AVG(CASE WHEN r.output_tokens > 0 AND r.latency_ms > 0
THEN r.output_tokens / (r.latency_ms / 1000.0) ELSE NULL END) as avg_tokens_per_second,
SUM(r.input_tokens) as total_input_tokens,
SUM(r.output_tokens) as total_output_tokens
FROM requests r
LEFT JOIN api_keys k ON k.id = r.key_id
WHERE r.created_at >= ?
GROUP BY r.platform, ${ENDPOINT_ID_SQL}
ORDER BY requests DESC
`).all(since) as any[];
// P95 latency is a per-group percentile; SQLite has no native percentile
// aggregate, so we take the nearest-rank value per group with a small
// ORDER BY/OFFSET query. The group count is tiny (one row per provider /
// custom endpoint), so the extra round-trips are negligible and keep the SQL
// readable. The WHERE must match the grouping exactly — platform AND the
// endpoint's base_url — or a custom endpoint's p95 would bleed in latency
// from every other custom endpoint.
const p95Stmt = db.prepare(`
SELECT r.latency_ms FROM requests r
LEFT JOIN api_keys k ON k.id = r.key_id
WHERE r.created_at >= ? AND r.platform = ? AND ${ENDPOINT_ID_SQL} = ? AND r.latency_ms IS NOT NULL
ORDER BY r.latency_ms ASC
LIMIT 1 OFFSET ?
`);
res.json(rows.map(r => {
// Offset math and the ordered selection both range over the non-null
// latency rows (latency_count), so a NULL can neither be counted into the
// denominator nor selected as the p95 value.
const latencyCount = r.latency_count ?? 0;
const baseUrl: string | null = r.base_url || null;
const p95Row = latencyCount > 0
? (p95Stmt.get(since, r.platform, r.base_url, Math.floor((latencyCount - 1) * 0.95)) as { latency_ms: number } | undefined)
: undefined;
return {
platform: r.platform,
// Stable, unique id for this row: the platform slug for catalog providers,
// 'custom:<base_url>' for custom endpoints (falls back to 'custom' when
// the key is gone). The client uses this as the chart key and the
// recent-calls filter value.
providerId: providerIdFor(r.platform, baseUrl),
// The short identifier the operator actually reads: the endpoint host for
// custom rows, the platform slug otherwise.
endpoint: providerDisplayName(r.platform, baseUrl),
requests: r.requests,
successRate: Math.round((r.success_rate ?? 0) * 10) / 10,
avgLatencyMs: Math.round(r.avg_latency_ms),
p95LatencyMs: p95Row ? Math.round(p95Row.latency_ms) : null,
avgTtfbMs: r.avg_ttfb_ms != null ? Math.round(r.avg_ttfb_ms) : null,
errorCount: r.error_count ?? 0,
avgTokensPerSecond: r.avg_tokens_per_second != null
? Math.round(r.avg_tokens_per_second * 10) / 10
: null,
totalInputTokens: r.total_input_tokens ?? 0,
totalOutputTokens: r.total_output_tokens ?? 0,
};
}));
});
analyticsRouter.get('/by-client', (req: Request, res: Response) => {
const range = (req.query.range as string) ?? '7d';
const since = getSinceTimestamp(range);
const rows = getDb().prepare(`
SELECT
COALESCE(client_agent, 'unknown') AS client_agent,
COUNT(*) AS requests,
SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) * 100.0 / NULLIF(SUM(CASE WHEN status <> 'canceled' THEN 1 ELSE 0 END), 0) AS success_rate,
AVG(latency_ms) AS avg_latency_ms,
SUM(input_tokens) AS total_input_tokens,
SUM(output_tokens) AS total_output_tokens,
MAX(strftime('%Y-%m-%dT%H:%M:%SZ', created_at)) AS last_seen_at
FROM requests
WHERE created_at >= ?
GROUP BY client_agent
ORDER BY requests DESC
`).all(since) as any[];
res.json(rows.map(row => ({
clientAgent: row.client_agent,
requests: row.requests,
successRate: Math.round((row.success_rate ?? 0) * 10) / 10,
avgLatencyMs: Math.round(row.avg_latency_ms ?? 0),
totalInputTokens: row.total_input_tokens ?? 0,
totalOutputTokens: row.total_output_tokens ?? 0,
lastSeenAt: row.last_seen_at,
})));
});
// Stats grouped by API key. Raw-row scoped (the hourly aggregate has no key
// dimension), LEFT JOINed to api_keys so a request whose key was later deleted
// still shows up with a null label — the keyId is always returned.
analyticsRouter.get('/by-key', (req: Request, res: Response) => {
const range = (req.query.range as string) ?? '7d';
const since = getSinceTimestamp(range);
const db = getDb();
const rows = db.prepare(`
SELECT
r.key_id as key_id,
k.label as label,
k.platform as platform,
COUNT(*) as requests,
SUM(CASE WHEN r.status = 'success' THEN 1 ELSE 0 END) * 100.0 / NULLIF(SUM(CASE WHEN r.status <> 'canceled' THEN 1 ELSE 0 END), 0) 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
FROM requests r
LEFT JOIN api_keys k ON k.id = r.key_id
WHERE r.key_id IS NOT NULL AND r.created_at >= ?
GROUP BY r.key_id
ORDER BY requests DESC
LIMIT 50
`).all(since) as any[];
res.json(rows.map(r => ({
keyId: r.key_id,
// Null when the key row was deleted, or the empty string when the key
// exists but was never labelled; the client falls back to "Key #<id>".
label: r.label ?? null,
platform: r.platform ?? null,
requests: r.requests,
successRate: Math.round((r.success_rate ?? 0) * 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: Request, res: Response) => {
const range = (req.query.range as string) ?? '7d';
const interval = (req.query.interval as string) ?? (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';
// tzOffset: viewer's local offset from UTC in minutes (480 = UTC+8), sent by
// the browser so hour/day bucket boundaries follow the viewer's wall clock
// instead of UTC. Whitelisted to a sane integer range; bound as a parameter,
// never interpolated into SQL.
const rawOffset = Number(req.query.tzOffset);
const tzOffset = Number.isInteger(rawOffset) && rawOffset >= -720 && rawOffset <= 840 ? rawOffset : 0;
// The single current offset applies to the whole range (SQLite has no tz
// database), so buckets on the far side of a DST transition sit 1h off.
const tzModifier = `${tzOffset >= 0 ? '+' : '-'}${Math.abs(tzOffset)} minutes`;
// Read from request_hourly (hour-bucketed) for both 'hour' and 'day'
// intervals. Day buckets are rolled up via strftime on the hour column,
// which keeps the timeline accurate past the raw-row prune window.
const rows = db.prepare(`
SELECT
strftime(?, hour, ?) as timestamp,
SUM(total_requests) as requests,
SUM(success_count) as success_count,
SUM(error_count) as failure_count,
SUM(input_tokens) as input_tokens,
SUM(output_tokens) as output_tokens
FROM request_hourly
WHERE hour >= ?
GROUP BY timestamp
ORDER BY timestamp ASC
`).all(dateFormat, tzModifier, since) as any[];
res.json(rows.map(r => ({
timestamp: r.timestamp,
requests: r.requests,
successCount: r.success_count,
failureCount: r.failure_count,
inputTokens: r.input_tokens ?? 0,
outputTokens: r.output_tokens ?? 0,
})));
});
// Error distribution (grouped by error type and platform)
analyticsRouter.get('/error-distribution', (req: Request, res: Response) => {
const range = (req.query.range as string) ?? '7d';
const since = getSinceTimestamp(range);
const db = getDb();
// Group errors by category (extract the key part of the error message).
// LOWER() first: LIKE is case-sensitive in SQLite, and the error strings we
// persist are dominated by capitalized upstream messages ("Invalid API key",
// "Too Many Requests", "Timeout"), which all landed in 'Other' before. The
// old '%invalid.*key%' pattern was regex syntax pasted into LIKE, where '.'
// and '*' are literal characters, so it matched nothing real.
const rows = db.prepare(`
SELECT
platform,
model_id,
CASE
WHEN lower(error) LIKE '%429%' OR lower(error) LIKE '%rate limit%' OR lower(error) LIKE '%too many%' OR lower(error) LIKE '%quota%' THEN 'Rate Limited (429)'
WHEN lower(error) LIKE '%401%' OR lower(error) LIKE '%unauthorized%' OR lower(error) LIKE '%invalid api key%' OR lower(error) LIKE '%invalid_api_key%' OR lower(error) LIKE '%incorrect api key%' THEN 'Auth Error (401)'
WHEN lower(error) LIKE '%403%' OR lower(error) LIKE '%forbidden%' THEN 'Forbidden (403)'
WHEN lower(error) LIKE '%404%' OR lower(error) LIKE '%not found%' THEN 'Not Found (404)'
WHEN lower(error) LIKE '%timeout%' OR lower(error) LIKE '%timed out%' OR lower(error) LIKE '%etimedout%' OR lower(error) LIKE '%econnrefused%' THEN 'Timeout/Connection'
WHEN lower(error) LIKE '%500%' OR lower(error) LIKE '%internal server%' THEN 'Server Error (500)'
WHEN lower(error) LIKE '%503%' OR lower(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) as any[];
// Also get totals by category
const byCategory = db.prepare(`
SELECT
CASE
WHEN lower(error) LIKE '%429%' OR lower(error) LIKE '%rate limit%' OR lower(error) LIKE '%too many%' OR lower(error) LIKE '%quota%' THEN 'Rate Limited (429)'
WHEN lower(error) LIKE '%401%' OR lower(error) LIKE '%unauthorized%' OR lower(error) LIKE '%invalid api key%' OR lower(error) LIKE '%invalid_api_key%' OR lower(error) LIKE '%incorrect api key%' THEN 'Auth Error (401)'
WHEN lower(error) LIKE '%403%' OR lower(error) LIKE '%forbidden%' THEN 'Forbidden (403)'
WHEN lower(error) LIKE '%404%' OR lower(error) LIKE '%not found%' THEN 'Not Found (404)'
WHEN lower(error) LIKE '%timeout%' OR lower(error) LIKE '%timed out%' OR lower(error) LIKE '%etimedout%' OR lower(error) LIKE '%econnrefused%' THEN 'Timeout/Connection'
WHEN lower(error) LIKE '%500%' OR lower(error) LIKE '%internal server%' THEN 'Server Error (500)'
WHEN lower(error) LIKE '%503%' OR lower(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) as any[];
// Errors by provider. Endpoint-scoped like /by-platform: one bar per custom
// relay, not one bar pooling every relay's failures under "custom" (#889) —
// a bar the operator cannot act on, because it never says which endpoint is
// the one failing.
const byPlatformRows = db.prepare(`
SELECT r.platform, ${ENDPOINT_ID_SQL} as base_url, COUNT(*) as count
FROM requests r
LEFT JOIN api_keys k ON k.id = r.key_id
WHERE r.status = 'error' AND r.created_at >= ?
GROUP BY r.platform, ${ENDPOINT_ID_SQL}
ORDER BY count DESC
`).all(since) as any[];
const byPlatform = byPlatformRows.map(r => {
const baseUrl: string | null = r.base_url || null;
return {
// `platform` stays the raw slug: it is what the chart colors by.
platform: r.platform,
providerId: providerIdFor(r.platform, baseUrl),
endpoint: providerDisplayName(r.platform, baseUrl),
count: r.count,
};
});
res.json({
byCategory,
byPlatform,
detailed: rows,
});
});
// Recent errors
analyticsRouter.get('/errors', (req: Request, res: Response) => {
const range = (req.query.range as string) ?? '7d';
const since = getSinceTimestamp(range);
const db = getDb();
// Joined to the serving key so each error names the endpoint it came from.
// Without it every custom relay's failures read as a bare "custom" in the
// panel (#889) and the operator has to guess which one broke.
const rows = db.prepare(`
SELECT r.id, r.platform, ${ENDPOINT_ID_SQL} as base_url, r.model_id, r.error,
r.latency_ms, r.created_at
FROM requests r
LEFT JOIN api_keys k ON k.id = r.key_id
WHERE r.status = 'error' AND r.created_at >= ?
ORDER BY r.created_at DESC
LIMIT 50
`).all(since) as any[];
res.json(rows.map(r => {
const baseUrl: string | null = r.base_url || null;
return {
id: r.id,
platform: r.platform,
providerId: providerIdFor(r.platform, baseUrl),
endpoint: providerDisplayName(r.platform, baseUrl),
modelId: r.model_id,
error: r.error,
latencyMs: r.latency_ms,
createdAt: r.created_at,
};
}));
});
// Recent calls — one row per proxied request, newest first, with the caller's
// IP and User-Agent (all local clients share the unified key, so client_ip is
// the only per-caller discriminator; UA disambiguates tunneled loopback calls).
// Reads the raw `requests` table, so history is bounded by the retention prune.
analyticsRouter.get('/requests', (req: Request, res: Response) => {
const range = (req.query.range as string) ?? '7d';
const since = getSinceTimestamp(range);
const limit = Math.min(Math.max(parseInt(req.query.limit as string, 10) || 100, 1), 500);
const offset = Math.max(parseInt(req.query.offset as string, 10) || 0, 0);
// First value only, like routes/logs.ts: Express hands back an ARRAY for a
// repeated query param, and the provider shape check below dies on
// `provider.startsWith(...)` — arrays have no such method — turning a client
// bug into a 500 on the dashboard's recent-calls panel.
const status = firstParam(req.query.status);
if (status !== undefined && status !== 'success' && status !== 'error' && status !== 'canceled') {
res.status(400).json({ error: "invalid status filter (expected 'success', 'error' or 'canceled')" });
return;
}
// Provider filter. The `provider` param carries the stable row id returned by
// /by-platform: a platform slug ('groq') or 'custom:<base_url>' for a custom
// endpoint (#889 — every custom relay shares the 'custom' platform id, so the
// endpoint's base_url is what actually selects one). The legacy `platform`
// param still works for old clients. Both are bound parameters, never
// interpolated, so the only validation needed is shape.
const provider = firstParam(req.query.provider);
const platform = firstParam(req.query.platform);
let providerFilterSql = '';
const providerFilterParams: string[] = [];
if (provider !== undefined) {
if (provider.length > 256 || /[\r\n]/.test(provider)) {
res.status(400).json({ error: 'invalid provider filter' });
return;
}
if (provider === 'custom') {
// The BARE 'custom' id is the orphan bucket, not "all custom traffic":
// /by-platform emits it only for rows whose endpoint is unknown (the key
// was deleted, or never carried a base_url). Falling through to the slug
// branch below would filter on `platform = 'custom'` and return every
// relay's traffic — a list that contradicts the very row the user
// clicked, which counted the orphans alone. Match what that row counted.
providerFilterSql = ` AND r.platform = 'custom' AND ${ENDPOINT_ID_SQL} = ''`;
} else if (provider.startsWith('custom:')) {
// Select one custom endpoint by its base_url. Normalized on the way in
// for the same reason ENDPOINT_ID_SQL normalizes on the way out.
providerFilterSql = ` AND r.platform = 'custom' AND ${ENDPOINT_ID_SQL} = ?`;
providerFilterParams.push(normalizeBaseUrl(provider.slice('custom:'.length)));
} else if (/^[A-Za-z0-9_-]{1,64}$/.test(provider)) {
providerFilterSql = ' AND r.platform = ?';
providerFilterParams.push(provider);
} else {
res.status(400).json({ error: 'invalid provider filter' });
return;
}
} else if (platform !== undefined) {
// The legacy param keeps its pre-#889 meaning: `platform=custom` is every
// custom relay's traffic. Only the `provider` ids are endpoint-scoped.
// Platform ids are short slugs ('groq', 'pt-custom_1'); anything else is a
// client bug, not a filter.
if (!/^[A-Za-z0-9_-]{1,64}$/.test(platform)) {
res.status(400).json({ error: 'invalid platform filter' });
return;
}
providerFilterSql = ' AND r.platform = ?';
providerFilterParams.push(platform);
}
const db = getDb();
const filterSql =
(status !== undefined ? ' AND r.status = ?' : '') +
providerFilterSql;
const filterParams = [
...(status !== undefined ? [status] : []),
...providerFilterParams,
];
const total = (db.prepare(
`SELECT COUNT(*) as c FROM requests r
LEFT JOIN api_keys k ON k.id = r.key_id
WHERE r.created_at >= ?${filterSql}`
).get(since, ...filterParams) as { c: number }).c;
const rows = db.prepare(`
SELECT r.id, r.platform, r.model_id, r.requested_model, r.request_type, r.status,
r.input_tokens, r.output_tokens, r.latency_ms, r.error,
r.client_ip, r.client_user_agent, r.client_agent,
strftime('%Y-%m-%dT%H:%M:%SZ', r.created_at) as created_at_iso,
(SELECT COUNT(*) FROM request_attempts a WHERE a.request_id = r.id) as attempt_count,
k.label as key_label
FROM requests r
LEFT JOIN api_keys k ON k.id = r.key_id
WHERE r.created_at >= ?${filterSql}
ORDER BY r.created_at DESC, r.id DESC
LIMIT ? OFFSET ?
`).all(since, ...filterParams, limit, offset) as any[];
res.json({
total,
rows: rows.map(r => ({
id: r.id,
platform: r.platform,
modelId: r.model_id,
requestedModel: r.requested_model,
requestType: r.request_type,
status: r.status,
inputTokens: r.input_tokens,
outputTokens: r.output_tokens,
latencyMs: r.latency_ms,
error: r.error,
clientIp: r.client_ip,
clientUserAgent: r.client_user_agent,
clientAgent: r.client_agent,
createdAt: r.created_at_iso,
// #785: custom endpoints all share the generic 'custom' platform id, so
// the user's key label ("Ollama box") rides along to name the real
// provider in the recent-calls list. Null when the key was deleted or
// never labelled.
keyLabel: r.key_label ?? null,
// Failover-ladder length for this row. Attempts hang off the TERMINAL
// row of a proxied request; mid-ladder failure rows report 0.
attemptCount: r.attempt_count,
})),
});
});
// Per-request detail: the row plus its durable failover ladder — one entry per
// dispatched attempt (including the successful final one), ordinal-ordered,
// with the failure class and timing of each hop. keyOrdinal is the per-request
// key ordinal (key1, key2…), same anonymization as X-Fallback-Trail — internal
// key ids are never exposed. Attempts are keyed to the ladder's terminal row
// (the success row, or the last failure row when it exhausted), so mid-ladder
// error rows legitimately return an empty attempts array.
analyticsRouter.get('/requests/:id', (req: Request, res: Response) => {
const id = Number.parseInt(req.params.id as string, 10);
if (!Number.isInteger(id) || id <= 0) {
res.status(400).json({ error: 'invalid request id' });
return;
}
const db = getDb();
const r = db.prepare(`
SELECT id, platform, model_id, requested_model, served_model, request_type, status,
input_tokens, output_tokens, latency_ms, ttfb_ms, error,
client_ip, client_user_agent, client_agent,
strftime('%Y-%m-%dT%H:%M:%SZ', created_at) as created_at_iso
FROM requests
WHERE id = ?
`).get(id) as any;
if (!r) {
res.status(404).json({ error: 'request not found' });
return;
}
const attempts = db.prepare(`
SELECT ordinal, platform, model_id, key_ordinal, key_label, outcome, start_offset_ms, duration_ms, error_summary
FROM request_attempts
WHERE request_id = ?
ORDER BY ordinal ASC
`).all(id) as any[];
res.json({
id: r.id,
platform: r.platform,
modelId: r.model_id,
requestedModel: r.requested_model,
// Upstream-reported model when it genuinely differed from the routed
// model_id (#534 served-model drift guard); null in the healthy case.
servedModel: r.served_model,
requestType: r.request_type,
status: r.status,
inputTokens: r.input_tokens,
outputTokens: r.output_tokens,
latencyMs: r.latency_ms,
ttfbMs: r.ttfb_ms,
error: r.error,
clientIp: r.client_ip,
clientUserAgent: r.client_user_agent,
clientAgent: r.client_agent,
createdAt: r.created_at_iso,
attempts: attempts.map(a => ({
ordinal: a.ordinal,
platform: a.platform,
modelId: a.model_id,
keyOrdinal: a.key_ordinal,
keyLabel: a.key_label ?? null,
outcome: a.outcome,
startOffsetMs: a.start_offset_ms,
durationMs: a.duration_ms,
// Short, redacted per-hop error text (null for successful hops and for
// rows written before the error_summary migration).
errorSummary: a.error_summary ?? null,
})),
});
});