CI Quickstart

The analyzer doubles as a GitHub Action that reviews every pull request for index recommendations. It captures the queries your test suite runs against a real PostgreSQL database, analyzes them against your schema, and posts a comment on the PR with its findings.

It works with any language, ORM, or query builder — the analyzer sees every query regardless of how it was generated.

If you use the Query Doctor MCP server, you can skip the hand-wiring below: ask your agent to “set up Query Doctor CI for this repo” and it will call the setup_ci tool.

Your agent does the reading. The server has no working directory, so the agent resolves the git remote and sends the package.json nearest your code, along with whether a lockfile and a Drizzle config sit beside it. setup_ci returns the workflow contents and the gh secret set command; your agent writes the file. Detection covers Node today — on any other stack the migrate and test commands come back as TODOs that fail loudly, and you fill them in.

The call needs the mcp:provision scope. Without it the server answers 403 insufficient_scope before the tool runs, and your client re-authorizes to continue.

The manual setup here is the reference for what that produces.

How it works

Your CI pipeline starts PostgreSQL with pg_stat_statements enabled.
Your migrations and seed scripts set up the schema and seed data.
Your test suite (integration, e2e, load tests, etc.) runs queries against that database.
The analyzer reads the captured queries, introspects the schema, and generates index recommendations.
A comment is posted to the PR with the results.

Queries can run inside rolled-back transactions and will still be captured. The analyzer’s own work is also done in transactions that are always rolled back — no data is modified.

Prerequisites

GitHub Actions on an ubuntu runner
A test suite that hits a real PostgreSQL database — the source doesn’t matter (unit, integration, e2e)
pull-requests: write permission for the job so the analyzer can post PR comments
A Query Doctor project with a token (see “Get your project token” below)

Environment variables

The analyzer action requires these environment variables:

Variable	Required	Description
`TOKEN`	Yes	Project token from your Query Doctor project. See “Get your project token” below.
`SOURCE_DATABASE_URL`	Yes	Connection string for the database your test suite runs against (`postgres://user@host/db`).
`GITHUB_TOKEN`	Yes	GitHub token for posting PR comments — use `${{ github.token }}`.
`SITE_API_ENDPOINT`	No	Query Doctor API endpoint — set to `https://api.querydoctor.com` by default.

Get your project token

In the Query Doctor app, open your project and go to Settings → CI. Copy the value from the “Analyzer token” panel — use the rotate button to invalidate the current token and issue a new one.

Then store the value as an Actions secret on your GitHub repository. Either via the GitHub repo UI under Settings → Secrets and variables → Actions, or via the gh CLI:

gh secret set QUERYDOCTOR_TOKEN --body "<token>" --repo <owner>/<repo>

The name QUERYDOCTOR_TOKEN is the convention used in the example below.

Setup

Workflow trigger

Your workflow should trigger on both pull_request (for PR analysis) and push to your main branch (to establish a baseline for comparison). On push events, the analyzer creates a baseline on the dashboard without posting a PR comment.

on:
  pull_request:
  push:
    branches: [main]

Use a service container for full control over the PostgreSQL version and configuration. Pass postgres flags directly with command:.

1. Add a postgres service

jobs:
  query-doctor:
    runs-on: ubuntu-latest
    permissions:
      contents: read
      pull-requests: write
    services:
      postgres:
        image: postgres:16
        env:
          POSTGRES_USER: postgres
          POSTGRES_PASSWORD: postgres
          POSTGRES_DB: myapp_test
        ports:
          - 5432:5432
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
        command: >-
          postgres
          -c shared_preload_libraries=pg_stat_statements
    steps:
      - uses: actions/checkout@v4
      - name: Enable pg_stat_statements
        run: psql -h localhost -U postgres -d myapp_test -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"
        env:
          PGPASSWORD: postgres

2. Install your dependencies

- uses: actions/setup-node@v4
  with:
    node-version: 24
- name: Install dependencies
  run: npm ci

setup_ci always emits this step. Without it the next step calls tools that were never installed and the job dies at exit 127. On a stack Query Doctor doesn’t detect — it reads Node projects only — you get a step that fails with a TODO instead, so the gap is visible rather than silent.

3. Run migrations and seed

- name: Migrate and seed
  run: npm run migrate && npm run seed
  env:
    POSTGRES_URL: postgres://postgres:postgres@localhost/myapp_test

4. Run your test suite

- name: Run tests
  run: npm run test:integration
  env:
    POSTGRES_URL: postgres://postgres:postgres@localhost/myapp_test

5. Run the analyzer

- name: Run Query Doctor
  uses: query-doctor/analyzer@main
  env:
    GITHUB_TOKEN: ${{ github.token }}
    TOKEN: ${{ secrets.QUERYDOCTOR_TOKEN }}
    SOURCE_DATABASE_URL: postgres://postgres:postgres@localhost/myapp_test
    SITE_API_ENDPOINT: https://api.querydoctor.com

Capture every call site with auto_explain

pg_stat_statements captures each query’s source — the file and route from the SQLCommenter comment. But it aggregates repeat executions into one row, so a query that runs from several places keeps one representative comment, and literals collapse to $1. Reading from auto_explain instead logs every execution, so you get each call site’s source with its real values.

Replace the postgres service (step 1) with Postgres on the runner — so the analyzer can read its log — with auto_explain enabled:

- name: Run Postgres
  run: |
    sudo tee -a /etc/postgresql/16/main/postgresql.conf <<EOF
      shared_preload_libraries = 'pg_stat_statements,auto_explain'
      auto_explain.log_min_duration = 0
      auto_explain.log_analyze = true
      auto_explain.log_verbose = true
      auto_explain.log_buffers = true
      auto_explain.log_format = 'json'
      logging_collector = on
      log_directory = '/var/log/postgresql'
      log_filename = 'postgres.log'
    EOF
    sudo tee /etc/postgresql/16/main/pg_hba.conf > /dev/null <<EOF
      host all all 127.0.0.1/32 trust
      host all all ::1/128 trust
      local all all peer
    EOF
    sudo systemctl start postgresql.service
    sudo -u postgres createuser -s -d -r -w me
    sudo -u postgres createdb testing
    sudo chmod 666 /var/log/postgresql/postgres.log

auto_explain.log_min_duration = 0 logs every query no matter how fast it runs. shared_preload_libraries is a single assignment rather than a list you append to, so name both extensions: dropping pg_stat_statements here would leave the analyzer with nothing to fall back on.

Set LOG_PATH on the analyzer step to the file you configured:

- name: Run Query Doctor
  uses: query-doctor/analyzer@main
  env:
    LOG_PATH: /var/log/postgresql/postgres.log
    # ...the rest as above

LOG_PATH has no default. Leave it unset and the analyzer reads pg_stat_statements instead, which is a working run that quietly ignores everything you just configured. Point the migrate, seed, test, and analyzer steps at postgres://me@localhost/testing.

Dashboard and run comparison

When SITE_API_ENDPOINT is set and TOKEN is valid, the analyzer sends each CI run’s query data to the Query Doctor dashboard. This enables:

Run history — browse all CI runs at /ci, filterable by repo and branch.
Run comparison — each PR comment includes a comparison against the previous run, showing regressed, new, and disappeared queries.
Check gating — a run can fail the GitHub Actions step. See What fails the check, and Require the check in a branch ruleset to turn a red check into a blocked merge.
Direct links — the PR comment links to the full run details and individual query history pages on the dashboard.

What fails the check

The analyzer fails the GitHub Actions step — a red check — on any of these:

Untested data access. The PR changes data-access code, but no real-DB test changed with it. Fails by default, with no baseline needed. Add a test that runs the query against Postgres, or triage the flagged queries, then re-run. Set this check to warn or off per repo (see Per-repo configuration).
Cost regression. A query’s cost rose past the repo’s regression threshold against the baseline. This needs a baseline run on the comparison branch — a PR opened before the first baseline push has nothing to compare, so it can’t block. Acknowledge or resolve the query to stop it blocking.
New query with a high-impact index. A brand-new query ships with an index recommendation that cuts its cost. It blocks when the query is introduced — the cheapest time to add the index. Acknowledge it on the dashboard to allow it.
Schema drift. The run’s schema changed against the baseline, so a person should look at the migration. Set this check to warn or off per repo.

The PR comment names the reason and the next step. Fix the cause, triage the query, or soften the check, and the next run goes green.

Require the check in a branch ruleset

A red check doesn’t stop a merge on its own. Require the job as a status check, once per repo. Do it from the command line or in the GitHub UI; a coding agent with the gh CLI can run the commands for you.

1. Get the check’s name

Query Doctor publishes no check of its own. The analyzer runs as a step in your workflow, so the check GitHub sees is the job holding that step. Its name is the job’s name:, or the job id when the job has no name: — query-doctor in the workflow above.

Two things change that name. A matrix job reports once per combination, as job (value). A workflow that another one calls with uses: reports as caller-job / callee-job, like analyzer / Analyzer. Read the name off a pull request that already ran the workflow instead of deriving it:

gh pr checks <pr-number> --json name --jq '.[].name'

2. Create the ruleset with the gh CLI

Put your check name in context and run:

gh api repos/<owner>/<repo>/rulesets --method POST --input - <<'JSON'
{
  "name": "main: require Query Doctor",
  "target": "branch",
  "enforcement": "active",
  "conditions": { "ref_name": { "include": ["~DEFAULT_BRANCH"], "exclude": [] } },
  "rules": [
    {
      "type": "required_status_checks",
      "parameters": {
        "strict_required_status_checks_policy": false,
        "required_status_checks": [{ "context": "query-doctor" }]
      }
    }
  ]
}
JSON

~DEFAULT_BRANCH targets whatever your default branch is, so you don’t have to name it. strict_required_status_checks_policy is not optional, and the request fails without it: false lets a pull request merge without pulling in the latest base commit, true requires an update and a fresh run first. Keep enforcement on active, since disabled and evaluate block nothing.

To see what the repo has now:

gh api repos/<owner>/<repo>/rulesets --jq '.[] | "\(.id) \(.name) \(.enforcement)"'

Or create it in the GitHub UI

Go to Settings → Rules → Rulesets, then New ruleset → New branch ruleset:

Name it something you’ll recognize later, such as main: require Query Doctor.
Set Enforcement status to Active.
Under Target branches, click Add target → Include default branch.
Tick Require status checks to pass.
Click Add checks, type the name from step 1, and select it from the list.
Click Create.

The check appears in that search box only after it has reported at least once. If it isn’t there, open a pull request, let the workflow finish, and come back.

What you get

The gate is the job’s exit code, so the policies below carry over unchanged. A condition set to fail exits non-zero and blocks the merge. warn and off leave the job green. Triage behaves the same way: acknowledged, resolved and ignored queries never fail the run.

Two things to know before you turn it on:

The check covers the whole job. The analyzer shares a job with your tests because it reads the queries they ran against the same database. A failing test step reds the same check.
A required check that never reports blocks the merge. GitHub waits for it indefinitely. Don’t require the job if a path filter or an if: condition can skip it. Pull requests from forks get no Actions secrets, so TOKEN is empty, the API rejects the run, and the step fails instead of skipping.

PR comment behavior

The PR comment adapts based on whether action is needed:

All Clear — no untriaged regressions. Shows a minimal summary with query count, index recommendations, and optimizations if any.
Action Required — untriaged regressions exist. Shows a prominent table of regressed queries with links to their history pages. The GitHub Actions step fails, blocking the PR.

Acknowledged regressions are shown in a collapsed section and do not block the PR. Ignored queries are excluded entirely.

Query triage

Each query can be triaged via the dashboard at /ci/:runId:

Status	PR comment behavior	Blocks PR?
New (default)	Shown in “Regressions Requiring Triage”	Yes
Acknowledged	Shown in collapsed “acknowledged” section	No
Resolved	Shown in collapsed “acknowledged” section	No
Ignored	Hidden from PR comment and comparison	No

Per-repo configuration

Open your project in the Query Doctor app, go to Settings → CI, and pick the repo. The first group controls what a run reports:

Setting	Description	Default
Minimum query cost to report	Queries below this cost are excluded from PR comments and regression checks.	`0` (show all)
Regression threshold	Only flag regressions above this percentage increase.	`5%`
Statistics scale	Model query plans at this multiple of your current data size. Changes results only when the stats come from a real export, not the synthetic baseline.	`1×`
Comparison branch	Branch to compare against for regressions (e.g. `main`, `staging`). If empty, compares against the current branch.	Current branch

Under Gate Policies, each condition takes one of three policies. fail reds the check and blocks the PR. warn annotates the Actions run and stays green. off drops the finding from the check entirely.

Condition	What it catches	Default
Cost regression	A query’s plan cost rose past the regression threshold.	`fail`
Untested data access	A changed data-access file ships with no related test.	`fail`
New query with index recommendation	A new query ships with an index recommendation past the threshold.	`fail`
Schema drift	The run’s schema changed against the baseline.	`fail`
New query	A query with no prior baseline was introduced.	`warn`
High-value nudge	A high-impact index or rewrite recommendation was found.	`fail`

The last two are inert today. Nothing emits New query or High-value nudge, so their policy changes nothing; the four above them decide the check.

These settings are also available via the API. Reading the config takes no credentials. Updating it takes a personal access token, which you create under Settings → Account:

# Read config
curl https://api.querydoctor.com/ci/repos/org%2Frepo/config

# Update config
curl -X PUT https://api.querydoctor.com/ci/repos/org%2Frepo/config \
  -H 'Authorization: Bearer <your-token>' \
  -H 'Content-Type: application/json' \
  -d '{"minimumCost": 10, "regressionThreshold": 20, "comparisonBranch": "main", "conditionPolicies": {"untested-data-access": "warn", "schema-drift": "off"}}'

Production statistics (optional)

By default, the analyzer works with the statistics generated by your test data. For more accurate recommendations that reflect real-world data distribution, you can sync statistics from your production database.

Create the following function in your production database:

CREATE OR REPLACE FUNCTION _qd_dump_stats(include_sensitive_info boolean)
RETURNS jsonb AS $$
SELECT json_agg(t) FROM (
  SELECT
    c.table_name as "tableName",
    c.table_schema as "schemaName",
    cl.reltuples, cl.relpages, cl.relallvisible,
    n.nspname as "schemaName",
    json_agg(
      json_build_object(
        'columnName', c.column_name,
        'dataType', c.data_type,
        'isNullable', (c.is_nullable = 'YES')::boolean,
        'stats', (
          select json_build_object(
            'stanullfrac', s.stanullfrac,
            'stawidth', s.stawidth,
            'stadistinct', s.stadistinct,
            'stakind1', s.stakind1, 'stanumbers1', s.stanumbers1,
            'stakind2', s.stakind2, 'stanumbers2', s.stanumbers2,
            'stakind3', s.stakind3, 'stanumbers3', s.stanumbers3,
            'stakind4', s.stakind4, 'stanumbers4', s.stanumbers4,
            'stakind5', s.stakind5, 'stanumbers5', s.stanumbers5,
            'stavalues1', case when $1 then s.stavalues1 else null end,
            'stavalues2', case when $1 then s.stavalues2 else null end,
            'stavalues3', case when $1 then s.stavalues3 else null end,
            'stavalues4', case when $1 then s.stavalues4 else null end,
            'stavalues5', case when $1 then s.stavalues5 else null end
          ) from pg_statistic s
          where s.starelid = a.attrelid and s.staattnum = a.attnum
        )
      ) ORDER BY c.ordinal_position
    ) as columns
  FROM information_schema.columns c
  JOIN pg_attribute a ON a.attrelid = (quote_ident(c.table_schema) || '.' || quote_ident(c.table_name))::regclass AND a.attname = c.column_name
  JOIN pg_class cl ON cl.relname = c.table_name
  JOIN pg_namespace n ON n.oid = cl.relnamespace
  WHERE c.table_name not like 'pg_%'
    AND n.nspname <> 'information_schema'
  GROUP BY c.table_name, c.table_schema, cl.reltuples, cl.relpages, cl.relallvisible, n.nspname
) t;
$$ LANGUAGE sql STABLE SECURITY DEFINER;

Then dump and provide the stats file:

psql -d yourdb -At -F "" -c "select _qd_dump_stats(false)" > stats.json

Pass false to include_sensitive_info to exclude actual cell values from the dump — only statistical distributions are included. Pass true if you need the most accurate recommendations and your data isn’t sensitive.