Schema overview

The database schema lives entirely in sql/. Each table has its own .sql file. The schema uses utf8mb4 / utf8mb4_general_ci throughout. There are roughly 130 tables, organized into the functional groups below.

Accounts

Tables that store player account data, authentication, and session state.

Table	Description
`accounts`	Core account record: login, hashed password, email, status, privileges, expansions
`accounts_banned`	Ban records linked to an account
`accounts_sessions`	Active session tracking
`accounts_parties`	Party membership persistence
`accounts_totp`	TOTP two-factor authentication secrets
`accounts_trust_tokens`	Trusted device tokens
`account_ip_record`	IP address history per account
`ip_exceptions`	IP allowlist/denylist overrides
`help_desk`	In-game GM help requests

Characters

Tables that store everything about a player character. These are the “protected” tables that dbtool.py never overwrites during updates.

Table	Description
`chars`	Core character record: name, nation, position (zone, x/y/z), playtime, GM level, quests, keyitems, missions (stored as blobs)
`char_look`	Race, face, and equipment appearance
`char_stats`	HP, MP, and base attribute values
`char_jobs`	Job levels and experience
`char_exp`	Experience and limit point totals
`char_job_points`	Job point accumulation
`char_inventory`	Item inventory contents and slots
`char_equip`	Currently equipped gear
`char_equip_saved`	Saved equipment sets
`char_spells`	Known spells
`char_skills`	Weapon and magic skill levels
`char_merit`	Merit point allocations
`char_effects`	Active status effects
`char_vars`	Key-value store for script variables
`char_flags`	Boolean flags (tutorial completion, etc.)
`char_points`	Currency totals (conquest points, sparks, etc.)
`char_unlocks`	Unlocked abilities, weapon skills, etc.
`char_profile`	Bazaar message and other profile fields
`char_storage`	Mog locker and porter moogle storage
`char_style`	Style lock appearance
`char_pet`	Persistent pet data
`char_chocobos`	Raised chocobo records
`char_monstrosity`	Monstrosity species/level data
`char_history`	Login timestamps and historical stats
`char_blacklist`	Per-character blacklist entries
`char_fishing_contest_history`	Fishing contest participation history
`char_recast`	Ability recast timers

Items

Static game data tables describing all item types. These are imported from source SQL and are not player data.

Table	Description
`item_basic`	Base item record: item ID, name, sort key, AH category, flags
`item_equipment`	Equipment slots, level, jobs, races, element
`item_weapon`	Weapon-specific stats: damage, delay, skill
`item_usable`	Usable item scripts and effects
`item_furnishing`	Furnishing size and Mog House storage bonus
`item_puppet`	Automaton attachment data
`item_mods`	Item stat modifiers
`item_mods_pet`	Pet-specific item modifiers
`item_latents`	Latent effect conditions and modifiers
`augments`	Augment stat definitions

Mobs and NPCs

Tables that define monsters, NPCs, and their behaviors.

Table	Description
`mob_pools`	Mob template: family, model, jobs, combat stats, behavior flags
`mob_spawn_points`	Zone-specific spawn locations and rotations
`mob_spawn_slots`	Grouping of spawn points into spawn slots
`mob_groups`	Groupings of mob pools for a zone
`mob_family_system`	Mob family definitions: resistances, aggro type
`mob_family_mods`	Stat modifiers per mob family
`mob_pool_mods`	Per-pool stat modifier overrides
`mob_resistances`	Element/status resistance values per pool
`mob_droplist`	Item drop tables with rates
`mob_skill_lists`	Which weapon skill lists a mob can use
`mob_skills`	Individual mob weapon skill definitions
`mob_spell_lists`	Which spell lists a mob can use
`npc_list`	NPC definitions: name, zone, position, model
`pet_list`	Summonable pet definitions
`pet_skills`	Pet ability definitions
`pet_name`	Randomly assigned pet name pools
`instance_list`	Instanced content (BCNM, etc.) definitions
`instance_entities`	Mob/NPC spawns within instances
`bcnm_info`	BCNM-specific configuration

Zones

Tables that define world geography, routing, and zone configuration.

Table	Description
`zone_settings`	Per-zone configuration: zone ID, IP, port, music, tax rate, misc flags
`zone_weather`	Weather pattern definitions per zone
`zonelines`	Zone transition connections and destination positions
`transport`	Ship/airship transport routes
`water_points`	Water tile definitions for fishing
`campaign_map`	Campaign zone ownership data
`campaign_nation`	Nation-specific campaign stats

Spells and abilities

Tables covering magic, abilities, and job-specific systems.

Table	Description
`spell_list`	Master spell definitions: MP cost, cast time, recast, targets
`blue_spell_list`	Blue Mage spell-specific data
`blue_spell_mods`	Blue Mage spell stat modifier contributions
`blue_traits`	Blue Mage trait unlocks from spell sets
`abilities`	Job ability definitions
`abilities_charges`	Charge-based ability configuration
`automaton_abilities`	Automaton ability definitions
`automaton_spells`	Automaton spell definitions
`status_effects`	Status effect definitions and flags
`traits`	Passive trait definitions
`skill_caps`	Weapon/magic skill caps by job and level
`skill_ranks`	Skill rank tiers by job
`weapon_skills`	Weapon skill definitions
`skillchain_damage_modifiers`	Skillchain element multipliers
`merits`	Merit category and point cost data
`job_points`	Job point category definitions
`job_point_gifts`	Milestone gift rewards per job
`despoil_effects`	Despoil ability result table

Economy and crafting

Tables for the auction house, shops, and crafting systems.

Table	Description
`auction_house`	Active auction listings
`auction_house_items`	Historical sold item records
`delivery_box`	Delivery box item queue
`guild_shops`	Guild shop stock and prices
`guilds`	Guild definitions and crafting ranks
`guild_item_points`	Guild item point values
`synth_recipes`	Synthesis crafting recipes
`synergy_recipes`	Synergy crafting recipes
`gardening_results`	Gardening yield table

Server state and audit

Tables for server-wide state, economy control, and GM audit logging.

Table	Description
`server_variables`	Key-value store for server-wide state (conquest tallies, event flags)
`conquest_system`	Conquest region ownership data
`unity_system`	Unity accord state
`fishing_contest`	Active fishing contest state
`fishing_contest_entries`	Fishing contest participant entries
`linkshells`	Linkshell membership and rank data
`audit_chat`	Chat log for GM review
`audit_trade`	Trade transaction log
`audit_bazaar`	Bazaar purchase log
`audit_dbox`	Delivery box transaction log
`audit_vendor`	NPC vendor purchase log
`audit_gm`	GM command log
`cheat_types`	Cheat detection type definitions

Experience and progression

Tables for leveling, skill progression, and experience tables.

Table	Description
`exp_table`	Experience points required per level
`exp_base`	Base experience point values
`monstrosity_exp_table`	Monstrosity species experience table
`monstrosity_species`	Monstrosity playable species definitions
`monstrosity_instincts`	Monstrosity instinct ability definitions
`monstrosity_instinct_mods`	Monstrosity instinct stat modifiers

Fishing

Tables for the fishing system.

Table	Description
`fishing_area`	Fishing area definitions
`fishing_bait`	Bait item definitions
`fishing_bait_affinity`	Bait-to-fish affinity mappings
`fishing_catch`	Catch result table
`fishing_fish`	Fish species definitions
`fishing_group`	Fishing group (NPC fishing group) data
`fishing_mob`	Mob catches from fishing
`fishing_rod`	Fishing rod definitions
`fishing_zone`	Per-zone fishing configuration

Protected tables

The following tables contain player data and are never overwritten by dbtool.py update. They are only imported during a fresh database setup (when the table doesn’t yet exist): accounts, accounts_banned, accounts_totp, auction_house, auction_house_items, audit_bazaar, audit_dbox, audit_trade, audit_vendor, char_blacklist, char_chocobos, char_effects, char_equip, char_equip_saved, char_exp, char_fishing_contest_history, char_flags, char_history, char_inventory, char_jobs, char_job_points, char_look, char_merit, char_monstrosity, char_pet, char_points, char_profile, char_skills, char_spells, char_stats, char_storage, char_style, char_unlocks, char_vars, chars, conquest_system, delivery_box, fishing_contest, fishing_contest_entries, help_desk, ip_exceptions, linkshells, server_variables, unity_system.

Introduction

Installation

Database Setup

Protected tables

​Protected tables

Protected tables