OLOW Hotel ERP

Appearance

Database Schema Overview ​

Note: Detailed Entity-Relationship Diagrams (ERDs) are available in each specific Module Documentation file. This document provides the high-level map of how domains connect.

1. Core Shared Tables (Kernel) ​

These tables act as the glue between different modules.

properties ​

The central tenant table.

  • id: Primary Key.
  • name: Hotel Name.
  • subdomain: Multi-tenancy key.

parties (The "Party" Pattern) ​

Unified contact record for any person/entity entering the system.

  • id: PK.
  • property_id: Tenant.
  • name, email, phone: Shared contact info.
  • party_type: CUSTOMER | VENDOR | STAFF.
  • Related:
    • customers table (extends Party).
    • vendors table (extends Party).
    • staff table (extends Party).

users ​

Authentication credentials.

  • id: PK.
  • email, password: Login.
  • party_id: Link to the human record (Staff/Customer).
  • Polymorphism: A User can be a Staff member or a Guest.

2. Module Interactions ​

Front Desk <-> Accounting ​

  • Link: folios -> accounting_accounts (via Service).
  • Flow: Charges in folio_lines are transactional records; they post aggregated journals to accounting_journal_entries.

Front Desk <-> Housekeeping ​

  • Link: rooms -> housekeeping_tasks.
  • Flow: App\Domain\FrontDesk\Models\Room contains the status (clean, dirty) which drives housekeeping_tasks.

POS <-> Inventory ​

  • Link: pos_products -> inventory_recipes -> inventory_items.
  • Flow: Selling a Product triggers consumption of Inventory Items.

Restaurant <-> FrontDesk ​

  • Link: restaurant_orders -> folio_lines (via FolioService).
  • Flow: Room service orders charged directly to guest folio.

All Modules <-> Accounting ​

  • Link: All financial modules resolve accounts via AccountingMapService + AccountingKey enum.
  • Data: accounting_mappings table stores per-property key-to-account mappings.

3. Key Conventions ​

  • Foreign Keys: Always use unsignedBigInteger. Standard format model_name_id.
  • Timestamps: All tables must have created_at, updated_at.
  • Soft Deletes: Critical business data (Bookings, Transactions) must use deleted_at.
  • Money: Stored as decimal(15, 2) or bigint (cents). Be consistent. Current Audit shows mix; preference is Decimal for this codebase.

4. Indexing Strategy ​

  • Primary Index: id.
  • Tenant Index: Compound index (property_id, ...) on almost all lists to ensure performant scoped queries.
  • Search Index: Full text or B-Tree on name, email, phone, booking_number.