DataBase.sql

-- Copyright (c) 2026 Ilia
-- SPDX-License-Identifier: LGPL-3.0-or-later

-- ============================================================================
--  bansystem - schema
--  Import once. server/database.lua also creates these on first start.
-- ============================================================================

-- ----------------------------------------------------------------------------
--  Identity store: every primary identifier we have ever seen, with its
--  hardware tokens. Which identifier that is comes from Config.Identity.primary
--  (license by default, but it can be steam, discord, fivem, ...).
--  Lets us resolve offline players by name and trace alt accounts.
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `bansystem_players` (
    `identifier`  VARCHAR(96)  NOT NULL,
    `name`        VARCHAR(96)  DEFAULT NULL,
    `tokens`      LONGTEXT     DEFAULT NULL COMMENT 'JSON array of hardware tokens',
    `identifiers` LONGTEXT     DEFAULT NULL COMMENT 'JSON map of extra identifiers (display only)',
    `first_seen`  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `last_seen`   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`identifier`),
    KEY `idx_name` (`name`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- ----------------------------------------------------------------------------
--  Bans. `type` = 'full' (blocked from the server) or 'function' (can play,
--  specific capabilities locked). `expires_at` NULL means permanent.
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `bansystem_bans` (
    `id`            INT UNSIGNED             NOT NULL AUTO_INCREMENT,
    `code`          VARCHAR(16)              NOT NULL COMMENT 'Public code shown to the player',
    `type`          ENUM ('full','function') NOT NULL DEFAULT 'full',
    `identifier`    VARCHAR(96)              NOT NULL,
    `target_name`   VARCHAR(96)              DEFAULT NULL,
    `functions`     LONGTEXT                 DEFAULT NULL COMMENT 'JSON array, function bans only',
    `reason_key`    VARCHAR(48)              DEFAULT NULL COMMENT 'Config.Reasons key, NULL for custom',
    `reason`        TEXT                     NOT NULL,
    `note`          TEXT                     DEFAULT NULL COMMENT 'Internal staff note',
    `evidence`      TEXT                     DEFAULT NULL,
    `offence_count` INT UNSIGNED             NOT NULL DEFAULT 1 COMMENT 'Nth offence for this reason, drives escalation',
    `admin_name`    VARCHAR(96)              NOT NULL DEFAULT 'console',
    `admin_id`      VARCHAR(96)              DEFAULT NULL,
    `created_at`    DATETIME                 NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `expires_at`    DATETIME                 DEFAULT NULL COMMENT 'NULL = permanent',
    `active`        TINYINT(1)               NOT NULL DEFAULT 1,
    `revoked_at`    DATETIME                 DEFAULT NULL,
    `revoked_by`    VARCHAR(96)              DEFAULT NULL,
    `revoke_reason` TEXT                     DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uniq_code` (`code`),
    KEY `idx_identifier_active` (`identifier`, `active`),
    KEY `idx_active_expires` (`active`, `expires_at`),
    KEY `idx_type_active` (`type`, `active`),
    KEY `idx_reason_key` (`reason_key`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- ----------------------------------------------------------------------------
--  Snapshot of the hardware tokens the target held when the ban was issued.
--  This is what catches a new account on the same machine.
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `bansystem_ban_tokens` (
    `ban_id` INT UNSIGNED NOT NULL,
    `token`  VARCHAR(191) NOT NULL,
    PRIMARY KEY (`ban_id`, `token`),
    KEY `idx_token` (`token`),
    CONSTRAINT `fk_ban_tokens_ban`
        FOREIGN KEY (`ban_id`) REFERENCES `bansystem_bans` (`id`)
            ON DELETE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- ----------------------------------------------------------------------------
--  Audit trail. Never deleted, survives ban removal.
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `bansystem_logs` (
    `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `action`     VARCHAR(32)  NOT NULL COMMENT 'ban | fban | unban | expire | evasion | tamper | connect_blocked | import',
    `ban_id`     INT UNSIGNED DEFAULT NULL,
    `identifier` VARCHAR(96)  DEFAULT NULL,
    `actor`      VARCHAR(96)  DEFAULT NULL,
    `detail`     TEXT         DEFAULT NULL,
    `created_at` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_identifier` (`identifier`),
    KEY `idx_action` (`action`),
    KEY `idx_created` (`created_at`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;