> For the complete documentation index, see [llms.txt](https://tyrix.gitbook.io/docs/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://tyrix.gitbook.io/docs/script-resources/tyrix-multi-job/database.md).

# Database

The schema installs itself. On every resource start the server reads `SQL.sql` from the resource folder, strips its comments, splits it on semicolons and runs each statement, then prints a confirmation:

```
[tyrix_multijob] ready | framework esx | locale en | 24 jobs | 6 tables | hours on | discord off
```

There is no manual import step. Importing `SQL.sql` into your database yourself is optional and only useful if you want to pre-provision the schema before the first start, or hand it to a DBA as a reference of what the resource will create.

Every statement in the file is `CREATE TABLE IF NOT EXISTS`, so re-running on every restart is harmless: a table that already exists is left as it is. Each statement is run independently and any failure is printed on its own line rather than aborting the rest, so a single permissions problem does not leave you with a half-built schema and no explanation:

```
[tyrix_multijob] schema statement failed: ...
```

After the tables exist, a small set of **migrations** runs on the same boot pass, each one existence-checked so it applies exactly once. They bring older installs up to the current schema: duplicate `(identifier, job)` rows in the hours table are merged (highest value per column wins — historic writes hit every copy, so that is the faithful merge) and the pair then gets a unique key; two redundant single-column indexes are dropped; and indexes on `clock_in_time` and `week_start` are added for the periodic saver and the weekly sweep. Every migration failure is logged and skipped rather than aborting the boot. Column types are never altered — if you have manually changed one, the resource will not change it back, and will not warn you either.

## Tables

Four tables belong to this resource:

| Table                          | Purpose                                                                 |
| ------------------------------ | ----------------------------------------------------------------------- |
| `tyrix_multijob`               | Which jobs each player holds. One row per player per job.               |
| `tyrix_multijob_hours`         | All-time and weekly hours per player per job, plus the open clock-in.   |
| `tyrix_multijob_goals`         | The weekly hour goal for each job and rank.                             |
| `tyrix_multijob_funds_history` | The society transaction ledger behind the boss menu's Transactions tab. |

`SQL.sql` also contains `addon_account` and `addon_account_data`. These are the standard ESX society-account tables and are not owned by this resource — they are included so society balances work out of the box on a server that does not have them yet. If your ESX install already has them, the `IF NOT EXISTS` guard leaves them and their balances untouched.

Jobs and ranks themselves are not stored here. They live in your framework's own tables; this resource only records which of them each player holds. See [Framework tables it reads](#framework-tables-it-reads).

## Columns

`identifier` throughout means whatever your framework uses as a player key: the `license:…` string on ESX, or the `citizenid` on QBCore and QBox. The bridge resolves it, so the same schema serves both — but it does mean the column contents are not portable between frameworks.

### tyrix\_multijob

| Column       | Type           | Notes                           |
| ------------ | -------------- | ------------------------------- |
| `identifier` | `varchar(46)`  | Player key.                     |
| `job`        | `varchar(100)` | Job name, matching `jobs.name`. |
| `grade`      | `int(10)`      | The player's rank in that job.  |

Identity is the pair `(identifier, job)`, enforced by a unique key rather than a primary key. That unique key is load-bearing: job saves are written as `INSERT … ON DUPLICATE KEY UPDATE`, so the same player being assigned the same job twice updates their grade instead of creating a duplicate row. Drop or rename that key and you will accumulate duplicate roster entries.

A row appears here automatically whenever your framework assigns a player a job, unless that job is listed in `Config.blacklist`, is the configured off-duty job, or the player is already at their `maxJobs` cap. Hiring from the boss menu writes a row the same way. Deleting a row removes the job from that player's menu.

An additional index on `job` exists because the boss menu's roster and employee count read by job; reads by player are covered by the unique key's leading column.

### tyrix\_multijob\_hours

| Column          | Type               | Notes                                                                    |
| --------------- | ------------------ | ------------------------------------------------------------------------ |
| `identifier`    | `varchar(46)`      | Player key.                                                              |
| `job`           | `varchar(100)`     | Job name.                                                                |
| `clock_in_time` | `bigint`, nullable | Unix timestamp in seconds of the open session. `NULL` means clocked out. |
| `total_hours`   | `decimal(10,2)`    | Cumulative hours, never reset.                                           |
| `weekly_hours`  | `decimal(10,2)`    | Hours since the start of the current week.                               |
| `week_start`    | `date`, nullable   | The week this row's `weekly_hours` belongs to.                           |

Identity is again `(identifier, job)`, enforced by the same kind of unique key as the roster table — the session upsert depends on it. Supporting indexes cover the hot sweeps: `job` for the boss roster, `clock_in_time` for the periodic saver's open-session scan, and `week_start` for the weekly reset.

`clock_in_time` doing double duty as both the session start and the clocked-in flag is what lets an in-progress session survive a crash: on the next clock-out, periodic save or weekly sweep the elapsed time is calculated from the stored timestamp, not from anything held in memory.

`week_start` is the reset marker. A row whose `week_start` is older than the current week has its `weekly_hours` zeroed the next time it is touched, and a periodic sweep catches rows nobody touched — including offline players and sessions left running across the boundary. If you edit `weekly_hours` by hand without also setting `week_start` to the current week, your edit will be wiped on the next sweep. The mechanics are covered in [Hours Tracking](/docs/script-resources/tyrix-multi-job/hours-tracking.md).

### tyrix\_multijob\_goals

| Column        | Type            | Notes                                                 |
| ------------- | --------------- | ----------------------------------------------------- |
| `job`         | `varchar(100)`  | Job name. Part of the primary key.                    |
| `grade`       | `int(10)`       | Rank number. Part of the primary key.                 |
| `weekly_goal` | `decimal(10,2)` | Target hours per week for that rank. Defaults to `5`. |

The primary key is the pair `(job, grade)`, so a goal is per rank, not per employee. A rank with no row here falls back to `Config.hoursTracking.defaultWeeklyGoal`, which ships as 5 hours, so you do not need to seed this table. Bosses create rows from the Weekly Goals tab.

### tyrix\_multijob\_funds\_history

| Column              | Type                     | Notes                                                         |
| ------------------- | ------------------------ | ------------------------------------------------------------- |
| `id`                | `int`, auto increment    | Primary key.                                                  |
| `society`           | `varchar(100)`           | Job name the transaction belongs to.                          |
| `type`              | `varchar(16)`            | `deposit`, `withdraw`, `bonus` or `wash`.                     |
| `amount`            | `int`                    | Signed: positive for `deposit`, negative for everything else. |
| `actor_identifier`  | `varchar(46)`            | The boss who performed the action.                            |
| `actor_name`        | `varchar(100)`           | Their character name, stored at the time of the transaction.  |
| `target_identifier` | `varchar(46)`, nullable  | The affected employee, where one applies.                     |
| `target_name`       | `varchar(100)`, nullable | Their character name.                                         |
| `created_at`        | `bigint`                 | Unix timestamp in **milliseconds**.                           |

The signed `amount` is deliberate: deposits are stored positive and everything else negative, so the UI can render `+`/`−` straight from the column. It is not quite a balance journal, though — a `wash` row is stored negative even though washing converts the boss's own black money and never debits the society account, which is why the balance-over-time chart excludes `type = 'wash'`. Exclude washes yourself before treating `SUM(amount)` as net movement.

Names are denormalised into `actor_name` and `target_name` on write so the ledger stays readable after a character is renamed or deleted. Treat them as a snapshot, not as a live join.

`created_at` is milliseconds, not seconds, which is the opposite of `clock_in_time` in the hours table. Divide by 1000 before feeding it to anything that expects a Unix second timestamp.

Only `bonus` rows populate the target columns; deposits, withdrawals and washes have no counterparty and leave them `NULL`. The composite index on `(society, created_at)` is what keeps the Transactions tab and the balance-over-time chart fast on societies with long histories.

## Society accounts

On ESX, society money is not stored in this resource's tables at all. It is held by `esx_addonaccount` in a shared account named `society_<jobname>` — `society_police` for the `police` job — and every deposit, withdrawal and bonus goes through that account.

The first time a boss menu needs an account that `esx_addonaccount` does not know about, the resource registers it: it inserts the missing `addon_account` row using the job's label from your framework, announces the society so other society-aware resources pick it up, and asks `esx_addonaccount` to reload its registry. Existing rows are never modified, so a society you already have keeps its balance and its label. In practice this means you do not have to pre-create society accounts for new jobs — opening the boss menu once is enough.

{% hint style="warning" %}
Do not edit `addon_account_data` directly to adjust a balance. The Transactions tab, the dashboard's stat tiles and the balance-over-time chart are all built from `tyrix_multijob_funds_history`, which is only written when money moves through the boss menu. A manual balance edit leaves the ledger disagreeing with the account, and the discrepancy looks exactly like theft. Deposit or withdraw through the menu instead.
{% endhint %}

Transfers are blocked for the last ten minutes before a txAdmin scheduled restart, and the boss menu shows the funds panel as locked while that is in effect. The reason is that a transfer is not a single atomic write — money leaves the player and enters the account, or the reverse — and a restart landing between the two halves loses it. The lock is a deliberate trade of ten minutes of convenience for never having to explain where a payroll deposit went.

## Framework tables it reads

Two of your framework's own tables hold the job definitions. This resource reads them; it never creates them:

| Table        | Read for                                     |
| ------------ | -------------------------------------------- |
| `jobs`       | Job names and display labels.                |
| `job_grades` | Rank numbers, rank labels and rank salaries. |

Both are loaded once at resource start and cached, which is why a job or rank you add directly in SQL will not appear until you restart the resource.

The only write this resource ever makes to either of them is `job_grades.salary`, and only when salary management is enabled — a boss changing a rank's pay from the menu updates that column and the cached value, then pushes the new figure to connected clients. Nothing else in `jobs` or `job_grades` is modified.

That write is also the whole of the salary feature. This resource does not run a payroll loop and never pays a recurring wage: it edits the number, and your framework's own paycheck system pays it out on its own schedule. Salary management ships disabled, since handing bosses control of a value your framework pays out every cycle is a decision worth making on purpose — see [Configuration → Boss menu](/docs/script-resources/tyrix-multi-job/configuration.md#boss-menu).

`job_grades` also holds the grade *name* column, which is what boss permissions are matched against — the name, not the label and not the grade number. Getting that wrong is the usual reason a boss cannot open the boss menu; [Troubleshooting](/docs/script-resources/tyrix-multi-job/troubleshooting.md) covers the symptom.