Database

Complete database structure for hsJobQuest V2.

---

Overview

hsJobQuest V2 uses MySQL (via oxmysql) with the following tables:

Table	Purpose
hsjobquest_players	Core player data
hsjobquest_skills	Player skill levels
hsjobquest_factions	Faction reputation
hsjobquest_achievements	Unlocked achievements
hsjobquest_statistics	Detailed player stats
hsjobquest_heat	Illegal runs heat tracking
hsjobquest_mission_history	Completed missions log
hsjobquest_coop_groups	Co-op group data
hsjobquest_coop_members	Group membership
hsjobquest_economy	Dynamic economy state
hsjobquest_transactions	ATM transaction log

---

hsjobquest_players

Core player information and progression.

hsjobquest_players.sql

CREATE TABLE `hsjobquest_players` (
    `citizenid` VARCHAR(50) PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL,
    `xp` INT DEFAULT 0,
    `rank` INT DEFAULT 1,
    `money` INT DEFAULT 0,
    `prestige` INT DEFAULT 0,
    `career_path` INT DEFAULT 0,
    `skill_points` INT DEFAULT 0,
    `total_deliveries` INT DEFAULT 0,
    `perfect_deliveries` INT DEFAULT 0,
    `total_earnings` BIGINT DEFAULT 0,
    `total_distance` FLOAT DEFAULT 0,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `last_played` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Column	Type	Description
citizenid	VARCHAR(50)	Player identifier (PK)
name	VARCHAR(100)	Player name
xp	INT	Total career XP
rank	INT	Current rank (1-10)
money	INT	ATM balance
prestige	INT	Prestige level (0-5)
skill_points	INT	Available skill points
total_deliveries	INT	Lifetime deliveries
perfect_deliveries	INT	Zero damage deliveries
total_earnings	BIGINT	Lifetime earnings
total_distance	FLOAT	Total km traveled

---

hsjobquest_skills

Player skill levels.

hsjobquest_skills.sql

CREATE TABLE `hsjobquest_skills` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `citizenid` VARCHAR(50) NOT NULL,
    `category` VARCHAR(50) NOT NULL,
    `skill_name` VARCHAR(50) NOT NULL,
    `level` INT DEFAULT 0,
    UNIQUE KEY `unique_skill` (`citizenid`, `category`, `skill_name`),
    FOREIGN KEY (`citizenid`) REFERENCES `hsjobquest_players`(`citizenid`) ON DELETE CASCADE
);

Column	Type	Description
citizenid	VARCHAR(50)	Player identifier
category	VARCHAR(50)	driving, efficiency, combat, special
skill_name	VARCHAR(50)	Skill identifier
level	INT	Current level (0 to max)

---

hsjobquest_factions

Faction reputation tracking.

hsjobquest_factions.sql

CREATE TABLE `hsjobquest_factions` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `citizenid` VARCHAR(50) NOT NULL,
    `faction_id` VARCHAR(50) NOT NULL,
    `reputation` INT DEFAULT 0,
    `missions_completed` INT DEFAULT 0,
    UNIQUE KEY `unique_faction` (`citizenid`, `faction_id`),
    FOREIGN KEY (`citizenid`) REFERENCES `hsjobquest_players`(`citizenid`) ON DELETE CASCADE
);

Column	Type	Description
citizenid	VARCHAR(50)	Player identifier
faction_id	VARCHAR(50)	Faction identifier
reputation	INT	Rep points (0-10000+)
missions_completed	INT	Jobs done for faction

---

hsjobquest_achievements

Unlocked achievements.

hsjobquest_achievements.sql

CREATE TABLE `hsjobquest_achievements` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `citizenid` VARCHAR(50) NOT NULL,
    `achievement_id` VARCHAR(100) NOT NULL,
    `unlocked_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `claimed` TINYINT(1) DEFAULT 0,
    UNIQUE KEY `unique_achievement` (`citizenid`, `achievement_id`),
    FOREIGN KEY (`citizenid`) REFERENCES `hsjobquest_players`(`citizenid`) ON DELETE CASCADE
);

Column	Type	Description
citizenid	VARCHAR(50)	Player identifier
achievement_id	VARCHAR(100)	Achievement identifier
unlocked_at	TIMESTAMP	When achievement was unlocked
claimed	TINYINT(1)	Whether reward was claimed (0/1)

---

hsjobquest_statistics

Detailed player statistics for achievements.

hsjobquest_statistics.sql

CREATE TABLE `hsjobquest_statistics` (
    `citizenid` VARCHAR(50) PRIMARY KEY,
    `fast_deliveries` INT DEFAULT 0,
    `session_deliveries` INT DEFAULT 0,
    `survived_robberies` INT DEFAULT 0,
    `bad_weather_deliveries` INT DEFAULT 0,
    `night_deliveries` INT DEFAULT 0,
    `coop_deliveries` INT DEFAULT 0,
    `bonus_objectives_completed` INT DEFAULT 0,
    `perfect_streak_current` INT DEFAULT 0,
    `perfect_streak_best` INT DEFAULT 0,
    `unique_job_types_completed` TEXT,
    `stealth_perfect` INT DEFAULT 0,
    `total_distance` FLOAT DEFAULT 0,
    `best_single_mission_distance` FLOAT DEFAULT 0,
    `long_haul_deliveries` INT DEFAULT 0,
    FOREIGN KEY (`citizenid`) REFERENCES `hsjobquest_players`(`citizenid`) ON DELETE CASCADE
);

---

hsjobquest_heat

Illegal runs heat tracking.

hsjobquest_heat.sql

CREATE TABLE `hsjobquest_heat` (
    `citizenid` VARCHAR(50) PRIMARY KEY,
    `heat_level` INT DEFAULT 0,
    `last_decay` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`citizenid`) REFERENCES `hsjobquest_players`(`citizenid`) ON DELETE CASCADE
);

---

hsjobquest_mission_history

Completed mission log.

hsjobquest_mission_history.sql

CREATE TABLE `hsjobquest_mission_history` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `citizenid` VARCHAR(50) NOT NULL,
    `mission_type` VARCHAR(50),
    `job_name` VARCHAR(100),
    `faction_id` VARCHAR(50),
    `xp_earned` INT DEFAULT 0,
    `money_earned` INT DEFAULT 0,
    `completion_time` INT DEFAULT 0,
    `damage_taken` FLOAT DEFAULT 0,
    `bonus_completed` TINYINT(1) DEFAULT 0,
    `was_coop` TINYINT(1) DEFAULT 0,
    `distance_traveled` FLOAT DEFAULT 0,
    `completed_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_player_history` (`citizenid`, `completed_at`),
    FOREIGN KEY (`citizenid`) REFERENCES `hsjobquest_players`(`citizenid`) ON DELETE CASCADE
);

---

hsjobquest_coop_groups

Co-op group data.

hsjobquest_coop_groups.sql

CREATE TABLE `hsjobquest_coop_groups` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `leader_citizenid` VARCHAR(50) NOT NULL,
    `mission_id` VARCHAR(100),
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `expires_at` TIMESTAMP,
    FOREIGN KEY (`leader_citizenid`) REFERENCES `hsjobquest_players`(`citizenid`) ON DELETE CASCADE
);

---

hsjobquest_coop_members

Group membership.

hsjobquest_coop_members.sql

CREATE TABLE `hsjobquest_coop_members` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `group_id` INT NOT NULL,
    `citizenid` VARCHAR(50) NOT NULL,
    `joined_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `unique_member` (`group_id`, `citizenid`),
    FOREIGN KEY (`group_id`) REFERENCES `hsjobquest_coop_groups`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`citizenid`) REFERENCES `hsjobquest_players`(`citizenid`) ON DELETE CASCADE
);

---

hsjobquest_economy

Dynamic economy state.

hsjobquest_economy.sql

CREATE TABLE `hsjobquest_economy` (
    `job_type` VARCHAR(50) PRIMARY KEY,
    `current_multiplier` FLOAT DEFAULT 1.0,
    `demand_level` INT DEFAULT 50,
    `completions_this_period` INT DEFAULT 0,
    `last_updated` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

---

hsjobquest_transactions

ATM transaction history.

hsjobquest_transactions.sql

sql CREATE TABLE `hsjobquest_transactions` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `citizenid` VARCHAR(50) NOT NULL, `type` ENUM('deposit', 'withdrawal', 'mission_payout') NOT NULL, `amount` INT NOT NULL, `description` VARCHAR(255), `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX `idx_player_transactions` (`citizenid`, `created_at` DESC), FOREIGN KEY (`citizenid`) REFERENCES `hsjobquest_players`(`citizenid`) ON DELETE CASCADE ); 

---

Indexes

The schema includes indexes for:

• Player lookups by citizenid

• Mission history queries

• Transaction history queries

• Leaderboard rankings

---

Foreign Keys

All tables reference hsjobquest_players with ON DELETE CASCADE, meaning:

• Deleting a player removes all their data

• Data integrity is maintained automatically