sql-plan-audit

name	sql-plan-audit
description	Run EXPLAIN QUERY PLAN against every query in a sqlc-style catalog and diff the plans against a baseline. Detects new full-table SCANs and TEMP B-TREE sort scans introduced by PRs. SQLite/D1-only today; engine extension noted below.
version	0.1.0
metadata	{"hermes":{"tags":["sql","sqlite","d1","dba","performance","sqlc"],"related_skills":["sql-lint","sql-schema-audit","sql-security"],"engines":["sqlite"]}}

SQL Plan Audit

Use this when a project has a sqlc-style query catalog (or any file with -- name: X :type markers) and wants to keep query plans visible in code review.

Why

sqlc enforces SQL syntax at codegen, but it doesn't watch the execution plan. A column rename, a removed index, or a small WHERE-clause addition can flip a query from SEARCH USING INDEX to SCAN TABLE silently. On Cloudflare D1 / SQLite without EXPLAIN ANALYZE, the planner output is the only static signal. This skill freezes that output as a reviewable artifact.

When to invoke

• Schema or query catalog changed in a PR.
• A new index was added and you want to confirm queries pick it up.
• Quarterly audit of an existing query catalog.

Workflow

1. Identify the schema file and the query catalog file. Typical layout:

   <project>/db/schema.sql
   <project>/db/queries.sql   (or db/sqlite/query.sql for sqlc projects)
   

2. Run the runner:

   node scripts/explain-runner.mjs \
     --schema your-project/db/schema.sql \
     --queries your-project/db/queries.sql \
     --out your-project/.linters/query-plans.txt
   

3. Commit the output. The text format is line-stable across runs; diffs surface plan changes.
4. To enforce in CI, regenerate to JSON and diff against a committed baseline:

   node scripts/explain-runner.mjs \
     --schema your-project/db/schema.sql \
     --queries your-project/db/queries.sql \
     --baseline your-project/.linters/query-plans.json \
     --format json --fail-on regress
   

5. When intentional regressions land (e.g. an index was retired on purpose), regenerate the baseline in the same PR.

What success and failure look like

The runner is deliberately silent on success. Knowing where output lands matters when wiring it into CI:

Invocation	stdout	stderr	Exit
--out <path> (regen)	nothing	only Node's node:sqlite experimental warning	0
no --out (regen)	full text/JSON report	only the experimental warning	0
--baseline <path> --fail-on regress (CI check), clean	full text/JSON report (or nothing if --out is set)	only the experimental warning	0
--baseline <path> --fail-on regress (CI check), regression	full text/JSON report	per-query regression detail (before vs after plans)	1
any path, internal error (e.g. schema fails to load)	nothing or partial	Node stack trace	1

Read this as: stderr is where pass/fail diagnostics live. In CI, capture stderr explicitly (2>&1, 2>artifact.log, or separate streams) — piping only stdout to a parser will lose every regression message. If a regen run "prints nothing", check $? and ls <out path> to confirm; that is the documented happy-path.

How the runner handles placeholders

sqlc.arg('x') and sqlc.slice('x') are rewritten to NULL before EXPLAIN runs. The plan does not depend on bind values, only on the SQL shape, so this is safe. ? positional placeholders are left as-is — SQLite accepts them inside EXPLAIN.

Severity markers

• ! SCAN — full-table scan. Usually a missing index or a query intentionally touching every row.
• ? TEMP B-TREE — USE TEMP B-TREE FOR ORDER BY / GROUP BY / DISTINCT. Sort happens in memory because no index covers the ordering.
• SEARCH — index hit. Normal.

A plan with one SCAN on a small table (e.g. users with <1k rows) is often fine; the marker is a flag, not a verdict.

CI integration

Add a step that runs the JSON variant and fails on regression. A typical justfile:

sql-plan-audit:
    node scripts/explain-runner.mjs \
      --schema db/schema.sql \
      --queries db/queries.sql \
      --out .linters/query-plans.txt
    node scripts/explain-runner.mjs \
      --schema db/schema.sql \
      --queries db/queries.sql \
      --format json --out .linters/query-plans.json

sql-plan-audit-check:
    node scripts/explain-runner.mjs \
      --schema db/schema.sql \
      --queries db/queries.sql \
      --baseline .linters/query-plans.json \
      --format json --fail-on regress

Pre-push (pkfire / lefthook / pre-commit) is the right boundary — running this on every commit is slow and noisy.

Limitations

• SQLite EXPLAIN does not report estimated row counts, so the runner cannot rank "bad SCAN of 10M rows" vs "fine SCAN of 50 rows". Combine with manual review.
• FTS5 virtual tables show as SCAN VIRTUAL TABLE. Treated as info, not flagged.
• Functions like datetime('now') are evaluated at plan time. Side effects (writes) are not run because the in-memory DB has the same schema but no rows.
• Subqueries and CTEs may produce extra rows in the plan; the diff treats them stably.

Engine extensibility

The runner is SQLite-specific. To support Postgres / MySQL: swap the node:sqlite driver and the EXPLAIN syntax; the parser, baseline diff, and severity classifier are engine-agnostic. Not implemented here — drop a sibling script when needed.

Requirements

• Node 22 or newer (uses the built-in node:sqlite module).
• A sqlc-style query catalog (-- name: X :type markers). Other named-query formats can be supported by adjusting parseQueryCatalog in the runner.

Files

• scripts/explain-runner.mjs — CLI entrypoint, no dependencies.