cal/paper-dynasty-database
1
0
Fork 0
Code Issues 7 Pull Requests 4 Actions Packages Projects Releases Wiki Activity

WP-04: SQL Migration Script #69

New Issue
Closed
opened 2026-03-12 20:55:29 +00:00 by cal · 2 comments
cal commented 2026-03-12 20:55:29 +00:00
Owner
Copy Link

Description

SQL migration creating all five new tables (player_season_stats + four evolution tables) and adding variant column to card and image_url column to battingcard/pitchingcard. Must be idempotent.

Note: evolution_milestone, evolution_progress tables are NOT created. image_format column is NOT added.

Repo: database
Phase: 1a (Schema & Data Foundation)
Dependencies: WP-01, WP-02
Complexity: M

Tables Created

  1. player_season_stats
  2. evolution_track
  3. evolution_card_state
  4. evolution_tier_boost (Phase 2 stub)
  5. evolution_cosmetic (Phase 2 stub)

Files

  • Create: database/migrations/2026-XX-XX_add_evolution_tables.sql

Tests (write first in database/tests/test_evolution_migration.py)

  • Integration: fresh migration creates all 5 tables with expected columns
  • Integration: idempotent re-run (no errors on second run)
  • Integration: index verification via pg_indexes
  • Integration: FK verification
  • Integration: existing data in modified tables preserved

Acceptance Criteria

  1. All five tables match schema
  2. card.variant added (INTEGER, NULL, DEFAULT NULL)
  3. battingcard.image_url added (VARCHAR(500), NULL)
  4. Same for pitchingcard
  5. All indexes created
  6. Idempotent
  7. Existing data preserved

Plan reference: docs/prd-evolution/PHASE1_PROJECT_PLAN.md WP-04

## Description SQL migration creating all five new tables (`player_season_stats` + four evolution tables) and adding `variant` column to `card` and `image_url` column to `battingcard`/`pitchingcard`. Must be idempotent. Note: `evolution_milestone`, `evolution_progress` tables are NOT created. `image_format` column is NOT added. **Repo:** `database` **Phase:** 1a (Schema & Data Foundation) **Dependencies:** WP-01, WP-02 **Complexity:** M ## Tables Created 1. `player_season_stats` 2. `evolution_track` 3. `evolution_card_state` 4. `evolution_tier_boost` (Phase 2 stub) 5. `evolution_cosmetic` (Phase 2 stub) ## Files - **Create:** `database/migrations/2026-XX-XX_add_evolution_tables.sql` ## Tests (write first in `database/tests/test_evolution_migration.py`) - [ ] Integration: fresh migration creates all 5 tables with expected columns - [ ] Integration: idempotent re-run (no errors on second run) - [ ] Integration: index verification via pg_indexes - [ ] Integration: FK verification - [ ] Integration: existing data in modified tables preserved ## Acceptance Criteria 1. All five tables match schema 2. `card.variant` added (INTEGER, NULL, DEFAULT NULL) 3. `battingcard.image_url` added (VARCHAR(500), NULL) 4. Same for `pitchingcard` 5. All indexes created 6. Idempotent 7. Existing data preserved **Plan reference:** `docs/prd-evolution/PHASE1_PROJECT_PLAN.md` WP-04
cal added this to the Card Evolution Phase 1 milestone 2026-03-12 20:59:09 +00:00
cal added the
evolution
phase-1a
 labels 2026-03-12 20:59:22 +00:00
Claude added the
ai-working
 label 2026-03-12 23:01:25 +00:00
Claude added the
ai-pr-opened
 label 2026-03-12 23:09:05 +00:00
Claude commented 2026-03-12 23:09:12 +00:00
Collaborator
Copy Link

PR #84 opened: #84

Created migrations/2026-03-12_add_evolution_tables.sql — idempotent PostgreSQL migration that:

  • Creates 5 tables: player_season_stats, evolution_track, evolution_card_state, evolution_tier_boost (stub), evolution_cosmetic (stub)
  • Adds card.variant (INTEGER NULL DEFAULT NULL), battingcard.image_url (VARCHAR 500 NULL), pitchingcard.image_url (VARCHAR 500 NULL)
  • All DDL uses IF NOT EXISTS throughout; wrapped in BEGIN/COMMIT

Schema derived from WP-01 Peewee models (branch ai/paper-dynasty-database#67) — evolution_track uses t1_threshold…t4_threshold column names to match the approved ORM model.

Tests: 16 unit tests pass (SQL content validation); 6 integration tests auto-skip when POSTGRES_HOST is not set.

PR #84 opened: https://git.manticorum.com/cal/paper-dynasty-database/pulls/84 Created `migrations/2026-03-12_add_evolution_tables.sql` — idempotent PostgreSQL migration that: - Creates 5 tables: `player_season_stats`, `evolution_track`, `evolution_card_state`, `evolution_tier_boost` (stub), `evolution_cosmetic` (stub) - Adds `card.variant` (INTEGER NULL DEFAULT NULL), `battingcard.image_url` (VARCHAR 500 NULL), `pitchingcard.image_url` (VARCHAR 500 NULL) - All DDL uses `IF NOT EXISTS` throughout; wrapped in BEGIN/COMMIT Schema derived from WP-01 Peewee models (branch ai/paper-dynasty-database#67) — `evolution_track` uses `t1_threshold`…`t4_threshold` column names to match the approved ORM model. Tests: 16 unit tests pass (SQL content validation); 6 integration tests auto-skip when `POSTGRES_HOST` is not set.
Claude removed the
ai-working
 label 2026-03-12 23:09:17 +00:00
cal commented 2026-03-25 05:30:31 +00:00
Author
Owner
Copy Link

Implemented and merged. Refractor system deployed to dev.

Implemented and merged. Refractor system deployed to dev.
cal closed this issue 2026-03-25 05:30:36 +00:00
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels   
ai-changes-requested

   
ai-failed

   
ai-merged

PR merged by AI ops agent

  
ai-pr-opened

   
ai-reviewed

   
ai-reviewing

   
ai-reviewing

   
ai-working

   
bug

   
enhancement

   
evolution

Card Evolution system

  
performance

   
phase-0

Render Pipeline Optimization

  
phase-1a

Schema & Data Foundation

  
phase-1b

API & Formula Engine

  
phase-1c

Bot Integration

  
phase-1d

Deployment

  
security

   
tech-debt

   
todo
No Label
ai-changes-requested
ai-failed
ai-merged
ai-pr-opened
ai-reviewed
ai-reviewing
ai-reviewing
ai-working
bug
enhancement
evolution
performance
phase-0
phase-1a
phase-1b
phase-1c
phase-1d
security
tech-debt
todo
Milestone
Clear milestone
No items
No Milestone
Card Evolution Phase 1
Projects
Clear projects
No project
Assignees
Clear assignees
No Assignees
2 Participants
Notifications
Due Date
The due date is invalid or out of range. Please use the format 'yyyy-mm-dd'.

No due date set.

Dependencies

No dependencies set.

Reference: cal/paper-dynasty-database#69
No description provided.
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?