Skip to content

Migrate Player PK from Long to UUID #268

@nanotaboada

Description

@nanotaboada

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

Method Path Key type Notes
GET /players unchanged
POST /players unchanged
GET /players/squadnumber/{squadNumber} Squad Number (Integer) unchanged
GET /players/{id} UUID (PK) unchanged conceptually
PUT /players/{squadNumber} Squad Number (Integer) changed from Long id
DELETE /players/{squadNumber} Squad Number (Integer) changed from Long id

References

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

Metadata

Metadata

Assignees

Labels

enhancementNew feature or requestjavaPull requests that update Java codeplanningEnables automatic issue planning with CodeRabbitpriority:highImportant for production readiness. Schedule for current milestone.

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions