feat(production): SQL-agent production layer — tracing, exec-accuracy evals, AST guardrails, read-only data access, cost, graceful degradation

[AttiR]

Goal

Make InsightIq safe to point at a real database and provable on
every deploy. The agent generates executable SQL, so the production
layer is mostly safety + observability.

Layer 1 — Observability (Langfuse)

• pip install langfuse; keys as Container App secrets
• @observe the pipeline: question -> SQL gen -> validate -> execute -> insight
• Log on the trace: generated SQL, exec time (ms), row count,
  tokens, cost, retry count, guardrail verdict
• Tag trace: cache_hit, degraded, blocked
• flush traces on SIGTERM (graceful shutdown), not per request

Layer 2 — Evaluation (execution accuracy, CI-gated)

• tests/eval/pairs.jsonl — 15 labelled question + gold SQL
• Grade by EXECUTION ACCURACY: run generated SQL and gold SQL,
  compare result sets (order-insensitive). NOT string match.
• test_exec_accuracy.py -> fails CI if accuracy < 0.90
• test_guardrail_block.py -> every malicious prompt is blocked (must = 1.0)
• runs in ci.yml on every PR; eval badge in README

Layer 3 — SQL guardrails (model-output safety)

• Parse generated SQL with sqlglot (AST), not regex
• Allow exactly one statement; must be a SELECT (reject CTE-wrapped writes)
• Table + column whitelist enforced by walking the AST
• Reject if any DDL/DML node present (Drop/Delete/Update/Insert/Alter)
• Injection scan on the natural-language question before the LLM call
• Blocked query -> logged + clear user error, never executed

Layer 4 — Data access & security (defense in depth) [NEW]

• App connects via a READ-ONLY Postgres role (GRANT SELECT only)
• SET statement_timeout per query (e.g. 5s)
• Force a LIMIT on every result set (append if missing)
• PII column denylist — block/mask sensitive columns
• query_audit table: user, question, sql, verdict, rows, ts
• Least-privilege: the write role is never used by the request path

Layer 5 — Cost

• token + execution cost per query on the trace + in response
• daily/monthly spend aggregate; alert if daily > BUDGET_USD
• per-account cap (ties to 3-runs-per-account)

Layer 6 — Reliability (graceful degradation)

• DB timeout -> "query took too long, try narrowing it"
• Unparseable / invalid SQL after self-heal (3x) -> clear message, no 500
• Zero rows -> "no matching data" (not an empty error)
• Every failure mode returns a typed, user-facing message

Layer 7 — Caching (optional, pays for itself)

• Normalise the question, hash -> cache (result + SQL) with TTL
• Cache hit skips the LLM call; tag trace cache_hit=true

Operations — scale-to-zero (Container Apps)

• minReplicas 0 (idle = $0); HTTP scale rule
• Liveness/readiness probe on /health
• Warm-up notice on login page + README (cold start != broken)
• Optional minReplicas 1 on backend for interview demos

Acceptance

• One query = one Langfuse trace with SQL, time, rows, cost
• CI blocks merge if exec accuracy < 90% or any malicious prompt runs
• A generated DROP/DELETE is parsed, blocked, logged — and would fail
  anyway because the role can't write
• statement_timeout + LIMIT enforced; PII column request is refused
• Every failure mode shows a clear message, never a raw 500

Closes #53