# Skill: Database Schema Design (Prisma + PostgreSQL)

## Identity
- **Skill ID**: `database-schema-design`
- **Domain**: Backend Database Schema & Migrations
- **Technologies**: Prisma ORM, PostgreSQL, Database Migrations
- **Source Agent**: `backend-app-expert.md`

## When to Load This Skill
- Task involves database schema design
- Creating or modifying Prisma models
- Database migrations
- Index optimization
- Files matching: `press-zone-backend/api/prisma/**/*.prisma`, migration files

## Core Patterns

### Prisma Model Definition
```prisma
model TranslationJob {
  id                  String                @id @default(uuid()) @db.Uuid
  user_id             String                @db.Uuid
  client_job_id       String?               // Optional field
  status              TranslationJobStatus  @default(pending)
  source_lang         String                @db.Char(2)
  target_lang         String                @db.Char(2)
  content             String                @db.Text
  content_hash        String                // SHA-256 for deduplication
  translation         String?               @db.Text
  tokens_used         Int                   @default(0)
  cost                Decimal               @default(0) @db.Decimal(10, 4)
  created_at          DateTime              @default(now())
  updated_at          DateTime              @updatedAt
  
  // Relations
  user                User                 @relation(fields: [user_id], references: [id], onDelete: Cascade)
  
  // Indexes for performance
  @@index([user_id, status, created_at])
  @@index([content_hash])
  @@index([created_at])
  @@map("translation_jobs")
}
```

### Enum Definitions
```prisma
enum TranslationJobStatus {
  pending
  processing
  completed
  failed
  cancelled
}

enum PlanTier {
  starter
  professional
  enterprise
}
```

### Relations & Foreign Keys
```prisma
model User {
  id          String   @id @default(uuid()) @db.Uuid
  email       String   @unique
  created_at  DateTime @default(now())
  
  // One-to-many
  api_keys             ApiKey[]
  translation_jobs     TranslationJob[]
  
  // One-to-one
  subscription         Subscription?
  
  @@map("users")
}

model ApiKey {
  id         String   @id @default(uuid()) @db.Uuid
  user_id    String   @db.Uuid
  key_hash   String   @unique
  
  // Cascade delete when user is deleted
  user User @relation(fields: [user_id], references: [id], onDelete: Cascade)
  
  @@index([user_id])
  @@map("api_keys")
}
```

### Index Strategy
```prisma
model TranslationJob {
  // ...fields...
  
  // Composite index for common queries
  @@index([user_id, status, created_at])
  
  // Individual indexes
  @@index([content_hash])
  @@index([created_at])
  @@index([client_job_id])
}
```

### Migrations
```bash
# Generate migration from schema changes
npx prisma migrate dev --name add_tone_field

# Apply migrations to production
npx prisma migrate deploy

# Generate Prisma Client
npx prisma generate

# Reset database (DEV ONLY)
npx prisma migrate reset
```

## Anti-Patterns (Forbidden)

| Mistake | Fix |
|---------|-----|
| Missing `@@map()` for tables | Always specify table name |
| No indexes on foreign keys | Add `@@index([user_id])` |
| Missing `onDelete` cascade | Specify `onDelete: Cascade` or `SetNull` |
| Using `Int` for IDs | Use `String @db.Uuid` for UUIDs |
| No `updated_at` field | Add `updated_at DateTime @updatedAt` |
| Missing unique constraints | Use `@unique` for email, keys |
| No database column types | Specify `@db.Text`, `@db.Uuid`, etc. |

## PostgreSQL Best Practices

### Data Types
| Prisma Type | PostgreSQL Type | Use For |
|-------------|-----------------|---------|
| `String @db.Uuid` | UUID | IDs |
| `String @db.Text` | TEXT | Long content |
| `String @db.VarChar(255)` | VARCHAR | Short strings |
| `Decimal @db.Decimal(10, 2)` | DECIMAL | Money |
| `Int` | INTEGER | Counts, numeric IDs |
| `DateTime` | TIMESTAMP | Dates/times |
| `Boolean` | BOOLEAN | Flags |
| `Json` | JSONB | Flexible data |

### Performance Indexes
```prisma
// Single column index
@@index([email])

// Composite index (order matters!)
@@index([user_id, status, created_at])

// Unique constraint
@unique
@@unique([user_id, site_url])

// Full-text search (raw SQL needed)
@@index([content], type: Gin)
```

## Integration with Other Skills
- **Often combined with**: `api-endpoint-creation`, `authentication-security`
- **For migrations**: Coordinate with `deployment-dockerization`
- **For queries**: Load `queue-management` for background jobs

## Quick Reference

### Common Prisma Commands
```bash
# Schema validation
npx prisma validate

# View database
npx prisma studio

# Format schema
npx prisma format

# Pull schema from existing DB
npx prisma db pull

# Push schema without migration
npx prisma db push
```

### Cascade Delete Options
| Option | Behavior |
|--------|----------|
| `Cascade` | Delete related records |
| `SetNull` | Set foreign key to NULL |
| `Restrict` | Prevent deletion |
| `NoAction` | Database default |

## Validation Checklist
- [ ] All models have `@@map("table_name")`
- [ ] IDs use `@default(uuid()) @db.Uuid`
- [ ] Foreign keys have `onDelete` behavior
- [ ] Indexes on foreign keys and query fields
- [ ] `@unique` on emails, API keys
- [ ] `updated_at` fields use `@updatedAt`
- [ ] Enums defined for fixed values
- [ ] Column types specified (`@db.Text`, `@db.Uuid`)
- [ ] Relations properly defined both sides