Database Audit

Analyze database layer for performance and correctness issues. Output to .claude/audits/AUDIT_DB.md.

Check

Query Patterns

• N+1 queries (loops with individual fetches)
• Unbounded fetches (no LIMIT, no pagination)
• SELECT * instead of specific columns
• Missing WHERE clauses on large tables
• Queries inside loops

Schema Issues

• Missing indexes on frequently queried columns
• Missing foreign key constraints
• No cascade rules defined
• Inconsistent naming conventions
• Missing timestamps (created_at, updated_at)

Connection & Pooling

• Connection pool configuration
• Connection leaks (connections not released)
• Missing connection timeouts
• No retry logic for transient failures

Migrations

• Unsafe migrations (data loss potential)
• Missing down migrations
• Schema drift between environments
• Large table alterations without planning

ORM Usage

• Eager loading not configured (N+1 source)
• Raw queries with string interpolation (SQL injection)
• Missing transaction boundaries
• Inconsistent model definitions

Grep

# N+1 patterns - queries in loops
grep -rn "for.*await.*find\|forEach.*await.*query" src --include="*.ts"

# Unbounded fetches
grep -rn "findMany()\|find({})\|SELECT \*" src --include="*.ts"

# Raw queries (potential injection)
grep -rn "\$queryRaw\|\$executeRaw\|\.query(" src --include="*.ts"

# Missing indexes - check schema
grep -rn "@index\|@@index\|createIndex" prisma --include="*.prisma"

# Connection pool settings
grep -rn "pool\|connectionLimit\|max_connections" . --include="*.ts" --include="*.env*"

Output

# Database Audit

## Summary
| Category | Critical | High | Medium | Low |
|----------|----------|------|--------|-----|
| Queries | X | X | X | X |
| Schema | X | X | X | X |
| Connections | X | X | X | X |
| Migrations | X | X | X | X |

**Database:** [Detected DB type]
**ORM:** [Prisma/Drizzle/TypeORM/etc.]

## Critical

### DB-001: N+1 Query in User Loading
**File:** `src/api/users.ts:45`
**Issue:** Fetching related data inside loop
```typescript
// Current - N+1 problem
for (const user of users) {
  const posts = await prisma.post.findMany({ where: { userId: user.id } });
}

Impact: O(n) queries instead of O(1). 100 users = 101 queries. Fix:

// Use include for eager loading
const users = await prisma.user.findMany({
  include: { posts: true }
});

DB-002: Unbounded Query on Large Table

File: src/api/products.ts:23 Issue: No LIMIT on product listing

const products = await prisma.product.findMany();

Impact: Memory exhaustion with large datasets Fix:

const products = await prisma.product.findMany({
  take: 100,
  skip: page * 100
});

High

DB-003: Missing Index on Frequently Queried Column

File: prisma/schema.prisma Issue: email column queried often but not indexed Impact: Full table scan on every login Fix:

model User {
  email String @unique
  @@index([email])
}

DB-004: Raw Query with String Interpolation

File: src/lib/search.ts:67 Issue: SQL injection vulnerability

const results = await prisma.$queryRaw`SELECT * FROM users WHERE name LIKE '%${search}%'`;

Fix: Use parameterized queries

Medium

DB-005: No Connection Pool Configuration

File: prisma/schema.prisma Issue: Using default pool settings Impact: Connection exhaustion under load Fix: Configure connection_limit in DATABASE_URL

DB-006: Missing Transaction on Related Writes

File: src/api/orders.ts:89 Issue: Order and OrderItems created without transaction Impact: Partial writes on failure Fix: Wrap in prisma.$transaction()

Recommendations

1. Add indexes for all columns used in WHERE clauses
2. Enable query logging in development to catch N+1
3. Set connection pool limits appropriate for your hosting
4. Add pagination to all list endpoints
5. Use transactions for multi-table writes

Focus on queries that will cause production problems at scale. Include file:line for every finding.