Query Endpoints

Endpoints for converting natural language to SQL and executing queries.

POST /api/query/generate

Location: app/api/query/generate/route.ts

Converts natural language queries to SQL using OpenAI.

Request

interface GenerateRequest {
  query: string;              // Natural language query
  databaseType: string;       // e.g., "PostgreSQL"
  vectorStoreId: string;      // OpenAI vector store ID
  schemaData?: Schema;        // Optional: for auto-reupload
  existingFileId?: string;    // Optional: for cleanup
  examples?: { question?: string; sql?: string }[];                        // Optional: proven few-shot examples (learning)
  corrections?: { question?: string; badSql?: string; error?: string; goodSql?: string }[]; // Optional: failed→fixed corrections (learning)
  defaultLimit?: number | 'none';  // Optional: user's default row limit; shapes prompt rule 4
  model?: string;             // Optional: eval-only model override (see note)
  effort?: 'none' | 'minimal' | 'low' | 'medium' | 'high' | 'xhigh' | 'max'; // Optional: eval-only (see note)
}

defaultLimit comes from the query page's "Default row limit" dropdown. It rewrites generation rule 4: a number asks the model for at most that many rows using the dialect-appropriate syntax; 'none' tells the model not to add an automatic row limit. Invalid or absent values fall back to QUERY_LIMIT.DEFAULT (100). The same value is sent to /api/query/execute, which enforces it independently.

model / effort are for the eval harness, not for normal clients. Both are honored only when the server env EVAL_ALLOW_MODEL_OVERRIDE=true; otherwise they are ignored entirely and the route uses OPENAI_MODEL. When the flag is on, a supplied model must match /^[a-zA-Z0-9._:-]{1,64}$/ — anything else returns HTTP 400 rather than silently falling back to OPENAI_MODEL. See evals/README.md.

Reasoning Effort

Reasoning effort is configured server-side via the optional OPENAI_REASONING_EFFORT env variable. Accepted values: none | minimal | low | medium | high | xhigh | max. The per-request effort override rides the same EVAL_ALLOW_MODEL_OVERRIDE flag. On an eval-override request (flag on + body model present) the env variable is not consulted at all — effort comes from the body alone, so a variant sent without effort measures the provider default. All other requests keep the env fallback.

The reasoning parameter applies to gpt-5 / o-series models only, and not every reasoning model supports every value — the OpenAI API validates the combination, not the SDK. When nothing resolves (env unset/invalid and no honored override), the reasoning key is omitted from the request entirely, so default behavior is unchanged.

Response

// Success
interface GenerateResponse {
  sql: string;              // Generated SQL query
  explanation: string;      // What the query does
  confidence: number;       // 0-1 confidence score
  warnings: string[];       // Potential issues
  // If schema was reuploaded:
  newFileId?: string;
  newVectorStoreId?: string;
  schemaReuploaded?: boolean;
  // Present when OpenAI reports token usage:
  usage?: {
    model: string;              // Model OpenAI actually served (often a dated snapshot)
    inputTokens: number;
    cachedInputTokens: number;  // subset of inputTokens
    cacheWriteTokens: number;   // subset of inputTokens
    outputTokens: number;
    reasoningTokens: number;    // subset of outputTokens
    totalTokens: number;
  };
  // Rate-limit info; `Infinity` when DEMO_RATE_LIMIT is unset (serializes to null over JSON).
  // Omitted on the two fallback paths: a JSON code block salvaged from a non-JSON
  // reply, and the HTTP 200 mock response returned when the request itself throws.
  rateLimit?: { remaining: number | null; limit: number | null };
}

// Error — `usage` (same shape as above) is included when OpenAI billed tokens
// before the failure, e.g. the 500 returned when the response status is not "completed"
{ "error": "Error message", "usage": { /* optional */ } }

Token Usage (`usage`)

Included whenever OpenAI reports usage on the response, for cost accounting.

model is the model OpenAI actually served, which is typically a dated snapshot (e.g. gpt-5.4-2026-03-05) rather than the requested alias.
The breakdowns are subsets, never additive: reasoningTokens is part of outputTokens; cachedInputTokens and cacheWriteTokens are parts of inputTokens. Do not add them together when computing totals.

usage is returned by the generate route only. The other OpenAI routes (enhance, revise, followup, dashboard/suggestions, schema/generate-descriptions, chart/generate) still discard usage.

Behavior

Validation: Checks for required query parameter
API Key Check: Verifies OPENAI_API_KEY is set
OpenAI Request: Uses Responses API with file_search tool
Vector Store Recovery: If vector store 404, attempts automatic reupload
Response Parsing: Extracts JSON from response, with fallback parsing

System Prompt

The AI is instructed to:

Generate only SELECT statements
Use proper JOIN syntax
Include appropriate WHERE clauses
Use LIMIT for large result sets
Validate tables/columns exist in schema
Return JSON format only

Learning Prompt Sections ("Learn from previous queries")

When the client sends examples and/or corrections (device-local query history, keyed by schema fingerprint), buildLearningSections() renders two optional, guarded sections into the system prompt:

Proven examples for this schema — past question/SQL pairs that ran successfully, used as guidance for table/column names and query patterns.
Known corrections — avoid these mistakes — previously failed queries and their corrected versions, so the model avoids repeating wrong table/column names.

Both sections are omitted entirely when no history is supplied, so the base prompt is byte-identical with no learning data. Server-side the arrays are defensively capped (examples sliced to 6, corrections to 4); the client sends at most AI.MAX_FEW_SHOT (4) examples and AI.MAX_CORRECTIONS (2) corrections (lib/constants.ts). The schema file remains the only source of truth — examples/corrections never override it.

Fallback Parsing

If OpenAI returns non-JSON, the endpoint attempts to extract:

SQL from ```sql code blocks
SELECT statements directly
Explanation and confidence from text

Error Handling

// Missing query
{ "error": "Query is required" } // 400

// Missing API key
{ "error": "OpenAI API key not configured" } // 400

// OpenAI API failure
{ "error": "OpenAI API request failed: ..." } // 500

// Fallback mock response on error
{
  "sql": "SELECT table_name, column_name...",
  "explanation": "Shows database schema information...",
  "confidence": 0.3,
  "warnings": ["This is a mock response..."]
}

Example Usage

const response = await fetch('/api/query/generate', {
  method: 'POST',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify({
    query: "Show me all customers who ordered in the last month",
    databaseType: "PostgreSQL",
    vectorStoreId: "vs_abc123",
    schemaData: currentSchema,
    existingFileId: "file_xyz"
  })
});

const { sql, explanation, confidence, warnings } = await response.json();

POST /api/query/execute

Location: app/api/query/execute/route.ts

Executes SQL queries against the connected database.

Request

interface ExecuteRequest {
  sql: string;                    // SQL query to execute
  // No-auth mode: full connection object (credentials supplied by client)
  connection?: {
    host: string;
    port: string;
    database: string;
    username: string;
    password: string;
  };
  // Auth mode: credentials resolved server-side from the app DB
  connectionId?: string;
  source?: "local" | "server";
  type?: string;
  defaultLimit?: number | 'none'; // Optional: inject a row limit when the SQL has none
  dirtyRead?: boolean;            // Optional: run at READ UNCOMMITTED (see Dirty Reads below)
  queryId?: string;               // Optional: client-generated UUID enabling POST /api/query/cancel
  // Optional audit-log metadata (never required)
  question?: string;              // Natural-language prompt behind the SQL
  querySource?: string;           // e.g. "report", "followup"
}

queryId must be a well-formed UUID; a malformed one is rejected with 400 INVALID_QUERY_ID. Omitting it is fine — the query simply runs untracked and cannot be cancelled.

Response

// Success
interface ExecuteResponse {
  columns: string[];          // Column names
  rows: string[][];          // Row data (all values as strings)
  rowCount: number;          // Number of rows returned
  executionTime: number;     // Milliseconds to execute
  limitApplied?: number;     // Present only when a default row limit was injected
  dirtyReadApplied?: boolean; // Present only when dirtyRead was requested.
                              // false = this engine has no dirty-read mode (no-op)
}

// Error
{ "error": "Error message" }

// Cancelled — HTTP 499
{ "error": "Query cancelled", "errorCode": "QUERY_CANCELLED", "cancelled": true }

In the normal flow the client never reads the 499: its own fetch has already rejected with AbortError. The branch exists so the audit log records the cancellation correctly and so a cancellation is not reported as a 500.

Security Validation (AST-based)

Validation runs through validateReadOnlySql(sql, dbType) in lib/database/sql-validator.ts, which replaced the old regex keyword blocklist (DANGEROUS_SQL_KEYWORDS). The old list was trivially bypassable via comments, write-via-function, or stacked statements.

import { validateReadOnlySql } from "@/lib/database/sql-validator";

const sqlCheck = validateReadOnlySql(sql, dbType);
if (!sqlCheck.valid) {
  return NextResponse.json({ error: sqlCheck.error }, { status: 400 });
}

Two layers:

AST (primary) — parses the SQL with node-sql-parser (dependency ^5.4.0) using the per-dialect grammar (postgresql / mysql / transactsql / sqlite). Requires exactly one statement of type SELECT (CTEs included); anything else (multiple statements, non-SELECT) is rejected.
Heuristic fallback — when the parser throws (its grammars reject some valid SQL), heuristicReadOnly() strips comments/strings/quoted identifiers, then requires a single statement starting with SELECT/WITH and containing no write/DDL keyword (blocks stacked statements, data-modifying CTEs, SELECT INTO).

Read-Only Execution Enforcement

Defense-in-depth: after validation the route sets config.readOnly = true, so the adapter runs the query in a read-only context even if a write slipped past the validator:

PostgreSQL — read-only transaction
MySQL — read-only transaction + ROLLBACK
SQL Server — wrapped statement, always ROLLBACK
SQLite — connection opened with readonly: true

The readOnly flag is also set by /api/schema/sample-data. Schema introspection intentionally stays writable (internal callers leave the flag unset).

Every user query also gets a per-dialect statement timeout (QUERY_TIMEOUT.STATEMENT_MS, 120s): PostgreSQL statement_timeout (SET LOCAL, on the same round trip as set_config), MySQL max_execution_time, SQL Server requestTimeout. It is the backstop for when cancellation fails or is impossible.

Dirty Reads (READ UNCOMMITTED)

Sending dirtyRead: true lowers the isolation level of the read-only transaction the adapter already opens, so the query never waits on another transaction's locks. It is the portable form of SQL Server's WITH (NOLOCK), opt-in per user and off by default.

Engine	Mechanism
SQL Server	`tx.begin(sql.ISOLATION_LEVEL.READ_UNCOMMITTED)` — per-transaction, so it cannot leak through the pool
MySQL	`SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED` at connect (see below)
PostgreSQL	no-op — accepts READ UNCOMMITTED only as a synonym for READ COMMITTED, and under MVCC readers never block writers
SQLite	no-op — `PRAGMA read_uncommitted` only applies in shared-cache mode, which better-sqlite3 never enables

dirtyReadApplied on the response reports which of these happened, so a no-op is visible rather than silently implied.

Why it is not done in the SQL text. validateReadOnlySql rejects SET TRANSACTION ISOLATION LEVEL ... at three independent points — WRITE_KEYWORDS contains set (sql-validator.ts:45), heuristicReadOnly rejects an embedded ; (:60), and the AST path rejects more than one statement (:93). Per-table WITH (NOLOCK) hints were also rejected: they must be attached to every table reference and silently miss tables reached through views, and re-emitting the parsed statement bracket-quotes every identifier so the executed SQL would stop matching what the user sees. Isolation level covers every table, view and subquery at once and never touches the SQL.

Why MySQL sets it at connect time. MySQL raises ER_CANT_CHANGE_TX_CHARACTERISTICS (1568) if transaction characteristics change while a transaction is open, and executeRawQuery opens one with START TRANSACTION READ ONLY. Access mode and isolation level are orthogonal, so READ ONLY + READ UNCOMMITTED is valid — and is InnoDB's cheapest read path. Session scope is safe only because the adapter uses a per-request createConnection; with a pool the level would have to be reset before release.

Correctness warning. Results may be wrong, not merely stale: you can see rows from transactions that are still open and may roll back, and a row can be counted twice or skipped entirely if the engine moves it mid-scan. On SQL Server dirty reads can also make a previously-working query fail with error 601 ("Could not continue scan with NOLOCK due to data movement"), which surfaces as a generic database error. readOnly is never weakened — isolation governs visibility, never write capability.

POST /api/query/cancel

Kills an in-flight query started by /api/query/execute.

Request

{ queryId: string }   // the UUID sent with the execute request

Response

{ success: true, data: { status: CancelStatus, message?: string } }

`status`	Meaning
`cancelling`	The kill was requested. Not `cancelled` — the authoritative outcome is how the execute request resolves, and a kill can still be refused
`already_cancelled`	A cancel was already issued for this query
`not_cancellable`	This engine cannot stop a running query (SQLite)
`already_finished`	No such in-flight query — the ordinary completion race, so `200` rather than `404`

403 when the query belongs to another user; 400 for a missing or malformed queryId.

How the kill reaches the database, per engine — plus the registry's process-local limitation and the cooperative introspection variant — is documented in the Query Cancellation section of CLAUDE.md.

Audit Logging

Every execution (success and failure) is recorded via fire-and-forget logQuery() (lib/query-log.ts). It writes to the app DB table query_log (migration 005_query_log.sql) when the app DB is enabled, otherwise to logs/query-log.jsonl (lib/query-log-file.ts). Credentials are never logged — QueryLogEntry has no field for them. Logged fields include user/connection id, database type, SQL, optional question/source, row count, duration, and (on failure) the sanitized error.

Database Connection

Uses database-specific adapters via the factory pattern:

import { DatabaseAdapterFactory } from "@/lib/database";

const adapter = DatabaseAdapterFactory.create(connection.type);
await adapter.connect(config);
const result = await adapter.executeQuery(sql);
await adapter.disconnect();

Supported databases: PostgreSQL (pg), MySQL (mysql2), SQL Server (mssql), SQLite (better-sqlite3).

Result Formatting

All values converted to strings
null → "NULL"
Date → ISO date string (date portion only)
Numbers → string representation

Error Messages

Specific error messages for common issues:

// Column not found
"Column not found: column \"xyz\" does not exist"

// Table not found
"Table not found: relation \"abc\" does not exist"

// Other errors
error.message

Example Usage

const response = await fetch('/api/query/execute', {
  method: 'POST',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify({
    sql: "SELECT name, email FROM customers LIMIT 10",
    connection: {
      host: "localhost",
      port: "5432",
      database: "mydb",
      username: "user",
      password: "pass"
    }
  })
});

const { columns, rows, rowCount, executionTime } = await response.json();
// columns: ["name", "email"]
// rows: [["John", "john@example.com"], ["Jane", "jane@example.com"]]

Query Flow Diagram

User Input
    │
    ▼
┌─────────────────────────┐
│ POST /api/query/generate │
├─────────────────────────┤
│ 1. Validate input       │
│ 2. Call OpenAI API      │
│ 3. Parse response       │
│ 4. Return SQL           │
└───────────┬─────────────┘
            │
            ▼
    User reviews SQL
            │
            ▼
┌─────────────────────────┐
│ POST /api/query/execute  │
├─────────────────────────┤
│ 1. AST read-only check  │
│ 2. Connect (read-only)  │
│ 3. Execute query        │
│ 4. Log + format results │
└───────────┬─────────────┘
            │
            ▼
    Display results

POST /api/query/enhance

Location: app/api/query/enhance/route.ts

Improves natural language queries with specific schema details.

Request

interface EnhanceRequest {
  query: string;              // Original natural language query
  vectorStoreId?: string;     // OpenAI vector store ID for schema context
  databaseType?: string;      // Database type
}

Response

interface EnhanceResponse {
  enhancedQuery: string;      // Improved version of input query
  improvements: string[];     // List of improvements made
}

Behavior

Takes a vague query (e.g., "show revenue by month")
Uses schema context to add specific table/column names
Returns enhanced query with detailed instructions about tables, columns, and aggregations
Rate limiting and BYOK support

POST /api/query/revise

Location: app/api/query/revise/route.ts

Fixes SQL queries that failed during execution.

Request

interface ReviseRequest {
  originalQuestion: string;   // User's initial question
  generatedSql: string;       // The SQL that failed
  errorMessage: string;       // Database error message
  databaseType: string;       // Database type
  vectorStoreId?: string;     // Schema context
}

Response

interface ReviseResponse {
  sql: string;                // Corrected SQL query
  explanation: string;        // Why it was fixed
  confidence: number;         // 0-1 confidence score
}

Behavior

Analyzes the error message from the failed query
Searches schema context for correct table/column names
Generates corrected SQL addressing the specific error
Rate limiting and BYOK support

POST /api/query/followup

Location: app/api/query/followup/route.ts

Processes follow-up questions on query results.

Request

interface FollowUpRequest {
  followUpQuestion: string;   // User's follow-up question
  originalQuestion: string;   // Initial query
  generatedSql: string;       // SQL that generated results
  resultColumns: string[];    // Column names from results
  resultRows: string[][];     // Result data
  totalRowCount: number;      // Total rows returned
  vectorStoreId?: string;     // Schema context
  databaseType: string;       // Database type
}

Response

// Can be one of two types:
interface FollowUpQueryResponse {
  responseType: 'query';
  sql: string;                // New SQL query
  explanation: string;
  confidence: number;
  warnings: string[];
}

interface FollowUpExplanationResponse {
  responseType: 'explanation';
  text: string;               // Analysis/explanation text
  confidence: number;
}

Behavior

Builds markdown table context from the original results
Determines if follow-up needs a new query or just analysis
If query: generates new SQL based on original context + follow-up
If explanation: provides analysis text without generating SQL
Rate limiting and BYOK support

FilesExpand file tree

query-endpoints.md

Latest commit

History

query-endpoints.md

File metadata and controls

Query Endpoints

POST /api/query/generate

Request

Reasoning Effort

Response

Token Usage (usage)

Behavior

System Prompt

Learning Prompt Sections ("Learn from previous queries")

Fallback Parsing

Error Handling

Example Usage

POST /api/query/execute

Request

Response

Security Validation (AST-based)

Read-Only Execution Enforcement

Dirty Reads (READ UNCOMMITTED)

POST /api/query/cancel

Request

Response

Audit Logging

Database Connection

Result Formatting

Error Messages

Example Usage

Query Flow Diagram

POST /api/query/enhance

Request

Response

Behavior

POST /api/query/revise

Request

Response

Behavior

POST /api/query/followup

Request

Response

Behavior

Related Documentation

Token Usage (`usage`)