Text-to-SQL foundation: read-only views, chat_ro role, SqlValidator

[rghvgrv]

Parent

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

What to build

The security foundation for open-ended questions: user-scoped read-only database views, a dedicated read-only Postgres role restricted to those views, and a standalone SQL validator that enforces read-only/single-statement/scoped/bounded queries. This slice has no user-visible chat behavior yet — it is verified via unit tests and direct DB inspection — and is deliberately isolated from the router/orchestrator so the security boundary can be built and tested independently before anything wires an LLM to it (in #152).

Implementation Steps

1. Read-only views migration — Add a new EF Core migration under backend/splitzy-dotnet/Migrations/ (raw SQL in Up/Down, following the pattern in 20260220190326_AddExpenseCategoryEnum.cs) creating views v_my_expenses (per-expense rows: id, name, amount, category, created_at, paid_by_userid, group_id) and v_my_expense_splits (expense_id, userid, owed_amount) — both defined over the existing expenses/expense_splits tables with no additional filtering baked in (user-scoping is enforced by the validator's mandatory predicate, not by the view itself, since views can't take runtime parameters in Postgres without functions). Auto-applies via the existing db.Database.Migrate() call in backend/splitzy-dotnet/Application/Startup.cs::Configure.
2. Read-only role — In the same migration (or a follow-up raw-SQL migration), add guarded (IF NOT EXISTS) SQL to create a Postgres role chat_ro with SELECT granted only on v_my_expenses and v_my_expense_splits — no access to base tables or any other table. [HITL]: the role's password/connection string must be provisioned as a secret per environment (dev/prod) by whoever manages deployment secrets; this slice creates the role structurally but cannot supply real production credentials.
3. Read-only connection — Add a ChatReadOnlyConnectionString entry to LlmSettings or a new SqlSandboxSettings in backend/splitzy-dotnet/Extensions/SplitzyConfig.cs (following Chat foundation: streaming balance intent + router skeleton #146's LlmSettings pattern), and a factory (e.g. NpgsqlDataSource) registered in Startup.cs for opening connections as chat_ro, with CommandTimeout bound to SqlStatementTimeoutMs from Chat foundation: streaming balance intent + router skeleton #146.
4. SQL validator — Create backend/splitzy-dotnet/Services/Chat/SqlValidator.cs with (bool ok, string? reason) Validate(string sql): rejects unless the statement is a single SELECT (no ;, no multi-statement), contains no destructive/DDL keywords (INSERT, UPDATE, DELETE, DROP, ALTER, TRUNCATE, GRANT, COPY, pg_-prefixed functions), references only v_my_expenses/v_my_expense_splits, contains a userid = @userId-style scope predicate, and has a LIMIT clause at or below SqlRowLimit from Chat foundation: streaming balance intent + router skeleton #146's config.
5. Tests — Create backend/spllitzy-dotnet-tests/SqlValidatorTests.cs (NUnit, following existing test conventions): exhaustive cases covering valid queries, write attempts, multi-statement injection attempts, missing scope predicate, missing/oversized LIMIT, and references to non-whitelisted tables — all must be rejected except the valid case. Add a migration test (or manual verification note) confirming the views and chat_ro role are created cleanly against a test database.

Agent Routing

agent_routing:
  complexity_hint: complex
  required_capability: advanced
  parallel_safe: true
  cost_preference: balanced
  speed_preference: low
  ownership_scope:
    - backend/splitzy-dotnet/Migrations/
    - backend/splitzy-dotnet/Services/Chat/SqlValidator.cs
    - backend/splitzy-dotnet/Extensions/SplitzyConfig.cs
    - backend/spllitzy-dotnet-tests/SqlValidatorTests.cs
  verification:
    - dotnet test backend/spllitzy-dotnet-tests
    - dotnet ef database update against a local/test Postgres instance to confirm the migration applies cleanly

Technical Context Snapshot

Current stack in scope

• EF Core 8 + Npgsql for migrations and data access; Postgres as the RDBMS. Existing migration conventions in backend/splitzy-dotnet/Migrations/ mix EF-generated column changes and hand-written raw SQL (see AddExpenseCategoryEnum.cs).

Dependencies in scope

• Reuse: Npgsql/Npgsql.EntityFrameworkCore.PostgreSQL (already referenced in splitzy-dotnet.csproj) for the dedicated read-only connection — no new package needed for NpgsqlDataSource.
• New dependency additions allowed for this slice: no.

Architecture alignment

• This slice is intentionally decoupled from ChatOrchestrator/router — it only depends on Chat foundation: streaming balance intent + router skeleton #146 for the LlmSettings/config pattern to extend, not on any chat-intent code. Safe to build in parallel with Category/overall spend + transaction-count intents #147–Android/Expo chat screen #150.
• The security boundary here is structural (dedicated role + view whitelist + validator), not solely code-review-dependent — matches the resolved requirement for defense-in-depth SQL sandboxing.
• 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

• New DB objects: v_my_expenses, v_my_expense_splits views, chat_ro role. No changes to existing tables/columns.
• No API contract changes in this slice — SqlValidator and the read-only connection are not yet wired into any endpoint (that's Text-to-SQL execution: SQL_COMPLEX intent end-to-end #152).
• [HITL]: production secret for the chat_ro connection string must be added to each environment's config/secrets store before Text-to-SQL execution: SQL_COMPLEX intent end-to-end #152 can run against real infrastructure.

Acceptance criteria

• The migration creates v_my_expenses, v_my_expense_splits, and the chat_ro role cleanly on a fresh database, and chat_ro has no privileges beyond SELECT on those two views.
• SqlValidator.Validate rejects every disallowed case in the test suite (writes, multi-statement, missing scope, missing/oversized LIMIT, non-whitelisted tables) and accepts only well-formed, scoped, bounded SELECTs.
• No LLM or router code path yet depends on this slice — it is fully testable standalone.

Blocked by

• Chat foundation: streaming balance intent + router skeleton #146