DB Audit

/db-audit — Database Audit (Drizzle ORM + PostgreSQL)

Skill for database projects focused on Drizzle ORM and PostgreSQL. Automatically detects the DB configuration, checks schema design, migrations, connection setup, security, and performance. Patterns are general enough for other ORMs/DBs, but checks and templates are PostgreSQL-optimized.

Complementary to /audit and /project-audit: This skill checks exclusively database-specific topics. Generic code quality, SEO, a11y, etc. belong to /audit or /project-audit.

Modes

Detect the mode from user input:

• start -> Mode 1 (Scan project, identify areas)
• continue -> Mode 2 (Process next areas/fixes)
• status -> Mode 3 (Show progress)
• report -> Mode 4 (Structured Markdown report)
• refresh -> Mode 5 (Check for new Drizzle/PostgreSQL releases)
• auto -> Mode 6 (Fully autonomous run)

Mode 1: /db-audit start — Scan Project

Auto-Detection (in this order)

1. package.json -> drizzle-orm, drizzle-kit, pg, postgres, @neondatabase/serverless, other DB packages
2. drizzle.config.ts/js -> Dialect, schema path, migrations directory, connection config
3. Schema directory -> src/lib/server/db/, src/db/, db/, src/schema/ — tables, relations, enums
4. Docker Compose -> PostgreSQL service, volumes, health checks, env variables
5. .env / .env.example -> DATABASE_URL pattern, credentials handling
6. Framework integration -> SvelteKit ($lib/server/db), Astro (server endpoints), Next.js (server actions)

Version Check

After detection:

1. Read installed Drizzle version from package.json
2. Check latest version: npm view drizzle-orm version + npm view drizzle-kit version
3. Determine PostgreSQL version (Docker image, managed service, or psql --version)
4. Display result table

Flow After Detection

1. Display result table: Drizzle version, PostgreSQL version, framework, schema path, connection type
2. Prioritize areas by relevance (max 2 per session)
3. Check each checkpoint against the project
4. Save findings to .db-audit-state.json
5. Display summary + prioritized list
6. Session end: "Start next session with /db-audit continue"

Mode 2: /db-audit continue — Resume

1. Read .db-audit-state.json
2. Generate smart recommendation:

   IF open CRITICAL findings > 0:
     -> "Recommendation: Fix {N} CRITICAL findings first ({IDs})"
   IF open HIGH findings > 3:
     -> "Recommendation: Fix HIGH findings, then continue"
   ELSE IF areas open:
     -> "Recommendation: Next areas ({area names})"
   ELSE:
     -> "Recommendation: Fix remaining findings"
   

3. If areas open -> process next 2 areas
4. If all areas done -> next 5 findings by priority
5. Load fix templates (from fix-templates.md) where applicable
6. Ask user: Follow recommendation? Choose different? Skip?
7. Implement fixes -> verify -> update state

Mode 3: /db-audit status — Progress

1. Read .db-audit-state.json
2. Display table: Done/Open/Total per area + severity
3. Next recommended action

Mode 4: /db-audit report — Markdown Report

Generate a structured Markdown report.

1. Read state file
2. Group all findings by area
3. Report with executive summary, findings per area, recommendations
4. Insert trend analysis (if history available):

   Trend (recent audits):
     CRITICAL: 3 -> 1 -> 0  (resolved)
     HIGH:     5 -> 3 -> 2  (declining)
     Total:   12 -> 8 -> 5
   Assessment: Project is steadily improving.
   

5. Save report as DB-AUDIT-REPORT-{YYYY-MM-DD}.md in project root
6. If previous reports exist: Diff section (new/resolved since last report)

Mode 5: /db-audit refresh — Check New Releases

1. npm view drizzle-orm version + npm view drizzle-kit version -> latest version
2. If newer version than in state: check release notes via context7 or WebSearch
3. Show delta:

   Last checked: drizzle-orm 0.38.x / drizzle-kit 0.30.x
   Current:      drizzle-orm 0.39.x / drizzle-kit 0.31.x
   New changes:  Breaking changes, new features
   -> Schema/migration review recommended
   

4. Check PostgreSQL major version updates (Docker Hub tags)
5. Inform user + recommendation

Mode 6: /db-audit auto — Autonomous Run

Fully autonomous DB audit without prompts.

Flow

1. Auto-detection as in start
2. All areas processed (no 2-area limitation)
3. Document findings with fix templates from fix-templates.md
4. Context management: When context runs low:
   • Write state immediately
   • Create task in .tasks.json with handoff note
   • "New session with /db-audit continue"
5. At end: Summary with prioritized fix list

Severity Order of Areas in Auto Mode

CRITICAL areas first: ENV -> SCHEMA -> SEC -> CONN -> MIG -> QUERY -> PERF -> BAK -> INT -> TOOL

Areas

Check Priorities + Completeness Tracking

See _shared/audit-patterns.md (MUST/SHOULD/CAN markers, completeness counting, layer status standard). Area with <100% MUST checks cannot be marked as done.

Area Details

ENV — Environment

[MUST] Determine PostgreSQL version (Docker image tag, managed service, or psql) [MUST] DATABASE_URL in .env/.env.example present (not hardcoded in code) [MUST] Credentials NOT in repository (no .env in Git) [SHOULD] .env.example with placeholders present [SHOULD] Docker Compose health check for PostgreSQL [CAN] Separate DB for development/testing/production

SCHEMA — Schema Design

[MUST] All tables with primary key (serial/uuid) [MUST] Foreign keys defined for relations [MUST] NOT NULL constraints where semantically required [MUST] Timestamps (createdAt, updatedAt) on relevant tables [SHOULD] Drizzle relations defined (not just FK columns) [SHOULD] Consistent naming convention (snake_case for DB, camelCase for TypeScript) [SHOULD] Enums via pgEnum instead of string columns [SHOULD] Schema split into separate files (per domain/feature) [CAN] CHECK constraints for value range validation [CAN] Composite indexes for frequent WHERE combinations

MIG — Migrations

[MUST] Migration strategy defined (generate+migrate vs push) [MUST] Migrations directory exists and is in Git [MUST] drizzle.config.ts correctly configured (dialect, schema, out) [SHOULD] Migrations not manually edited after generation [SHOULD] Production uses migrate() (not push) [CAN] Migration tests or seed data present

CONN — Connection

[MUST] Connection pool configured (not single connections) [MUST] Pool limits set (max connections) [SHOULD] Idle timeout configured [SHOULD] Connection error handling (retry, graceful shutdown) [SHOULD] SSL connection in production [CAN] Connection monitoring/logging

QUERY — Query Patterns

[MUST] No raw SQL with string interpolation (SQL injection risk) [MUST] Prepared statements or Drizzle query builder [SHOULD] Transactions for related operations [SHOULD] Select only needed columns (no select * equivalent) [SHOULD] Type-safe queries (use Drizzle infer types) [CAN] Prepared statements for recurring queries

SEC — Security

[MUST] No credentials in source code [MUST] SSL for production connections [MUST] Parameterized queries (no string building) [SHOULD] Separate DB user for app (not postgres superuser) [SHOULD] Least-privilege principle (only required permissions) [CAN] Row-level security for multi-tenant [CAN] Audit logging for critical tables

PERF — Performance

[MUST] Indexes on foreign keys [MUST] Indexes on frequently queried columns (WHERE, ORDER BY) [SHOULD] N+1 query detection (loops with DB calls) [SHOULD] Cursor-based pagination instead of OFFSET/LIMIT for large datasets [SHOULD] EXPLAIN ANALYZE for critical queries [CAN] Partial indexes for filtered queries [CAN] GIN indexes for JSONB columns [CAN] Connection pool sizing based on load

BAK — Backup

[MUST] Backup strategy present (pg_dump or managed backups) [MUST] Docker volumes for persistent data (not container-internal) [SHOULD] Automated backup script [SHOULD] Restore tested [CAN] Point-in-time recovery (WAL archiving) [CAN] Offsite backup

TOOL — Tooling

[SHOULD] drizzle-kit studio or pgAdmin for DB management [SHOULD] Health check endpoint for DB connection [CAN] DB monitoring (pg_stat_statements, Grafana) [CAN] Seed script for development data

INT — Integration

[MUST] DB access server-side only (no DB code in client bundle) [MUST] Environment variables loaded correctly (framework-specific) [SHOULD] DB client as singleton (no connection leak on hot reload) [SHOULD] Graceful shutdown (close pool on server stop) [CAN] Type export for frontend (InferSelectModel without DB import)

Severity Definitions

State Schema v2.1 (.db-audit-state.json)

-> Full state schema (JSON example) + migration v1->v2.1: See state-schema.md

Rules

• Context protection: Max 2 areas OR 5 fixes per session. At limit: save state, recommend /db-audit continue.
• Write state immediately: Update .db-audit-state.json after every area and every fix.
• No auto-fix: Document findings, then ask user whether to fix.
• Severity rules:
  • CRITICAL: Data loss risk, SQL injection, unencrypted credentials
  • HIGH: Missing indexes on hot paths, no backup, connection leaks
  • MEDIUM: Missing constraints, suboptimal schema, no migrations
  • LOW: Nice-to-have indexes, monitoring, documentation
• Finding prefix: Always DB-NN, not MIG/SEC like other audit skills.
• Fix templates: Load matching template from fix-templates.md for findings.
• npm view: Run npm view drizzle-orm version before any version statement. Never from memory.
• context7: Use for Drizzle documentation when available.
• PostgreSQL focus: Checks are PostgreSQL-specific. For SQLite/MySQL: inform user, adapt checks.

Session Strategy

Smart Next Steps

After completing the DB audit, recommend relevant follow-up skills:

Output after last area: "Next steps:" + 2-3 most relevant recommendations.

Additional Files

• fix-templates.md — Quick-fix templates for common DB findings
• state-schema.md — State schema v2.1 + migration v1->v2.1

As of: 2026-03-20 (State schema v2.1 migration)