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
| NN  is_guest           INTEGER   | DEFAULT 0
|     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      | UNIQUE
| NN  inventory          TEXT      | DEFAULT '[]'
| NN  is_guest_created   INTEGER   | DEFAULT 0
|     created_at         TIMESTAMP | DEFAULT CURRENT_TIMESTAMP
|     updated_at         TIMESTAMP | DEFAULT CURRENT_TIMESTAMP
+----------------------------------+


+----------------------------------+
| character_locations              |
+----------------------------------+
| PK  character_id      INTEGER    | FK -> characters.id (ON DELETE CASCADE)
| 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)
| 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  room               TEXT      |
|     recipient_character_id INTEGER | FK -> characters.id (ON DELETE SET NULL)
|     timestamp          TIMESTAMP | DEFAULT CURRENT_TIMESTAMP
+----------------------------------+

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.