Skip to main content

Documentation Index

Fetch the complete documentation index at: https://mintlify.com/TaylorZaneKirk/MMO-Project/llms.txt

Use this file to discover all available pages before exploring further.

The prototype database uses PostgreSQL with a schema intentionally scoped to the current feature slice. Every table that exists today supports one of the active gameplay systems: authentication, character state, inventory, equipment, skills, dropped world items, and runtime delta delivery. Areas that are not yet implemented — NPCs, mobs, combat, quests, banking, guilds, and houses — are explicitly deferred and have no tables in the schema.

​
Accounts and sessions

accounts is the root identity record. Each row holds a unique account_name, a bcrypt password_hash, and an is_active flag that gates login without requiring a hard delete. sessions links back to accounts via account_id and tracks the full lifecycle of an authenticated connection: when it was created, when it expires, the last time a message was received (last_seen_at), an explicit revocation timestamp (revoked_at set on logout), and a disconnect timestamp (disconnected_at) reserved for the reconnect grace window. 
CREATE TABLE accounts (
    account_id UUID PRIMARY KEY,
    account_name TEXT NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE sessions (
    session_id UUID PRIMARY KEY,
    account_id UUID NOT NULL REFERENCES accounts(account_id),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    expires_at TIMESTAMPTZ NOT NULL,
    last_seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    revoked_at TIMESTAMPTZ NULL,
    disconnected_at TIMESTAMPTZ NULL
);

​
Characters

characters stores each character’s persistent world position (map_id, tile_x, tile_y) alongside the owning account_id. The last_saved_at column is updated whenever the server flushes a position change. character_stats is a one-to-one extension keyed on character_id; it holds the three resource pools — health, concentration, and special — each with a current and maximum value. 
CREATE TABLE characters (
    id UUID PRIMARY KEY,
    account_id UUID NOT NULL REFERENCES accounts(account_id),
    name TEXT NOT NULL UNIQUE,
    map_id TEXT NOT NULL,
    tile_x INTEGER NOT NULL,
    tile_y INTEGER NOT NULL,
    last_saved_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE character_stats (
    character_id UUID PRIMARY KEY REFERENCES characters(id),
    health_current INTEGER NOT NULL,
    health_max INTEGER NOT NULL,
    concentration_current INTEGER NOT NULL,
    concentration_max INTEGER NOT NULL,
    special_current INTEGER NOT NULL,
    special_max INTEGER NOT NULL,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

​
Items and inventory

item_definitions is the catalogue of every item in the game. Each row has a stable item_id text key, a human-readable item_name, an icon_texture_path for the UI, an optional equipment_slot_id foreign key (nullable — non-equippable items leave this NULL), and a required_strength threshold enforced at equip time. character_inventory uses a composite primary key of (character_id, slot_index) to model a fixed-slot bag, with stack_count tracking quantity for stackable items. 
CREATE TABLE item_definitions (
    item_id TEXT PRIMARY KEY,
    item_name TEXT NOT NULL,
    icon_texture_path TEXT NOT NULL,
    equipment_slot_id TEXT NULL,
    required_strength INTEGER NOT NULL DEFAULT 1,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE character_inventory (
    character_id UUID NOT NULL REFERENCES characters(id),
    slot_index INTEGER NOT NULL,
    item_id TEXT NOT NULL REFERENCES item_definitions(item_id),
    stack_count INTEGER NOT NULL DEFAULT 1,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (character_id, slot_index)
);

​
Equipment

equipment_slot_definitions is a static reference table seeded at migration time. Each slot has a short slot_id key, a display_name for the UI, and a sort_order that controls the render order of the paper-doll. character_equipment models what a character currently has equipped via a composite primary key of (character_id, slot_id) — one row per slot per character, replaced in place on equip changes. 
CREATE TABLE equipment_slot_definitions (
    slot_id TEXT PRIMARY KEY,
    display_name TEXT NOT NULL,
    sort_order INTEGER NOT NULL UNIQUE
);

INSERT INTO equipment_slot_definitions (slot_id, display_name, sort_order) VALUES
    ('head',       'Head',        10),
    ('cape',       'Cape',        20),
    ('body',       'Body',        30),
    ('legs',       'Legs',        40),
    ('boots',      'Boots',       50),
    ('right_hand', 'Right Hand',  60),
    ('left_hand',  'Left Hand',   70),
    ('gloves',     'Gloves',      80),
    ('ring',       'Ring',        90);

CREATE TABLE character_equipment (
    character_id UUID NOT NULL REFERENCES characters(id),
    slot_id TEXT NOT NULL REFERENCES equipment_slot_definitions(slot_id),
    item_id TEXT NOT NULL REFERENCES item_definitions(item_id),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (character_id, slot_id)
);

​
Skills

Migration 008_skill_equipment_schema.sql adds the full skill layer. skill_definitions is a seeded reference table with 15 skills across three categories: combat (attack, strength, defence, vitality, ranged, magic, discipline), gathering (fishing, farming, mining, woodcutting), and production (cooking, blacksmithing, alchemy, crafting). character_skills tracks each character’s base_value per skill with a composite PK of (character_id, skill_id). item_skill_requirements records the minimum skill values a character must meet before equipping an item, expressed as (item_id, skill_id, required_value) pairs. item_skill_modifiers records the stat bonuses and penalties an equipped item applies to a character’s effective skill values — modifiers can be positive or negative integers. 
CREATE TABLE IF NOT EXISTS skill_definitions (
    skill_id TEXT PRIMARY KEY,
    display_name TEXT NOT NULL,
    category TEXT NOT NULL,
    sort_order INTEGER NOT NULL UNIQUE,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT skill_definitions_category_check
        CHECK (category IN ('combat', 'gathering', 'production'))
);

CREATE TABLE IF NOT EXISTS character_skills (
    character_id UUID NOT NULL REFERENCES characters(id),
    skill_id TEXT NOT NULL REFERENCES skill_definitions(skill_id),
    base_value INTEGER NOT NULL DEFAULT 1,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (character_id, skill_id),
    CONSTRAINT character_skills_base_value_check
        CHECK (base_value >= 1)
);

CREATE TABLE IF NOT EXISTS item_skill_requirements (
    item_id TEXT NOT NULL REFERENCES item_definitions(item_id),
    skill_id TEXT NOT NULL REFERENCES skill_definitions(skill_id),
    required_value INTEGER NOT NULL,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (item_id, skill_id),
    CONSTRAINT item_skill_requirements_required_value_check
        CHECK (required_value >= 1)
);

CREATE TABLE IF NOT EXISTS item_skill_modifiers (
    item_id TEXT NOT NULL REFERENCES item_definitions(item_id),
    skill_id TEXT NOT NULL REFERENCES skill_definitions(skill_id),
    modifier_value INTEGER NOT NULL,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (item_id, skill_id)
);

​
Ground items

ground_items represents items that have been dropped onto a map tile. Each row records the owner_character_id who dropped the item, its world position (map_id, tile_x, tile_y), the item_id and stack_count, and two visibility timestamps. dropped_at is the wall-clock time of the drop. visible_to_others_at is set to NOW() + DroppedItemPrivateVisibilitySeconds at drop time: before that moment only the owner can see the item, after it the item becomes publicly visible to all players on the tile. public_visibility_notified_at is populated by the notification trigger in migration 007 once the public reveal NOTIFY has been sent. Two indexes support the two primary read patterns: ground_items_map_visibility_idx on (map_id, visible_to_others_at) for server-side visibility sweeps, and ground_items_owner_idx on (owner_character_id) for owner-specific lookups. 
CREATE TABLE ground_items (
    ground_item_id UUID PRIMARY KEY,
    owner_character_id UUID NOT NULL REFERENCES characters(id),
    map_id TEXT NOT NULL REFERENCES maps(id),
    tile_x INTEGER NOT NULL,
    tile_y INTEGER NOT NULL,
    item_id TEXT NOT NULL REFERENCES item_definitions(item_id),
    stack_count INTEGER NOT NULL DEFAULT 1,
    dropped_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    visible_to_others_at TIMESTAMPTZ NOT NULL,
    public_visibility_notified_at TIMESTAMPTZ NULL
);

CREATE INDEX ground_items_map_visibility_idx
ON ground_items (map_id, visible_to_others_at);

CREATE INDEX ground_items_owner_idx
ON ground_items (owner_character_id);

​
Map presence

map_presence is a live snapshot of every character’s current position, updated on every movement tick. It uses character_id as its primary key (one row per character) and stores map_id, tile_x, and tile_y. The server uses this table to determine which characters are visible to each other on a given map and to restore a character’s in-progress position on reconnect without relying on the characters table flush. 
CREATE TABLE map_presence (
    character_id UUID PRIMARY KEY REFERENCES characters(id),
    map_id TEXT NOT NULL REFERENCES maps(id),
    tile_x INTEGER NOT NULL,
    tile_y INTEGER NOT NULL,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

​
Runtime revisions

runtime_revision_streams provides monotonically increasing revision counters used by the delta delivery system. Each row is keyed on (stream_type, stream_key) — for example, ('character', '<uuid>') or ('map', '<map_id>') — and holds a current_revision BIGINT that increments whenever the corresponding entity changes. Clients send their last-known revision, and the server uses the delta between stored and client revision to determine which updates to replay. 
CREATE TABLE IF NOT EXISTS runtime_revision_streams (
    stream_type TEXT NOT NULL,
    stream_key TEXT NOT NULL,
    current_revision BIGINT NOT NULL DEFAULT 0,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (stream_type, stream_key)
);
Deferred tables (NPCs, mobs, combat, quests, banking, guilds, houses) are not yet created. They will be added in later phases.

Build docs developers (and LLMs) love

Get started for freeTalk to us