186 lines
9.4 KiB
MySQL
186 lines
9.4 KiB
MySQL
|
-- 001_initial_schema.sql
|
|||
|
|
-- Xyvera game database — initial schema
|
|||
|
|
-- Applied automatically to the xyvera PostgreSQL container on first boot.
|
|||
|
|
--
|
|||
|
|
-- This schema covers Steps 1–3 of the implementation sequence:
|
|||
|
|
-- - player profiles and accounts
|
|||
|
|
-- - resource inventory and currency ledger
|
|||
|
|
-- - character roster
|
|||
|
|
-- - gacha pull history and pity counters
|
|||
|
|
--
|
|||
|
|
-- Nakama has its own tables in CockroachDB. This Postgres DB stores
|
|||
|
|
-- game-specific state that Nakama does not model natively:
|
|||
|
|
-- - economy ledger (full audit trail)
|
|||
|
|
-- - character progression state
|
|||
|
|
-- - village assignment history
|
|||
|
|
-- - content configuration cache (loaded from YAML by the content importer)
|
|||
|
|
|
|||
|
|
BEGIN;
|
|||
|
|
|
|||
|
|
-- ── Extensions ──────────────────────────────────────────────────────────────
|
|||
|
|
|
|||
|
|
CREATE EXTENSION IF NOT EXISTS "pgcrypto"; -- gen_random_uuid()
|
|||
|
|
|
|||
|
|
-- ── Players ──────────────────────────────────────────────────────────────────
|
|||
|
|
-- Mirrors Nakama user IDs. All economy and progression data links here.
|
|||
|
|
|
|||
|
|
CREATE TABLE players (
|
|||
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|||
|
|
nakama_user_id TEXT NOT NULL UNIQUE,
|
|||
|
|
display_name TEXT NOT NULL DEFAULT 'Traveller',
|
|||
|
|
region TEXT NOT NULL DEFAULT 'heartwood_vale',
|
|||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
CREATE INDEX idx_players_nakama_user_id ON players(nakama_user_id);
|
|||
|
|
|
|||
|
|
-- ── Resource Ledger ───────────────────────────────────────────────────────────
|
|||
|
|
-- Every grant and spend recorded. Never update; only insert.
|
|||
|
|
-- This makes the economy fully auditable (Economy Rule from spec).
|
|||
|
|
|
|||
|
|
CREATE TABLE resource_ledger (
|
|||
|
|
id BIGSERIAL PRIMARY KEY,
|
|||
|
|
player_id UUID NOT NULL REFERENCES players(id) ON DELETE CASCADE,
|
|||
|
|
resource_id TEXT NOT NULL, -- matches resource schema id
|
|||
|
|
delta INTEGER NOT NULL, -- positive = grant, negative = spend
|
|||
|
|
balance_after INTEGER NOT NULL, -- denormalized for easy dashboards
|
|||
|
|
source TEXT NOT NULL, -- e.g. 'lumber_camp_collect', 'gacha_pull'
|
|||
|
|
source_ref TEXT, -- optional FK to another table (e.g. pull_id)
|
|||
|
|
notes JSONB,
|
|||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
CREATE INDEX idx_resource_ledger_player ON resource_ledger(player_id, resource_id);
|
|||
|
|
CREATE INDEX idx_resource_ledger_source ON resource_ledger(source);
|
|||
|
|
CREATE INDEX idx_resource_ledger_created ON resource_ledger(created_at);
|
|||
|
|
|
|||
|
|
-- ── Character Roster ─────────────────────────────────────────────────────────
|
|||
|
|
|
|||
|
|
CREATE TABLE roster (
|
|||
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|||
|
|
player_id UUID NOT NULL REFERENCES players(id) ON DELETE CASCADE,
|
|||
|
|
character_id TEXT NOT NULL, -- matches character schema id
|
|||
|
|
presentation TEXT NOT NULL DEFAULT 'female' CHECK (presentation IN ('female', 'male')),
|
|||
|
|
level INTEGER NOT NULL DEFAULT 1 CHECK (level >= 1 AND level <= 100),
|
|||
|
|
ascension_tier INTEGER NOT NULL DEFAULT 0 CHECK (ascension_tier >= 0 AND ascension_tier <= 6),
|
|||
|
|
bond_level INTEGER NOT NULL DEFAULT 0,
|
|||
|
|
assignment_mastery JSONB NOT NULL DEFAULT '{}', -- {job_id: hours_accumulated}
|
|||
|
|
skills JSONB NOT NULL DEFAULT '{}', -- {skill_id: current_level}
|
|||
|
|
obtained_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|||
|
|
obtained_source TEXT NOT NULL, -- 'gacha_standard', 'gacha_event', 'starter_seed'
|
|||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
CREATE UNIQUE INDEX idx_roster_player_char ON roster(player_id, character_id);
|
|||
|
|
CREATE INDEX idx_roster_player ON roster(player_id);
|
|||
|
|
|
|||
|
|
-- ── Village Assignment State ──────────────────────────────────────────────────
|
|||
|
|
|
|||
|
|
CREATE TABLE village_assignments (
|
|||
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|||
|
|
player_id UUID NOT NULL REFERENCES players(id) ON DELETE CASCADE,
|
|||
|
|
roster_id UUID NOT NULL REFERENCES roster(id) ON DELETE CASCADE,
|
|||
|
|
job_id TEXT NOT NULL, -- matches job schema id
|
|||
|
|
assigned_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|||
|
|
last_collected_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|||
|
|
|
|||
|
|
UNIQUE (player_id, roster_id) -- one assignment per character
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
CREATE INDEX idx_village_assignments_player ON village_assignments(player_id);
|
|||
|
|
|
|||
|
|
-- ── Manual Work State ────────────────────────────────────────────────────────
|
|||
|
|
-- Tracks daily manual work action counts for diminishing-returns enforcement.
|
|||
|
|
|
|||
|
|
CREATE TABLE manual_work_state (
|
|||
|
|
player_id UUID PRIMARY KEY REFERENCES players(id) ON DELETE CASCADE,
|
|||
|
|
actions_today INTEGER NOT NULL DEFAULT 0,
|
|||
|
|
actions_reset_at TIMESTAMPTZ NOT NULL DEFAULT now(), -- midnight UTC of current day
|
|||
|
|
total_actions_lifetime BIGINT NOT NULL DEFAULT 0,
|
|||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
-- ── Gacha Pull History ────────────────────────────────────────────────────────
|
|||
|
|
|
|||
|
|
CREATE TABLE pulls (
|
|||
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|||
|
|
player_id UUID NOT NULL REFERENCES players(id) ON DELETE CASCADE,
|
|||
|
|
banner_id TEXT NOT NULL,
|
|||
|
|
character_id TEXT NOT NULL,
|
|||
|
|
rarity TEXT NOT NULL CHECK (rarity IN ('R', 'SR', 'SSR', 'UR')),
|
|||
|
|
pity_count_at_pull INTEGER NOT NULL,
|
|||
|
|
was_soft_pity BOOLEAN NOT NULL DEFAULT false,
|
|||
|
|
was_hard_pity BOOLEAN NOT NULL DEFAULT false,
|
|||
|
|
was_featured BOOLEAN NOT NULL DEFAULT false,
|
|||
|
|
pulled_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
CREATE INDEX idx_pulls_player ON pulls(player_id, pulled_at DESC);
|
|||
|
|
CREATE INDEX idx_pulls_banner ON pulls(banner_id, pulled_at DESC);
|
|||
|
|
|
|||
|
|
-- ── Pity Counters ─────────────────────────────────────────────────────────────
|
|||
|
|
-- One row per (player, banner). Updated after every pull.
|
|||
|
|
|
|||
|
|
CREATE TABLE pity_counters (
|
|||
|
|
player_id UUID NOT NULL REFERENCES players(id) ON DELETE CASCADE,
|
|||
|
|
banner_id TEXT NOT NULL,
|
|||
|
|
current_pity INTEGER NOT NULL DEFAULT 0,
|
|||
|
|
featured_guarantee_due BOOLEAN NOT NULL DEFAULT false, -- lost the 50/50; next SSR guaranteed
|
|||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|||
|
|
|
|||
|
|
PRIMARY KEY (player_id, banner_id)
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
-- ── Content Cache Tables ──────────────────────────────────────────────────────
|
|||
|
|
-- These are populated by tools/import_content.py from the YAML content files.
|
|||
|
|
-- They are NOT the source of truth (YAML files are); they are the runtime cache.
|
|||
|
|
|
|||
|
|
CREATE TABLE content_characters (
|
|||
|
|
id TEXT PRIMARY KEY,
|
|||
|
|
data JSONB NOT NULL,
|
|||
|
|
schema_version TEXT NOT NULL DEFAULT 'v1',
|
|||
|
|
imported_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
CREATE TABLE content_jobs (
|
|||
|
|
id TEXT PRIMARY KEY,
|
|||
|
|
data JSONB NOT NULL,
|
|||
|
|
schema_version TEXT NOT NULL DEFAULT 'v1',
|
|||
|
|
imported_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
CREATE TABLE content_resources (
|
|||
|
|
id TEXT PRIMARY KEY,
|
|||
|
|
data JSONB NOT NULL,
|
|||
|
|
schema_version TEXT NOT NULL DEFAULT 'v1',
|
|||
|
|
imported_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
CREATE TABLE content_banners (
|
|||
|
|
id TEXT PRIMARY KEY,
|
|||
|
|
data JSONB NOT NULL,
|
|||
|
|
active BOOLEAN NOT NULL DEFAULT false,
|
|||
|
|
schema_version TEXT NOT NULL DEFAULT 'v1',
|
|||
|
|
imported_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
CREATE TABLE content_events (
|
|||
|
|
id TEXT PRIMARY KEY,
|
|||
|
|
data JSONB NOT NULL,
|
|||
|
|
active BOOLEAN NOT NULL DEFAULT false,
|
|||
|
|
schema_version TEXT NOT NULL DEFAULT 'v1',
|
|||
|
|
imported_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
-- ── Schema Version Tracking ───────────────────────────────────────────────────
|
|||
|
|
|
|||
|
|
CREATE TABLE schema_migrations (
|
|||
|
|
version TEXT PRIMARY KEY,
|
|||
|
|
applied_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
INSERT INTO schema_migrations(version) VALUES ('001_initial_schema');
|
|||
|
|
|
|||
|
|
COMMIT;
|