# Plan 011: Add Database Indexes

**Priority**: MEDIUM
**Effort**: 1 hour
**Status**: NOT STARTED
**Risk Level**: LOW - Performance optimization

---

## Problem Statement

The database schema is missing composite indexes for common query patterns:
- `(game_id, play_number)` on plays table
- `(game_id, team_id)` on lineups table
- `(game_id, is_active)` on lineups table

This causes sequential scans on tables that could use index lookups.

## Impact

- **Performance**: Slower queries for game recovery
- **Scalability**: Performance degrades with more data
- **Cost**: Higher database CPU usage

## Current Query Patterns

### 1. Get Plays for Game (Recovery)
```python
# operations.py:467-468
select(Play).where(Play.game_id == game_id).order_by(Play.play_number)
```
**Current**: Index on `game_id`, sequential scan for ordering
**Needed**: Composite index `(game_id, play_number)`

### 2. Get Lineups for Team
```python
# operations.py:488-493
select(Lineup).where(
    Lineup.game_id == game_id,
    Lineup.team_id == team_id,
    Lineup.is_active == True
)
```
**Current**: Index on `game_id`, filter on `team_id` and `is_active`
**Needed**: Composite index `(game_id, team_id, is_active)`

### 3. Get Active Players
```python
select(Lineup).where(
    Lineup.game_id == game_id,
    Lineup.is_active == True
)
```
**Current**: Index on `game_id`, filter on `is_active`
**Needed**: Composite index `(game_id, is_active)`

## Implementation Steps

### Step 1: Create Migration (15 min)

```bash
cd /mnt/NV2/Development/strat-gameplay-webapp/backend
alembic revision -m "Add composite indexes for common queries"
```

Create migration file:

```python
"""Add composite indexes for common queries

Revision ID: 005
Revises: 004
Create Date: 2025-01-27

"""
from typing import Sequence, Union
from alembic import op

revision: str = '005'
down_revision: str = '004'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    # Plays table - optimize game recovery and play history
    op.create_index(
        'idx_play_game_number',
        'plays',
        ['game_id', 'play_number'],
        unique=False
    )

    # Lineups table - optimize team lineup queries
    op.create_index(
        'idx_lineup_game_team_active',
        'lineups',
        ['game_id', 'team_id', 'is_active'],
        unique=False
    )

    # Lineups table - optimize active player queries
    op.create_index(
        'idx_lineup_game_active',
        'lineups',
        ['game_id', 'is_active'],
        unique=False
    )

    # Rolls table - optimize roll history queries
    op.create_index(
        'idx_roll_game_type',
        'rolls',
        ['game_id', 'roll_type'],
        unique=False
    )

    # Games table - optimize status queries
    op.create_index(
        'idx_game_status_created',
        'games',
        ['status', 'created_at'],
        unique=False
    )


def downgrade() -> None:
    op.drop_index('idx_play_game_number', table_name='plays')
    op.drop_index('idx_lineup_game_team_active', table_name='lineups')
    op.drop_index('idx_lineup_game_active', table_name='lineups')
    op.drop_index('idx_roll_game_type', table_name='rolls')
    op.drop_index('idx_game_status_created', table_name='games')
```

### Step 2: Update Models (Optional - for documentation) (15 min)

Update `backend/app/models/db_models.py` to document indexes:

```python
class Play(Base):
    __tablename__ = "plays"

    # ... columns ...

    __table_args__ = (
        Index('idx_play_game_number', 'game_id', 'play_number'),
        # ... other constraints ...
    )


class Lineup(Base):
    __tablename__ = "lineups"

    # ... columns ...

    __table_args__ = (
        Index('idx_lineup_game_team_active', 'game_id', 'team_id', 'is_active'),
        Index('idx_lineup_game_active', 'game_id', 'is_active'),
        # ... other constraints ...
    )
```

### Step 3: Apply Migration (5 min)

```bash
cd /mnt/NV2/Development/strat-gameplay-webapp/backend

# Apply migration
alembic upgrade head

# Verify indexes created
psql -d strat_gameplay -c "\di"
```

### Step 4: Verify Query Plans (15 min)

Test that queries use the new indexes:

```sql
-- Check play query uses index
EXPLAIN ANALYZE
SELECT * FROM plays
WHERE game_id = 'some-uuid'
ORDER BY play_number;

-- Should show: Index Scan using idx_play_game_number

-- Check lineup query uses index
EXPLAIN ANALYZE
SELECT * FROM lineups
WHERE game_id = 'some-uuid'
AND team_id = 1
AND is_active = true;

-- Should show: Index Scan using idx_lineup_game_team_active
```

### Step 5: Add Performance Test (15 min)

Create `backend/tests/integration/test_query_performance.py`:

```python
"""Performance tests for database queries."""

import pytest
import time
from uuid import uuid4

class TestQueryPerformance:
    """Tests that queries use indexes efficiently."""

    @pytest.mark.integration
    @pytest.mark.asyncio
    async def test_get_plays_uses_index(self, db_ops, sample_game):
        """Play retrieval should use composite index."""
        # Create 100 plays
        for i in range(100):
            await db_ops.save_play({
                "game_id": sample_game,
                "play_number": i + 1,
                # ... other fields
            })

        # Time the query
        start = time.perf_counter()
        plays = await db_ops.get_plays(sample_game)
        duration = time.perf_counter() - start

        assert len(plays) == 100
        assert duration < 0.1  # Should be < 100ms with index

    @pytest.mark.integration
    @pytest.mark.asyncio
    async def test_get_lineups_uses_index(self, db_ops, sample_game, sample_lineups):
        """Lineup retrieval should use composite index."""
        start = time.perf_counter()
        lineups = await db_ops.get_lineups(sample_game, team_id=1)
        duration = time.perf_counter() - start

        assert len(lineups) == 9
        assert duration < 0.05  # Should be < 50ms with index
```

## Index Summary

| Index Name | Table | Columns | Purpose |
|------------|-------|---------|---------|
| `idx_play_game_number` | plays | (game_id, play_number) | Game recovery, play history |
| `idx_lineup_game_team_active` | lineups | (game_id, team_id, is_active) | Team lineup queries |
| `idx_lineup_game_active` | lineups | (game_id, is_active) | Active player queries |
| `idx_roll_game_type` | rolls | (game_id, roll_type) | Roll history queries |
| `idx_game_status_created` | games | (status, created_at) | Game listing queries |

## Expected Performance Improvement

| Query | Before | After | Improvement |
|-------|--------|-------|-------------|
| Get 100 plays | ~50ms | ~5ms | 10x |
| Get team lineup | ~20ms | ~2ms | 10x |
| List active games | ~100ms | ~10ms | 10x |

## Verification Checklist

- [ ] Migration created and reviewed
- [ ] Migration applied successfully
- [ ] EXPLAIN shows index usage
- [ ] Performance tests pass
- [ ] No regression in other queries

## Rollback Plan

```bash
# Revert migration
alembic downgrade -1
```

Indexes are non-destructive - removing them just reverts to slower queries.

## Dependencies

- Plan 004 (Alembic Migrations) - need migration infrastructure

## Notes

- Monitor index usage with `pg_stat_user_indexes`
- Consider partial indexes for very large tables
- Future: Add covering indexes if SELECT includes specific columns