PostgreSQL control service

This is the server transaction layer for roadmap milestone 8. It is runnable and crash-tested, but the playable relay still uses its existing fsynced room files. Starting this service does not migrate that relay or make the browser's current saves database-backed. The opt-in persistent cooperative room now uses this ledger directly for browser admission, shared interactions and staged gateway travel; it has a separate launch/deployment profile. Migration of existing relay saves remains future work. No numbered milestone is complete.

#Run the acceptance suite

Install Node dependencies with npm ci, and install PostgreSQL client/server tools (initdb, pg_ctl, pg_dump, pg_restore, createdb) on PATH. Then run:

npm run test:control

The command builds the service, starts a disposable database under /tmp using a private Unix socket and no TCP listener, tests it, and removes that database. It does not use the existing editor or its saved world. The tests race two item claims, kill transaction workers at six boundaries, abruptly restart PostgreSQL, restore a logical backup into another database, and check the built service's startup/shutdown. The recovery measurements and server log go into test-results/.

DW_TEST_DATABASE_URL explicitly selects an existing test-only database instead. The suite creates its control tables and fixture rooms there; it skips the local cluster restart/backup and built-entry-point checks. Do not use a production database for that option. Ordinary npm test skips database tests when no test database is selected; the dedicated CI job runs the full temporary-cluster suite.

#Start the service

For a PostgreSQL database owned by this application, set DW_DATABASE_URL and a private DW_CONTROL_TOKEN of 32–256 characters. Alternatively, set PGHOST, PGUSER, PGDATABASE, and PGPASSWORD. These separate variables allow arbitrary password characters without URI encoding.

npm run control

The service applies checksum-verified migrations, then listens on 127.0.0.1:8792. DW_CONTROL_PORT changes the port; DW_CONTROL_BIND changes the bind address. Production builds use npm run build:control followed by node control-dist/main.js. GET /health checks database availability. Migration drift or an unknown future migration prevents startup. Automatic startup also refuses a new migration when retained rooms exist; schema changes require a separately qualified migration procedure.

compose.control.yaml provides an optional internal PostgreSQL/control-service pair with its own named volume, darkwind-control-db. Set DW_POSTGRES_PASSWORD and DW_CONTROL_TOKEN in the deployment environment. Docker creates and reuses that volume. Neither service publishes a host port, and neither should receive a public Coolify domain. This file is separate from the playable beta's compose.coolify.yaml and does not replace it. Local Compose configuration and compiled startup were checked; a Docker runtime smoke check is configured in CI. Local Docker execution was unavailable because this user cannot access the Docker daemon without a password.

The PostgreSQL 18 image uses /var/lib/postgresql for the volume mount, as specified by the official image documentation.

#Internal API

All /v1/ operations require POST, JSON, and Authorization: Bearer <DW_CONTROL_TOKEN>. Browser Origin requests are rejected; there is no CORS grant. This credential belongs to a trusted server, never to player JavaScript. Requests are bounded to eight in flight, 64 KiB for commands, and 16 MiB for release publication. The service is an internal authority component, not a public account or authorization service.

Owners, actors and operation IDs are canonical lowercase UUIDs. Use a new owner UUID per host lifetime, a stable actor identity, and a stable operation ID for every retry of the same transaction. Epochs, player/item revisions and event sequences are decimal strings. Lease durations are 100–30,000 ms. A host should renew ahead of expiry; an expired lease cannot be revived by renew, and acquiring again receives a new epoch.

Commands are:

• join: create an absent player's entry pose or retain the existing player.

• checkpoint: expected player revision and a bounded pose in the current place. The trusted host must validate movement before calling it; this service does not simulate physics.

• travel: expected player revision, an authored gateway, and readyRelease. It checks reach, destination and item-based requirements, then commits the named arrival pose while retaining inventory. readyRelease binds the caller's preparation to the pinned source; it is not independent proof that collision or GPU resources were loaded. The host must prepare those before committing.

• pickup / drop: expected player revision, item, and itemRevision. A pickup checks the committed item's location/reach. Dropping puts it at the player's committed place and position. SQL constraints give each item exactly one location, in the world, held by one player, or stored in one container.

• return: expected player revision; return to the pinned entry.

• act: expected player revision and authored action; commit supported flag, door, dialogue or equipment changes.

• deposit / withdraw: expected player revision, item, itemRevision and container; validate reach, requirements, ownership and capacity, then transfer atomically. Depositing equipped items stows them.

All non-join commands increment the player revision. Items have their own revisions. Dropped items remain at their new location and can be claimed again. A disconnected player retains owned items. The persistent adapter supplies supported flag/door requirements; the old relay is not imported. Unsupported requirements fail closed. Removed authored items and containers use the staged upgrade policy. Rewards and trades remain future work.

#Commit and recovery contract

A room row serializes its transactions. The implementation uses one checked-out PostgreSQL connection for each transaction, consistent with node-postgres transaction guidance. State, item location, idempotency receipt, room sequence and publication outbox commit together with synchronous_commit=on. A matching live lease is checked under the room lock and again before commit. The HTTP response follows the commit acknowledgement.

A lost response has an uncertain outcome. Retry the same actor/operation ID and exact command under a current lease. If the earlier commit succeeded, the original receipt is returned; a reused operation ID with different work is rejected. A conflict is HTTP 409, malformed input is 400, and uncertain database availability is 503. A successful old receipt may contain an older player revision: use players/read for the current state and never apply an old receipt as a new state update.

The outbox supports at-least-once delivery. Consumers read after their committed cursor, apply events in sequence, then acknowledge progress. They must deduplicate room/sequence when their acknowledgement is lost. Cursor races are rejected; consumers re-read the cursor before retrying. Player and upgrade outboxes share the room sequence. Upgrade events have kind: "upgrade" and no player actor; consumers must handle both receipt forms. The persistent adapter publishes committed player state; a release upgrade asks connected browsers to reload.

The initial room budgets are 128 persisted players and 100,000 retained transactions, plus a maximum of 32 upgrade plans within that sequence budget. Reaching the transaction budget rejects new mutations rather than pruning retry receipts. This is not a measured capacity claim. Retention/compaction and archival must preserve the deduplication contract before the limits can increase.

#Backup and restore runbook

The manifest-backed persistent backup tool automates consistent database capture, exact retained-engine archival, empty-target restore and logical state verification. The manual steps below remain useful for diagnosis and recovery planning.

1. Back up the control database with pg_dump --format=custom. Store the dump outside the database's volume. A named volume alone is not a backup. This database includes the exact world source and engine hash, player state, item ownership, transaction receipts, outbox and consumer cursors.
2. Preserve the corresponding immutable engine bundle separately. Its hash identifies it; the database does not contain the executable engine bundle or an object-store copy of its assets.
3. Restore with pg_restore --no-owner --no-acl into a new, isolated database. Run the service's migrations against the copy; checksum drift must fail. Verify player/world hashes, item ownership, receipts and outbox before directing traffic to it.
4. Stop the old host before promotion, allow its maximum 30-second lease to expire, and acquire with a new host UUID. Do not run restored and original databases as competing production authorities. The room lease fences writers within one database, not independent restored copies.
5. Replay the outbox from consumer cursors. Retry uncertain operations with their original IDs. Resume the playable adapter only after it has restored compatible content and installed collision readiness.

The local fixture measured zero lost acknowledged transactions after worker kills and an abrupt database restart, and restored identical release/state/receipts from a logical dump. The recorded timings describe one small fixture on this machine. They do not establish production recovery time, storage power-loss behavior, a five-second motion rewind bound, replica failover, or account recovery. The existing beta/editor relay retains its file-backed storage profile. See the separate persistent-room acceptance for the opt-in browser integration.