# 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](/new/mdt/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.