awesome-database-audit

Database Audit

Audit a database layer — schema, queries, migrations, and the operational habits around them — for the design defects that surface as slow queries, silent data corruption, and unrunnable migrations in production. Read-only: it reports findings and a verdict; it never edits schema, data, or code. Works from the repo's schema files, migrations, and query sites; a live connection is optional and read-only when present. An executing plan (EXPLAIN ANALYZE and its equivalents) runs the statement it measures, so on a live database it is used for reads only; a write gets the estimated plan, or its executing plan is prescribed for a disposable copy.

Evidence, not taste. Every finding cites its artifact — a file:line in a migration or model, a query site, a schema definition, an EXPLAIN output if a connection exists. A "smelly" table name is a lead; confirm the defect (the missing constraint, the unindexable predicate) before flagging.

Four audit tracks — run the ones in scope:

• A. Schema design — types, keys, and the anti-pattern catalog.
• B. Query patterns and indexes — what the code asks, and whether an index can answer it.
• C. Integrity and concurrency — constraints, transactions, locking strategy.
• D. Migrations and operations — evolution, restore path, seeds, pooling.

Scope and method

1. Establish scope — the whole schema, one domain's tables, or the migration history. Name it; findings without a boundary don't prioritize.
2. Locate the source of truth — schema files, ORM models, migration directory; note the engine and version (Postgres/MySQL/SQLite behave differently and some findings are engine-specific — say which).
3. Read the project's own words — its glossary (CONTEXT.md, a domain doc) or the vocabulary its models, tests and API already use, and name every finding in those terms; a table or column that contradicts the glossary's own definition is itself a finding, and a report that renames the domain makes the reader translate before they can act.
4. Read schema before queries — a table designed wrong makes every query against it a finding; start where the defects multiply.
5. Grep the query sites — ORM calls and raw SQL both; an anti-pattern that never runs on a hot path is a note, not a FIX. Zero hits is not proof of absence — ripgrep honors .gitignore; re-scan with ignores off before concluding.
6. With a live connection, start from real load — the top statements by total time (calls × mean) from the engine's statement statistics or slow-query log, each mapped to its call site in code; that list orders Track B. Statistics collection off → the load-based part is NOT ASSESSED, and the missing collection is a Track D finding.
7. Score, gate, report — see Output.