# 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
```
