/check-database

Audit this repository's database layer against the 20 common database anti-patterns below. Produce an evidence-backed scorecard, then a prioritized fix list. Optional focus: $ARGUMENTS (if provided, go deeper on that area but still fill the full scorecard).

Method (do this, in order)

1. Detect the stack first. Do not assume. Identify the database and access layer before auditing:
   • DB engine: Postgres / MySQL / SQLite / Mongo / etc. (check package.json, requirements.txt, go.mod, ORM config, docker-compose, env samples).
   • Access layer: raw SQL, Supabase client, Prisma, Drizzle, TypeORM, Sequelize, SQLAlchemy, ActiveRecord, etc.
   • Schema source: migrations dir, schema.prisma, *.sql, or a live DB you can query via an available MCP (Supabase, Postgres, etc.). Read the project's CLAUDE.md / AGENTS.md — it may name the schema source of truth (e.g. "query live DB, migrations are historical only").
2. Gather evidence in parallel. Dispatch Explore/general-purpose agents concurrently for the code-level checks (pagination, SELECT *, N+1, rate limiting, connection handling, secret storage, media storage). Run schema-level checks (indexes, timestamp types, FKs, JSON columns, PK types, query timeout, soft deletes) against the live DB if an MCP is available, otherwise from migrations/schema files.
3. Prefer ground truth over code. For "is there a timeout / index / constraint?" questions, the live DB config beats reading app code. State which source each verdict came from.
4. Never hardcode IDs from other projects. Detect this repo's project ID / connection from its own config.
5. Read-only. Do not modify files, run migrations, or write to the DB. This is an audit.

The 20 checks

For each: give a verdict (✅ clean / ⚠️ partial / ❌ issue / ➖ by-design-or-N/A / ❓ unverifiable), concrete evidence (file:line or query result), and note the source.

1. Indexes on hot columns — are frequently-filtered/joined columns (tenant keys, FKs, status, created_at) indexed? Find unindexed foreign keys specifically (leading-column index missing). Flag high-volume tables hardest.
2. Images/blobs in the DB — binary stored as bytea/BLOB/base64 columns, or externalized to object storage with only URLs in the DB?
3. Connection pooling — new connection per request, or a pool / pooled endpoint (pgbouncer/supavisor) / serverless-managed client? Note if the access pattern is fine for the runtime (serverless vs long-lived).
4. N+1 queries — loops (for/.map/Promise.all over rows) issuing one query per item instead of a batched join / IN / .in(). Cite worst offenders.
5. Pagination on list endpoints — do collection endpoints use limit/offset or cursor pagination, or fetch unbounded rows? Find every endpoint that returns all rows.
6. SELECT * — proportion of queries fetching all columns vs explicit column lists. Note high-frequency offenders.
7. Soft deletes — deleted_at/is_deleted where data loss matters, or hard deletes everywhere? Judge by domain (some hard deletes are intended).
8. Timestamps without timezone — count timestamp without time zone (or naive datetime) columns vs tz-aware. Naive = bug source.
9. Migrations tracked — is there a tracked, ordered migration history, or ad-hoc manual DDL? Note any drift risk between local files and applied state.
10. JSON columns overused — JSON/JSONB used appropriately (flexible metadata) vs as a dumping ground that should be relational/queryable.
11. Foreign key constraints — are relationships enforced by FKs, or implied and unenforced (orphan risk)? Count constraints.
12. Single DB for everything — auth + app data + analytics + logs in one instance? Note cross-impact risk and any mitigations (timeouts, separate schemas).
13. Read replicas at scale — all reads on primary? Judge against actual scale; "none" is fine when small.
14. Auto-increment IDs exposed in URLs — enumerable integer PKs in routes, or opaque UUIDs/slugs? Check which PKs actually reach URLs.
15. Query timeout configured — statement/role timeout at the DB, or app-level abort signals? Check DB role config, not just code.
16. Plaintext passwords/secrets — passwords hashed (bcrypt/scrypt/argon2) or delegated to a managed auth provider? API tokens/credentials encrypted at rest or plaintext? Inspect how encryption keys are derived/stored (hardcoded/deterministic keys are a finding).
17. Backups tested — any evidence of backup config and a tested restore. Usually ops-level → mark ❓ if not verifiable from the repo; recommend a restore drill.
18. Schema-change downtime — do migrations on large tables use lock-safe patterns (CREATE INDEX CONCURRENTLY, nullable-add + backfill) or blocking ALTER?
19. Rate limiting on DB-heavy endpoints — limits on search/list/expensive writes, or unbounded? Note partial coverage and mitigations (RLS, timeouts).
20. Trusting the ORM blindly — does the ORM generate questionable SQL (implicit SELECT *, accidental joins, missing indexes for its query shapes)? Note where raw SQL or query logging would help.

Output format

# Database Audit — <repo name> — <date>

Stack: <engine> + <access layer>. Schema source: <live DB via MCP | migrations | schema file>.

## Scorecard
| # | Issue | Verdict | Evidence (source) |
|---|-------|---------|-------------------|
... all 20 rows ...

## Key context
<any single setting that changes the picture, e.g. role statement_timeout>

## Issues to fix (priority order)
<only the ⚠️/❌ items, each with concrete fix + code/SQL snippet>

## Unverifiable / ops
<❓ items + what to check manually>

## Bottom line
<2-3 sentences: overall posture + top 3 actions>

Keep verdicts honest — call out ✅ clean items briefly; spend detail on real gaps. Quote real evidence, never invent it. If a check can't be evaluated, mark ❓ and say why.