Database Design

This document describes the database architecture and schema design for HMP.

Database Overview

HMP uses PostgreSQL as the primary database with a schema-per-tenant multi-tenancy model.

Multi-Tenancy Model

Each property has its own database schema:

-- Create schema for new property
CREATE SCHEMA property_htl001;

-- All tables are created within the schema
CREATE TABLE property_htl001.rooms (...);
CREATE TABLE property_htl001.reservations (...);

Core Tables

Properties

CREATE TABLE public.properties (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    code VARCHAR(20) UNIQUE NOT NULL,
    name VARCHAR(200) NOT NULL,
    schema_name VARCHAR(50) NOT NULL,
    timezone VARCHAR(50) DEFAULT 'UTC',
    currency VARCHAR(3) DEFAULT 'USD',
    status VARCHAR(20) DEFAULT 'ACTIVE',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

Room Types

CREATE TABLE room_types (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    code VARCHAR(20) NOT NULL,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    max_occupancy INTEGER DEFAULT 2,
    max_adults INTEGER DEFAULT 2,
    max_children INTEGER DEFAULT 0,
    base_rate DECIMAL(10,2),
    sort_order INTEGER DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(code)
);

Rooms

CREATE TABLE rooms (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    room_number VARCHAR(20) NOT NULL,
    room_type_id UUID REFERENCES room_types(id),
    floor VARCHAR(10),
    building VARCHAR(50),
    status VARCHAR(20) DEFAULT 'VACANT_CLEAN',
    fo_status VARCHAR(20) DEFAULT 'VACANT',
    hk_status VARCHAR(20) DEFAULT 'CLEAN',
    is_active BOOLEAN DEFAULT TRUE,
    features JSONB DEFAULT '[]',
    notes TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(room_number)
);

-- Room status values
-- fo_status: VACANT, OCCUPIED, OUT_OF_ORDER, OUT_OF_SERVICE
-- hk_status: CLEAN, DIRTY, INSPECTED, OUT_OF_ORDER

Guests

CREATE TABLE guests (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255),
    phone VARCHAR(50),
    nationality VARCHAR(3),
    id_type VARCHAR(20),
    id_number VARCHAR(50),
    date_of_birth DATE,
    gender VARCHAR(10),
    address JSONB,
    preferences JSONB DEFAULT '{}',
    vip_status VARCHAR(20),
    loyalty_number VARCHAR(50),
    total_stays INTEGER DEFAULT 0,
    total_revenue DECIMAL(12,2) DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_guests_email ON guests(email);
CREATE INDEX idx_guests_phone ON guests(phone);
CREATE INDEX idx_guests_name ON guests(last_name, first_name);

Reservations

CREATE TABLE reservations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    confirmation_number VARCHAR(30) UNIQUE NOT NULL,
    guest_id UUID REFERENCES guests(id),
    status VARCHAR(20) DEFAULT 'CONFIRMED',
    source VARCHAR(30) DEFAULT 'DIRECT',
    channel VARCHAR(50),
    arrival_date DATE NOT NULL,
    departure_date DATE NOT NULL,
    nights INTEGER GENERATED ALWAYS AS (departure_date - arrival_date) STORED,
    adults INTEGER DEFAULT 1,
    children INTEGER DEFAULT 0,
    special_requests TEXT,
    internal_notes TEXT,
    total_amount DECIMAL(12,2) DEFAULT 0,
    paid_amount DECIMAL(12,2) DEFAULT 0,
    balance DECIMAL(12,2) GENERATED ALWAYS AS (total_amount - paid_amount) STORED,
    cancellation_reason TEXT,
    cancelled_at TIMESTAMPTZ,
    created_by UUID,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Reservation status values:
-- PENDING, CONFIRMED, CHECKED_IN, CHECKED_OUT, CANCELLED, NO_SHOW

CREATE INDEX idx_reservations_dates ON reservations(arrival_date, departure_date);
CREATE INDEX idx_reservations_status ON reservations(status);
CREATE INDEX idx_reservations_guest ON reservations(guest_id);

Reservation Rooms

CREATE TABLE reservation_rooms (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    reservation_id UUID REFERENCES reservations(id) ON DELETE CASCADE,
    room_type_id UUID REFERENCES room_types(id),
    rate_plan_id UUID REFERENCES rate_plans(id),
    room_id UUID REFERENCES rooms(id),
    adults INTEGER DEFAULT 1,
    children INTEGER DEFAULT 0,
    rate_amount DECIMAL(10,2),
    status VARCHAR(20) DEFAULT 'RESERVED',
    check_in_time TIMESTAMPTZ,
    check_out_time TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

Folios

CREATE TABLE folios (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    reservation_room_id UUID REFERENCES reservation_rooms(id),
    folio_number VARCHAR(30) UNIQUE NOT NULL,
    guest_id UUID REFERENCES guests(id),
    status VARCHAR(20) DEFAULT 'OPEN',
    total_charges DECIMAL(12,2) DEFAULT 0,
    total_payments DECIMAL(12,2) DEFAULT 0,
    balance DECIMAL(12,2) GENERATED ALWAYS AS (total_charges - total_payments) STORED,
    closed_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE folio_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    folio_id UUID REFERENCES folios(id) ON DELETE CASCADE,
    item_type VARCHAR(20) NOT NULL, -- CHARGE, PAYMENT, ADJUSTMENT
    transaction_code VARCHAR(20),
    description VARCHAR(255),
    amount DECIMAL(10,2) NOT NULL,
    tax_amount DECIMAL(10,2) DEFAULT 0,
    reference VARCHAR(100),
    posted_by UUID,
    posted_at TIMESTAMPTZ DEFAULT NOW(),
    reversed BOOLEAN DEFAULT FALSE,
    reversed_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

Rate Management Tables

Rate Plans

CREATE TABLE rate_plans (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    code VARCHAR(20) NOT NULL,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    rate_type VARCHAR(20) DEFAULT 'PUBLIC',
    meal_plan VARCHAR(20) DEFAULT 'EP',
    cancellation_policy_id UUID,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(code)
);

Rates

CREATE TABLE rates (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    room_type_id UUID REFERENCES room_types(id),
    rate_plan_id UUID REFERENCES rate_plans(id),
    date DATE NOT NULL,
    rate_amount DECIMAL(10,2) NOT NULL,
    single_rate DECIMAL(10,2),
    extra_adult DECIMAL(10,2) DEFAULT 0,
    extra_child DECIMAL(10,2) DEFAULT 0,
    min_stay INTEGER DEFAULT 1,
    closed BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(room_type_id, rate_plan_id, date)
);

CREATE INDEX idx_rates_date ON rates(date);

Indexing Strategy

Primary Indexes

All primary keys use UUID with B-tree indexes
Unique constraints on business identifiers

Search Indexes

-- Full-text search on guest names
CREATE INDEX idx_guests_search ON guests
USING GIN (to_tsvector('english', first_name || ' ' || last_name));

-- Date range queries
CREATE INDEX idx_reservations_arrival ON reservations(arrival_date);
CREATE INDEX idx_rates_lookup ON rates(room_type_id, rate_plan_id, date);

Data Retention

-- Archive old reservations
CREATE TABLE reservations_archive (LIKE reservations INCLUDING ALL);

-- Move old data periodically
INSERT INTO reservations_archive
SELECT * FROM reservations
WHERE departure_date < NOW() - INTERVAL '2 years';

Database Overview​

Multi-Tenancy Model​

Core Tables​

Properties​

Room Types​

Rooms​

Guests​

Reservations​

Reservation Rooms​

Folios​

Rate Management Tables​

Rate Plans​

Rates​

Indexing Strategy​

Primary Indexes​

Search Indexes​

Data Retention​

Related Documentation​