Persistence schemas

The database: its schemas, how they're migrated, how character ids are handed out, and the one direct read sign-on makes. How work reaches the database is on Persistence write-behind, and why it's built this way on Persistence.

The schemas

One PostgreSQL 18 database holds three schemas, each with its own context and its own migration history inside the schema:

Schema	Holds
account	Accounts (email, salt, verifier, when created) and their suspensions (start, end or none, reason, who)
realm	Characters, with their look choices and bone values, and the sequence character ids come from
ops	The command layer's audit rows

Rules every schema follows

• Names and emails ignore case. Both use an ICU collation (und-u-ks-level2) that compares without case; each schema's first migration creates it if it isn't there, so the schemas can migrate in any order. An email is unique in any case, and so is a character name among the characters not deleted, so deleting a character frees its name.
• Unsigned ids are stored as bigint, through one convention.
• Contexts are pooled, and each runs Npgsql's retry strategy.

Migrations

Migrations are explicit: NexusTomorrow.Server migrate applies account, then realm, then ops, and the server won't start while any is pending (Host). The tests apply every migration to an empty database.

Character ids

The character id sequence steps by 1,000, so one fetch reserves a block of 1,000 ids. The realm owner takes its first block before the server listens, and asks for the next once fewer than half are left.

The account directory

Sign-on reads an account's salt, verifier and suspensions directly, with one query, because the gateway holds no game state. The query costs the same whether or not the account exists.

Known edges

• The block size is fixed in code, and only character ids come in blocks.

Where the code lives

Piece	Path
Contexts, conventions and migrations	Source/NexusTomorrow.Persistence/
Account work and the account directory	Source/NexusTomorrow.Persistence/Account/
Character work and id blocks	Source/NexusTomorrow.Persistence/Realm/
The audit	Source/NexusTomorrow.Persistence/Ops/
Tests	Tests/NexusTomorrow.Persistence.Tests/