Case Study · Full Stack / AI
SQLCopilot
A web app that finds slow SQL before it ships — paste a query and get plain-English performance findings, a health score, and concrete fixes in seconds, without ever connecting to a database.
The problem
Most slow SQL is slow for a small set of well-known reasons — SELECT *, leading-wildcard LIKE, cartesian joins, NULL-unsafe NOT IN, functions on indexed columns. The trouble is that catching them usually means running EXPLAIN against a real database, knowing what to look for, and having production-like data. That friction pushes the problem downstream, so it surfaces in code review if you're lucky and in a production incident if you're not.
SQLCopilot does the analysis on the query text itself: paste a query and get plain-English findings, a health score, and concrete fixes — no database connection, no setup. Existing linters tend to be IDE-bound, single-dialect, or light on actionable explanation; I wanted something dialect-aware for Postgres and MySQL that pairs a deterministic rule engine with optional AI explanations.
How it works
Ingest & parse — pure text, never the database
The query text is parsed by node-sql-parser into a normalized, dialect-aware AST (Postgres or MySQL), with a normalization layer that smooths over per-dialect AST quirks. Nothing ever connects to or executes against a database — the analyzer packages contain no DB-connection code at all, so it's safe to paste a production query.
Detect & score — a deterministic rule engine
A rule engine runs 25 rules — one file per rule, versioned as a rule set — over the AST. Each finding carries a severity, and the health score is a capped, severity-weighted sum (info 1, low 3, medium 7, high 15, critical 30) on a 0–100 scale where lower is better.
For deeper insight you can paste EXPLAIN output — Postgres EXPLAIN (FORMAT JSON) or MySQL EXPLAIN FORMAT=JSON — which is parsed into a normalized plan and run through plan-level detectors such as sequential scans.
Where the LLM fits — it explains, it never decides
The language model only explains a finding the deterministic engine has already produced; it never decides what's wrong or invents SQL, and its output is validated with destructive/injection patterns rejected. Explanations are cached in Postgres, and a separate retrieval-grounded assistant answers product questions. The frontend is a static Next.js 15 export on Cloudflare Pages; the NestJS API runs on Render, with Supabase Postgres (via Drizzle) holding accounts, history, and the LLM cache.
Key decisions & tradeoffs
Static text analysis — never touch the user's database
Why: Zero setup, nothing to authenticate, and it's safe to paste a production query. It removes the single biggest adoption barrier for this kind of tool: needing live database access.
Tradeoff: Without real table sizes, indexes, or row counts the findings are heuristics — a flagged "scan" may be perfectly fine on a tiny table. The optional EXPLAIN input is how I close that gap rather than connecting to a live planner.
“AI explains, never decides”
Why: Keeping detection in deterministic rules makes results reproducible and CI-testable; the LLM is a UX layer that phrases the why and the fix, not the engine that judges correctness.
Tradeoff: It's more engineering up front — 25 hand-written rules plus a 200-case accuracy corpus — instead of letting a model decide what's wrong.
Split hosting across free tiers (Cloudflare Pages + Render + Supabase)
Why: The whole stack runs at $0 with no credit card while the product is in beta and the idea is being validated.
Tradeoff: The API cold-starts (~50s after idle, mitigated with an uptime ping) and the free LLM quotas are small — acceptable trade-offs for a beta, not for scale.
Outcome
- 25 deterministic rules over a normalized AST, dialect-aware for Postgres and MySQL.
- Findings validated by a 200-case accuracy corpus, gated in CI as a blocking check (accuracy floor 0.80).
- Live in free beta: stored queries are envelope-encrypted at rest (pgcrypto, per-org key), PII and secrets are scrubbed (emails, SSNs, Luhn-checked cards, JWTs, API keys), and it never asks for database credentials.
- Free tier gives 3 analyses/day; accounts add saved history, PDF reports, and AI explanations.
What I'd improve next
- Wire up real readiness checks — /readyz is currently a stub that will ping the database and Redis.
- Finish the background worker for asynchronous analysis and scheduled data retention.
- Raise the CI accuracy-corpus floor toward 0.90 as the corpus grows.