Database Schema

Database Schema

Complete reference for all database tables, stored procedures, and views.

---

Tables Overview

Important

The schema has been updated to 5 tables in v0.2.9 (previously 4). The new player_fractures table was added for the fall damage/fracture system.

Tables (5 Total)

1. player_wounds - Active wounds and healed scars
2. medical_treatments - Current active treatments
3. player_infections - Active infections with progression
4. player_fractures - Bone fractures/breaks (New in v0.2.9)
5. medical_history - Complete audit trail

Views

• active_medical_status - Joined view for NUI integration (New in v0.2.9)

Stored Procedures

• GetCompleteMedicalProfile - Get all medical data in one call
• CleanupExpiredMedicalData - Remove old data (>30 days)

---

Table Structures

1. player_wounds

Stores all active wounds and healed scars.

CREATE TABLE `player_wounds` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `citizenid` VARCHAR(50) NOT NULL,
    `body_part` VARCHAR(20) NOT NULL,
    `pain_level` DECIMAL(3,1) DEFAULT 0.0,
    `bleeding_level` DECIMAL(3,1) DEFAULT 0.0,
    `current_health` DECIMAL(5,2) DEFAULT 100.00,
    `max_health` DECIMAL(5,2) DEFAULT 100.00,
    `health_percentage` DECIMAL(5,2) DEFAULT 100.00,
    `weapon_data` VARCHAR(100),
    `weapon_hash` INT,
    `weapon_name` VARCHAR(100),
    `damage_type` VARCHAR(50),
    `wound_description` TEXT,
    `is_scar` BOOLEAN DEFAULT FALSE,
    `scar_time` TIMESTAMP NULL,
    `metadata` JSON,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `unique_wound` (`citizenid`, `body_part`),
    INDEX `idx_citizenid` (`citizenid`),
    INDEX `idx_scar` (`is_scar`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Key Fields:

• unique_wound constraint: Only ONE wound per body part per player
• is_scar: When TRUE, wound is healed (pain=0, bleeding=0)
• metadata: JSON field storing ballistic data, shooter info, etc.

Example Metadata:

{
  "weaponHash": -1234567,
  "weaponType": ".45 Colt",
  "bulletStatus": "stuck",
  "requiresSurgery": true,
  "description": "Moderate .45 Colt wound to head...",
  "shooterDistance": 15.2,
  "ballisticType": "handgun"
}

---

2. medical_treatments

Stores all active treatments (bandages, medicines, tourniquets, injections).

CREATE TABLE `medical_treatments` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `citizenid` VARCHAR(50) NOT NULL,
    `body_part` VARCHAR(20),              -- NULL for system-wide treatments (medicines)
    `treatment_type` ENUM('bandage', 'tourniquet', 'medicine', 'injection') NOT NULL,
    `item_type` VARCHAR(50) NOT NULL,     -- Specific item (cotton, morphine, etc.)
    `applied_by` VARCHAR(50),              -- Citizenid of medic (or 'self')
    `applied_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `expiration_time` TIMESTAMP NULL,
    `duration` INT DEFAULT 0,              -- Duration in seconds
    `original_pain_level` DECIMAL(3,1) DEFAULT 0.0,
    `original_bleeding_level` DECIMAL(3,1) DEFAULT 0.0,
    `pain_reduction` DECIMAL(3,1) DEFAULT 0.0,
    `bleeding_reduction` DECIMAL(3,1) DEFAULT 0.0,
    `is_active` BOOLEAN DEFAULT TRUE,
    `metadata` JSON,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_citizen_active` (`citizenid`, `is_active`),
    INDEX `idx_expiration` (`expiration_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Key Fields:

• body_part can be NULL for medicines (they affect all body parts)
• is_active: Set to FALSE when treatment expires or is removed
• expiration_time: When the treatment effects end

Example Metadata:

{
  "label": "Cotton Bandage",
  "description": "...",
  "originalEffectiveness": 70,
  "bleedingReduced": 4,
  "immediateHeal": 12
}

---

3. player_infections

Stores active infections with stage progression.

CREATE TABLE `player_infections` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `citizenid` VARCHAR(50) NOT NULL,
    `body_part` VARCHAR(20) NOT NULL,
    `infection_type` VARCHAR(50) DEFAULT 'bandage_infection',
    `category` VARCHAR(50) DEFAULT 'dirtyBandage',
    `stage` INT DEFAULT 1,                 -- 1-4 progression
    `start_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `last_progress_check` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `cure_progress` DECIMAL(5,2) DEFAULT 0.00,  -- 0-100%
    `immunity_end_time` TIMESTAMP NULL,
    `is_active` BOOLEAN DEFAULT TRUE,
    `cured_at` TIMESTAMP NULL,
    `cured_by` VARCHAR(50),
    `metadata` JSON,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `unique_infection` (`citizenid`, `body_part`),
    INDEX `idx_citizen_active` (`citizenid`, `is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Key Fields:

• stage: 1 (25%), 2 (50%), 3 (75%), 4 (90%+)
• cure_progress: Increases with each treatment, cured at 100%
• immunity_end_time: Temporary immunity after cure

Example Metadata:

{
  "causeDescription": "Dirty bandage infection from cotton bandage",
  "symptoms": [
    "Redness and swelling",
    "Mild fever",
    "Pain when moving"
  ],
  "treatments": [
    {"item": "antibiotics", "timestamp": 1234567890, "progress": 40}
  ]
}

---

4. player_fractures (New in v0.2.9)

Stores bone fractures and breaks from falls/impacts.

CREATE TABLE `player_fractures` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `citizenid` VARCHAR(50) NOT NULL,
    `body_part` VARCHAR(20) NOT NULL,
    `fracture_type` ENUM('fracture', 'bone_break') NOT NULL,
    `severity` INT(2) NOT NULL DEFAULT 5,  -- 1-10 scale
    `pain_level` DECIMAL(3,1) NOT NULL DEFAULT 0.0,
    `mobility_impact` DECIMAL(3,2) NOT NULL DEFAULT 0.0,  -- 0.0-1.0 (movement penalty)
    `healing_progress` DECIMAL(5,2) NOT NULL DEFAULT 0.00,  -- 0-100%
    `requires_surgery` BOOLEAN DEFAULT FALSE,
    `fracture_description` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `healed_at` TIMESTAMP NULL,
    UNIQUE KEY `unique_fracture` (`citizenid`, `body_part`),
    INDEX `idx_citizenid` (`citizenid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Key Fields:

• fracture_type: "fracture" (minor) or "bone_break" (major)
• mobility_impact: 0.2 = 20% speed reduction
• requires_surgery: TRUE for bone_break type

---

5. medical_history

Complete audit trail of all medical events.

CREATE TABLE `medical_history` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `citizenid` VARCHAR(50) NOT NULL,
    `event_type` ENUM('wound_created', 'wound_scarred', 'wound_healed',
                      'treatment_applied', 'treatment_removed', 'infection_started',
                      'infection_cured', 'medical_inspection', 'admin_clear_wounds',
                      'fracture_created', 'fracture_healed') NOT NULL,
    `body_part` VARCHAR(20),
    `details` JSON,
    `performed_by` VARCHAR(50),           -- Citizenid of medic/admin
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_citizenid_date` (`citizenid`, `created_at`),
    INDEX `idx_event_type` (`event_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Key Fields:

• details: JSON field storing event-specific data
• performed_by: NULL for automatic events, citizenid for medic/admin actions
• Automatically cleaned up after 30 days (via stored procedure)

Example Entry:

INSERT INTO medical_history VALUES (
    NULL,
    'ABC12345',
    'treatment_applied',
    'HEAD',
    '{"treatmentType":"bandage","itemType":"cotton","appliedBy":"XYZ67890"}',
    'XYZ67890',
    NOW()
);

---

Stored Procedures

GetCompleteMedicalProfile

Retrieves all medical data for a player in one call (optimized).

CREATE PROCEDURE GetCompleteMedicalProfile(IN p_citizenid VARCHAR(50))
BEGIN
    -- Get all wounds (including scars)
    SELECT * FROM player_wounds WHERE citizenid = p_citizenid;

    -- Get active treatments
    SELECT * FROM medical_treatments
    WHERE citizenid = p_citizenid AND is_active = TRUE;

    -- Get active infections
    SELECT * FROM player_infections
    WHERE citizenid = p_citizenid AND is_active = TRUE;

    -- Get fractures
    SELECT * FROM player_fractures
    WHERE citizenid = p_citizenid AND healed_at IS NULL;

    -- Get recent medical history (last 30 days)
    SELECT * FROM medical_history
    WHERE citizenid = p_citizenid
      AND created_at > DATE_SUB(NOW(), INTERVAL 30 DAY)
    ORDER BY created_at DESC
    LIMIT 50;
END

Usage in Lua:

local profile = MySQL.query.await('CALL GetCompleteMedicalProfile(?)', {citizenid})

Returns 5 result sets:

1. All wounds (active + scars)
2. Active treatments
3. Active infections
4. Active fractures
5. Recent history (50 latest events)

---

CleanupExpiredMedicalData

Automatic cleanup of old data (run daily/hourly).

CREATE PROCEDURE CleanupExpiredMedicalData()
BEGIN
    -- Mark old treatments as inactive
    UPDATE medical_treatments
    SET is_active = FALSE
    WHERE expiration_time < NOW() AND is_active = TRUE;

    -- Delete very old inactive treatments (>7 days)
    DELETE FROM medical_treatments
    WHERE is_active = FALSE
      AND applied_at < DATE_SUB(NOW(), INTERVAL 7 DAY);

    -- Delete old medical history (>30 days)
    DELETE FROM medical_history
    WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);

    -- Mark cured infections as inactive
    UPDATE player_infections
    SET is_active = FALSE
    WHERE cure_progress >= 100.0 AND is_active = TRUE;

    -- Delete old inactive infections (>7 days)
    DELETE FROM player_infections
    WHERE is_active = FALSE
      AND cured_at < DATE_SUB(NOW(), INTERVAL 7 DAY);
END

Usage in Lua:

-- Run manually or via cron job
MySQL.execute('CALL CleanupExpiredMedicalData()')

Tip

Set up a cron job to run CleanupExpiredMedicalData() daily at 3 AM to keep your database optimized.

Linux Cron Example:

# Daily at 3 AM
0 3 * * * mysql -u user -ppassword database -e "CALL CleanupExpiredMedicalData();"

---

Database Views

active_medical_status (New in v0.2.9)

View for easy NUI integration - shows complete medical status.

CREATE VIEW active_medical_status AS
SELECT
    w.citizenid,
    w.body_part,
    w.pain_level,
    w.bleeding_level,
    w.current_health,
    w.health_percentage,
    w.is_scar,
    t.treatment_type,
    t.item_type AS treatment_item,
    t.expiration_time AS treatment_expires,
    i.stage AS infection_stage,
    i.cure_progress,
    f.fracture_type,
    f.severity AS fracture_severity
FROM player_wounds w
LEFT JOIN medical_treatments t ON w.citizenid = t.citizenid
    AND w.body_part = t.body_part
    AND t.is_active = TRUE
LEFT JOIN player_infections i ON w.citizenid = i.citizenid
    AND w.body_part = i.body_part
    AND i.is_active = TRUE
LEFT JOIN player_fractures f ON w.citizenid = f.citizenid
    AND w.body_part = f.body_part
    AND f.healed_at IS NULL;

Usage:

SELECT * FROM active_medical_status WHERE citizenid = 'ABC12345';

Returns: Complete medical status per body part with joined data.

---

Query Examples

Get All Active Wounds for Player

SELECT * FROM player_wounds
WHERE citizenid = 'ABC12345' AND is_scar = FALSE;

Get Treatment History for Body Part

SELECT * FROM medical_treatments
WHERE citizenid = 'ABC12345'
  AND body_part = 'HEAD'
ORDER BY applied_at DESC;

Check for Active Infections

SELECT body_part, stage, cure_progress
FROM player_infections
WHERE citizenid = 'ABC12345' AND is_active = TRUE;

Get Recent Medical Events

SELECT event_type, body_part, created_at, performed_by
FROM medical_history
WHERE citizenid = 'ABC12345'
ORDER BY created_at DESC
LIMIT 10;

Find Players with Severe Bleeding

SELECT DISTINCT w.citizenid, SUM(w.bleeding_level) AS total_bleeding
FROM player_wounds w
WHERE w.is_scar = FALSE
GROUP BY w.citizenid
HAVING total_bleeding > 10
ORDER BY total_bleeding DESC;

---

Performance Optimization

Indexes

All tables have optimized indexes:

• idx_citizenid - Fast player lookups
• idx_citizen_active - Fast active data queries
• idx_expiration - Fast expiration checks
• unique_wound / unique_infection / unique_fracture - Prevent duplicates

Query Best Practices

-- ❌ BAD (4 separate queries)
local wounds = MySQL.query.await('SELECT * FROM player_wounds WHERE citizenid = ?', {cid})
local treatments = MySQL.query.await('SELECT * FROM medical_treatments WHERE citizenid = ?', {cid})
local infections = MySQL.query.await('SELECT * FROM player_infections WHERE citizenid = ?', {cid})
local fractures = MySQL.query.await('SELECT * FROM player_fractures WHERE citizenid = ?', {cid})

-- ✅ GOOD (1 stored procedure call)
local profile = MySQL.query.await('CALL GetCompleteMedicalProfile(?)', {cid})

Regular Maintenance

Run cleanup procedure regularly:

# Weekly
0 2 * * 0 mysql -u user -ppassword database -e "CALL CleanupExpiredMedicalData();"

---

← Server Systems | Next: Configuration →