Database Schema - gabrielmaialva33/innkeeper GitHub Wiki

🗄️ Database Schema

Comprehensive database design and structure documentation

📋 Overview

This document provides detailed information about Innkeeper's database schema, including table structures, relationships, indexes, and design decisions. The database is designed to support multi-tenancy, scalability, and data integrity.

**Interactive Database Diagram **: View on dbdiagram.io

🏗️ Schema Overview

Core Entity Groups

graph TB
    subgraph "Organization & Users"
        ORG[organizations]
        USER[users]
        STAFF[staff]
        ROLE[roles]
        PERM[permissions]
    end
    
    subgraph "Hotel Management"
        HOTEL[hotels]
        ROOM_TYPE[room_types]
        ROOM[rooms]
        AMENITY[amenities]
        SERVICE[services]
    end
    
    subgraph "Guest & Reservations"
        GUEST[guests]
        RESERVATION[reservations]
        FOLIO[folios]
        PAYMENT[payments]
    end
    
    subgraph "System"
        AUDIT[audit_logs]
        FILE[files]
        TOKEN[auth_access_tokens]
    end
    
    ORG --> HOTEL
    ORG --> USER
    ORG --> GUEST
    HOTEL --> ROOM_TYPE
    HOTEL --> ROOM
    GUEST --> RESERVATION
    RESERVATION --> FOLIO
    RESERVATION --> PAYMENT
Loading

📊 Detailed Table Structures

Organizations Table

CREATE TABLE organizations (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(255) UNIQUE NOT NULL,
    tax_id VARCHAR(255),
    email VARCHAR(255),
    phone VARCHAR(255),
    address VARCHAR(255),
    city VARCHAR(255),
    state VARCHAR(255),
    country VARCHAR(255),
    postal_code VARCHAR(255),
    currency VARCHAR(3) DEFAULT 'USD',
    timezone VARCHAR(255) DEFAULT 'UTC',
    is_active BOOLEAN DEFAULT true,
    is_deleted BOOLEAN DEFAULT false,
    settings JSONB DEFAULT '{}',
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

Hotels Table

CREATE TABLE hotels (
    id SERIAL PRIMARY KEY,
    organization_id INTEGER NOT NULL REFERENCES organizations(id),
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL,
    description TEXT,
    email VARCHAR(255),
    phone VARCHAR(255),
    website VARCHAR(255),
    address VARCHAR(255),
    city VARCHAR(255),
    state VARCHAR(255),
    country VARCHAR(255),
    postal_code VARCHAR(255),
    latitude DECIMAL(10,8),
    longitude DECIMAL(11,8),
    timezone VARCHAR(255),
    currency VARCHAR(3),
    star_rating INTEGER CHECK (star_rating >= 1 AND star_rating <= 5),
    total_rooms INTEGER DEFAULT 0,
    check_in_time TIME DEFAULT '15:00',
    check_out_time TIME DEFAULT '11:00',
    is_active BOOLEAN DEFAULT true,
    is_deleted BOOLEAN DEFAULT false,
    policies JSONB DEFAULT '{}',
    contact_info JSONB DEFAULT '{}',
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    UNIQUE(organization_id, slug)
);

🔗 Key Relationships

Multi-Tenant Data Isolation

All tenant-specific tables include organization_id for data isolation:

-- Example of tenant isolation pattern
SELECT * FROM reservations 
WHERE organization_id = $1 
  AND check_in_date >= $2;

Foreign Key Constraints

-- Hotel relationships
ALTER TABLE hotels 
ADD CONSTRAINT fk_hotels_organization 
FOREIGN KEY (organization_id) REFERENCES organizations(id);

-- Room relationships
ALTER TABLE rooms 
ADD CONSTRAINT fk_rooms_hotel 
FOREIGN KEY (hotel_id) REFERENCES hotels(id);

ALTER TABLE rooms 
ADD CONSTRAINT fk_rooms_room_type 
FOREIGN KEY (room_type_id) REFERENCES room_types(id);

-- Reservation relationships
ALTER TABLE reservations 
ADD CONSTRAINT fk_reservations_guest 
FOREIGN KEY (guest_id) REFERENCES guests(id);

📚 Next Steps

Continue exploring the database schema:

  1. System Architecture - Overall system design
  2. Multi-Tenant Architecture - Tenancy implementation
  3. API Documentation - API endpoints and usage

← Previous: System Architecture | Wiki Home | Next: Multi-Tenant Architecture →

⚠️ **GitHub.com Fallback** ⚠️