# Database Schema

All tables use InnoDB, utf8mb4, bigint primary keys, timestamps, and soft deletes where noted.

## Identity & Access

| Table | Purpose |
|-------|---------|
| users | Staff accounts; is_active, last_login_at |
| roles | Named roles |
| permissions | Granular permission slugs |
| role_user | User ↔ role |
| permission_role | Role ↔ permission |

## Master Data

| Table | Purpose |
|-------|---------|
| customers | Customer/org records (type, contacts, TIN, terms); soft delete = archive |
| space_types | Conference room, banquet, garden, … |
| spaces | Venue spaces with rates, hours, capacity, operational_status |
| space_images | Space gallery |
| space_layouts | Theatre/classroom/… capacities |
| space_blocks | Maintenance / internal blocks |
| event_types | Event type lookup |
| product_categories | Product categories |
| products | Sellable products/services; soft delete = archive |
| packages | Packages; soft delete = archive |
| package_items | Package line definitions |

## Operations

| Table | Purpose |
|-------|---------|
| bookings | Event booking (central record); unique booking_number |
| booking_spaces | Space reservations per booking; hold_expires_at; reservation_status |
| booking_items | Products/packages on booking |
| quotations | Quote headers + snapshots; unique quotation_number; version |
| quotation_items | Quote lines (snapshot) |
| invoices | Invoice headers + snapshots; unique invoice_number |
| invoice_items | Invoice lines (snapshot) |
| payments | Posted payments/receipts; unique payment_number |
| attachments | Polymorphic secure files |
| notifications | In-app notifications |
| number_sequences | Document sequences (type, year, last_number) — locked on allocate |
| settings | Key/value settings (sensitive values encrypted) |
| activity_logs | Audit trail |

## Key Indexes

- bookings: (event_start, event_end), status, customer_id, coordinator_id, booking_number
- booking_spaces: (space_id, setup_start, breakdown_end), reservation_status, hold_expires_at
- quotations: quotation_number, status, customer_id, booking_id, expires_at
- invoices: invoice_number, status, customer_id, booking_id, due_date
- payments: payment_number, invoice_id, customer_id, payment_date
- space_blocks: (space_id, starts_at, ends_at)

## Money Columns

All amounts are `DECIMAL(15,2)`. Never float/double for money.

## Snapshots

Quotation/invoice tables store text/JSON snapshot columns such as:

- customer_snapshot
- event_snapshot
- spaces_snapshot
- terms_snapshot

Parsed and rendered by the application (portable across MySQL/MariaDB).

## Document Number Formats (default)

- BKG-YYYY-######
- QTN-YYYY-######
- INV-YYYY-######
- RCT-YYYY-######

Prefixes and padding are settings-driven.
