import type { Database } from "bun:sqlite";import type { UsageStats } from "@local-studio/contracts/usage";import type { Effect } from "effect";import { openInitializedDatabase, makeDatabaseCloser, repositoryEffect, type RepositoryError, toFiniteNumber, toNullableNumber,} from "./sqlite"; export interface InferenceRequestRecord { model: string; source?: string | null; session_id?: string | null; provider?: string | null; prompt_tokens: number; completion_tokens: number; reasoning_tokens?: number; cache_read_tokens?: number; cache_write_tokens?: number; ttft_ms?: number | null; duration_ms?: number | null; status?: number; streamed?: boolean;} export type UsageAggregate = Omit<UsageStats, "controller">; interface NumberRow { [key: string]: number;} const buildModelFilter = ( knownModels?: ReadonlySet<string>,): { clause: string; params: string[] } => { if (!knownModels || knownModels.size === 0) return { clause: "", params: [] }; const params = [...knownModels]; const placeholders = params.map(() => "?").join(","); return { clause: ` AND model IN (${placeholders})`, params };}; export class InferenceRequestStore { private readonly db: Database; private readonly closeDatabase: () => Effect.Effect<void, RepositoryError>; public constructor(dbPath: string) { this.db = openInitializedDatabase(dbPath, (db) => this.migrate(db)); this.closeDatabase = makeDatabaseCloser(this.db, "inference-requests.close"); } private migrate(db: Database): void { db.run(` CREATE TABLE IF NOT EXISTS inference_requests ( id INTEGER PRIMARY KEY AUTOINCREMENT, created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, model TEXT NOT NULL, source TEXT, session_id TEXT, provider TEXT, prompt_tokens INTEGER NOT NULL DEFAULT 0, completion_tokens INTEGER NOT NULL DEFAULT 0, reasoning_tokens INTEGER NOT NULL DEFAULT 0, cache_read_tokens INTEGER NOT NULL DEFAULT 0, cache_write_tokens INTEGER NOT NULL DEFAULT 0, total_tokens INTEGER NOT NULL DEFAULT 0, ttft_ms INTEGER, duration_ms INTEGER, status INTEGER NOT NULL DEFAULT 200, streamed INTEGER NOT NULL DEFAULT 0 ) `); db.run( `CREATE INDEX IF NOT EXISTS idx_inference_requests_created_at ON inference_requests(created_at)`, ); db.run( `CREATE INDEX IF NOT EXISTS idx_inference_requests_model_created ON inference_requests(model, created_at)`, ); } private recordSync(record: InferenceRequestRecord): void { const promptTokens = Math.max(0, Math.round(record.prompt_tokens)); const completionTokens = Math.max(0, Math.round(record.completion_tokens)); const reasoningTokens = Math.max(0, Math.round(record.reasoning_tokens ?? 0)); const cacheRead = Math.max(0, Math.round(record.cache_read_tokens ?? 0)); const cacheWrite = Math.max(0, Math.round(record.cache_write_tokens ?? 0)); const totalTokens = promptTokens + completionTokens; this.db .query( `INSERT INTO inference_requests ( model, source, session_id, provider, prompt_tokens, completion_tokens, reasoning_tokens, cache_read_tokens, cache_write_tokens, total_tokens, ttft_ms, duration_ms, status, streamed ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`, ) .run( record.model, record.source ?? null, record.session_id ?? null, record.provider ?? null, promptTokens, completionTokens, reasoningTokens, cacheRead, cacheWrite, totalTokens, record.ttft_ms ?? null, record.duration_ms ?? null, record.status ?? 200, record.streamed ? 1 : 0, ); } public record(record: InferenceRequestRecord): Effect.Effect<void, RepositoryError> { return repositoryEffect("inference-requests.record", () => this.recordSync(record)); } public aggregate(knownModels?: ReadonlySet<string>): UsageAggregate | null { const filter = buildModelFilter(knownModels); const params = filter.params; const summary = this.db .query<NumberRow, string[]>( `SELECT COUNT(*) as total_requests, COALESCE(SUM(prompt_tokens), 0) as prompt_tokens, COALESCE(SUM(completion_tokens), 0) as completion_tokens, COALESCE(SUM(reasoning_tokens), 0) as reasoning_tokens, COALESCE(SUM(cache_read_tokens), 0) as cache_read, COALESCE(SUM(cache_write_tokens), 0) as cache_write, COUNT(DISTINCT session_id) as unique_sessions, SUM(CASE WHEN status >= 200 AND status < 300 THEN 1 ELSE 0 END) as ok, AVG(duration_ms) as avg_dur, AVG(ttft_ms) as avg_ttft, SUM(CASE WHEN datetime(created_at) >= datetime('now', '-1 hour') THEN 1 ELSE 0 END) as last_hour, SUM(CASE WHEN datetime(created_at) >= datetime('now', '-24 hours') THEN 1 ELSE 0 END) as last_24h, SUM(CASE WHEN datetime(created_at) >= datetime('now', '-48 hours') AND datetime(created_at) < datetime('now', '-24 hours') THEN 1 ELSE 0 END) as prev_24h, COALESCE(SUM(CASE WHEN datetime(created_at) >= datetime('now', '-24 hours') THEN prompt_tokens + completion_tokens ELSE 0 END), 0) as last_24h_tokens, SUM(CASE WHEN datetime(created_at) >= datetime('now', '-7 days') THEN 1 ELSE 0 END) as this_week_requests, SUM(CASE WHEN datetime(created_at) >= datetime('now', '-7 days') THEN prompt_tokens + completion_tokens ELSE 0 END) as this_week_tokens, SUM(CASE WHEN datetime(created_at) >= datetime('now', '-7 days') AND status >= 200 AND status < 300 THEN 1 ELSE 0 END) as this_week_ok, SUM(CASE WHEN datetime(created_at) >= datetime('now', '-14 days') AND datetime(created_at) < datetime('now', '-7 days') THEN 1 ELSE 0 END) as last_week_requests, SUM(CASE WHEN datetime(created_at) >= datetime('now', '-14 days') AND datetime(created_at) < datetime('now', '-7 days') THEN prompt_tokens + completion_tokens ELSE 0 END) as last_week_tokens, SUM(CASE WHEN datetime(created_at) >= datetime('now', '-14 days') AND datetime(created_at) < datetime('now', '-7 days') AND status >= 200 AND status < 300 THEN 1 ELSE 0 END) as last_week_ok FROM inference_requests WHERE 1=1${filter.clause}`, ) .get(...params) as NumberRow | null; const totalRequests = toFiniteNumber(summary?.["total_requests"]); if (totalRequests === 0) return null; const promptTokens = toFiniteNumber(summary?.["prompt_tokens"]); const completionTokens = toFiniteNumber(summary?.["completion_tokens"]); const totalTokens = promptTokens + completionTokens; const cacheHits = toFiniteNumber(summary?.["cache_read"]); const cacheMisses = toFiniteNumber(summary?.["cache_write"]); const successful = toFiniteNumber(summary?.["ok"]); const byModel = this.db .query<Record<string, unknown>, string[]>( `SELECT model, COUNT(*) as requests, SUM(CASE WHEN status >= 200 AND status < 300 THEN 1 ELSE 0 END) as successful, COALESCE(SUM(prompt_tokens), 0) as prompt_tokens, COALESCE(SUM(completion_tokens), 0) as completion_tokens, COALESCE(SUM(prompt_tokens), 0) + COALESCE(SUM(completion_tokens), 0) as total_tokens, AVG(duration_ms) as avg_latency_ms, AVG(ttft_ms) as avg_ttft_ms FROM inference_requests WHERE 1=1${filter.clause} GROUP BY model ORDER BY total_tokens DESC LIMIT 25`, ) .all(...params) as Array<Record<string, unknown>>; const daily = this.db .query<Record<string, unknown>, string[]>( `SELECT DATE(created_at) as date, COUNT(*) as requests, SUM(CASE WHEN status >= 200 AND status < 300 THEN 1 ELSE 0 END) as successful, COALESCE(SUM(prompt_tokens), 0) as prompt_tokens, COALESCE(SUM(completion_tokens), 0) as completion_tokens, COALESCE(SUM(prompt_tokens), 0) + COALESCE(SUM(completion_tokens), 0) as total_tokens, AVG(duration_ms) as avg_latency_ms FROM inference_requests WHERE DATE(created_at) >= DATE('now', '-366 days')${filter.clause} GROUP BY DATE(created_at) ORDER BY date DESC LIMIT 400`, ) .all(...params) as Array<Record<string, unknown>>; const dailyByModel = this.db .query<Record<string, unknown>, string[]>( `SELECT DATE(created_at) as date, model, COUNT(*) as requests, SUM(CASE WHEN status >= 200 AND status < 300 THEN 1 ELSE 0 END) as successful, COALESCE(SUM(prompt_tokens), 0) as prompt_tokens, COALESCE(SUM(completion_tokens), 0) as completion_tokens, COALESCE(SUM(prompt_tokens), 0) + COALESCE(SUM(completion_tokens), 0) as total_tokens FROM inference_requests WHERE DATE(created_at) >= DATE('now', '-366 days')${filter.clause} GROUP BY DATE(created_at), model ORDER BY date DESC LIMIT 10000`, ) .all(...params) as Array<Record<string, unknown>>; const hourly = this.db .query<Record<string, unknown>, string[]>( `SELECT CAST(strftime('%H', created_at) AS INTEGER) as hour, COUNT(*) as requests, SUM(CASE WHEN status >= 200 AND status < 300 THEN 1 ELSE 0 END) as successful, COALESCE(SUM(prompt_tokens + completion_tokens), 0) as tokens FROM inference_requests WHERE 1=1${filter.clause} GROUP BY strftime('%H', created_at) ORDER BY hour`, ) .all(...params) as Array<Record<string, unknown>>; const peakDays = this.db .query<Record<string, unknown>, string[]>( `SELECT DATE(created_at) as date, COUNT(*) as requests, COALESCE(SUM(prompt_tokens + completion_tokens), 0) as tokens FROM inference_requests WHERE 1=1${filter.clause} GROUP BY DATE(created_at) ORDER BY requests DESC LIMIT 5`, ) .all(...params) as Array<Record<string, unknown>>; const peakHours = this.db .query<Record<string, unknown>, string[]>( `SELECT CAST(strftime('%H', created_at) AS INTEGER) as hour, COUNT(*) as requests FROM inference_requests WHERE DATE(created_at) >= DATE('now', '-7 days')${filter.clause} GROUP BY strftime('%H', created_at) ORDER BY requests DESC LIMIT 5`, ) .all(...params) as Array<Record<string, unknown>>; const calcChangePct = (current: number, previous: number): number | null => { if (previous === 0) return current === 0 ? 0 : null; return ((current - previous) / previous) * 100; }; return { totals: { total_tokens: totalTokens, prompt_tokens: promptTokens, completion_tokens: completionTokens, total_requests: totalRequests, successful_requests: successful, failed_requests: totalRequests - successful, success_rate: totalRequests ? (successful / totalRequests) * 100 : 0, unique_sessions: toFiniteNumber(summary?.["unique_sessions"]), unique_users: 0, }, latency: { avg_ms: toNullableNumber(summary?.["avg_dur"]), p50_ms: null, p95_ms: null, p99_ms: null, min_ms: null, max_ms: null, }, ttft: { avg_ms: toNullableNumber(summary?.["avg_ttft"]), p50_ms: null, p95_ms: null, p99_ms: null, }, tokens_per_request: { avg: totalRequests ? Math.round(totalTokens / totalRequests) : 0, avg_prompt: totalRequests ? Math.round(promptTokens / totalRequests) : 0, avg_completion: totalRequests ? Math.round(completionTokens / totalRequests) : 0, max: byModel.reduce( (max, row) => Math.max( max, toFiniteNumber(row["requests"]) ? Math.round(toFiniteNumber(row["total_tokens"]) / toFiniteNumber(row["requests"])) : 0, ), 0, ), p50: 0, p95: 0, }, cache: { hits: cacheHits, misses: cacheMisses, hit_tokens: cacheHits, miss_tokens: cacheMisses, hit_rate: cacheHits + cacheMisses > 0 ? (cacheHits / (cacheHits + cacheMisses)) * 100 : 0, }, week_over_week: { this_week: { requests: toFiniteNumber(summary?.["this_week_requests"]), tokens: toFiniteNumber(summary?.["this_week_tokens"]), successful: toFiniteNumber(summary?.["this_week_ok"]), }, last_week: { requests: toFiniteNumber(summary?.["last_week_requests"]), tokens: toFiniteNumber(summary?.["last_week_tokens"]), successful: toFiniteNumber(summary?.["last_week_ok"]), }, change_pct: { requests: calcChangePct( toFiniteNumber(summary?.["this_week_requests"]), toFiniteNumber(summary?.["last_week_requests"]), ), tokens: calcChangePct( toFiniteNumber(summary?.["this_week_tokens"]), toFiniteNumber(summary?.["last_week_tokens"]), ), }, }, recent_activity: { last_hour_requests: toFiniteNumber(summary?.["last_hour"]), last_24h_requests: toFiniteNumber(summary?.["last_24h"]), prev_24h_requests: toFiniteNumber(summary?.["prev_24h"]), last_24h_tokens: toFiniteNumber(summary?.["last_24h_tokens"]), change_24h_pct: calcChangePct( toFiniteNumber(summary?.["last_24h"]), toFiniteNumber(summary?.["prev_24h"]), ), }, peak_days: peakDays.map((row) => ({ date: String(row["date"] ?? ""), requests: toFiniteNumber(row["requests"]), tokens: toFiniteNumber(row["tokens"]), })), peak_hours: peakHours.map((row) => ({ hour: toFiniteNumber(row["hour"]), requests: toFiniteNumber(row["requests"]), })), by_model: byModel.map((row) => { const requests = toFiniteNumber(row["requests"]); const ok = toFiniteNumber(row["successful"]); return { model: String(row["model"] ?? "unknown"), requests, successful: ok, success_rate: requests ? (ok / requests) * 100 : 0, total_tokens: toFiniteNumber(row["total_tokens"]), prompt_tokens: toFiniteNumber(row["prompt_tokens"]), completion_tokens: toFiniteNumber(row["completion_tokens"]), avg_tokens: requests ? Math.round(toFiniteNumber(row["total_tokens"]) / requests) : 0, avg_latency_ms: toNullableNumber(row["avg_latency_ms"]), p50_latency_ms: null, avg_ttft_ms: toNullableNumber(row["avg_ttft_ms"]), tokens_per_sec: null, prefill_tps: null, generation_tps: null, }; }), daily: daily.map((row) => { const requests = toFiniteNumber(row["requests"]); const ok = toFiniteNumber(row["successful"]); return { date: String(row["date"] ?? ""), requests, successful: ok, success_rate: requests ? (ok / requests) * 100 : 0, total_tokens: toFiniteNumber(row["total_tokens"]), prompt_tokens: toFiniteNumber(row["prompt_tokens"]), completion_tokens: toFiniteNumber(row["completion_tokens"]), avg_latency_ms: toFiniteNumber(row["avg_latency_ms"]), }; }), daily_by_model: dailyByModel.map((row) => { const requests = toFiniteNumber(row["requests"]); const ok = toFiniteNumber(row["successful"]); return { date: String(row["date"] ?? ""), model: String(row["model"] ?? "unknown"), requests, successful: ok, success_rate: requests ? (ok / requests) * 100 : 0, total_tokens: toFiniteNumber(row["total_tokens"]), prompt_tokens: toFiniteNumber(row["prompt_tokens"]), completion_tokens: toFiniteNumber(row["completion_tokens"]), }; }), hourly_pattern: hourly.map((row) => ({ hour: toFiniteNumber(row["hour"]), requests: toFiniteNumber(row["requests"]), successful: toFiniteNumber(row["successful"]), tokens: toFiniteNumber(row["tokens"]), })), }; } public aggregateEffect( knownModels?: ReadonlySet<string>, ): Effect.Effect<UsageAggregate | null, RepositoryError> { return repositoryEffect("inference-requests.aggregate", () => this.aggregate(knownModels)); } public close(): Effect.Effect<void, RepositoryError> { return this.closeDatabase(); }}
import type { Database } from "bun:sqlite";
import type { UsageStats } from "@local-studio/contracts/usage";
import type { Effect } from "effect";
import {
openInitializedDatabase,
makeDatabaseCloser,
repositoryEffect,
type RepositoryError,
toFiniteNumber,
toNullableNumber,
} from "./sqlite";
export interface InferenceRequestRecord {
model: string;
source?: string | null;
session_id?: string | null;
provider?: string | null;
prompt_tokens: number;
completion_tokens: number;
reasoning_tokens?: number;
cache_read_tokens?: number;
cache_write_tokens?: number;
ttft_ms?: number | null;
duration_ms?: number | null;
status?: number;
streamed?: boolean;
}
export type UsageAggregate = Omit<UsageStats, "controller">;
interface NumberRow {
[key: string]: number;
}
const buildModelFilter = (
knownModels?: ReadonlySet<string>,
): { clause: string; params: string[] } => {
if (!knownModels || knownModels.size === 0) return { clause: "", params: [] };
const params = [...knownModels];
const placeholders = params.map(() => "?").join(",");
return { clause: ` AND model IN (${placeholders})`, params };
};
export class InferenceRequestStore {
private readonly db: Database;
private readonly closeDatabase: () => Effect.Effect<void, RepositoryError>;
public constructor(dbPath: string) {
this.db = openInitializedDatabase(dbPath, (db) => this.migrate(db));
this.closeDatabase = makeDatabaseCloser(this.db, "inference-requests.close");
}
private migrate(db: Database): void {
db.run(`
CREATE TABLE IF NOT EXISTS inference_requests (
id INTEGER PRIMARY KEY AUTOINCREMENT,
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
model TEXT NOT NULL,
source TEXT,
session_id TEXT,
provider TEXT,
prompt_tokens INTEGER NOT NULL DEFAULT 0,
completion_tokens INTEGER NOT NULL DEFAULT 0,
reasoning_tokens INTEGER NOT NULL DEFAULT 0,
cache_read_tokens INTEGER NOT NULL DEFAULT 0,
cache_write_tokens INTEGER NOT NULL DEFAULT 0,
total_tokens INTEGER NOT NULL DEFAULT 0,
ttft_ms INTEGER,
duration_ms INTEGER,
status INTEGER NOT NULL DEFAULT 200,
streamed INTEGER NOT NULL DEFAULT 0
)
`);
db.run(
`CREATE INDEX IF NOT EXISTS idx_inference_requests_created_at ON inference_requests(created_at)`,
);
db.run(
`CREATE INDEX IF NOT EXISTS idx_inference_requests_model_created ON inference_requests(model, created_at)`,
);
}
private recordSync(record: InferenceRequestRecord): void {
const promptTokens = Math.max(0, Math.round(record.prompt_tokens));
const completionTokens = Math.max(0, Math.round(record.completion_tokens));
const reasoningTokens = Math.max(0, Math.round(record.reasoning_tokens ?? 0));
const cacheRead = Math.max(0, Math.round(record.cache_read_tokens ?? 0));
const cacheWrite = Math.max(0, Math.round(record.cache_write_tokens ?? 0));
const totalTokens = promptTokens + completionTokens;
this.db
.query(
`INSERT INTO inference_requests (
model, source, session_id, provider,
prompt_tokens, completion_tokens, reasoning_tokens,
cache_read_tokens, cache_write_tokens, total_tokens,
ttft_ms, duration_ms, status, streamed
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
)
.run(
record.model,
record.source ?? null,
record.session_id ?? null,
record.provider ?? null,
promptTokens,
completionTokens,
reasoningTokens,
cacheRead,
cacheWrite,
totalTokens,
record.ttft_ms ?? null,
record.duration_ms ?? null,
record.status ?? 200,
record.streamed ? 1 : 0,
);
}
public record(record: InferenceRequestRecord): Effect.Effect<void, RepositoryError> {
return repositoryEffect("inference-requests.record", () => this.recordSync(record));
}
public aggregate(knownModels?: ReadonlySet<string>): UsageAggregate | null {
const filter = buildModelFilter(knownModels);
const params = filter.params;
const summary = this.db
.query<NumberRow, string[]>(
`SELECT
COUNT(*) as total_requests,
COALESCE(SUM(prompt_tokens), 0) as prompt_tokens,
COALESCE(SUM(completion_tokens), 0) as completion_tokens,
COALESCE(SUM(reasoning_tokens), 0) as reasoning_tokens,
COALESCE(SUM(cache_read_tokens), 0) as cache_read,
COALESCE(SUM(cache_write_tokens), 0) as cache_write,
COUNT(DISTINCT session_id) as unique_sessions,
SUM(CASE WHEN status >= 200 AND status < 300 THEN 1 ELSE 0 END) as ok,
AVG(duration_ms) as avg_dur,
AVG(ttft_ms) as avg_ttft,
SUM(CASE WHEN datetime(created_at) >= datetime('now', '-1 hour') THEN 1 ELSE 0 END) as last_hour,
SUM(CASE WHEN datetime(created_at) >= datetime('now', '-24 hours') THEN 1 ELSE 0 END) as last_24h,
SUM(CASE WHEN datetime(created_at) >= datetime('now', '-48 hours') AND datetime(created_at) < datetime('now', '-24 hours') THEN 1 ELSE 0 END) as prev_24h,
COALESCE(SUM(CASE WHEN datetime(created_at) >= datetime('now', '-24 hours') THEN prompt_tokens + completion_tokens ELSE 0 END), 0) as last_24h_tokens,
SUM(CASE WHEN datetime(created_at) >= datetime('now', '-7 days') THEN 1 ELSE 0 END) as this_week_requests,
SUM(CASE WHEN datetime(created_at) >= datetime('now', '-7 days') THEN prompt_tokens + completion_tokens ELSE 0 END) as this_week_tokens,
SUM(CASE WHEN datetime(created_at) >= datetime('now', '-7 days') AND status >= 200 AND status < 300 THEN 1 ELSE 0 END) as this_week_ok,
SUM(CASE WHEN datetime(created_at) >= datetime('now', '-14 days') AND datetime(created_at) < datetime('now', '-7 days') THEN 1 ELSE 0 END) as last_week_requests,
SUM(CASE WHEN datetime(created_at) >= datetime('now', '-14 days') AND datetime(created_at) < datetime('now', '-7 days') THEN prompt_tokens + completion_tokens ELSE 0 END) as last_week_tokens,
SUM(CASE WHEN datetime(created_at) >= datetime('now', '-14 days') AND datetime(created_at) < datetime('now', '-7 days') AND status >= 200 AND status < 300 THEN 1 ELSE 0 END) as last_week_ok
FROM inference_requests
WHERE 1=1${filter.clause}`,
)
.get(...params) as NumberRow | null;
const totalRequests = toFiniteNumber(summary?.["total_requests"]);
if (totalRequests === 0) return null;
const promptTokens = toFiniteNumber(summary?.["prompt_tokens"]);
const completionTokens = toFiniteNumber(summary?.["completion_tokens"]);
const totalTokens = promptTokens + completionTokens;
const cacheHits = toFiniteNumber(summary?.["cache_read"]);
const cacheMisses = toFiniteNumber(summary?.["cache_write"]);
const successful = toFiniteNumber(summary?.["ok"]);
const byModel = this.db
.query<Record<string, unknown>, string[]>(
`SELECT
model,
COUNT(*) as requests,
SUM(CASE WHEN status >= 200 AND status < 300 THEN 1 ELSE 0 END) as successful,
COALESCE(SUM(prompt_tokens), 0) as prompt_tokens,
COALESCE(SUM(completion_tokens), 0) as completion_tokens,
COALESCE(SUM(prompt_tokens), 0) + COALESCE(SUM(completion_tokens), 0) as total_tokens,
AVG(duration_ms) as avg_latency_ms,
AVG(ttft_ms) as avg_ttft_ms
FROM inference_requests
WHERE 1=1${filter.clause}
GROUP BY model
ORDER BY total_tokens DESC
LIMIT 25`,
)
.all(...params) as Array<Record<string, unknown>>;
const daily = this.db
.query<Record<string, unknown>, string[]>(
`SELECT
DATE(created_at) as date,
COUNT(*) as requests,
SUM(CASE WHEN status >= 200 AND status < 300 THEN 1 ELSE 0 END) as successful,
COALESCE(SUM(prompt_tokens), 0) as prompt_tokens,
COALESCE(SUM(completion_tokens), 0) as completion_tokens,
COALESCE(SUM(prompt_tokens), 0) + COALESCE(SUM(completion_tokens), 0) as total_tokens,
AVG(duration_ms) as avg_latency_ms
FROM inference_requests
WHERE DATE(created_at) >= DATE('now', '-366 days')${filter.clause}
GROUP BY DATE(created_at)
ORDER BY date DESC
LIMIT 400`,
)
.all(...params) as Array<Record<string, unknown>>;
const dailyByModel = this.db
.query<Record<string, unknown>, string[]>(
`SELECT
DATE(created_at) as date,
model,
COUNT(*) as requests,
SUM(CASE WHEN status >= 200 AND status < 300 THEN 1 ELSE 0 END) as successful,
COALESCE(SUM(prompt_tokens), 0) as prompt_tokens,
COALESCE(SUM(completion_tokens), 0) as completion_tokens,
COALESCE(SUM(prompt_tokens), 0) + COALESCE(SUM(completion_tokens), 0) as total_tokens
FROM inference_requests
WHERE DATE(created_at) >= DATE('now', '-366 days')${filter.clause}
GROUP BY DATE(created_at), model
ORDER BY date DESC
LIMIT 10000`,
)
.all(...params) as Array<Record<string, unknown>>;
const hourly = this.db
.query<Record<string, unknown>, string[]>(
`SELECT
CAST(strftime('%H', created_at) AS INTEGER) as hour,
COUNT(*) as requests,
SUM(CASE WHEN status >= 200 AND status < 300 THEN 1 ELSE 0 END) as successful,
COALESCE(SUM(prompt_tokens + completion_tokens), 0) as tokens
FROM inference_requests
WHERE 1=1${filter.clause}
GROUP BY strftime('%H', created_at)
ORDER BY hour`,
)
.all(...params) as Array<Record<string, unknown>>;
const peakDays = this.db
.query<Record<string, unknown>, string[]>(
`SELECT
DATE(created_at) as date,
COUNT(*) as requests,
COALESCE(SUM(prompt_tokens + completion_tokens), 0) as tokens
FROM inference_requests
WHERE 1=1${filter.clause}
GROUP BY DATE(created_at)
ORDER BY requests DESC
LIMIT 5`,
)
.all(...params) as Array<Record<string, unknown>>;
const peakHours = this.db
.query<Record<string, unknown>, string[]>(
`SELECT
CAST(strftime('%H', created_at) AS INTEGER) as hour,
COUNT(*) as requests
FROM inference_requests
WHERE DATE(created_at) >= DATE('now', '-7 days')${filter.clause}
GROUP BY strftime('%H', created_at)
ORDER BY requests DESC
LIMIT 5`,
)
.all(...params) as Array<Record<string, unknown>>;
const calcChangePct = (current: number, previous: number): number | null => {
if (previous === 0) return current === 0 ? 0 : null;
return ((current - previous) / previous) * 100;
};
return {
totals: {
total_tokens: totalTokens,
prompt_tokens: promptTokens,
completion_tokens: completionTokens,
total_requests: totalRequests,
successful_requests: successful,
failed_requests: totalRequests - successful,
success_rate: totalRequests ? (successful / totalRequests) * 100 : 0,
unique_sessions: toFiniteNumber(summary?.["unique_sessions"]),
unique_users: 0,
},
latency: {
avg_ms: toNullableNumber(summary?.["avg_dur"]),
p50_ms: null,
p95_ms: null,
p99_ms: null,
min_ms: null,
max_ms: null,
},
ttft: {
avg_ms: toNullableNumber(summary?.["avg_ttft"]),
p50_ms: null,
p95_ms: null,
p99_ms: null,
},
tokens_per_request: {
avg: totalRequests ? Math.round(totalTokens / totalRequests) : 0,
avg_prompt: totalRequests ? Math.round(promptTokens / totalRequests) : 0,
avg_completion: totalRequests ? Math.round(completionTokens / totalRequests) : 0,
max: byModel.reduce(
(max, row) =>
Math.max(
max,
toFiniteNumber(row["requests"])
? Math.round(toFiniteNumber(row["total_tokens"]) / toFiniteNumber(row["requests"]))
: 0,
),
0,
),
p50: 0,
p95: 0,
},
cache: {
hits: cacheHits,
misses: cacheMisses,
hit_tokens: cacheHits,
miss_tokens: cacheMisses,
hit_rate: cacheHits + cacheMisses > 0 ? (cacheHits / (cacheHits + cacheMisses)) * 100 : 0,
},
week_over_week: {
this_week: {
requests: toFiniteNumber(summary?.["this_week_requests"]),
tokens: toFiniteNumber(summary?.["this_week_tokens"]),
successful: toFiniteNumber(summary?.["this_week_ok"]),
},
last_week: {
requests: toFiniteNumber(summary?.["last_week_requests"]),
tokens: toFiniteNumber(summary?.["last_week_tokens"]),
successful: toFiniteNumber(summary?.["last_week_ok"]),
},
change_pct: {
requests: calcChangePct(
toFiniteNumber(summary?.["this_week_requests"]),
toFiniteNumber(summary?.["last_week_requests"]),
),
tokens: calcChangePct(
toFiniteNumber(summary?.["this_week_tokens"]),
toFiniteNumber(summary?.["last_week_tokens"]),
),
},
},
recent_activity: {
last_hour_requests: toFiniteNumber(summary?.["last_hour"]),
last_24h_requests: toFiniteNumber(summary?.["last_24h"]),
prev_24h_requests: toFiniteNumber(summary?.["prev_24h"]),
last_24h_tokens: toFiniteNumber(summary?.["last_24h_tokens"]),
change_24h_pct: calcChangePct(
toFiniteNumber(summary?.["last_24h"]),
toFiniteNumber(summary?.["prev_24h"]),
),
},
peak_days: peakDays.map((row) => ({
date: String(row["date"] ?? ""),
requests: toFiniteNumber(row["requests"]),
tokens: toFiniteNumber(row["tokens"]),
})),
peak_hours: peakHours.map((row) => ({
hour: toFiniteNumber(row["hour"]),
requests: toFiniteNumber(row["requests"]),
})),
by_model: byModel.map((row) => {
const requests = toFiniteNumber(row["requests"]);
const ok = toFiniteNumber(row["successful"]);
return {
model: String(row["model"] ?? "unknown"),
requests,
successful: ok,
success_rate: requests ? (ok / requests) * 100 : 0,
total_tokens: toFiniteNumber(row["total_tokens"]),
prompt_tokens: toFiniteNumber(row["prompt_tokens"]),
completion_tokens: toFiniteNumber(row["completion_tokens"]),
avg_tokens: requests ? Math.round(toFiniteNumber(row["total_tokens"]) / requests) : 0,
avg_latency_ms: toNullableNumber(row["avg_latency_ms"]),
p50_latency_ms: null,
avg_ttft_ms: toNullableNumber(row["avg_ttft_ms"]),
tokens_per_sec: null,
prefill_tps: null,
generation_tps: null,
};
}),
daily: daily.map((row) => {
const requests = toFiniteNumber(row["requests"]);
const ok = toFiniteNumber(row["successful"]);
return {
date: String(row["date"] ?? ""),
requests,
successful: ok,
success_rate: requests ? (ok / requests) * 100 : 0,
total_tokens: toFiniteNumber(row["total_tokens"]),
prompt_tokens: toFiniteNumber(row["prompt_tokens"]),
completion_tokens: toFiniteNumber(row["completion_tokens"]),
avg_latency_ms: toFiniteNumber(row["avg_latency_ms"]),
};
}),
daily_by_model: dailyByModel.map((row) => {
const requests = toFiniteNumber(row["requests"]);
const ok = toFiniteNumber(row["successful"]);
return {
date: String(row["date"] ?? ""),
model: String(row["model"] ?? "unknown"),
requests,
successful: ok,
success_rate: requests ? (ok / requests) * 100 : 0,
total_tokens: toFiniteNumber(row["total_tokens"]),
prompt_tokens: toFiniteNumber(row["prompt_tokens"]),
completion_tokens: toFiniteNumber(row["completion_tokens"]),
};
}),
hourly_pattern: hourly.map((row) => ({
hour: toFiniteNumber(row["hour"]),
requests: toFiniteNumber(row["requests"]),
successful: toFiniteNumber(row["successful"]),
tokens: toFiniteNumber(row["tokens"]),
})),
};
}
public aggregateEffect(
knownModels?: ReadonlySet<string>,
): Effect.Effect<UsageAggregate | null, RepositoryError> {
return repositoryEffect("inference-requests.aggregate", () => this.aggregate(knownModels));
}
public close(): Effect.Effect<void, RepositoryError> {
return this.closeDatabase();
}
}