Database

Current Database Schema

The ASCII diagram below reflects the current SQLite schema in data/mud.db.

+----------------------------------+
| users                            |
+----------------------------------+
| PK  id                 INTEGER   |
| NN  username           TEXT      | UNIQUE
| NN  password_hash      TEXT      |
|     email_hash          TEXT     | UNIQUE
| NN  role               TEXT      | DEFAULT 'player'
| NN  is_active          INTEGER   | DEFAULT 1, CHECK IN (0, 1)
| NN  is_guest           INTEGER   | DEFAULT 0, CHECK IN (0, 1)
|     guest_expires_at   TIMESTAMP |
| NN  account_origin     TEXT      | DEFAULT 'legacy'
|     created_at         TIMESTAMP | DEFAULT CURRENT_TIMESTAMP
|     last_login         TIMESTAMP |
|     tombstoned_at      TIMESTAMP |
+----------------------------------+


+----------------------------------+
| characters                       |
+----------------------------------+
| PK  id                 INTEGER   |
|     user_id            INTEGER   | FK -> users.id (ON DELETE SET NULL)
| NN  name               TEXT      |
| NN  world_id           TEXT      | PART OF UNIQUE(world_id, name)
| NN  inventory          TEXT      | DEFAULT '[]'
| NN  is_guest_created   INTEGER   | DEFAULT 0, CHECK IN (0, 1)
|     created_at         TIMESTAMP | DEFAULT CURRENT_TIMESTAMP
|     updated_at         TIMESTAMP | DEFAULT CURRENT_TIMESTAMP
|     base_state_json    TEXT      |
|     current_state_json TEXT      |
|     state_seed         INTEGER   | DEFAULT 0, CHECK >= 0
|     state_version      TEXT      |
|     state_updated_at   TIMESTAMP |
+----------------------------------+


+----------------------------------+
| character_locations              |
+----------------------------------+
| PK  character_id      INTEGER    | FK -> characters.id (ON DELETE CASCADE)
| NN  world_id           TEXT      |
| NN  room_id            TEXT      |
|     updated_at         TIMESTAMP | DEFAULT CURRENT_TIMESTAMP
+----------------------------------+


+----------------------------------+
| sessions                         |
+----------------------------------+
| PK  id                 INTEGER   |
| NN  user_id            INTEGER   | FK -> users.id (ON DELETE CASCADE)
|     character_id       INTEGER   | FK -> characters.id (ON DELETE SET NULL)
|     world_id           TEXT      |
| NN  session_id         TEXT      | UNIQUE
|     created_at         TIMESTAMP | DEFAULT CURRENT_TIMESTAMP
|     last_activity      TIMESTAMP | DEFAULT CURRENT_TIMESTAMP
|     expires_at         TIMESTAMP |
|     client_type        TEXT      | DEFAULT 'unknown'
+----------------------------------+


+----------------------------------+
| chat_messages                    |
+----------------------------------+
| PK  id                 INTEGER   |
|     character_id       INTEGER   | FK -> characters.id (ON DELETE SET NULL)
|     user_id            INTEGER   | FK -> users.id (ON DELETE SET NULL)
| NN  message            TEXT      |
| NN  world_id           TEXT      |
| NN  room               TEXT      |
|     recipient_character_id INTEGER | FK -> characters.id (ON DELETE SET NULL)
|     timestamp          TIMESTAMP | DEFAULT CURRENT_TIMESTAMP
+----------------------------------+


+----------------------------------+
| worlds                           |
+----------------------------------+
| PK  id                 TEXT      |
| NN  name               TEXT      |
|     description        TEXT      |
| NN  is_active          INTEGER   | DEFAULT 1, CHECK IN (0, 1)
| NN  config_json        TEXT      | DEFAULT '{}'
|     created_at         TIMESTAMP | DEFAULT CURRENT_TIMESTAMP
+----------------------------------+


+----------------------------------+
| world_permissions                |
+----------------------------------+
| PK  user_id            INTEGER   | FK -> users.id (ON DELETE CASCADE)
| PK  world_id           TEXT      | FK -> worlds.id (ON DELETE CASCADE)
| NN  can_access         INTEGER   | DEFAULT 1, CHECK IN (0, 1)
|     created_at         TIMESTAMP | DEFAULT CURRENT_TIMESTAMP
+----------------------------------+


+----------------------------------+
| axis                             |
+----------------------------------+
| PK  id                 INTEGER   |
| NN  world_id           TEXT      |
| NN  name               TEXT      |
|     description        TEXT      |
|     ordering_json      TEXT      |
+----------------------------------+


+----------------------------------+
| axis_value                       |
+----------------------------------+
| PK  id                 INTEGER   |
| NN  axis_id            INTEGER   | FK -> axis.id (ON DELETE CASCADE)
| NN  value              TEXT      |
|     min_score          REAL      |
|     max_score          REAL      |
|     ordinal            INTEGER   |
+----------------------------------+


+----------------------------------+
| event_type                       |
+----------------------------------+
| PK  id                 INTEGER   |
| NN  world_id           TEXT      |
| NN  name               TEXT      |
|     description        TEXT      |
+----------------------------------+


+----------------------------------+
| character_axis_score             |
+----------------------------------+
| PK  character_id      INTEGER    | FK -> characters.id (ON DELETE CASCADE)
| PK  axis_id           INTEGER    | FK -> axis.id (ON DELETE CASCADE)
| NN  world_id           TEXT      |
| NN  axis_score         REAL      |
|     updated_at         TIMESTAMP | DEFAULT CURRENT_TIMESTAMP
+----------------------------------+


+----------------------------------+
| event                            |
+----------------------------------+
| PK  id                 INTEGER   |
| NN  world_id           TEXT      |
| NN  event_type_id      INTEGER   | FK -> event_type.id
|     timestamp          TIMESTAMP | DEFAULT CURRENT_TIMESTAMP
+----------------------------------+


+----------------------------------+
| event_entity_axis_delta          |
+----------------------------------+
| PK  id                 INTEGER   |
| NN  event_id           INTEGER   | FK -> event.id (ON DELETE CASCADE)
| NN  character_id       INTEGER   | FK -> characters.id (ON DELETE CASCADE)
| NN  axis_id            INTEGER   | FK -> axis.id (ON DELETE CASCADE)
| NN  old_score          REAL      |
| NN  new_score          REAL      |
| NN  delta              REAL      |
+----------------------------------+


+----------------------------------+
| event_metadata                   |
+----------------------------------+
| PK  id                 INTEGER   |
| NN  event_id           INTEGER   | FK -> event.id (ON DELETE CASCADE)
| NN  key                TEXT      |
| NN  value              TEXT      |
+----------------------------------+


+----------------------------------+
| policy_item                      |
+----------------------------------+
| PK  policy_id          TEXT      |
| NN  policy_type        TEXT      |
| NN  namespace          TEXT      |
| NN  policy_key         TEXT      |
| NN  created_at         TEXT      | DEFAULT CURRENT_TIMESTAMP
| UNI(policy_type, namespace, policy_key)
+----------------------------------+


+----------------------------------+
| policy_variant                   |
+----------------------------------+
| PK  id                 INTEGER   |
| NN  policy_id          TEXT      | FK -> policy_item.policy_id (ON DELETE CASCADE)
| NN  variant            TEXT      |
| NN  schema_version     TEXT      |
| NN  policy_version     INTEGER   |
| NN  status             TEXT      | CHECK IN (draft, candidate, active, archived)
| NN  content_json       TEXT      |
| NN  content_hash       TEXT      |
| NN  updated_at         TEXT      |
| NN  updated_by         TEXT      |
| UNI(policy_id, variant)
+----------------------------------+


+----------------------------------+
| policy_activation                |
+----------------------------------+
| PK  world_id           TEXT      | FK -> worlds.id (ON DELETE CASCADE)
| PK  client_profile     TEXT      | DEFAULT ''
| PK  policy_id          TEXT      | FK -> policy_item.policy_id (ON DELETE CASCADE)
| NN  variant            TEXT      | FK -> policy_variant(policy_id, variant)
| NN  activated_at       TEXT      |
| NN  activated_by       TEXT      |
|     rollback_of_activation_id INTEGER |
+----------------------------------+


+----------------------------------+
| policy_validation_run            |
+----------------------------------+
| PK  id                 INTEGER   |
| NN  policy_id          TEXT      |
| NN  variant            TEXT      |
| NN  is_valid           INTEGER   | CHECK IN (0, 1)
| NN  errors_json        TEXT      | DEFAULT '[]'
| NN  validated_at       TEXT      |
| NN  validated_by       TEXT      |
+----------------------------------+


+----------------------------------+
| policy_audit_event               |
+----------------------------------+
| PK  id                 INTEGER   |
| NN  event_type         TEXT      |
|     world_id           TEXT      |
| NN  client_profile     TEXT      | DEFAULT ''
|     policy_id          TEXT      |
|     variant            TEXT      |
| NN  actor              TEXT      |
| NN  event_payload_json TEXT      | DEFAULT '{}'
| NN  created_at         TEXT      |
+----------------------------------+


+----------------------------------+
| policy_publish_run               |
+----------------------------------+
| PK  id                 INTEGER   |
| NN  world_id           TEXT      | FK -> worlds.id (ON DELETE CASCADE)
| NN  client_profile     TEXT      | DEFAULT ''
| NN  actor              TEXT      |
| NN  manifest_json      TEXT      |
| NN  created_at         TEXT      |
+----------------------------------+

Notes

• users.email_hash stores a hashed email address (no plaintext email). The column is nullable during development but intended to be required later; the unique index keeps that migration path open.

• users.is_guest + users.guest_expires_at model temporary accounts that are auto-purged; the user row is deleted and related characters are unlinked (user_id set to NULL) rather than deleted.

• characters.name is a plain TEXT field, so names with spaces (e.g., first + last) are supported; uniqueness is enforced per world by UNIQUE(world_id, name).

• Session integrity is enforced by SQLite triggers: account-only sessions must keep both character_id and world_id NULL; in-world sessions must set both and match character ownership/world.

• Axis state is tracked in normalized tables (axis, axis_value, character_axis_score) with an event ledger (event* tables).

• Canonical policy control-plane state lives in policy_* tables. Runtime policy reads use effective policy_activation pointers rather than world package files.

• world_permissions stores invite-style access grants. Open-world access is policy-driven from config and may not require a row in this table.

• Hot-path indexes are intentionally maintained for world-scoped session activity, character ownership queries, and room chat history lookups.

• Runtime code should import DB operations from mud_server.db.facade; mud_server.db.database is a compatibility re-export surface.