Skip to main content

Database Schema Architecture

This document details the database schema design for the Stayzr Hotel Management System, focusing on multi-tenant data isolation, performance optimization, and scalability.

🗄️ Database Overview​

The platform uses PostgreSQL 15+ as the primary database with a single-database, multi-tenant approach that ensures data isolation while maintaining operational efficiency.

Key Design Principles​

  • Multi-tenant isolation with organization-based row-level security
  • Normalized schema with efficient relationships
  • Audit trails for compliance and data integrity
  • Scalable indexing for performance optimization
  • Data encryption for sensitive information

🏗️ Schema Architecture​

Multi-Tenant Foundation​

-- Core tenant entity
CREATE TABLE organizations (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    tenant_id VARCHAR(100) UNIQUE NOT NULL,
    
    -- Subscription details
    subscription_tier subscription_tier_enum NOT NULL DEFAULT 'starter',
    subscription_status subscription_status_enum NOT NULL DEFAULT 'trial',
    subscription_start_date TIMESTAMP WITH TIME ZONE,
    subscription_end_date TIMESTAMP WITH TIME ZONE,
    
    -- Contact information
    primary_email VARCHAR(255),
    primary_phone VARCHAR(50),
    website_url VARCHAR(255),
    
    -- Address
    street_address TEXT,
    city VARCHAR(100),
    state_province VARCHAR(100),
    postal_code VARCHAR(20),
    country VARCHAR(100),
    timezone VARCHAR(50) DEFAULT 'UTC',
    
    -- Business details
    business_registration_number VARCHAR(100),
    tax_identification_number VARCHAR(100),
    industry VARCHAR(100) DEFAULT 'hospitality',
    
    -- Platform settings
    default_language VARCHAR(10) DEFAULT 'en',
    default_currency VARCHAR(3) DEFAULT 'USD',
    date_format VARCHAR(20) DEFAULT 'YYYY-MM-DD',
    
    -- Status and metadata
    is_active BOOLEAN NOT NULL DEFAULT true,
    onboarding_completed BOOLEAN NOT NULL DEFAULT false,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    deleted_at TIMESTAMP WITH TIME ZONE
);

-- Subscription tier enumeration
CREATE TYPE subscription_tier_enum AS ENUM (
    'starter',
    'professional', 
    'enterprise',
    'custom'
);

-- Subscription status enumeration
CREATE TYPE subscription_status_enum AS ENUM (
    'trial',
    'active',
    'past_due',
    'cancelled',
    'suspended'
);

👥 User Management Schema​

Core User Entity​

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    organization_id INTEGER NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    
    -- Authentication
    email VARCHAR(255) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    email_verified BOOLEAN NOT NULL DEFAULT false,
    email_verification_token VARCHAR(255),
    
    -- Profile information
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    display_name VARCHAR(200),
    avatar_url VARCHAR(500),
    phone_number VARCHAR(50),
    
    -- System access
    is_active BOOLEAN NOT NULL DEFAULT true,
    is_super_admin BOOLEAN NOT NULL DEFAULT false,
    current_organization_id INTEGER REFERENCES organizations(id),
    last_login_at TIMESTAMP WITH TIME ZONE,
    last_active_at TIMESTAMP WITH TIME ZONE,
    
    -- Security
    password_reset_token VARCHAR(255),
    password_reset_expires_at TIMESTAMP WITH TIME ZONE,
    failed_login_attempts INTEGER DEFAULT 0,
    locked_until TIMESTAMP WITH TIME ZONE,
    
    -- Multi-factor authentication
    mfa_enabled BOOLEAN NOT NULL DEFAULT false,
    mfa_secret VARCHAR(255),
    mfa_backup_codes TEXT[],
    
    -- Preferences
    language VARCHAR(10) DEFAULT 'en',
    timezone VARCHAR(50),
    notification_preferences JSONB DEFAULT '{}',
    
    -- Metadata
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    deleted_at TIMESTAMP WITH TIME ZONE,
    
    -- Constraints
    UNIQUE(organization_id, email),
    CONSTRAINT email_format CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
);

-- User roles and permissions
CREATE TABLE roles (
    id SERIAL PRIMARY KEY,
    organization_id INTEGER NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    
    name VARCHAR(100) NOT NULL,
    description TEXT,
    permissions TEXT[] NOT NULL DEFAULT '{}',
    is_system_role BOOLEAN NOT NULL DEFAULT false,
    
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    UNIQUE(organization_id, name)
);

CREATE TABLE user_roles (
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role_id INTEGER NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
    assigned_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    assigned_by INTEGER REFERENCES users(id),
    
    PRIMARY KEY (user_id, role_id)
);

🏨 Hotel Core Schema​

Property and Room Management​

-- Hotel properties
CREATE TABLE properties (
    id SERIAL PRIMARY KEY,
    organization_id INTEGER NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    
    -- Basic information
    name VARCHAR(255) NOT NULL,
    description TEXT,
    property_type property_type_enum NOT NULL DEFAULT 'hotel',
    star_rating INTEGER CHECK (star_rating >= 1 AND star_rating <= 5),
    
    -- Contact and location
    email VARCHAR(255),
    phone VARCHAR(50),
    website VARCHAR(255),
    
    -- Address
    street_address TEXT NOT NULL,
    city VARCHAR(100) NOT NULL,
    state_province VARCHAR(100),
    postal_code VARCHAR(20),
    country VARCHAR(100) NOT NULL,
    latitude DECIMAL(10, 8),
    longitude DECIMAL(11, 8),
    
    -- Operational details
    check_in_time TIME DEFAULT '15:00:00',
    check_out_time TIME DEFAULT '11:00:00',
    timezone VARCHAR(50) NOT NULL,
    
    -- Amenities and features
    amenities TEXT[],
    policies JSONB DEFAULT '{}',
    
    -- Status
    is_active BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    UNIQUE(organization_id, name)
);

CREATE TYPE property_type_enum AS ENUM (
    'hotel',
    'resort',
    'motel',
    'bed_and_breakfast',
    'vacation_rental',
    'boutique_hotel',
    'business_hotel',
    'extended_stay'
);

-- Room types
CREATE TABLE room_types (
    id SERIAL PRIMARY KEY,
    organization_id INTEGER NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    property_id INTEGER NOT NULL REFERENCES properties(id) ON DELETE CASCADE,
    
    -- Basic information
    name VARCHAR(255) NOT NULL,
    description TEXT,
    short_description VARCHAR(500),
    
    -- Capacity and configuration
    max_occupancy INTEGER NOT NULL CHECK (max_occupancy > 0),
    max_adults INTEGER NOT NULL CHECK (max_adults > 0),
    max_children INTEGER DEFAULT 0,
    bed_configuration VARCHAR(255),
    room_size_sqft INTEGER,
    room_size_sqm INTEGER,
    
    -- Amenities and features
    amenities TEXT[],
    features JSONB DEFAULT '{}',
    
    -- Pricing
    base_rate DECIMAL(10, 2) NOT NULL CHECK (base_rate >= 0),
    currency VARCHAR(3) DEFAULT 'USD',
    
    -- Media
    images JSONB DEFAULT '[]',
    virtual_tour_url VARCHAR(500),
    
    -- Status
    is_active BOOLEAN NOT NULL DEFAULT true,
    sort_order INTEGER DEFAULT 0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    UNIQUE(organization_id, property_id, name)
);

-- Individual rooms
CREATE TABLE rooms (
    id SERIAL PRIMARY KEY,
    organization_id INTEGER NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    property_id INTEGER NOT NULL REFERENCES properties(id) ON DELETE CASCADE,
    room_type_id INTEGER NOT NULL REFERENCES room_types(id) ON DELETE CASCADE,
    
    -- Room identification
    room_number VARCHAR(50) NOT NULL,
    floor_number INTEGER,
    building VARCHAR(100),
    
    -- Status and condition
    status room_status_enum NOT NULL DEFAULT 'available',
    condition room_condition_enum NOT NULL DEFAULT 'clean',
    last_cleaned_at TIMESTAMP WITH TIME ZONE,
    
    -- Special features
    is_accessible BOOLEAN NOT NULL DEFAULT false,
    is_smoking_allowed BOOLEAN NOT NULL DEFAULT false,
    special_features TEXT[],
    notes TEXT,
    
    -- Maintenance
    last_maintenance_date DATE,
    next_maintenance_date DATE,
    maintenance_notes TEXT,
    
    -- Status
    is_active BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    UNIQUE(organization_id, property_id, room_number)
);

CREATE TYPE room_status_enum AS ENUM (
    'available',
    'occupied',
    'reserved',
    'out_of_order',
    'maintenance',
    'cleaning'
);

CREATE TYPE room_condition_enum AS ENUM (
    'clean',
    'dirty',
    'needs_inspection',
    'maintenance_required'
);

👤 Guest Management Schema​

Guest Profiles and Preferences​

-- Guest profiles
CREATE TABLE guests (
    id SERIAL PRIMARY KEY,
    organization_id INTEGER NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    
    -- Personal information
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    middle_name VARCHAR(100),
    preferred_name VARCHAR(100),
    title VARCHAR(50),
    
    -- Contact information
    email VARCHAR(255),
    phone_primary VARCHAR(50),
    phone_secondary VARCHAR(50),
    
    -- Demographics
    date_of_birth DATE,
    gender gender_enum,
    nationality VARCHAR(100),
    language_preference VARCHAR(10) DEFAULT 'en',
    
    -- Address
    street_address TEXT,
    city VARCHAR(100),
    state_province VARCHAR(100),
    postal_code VARCHAR(20),
    country VARCHAR(100),
    
    -- Identity documents
    passport_number VARCHAR(50),
    passport_country VARCHAR(100),
    passport_expiry_date DATE,
    national_id VARCHAR(100),
    driver_license VARCHAR(100),
    
    -- Guest preferences
    room_preferences JSONB DEFAULT '{}',
    dining_preferences JSONB DEFAULT '{}',
    accessibility_needs TEXT[],
    special_requests TEXT,
    allergies TEXT[],
    
    -- Communication preferences
    preferred_contact_method contact_method_enum DEFAULT 'email',
    marketing_opt_in BOOLEAN NOT NULL DEFAULT false,
    sms_opt_in BOOLEAN NOT NULL DEFAULT false,
    
    -- Guest history and loyalty
    total_stays INTEGER DEFAULT 0,
    total_nights INTEGER DEFAULT 0,
    total_revenue DECIMAL(12, 2) DEFAULT 0,
    first_stay_date DATE,
    last_stay_date DATE,
    loyalty_tier loyalty_tier_enum DEFAULT 'standard',
    loyalty_points INTEGER DEFAULT 0,
    
    -- Privacy and compliance
    data_processing_consent BOOLEAN NOT NULL DEFAULT false,
    data_processing_consent_date TIMESTAMP WITH TIME ZONE,
    gdpr_consent BOOLEAN NOT NULL DEFAULT false,
    
    -- Status and metadata
    is_active BOOLEAN NOT NULL DEFAULT true,
    is_vip BOOLEAN NOT NULL DEFAULT false,
    blacklisted BOOLEAN NOT NULL DEFAULT false,
    blacklist_reason TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    deleted_at TIMESTAMP WITH TIME ZONE,
    
    -- Constraints
    UNIQUE(organization_id, email),
    CONSTRAINT email_format CHECK (email IS NULL OR email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
);

CREATE TYPE gender_enum AS ENUM ('male', 'female', 'non_binary', 'prefer_not_to_say');
CREATE TYPE contact_method_enum AS ENUM ('email', 'phone', 'sms', 'app_notification');
CREATE TYPE loyalty_tier_enum AS ENUM ('standard', 'silver', 'gold', 'platinum', 'diamond');

-- Guest companions for group bookings
CREATE TABLE guest_companions (
    id SERIAL PRIMARY KEY,
    organization_id INTEGER NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    primary_guest_id INTEGER NOT NULL REFERENCES guests(id) ON DELETE CASCADE,
    
    -- Companion information
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    relationship VARCHAR(100),
    date_of_birth DATE,
    
    -- Special needs
    accessibility_needs TEXT[],
    allergies TEXT[],
    
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

📅 Reservation Management Schema​

Bookings and Availability​

-- Reservations
CREATE TABLE reservations (
    id SERIAL PRIMARY KEY,
    organization_id INTEGER NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    property_id INTEGER NOT NULL REFERENCES properties(id) ON DELETE CASCADE,
    guest_id INTEGER NOT NULL REFERENCES guests(id) ON DELETE CASCADE,
    room_type_id INTEGER NOT NULL REFERENCES room_types(id) ON DELETE CASCADE,
    room_id INTEGER REFERENCES rooms(id),
    
    -- Reservation identification
    confirmation_number VARCHAR(50) UNIQUE NOT NULL,
    external_booking_id VARCHAR(100),
    booking_source booking_source_enum NOT NULL DEFAULT 'direct',
    
    -- Dates and duration
    check_in_date DATE NOT NULL,
    check_out_date DATE NOT NULL,
    nights INTEGER GENERATED ALWAYS AS (check_out_date - check_in_date) STORED,
    
    -- Guest details
    number_of_adults INTEGER NOT NULL DEFAULT 1 CHECK (number_of_adults > 0),
    number_of_children INTEGER NOT NULL DEFAULT 0 CHECK (number_of_children >= 0),
    number_of_infants INTEGER NOT NULL DEFAULT 0 CHECK (number_of_infants >= 0),
    
    -- Pricing
    room_rate DECIMAL(10, 2) NOT NULL CHECK (room_rate >= 0),
    total_amount DECIMAL(12, 2) NOT NULL CHECK (total_amount >= 0),
    taxes_amount DECIMAL(10, 2) DEFAULT 0,
    fees_amount DECIMAL(10, 2) DEFAULT 0,
    currency VARCHAR(3) DEFAULT 'USD',
    
    -- Payment status
    payment_status payment_status_enum NOT NULL DEFAULT 'pending',
    deposit_amount DECIMAL(10, 2) DEFAULT 0,
    deposit_paid BOOLEAN NOT NULL DEFAULT false,
    balance_due DECIMAL(10, 2) DEFAULT 0,
    
    -- Reservation status
    status reservation_status_enum NOT NULL DEFAULT 'confirmed',
    cancellation_reason TEXT,
    cancelled_at TIMESTAMP WITH TIME ZONE,
    cancelled_by INTEGER REFERENCES users(id),
    
    -- Special requests and notes
    special_requests TEXT,
    internal_notes TEXT,
    guest_notes TEXT,
    
    -- Check-in/out details
    checked_in_at TIMESTAMP WITH TIME ZONE,
    checked_out_at TIMESTAMP WITH TIME ZONE,
    early_checkin_requested BOOLEAN DEFAULT false,
    late_checkout_requested BOOLEAN DEFAULT false,
    
    -- Package and add-ons
    package_inclusions JSONB DEFAULT '[]',
    add_ons JSONB DEFAULT '[]',
    
    -- Marketing and source
    promotional_code VARCHAR(100),
    discount_amount DECIMAL(10, 2) DEFAULT 0,
    marketing_source VARCHAR(255),
    referral_code VARCHAR(100),
    
    -- Policies
    cancellation_policy_id INTEGER,
    no_show_policy_id INTEGER,
    
    -- Metadata
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    -- Constraints
    CHECK (check_out_date > check_in_date),
    CHECK (total_amount >= 0),
    CHECK (number_of_adults + number_of_children <= 20)
);

CREATE TYPE booking_source_enum AS ENUM (
    'direct',
    'booking_com',
    'expedia',
    'airbnb',
    'agoda',
    'hotels_com',
    'tripadvisor',
    'phone',
    'walk_in',
    'corporate',
    'other'
);

CREATE TYPE payment_status_enum AS ENUM (
    'pending',
    'partial',
    'paid',
    'refunded',
    'failed',
    'disputed'
);

CREATE TYPE reservation_status_enum AS ENUM (
    'confirmed',
    'checked_in',
    'checked_out',
    'cancelled',
    'no_show',
    'modified'
);

-- Room assignments (for tracking room changes)
CREATE TABLE room_assignments (
    id SERIAL PRIMARY KEY,
    organization_id INTEGER NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    reservation_id INTEGER NOT NULL REFERENCES reservations(id) ON DELETE CASCADE,
    room_id INTEGER NOT NULL REFERENCES rooms(id) ON DELETE CASCADE,
    
    assigned_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    assigned_by INTEGER REFERENCES users(id),
    reason VARCHAR(255),
    
    -- Date range for the assignment
    from_date DATE NOT NULL,
    to_date DATE NOT NULL,
    
    is_active BOOLEAN NOT NULL DEFAULT true
);

🛎️ Service Management Schema​

Staff and Departments​

-- Departments
CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    organization_id INTEGER NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    
    name VARCHAR(100) NOT NULL,
    description TEXT,
    head_of_department INTEGER REFERENCES users(id),
    
    -- Operating hours
    operating_hours JSONB DEFAULT '{}',
    contact_email VARCHAR(255),
    contact_phone VARCHAR(50),
    
    -- Budget and costs
    budget_allocation DECIMAL(12, 2),
    cost_center_code VARCHAR(50),
    
    is_active BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    UNIQUE(organization_id, name)
);

-- Staff management
CREATE TABLE staff (
    id SERIAL PRIMARY KEY,
    organization_id INTEGER NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    department_id INTEGER REFERENCES departments(id),
    
    -- Employment details
    employee_id VARCHAR(50) NOT NULL,
    job_title VARCHAR(255) NOT NULL,
    employment_type employment_type_enum NOT NULL DEFAULT 'full_time',
    hire_date DATE NOT NULL,
    termination_date DATE,
    
    -- Compensation
    hourly_rate DECIMAL(8, 2),
    salary_annual DECIMAL(12, 2),
    currency VARCHAR(3) DEFAULT 'USD',
    
    -- Contact and emergency
    emergency_contact_name VARCHAR(255),
    emergency_contact_phone VARCHAR(50),
    emergency_contact_relationship VARCHAR(100),
    
    -- Skills and certifications
    skills TEXT[],
    certifications JSONB DEFAULT '[]',
    languages TEXT[],
    
    -- Performance and status
    performance_rating DECIMAL(3, 2) CHECK (performance_rating >= 0 AND performance_rating <= 5),
    last_review_date DATE,
    next_review_date DATE,
    
    is_active BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    UNIQUE(organization_id, employee_id),
    UNIQUE(organization_id, user_id)
);

CREATE TYPE employment_type_enum AS ENUM (
    'full_time',
    'part_time',
    'contract',
    'temporary',
    'intern',
    'consultant'
);

-- Service requests and tasks
CREATE TABLE service_requests (
    id SERIAL PRIMARY KEY,
    organization_id INTEGER NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    property_id INTEGER NOT NULL REFERENCES properties(id) ON DELETE CASCADE,
    guest_id INTEGER REFERENCES guests(id),
    reservation_id INTEGER REFERENCES reservations(id),
    room_id INTEGER REFERENCES rooms(id),
    
    -- Request details
    type service_request_type_enum NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    priority priority_level_enum NOT NULL DEFAULT 'medium',
    
    -- Assignment
    assigned_to INTEGER REFERENCES staff(id),
    assigned_department INTEGER REFERENCES departments(id),
    assigned_at TIMESTAMP WITH TIME ZONE,
    
    -- Status and tracking
    status service_request_status_enum NOT NULL DEFAULT 'pending',
    requested_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    started_at TIMESTAMP WITH TIME ZONE,
    completed_at TIMESTAMP WITH TIME ZONE,
    estimated_completion TIMESTAMP WITH TIME ZONE,
    
    -- Guest communication
    guest_notification_sent BOOLEAN DEFAULT false,
    guest_satisfaction_rating INTEGER CHECK (guest_satisfaction_rating >= 1 AND guest_satisfaction_rating <= 5),
    guest_feedback TEXT,
    
    -- Internal notes
    internal_notes TEXT,
    completion_notes TEXT,
    
    -- Attachments and media
    attachments JSONB DEFAULT '[]',
    
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TYPE service_request_type_enum AS ENUM (
    'housekeeping',
    'maintenance',
    'concierge',
    'room_service',
    'transportation',
    'laundry',
    'spa',
    'it_support',
    'other'
);

CREATE TYPE priority_level_enum AS ENUM ('low', 'medium', 'high', 'urgent');

CREATE TYPE service_request_status_enum AS ENUM (
    'pending',
    'assigned',
    'in_progress',
    'completed',
    'cancelled',
    'on_hold'
);

💰 Financial Schema​

Billing and Payments​

-- Invoices and billing
CREATE TABLE invoices (
    id SERIAL PRIMARY KEY,
    organization_id INTEGER NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    property_id INTEGER NOT NULL REFERENCES properties(id) ON DELETE CASCADE,
    guest_id INTEGER NOT NULL REFERENCES guests(id) ON DELETE CASCADE,
    reservation_id INTEGER REFERENCES reservations(id),
    
    -- Invoice identification
    invoice_number VARCHAR(100) UNIQUE NOT NULL,
    invoice_date DATE NOT NULL DEFAULT CURRENT_DATE,
    due_date DATE NOT NULL,
    
    -- Amounts
    subtotal DECIMAL(12, 2) NOT NULL CHECK (subtotal >= 0),
    tax_amount DECIMAL(10, 2) NOT NULL DEFAULT 0,
    discount_amount DECIMAL(10, 2) NOT NULL DEFAULT 0,
    total_amount DECIMAL(12, 2) NOT NULL CHECK (total_amount >= 0),
    currency VARCHAR(3) DEFAULT 'USD',
    
    -- Payment status
    status invoice_status_enum NOT NULL DEFAULT 'pending',
    paid_amount DECIMAL(12, 2) NOT NULL DEFAULT 0,
    balance_due DECIMAL(12, 2) GENERATED ALWAYS AS (total_amount - paid_amount) STORED,
    
    -- Billing details
    billing_address JSONB,
    payment_terms INTEGER DEFAULT 30, -- days
    
    -- Notes and references
    notes TEXT,
    external_reference VARCHAR(255),
    
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TYPE invoice_status_enum AS ENUM (
    'draft',
    'pending',
    'sent',
    'paid',
    'overdue',
    'cancelled',
    'refunded'
);

-- Invoice line items
CREATE TABLE invoice_line_items (
    id SERIAL PRIMARY KEY,
    organization_id INTEGER NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    invoice_id INTEGER NOT NULL REFERENCES invoices(id) ON DELETE CASCADE,
    
    -- Item details
    description VARCHAR(500) NOT NULL,
    item_type item_type_enum NOT NULL DEFAULT 'room',
    quantity DECIMAL(8, 2) NOT NULL DEFAULT 1 CHECK (quantity > 0),
    unit_price DECIMAL(10, 2) NOT NULL CHECK (unit_price >= 0),
    total_price DECIMAL(12, 2) GENERATED ALWAYS AS (quantity * unit_price) STORED,
    
    -- Dates
    service_date DATE,
    service_period_start DATE,
    service_period_end DATE,
    
    -- Tax information
    tax_rate DECIMAL(5, 4) DEFAULT 0,
    tax_amount DECIMAL(10, 2) DEFAULT 0,
    
    -- References
    room_id INTEGER REFERENCES rooms(id),
    service_request_id INTEGER REFERENCES service_requests(id),
    
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TYPE item_type_enum AS ENUM (
    'room',
    'tax',
    'fee',
    'service',
    'food_beverage',
    'spa',
    'transportation',
    'other'
);

-- Payments
CREATE TABLE payments (
    id SERIAL PRIMARY KEY,
    organization_id INTEGER NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    invoice_id INTEGER REFERENCES invoices(id),
    guest_id INTEGER NOT NULL REFERENCES guests(id),
    
    -- Payment identification
    payment_reference VARCHAR(100) UNIQUE NOT NULL,
    external_payment_id VARCHAR(255),
    
    -- Payment details
    amount DECIMAL(12, 2) NOT NULL CHECK (amount > 0),
    currency VARCHAR(3) DEFAULT 'USD',
    payment_method payment_method_enum NOT NULL,
    
    -- Credit card details (encrypted)
    card_last_four VARCHAR(4),
    card_brand VARCHAR(50),
    card_expiry_month INTEGER,
    card_expiry_year INTEGER,
    
    -- Payment status
    status payment_status_transaction_enum NOT NULL DEFAULT 'pending',
    processed_at TIMESTAMP WITH TIME ZONE,
    failure_reason TEXT,
    
    -- Gateway information
    payment_gateway VARCHAR(100),
    gateway_transaction_id VARCHAR(255),
    gateway_fee DECIMAL(8, 2) DEFAULT 0,
    
    -- Refund information
    refunded_amount DECIMAL(12, 2) DEFAULT 0,
    refund_reason TEXT,
    refunded_at TIMESTAMP WITH TIME ZONE,
    
    -- Notes
    notes TEXT,
    
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TYPE payment_method_enum AS ENUM (
    'credit_card',
    'debit_card',
    'bank_transfer',
    'cash',
    'check',
    'digital_wallet',
    'cryptocurrency',
    'loyalty_points'
);

CREATE TYPE payment_status_transaction_enum AS ENUM (
    'pending',
    'processing',
    'completed',
    'failed',
    'cancelled',
    'refunded',
    'disputed'
);

🔒 Security and Audit Schema​

Data Protection and Compliance​

-- Audit log for all data changes
CREATE TABLE audit_logs (
    id SERIAL PRIMARY KEY,
    organization_id INTEGER REFERENCES organizations(id),
    
    -- Action details
    table_name VARCHAR(100) NOT NULL,
    record_id INTEGER NOT NULL,
    action audit_action_enum NOT NULL,
    
    -- User context
    user_id INTEGER REFERENCES users(id),
    user_email VARCHAR(255),
    user_ip_address INET,
    user_agent TEXT,
    
    -- Data changes
    old_values JSONB,
    new_values JSONB,
    changed_fields TEXT[],
    
    -- Metadata
    timestamp TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    session_id VARCHAR(255),
    request_id VARCHAR(255)
);

CREATE TYPE audit_action_enum AS ENUM ('INSERT', 'UPDATE', 'DELETE', 'SELECT');

-- Security events log
CREATE TABLE security_logs (
    id SERIAL PRIMARY KEY,
    organization_id INTEGER REFERENCES organizations(id),
    
    -- Event details
    event_type security_event_type_enum NOT NULL,
    severity security_severity_enum NOT NULL DEFAULT 'info',
    description TEXT NOT NULL,
    
    -- User context
    user_id INTEGER REFERENCES users(id),
    user_email VARCHAR(255),
    ip_address INET,
    user_agent TEXT,
    
    -- Additional context
    metadata JSONB DEFAULT '{}',
    
    -- Resolution
    resolved BOOLEAN DEFAULT false,
    resolved_at TIMESTAMP WITH TIME ZONE,
    resolved_by INTEGER REFERENCES users(id),
    resolution_notes TEXT,
    
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TYPE security_event_type_enum AS ENUM (
    'login_success',
    'login_failure',
    'logout',
    'password_change',
    'password_reset',
    'account_locked',
    'permission_denied',
    'suspicious_activity',
    'data_export',
    'admin_action'
);

CREATE TYPE security_severity_enum AS ENUM ('info', 'warning', 'error', 'critical');

-- Data retention policies
CREATE TABLE data_retention_policies (
    id SERIAL PRIMARY KEY,
    organization_id INTEGER NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    
    table_name VARCHAR(100) NOT NULL,
    retention_period_days INTEGER NOT NULL CHECK (retention_period_days > 0),
    purge_condition TEXT, -- SQL condition for selective purging
    
    -- Policy details
    policy_type data_retention_type_enum NOT NULL DEFAULT 'time_based',
    is_active BOOLEAN NOT NULL DEFAULT true,
    
    -- Compliance
    legal_basis TEXT,
    compliance_framework VARCHAR(100), -- GDPR, CCPA, etc.
    
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    UNIQUE(organization_id, table_name)
);

CREATE TYPE data_retention_type_enum AS ENUM ('time_based', 'event_based', 'manual');

📈 Analytics Schema​

Reporting and Business Intelligence​

-- Feature usage tracking
CREATE TABLE feature_usage (
    id SERIAL PRIMARY KEY,
    organization_id INTEGER NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    
    feature_code VARCHAR(100) NOT NULL,
    current_usage INTEGER NOT NULL DEFAULT 0,
    usage_limit INTEGER,
    
    -- Tracking periods
    tracking_period tracking_period_enum NOT NULL DEFAULT 'monthly',
    period_start DATE NOT NULL,
    period_end DATE NOT NULL,
    
    -- Metadata
    last_updated TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    UNIQUE(organization_id, feature_code, period_start)
);

CREATE TYPE tracking_period_enum AS ENUM ('daily', 'weekly', 'monthly', 'yearly');

-- Performance metrics
CREATE TABLE performance_metrics (
    id SERIAL PRIMARY KEY,
    organization_id INTEGER NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    property_id INTEGER REFERENCES properties(id),
    
    -- Metric identification
    metric_name VARCHAR(100) NOT NULL,
    metric_value DECIMAL(15, 4) NOT NULL,
    metric_unit VARCHAR(50),
    
    -- Time dimensions
    measurement_date DATE NOT NULL,
    measurement_hour INTEGER CHECK (measurement_hour >= 0 AND measurement_hour <= 23),
    
    -- Categorization
    category VARCHAR(100),
    subcategory VARCHAR(100),
    
    -- Additional context
    metadata JSONB DEFAULT '{}',
    
    recorded_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create indexes for common queries
CREATE INDEX idx_performance_metrics_org_date ON performance_metrics(organization_id, measurement_date);
CREATE INDEX idx_performance_metrics_metric_name ON performance_metrics(metric_name);

🔍 Indexing Strategy​

Performance Optimization​

-- Multi-tenant isolation indexes (most critical)
CREATE INDEX idx_organizations_tenant_id ON organizations(tenant_id);
CREATE INDEX idx_users_org_email ON users(organization_id, email);
CREATE INDEX idx_guests_org_email ON guests(organization_id, email);
CREATE INDEX idx_reservations_org_dates ON reservations(organization_id, check_in_date, check_out_date);
CREATE INDEX idx_rooms_org_property ON rooms(organization_id, property_id);

-- Lookup and search indexes
CREATE INDEX idx_reservations_confirmation ON reservations(confirmation_number);
CREATE INDEX idx_guests_phone ON guests(phone_primary);
CREATE INDEX idx_staff_employee_id ON staff(organization_id, employee_id);

-- Date-based queries
CREATE INDEX idx_reservations_checkin ON reservations(check_in_date);
CREATE INDEX idx_reservations_checkout ON reservations(check_out_date);
CREATE INDEX idx_audit_logs_timestamp ON audit_logs(timestamp);

-- Full-text search indexes
CREATE INDEX idx_guests_name_search ON guests USING gin(to_tsvector('english', first_name || ' ' || last_name));
CREATE INDEX idx_service_requests_text_search ON service_requests USING gin(to_tsvector('english', title || ' ' || description));

-- Composite indexes for complex queries
CREATE INDEX idx_reservations_status_dates ON reservations(organization_id, status, check_in_date, check_out_date);
CREATE INDEX idx_payments_status_date ON payments(organization_id, status, created_at);
CREATE INDEX idx_invoices_guest_status ON invoices(organization_id, guest_id, status);

-- Partial indexes for active records
CREATE INDEX idx_active_users ON users(organization_id, id) WHERE is_active = true;
CREATE INDEX idx_active_staff ON staff(organization_id, id) WHERE is_active = true;
CREATE INDEX idx_active_rooms ON rooms(organization_id, property_id) WHERE is_active = true;

🛡️ Row Level Security​

Multi-Tenant Data Isolation​

-- Enable RLS on all tenant tables
ALTER TABLE organizations ENABLE ROW LEVEL SECURITY;
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE properties ENABLE ROW LEVEL SECURITY;
ALTER TABLE rooms ENABLE ROW LEVEL SECURITY;
ALTER TABLE room_types ENABLE ROW LEVEL SECURITY;
ALTER TABLE guests ENABLE ROW LEVEL SECURITY;
ALTER TABLE reservations ENABLE ROW LEVEL SECURITY;
ALTER TABLE service_requests ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE payments ENABLE ROW LEVEL SECURITY;

-- Create RLS policies for tenant isolation
CREATE POLICY tenant_isolation_organizations ON organizations
    FOR ALL TO authenticated_users
    USING (id = current_setting('app.current_organization_id')::int);

CREATE POLICY tenant_isolation_users ON users
    FOR ALL TO authenticated_users
    USING (organization_id = current_setting('app.current_organization_id')::int);

CREATE POLICY tenant_isolation_guests ON guests
    FOR ALL TO authenticated_users
    USING (organization_id = current_setting('app.current_organization_id')::int);

CREATE POLICY tenant_isolation_reservations ON reservations
    FOR ALL TO authenticated_users
    USING (organization_id = current_setting('app.current_organization_id')::int);

-- Super admin bypass policy (highest priority)
CREATE POLICY super_admin_bypass ON users
    FOR ALL TO super_admin_role
    USING (true);

-- Additional policies can be created for more granular access control
CREATE POLICY guest_data_access ON guests
    FOR SELECT TO guest_service_role
    USING (
        organization_id = current_setting('app.current_organization_id')::int
        AND (
            -- Staff can see all guests in their organization
            current_user_is_staff()
            OR
            -- Guests can only see their own data
            id = current_guest_id()
        )
    );

🔧 Database Functions and Triggers​

Automated Data Management​

-- Function to update updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

-- Apply to all tables with updated_at column
CREATE TRIGGER update_organizations_updated_at BEFORE UPDATE ON organizations FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_users_updated_at BEFORE UPDATE ON users FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_guests_updated_at BEFORE UPDATE ON guests FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_reservations_updated_at BEFORE UPDATE ON reservations FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Function to generate confirmation numbers
CREATE OR REPLACE FUNCTION generate_confirmation_number()
RETURNS TEXT AS $$
DECLARE
    chars TEXT := 'ABCDEFGHJKLMNPQRSTUVWXYZ23456789';
    result TEXT := '';
    i INTEGER;
BEGIN
    FOR i IN 1..8 LOOP
        result := result || substr(chars, floor(random() * length(chars) + 1)::int, 1);
    END LOOP;
    RETURN result;
END;
$$ LANGUAGE plpgsql;

-- Trigger to auto-generate confirmation numbers
CREATE OR REPLACE FUNCTION set_confirmation_number()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.confirmation_number IS NULL THEN
        NEW.confirmation_number := generate_confirmation_number();
        -- Ensure uniqueness
        WHILE EXISTS (SELECT 1 FROM reservations WHERE confirmation_number = NEW.confirmation_number) LOOP
            NEW.confirmation_number := generate_confirmation_number();
        END LOOP;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_reservation_confirmation_number
    BEFORE INSERT ON reservations
    FOR EACH ROW EXECUTE FUNCTION set_confirmation_number();

-- Audit trigger function
CREATE OR REPLACE FUNCTION audit_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO audit_logs (table_name, record_id, action, user_id, new_values, organization_id)
        VALUES (TG_TABLE_NAME, NEW.id, 'INSERT', current_user_id(), row_to_json(NEW), NEW.organization_id);
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO audit_logs (table_name, record_id, action, user_id, old_values, new_values, organization_id)
        VALUES (TG_TABLE_NAME, NEW.id, 'UPDATE', current_user_id(), row_to_json(OLD), row_to_json(NEW), NEW.organization_id);
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO audit_logs (table_name, record_id, action, user_id, old_values, organization_id)
        VALUES (TG_TABLE_NAME, OLD.id, 'DELETE', current_user_id(), row_to_json(OLD), OLD.organization_id);
        RETURN OLD;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- Apply audit triggers to sensitive tables
CREATE TRIGGER audit_users AFTER INSERT OR UPDATE OR DELETE ON users FOR EACH ROW EXECUTE FUNCTION audit_trigger();
CREATE TRIGGER audit_guests AFTER INSERT OR UPDATE OR DELETE ON guests FOR EACH ROW EXECUTE FUNCTION audit_trigger();
CREATE TRIGGER audit_reservations AFTER INSERT OR UPDATE OR DELETE ON reservations FOR EACH ROW EXECUTE FUNCTION audit_trigger();
CREATE TRIGGER audit_payments AFTER INSERT OR UPDATE OR DELETE ON payments FOR EACH ROW EXECUTE FUNCTION audit_trigger();

📊 Database Views​

Reporting and Analytics Views​

-- Occupancy analytics view
CREATE VIEW v_occupancy_analytics AS
SELECT 
    r.organization_id,
    r.property_id,
    p.name as property_name,
    r.check_in_date,
    r.check_out_date,
    COUNT(DISTINCT r.room_id) as occupied_rooms,
    COUNT(DISTINCT rooms.id) as total_rooms,
    ROUND(COUNT(DISTINCT r.room_id)::numeric / COUNT(DISTINCT rooms.id) * 100, 2) as occupancy_rate,
    SUM(r.total_amount) as total_revenue,
    AVG(r.total_amount / r.nights) as average_daily_rate
FROM reservations r
JOIN properties p ON r.property_id = p.id
JOIN rooms ON rooms.property_id = p.id
WHERE r.status IN ('confirmed', 'checked_in', 'checked_out')
GROUP BY r.organization_id, r.property_id, p.name, r.check_in_date, r.check_out_date;

-- Guest analytics view
CREATE VIEW v_guest_analytics AS
SELECT 
    g.organization_id,
    g.id as guest_id,
    g.first_name || ' ' || g.last_name as guest_name,
    g.email,
    g.loyalty_tier,
    COUNT(r.id) as total_reservations,
    SUM(r.total_amount) as total_revenue,
    AVG(r.total_amount) as average_reservation_value,
    MAX(r.check_out_date) as last_stay_date,
    MIN(r.check_in_date) as first_stay_date
FROM guests g
LEFT JOIN reservations r ON g.id = r.guest_id AND r.status = 'checked_out'
GROUP BY g.organization_id, g.id, g.first_name, g.last_name, g.email, g.loyalty_tier;

-- Staff performance view
CREATE VIEW v_staff_performance AS
SELECT 
    s.organization_id,
    s.id as staff_id,
    u.first_name || ' ' || u.last_name as staff_name,
    d.name as department_name,
    s.job_title,
    COUNT(sr.id) as total_tasks,
    COUNT(CASE WHEN sr.status = 'completed' THEN 1 END) as completed_tasks,
    ROUND(COUNT(CASE WHEN sr.status = 'completed' THEN 1 END)::numeric / NULLIF(COUNT(sr.id), 0) * 100, 2) as completion_rate,
    AVG(EXTRACT(EPOCH FROM (sr.completed_at - sr.started_at))/3600) as avg_completion_hours,
    AVG(sr.guest_satisfaction_rating) as avg_guest_rating
FROM staff s
JOIN users u ON s.user_id = u.id
LEFT JOIN departments d ON s.department_id = d.id
LEFT JOIN service_requests sr ON s.id = sr.assigned_to
GROUP BY s.organization_id, s.id, u.first_name, u.last_name, d.name, s.job_title;

🔄 Data Maintenance​

Cleanup and Optimization​

-- Function to archive old data
CREATE OR REPLACE FUNCTION archive_old_data(retention_days INTEGER DEFAULT 2555) -- 7 years default
RETURNS INTEGER AS $$
DECLARE
    archived_count INTEGER := 0;
    cutoff_date DATE := CURRENT_DATE - retention_days;
BEGIN
    -- Archive old audit logs
    WITH archived AS (
        DELETE FROM audit_logs 
        WHERE timestamp < cutoff_date
        RETURNING id
    )
    SELECT COUNT(*) INTO archived_count FROM archived;
    
    -- Archive old security logs
    DELETE FROM security_logs 
    WHERE created_at < (CURRENT_DATE - INTERVAL '2 years');
    
    -- Archive completed service requests older than 1 year
    DELETE FROM service_requests
    WHERE status = 'completed' 
    AND completed_at < (CURRENT_DATE - INTERVAL '1 year');
    
    RETURN archived_count;
END;
$$ LANGUAGE plpgsql;

-- Function to update performance metrics
CREATE OR REPLACE FUNCTION update_performance_metrics()
RETURNS VOID AS $$
BEGIN
    -- Calculate daily occupancy rates
    INSERT INTO performance_metrics (organization_id, property_id, metric_name, metric_value, measurement_date, category)
    SELECT 
        p.organization_id,
        p.id,
        'occupancy_rate',
        COALESCE(
            (COUNT(DISTINCT r.room_id)::decimal / COUNT(DISTINCT rooms.id)) * 100, 
            0
        ),
        CURRENT_DATE - 1,
        'operational'
    FROM properties p
    LEFT JOIN rooms ON rooms.property_id = p.id AND rooms.is_active = true
    LEFT JOIN reservations r ON r.property_id = p.id 
        AND r.check_in_date <= CURRENT_DATE - 1 
        AND r.check_out_date > CURRENT_DATE - 1
        AND r.status IN ('confirmed', 'checked_in', 'checked_out')
    GROUP BY p.organization_id, p.id
    ON CONFLICT (organization_id, metric_name, measurement_date) DO UPDATE 
    SET metric_value = EXCLUDED.metric_value;
    
    -- Calculate revenue metrics
    INSERT INTO performance_metrics (organization_id, property_id, metric_name, metric_value, measurement_date, category)
    SELECT 
        organization_id,
        property_id,
        'daily_revenue',
        COALESCE(SUM(total_amount), 0),
        CURRENT_DATE - 1,
        'financial'
    FROM reservations
    WHERE check_in_date = CURRENT_DATE - 1
    AND status IN ('confirmed', 'checked_in', 'checked_out')
    GROUP BY organization_id, property_id
    ON CONFLICT (organization_id, metric_name, measurement_date) DO UPDATE 
    SET metric_value = EXCLUDED.metric_value;
END;
$$ LANGUAGE plpgsql;

-- Schedule daily metrics update
SELECT cron.schedule('update-metrics', '0 1 * * *', 'SELECT update_performance_metrics();');

📚 Related Documentation​