# Database Analysis — Legacy Schema vs. Target Schema

This document is Phase 1 of the UCP rebuild. It records every table in the legacy
`base.sql` dump, what it did, and its disposition in the new Laravel application,
followed by the full target schema (UCP-owned tables + the FiveM/QBCore-facing
tables accessed on a second database connection).

## 1. Legacy schema (`base.sql`, SA-MP "EmpireRP" panel, 2019)

| Table | Columns | Purpose | Disposition |
|---|---|---|---|
| `ucp_admin` | `a_id`, `a_admin`, `a_pass` | Single flat admin account list. Password compared with `==`, **stored in plaintext** (`123qwe` in the seed dump). No roles. | **Reimagined** as Laravel `users` table + Spatie `roles`/`model_has_roles`. Passwords rehashed with `bcrypt`/`argon2id`. |
| `ucp_category_roulette` | `id`, `name` | Category labels for the gacha/roulette feature ("Money", "Donate", "Car", "Clothes", "EXP", "Money x2"). | **Superseded** by `roulette_categories` (same shape, added `slug`, timestamps). |
| `ucp_item_roulette` | `id`, `i_name`, `i_images`, `i_category`, `i_change`, `i_start_rand`, `i_end_rand` | Prize pool: name, image path, category FK, probability weight (`i_change`), reward value range. Values for `i_change`/`i_start_rand`/`i_end_rand` were **never validated as numeric** server-side. | **Superseded** by `roulette_items` (adds validated numeric casts, foreign key constraint instead of a loose int column). |
| `ucp_drop_roulette` | `p_number`, `p_user`, `p_data`, `p_value`, `p_id`, `p_status` | Win log: which player won what, when, claimed or not. `p_user` stored as a raw nickname string, not a FK. | **Superseded** by `roulette_drops` (FK to `users.id` and `roulette_items.id`, `claimed_at` timestamp instead of a status int). |
| `ucp_news` | `n_id`, `n_title`, `n_text`, `n_data`, `n_images`, `n_url` | News posts, echoed **unescaped** into every public page — this is the stored-XSS vector documented in the security audit. | **Superseded** by `news` (adds `user_id` author FK, `published_at`, soft deletes; all output goes through React's default escaping, never raw HTML). |
| `ucp_settings` | `s_title`, `s_favicon`, `s_logo`, `s_md5`, `s_donate_cost` | Single-row site config, including an `s_md5` flag that toggled whether player passwords were hashed with unsalted MD5 or stored in plaintext. | **Superseded** by `site_settings` (key/value table). The `s_md5` password-hashing toggle is **dropped entirely** — the new app always uses Laravel's Argon2id/Bcrypt hasher, no insecure option is exposed. |
| `ucp_table_settings` (referenced by `engine/config/table.php`, not present as CREATE TABLE in this dump but read/written by `update_table_setting`) | `name_column`, `value_column`, `name` | Meta-indirection so the panel could point at differently-named SA-MP `accounts` columns. Itself editable through an unauthenticated, SQL-injectable endpoint. | **Dropped.** FiveM/QBCore schemas are consistent enough across servers that this indirection isn't needed; the `FrameworkAdapter` layer (see Phase 2) replaces it with typed, versioned adapters instead of a runtime-editable column map. |
| SA-MP `accounts` table (external, not created by this project — game server-owned) | `ID`, `Name`, `Password`, `Money`, `Bank`, `Donate`, `Level`, `Skin`, per-weapon skill columns, etc. | The live player table the whole UCP was a thin read/write veneer over. | **Not applicable to FiveM.** Replaced conceptually by the `fivem` connection's `players` table (QBCore convention) in the target schema below. |

## 2. Target schema — UCP-owned tables (`mysql` connection, Laravel migrations)

| Table | Key columns | Notes |
|---|---|---|
| `users` | `id`, `name`, `email`, `password`, `discord_id`, `discord_avatar`, `two_factor_secret`, `two_factor_recovery_codes`, `two_factor_confirmed_at`, timestamps | Laravel's default users table extended with Discord OAuth + Fortify-style 2FA columns. `password` nullable (Discord-only accounts never set one). |
| `roles`, `permissions`, `model_has_roles`, `model_has_permissions`, `role_has_permissions` | Spatie `laravel-permission` package tables | RBAC. Seeded roles: `superadmin`, `admin`, `moderator`, `player`. |
| `audit_logs` | `id`, `user_id`, `action`, `subject_type`, `subject_id`, `changes` (json), `ip_address`, `created_at` | Every admin mutation (ban, warn, item grant, economy edit, settings change) writes one row here — replaces the total lack of an audit trail in the legacy app. |
| `news` | `id`, `user_id`, `title`, `body`, `image_path`, `url`, `published_at`, `created_at`, `updated_at`, `deleted_at` | Successor to `ucp_news`. |
| `roulette_categories` | `id`, `name`, `slug` | Successor to `ucp_category_roulette`. |
| `roulette_items` | `id`, `roulette_category_id` (FK), `name`, `image_path`, `weight` (unsigned int), `value_min`, `value_max` | Successor to `ucp_item_roulette`, with a DB-level FK and validated numeric columns. |
| `roulette_drops` | `id`, `user_id` (FK), `roulette_item_id` (FK), `value`, `claimed_at`, `created_at` | Successor to `ucp_drop_roulette`. The balance debit that pairs with each spin is wrapped in a DB transaction with a row lock (`lockForUpdate`) to close the legacy TOCTOU double-spend bug. |
| `site_settings` | `key`, `value` | Successor to `ucp_settings`, minus the `s_md5` toggle. |
| `discord_role_maps` | `id`, `discord_role_id`, `spatie_role_name` | Config for the Discord Role Sync job. |
| `personal_access_tokens` (Sanctum) | standard Sanctum columns | API tokens issued per FiveM server for the REST API (Phase 7). |
| `sessions` | standard Laravel sessions table | Database session driver, so sessions survive app restarts and can be revoked server-side. |

## 3. Target schema — FiveM/QBCore-facing tables (`fivem` connection, models only — see Phase 2)

These are **not created by this project's migrations**; they're modeled against the
live FiveM server's `oxmysql` database using standard QBCore/Qbox naming. The
`FrameworkAdapter` layer documents the exact columns each adapter expects so a
server owner can confirm/adjust to their real schema before going live.

| Table (QBCore convention) | Key columns | Adapter model |
|---|---|---|
| `players` | `citizenid`, `license`, `name`, `money` (json), `charinfo` (json), `job` (json), `gang` (json), `position` (json), `metadata` (json), `last_updated` | `Player` |
| `player_vehicles` | `id`, `citizenid`, `vehicle`, `hash`, `mods` (json), `plate`, `garage`, `state` | `Vehicle` |
| `player_outfits` / inventory (QBCore stores inventory as a `metadata.inventory` json column, or a dedicated `inventory` json column depending on qb-inventory version) | citizenid, items (json) | `InventoryItem` (adapter-normalized) |
| `player_houses` (qb-houses convention) | `id`, `citizenid`, `house`, `garage`, `decorations` (json) | `House` |
| `gangs` / `jobs` (from `qb-core` shared Lua tables, mirrored into DB by the adapter for admin-panel editing) | `name`, `label`, `grades` (json) | `Gang`, `Job` |
| `bans` (custom table this project adds to the `fivem` DB, since QBCore itself doesn't ship one) | `id`, `citizenid`, `license`, `reason`, `banned_by`, `expires_at` | `Ban` |
| `warnings` (custom, same rationale as `bans`) | `id`, `citizenid`, `reason`, `warned_by`, `created_at` | `Warning` |

An ESX-convention adapter (`users`, `owned_vehicles`, `datastore`) and a Qbox-convention
adapter are stubbed against the same interface so the framework choice is a config
value (`config('fivem.framework')`), not a code fork.
