Database schema
This page documents the core tables in the YumKiosk database, their relationships, and the key indexes that make our queries fast. It's meant as a reference for anyone working in the codebase — not every column is listed (that's what the migration files are for), but the high-level structure and the nuances of how things link together are. Tables are grouped by concern.
Identity and tenancy
owners
The root entity. One row per business on the platform.
| column | type | notes |
|---|---|---|
| id | bigint | PK |
| name | string | Legal business name |
| display_name | string | What customers see |
| stripe_id | string nullable | Stripe customer ID for SaaS billing |
| stripe_connect_id | string nullable | Connect account ID for Connect flows |
| plan | enum | free, starter, pro, custom |
| trial_ends_at | timestamp nullable | End of 30-day trial |
users
Everyone with a login, across all roles. Spatie roles are attached via the pivot.
| column | type | notes |
|---|---|---|
| id | bigint | PK |
| owner_id | bigint nullable | FK owners.id (null = shared-pool agent) |
| string unique | ||
| password | string | bcrypt |
| agent_status | enum | offline, available, busy, break |
| hourly_rate_cents | int | Set per agent by the owner |
| last_seen_at | timestamp | Heartbeat-updated |
Indexes: (owner_id, agent_status) for fast "who's available?" lookups.
locations
A physical address for an owner. Most owners have one, chains have many.
| column | type |
|---|---|
| id | bigint |
| owner_id | bigint |
| name | string |
| address | text |
| timezone | string |
Devices
kiosks
A paired tablet.
| column | type | notes |
|---|---|---|
| id | bigint | |
| owner_id | bigint | |
| location_id | bigint | |
| name | string | Display name |
| device_id | uuid | Tablet-generated |
| device_token | string | Long-lived API token (hashed at rest) |
| pairing_code | string nullable | 6-digit code during pending state |
| pairing_expires_at | timestamp nullable | |
| status | enum | unpaired, active, disabled |
| last_seen_at | timestamp nullable |
Indexes: (device_id) unique, (status, last_seen_at) for health reports.
Catalog
categories
Top-level menu organization.
| column | type |
|---|---|
| id | bigint |
| owner_id | bigint |
| name | string |
| sort_order | int |
| is_active | bool |
menu_items
The actual sellable things.
| column | type |
|---|---|
| id | bigint |
| owner_id | bigint |
| category_id | bigint |
| name | string |
| description | text |
| price_cents | int |
| photo_path | string nullable |
| is_active | bool |
modifier_groups and modifier_options
Customizations attached to menu items via a menu_item_modifier_groups pivot table. Supports reuse — one modifier group can be attached to many items.
Sessions and orders
sessions
One row per kiosk-initiated interaction.
| column | type | notes |
|---|---|---|
| id | bigint | |
| uuid | uuid | public-facing identifier |
| owner_id | bigint | |
| kiosk_id | bigint | |
| agent_id | bigint nullable | null while pending |
| status | enum | pending, active, payment, completed, abandoned, failed |
| started_at | timestamp | |
| accepted_at | timestamp nullable | |
| ended_at | timestamp nullable | |
| end_reason | string nullable |
Indexes: (status, owner_id) for incoming-session queries, (agent_id, started_at) for shift calculations.
orders
The cart that got finalized and paid.
| column | type |
|---|---|
| id | bigint |
| session_id | bigint |
| owner_id | bigint |
| subtotal_cents | int |
| tax_cents | int |
| tip_cents | int |
| total_cents | int |
| stripe_payment_intent | string |
| status | enum (pending, paid, refunded) |
| refunded_at | timestamp nullable |
order_lines
Individual items on an order.
| column | type |
|---|---|
| id | bigint |
| order_id | bigint |
| menu_item_id | bigint |
| quantity | int |
| unit_price_cents | int |
| modifiers_json | json |
| notes | text nullable |
We snapshot the modifier selections as JSON rather than FK-ing back to modifier_options because historical orders shouldn't break when the catalog changes. The canonical option IDs are included in the JSON for analytics, but the display text and price is frozen at order time.
Shifts and billing
shifts
Rolled-up per-day agent clocking.
| column | type |
|---|---|
| id | bigint |
| user_id | bigint |
| owner_id | bigint |
| date | date |
| minutes_clocked | int |
| hourly_rate_cents | int |
| gross_cents | int |
| tips_cents | int |
subscription_invoices (local mirror of Stripe)
We mirror our SaaS billing invoices locally for fast listing in the owner panel.
| column | type |
|---|---|
| id | bigint |
| owner_id | bigint |
| stripe_invoice_id | string |
| period_start | date |
| period_end | date |
| plan_fee_cents | int |
| agent_hours_cents | int |
| platform_fee_cents | int |
| tax_cents | int |
| total_cents | int |
| status | enum (draft, open, paid, uncollectible, void) |
| pdf_url | string nullable |
Multi-tenant enforcement
Every table with an owner_id column has a Laravel global scope (BelongsToOwner trait) that automatically adds WHERE owner_id = ? to every query based on the current user's owner. This is the single most important safety net in the codebase — removing it would immediately let tenants see each other's data. We have a test (tests/Feature/TenantIsolationTest.php) that verifies every model with owner_id has the scope applied.
Migrations
All migrations live under database/migrations/. We never edit old migrations — any schema change is a new migration file. This means deployment is always php artisan migrate with no manual steps. Rollbacks are supported but discouraged in production; we prefer forward-only fixes.