Configuration reference

How configuration is loaded

The API and worker build their configuration in three layers, validated once at the end:

built-in defaults  →  configuration file (YAML)  →  environment variables

Defaults live in the configuration schema. With nothing set, QueryPilot runs as a fully local, standalone install with every optional integration off.
Configuration file: set QUERYPILOT_CONFIG_FILE to a YAML file whose keys mirror the setting paths below (for example agent.intervals.queryStatsMs).
Environment variables override the file. The mapping is explicit: only the variables listed here are read, and a variable that cannot be parsed stops startup with a message naming it. Booleans accept true/false, 1/0, yes/no, on/off; lists are comma-separated.
Secrets are read only from the environment, never from the file.

An invalid configuration fails startup loudly instead of running half-applied. Contradictions are rejected too, for example an enabled webhook exporter without an endpoint, an openai-compatible AI provider without ai.endpoint, or connection-utilization thresholds that do not increase.

Roadmap marks settings that are accepted and validated but have no effect yet, because the feature behind them is not implemented.

Required

Variable	Description
`DATABASE_URL`	Connection string of QueryPilot’s own storage database, not the database being observed.
`QUERYPILOT_ENCRYPTION_KEY`	32-byte key, base64 or hex, used to encrypt stored database credentials (AES-256-GCM). Generate with `openssl rand -base64 32`.

Process and ports

Variable	Default	Description
`QUERYPILOT_CONFIG_FILE`	None	Path to an optional YAML configuration file.
`QUERYPILOT_AUTO_MIGRATE`	enabled	Set to `false` to stop the API applying migrations at startup.
`LOG_LEVEL`	`info`	`debug`, `info`, `warn`, `error` (setting `logging.level`).
`NODE_ENV`	None	`development` or `production`.
`API_HOST`	`0.0.0.0`	API bind address (`api.host`).
`API_PORT`	`8080`	API port (`api.port`).
`WORKER_PORT`	`8081`	Worker health/metrics port.
`WEB_PORT`	`3000`	Web app port.
`NEXT_PUBLIC_API_URL`	`http://localhost:8080`	API URL the web app uses, as reachable from the browser.

The agent has its own, much smaller set of variables; see Installing the agent.

Deployment

Variable	Setting	Default	Description
`QUERYPILOT_DEPLOYMENT_MODE`	`deployment.mode`	`standalone`	`standalone`, `self-hosted` or `cloud`. Reported by the public config endpoint.
`QUERYPILOT_INSTANCE_NAME`	`deployment.instanceName`	`querypilot`	Instance name shown to operators (1–64 characters).

API

Variable	Setting	Default	Description
`QUERYPILOT_PUBLIC_API_URL`	`api.publicUrl`	None	URL agents use to reach the API, shown in generated setup commands. Falls back to the URL of the operator’s request.
`QUERYPILOT_CORS_ORIGINS`	`api.corsOrigins`	empty (same-origin only)	Comma-separated origins allowed to call the API. The Compose file sets `http://localhost:3000`.
`QUERYPILOT_RATE_LIMIT_ENABLED`	`api.rateLimit.enabled`	`true`	Per-IP rate limiting.
None	`api.rateLimit.windowMs`	`60000`	Rate-limit window.
None	`api.rateLimit.max`	`300`	Requests per window per IP.
None	`api.rateLimit.agentMax`	`1200`	Separate agent budget. Roadmap Currently the single `max` budget applies to all routes.
`QUERYPILOT_MAX_PAYLOAD_BYTES`	`api.maxPayloadBytes`	`8388608` (8 MiB)	Maximum request body size.

Privacy

Variable	Setting	Default	Description
`QUERYPILOT_STORE_QUERY_TEXT`	`privacy.storeQueryText`	`true`	Store normalized query text. When `false`, agents never send text and queries are identified by fingerprint only.
`QUERYPILOT_STORE_PARAMETERS`	`privacy.storeParameters`	`false`	Reserved. Query parameters are never collected in the current release.

Agent collection

These values are held by the API and delivered to agents when they register. Restart an agent to pick up changed intervals or limits. Plan-capture settings are the exception: they are also delivered on every heartbeat.

Variable	Setting	Default	Description
`QUERYPILOT_AGENT_QUERY_STATS_INTERVAL_MS`	`agent.intervals.queryStatsMs`	`15000`	`pg_stat_statements` collection.
`QUERYPILOT_AGENT_ACTIVITY_INTERVAL_MS`	`agent.intervals.activityMs`	`5000`	`pg_stat_activity` collection.
`QUERYPILOT_AGENT_LOCKS_INTERVAL_MS`	`agent.intervals.locksMs`	`5000`	`pg_locks` collection.
`QUERYPILOT_AGENT_CONNECTIONS_INTERVAL_MS`	`agent.intervals.connectionsMs`	`10000`	Connection counts.
`QUERYPILOT_AGENT_TABLE_STATS_INTERVAL_MS`	`agent.intervals.tableStatsMs`	`60000`	`pg_stat_user_tables` collection.
`QUERYPILOT_AGENT_INDEX_STATS_INTERVAL_MS`	`agent.intervals.indexStatsMs`	`60000`	`pg_stat_user_indexes` collection.
`QUERYPILOT_AGENT_HEARTBEAT_INTERVAL_MS`	`agent.intervals.heartbeatMs`	`30000`	Heartbeat. An agent silent for 3 intervals is shown `OFFLINE`.
`QUERYPILOT_AGENT_STATEMENT_TIMEOUT_MS`	`agent.statementTimeoutMs`	`5000`	`statement_timeout` on the agent’s sessions on the observed database.
`QUERYPILOT_AGENT_REQUEST_TIMEOUT_MS`	`agent.requestTimeoutMs`	`10000`	Agent → API request timeout.
`QUERYPILOT_AGENT_MAX_QUEUE_SIZE`	`agent.maxQueueSize`	`10000`	Rows the agent buffers in memory during an API outage (oldest dropped first).
`QUERYPILOT_AGENT_MAX_BATCH_SIZE`	`agent.maxBatchSize`	`1000`	Roadmap Accepted but not applied yet; agents currently cap a batch at 5,000 rows.

Plan capture defaults

Defaults for every database; each database can override them in Database settings.

Variable	Setting	Default	Range
`QUERYPILOT_PLAN_CAPTURE_ENABLED`	`agent.planCapture.enabled`	`true`
`QUERYPILOT_PLAN_CAPTURE_INTERVAL_MINUTES`	`agent.planCapture.intervalMinutes`	`10`	5–1440
`QUERYPILOT_PLAN_CAPTURE_MAX_QUERIES`	`agent.planCapture.maxQueries`	`20`	1–100

Worker

Variable	Setting	Default	Description
`QUERYPILOT_WORKER_CONCURRENCY`	`worker.concurrency`	`4`	Concurrent jobs (max 64). Also sizes the worker’s connection pool.
None	`worker.jobTimeoutMs`	`120000`	Per-job timeout.
None	`worker.maxJobAttempts`	`3`	Attempts before a job fails.
`QUERYPILOT_WORKER_ANALYSIS_INTERVAL_MS`	`worker.analysisIntervalMs`	`60000`	How often analysis runs per database. The validation sweep uses the same interval.
None	`worker.cleanupIntervalMs`	`3600000`	Roadmap Cleanup cadence.

Retention Roadmap

Accepted and reported by the public config endpoint, but telemetry is not purged automatically yet.

Variable	Setting	Default
`QUERYPILOT_RETENTION_QUERY_METRICS_DAYS`	`retention.queryMetricsDays`	`30`
`QUERYPILOT_RETENTION_PLANS_DAYS`	`retention.plansDays`	`14`
`QUERYPILOT_RETENTION_FINDINGS_DAYS`	`retention.findingsDays`	`90`
`QUERYPILOT_RETENTION_TELEMETRY_DAYS`	`retention.telemetryDays`	`30`
None	`retention.auditDays`	`180`

Analysis

Variable	Setting	Default	Description
`QUERYPILOT_ANALYSIS_ENABLED`	`analysis.enabled`	`true`	Run the analysis rules at all.
`QUERYPILOT_ANALYSIS_MIN_CONFIDENCE`	`analysis.minimumConfidence`	`0.5`	Minimum confidence before a finding is surfaced.
None	`analysis.rules.<rule>.enabled`	`true`	Switch one rule off by its id, for example `analysis.rules.unused-index.enabled: false`.

Rule thresholds are file-only (analysis.thresholds.*), so that “is this bad?” stays an operator decision. connectionUtilization levels must increase from info to critical, or startup fails. See the findings guide for what each rule does.

Threshold	Defaults
`connectionUtilization`	info 0.7 · warning 0.8 · high 0.9 · critical 0.95
`longTransaction`	warningSeconds 60 · criticalSeconds 300
`idleInTransaction`	warningSeconds 30 · criticalSeconds 120
`queryRegression`	p95Percent 100 · minimumP95Ms 50 · minimumCalls 20
`rowEstimateMismatch`	ratio 10 · minimumRows 1000
`sequentialScan`	minimumTableRows 100000 · minimumRowsScannedRatio 100 · minimumCallsPerHour 60
`expensiveSort`	minimumDurationMs 500 · requireDiskSpill false
`nestedLoop`	minimumLoops 1000 · minimumDurationMs 500
`highIo`	readRatio 0.5 · minimumBlocksRead 10000
`lockContention`	waitSeconds 5 · criticalWaitSeconds 30
`staleStatistics`	days 7 · deadRowRatio 0.1 · thresholdMultiple 2 · minimumRows 10000
`tableBloat`	deadRatio 0.2 · minimumDeadRows 10000 · thresholdMultiple 2 · longTransactionSeconds 300
`xidWraparound`	warningRatio 0.5 · criticalRatio 0.75
`unusedIndex`	minimumObservedDays 7 · minimumBytes 10485760 (10 MB)
`duplicateIndex`	minimumBytes 1048576 (1 MB)
`nPlusOne`	minimumRatio 5 · minimumChildCallsPerHour 600 · maximumChildRowsPerCall 2 · minimumParentCalls 20 · ratioTolerance 0.25 · maximumRatioVariation 0.35 · minimumCorrelation 0.8

Validation

How an applied recommendation is measured; see recommendations & validation.

Variable	Setting	Default	Description
`QUERYPILOT_VALIDATION_OBSERVATION_WINDOW_MS`	`validation.observationWindowMs`	`3600000` (1 h)	How long to watch after a change before judging it.
`QUERYPILOT_VALIDATION_MAX_OBSERVATION_MS`	`validation.maxObservationMs`	`86400000` (24 h)	Upper bound when there are too few calls by then.
`QUERYPILOT_VALIDATION_MIN_CALLS`	`validation.minimumCalls`	`30`	Calls required on each side.
None	`validation.beforeWindowMs`	`86400000` (24 h)	How far back the “before” measurement reaches.
None	`validation.settleMs`	`60000`	Ignored right after applying, while an index build settles.
None	`validation.minimumIntervals`	`4`	Snapshot intervals required on each side.
None	`validation.minimumChangePercent`	`10`	Smaller changes are reported as no change.

AI Roadmap

Variable	Setting	Default
`QUERYPILOT_AI_ENABLED`	`ai.enabled`	`false`
`QUERYPILOT_AI_PROVIDER`	`ai.provider`	`ollama` (`ollama`, `openai`, `anthropic`, `openai-compatible`)
`QUERYPILOT_AI_ENDPOINT`	`ai.endpoint`	None
`QUERYPILOT_AI_MODEL`	`ai.model`	`llama3.1`
`QUERYPILOT_AI_SEND_QUERY_TEXT`	`ai.privacy.sendQueryText`	`false`
`QUERYPILOT_AI_SEND_DATABASE_NAMES`	`ai.privacy.sendDatabaseNames`	`false`
None	`ai.timeoutMs` / `ai.maxTokens`	`60000` / `2048`
`OPENAI_API_KEY`, `ANTHROPIC_API_KEY`	secrets	None

Telemetry export Roadmap

Service self-metrics on /metrics are always available; these settings control exporting query telemetry, which is not implemented yet.

Variable	Setting	Default
`QUERYPILOT_TELEMETRY_EXPORT_ENABLED`	`telemetry.export.enabled`	`false`
`QUERYPILOT_PROMETHEUS_ENABLED`	`telemetry.export.prometheus.enabled`	`false`
None	`telemetry.export.prometheus.topQueries`	`50`
`QUERYPILOT_OTLP_ENABLED`	`telemetry.export.otlp.enabled`	`false`
`QUERYPILOT_OTLP_ENDPOINT`	`telemetry.export.otlp.endpoint`	None
`QUERYPILOT_OTLP_PROTOCOL`	`telemetry.export.otlp.protocol`	`grpc` (`grpc`, `http`)
`QUERYPILOT_WEBHOOK_ENABLED`	`telemetry.export.webhook.enabled`	`false`
`QUERYPILOT_WEBHOOK_ENDPOINT`	`telemetry.export.webhook.endpoint`	None
`QUERYPILOT_WEBHOOK_ALLOW_PRIVATE_NETWORKS`	`telemetry.export.webhook.allowPrivateNetworks`	`false`
None	`telemetry.export.webhook.timeoutMs` / `maxAttempts`	`10000` / `5`
`QUERYPILOT_WEBHOOK_SIGNING_SECRET`	secret	None

Cloud Roadmap

Variable	Setting	Default
`QUERYPILOT_CLOUD_ENABLED`	`cloud.enabled`	`false`

Example file

deployment:
  mode: self-hosted
  instanceName: production
api:
  publicUrl: https://querypilot.internal
  corsOrigins:
    - https://querypilot.internal
privacy:
  storeQueryText: true
agent:
  statementTimeoutMs: 3000
  intervals:
    queryStatsMs: 30000
    tableStatsMs: 300000
  planCapture:
    enabled: true
    intervalMinutes: 30
    maxQueries: 10
worker:
  concurrency: 2

Run with QUERYPILOT_CONFIG_FILE=/etc/querypilot/querypilot.yaml.