The OfficeFighters database holds accounts, fighter versions, asset relationships, match records and pending email. Concurrent edits and a refresh racing with logout are therefore ordinary data-model concerns. Its DB-first workflow and AtomicWrite helper connect schema ownership with the persistence of those decisions.
Start the schema in SQL
Numbered scripts in database define changes and schema_versions records their SHA-256 information. scaffold.ps1 then generates EF Core entities and the context from the database. Tables, constraints and indexes are reviewed as SQL before application models consume them.
Microsoft describes reverse engineering as generating entity classes and a DbContext from a schema. In this project, durable custom behavior belongs outside generated files so regeneration does not erase it. Services and explicit extension boundaries preserve that separation.
Understand the connection role
Development uses Supabase PostgreSQL. Application access and administrative schema/scaffolding tasks have different responsibilities. The project documents a Session pooler connection for IPv4 and bounds the application's connection pool. A pool limit is a resource limit, not a guarantee of unlimited query capacity. Connection values remain deployment configuration.
Shape read queries for their callers
AssetService applies filters in SQL, reads without tracking, projects the required fields and limits page size to one hundred. Listing metadata does not require transporting atlas bytes or every historical version. Total count and page retrieval are separate queries, so concurrent changes can produce a temporary difference between the displayed total and page contents.
This division keeps lists small and lets detail requests load versions and references when required. Query review should inspect returned fields and rows, not only the number of database calls.
Use revision to expose stale edits
A revision check asks whether the record is still the version the caller edited. This illustrative SQL explains the principle rather than reproducing an application endpoint.
-- Illustrative SQL equivalent of a revision-checked update.
-- :id and :expected_revision are application parameters.
UPDATE fighters
SET revision = revision + 1
WHERE id = :id AND revision = :expected_revision;
-- Zero affected rows means the expected version was not updated.If no row changes, silently applying the old form over newer data can discard another edit. The UI needs to preserve the user's input while reporting the conflict. A row revision protects the expected version; a decision derived from several rows requires a broader transaction boundary.
Retry the complete decision
AtomicWrite places read-modify-write work inside a Serializable transaction covered by the execution strategy.
// AtomicWrite.RunAsync: abbreviated transaction boundary.
return db.Database.CreateExecutionStrategy().ExecuteAsync(
async () => {
db.ChangeTracker.Clear();
await using var tx = await db.Database.BeginTransactionAsync(
IsolationLevel.Serializable, ct);
var result = await work(ct);
await tx.CommitAsync(ct);
return result;
});Clearing ChangeTracker at the beginning prevents a new attempt from blindly reusing tracked state. The work must read its required data again and remain valid when executed more than once. Closing over an earlier entity or a previously calculated decision would weaken that design.
Choose commit semantics deliberately
RunAsync can commit even when the returned business result is a failure. Token-family revocation on a denied operation is one reason. RunOrRollbackAsync commits only successful ServiceResult values, protecting multi-step edits from partial persistence after a later rejection.
For example, replacing relationships must not leave only the initial deletion committed if insertion fails. A security rejection may instead need its revocation written. The helper choice therefore describes business semantics rather than coding style.
Keep external effects outside retryable database work
A serialization conflict or deadlock can rerun the transaction delegate. SMTP and object-store writes cannot be rolled back by PostgreSQL. Putting them casually inside that delegate can repeat an external effect. The email outbox demonstrates recording an intention in the database and performing delivery later.
Useful checks cover two edits with one revision, logout racing with refresh, rollback after a rejected second write and clean tracking state on retry. This article reviews the inspected implementation; no OfficeFighters schema changes or live-data experiments were performed for it.
Kaynaklar / References
Microsoft Learn — EF Core reverse engineering · PostgreSQL — transaction isolation
More in the OfficeFighters series
- OfficeFighters — managing game assets with Supabase Storage and S3
- OfficeFighters — real-time game communication with SignalR and WebSocket
- OfficeFighters — Phaser, fixed-step simulation and snapshot interpolation
- OfficeFighters — resilient email delivery with MailKit and an outbox
- OfficeFighters — JWT, refresh-token rotation and multi-tab session management