Dashboard

Useful Queries

Reference SQL queries for common cost analysis tasks. Run these directly in TimescaleDB or use them as Grafana panel queries.

Spend analysis

-- Spend today by project
SELECT tags->>'project' AS project, SUM(cost_usd) AS total
FROM llm_calls
WHERE time >= date_trunc('day', NOW())
GROUP BY 1 ORDER BY 2 DESC;

-- Spend this month by feature
SELECT tags->>'feature' AS feature, SUM(cost_usd) AS total, COUNT(*) AS calls
FROM llm_calls
WHERE time >= date_trunc('month', NOW())
GROUP BY 1 ORDER BY 2 DESC;

-- Daily spend trend for the last 30 days
SELECT time_bucket('1 day', time) AS day, SUM(cost_usd)
FROM llm_calls
WHERE time > NOW() - INTERVAL '30 days'
GROUP BY 1 ORDER BY 1;

Model and token analysis

-- Cost and tokens per model
SELECT model, provider,
  COUNT(*) AS calls,
  SUM(input_tokens) AS total_input,
  SUM(output_tokens) AS total_output,
  SUM(cached_tokens) AS total_cached,
  SUM(cost_usd) AS total_cost,
  AVG(cost_usd) AS avg_cost_per_call
FROM llm_calls
WHERE time > NOW() - INTERVAL '7 days'
GROUP BY model, provider ORDER BY total_cost DESC;

-- Cache efficiency ratio
SELECT model,
  SUM(cached_tokens)::float / NULLIF(SUM(input_tokens), 0) AS cache_hit_ratio,
  SUM(cached_tokens * 0.000003) AS estimated_savings_usd
FROM llm_calls
WHERE time > NOW() - INTERVAL '7 days'
GROUP BY model ORDER BY cache_hit_ratio DESC;

Agentic workflow costs

-- Most expensive workflow runs
SELECT * FROM trace_costs
ORDER BY total_cost_usd DESC LIMIT 20;

-- Average cost per workflow type (feature)
SELECT feature, COUNT(*) AS runs,
  AVG(total_cost_usd) AS avg_cost,
  MAX(total_cost_usd) AS max_cost
FROM trace_costs
WHERE feature IS NOT NULL
GROUP BY 1 ORDER BY avg_cost DESC;

-- All calls in a specific trace
SELECT time, provider, model, cost_usd,
  tags->>'span_name' AS span, latency_ms
FROM llm_calls
WHERE trace_id = 'paste-your-trace-uuid-here'
ORDER BY time;

Budget monitoring

-- Check spend vs budget for all projects this month
SELECT
  b.tag_value AS project,
  b.limit_usd AS budget,
  COALESCE(SUM(c.cost_usd), 0) AS spent,
  ROUND(COALESCE(SUM(c.cost_usd), 0) / b.limit_usd * 100, 1) AS pct_used
FROM budgets b
LEFT JOIN llm_calls c
  ON c.tags->>'project' = b.tag_value
  AND c.time >= date_trunc('month', NOW())
WHERE b.tag_key = 'project' AND b.period = 'monthly'
GROUP BY b.tag_value, b.limit_usd
ORDER BY pct_used DESC;

Writer health

-- Check writer status and SQLite backlog
SELECT service_id, timescale_ok, sqlite_backlog,
  written_timescale, written_sqlite, failed, last_flush
FROM writer_health;

← Previous

Grafana Overview

FastAPI