[P2] Performance: Add database indexes for common queries

[Pamacea]

Priority

P2 - Medium Priority

Category

Performance

Description

Many database queries filter on fields that lack indexes, causing full table scans. This impacts performance as the dataset grows.

Recommended Indexes

1. World-Based Queries

model Character {
  id          String   @id @default(cuid())
  gameWorldId String
  // Add:
  @@index([gameWorldId, isVisible])
  @@index([gameWorldId, order])
}

model LoreEntry {
  id          String   @id @default(cuid())
  gameWorldId String
  slug        String
  isVisible   Boolean  @default(false)
  // Add:
  @@index([gameWorldId, slug])
  @@index([gameWorldId, isVisible])
  @@unique([gameWorldId, slug])
}

model Pin {
  id          String   @id @default(cuid())
  gameWorldId String
  layerId     String?
  // Add:
  @@index([gameWorldId, layerId])
  @@index([gameWorldId, isVisible])
}

2. User-Based Queries

model Character {
  userId String
  // Add:
  @@index([userId, gameWorldId])
}

3. Foreign Key Indexes

// All foreign keys should be indexed
model GalleryItem {
  pinId        String?
  loreEntryId  String?
  worldId      String
  // Add:
  @@index([pinId])
  @@index([loreEntryId])
  @@index([worldId])
}

Implementation

# Create migration
npx prisma migrate dev --name add_performance_indexes

# Or add to existing migration
npx prisma migrate dev --create-only --name add_indexes

Verification

// Test query performance before/after
console.time('query');
const results = await prisma.character.findMany({
  where: { gameWorldId, isVisible: true },
});
console.timeEnd('query');

Success Criteria

• All foreign keys have indexes
• Common query patterns (worldId + field) have composite indexes
• Query execution time reduced by >50%
• EXPLAIN ANALYZE shows index scans instead of seq scans

Co-Authored-By: Claude Opus 4.6 noreply@anthropic.com