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

# Database

Full reference for every table created by `koja-carmarket`.

***

## How tables are created

Two paths, both produce the same schema:

| Method     | When                                                      | Where                                                                           |
| ---------- | --------------------------------------------------------- | ------------------------------------------------------------------------------- |
| **Auto**   | First start when `Config.Database.AutoCreateTable = true` | `server/database.lua`                                                           |
| **Manual** | You run the SQL yourself                                  | [`database.sql`](https://github.com/KOJA/koja-carmarket/blob/main/database.sql) |

{% hint style="info" %}
Auto-create also runs idempotent `ALTER TABLE` migrations every startup for backwards compatibility. Safe to leave enabled in production.
{% endhint %}

***

## Tables

### `koja_carmarket`

Vehicles currently **physically spawned** in market zones (the eye-candy parked car you walk up to).

| Column    | Type        | Note                                |
| --------- | ----------- | ----------------------------------- |
| `id`      | INT PK      | auto-increment                      |
| `zone_id` | VARCHAR(50) | references `Config.Zones[*].id`     |
| `slot_id` | VARCHAR(64) | references a `CarMarketBoxes[*].id` |
| `owner`   | VARCHAR(50) | seller identifier                   |
| `vehicle` | JSON        | full vehicle properties             |
| `plate`   | VARCHAR(8)  |                                     |
| `coords`  | JSON        | `{x, y, z}`                         |
| `heading` | FLOAT       |                                     |

### `koja_carmarket_listings`

The **logical** listing — what shows up in the market UI. One row per active offer.

| Column                   | Type         | Note                                              |
| ------------------------ | ------------ | ------------------------------------------------- |
| `id`                     | INT PK       |                                                   |
| `name`                   | VARCHAR(100) | shown in UI                                       |
| `respname`               | VARCHAR(100) | spawn model name                                  |
| `owner`                  | VARCHAR(50)  | seller identifier                                 |
| `car_type`               | VARCHAR(50)  | `sedan`, `coupe`, `suv`, ...                      |
| `drive_type`             | VARCHAR(50)  | `2x4`, `4x4`, `rear-2x4`                          |
| `fuel_type`              | VARCHAR(50)  | `gasoline`, `electric`                            |
| `offert_type`            | VARCHAR(50)  | `buy` or `auction`                                |
| `tags`                   | JSON         | array of strings                                  |
| `price`                  | INT          | live auction price = highest bid                  |
| `mileage`                | INT          | km                                                |
| `description`            | TEXT         |                                                   |
| `plate`                  | VARCHAR(20)  |                                                   |
| `vehicle_data`           | JSON         | full vehicle properties snapshot                  |
| `owned_vehicle_id`       | INT          | FK to ESX `owned_vehicles.id` / QBCore equivalent |
| `seller_name`            | VARCHAR(100) | denormalized for offline-seller display           |
| `zone_id`                | VARCHAR(50)  | NULL if not parked anywhere                       |
| `auction_starts_at`      | TIMESTAMP    | NULL for buy-now                                  |
| `auction_ends_at`        | TIMESTAMP    | NULL for buy-now                                  |
| `listing_fee_paid_until` | TIMESTAMP    | next listing fee due                              |
| `created_at`             | TIMESTAMP    |                                                   |

### `koja_carmarket_offers`

Individual auction bids.

| Column             | Type         | Note                              |
| ------------------ | ------------ | --------------------------------- |
| `id`               | INT PK       |                                   |
| `listing_id`       | INT          | FK → `koja_carmarket_listings.id` |
| `buyer_identifier` | VARCHAR(64)  |                                   |
| `buyer_name`       | VARCHAR(100) |                                   |
| `amount`           | INT          |                                   |
| `status`           | VARCHAR(20)  | always `'pending'` currently      |
| `created_at`       | TIMESTAMP    |                                   |

### `koja_carmarket_history`

Audit trail of every sale/listing/auction event.

| Column              | Type         | Note                                                 |
| ------------------- | ------------ | ---------------------------------------------------- |
| `id`                | INT PK       |                                                      |
| `listing_id`        | INT          | FK (logical, not enforced — listings can be deleted) |
| `type`              | VARCHAR(20)  | `listing`, `auction`, `purchase`                     |
| `price`             | INT          |                                                      |
| `seller_identifier` | VARCHAR(64)  |                                                      |
| `seller_name`       | VARCHAR(100) |                                                      |
| `buyer_identifier`  | VARCHAR(64)  | NULL for non-purchase rows                           |
| `buyer_name`        | VARCHAR(100) |                                                      |
| `vehicle_info`      | JSON         | snapshot at sale time                                |
| `created_at`        | TIMESTAMP    |                                                      |

{% hint style="warning" %}
History rows are **not** auto-cleaned. If you have very long-running servers, set up a cron to archive rows older than N months.
{% endhint %}

### `koja_carmarket_slot_owners`

Per-player owned slot inside zones.

| Column             | Type           | Note |
| ------------------ | -------------- | ---- |
| `slot_id`          | VARCHAR(64) PK |      |
| `zone_id`          | VARCHAR(50)    |      |
| `owner_identifier` | VARCHAR(64)    |      |
| `weekly_fee`       | INT            |      |
| `next_payment_at`  | TIMESTAMP      |      |
| `created_at`       | TIMESTAMP      |      |

### `koja_carmarket_exchange`

Per-zone ownership & business settings.

| Column                 | Type           | Note                |
| ---------------------- | -------------- | ------------------- |
| `zone_id`              | VARCHAR(50) PK |                     |
| `owner_identifier`     | VARCHAR(64)    | NULL = server-owned |
| `listing_fee_per_week` | INT            |                     |
| `max_listings`         | INT            |                     |
| `commission_percent`   | INT            | 0–100               |
| `created_at`           | TIMESTAMP      |                     |

### `koja_carmarket_parkings` & `koja_carmarket_parking_slots`

External player-owned parkings (not inside market zones). Reserved for future expansion.

### `koja_carmarket_garage_slots`

Garage slot ↔ vehicle bindings. Reserved.

***

## Common queries

**How much commission did `Z` earn last week?**

```sql
SELECT SUM(price * 0.05) AS commission_estimate
FROM koja_carmarket_history h
JOIN koja_carmarket_listings l ON l.id = h.listing_id
JOIN koja_carmarket_exchange e ON e.zone_id = l.zone_id
WHERE h.type = 'purchase'
  AND e.owner_identifier = 'license:abc123'
  AND h.created_at > NOW() - INTERVAL 7 DAY;
```

**Top 10 sellers by revenue:**

```sql
SELECT seller_identifier, seller_name, SUM(price) AS revenue, COUNT(*) AS sales
FROM koja_carmarket_history
WHERE type = 'purchase'
GROUP BY seller_identifier, seller_name
ORDER BY revenue DESC
LIMIT 10;
```

**Release a zone owner (admin SQL):**

```sql
UPDATE koja_carmarket_exchange
SET owner_identifier = NULL
WHERE zone_id = 'zone1';
```