ProdPilot

Catch production-breaking N+1 queries before they reach your users.

Comment

Inspiration

An innocuous N+1 query in a pull request could escalate to an entire production outage when actual traffic amplifies each database call. This is why we have created ProdPilot; to bring the risk of such bugs into light before a pull request even goes to production: not only “this loop is risky” but “this code could cause increased queries, saturated pools, and slower users.”

What It Does

ProdPilot scans for nested-loop database calls from the code of a pull request. It then integrates that information with synthetic production data to give a deterministic projection of:

  • Projected number of queries
  • Projected QPS
  • Pool utilization
  • Latency effect
  • Risk threshold breach

Finally, it creates a PR comment in GitHub with an assessment of the severity of the issue, a suggestion to fix it, and a description of its impact on users. The information is saved in a Gradio dashboard.

How We Built It

We implemented the system as a multi-stage pipeline:

  1. Code Agent: uses Python ast to detect database call-like expressions within nested loops.
  2. Metrics Agent: loads unambiguously labeled artificial telemetry data for demo purposes.
  3. Calculator: does all the math for query counting and impact estimation in plain Python—not in any LLM.
  4. Risk, Recommendation, and Impact Agents: optionally employ structured JSON outputs of GPT-5.6 to interpret the already calculated facts—falling back is always safe.
  5. FastAPI + PyGithub + smee.io: receives GitHub pull-request webhooks, retrieves the modified files, runs the pipeline, and leaves markdown comments on the PR.
  6. Gradio Dashboard: shows the saved history of PR analysis with severity-colored output.

Codex helped with scaffolding, testing, debugging Windows environment issues, setting up GitHub webhooks, fixing dependencies, documenting and preparing the demo.

Challenges We Faced

Our major challenge was separating deterministic engineering calculations from LLM reasoning. The latter can never invent any numbers like query counts, QPS or thresholds.

In addition, we addressed practical issues related to integration: Windows virtualization, compatibility of the Gradio dependency, smee tunneling, webhook handling, permissions of the GitHub token, and OpenAI API limits. The fallback was a key architectural choice since it made it possible to keep the core pipeline testable without having access to LLMs externally.

Our Takeaways

We've discovered that good developer AI tools require not only intelligence but reliability as well. AST parsing and deterministic calculations give us reproducible results, whereas LLMs generate readable interpretations and actionable recommendations. Together, this provides a better experience for the developer.

Built With

  • fastapi
  • github
  • github-webhooks
  • gradio
  • pygithub
  • pytest
  • python
  • pythonast
  • smee.io
  • uvicorn
Share this project:

Updates