[P2] Database indexing for analytics queries

[hazeliscoding]

Problem

No indexes are defined beyond EF Core auto-generated key indexes. Analytics queries (compatibility, rating gaps) join across participant entries and ratings and will be slow at scale.

Requirements

• Analyze query patterns for analytics endpoints (5.1–5.5)
• Add composite indexes on:
  • ParticipantEntry (WatchSpaceAnimeId, UserId)
  • Rating (WatchSpaceAnimeId, UserId)
  • WatchSpaceAnime (WatchSpaceId, SharedStatus)
  • WatchSession (WatchSpaceAnimeId, SessionDateUtc)
• Create an EF Core migration with the indexes
• Validate with EXPLAIN ANALYZE on representative queries

Acceptance Criteria

• Analytics endpoints perform well with 100+ anime per watch space
• No sequential scans on analytics-critical tables
• Migration is reversible
• EXPLAIN ANALYZE output documented for key queries

References

• Roadmap §6 — Data Integrity & Edge Cases