Database Connections

-- List all projects
SELECT id, name, slug FROM projects;

-- List all agents with their SLOs
SELECT a.agent_name, a.project_id, s.metric, s.target, s.window_hours
FROM agent_metadata a
LEFT JOIN agent_slos s ON s.agent_name = a.agent_name AND s.project_id = a.project_id
ORDER BY a.agent_name;

-- List model pricing
SELECT model_id, input_per_1m_usd, output_per_1m_usd FROM model_pricing ORDER BY provider, model_id;

-- List users
SELECT id, email, role, created_at FROM users ORDER BY created_at;

PGPASSWORD=$(grep POSTGRES_PASSWORD .env | cut -d= -f2) psql -h localhost -p 5432 -U langsight -d langsight

-- Recent sessions with health tags
SELECT
    t.session_id,
    t.agent_name,
    h.health_tag,
    count() AS spans,
    min(t.started_at) AS started,
    max(t.ended_at) AS ended
FROM mcp_tool_calls t
LEFT JOIN session_health_tags h ON h.session_id = t.session_id
WHERE t.started_at >= now() - INTERVAL 24 HOUR
  AND t.session_id != ''
GROUP BY t.session_id, t.agent_name, h.health_tag
ORDER BY started DESC
LIMIT 50;

-- Error breakdown last 24h
SELECT
    status,
    count() AS cnt,
    round(count() * 100.0 / sum(count()) OVER (), 1) AS pct
FROM mcp_tool_calls
WHERE started_at >= now() - INTERVAL 24 HOUR
  AND status != 'success'
GROUP BY status
ORDER BY cnt DESC;

-- Token usage by model last 7 days
SELECT
    model_id,
    count() AS calls,
    sum(input_tokens) AS total_input,
    sum(output_tokens) AS total_output,
    round(avg(latency_ms), 0) AS avg_latency_ms
FROM mcp_tool_calls
WHERE started_at >= now() - INTERVAL 7 DAY
  AND model_id != ''
GROUP BY model_id
ORDER BY calls DESC;

-- Slowest tools (p99 latency)
SELECT
    server_name,
    tool_name,
    count() AS calls,
    round(quantile(0.99)(latency_ms), 0) AS p99_ms,
    round(avg(latency_ms), 0) AS avg_ms
FROM mcp_tool_calls
WHERE started_at >= now() - INTERVAL 24 HOUR
  AND span_type = 'tool_call'
GROUP BY server_name, tool_name
ORDER BY p99_ms DESC
LIMIT 20;

docker exec -it langsight-clickhouse clickhouse-client --database langsight

clickhouse-client -h localhost --port 9000 --database langsight

# docker-compose.yml
services:
  postgres:
    environment:
      POSTGRES_PASSWORD: your-strong-password
  api:
    environment:
      LANGSIGHT_POSTGRES_URL: postgresql://langsight:your-strong-password@postgres:5432/langsight