PostgresAudit

Read-only PostgreSQL evidence collector and local review toolkit for Database Optimization Tool.

Run this CLI when you need a local evidence package for slow queries, missing-index review, table/index health checks, managed Postgres visibility limits, or a before/after validation handoff. It is designed to help you create audit.json, explain.json, benchmark.json, benchmark-artifact.json, and handoff.md.

It does not automatically upload files, create indexes, run VACUUM, change settings, or roll out production changes. Generated files stay on your machine until you review them and decide what to attach to a Database Optimization Tool project.

What This Toolkit Does

• Collects PostgreSQL metadata and performance signals with a restricted read-only user.
• Validates the shape of local evidence files before upload.
• Captures safe EXPLAIN (FORMAT JSON, BUFFERS) output for one SELECT or WITH query.
• Creates sandbox/staging benchmark plans and dry-run artifacts.
• Generates a local handoff.md for a database owner, backend engineer, or DBA.
• Documents evidence levels so rough ideas do not look like validated production results.

What It Will Not Do

• It will not connect to Database Optimization Tool or upload files by itself.
• It will not execute production DDL, DML, VACUUM, REINDEX, or ALTER SYSTEM.
• It will not run EXPLAIN ANALYZE unless you pass the explicit consent flag.
• It will not run benchmark SQL unless you use a sandbox/staging spec and pass the explicit consent flag.
• It does not contain the SaaS backend, auth, billing, Cloudflare/D1 config, or AI workflow.

Quick Start

npm install
npm run smoke

DATABASE_URL="postgres://readonly_user:password@localhost:5432/app" \
  node bin/postgresaudit.mjs collect --out audit.json

node bin/postgresaudit.mjs validate --file audit.json
node bin/postgresaudit.mjs handoff --file audit.json --out handoff.md

You can also install the package from a local checkout:

npm link
postgresaudit --help

Create a Restricted PostgreSQL User

Use a dedicated role. The collector sets statement_timeout and default_transaction_read_only=on, but database permissions should still enforce the boundary.

create role postgresaudit_readonly login password 'replace-with-a-secret';
grant connect on database your_database to postgresaudit_readonly;
grant usage on schema public to postgresaudit_readonly;
grant select on all tables in schema public to postgresaudit_readonly;
alter default privileges in schema public grant select on tables to postgresaudit_readonly;

Managed providers may require provider-specific grants for pg_stat_statements or catalog views. See docs/permissions.md and docs/managed-postgres.md.

Collect audit.json

postgresaudit collect --url "$DATABASE_URL" --out audit.json
postgresaudit validate --file audit.json

audit.json contains database version, relation size, table stats, index stats, query stats when available, catalog shape, generated findings, and coverage notes. Review it before upload.

Capture EXPLAIN Evidence Safely

postgresaudit explain \
  --url "$DATABASE_URL" \
  --query-file slow-query.sql \
  --out explain.json

Default explain uses EXPLAIN (FORMAT JSON, BUFFERS), which plans the query without running it. The CLI accepts one SELECT or WITH statement and rejects mutation, DDL, and multiple statements before connecting.

EXPLAIN ANALYZE runs the query. Only use it after review, preferably in staging or a sandbox:

postgresaudit explain \
  --url "$STAGING_DATABASE_URL" \
  --query-file slow-query.sql \
  --analyze \
  --i-understand-analyze-runs-query \
  --out explain-analyze.json

Plan and Run Sandbox Benchmarks

Create a benchmark plan without connecting to a database:

postgresaudit benchmark-plan \
  --schema schema.sql \
  --original-query before.sql \
  --optimized-query after.sql \
  --target-rows 1000000 \
  --environment sandbox \
  --out benchmark.json

Dry-run fixture preparation and artifact generation:

postgresaudit benchmark-prepare --spec benchmark.json --dry-run --out fixture-prep.json
postgresaudit benchmark-run --spec benchmark.json --dry-run --out benchmark-artifact.json

Live benchmark commands require BENCHMARK_DATABASE_URL, a sandbox/staging spec, and the explicit --i-understand-benchmark-runs-sql flag.

Validate and Create handoff.md

postgresaudit validate --file audit.json
postgresaudit handoff --file audit.json --out handoff.md

The handoff summarizes coverage gaps, top findings, EXPLAIN requests, and upload next steps. It is not a production rollout plan.

Upload to Database Optimization Tool

Open Database Optimization Tool, create a project, and attach reviewed files only if your policy allows it. The website turns approved local evidence into project review, owner notes, validation tasks, and report artifacts.

Useful links:

• How optimization works
• Collector docs
• Local companion docs
• Sample report

Evidence Levels

Level	Input	Meaning
problem-only	user symptom	Directional, not enough for a production change.
SQL draft	slow SQL text	Useful for query-shape review.
schema-backed	SQL plus table shape	Supports candidate index or rewrite discussion.
plan-backed	EXPLAIN evidence	Ties a recommendation to planner behavior.
collector-backed	audit.json	Uses workload and table/index history.
benchmark-backed	sandbox/staging artifact	Compares before and after evidence before rollout.

See docs/evidence-levels.md.

Privacy and Redaction

Review every generated file before sharing. The CLI redacts common plan literals in EXPLAIN output, but it cannot know every business-sensitive identifier, query fragment, tenant value, or schema name. See docs/privacy-and-redaction.md.

Development

npm install
npm run check

The test suite includes CLI smoke tests, evidence validation tests, SQL safety parser tests, benchmark-plan tests, dry-run benchmark artifact tests, and handoff fixture tests.

License

MIT. See LICENSE.