QueryPilot

QueryPilot: stop slow SQL before it hits prod, and keep AI agents on a leash.

Comment

Inspiration

Every AI coding assistant can write SQL. Almost none of them can tell you whether it will melt your database in production.

We kept watching teammates merge "working" PRs that shipped full table scans on million-row tables. The tools to catch these issues exist, but they live behind manual EXPLAIN ANALYZE sessions that nobody runs until after the outage.

We wanted the check to run automatically, in CI, in seconds, and to be safe enough that an autonomous agent could run it without risking the schema.

What It Does

QueryPilot is a CLI and MCP server that catches slow database queries before they reach production.

  • Execution Plan Analysis: Analyzes PostgreSQL EXPLAIN (FORMAT JSON) plans and renders a color-coded execution tree in the terminal.
  • Anti-Pattern Detection: Flags critical issues, including sequential scans on large tables, unindexed sorts, Cartesian joins, and expensive nested loops.
  • AI-Powered Fixes: Generates concrete SQL rewrites and exact CREATE INDEX DDL using Groq, Gemini, or OpenAI.
  • Prisma Schema Parsing: Parses .prisma schemas standalone, without a Prisma client dependency, to give the LLM real table context.
  • CI Regression Gating: Compares plan costs against a .baseline.json snapshot and fails pipelines when regressions exceed a configurable threshold.
  • Zero-Trust Governance: Uses ArmorIQ to enforce read-only plan inspection while cryptographically blocking unauthorized schema mutations.
  • MCP Integration: Exposes explain_query and apply_index tools through a Streamable HTTP MCP server on port 3333.
  • PR-Ready Reports: Generates GitHub-flavored Markdown summaries with baseline-versus-current cost deltas.

How We Built It

  • Core Stack: TypeScript, Node.js 18+, Commander.js, and pg for PostgreSQL connectivity.
  • Plan Analysis: A recursive plan tree walker in src/utils/planAnalyzer.ts computes costs, estimated rows, and node-level diagnostics.
  • Terminal Visualization: src/utils/planPrinter.ts renders execution plans with ANSI severity coloring.
  • LLM Suggestion Engine: src/llm/suggest.ts supports multiple AI providers.
  • Schema Context: src/db/prismaParser.ts parses Prisma schemas without requiring a Prisma client.
  • Security Layer: src/security/armoriq.ts integrates the ArmorIQ policy engine, backed by armoriq.yaml and CSRG intent tokens.
  • MCP Server: src/mcp/server.ts exposes tools with constant-time bearer token validation.
  • Regression Detection: src/utils/baseline.ts and src/actions/compare.ts handle baseline persistence and cost comparisons.
  • Testing: Vitest suite with 41 passing unit and integration tests.
  • CI/CD: GitHub Actions workflow and a GitLab CI mirror for cross-platform delivery.

Challenges We Ran Into

  • Reliably parsing PostgreSQL's nested JSON execution plans, including subplans and CTEs, without flagging harmless scans.
  • Keeping LLM suggestions grounded in actual schema context instead of hallucinated columns, which required standalone Prisma parsing.
  • Designing ArmorIQ policies that allow read-only diagnostics while blocking mutations through cryptographic intent tokens rather than simple string checks.
  • Making CI gating deterministic and fast enough that developers would not bypass it.
  • Supporting offline mock mode so demos and tests could run without a live database.

Accomplishments We're Proud Of

  • Built a working end-to-end proof of concept where one command turns a slow query into a diagnosed, fixable, and gated result.
  • Achieved 41 passing tests covering plan analysis, baseline comparison, and policy enforcement.
  • Implemented zero-trust governance that visibly approves explain_query while blocking apply_index.
  • Created an offline demo using sample execution plan JSON, allowing reviewers to explore the tool without PostgreSQL.
  • Maintained a clean separation between deterministic analysis and probabilistic AI suggestions, keeping feedback fast and trustworthy.

What We Learned

  • AI speed without verification is just faster technical debt.
  • Deterministic analysis should always come before LLM suggestions, both for cost efficiency and trust.
  • Zero-trust beats "trust but verify" when autonomous agents interact with production data.
  • Writing planning documents before coding kept the scope honest and prevented feature creep.
  • Shipping a narrow, runnable proof of concept beats building a broad, half-finished platform.

What's Next for QueryPilot?

  • Real-Time Query Interception: Build a PostgreSQL wire protocol proxy for live query interception.
  • GitHub App Integration: Comment on pull requests and suggest fixes directly.
  • Multi-Database Support: Add execution plan support for MySQL and SQLite.
  • Safer Index Migrations: Generate automated index migrations with rollback safety.
  • Team Dashboard: Build a web dashboard for tracking query health trends across teams.

Built With

Share this project:

Updates

Submission history