aafe527d51
All checks were successful
Reindex Knowledge Base / reindex (push) Successful in 5s
Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
273 lines
12 KiB
Markdown
273 lines
12 KiB
Markdown
# 6. Database Schema
|
|
|
|
[< Back to Index](README.md) | [Next: Variant System >](07-variant-system.md)
|
|
|
|
---
|
|
|
|
## 6.1 New Tables
|
|
|
|
### `player_season_stats` (Early Deliverable — Phase 1)
|
|
|
|
Pre-computed season totals per `(player_id, team_id, season)`. Updated incrementally in the
|
|
post-game callback. Used by the evolution milestone evaluator for fast lookups instead of
|
|
scanning `stratplay`. Also benefits leaderboards, scouting, and any future feature needing
|
|
season totals.
|
|
|
|
**Reference implementation exists in the Major Domo project** (SBA fantasy baseball) — use
|
|
that as the basis for schema design and incremental update logic.
|
|
|
|
```sql
|
|
CREATE TABLE player_season_stats (
|
|
id SERIAL PRIMARY KEY,
|
|
player_id INTEGER NOT NULL REFERENCES player(id),
|
|
team_id INTEGER NOT NULL REFERENCES team(id),
|
|
season INTEGER NOT NULL,
|
|
-- batting totals
|
|
games_batting INTEGER NOT NULL DEFAULT 0,
|
|
pa INTEGER NOT NULL DEFAULT 0,
|
|
ab INTEGER NOT NULL DEFAULT 0,
|
|
hits INTEGER NOT NULL DEFAULT 0,
|
|
hr INTEGER NOT NULL DEFAULT 0,
|
|
doubles INTEGER NOT NULL DEFAULT 0,
|
|
triples INTEGER NOT NULL DEFAULT 0,
|
|
bb INTEGER NOT NULL DEFAULT 0,
|
|
hbp INTEGER NOT NULL DEFAULT 0,
|
|
so INTEGER NOT NULL DEFAULT 0,
|
|
rbi INTEGER NOT NULL DEFAULT 0,
|
|
runs INTEGER NOT NULL DEFAULT 0,
|
|
sb INTEGER NOT NULL DEFAULT 0,
|
|
cs INTEGER NOT NULL DEFAULT 0,
|
|
-- pitching totals
|
|
games_pitching INTEGER NOT NULL DEFAULT 0,
|
|
innings NUMERIC(5,1) NOT NULL DEFAULT 0,
|
|
k INTEGER NOT NULL DEFAULT 0,
|
|
bb_allowed INTEGER NOT NULL DEFAULT 0,
|
|
hits_allowed INTEGER NOT NULL DEFAULT 0,
|
|
hr_allowed INTEGER NOT NULL DEFAULT 0,
|
|
wins INTEGER NOT NULL DEFAULT 0,
|
|
losses INTEGER NOT NULL DEFAULT 0,
|
|
saves INTEGER NOT NULL DEFAULT 0,
|
|
holds INTEGER NOT NULL DEFAULT 0,
|
|
blown_saves INTEGER NOT NULL DEFAULT 0,
|
|
-- metadata
|
|
last_game_id INTEGER REFERENCES stratgame(id), -- last game included in totals
|
|
last_updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
CONSTRAINT uq_player_season UNIQUE (player_id, team_id, season)
|
|
);
|
|
CREATE INDEX idx_player_season_stats_team ON player_season_stats(team_id, season);
|
|
CREATE INDEX idx_player_season_stats_player ON player_season_stats(player_id, season);
|
|
```
|
|
|
|
**Update pattern:** After each game completes, the post-game callback computes deltas from the
|
|
game's `stratplay`/`decision` rows and adds them to the running totals via
|
|
`UPDATE ... SET hits = hits + delta_hits, ...`. The `last_game_id` field provides an audit trail
|
|
and prevents double-counting if the callback retries.
|
|
|
|
**Backfill:** A one-time backfill script aggregates all existing `stratplay`/`decision` rows
|
|
into this table, grouped by `(player_id, team_id, season)`.
|
|
|
|
---
|
|
|
|
### `evolution_track`
|
|
|
|
Defines available evolution tracks. Populated by admin at launch and expandable over time.
|
|
|
|
```sql
|
|
CREATE TABLE evolution_track (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL, -- e.g. "Batter", "Starting Pitcher", "Relief Pitcher"
|
|
card_type VARCHAR(20) NOT NULL, -- 'batter', 'sp', 'rp'
|
|
description TEXT,
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW()
|
|
);
|
|
```
|
|
|
|
Tracks are universal per card type — not rarity-gated. All batters follow the same track
|
|
regardless of whether they are Replacement or Hall of Fame. Rarity upgrade at T4 is universal
|
|
(capped at HoF). See [03-tracks.md](03-tracks.md) for design rationale.
|
|
|
|
### `evolution_milestone`
|
|
|
|
Defines individual milestones within a track. Each track has 9-10 milestones across 4 tiers.
|
|
|
|
```sql
|
|
CREATE TABLE evolution_milestone (
|
|
id SERIAL PRIMARY KEY,
|
|
track_id INTEGER NOT NULL REFERENCES evolution_track(id),
|
|
tier SMALLINT NOT NULL CHECK (tier BETWEEN 1 AND 4),
|
|
name VARCHAR(200) NOT NULL, -- e.g. "First Steps: Win 3 games"
|
|
description TEXT NOT NULL, -- Player-facing description
|
|
challenge_type VARCHAR(50) NOT NULL, -- 'win_games', 'record_hits', 'record_hr',
|
|
-- 'record_k', 'record_sv_hd', 'record_qs',
|
|
-- 'complete_gauntlet', 'win_gauntlet',
|
|
-- 'complete_campaign_stage'
|
|
threshold INTEGER NOT NULL, -- Quantity required (e.g. 15 for 15 hits)
|
|
game_mode VARCHAR(30), -- NULL = any mode, or 'campaign', 'gauntlet', etc.
|
|
sort_order SMALLINT NOT NULL DEFAULT 0
|
|
);
|
|
```
|
|
|
|
### `evolution_card_state`
|
|
|
|
Cached evolution tier and metadata per `(player_id, team_id)` pair. This is the fundamental
|
|
evolution unit — all card instances sharing the same `player_id` on the same team share this
|
|
single record. Duplicate cards are not differentiated.
|
|
|
|
This is a cache — authoritative data lives in the game tables (stratplay, decision, stratgame).
|
|
Recomputable via evaluate endpoint.
|
|
|
|
```sql
|
|
CREATE TABLE evolution_card_state (
|
|
id SERIAL PRIMARY KEY,
|
|
player_id INTEGER NOT NULL REFERENCES player(id),
|
|
team_id INTEGER NOT NULL REFERENCES team(id),
|
|
track_id INTEGER NOT NULL REFERENCES evolution_track(id),
|
|
current_tier SMALLINT NOT NULL DEFAULT 0, -- 0 = no tier complete yet
|
|
variant INTEGER, -- current variant hash (shared by all card instances)
|
|
progress_since TIMESTAMP NOT NULL, -- earliest card.created_at for this player+team
|
|
last_evaluated_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
fully_evolved BOOLEAN NOT NULL DEFAULT FALSE,
|
|
initial_rarity_id INTEGER REFERENCES rarity(id),
|
|
final_rarity_id INTEGER REFERENCES rarity(id), -- set on T4 completion
|
|
CONSTRAINT uq_evolution_player_team UNIQUE (player_id, team_id)
|
|
);
|
|
CREATE INDEX idx_evolution_card_state_team ON evolution_card_state(team_id);
|
|
CREATE INDEX idx_evolution_card_state_player ON evolution_card_state(player_id);
|
|
```
|
|
|
|
**Key design points:**
|
|
- `card_id` is intentionally absent — the state belongs to `(player_id, team_id)`, not to a
|
|
card instance. All card instances with the same player_id on the same team read from this record.
|
|
- `variant` is stored here (the single source of truth) and propagated to all matching
|
|
`card.variant` fields when it changes.
|
|
- `progress_since` uses the earliest `card.created_at` among all cards of this player on this team.
|
|
|
|
### `evolution_progress`
|
|
|
|
Tracks per-milestone completion state for each `(player_id, team_id)`. One row per (card_state, milestone).
|
|
|
|
```sql
|
|
CREATE TABLE evolution_progress (
|
|
id SERIAL PRIMARY KEY,
|
|
card_state_id INTEGER NOT NULL REFERENCES evolution_card_state(id),
|
|
milestone_id INTEGER NOT NULL REFERENCES evolution_milestone(id),
|
|
current_count INTEGER NOT NULL DEFAULT 0,
|
|
completed BOOLEAN NOT NULL DEFAULT FALSE,
|
|
completed_at TIMESTAMP,
|
|
CONSTRAINT uq_card_milestone UNIQUE (card_state_id, milestone_id)
|
|
);
|
|
CREATE INDEX idx_evolution_progress_card_state ON evolution_progress(card_state_id);
|
|
```
|
|
|
|
### `evolution_tier_boost`
|
|
|
|
Audit record of rating changes applied when a tier is completed. Allows rollback and inspection.
|
|
|
|
```sql
|
|
CREATE TABLE evolution_tier_boost (
|
|
id SERIAL PRIMARY KEY,
|
|
card_state_id INTEGER NOT NULL REFERENCES evolution_card_state(id),
|
|
tier SMALLINT NOT NULL,
|
|
battingcard_id INTEGER REFERENCES battingcard(id), -- NULL if pitcher
|
|
pitchingcard_id INTEGER REFERENCES pitchingcard(id), -- NULL if batter
|
|
variant_created INTEGER NOT NULL, -- the variant hash written
|
|
boost_delta_json JSONB NOT NULL, -- column deltas applied, keyed by vs_hand
|
|
profile_used VARCHAR(50) NOT NULL, -- 'auto_power', 'auto_contact', 'override_contact', etc.
|
|
applied_at TIMESTAMP NOT NULL DEFAULT NOW()
|
|
);
|
|
```
|
|
|
|
### `evolution_cosmetic`
|
|
|
|
Tracks cosmetic purchases per `(player_id, team_id)` via the evolution_card_state FK. All
|
|
duplicate card instances of the same player on the same team share cosmetics. This is the
|
|
primary revenue table for the evolution system.
|
|
|
|
```sql
|
|
CREATE TABLE evolution_cosmetic (
|
|
id SERIAL PRIMARY KEY,
|
|
card_state_id INTEGER NOT NULL REFERENCES evolution_card_state(id),
|
|
cosmetic_type VARCHAR(50) NOT NULL, -- 'frame_gold', 'frame_animated', 'border_glow',
|
|
-- 'shimmer_effect', 'tier_badge_premium', etc.
|
|
cosmetic_key VARCHAR(100) NOT NULL, -- specific variant/skin identifier
|
|
cost_paid INTEGER NOT NULL, -- manticoins spent
|
|
purchased_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
active BOOLEAN NOT NULL DEFAULT TRUE
|
|
);
|
|
CREATE INDEX idx_evolution_cosmetic_card ON evolution_cosmetic(card_state_id);
|
|
```
|
|
|
|
**Note:** `team_id` is not stored directly — it is available via `card_state_id → evolution_card_state.team_id`.
|
|
This avoids redundancy and ensures cosmetics are always consistent with the owning team.
|
|
|
|
## 6.2 Modified Tables
|
|
|
|
### `battingcard` / `pitchingcard` — Add `image_url` and `image_format` columns
|
|
|
|
```sql
|
|
ALTER TABLE battingcard ADD COLUMN image_url VARCHAR(500) NULL;
|
|
ALTER TABLE battingcard ADD COLUMN image_format VARCHAR(10) NULL DEFAULT 'png';
|
|
ALTER TABLE pitchingcard ADD COLUMN image_url VARCHAR(500) NULL;
|
|
ALTER TABLE pitchingcard ADD COLUMN image_format VARCHAR(10) NULL DEFAULT 'png';
|
|
```
|
|
|
|
For `variant = 0` rows, `image_url` remains NULL (use `player.image` / `player.image2` as before).
|
|
For evolution/cosmetic variant rows, `image_url` stores the S3 URL for the rendered card with
|
|
boosts and cosmetics baked in. `image_format` indicates whether the image is `'png'` (static) or
|
|
`'apng'` (animated). This keeps the image co-located with the ratings data it was rendered from.
|
|
The `battingcard`/`pitchingcard` tables are not auth-gated (unlike the ratings tables), so the
|
|
bot can always fetch the image URL.
|
|
|
|
The existing `UNIQUE(player_id, variant)` constraint enforces one set of ratings and one image
|
|
per player per variant hash.
|
|
|
|
### `card` — Add `variant` column
|
|
|
|
```sql
|
|
ALTER TABLE card ADD COLUMN variant INTEGER NULL DEFAULT NULL;
|
|
```
|
|
|
|
`variant` defaults to NULL, meaning the card uses `variant = 0` (base card, `player.image`).
|
|
When evolution state changes (tier completion or cosmetic purchase), the new
|
|
variant hash is computed in `evolution_card_state.variant` and propagated to `card.variant` on
|
|
**all** card instances matching that `(player_id, team_id)` pair. The bot's image lookup:
|
|
|
|
```python
|
|
if card.variant is not None:
|
|
batting_card = BattingCard.get(player_id=card.player_id, variant=card.variant)
|
|
image_url = batting_card.image_url # S3 URL with cosmetics/boosts baked in
|
|
else:
|
|
image_url = card.player.image # standard base card image
|
|
```
|
|
|
|
**Variant propagation:** When `evolution_card_state.variant` changes, all card instances with
|
|
the matching `player_id` owned by the matching `team_id` are updated:
|
|
|
|
```python
|
|
Card.update(variant=new_variant).where(
|
|
Card.player_id == player_id,
|
|
Card.team_id == team_id,
|
|
).execute()
|
|
```
|
|
|
|
Thousands of existing cards will have `variant = NULL` — no backfill needed for the column itself.
|
|
The `evolution_card_state` backfill job handles computing variant hashes for cards with evolution
|
|
progress.
|
|
|
|
## 6.3 Schema Relationships Diagram (text)
|
|
|
|
```
|
|
team ──< evolution_card_state(player_id, team_id) ──> player
|
|
| |
|
|
| (variant propagated to all matching ├──< battingcard(variant=0..<hash>)
|
|
| card instances) │ ├── battingcardratings
|
|
| │ ├── image_url (S3, nullable)
|
|
| card.variant ← evo_state.variant │ └── image_format ('png' | 'apng')
|
|
| │
|
|
├──< evolution_progress >── evolution_milestone >── evolution_track
|
|
|
|
|
├──< evolution_tier_boost
|
|
|
|
|
└──< evolution_cosmetic
|
|
```
|