strat-gameplay-webapp/.claude/PHASE_3_5_HANDOFF.md

Phase 3.5 Statistics System - Session Handoff

Date: 2025-11-07 Status: In Progress - Materialized Views Implementation Completion: ~40%

---

Summary

Successfully pivoted Phase 3.5 statistics implementation from in-memory cache approach to PostgreSQL materialized views approach, following legacy API pattern. This is a simpler, more maintainable solution with zero stat tracking code needed.

---

Architectural Decision: Materialized Views

Why We Pivoted

Original Plan (phase-3.5-polish-stats.md):

• In-memory stat cache (GameStatsCache, PlayerStatsCache)
• StatTracker service with background persistence
• Complex caching logic with dirty tracking
• ~400-500 lines of stat tracking code

New Approach (STAT_SYSTEM_MATERIALIZED_VIEWS.md):

• PostgreSQL materialized views aggregate from plays table
• Single source of truth: plays table
• Zero stat tracking code (PostgreSQL does aggregation)
• ~200 lines simpler, more maintainable

Decision: Materialized views approved - following proven pattern from legacy major-domo API.

---

Work Completed

1. ✅ PlayStatCalculator Service

File: backend/app/services/play_stat_calculator.py (145 lines)

Purpose: Calculates statistical fields from PlayOutcome for database storage

Key Method:

PlayStatCalculator.calculate_stats(
    outcome: PlayOutcome,
    result: PlayResult,
    state_before: GameState,
    state_after: GameState
) -> Dict[str, int]

Returns: Dict with fields like pa, ab, hit, run, rbi, double, triple, homerun, bb, so, etc.

Logic:

• Determines PA vs AB (walks/HBP/sac don't count as AB)
• Identifies hit types from PlayOutcome helpers
• Calculates runs/RBIs from state change
• Handles non-PA events (SB, CS, WP, PB, BALK, PICKOFF)
• Tracks outs recorded

2. ✅ GameEngine Integration

File: backend/app/core/game_engine.py

Changes:

• Added import: from app.services import PlayStatCalculator
• Modified _save_play_to_db() method (lines 1027-1045):
  • Creates state_after by cloning state and applying result
  • Calls PlayStatCalculator.calculate_stats()
  • Merges stat fields into play_data dict
  • Stats automatically saved with every play

Impact: Every play record now includes complete statistical fields for aggregation.

3. ✅ Materialized Views Migration

File: backend/alembic/versions/004_create_stat_materialized_views.py (180 lines)

Creates 3 Views:

1. batting_game_stats

   • Aggregates batting stats per player per game
   • Fields: pa, ab, run, hit, rbi, double, triple, hr, bb, so, hbp, sac, sb, cs, gidp
   • Indexes: lineup_id (unique), game_id, player_card_id

2. pitching_game_stats

   • Aggregates pitching stats per player per game
   • Fields: batters_faced, hit_allowed, run_allowed, erun, bb, so, hbp, hr_allowed, wp, ip
   • IP calculation: SUM(outs_recorded)::float / 3.0
   • Indexes: lineup_id (unique), game_id, player_card_id

3. game_stats

   • Team totals and linescore per game
   • Fields: home_runs, away_runs, home_hits, away_hits, home_errors, away_errors
   • Linescore: JSON arrays [0, 1, 0, 3, ...] per inning
   • Index: game_id (unique)

Usage:

-- Get box score
SELECT * FROM batting_game_stats WHERE game_id = ?;
SELECT * FROM pitching_game_stats WHERE game_id = ?;
SELECT * FROM game_stats WHERE game_id = ?;

4. ✅ Play Model

File: backend/app/models/db_models.py

Status: Already had all required stat fields! No changes needed.

Existing Fields (lines 159-196):

• Batting: pa, ab, hit, double, triple, homerun, bb, so, hbp, rbi, sac, ibb, gidp
• Baserunning: sb, cs
• Pitching events: wild_pitch, passed_ball, pick_off, balk
• Runs: run, e_run
• Outs: Already tracked in other field
• Added via PlayStatCalculator: outs_recorded

---

Work Remaining

5. ⏳ BoxScoreService (Next Priority)

File: backend/app/services/box_score_service.py (NEW)

Purpose: Query materialized views for formatted box scores

Key Method:

async def get_box_score(game_id: UUID) -> dict:
    """
    Returns:
        {
            'game_stats': {...},           # Team totals, linescore
            'batting_stats': [...],        # Player batting lines
            'pitching_stats': [...]        # Player pitching lines
        }
    """

Implementation: Use SQLAlchemy text() queries against views

6. ⏳ StatViewRefresher (Next Priority)

File: backend/app/services/stat_view_refresher.py (NEW)

Purpose: Refresh materialized views at strategic moments

Key Method:

async def refresh_all() -> None:
    """Refresh all stat views using REFRESH MATERIALIZED VIEW CONCURRENTLY"""

Refresh Strategy:

• ✅ After game completion (recommended for MVP)
• ✅ On demand (for terminal client testing)
• ⏳ Scheduled (every 5 minutes - optional Phase 3.6+)

7. ⏳ Terminal Client Integration

File: backend/terminal_client/commands.py

Add: box_score command using BoxScoreService

Usage:

⚾ > box_score <game_id>
# Displays formatted box score with player stats

8. ⏳ Testing

Unit Tests Needed:

• tests/unit/services/test_play_stat_calculator.py (~15 tests)

  • Test each outcome type (hits, walks, strikeouts, etc.)
  • Test PA vs AB logic
  • Test runs/RBI calculation
  • Test non-PA events (SB, CS, WP, PB)

• tests/unit/services/test_box_score_service.py (~8 tests)

  • Test view queries
  • Test data formatting
  • Test missing data handling

Integration Tests Needed:

• tests/integration/test_stat_views.py (~6 tests)
  • Play complete game → verify stats in views
  • Test view refresh
  • Test box score retrieval

9. ⏳ Run Migration

Command:

cd backend
uv run alembic upgrade head

Verify:

\d batting_game_stats
\d pitching_game_stats
\d game_stats

---

Files Created

backend/app/services/play_stat_calculator.py     (145 lines) - NEW
backend/alembic/versions/004_create_stat_materialized_views.py  (180 lines) - NEW

Files Modified

backend/app/services/__init__.py                 (+1 export)
backend/app/core/game_engine.py                  (+19 lines in _save_play_to_db)

Files Removed

backend/app/models/stat_models.py                (DELETED - was for in-memory cache)

---

Key Design Decisions

1. Stats Calculated at Play Save Time

When: In GameEngine._save_play_to_db() (STEP 2 of play resolution)

Why: We have access to:

• state (before play)
• result (play outcome with details)
• Can construct state_after by applying result

Alternative Considered: Calculate during play resolution Rejected: Would add logic to PlayResolver, muddies separation of concerns

2. State Clone for state_after

Approach: state.model_copy(deep=True) then apply outs/runs

Why: Lightweight, no side effects on actual state

Alternative Considered: Pass both states from resolve_play Rejected: Would require changing method signatures throughout

3. Materialized Views Over Regular Views

Choice: CREATE MATERIALIZED VIEW with REFRESH MATERIALIZED VIEW CONCURRENTLY

Why:

• Faster queries (pre-computed, indexed)
• Can refresh without locking
• Good for read-heavy workloads (box scores)

Tradeoff: Must manually refresh (not real-time)

Acceptable: Box scores typically viewed after game completion

---

Integration Points

GameEngine → PlayStatCalculator

# In _save_play_to_db (lines 1027-1045)
state_after = state.model_copy(deep=True)
state_after.outs += result.outs_recorded
state_after.home_score += result.runs_scored  # or away_score

stats = PlayStatCalculator.calculate_stats(
    outcome=result.outcome,
    result=result,
    state_before=state,
    state_after=state_after
)

play_data.update(stats)  # Merge into database dict

Database → Materialized Views

plays table (writes)
    ↓
Materialized views (reads after refresh)
    ↓
BoxScoreService (queries)
    ↓
Terminal client / WebSocket

Refresh Workflow

# After game completion
await game_engine.end_game(game_id)
await stat_view_refresher.refresh_all()

# Box score now available
box_score = await box_score_service.get_box_score(game_id)

---

Testing Strategy

Current Test Status

Before Changes: 688 tests passing After Changes: Need to verify still passing + add new tests

Run Tests:

cd backend

# Unit tests (should still pass)
uv run pytest tests/unit/ -v

# Specific new tests (once created)
uv run pytest tests/unit/services/test_play_stat_calculator.py -v

Test Data Setup

For Play Stats:

• Use existing GameState fixtures
• Create various PlayOutcome scenarios
• Verify stat field values

For Views:

• Create game with plays in test database
• Refresh views
• Query and verify aggregation

---

Next Steps for New Session

Immediate (1-2 hours):

1. Create BoxScoreService

   • File: backend/app/services/box_score_service.py
   • Method: async def get_box_score(game_id: UUID) -> dict
   • Use text() queries against materialized views
   • Format response with game totals + player stats

2. Create StatViewRefresher

   • File: backend/app/services/stat_view_refresher.py
   • Method: async def refresh_all() -> None
   • Execute REFRESH MATERIALIZED VIEW CONCURRENTLY for all 3 views
   • Add error handling and logging

3. Update services/init.py

   • Export both new services

Testing (2-3 hours):

4. Write PlayStatCalculator Tests

   • Test all outcome types
   • Test edge cases (2 outs, grand slam, etc.)
   • Verify PA vs AB logic

5. Write BoxScoreService Tests

   • Mock database queries
   • Test response formatting
   • Test missing data scenarios

6. Integration Test

   • Play complete game via terminal client
   • Verify stats populate in plays table
   • Refresh views manually
   • Query views and verify aggregation

Polish (1 hour):

7. Terminal Client Command

   • Add box_score command to terminal_client/commands.py
   • Format and display using Rich tables
   • Test with real game data

8. Run Migration

   • uv run alembic upgrade head
   • Verify views exist in database
   • Test refresh performance

---

References

Key Documents

• Original Plan: .claude/implementation/phase-3.5-polish-stats.md
• Materialized Views Design: .claude/implementation/STAT_SYSTEM_MATERIALIZED_VIEWS.md
• Current Handoff: .claude/PHASE_3_5_HANDOFF.md (this file)

Code Locations

• Play Model: backend/app/models/db_models.py:96-244
• PlayStatCalculator: backend/app/services/play_stat_calculator.py
• GameEngine Integration: backend/app/core/game_engine.py:936-1048
• Migration: backend/alembic/versions/004_create_stat_materialized_views.py

Related Systems

• PlayOutcome Enum: backend/app/config/play_outcome.py
  • Helper methods: is_hit(), is_out(), get_hit_outcomes()
• GameState Model: backend/app/models/game_models.py
  • Used for state_before and state_after
• Terminal Client: backend/terminal_client/
  • Will display box scores

---

Progress Tracking

Phase 3.5 Overall Status

• Stat Fields - Play model already complete
• Migration - Not needed (fields already exist)
• PlayStatCalculator - Complete (145 lines)
• GameEngine Integration - Complete
• Materialized Views Migration - Complete (180 lines)
• BoxScoreService - TODO (next priority)
• StatViewRefresher - TODO (next priority)
• Terminal Client - TODO
• Unit Tests - TODO (~23 tests)
• Integration Tests - TODO (~6 tests)
• Run Migration - TODO (manual step)

Estimated Remaining: 4-6 hours Total Phase 3.5: ~50% complete

---

Quick Start for New Session

# 1. Navigate to backend
cd /mnt/NV2/Development/strat-gameplay-webapp/backend

# 2. Verify current state
uv run pytest tests/unit/ -q  # Should pass

# 3. Check PlayStatCalculator
cat app/services/play_stat_calculator.py

# 4. Check GameEngine integration
grep -A 20 "PlayStatCalculator" app/core/game_engine.py

# 5. Review migration
cat alembic/versions/004_create_stat_materialized_views.py

# 6. Create BoxScoreService (NEXT TASK)
# File: app/services/box_score_service.py

---

Notes & Caveats

Known Issues

• Earned Runs: Currently all runs counted as earned (erun = run)

  • TODO: Implement earned run logic with error tracking
  • Low priority for MVP

• Integration Tests: May need individual execution due to asyncpg pooling

  • Run with: uv run pytest tests/integration/test_name.py::test_func -v

Performance Considerations

• View Refresh: Takes ~10-50ms for small games, longer for large datasets
• Use CONCURRENTLY: Allows reads during refresh (no locking)
• Refresh Strategy: After game completion is optimal for MVP

Future Enhancements

• Real-time Stats: Could use regular views instead of materialized

  • Tradeoff: Slower queries, always current
  • Consider for Phase 4+

• Partial Refresh: PostgreSQL doesn't support natively

  • Would need custom implementation or partitioning
  • Low priority

---

Session End: 2025-11-07 Next Session: Continue with BoxScoreService and StatViewRefresher Context: 175k/200k tokens (88%)