Skip to main content

Bookings, Quotes & Holds - Domain Specification

First Introduced: MVP.1 (read-only) / V1.0 (full) Status: Specification Complete Last Updated: 2025-10-25

Overview​

The Bookings domain governs the complete reservation lifecycle — from customer inquiry to confirmed reservation, through modification, cancellation, and completion. It represents the core transaction engine of the platform, connecting pricing (RatePlans, Quotes), availability (Calendars, Holds), and payments (Financial transactions) to produce a reliable, auditable, and immutable record of every stay.

This domain defines how quotes are generated, how inventory is temporarily held during checkout, how bookings are confirmed and tracked, and how cancellations are processed according to policy rules.

Responsibilities​

This domain IS responsible for:

  • Generating price quotes based on RatePlans, dates, and occupancy
  • Creating temporary inventory holds during checkout workflows
  • Managing the complete booking state machine (pending → confirmed → checked_in → completed → canceled)
  • Recording detailed line items for rent, fees, taxes, and adjustments
  • Enforcing cancellation policies and calculating refund amounts
  • Maintaining immutable audit trails of all booking lifecycle events
  • Coordinating with Availability domain to block/release calendar slots
  • Providing booking data for financial reconciliation and reporting

This domain is NOT responsible for:

  • Defining pricing rules (→ Pricing & Revenue domain)
  • Managing calendar availability or blocking logic (→ Availability domain)
  • Processing payments or issuing refunds (→ Payments & Financials domain)
  • Syncing bookings to/from external channels (→ Channels & Distribution domain)
  • Guest communication and notifications (→ future Communications domain)
  • Permission checks and access control (→ Authorization domain)

Relationships​

Depends On:

Depended On By:

Related Domains:

Core Concepts​

Entity: Quote​

Purpose: A priced offer representing the cost of a potential stay, calculated from RatePlan rules at a specific point in time. Quotes are immutable snapshots that preserve pricing context even if rate rules change later.

Key Attributes:

  • id (UUID, primary key)
  • org_id (UUID, foreign key → organizations.id)
  • account_id (UUID, foreign key → accounts.id)
  • space_id (UUID, foreign key → spaces.id)
  • unit_id (UUID, nullable, foreign key → units.id) - Future multi-unit support
  • rate_plan_id (UUID, foreign key → rate_plans.id)
  • checkin (DATE, required) - Check-in date
  • checkout (DATE, required) - Check-out date
  • nights (INTEGER, computed) - Number of nights (checkout - checkin)
  • guests (INTEGER, required) - Number of guests
  • adults (INTEGER, nullable) - Adult guest count
  • children (INTEGER, nullable) - Child guest count
  • currency (VARCHAR(3), default 'USD') - ISO 4217 currency code
  • subtotal (BIGINT, required) - Total before fees/taxes in minor units (cents)
  • fees_total (BIGINT, required) - Sum of all fees
  • taxes_total (BIGINT, required) - Sum of all taxes
  • total (BIGINT, required) - Final total amount
  • source (ENUM) - direct | partner | manual | api
  • valid_until (TIMESTAMP) - Quote expiration time
  • created_by (UUID, foreign key → users.id)
  • created_at, updated_at (timestamps)

Relationships:

  • Quote → Org (*, many-to-one)
  • Quote → Account (*, many-to-one)
  • Quote → Space (*, many-to-one)
  • Quote → Unit (*, many-to-one, optional)
  • Quote → RatePlan (*, many-to-one)
  • Quote → Hold (1:1, one-to-one, optional)
  • Quote → Booking (1:1, one-to-one)
  • Quote → QuoteLineItem (1:*, one-to-many)

Lifecycle:

  • Created: When user requests pricing for dates/occupancy
  • Valid: Until valid_until timestamp expires (typically 15-30 minutes)
  • Converted: When confirmed into a Booking
  • Expired: Automatically after TTL, no longer valid for booking

Business Rules:

  • Quote totals must reconcile: total = subtotal + fees_total + taxes_total
  • checkout must be strictly after checkin
  • nights must equal checkout - checkin
  • Cannot create quote for unavailable date range
  • Quote becomes immutable once referenced by a Booking
  • Expired quotes cannot be converted to bookings

Entity: Quote Line Item (quote_line_items)​

Purpose: Itemized breakdown of charges within a quote, providing transparency for nightly rates, cleaning fees, service charges, taxes, and other adjustments.

Key Attributes:

  • id (UUID, primary key)
  • quote_id (UUID, foreign key → quotes.id)
  • type (ENUM) - nightly_rate | cleaning_fee | service_fee | tax | discount | adjustment
  • description (VARCHAR(255)) - Human-readable label (e.g., "Nightly Rate (3 nights @ $200)")
  • category (ENUM) - rent | fee | tax | discount
  • basis (ENUM) - per_stay | per_night | per_guest | percentage
  • quantity (DECIMAL) - Number of units (e.g., 3 nights, 4 guests)
  • unit_amount (BIGINT) - Amount per unit in minor units
  • subtotal (BIGINT) - quantity * unit_amount
  • sort_order (INTEGER) - Display order
  • metadata (JSONB) - Additional context (rule_id, applied_discounts)
  • created_at (TIMESTAMP)

Relationships:

  • QuoteLineItem → Quote (*, many-to-one)

Lifecycle:

  • Created: When quote is generated from RatePlan
  • Immutable: Never modified after quote creation
  • Retained: Indefinitely for audit trail

Business Rules:

  • Line items must sum to quote totals by category
  • Each quote must have at least one line item (base rent)
  • Sort order determines display sequence in UI/invoices
  • Type 'nightly_rate' should appear first
  • Taxes typically appear last

Entity: Hold​

Purpose: Temporary inventory reservation that prevents double-booking during checkout or manual "pencil hold" workflows. Holds have a TTL and either expire automatically or convert to confirmed bookings.

Key Attributes:

  • id (UUID, primary key)
  • org_id (UUID, foreign key → organizations.id)
  • account_id (UUID, foreign key → accounts.id)
  • space_id (UUID, foreign key → spaces.id)
  • unit_id (UUID, nullable, foreign key → units.id)
  • calendar_id (UUID, foreign key → availability_calendars.id)
  • quote_id (UUID, nullable, foreign key → quotes.id) - Links to pricing snapshot
  • start_at (TIMESTAMP, required) - Check-in date/time
  • end_at (TIMESTAMP, required) - Check-out date/time
  • status (ENUM) - active | expired | confirmed | canceled
  • expires_at (TIMESTAMP, required) - Automatic expiration time (TTL)
  • source (ENUM) - checkout | manual | ops | api
  • created_by (UUID, foreign key → users.id)
  • created_at, updated_at (timestamps)
  • confirmed_at (TIMESTAMP, nullable) - When converted to booking
  • canceled_at (TIMESTAMP, nullable) - Manual cancellation

Relationships:

  • Hold → Org (*, many-to-one)
  • Hold → Account (*, many-to-one)
  • Hold → Space (*, many-to-one)
  • Hold → Unit (*, many-to-one, optional)
  • Hold → AvailabilityCalendar (*, many-to-one)
  • Hold → Quote (*, many-to-one, optional)
  • Hold → Booking (1:1, one-to-one) - After confirmation

Lifecycle:

  • Created: When user enters checkout flow or staff creates manual hold
  • Active: Blocks inventory, preventing overlapping bookings
  • Expired: Automatically after expires_at (typically 15-30 minutes)
  • Confirmed: Converted to Booking, status updated to 'confirmed'
  • Canceled: Manually released by user/staff

Business Rules:

  • end_at must be strictly after start_at
  • Cannot create overlapping holds on same calendar
  • Active holds block booking creation for overlapping periods
  • Expired holds automatically release inventory
  • Confirmed holds cannot be modified or canceled
  • TTL typically 15 minutes for checkout, 24 hours for manual holds
  • Background job cleans up expired holds periodically

Entity: Booking​

Purpose: Authoritative record of a confirmed reservation representing a contractual commitment for a guest to stay at a property. The booking is the single source of truth for operations, finance, and reporting.

Key Attributes:

  • id (UUID, primary key)
  • org_id (UUID, foreign key → organizations.id)
  • account_id (UUID, foreign key → accounts.id)
  • space_id (UUID, foreign key → spaces.id)
  • unit_id (UUID, nullable, foreign key → units.id)
  • quote_id (UUID, nullable, foreign key → quotes.id) - Pricing snapshot
  • hold_id (UUID, nullable, foreign key → holds.id) - Originated from hold
  • confirmation_code (VARCHAR(12), unique) - Guest-facing booking reference
  • state (ENUM) - pending | confirmed | checked_in | completed | canceled
  • source (ENUM) - direct | airbnb | vrbo | booking_com | manual | api
  • channel_ref (VARCHAR(255), nullable) - External booking ID from OTA
  • checkin (DATE, required) - Check-in date
  • checkout (DATE, required) - Check-out date
  • nights (INTEGER, computed) - Number of nights
  • guests (INTEGER, required) - Total guest count
  • adults (INTEGER, nullable) - Adult count
  • children (INTEGER, nullable) - Child count
  • guest_name (VARCHAR(255), required) - Primary guest name
  • guest_email (VARCHAR(255), required) - Primary guest email
  • guest_phone (VARCHAR(50), nullable) - Guest phone number
  • currency (VARCHAR(3), default 'USD')
  • total_price (BIGINT, required) - Final total in minor units
  • paid_amount (BIGINT, default 0) - Amount paid so far
  • refunded_amount (BIGINT, default 0) - Amount refunded
  • special_requests (TEXT, nullable) - Guest notes/requests
  • internal_notes (TEXT, nullable) - Staff-only notes
  • created_by (UUID, foreign key → users.id)
  • created_at, updated_at (timestamps)
  • confirmed_at (TIMESTAMP, nullable) - When booking was confirmed
  • checked_in_at (TIMESTAMP, nullable) - Actual check-in time
  • checked_out_at (TIMESTAMP, nullable) - Actual check-out time
  • canceled_at (TIMESTAMP, nullable) - Cancellation timestamp
  • canceled_by (UUID, nullable, foreign key → users.id)
  • cancellation_reason (TEXT, nullable)

Relationships:

  • Booking → Org (*, many-to-one)
  • Booking → Account (*, many-to-one)
  • Booking → Space (*, many-to-one)
  • Booking → Unit (*, many-to-one, optional)
  • Booking → Quote (*, many-to-one, optional)
  • Booking → Hold (*, many-to-one, optional)
  • Booking → BookingLineItem (1:*, one-to-many)
  • Booking → Payment (1:*, one-to-many) - In Payments domain
  • Booking → Cancellation (1:1, one-to-one, optional)
  • Booking → Block (1:1, one-to-one) - Creates calendar block

Lifecycle:

State Machine:

    pending
       ↓
   confirmed ──→ checked_in ──→ completed
       ↓              ↓
   canceled      canceled
  • pending: Initial state, awaiting payment or manual confirmation
  • confirmed: Payment received or manually confirmed, inventory locked
  • checked_in: Guest has arrived and checked in
  • completed: Guest has checked out, stay is complete
  • canceled: Booking was canceled before or during stay

State Transitions:

  • pending → confirmed (payment received or manual approval)
  • pending → canceled (payment failed or manual cancellation)
  • confirmed → checked_in (guest arrives)
  • confirmed → canceled (cancellation before arrival)
  • checked_in → completed (guest departs)
  • checked_in → canceled (cancellation during stay - rare)

Business Rules:

  • Confirmation code must be unique across all bookings
  • Cannot create overlapping bookings for same unit/space
  • checkout must be strictly after checkin
  • Total must match sum of line items
  • Canceled bookings release inventory immediately
  • Completed bookings trigger payout calculations
  • State transitions must follow state machine rules
  • All state changes must be logged to audit trail
  • Bookings are never hard deleted (soft delete via canceled_at)
  • Guest email must be valid format
  • Nights must equal checkout - checkin

Entity: Booking Line Item (booking_line_items)​

Purpose: Itemized breakdown of charges within a booking, mirroring the quote line items at confirmation time. Provides transparent financial records for rent, fees, taxes, and adjustments.

Key Attributes:

  • id (UUID, primary key)
  • booking_id (UUID, foreign key → bookings.id)
  • type (ENUM) - nightly_rate | cleaning_fee | service_fee | resort_fee | tax | discount | adjustment
  • description (VARCHAR(255)) - Display label
  • category (ENUM) - rent | fee | tax | discount
  • basis (ENUM) - per_stay | per_night | per_guest | percentage
  • quantity (DECIMAL) - Number of units
  • unit_amount (BIGINT) - Price per unit in minor units
  • subtotal (BIGINT) - quantity * unit_amount
  • is_refundable (BOOLEAN, default true) - Whether included in refund calculations
  • sort_order (INTEGER) - Display order
  • metadata (JSONB) - Additional context (rate_plan_id, fee_rule_id)
  • created_at (TIMESTAMP)

Relationships:

  • BookingLineItem → Booking (*, many-to-one)

Lifecycle:

  • Created: When booking is confirmed from quote
  • Immutable: Never modified after creation (adjustments create new line items)
  • Retained: Indefinitely for financial audit compliance

Business Rules:

  • Line items must sum to booking total
  • Each booking must have at least one line item
  • Line items are copied from quote at confirmation time
  • Cannot delete line items (create adjustment line items instead)
  • Refund calculations use only refundable line items
  • Sort order determines invoice display sequence

Entity: Cancellation (cancellations) - V1.0+​

Purpose: Records cancellation requests, policy evaluation, and refund calculations when a booking is canceled. Provides audit trail of cancellation decisions and financial impact.

Key Attributes:

  • id (UUID, primary key)
  • booking_id (UUID, foreign key → bookings.id, unique) - One cancellation per booking
  • org_id (UUID, foreign key → organizations.id)
  • account_id (UUID, foreign key → accounts.id)
  • policy_type (ENUM) - flexible | moderate | strict | non_refundable | custom
  • canceled_at (TIMESTAMP, required) - When cancellation occurred
  • canceled_by (UUID, foreign key → users.id)
  • reason (TEXT, nullable) - Cancellation reason/notes
  • days_before_checkin (INTEGER) - Days from cancellation to check-in
  • original_amount (BIGINT) - Original booking total
  • refund_amount (BIGINT) - Calculated refund amount
  • refund_percentage (DECIMAL) - Percentage refunded (0-100)
  • penalty_amount (BIGINT) - Cancellation penalty/fee
  • policy_rules (JSONB) - Snapshot of policy rules applied
  • refund_issued_at (TIMESTAMP, nullable) - When refund was processed
  • refund_status (ENUM) - pending | processing | completed | failed
  • created_at, updated_at (timestamps)

Relationships:

  • Cancellation → Booking (1:1, one-to-one)
  • Cancellation → Org (*, many-to-one)
  • Cancellation → Account (*, many-to-one)

Lifecycle:

  • Created: When booking is canceled
  • Pending: Awaiting refund processing
  • Processing: Refund initiated with payment processor
  • Completed: Refund successfully issued
  • Failed: Refund processing failed, requires manual intervention

Business Rules:

  • One cancellation record per booking
  • Cancellation policies stored as JSON snapshot (immutable)
  • Refund calculations follow policy rules based on days before check-in
  • Non-refundable bookings have refund_amount = 0
  • Penalty amount typically deducted from refund
  • Canceled bookings immediately release calendar inventory
  • Financial reconciliation links to payment refund records
  • All cancellations must be logged to audit events

Cancellation Policy Types (V1.0+):

  • flexible: Full refund up to 24 hours before check-in
  • moderate: Full refund up to 5 days before, 50% up to 24 hours
  • strict: Full refund up to 14 days before, 50% up to 7 days, no refund after
  • non_refundable: No refunds under any circumstances
  • custom: Policy defined per property/booking with custom rules

Workflows​

Workflow: Quote Generation​

Trigger: User requests pricing for specific dates, space, and occupancy

Steps:

  1. Validate inputs: Ensure checkin < checkout, valid space_id, positive guest count
  2. Check availability: Query availability calendar for conflicts
  3. Load RatePlan: Retrieve active rate plan for space/account
  4. Evaluate rate rules: Calculate nightly rates based on date, length of stay, occupancy
  5. Apply fee rules: Add cleaning fees, service fees, resort fees per rule definitions
  6. Calculate taxes: Apply tax rules based on location and totals
  7. Create Quote record: Store quote with TTL (typically 30 minutes)
  8. Generate line items: Create quote_line_items for each charge
  9. Return quote: Include quote_id, totals, line items, expiration time

Postconditions:

  • Quote record exists with valid_until timestamp
  • Line items sum to quote totals
  • Quote available for hold/booking creation
  • Quote expires automatically after TTL

Error Handling:

  • Date conflicts → Return availability error
  • No rate plan → Return configuration error
  • Invalid input → Return validation error

Workflow: Create Hold (Checkout Flow)​

Trigger: User proceeds to checkout with selected quote

Steps:

  1. Validate quote: Ensure quote exists and not expired
  2. Acquire inventory lock: Use database transaction with row-level lock on calendar
  3. Check availability: Verify no overlapping bookings or holds exist
  4. Create Hold record:
    • Link to quote_id, space_id, calendar_id
    • Set expires_at = now() + 15 minutes
    • Set status = 'active'
  5. Create calendar block: Write to availability calendar (optional, depends on implementation)
  6. Release lock: Commit transaction
  7. Return hold_id: Client can now complete payment

Postconditions:

  • Hold record exists with active status
  • Inventory blocked for 15 minutes
  • Overlapping booking attempts will fail
  • Background job will cleanup if expired

Error Handling:

  • Overlapping hold/booking → Return conflict error, suggest new dates
  • Lock timeout → Return retry error
  • Expired quote → Return validation error

Workflow: Confirm Booking (from Hold)​

Trigger: Payment successful or manual confirmation by staff

Steps:

  1. Validate hold: Ensure hold exists, status='active', not expired
  2. Start transaction: Begin database transaction
  3. Create Booking record:
    • Generate unique confirmation_code (8-12 char alphanumeric)
    • Copy data from quote and hold
    • Set state = 'confirmed'
    • Set confirmed_at = now()
    • Link quote_id, hold_id, space_id
  4. Copy line items: Create booking_line_items from quote_line_items
  5. Update Hold: Set status = 'confirmed', confirmed_at = now()
  6. Create calendar block: Permanent block in availability calendar
  7. Commit transaction
  8. Publish events:
    • booking.created
    • booking.confirmed
    • calendar.updated
  9. Trigger notifications: (future) Send confirmation email to guest
  10. Return booking: Include booking_id, confirmation_code, booking details

Postconditions:

  • Booking record exists with state='confirmed'
  • Inventory permanently blocked for dates
  • Hold converted to booking
  • Calendar block created
  • Guest has confirmation code
  • Events published for downstream processing

Error Handling:

  • Hold expired → Return error, require new quote/hold
  • Payment failed → Keep hold active, return payment error
  • Duplicate confirmation code → Regenerate and retry
  • Transaction failure → Rollback all changes

Workflow: Cancel Booking (V1.0+)​

Trigger: Guest or staff initiates cancellation

Steps:

  1. Validate booking: Ensure booking exists, state in (pending, confirmed, checked_in)
  2. Load cancellation policy: Retrieve policy from rate plan or booking metadata
  3. Calculate refund:
    • Determine days_before_checkin = checkin - today
    • Apply policy rules to determine refund_percentage
    • Calculate refund_amount = original_amount * refund_percentage
    • Calculate penalty_amount = original_amount - refund_amount
  4. Create Cancellation record:
    • Link booking_id
    • Store policy snapshot, refund calculations
    • Set canceled_at = now(), canceled_by = user_id
    • Set refund_status = 'pending'
  5. Update Booking:
    • Set state = 'canceled'
    • Set canceled_at = now(), canceled_by = user_id
    • Store cancellation_reason
  6. Release inventory:
    • Delete/archive calendar block
    • Update availability calendar
  7. Initiate refund: (if refund_amount > 0)
    • Create refund record in Payments domain
    • Update cancellation.refund_status = 'processing'
  8. Publish events:
    • booking.canceled
    • calendar.updated
    • refund.initiated (if applicable)
  9. Trigger notifications: (future) Send cancellation confirmation email

Postconditions:

  • Booking state = 'canceled'
  • Cancellation record exists
  • Inventory released and available for new bookings
  • Refund initiated (if applicable)
  • Events published for downstream processing

Error Handling:

  • Booking already canceled → Return error
  • Invalid state transition → Return error
  • Refund processing failure → Set refund_status='failed', alert ops team
  • Policy evaluation error → Use default policy or manual review

Workflow: Check-In Guest​

Trigger: Guest arrives at property, staff initiates check-in

Steps:

  1. Validate booking: Ensure booking exists, state='confirmed'
  2. Verify dates: Confirm today is on or near checkin date
  3. Update Booking:
    • Set state = 'checked_in'
    • Set checked_in_at = now()
  4. Publish events:
    • booking.checked_in
  5. Trigger notifications: (future) Welcome message, property instructions

Postconditions:

  • Booking state = 'checked_in'
  • Guest is officially occupying property
  • Operations team aware of active stay

Workflow: Complete Stay​

Trigger: Guest departs, checkout date reached, or manual completion by staff

Steps:

  1. Validate booking: Ensure booking exists, state='checked_in'
  2. Verify dates: Confirm today is on or after checkout date
  3. Update Booking:
    • Set state = 'completed'
    • Set checked_out_at = now()
  4. Publish events:
    • booking.completed
    • booking.ready_for_reconciliation
  5. Trigger financial processes:
    • Calculate final payouts per revenue rules
    • Create payout records in Payments domain
  6. Trigger notifications: (future) Thank you email, review request

Postconditions:

  • Booking state = 'completed'
  • Property available for next booking
  • Payout calculations initiated
  • Financial reconciliation can proceed

Business Rules​

Quote Rules​

  1. Expiration: Quotes expire after 15-30 minutes (configurable per org)
  2. Immutability: Quotes cannot be modified once created (create new quote instead)
  3. Validation: All quote inputs must pass validation (dates, capacity, etc.)
  4. Totals: Line items must sum to quote totals: total = subtotal + fees_total + taxes_total
  5. Availability: Cannot create quote for unavailable date ranges

Hold Rules​

  1. TTL: Checkout holds expire after 15 minutes; manual holds after 24 hours
  2. Exclusivity: No overlapping active holds allowed on same calendar
  3. Conversion: Holds must be converted within expiration window
  4. Cleanup: Background job removes expired holds every 5 minutes
  5. Precedence: Active holds block booking creation but not quote generation

Booking Rules​

  1. Uniqueness: Confirmation codes must be globally unique
  2. State machine: Only valid state transitions allowed (enforced in application layer)
  3. Immutability: Core booking fields immutable after confirmation (create modification records instead)
  4. Soft delete: Bookings never hard deleted (use canceled_at timestamp)
  5. Audit trail: All state changes logged to audit_events table
  6. Inventory: Confirmed bookings block calendar exclusively
  7. Financial: Total must reconcile with line items
  8. Modification: V1.0+ supports amendments via booking_modifications table

Cancellation Rules (V1.0+)​

  1. Policy enforcement: Refund calculations strictly follow policy rules
  2. Timing: Days before check-in determines refund percentage
  3. One per booking: Only one cancellation record per booking
  4. Immutability: Cancellation records never modified after creation
  5. Inventory: Canceled bookings immediately release calendar inventory
  6. Refunds: Refund processing handled in Payments domain
  7. Audit: All cancellations logged to audit_events with full context

General Rules​

  1. Tenancy: All queries must filter by org_id (enforced via RLS or application layer)
  2. Authorization: Permission checks via Membership and Role system
  3. Timestamps: All records include created_at and updated_at
  4. Currency: All amounts stored in minor units (cents) for precision
  5. Date ranges: Always half-open intervals [checkin, checkout)
  6. Events: Major lifecycle transitions publish events to event bus

Implementation Notes​

MVP.0 Implementation (Out of Scope)​

Status: Bookings domain not implemented

Scope:

  • No booking creation capability
  • No quote generation
  • Platform focuses on supply management only
  • External bookings tracked informally if needed

Rationale:

  • MVP.0 focused on foundation: tenancy, authorization, supply, content
  • Booking engine deferred to reduce initial scope
  • Channel integrations require bookings, so both deferred together

MVP.1 Implementation (Read-Only Booking Awareness)​

Status: Passive booking awareness from channels

Included:

  • Read-only bookings table for imported bookings
  • Basic fields: confirmation_code, space_id, checkin, checkout, guest info
  • Bookings imported from iCal feeds as calendar blocks
  • Display booked dates on calendar UI
  • No quote, hold, or booking creation capability

Deferred:

  • Quote generation engine
  • Hold management
  • Booking creation/confirmation workflow
  • Cancellation processing
  • Line item tracking
  • State machine transitions

Database Schema (MVP.1):

-- Minimal bookings table for channel imports
CREATE TABLE bookings (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id UUID NOT NULL REFERENCES organizations(id),
  account_id UUID NOT NULL REFERENCES accounts(id),
  space_id UUID NOT NULL REFERENCES spaces(id),
  confirmation_code VARCHAR(50),
  source VARCHAR(50) NOT NULL, -- 'airbnb', 'vrbo', etc.
  channel_ref VARCHAR(255), -- External booking ID
  checkin DATE NOT NULL,
  checkout DATE NOT NULL,
  nights INTEGER GENERATED ALWAYS AS (checkout - checkin) STORED,
  guests INTEGER,
  guest_name VARCHAR(255),
  guest_email VARCHAR(255),
  state VARCHAR(50) DEFAULT 'confirmed',
  total_price BIGINT,
  currency VARCHAR(3) DEFAULT 'USD',
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_bookings_org_space ON bookings(org_id, space_id);
CREATE INDEX idx_bookings_dates ON bookings(checkin, checkout);
CREATE INDEX idx_bookings_confirmation ON bookings(confirmation_code) WHERE confirmation_code IS NOT NULL;

Operational Notes:

  • Bookings imported from iCal feeds (via VEVENT parsing)
  • Create matching block in availability_calendar
  • Display on calendar UI as "Booked" periods
  • No editing or cancellation from TVL platform
  • Guests manage bookings via original OTA

V1.0 Implementation (Full Booking Engine)​

Status: Complete booking lifecycle with quotes, holds, confirmations

Included:

  • ✅ Full quotes table with line items
  • ✅ Full quote_line_items table
  • ✅ Full holds table with TTL and expiration
  • ✅ Full bookings table with state machine
  • ✅ Full booking_line_items table
  • ✅ Full cancellations table with policy engine
  • ✅ Quote generation from rate plans
  • ✅ Hold creation during checkout
  • ✅ Booking confirmation workflow
  • ✅ State machine transitions (pending → confirmed → checked_in → completed)
  • ✅ Cancellation processing with refund calculations
  • ✅ Integration with availability calendar (create/delete blocks)
  • ✅ Integration with payments domain (link payments to bookings)
  • ✅ Event publishing for downstream consumers
  • ✅ Confirmation code generation
  • ✅ Background job for hold expiration cleanup

Deferred to V1.1+:

  • Booking modifications (date changes, upgrades)
  • Multi-unit bookings
  • Group bookings (multiple spaces)
  • Guest profiles and history
  • Automated communication/notifications
  • Advanced cancellation policies (pro-rated, custom rules)

Database Indexes (V1.0):

-- Quotes
CREATE INDEX idx_quotes_org_space ON quotes(org_id, space_id);
CREATE INDEX idx_quotes_valid_until ON quotes(valid_until) WHERE valid_until > NOW();
CREATE INDEX idx_quote_line_items_quote ON quote_line_items(quote_id);

-- Holds
CREATE INDEX idx_holds_calendar ON holds(calendar_id, start_at, end_at);
CREATE INDEX idx_holds_expires_at ON holds(expires_at) WHERE status = 'active';
CREATE INDEX idx_holds_status ON holds(status) WHERE status = 'active';

-- Bookings
CREATE INDEX idx_bookings_org_account ON bookings(org_id, account_id);
CREATE INDEX idx_bookings_space ON bookings(space_id, checkin, checkout);
CREATE INDEX idx_bookings_confirmation ON bookings(confirmation_code);
CREATE INDEX idx_bookings_state ON bookings(state) WHERE state != 'canceled';
CREATE INDEX idx_bookings_dates ON bookings(checkin, checkout);
CREATE INDEX idx_booking_line_items_booking ON booking_line_items(booking_id);

-- Cancellations
CREATE UNIQUE INDEX idx_cancellations_booking ON cancellations(booking_id);
CREATE INDEX idx_cancellations_refund_status ON cancellations(refund_status) WHERE refund_status IN ('pending', 'processing');

Constraints (V1.0):

-- Quotes
ALTER TABLE quotes ADD CONSTRAINT quotes_checkout_after_checkin CHECK (checkout > checkin);
ALTER TABLE quotes ADD CONSTRAINT quotes_guests_positive CHECK (guests > 0);
ALTER TABLE quotes ADD CONSTRAINT quotes_nights_match CHECK (nights = (checkout - checkin));

-- Holds
ALTER TABLE holds ADD CONSTRAINT holds_end_after_start CHECK (end_at > start_at);
ALTER TABLE holds ADD CONSTRAINT holds_expires_in_future CHECK (expires_at > created_at);

-- Bookings
ALTER TABLE bookings ADD CONSTRAINT bookings_checkout_after_checkin CHECK (checkout > checkin);
ALTER TABLE bookings ADD CONSTRAINT bookings_guests_positive CHECK (guests > 0);
ALTER TABLE bookings ADD CONSTRAINT bookings_nights_match CHECK (nights = (checkout - checkin));
ALTER TABLE bookings ADD CONSTRAINT bookings_confirmation_code_format CHECK (confirmation_code ~ '^[A-Z0-9]{8,12}$');

-- Cancellations
ALTER TABLE cancellations ADD CONSTRAINT cancellations_refund_positive CHECK (refund_amount >= 0);
ALTER TABLE cancellations ADD CONSTRAINT cancellations_penalty_positive CHECK (penalty_amount >= 0);
ALTER TABLE cancellations ADD CONSTRAINT cancellations_refund_percentage_range CHECK (refund_percentage BETWEEN 0 AND 100);

Operational Notes:

  • Use database transactions with row-level locks for hold/booking creation
  • Background job runs every 5 minutes to cleanup expired holds
  • State machine transitions enforced in application layer with validation
  • All state changes logged to audit_events table
  • Booking confirmation generates unique 8-12 char alphanumeric code
  • Events published asynchronously via message queue
  • Cancellation refunds coordinated with Payments domain
  • Foreign key cascades handled carefully (soft deletes preferred)

Future Enhancements​

V1.1: Booking Modifications​

  • Date changes (extend/shorten stay)
  • Guest count changes
  • Room/unit upgrades
  • Partial cancellations (multi-unit bookings)
  • Amendment audit trail via booking_modifications table
  • Repricing for modifications

V1.2: Multi-Unit Bookings​

  • Book multiple units in single transaction
  • Parent-child booking structure
  • Partial fulfillment (some units confirmed, others pending)
  • Group pricing discounts

V1.3: Advanced Cancellations​

  • Pro-rated refunds based on % of stay completed
  • Custom cancellation policies per property
  • Cancellation reason taxonomy
  • Cancellation analytics and forecasting

V2.0: Guest Profiles & History​

  • Centralized guest identity
  • Booking history across properties
  • Guest preferences and special needs
  • Loyalty/repeat guest recognition
  • Guest reviews and ratings

V2.1: Automated Communication​

  • Confirmation emails with property details
  • Pre-arrival instructions and check-in info
  • Mid-stay check-in messages
  • Post-stay thank you and review requests
  • Cancellation and refund notifications

V2.2: Channel Bidirectional Sync​

  • Push confirmed bookings to OTAs
  • Receive booking updates via webhooks
  • Automatic calendar blocking on all channels
  • Conflict resolution for double-bookings

V2.3: Smart Booking Engine​

  • ML-based pricing recommendations
  • Dynamic availability rules (min stay, gap filling)
  • Overbooking risk management
  • Demand forecasting

Physical Schema​

See 001_initial_schema.sql for complete CREATE TABLE statements.

Summary:

  • 5 tables: quotes, quote_line_items, holds, bookings, booking_line_items, cancellations
  • 20+ indexes for query performance
  • 15+ constraints for data integrity
  • Foreign keys to: organizations, accounts, spaces, units, rate_plans, availability_calendars
  • Soft delete pattern (canceled_at timestamps, no hard deletes)