# Database

koja-dispatcher creates its tables on start with `CREATE TABLE IF NOT EXISTS`. There is no SQL file to import. All tables use the `koja_dispatch_` prefix.

| Table | Created by | Purpose |
|---|---|---|
| `koja_dispatch_shifts` | core | One row per finished shift: dispatcher, start and end time, calls, resolved, failed, score, star rating and pay. Career stats on the boot screen and shift report come from here. |
| `koja_dispatch_callsigns` | core | Callsigns that dispatchers set for player units, per character identifier. |
| `koja_dispatch_settings` | core | Admin menu data as JSON. Row `main` holds the setting values, the stations and the allowed jobs; row `fleet_override` marks that the fleet editor controls the AI fleet. |
| `koja_dispatch_avatars` | core | Character headshots (image data) shown in the CAD and on the station screen. |
| `koja_dispatch_career` | career module | XP per dispatcher. |
| `koja_dispatch_history` | stats module | Every closed incident: category, priority, position, street, created, first arrival and end time, outcome, response time per department, unit count, source and dispatcher. Used by the statistics and the heatmap; rows older than `stats.retentionDays` are deleted every hour. |
| `koja_dispatch_scenarios` | scenario editor | Custom scenarios, and the enabled flag and weight of built-in scenarios changed in the editor (JSON per scenario id). |
| `koja_dispatch_ai_stations` | fleet editor | AI stations: id, label, department and position. |
| `koja_dispatch_ai_fleet` | fleet editor | AI units: callsign, type, station and enabled flag. |
| `koja_dispatch_bolos` | MDT workspace | BOLOs: type, title, description, plate, model, colour, priority, photo link, last known location, linked incident, author and status. For BOLOs issued in koja-mdt it keeps the MDT id and the fields the MDT does not store. |
| `koja_dispatch_patrol_zones` | patrol module | Patrol zones: name, shape (polygon or circle), assigned units and who created them. |

## Key columns

| Table | Key | Notable columns |
|---|---|---|
| `koja_dispatch_shifts` | `id` (auto), index on `identifier` | `name`, `started_at`, `ended_at`, `calls`, `resolved`, `failed`, `score`, `rating`, `earned` |
| `koja_dispatch_callsigns` | `identifier` | `callsign` |
| `koja_dispatch_settings` | `id` | `data` (LONGTEXT JSON) |
| `koja_dispatch_avatars` | `identifier` | `image`, `updated_at` |
| `koja_dispatch_career` | `identifier` | `name`, `xp`, `updated_at` |
| `koja_dispatch_history` | `id` (auto), indexes on `created_at` and `category` | `x`, `y`, `street`, `first_arrival`, `ended_at`, `outcome`, `response`, `resp_police`, `resp_ems`, `resp_fire`, `units`, `source`, `dispatcher`, `dispatcher_id` |
| `koja_dispatch_scenarios` | `id` | `data` (JSON) |
| `koja_dispatch_ai_stations` | `id` | `label`, `dept`, `x`, `y`, `z` |
| `koja_dispatch_ai_fleet` | `id` (auto) | `callsign`, `type`, `station`, `enabled` |
| `koja_dispatch_bolos` | `id` (auto), indexes on `mdt_id` and `status` | `type`, `title`, `plate`, `x`, `y`, `incident`, `created_by`, `created_at` |
| `koja_dispatch_patrol_zones` | `id` (auto) | `name`, `shape`, `units`, `created_by`, `created_at` |

Identifiers are the framework character identifier from koja-lib, or the license when there is none.

## Resetting data

Stop the resource before you change tables by hand.

| To reset | Do this |
|---|---|
| All admin settings, stations and allowed jobs | **Reset all settings** in the admin menu resets the settings only. To also go back to `Config.Consoles` and `Config.Dispatcher.jobs`, delete the row `main` from `koja_dispatch_settings`. |
| The AI fleet | **Reset to config** on the AI fleet page, or delete the row `fleet_override` and empty `koja_dispatch_ai_stations` and `koja_dispatch_ai_fleet`. |
| Statistics and heatmap | Empty `koja_dispatch_history`. |
| Ranks | Empty `koja_dispatch_career`. XP is then seeded again from `koja_dispatch_shifts` (`Config.Career.seed`). Empty both for a full restart. |
| Headshots | Empty `koja_dispatch_avatars`. They are taken again. |
| Custom scenarios and built-in overrides | Delete rows from `koja_dispatch_scenarios`, or use the scenario editor. |