Database & SQL

Finals Management uses oxmysql for all relational persistence.

1. Automated Bootstrap (Config.SqlBootstrap)

When Config.SqlBootstrap.enabled = true is active in config.lua, table creation scripts run automatically upon server startup:

Lua

Config.SqlBootstrap = {
    enabled = true,
    waitMs = 30000,
}

Manual SQL execution is only required if your database user lacks CREATE TABLE privileges.

2. Database Tables & Schemas

A. Duty Sessions (finalsdutysessions.sql)

Records historical duty stints for payroll and auditing:

SQL

CREATE TABLE IF NOT EXISTS `finals_duty_sessions` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `citizenid` VARCHAR(64) NOT NULL,
  `job` VARCHAR(50) NOT NULL,
  `grade` INT NOT NULL DEFAULT 0,
  `clock_in` DATETIME NOT NULL,
  `clock_out` DATETIME DEFAULT NULL,
  `total_seconds` INT DEFAULT 0,
  INDEX `idx_citizenid_job` (`citizenid`, `job`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

B. Recruitment Portals (finals_applications.sql)

Stores questionnaire templates and civilian submissions:

SQL

CREATE TABLE IF NOT EXISTS `finals_applications_questions` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `job` VARCHAR(50) NOT NULL UNIQUE,
  `questions_json` LONGTEXT NOT NULL,
  `interview_location` VARCHAR(255) DEFAULT '',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `finals_applications_filings` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `job` VARCHAR(50) NOT NULL,
  `citizenid` VARCHAR(64) NOT NULL,
  `applicant_name` VARCHAR(128) NOT NULL,
  `answers_json` LONGTEXT NOT NULL,
  `status` VARCHAR(32) NOT NULL DEFAULT 'pending',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_job_status` (`job`, `status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

C. Digital Employment Offers (finalshirepending.sql)

Maintains pending digital employment contracts and signature data:

SQL

CREATE TABLE IF NOT EXISTS `finals_hire_pending` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `job` VARCHAR(50) NOT NULL,
  `grade` INT NOT NULL DEFAULT 0,
  `citizenid` VARCHAR(64) NOT NULL,
  `boss_citizenid` VARCHAR(64) NOT NULL,
  `boss_signature` MEDIUMTEXT NOT NULL,
  `candidate_signature` MEDIUMTEXT DEFAULT NULL,
  `status` VARCHAR(32) NOT NULL DEFAULT 'pending',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `expires_at` TIMESTAMP NULL DEFAULT NULL,
  INDEX `idx_hire_pending` (`citizenid`, `status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

D. Uncollected Paychecks (finalspendingpaychecks.sql)

Stores payroll balances for collection clerk NPC or tablet redemption:

SQL

CREATE TABLE IF NOT EXISTS `finals_pending_paychecks` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `citizenid` VARCHAR(64) NOT NULL,
  `job` VARCHAR(50) NOT NULL,
  `amount` INT NOT NULL DEFAULT 0,
  `money_type` VARCHAR(20) NOT NULL DEFAULT 'bank',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_citizenid` (`citizenid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

E. Built-in Society Ledger (finalsownsociety.sql)

Used when Config.SocietyBridge.mode = 'own_system':

SQL

CREATE TABLE IF NOT EXISTS `finals_own_society` (
  `job` VARCHAR(50) NOT NULL PRIMARY KEY,
  `balance` BIGINT NOT NULL DEFAULT 0,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;