Ask a business question in plain English. Get the SQL, a chart, and a written insight in seconds.
InsightIQ turns a natural-language question into a parameter-safe SQL query, runs it against a live PostgreSQL database, picks an appropriate chart, and writes a short interpretation — with the generated SQL always shown for inspection. It is built as a production system, not a demo: every query is traced, cost-metered, audited, and rate-limited, and the whole thing scales to zero when idle.
Live: https://insightiq-frontend.orangemeadow-25c8b891.westeurope.azurecontainerapps.io
First load after idle takes ~30–60s. The apps scale to zero, so the first request wakes them; this keeps hosting near $0. Open the link a minute before a demo — sign-in wakes the backend automatically.
- What it does
- Production posture
- Threat model
- How a query flows
- System architecture
- Engineering decisions
- Data model
- Observability & operations
- Testing
- Security & secrets
- Cost
- Limitations & hardening roadmap
- Running it
- Project layout
Managers at SaaS companies are locked out of their own data. "Which customers are at churn risk?" means writing SQL, or waiting two days for an analyst. Analysts, in turn, lose most of their time to ad-hoc query requests instead of strategic work.
InsightIQ removes that dependency. A non-technical user asks a question; the system generates and executes the SQL, chooses the chart that fits the data shape, and returns a written business interpretation. The query is shown alongside the answer, so a technical user can verify exactly what ran.
The design principle is narrow and deliberate: automate the retrieval and synthesis, never hide the query. Trust in a text-to-SQL system comes from transparency, not from asking the user to take the answer on faith.
The interesting problem here is not "call an LLM to write SQL" — that is a weekend. The problem is doing it safely against a real database, observably, and within a cost ceiling. Everything below is implemented and running today.
| Concern | What's in place |
|---|---|
| Observability | Langfuse trace per query — question, generated SQL, execution time, row count, retry count, cost_usd. The trace is the first place I look when an answer is wrong. |
| Cost control | Per-query token cost metered onto the trace and into query_audit; daily spend tracked in daily_cost_spend with a webhook alert past BUDGET_USD_DAILY; a scheduled job opens a GitHub issue if Anthropic spend clears $5/day. |
| Write prevention | A read-only guard validates every generated statement before execution: comments are stripped, the first token must be SELECT, and a secondary scan rejects DROP/DELETE/INSERT/UPDATE/TRUNCATE/ALTER anywhere in the string (catches SELECT 1; DROP TABLE …). |
| Self-healing | On execution error, the failed SQL and the database error are fed back to the model and retried, capped at 3 attempts. Retry count is persisted and surfaced — a rising retry rate is an early signal of prompt drift. |
| Abuse limits | Per-user query budget enforced with a single atomic UPDATE … WHERE total_queries < query_limit RETURNING … — no Redis, no check-then-increment race. |
| Caching | Query results (rows, columns, explanation) are stored as JSONB in query_history; replaying a past query is a free Postgres read, not another model round-trip. |
| Operations | Scale-to-zero on Azure Container Apps ($0 idle); zero-downtime rolling deploys; smoke-test failure triggers automatic revision rollback. |
| Secrets | Zero credentials in the repo. OIDC federated login to Azure (no stored service-principal password); runtime secrets injected as Container App secretref. |
Items I have not built yet, and why they matter, are listed honestly under Limitations & hardening roadmap. The most important of them is a dedicated read-only database role — today the guard is the only thing standing between a bypassed validation and a write.
InsightIQ takes untrusted natural language and turns it into code that runs against a production database. That is the whole risk in one sentence. There are three trust boundaries, and a control at each.
1 — The user's question is untrusted. It can carry injection ("ignore the schema and …"). The question is never string-concatenated into SQL; it is an input to the model, and the generated SQL is validated independently of the question. The natural-language text never reaches the database.
2 — The model's output is untrusted. A non-deterministic code generator must be treated as hostile, not helpful. Every generated statement passes the read-only guard before it runs: comment-stripped token parse (first token must be SELECT) plus a keyword scan that rejects DDL/DML and semicolon-separated injection. This is token parsing rather than a naive regex precisely because re.match(r'SELECT', …) is defeated by leading whitespace, comments, or encoding tricks.
3 — Database access is the blast radius. Connections are TLS-only (sslmode=require); a per-user query budget bounds abuse; a statement-timeout guard bounds runaway queries. Known gap: the application still connects with a single write-capable role, so the guard at boundary 2 is currently the only write-prevention. The planned fix — a dedicated read-only Postgres role — moves write-prevention into the database itself, so a guard bypass degrades to "permission denied" rather than data loss. This is the top item on the roadmap, and I'd rather name it than imply it's already there.
User question
│
▼
Schema injection ──── full Postgres schema + 3 sample values per column,
│ baked into the Claude system prompt
▼
Claude generates SQL
│
▼
Read-only guard ──── strip comments → first token must be SELECT →
│ secondary scan rejects DROP/DELETE/INSERT/UPDATE/TRUNCATE/ALTER
▼
Execute via asyncpg pool (statement-timeout guard)
│
┌──┴──────────────────┐
│ Execution error? │ Yes ──► error + failed SQL fed back to Claude,
│ │ retried up to 3× with full context
└──┬──────────────────┘
│ No
▼
Trace + cost to Langfuse · row to query_audit · results cached to query_history (JSONB)
│
▼
Chart type derived from data shape:
time column present → line
1 string + 1 numeric ≤8 rows → pie
1 string + 1 numeric >8 rows → bar
everything else → table
│
▼
Response: SQL · rows · explanation · execution_ms · retry count · cost
Browser
│ HTTPS — managed TLS via Azure Container Apps
│
├─► Frontend Container App (nginx + React 18 + TypeScript)
│ Serves the static bundle only — no backend proxy.
│ VITE_API_BASE_URL is baked into the bundle at build time;
│ the browser calls the backend's ACA URL directly.
│
└─► Backend Container App (FastAPI + asyncpg + Claude)
min-replicas: 0 (scale to zero — wakes on first HTTP request)
max-replicas: 3 (autoscale on CPU)
│ Langfuse (traces + cost)
▼
Neon Serverless PostgreSQL
asyncpg pool (min=2, max=10) · sslmode=require
CI/CD
Pull request → ruff · pytest · tsc · Trivy
Merge to main → Neon migrations → deploy backend → deploy frontend → smoke test → rollback on failure
Daily 09:00 → Anthropic spend check → open a GitHub issue if daily cost > $5
Each of these is a decision I'd defend in a design review, stated with the alternative I rejected and the cost I accepted.
Docker Compose gives services a shared bridge network, so http://backend:8000 resolves. ACA does not: each Container App is network-isolated with its own public FQDN. An nginx proxy_pass http://insightiq-backend:8000 that works locally fails in production with a silent 502.
The fix is architectural, not a config tweak. In production, nginx serves static files only; the React bundle calls the backend at its full ACA URL (injected as VITE_API_BASE_URL at build time), and the backend CORS allow-list names the frontend's ACA domain (injected as FRONTEND_URL at runtime).
| Docker Compose (local) | Azure Container Apps (prod) | |
|---|---|---|
| Frontend → Backend | nginx proxy_pass |
React calls full HTTPS ACA URL |
| Backend hostname | bridge DNS | public ACA FQDN |
| CORS | not needed (same origin) | required (separate domains) |
| nginx role | reverse proxy + SPA | SPA server only |
A failed query and its database error are appended to the conversation and retried. The cap is deliberate: a fourth attempt rarely succeeds where three failed — by then the issue is the question (ambiguous, references absent data) or a schema mismatch needing a human, not more tokens. Retrying further just burns credits and lengthens the wait before an inevitable failure. The retry count is persisted; monitoring its rate is a cheap proxy for prompt-quality drift.
Results are stored as JSONB on first execution; replaying via GET /api/history/:id is a free Postgres read. Without it, every history click would be a full model round-trip — at sonnet-4-5 pricing, replaying a ten-query session could cost as much as running it fresh. The cache hit is shown in the UI so the saving is visible.
UPDATE users
SET total_queries = total_queries + 1
WHERE id = $1 AND is_active = TRUE AND total_queries < query_limit
RETURNING id, total_queries, query_limitNo rows returned means the limit is hit; a row means the request is allowed. The check and increment are one statement, so concurrent requests from the same user cannot both pass. A distributed lock or a Redis counter would be the textbook answer; at this scale the database does it correctly with zero extra infrastructure.
The full schema — tables, columns, types, foreign keys, and 3 sample values per column — is injected into every system prompt. The sample values matter most: without them the model invents domain vocabulary; with them it knows plan is one of starter/pro/business/enterprise. The schema is cached in-process with a 5-minute TTL — sufficient at this scale, and invalidation falls out naturally on pod restart.
Validation strips comments (--, /* */), extracts the first token, and compares it; a secondary scan rejects dangerous keywords anywhere in the string. A regex on the raw query is bypassable with whitespace, comments, or encoding; token parsing is not, and the keyword scan catches semicolon-separated injection. (This is the application-layer control. The database-layer control — a read-only role — is on the roadmap; see below.)
asyncpg speaks PostgreSQL's binary protocol and is natively async, so it joins FastAPI's event loop without the thread-pool workarounds psycopg2 needs. The pool is created during lifespan startup, so connections are warm before the first request.
customers 1,000 rows — company, plan, country, health_score, churn date
├── subscriptions MRR, billing cycle, status, cancellation
├── events ~50K product usage: login, feature_used, report_export, …
├── invoices ~3K amount, status: paid | overdue | failed
└── support_tickets ~4K priority: low | medium | high | critical
users registered users — google_id, email, query budget
└── query_history every question, generated SQL, results (JSONB), timing, retries
Views:
customer_health customers + active subscription + 30-day events + open tickets
monthly_mrr pre-aggregated MRR by month across active subscriptions
Seed data is reproducible (seed=42): 1,000 customers and ~60K related rows via Faker.
I treat the alert thresholds below as informal SLOs — the lines past which something is wrong and I want to know before a user tells me.
Tracing — Langfuse on every query: the SQL, the timing, the retries, the cost. Reconstructing "why was this answer wrong" is a trace lookup, not a log grep.
Azure Monitor — email alerts on backend CPU > 80% for 5 min, HTTP 5xx > 10 in 5 min (sev 1), and P95 latency > 10s.
Cost monitor — a scheduled GitHub Action queries the Anthropic usage API and opens an issue automatically if daily spend clears $5; the issue ships with a SQL template to find unusual volume in query_history. An Azure budget emails at 80% and 100% of $30/month.
Deploys — rolling and zero-downtime; migrations run before either app updates; a failed smoke test triggers az containerapp revision deactivate for an automatic rollback.
CI runs on every PR: ruff lint, pytest, tsc typecheck, and a Trivy image scan.
Current unit coverage focuses on the parts where a bug is dangerous or silent: the read-only guard (12 cases, including comment and semicolon-injection bypasses), result serialisation and Pydantic boundaries, and auth — JWT verification, the atomic rate-limit path, and 401 handling. An execution-accuracy evaluation gate is the next addition (see roadmap); it is not in CI yet, and I'd rather say so than badge it.
No credentials live in the repository.
Source (public) GitHub Secrets (CI/CD) Azure runtime (Container App)
─────────────── ────────────────────── ─────────────────────────────
Source code AZURE_CLIENT_ID (OIDC) ANTHROPIC_API_KEY secretref:
Dockerfiles NEON_DATABASE_URL JWT_SECRET_KEY secretref:
nginx.conf ANTHROPIC_API_KEY DATABASE_URL secretref:
Workflows JWT_SECRET_KEY GOOGLE_CLIENT_ID secretref:
GOOGLE_CLIENT_ID FRONTEND_URL plain env
FRONTEND_URL
Azure auth is OIDC federated, scoped to this repository and branch — GitHub exchanges a short-lived token instead of holding a service-principal password. Runtime secrets are Container App secretref values, so they never appear in the plaintext revision spec. Auth is Google OAuth with server-side token verification and HS256 JWTs; no passwords are stored.
| Service | Monthly |
|---|---|
| Container Apps — backend (scale to zero) | ~$1–5 |
| Container Apps — frontend (scale to zero) | ~$1–3 |
| Neon Serverless PostgreSQL | $0 (free tier) |
| GitHub Actions | $0 (free tier) |
| Infrastructure total | ~$2–8 |
Model cost scales with volume. A measured production trace (full schema prompt, sonnet-4-5) costs ~$0.06 per generate_sql call (19k input tokens) and **$0.12** end-to-end when the self-healing agent retries once (38.5k prompt + 201 completion tokens, 17.45s total). The dominant term is the injected schema, so prompt caching or a slimmer schema is the lever toward ~$0.004 per query. Every query’s cost_usd lands on the Langfuse trace and in query_audit; daily_cost_spend drives the budget webhook. The per-user cap and JSONB caching bound marginal cost per visitor.
See docs/cost.md for the Langfuse dashboard walkthrough and SQL to compute live averages.
What a system doesn't do yet is as honest a signal as what it does. In rough priority order:
- Dedicated read-only database role. Today the app connects with a single write-capable role, so the read-only guard is the only write-prevention. Next step: a
GRANT SELECT-only role on the request path, so a guard bypass is stopped by Postgres itself, not just the application. Highest priority — it's the difference between two locks and one. - AST-based validation. Move the guard from token parsing to a
sqlglotAST walk. That enables a real table/column allow-list and closes CTE/encoding edge cases that token scanning can only approximate. - Forced
LIMIT+ PII column policy. Cap result-set size at the query layer so no question can return the whole table; deny sensitive columns by policy rather than by prompt. - Execution-accuracy eval gate in CI. A labelled question/gold-SQL set graded by result-set equivalence (not string match — two correct queries can look nothing alike), wired as a merge gate so a correctness regression blocks the deploy.
- Single-region data. One Neon instance, and the schema cache is per-pod in-process; a multi-region deployment would need a shared cache and a read-replica strategy.
- Quotas are per-user, not per-org. Fine for a portfolio; a real tenant model would meter and bill per organisation.
git clone https://github.com/AttiR/InsightIQ
cd InsightIQ
cp .env.example .env # ANTHROPIC_API_KEY, GOOGLE_CLIENT_ID, JWT_SECRET_KEY
# Postgres
docker compose up -d
psql $DATABASE_URL -f database/migrations/002_auth.sql
python3 database/seed.py # 1,000 customers + ~60K related rows (seed=42)
# Backend
cd backend
python3 -m venv venv && source venv/bin/activate
pip install -r requirements.txt
uvicorn main:app --reload --port 8000
# Frontend (new terminal)
cd frontend
npm install
npm run dev # http://localhost:5173Production deploy (automatic on merge to main):
./infra/provision.sh # one-time: ACA environment + OIDC federated credentials
./infra/alerts.sh your@email.com # Azure Monitor: CPU · 5xx · latency · budget
git push origin main # CI/CD takes it from hereDeploys are zero-downtime rolling updates; migrations apply before either Container App updates; a failed smoke test rolls back automatically.
Full tree
InsightIQ/
├── .github/workflows/
│ ├── ci.yml ruff · pytest · tsc · trivy (every PR)
│ ├── cd.yml migrate · deploy · smoke test · rollback (main)
│ └── cost-monitor.yml daily Anthropic spend check
├── backend/
│ ├── app/
│ │ ├── agent/sql_agent.py schema injection · generation · guard · healing · cache
│ │ ├── api/query.py POST /api/query · GET /api/history · GET /api/schema
│ │ ├── auth/
│ │ │ ├── router.py POST /api/auth/google · GET /api/auth/me
│ │ │ ├── service.py Google verify · JWT · rate limit (atomic UPDATE)
│ │ │ └── dependencies.py get_current_user dependency
│ │ ├── core/config.py Pydantic Settings · CORS · FRONTEND_URL
│ │ └── db/
│ │ ├── pool.py asyncpg pool · type codecs · timeout guard
│ │ └── schema_loader.py schema + sample values · 5-min TTL cache
│ ├── Dockerfile multi-stage · non-root (UID 1001) · healthcheck
│ └── requirements.txt
├── frontend/
│ ├── src/
│ │ ├── api/client.ts typed fetch · Authorization header · abort signals
│ │ ├── context/ AuthContext — Google sign-in · JWT · refreshUser
│ │ ├── hooks/ useQuery · useHistory (loadFromHistory)
│ │ ├── components/ TopNav · Sidebar · ChartView · DataTable · SQLViewer
│ │ └── pages/LoginPage.tsx split-screen · Google Identity Services
│ ├── Dockerfile multi-stage Node build · nginx runtime · non-root
│ ├── nginx.conf SPA routing · security headers · no backend proxy
│ └── tsconfig.json strict · noImplicitAny · noUnusedLocals
├── database/
│ ├── migrations/002_auth.sql users · query_history user_id + result_json
│ └── seed.py 1,000 customers · ~60K events · Faker · seed=42
├── infra/
│ ├── provision.sh ACA environment · OIDC federated credentials
│ └── alerts.sh Azure Monitor · CPU · 5xx · latency · budget
├── tests/
│ ├── test_sql_agent.py read-only guard (12 cases) · serialisation · Pydantic
│ └── test_auth.py JWT · rate limit (atomic mock) · auth 401
├── docker-compose.yml
└── .env.example