Autorag Query

AutoRAG-Query: Text2SQL Agent Skill

Query AutoRAG pipeline results with natural language. Converts to SQL, executes safely, returns tables/JSON/CSV.

Quick Example

User: "Which pipeline has the best BLEU score?"

Agent: 1. Read references/schema.sql (understand tables) 2. Generate SQL:

   SELECT p.name, s.metric_result
   FROM summary s
   JOIN pipeline p ON s.pipeline_id = p.id
   JOIN metric m ON s.metric_id = m.id
   WHERE m.name = 'bleu'
   ORDER BY s.metric_result DESC LIMIT 1;

3. Execute: uv run python .agents/skills/autorag-query/scripts/query_executor.py --query "..." 4. Present: "hybrid_search_v2 has best BLEU: 0.85"

Workflow

1. Parse intent: What data? (metrics/pipelines/queries) What operation? (rank/aggregate/filter) 2. Load schema: Read references/schema.sql - key tables:

• summary: Aggregated pipeline metrics (best for rankings)
• evaluation_result: Per-query scores (detailed analysis)
• executor_result: Generation outputs with token_usage JSONB
• chunk_retrieved_result: Retrieval scores/ranks

3. Generate SQL following rules:

• ✅ SELECT-only, ⛔ Never: INSERT/UPDATE/DELETE/DROP/CREATE
• ⛔ Exclude vector columns: embedding, embeddings, bm25_tokens (cause type errors)
• Add LIMIT 100 if not specified
• Use JOINs: query_id → query.id, pipeline_id → pipeline.id, metric_id → metric.id
• JSONB: token_usage->>'field' (text) or (token_usage->>'field')::int (cast)

4. Execute: uv run python .agents/skills/autorag-query/scripts/query_executor.py --query "..." [--format json|csv|table] 5. Present: Summarize findings, show table, highlight insights

Key Tables

Relationships: query_id → query.id, pipeline_id → pipeline.id, metric_id → metric.id, chunk_id → chunk.id

Common Queries

See references/common-queries.md for 20+ templates.

Pipeline ranking:

SELECT p.name, s.metric_result
FROM summary s
JOIN pipeline p ON s.pipeline_id = p.id
JOIN metric m ON s.metric_id = m.id
WHERE m.name = 'bleu'
ORDER BY s.metric_result DESC;

Token usage:

SELECT p.name,
       SUM((exe.token_usage->>'total_tokens')::int) AS total_tokens,
       AVG((exe.token_usage->>'total_tokens')::int) AS avg_per_query
FROM executor_result exe
JOIN pipeline p ON exe.pipeline_id = p.id
WHERE exe.token_usage IS NOT NULL
GROUP BY p.name
ORDER BY total_tokens DESC;

Retrieval results:

SELECT c.content, crr.score, crr.rank
FROM chunk_retrieved_result crr
JOIN chunk c ON crr.chunk_id = c.id
WHERE crr.query_id = :query_id AND crr.pipeline_id = :pipeline_id
ORDER BY crr.rank LIMIT 10;

JSONB Extraction

executor_result.token_usage:

{"prompt_tokens": 150, "completion_tokens": 50, "total_tokens": 200}

Extract:

• Text: token_usage->>'prompt_tokens' → "150"
• Integer: (token_usage->>'total_tokens')::int → 200
• JSON: token_usage->'embedding_tokens' → preserves type

pipeline.config: config->>'model' → "gpt-4"

Critical Rules

1. ⛔ Always exclude: embedding, embeddings, bm25_tokens columns (cause type errors) 2. ✅ SELECT-only: Script validates and rejects DDL/DML 3. 📏 Add LIMIT: Prevent large result sets 4. 🔗 Use JOINs: Connect via foreign keys 5. ⚡ Timeout: 10s default (add WHERE filters if slow)

Script Usage

uv run python .agents/skills/autorag-query/scripts/query_executor.py \
  --query "SELECT ..." \
  --format table|json|csv \
  --timeout 10 \
  --limit 10000 \
  --database autorag_research  # optional

Connection: Auto-loads from configs/db.yaml or POSTGRES_* env vars using DBConnection class.

Output formats:

• table: ASCII table (default)
• json: JSON array
• csv: CSV with headers

Row count: Printed to stderr: (N rows)

Error Handling

Advanced: Window Functions & Pivots

Ranking:

SELECT p.name, m.name, s.metric_result,
       RANK() OVER (PARTITION BY m.name ORDER BY s.metric_result DESC) AS rank
FROM summary s
JOIN pipeline p ON s.pipeline_id = p.id
JOIN metric m ON s.metric_id = m.id;

Pivot:

SELECT p.name,
       MAX(CASE WHEN m.name = 'bleu' THEN s.metric_result END) AS bleu,
       MAX(CASE WHEN m.name = 'rouge' THEN s.metric_result END) AS rouge
FROM summary s
JOIN pipeline p ON s.pipeline_id = p.id
JOIN metric m ON s.metric_id = m.id
GROUP BY p.name;

References

• Schema: references/schema.sql - full DB schema with comments
• Templates: references/common-queries.md - 20+ query examples
• Executor: scripts/query_executor.py - safe SQL execution script

Installation: Works from .agents/skills/autorag-query/ (auto-detected by agents).

Common Query Templates

This document provides curated SQL query templates for common analysis tasks in AutoRAG-Research.

IMPORTANT: Always exclude vector/embedding columns: embedding, embeddings, bm25_tokens

Parameterized Queries: Templates use :param_name syntax for safe value substitution. Pass parameters via --params '{"param_name": "value"}' to the query executor.

Pipeline Performance Comparison

Top pipelines by metric score

SELECT
    p.name AS pipeline_name,
    m.name AS metric_name,
    s.metric_result,
    s.created_at
FROM summary s
JOIN pipeline p ON s.pipeline_id = p.id
JOIN metric m ON s.metric_id = m.id
WHERE m.name = :metric_name
ORDER BY s.metric_result DESC
LIMIT :top_n;

Parameters: :metric_name (e.g., 'bleu', 'retrieval_precision'), :top_n (e.g., 10)

Example output:

pipeline_name       | metric_name | metric_result | created_at
--------------------|-------------|---------------|------------
hybrid_search_v2    | bleu        | 0.85         | 2025-01-15
naive_rag           | bleu        | 0.72         | 2025-01-14

---

Average metrics across all queries

SELECT
    p.name AS pipeline_name,
    m.name AS metric_name,
    AVG(er.metric_result) AS avg_score,
    COUNT(*) AS query_count
FROM evaluation_result er
JOIN pipeline p ON er.pipeline_id = p.id
JOIN metric m ON er.metric_id = m.id
GROUP BY p.name, m.name
ORDER BY avg_score DESC;

Description: Shows average metric scores per pipeline across all queries.

---

Pipeline ranking by multiple metrics

SELECT
    p.name AS pipeline_name,
    MAX(CASE WHEN m.name = 'bleu' THEN s.metric_result END) AS bleu,
    MAX(CASE WHEN m.name = 'retrieval_precision' THEN s.metric_result END) AS precision,
    MAX(CASE WHEN m.name = 'retrieval_recall' THEN s.metric_result END) AS recall
FROM summary s
JOIN pipeline p ON s.pipeline_id = p.id
JOIN metric m ON s.metric_id = m.id
WHERE m.name IN ('bleu', 'retrieval_precision', 'retrieval_recall')
GROUP BY p.name
ORDER BY bleu DESC, precision DESC;

Description: Compare pipelines across multiple metrics using pivot table.

---

Per-Query Analysis

Query-level metric breakdown

SELECT
    q.query AS question,
    p.name AS pipeline_name,
    m.name AS metric_name,
    er.metric_result,
    er.created_at
FROM evaluation_result er
JOIN query q ON er.query_id = q.id
JOIN pipeline p ON er.pipeline_id = p.id
JOIN metric m ON er.metric_id = m.id
WHERE q.id = :query_id
ORDER BY m.name, p.name;

Parameters: :query_id (specific query to analyze)

Description: Shows all metric scores for a specific query across all pipelines.

---

Compare generated vs ground truth

SELECT
    q.query AS question,
    q.ground_truths,
    p.name AS pipeline_name,
    exe.generation_result,
    (exe.token_usage->>'total_tokens')::int AS total_tokens
FROM executor_result exe
JOIN query q ON exe.query_id = q.id
JOIN pipeline p ON exe.pipeline_id = p.id
WHERE q.id = :query_id
ORDER BY p.name;

Parameters: :query_id

Description: Compare generation outputs against ground truth answers.

JSONB Note: Use token_usage->>'field' to extract text, cast to ::int for numbers.

---

Find worst-performing queries

SELECT
    q.query AS question,
    p.name AS pipeline_name,
    m.name AS metric_name,
    er.metric_result,
    q.ground_truths
FROM evaluation_result er
JOIN query q ON er.query_id = q.id
JOIN pipeline p ON er.pipeline_id = p.id
JOIN metric m ON er.metric_id = m.id
WHERE m.name = :metric_name
  AND p.name = :pipeline_name
ORDER BY er.metric_result ASC
LIMIT :bottom_n;

Parameters: :metric_name, :pipeline_name, :bottom_n

Description: Identify queries with lowest scores for error analysis.

---

Retrieval Results Analysis

Top retrieved chunks for a query

SELECT
    q.query AS question,
    c.content AS chunk_text,
    crr.score AS retrieval_score,
    crr.rank,
    p.name AS pipeline_name
FROM chunk_retrieved_result crr
JOIN query q ON crr.query_id = q.id
JOIN chunk c ON crr.chunk_id = c.id
JOIN pipeline p ON crr.pipeline_id = p.id
WHERE q.id = :query_id
  AND p.name = :pipeline_name
ORDER BY crr.rank ASC
LIMIT :top_k;

Parameters: :query_id, :pipeline_name, :top_k

Description: Shows actual retrieved chunks with scores and rankings.

---

Retrieval score distribution

SELECT
    p.name AS pipeline_name,
    AVG(crr.score) AS avg_score,
    MIN(crr.score) AS min_score,
    MAX(crr.score) AS max_score,
    COUNT(*) AS total_retrievals
FROM chunk_retrieved_result crr
JOIN pipeline p ON crr.pipeline_id = p.id
GROUP BY p.name
ORDER BY avg_score DESC;

Description: Analyze retrieval score statistics per pipeline.

---

Ground truth comparison (retrieval)

SELECT
    q.query AS question,
    p.name AS pipeline_name,
    COUNT(DISTINCT rr.chunk_id) AS total_relevant_chunks,
    COUNT(DISTINCT CASE WHEN crr.chunk_id IS NOT NULL THEN rr.chunk_id END) AS retrieved_relevant_chunks,
    CAST(COUNT(DISTINCT CASE WHEN crr.chunk_id IS NOT NULL THEN rr.chunk_id END) AS FLOAT) /
        NULLIF(COUNT(DISTINCT rr.chunk_id), 0) AS recall
FROM query q
JOIN retrieval_relation rr ON q.id = rr.query_id
JOIN pipeline p ON p.id = :pipeline_id
LEFT JOIN chunk_retrieved_result crr ON
    rr.query_id = crr.query_id
    AND rr.chunk_id = crr.chunk_id
    AND crr.pipeline_id = p.id
WHERE q.id = :query_id
GROUP BY q.query, p.name;

Parameters: :query_id, :pipeline_id

Description: Calculate recall by comparing retrieved chunks against ground truth.

---

Token Usage Analysis

Token usage by pipeline

SELECT
    p.name AS pipeline_name,
    COUNT(*) AS query_count,
    SUM((exe.token_usage->>'prompt_tokens')::int) AS total_prompt_tokens,
    SUM((exe.token_usage->>'completion_tokens')::int) AS total_completion_tokens,
    SUM((exe.token_usage->>'total_tokens')::int) AS total_tokens,
    AVG((exe.token_usage->>'total_tokens')::int) AS avg_tokens_per_query
FROM executor_result exe
JOIN pipeline p ON exe.pipeline_id = p.id
WHERE exe.token_usage IS NOT NULL
GROUP BY p.name
ORDER BY total_tokens DESC;

Description: Aggregate token usage statistics per pipeline.

JSONB Extraction: Cast to ::int after extracting with ->>.

---

Most expensive queries

SELECT
    q.query AS question,
    p.name AS pipeline_name,
    (exe.token_usage->>'total_tokens')::int AS total_tokens,
    (exe.token_usage->>'prompt_tokens')::int AS prompt_tokens,
    (exe.token_usage->>'completion_tokens')::int AS completion_tokens,
    exe.execution_time
FROM executor_result exe
JOIN query q ON exe.query_id = q.id
JOIN pipeline p ON exe.pipeline_id = p.id
WHERE exe.token_usage IS NOT NULL
ORDER BY (exe.token_usage->>'total_tokens')::int DESC
LIMIT :top_n;

Parameters: :top_n

Description: Find queries with highest token consumption.

---

Token usage over time

SELECT
    DATE(exe.created_at) AS date,
    p.name AS pipeline_name,
    SUM((exe.token_usage->>'total_tokens')::int) AS daily_tokens,
    COUNT(*) AS query_count
FROM executor_result exe
JOIN pipeline p ON exe.pipeline_id = p.id
WHERE exe.token_usage IS NOT NULL
  AND exe.created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY DATE(exe.created_at), p.name
ORDER BY date DESC, daily_tokens DESC;

Description: Track token usage trends over last 30 days.

---

Execution Performance

Slowest queries by execution time

SELECT
    q.query AS question,
    p.name AS pipeline_name,
    exe.execution_time,
    (exe.token_usage->>'total_tokens')::int AS total_tokens,
    exe.created_at
FROM executor_result exe
JOIN query q ON exe.query_id = q.id
JOIN pipeline p ON exe.pipeline_id = p.id
ORDER BY exe.execution_time DESC
LIMIT :top_n;

Parameters: :top_n

Description: Identify performance bottlenecks.

---

Average execution time by pipeline

SELECT
    p.name AS pipeline_name,
    COUNT(*) AS query_count,
    AVG(exe.execution_time) AS avg_execution_time,
    MIN(exe.execution_time) AS min_execution_time,
    MAX(exe.execution_time) AS max_execution_time
FROM executor_result exe
JOIN pipeline p ON exe.pipeline_id = p.id
GROUP BY p.name
ORDER BY avg_execution_time DESC;

Description: Compare pipeline performance across all queries.

---

JSONB Extraction Patterns

Extract nested token usage details

SELECT
    p.name AS pipeline_name,
    q.query AS question,
    exe.token_usage->>'prompt_tokens' AS prompt_tokens_text,
    (exe.token_usage->>'prompt_tokens')::int AS prompt_tokens_int,
    exe.token_usage->'embedding_tokens' AS embedding_tokens_json,
    exe.token_usage AS full_token_usage
FROM executor_result exe
JOIN pipeline p ON exe.pipeline_id = p.id
JOIN query q ON exe.query_id = q.id
WHERE exe.token_usage IS NOT NULL
LIMIT 5;

JSONB Operators:

• -> extracts as JSON (preserves type)
• ->> extracts as text
• Cast with ::int, ::float, etc.

---

Parse pipeline config JSONB

SELECT
    name AS pipeline_name,
    config->>'type' AS pipeline_type,
    config->>'model' AS model_name,
    (config->>'top_k')::int AS top_k,
    config AS full_config
FROM pipeline
WHERE config IS NOT NULL
LIMIT 10;

Description: Extract structured data from pipeline configuration.

---

Complex Multi-Table JOINs

Full pipeline execution report

SELECT
    p.name AS pipeline_name,
    q.query AS question,
    exe.generation_result,
    m.name AS metric_name,
    er.metric_result AS score,
    (exe.token_usage->>'total_tokens')::int AS tokens,
    exe.execution_time
FROM executor_result exe
JOIN query q ON exe.query_id = q.id
JOIN pipeline p ON exe.pipeline_id = p.id
LEFT JOIN evaluation_result er ON
    er.query_id = exe.query_id
    AND er.pipeline_id = exe.pipeline_id
LEFT JOIN metric m ON er.metric_id = m.id
WHERE p.name = :pipeline_name
ORDER BY q.query, m.name;

Parameters: :pipeline_name

Description: Comprehensive view of pipeline execution with all metrics.

---

Retrieval + Generation + Evaluation combined

SELECT
    q.query AS question,
    p.name AS pipeline_name,
    STRING_AGG(DISTINCT c.content, ' | ') AS retrieved_chunks,
    exe.generation_result,
    MAX(CASE WHEN m.name = 'bleu' THEN er.metric_result END) AS bleu,
    MAX(CASE WHEN m.name = 'rouge' THEN er.metric_result END) AS rouge
FROM query q
JOIN pipeline p ON p.id = :pipeline_id
LEFT JOIN chunk_retrieved_result crr ON
    crr.query_id = q.id
    AND crr.pipeline_id = p.id
LEFT JOIN chunk c ON crr.chunk_id = c.id
LEFT JOIN executor_result exe ON
    exe.query_id = q.id
    AND exe.pipeline_id = p.id
LEFT JOIN evaluation_result er ON
    er.query_id = q.id
    AND er.pipeline_id = p.id
LEFT JOIN metric m ON er.metric_id = m.id
WHERE q.id = :query_id
GROUP BY q.query, p.name, exe.generation_result;

Parameters: :query_id, :pipeline_id

Description: Complete RAG pipeline trace for a single query.

---

Metadata Queries

List all pipelines

SELECT
    id,
    name,
    pipeline_type,
    created_at,
    config->>'model' AS model,
    config->>'type' AS config_type
FROM pipeline
ORDER BY created_at DESC;

---

List all metrics

SELECT
    id,
    name,
    metric_type,
    created_at
FROM metric
ORDER BY metric_type, name;

Note: metric_type is either 'retrieval' or 'generation'.

---

Count queries by dataset

SELECT
    dataset_name,
    COUNT(*) AS query_count,
    COUNT(DISTINCT ground_truths) AS unique_ground_truths
FROM query
GROUP BY dataset_name
ORDER BY query_count DESC;

---

Notes

• Always exclude: embedding, embeddings, bm25_tokens columns
• JSONB extraction: Use ->> for text, cast to type for numbers
• Performance: Add LIMIT clauses to prevent large result sets
• Parameterization: Use :param_name with --params '{"param_name": "value"}' for safe value substitution
• NULL handling: Use NULLIF() and COALESCE() for division and defaults
• Example with params: python query_executor.py -q "SELECT * FROM pipeline WHERE name = :name" -p '{"name": "naive_rag"}'