spb/chat-spboucher-ai

1import { guarded, json } from "@/lib/api";2import { sql } from "@/lib/db";3import { getSpending } from "@/lib/usage";45export const GET = guarded(async (req) => {6  const p = req.nextUrl.searchParams;7  const from = p.get("from") ?? new Date(Date.now() - 30 * 86_400_000).toISOString();8  const to = p.get("to") ?? new Date(Date.now() + 60_000).toISOString();9  const model = p.get("model"); const provider = p.get("provider"); const project = p.get("project");10  const where = sql`WHERE at >= ${from}::timestamptz AND at <= ${to}::timestamptz AND (${!model} OR model_id = ${model ?? ""}) AND (${!provider} OR provider = ${provider ?? ""}) AND (${!project} OR project_id = ${project ?? ""})`;11  const totals = await sql`SELECT count(*)::int AS requests, coalesce(sum(cost_estimated),0)::float AS cost, coalesce(sum(cost_confirmed),0)::float AS cost_confirmed, coalesce(sum(input_tokens),0)::bigint AS input_tokens, coalesce(sum(output_tokens),0)::bigint AS output_tokens, coalesce(sum(cached_tokens),0)::bigint AS cached_tokens, coalesce(sum(reasoning_tokens),0)::bigint AS reasoning_tokens, avg(latency_ms)::int AS avg_latency, avg(ttft_ms)::int AS avg_ttft, count(*) FILTER (WHERE status = 'error')::int AS errors FROM usage_events ${where}`;12  const daily = await sql`SELECT date_trunc('day', at)::date AS day, provider, coalesce(sum(cost_estimated),0)::float AS cost, count(*)::int AS requests, sum(input_tokens)::bigint AS input_tokens, sum(output_tokens)::bigint AS output_tokens FROM usage_events ${where} GROUP BY 1, 2 ORDER BY 1`;13  const byModel = await sql`SELECT model_id, provider, count(*)::int AS requests, coalesce(sum(cost_estimated),0)::float AS cost, sum(input_tokens)::bigint AS input_tokens, sum(output_tokens)::bigint AS output_tokens, sum(cached_tokens)::bigint AS cached_tokens, avg(latency_ms)::int AS avg_latency, avg(ttft_ms)::int AS avg_ttft, count(*) FILTER (WHERE status = 'error')::int AS errors FROM usage_events ${where} GROUP BY 1, 2 ORDER BY cost DESC`;14  const byKind = await sql`SELECT kind, count(*)::int AS requests, coalesce(sum(cost_estimated),0)::float AS cost FROM usage_events ${where} GROUP BY 1 ORDER BY cost DESC`;15  const byProject = await sql`SELECT coalesce(p.name, '—') AS project, u.project_id, count(*)::int AS requests, coalesce(sum(u.cost_estimated),0)::float AS cost FROM usage_events u LEFT JOIN projects p ON p.id = u.project_id ${where} GROUP BY 1, 2 ORDER BY cost DESC`;16  const monthly = await sql`SELECT date_trunc('month', at)::date AS month, coalesce(sum(cost_estimated),0)::float AS cost FROM usage_events GROUP BY 1 ORDER BY 1 DESC LIMIT 12`;17  const spending = await getSpending();18  return json({ totals: totals[0], daily, byModel, byKind, byProject, monthly, spending, range: { from, to } });19});20