BaseballHelm: Compute OBP/SLG/OPS in legacy aggregate recalculation

[njrini99-code]

Problem

recalculatePlayerAggregates only persists career_avg (plus practice/game avgs), but My Stats StatsOverviewCards reads career_obp, career_slg, and career_ops — fields that are never written.

Evidence

• src/app/baseball/actions/stats.ts (lines 515-531): upserts career_avg, practice_avg, game_avg only
• src/components/baseball/player-stats/StatsOverviewCards.tsx (lines 85-88): displays aggregates.career_obp/slg/ops

Why it matters

Players with CSV/practice upload data see AVG populated but OBP/SLG/OPS permanently show "---" in the overview cards, despite sufficient counting stats in baseball_player_stats.

Acceptance criteria

• recalculatePlayerAggregates derives and stores career_obp, career_slg, career_ops from summed counting stats (standard formulas, null on zero denominators).
• My Stats overview cards show the computed slash line after upload.
• Unit test covers a multi-session aggregate with walks/SF/HBP.

[coderabbitai]

🔗 Related PRs

#239 - fix(golf): SG recalibration + stat-formula corrections + CoachHelm regen + silt fix [merged]
#241 - chore(golf): durable guards for stat correctness (SG scales + cross-surface consistency) [merged]
#257 - fix(stats): accuracy + population audit — pagination caps, weighted math, null-honesty, up_and_down writer, staleness safety net [merged]
#339 - fix(stats): recover full detailed stats on coach-view statement_timeout (Ben's blank stats) [merged]

---

📝 Issue Planner

_{Check the box below or use the @coderabbitai plan command to generate an implementation plan and prompts that you can use with your favorite coding assistant.}

---

🧪 Issue enrichment is currently in open beta.

You can configure auto-planning by selecting labels in the issue_enrichment configuration.

To disable automatic issue enrichment, add the following to your .coderabbit.yaml:

issue_enrichment:
  auto_enrich:
    enabled: false

💬 Have feedback or questions? Drop into our discord!

[njrini99-code]

@coderabbitai plan

[coderabbitai]

Coding Plan

Summary

Add career_obp/slg/ops columns to baseball_player_aggregates via a new forward-only migration and regenerate the canonical Supabase types. Extend recalculatePlayerAggregates to sum the relevant counting stats and compute the slash line with standard formulas (null on zero denominators), reading HBP/SF defensively so the math is correct when those values are present and approximates safely when they are absent. Persist the three new fields in the existing upsert; the client (getMyAggregates + StatsOverviewCards) already consumes them, so no UI change is needed. Cover the new logic with a unit test exercising a multi-session aggregate including walks/SF/HBP plus a zero-denominator null case.

Design Choices

Design Choice 1: How should OBP handle the missing `hit_by_pitch` / `sacrifice_flies` columns on `baseball_player_stats`?

Options Considered:

1. Compute the slash line from available columns only, treating HBP/SF as 0 when absent — but have the calculation read the TS-typed hit_by_pitch/sacrifice_flies fields (defaulting to 0) so they are correctly incorporated whenever present (e.g., in test fixtures or future schema additions).
2. Add hit_by_pitch and sacrifice_flies columns to baseball_player_stats via migration and wire them through the CSV/import path so production data actually persists them.

Chosen Option: 1

Rationale: It satisfies the ticket (SLG/OPS fully correct from existing columns; OBP correct when HBP/SF present, and a sound approximation when absent), keeps scope tight, and makes the required unit test meaningful without expanding into import-pipeline schema changes.

Design Choice 2: What numeric precision should the new aggregate columns use?

Options Considered:

1. numeric(5,3) — matches the platform-wide slash-line convention.
2. Bare numeric — matches the neighboring career_avg column.

Chosen Option: 1

Rationale: Aligns with the dominant, repeated OBP/SLG/OPS convention across the schema and round-to-3-decimals usage on the client.

💡 User Tips

Regenerate the plan with different choices with @coderabbitai <feedback>.

Implementation Steps

Phase 1: Persist new slash-line columns on the aggregates table

Establish the storage for career OBP/SLG/OPS so the recalculation has a place to write and the client query (which uses select('*')) automatically surfaces them.

Task 1: Add a forward-only migration for the new aggregate columns

Add the three rate-stat columns to the existing aggregates table.

• Create a new migration file in supabase/migrations/ with a timestamp later than the current latest (20260626000030_baseball_notifications_revoke_anon.sql), following the YYYYMMDDHHMMSS_description.sql convention.
• Use ALTER TABLE public.baseball_player_aggregates ADD COLUMN IF NOT EXISTS for career_obp, career_slg, and career_ops, each typed numeric(5,3).
• No RLS/policy changes are required since this only adds columns to an existing table.

Task 2: Regenerate the canonical Supabase types

Keep the generated DB types in sync so drift checks pass and Row/Insert/Update shapes include the new columns.

• Regenerate src/lib/types/database.ts via the db:types script (do not hand-edit); this updates the baseball_player_aggregates Row/Insert/Update types.
• Confirm BaseballPlayerAggregates in src/lib/types/index.ts already declares career_obp/slg/ops as number | null (it does) — no manual type change expected beyond the regenerated file.

🤖 Prompt for AI agents

Implement the database storage layer for the new slash-line columns. Goal:
ensure `baseball_player_aggregates` has columns for `career_obp`, `career_slg`,
and `career_ops`, and that generated types reflect them.

- Create a new forward-only migration file in `supabase/migrations/` with a
timestamp later than `20260626000030_baseball_notifications_revoke_anon.sql`,
following the `YYYYMMDDHHMMSS_description.sql` naming convention. Do not modify
historical migrations (≤ `20260527120000`).
- In the migration, use `ALTER TABLE public.baseball_player_aggregates ADD
COLUMN IF NOT EXISTS` to add `career_obp`, `career_slg`, and `career_ops`, each
typed `numeric(5,3)`.
- No RLS/policy changes are needed since this only adds columns to an existing
table.
- Regenerate `src/lib/types/database.ts` using the `npm run db:types` script (do
not hand-edit) so the `baseball_player_aggregates` Row/Insert/Update types
include the new columns.
- Verify that `BaseballPlayerAggregates` in `src/lib/types/index.ts` already
declares `career_obp/slg/ops` as `number | null` — no manual edits expected
there beyond the regenerated database types file.
- Verify with `db:types:check` / `check:types-drift` and `npm run check:ledger`.

Phase 2: Compute and store the slash line in recalculation

Add the slash-line math to the single source of truth so every recalculation populates the new fields.

Task 1: Derive career OBP/SLG/OPS in `recalculatePlayerAggregates`

Extend the aggregation logic in src/app/baseball/actions/stats.ts to sum the relevant counting stats and compute the slash line.

• Within the existing function (which already sums at_bats/hits), additionally sum walks, doubles, triples, home_runs, and the TS-typed hit_by_pitch/sacrifice_flies (each defaulting to 0 via || 0) across all of the player's stat rows.
• Compute career_slg = totalBases / AB where totalBases = singles + 2*doubles + 3*triples + 4*home_runs and singles = hits - doubles - triples - home_runs; return null when summed AB is 0.
• Compute career_obp = (hits + walks + hbp) / (AB + walks + hbp + sf); return null when the denominator is 0.
• Compute career_ops = career_obp + career_slg; return null when either component is null.
• Round each computed value to 3 decimals to match the numeric(5,3) column and the client's .toFixed(3) rendering.
• Reuse the standard formula convention already documented in src/lib/baseball/read-models/stats-center.ts for consistency.

Task 2: Add the new fields to the upsert payload

Ensure the computed values are persisted.

• Add career_obp, career_slg, career_ops to the aggregates object upserted into baseball_player_aggregates (same onConflict: 'player_id,team_id' call).
• No change needed to getMyAggregates() (uses select('*')) or StatsOverviewCards.tsx (already reads these fields) — the slash line will populate automatically once persisted.

🤖 Prompt for AI agents

Implement the slash-line computation and persistence in the existing aggregation
function. Goal: have `recalculatePlayerAggregates` derive and store
`career_obp`, `career_slg`, `career_ops`.

- In `src/app/baseball/actions/stats.ts`, within the existing function that
already sums `at_bats`/`hits` across a player's stat rows, additionally sum
`walks`, `doubles`, `triples`, `home_runs`, and the TS-typed
`hit_by_pitch`/`sacrifice_flies` fields (each defaulting to 0 via `|| 0`).
- Compute `career_slg = totalBases / AB`, where `totalBases = singles +
2*doubles + 3*triples + 4*home_runs` and `singles = hits - doubles - triples -
home_runs`; return `null` when summed AB is 0.
- Compute `career_obp = (hits + walks + hbp) / (AB + walks + hbp + sf)`; return
`null` when the denominator is 0.
- Compute `career_ops = career_obp + career_slg`; return `null` when either
component is null.
- Round each computed value to 3 decimals to match the `numeric(5,3)` column
type and the client's `.toFixed(3)` rendering.
- Reuse the standard formula convention already documented in
`src/lib/baseball/read-models/stats-center.ts` for consistency.
- Add `career_obp`, `career_slg`, `career_ops` to the `aggregates` object that
gets upserted into `baseball_player_aggregates`, keeping the same `onConflict:
'player_id,team_id'` call.
- No changes are needed to `getMyAggregates()` (already uses `select('*')`) or
`StatsOverviewCards.tsx` (already reads these fields) — the slash line populates
automatically once persisted.

Phase 3: Test coverage

Lock in the new behavior with a focused unit test following established baseball action test conventions.

Add a test for slash-line aggregation

Create a test verifying multi-session OBP/SLG/OPS computation including walks/SF/HBP.

• Add a new test (e.g., src/app/baseball/actions/__tests__/stats.test.ts) modeled on the mocking style in complete-player-onboarding.test.ts augmented with the table-aware from dispatch pattern from travel.test.ts.
• Mock @/lib/supabase/server so requireCoachAuth/verifyTeamAccess pass: seed auth.getUser(), a baseball_coaches row, and baseball_teams/baseball_team_coach_staff rows; return a fixture array of multi-session baseball_player_stats rows (with at_bats/hits/walks/hit_by_pitch/sacrifice_flies/doubles/triples/home_runs) for the source table.
• Assert the baseball_player_aggregates upsert payload's career_obp/slg/ops against hand-computed expected values using (H+BB+HBP)/(AB+BB+HBP+SF), totalBases/AB, and obp+slg, plus the onConflict: 'player_id,team_id' option.
• Include a zero-denominator case asserting null output, and a sanity check that existing career_avg behavior is unchanged.

🤖 Prompt for AI agents

Add unit test coverage for the new slash-line aggregation logic. Goal: lock in
correct multi-session computation including walks/SF/HBP and zero-denominator
null handling.

- Add a new test file, e.g. `src/app/baseball/actions/__tests__/stats.test.ts`,
modeled on the mocking style used in `complete-player-onboarding.test.ts`,
augmented with the table-aware `from` dispatch pattern from `travel.test.ts`.
- Mock `@/lib/supabase/server` so `requireCoachAuth`/`verifyTeamAccess` pass:
seed `auth.getUser()`, a `baseball_coaches` row, and
`baseball_teams`/`baseball_team_coach_staff` rows.
- Provide a fixture array of multi-session `baseball_player_stats` rows
(including `at_bats`, `hits`, `walks`, `hit_by_pitch`, `sacrifice_flies`,
`doubles`, `triples`, `home_runs`) as the source table data.
- Assert that the upsert payload to `baseball_player_aggregates` contains
`career_obp`, `career_slg`, `career_ops` matching hand-computed expected values
using the formulas `(H+BB+HBP)/(AB+BB+HBP+SF)` for OBP, `totalBases/AB` for SLG,
and `obp+slg` for OPS, along with the `onConflict: 'player_id,team_id'` option.
- Include a zero-denominator test case asserting `null` output for the affected
fields.
- Include a sanity check confirming existing `career_avg` behavior is unchanged
by these additions.

Research

The baseball stats pipeline stores per-session counting stats in baseball_player_stats and rolling aggregates in baseball_player_aggregates. The server action recalculatePlayerAggregates (in src/app/baseball/actions/stats.ts) reads all of a player's stat rows, computes batting-average–based metrics (career_avg, practice_avg, game_avg, last_5/10_avg, pressure_gap, recent_trend, trend_data), and upserts them into baseball_player_aggregates keyed on (player_id, team_id). The client surface StatsOverviewCards.tsx (fed by getMyAggregates() → MyStatsClient.tsx) already reads career_obp/slg/ops from the BaseballPlayerAggregates type, but those columns do not exist on the live table and are never computed, so the cards render "---". The BaseballPlayerAggregates TS interface already declares these three fields as number | null; the gap is purely (a) missing DB columns and (b) missing computation/persistence logic.

---

🚀 Next Steps

🤖 All AI agent prompts combined

Task: 1

Implement the database storage layer for the new slash-line columns. Goal:
ensure `baseball_player_aggregates` has columns for `career_obp`, `career_slg`,
and `career_ops`, and that generated types reflect them.

- Create a new forward-only migration file in `supabase/migrations/` with a
timestamp later than `20260626000030_baseball_notifications_revoke_anon.sql`,
following the `YYYYMMDDHHMMSS_description.sql` naming convention. Do not modify
historical migrations (≤ `20260527120000`).
- In the migration, use `ALTER TABLE public.baseball_player_aggregates ADD
COLUMN IF NOT EXISTS` to add `career_obp`, `career_slg`, and `career_ops`, each
typed `numeric(5,3)`.
- No RLS/policy changes are needed since this only adds columns to an existing
table.
- Regenerate `src/lib/types/database.ts` using the `npm run db:types` script (do
not hand-edit) so the `baseball_player_aggregates` Row/Insert/Update types
include the new columns.
- Verify that `BaseballPlayerAggregates` in `src/lib/types/index.ts` already
declares `career_obp/slg/ops` as `number | null` — no manual edits expected
there beyond the regenerated database types file.
- Verify with `db:types:check` / `check:types-drift` and `npm run check:ledger`.
===============================================================================

Task: 2

Implement the slash-line computation and persistence in the existing aggregation
function. Goal: have `recalculatePlayerAggregates` derive and store
`career_obp`, `career_slg`, `career_ops`.

- In `src/app/baseball/actions/stats.ts`, within the existing function that
already sums `at_bats`/`hits` across a player's stat rows, additionally sum
`walks`, `doubles`, `triples`, `home_runs`, and the TS-typed
`hit_by_pitch`/`sacrifice_flies` fields (each defaulting to 0 via `|| 0`).
- Compute `career_slg = totalBases / AB`, where `totalBases = singles +
2*doubles + 3*triples + 4*home_runs` and `singles = hits - doubles - triples -
home_runs`; return `null` when summed AB is 0.
- Compute `career_obp = (hits + walks + hbp) / (AB + walks + hbp + sf)`; return
`null` when the denominator is 0.
- Compute `career_ops = career_obp + career_slg`; return `null` when either
component is null.
- Round each computed value to 3 decimals to match the `numeric(5,3)` column
type and the client's `.toFixed(3)` rendering.
- Reuse the standard formula convention already documented in
`src/lib/baseball/read-models/stats-center.ts` for consistency.
- Add `career_obp`, `career_slg`, `career_ops` to the `aggregates` object that
gets upserted into `baseball_player_aggregates`, keeping the same `onConflict:
'player_id,team_id'` call.
- No changes are needed to `getMyAggregates()` (already uses `select('*')`) or
`StatsOverviewCards.tsx` (already reads these fields) — the slash line populates
automatically once persisted.
===============================================================================

Task: 3

Add unit test coverage for the new slash-line aggregation logic. Goal: lock in
correct multi-session computation including walks/SF/HBP and zero-denominator
null handling.

- Add a new test file, e.g. `src/app/baseball/actions/__tests__/stats.test.ts`,
modeled on the mocking style used in `complete-player-onboarding.test.ts`,
augmented with the table-aware `from` dispatch pattern from `travel.test.ts`.
- Mock `@/lib/supabase/server` so `requireCoachAuth`/`verifyTeamAccess` pass:
seed `auth.getUser()`, a `baseball_coaches` row, and
`baseball_teams`/`baseball_team_coach_staff` rows.
- Provide a fixture array of multi-session `baseball_player_stats` rows
(including `at_bats`, `hits`, `walks`, `hit_by_pitch`, `sacrifice_flies`,
`doubles`, `triples`, `home_runs`) as the source table data.
- Assert that the upsert payload to `baseball_player_aggregates` contains
`career_obp`, `career_slg`, `career_ops` matching hand-computed expected values
using the formulas `(H+BB+HBP)/(AB+BB+HBP+SF)` for OBP, `totalBases/AB` for SLG,
and `obp+slg` for OPS, along with the `onConflict: 'player_id,team_id'` option.
- Include a zero-denominator test case asserting `null` output for the affected
fields.
- Include a sanity check confirming existing `career_avg` behavior is unchanged
by these additions.

💡 Iterate on the plan with: `@coderabbitai` <feedback>

Example Feedback
- `@coderabbitai` You can skip phase 3. Add a simple unit test case for phase 2.
- `@coderabbitai` For design choice 1 go ahead with option 3 and replan.

---

💬 Have feedback or questions? Drop into our discord!

[njrini99-code]

Fixed and deployed in PR #650 (main 08a94d1) — explicitly named in the PR's fix list. Reopen if it doesn't hold on prod.