schema-audit

Overview

Full database schema audit pipeline: discovery → index audit → constraint audit → naming audit → migration safety → scorecard. Outputs a prioritized report with severity ratings and fix recommendations.

Required References

Before starting, read references/backend/schema-audit-checklist.md for the complete audit checklist and scoring criteria.

Step 1: Discovery

1. ORM detection:
   • Check for schema.prisma, prisma/schema.prisma, prisma/ directory (Prisma)
   • Check for alembic/, alembic.ini, files importing sqlalchemy (SQLAlchemy)
   • Check for *.sql migration files, migrations/ directories
   • Check for *.sqlite, *.db files (SQLite)
2. Schema inventory:
   • List all models/tables with their fields and types
   • Map all relationships (1:1, 1:N, M:N)
   • Identify join tables and through models
3. Migration history:
   • List all migrations in chronological order
   • Identify pending/unapplied migrations

Step 2: Index Audit

For each table:

1. Missing indexes:
   • Foreign key columns without indexes
   • Columns used in WHERE clauses (grep for query patterns)
   • Columns used in ORDER BY
   • Composite queries needing compound indexes
2. Redundant indexes:
   • Indexes that are prefixes of other compound indexes
   • Duplicate indexes on the same column(s)
   • Indexes on boolean columns with low cardinality
3. Index naming:
   • Verify consistent naming convention (e.g., idx_{table}_{columns})

Step 3: Constraint Audit

1. Missing foreign keys:
   • Fields ending in _id or Id without FK constraints
   • Relationships defined in ORM but missing at DB level
2. Missing NOT NULL:
   • Required business fields that allow NULL
   • Enum/status fields without defaults
3. Missing unique constraints:
   • Natural keys (email, slug, code) without unique indexes
   • Composite uniqueness (tenant_id + name) not enforced
4. Cascade rules:
   • ON DELETE behavior for each FK (CASCADE vs SET NULL vs RESTRICT)
   • Orphan risk analysis