Database Schema — PostgreSQL Design

Версия: 1.0
Дата: 19.04.2026
Статус: Черновик

Обзор

Database Schema определяет полную структуру PostgreSQL базы данных для vitrip.store согласно технологическому стеку "Database | PostgreSQL | Надёжность, JSONB, ltree для иерархий, PostGIS" из Архитектура платформы. Включает 5 схем данных из Storage Layer: public, hotels, content, bookings, users с поддержкой hotel matching алгоритма из Ingestion Layer и PostGIS geo-функций для Business Services.

Схемы базы данных: См. диаграмму vitrip_database_schema.jpg — полная ER диаграмма со всеми таблицами, связями и индексами.

Отношения таблиц: См. диаграмму vitrip_table_relationships.jpg — детальные связи между hotels, bookings, users схемами с foreign keys.

Индексы и производительность: См. диаграмму vitrip_database_indexes.jpg — схема всех индексов для geo-поиска, full-text search и производительности.

Инициализация базы данных

Расширения PostgreSQL

-- Включение необходимых расширений согласно технологическому стеку
-- PostGIS для географических данных из overview/index.md
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS postgis_topology;

-- ltree для иерархий (регионы, категории) из overview/index.md  
CREATE EXTENSION IF NOT EXISTS ltree;

-- UUID для primary keys
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Full-text search для отелей
CREATE EXTENSION IF NOT EXISTS unaccent;
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- JSON functions расширение
CREATE EXTENSION IF NOT EXISTS btree_gin;
CREATE EXTENSION IF NOT EXISTS btree_gist;

Создание схем

-- Схемы согласно reference/storage.md
CREATE SCHEMA IF NOT EXISTS public;      -- Общие справочники и настройки
CREATE SCHEMA IF NOT EXISTS hotels;      -- Данные об отелях и их характеристиках
CREATE SCHEMA IF NOT EXISTS content;     -- Мультимедиа контент и переводы  
CREATE SCHEMA IF NOT EXISTS bookings;    -- Бронирования и связанные транзакции
CREATE SCHEMA IF NOT EXISTS users;       -- Агенты, роли и права доступа

Public Schema — Справочники

Поставщики данных

-- Поставщики согласно reference/suppliers.md
CREATE TABLE public.suppliers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,                -- 'stuba', 'hometogo', 'ratehawk'
    type VARCHAR(20) NOT NULL,                       -- 'xml', 'graphql', 'rest'
    base_url TEXT,                                   -- API endpoint
    auth_method VARCHAR(20),                         -- 'api_key', 'oauth2', 'ftp'
    active BOOLEAN DEFAULT true,
    config JSONB NOT NULL DEFAULT '{}',             -- Конфигурация адаптера
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now()
);

-- Данные согласно reference/suppliers.md
INSERT INTO public.suppliers (name, type, base_url, auth_method, config) VALUES
('stuba', 'xml', 'ftp://ftp.stuba.com', 'ftp', '{"sync_interval": "4h", "file_patterns": ["hotels_*.xml"]}'),
('hometogo', 'graphql', 'https://api.hometogo.com/graphql', 'api_key', '{"sync_interval": "1h", "regions": ["prague", "berlin"]}'),
('ratehawk', 'rest', 'https://api.ratehawk.com/v1', 'api_key', '{"sync_interval": "2h", "endpoints": ["/hotels", "/prices"]}');

Регионы и локации

-- Иерархия регионов с ltree согласно overview/index.md
CREATE TABLE public.regions (
    id SERIAL PRIMARY KEY,
    path ltree NOT NULL,                             -- Europe.Czech.Prague.Center  
    name_en VARCHAR(100) NOT NULL,
    name_ru VARCHAR(100),
    name_uk VARCHAR(100),
    level INTEGER NOT NULL,                          -- 1=continent, 2=country, 3=city, 4=district
    iso_code VARCHAR(10),                           -- ISO коды для стран/городов
    timezone VARCHAR(50),                           -- Europe/Prague
    currency_code CHAR(3),                          -- EUR, CZK, UAH
    coordinates POINT,                              -- PostGIS центр региона
    bbox POLYGON,                                   -- PostGIS границы региона
    created_at TIMESTAMPTZ DEFAULT now()
);

-- Индекс для ltree queries согласно архитектуре
CREATE INDEX regions_path_idx ON public.regions USING gist (path);
CREATE INDEX regions_coordinates_idx ON public.regions USING gist (coordinates);

-- Примеры данных для иерархии
INSERT INTO public.regions (path, name_en, name_ru, level, iso_code, timezone, currency_code) VALUES
('Europe', 'Europe', 'Европа', 1, NULL, NULL, NULL),
('Europe.Czech', 'Czech Republic', 'Чехия', 2, 'CZ', 'Europe/Prague', 'CZK'),
('Europe.Czech.Prague', 'Prague', 'Прага', 3, NULL, 'Europe/Prague', 'CZK'),
('Europe.Czech.Prague.Center', 'Prague Center', 'Центр Праги', 4, NULL, 'Europe/Prague', 'CZK'),
('Europe.Ukraine', 'Ukraine', 'Украина', 2, 'UA', 'Europe/Kiev', 'UAH'),
('Europe.Ukraine.Kyiv', 'Kyiv', 'Киев', 3, NULL, 'Europe/Kiev', 'UAH');

Валюты и курсы

-- Валютные курсы для pricing_service из business_services.md
CREATE TABLE public.currencies (
    code CHAR(3) PRIMARY KEY,                       -- EUR, USD, CZK, UAH
    name_en VARCHAR(50) NOT NULL,
    name_ru VARCHAR(50),
    symbol VARCHAR(10) NOT NULL,                    -- €, $, Kč, ₴
    decimal_places INTEGER DEFAULT 2,
    active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE public.exchange_rates (
    id SERIAL PRIMARY KEY,
    from_currency CHAR(3) REFERENCES public.currencies(code),
    to_currency CHAR(3) REFERENCES public.currencies(code),
    rate DECIMAL(12,6) NOT NULL,
    date DATE NOT NULL,
    source VARCHAR(50),                             -- 'ecb', 'bank_api', 'manual'
    created_at TIMESTAMPTZ DEFAULT now(),
    
    UNIQUE(from_currency, to_currency, date)
);

-- Индексы для быстрого поиска курсов
CREATE INDEX exchange_rates_date_idx ON public.exchange_rates (date DESC);
CREATE INDEX exchange_rates_pair_idx ON public.exchange_rates (from_currency, to_currency, date DESC);

-- Базовые валюты
INSERT INTO public.currencies (code, name_en, name_ru, symbol) VALUES
('EUR', 'Euro', 'Евро', '€'),
('USD', 'US Dollar', 'Доллар США', '$'),
('CZK', 'Czech Koruna', 'Чешская крона', 'Kč'),
('UAH', 'Ukrainian Hryvnia', 'Украинская гривна', '₴');

Настройки системы

-- Настройки для различных сервисов
CREATE TABLE public.settings (
    key VARCHAR(100) PRIMARY KEY,
    value JSONB NOT NULL,
    description TEXT,
    category VARCHAR(50),                           -- 'search', 'pricing', 'booking'
    updated_by VARCHAR(100),
    updated_at TIMESTAMPTZ DEFAULT now()
);

-- Настройки согласно business_services.md и reference/ingestion.md
INSERT INTO public.settings (key, value, description, category) VALUES
('search.default_radius_km', '50', 'Радиус поиска по умолчанию', 'search'),
('search.max_results_per_page', '100', 'Максимум результатов на странице', 'search'),
('hotel_matching.confidence_threshold', '0.8', 'Минимальная уверенность для автоматического матчинга', 'ingestion'),
('hotel_matching.distance_threshold_meters', '100', 'Максимальное расстояние для матчинга отелей', 'ingestion'),
('pricing.default_markup_percent', '10', 'Наценка агента по умолчанию', 'pricing'),
('booking.confirmation_timeout_minutes', '30', 'Таймаут подтверждения бронирования', 'booking');

Hotels Schema — Отели и их данные

Основные таблицы отелей

-- Основная таблица отелей согласно reference/storage.md
CREATE TABLE hotels.properties (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    external_id VARCHAR(100),                       -- ID от поставщика (может быть NULL для мастер-записей)
    supplier_id INTEGER REFERENCES public.suppliers(id),
    name VARCHAR(200) NOT NULL,
    slug VARCHAR(250) UNIQUE,                       -- URL-friendly версия названия
    address JSONB NOT NULL,                         -- Структурированный адрес
    coordinates POINT,                              -- PostGIS для геопоиска из business_services.md
    region_id INTEGER REFERENCES public.regions(id),
    star_rating DECIMAL(2,1) CHECK (star_rating >= 0 AND star_rating <= 5),
    chain_id INTEGER REFERENCES hotels.chains(id),
    property_type VARCHAR(50) DEFAULT 'hotel',      -- hotel, apartment, villa, resort
    status VARCHAR(20) DEFAULT 'active',            -- active, inactive, pending_review
    features JSONB DEFAULT '{}',                    -- Общие характеристики отеля
    metadata JSONB DEFAULT '{}',                    -- Дополнительные данные от поставщиков
    search_vector tsvector,                         -- Full-text search
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now(),
    
    -- Ограничения
    CHECK (jsonb_typeof(address) = 'object'),
    CHECK (jsonb_typeof(features) = 'object'),
    CHECK (jsonb_typeof(metadata) = 'object')
);

-- Структура JSONB address согласно reference/suppliers.md
COMMENT ON COLUMN hotels.properties.address IS 
'JSONB структура: {"street": "Wenceslas Square 1", "city": "Prague", "postal_code": "110 00", "country": "Czech Republic", "region": "Prague"}';

-- Структура JSONB features
COMMENT ON COLUMN hotels.properties.features IS 
'JSONB структура: {"wifi": true, "parking": true, "pet_friendly": false, "wheelchair_accessible": true, "business_center": true}';

-- Индексы для производительности согласно business_services.md geo-поиска
CREATE INDEX hotels_properties_coordinates_idx ON hotels.properties USING gist (coordinates);
CREATE INDEX hotels_properties_region_idx ON hotels.properties (region_id);
CREATE INDEX hotels_properties_search_idx ON hotels.properties USING gin (search_vector);
CREATE INDEX hotels_properties_address_gin_idx ON hotels.properties USING gin (address);
CREATE INDEX hotels_properties_features_gin_idx ON hotels.properties USING gin (features);
CREATE INDEX hotels_properties_supplier_external_idx ON hotels.properties (supplier_id, external_id);
CREATE INDEX hotels_properties_status_idx ON hotels.properties (status) WHERE status = 'active';
CREATE INDEX hotels_properties_star_rating_idx ON hotels.properties (star_rating) WHERE star_rating IS NOT NULL;

-- Функция для обновления search_vector
CREATE OR REPLACE FUNCTION hotels.update_property_search_vector()
RETURNS TRIGGER AS $$
BEGIN
    NEW.search_vector := 
        setweight(to_tsvector('russian', coalesce(NEW.name, '')), 'A') ||
        setweight(to_tsvector('russian', coalesce(NEW.address->>'city', '')), 'B') ||
        setweight(to_tsvector('russian', coalesce(NEW.address->>'street', '')), 'C');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_properties_search_vector
    BEFORE INSERT OR UPDATE ON hotels.properties
    FOR EACH ROW EXECUTE FUNCTION hotels.update_property_search_vector();

Матчинг отелей между поставщиками

-- Таблица для hotel matching из reference/ingestion.md
CREATE TABLE hotels.supplier_mapping (
    hotel_id UUID REFERENCES hotels.properties(id) ON DELETE CASCADE,
    supplier_id INTEGER REFERENCES public.suppliers(id),
    external_id VARCHAR(100) NOT NULL,
    confidence DECIMAL(3,2) CHECK (confidence >= 0.00 AND confidence <= 1.00),
    verified BOOLEAN DEFAULT false,                 -- Ручная верификация
    match_factors JSONB DEFAULT '{}',              -- Детали матчинга
    created_at TIMESTAMPTZ DEFAULT now(),
    verified_at TIMESTAMPTZ,
    verified_by UUID,
    
    PRIMARY KEY (hotel_id, supplier_id)
);

-- Структура match_factors согласно reference/ingestion.md
COMMENT ON COLUMN hotels.supplier_mapping.match_factors IS 
'JSONB структура: {"name_similarity": 0.95, "distance_meters": 45.2, "address_similarity": 0.88, "amenities_overlap": 0.6}';

-- Индексы для поиска матчинга
CREATE INDEX supplier_mapping_external_id_idx ON hotels.supplier_mapping (supplier_id, external_id);
CREATE INDEX supplier_mapping_confidence_idx ON hotels.supplier_mapping (confidence DESC);
CREATE INDEX supplier_mapping_verified_idx ON hotels.supplier_mapping (verified, confidence DESC);
CREATE INDEX supplier_mapping_factors_idx ON hotels.supplier_mapping USING gin (match_factors);

-- Функция для автоматического матчинга согласно reference/ingestion.md
CREATE OR REPLACE FUNCTION hotels.auto_match_hotel(
    p_supplier_id INTEGER,
    p_external_id VARCHAR(100),
    p_name VARCHAR(200),
    p_coordinates POINT,
    p_address JSONB
) RETURNS TABLE(hotel_id UUID, confidence DECIMAL(3,2)) AS $$
DECLARE
    v_threshold DECIMAL(3,2) := 0.8;  -- Из settings
    v_distance_threshold INTEGER := 100; -- метры, из settings
BEGIN
    -- Поиск кандидатов в радиусе distance_threshold
    RETURN QUERY
    SELECT 
        hp.id,
        -- Упрощенный расчет confidence (в реальности используется Rust алгоритм)
        GREATEST(
            similarity(hp.name, p_name) * 0.4 +
            (CASE WHEN ST_DWithin(hp.coordinates, p_coordinates, v_distance_threshold) 
             THEN 1.0 - (ST_Distance(hp.coordinates, p_coordinates) / v_distance_threshold)
             ELSE 0.0 END) * 0.4 +
            similarity(hp.address->>'street', p_address->>'street') * 0.2,
            0.0
        )::DECIMAL(3,2) as confidence
    FROM hotels.properties hp
    WHERE hp.status = 'active'
      AND ST_DWithin(hp.coordinates, p_coordinates, v_distance_threshold)
      AND similarity(hp.name, p_name) >= 0.3
    HAVING GREATEST(
        similarity(hp.name, p_name) * 0.4 +
        (1.0 - (ST_Distance(hp.coordinates, p_coordinates) / v_distance_threshold)) * 0.4 +
        similarity(hp.address->>'street', p_address->>'street') * 0.2,
        0.0
    ) >= v_threshold
    ORDER BY confidence DESC
    LIMIT 5;
END;
$$ LANGUAGE plpgsql;

Цепочки отелей

-- Цепочки отелей (Marriott, Hilton, etc.)
CREATE TABLE hotels.chains (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    slug VARCHAR(120) UNIQUE NOT NULL,
    logo_url TEXT,
    website_url TEXT,
    description TEXT,
    country_origin CHAR(2),                         -- ISO country code
    founded_year INTEGER,
    created_at TIMESTAMPTZ DEFAULT now()
);

-- Индекс для поиска по названию
CREATE INDEX chains_name_idx ON hotels.chains (name);
CREATE INDEX chains_slug_idx ON hotels.chains (slug);

-- Примеры данных
INSERT INTO hotels.chains (name, slug, country_origin, founded_year) VALUES
('Marriott International', 'marriott', 'US', 1927),
('Hilton Hotels', 'hilton', 'US', 1919),
('AccorHotels', 'accor', 'FR', 1967),
('InterContinental Hotels Group', 'ihg', 'GB', 2003);

Удобства и категории

-- Категории удобств
CREATE TABLE hotels.amenity_categories (
    id SERIAL PRIMARY KEY,
    name_en VARCHAR(50) NOT NULL,
    name_ru VARCHAR(50),
    name_uk VARCHAR(50),
    icon VARCHAR(50),                               -- CSS class или emoji
    sort_order INTEGER DEFAULT 0
);

-- Удобства отелей
CREATE TABLE hotels.amenities (
    id SERIAL PRIMARY KEY,
    category_id INTEGER REFERENCES hotels.amenity_categories(id),
    name_en VARCHAR(100) UNIQUE NOT NULL,
    name_ru VARCHAR(100),
    name_uk VARCHAR(100),
    icon VARCHAR(50),
    important BOOLEAN DEFAULT false,                -- Важное удобство для фильтров
    created_at TIMESTAMPTZ DEFAULT now()
);

-- Связь отелей и удобств
CREATE TABLE hotels.property_amenities (
    property_id UUID REFERENCES hotels.properties(id) ON DELETE CASCADE,
    amenity_id INTEGER REFERENCES hotels.amenities(id),
    value JSONB DEFAULT 'true',                     -- Boolean или дополнительная информация
    verified BOOLEAN DEFAULT false,
    created_at TIMESTAMPTZ DEFAULT now(),
    
    PRIMARY KEY (property_id, amenity_id)
);

-- Индексы для фильтрации
CREATE INDEX property_amenities_property_idx ON hotels.property_amenities (property_id);
CREATE INDEX property_amenities_amenity_idx ON hotels.property_amenities (amenity_id);
CREATE INDEX property_amenities_important_idx ON hotels.property_amenities (amenity_id) 
    WHERE amenity_id IN (SELECT id FROM hotels.amenities WHERE important = true);

-- Базовые категории удобств
INSERT INTO hotels.amenity_categories (name_en, name_ru, icon, sort_order) VALUES
('Internet', 'Интернет', 'wifi', 1),
('Parking', 'Парковка', 'local_parking', 2),
('Fitness', 'Фитнес', 'fitness_center', 3),
('Business', 'Бизнес', 'business_center', 4),
('Accessibility', 'Доступность', 'accessible', 5);

-- Базовые удобства
INSERT INTO hotels.amenities (category_id, name_en, name_ru, important) VALUES
(1, 'Free WiFi', 'Бесплатный WiFi', true),
(1, 'High-speed Internet', 'Высокоскоростной интернет', false),
(2, 'Free Parking', 'Бесплатная парковка', true),
(2, 'Valet Parking', 'Парковка с обслуживанием', false),
(3, 'Fitness Center', 'Фитнес-центр', true),
(3, 'Swimming Pool', 'Бассейн', true),
(4, 'Business Center', 'Бизнес-центр', false),
(4, 'Meeting Rooms', 'Переговорные комнаты', false),
(5, 'Wheelchair Accessible', 'Доступ для инвалидов', true);

Номера отелей

-- Типы номеров
CREATE TABLE hotels.room_types (
    id SERIAL PRIMARY KEY,
    property_id UUID REFERENCES hotels.properties(id) ON DELETE CASCADE,
    name_en VARCHAR(100) NOT NULL,
    name_ru VARCHAR(100),
    name_uk VARCHAR(100),
    description TEXT,
    max_occupancy INTEGER NOT NULL DEFAULT 2,
    size_sqm INTEGER,                               -- Площадь в кв.м
    bed_configuration JSONB,                        -- {"queen": 1, "single": 1}
    amenities JSONB DEFAULT '[]',                   -- ["wifi", "tv", "minibar"]
    images JSONB DEFAULT '[]',                      -- URLs фотографий
    base_price DECIMAL(10,2),                      -- Базовая цена за ночь
    currency_code CHAR(3) DEFAULT 'EUR',
    active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now(),
    
    CHECK (max_occupancy > 0),
    CHECK (size_sqm IS NULL OR size_sqm > 0),
    CHECK (jsonb_typeof(bed_configuration) = 'object'),
    CHECK (jsonb_typeof(amenities) = 'array'),
    CHECK (jsonb_typeof(images) = 'array')
);

-- Индексы для поиска номеров
CREATE INDEX room_types_property_idx ON hotels.room_types (property_id);
CREATE INDEX room_types_occupancy_idx ON hotels.room_types (max_occupancy);
CREATE INDEX room_types_price_idx ON hotels.room_types (base_price) WHERE active = true;
CREATE INDEX room_types_amenities_idx ON hotels.room_types USING gin (amenities);

-- Структуры JSONB
COMMENT ON COLUMN hotels.room_types.bed_configuration IS 
'JSONB структура: {"king": 1, "queen": 0, "double": 0, "single": 2, "sofa_bed": 1}';

COMMENT ON COLUMN hotels.room_types.amenities IS 
'JSONB массив: ["wifi", "tv", "minibar", "safe", "balcony", "city_view"]';

COMMENT ON COLUMN hotels.room_types.images IS 
'JSONB массив: [{"url": "https://cdn.vitrip.store/rooms/123/1.jpg", "alt": "Bedroom", "primary": true}]';

Content Schema — Мультимедиа и переводы

Изображения

-- Изображения отелей и номеров
CREATE TABLE content.images (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    entity_type VARCHAR(20) NOT NULL,              -- 'property', 'room_type', 'amenity'
    entity_id UUID NOT NULL,                       -- ID соответствующей записи
    url TEXT NOT NULL,
    alt_text TEXT,
    caption TEXT,
    width INTEGER,
    height INTEGER,
    file_size INTEGER,                              -- Размер в байтах
    mime_type VARCHAR(50),                          -- image/jpeg, image/webp
    is_primary BOOLEAN DEFAULT false,
    sort_order INTEGER DEFAULT 0,
    metadata JSONB DEFAULT '{}',                    -- EXIF, источник и т.д.
    uploaded_at TIMESTAMPTZ DEFAULT now(),
    
    CHECK (entity_type IN ('property', 'room_type', 'amenity')),
    CHECK (width IS NULL OR width > 0),
    CHECK (height IS NULL OR height > 0),
    CHECK (file_size IS NULL OR file_size > 0)
);

-- Индексы для загрузки изображений
CREATE INDEX images_entity_idx ON content.images (entity_type, entity_id, sort_order);
CREATE INDEX images_primary_idx ON content.images (entity_type, entity_id) WHERE is_primary = true;
CREATE INDEX images_mime_type_idx ON content.images (mime_type);

-- Ограничение: только одно primary изображение на сущность
CREATE UNIQUE INDEX images_primary_unique_idx ON content.images (entity_type, entity_id) 
WHERE is_primary = true;

Переводы

-- Переводы контента для мультиязычности
CREATE TABLE content.translations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    entity_type VARCHAR(20) NOT NULL,              -- 'property', 'room_type', 'amenity'
    entity_id UUID NOT NULL,
    field_name VARCHAR(50) NOT NULL,               -- 'name', 'description', 'address'
    language CHAR(2) NOT NULL,                     -- ru, en, uk, cs
    content TEXT NOT NULL,
    auto_translated BOOLEAN DEFAULT false,         -- Машинный перевод
    verified BOOLEAN DEFAULT false,               -- Проверен носителем языка
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now(),
    
    UNIQUE(entity_type, entity_id, field_name, language),
    CHECK (entity_type IN ('property', 'room_type', 'amenity', 'description')),
    CHECK (language IN ('ru', 'en', 'uk', 'cs', 'de', 'fr'))
);

-- Индексы для быстрого получения переводов
CREATE INDEX translations_entity_lang_idx ON content.translations (entity_type, entity_id, language);
CREATE INDEX translations_field_lang_idx ON content.translations (field_name, language);
CREATE INDEX translations_auto_idx ON content.translations (auto_translated) WHERE auto_translated = true;

-- Функция для получения перевода с fallback
CREATE OR REPLACE FUNCTION content.get_translation(
    p_entity_type VARCHAR(20),
    p_entity_id UUID,
    p_field_name VARCHAR(50),
    p_language CHAR(2),
    p_fallback_language CHAR(2) DEFAULT 'en'
) RETURNS TEXT AS $$
DECLARE
    v_content TEXT;
BEGIN
    -- Попытка получить перевод на запрашиваемом языке
    SELECT content INTO v_content
    FROM content.translations
    WHERE entity_type = p_entity_type
      AND entity_id = p_entity_id
      AND field_name = p_field_name
      AND language = p_language;
      
    -- Если не найден, использовать fallback язык
    IF v_content IS NULL THEN
        SELECT content INTO v_content
        FROM content.translations
        WHERE entity_type = p_entity_type
          AND entity_id = p_entity_id
          AND field_name = p_field_name
          AND language = p_fallback_language;
    END IF;
    
    RETURN v_content;
END;
$$ LANGUAGE plpgsql;

Users Schema — Пользователи и права

Пользователи системы

-- Пользователи (турагенты, администраторы)
CREATE TABLE users.profiles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255),                     -- bcrypt hash
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    language CHAR(2) DEFAULT 'ru',
    timezone VARCHAR(50) DEFAULT 'Europe/Kiev',
    status VARCHAR(20) DEFAULT 'active',            -- active, suspended, pending
    email_verified BOOLEAN DEFAULT false,
    phone_verified BOOLEAN DEFAULT false,
    last_login_at TIMESTAMPTZ,
    login_count INTEGER DEFAULT 0,
    preferences JSONB DEFAULT '{}',
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now(),
    
    CHECK (status IN ('active', 'suspended', 'pending', 'deleted')),
    CHECK (language IN ('ru', 'en', 'uk', 'cs'))
);

-- Индексы для аутентификации и поиска
CREATE INDEX users_profiles_email_idx ON users.profiles (email);
CREATE INDEX users_profiles_status_idx ON users.profiles (status);
CREATE INDEX users_profiles_login_idx ON users.profiles (last_login_at DESC) WHERE status = 'active';

-- Структура preferences
COMMENT ON COLUMN users.profiles.preferences IS 
'JSONB структура: {"currency": "EUR", "notifications_email": true, "default_markup": 15, "search_radius": 50}';

Роли и права доступа

-- Роли в системе
CREATE TABLE users.roles (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,
    slug VARCHAR(50) UNIQUE NOT NULL,
    description TEXT,
    permissions JSONB DEFAULT '[]',                 -- Массив разрешений
    created_at TIMESTAMPTZ DEFAULT now()
);

-- Связь пользователей и ролей (many-to-many)
CREATE TABLE users.user_roles (
    user_id UUID REFERENCES users.profiles(id) ON DELETE CASCADE,
    role_id INTEGER REFERENCES users.roles(id),
    granted_at TIMESTAMPTZ DEFAULT now(),
    granted_by UUID,
    expires_at TIMESTAMPTZ,
    
    PRIMARY KEY (user_id, role_id)
);

-- Индексы для проверки прав
CREATE INDEX user_roles_user_idx ON users.user_roles (user_id);
CREATE INDEX user_roles_role_idx ON users.user_roles (role_id);
CREATE INDEX user_roles_expires_idx ON users.user_roles (expires_at) WHERE expires_at IS NOT NULL;

-- Базовые роли
INSERT INTO users.roles (name, slug, description, permissions) VALUES
('Administrator', 'admin', 'Полные права администратора', '["*"]'),
('Travel Agent', 'agent', 'Турагент с правами поиска и бронирования', 
 '["search.hotels", "booking.create", "booking.view_own", "tours.create", "users.view_own"]'),
('Manager', 'manager', 'Менеджер агентства', 
 '["search.hotels", "booking.*", "tours.*", "users.view_team", "reports.view"]'),
('Support', 'support', 'Служба поддержки', 
 '["search.hotels", "booking.view", "booking.modify", "users.view", "support.chat"]');

-- Функция проверки прав
CREATE OR REPLACE FUNCTION users.has_permission(
    p_user_id UUID,
    p_permission VARCHAR(100)
) RETURNS BOOLEAN AS $$
DECLARE
    v_has_permission BOOLEAN := false;
    v_permissions JSONB;
BEGIN
    -- Проверка через активные роли пользователя
    SELECT INTO v_has_permission
    EXISTS(
        SELECT 1
        FROM users.user_roles ur
        JOIN users.roles r ON ur.role_id = r.id
        WHERE ur.user_id = p_user_id
          AND (ur.expires_at IS NULL OR ur.expires_at > now())
          AND (
              r.permissions ? '*' OR  -- Полные права
              r.permissions ? p_permission OR  -- Точное совпадение
              EXISTS(  -- Wildcard проверка (например, "booking.*")
                  SELECT 1
                  FROM jsonb_array_elements_text(r.permissions) as perm
                  WHERE perm LIKE REPLACE(p_permission, '*', '%')
                     OR p_permission LIKE REPLACE(perm, '*', '%')
              )
          )
    );
    
    RETURN COALESCE(v_has_permission, false);
END;
$$ LANGUAGE plpgsql;

Агентства и команды

-- Агентства (турфирмы)
CREATE TABLE users.agencies (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    slug VARCHAR(220) UNIQUE NOT NULL,
    legal_name VARCHAR(200),
    tax_id VARCHAR(50),
    address JSONB,
    contact_email VARCHAR(255),
    contact_phone VARCHAR(20),
    website_url TEXT,
    logo_url TEXT,
    default_markup_percent DECIMAL(5,2) DEFAULT 10.0,
    commission_percent DECIMAL(5,2) DEFAULT 5.0,
    credit_limit DECIMAL(12,2) DEFAULT 0,
    currency_code CHAR(3) DEFAULT 'EUR',
    status VARCHAR(20) DEFAULT 'active',
    settings JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now(),
    
    CHECK (status IN ('active', 'suspended', 'pending_approval', 'closed')),
    CHECK (default_markup_percent >= 0),
    CHECK (commission_percent >= 0)
);

-- Связь пользователей и агентств
CREATE TABLE users.agency_members (
    user_id UUID REFERENCES users.profiles(id) ON DELETE CASCADE,
    agency_id INTEGER REFERENCES users.agencies(id),
    position VARCHAR(100),
    is_primary BOOLEAN DEFAULT false,               -- Основной контакт агентства
    joined_at TIMESTAMPTZ DEFAULT now(),
    
    PRIMARY KEY (user_id, agency_id)
);

-- Индексы для агентств
CREATE INDEX agencies_slug_idx ON users.agencies (slug);
CREATE INDEX agencies_status_idx ON users.agencies (status);
CREATE INDEX agency_members_agency_idx ON users.agency_members (agency_id);
CREATE INDEX agency_members_user_idx ON users.agency_members (user_id);

-- Ограничение: только один primary contact на агентство
CREATE UNIQUE INDEX agency_members_primary_unique_idx ON users.agency_members (agency_id) 
WHERE is_primary = true;

Bookings Schema — Бронирования

Основные бронирования

-- Бронирования отелей согласно business_services.md
CREATE TABLE bookings.reservations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    confirmation_code VARCHAR(20) UNIQUE NOT NULL, -- Код бронирования для клиента
    supplier_booking_id VARCHAR(100),              -- ID бронирования у поставщика
    
    -- Отель и номер
    property_id UUID REFERENCES hotels.properties(id),
    room_type_id INTEGER REFERENCES hotels.room_types(id),
    
    -- Пользователь и агентство
    user_id UUID REFERENCES users.profiles(id),
    agency_id INTEGER REFERENCES users.agencies(id),
    
    -- Даты пребывания
    check_in DATE NOT NULL,
    check_out DATE NOT NULL,
    nights INTEGER GENERATED ALWAYS AS (check_out - check_in) STORED,
    
    -- Гости
    adults INTEGER NOT NULL DEFAULT 1,
    children INTEGER DEFAULT 0,
    guest_details JSONB NOT NULL,                   -- Информация о гостях
    
    -- Цены и платежи
    base_price DECIMAL(10,2) NOT NULL,
    markup_amount DECIMAL(10,2) DEFAULT 0,
    total_amount DECIMAL(10,2) GENERATED ALWAYS AS (base_price + markup_amount) STORED,
    currency_code CHAR(3) NOT NULL DEFAULT 'EUR',
    
    -- Статус и даты
    status VARCHAR(20) DEFAULT 'pending',           -- pending, confirmed, cancelled, completed
    created_at TIMESTAMPTZ DEFAULT now(),
    confirmed_at TIMESTAMPTZ,
    cancelled_at TIMESTAMPTZ,
    cancellation_reason TEXT,
    
    -- Дополнительная информация
    special_requests TEXT,
    internal_notes TEXT,                            -- Заметки агента
    metadata JSONB DEFAULT '{}',
    
    -- Ограничения
    CHECK (check_out > check_in),
    CHECK (nights > 0),
    CHECK (adults > 0),
    CHECK (children >= 0),
    CHECK (base_price > 0),
    CHECK (markup_amount >= 0),
    CHECK (status IN ('pending', 'confirmed', 'cancelled', 'completed', 'failed')),
    CHECK (jsonb_typeof(guest_details) = 'object')
);

-- Индексы для поиска бронирований
CREATE INDEX reservations_confirmation_idx ON bookings.reservations (confirmation_code);
CREATE INDEX reservations_user_idx ON bookings.reservations (user_id, created_at DESC);
CREATE INDEX reservations_agency_idx ON bookings.reservations (agency_id, created_at DESC);
CREATE INDEX reservations_property_idx ON bookings.reservations (property_id, check_in);
CREATE INDEX reservations_status_idx ON bookings.reservations (status, created_at DESC);
CREATE INDEX reservations_dates_idx ON bookings.reservations (check_in, check_out);
CREATE INDEX reservations_guest_details_idx ON bookings.reservations USING gin (guest_details);

-- Структура guest_details
COMMENT ON COLUMN bookings.reservations.guest_details IS 
'JSONB структура: {"primary_guest": {"first_name": "John", "last_name": "Doe", "email": "john@example.com", "phone": "+420123456789"}, "additional_guests": [{"first_name": "Jane", "last_name": "Doe", "age": 30}]}';

-- Функция генерации confirmation_code
CREATE OR REPLACE FUNCTION bookings.generate_confirmation_code()
RETURNS TEXT AS $$
DECLARE
    v_code TEXT;
    v_exists BOOLEAN;
BEGIN
    LOOP
        -- Генерация кода: 2 буквы + 6 цифр (например, VT123456)
        v_code := 'VT' || lpad((random() * 999999)::INTEGER::TEXT, 6, '0');
        
        SELECT EXISTS(SELECT 1 FROM bookings.reservations WHERE confirmation_code = v_code)
        INTO v_exists;
        
        EXIT WHEN NOT v_exists;
    END LOOP;
    
    RETURN v_code;
END;
$$ LANGUAGE plpgsql;

-- Триггер для автоматической генерации confirmation_code
CREATE OR REPLACE FUNCTION bookings.set_confirmation_code()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.confirmation_code IS NULL THEN
        NEW.confirmation_code := bookings.generate_confirmation_code();
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_reservation_confirmation_code
    BEFORE INSERT ON bookings.reservations
    FOR EACH ROW EXECUTE FUNCTION bookings.set_confirmation_code();

Платежи и транзакции

-- Платежи по бронированиям
CREATE TABLE bookings.payments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    reservation_id UUID REFERENCES bookings.reservations(id) ON DELETE CASCADE,
    amount DECIMAL(10,2) NOT NULL,
    currency_code CHAR(3) NOT NULL,
    payment_method VARCHAR(50) NOT NULL,           -- card, bank_transfer, cash, crypto
    provider VARCHAR(50),                          -- stripe, liqpay, monobank
    provider_transaction_id VARCHAR(200),
    status VARCHAR(20) DEFAULT 'pending',          -- pending, completed, failed, refunded
    processed_at TIMESTAMPTZ,
    failure_reason TEXT,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT now(),
    
    CHECK (amount > 0),
    CHECK (status IN ('pending', 'completed', 'failed', 'refunded', 'partially_refunded')),
    CHECK (payment_method IN ('card', 'bank_transfer', 'cash', 'crypto', 'agent_credit'))
);

-- Индексы для платежей
CREATE INDEX payments_reservation_idx ON bookings.payments (reservation_id, created_at DESC);
CREATE INDEX payments_status_idx ON bookings.payments (status, created_at DESC);
CREATE INDEX payments_provider_idx ON bookings.payments (provider, provider_transaction_id);

-- Возвраты средств
CREATE TABLE bookings.refunds (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    payment_id UUID REFERENCES bookings.payments(id),
    amount DECIMAL(10,2) NOT NULL,
    currency_code CHAR(3) NOT NULL,
    reason TEXT NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    processed_at TIMESTAMPTZ,
    provider_refund_id VARCHAR(200),
    created_by UUID REFERENCES users.profiles(id),
    created_at TIMESTAMPTZ DEFAULT now(),
    
    CHECK (amount > 0),
    CHECK (status IN ('pending', 'completed', 'failed'))
);

CREATE INDEX refunds_payment_idx ON bookings.refunds (payment_id, created_at DESC);
CREATE INDEX refunds_status_idx ON bookings.refunds (status, created_at DESC);

Туры и программы

-- Туры (составные программы) согласно business_services.md TourBuilderService
CREATE TABLE bookings.tours (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(200) NOT NULL,
    description TEXT,
    
    -- Создатель и агентство
    user_id UUID REFERENCES users.profiles(id),
    agency_id INTEGER REFERENCES users.agencies(id),
    
    -- Даты тура
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    duration_days INTEGER GENERATED ALWAYS AS (end_date - start_date + 1) STORED,
    
    -- Участники
    max_participants INTEGER,
    current_participants INTEGER DEFAULT 0,
    min_age INTEGER,
    max_age INTEGER,
    
    -- Цены
    base_price_per_person DECIMAL(10,2),
    markup_percent DECIMAL(5,2) DEFAULT 0,
    total_price DECIMAL(10,2),
    currency_code CHAR(3) DEFAULT 'EUR',
    
    -- Статус
    status VARCHAR(20) DEFAULT 'draft',            -- draft, published, active, completed, cancelled
    published_at TIMESTAMPTZ,
    
    -- Настройки
    auto_confirm_bookings BOOLEAN DEFAULT false,
    allow_partial_booking BOOLEAN DEFAULT true,
    
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now(),
    
    CHECK (end_date >= start_date),
    CHECK (duration_days > 0),
    CHECK (max_participants IS NULL OR max_participants > 0),
    CHECK (current_participants >= 0),
    CHECK (base_price_per_person IS NULL OR base_price_per_person > 0),
    CHECK (status IN ('draft', 'published', 'active', 'completed', 'cancelled'))
);

-- Дни тура с отелями и активностями
CREATE TABLE bookings.tour_days (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tour_id UUID REFERENCES bookings.tours(id) ON DELETE CASCADE,
    day_number INTEGER NOT NULL,
    date DATE NOT NULL,
    city VARCHAR(100),
    
    -- Размещение
    property_id UUID REFERENCES hotels.properties(id),
    room_type_id INTEGER REFERENCES hotels.room_types(id),
    check_in_date DATE,
    check_out_date DATE,
    
    -- Программа дня
    title VARCHAR(200),
    description TEXT,
    activities JSONB DEFAULT '[]',                  -- Активности дня
    meals JSONB DEFAULT '{}',                       -- Питание (завтрак, обед, ужин)
    transport JSONB DEFAULT '{}',                   -- Транспорт
    
    -- Временные рамки
    start_time TIME,
    end_time TIME,
    
    created_at TIMESTAMPTZ DEFAULT now(),
    
    PRIMARY KEY (tour_id, day_number),
    CHECK (day_number > 0),
    CHECK (jsonb_typeof(activities) = 'array'),
    CHECK (jsonb_typeof(meals) = 'object'),
    CHECK (jsonb_typeof(transport) = 'object')
);

-- Индексы для туров
CREATE INDEX tours_user_idx ON bookings.tours (user_id, created_at DESC);
CREATE INDEX tours_agency_idx ON bookings.tours (agency_id, created_at DESC);
CREATE INDEX tours_status_idx ON bookings.tours (status, start_date);
CREATE INDEX tours_dates_idx ON bookings.tours (start_date, end_date);
CREATE INDEX tour_days_tour_idx ON bookings.tour_days (tour_id, day_number);
CREATE INDEX tour_days_property_idx ON bookings.tour_days (property_id, date);

-- Структура activities
COMMENT ON COLUMN bookings.tour_days.activities IS 
'JSONB массив: [{"time": "09:00", "title": "Экскурсия по Старому городу", "duration": 180, "price": 25.0, "included": true}]';

-- Структура meals  
COMMENT ON COLUMN bookings.tour_days.meals IS 
'JSONB объект: {"breakfast": {"included": true, "venue": "Hotel restaurant"}, "lunch": {"included": false, "suggestions": ["Local cafe"]}, "dinner": {"included": true, "venue": "Traditional restaurant"}}';

Партиционирование и оптимизация

Партиционирование больших таблиц

-- Партиционирование reservations по месяцам для производительности
-- Сначала создаем новую партиционированную таблицу
CREATE TABLE bookings.reservations_partitioned (
    LIKE bookings.reservations INCLUDING ALL
) PARTITION BY RANGE (created_at);

-- Создание партиций для текущего и будущих лет
CREATE TABLE bookings.reservations_2026_01 PARTITION OF bookings.reservations_partitioned
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE bookings.reservations_2026_02 PARTITION OF bookings.reservations_partitioned
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
CREATE TABLE bookings.reservations_2026_03 PARTITION OF bookings.reservations_partitioned
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');
CREATE TABLE bookings.reservations_2026_04 PARTITION OF bookings.reservations_partitioned
    FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');
-- ... продолжение для всех месяцев

-- Функция для автоматического создания партиций
CREATE OR REPLACE FUNCTION bookings.create_reservation_partitions()
RETURNS void AS $$
DECLARE
    start_date date;
    end_date date;
    partition_name text;
BEGIN
    -- Создание партиций на следующие 12 месяцев
    FOR i IN 0..11 LOOP
        start_date := date_trunc('month', now()) + (i || ' months')::interval;
        end_date := start_date + interval '1 month';
        partition_name := 'reservations_' || to_char(start_date, 'YYYY_MM');
        
        EXECUTE format('
            CREATE TABLE IF NOT EXISTS bookings.%I PARTITION OF bookings.reservations_partitioned
            FOR VALUES FROM (%L) TO (%L)',
            partition_name, start_date, end_date);
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- Планировщик для автоматического создания партиций (выполнять через cron)
-- SELECT bookings.create_reservation_partitions();

Представления для удобства работы

-- Представление для поиска отелей с переводами
CREATE VIEW hotels.properties_with_translations AS
SELECT 
    p.id,
    p.slug,
    p.coordinates,
    p.star_rating,
    p.property_type,
    p.status,
    p.features,
    
    -- Название на разных языках
    COALESCE(t_name_ru.content, p.name) as name_ru,
    COALESCE(t_name_en.content, p.name) as name_en,
    COALESCE(t_name_uk.content, p.name) as name_uk,
    
    -- Описание на разных языках
    t_desc_ru.content as description_ru,
    t_desc_en.content as description_en,
    t_desc_uk.content as description_uk,
    
    p.address,
    p.created_at,
    p.updated_at
    
FROM hotels.properties p
LEFT JOIN content.translations t_name_ru ON (
    t_name_ru.entity_type = 'property' AND 
    t_name_ru.entity_id = p.id AND 
    t_name_ru.field_name = 'name' AND 
    t_name_ru.language = 'ru'
)
LEFT JOIN content.translations t_name_en ON (
    t_name_en.entity_type = 'property' AND 
    t_name_en.entity_id = p.id AND 
    t_name_en.field_name = 'name' AND 
    t_name_en.language = 'en'
)
LEFT JOIN content.translations t_name_uk ON (
    t_name_uk.entity_type = 'property' AND 
    t_name_uk.entity_id = p.id AND 
    t_name_uk.field_name = 'name' AND 
    t_name_uk.language = 'uk'
)
LEFT JOIN content.translations t_desc_ru ON (
    t_desc_ru.entity_type = 'property' AND 
    t_desc_ru.entity_id = p.id AND 
    t_desc_ru.field_name = 'description' AND 
    t_desc_ru.language = 'ru'
)
LEFT JOIN content.translations t_desc_en ON (
    t_desc_en.entity_type = 'property' AND 
    t_desc_en.entity_id = p.id AND 
    t_desc_en.field_name = 'description' AND 
    t_desc_en.language = 'en'
)
LEFT JOIN content.translations t_desc_uk ON (
    t_desc_uk.entity_type = 'property' AND 
    t_desc_uk.entity_id = p.id AND 
    t_desc_uk.field_name = 'description' AND 
    t_desc_uk.language = 'uk'
);

-- Представление для активных бронирований с деталями
CREATE VIEW bookings.active_reservations_detailed AS
SELECT 
    r.id,
    r.confirmation_code,
    r.check_in,
    r.check_out,
    r.nights,
    r.adults,
    r.children,
    r.total_amount,
    r.currency_code,
    r.status,
    r.created_at,
    
    -- Отель
    p.name as hotel_name,
    p.address as hotel_address,
    p.coordinates as hotel_coordinates,
    
    -- Пользователь
    u.first_name,
    u.last_name,
    u.email,
    
    -- Агентство
    a.name as agency_name,
    
    -- Тип номера
    rt.name_en as room_type_name,
    rt.max_occupancy
    
FROM bookings.reservations r
JOIN hotels.properties p ON r.property_id = p.id
JOIN users.profiles u ON r.user_id = u.id
JOIN users.agencies a ON r.agency_id = a.id
LEFT JOIN hotels.room_types rt ON r.room_type_id = rt.id
WHERE r.status IN ('confirmed', 'pending')
  AND r.check_out >= CURRENT_DATE;

Связанная документация

• Общая архитектура: технологический стек PostgreSQL из Архитектура платформы
• Storage Layer: схемы и таблицы hotels, bookings, users из Storage Layer
• Business Services: PostGIS функции ST_Distance, ST_MakePoint из Business Services
• Ingestion Layer: hotel matching алгоритм и hotels.supplier_mapping из Ingestion Layer
• API Layer: REST endpoints работают с этими схемами через API Layer
• Suppliers Layer: supplier_mapping таблица для адаптеров из Suppliers Layer
• Clients Layer: веб-приложение использует эти данные через API из Clients Layer