Insight SQL API

POST /api/public/sql
Content-Type: application/json

{
  "data": [
    { "id": "abc-123", "name": "my-trace", "timestamp": "2025-01-15 10:30:00.000" }
  ],
  "meta": {
    "rowCount": 1,
    "executionTimeMs": 42,
    "limitApplied": 5
  }
}

{
  "data": [],
  "meta": { "rowCount": 0, "executionTimeMs": 0, "limitApplied": 1 },
  "error": "Table 'users' is not allowed. Allowed tables: traces, observations, scores."
}

-- Day-level (uses partition key directly — fastest)
WHERE toDate(timestamp) = '2026-04-17'
 
-- Range
WHERE timestamp >= '2026-04-10' AND timestamp < '2026-04-17'

-- Good: lightweight columns only
SELECT id, name, timestamp, user_id, session_id, tags
FROM traces ORDER BY timestamp DESC
 
-- OK when needed: use length() to check size without reading full content
SELECT id, name, length(input) as input_len, length(output) as output_len
FROM traces WHERE timestamp >= '2026-04-17'

-- Recent traces
SELECT id, name, timestamp, user_id, session_id
FROM traces
WHERE timestamp >= '2026-04-10'
ORDER BY timestamp DESC
LIMIT 100
 
-- Count observations by type
SELECT type, count() as cnt
FROM observations
WHERE start_time >= '2026-04-10'
GROUP BY type
 
-- Score overview
SELECT name, data_type, avg(value) as avg_val, count() as cnt
FROM scores
WHERE timestamp >= '2026-04-01'
GROUP BY name, data_type
ORDER BY cnt DESC

-- Cost per trace
SELECT
    t.id,
    t.name,
    count() as span_count,
    sum(o.total_cost) as total_cost
FROM traces t
JOIN observations o ON t.id = o.trace_id
WHERE t.timestamp >= '2026-04-10'
  AND o.start_time >= '2026-04-10'
GROUP BY t.id, t.name
ORDER BY total_cost DESC NULLS LAST
LIMIT 10

-- Pre-aggregate scores, then join (faster than a flat 3-way JOIN)
SELECT t.name, agg.avg_score, agg.score_count
FROM traces t
JOIN (
    SELECT trace_id, avg(value) as avg_score, count() as score_count
    FROM scores
    WHERE name = 'correctness' AND timestamp >= '2026-04-10'
    GROUP BY trace_id
) agg ON t.id = agg.trace_id
WHERE t.timestamp >= '2026-04-10'
ORDER BY agg.score_count DESC
LIMIT 10

-- Traces with above-average span count
SELECT name, trace_count, avg_spans FROM (
    SELECT
        t.name,
        count(DISTINCT t.id) as trace_count,
        avg(span_cnt) as avg_spans
    FROM traces t
    JOIN (
        SELECT trace_id, count() as span_cnt
        FROM observations
        WHERE start_time >= '2026-04-10'
        GROUP BY trace_id
    ) o ON t.id = o.trace_id
    WHERE t.timestamp >= '2026-04-10'
    GROUP BY t.name
) sub
WHERE trace_count > 5
ORDER BY trace_count DESC

-- Discover available metadata keys
SELECT mapKeys(metadata) as keys
FROM traces
WHERE timestamp >= '2026-04-15' AND length(mapKeys(metadata)) > 0
LIMIT 10
 
-- Token usage by model
SELECT
    provided_model_name,
    sum(usage_details['input'])  as input_tokens,
    sum(usage_details['output']) as output_tokens,
    sum(usage_details['total'])  as total_tokens,
    count() as calls
FROM observations
WHERE type = 'GENERATION'
  AND start_time >= '2026-04-10'
  AND provided_model_name != ''
GROUP BY provided_model_name
ORDER BY total_tokens DESC
 
-- Cost breakdown by model
SELECT
    provided_model_name,
    sum(cost_details['input'])  as input_cost,
    sum(cost_details['output']) as output_cost,
    sum(total_cost)             as total,
    count() as calls
FROM observations
WHERE type = 'GENERATION'
  AND start_time >= '2026-04-10'
  AND total_cost IS NOT NULL AND total_cost > 0
GROUP BY provided_model_name
ORDER BY total DESC

-- Explode tags to individual rows
SELECT arrayJoin(tags) as tag, count() as cnt
FROM traces
WHERE timestamp >= '2026-04-10' AND length(tags) > 0
GROUP BY tag ORDER BY cnt DESC
 
-- Filter traces by specific tag
SELECT id, name FROM traces
WHERE has(tags, 'production') AND timestamp >= '2026-04-10'
LIMIT 10

-- Traces per hour
SELECT toStartOfHour(timestamp) as hour, count() as cnt
FROM traces
WHERE timestamp >= '2026-04-16'
GROUP BY hour ORDER BY hour
 
-- Daily dashboard with unique counts
SELECT
    toStartOfDay(timestamp) as day,
    count()              as traces,
    uniq(session_id)     as unique_sessions,
    uniq(user_id)        as unique_users
FROM traces
WHERE timestamp >= '2026-04-01'
GROUP BY day ORDER BY day

-- p50/p95/p99 latency per generation
SELECT
    name,
    quantile(0.5)(toUnixTimestamp64Milli(end_time) - toUnixTimestamp64Milli(start_time))  AS p50_ms,
    quantile(0.95)(toUnixTimestamp64Milli(end_time) - toUnixTimestamp64Milli(start_time)) AS p95_ms,
    quantile(0.99)(toUnixTimestamp64Milli(end_time) - toUnixTimestamp64Milli(start_time)) AS p99_ms,
    count() AS cnt
FROM observations
WHERE type = 'GENERATION'
  AND start_time >= '2026-04-10'
  AND end_time IS NOT NULL
GROUP BY name
HAVING cnt >= 10
ORDER BY p95_ms DESC
 
-- Error rate by operation
SELECT
    name,
    countIf(level = 'ERROR') as errors,
    count() as total,
    round(countIf(level = 'ERROR') * 100.0 / count(), 2) as error_rate_pct
FROM observations
WHERE start_time >= '2026-04-10' AND name != ''
GROUP BY name
HAVING total >= 10
ORDER BY error_rate_pct DESC

-- Score statistics
SELECT name, data_type, min(value), max(value), avg(value), count()
FROM scores
WHERE timestamp >= '2026-04-01'
GROUP BY name, data_type
ORDER BY count() DESC
 
-- Session-level scores
SELECT name, source, avg(value) as avg_val, count() as cnt
FROM scores
WHERE session_id IS NOT NULL AND session_id != ''
  AND timestamp >= '2026-04-01'
GROUP BY name, source
ORDER BY cnt DESC
 
-- Categorical score distribution
SELECT name, string_value, count() as cnt
FROM scores
WHERE data_type = 'CATEGORICAL'
  AND timestamp >= '2026-04-01'
  AND string_value IS NOT NULL
GROUP BY name, string_value
ORDER BY cnt DESC
 
-- Session duration and trace count
SELECT
    session_id,
    count() as trace_count,
    min(timestamp) as first_trace,
    max(timestamp) as last_trace
FROM traces
WHERE timestamp >= '2026-04-15'
  AND session_id IS NOT NULL AND session_id != ''
GROUP BY session_id
ORDER BY trace_count DESC
LIMIT 10

"Table 'users' is not allowed. Allowed tables: traces, observations, scores."
"Column 'nonexistent_column' not found. Check table schema."
"Multi-statement SQL is not allowed. Remove semicolons."
"Only SELECT queries are allowed."
"SQL query must not be empty"