# Rafif Hotel — Booking Management System — Build Plan

Solo-maintained booking system for a ~30-room family hotel. Works fully offline at
the frontdesk, syncs online across devices (frontdesk PC + owner's phone/laptop),
and is deployable to standard cPanel hosting.

## 1. Tech stack

- **Backend**: Node.js + Express + MySQL (`mysql2`). One Express app serves both
  the REST API and the built React frontend — a single deployable unit that fits
  cPanel's "Setup Node.js App" (Passenger) + a standard MySQL database.
- **Frontend**: React + TypeScript + Vite, built as a PWA (installable, offline
  app-shell caching via service worker).
- **Local/offline storage**: Dexie.js (IndexedDB wrapper) in the browser. All
  writes go to the local DB first, so the frontdesk UI never blocks on network.
- **Sync**: custom outbox pattern — queued local changes are flushed to the
  REST API when online; server changes are pulled down periodically / on
  reconnect. See §5.
- **Auth**: JWT, two roles — `owner` and `frontdesk`.
- **Styling**: Tailwind CSS.

## 2. Roles & the shared-login / audit-log tradeoff

- One **Frontdesk** login (shared by all reception staff) and one **Owner** login
  (full access: reports, settings, rates).
- Because the frontdesk login is shared, raw auth alone can't tell staff apart.
  To still get useful logs of "who did what": on opening the app each session,
  frontdesk is prompted **"Who's on shift?"** — pick/type a staff name from a
  short list. This is just an attribution tag stored with each action, not a
  security boundary. It's stored locally and attached to every change made in
  that session, including ones made offline.

## 3. Data model (MySQL)

```
users
  id, role ENUM('owner','frontdesk'), username, password_hash, created_at

staff_names            -- lightweight list for the "who's on shift" picker
  id, name, active

rooms
  id, number, floor, type, base_rate, status ENUM('active','maintenance')

guests
  id, full_name, nationality, id_or_passport_no, phone, email, created_at

bookings
  id, guest_id, room_id, check_in, check_out,
  nights INT,                         -- derived, but stored for easy querying
  rate_per_night, total_price,
  status ENUM('confirmed','checked_in','checked_out','cancelled','no_show'),
  source ENUM('walk_in','phone','other'),
  notes,
  created_by_staff_name, created_at, updated_at

payments
  id, booking_id, amount, method ENUM('cash','card','other'),
  type ENUM('deposit','balance','refund'),
  receipt_no, created_by_staff_name, created_at

audit_logs
  id, entity_type ENUM('booking','payment','guest','room'),
  entity_id, action ENUM('create','update','delete','check_in','check_out'),
  staff_name, changes JSON,           -- before/after diff
  device_id,                          -- which device made the change
  created_at,                         -- server receipt time
  client_created_at                   -- original time on the device (may be offline-delayed)
```

Notes:
- `nights` is derived from `check_in`/`check_out` but stored on the row so
  list/report queries don't need date math.
- `audit_logs.changes` stores a JSON diff (old values vs new values) so the
  owner can see exactly what changed, not just that something changed.
- Every table that can be edited offline needs a stable client-generated UUID
  (in addition to/instead of auto-increment id) so the sync layer can match
  records created offline with their server copy without collisions across
  devices. Recommend UUID primary keys for `bookings`, `guests`, `payments`,
  `audit_logs`.

## 4. REST API (v1)

Auth
- `POST /api/auth/login` — returns JWT + role

Rooms
- `GET /api/rooms` — list + status
- `POST/PUT /api/rooms/:id` — owner only

Guests
- `GET /api/guests?search=`
- `POST/PUT /api/guests/:id`

Bookings
- `GET /api/bookings?from=&to=&status=` — for calendar/list views
- `GET /api/bookings/availability?room_id=&from=&to=` — conflict check
- `POST /api/bookings`
- `PUT /api/bookings/:id` (edit, check-in, check-out, cancel are all status/field updates)

Payments
- `POST /api/payments` (tied to a booking, generates receipt_no)
- `GET /api/payments?booking_id=`

Sync
- `POST /api/sync/push` — batch of queued local changes (bookings/guests/payments/audit_logs)
- `GET /api/sync/pull?since=<timestamp>` — changes since last sync, for pulling
  down what other devices/frontdesk did while this device was offline

Reports (owner only)
- `GET /api/reports/occupancy?from=&to=`
- `GET /api/reports/revenue?from=&to=`
- `GET /api/audit-logs?entity_type=&from=&to=` — the "what did frontdesk do" view

## 5. Offline sync design

1. All writes (create/update booking, record payment, etc.) go to Dexie
   immediately and render instantly in the UI (optimistic).
2. Each write is also appended to a local **outbox** table with a client
   timestamp, staff_name tag, and device id.
3. A background sync service (runs on interval + on `online` browser event):
   - POSTs outbox entries to `/api/sync/push` in order.
   - On success, marks them synced and removes from outbox.
   - Calls `/api/sync/pull?since=<last_sync_time>` to fetch changes made
     elsewhere (e.g. owner edited a booking from their phone) and merges them
     into local Dexie tables.
4. **Conflict rule**: last-write-wins per record, based on server receipt time.
   Every overwrite is still recorded in `audit_logs`, so if two edits collide,
   the owner can see both versions in the log even though only one "wins" the
   live record. This is enough precision for a 30-room family hotel — no need
   for field-level CRDT merging.
5. Every `audit_logs` write goes through the same outbox/sync path, so actions
   taken offline still show up in the owner's log once the device reconnects.

## 6. Frontend screens

1. **Login** — role-based (Owner / Frontdesk), frontdesk also picks "who's on shift"
2. **Calendar/timeline** (primary view) — rooms down the side, dates across the
   top, bookings as colored bars by status; click a bar to open/edit
3. **Bookings list** — searchable/filterable table (by date range, status, guest name)
4. **New/edit booking form** — guest fields (name, nationality, ID/passport,
   phone), room picker filtered to available rooms for the chosen dates,
   auto-calculated nights, rate, deposit entry
5. **Check-in / check-out** — quick actions from calendar or list, status change
6. **Payment entry + receipt** — record payment against a booking, print a
   simple receipt
7. **Owner dashboard** — occupancy %, revenue, upcoming arrivals/departures
8. **Audit log view** (owner only) — filterable feed of frontdesk actions:
   who, what, when, before → after

## 7. Suggested build order

1. MySQL schema + migrations, seed ~30 rooms
2. Express API: auth, rooms, guests, bookings, payments, audit-logs (write
   audit entries from within each mutating endpoint, not bolted on later)
3. Sync endpoints (`push`/`pull`)
4. React app shell, PWA config, Dexie schema mirroring MySQL tables
5. Login + "who's on shift" flow
6. Booking form + list view (get core CRUD working online-only first)
7. Offline outbox + sync service wired in
8. Calendar/timeline view
9. Payments + receipt printing
10. Owner dashboard + audit log view
11. Test offline scenarios explicitly: airplane-mode booking creation, two
    devices editing the same booking, reconnect-and-sync timing

## 8. Implementation tooling

When implementing this in Claude Code (VS Code):

- Use the **senior-fullstack** skill for scaffolding and reviewing the
  Node/Express/MySQL + React project structure, API design, and code quality.
- Use the **UI/UX Pro Max** skill for the actual screen design work (calendar/
  timeline grid, forms, dashboard) so the interface stays consistent and usable
  for non-technical frontdesk staff.
- A **`design.md`** already exists in this folder and is the single source of
  truth for the visual/UX system — it documents the existing Rafif Hotel brand
  (Ancient Egypt theme: navy/gold/sand palette, Cormorant + Montserrat fonts)
  used on the public site, plus a separate neutral/functional admin dashboard
  style (no gold gradients or motifs, `#0f1117` sidebar, `--al-*` tokens). The
  frontdesk/owner booking screens are internal tools, so they should follow the
  **admin dashboard** styling in `design.md`, not the public-site theme.
  Consult and update `design.md` whenever a new screen is built rather than
  deciding styling ad hoc per component — extend it with booking-specific
  needs it doesn't yet cover (calendar/timeline grid colors, receipt print
  layout, RTL/Arabic support for guest-facing documents).
- Reminder on build order (§7): get core booking CRUD working **online-only**
  first, before wiring in the offline outbox/sync layer — it isolates bugs to
  one layer at a time instead of debugging data logic and sync logic together.

## 9. cPanel deployment notes

- Node app via cPanel's "Setup Node.js App" (Passenger), pointed at the Express
  entry file that serves the built `frontend/dist` as static files plus `/api/*`.
- Standard cPanel MySQL database + user, connection details in `.env`
  (never commit `.env`).
- Build the frontend locally (`vite build`) and deploy the `dist` output —
  cPanel Node apps don't need to run the Vite dev server.