Text-to-SQL Endpoints
The Text-to-SQL API provides endpoints for converting natural language questions into SQL queries, validating generated SQL, executing queries against the data warehouse, and managing query suggestions. These endpoints power the core conversational analytics flow in the BI Workbench.
Generate SQL
Converts a natural language question into a SQL query using schema context and RAG retrieval.
| Property | Value |
|---|---|
| Method | POST |
| Path | /api/v1/sql/generate |
| Auth | JWT required |
Request Body
{
"question": "What were our top 10 products by revenue last quarter?",
"tenant_id": "acme-corp",
"schema_context": "optional override",
"dialect": "trino",
"max_retries": 3,
"include_explanation": true
}Response
{
"success": true,
"sql": "SELECT product_name, SUM(revenue) as total_revenue FROM sales WHERE quarter = 'Q4' GROUP BY product_name ORDER BY total_revenue DESC LIMIT 10",
"explanation": "This query aggregates revenue by product for the last quarter...",
"confidence": 0.92,
"schema_tables_used": ["sales"],
"execution_plan": "sequential",
"warnings": []
}Validate SQL
Validates a SQL query against the tenant schema without executing it.
| Property | Value |
|---|---|
| Method | POST |
| Path | /api/v1/sql/validate |
| Auth | JWT required |
Request Body
{
"sql": "SELECT * FROM sales WHERE date > '2025-01-01'",
"dialect": "trino",
"tenant_id": "acme-corp"
}Response
{
"valid": true,
"errors": [],
"warnings": ["Query may return large result set without LIMIT clause"],
"tables_referenced": ["sales"],
"estimated_complexity": "low"
}Execute Query
Executes a validated SQL query against the Query Engine and returns results.
| Property | Value |
|---|---|
| Method | POST |
| Path | /api/v1/sql/execute |
| Auth | JWT required |
Request Body
{
"sql": "SELECT product_name, SUM(revenue) FROM sales GROUP BY product_name LIMIT 10",
"tenant_id": "acme-corp",
"timeout_seconds": 30,
"max_rows": 1000
}Response
{
"success": true,
"columns": ["product_name", "sum_revenue"],
"rows": [["Widget A", 150000], ["Widget B", 120000]],
"row_count": 10,
"execution_time_ms": 342,
"truncated": false
}Get Suggestions
Returns query suggestions based on the tenant schema and previous conversations.
| Property | Value |
|---|---|
| Method | GET |
| Path | /api/v1/sql/suggestions |
| Auth | JWT required |
Query Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
| tenant_id | string | yes | Tenant identifier |
| limit | integer | no | Maximum suggestions (default 5) |
| category | string | no | Filter by category (trending, recent, recommended) |
Response
{
"suggestions": [
{
"question": "What is our monthly revenue trend?",
"category": "trending",
"confidence": 0.95
}
]
}Decompose Query
Splits a complex natural language question into simpler sub-queries for sequential execution.
| Property | Value |
|---|---|
| Method | POST |
| Path | /api/v1/sql/decompose |
| Auth | JWT required |
Request Body
{
"question": "Compare Q3 and Q4 revenue by region and show the growth rate",
"tenant_id": "acme-corp"
}Response
{
"sub_queries": [
{
"step": 1,
"question": "What was the revenue by region in Q3?",
"depends_on": []
},
{
"step": 2,
"question": "What was the revenue by region in Q4?",
"depends_on": []
},
{
"step": 3,
"question": "Calculate growth rate between Q3 and Q4 by region",
"depends_on": [1, 2]
}
],
"execution_strategy": "parallel_then_merge"
}