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:
- 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
- Squad Number as natural key: Promote
squadNumber to a UNIQUE constraint and make it the path variable for PUT and DELETE operations
- 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
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
Problem
Currently, the
Playerentity usesLongwith@GeneratedValue(strategy = GenerationType.IDENTITY)as its primary key, andsquadNumberis just another field with no uniqueness constraint. This has several limitations:Proposed Solution
Restructure the
Playeridentity model in two coordinated changes:LongwithUUID— generated at application level viaGenerationType.UUID, stored asVARCHAR(36), used inGET /players/{id}for admin/internal lookupsquadNumberto aUNIQUEconstraint and make it the path variable forPUTandDELETEoperationsPUT /players/{squadNumber}andDELETE /players/{squadNumber}replace the current/{id}variants;GET /players/{id}(UUID) is retained for direct lookupSuggested Approach
1. Update Entity (
Player.java)Promote UUID to
@IdwithGenerationType.UUID, add@Column(unique=true)tosquadNumber:2. Update DTOs (
PlayerDTO.java)3. Update Repository (
PlayersRepository.java)JPA repository now keyed on
UUID:4. Update Service Layer (
PlayersService.java)5. Update Controller (
PlayersController.java)6. Database Migration
SQLite Schema Update (
storage/players-sqlite3.db):Test Schema (
src/test/resources/ddl.sql):Test Data (
src/test/resources/dml.sql): Update INSERT statements to include a UUID value per player.Acceptance Criteria
Playerentity:idis@Id UUIDwithGenerationType.UUID;squadNumberhas@Column(unique=true)JpaRepository<Player, UUID>keyed on UUIDupdateanddeleteBySquadNumbermethods (usingfindBySquadNumberinternally)PUT /players/{squadNumber}andDELETE /players/{squadNumber}use squad number path variableGET /players/{id}retains UUID path variableGET /players,GET /players/squadnumber/{squadNumber},POST /players)ddl.sqlanddml.sqlupdated for test fixtures./mvnw clean test)idand Integer forsquadNumberpath varsAPI Contract Summary
/players/players/players/squadnumber/{squadNumber}/players/{id}/players/{squadNumber}/players/{squadNumber}References
Migration Impact
Breaking changes:
PUT /players/{id}andDELETE /players/{id}path variables change from numeric Long to Integer squad numberNon-breaking:
GET /playersandPOST /playersunaffectedGET /players/squadnumber/{squadNumber}path shape unchanged