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:

``sql SELECT p.name, s.metricresult FROM summary s JOIN pipeline p ON s.pipelineid = p.id JOIN metric m ON s.metricid = m.id WHERE m.name = 'bleu' ORDER BY s.metricresult DESC LIMIT 1; ``

1. Execute: uv run python .agents/skills/autorag-query/scripts/query_executor.py --query "..."
2. Present: "hybridsearchv2 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) - evaluationresult: Per-query scores (detailed analysis) - executorresult: Generation outputs with tokenusage JSONB - chunkretrieved_result: Retrieval scores/ranks

1. Generate SQL following rules:

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

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

Key Tables

Relationships: queryid → query.id, pipelineid → pipeline.id, metricid → metric.id, chunkid → 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

executorresult.tokenusage:

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

Extract:

• Text: tokenusage->>'prompttokens' → "150"
• Integer: (tokenusage->>'totaltokens')::int → 200
• JSON: tokenusage->'embeddingtokens' → 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).