Database Schema

🗄️

Auto-migrated

All tables and columns are created and migrated automatically on first start via oxmysql. No manual SQL import is required - this reference is for developers integrating with the data.

guidebook_categories

Column	Type	Description
`id`	INT AUTO_INCREMENT	Primary key
`key`	VARCHAR(50) UNIQUE	Unique identifier
`title`	VARCHAR(255)	Display title
`icon`	VARCHAR(100)	Emoji or Font Awesome class
`order`	INT	Sort order (0-based)
`enabled`	TINYINT	1 = visible, 0 = hidden
`collapsible`	TINYINT	Whether the category can be collapsed
`expanded`	TINYINT	Default expanded state
`permissions`	TEXT (JSON)	Job restrictions (see format below)
`guidebook_id`	VARCHAR(50)	Which guidebook this belongs to

guidebook_pages

Column	Type	Description
`id`	INT AUTO_INCREMENT	Primary key
`key`	VARCHAR(50) UNIQUE	Unique identifier
`category_key`	VARCHAR(50)	Parent category key
`title`	VARCHAR(255)	Display title
`icon`	VARCHAR(100)	Emoji or Font Awesome class
`content`	LONGTEXT	HTML content (sanitized via DOMPurify)
`order`	INT	Sort order within category
`enabled`	TINYINT	1 = visible, 0 = hidden
`permissions`	TEXT (JSON)	Job restrictions
`updated_at`	TIMESTAMP	Auto-updated on modification

guidebook_points

Column	Type	Description
`id`	INT AUTO_INCREMENT	Primary key
`key`	VARCHAR(50) UNIQUE	Unique identifier
`label`	VARCHAR(255)	3D text label
`coords`	TEXT (JSON)	`{"x": 0.0, "y": 0.0, "z": 0.0}`
`type`	VARCHAR(20)	Point type (`text`)
`show_text`	TINYINT	Show 3D text
`text_size`	FLOAT	Text size multiplier
`text_color`	VARCHAR(20)	Text hex color
`text_font`	INT	GTA text font ID
`interact_text_size`	FLOAT	Interaction text size
`interact_text_offset`	FLOAT	Interaction text Z offset (above label)
`text_z_offset`	FLOAT	Label height (Z) offset - added via auto-migration, default 1.0
`draw_distance`	FLOAT	Distance to render text
`interact_distance`	FLOAT	Distance to interact
`show_marker`	TINYINT	Show ground marker
`marker_config`	TEXT (JSON)	Marker type, color, size
`marker_draw_distance`	FLOAT	Distance to render the marker (default 20.0)
`marker_size`	TEXT (JSON)	`{"x":..,"y":..,"z":..}` size override
`marker_color`	VARCHAR(20)	Marker hex color override
`marker_rotation`	TINYINT	Rotate the marker
`marker_face_camera`	TINYINT	Marker always faces the camera
`marker_z_offset`	FLOAT	Marker vertical offset
`show_blip`	TINYINT	Show minimap blip
`blip_config`	TEXT (JSON)	Blip sprite, color, scale
`can_navigate`	TINYINT	Allow GPS navigation
`open_page_key`	VARCHAR(50)	Page to open on interact (empty = standalone custom content)
`custom_content`	LONGTEXT	Custom HTML content (used when no page is linked)
`enabled`	TINYINT	1 = active, 0 = disabled
`permissions`	TEXT (JSON)	Job restrictions

guidebook_player_progress

Help point views - unique on (citizenid, point_key).

Column	Type	Description
`id`	INT AUTO_INCREMENT	Primary key
`citizenid`	VARCHAR(50)	Player identifier (citizenid/identifier/license)
`point_key`	VARCHAR(50)	Viewed help point key
`viewed_at`	TIMESTAMP	First view timestamp

guidebook_page_reads

Reading progress - unique on (citizenid, page_key). Drives the per-player progress bar and the “NEW” badge.

Column	Type	Description
`id`	INT AUTO_INCREMENT	Primary key
`citizenid`	VARCHAR(50)	Player identifier
`page_key`	VARCHAR(50)	Read page key
`content_ver`	VARCHAR(64)	Content version (resets “read” state when a page is edited)
`read_at`	TIMESTAMP	Last read timestamp

guidebook_history

Audit trail for rollback support.

Column	Type	Description
`id`	INT AUTO_INCREMENT	Primary key
`action`	VARCHAR(50)	`create`, `update`, `delete`, `duplicate`, `import`, `rollback`
`entity_type`	VARCHAR(20)	`category`, `page`, `point`, `all`
`entity_key`	VARCHAR(50)	Key of the affected entity
`player_id`	VARCHAR(50)	Player identifier
`player_name`	VARCHAR(100)	Player name
`details`	TEXT	Action details + optional `SNAPSHOT:` JSON for rollback
`created_at`	TIMESTAMP	When the action occurred

guidebook_analytics

Page views.

Column	Type	Description
`id`	INT AUTO_INCREMENT	Primary key
`page_key`	VARCHAR(50)	Viewed page key
`player_id`	VARCHAR(50)	Viewer identifier
`viewed_at`	TIMESTAMP	View timestamp

Permissions JSON Format

The permissions column stores a JSON array of job requirements:


[
    {"job": "police", "grade": 0},
    {"job": "ambulance", "grade": 2}
]

Empty array or null = visible to everyone
Multiple entries = player needs ANY of the listed jobs (OR logic)
grade = minimum grade required
Permission groups: use @groupname as the job name (e.g. @emergency)