WP-05: PlayerSeasonStats Incremental Update Logic #70

New Issue

cal · 2026-03-12T20:55:38Z

cal commented

2026-03-12 20:55:38 +00:00

Description

Implement update_season_stats(game_id) — computes stat deltas from a completed game's stratplay and decision rows, then upserts into player_season_stats. Runs in the post-game callback.

Repo: database
Phase: 1a (Schema & Data Foundation)
Dependencies: WP-02
Complexity: L

Implementation

Accept game_id as input
Query all stratplay rows for that game
Group by (batter_id, batter_team_id) for batting stats
Group by (pitcher_id, pitcher_team_id) for pitching stats
Query decision rows for W/L/S/H/BS
Upsert using INSERT ... ON CONFLICT (player_id, team_id, season) DO UPDATE SET hits = hits + excluded.hits, ...
Set last_game_id and last_updated_at

Note: team_wins and quality_starts are NOT computed. outs stored as raw integer (IP = outs/3 at eval time).

Files

Create: database/app/services/season_stats.py

Tests (write first in `database/tests/test_season_stats_update.py`)

Unit: batting delta extraction from stratplay rows
Unit: pitching delta extraction (k, outs, hits_allowed, etc.)
Unit: decision row extraction (W/L/S/H/BS)
Unit: team scoping — deltas attributed to correct (player_id, team_id)
Integration: insert new row for first-time player
Integration: increment existing row (not replace)
Integration: double-count prevention (same game_id twice)
Integration: multi-team game

Plan reference: docs/prd-evolution/PHASE1_PROJECT_PLAN.md WP-05

## Description Implement `update_season_stats(game_id)` — computes stat deltas from a completed game's `stratplay` and `decision` rows, then upserts into `player_season_stats`. Runs in the post-game callback. **Repo:** `database` **Phase:** 1a (Schema & Data Foundation) **Dependencies:** WP-02 **Complexity:** L ## Implementation 1. Accept `game_id` as input 2. Query all `stratplay` rows for that game 3. Group by `(batter_id, batter_team_id)` for batting stats 4. Group by `(pitcher_id, pitcher_team_id)` for pitching stats 5. Query `decision` rows for W/L/S/H/BS 6. Upsert using `INSERT ... ON CONFLICT (player_id, team_id, season) DO UPDATE SET hits = hits + excluded.hits, ...` 7. Set `last_game_id` and `last_updated_at` Note: `team_wins` and `quality_starts` are NOT computed. `outs` stored as raw integer (IP = outs/3 at eval time). ## Files - **Create:** `database/app/services/season_stats.py` ## Tests (write first in `database/tests/test_season_stats_update.py`) - [ ] Unit: batting delta extraction from stratplay rows - [ ] Unit: pitching delta extraction (k, outs, hits_allowed, etc.) - [ ] Unit: decision row extraction (W/L/S/H/BS) - [ ] Unit: team scoping — deltas attributed to correct (player_id, team_id) - [ ] Integration: insert new row for first-time player - [ ] Integration: increment existing row (not replace) - [ ] Integration: double-count prevention (same game_id twice) - [ ] Integration: multi-team game **Plan reference:** `docs/prd-evolution/PHASE1_PROJECT_PLAN.md` WP-05

cal added this to the Card Evolution Phase 1 milestone 2026-03-12 20:59:10 +00:00

cal added the

evolution

phase-1a

labels 2026-03-12 20:59:22 +00:00

cal referenced this issue

2026-03-12 20:59:51 +00:00

Card Evolution Phase 1 — Master Tracker #81

Claude added the

ai-working

label 2026-03-13 04:31:35 +00:00

cal commented

2026-03-17 15:07:21 +00:00

Schema change notice: PlayerSeasonStats has been split into two separate models — BattingSeasonStats and PitchingSeasonStats — with descriptive column names.

Relevant commits on next-release:

bd8e457 — refactor: split PlayerSeasonStats into BattingSeasonStats and PitchingSeasonStats
6580c1b — refactor: deduplicate pitcher formula and test constants

Key changes for this WP:

Table names are now batting_season_stats and pitching_season_stats
Unique constraint is (player_id, team_id, season) on each table (unchanged pattern)
Column names are fully descriptive: strikeouts (not k/so), wins (not w), earned_runs, runs_allowed, wild_pitches, balks, games_started, etc.
New columns added: sac, ibb, gidp (batting); earned_runs, runs_allowed, hbp, wild_pitches, balks, games_started (pitching)
Incremental update logic should target two separate tables instead of one combined table

**Schema change notice:** `PlayerSeasonStats` has been split into two separate models — `BattingSeasonStats` and `PitchingSeasonStats` — with descriptive column names. Relevant commits on `next-release`: - `bd8e457` — refactor: split PlayerSeasonStats into BattingSeasonStats and PitchingSeasonStats - `6580c1b` — refactor: deduplicate pitcher formula and test constants Key changes for this WP: - Table names are now `batting_season_stats` and `pitching_season_stats` - Unique constraint is `(player_id, team_id, season)` on each table (unchanged pattern) - Column names are fully descriptive: `strikeouts` (not `k`/`so`), `wins` (not `w`), `earned_runs`, `runs_allowed`, `wild_pitches`, `balks`, `games_started`, etc. - New columns added: `sac`, `ibb`, `gidp` (batting); `earned_runs`, `runs_allowed`, `hbp`, `wild_pitches`, `balks`, `games_started` (pitching) - Incremental update logic should target two separate tables instead of one combined table

cal referenced this issue

2026-03-18 00:26:58 +00:00

Card Evolution Phase 1a: Schema & Data Foundation #104

cal commented

2026-03-25 05:30:31 +00:00

Implemented and merged. Refractor system deployed to dev.

cal closed this issue

2026-03-25 05:30:37 +00:00

Sign in to join this conversation.