Normalize the `PlayerModel` by extracting `position`, `team`, and `league` into separate tables

[nanotaboada]

Description

Refactor the Player schema by normalizing related fields (position, team, and league) into separate tables. This will improve data integrity and reduce redundancy.

ERD

DDL

CREATE TABLE IF NOT EXISTS "leagues" (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "name" TEXT NOT NULL UNIQUE,
    "country" TEXT NOT NULL,
    "confederation" TEXT
);

CREATE TABLE IF NOT EXISTS "teams" (
    -- https://liaison.reuters.com/tools/sports-team-codes
    "id" TEXT PRIMARY KEY,
    "name" TEXT NOT NULL UNIQUE,
    "leagueId" INTEGER NOT NULL,
    FOREIGN KEY ("leagueId") REFERENCES "leagues"("id") ON UPDATE NO ACTION ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS "positions" (
    "id" TEXT NOT NULL PRIMARY KEY,
    "name" TEXT NOT NULL,
    CHECK (
        id = 'GK' AND name = 'Goalkeeper'
        OR id = 'RB' AND name = 'Right-Back'
        OR id = 'LB' AND name = 'Left-Back'
        OR id = 'CB' AND name = 'Centre-Back'
        OR id = 'DM' AND name = 'Defensive Midfield'
        OR id = 'CM' AND name = 'Central Midfield'
        OR id = 'RW' AND name = 'Right Winger'
        OR id = 'AM' AND name = 'Attacking Midfield'
        OR id = 'CF' AND name = 'Centre-Forward'
        OR id = 'SS' AND name = 'Second Striker'
        OR id = 'LW' AND name = 'Left Winger'
    )
);

CREATE TABLE IF NOT EXISTS "players" (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "firstName" TEXT NOT NULL,
    "middleName" TEXT,
    "lastName" TEXT NOT NULL,
    "dateOfBirth" TEXT,
    "squadNumber" INTEGER NOT NULL UNIQUE,
    "positionId" TEXT NOT NULL,
    "teamId" TEXT NOT NULL,
    "starting11" BOOLEAN,
    FOREIGN KEY ("positionId") REFERENCES "positions"("id") ON UPDATE NO ACTION ON DELETE NO ACTION,
    FOREIGN KEY ("teamId") REFERENCES "teams"("id") ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE INDEX IF NOT EXISTS "idx_teams_leagueId" ON "teams"("leagueId");
CREATE INDEX IF NOT EXISTS "idx_players_positionId" ON "players"("positionId");
CREATE INDEX IF NOT EXISTS "idx_players_teamId" ON "players"("teamId");

Acceptance Criteria

• Create new tables with the proposed DDL script.
• Create new schemas for Position, Team, and League, defining relationships with the existing Player.
• Create new PositionModel, TeamModel and LeagueModel.
• Update existing the SQLAlchemy schemas, Pydantic models and FastAPI endpoints accordingly.
• Migrate existing data without breaking API functionality.

[nanotaboada]

@coderabbitai plan

[coderabbitai]

Coding Plan

Approach

Normalize the Player database schema by extracting position, team, and league fields into separate reference tables with foreign key relationships, while maintaining full backward compatibility with existing API contracts.

Observations

This is a FastAPI application with an async SQLAlchemy ORM layer using SQLite. The project follows a clear separation between database models (schemas/) and API models (models/), with a function-based service layer (services/) handling CRUD operations. The database is pre-seeded and copied via Docker entrypoint, with no migration framework like Alembic in place. API responses use camelCase via Pydantic model configuration, while database columns also use camelCase naming. The current Player model stores position, team, and league as simple string fields without normalization or validation.

Assumptions

Assumption 1: Database Migration Approach

Options Considered:

• Create a standalone Python migration script that can be run once to update the database structure and migrate data
• Create a startup migration check that runs automatically on application boot
• Manually update the pre-seeded database file and replace it

Chosen Option: 1

Rationale: Create a standalone Python migration script that developers run manually. This approach is explicit, testable, and aligns with the current pre-seeded database pattern.

Assumption 2: Handling Players with Missing Team/League Data

Options Considered:

• Create a default "Unknown" team/league for players missing this data
• Require manual data cleanup before migration
• Make teamId nullable in contradiction to the DDL

Chosen Option: 1

Rationale: Create a default "Unassigned" team in an "Unknown League" to handle existing NULL values. This maintains data integrity while respecting the NOT NULL constraint.

Assumption 3: Position Code Mapping Strategy

Options Considered:

• Use abbr_position as source of truth and ignore position name
• Use position name and map to codes based on predefined mapping
• Validate both fields match and fail if inconsistent

Chosen Option: 1

Rationale: Use abbr_position as the primary source of truth since it maps directly to the position IDs in the DDL. The migration will validate that abbr_position values match the 11 valid codes (GK, RB, LB, CB, DM, CM, RW, AM, CF, SS, LW).

Assumption 4: Reuters Team Code Assignment

Options Considered:

• Generate simple 3-letter codes from team names (e.g., "AVI" for Aston Villa)
• Leave this as manual configuration for production data
• Use team name slugs as codes

Chosen Option: 1

Rationale: Generate simple 3-letter codes algorithmically (first 3 letters of significant words, uppercase) during migration for development purposes. Document that production deployments should use official Reuters codes.

💡 User Tips

Regenerate the plan with different choices with @coderabbitai <feedback>.

Implementation Steps

Phase 1: Create Database Schema and ORM Models

Create the normalized database tables and SQLAlchemy ORM models for Position, Team, and League entities, establishing the foundation for the relational structure.

Task 1: Create Database Migration Script with Schema Changes

Execute the provided DDL to create positions, teams, leagues tables and update players table structure.

• Create scripts/migrate_schema.py that executes the DDL statements using async SQLAlchemy
• Create positions, leagues, and teams tables with appropriate constraints and indexes
• Alter players table to add positionId and teamId foreign key columns
• Include verification queries to confirm schema changes were applied successfully

Task 2: Create SQLAlchemy ORM Models for Reference Tables

Define new ORM classes for Position, Team, and League in the schemas directory following existing patterns.

• Create schemas/position_schema.py with Position ORM class mapping to "positions" table with id (Text, primary key) and name (Text) fields
• Create schemas/league_schema.py with League ORM class mapping to "leagues" table with id (Integer, primary key), name, country, and confederation fields
• Create schemas/team_schema.py with Team ORM class mapping to "teams" table with id (Text, primary key), name, and leagueId foreign key with relationship to League
• Use the same camelCase column naming convention as existing Player model

Task 3: Update Player ORM Model with Foreign Key Relationships

Modify the existing Player ORM model to use foreign keys and establish relationships with the new reference tables.

• Update schemas/player_schema.py to replace position, abbr_position, team, league columns with position_id and team_id foreign key columns
• Add SQLAlchemy relationship attributes: player.position (many-to-one to Position), player.team (many-to-one to Team)
• Configure relationship loading strategy (lazy='selectin' for async compatibility)
• Keep database column names as positionId and teamId per DDL specification

🤖 Prompt for AI agents

Create the normalized database schema and ORM models for Position, Team, and
League entities.

Create `scripts/migrate_schema.py`:
- Execute DDL to create positions, leagues, and teams tables with constraints
and indexes
- Alter players table to add positionId and teamId foreign key columns
- Add verification queries for schema changes

Create new ORM models in schemas directory:
- `schemas/position_schema.py`: Position class with id (Text, PK) and name
fields
- `schemas/league_schema.py`: League class with id (Integer, PK), name, country,
confederation fields
- `schemas/team_schema.py`: Team class with id (Text, PK), name, leagueId FK
with relationship to League
- Use camelCase column naming

Update `schemas/player_schema.py`:
- Replace position, abbr_position, team, league columns with positionId and
teamId foreign keys
- Add relationships: player.position (many-to-one to Position), player.team
(many-to-one to Team)
- Configure lazy='selectin' for async compatibility

Phase 2: Populate Reference Tables and Migrate Player Data

Create and execute a data migration script that populates the normalized tables with data from existing player records while maintaining referential integrity.

Task 1: Create Data Migration Script

Develop a comprehensive Python script that analyzes existing player data and populates the new reference tables.

• Create scripts/migrate_data.py that connects to the database using async SQLAlchemy
• Seed positions table with all 11 valid position codes from DDL CHECK constraint
• Extract distinct league values from current players, create league records with placeholder country values
• Extract distinct team values, generate 3-letter codes, create team records with foreign keys to leagues
• Create "Unassigned" default team in "Unknown" league for players with NULL team values
• Log all generated codes and mappings for review

Task 2: Migrate Player Foreign Keys and Clean Up Legacy Columns

Update all player records to reference the new entities and remove denormalized columns.

• Extend the migration script to update each player's positionId based on their abbr_position value
• Update each player's teamId based on their team name, mapping to the generated team code
• Assign players with NULL team to the "Unassigned" default team
• Validate all foreign key constraints are satisfied before committing
• Drop the deprecated position, abbr_position, team, and league columns from players table after successful migration

🤖 Prompt for AI agents

Populate reference tables and migrate existing player data to the normalized
structure.

Create `scripts/migrate_data.py`:
- Connect using async SQLAlchemy
- Seed positions table with 11 valid position codes (GK, RB, LB, CB, DM, CM, RW,
AM, CF, SS, LW)
- Extract distinct league values from players, create league records with
placeholder country
- Extract distinct team values, generate 3-letter uppercase codes, create team
records with leagueId
- Create "Unassigned" team in "Unknown" league for NULL team handling
- Log all generated codes and mappings

Extend migration script to update players:
- Map each player's positionId from abbr_position value
- Map each player's teamId from team name to generated code
- Assign NULL teams to "Unassigned" default team
- Validate all foreign key constraints
- Drop deprecated columns: position, abbr_position, team, league

Phase 3: Update API Models, Service Layer, and Endpoints

Build Pydantic models for the new entities, implement backward-compatible serialization, and update the service layer to work with normalized data.

Task 1: Create Pydantic Models for New Entities

Define API validation models for Position, Team, and League following existing Pydantic patterns.

• Create models/position_model.py with PositionModel class extending MainModel base, with id and name fields
• Create models/league_model.py with LeagueModel class including id, name, country, and optional confederation fields
• Create models/team_model.py with TeamModel class including id, name, and leagueId fields
• Apply the same camelCase alias configuration as existing PlayerModel via MainModel inheritance

Task 2: Update PlayerModel with Backward-Compatible Serialization

Modify the PlayerModel to maintain the existing API contract while working with normalized data internally.

• Update models/player_model.py PlayerModel to include position_id and team_id fields for internal use
• Add computed properties or Pydantic model_serializer to expose position, abbr_position, team, and league as strings derived from relationships
• For API responses: serialize position as full name, abbr_position as position_id, team as team.name, league as team.league.name
• For API requests: accept position/team as strings (resolved in service layer)
• Maintain all existing fields with their current types and optionality

Task 3: Update Player Service with Relationship Loading and Reference Resolution

Modify the player service functions to work with the normalized structure.

• Update services/player_service.py query functions to eager load Position and Team relationships using selectinload
• Update create_async to resolve position string to positionId by querying Position table
• Update create_async to resolve team string to teamId by querying Team table
• Update update_async with same reference resolution logic
• Raise appropriate validation errors with clear messages when position/team not found

Task 4: Update Player Route Response Handling

Ensure player endpoints correctly serialize normalized data to maintain backward compatibility.

• Update routes/player_route.py to properly convert ORM objects with relationships to PlayerModel responses
• Ensure all GET endpoints return player data with position, abbr_position, team, and league as string fields
• Verify POST and PUT endpoints accept position and team as string values per existing API contract
• Maintain all existing status codes and error handling patterns

🤖 Prompt for AI agents

Create Pydantic models for new entities and update Player API layer for backward
compatibility.

Create new Pydantic models:
- `models/position_model.py`: PositionModel extending MainModel with id and name
fields
- `models/league_model.py`: LeagueModel with id, name, country, optional
confederation
- `models/team_model.py`: TeamModel with id, name, leagueId
- Use camelCase aliases via MainModel inheritance

Update `models/player_model.py`:
- Add position_id and team_id fields for internal use
- Add computed properties/model_serializer for backward compatibility
- Serialize position as full name, abbr_position as position_id, team as
team.name, league as team.league.name
- Accept position/team as strings in requests
- Maintain existing field types and optionality

Update `services/player_service.py`:
- Add selectinload for Position and Team relationships in queries
- In create_async: resolve position string to positionId, team string to teamId
- In update_async: apply same reference resolution
- Raise validation errors when position/team not found

Update `routes/player_route.py`:
- Convert ORM objects with relationships to PlayerModel responses
- Ensure GET endpoints return position, abbr_position, team, league as strings
- Verify POST/PUT accept position and team as strings
- Maintain existing error handling

Phase 4: Update Test Fixtures

Modify existing test data to work with the normalized database structure.

Update Test Fixtures and Stubs

Modify test data to reflect the new normalized structure.

• Update tests/player_stub.py to ensure test players reference valid position codes (GK, DM) and team IDs
• Verify existing_player and nonexistent_player fixtures work with the normalized schema
• Update test assertions if needed to work with the new data flow
• Ensure backward-compatible serialization maintains existing test expectations for API response structure

🤖 Prompt for AI agents

Update test fixtures to work with normalized database structure.

Update `tests/player_stub.py`:
- Ensure test players reference valid position codes (GK, DM) and team IDs
- Verify existing_player and nonexistent_player fixtures work with normalized
schema
- Update test assertions for new data flow
- Ensure backward-compatible serialization maintains existing test expectations

---

🚀 Next Steps

🤖 All AI agent prompts combined

Task: 1

Create the normalized database schema and ORM models for Position, Team, and
League entities.

Create `scripts/migrate_schema.py`:
- Execute DDL to create positions, leagues, and teams tables with constraints
and indexes
- Alter players table to add positionId and teamId foreign key columns
- Add verification queries for schema changes

Create new ORM models in schemas directory:
- `schemas/position_schema.py`: Position class with id (Text, PK) and name
fields
- `schemas/league_schema.py`: League class with id (Integer, PK), name, country,
confederation fields
- `schemas/team_schema.py`: Team class with id (Text, PK), name, leagueId FK
with relationship to League
- Use camelCase column naming

Update `schemas/player_schema.py`:
- Replace position, abbr_position, team, league columns with positionId and
teamId foreign keys
- Add relationships: player.position (many-to-one to Position), player.team
(many-to-one to Team)
- Configure lazy='selectin' for async compatibility
===============================================================================

Task: 2

Populate reference tables and migrate existing player data to the normalized
structure.

Create `scripts/migrate_data.py`:
- Connect using async SQLAlchemy
- Seed positions table with 11 valid position codes (GK, RB, LB, CB, DM, CM, RW,
AM, CF, SS, LW)
- Extract distinct league values from players, create league records with
placeholder country
- Extract distinct team values, generate 3-letter uppercase codes, create team
records with leagueId
- Create "Unassigned" team in "Unknown" league for NULL team handling
- Log all generated codes and mappings

Extend migration script to update players:
- Map each player's positionId from abbr_position value
- Map each player's teamId from team name to generated code
- Assign NULL teams to "Unassigned" default team
- Validate all foreign key constraints
- Drop deprecated columns: position, abbr_position, team, league
===============================================================================

Task: 3

Create Pydantic models for new entities and update Player API layer for backward
compatibility.

Create new Pydantic models:
- `models/position_model.py`: PositionModel extending MainModel with id and name
fields
- `models/league_model.py`: LeagueModel with id, name, country, optional
confederation
- `models/team_model.py`: TeamModel with id, name, leagueId
- Use camelCase aliases via MainModel inheritance

Update `models/player_model.py`:
- Add position_id and team_id fields for internal use
- Add computed properties/model_serializer for backward compatibility
- Serialize position as full name, abbr_position as position_id, team as
team.name, league as team.league.name
- Accept position/team as strings in requests
- Maintain existing field types and optionality

Update `services/player_service.py`:
- Add selectinload for Position and Team relationships in queries
- In create_async: resolve position string to positionId, team string to teamId
- In update_async: apply same reference resolution
- Raise validation errors when position/team not found

Update `routes/player_route.py`:
- Convert ORM objects with relationships to PlayerModel responses
- Ensure GET endpoints return position, abbr_position, team, league as strings
- Verify POST/PUT accept position and team as strings
- Maintain existing error handling
===============================================================================

Task: 4

Update test fixtures to work with normalized database structure.

Update `tests/player_stub.py`:
- Ensure test players reference valid position codes (GK, DM) and team IDs
- Verify existing_player and nonexistent_player fixtures work with normalized
schema
- Update test assertions for new data flow
- Ensure backward-compatible serialization maintains existing test expectations

💡 Iterate on the plan with: @coderabbitai <feedback>

Example Feedback
- @coderabbitai You can skip phase 3. Add a simple unit test case for phase 2.
- @coderabbitai For assumption 1 go ahead with option 3 and replan.

---

💬 Have feedback or questions? Drop into our discord!

[coderabbitai]

🤖 CodeRabbit Planning

Planning is in progress. This will take a moment...

[coderabbitai]

I've updated the plan above based on your feedback. Ready to proceed when you are!