E R A Y

PostgreSQL · Foundry Local · Evidence-first analysis

QueryPilot Local

An offline-first PostgreSQL execution-plan assistant that combines strict read-only controls, machine-readable plan analysis, deterministic diagnostics, and local knowledge retrieval.

Open live demo ↗ ← Back to Projects
Read-onlyAST validation, restricted database role, and bounded execution
EXPLAIN JSONRecursive plan normalization and evidence extraction
24 chunksSix local PostgreSQL knowledge documents
Top-3Foundry Local embeddings and cosine retrieval

The problem

PostgreSQL execution plans contain the evidence needed to diagnose slow queries, but their nested structure and planner terminology make them difficult to interpret quickly.

QueryPilot separates measurable plan facts from explanatory prose. It turns structured plan evidence into deterministic findings first, then retrieves relevant local documentation for additional context.

Safety contract

  • One read-only statement. Only a single SELECT or WITH ... SELECT is accepted.
  • Database-enforced limits. Analysis uses a SELECT-only role, read-only transaction, and three-second timeout.
  • Evidence before advice. Findings are derived from normalized plan data rather than free-form model output.
  • No automatic changes. Suggested SQL remains display-only and is never executed.

Implemented working slice

Analysis pipeline

querypilot / analysis lifecycle

01 · Guard SQL validation AST-based single-statement and read-only checks
02 · Execute Controlled EXPLAIN Read-only role, transaction, and timeout
03 · Normalize Plan parser Recursive EXPLAIN JSON tree and numeric evidence
04 · Diagnose Rule engine Indexes, nested loops, disk sorts, cardinality
05 · Explain Retrieval module Foundry embeddings, top-3 sources, validated report

Current milestone

  • FastAPI health and analysis endpoints backed by a seeded PostgreSQL demo environment.
  • Deterministic rules for missing-index signals, costly nested loops, disk-based sorts, and row-estimate errors.
  • Persisted NumPy cosine index built from Foundry Local embeddings.
  • Strict report validation, retrieved-citation allowlisting, numeric integrity checks, and deterministic fallback.
  • Unit, API, generation-fallback, and retrieval tests covering the working slice.

Explicit boundary

  • Local retrieval and Foundry Local text generation are not yet connected to the live analysis response.
  • Current API responses intentionally use the deterministic fallback path.
  • The retrieval check currently covers four curated cases, not a headline benchmark.
  • The Streamlit interface and 12-scenario evaluation remain the next milestone.

The project is still developed locally and is not presented as a public GitHub repository.

My contribution

  • Designed and implemented the safety-first analysis pipeline from SQL validation to structured reporting.
  • Built the recursive plan parser and deterministic diagnostic rules around measurable PostgreSQL evidence.
  • Integrated local embeddings, persistent retrieval, citation constraints, FastAPI endpoints, and automated tests.

QueryPilot Local began during the completed Microsoft Türkiye AI Innovators Summer Internship Program and continues as a local portfolio project.

Engineering stack

Python 3.11+ FastAPI Pydantic PostgreSQL Psycopg SQLGlot Foundry Local NumPy Docker Compose pytest Ruff Pyright