Migrate Player PK from Long to UUID #268

Problem

Currently, the Player entity uses Long with @GeneratedValue(strategy = GenerationType.IDENTITY) as its primary key, and squadNumber is just another field with no uniqueness constraint. This has several limitations:

• Not globally unique: Sequential numeric IDs can conflict in distributed systems or during database mergers
• Predictable: Exposes business information (e.g., total player count) and enables enumeration attacks
• Database-dependent: IDENTITY strategy behavior varies between SQLite and PostgreSQL
• Wrong natural key: Squad numbers are the domain-meaningful identifiers — they are unique per team, stable, and user-facing; they should be the API key for mutations

Proposed Solution

Restructure the Player identity model in two coordinated changes:

1. UUID as primary key: Replace Long with UUID — generated at application level via GenerationType.UUID, stored as VARCHAR(36), used in GET /players/{id} for admin/internal lookup
2. Squad Number as natural key: Promote squadNumber to a UNIQUE constraint and make it the path variable for PUT and DELETE operations
3. API contract update: PUT /players/{squadNumber} and DELETE /players/{squadNumber} replace the current /{id} variants; GET /players/{id} (UUID) is retained for direct lookup

Suggested Approach

1. Update Entity (Player.java)

Promote UUID to @Id with GenerationType.UUID, add @Column(unique=true) to squadNumber:

@Entity
@Table(name = "players")
public class Player {

    // Primary key — UUID generated at application level
    @Id
    @GeneratedValue(strategy = GenerationType.UUID)
    @Column(name = "id", nullable = false, updatable = false, columnDefinition = "VARCHAR(36)")
    private UUID id;

    // Natural key — domain identifier, path variable for PUT and DELETE
    @Column(name = "squadNumber", nullable = false, unique = true, updatable = false)
    private Integer squadNumber;

    // ... rest of fields
}

2. Update DTOs (PlayerDTO.java)

public class PlayerDTO {
    private UUID id;           // primary key, read-only, returned in responses
    private Integer squadNumber;  // natural key, required on create/update
    // ... rest of fields
}

3. Update Repository (PlayersRepository.java)

JPA repository now keyed on UUID:

public interface PlayersRepository extends JpaRepository<Player, UUID> {
    Optional<Player> findBySquadNumber(Integer squadNumber);
    List<Player> findByLeagueContainingIgnoreCase(String league);
}

4. Update Service Layer (PlayersService.java)

// Direct lookup by UUID PK (inherited findById)
public PlayerDTO retrieveById(UUID id) { ... }

// Standard operations: keyed on squad number
public PlayerDTO retrieveBySquadNumber(Integer squadNumber) { ... }
public boolean update(Integer squadNumber, PlayerDTO dto) {
    // findBySquadNumber → preserve UUID PK → save
}
public boolean deleteBySquadNumber(Integer squadNumber) {
    // findBySquadNumber → deleteById(UUID)
}

5. Update Controller (PlayersController.java)

// GET /players/{id} — UUID PK, direct lookup
@GetMapping("/{id}")
public ResponseEntity<PlayerDTO> getPlayerById(@PathVariable UUID id) { ... }

// PUT /players/{squadNumber} — natural key
@PutMapping("/{squadNumber}")
public ResponseEntity<Void> updatePlayer(
    @PathVariable Integer squadNumber,
    @RequestBody @Valid PlayerDTO dto) { ... }

// DELETE /players/{squadNumber} — natural key
@DeleteMapping("/{squadNumber}")
public ResponseEntity<Void> deletePlayer(@PathVariable Integer squadNumber) { ... }

6. Database Migration

SQLite Schema Update (storage/players-sqlite3.db):

CREATE TABLE players (
    id          VARCHAR(36)  PRIMARY KEY,
    squadNumber INTEGER      NOT NULL UNIQUE,
    firstName   TEXT         NOT NULL,
    lastName    TEXT         NOT NULL,
    -- ... other columns
);

Test Schema (src/test/resources/ddl.sql):

CREATE TABLE players (
    id          VARCHAR(36)  PRIMARY KEY,
    squadNumber INTEGER      NOT NULL UNIQUE,
    -- ... rest of columns
);

Test Data (src/test/resources/dml.sql): Update INSERT statements to include a UUID value per player.

Acceptance Criteria

• Player entity: id is @Id UUID with GenerationType.UUID; squadNumber has @Column(unique=true)
• JpaRepository<Player, UUID> keyed on UUID
• Service layer exposes squad-number-keyed update and deleteBySquadNumber methods (using findBySquadNumber internally)
• PUT /players/{squadNumber} and DELETE /players/{squadNumber} use squad number path variable
• GET /players/{id} retains UUID path variable
• All other endpoints unaffected (GET /players, GET /players/squadnumber/{squadNumber}, POST /players)
• Database schema migrated; 25 players preserved with assigned UUIDs
• ddl.sql and dml.sql updated for test fixtures
• All tests updated and passing (./mvnw clean test)
• Coverage maintained or improved (JaCoCo check passes)
• Swagger/OpenAPI reflects UUID type for id and Integer for squadNumber path vars

API Contract Summary

References

• JPA 3.1 UUID Generation
• Hibernate UUID Generator
• SQLite UUID Functions
• Natural vs Surrogate Keys

Migration Impact

Breaking changes:

• PUT /players/{id} and DELETE /players/{id} path variables change from numeric Long to Integer squad number
• API clients using numeric Long IDs for mutations must switch to squad numbers

Non-breaking:

• GET /players and POST /players unaffected
• Response structure unchanged (id field changes to UUID string format)
• GET /players/squadnumber/{squadNumber} path shape unchanged