Resource storage is asynchronous SQL in a namespace per resource, with migrations per backend

Status: Accepted (2026-11-08)

Context

Player profiles, a game mode's season and its economy must survive a restart. Storage is managed by the server and owned by resources: the server owns the backend and the schema migrations, and a resource sees only its own namespace. SQLite comes first; PostgreSQL and MySQL are expected after it. A query to those is a network round trip, while Lua runs inside the server step, and their SQL dialects differ (placeholders, RETURNING, upserts). The resulting API is dlmp.db in resources/.types/dlmp.lua.

Decision

• Asynchronous, off the step. dlmp.db.query, exec and transaction queue the request in the resource's namespace and return true at once, or nil, err when the queue is full; a malformed argument raises. The answer reaches a callback in a later step, under the same watchdog and dispatch context as a timer. The requests of one namespace run strictly in order on its own worker thread. SQLite gets the same treatment, so a disk sync never stalls the step.
• The backend's native SQL. The server does not translate dialects. Migrations are declared per backend in the resource manifest ([storage.migrations] sqlite = [...]); a resource with no list for the server's backend does not start. Parameters are positional only; values are nil, boolean, integer, number and UTF-8 string.
• A transaction is an atomic batch of statements: all or nothing, with no Lua between them. The live state is in the resource's memory, and the server is its authority; storage is its persistence. A condition goes into the SQL (WHERE money >= ?). No connection or lock is held across steps.
• One namespace per resource, named after the resource. With SQLite it is the file data/<resource>.sqlite3 next to resources/. An authorizer lets a resource statement only read and change rows (schema changes happen only in migrations) and forbids ATTACH, PRAGMA, transaction control and tables prefixed _dlmp_; attached databases are limited to zero, defensive mode is on and extensions are off. With PostgreSQL or MySQL it will be a schema or database and a role per resource.
• The server applies migrations. They run when the resource starts, before its scripts, each in its own transaction; the server records each applied migration (number, name, SHA-256) in _dlmp_migrations. An applied migration that was edited or removed stops the resource from starting. The start waits for the namespace to open and the migrations to finish.
• Lifecycle. Stopping a resource drops its pending callbacks, but the writes it already queued still run; starting the same resource again waits until the previous instance's queue is empty, so the new run reads what the old one wrote. On shutdown (the console shutdown, Ctrl-C, SIGTERM) the server stops resources with resource_stop, dependents before their dependencies, and waits for every queue to drain. A crash loses only requests that had not run; a transaction is never left half-applied.
• Limits. Per resource 256 queued requests; a statement up to 64 KiB of SQL and 256 parameters; a transaction up to 256 statements; a value up to 1 MiB; a result up to 10 000 rows and 16 MiB; 5 s per statement or transaction (60 s per migration); an SQLite namespace up to 1 GiB.
• No backend abstraction yet. With one backend the code is concrete, but the request, reply and namespace types do not depend on it. Choosing the backend in server.toml arrives with the second one.

Consequences

• The wire protocol does not change. The server depends on rusqlite with a bundled SQLite.
• A resource with [storage] needs a name of [a-z0-9_-], 1..31 bytes: it is the file name and the namespace.
• The shipped accounts resource is a device-only auth resource: the launcher-proven credential (ADR 0053) is the account, and a connection without one is refused at the auth gate. Other resources get the account through the export account_id(player_id).

Alternatives considered

• A synchronous API on SQLite. With a network backend the step would wait for a round trip, and switching backends would break every resource.
• Key-value documents. No queries by field (leaderboards, owners of territories), so every resource would maintain indexes by hand.
• Portable SQL with translation. A dialect lexer and endless mismatches: an ORM in all but name.
• An interactive transaction on a coroutine. A connection and locks held across steps, the world changing between yields, and a separate rollback path for a stop in the middle. Not taken until a real need appears.
• Coroutines and await instead of callbacks. A new SDK concept and resumption under the watchdog; dialogs and timers already use callbacks.
• PRAGMA user_version instead of a migrations table. SQLite-only, and it cannot check a migration's content.