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.

â„šī¸ Canonical DB name (verified)

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 (added source_name, new_item_id); old int columns preserved

New system

TableNotable columns
characterslevel, experience, gold, stat_str/agi/int/vit, pos_*, class_name, class_index, map
itemsid 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)
inventorycharacter_id, slot_index, item_id, quantity, equipped
professionscharacter_id, profession_id VARCHAR (values: 'woodcutting', 'fishing', 'mining'), skill_level, skill_xp
recipesid 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_ingredientsrecipe_id VARCHAR (references recipes.id), item_id, quantity
enemy_templatesid, display_name, max_hp, damage_min/max, move_speed, aggro_range, xp_reward, gold_reward_*, loot_source_id
â„šī¸ Inventory stack columns (added 2026-08-06)

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.

âš ī¸ Column gotcha

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-02

The 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