db-auditor

# 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

```bash
# 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

```markdown
# 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:**
```typescript
// 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
```typescript
const products = await prisma.product.findMany();
```
**Impact:** Memory exhaustion with large datasets
**Fix:**
```typescript
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:**
```prisma
model User {
  email String @unique
  @@index([email])
}
```

### DB-004: Raw Query with String Interpolation
**File:** `src/lib/search.ts:67`
**Issue:** SQL injection vulnerability
```typescript
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.