> For the complete documentation index, see [llms.txt](https://code-after-sex.gitbook.io/script-documentation/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://code-after-sex.gitbook.io/script-documentation/cas-advanced-gang/developers/database.md).

# Database

The oxmysql tables of CAS Advanced Gang System: what each one holds, what is kept, automatic updates, disbanding and a clean slate.

CAS Advanced Gang System uses **oxmysql**. Every table is created on the first start. `sql/bearclaw.sql` holds the same statements, for a database user that can't create tables at runtime.

## Tables

| Table                    | Holds                                                                                                                                                                   |
| ------------------------ | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `bearclaw_gangs`         | One row per gang: name, tag, motto, treasury, tier, camp centre, influence, upkeep due and debt, and the lifetime challenge counters.                                   |
| `bearclaw_members`       | One row per character in a gang: rank, contributed, deposits, last seen, and the day's stash and treasury use. `char_id` is unique, so a character rides with one gang. |
| `bearclaw_ranks`         | Each gang's own copy of the ranks: name, order, permissions, allowance and ceiling.                                                                                     |
| `bearclaw_stash`         | One row per item stack in a gang's wagon, with RSG freshness.                                                                                                           |
| `bearclaw_stash_weapons` | One row per stored gun: serial, label, rounds and components (VORP), or `info` (RSG).                                                                                   |
| `bearclaw_ledger`        | Treasury and stash movements: `type`, `who`, `amount`, `note` and the `balance` after it.                                                                               |
| `bearclaw_structures`    | Placed structures: `structure_id`, position and heading.                                                                                                                |
| `bearclaw_invites`       | Invitations and their status: `pending`, `accepted` or `declined`.                                                                                                      |
| `bearclaw_audit`         | The camp's record: `who` did `what`.                                                                                                                                    |
| `bearclaw_requests`      | Treasury requests waiting for the leader. A row is deleted when the request is answered, or when its rider leaves or is kicked.                                         |

Ledger `type` is one of `deposit`, `withdraw`, `stash_in`, `stash_out`, `upgrade` or `upkeep`.

## What's kept

| Data        | Kept per gang                                   |
| ----------- | ----------------------------------------------- |
| Ledger      | The newest `Config.LedgerHistory` entries (200) |
| Record      | The newest 25                                   |
| Invitations | The newest 25                                   |

Older rows are trimmed as new ones are written, so the tables don't grow without end.

## Updates

New columns are added to existing tables on start. The matching `ALTER TABLE` lines sit next to each table in `sql/bearclaw.sql`.

## Disbanding

A disband deletes the gang's rows from every table in **one transaction**. If it fails, nothing is deleted, the console says so, and the gang is back after a restart. It's never half deleted.

## Clean slate

To wipe every gang, stop the resource and drop the ten `bearclaw_` tables. They're created again on the next start.