# Reservation Workflow Analysis: Current vs Enhanced PMS Recommendations
## Executive Summary
The current Zapazime system has partially implemented the recommended Reservation → Stay separation pattern, but significant gaps exist in both the structural workflow and the financial/operational lifecycle. The analysis has been enhanced with expert feedback to include a complete PMS architecture including Folio/Charges system, dual guest tracking, and a safer migration strategy.
---
## Current System Structure
### Models
1. **Reservation Model** (Deprecated)
- Uses `bookings` table
- Marked for removal in version 2.0
- Kept for backward compatibility only
2. **Booking Model** (Current Main Model)
- Uses `bookings` table
- Contains both reservation intent AND stay execution data
- Has check_in/check_out/status fields that should belong to Stay
3. **Stay Model**
- Uses `stays` table
- Linked to bookings via `booking_id` foreign key
- Partially implements the recommended structure
4. **BookingGuest Model**
- Uses `booking_guests` table
- Linked to bookings (not stays)
- Contains personal data in the same table (not separated)
---
## Enhanced PMS Architecture (Expert Feedback)
### Complete Lifecycle Structure
```
BOOKING
│
├── Booking Items
│ ├── Accommodation
│ ├── Extras
│ └── Packages
│
├── Booking Guests / Expected Guests
│
├── Payments
│ ├── Deposit
│ ├── Payment
│ └── Refund
│
├── Cancellation / No-show
│
└── STAY (1..N)
│
├── Stay Guests (Actual Guests)
│ └── Guest / Person
│
├── Room Assignments
│
├── Check-in / Check-out
│
└── FOLIO
├── Accommodation charges
├── Extras
├── Discounts
├── Taxes
├── Payments allocation
└── Adjustments
↓
Invoice / Credit Note
Fiscal Receipt
Cross-Cutting Concerns:
- Notifications
- Documents
- Activity / Audit Log
- Housekeeping
- ESTI reporting
```
---
## Original Recommendations vs Current Implementation
### 1. Clear Separation: Reservation vs Stay
**Recommendation:**
- **Reservation** = Intention/contract for future stay
- **Stay** = Actual physical presence after check-in
**Current Status:** ⚠️ PARTIALLY IMPLEMENTED
- Stay model exists but Booking model still contains stay-related fields
- Booking has: `checked_in_at`, `checked_out_at`, `checked_in_by`, `checked_out_by`, `status` (including checked_in/checked_out)
- These fields should be in Stay, not Booking
**Gap:** Booking model mixes reservation intent with stay execution
---
### 2. Stays Table Structure Comparison
| Field | ChatGPT Recommendation | Current Implementation | Status |
|-------|----------------------|------------------------|---------|
| id | ✓ | ✓ | ✅ |
| reservation_id | ✓ | booking_id (not reservation_id) | ⚠️ |
| property_id | ✓ | venue_id | ✅ (equivalent) |
| unit_id | ✓ | venue_object_id | ✅ (equivalent) |
| status: checked_in/checked_out/cancelled | ✓ | pending/active/completed/cancelled | ⚠️ (different values) |
| check_in_at | ✓ | actual_check_in | ✅ (equivalent) |
| expected_check_out_at | ✓ | ❌ MISSING | ❌ |
| check_out_at | ✓ | actual_check_out | ✅ (equivalent) |
| checked_in_by | ✓ | ❌ MISSING | ❌ |
| checked_out_by | ✓ | ❌ MISSING | ❌ |
| guest_count_adults | ✓ | ❌ MISSING | ❌ |
| guest_count_children | ✓ | ❌ MISSING | ❌ |
| notes | ✓ | check_in_notes, check_out_notes, internal_notes | ✅ |
| created_at, updated_at | ✓ | ✓ | ✅ |
**Missing Fields in Stays Table:**
- `expected_check_out_at` - Planned departure time
- `checked_in_by` - Staff member who performed check-in
- `checked_out_by` - Staff member who performed check-out
- `guest_count_adults` - Actual number of adults
- `guest_count_children` - Actual number of children
**Status Values Assessment:**
- **Expert Feedback:** Current values are actually better
- Current: `pending`, `active`, `completed`, `cancelled` ✅
- Reason: These describe lifecycle state, not just events
- Check-in/check-out are events, not statuses:
```
pending → CHECK-IN → active → CHECK-OUT → completed
```
- This is a cleaner model than using checked_in/checked_out as statuses
---
### 3. Guest Structure
**Expert Enhanced Recommendation:**
```
Booking → Booking Guests (Expected) → Guest/Person
↓
Stay → Stay Guests (Actual) → Guest/Person
```
**Dual Guest Concept:**
- **Booking Guests** = Expected guests (who was booked)
- **Stay Guests** = Actual guests (who actually stayed)
**Example:**
- Ivan and Maria are booked in the reservation
- Ivan actually checks in, Maria doesn't come, Peter is added at check-in
**Current Implementation:**
```
Booking → BookingGuest (personal data in same table)
```
**Current BookingGuest Table Fields:**
- booking_id
- full_name (encrypted)
- id_number (encrypted)
- guest_type (adult/child/infant)
- nationality (encrypted)
- date_of_birth (encrypted)
- room_assignment (venue_object_id)
- id_document_path (encrypted)
- notes
- is_primary
- address
- sex
- id_document_type
- id_document_country_code
- esti_registration_id
**Gap Analysis:**
❌ No separate `StayGuests` table (guests linked to booking, not stay)
❌ No separate `Guest`/`Person` table (personal data mixed with booking relationship)
❌ Cannot track guest check-in/check-out times per stay
❌ Cannot track which guests are actually checked in vs just booked
❌ No distinction between expected vs actual guests
**Enhanced Recommended Structure:**
- `booking_guests` table (keep existing, these are expected guests):
- booking_id
- guest_id
- is_primary
- guest_type
- relationship_to_booker
- `stay_guests` table (NEW, these are actual guests):
- stay_id
- guest_id
- is_primary
- guest_type
- checked_in_at
- checked_out_at
- `guests`/`persons` table (NEW, reusable personal data):
- Personal data fields from booking_guests
- Reusable across bookings and stays
- Single source of truth for guest profiles
---
### 4. Room Assignment
**Expert Enhanced Recommendation:**
- Separate reserved type from actual provided room
- `unit_id` should belong to Stay, not Reservation
- Allows for room changes, upgrades
- Advanced: Stay Room Assignments with full history
**Current Implementation:**
- `venue_object_id` exists in both Booking and Stay
- No separate room assignment tracking
- Cannot support room changes during a single stay
- Cannot track history of room assignments
**Gap Analysis:**
⚠️ venue_object_id in Booking duplicates stay data
❌ No `stay_room_assignments` table for tracking room changes
❌ Cannot handle scenario: "12 Aug – Room 204, 13–15 Aug – Room 307" without creating new stay
**Enhanced Recommended Structure:**
- `stay_room_assignments` table:
- stay_id
- venue_object_id
- assigned_at
- released_at
- assigned_by
- reason (upgrade, maintenance, guest request, etc.)
- notes
**Example Room Assignment History:**
```
Stay #1521
12 Aug 14:00 → Room 204 (assigned_by: staff_1, reason: standard assignment)
13 Aug 11:30 → Room 204 released
13 Aug 11:30 → Room 307 (assigned_by: staff_2, reason: guest upgrade request)
15 Aug 10:42 → Room 307 released
```
This provides complete audit trail of room changes during stay.
---
### 5. Data Duplication & Folio System
**Expert Enhanced Recommendation:**
- **Booking** should hold contracted/agreed price
- **Folio** should hold actual charges during stay
- This separation solves billing accuracy issues when actual stay differs from booking
**Current Implementation:**
- Booking model contains all pricing/payment data: `subtotal`, `tax`, `service_fee`, `total_price`, `total_amount`, `deposit_amount`, `paid_amount`
- Stay model duplicates some pricing: `actual_amount`, `deposit_amount`, `additional_charges`
- No Folio system exists
**Gap Analysis:**
❌ No Folio system to track actual charges
⚠️ Stay model duplicates pricing data from Booking
- This creates data inconsistency risk
- Makes it harder to distinguish between booked price vs actual charged amount
**Example of the Problem:**
```
BOOKING (Contracted Price):
3 nights × 150 = 450 BGN
Breakfast = 60 BGN
--------------------
Booked total = 510 BGN
FOLIO (Actual Charges - Missing):
Accommodation 450
Breakfast 60
Parking 30 ← added during stay
Minibar 18 ← added during stay
Late checkout 50 ← added during stay
Discount -20
-------------------------
TOTAL 588
```
**Enhanced Recommended Structure:**
- Keep contracted pricing in Booking
- Add Folio system for actual charges:
- `folios` table:
- stay_id
- folio_number
- status (open/closed)
- total_amount
- balance_due
- `folio_charges` table:
- folio_id
- charge_type (accommodation, extra, adjustment, discount)
- description
- amount
- tax_amount
- charged_at
- charged_by
- `folio_payments` table:
- folio_id
- payment_id
- amount_allocated
- allocated_at
---
### 6. One Reservation → Multiple Stays
**ChatGPT Recommendation:**
- One reservation should be able to create multiple stays
- Example: Group booking with 3 rooms
**Current Implementation:**
- Stay has `booking_id` foreign key (one-to-many: Booking → Stays)
- This structure DOES support multiple stays per booking
- However, the workflow doesn't explicitly implement this pattern
**Status:** ✅ STRUCTURALLY SUPPORTED, ⚠️ NOT FULLY IMPLEMENTED IN WORKFLOW
---
## QR Workflow Comparison
**ChatGPT Recommended Workflow:**
```
Booking → Show QR → Scan → Validate reservation → Create Stay → Add guests → Assign room → Check-in → Stay active → Check-out
```
**Current Implementation:**
- Booking model has `checkIn()` method that creates Stay automatically
- Stay creation happens during check-in
- No explicit "Create Stay" step before check-in
- Guests are added to Booking, not Stay
**Gap Analysis:**
⚠️ Stay creation is automatic during check-in, not a separate step
❌ Cannot create stay without checking in
❌ Guests are linked to Booking, not Stay
❌ No separate "Add guests to stay" workflow
---
## Critical Gaps Summary
### High Priority Gaps
1. ❌ Missing `expected_check_out_at` in stays table
2. ❌ Missing `checked_in_by` and `checked_out_by` in stays table
3. ❌ Missing `guest_count_adults` and `guest_count_children` in stays table
4. ❌ No `stay_guests` table (guests linked to booking, not stay)
5. ❌ No separate `guests`/`persons` table for personal data
6. ❌ No `stay_room_assignments` table for room change tracking
### Medium Priority Gaps
7. ⚠️ Status values don't match recommendation (pending/active vs checked_in/checked_out)
8. ⚠️ Booking model contains stay-related fields that should be in Stay
9. ⚠️ Stay model duplicates pricing data from Booking
10. ⚠️ Workflow doesn't explicitly support multiple stays per booking
### Low Priority Gaps
11. ⚠️ No explicit "Create Stay" step before check-in
**Note on booking_id:** The original analysis marked `booking_id` as a gap (suggesting `reservation_id` instead). Expert feedback indicates this is NOT a problem. Since the system uses Booking as the main model, `booking_id` is the correct foreign key naming. No change needed.
---
### Missing Lifecycle Components
The original analysis focused on structural gaps but missed critical PMS lifecycle components:
**7. Folio/Charges System** ❌ MISSING
- No folio table to track actual charges during stay
- No folio_charges table for individual charge items
- No folio_payments table for payment allocation
- Cannot handle scenarios where actual charges differ from booking
**8. Payment Allocations** ❌ MISSING
- No mechanism to allocate payments to specific charges
- Cannot track which payment covers which folio items
- Makes refunds and adjustments difficult
**9. Refunds Management** ⚠️ PARTIAL
- Refunds exist but not integrated with folio system
- No clear refund workflow tied to specific charges
**10. Housekeeping Lifecycle** ❌ MISSING
- No housekeeping status tracking for rooms
- No housekeeping assignments related to stays
- Cannot track cleaning schedules based on check-outs
**11. Audit/Activity Logging** ⚠️ PARTIAL
- Booking activity logs exist but not comprehensive
- No audit trail for room assignments, folio changes, guest modifications
- Cannot reconstruct full operational history
**12. Notifications** ⚠️ PARTIAL
- Notification system exists but not integrated with new stay/folio workflow
- No systematic notification triggers for stay events
**13. ESTI Reporting Integration** ⚠️ PARTIAL
- ESTI submission exists in Stay model
- Not integrated with guest data structure
- May need updates for new guest/folio architecture
---
## Enhanced Recommendations (Expert Feedback)
**IMPORTANT SAFETY NOTE:** Do NOT start by removing/deleting fields from Booking. The expert strongly advises an additive migration strategy:
1. Add new architecture first
2. Migrate workflow to new structure
3. Keep legacy fields temporarily for compatibility
4. Remove legacy fields only after system is stabilized
### Phase 1: Critical Field Additions (Immediate)
1. Add migration to add missing fields to `stays` table:
- `expected_check_out_at`
- `checked_in_by` (foreign key to users)
- `checked_out_by` (foreign key to users)
- `guest_count_adults`
- `guest_count_children`
### Phase 2: Guest Structure Enhancement (High Priority)
2. Create `guests`/`persons` table:
- Personal data fields from booking_guests
- Reusable across bookings and stays
- Single source of truth for guest profiles
3. Create `stay_guests` table (for actual guests):
- stay_id
- guest_id
- is_primary
- guest_type
- checked_in_at
- checked_out_at
4. Update `booking_guests` table (keep as expected guests):
- Add guest_id foreign key to link to guests table
- Add relationship_to_booker field
- Keep existing fields for backward compatibility
5. Migrate existing booking_guests data to new structure
### Phase 3: Room Assignment Tracking (Medium Priority)
6. Create `stay_room_assignments` table:
- stay_id
- venue_object_id
- assigned_at
- released_at
- assigned_by
- reason (upgrade, maintenance, guest request, etc.)
- notes
7. Update workflow to track room assignments
8. Add UI for room change management during stay
### Phase 4: Folio/Charges System (HIGH PRIORITY - Critical for PMS)
9. Create `folios` table:
- stay_id
- folio_number
- status (open/closed)
- total_amount
- balance_due
- created_at
- closed_at
10. Create `folio_charges` table:
- folio_id
- charge_type (accommodation, extra, adjustment, discount)
- description
- amount
- tax_amount
- charged_at
- charged_by
11. Create `folio_payments` table:
- folio_id
- payment_id
- amount_allocated
- allocated_at
12. Update invoicing to use folio data instead of booking data
13. Update fiscal receipt integration to use folio charges
### Phase 5: Payment Allocations & Refunds (Medium Priority)
14. Enhance payment system to support allocation to specific folio charges
15. Implement refund workflow tied to specific folio charges
16. Add payment allocation tracking and reporting
### Phase 6: Housekeeping Integration (Medium Priority)
17. Create housekeeping status tracking linked to stays
18. Add housekeeping assignments based on check-out schedules
19. Integrate room assignment history with housekeeping workflow
### Phase 7: Enhanced Audit Logging (Medium Priority)
20. Expand activity logging to cover:
- Room assignment changes
- Folio charge modifications
- Guest check-in/check-out events
- Payment allocations
- Refund processing
21. Add audit trail reconstruction capabilities
### Phase 8: Notification System Updates (Low Priority)
22. Integrate notification triggers with new stay/folio events
23. Add notifications for:
- Room changes
- Folio charges
- Payment allocations
- Housekeeping status changes
### Phase 9: ESTI Integration Updates (Medium Priority)
24. Update ESTI submission to use new guest data structure
25. Integrate ESTI with stay_guests instead of booking_guests
26. Ensure folio charges align with ESTI reporting requirements
### Phase 10: Legacy Field Management (LOW PRIORITY - Only after stabilization)
27. **DO NOT REMOVE** existing Booking fields yet
28. Mark legacy fields as deprecated in code comments
29. Use new fields in all new code
30. After 3-6 months of stable operation, consider removing:
- checked_in_at/checked_out_at from Booking (use Stay fields)
- Pricing duplication from Stay (use Folio)
- venue_object_id from Booking (use stay_room_assignments)
### Phase 11: Workflow Enhancements (Low Priority)
31. Implement explicit "Create Stay" step before check-in
32. Update QR workflow to match recommended pattern
33. Add support for multiple stays per booking in UI
34. Add guest management UI for check-in/check-out per stay
---
## Conclusion
The current Zapazime system has a good foundation with the Stay model, but significant work is needed to transform it into a complete PMS (Property Management System). The original analysis identified structural gaps, and the expert feedback has expanded this to include critical financial and operational lifecycle components.
### Critical Findings
**Structural Gaps (from original analysis):**
1. Missing accountability fields in stays table (checked_in_by, checked_out_by, guest counts)
2. Guest structure not properly separated (should support both expected and actual guests)
3. No room assignment change tracking
4. Data duplication between Booking and Stay
**Major Missing Components (identified by expert):**
5. **Folio/Charges System** - This is the most critical missing piece for a proper PMS
6. Payment allocation mechanism
7. Housekeeping lifecycle integration
8. Comprehensive audit logging
9. Enhanced ESTI integration
### Key Improvements from Expert Feedback
1. **Dual Guest Concept** - Keep both Booking Guests (expected) and Stay Guests (actual)
2. **Folio System** - Separate contracted price (Booking) from actual charges (Folio)
3. **Current Status Values** - pending/active/completed/cancelled are actually better than checked_in/checked_out
4. **Safer Migration Strategy** - Add new architecture first, migrate workflow, keep legacy fields, remove only after stabilization
5. **booking_id is Correct** - No need to rename to reservation_id
### Recommended Implementation Strategy
The enhanced recommendations provide an 11-phase approach:
- **Phases 1-3:** Critical structural improvements (fields, guests, room assignments)
- **Phase 4:** Folio/Charges system (HIGH PRIORITY - transforms system into PMS)
- **Phases 5-9:** Additional lifecycle components (payments, housekeeping, audit, notifications, ESTI)
- **Phase 10:** Legacy field cleanup (only after 3-6 months of stable operation)
- **Phase 11:** Workflow enhancements
### Priority Order
**Immediate (Phase 1):** Add missing stays table fields
**High Priority (Phases 2, 4):** Guest structure + Folio system (Folio is critical for PMS)
**Medium Priority (Phases 3, 5-7, 9):** Room assignments, payments, housekeeping, audit, ESTI
**Low Priority (Phases 8, 10-11):** Notifications, legacy cleanup, workflow enhancements
The additive migration strategy ensures system stability while incrementally building a complete PMS architecture.