Analyze adding transaction support to Repository

[nanotaboada]

Problem

The current Repository<T> calls SaveChangesAsync() individually inside each write method (AddAsync, UpdateAsync, RemoveAsync). This means there is no way to group multiple operations into a single atomic unit — if a multi-step write fails partway through, partial changes are already committed.

This is not a current bug (no endpoint today requires multi-step writes), but it becomes relevant if PATCH (#342), concurrency tokens (#65), or any future feature needs to update more than one entity atomically.

Proposed Solution

Expose EF Core's transaction support through the repository layer so callers can opt into atomic multi-step operations when needed, without changing the existing single-operation behavior.

The idiomatic EF Core approach is DbContext.Database.BeginTransactionAsync() / CommitAsync() / RollbackAsync(). Whether this is surfaced via a UnitOfWork wrapper, a transaction scope helper on the repository base, or left as a DbContext-level concern passed through the service layer is the design decision this issue should settle before implementation.

Suggested Approach

1. Spike / design decision — evaluate the three common patterns for this project's scope:
   • Expose IDbContextTransaction directly from a BeginTransactionAsync() method on IRepository<T>
   • Add a UnitOfWork abstraction that wraps the DbContext and exposes CommitAsync()
   • Keep transactions at the DbContext level inside the service (no repository change)
2. Document the chosen approach with a brief rationale (could be an ADR).
3. Implement and add integration tests (in-memory SQLite via DatabaseFakes.MigrateAsync()) covering commit and rollback paths.

Acceptance Criteria

• Design decision documented (ADR or inline comment)
• Chosen approach implemented without breaking existing single-operation behavior
• Rollback on failure is exercised by at least one test
• Tests use [Trait("Category", "Integration")] and DatabaseFakes.MigrateAsync()
• All existing tests continue to pass
• CHANGELOG.md updated

References

• EF Core Transactions
• Prerequisite: Add integration tests for Repository<T> and PlayerRepository #461 (repository integration tests — transaction tests belong in the same Integration/ layer)
• Related: Handle concurrency conflicts with EF Core using application-managed tokens #65 (concurrency conflicts — may share the transaction infrastructure)
• Related: Implement PATCH method for partial updates #342 (PATCH — first feature that could benefit from atomic multi-step writes)

[coderabbitai]

🤖 CodeRabbit Planning

Planning is in progress. This will take a moment...