Database

This document covers the MySQL/MariaDB backend: how the server talks to the database, what tables matter, and how schema changes are managed. Setup steps (creating users/databases) are in README.md. An entity-relationship diagram of the core tables is in diagrams/duris-database-model.html; its authority groups are traced to the bootstrap, immutable migrations, and runtime manifests rather than presented as a column-complete schema reference.

Connections and selection

See CONFIGURATION.md for the complete environment-variable reference. In particular, DB_NAME is the requested name, while the runtime port safety rule can redirect an implicit production name to duris_dev on a non-7777 port.

The server requires explicit DB_HOST, DB_USER, DB_PASSWD, and DB_NAME values from the process environment after loading .env; DB_PORT is optional and must be valid when present. It has no compiled credential or target defaults. The resolved host/database pair must also appear in DB_ALLOWED_TARGETS; remote TCP targets require verified TLS. See CONFIGURATION.md for parsing, trust-boundary, and precedence details.

The listen port applies a production safety redirect in sql_persistence_db_name() (src/sql/sql.c):

Never point a test run at production: use a non-7777 port, a development credential, and a disposable database for all development.

Connection architecture:

• Main connection - owns boot verification and the queries boot and shutdown still make. The game loop issues no query after boot: writes, command reads and account logins run on the one persistence writer, character loads on the player load worker.
• Connection pool (src/sql/sql_pool.c) - bounded, individually owned connections used by typed load, snapshot, critical-command, outbox, maintenance, locker, and retained compatibility workers. Acquire/release is mutex and condition-variable based.
• Failure behavior - the writer retries a lost connection at the head of its queue, and a login waits for one; the game loop carries on in both cases.

Every connection uses utf8mb4, UTC, READ-COMMITTED isolation, strict SQL modes, and 10-second connect/read/write deadlines. Remote targets require enforced TLS and CA verification; a protected local loopback/socket path is the only plaintext exception.

Persistence execution boundaries

Queued jobs live only in memory. Clean shutdown and copyover attempt to drain them; a crash loses what had not reached the database. Thirty seconds is the normal player checkpoint interval, not a bound during an outage or a checkpoint for artifact/guild state. Those entities queue immediate snapshots and retain dirty retry metadata.

Redis is not an authority for player dirty state. It holds floor-delta recovery data and optional sequence-numbered world generations used after graceful restart or an unclean exit (src/redis/redis.c). Recovery reads every referenced item from SQL in batches and accepts only an exact active room-owned graph before creating entities. A generation is cleared only after successful validated recovery and atomic runtime-custody hydration.

Critical gameplay commands are not coalesced like checkpoints. Each accepted command has one stable operation ID and is queued on the writer at submit, so it lands in capture order with the saves around it. The generic transaction stores canonical identity/result metadata in critical_operation_inbox, applies typed state, and inserts critical_outbox rows before one commit. Duplicate and ambiguous execution reread the inbox. Delivery is at least once with (consumer_id,outbox_id) dedupe, bounded retry, and retained dead letters. See CRITICAL_COMMAND_PIPELINE.md.

PvP combat outcomes capture presentation snapshots on the game thread before death changes equipment or level: the formatted level/class/name/guild/race description, equipment listing, and public PlayerLog text. Payload v3 owns those strings and carries them to the writer; the writer never reads live character pointers. The event and its participant snapshots commit with the existing outcome transaction, so rollback and operation-ID replay cannot leave a partially recorded battle or duplicate its participants.

The bounds preserve the former capture limits: 255 description bytes, 65,535 equipment bytes, and 99,999 public-log bytes per participant, with at most 15 participants. The critical-command payload ceiling is 3 MiB, with another 128 KiB allowed for encoded metadata; coordinator admission retains its 64 MiB budget. Payload versions 1 and 2 remain decodable with empty equipment/log snapshots. This does not recover historical missing text or add a durable pre-commit queue: the in-memory crash-loss limitation above still applies.

Immutable migration 0041_pkill_log_capacity widens legacy pkill_info.log columns from TEXT to MEDIUMTEXT before the updated server can boot. The legacy baseline upgrade alone can retain TEXT; follow it with python3 scripts/migration_runner.py run. Existing logs and NULLs are preserved, and canonical MEDIUMTEXT columns are unchanged on replay. An unexpected LONGTEXT column or other noncanonical column metadata is preserved but rejected by verification before migration success is recorded, since runtime boot requires the canonical metadata shape. This prevents strict SQL mode from rejecting 65,536–99,999-byte logs and rolling back their entire outcome.

The #58 implementation separates artifact/guild reward history from saved gameplay state. artifacts, artifact_bind and artifact_domain_state hold the artifact snapshot/mirror; guilds holds current guild totals. artifact_guild_outcome, artifact_guild_outcome_delta, artifact_delta_ledger and guild_outcome_ledger record captured reward facts. Their revisions order history; they do not approve gameplay or reconstruct present totals after later spending or timer adjustments. A history receipt alone does not prove that all state snapshots committed. Existing schema and committed v1 receipts/outbox remain supported. See the qualification evidence and the pipeline's reconciliation contract before interpreting these tables.

Artifact canonical identity storage is separate from the optional custody link within artifact_domain_state: canonical_item_uid has a unique key and no custody FK, while the existing item_uid FK remains intact. identity_state records unresolved (0), present (1), retired (2), or proven absent (3). Present and retired rows retain a positive canonical UID; unresolved and absent rows have NULL. Migration 0044 leaves legacy rows unresolved and preserves their existing timer, soul and ownership evidence. Boot stages these identities with the existing artifact/soul maps before publication. All known-artifact snapshots, including soul/reset writes, persist current UID/timer/soul together on the ordered writer. Legacy mirrors preserve identity. A failed or inconsistent refresh keeps current memory and requeues its snapshots. Retired and proven-absent evidence remains in memory after legacy tracking removal. Staff clear requires proven absence. Complete payload classification, canonical selection and admission remain tracked in issue #67.

The equivalent flat-file catalog is version 2, carrying canonical UID/state and rejecting one nonzero UID assigned to two vnums. Version 1 remains readable with unresolved identity; its next ordinary write emits version 2 without inventing a canonical copy. Older binaries refuse version 2 rather than silently dropping identity. Preserve an owner-controlled backup before any operational upgrade; none is authorized or performed by this storage implementation.

External consumers and writers

The server owns gameplay state even when another service presents or administers it. A website may read documented projections and maintain its own application tables, but it must not issue direct SQL mutations against MUD-owned player, account, item-custody, balance, auction, or lifecycle state. Those mutations must enter through an authenticated, authorized, typed server command so the game thread, ledgers, fences, and outbox remain one coherent authority.

The DurisWeb bridge follows this boundary. Its authenticated administrative character-deletion route is implemented by the MUD; auction bid and buy mutations are intentionally not exposed because a bid's money leaves the live player's wallet in memory. Adding a web action means adding a typed server-side contract and authorization policy, not granting a web database account broader write access. See api/durisweb.md.

Persistence observability

All shared MySQL execution paths record bounded, metadata-only metrics. Wrapper calls receive a compile-time file:function:line site; worker executors use an explicit semantic site. Context distinguishes the main thread and the relevant event, locker, or player-save worker. Statement classification records only a kind such as select, insert, or transaction, never SQL bytes or values.

The fixed-capacity registry aggregates calls, failures, total and maximum latency, and bounded latency buckets. When new sites exceed capacity, an overflow counter increases instead of allocating memory. Snapshots are copied under a short lock and sorted after unlock. Query execution never holds the metrics lock and the record path performs no filesystem or network I/O.

Redis workers and the remaining shared boot/recovery/maintenance command adapter expose separate bounded local health snapshots. Shared commands retain only a redacted subsystem class, operation kind, outcome counters, latency aggregates, last-success age, and primary connection/reconnect transitions. Presence, report-cache, floor, donation, and world publication workers retain matching operation counters, latency aggregates, categorized failures, failure streaks, and last-success age alongside existing bounded queue and connection state. The typed snapshots feed both redis detailed and world persistence; rendering either command performs no Redis query and stores no key, value, identity, endpoint, or credential.

Failure events may contain a process-local operation ID, source site, context, statement kind, duration, numeric MySQL error code, and SQLSTATE. They do not contain SQL text, MySQL error prose, player/account/item values, or filesystem paths. Operation IDs reset with the process and must not be used as durability, transaction, replay, or idempotency identifiers.

Schema layout

• migrations/bootstrap_legacy_baseline.sql - historical legacy input only; it is not the current install contract.
• migrations/bootstrap_multithread_safe.sql - the sealed 170-table fresh-install baseline for this branch.
• migrations/schema_migration_v*.sql -- incremental upgrades, versioned (accounts, hardcore, pets, obj UIDs, locker changes, ships/guilds retirements, ...).
• migrations/run_migration.sh -- the legacy additive upgrade/baseline-adoption path; re-runnable by design.
• migrations/migration_manifest.json and scripts/migration_runner.py -- the immutable manifest-driven path for every migration after the verified baseline. See IMMUTABLE_MIGRATIONS.md.
• migrations/runtime_compatibility_manifest.json and migrations/verify_runtime_compatibility.sh -- the read-only pre-boot contract for migration history, full metadata shape, storage engine, collation, and supported MySQL 8.0, MariaDB 10.11 and MariaDB 11.8 variants. RUNTIME_COMPATIBILITY.md states the current head and table count.

Applying schema changes

The commands below mutate schema or migration history unless marked read-only. Qualify them only against an empty disposable database or a backed-up development clone whose resolved host/database is explicitly allow-listed. Stop the game and every other writer first. Never use production for migration discovery, replay, or validation; after clone qualification, the runbook defines the separately authorized, backup-bound immutable production application.

# Empty disposable database only. Load with the explicit .env target shown in README.
# This is a mutating operation.
MYSQL_PWD="$DB_PASSWD" mysql --host="$DB_HOST" --port="${DB_PORT:-3306}" \
  --user="$DB_USER" "$DB_NAME" < migrations/bootstrap_multithread_safe.sql
python3 scripts/migration_runner.py adopt --kind fresh_bootstrap
python3 scripts/migration_runner.py run

# Local development database only. --help is safe; there is no dry-run mode.
# A normal invocation mutates immediately and records verified legacy adoption as its
# final database gate. When REDIS=TRUE, it then deletes only Duris-owned key patterns from
# the explicit local REDIS_HOST:REDIS_PORT/REDIS_DB target in REDIS_ALLOWED_TARGETS.
# Stop the game and every other Redis writer first; Redis failure fails the migration.
# Keep this owner-readable clone configuration separate from the server's .env.
MIGRATION_ENV_FILE=/path/to/owner-readable-clone.env ./migrations/run_migration.sh

# After an adopted baseline, apply immutable post-baseline migrations:
python3 scripts/migration_runner.py run

# After exact clone qualification, owner authorization, writer shutdown, and a fresh backup only:
python3 scripts/migration_runner.py run \
  --confirm-production-target "$DB_HOST/$DB_NAME" \
  --production-backup /absolute/path/to/fresh-production.sql.gz

# Read-only verification before starting the server or promoting a tested schema:
./migrations/verify_runtime_compatibility.sh

Scoped persistence/auction repair tools exist for archive-restored clones:

./migrations/verify_persistence_contract.sh
./migrations/apply_persistence_contract.sh --confirm-db <clone_db_name>

Rules of thumb (enforced by repo conventions):

• Migrations live in migrations/ -- that directory is authoritative.
• Keep them additive, guarded (IF NOT EXISTS / conditional columns), and re-runnable.
• Never run against a live database: back up, restore into a clone, validate replay against the clone first.
• Schema changes should come with a focused regression test where practical (several exist under tests/async/run_*_schema_mysql.sh; root-level tests/test_migration_replay_safety.sh checks replay safety).

Tables worth knowing

Player corpses

corpses holds the outer corpse object, corpse_items its normalized contents. sql_save_corpse() deletes and reinserts the row on every save, so created_at is the last save time, not the death time -- the stable save_id (corpse value 6) is the incident identifier and decodes to the death timestamp.

Beyond player_name, save_id, room_vnum and the display strings, the table stores the corpse's own name (owner keywords), weight, and values 0-5 and 7: death-time level, owner PID, recoverable death XP, race-war side, race, and the flag set including the humanoid and carved-part bits. All of it matters -- spell_resurrect() reads value 4, necromancy gates on CORPSE_LEVEL, do_carve() requires HUMANOID_CORPSE, and artifact looting checks the race-war side before rebinding. Restoring a corpse from prototype #2 alone (zero values, generic keywords, weight 200) silently changes all of those after a restart, which is what happened before migrations/corpse_persistence_state.sql added the columns.

Those columns are nullable on purpose. The migration reconstructs only what the table guarantees -- player-corpse classification and owner keywords -- and leaves unknown legacy weight, level, PID, XP loss, race-war side, and race as NULL rather than inventing values; the loader has runtime fallbacks for them. New corpses store the complete state.

Two conventions in sql_load_all_corpses() are worth preserving. The loader reads named result columns, not numeric indexes, and asserts the expected field count so a query edit cannot silently shift the mapping: the display fields were off by one from the day they were added (April 2026) and shifted again when ci.obj_uid/ci.item_condition were inserted ahead of them, which made every restored corpse display as the first contained item's condition (100) and then persisted that back to SQL on the next save. And when an item row fails to load, the loader records that last_item_id no longer names the object at obj_map[num_objs - 1], so a following affect row for that item is not applied to a different object.

Consistent player load

Existing-character login is asynchronous. src/player/player_load_pipeline.c owns one bounded request queue and worker; src/player/player_load_repository.c borrows one validated pool connection and opens a consistent read transaction. Required status, skills, affects, current item ownership, item metadata, pet rows, and pet item metadata are loaded in bounded set-based queries into owned DTOs. The worker never creates or traverses live P_char or P_obj instances.

A character is not loaded while it has a save queued, so a quick relog reads the latest state; the login waits while the game loop carries on. An item row is taken only if item_current_owner has no row for it or names this owner. Any other row is a stale or duplicate copy: it is skipped, logged to logs/log/dupes and removed by the owner's next save. Where the ownership row and the saved container_id disagree about nesting, the ownership row stands and the next save rewrites both.

The game thread accepts a completion only when request identity and PID still match, every required component succeeded, all configured row/byte/depth limits hold, and the item/pet graphs validate. ID maps provide linear assembly. Cancellation, timeout, missing component, malformed graph, overflow, and stale completion all discard the DTO and fail login cleanly; no partial character is published. The standalone database harness is tests/async/run_player_load_repository_mysql.sh.

Critical transactions and current item ownership

The critical-command repository uses prepared statements and a stable 128-bit operation ID. In one InnoDB transaction it creates or rereads the inbox identity, locks domain rows in deterministic key order, applies typed state and ledger changes, stores the canonical result, and inserts any outbox record. Duplicate delivery returns the stored result. If commit acknowledgement is ambiguous, the coordinator rereads by operation ID instead of replaying an unidentifiable mutation.

Memory is the authority for items. They move in memory at once, and item_current_owner is a copy that catches up: PRIMARY KEY (item_uid) lets it record one owner per item, and each save claims what its owner holds in the same transaction as the owner's rows. No row: insert one. A row naming this owner: nothing to do. A row naming any other owner: set it to this owner and write an item_owner_audit row. A row that says the item was destroyed is final: the save leaves the item and its contents out and logs them to logs/log/dupes. The claim writes root_item_uid and parent_item_uid from memory too, so moving an item, including into a container of the same owner, needs no transfer. Auctions, shops and the collector take what they trade out of memory at submit, so a later save never holds it. A save never deletes a row. Each boot deletes a player's active rows whose item is in no payload row at all (any character's or pet's, a locker's, a corpse's or a saved room item's), before the writer starts; a row an older copy still needs, to load as stale, stays, and a row auction_item_custody, artifact_domain_state or a child row still references is kept for the foreign keys (PLAYER_SAVE_PIPELINE.md).

item_ownership_ledger keeps the transfers that still commit as critical commands (creation grants, operator repair and destruction, auctions and the collector). A claim writes no ledger row, so the ledger does not explain item_revision and nothing reconciles the two; do not repair ledgers by hand.

Nesting drift from before the persistence reset (a container filled without a transfer) loads where item_current_owner puts it and is settled by the owner's next save. migrations/repair_item_nesting.sh settles it without a login, from the saved container linkage (--check reports without writing). The repair rewrites only parent_item_uid and root_item_uid, never ownership or item_revision.

For production-safe read-only classification, use:

python3 scripts/classify_item_topology.py --env-file /absolute/private/production.env

This runs one aggregate-safe snapshot query and reports separate counts for expected quarantined/inactive lifecycle rows, acyclic projection drift repairable by the existing tool, and corruption. Categories cover duplicate or ambiguous payloads, missing payload owners, missing payload/current parents, cycles, excessive depth, owner/context disagreement, vnum/state differences, and item-revision evidence. It never enables the development-only mutation script.

At an approved quiesced save boundary, stop the MUD, web process, workers, and every SQL writer, then write exact identifiers only to an existing owner-only directory:

install -d -m 0700 /private/item-topology
python3 scripts/classify_item_topology.py \
  --env-file /absolute/private/production.env \
  --artifact /private/item-topology/classification.tsv

Artifact mode refuses while another target-database connection exists. The file is mode 0600 and contains payload parent/root, current parent/root, owner/context, vnum, state, and revision evidence; stdout remains aggregate-only. Record its SHA-256 without copying rows into tickets or logs.

Rehearse migrations/repair_item_nesting.sh only against a fresh, isolated production clone configured as development. Its existing guards repair only repairable_projection_lag; owner disagreement, missing/foreign ancestors, cycles, depth failures, revision mismatches, and ambiguous evidence require a separately reviewed narrow correction. Compare byte-level fingerprints for every unaffected payload, item_current_owner, baseline, quarantine, and ledger row before and after rehearsal.

Any production correction requires explicit owner authorization, a fresh validated backup, the same protected artifact digest, writer quiescence, exact transactional row guards, and captured rollback evidence. Never relax the mutation script's production refusal. Before restart, require zero foreign-key violations and run the UID allocator, nesting, runtime compatibility, health, and log checks. Restore the exact backup on any discrepancy; do not regenerate UIDs, delete rows, reassign owners, or disable foreign keys to force a clean report.

Maintenance and data lifecycle

The maintenance scheduler gives each registered job a stable offset, row/time budget, continuation cursor, retry classification, and game-thread completion. It persists cursor/completion state under MAINTENANCE_STATE_FILE. The archive job is present but disabled in the compiled registry until lifecycle policy is approved and the manifest allows canonical mutation. The address_retention job runs hourly: it deletes account_ips and account_login_history rows last written over 30 days ago and clears the address of older log_entries, ip_info, player_data and account_characters rows (ADR 0003). An account keeps each address's last use in account_ips.updated_at, and a login drops an address past 30 days.

migrations/data_lifecycle_manifest.json inventories every database and non-database store and classifies subject mapping, purpose, season behavior, retention, archive, export, erasure, and protected exceptions. Validation fails closed on missing stores or pending destructive rules. Archive, export, and erasure schemas and operator scripts are implemented, but canonical mutation remains disabled where controller decisions are pending. This is an engineering control record, not legal advice. See DATA_LIFECYCLE.md, LIFECYCLE_ARCHIVE.md, PERSONAL_DATA_EXPORT.md, and ACCOUNT_ERASURE.md.

Active epic bonus read model

Active player epic bonuses are hydrated into fixed-capacity player-owned memory during the database player load. The login query joins the selected epic_bonus row to the union of historical positive, non-bottle epic_gain rows and committed positive, non-bottle epic_ledger rows after both the selection time and configured rolling cutoff, then groups them by calendar expiry boundary. The boundary calculation preserves the strict cutoff for gains recorded exactly at midnight. It returns no more than one row per supported expiry day rather than one row per historical gain.

The shipped rolling window is five days. The in-memory representation supports integer windows from 1 through 31 days with at most 32 daily buckets. Invalid configuration, malformed rows, query failure, or bucket overflow places that character's bonus state in an explicit unavailable state and yields a zero modifier. It never triggers a lazy query from regeneration, XP, shops, cargo, status, help, or award calculation. Cap and maximum-modifier property changes take effect from the in-memory property table on the next read. A rolling-window change marks existing player state unavailable until the next login because already-expired history cannot be reconstructed without I/O.

Selection and qualifying award ACK paths update this state on the game thread. Daily contributions expire locally at the same calendar boundary represented by the former CURDATE() predicate. The state is an active-player read model, not a new durability boundary. The balance itself is memory's: player_data.epics is what the last save wrote, and epic_ledger is history, not a sum that must match it.

Player checkpoints and terminal saves

Each player keeps per-component dirty state. The game thread captures a bounded immutable snapshot without unequipping objects or removing affects and queues it on the one persistence writer (src/player/player_save_worker.c), which writes player saves with their pets, corpse, locker and saved-room-item saves, critical commands, bank deltas and the game thread's sql jobs one at a time, in capture order. A newer save of an owner replaces its queued one only when that is the last job queued; otherwise it queues behind it, so it never overtakes a job that relies on the earlier save. With one writer every save is newer than the last, so there is no revision fence. A lost connection is retried at the head of the queue; any other failure is logged, the job dropped and the owner marked dirty, so its next save carries the state again. Dirty players are checkpointed every 30 seconds.

Camp, rent, death, idle/link-loss cleanup, ghost extraction, locker departure, copyover, shutdown, and reboot queue the final save and release the character at once; none of them waits on the database. Shutdown gives the writer 30 seconds for what is queued and always goes; a write it could not finish is named in the log. Copyover waits the same 30 seconds and is called off, leaving the game running, when the writer cannot drain.

Player replacement components

player_timers, player_undead_slots, player_forged_items, and player_granted_cmds are full replacement sets during a player status save. Each set is deleted by PID inside the active player-save transaction before its current non-zero entries are batch inserted. An empty in-memory set therefore removes every prior row instead of allowing a cleared timer, slot, recipe-like forge entry, or revoked command to return at the next login.

Every delete and insert is checked. A failure returns through the current transaction owner: a direct status save rolls back its own transaction, while a full player save rolls back the enclosing transaction. Languages and introductions use the same replacement contract. These are save semantics only; no table or index shape changed.

Operational notes

• Connection problems at boot print MySQL initialization failed! -- troubleshooting steps are in README.md, and the effective database host, port, and selected database are logged before the connection is opened.
• The cycle script records boot/shutdown timestamps and stop reasons into the database for reboot tracking (RUNBOOK.md).