MrixsCraft-server/migrations/001_init.sql

-- 001_init.sql — Initial schema for MrixsCraft server.
-- All tables from the Specification (§3).

-- Enable UUID extension (gen_random_uuid available in PG 13+).
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Users table.
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(32) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    uuid UUID UNIQUE NOT NULL DEFAULT gen_random_uuid(),
    role VARCHAR(16) DEFAULT 'user' CHECK (role IN ('user', 'admin')),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Player textures (skin / cape references into CAS).
CREATE TABLE player_textures (
    user_id INT PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    skin_hash VARCHAR(40),
    cape_hash VARCHAR(40),
    is_slim BOOLEAN DEFAULT FALSE
);

-- Yggdrasil sessions for launcher and game authentication.
CREATE TABLE yggdrasil_sessions (
    client_token UUID NOT NULL DEFAULT gen_random_uuid(),
    access_token UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    expires_at TIMESTAMP NOT NULL
);

-- Modpacks (game servers).
CREATE TABLE modpacks (
    id SERIAL PRIMARY KEY,
    slug VARCHAR(32) UNIQUE NOT NULL,
    name VARCHAR(64) NOT NULL,
    minecraft_version VARCHAR(16) NOT NULL,
    java_version INT NOT NULL CHECK (java_version IN (8, 11, 17, 21)),
    server_ip VARCHAR(255) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Global unique files (CAS registry).
CREATE TABLE global_files (
    sha1 VARCHAR(40) PRIMARY KEY CHECK (sha1 ~ '^[0-9a-f]{40}$'),
    size_bytes BIGINT NOT NULL CHECK (size_bytes > 0),
    file_name VARCHAR(255) NOT NULL,
    mime_type VARCHAR(127) DEFAULT 'application/octet-stream',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Launcher releases (populated via CI/CD).
CREATE TABLE launcher_releases (
    id SERIAL PRIMARY KEY,
    version VARCHAR(32) NOT NULL,
    os VARCHAR(16) NOT NULL CHECK (os IN ('windows', 'linux', 'darwin', 'universal')),
    arch VARCHAR(16) NOT NULL CHECK (arch IN ('amd64', 'arm64', 'universal')),
    sha256 VARCHAR(64) NOT NULL CHECK (sha256 ~ '^[0-9a-f]{64}$'),
    file_path VARCHAR(255) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    is_mandatory BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (version, os, arch)
);

-- ── Indexes ──────────────────────────────────────────────────

-- Session look-ups (every authenticated request).
CREATE INDEX idx_sessions_access_token ON yggdrasil_sessions (access_token);
CREATE INDEX idx_sessions_user_id ON yggdrasil_sessions (user_id);
CREATE INDEX idx_sessions_expires_at ON yggdrasil_sessions (expires_at);

-- User look-ups by login fields.
CREATE INDEX idx_users_uuid ON users (uuid);
CREATE INDEX idx_users_username ON users (username);
CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_users_role ON users (role);

-- Game client profile look-up.
CREATE INDEX idx_player_textures_skin ON player_textures (skin_hash) WHERE skin_hash IS NOT NULL;
CREATE INDEX idx_player_textures_cape ON player_textures (cape_hash) WHERE cape_hash IS NOT NULL;

-- Launcher latest version look-up.
CREATE INDEX idx_launcher_releases_os_arch ON launcher_releases (os, arch) WHERE is_active = TRUE;

-- CAS file name search.
CREATE INDEX idx_global_files_name ON global_files (file_name);