DATABASE.md

SQLite via better-sqlite3. Pragmas at connect: journal_mode=WAL, foreign_keys=ON, busy_timeout=5000.

Migration rules

Files in src/db/migrations/ named NNN_description.sql (zero-padded).
Applied in order; tracked in _migrations(name TEXT PRIMARY KEY, applied_at INTEGER).
Never edit an applied migration. Add a new one.
Each file runs inside a transaction.

Conventions

Primary keys: id INTEGER PRIMARY KEY AUTOINCREMENT.
Timestamps: INTEGER ms epoch UTC. Column names end in _at.
Booleans: INTEGER 0/1 with CHECK.
Money/stat columns: INTEGER with CHECK (>= 0) where negatives are illegal.
Add indexes for every foreign key and every leaderboard sort column.

Schema (v1)

001_users.sql

CREATE TABLE users (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  username      TEXT    NOT NULL,
  password_hash TEXT    NOT NULL,
  created_at    INTEGER NOT NULL,
  last_login_at INTEGER,
  is_banned     INTEGER NOT NULL DEFAULT 0 CHECK (is_banned IN (0,1))
);
CREATE UNIQUE INDEX idx_users_username_nocase ON users(username COLLATE NOCASE);

002_characters.sql

CREATE TABLE characters (
  id                INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id           INTEGER NOT NULL UNIQUE REFERENCES users(id) ON DELETE CASCADE,
  name              TEXT    NOT NULL,
  class             TEXT    NOT NULL CHECK (class IN ('warrior','mage','ranger')),
  level             INTEGER NOT NULL DEFAULT 1 CHECK (level >= 1),
  xp                INTEGER NOT NULL DEFAULT 0 CHECK (xp >= 0),
  gold              INTEGER NOT NULL DEFAULT 50 CHECK (gold >= 0),
  str               INTEGER NOT NULL,
  agi               INTEGER NOT NULL,
  int               INTEGER NOT NULL,
  vit               INTEGER NOT NULL,
  unspent_points    INTEGER NOT NULL DEFAULT 0 CHECK (unspent_points >= 0),
  hp_current        INTEGER NOT NULL CHECK (hp_current >= 0),
  energy            INTEGER NOT NULL CHECK (energy >= 0),
  energy_updated_at INTEGER NOT NULL,
  kills             INTEGER NOT NULL DEFAULT 0,
  deaths            INTEGER NOT NULL DEFAULT 0,
  created_at        INTEGER NOT NULL
);
CREATE UNIQUE INDEX idx_characters_name_nocase ON characters(name COLLATE NOCASE);
CREATE INDEX idx_characters_level ON characters(level DESC, xp DESC);
CREATE INDEX idx_characters_gold  ON characters(gold DESC);
CREATE INDEX idx_characters_kills ON characters(kills DESC);

003_content.sql (static game data, loaded by seeds)

CREATE TABLE zones (
  id          INTEGER PRIMARY KEY,
  slug        TEXT NOT NULL UNIQUE,
  name        TEXT NOT NULL,
  description TEXT NOT NULL,
  min_level   INTEGER NOT NULL,
  energy_cost INTEGER NOT NULL
);
CREATE TABLE monsters (
  id        INTEGER PRIMARY KEY,
  zone_id   INTEGER NOT NULL REFERENCES zones(id),
  name      TEXT NOT NULL,
  level     INTEGER NOT NULL,
  hp        INTEGER NOT NULL,
  attack    INTEGER NOT NULL,
  defense   INTEGER NOT NULL,
  speed     INTEGER NOT NULL,
  xp        INTEGER NOT NULL,
  gold_min  INTEGER NOT NULL,
  gold_max  INTEGER NOT NULL,
  weight    INTEGER NOT NULL DEFAULT 10      -- spawn weight within zone
);
CREATE TABLE items (
  id         INTEGER PRIMARY KEY,
  slug       TEXT NOT NULL UNIQUE,
  name       TEXT NOT NULL,
  description TEXT NOT NULL,
  type       TEXT NOT NULL CHECK (type IN ('weapon','armor','trinket','consumable','material')),
  rarity     TEXT NOT NULL CHECK (rarity IN ('common','uncommon','rare','epic','legendary')),
  min_level  INTEGER NOT NULL DEFAULT 1,
  buy_price  INTEGER NOT NULL DEFAULT 0,      -- 0 = not sold in shop
  stackable  INTEGER NOT NULL DEFAULT 0 CHECK (stackable IN (0,1)),
  bonus_str  INTEGER NOT NULL DEFAULT 0,
  bonus_agi  INTEGER NOT NULL DEFAULT 0,
  bonus_int  INTEGER NOT NULL DEFAULT 0,
  bonus_vit  INTEGER NOT NULL DEFAULT 0,
  bonus_attack  INTEGER NOT NULL DEFAULT 0,
  bonus_defense INTEGER NOT NULL DEFAULT 0,
  effect     TEXT                              -- consumables: 'heal:50' | 'energy:25'
);
CREATE TABLE loot_table (
  monster_id INTEGER NOT NULL REFERENCES monsters(id),
  item_id    INTEGER NOT NULL REFERENCES items(id),
  chance_pct REAL    NOT NULL CHECK (chance_pct > 0 AND chance_pct <= 100),
  qty_min    INTEGER NOT NULL DEFAULT 1,
  qty_max    INTEGER NOT NULL DEFAULT 1,
  PRIMARY KEY (monster_id, item_id)
);

Seeds use INSERT ... ON CONFLICT(id) DO UPDATE so re-running is safe.

004_inventory.sql

CREATE TABLE inventory (
  id           INTEGER PRIMARY KEY AUTOINCREMENT,
  character_id INTEGER NOT NULL REFERENCES characters(id) ON DELETE CASCADE,
  item_id      INTEGER NOT NULL REFERENCES items(id),
  quantity     INTEGER NOT NULL DEFAULT 1 CHECK (quantity >= 1),
  equipped_slot TEXT CHECK (equipped_slot IN ('weapon','armor','trinket')),
  acquired_at  INTEGER NOT NULL
);
CREATE INDEX idx_inventory_char ON inventory(character_id);
-- one equipped item per slot per character
CREATE UNIQUE INDEX idx_inventory_equipped
  ON inventory(character_id, equipped_slot) WHERE equipped_slot IS NOT NULL;

Rules: stackable items merge into one row per (character, item). Non-stackable items get one row each.

005_expeditions.sql

CREATE TABLE expedition_defs (
  id            INTEGER PRIMARY KEY,
  slug          TEXT NOT NULL UNIQUE,
  name          TEXT NOT NULL,
  description   TEXT NOT NULL,
  min_level     INTEGER NOT NULL,
  duration_sec  INTEGER NOT NULL,
  energy_cost   INTEGER NOT NULL,
  xp_reward     INTEGER NOT NULL,
  gold_min      INTEGER NOT NULL,
  gold_max      INTEGER NOT NULL
);
CREATE TABLE expeditions (
  id           INTEGER PRIMARY KEY AUTOINCREMENT,
  character_id INTEGER NOT NULL REFERENCES characters(id) ON DELETE CASCADE,
  def_id       INTEGER NOT NULL REFERENCES expedition_defs(id),
  started_at   INTEGER NOT NULL,
  ends_at      INTEGER NOT NULL,
  claimed_at   INTEGER                       -- NULL until claimed
);
-- at most one unclaimed expedition per character
CREATE UNIQUE INDEX idx_one_active_expedition
  ON expeditions(character_id) WHERE claimed_at IS NULL;

006_logs.sql

CREATE TABLE combat_log (
  id           INTEGER PRIMARY KEY AUTOINCREMENT,
  character_id INTEGER NOT NULL REFERENCES characters(id) ON DELETE CASCADE,
  monster_id   INTEGER NOT NULL REFERENCES monsters(id),
  won          INTEGER NOT NULL CHECK (won IN (0,1)),
  rounds       INTEGER NOT NULL,
  xp_gained    INTEGER NOT NULL,
  gold_gained  INTEGER NOT NULL,
  created_at   INTEGER NOT NULL
);
CREATE INDEX idx_combat_log_char ON combat_log(character_id, created_at DESC);

CREATE TABLE audit_log (            -- security-relevant events
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id    INTEGER,
  event      TEXT NOT NULL,         -- 'login_ok','login_fail','register','logout'
  ip         TEXT,
  created_at INTEGER NOT NULL
);

Sessions

Created automatically by the session store package. Don't hand-write that table.

Stretch-goal tables (do NOT create until needed)

guilds, guild_members, market_listings, pvp_matches, chat_messages, achievements, daily_rewards.

7.0 KiB Raw Permalink Blame History