AI Chat and AI Agent

AI Chat — conversational SQL assistant

POST /api/ai/chat is a multi-turn conversation: the client sends the whole message history on every call (state lives on the client, typically localStorage), keeping the server stateless. The model sees the current database's schema and can suggest DDL and DML, not just SELECT — but never executes anything itself: SQL blocks come back separate from the prose so the UI can show a "Run" button next to each.

curl -X POST $URL/api/ai/chat -H "X-Token: $TOK" -d '{
  "messages": [{"role": "user", "content": "Show the 10 most recent orders"}],
  "flavor": "postgresql"
}'

The response carries the model's full text (markdown), the schema it was shown (schema, for transparency), and the parsed SQL blocks classified as select / dml / ddl / other:

{
  "message": "Here's the query...",
  "blocks": [{"sql": "SELECT * FROM orders ORDER BY created_at DESC LIMIT 10", "kind": "select"}],
  "schema": "orders(id, customer_id, created_at, ...)",
  "model": "gpt-4o-mini"
}

A streaming variant exists too — POST /api/ai/chat/stream — same logic, but the reply streams out as it's generated instead of waiting for the full text.

AI Agent — read-only exploration

POST /api/ai/agent works differently: it isn't a conversation, it's a single request inside which the model takes several steps on its own, calling tools until it has enough to answer. Good for exploratory questions like "how many active users do we have — look in whichever table has user data" or "compare last week's revenue to the week before", where you don't know upfront which tables or queries you'll need.

curl -X POST $URL/api/ai/agent -H "X-Token: $TOK" -d '{
  "prompt": "How many active users are in the database?"
}'

The only available tools are list_tables, describe_table, and run_select — nothing else: there is no tool for INSERT/UPDATE/DELETE/DDL at all, so even a hallucinating model has no way to touch data. Additional guardrails:

• an iteration cap — 6 by default, overridable via max_iters in the request;
• automatic LIMIT injection when the model forgets to bound a SELECT (capped at 50 rows per step);
• a 90-second timeout for the whole request;
• the agent runs with the caller's own privileges (normal RBAC) — there's no separate per-tool authorization.

The response is the final text answer plus a full transcript of every step (what the model asked, what each tool returned) — handy for auditing:

{
  "answer": "There are 1,842 active users.",
  "iterations": 3,
  "transcript": [
    {"step": 1, "kind": "tool", "tool_name": "list_tables"},
    {"step": 2, "kind": "tool", "tool_name": "run_select", "tool_input": {"sql": "SELECT COUNT(*) FROM users WHERE status='active'"}},
    {"step": 3, "kind": "llm", "role": "assistant", "content": "There are 1,842 active users."}
  ]
}

Every call to /api/ai/agent starts a fresh session; if you need a multi-turn conversation with memory across HTTP calls, that's on the client to manage (same as AI Chat).