0569974454
All checks were successful
Build Docker Image / build (pull_request) Successful in 8m32s
Idempotent PostgreSQL migration extracted directly from paperdynasty_dev on 2026-03-23. Replaces PR #84 which had schema drift. Creates evolution_track, evolution_card_state, evolution_tier_boost, evolution_cosmetic, batting_season_stats, pitching_season_stats. Adds card.variant, battingcard.image_url, pitchingcard.image_url. All DDL uses CREATE TABLE IF NOT EXISTS and ADD COLUMN IF NOT EXISTS. Wrapped in a single transaction. Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
208 lines
9.9 KiB
PL/PgSQL
208 lines
9.9 KiB
PL/PgSQL
-- Migration: evolution/refractor schema
|
|
-- Generated from actual dev schema (paperdynasty_dev) on 2026-03-23
|
|
--
|
|
-- Creates:
|
|
-- - evolution_track
|
|
-- - evolution_card_state
|
|
-- - evolution_tier_boost
|
|
-- - evolution_cosmetic
|
|
-- - batting_season_stats
|
|
-- - pitching_season_stats
|
|
-- Adds columns:
|
|
-- - card.variant (INTEGER NULL)
|
|
-- - battingcard.image_url (VARCHAR(500) NULL)
|
|
-- - pitchingcard.image_url (VARCHAR(500) NULL)
|
|
--
|
|
-- All statements are idempotent (IF NOT EXISTS / ADD COLUMN IF NOT EXISTS).
|
|
-- Run order respects FK dependencies: evolution_track before child tables,
|
|
-- season_stats after player and team exist.
|
|
|
|
BEGIN;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- evolution_track
|
|
-- Three rows expected: Batter, SP, RP (card_type is UNIQUE)
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS evolution_track (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
card_type VARCHAR(50) NOT NULL,
|
|
formula VARCHAR(255) NOT NULL,
|
|
t1_threshold INTEGER NOT NULL,
|
|
t2_threshold INTEGER NOT NULL,
|
|
t3_threshold INTEGER NOT NULL,
|
|
t4_threshold INTEGER NOT NULL
|
|
);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS evolution_track_name_key
|
|
ON evolution_track (name);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- evolution_card_state
|
|
-- One row per (player_id, team_id) — tracks tier progression for a roster slot
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS evolution_card_state (
|
|
id SERIAL PRIMARY KEY,
|
|
player_id INTEGER NOT NULL,
|
|
team_id INTEGER NOT NULL,
|
|
track_id INTEGER NOT NULL,
|
|
current_tier INTEGER NOT NULL DEFAULT 0,
|
|
current_value DOUBLE PRECISION NOT NULL DEFAULT 0.0,
|
|
fully_evolved BOOLEAN NOT NULL DEFAULT false,
|
|
last_evaluated_at TIMESTAMP WITHOUT TIME ZONE,
|
|
CONSTRAINT evolution_card_state_player_id_fkey
|
|
FOREIGN KEY (player_id) REFERENCES player(player_id) ON DELETE CASCADE,
|
|
CONSTRAINT evolution_card_state_team_id_fkey
|
|
FOREIGN KEY (team_id) REFERENCES team(id) ON DELETE CASCADE,
|
|
CONSTRAINT evolution_card_state_track_id_fkey
|
|
FOREIGN KEY (track_id) REFERENCES evolution_track(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS evolution_card_state_player_team_uniq
|
|
ON evolution_card_state (player_id, team_id);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- evolution_tier_boost
|
|
-- Defines per-tier stat boosts for each track
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS evolution_tier_boost (
|
|
id SERIAL PRIMARY KEY,
|
|
track_id INTEGER NOT NULL,
|
|
tier INTEGER NOT NULL,
|
|
boost_type VARCHAR(50) NOT NULL,
|
|
boost_target VARCHAR(50) NOT NULL,
|
|
boost_value DOUBLE PRECISION NOT NULL DEFAULT 0.0,
|
|
CONSTRAINT evolution_tier_boost_track_id_fkey
|
|
FOREIGN KEY (track_id) REFERENCES evolution_track(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS evolution_tier_boost_track_tier_type_target_uniq
|
|
ON evolution_tier_boost (track_id, tier, boost_type, boost_target);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- evolution_cosmetic
|
|
-- Cosmetic unlocks tied to tier thresholds; standalone (no FK to card_state)
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS evolution_cosmetic (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
tier_required INTEGER NOT NULL DEFAULT 0,
|
|
cosmetic_type VARCHAR(50) NOT NULL,
|
|
css_class VARCHAR(255),
|
|
asset_url VARCHAR(500)
|
|
);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS evolution_cosmetic_name_key
|
|
ON evolution_cosmetic (name);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- batting_season_stats
|
|
-- Accumulates in-season batting stats per player per team per season.
|
|
-- last_game_id nullable FK to stratgame; SET NULL on delete.
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS batting_season_stats (
|
|
id SERIAL PRIMARY KEY,
|
|
player_id INTEGER NOT NULL,
|
|
team_id INTEGER NOT NULL,
|
|
season INTEGER NOT NULL,
|
|
games INTEGER NOT NULL DEFAULT 0,
|
|
pa INTEGER NOT NULL DEFAULT 0,
|
|
ab INTEGER NOT NULL DEFAULT 0,
|
|
hits INTEGER NOT NULL DEFAULT 0,
|
|
doubles INTEGER NOT NULL DEFAULT 0,
|
|
triples INTEGER NOT NULL DEFAULT 0,
|
|
hr INTEGER NOT NULL DEFAULT 0,
|
|
rbi INTEGER NOT NULL DEFAULT 0,
|
|
runs INTEGER NOT NULL DEFAULT 0,
|
|
bb INTEGER NOT NULL DEFAULT 0,
|
|
strikeouts INTEGER NOT NULL DEFAULT 0,
|
|
hbp INTEGER NOT NULL DEFAULT 0,
|
|
sac INTEGER NOT NULL DEFAULT 0,
|
|
ibb INTEGER NOT NULL DEFAULT 0,
|
|
gidp INTEGER NOT NULL DEFAULT 0,
|
|
sb INTEGER NOT NULL DEFAULT 0,
|
|
cs INTEGER NOT NULL DEFAULT 0,
|
|
last_game_id INTEGER,
|
|
last_updated_at TIMESTAMP WITHOUT TIME ZONE,
|
|
CONSTRAINT batting_season_stats_player_id_fkey
|
|
FOREIGN KEY (player_id) REFERENCES player(player_id) ON DELETE CASCADE,
|
|
CONSTRAINT batting_season_stats_team_id_fkey
|
|
FOREIGN KEY (team_id) REFERENCES team(id) ON DELETE CASCADE,
|
|
CONSTRAINT batting_season_stats_last_game_id_fkey
|
|
FOREIGN KEY (last_game_id) REFERENCES stratgame(id) ON DELETE SET NULL
|
|
);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS batting_season_stats_player_team_season_uniq
|
|
ON batting_season_stats (player_id, team_id, season);
|
|
|
|
CREATE INDEX IF NOT EXISTS batting_season_stats_player_season_idx
|
|
ON batting_season_stats (player_id, season);
|
|
|
|
CREATE INDEX IF NOT EXISTS batting_season_stats_team_season_idx
|
|
ON batting_season_stats (team_id, season);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- pitching_season_stats
|
|
-- Accumulates in-season pitching stats per player per team per season.
|
|
-- last_game_id nullable FK to stratgame; SET NULL on delete.
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS pitching_season_stats (
|
|
id SERIAL PRIMARY KEY,
|
|
player_id INTEGER NOT NULL,
|
|
team_id INTEGER NOT NULL,
|
|
season INTEGER NOT NULL,
|
|
games INTEGER NOT NULL DEFAULT 0,
|
|
games_started INTEGER NOT NULL DEFAULT 0,
|
|
outs INTEGER NOT NULL DEFAULT 0,
|
|
strikeouts INTEGER NOT NULL DEFAULT 0,
|
|
bb INTEGER NOT NULL DEFAULT 0,
|
|
hits_allowed INTEGER NOT NULL DEFAULT 0,
|
|
runs_allowed INTEGER NOT NULL DEFAULT 0,
|
|
earned_runs INTEGER NOT NULL DEFAULT 0,
|
|
hr_allowed INTEGER NOT NULL DEFAULT 0,
|
|
hbp INTEGER NOT NULL DEFAULT 0,
|
|
wild_pitches INTEGER NOT NULL DEFAULT 0,
|
|
balks INTEGER NOT NULL DEFAULT 0,
|
|
wins INTEGER NOT NULL DEFAULT 0,
|
|
losses INTEGER NOT NULL DEFAULT 0,
|
|
holds INTEGER NOT NULL DEFAULT 0,
|
|
saves INTEGER NOT NULL DEFAULT 0,
|
|
blown_saves INTEGER NOT NULL DEFAULT 0,
|
|
last_game_id INTEGER,
|
|
last_updated_at TIMESTAMP WITHOUT TIME ZONE,
|
|
CONSTRAINT pitching_season_stats_player_id_fkey
|
|
FOREIGN KEY (player_id) REFERENCES player(player_id) ON DELETE CASCADE,
|
|
CONSTRAINT pitching_season_stats_team_id_fkey
|
|
FOREIGN KEY (team_id) REFERENCES team(id) ON DELETE CASCADE,
|
|
CONSTRAINT pitching_season_stats_last_game_id_fkey
|
|
FOREIGN KEY (last_game_id) REFERENCES stratgame(id) ON DELETE SET NULL
|
|
);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS pitching_season_stats_player_team_season_uniq
|
|
ON pitching_season_stats (player_id, team_id, season);
|
|
|
|
CREATE INDEX IF NOT EXISTS pitching_season_stats_player_season_idx
|
|
ON pitching_season_stats (player_id, season);
|
|
|
|
CREATE INDEX IF NOT EXISTS pitching_season_stats_team_season_idx
|
|
ON pitching_season_stats (team_id, season);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Column additions to existing tables
|
|
-- All use ADD COLUMN IF NOT EXISTS for idempotency
|
|
-- ---------------------------------------------------------------------------
|
|
|
|
-- card.variant: nullable integer, identifies refractor/variant tier
|
|
ALTER TABLE card
|
|
ADD COLUMN IF NOT EXISTS variant INTEGER NULL;
|
|
|
|
-- battingcard.image_url: cached S3/CDN URL for rendered card image
|
|
ALTER TABLE battingcard
|
|
ADD COLUMN IF NOT EXISTS image_url VARCHAR(500) NULL;
|
|
|
|
-- pitchingcard.image_url: cached S3/CDN URL for rendered card image
|
|
ALTER TABLE pitchingcard
|
|
ADD COLUMN IF NOT EXISTS image_url VARCHAR(500) NULL;
|
|
|
|
COMMIT;
|