# Database Reference

Database: `petzy_pos` (MySQL 8+ / MariaDB 10.4+, `utf8mb4`). Import order:
`database/schema.sql` then `database/seed.sql` (or `npm run db:setup`).

**Conventions**: `INT UNSIGNED` / `BIGINT UNSIGNED` primary keys,
`DECIMAL(12,2)` for every money column (never floats), `DATETIME` for
timestamps, `created_at`/`updated_at` on nearly every table, `status` ENUMs
instead of hard deletes for business-critical data (spec §102).

## Table groups (87 tables total)

| Group | Tables |
|---|---|
| Multi-tenant core | companies, outlets, roles, permissions, role_permissions, users, user_outlets |
| CRM | customers, customer_addresses, customer_loyalty, suppliers, supplier_contacts |
| Menu | menu_categories, kitchen_stations, menu_items, menu_item_variations, menu_item_addons, menu_item_modifiers, menu_combos, menu_combo_items, taxes, discounts, coupons |
| Floor/Table | floors, tables, table_status_history, restaurant_reservations |
| Orders/Payments | orders, order_items, order_item_addons, order_item_modifiers, order_taxes, order_discounts, payments, payment_transactions, refunds |
| KOT/Kitchen | kot, kot_items, kitchen_orders |
| Inventory | inventory_units, inventory_items, inventory_stocks, inventory_transactions, stock_adjustments, stock_transfers, stock_transfer_items, wastage, recipes, recipe_items |
| Purchase | purchase_orders, purchase_order_items, purchase_invoices, purchase_invoice_items, purchase_returns, purchase_return_items |
| Hotel | hotel_room_types, hotel_room_amenities, hotel_rooms, hotel_rate_plans, hotel_room_blocks, hotel_guests, hotel_room_reservations, hotel_checkins, hotel_checkouts, hotel_folios, hotel_folio_items, hotel_room_charges, restaurant_room_charges |
| Ops | cash_registers, cash_transactions, day_end_closings, expenses, employees, attendance, loyalty_transactions |
| Online/QR | online_orders, online_order_items, qr_menus, qr_menu_tables |
| System | notifications, audit_logs, settings, social_integrations, printers, printer_routes, media, backup_logs |

## Key relationships

- `orders.table_id → tables.id`, `tables.current_order_id → orders.id`
  (bidirectional pointer kept in sync by the order controller when a table
  is occupied/freed).
- `kot.order_id → orders.id`, `kot_items.order_item_id → order_items.id`,
  `kitchen_orders.kot_id/station_id` — one KOT can fan out to multiple
  kitchen stations.
- `hotel_room_reservations.room_id → hotel_rooms.id`,
  `hotel_folios.reservation_id`, `hotel_folio_items.folio_id` — folio is the
  single running ledger for a stay; `restaurant_room_charges` links a
  restaurant `order_id` to the `folio_item_id` it posted (the "charge to
  room" flow, spec §48).
- `recipes.item_id → menu_items.id`, `recipe_items.inventory_item_id →
  inventory_items.id` — selling a recipe-linked menu item deducts every
  ingredient via `inventory_transactions` (type `consumption`).
- `inventory_stocks` is keyed `(inventory_item_id, outlet_id)` — stock is
  tracked **per outlet**, enabling true multi-store inventory.

## Numbering

Invoice/KOT/reservation numbers are generated **after** the row's
auto-increment ID is assigned (`backend/src/utils/generateNumber.js`),
formatted as `<outlet-prefix>-<year>-<6-digit-id>`. This is concurrency-safe
by construction — it relies on InnoDB's own AUTO_INCREMENT guarantee rather
than a hand-rolled counter table that would need its own locking.