Skip to content
QueryPilotDocs

Architecture

QueryPilot is a pnpm + Turborepo monorepo with four applications and a set of shared packages.

QueryPilot architectureThe agent runs in your infrastructure next to the observed PostgreSQL database, reads statistics read-only, and sends telemetry outbound over HTTPS to the QueryPilot API. The API stores data in QueryPilot's own PostgreSQL, which the worker also uses for analysis, validation and its job queue. The web app reads everything through the API.Your infrastructureQueryPilot (self-hosted)PostgreSQLyour database · pg_stat_statementsAgentcollectors · bounded bufferplan capture · task runnerread-only SQL2 connections · timeoutsAPINestJS · /api/v1ingestion · findings · plansHTTPSoutbound onlyWebNext.js UIoverview · queries · plansRESTWorkeranalysis · validation · jobsPostgreSQLQueryPilot storageTypeORM
QueryPilot architectureThe agent runs in your infrastructure next to the observed PostgreSQL database, reads statistics read-only, and sends telemetry outbound over HTTPS to the QueryPilot API. The API stores data in QueryPilot's own PostgreSQL, which the worker also uses for analysis, validation and its job queue. The web app reads everything through the API.Your infrastructureQueryPilot (self-hosted)PostgreSQLyour database · pg_stat_statementsread-only SQL2 connections · timeoutsAgentcollectors · bounded bufferplan capture · task runnerHTTPSoutbound onlyAPINestJS · /api/v1ingestion · findings · plansRESTWebNext.js UIqueries · plansWorkeranalysisvalidation · jobsTypeORMPostgreSQLQueryPilot storage
Data flows one way: from the observed database, through the agent, to the API.
QueryPilot architecture
QueryPilot architectureThe agent runs in your infrastructure next to the observed PostgreSQL database, reads statistics read-only, and sends telemetry outbound over HTTPS to the QueryPilot API. The API stores data in QueryPilot's own PostgreSQL, which the worker also uses for analysis, validation and its job queue. The web app reads everything through the API.Your infrastructureQueryPilot (self-hosted)PostgreSQLyour database · pg_stat_statementsAgentcollectors · bounded bufferplan capture · task runnerread-only SQL2 connections · timeoutsAPINestJS · /api/v1ingestion · findings · plansHTTPSoutbound onlyWebNext.js UIoverview · queries · plansRESTWorkeranalysis · validation · jobsPostgreSQLQueryPilot storageTypeORM

Scroll or pinch to explore. Press Esc to close.

Components

Section titled “Components”

Agent (apps/agent)

Section titled “Agent (apps/agent)”

Runs on your infrastructure, next to the database it observes. It is configured entirely through environment variables and only ever connects outbound: to PostgreSQL and to the API. Nothing connects to the agent except optional health checks.

detect capabilities → register → collect on intervals → buffer → send
                                → heartbeat → poll for tasks
  • Observer pool: at most 2 connections, default_transaction_read_only=on, application_name=querypilot-agent, and a statement_timeout (5 s by default) on every session.
  • Collectors each read one family of system views and run on their own interval. A collector never overlaps itself, and a failing collector only affects itself.
  • Bounded buffer: capped by rows (10,000 by default), dropping the oldest samples first. Drops are counted and reported in the heartbeat.
  • Transport: bearer-token HTTPS to /api/v1/agent/*, gzip for bodies over 16 KB, exponential backoff (1 s → 60 s). A failed batch is resent with the same batchId, so the API can discard duplicates.
  • Task runner: polls the API every 5 seconds for on-demand work such as “capture this plan now”.
  • Health server on port 8082: /health/live, /health/ready (states exactly what is missing) and /metrics.

See Installing the agent.

API (apps/api)

Section titled “API (apps/api)”

A NestJS service under the /api/v1 prefix (probes and /metrics sit at unversioned paths).

  • Database registration, connection testing and capability detection
  • Agent management and the agent protocol (register, heartbeat, telemetry, tasks)
  • Idempotent telemetry ingestion: authenticate → validate → deduplicate on batchId → normalize → store, in one transaction
  • Query explorer, query history and database overview (windowed metrics computed from snapshots)
  • Plan storage, fingerprinting, annotation and comparison
  • Per-database settings, handed to agents with a version number on every heartbeat

It applies pending migrations on startup (disable with QUERYPILOT_AUTO_MIGRATE=false) and serves an OpenAPI document at /api/docs. See the API reference.

Worker (apps/worker)

Section titled “Worker (apps/worker)”

Expensive analysis must never run inside an API request, so it belongs in the worker, driven by a database-backed job queue. Today the queue and its lifecycle are in place but no job handlers are registered: the worker runs, reports health on port 8081 and processes nothing. Analysis, baselines and validation handlers land with the analysis engine (Roadmap).

Web (apps/web)

Section titled “Web (apps/web)”

A Next.js app. The browser talks to the API directly at NEXT_PUBLIC_API_URL. Pages: dashboard, databases, database detail (capabilities, overview, top queries, agents, settings), query explorer and query detail (history and execution plans).

Storage

Section titled “Storage”

QueryPilot keeps its own data in PostgreSQL (16 in the Compose file), accessed through TypeORM. This is not the database being observed. Telemetry lands in snapshot tables such as query_metric_snapshots, activity_snapshots, connection_snapshots, lock_snapshots, table_stat_snapshots and index_stat_snapshots; queries are identified in query_fingerprints and plan versions in query_plans.

Shared packages

Section titled “Shared packages”
Package Responsibility
@querypilot/config Configuration schema (Zod), defaults, YAML file + environment loading
@querypilot/database TypeORM entities, migrations, seed
@querypilot/postgres Connections, capability detection, connection testing
@querypilot/query SQL tokenizer, normalization, fingerprinting, statement classification, history math
@querypilot/plans EXPLAIN JSON parser, sanitizer, structural fingerprint, annotation, comparison
@querypilot/telemetry Agent ↔ API protocol schemas
@querypilot/shared Logger, errors, IDs, crypto, redaction, health server
@querypilot/types Shared domain types

Key design decisions

Section titled “Key design decisions”
  • Deltas, not totals. pg_stat_statements counters are cumulative. Every figure in a window is a delta between snapshots, and counter resets are detected and marked on charts.
  • Estimated percentiles, labelled. pg_stat_statements does not record individual executions. p50/p95/p99 are estimated from each interval’s exact mean and variance using a log-normal model, capped at the observed maximum.
  • Gaps are gaps. A bucket with nothing collected is drawn as a gap, never a zero.
  • Query fingerprints are deterministic. SQL is tokenized, literals replaced with ?, unquoted words lowercased, and the canonical text hashed with SHA-256, so the same query gets the same fingerprint on every machine.
  • Plan fingerprints are structural. Only what the planner chose (node types, relations, indexes, join types, strategies, child order) is hashed (not costs, estimates or timings), so a new plan version means the planner actually decided differently.