API reference

The QueryPilot API is a REST/JSON API served by apps/api. This page is an overview of every endpoint in the current code. The running API also publishes a full, interactive OpenAPI description at /api/docs (for example http://localhost:8080/api/docs).

Conventions

Base path. Everything is under /api/v1, except the probes and metrics, which sit at fixed unversioned paths: /health/live, /health/ready and /metrics.

Errors use a single envelope:

{
  "error": {
    "code": "NOT_FOUND",
    "message": "No database found with id \"db_…\".",
    "requestId": "…"
  }
}

Validation. Unknown properties in a request body are rejected rather than ignored.

Pagination is cursor-based. List endpoints accept limit and cursor and return { "items": [...], "nextCursor": "…" | null }; some also return total. Cursors are opaque and, for the query explorer, only valid with the same sort and order.

Time windows. Windowed endpoints accept either a range of 15m, 1h (default), 6h, 24h or 7d, or an ISO-8601 from and to (which override range). Windows are at most 7 days. step (seconds, ≥ 60) sets the bucket size where supported; it is chosen automatically when omitted.

Limits. Request bodies are capped at 8 MiB by default and requests are rate-limited per client IP (300 per minute by default). Health probes and /metrics are not rate-limited.

Authentication. Operator endpoints are unauthenticated in the current release (Roadmap); only the agent protocol requires a token.

Health & system

Method	Path	Description
GET	/health/live	Liveness probe. Always 200 while the process runs.
GET	/health/ready	Readiness probe. Non-200 when a required dependency is down.
GET	/api/v1/health	Detailed health report, including optional subsystems.
GET	/metrics	Prometheus exposition format.
GET	/api/v1/config/public	Non-sensitive configuration: deployment mode, instance name, version, whether AI and exporters are enabled, retention.

Databases

Method	Path	Description
POST	/api/v1/databases	Register a database. 201; 409 if already registered.
GET	/api/v1/databases	List databases, newest first. Query: limit, cursor, projectId, search, status, environment (comma-separated, unassigned included), tag (repeatable; a database must carry every tag given). The response includes facets for status, environment and tags.
PATCH	/api/v1/databases/{id}	Change name, environment (null clears it) or tags. Only the fields you send are changed.
POST	/api/v1/databases/test	Test credentials without saving. Returns the connection result and capability checklist.
GET	/api/v1/databases/{id}	One database.
POST	/api/v1/databases/{id}/test	Re-test stored credentials and refresh detected capabilities.
DELETE	/api/v1/databases/{id}	Remove the database with its agents and telemetry. 204.
GET	/api/v1/databases/{id}/overview	Database-wide workload, connections, lock waits, statement-type breakdown and largest tables. Query: range or from/to, step.
GET	/api/v1/databases/{id}/settings	Resolved per-database settings and whether plan capture is supported.
PATCH	/api/v1/databases/{id}/settings	Change settings, e.g. {"planCapture":{"enabled":true,"intervalMinutes":30}}. Agents apply it on their next heartbeat.

Register a database:

curl -s -X POST http://localhost:8080/api/v1/databases \
  -H 'Content-Type: application/json' \
  -d '{
    "name": "orders-primary",
    "host": "db.internal",
    "port": 5432,
    "databaseName": "orders",
    "username": "querypilot",
    "password": "…",
    "sslMode": "require"
  }'

sslMode is one of disable, prefer (default), require, verify-ca, verify-full. The password is never included in any response.

Agents

Method	Path	Description
POST	/api/v1/databases/{id}/agents	Create an agent: {"name": "orders-agent"}. Returns the agent, the one-time token and setup instructions (env, dockerCommand). 201.
GET	/api/v1/databases/{id}/agents	List agents with derived status. Query: limit (≤ 100, default 20), cursor.
POST	/api/v1/agents/{id}/rotate-token	Issue a new token; the old one stops working immediately.
DELETE	/api/v1/agents/{id}	Revoke the agent. 204.

Queries

Method	Path	Description
GET	/api/v1/databases/{id}/queries	Query explorer: windowed metrics per query, filterable and paginated.
GET	/api/v1/queries/{id}	One query: fingerprint, normalized text, statement type, database, first/last seen.
GET	/api/v1/queries/{id}/history	Bucketed latency, throughput and I/O, stats resets and plan versions first seen in the window. Query: range or from/to, step.

Explorer query parameters:

Parameter	Description
range, from, to	Time window.
search	Substring of the query text, or a fingerprint prefix (≤ 200 characters).
statementType	Comma-separated: SELECT, INSERT, UPDATE, DELETE, DDL, UTILITY, OTHER, UNKNOWN.
minCalls, minMeanMs, maxMeanMs, minTotalTimeMs	Numeric thresholds.
sort	total_time (default), mean_time, calls, rows, io, temp.
order	desc (default) or asc.
limit, cursor	Page size 1–200 (default 50) and cursor.

Each item carries calls, callsPerMinute, totalTimeMs, meanTimeMs, p50Ms, p95Ms, p99Ms, maxTimeMs, rows, rowsPerCall, sharedBlksHit, sharedBlksRead, tempBlksWritten, cacheHitRatio, partialWindow and percentilesEstimated: true. The response also includes total, facets.statementType counts, the resolved window and lastCollectedAt.

Execution plans

Method	Path	Description
GET	/api/v1/queries/{id}/plans	Plan versions, newest first. Query: limit (≤ 100, default 20), cursor.
GET	/api/v1/queries/{id}/plans/{planId}	One version with its annotated node tree.
GET	/api/v1/queries/{id}/plans/compare?base={planId}&target={planId}	Semantic comparison: identical, changes[] (kind, relation, before, after, description).
POST	/api/v1/queries/{id}/plans	Upload {"plan": <EXPLAIN JSON>, "note": "…"}. Returns the stored version, or the existing one when the structure is already recorded. 201.
POST	/api/v1/queries/{id}/plans/capture	Ask the agent to capture an estimated plan now (PostgreSQL 16+). 202.
GET	/api/v1/queries/{id}/plans/capture-requests	Recent capture requests and their status. Query: limit (≤ 20, default 5).

Upload a plan:

psql -XqAt -c "EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT …" > plan.json
curl -s -X POST "http://localhost:8080/api/v1/queries/<id>/plans" \
  -H 'Content-Type: application/json' \
  -d "{\"plan\": $(cat plan.json), \"note\": \"after adding orders_customer_id_idx\"}"

Plans larger than 1 MB of JSON or 10,000 nodes are rejected.

Change kinds: access_method_changed, index_changed, join_strategy_changed, node_added, node_removed, estimate_changed.

Findings

Method	Path	Description
GET	/api/v1/databases/{id}/findings	Findings for a database, most severe first. Defaults to active findings (OPEN, ACKNOWLEDGED). Query: status, severity, type (comma-separated), search, queryId, limit, cursor. Includes total and facet counts.
GET	/api/v1/findings/{id}	One finding with its evidence and affected queries.
POST	/api/v1/findings/{id}/{action}	acknowledge, resolve, ignore or reopen. 409 when the transition is not allowed from the current status.

Statuses: OPEN, ACKNOWLEDGED, RESOLVED, IGNORED. A resolved finding reopens automatically if analysis detects the problem again. Severities: INFO, LOW, MEDIUM, HIGH, CRITICAL. See the findings guide.

Recommendations

Method	Path	Description
GET	/api/v1/databases/{id}/recommendations	Recommendations with their findings, newest first. Defaults to PROPOSED and ACKNOWLEDGED. Query: status, type, risk (comma-separated), limit, cursor. Includes facet counts.
GET	/api/v1/recommendations/{id}	One recommendation with its lifecycle events and validation runs.
POST	/api/v1/recommendations/{id}/{action}	acknowledge, apply or reject. Optional body for apply: {"observationMinutes": 5..10080}. 409 when the transition is not allowed.
POST	/api/v1/recommendations/{id}/validate	Start another validation run for an applied recommendation. 409 if it is not applied, already validating, or there is nothing measurable.

QueryPilot never executes a recommendation. apply records that an operator ran it and starts validation. Types: CREATE_INDEX, DROP_INDEX, ANALYZE_TABLE, VACUUM_TABLE, QUERY_REWRITE, CONFIGURATION, INVESTIGATE. Statuses: PROPOSED, ACKNOWLEDGED, APPLIED, VALIDATING, VALIDATED, REJECTED, REGRESSED. Validation results: IMPROVED, NO_CHANGE, REGRESSED, INCONCLUSIVE. See recommendations & validation.

Waits & locks

Method	Path	Description
GET	/api/v1/databases/{id}/waits	Wait timeline, wait events, the top waiting, blocking and blocked queries, and the latest blocking tree. Query: range or from/to, step.
GET	/api/v1/queries/{id}/waits	What one query waits on, what blocks it and what it blocks.

Sampled data: session time is estimated from activity samples and lock waits are upper bounds.

Tables & indexes

Method	Path	Description
GET	/api/v1/databases/{id}/tables	Table maintenance state (dead rows against the effective autovacuum threshold, analyze lag, wraparound age), indexes with their usage in the window, size and dead-row history, and open table and index findings. Query: range or from/to, step.

Agent protocol

These endpoints are called by the agent. Every request needs Authorization: Bearer <agent token>; the token is checked before the body is validated. Bodies over 16 KB are sent gzip-compressed.

Method	Path	Description
POST	/api/v1/agent/register	Handshake: the agent reports its version, protocol version and PostgreSQL capabilities; the API answers with enabled collectors, intervals, timeouts, queue size, privacy settings and plan-capture settings.
POST	/api/v1/agent/heartbeat	Liveness, collector error counts, queue depth and dropped samples. The response carries the current settings version.
POST	/api/v1/agent/telemetry	Ingest a batch of samples. Idempotent on batchId: a resent batch is acknowledged as a duplicate. 202.
POST	/api/v1/agent/tasks	Claim pending on-demand work (for example plan captures) for this agent.
POST	/api/v1/agent/tasks/{taskId}/result	Report a task’s outcome.