feat(persistence): Enforce one DrillItem per Challenge; handle concurrency and race conditions #40
Description
Problem / context
Under concurrent load, duplicate DrillItem rows could be created for the same Challenge, and concurrent updates might overwrite streak/nextDueAt values (lost updates).
Proposed solution
Uniqueness & race handling
- Enforce one DrillItem per Challenge via a unique constraint (drill_items.challenge_id).
- Update getOrCreateDrillItem to catch DataIntegrityViolationException and re-fetch the existing record.
Optimistic locking
- Utilize the @Version field added in MVP.
Tests
- Add DAO/service concurrency tests for create/update race scenarios.
Index
- Add unique index on drill_items.challenge_id in Flyway/Liquibase migration.
In scope / Out of scope
In Scope
- Service/DAO concurrency handling and tests.
- Schema constraint via migration (Week 8 milestone).
Out of Scope
- Refactoring unrelated domain entities.
- Non-persistence optimizations.
Acceptance criteria
- Unique constraint prevents duplicate DrillItem per Challenge.
- Concurrent updates use optimistic locking (@Version).
- Concurrency tests confirm correctness and stability.
- All existing tests remain green.
Area
area:persistence
Dependencies / related issues
No response
Checklist
- I’ve checked existing issues for duplicates.
- I can help implement this and open a PR.