All koja-mdt database tables with a one-line purpose, and how tables are created and migrated.
Database#
koja-mdt uses oxmysql. All tables start with koja_mdt_.
How tables are created#
On every start the resource runs koja-mdt.sql statement by statement. Every statement is CREATE TABLE IF NOT EXISTS, so nothing is overwritten. After that, a list of columns added since version 1 is checked and added with ALTER TABLE when missing (for example on koja_mdt_officers, koja_mdt_citations, koja_mdt_court_cases, koja_mdt_medical_bills). The web, custody, divisions and admin modules also run their own file from sql/ at start. You do not need to import anything by hand.
The only SQL file that is not run automatically is sql/jobs_fire.sql, an optional ESX script that adds a fire job with grades.
The MDT does not create or change your framework tables. It reads them through editable/server/framework.lua, see Integrations.
Tables#
Staff and profiles#
| Table | Purpose |
|---|
koja_mdt_officers | One row per MDT member: department, callsign, badge, phone, photo banner, signature, suspension, duty seconds, last seen |
koja_mdt_duty_sessions | Duty periods used for weekly hours and the duty heatmap |
koja_mdt_commendations | Rewards and strikes given to members |
koja_mdt_avatars | Profile pictures captured in game, and whether the member set a custom one |
koja_mdt_department_overrides | Departments created or changed in the Admin panel |
koja_mdt_divisions | Divisions per department |
koja_mdt_division_members | Members of each division and their role |
koja_mdt_certifications | Certification definitions per department |
koja_mdt_officer_certifications | Certifications assigned to members |
People, vehicles and records#
| Table | Purpose |
|---|
koja_mdt_profiles | MDT data for a person: photo, notes, flags, fingerprint, DNA and gallery |
koja_mdt_vehicles | MDT data for a vehicle: flags, notes, strike points, photo |
koja_mdt_weapons | Firearm registry with serial, owner and status |
koja_mdt_incidents | Reports |
koja_mdt_incident_officers | Officers on a report |
koja_mdt_incident_people | Suspects, victims and witnesses on a report, with charges, fine, months and processing state |
koja_mdt_incident_vehicles | Vehicles on a report |
koja_mdt_charges | The penal code, seeded from shared/penalcode.lua on first start |
koja_mdt_warrants | Warrants with type, status and expiry |
koja_mdt_bolos | Be-on-the-lookout notices |
koja_mdt_evidence | Evidence locker with chain of custody |
koja_mdt_bulletins | Department bulletins |
koja_mdt_citations | Citations, signature state and payment |
koja_mdt_anpr_log | Plate reader hits |
Custody#
| Table | Purpose |
|---|
koja_mdt_jail_records | Jail sentences, release time and outcome |
koja_mdt_impounds | Impounded vehicles with fee and hold |
Court#
| Table | Purpose |
|---|
koja_mdt_court_cases | Court cases and docket data |
koja_mdt_court_charges | Charges attached to a case |
koja_mdt_court_hearings | Scheduled hearings |
koja_mdt_court_docket | Docket entries (filings, motions, rulings, orders) |
koja_mdt_court_links | Records linked to a case |
koja_mdt_expungements | Expungement requests |
koja_mdt_expunged_records | Reports hidden from a person's record by an expungement |
koja_mdt_bar_members | Bar registry |
Communication#
| Table | Purpose |
|---|
koja_mdt_mail | Mail messages |
koja_mdt_mail_box | Per-recipient mailbox state (folder, read, starred, important) |
koja_mdt_notifications | Notification center entries |
koja_mdt_calendar_events | Calendar events |
koja_mdt_calendar_rsvp | Attendance answers |
koja_mdt_calendar_reactions | Reactions on events |
koja_mdt_calendar_comments | Comments on events |
koja_mdt_forms | Form definitions |
koja_mdt_form_submissions | Submitted forms and their review state |
koja_mdt_logs | Audit log |
Cameras#
| Table | Purpose |
|---|
koja_mdt_cameras | CCTV camera positions and settings |
koja_mdt_bodycam_sessions | Bodycam recordings with route and shot count |
koja_mdt_bodycam_events | Events inside a bodycam session |
Medical#
| Table | Purpose |
|---|
koja_mdt_medical_profiles | Patient medical profile: blood type, allergies, conditions, medications, emergency contact, DNR and donor state |
koja_mdt_treatments | ePCR treatment reports |
koja_mdt_medical_bills | Medical bills |
koja_mdt_admissions | Hospital admissions and bed use |
Web portal#
| Table | Purpose |
|---|
koja_mdt_web_accounts | Username and password hash for web sign in |
koja_mdt_web_sessions | Signed-in devices |
koja_mdt_web_codes | One-time login codes |
Retention#
Old rows are removed automatically according to the config:
| Data | Option |
|---|
| Audit log | Config.Logs.keepDays |
| Notifications | Config.Notifications.keepDays |
| Mail | Config.Mail.keepDays (0 keeps forever) |
| ANPR log | Config.Anpr.keepDays |
| Bodycam sessions | Config.Bodycams.keepDays |
| Expired web sessions and codes | Cleaned every 15 minutes |
The Admin panel can also purge old logs and expired web sessions manually.
