Consolidate Player Models with SQLModel

[nanotaboada]

Problem

The application currently maintains duplicate Player model definitions across two separate layers, creating significant maintenance overhead:

Current Architecture:

• schemas/player_schema.py → SQLAlchemy ORM class (11 attributes + custom HyphenatedUUID type)
• models/player_model.py → Pydantic validation models (PlayerRequestModel, PlayerResponseModel)

Pain Points:

1. Duplication: Every field is defined twice with slightly different syntax
2. Synchronization Risk: Schema changes require updating both files, risking inconsistencies
3. Maintenance Burden: Adding/modifying fields requires coordinated changes across multiple files
4. Translation Overhead: Converting between SQLAlchemy and Pydantic objects adds boilerplate

Example of current duplication:

# schemas/player_schema.py
first_name = Column(String, name="firstName", nullable=False)

# models/player_model.py
first_name: str  # Must manually keep in sync

This violates the DRY (Don't Repeat Yourself) principle and increases the likelihood of bugs during schema evolution.

Architectural Constraint

Important: Before proceeding, this proposal must be reconciled with a documented architectural decision in CLAUDE.md:

"Never reintroduce the removed PlayerModel; it was removed because a single flat model conflated ORM, request, and response concerns."

The SQLModel consolidation approach — a single class serving as both ORM table and API schema — is functionally equivalent to the previously removed PlayerModel. Any implementation plan must address why the prior concern no longer applies, or propose an alternative that preserves the ORM/request/response separation while reducing duplication.

Proposed Solution

Consolidate both layers into a single SQLModel-based class that serves dual purposes:

• Acts as the SQLAlchemy ORM model for database operations
• Acts as the Pydantic model for request/response validation and OpenAPI documentation

Benefits:

• Single Source of Truth: One class definition for all player data
• Automatic Validation: Inherits Pydantic's validation at the ORM level
• Type Safety: Full type hints work for both DB and API layers
• Simplified Codebase: Eliminates conversion logic between models
• FastAPI Native: SQLModel is created by the same author as FastAPI (Sebastián Ramírez)

SQLModel combines the best of both worlds:

class Player(SQLModel, table=True):
    # One definition, two purposes
    first_name: str = Field(alias="firstName")

Suggested Approach

Phase 1: Setup and Preparation

1.1 Install SQLModel

The project uses uv with PEP 735 dependency groups defined in pyproject.toml — there is no requirements.txt. Add SQLModel to the [project] dependencies section:

# pyproject.toml
[project]
dependencies = [
    "sqlmodel>=0.0.21",
    # ... existing dependencies
]

1.2 Update Database Configuration

Modify databases/player_database.py to use SQLModel.metadata instead of declarative_base():

from sqlmodel import SQLModel

# Replace: Base = declarative_base()
# With:
Base = SQLModel  # SQLModel.metadata replaces declarative_base metadata

Phase 2: Create SQLModel Player Class

2.1 Handle the HyphenatedUUID custom type

The current schema defines a custom HyphenatedUUID SQLAlchemy type in schemas/player_schema.py that stores UUIDs as hyphenated strings (e.g. '550e8400-e29b-41d4-a716-446655440000') and returns Python UUID objects. This type must be preserved or replicated in the SQLModel class — SQLModel has no built-in equivalent.

# Must carry over to the new model
class HyphenatedUUID(TypeDecorator):
    impl = String(36)
    cache_ok = True
    # ... (existing implementation)

2.2 Create New Unified Model

Create models/player_sqlmodel.py (temporary file during migration). Note the primary key is UUID, not int:

from typing import Optional
from uuid import UUID, uuid4
from sqlmodel import SQLModel, Field
from sqlalchemy import Column
# HyphenatedUUID must be imported or redefined here

class Player(SQLModel, table=True):
    __tablename__ = "players"

    id: Optional[UUID] = Field(
        default_factory=uuid4,
        primary_key=True,
        sa_column=Column(HyphenatedUUID(), primary_key=True, default=uuid4, nullable=False),
    )
    first_name: str = Field(sa_column=Column("firstName", nullable=False))
    middle_name: Optional[str] = Field(default=None, sa_column=Column("middleName"))
    last_name: str = Field(sa_column=Column("lastName", nullable=False))
    date_of_birth: Optional[str] = Field(default=None, sa_column=Column("dateOfBirth"))
    squad_number: int = Field(sa_column=Column("squadNumber", unique=True, nullable=False))
    position: str = Field(sa_column=Column(nullable=False))
    abbr_position: Optional[str] = Field(default=None, sa_column=Column("abbrPosition"))
    team: Optional[str] = Field(default=None)
    league: Optional[str] = Field(default=None)
    starting11: Optional[bool] = Field(default=None)

Note on separation of request/response models: Rather than using the table model directly in routes, consider keeping separate non-table SQLModel schemas for request/response to preserve the current architectural separation:

class PlayerRequestModel(SQLModel, table=False):
    """Schema for POST/PUT — no id field."""
    first_name: str
    # ...

class PlayerResponseModel(SQLModel, table=False):
    """Schema for GET/POST responses — includes id."""
    id: UUID
    # ...

Phase 3: Update Services and Routes

3.1 Update Service Layer

Modify services/player_service.py to use the new SQLModel. The existing select() pattern (SQLAlchemy 2.0 style) is already compatible with SQLModel:

from sqlmodel import select
from models.player_sqlmodel import Player

# Existing pattern is compatible — no query-style changes needed
statement = select(Player).where(Player.squad_number == squad_number)

3.2 Update Route Handlers

Modify routes/player_route.py to import from the new module:

from models.player_sqlmodel import PlayerRequestModel, PlayerResponseModel

Phase 4: Migration and Cleanup

4.1 Database Migration Compatibility

Ensure Alembic works with SQLModel. Update alembic/env.py:

from sqlmodel import SQLModel
from models.player_sqlmodel import Player  # triggers table registration

target_metadata = SQLModel.metadata

4.2 Update Tests

Modify tests/player_fake.py (note: not player_stub.py) and tests/conftest.py if needed. The existing integration test approach (real SQLite DB, no mocking) is unchanged.

4.3 Remove Legacy Code

Once all services and routes are migrated:

1. Delete schemas/player_schema.py
2. Delete models/player_model.py
3. Rename models/player_sqlmodel.py → models/player_model.py
4. Update all imports across the codebase

4.4 Update Documentation

Update CLAUDE.md and .github/copilot-instructions.md to reflect the new architecture and remove references to schemas/player_schema.py.

Acceptance Criteria

• SQLModel is added to pyproject.toml [project] dependencies (not requirements.txt)
• databases/player_database.py is updated to work with SQLModel metadata
• HyphenatedUUID custom type is preserved in the new model layer
• A unified Player SQLModel table class is created with all 11 attributes and a UUID primary key
• CamelCase database column names are preserved via sa_column
• Separate non-table SQLModel schemas for request/response are used in routes (preserving ORM/API separation)
• All CRUD operations in services/player_service.py use the SQLModel class
• All route handlers in routes/player_route.py are updated to use SQLModel
• Legacy schemas/player_schema.py is removed
• Legacy models/player_model.py is replaced by the unified SQLModel version
• FastAPI OpenAPI docs correctly show camelCase field names
• All existing integration tests pass without modifications
• tests/player_fake.py is updated if test fixtures need adjustment
• Database migrations (Alembic) work with SQLModel metadata
• CLAUDE.md is updated to reflect the new architecture
• No runtime errors occur when creating, reading, updating, or deleting players
• API responses maintain the same JSON structure (backward compatibility)

References

• SQLModel Documentation
• FastAPI with SQLModel
• SQLModel GitHub Repository
• Alembic with SQLModel (Related: Implement Alembic for database migrations #2)
• Pydantic Field Aliases

---

Migration Strategy Notes

Gradual Migration Approach

To minimize risk, consider this phased rollout:

1. Phase 1: Create SQLModel class alongside existing models
2. Phase 2: Update one service/route at a time
3. Phase 3: Run tests after each change
4. Phase 4: Remove legacy code only after everything works

Backward Compatibility

The API contract remains unchanged:

• Same endpoints
• Same request/response formats
• Same camelCase field names in JSON
• Existing clients continue working without changes

Rollback Plan

If issues arise:

1. Keep legacy files temporarily (don't delete immediately)
2. Git branch allows easy revert