> 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-droply/database.md).

# Database

All seven tables auto-create on resource start. No manual import is needed — running `schema.sql` yourself is optional and only useful if you want to pre-provision the schema or hand it to a DBA as reference.

On success the console prints:

```
[Droply] tables ready
```

## Tables

| Table                            | Purpose                                                                                          |
| -------------------------------- | ------------------------------------------------------------------------------------------------ |
| `tyrix_droply_storefronts`       | One row per business storefront — branding, prices, categories, managers, blocklist, open state. |
| `tyrix_droply_orders`            | Every order ever placed, with its full money breakdown and lifecycle timestamps.                 |
| `tyrix_droply_customers`         | Customer accounts — display name, avatar, banner, phone, favourites.                             |
| `tyrix_droply_order_messages`    | Customer ↔ driver chat, scoped per order.                                                        |
| `tyrix_droply_reviews`           | One review per delivered order.                                                                  |
| `tyrix_droply_promos`            | Promo codes, unique per business.                                                                |
| `tyrix_droply_promo_redemptions` | One row per consumed promo use, unique per order.                                                |

Full DDL lives in `schema.sql`, mirrored by the runtime bootstrap in `server/database.lua`.

## Notable columns

**`tyrix_droply_storefronts`**

| Column                           | Notes                                                                        |
| -------------------------------- | ---------------------------------------------------------------------------- |
| `business_id`                    | Primary key. Matches the business's job name in `tyrix_businesses`.          |
| `station_ids`                    | JSON array. **Admin-controlled** — bosses cannot change it.                  |
| `item_prices`                    | JSON map of `item → price`. An item priced `0` is not sold.                  |
| `item_descriptions`              | JSON map of `item → text`, 200 chars each.                                   |
| `hidden_items`                   | JSON array of item slugs kept off the customer menu.                         |
| `categories` / `item_categories` | Ordered category list (max 16) and the `item → category` assignments.        |
| `commission`                     | `DECIMAL(4,3)`, nullable. `NULL` inherits `Config.Droply.defaultCommission`. |
| `managers` / `blocked_customers` | JSON arrays of `{ identifier, name }`.                                       |
| `is_open`                        | `0` hides the order button but keeps the store listed.                       |

**`tyrix_droply_orders`**

| Column                                                       | Notes                                                                                                       |
| ------------------------------------------------------------ | ----------------------------------------------------------------------------------------------------------- |
| `status`                                                     | `pending`, `accepted`, `preparing`, `enroute`, `delivered`, `cancelled`, `expired`.                         |
| `cancel_reason`                                              | Internal code — `timeout`, `customer_cancel`, `funds`, `customer_dc`, `customer_offline`, `server_restart`. |
| `items`                                                      | JSON snapshot of the ordered lines, including the unit price at order time.                                 |
| `dropoff`                                                    | JSON `{ x, y, z, mode }` where `mode` is `live` or `pin`.                                                   |
| `subtotal` / `delivery_fee` / `tip` / `discount` / `total`   | Integer currency. All recomputed server-side.                                                               |
| `created_at` / `accepted_at` / `enroute_at` / `delivered_at` | Lifecycle timestamps for reporting.                                                                         |

Indexed on `(business_id, status)`, `customer_id`, and `worker_id` — the three access patterns the app actually uses.

**`tyrix_droply_reviews`** — `order_id` is `UNIQUE`, so one-review-per-order is guaranteed at the database level, not just in application code.

**`tyrix_droply_promo_redemptions`** — `order_id` is `UNIQUE`. Rows are written when a promo is claimed and deleted when the order is cancelled, so the counter and the redemption history stay consistent.

## Auto-migration

Tables are created with `CREATE TABLE IF NOT EXISTS`, then missing columns are backfilled through idempotent `ALTER TABLE … ADD COLUMN IF NOT EXISTS` batches on every start. Both are safe to re-run, so upgrading between versions never needs a manual migration step.

Columns currently backfilled this way include `orders.discount`, `orders.promo_id`, `storefronts.banner_url`, `storefronts.address`, `storefronts.item_descriptions`, `storefronts.hidden_items`, `storefronts.categories`, `storefronts.item_categories`, `storefronts.blocked_customers`, `customers.banner_url`, `customers.favorites`, and `promos.once_per_customer`.

## Promo counter repair

On every start, inflated `uses` counters left behind by an older build are recomputed from actual order history. The repair only claws back over-counted values and never inflates a correct one. See [Promotions → Counter repair](/docs/script-resources/tyrix-droply/promotions.md#counter-repair).

## Caching

Storefronts and customer accounts are held in memory and read from cache on the hot paths, with a database fallback on a miss. Writes invalidate and re-warm the affected entry immediately, so a save is visible to other players without a restart.

Order state lives in memory while an order is live and is written through to the database on every transition, so the row is always current even mid-flight.

## Legacy import

Coming from the `GoGrub` build of this resource? Old `tyrix_gogrub_*` tables are renamed in place automatically on first start.