Database Schema
MySQL 8, database crossworlds. Two systems live side by side: the sacred old gear tables and the newer character/inventory/combat tables. Don't mix them.
The live database is crossworlds (user crossworlds, creds in /opt/crossworlds-auth/.env). Older docs (SERVER_REFERENCE.md) reference rod_online / rodgame â that is stale. See Canonical Facts & Doc Divergences.
Old gear system â leave alone
Unity calls these on every spawn. Do not rename, remove, or alter:
- Tables:
item_template,item_instance,character_gear loot_tablesâ extended (addedsource_name,new_item_id); old int columns preserved
New system
| Table | Notable columns |
|---|---|
characters | level, experience, gold, stat_str/agi/int/vit, pos_*, class_name, class_index, map |
items | id VARCHAR, name, rarity, item_type ENUM (see below), stat_bonus JSON, sell_value, stackable (TINYINT 1, default 1), max_stack_size (INT UNSIGNED, default 99) |
inventory | character_id, slot_index, item_id, quantity, equipped |
professions | character_id, profession_id VARCHAR (values: 'woodcutting', 'fishing', 'mining'), skill_level, skill_xp |
recipes | id VARCHAR (stable string key), name, profession_id VARCHAR, skill_level_required, result_item_id, result_quantity, recipe_type ('smelt' or 'craft'), craft_time_seconds |
recipe_ingredients | recipe_id VARCHAR (references recipes.id), item_id, quantity |
enemy_templates | id, display_name, max_hp, damage_min/max, move_speed, aggro_range, xp_reward, gold_reward_*, loot_source_id |
Apply _CONTEXT/inventory-stack-settings-patch.sql before deploying stack-aware inventory endpoints. Adds stackable TINYINT(1) NOT NULL DEFAULT 1 and max_stack_size INT UNSIGNED NOT NULL DEFAULT 99 to the items table; existing rows default to stackable with a max stack of 99.
items.item_type ENUM
The full ENUM as of 2026-08-20 (_CONTEXT/professions-full-patch.sql):
'weapon', 'armor_head', 'armor_chest', 'armor_legs', 'armor_feet',
'armor_hands', 'offhand', 'ring', 'trinket', 'material', 'consumable'
'consumable' was added in the 2026-08-20 patch. Rows authored before that patch with a now-invalid type were repaired to their correct values by the same migration. LootPersistenceService maps LootDatabaseItemType.Consumable â "consumable" for deployment syncs.
professions.profession_id
The column stores stable string identifiers: 'woodcutting', 'fishing', 'mining'. The 2026-08-20 migration converted any rows written by the retired numeric contract ('0', '1', '2') to these strings. ProfessionManager.cs maps between client-side indices (0, 1, 2) and wire IDs at runtime via ToWireId / TryFromWireId.
recipes stable IDs
Recipe rows use stable VARCHAR primary keys (e.g. 'smelt_copper_ingot', 'craft_minor_healing_potion'). The previous schema used auto-increment integer IDs via LAST_INSERT_ID(); those rows were removed and replaced by the 2026-08-20 patch. Do not use LAST_INSERT_ID() for this table.
recipe_ingredients.recipe_id is a VARCHAR foreign key referencing recipes.id.
Tables with server endpoints (no client consumer yet)
The server exposes talents, quests, guilds, mastery, and leaderboards, backed by their own tables (guilds, guild_members, talent/quest/mastery/leaderboard tables). The current Unity client only consumes mastery among these; the rest are server-side only. gold_transactions and marketplace_listings exist as schema without an active feature. See Implementation Status.
The XP column is experience, not xp. Unity's progression payloads must send "experience".
accounts (auth, not game data)
Login/identity table behind crossworlds-auth, not part of the "old gear" or "new system" split above. Notable columns: username, email, discord_username, password_hash, active, alpha_access, terms_agreed/terms_agreed_at, role (player/admin/gm), gm_enabled, gm_level, gm_permissions. See Login & Authentication Flow for how role/alpha_access gate /login.
crossworlds_staging.accounts drifted from prod, fixed 2026-08-02The staging DB was cloned from prod before discord_username, alpha_access, terms_agreed, and terms_agreed_at existed, and was never re-synced â so staging couldn't be used to test alpha-gating or the terms-agreement flow (the columns simply didn't exist). Backed up the table, then added all four columns to match prod's exact type/collation/default/position; all 4 existing staging rows survived with the new columns backfilled to their defaults. Note this only fixes the schema â crossworlds-auth-staging/server.js still doesn't read/write these columns, so those flows still don't functionally exist on staging; that's a separate code task if ever needed.
Migration idiom (MySQL 8)
-- ADD COLUMN IF NOT EXISTS workaround
SET @sql = IF(
(SELECT COUNT(*) FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='tbl' AND COLUMN_NAME='col') = 0,
'ALTER TABLE tbl ADD COLUMN col INT NOT NULL DEFAULT 0',
'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
Related: Auth & Game API ¡ Loot, Inventory & Crafting ¡ Canonical Facts & Doc Divergences ¡ Login & Authentication Flow