> For the complete documentation index, see [llms.txt](https://docs.lostdev.store/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.lostdev.store/territories/database-1.md).

# database

Uses [`oxmysql`](https://github.com/overextended/oxmysql). Tables are created automatically on startup by `Persistence.Initialize` (server/persistence.lua), but the schema is also available in [`install.sql`](broken://pages/4a99ee40f882cdc75a838165eeecbda8500ba5cb) for manual/managed installs.

## `territories_data`

Holds the current state of each territory (config + custom zones).

| Column                | Type             | Description                                             |
| --------------------- | ---------------- | ------------------------------------------------------- |
| `id`                  | VARCHAR(50) PK   | Territory identifier.                                   |
| `owner`               | VARCHAR(50) NULL | Owning faction (`nil` = neutral).                       |
| `progress`            | FLOAT            | Capture progress, 0-100.                                |
| `state`               | VARCHAR(20)      | `neutral` \| `capturing` \| `contested` \| `cooldown`.  |
| `cooldown_start_time` | BIGINT           | `GetGameTimer()` at cooldown start.                     |
| `cooldown_duration`   | BIGINT           | Cooldown duration, in seconds.                          |
| `last_update`         | TIMESTAMP        | Automatically updated by MySQL.                         |
| `data`                | LONGTEXT         | JSON with the same fields, for quick inspection/backup. |

`INSERT ... ON DUPLICATE KEY UPDATE` — never duplicates, always upserts by `id`.

## `territories_custom_zones`

Territories created via the Admin panel (**ADMIN** tab → *Create Zone*), so they survive a resource restart.

| Column        | Type             | Description                         |
| ------------- | ---------------- | ----------------------------------- |
| `id`          | VARCHAR(50) PK   | Slug generated from the `label`.    |
| `label`       | VARCHAR(100)     | Territory name.                     |
| `description` | VARCHAR(255)     | Free-form description.              |
| `coord_x/y/z` | FLOAT            | Zone center.                        |
| `radius`      | FLOAT            | Radius, in meters.                  |
| `owner`       | VARCHAR(50) NULL | Initial owner.                      |
| `created_by`  | VARCHAR(100)     | Player identifier (or `'console'`). |
| `created_at`  | TIMESTAMP        | Automatic.                          |

When a custom zone is deleted (`Admin.DeleteZone`), the matching row is removed from this table — history in `territories_history` is **not** deleted.

## `territories_history`

Log of every capture/defend/lose/admin-action event. **Single source of data** for the tablet's My Faction, Statistics, History, and Activity Feed tabs — if this table is empty, those tabs show an empty state (nothing is simulated/invented).

| Column           | Type                                             | Description                                                        |
| ---------------- | ------------------------------------------------ | ------------------------------------------------------------------ |
| `id`             | INT PK AUTO\_INCREMENT                           |                                                                    |
| `territory_id`   | VARCHAR(50)                                      | Territory the event refers to.                                     |
| `event_type`     | VARCHAR(20)                                      | `captured` \| `defended` \| `lost` \| `admin_override`.            |
| `faction`        | VARCHAR(50)                                      | Faction the event happened to.                                     |
| `previous_owner` | VARCHAR(50) NULL                                 | Previous owner, when applicable.                                   |
| `is_admin`       | TINYINT(1)                                       | `1` if forced via the Admin panel/dev commands.                    |
| `reason`         | VARCHAR(255) NULL                                | Reason supplied by the admin (shown with a shield icon in the UI). |
| `created_at`     | TIMESTAMP                                        | Automatic.                                                         |
| Indexes          | `idx_faction`, `idx_territory`, `idx_created_at` | For the statistics queries.                                        |

{% hint style="info" %}
`is_admin` and `reason` were added in a later version; older installs are migrated automatically on startup via `Persistence.EnsureColumn` (conditional ALTER TABLE, safe to run repeatedly).
{% endhint %}

### Derived queries (server/persistence.lua + server/stats.lua)

* **Win rate** = `(captures + defenses) / (captures + defenses + losses) * 100`.
* **Streak** = most recent consecutive events without a `lost`, excluding `admin_override` (so it doesn't inflate/pollute a real win streak).
* **Longest control time** = largest difference between "now" and the last time the faction captured a territory it **still** owns.
* **Daily activity** = count of `captured`/`defended` per day, last N days (default 7).

## Maintenance

* There is no automatic cleanup routine for `territories_history` — it grows indefinitely. If you need to cap its size, consider a periodic task (`DELETE FROM territories_history WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY)`), outside of this resource.
* Manual backup: `Persistence.CreateBackup()` / `Persistence.RestoreFromBackup(backup)` operate on the in-memory state (`TerritoryStates`), not directly on the database — useful for snapshots within the same server session, not a substitute for a real database backup.


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://docs.lostdev.store/territories/database-1.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.