Text-to-SQL execution: SQL_COMPLEX intent end-to-end

[rghvgrv]

Parent

#145 — Ask Splitzy: personal spending chatbot (self-hosted LLM)

What to build

Wire the SQL sandbox from #151 into the live chat flow: a new SQL_COMPLEX router intent for open-ended questions the fixed intents can't answer, an executor that generates, validates, and runs the query, and synthesis of the resulting rows into a natural-language answer. This is transparent to both clients (#149, #150) — same endpoint and contract, the server just gets smarter about what it can answer.

Implementation Steps

1. Text-to-SQL prompt — Add a prompt template under backend/splitzy-dotnet/Services/Chat/Prompts/ documenting only the whitelisted view schemas (v_my_expenses, v_my_expense_splits from Text-to-SQL foundation: read-only views, chat_ro role, SqlValidator #151) — never the base tables — instructing the model to always include a userid = @userId predicate and a LIMIT.
2. Executor — Create backend/splitzy-dotnet/Services/Chat/TextToSqlExecutor.cs: calls ILlmClient.CompleteAsync (from Chat foundation: streaming balance intent + router skeleton #146) with the SqlModel, passes the result through SqlValidator.Validate (from Text-to-SQL foundation: read-only views, chat_ro role, SqlValidator #151), and — only if valid — executes it via the chat_ro connection factory (from Text-to-SQL foundation: read-only views, chat_ro role, SqlValidator #151) with the row LIMIT and SqlStatementTimeoutMs enforced, binding userId as a query parameter (never string-interpolated). Returns rows in a compact structure for synthesis, or a "couldn't run that safely, try rephrasing" signal if validation fails.
3. Router intent — Extend RouterResult's intent enum (from Chat foundation: streaming balance intent + router skeleton #146/Category/overall spend + transaction-count intents #147) with SqlComplex. Update the router prompt so questions that don't match a fixed intent (category/overall/count/balance) but are still clearly personal-finance-related route to SqlComplex; anything genuinely unrelated still routes to OutOfScope per Chat foundation: streaming balance intent + router skeleton #146.
4. Orchestrator wiring — Update ChatOrchestrator.AskAsync (from Chat foundation: streaming balance intent + router skeleton #146/Multi-turn conversation context (stateless history) #148) to dispatch SqlComplex to TextToSqlExecutor, feeding the returned rows (or the validation-failure message) into the same synthesis step used by the fixed intents.
5. Tests — Extend backend/spllitzy-dotnet-tests/ChatControllerTests.cs: an end-to-end SQL_COMPLEX scenario ("grocery expenses over $50 last Tuesday") returning correctly filtered rows; a cross-user isolation test proving the SQL path cannot surface another user's rows even via a crafted question; and a case where the generated SQL fails validation and the user receives the safe fallback message rather than an error or fabricated data.

Agent Routing

agent_routing:
  complexity_hint: complex
  required_capability: advanced
  parallel_safe: false
  cost_preference: balanced
  speed_preference: balanced
  ownership_scope:
    - backend/splitzy-dotnet/Services/Chat/TextToSqlExecutor.cs
    - backend/splitzy-dotnet/Services/Chat/Prompts/
    - backend/splitzy-dotnet/Services/Chat/RouterResult.cs
    - backend/spllitzy-dotnet-tests/
  verification:
    - dotnet test backend/spllitzy-dotnet-tests
    - Manual Swagger call with an open-ended question not covered by the fixed intents, confirming correct filtered results and correct refusal on a deliberately ambiguous/malformed question

Technical Context Snapshot

Current stack in scope

• Same backend stack as prior slices. This slice is the integration point between the LLM client (Chat foundation: streaming balance intent + router skeleton #146), router/orchestrator (Chat foundation: streaming balance intent + router skeleton #146/Multi-turn conversation context (stateless history) #148), and the SQL sandbox (Text-to-SQL foundation: read-only views, chat_ro role, SqlValidator #151) — no new infrastructure, purely wiring.

Dependencies in scope

• Reuse only: ILlmClient, SqlValidator, the chat_ro connection factory, and ChatOrchestrator, all from prior slices.
• New dependency additions allowed for this slice: no.

Architecture alignment

• The validator and read-only role remain the actual security boundary (per Text-to-SQL foundation: read-only views, chat_ro role, SqlValidator #151); this slice must not weaken that by, e.g., falling back to the app's normal read-write connection on validation failure — a failed validation must always produce the safe fallback message, never a retry against a more privileged connection.
• create-git-issue provides routing hints only; it must not assign concrete agent/model names.
• run-with-it remains the final runtime routing authority.

Integration touchpoints

• No client-visible contract change — same POST /api/chat/stream request/response shape from Multi-turn conversation context (stateless history) #148. Angular (Angular web chat UI #149) and Android (Android/Expo chat screen #150) clients require no changes to benefit from this slice.
• Depends on Text-to-SQL foundation: read-only views, chat_ro role, SqlValidator #151's views/role/validator and Multi-turn conversation context (stateless history) #148's history-aware orchestrator being in place.

Acceptance criteria

• An open-ended question outside the fixed intent set ("grocery expenses over $50 last Tuesday") returns correct, correctly-filtered results via the validated SQL path.
• A crafted or ambiguous question that would produce an invalid/unscoped query results in the safe fallback message, never raw errors or fabricated data.
• Cross-user isolation holds for the SQL path exactly as it does for the fixed-intent paths.

Blocked by

• Text-to-SQL foundation: read-only views, chat_ro role, SqlValidator #151
• Multi-turn conversation context (stateless history) #148