9d0d29ef18
Database Infrastructure: - Added Alembic migration system (alembic.ini, env.py) - Migration 001: Initial schema - Migration 004: Stat materialized views (enhanced) - Migration 005: Composite indexes for performance - operations.py: Session injection support for test isolation - session.py: Enhanced session management Application Updates: - main.py: Integration with new database infrastructure - health.py: Enhanced health checks with pool monitoring Integration Tests: - conftest.py: Session injection pattern for reliable tests - test_operations.py: Database operations tests - test_migrations.py: Migration verification tests Session injection pattern enables: - Production: Auto-commit per operation - Testing: Shared session with automatic rollback - Transactions: Multiple ops, single commit 🤖 Generated with [Claude Code](https://claude.com/claude-code) Co-Authored-By: Claude <noreply@anthropic.com>
289 lines
15 KiB
Python
289 lines
15 KiB
Python
"""Initial database schema
|
|
|
|
Revision ID: 001
|
|
Revises:
|
|
Create Date: 2025-01-27
|
|
|
|
Creates the core tables for the Paper Dynasty Real-Time Game Engine:
|
|
- games: Game container with status, scores, AI configuration
|
|
- plays: At-bat records with 30+ statistical fields
|
|
- lineups: Player assignments and substitution tracking
|
|
- game_sessions: WebSocket state tracking
|
|
- rolls: Dice roll audit trail
|
|
- roster_links: Eligible cards (PD) or players (SBA) per game
|
|
- game_cardset_links: PD league cardset configuration
|
|
"""
|
|
from typing import Sequence, Union
|
|
|
|
from alembic import op
|
|
import sqlalchemy as sa
|
|
from sqlalchemy.dialects import postgresql
|
|
|
|
# revision identifiers, used by Alembic.
|
|
revision: str = '001'
|
|
down_revision: Union[str, None] = None
|
|
branch_labels: Union[str, Sequence[str], None] = None
|
|
depends_on: Union[str, Sequence[str], None] = None
|
|
|
|
|
|
def upgrade() -> None:
|
|
# === GAMES TABLE ===
|
|
op.create_table(
|
|
'games',
|
|
sa.Column('id', postgresql.UUID(as_uuid=True), nullable=False),
|
|
sa.Column('league_id', sa.String(50), nullable=False),
|
|
sa.Column('home_team_id', sa.Integer(), nullable=False),
|
|
sa.Column('away_team_id', sa.Integer(), nullable=False),
|
|
sa.Column('status', sa.String(20), nullable=False, server_default='pending'),
|
|
sa.Column('game_mode', sa.String(20), nullable=False),
|
|
sa.Column('visibility', sa.String(20), nullable=False),
|
|
sa.Column('current_inning', sa.Integer(), nullable=True),
|
|
sa.Column('current_half', sa.String(10), nullable=True),
|
|
sa.Column('home_score', sa.Integer(), nullable=True, server_default='0'),
|
|
sa.Column('away_score', sa.Integer(), nullable=True, server_default='0'),
|
|
sa.Column('home_team_is_ai', sa.Boolean(), nullable=True, server_default='false'),
|
|
sa.Column('away_team_is_ai', sa.Boolean(), nullable=True, server_default='false'),
|
|
sa.Column('ai_difficulty', sa.String(20), nullable=True),
|
|
sa.Column('created_at', sa.DateTime(), nullable=True),
|
|
sa.Column('started_at', sa.DateTime(), nullable=True),
|
|
sa.Column('completed_at', sa.DateTime(), nullable=True),
|
|
sa.Column('winner_team_id', sa.Integer(), nullable=True),
|
|
sa.Column('game_metadata', sa.JSON(), nullable=True),
|
|
sa.PrimaryKeyConstraint('id')
|
|
)
|
|
op.create_index('ix_games_created_at', 'games', ['created_at'])
|
|
op.create_index('ix_games_league_id', 'games', ['league_id'])
|
|
op.create_index('ix_games_status', 'games', ['status'])
|
|
|
|
# === LINEUPS TABLE ===
|
|
op.create_table(
|
|
'lineups',
|
|
sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
|
|
sa.Column('game_id', postgresql.UUID(as_uuid=True), nullable=False),
|
|
sa.Column('team_id', sa.Integer(), nullable=False),
|
|
sa.Column('card_id', sa.Integer(), nullable=True),
|
|
sa.Column('player_id', sa.Integer(), nullable=True),
|
|
sa.Column('position', sa.String(10), nullable=False),
|
|
sa.Column('batting_order', sa.Integer(), nullable=True),
|
|
sa.Column('is_starter', sa.Boolean(), nullable=True, server_default='true'),
|
|
sa.Column('is_active', sa.Boolean(), nullable=True, server_default='true'),
|
|
sa.Column('entered_inning', sa.Integer(), nullable=True, server_default='1'),
|
|
sa.Column('replacing_id', sa.Integer(), nullable=True),
|
|
sa.Column('after_play', sa.Integer(), nullable=True),
|
|
sa.Column('is_fatigued', sa.Boolean(), nullable=True),
|
|
sa.Column('lineup_metadata', sa.JSON(), nullable=True),
|
|
sa.ForeignKeyConstraint(['game_id'], ['games.id'], ondelete='CASCADE'),
|
|
sa.CheckConstraint(
|
|
'(card_id IS NOT NULL)::int + (player_id IS NOT NULL)::int = 1',
|
|
name='lineup_one_id_required'
|
|
),
|
|
sa.PrimaryKeyConstraint('id')
|
|
)
|
|
op.create_index('ix_lineups_game_id', 'lineups', ['game_id'])
|
|
op.create_index('ix_lineups_is_active', 'lineups', ['is_active'])
|
|
op.create_index('ix_lineups_team_id', 'lineups', ['team_id'])
|
|
|
|
# === PLAYS TABLE ===
|
|
op.create_table(
|
|
'plays',
|
|
sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
|
|
sa.Column('game_id', postgresql.UUID(as_uuid=True), nullable=False),
|
|
sa.Column('play_number', sa.Integer(), nullable=False),
|
|
sa.Column('inning', sa.Integer(), nullable=False, server_default='1'),
|
|
sa.Column('half', sa.String(10), nullable=False),
|
|
sa.Column('outs_before', sa.Integer(), nullable=False, server_default='0'),
|
|
sa.Column('batting_order', sa.Integer(), nullable=False, server_default='1'),
|
|
sa.Column('away_score', sa.Integer(), nullable=True, server_default='0'),
|
|
sa.Column('home_score', sa.Integer(), nullable=True, server_default='0'),
|
|
# Players involved
|
|
sa.Column('batter_id', sa.Integer(), nullable=False),
|
|
sa.Column('pitcher_id', sa.Integer(), nullable=False),
|
|
sa.Column('catcher_id', sa.Integer(), nullable=False),
|
|
sa.Column('defender_id', sa.Integer(), nullable=True),
|
|
sa.Column('runner_id', sa.Integer(), nullable=True),
|
|
# Base runners
|
|
sa.Column('on_first_id', sa.Integer(), nullable=True),
|
|
sa.Column('on_second_id', sa.Integer(), nullable=True),
|
|
sa.Column('on_third_id', sa.Integer(), nullable=True),
|
|
# Runner final positions
|
|
sa.Column('on_first_final', sa.Integer(), nullable=True),
|
|
sa.Column('on_second_final', sa.Integer(), nullable=True),
|
|
sa.Column('on_third_final', sa.Integer(), nullable=True),
|
|
sa.Column('batter_final', sa.Integer(), nullable=True),
|
|
# Base state code
|
|
sa.Column('on_base_code', sa.Integer(), nullable=True, server_default='0'),
|
|
# Strategic decisions
|
|
sa.Column('defensive_choices', sa.JSON(), nullable=True),
|
|
sa.Column('offensive_choices', sa.JSON(), nullable=True),
|
|
# Play result
|
|
sa.Column('dice_roll', sa.String(50), nullable=True),
|
|
sa.Column('hit_type', sa.String(50), nullable=True),
|
|
sa.Column('result_description', sa.Text(), nullable=True),
|
|
sa.Column('outs_recorded', sa.Integer(), nullable=False, server_default='0'),
|
|
sa.Column('runs_scored', sa.Integer(), nullable=True, server_default='0'),
|
|
# Defensive details
|
|
sa.Column('check_pos', sa.String(10), nullable=True),
|
|
sa.Column('error', sa.Integer(), nullable=True, server_default='0'),
|
|
# Batting statistics
|
|
sa.Column('pa', sa.Integer(), nullable=True, server_default='0'),
|
|
sa.Column('ab', sa.Integer(), nullable=True, server_default='0'),
|
|
sa.Column('hit', sa.Integer(), nullable=True, server_default='0'),
|
|
sa.Column('double', sa.Integer(), nullable=True, server_default='0'),
|
|
sa.Column('triple', sa.Integer(), nullable=True, server_default='0'),
|
|
sa.Column('homerun', sa.Integer(), nullable=True, server_default='0'),
|
|
sa.Column('bb', sa.Integer(), nullable=True, server_default='0'),
|
|
sa.Column('so', sa.Integer(), nullable=True, server_default='0'),
|
|
sa.Column('hbp', sa.Integer(), nullable=True, server_default='0'),
|
|
sa.Column('rbi', sa.Integer(), nullable=True, server_default='0'),
|
|
sa.Column('sac', sa.Integer(), nullable=True, server_default='0'),
|
|
sa.Column('ibb', sa.Integer(), nullable=True, server_default='0'),
|
|
sa.Column('gidp', sa.Integer(), nullable=True, server_default='0'),
|
|
# Baserunning statistics
|
|
sa.Column('sb', sa.Integer(), nullable=True, server_default='0'),
|
|
sa.Column('cs', sa.Integer(), nullable=True, server_default='0'),
|
|
# Pitching events
|
|
sa.Column('wild_pitch', sa.Integer(), nullable=True, server_default='0'),
|
|
sa.Column('passed_ball', sa.Integer(), nullable=True, server_default='0'),
|
|
sa.Column('pick_off', sa.Integer(), nullable=True, server_default='0'),
|
|
sa.Column('balk', sa.Integer(), nullable=True, server_default='0'),
|
|
# Ballpark power events
|
|
sa.Column('bphr', sa.Integer(), nullable=True, server_default='0'),
|
|
sa.Column('bpfo', sa.Integer(), nullable=True, server_default='0'),
|
|
sa.Column('bp1b', sa.Integer(), nullable=True, server_default='0'),
|
|
sa.Column('bplo', sa.Integer(), nullable=True, server_default='0'),
|
|
# Advanced analytics
|
|
sa.Column('wpa', sa.Float(), nullable=True, server_default='0.0'),
|
|
sa.Column('re24', sa.Float(), nullable=True, server_default='0.0'),
|
|
# Earned/unearned runs
|
|
sa.Column('run', sa.Integer(), nullable=True, server_default='0'),
|
|
sa.Column('e_run', sa.Integer(), nullable=True, server_default='0'),
|
|
# Game situation flags
|
|
sa.Column('is_tied', sa.Boolean(), nullable=True, server_default='false'),
|
|
sa.Column('is_go_ahead', sa.Boolean(), nullable=True, server_default='false'),
|
|
sa.Column('is_new_inning', sa.Boolean(), nullable=True, server_default='false'),
|
|
sa.Column('in_pow', sa.Boolean(), nullable=True, server_default='false'),
|
|
# Play workflow
|
|
sa.Column('complete', sa.Boolean(), nullable=True, server_default='false'),
|
|
sa.Column('locked', sa.Boolean(), nullable=True, server_default='false'),
|
|
# Timestamps
|
|
sa.Column('created_at', sa.DateTime(), nullable=True),
|
|
# Extensibility
|
|
sa.Column('play_metadata', sa.JSON(), nullable=True),
|
|
# Foreign keys
|
|
sa.ForeignKeyConstraint(['game_id'], ['games.id'], ondelete='CASCADE'),
|
|
sa.ForeignKeyConstraint(['batter_id'], ['lineups.id']),
|
|
sa.ForeignKeyConstraint(['pitcher_id'], ['lineups.id']),
|
|
sa.ForeignKeyConstraint(['catcher_id'], ['lineups.id']),
|
|
sa.ForeignKeyConstraint(['defender_id'], ['lineups.id']),
|
|
sa.ForeignKeyConstraint(['runner_id'], ['lineups.id']),
|
|
sa.ForeignKeyConstraint(['on_first_id'], ['lineups.id']),
|
|
sa.ForeignKeyConstraint(['on_second_id'], ['lineups.id']),
|
|
sa.ForeignKeyConstraint(['on_third_id'], ['lineups.id']),
|
|
sa.PrimaryKeyConstraint('id')
|
|
)
|
|
op.create_index('ix_plays_complete', 'plays', ['complete'])
|
|
op.create_index('ix_plays_created_at', 'plays', ['created_at'])
|
|
op.create_index('ix_plays_game_id', 'plays', ['game_id'])
|
|
|
|
# === GAME_SESSIONS TABLE ===
|
|
op.create_table(
|
|
'game_sessions',
|
|
sa.Column('game_id', postgresql.UUID(as_uuid=True), nullable=False),
|
|
sa.Column('connected_users', sa.JSON(), nullable=True),
|
|
sa.Column('last_action_at', sa.DateTime(), nullable=True),
|
|
sa.Column('state_snapshot', sa.JSON(), nullable=True),
|
|
sa.ForeignKeyConstraint(['game_id'], ['games.id'], ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('game_id')
|
|
)
|
|
op.create_index('ix_game_sessions_last_action_at', 'game_sessions', ['last_action_at'])
|
|
|
|
# === ROLLS TABLE ===
|
|
op.create_table(
|
|
'rolls',
|
|
sa.Column('roll_id', sa.String(), nullable=False),
|
|
sa.Column('game_id', postgresql.UUID(as_uuid=True), nullable=False),
|
|
sa.Column('roll_type', sa.String(), nullable=False),
|
|
sa.Column('league_id', sa.String(), nullable=False),
|
|
sa.Column('team_id', sa.Integer(), nullable=True),
|
|
sa.Column('player_id', sa.Integer(), nullable=True),
|
|
sa.Column('roll_data', postgresql.JSONB(astext_type=sa.Text()), nullable=False),
|
|
sa.Column('context', postgresql.JSONB(astext_type=sa.Text()), nullable=True),
|
|
sa.Column('timestamp', sa.DateTime(timezone=True), nullable=False),
|
|
sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()')),
|
|
sa.ForeignKeyConstraint(['game_id'], ['games.id'], ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('roll_id')
|
|
)
|
|
op.create_index('ix_rolls_game_id', 'rolls', ['game_id'])
|
|
op.create_index('ix_rolls_league_id', 'rolls', ['league_id'])
|
|
op.create_index('ix_rolls_player_id', 'rolls', ['player_id'])
|
|
op.create_index('ix_rolls_roll_type', 'rolls', ['roll_type'])
|
|
op.create_index('ix_rolls_team_id', 'rolls', ['team_id'])
|
|
op.create_index('ix_rolls_timestamp', 'rolls', ['timestamp'])
|
|
|
|
# === ROSTER_LINKS TABLE ===
|
|
op.create_table(
|
|
'roster_links',
|
|
sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
|
|
sa.Column('game_id', postgresql.UUID(as_uuid=True), nullable=False),
|
|
sa.Column('card_id', sa.Integer(), nullable=True),
|
|
sa.Column('player_id', sa.Integer(), nullable=True),
|
|
sa.Column('team_id', sa.Integer(), nullable=False),
|
|
sa.ForeignKeyConstraint(['game_id'], ['games.id'], ondelete='CASCADE'),
|
|
sa.CheckConstraint(
|
|
'(card_id IS NOT NULL)::int + (player_id IS NOT NULL)::int = 1',
|
|
name='roster_link_one_id_required'
|
|
),
|
|
sa.UniqueConstraint('game_id', 'card_id', name='uq_game_card'),
|
|
sa.UniqueConstraint('game_id', 'player_id', name='uq_game_player'),
|
|
sa.PrimaryKeyConstraint('id')
|
|
)
|
|
op.create_index('ix_roster_links_game_id', 'roster_links', ['game_id'])
|
|
op.create_index('ix_roster_links_team_id', 'roster_links', ['team_id'])
|
|
|
|
# === GAME_CARDSET_LINKS TABLE ===
|
|
op.create_table(
|
|
'game_cardset_links',
|
|
sa.Column('game_id', postgresql.UUID(as_uuid=True), nullable=False),
|
|
sa.Column('cardset_id', sa.Integer(), nullable=False),
|
|
sa.Column('priority', sa.Integer(), nullable=True, server_default='1'),
|
|
sa.ForeignKeyConstraint(['game_id'], ['games.id'], ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('game_id', 'cardset_id')
|
|
)
|
|
op.create_index('ix_game_cardset_links_priority', 'game_cardset_links', ['priority'])
|
|
|
|
|
|
def downgrade() -> None:
|
|
# Drop tables in reverse order (respecting foreign key constraints)
|
|
op.drop_index('ix_game_cardset_links_priority', table_name='game_cardset_links')
|
|
op.drop_table('game_cardset_links')
|
|
|
|
op.drop_index('ix_roster_links_team_id', table_name='roster_links')
|
|
op.drop_index('ix_roster_links_game_id', table_name='roster_links')
|
|
op.drop_table('roster_links')
|
|
|
|
op.drop_index('ix_rolls_timestamp', table_name='rolls')
|
|
op.drop_index('ix_rolls_team_id', table_name='rolls')
|
|
op.drop_index('ix_rolls_roll_type', table_name='rolls')
|
|
op.drop_index('ix_rolls_player_id', table_name='rolls')
|
|
op.drop_index('ix_rolls_league_id', table_name='rolls')
|
|
op.drop_index('ix_rolls_game_id', table_name='rolls')
|
|
op.drop_table('rolls')
|
|
|
|
op.drop_index('ix_game_sessions_last_action_at', table_name='game_sessions')
|
|
op.drop_table('game_sessions')
|
|
|
|
op.drop_index('ix_plays_game_id', table_name='plays')
|
|
op.drop_index('ix_plays_created_at', table_name='plays')
|
|
op.drop_index('ix_plays_complete', table_name='plays')
|
|
op.drop_table('plays')
|
|
|
|
op.drop_index('ix_lineups_team_id', table_name='lineups')
|
|
op.drop_index('ix_lineups_is_active', table_name='lineups')
|
|
op.drop_index('ix_lineups_game_id', table_name='lineups')
|
|
op.drop_table('lineups')
|
|
|
|
op.drop_index('ix_games_status', table_name='games')
|
|
op.drop_index('ix_games_league_id', table_name='games')
|
|
op.drop_index('ix_games_created_at', table_name='games')
|
|
op.drop_table('games')
|