PTX Channel Manager

English

Tiếng Việt

English

Tiếng Việt

Appearance

Database Design (DB_DESIGN) ​

Project: PTX Channel Manager (ptx-cm)
Version: 3.0.5
Date: 2026-06-05
Database: PostgreSQL 16
ORM: Prisma 7 (implementation detail, schema is standard SQL)

1. ER Diagram ​

2. Table Definitions (DDL) ​

2.1 Enums ​

sql
CREATE TYPE ota_type AS ENUM ('booking', 'trip', 'expedia', 'agoda_hotel', 'agoda_homes', 'manual');

CREATE TYPE connection_status AS ENUM ('active', 'expired', 'error', 'requires_2fa');

CREATE TYPE two_factor_method AS ENUM ('none', 'totp', 'manual');

CREATE TYPE sync_job_type AS ENUM ('pull_bookings', 'push_availability', 'push_rates', 'verify');

CREATE TYPE sync_job_status AS ENUM ('pending', 'running', 'completed', 'failed');

CREATE TYPE alert_type AS ENUM ('overbooking', 'sync_failure', 'session_expired', 'sla_breach', 'missing_deposit', 'missing_contact', 'unconfirmed_checkin', 'payment_mismatch');

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

CREATE TYPE audit_action AS ENUM ('create', 'update', 'delete');

CREATE TYPE rate_rule_type AS ENUM ('markup', 'discount', 'seasonal');

CREATE TYPE department_function AS ENUM ('customer_support', 'purchasing', 'finance', 'ota', 'sales', 'operations', 'ground_ops', 'other');

CREATE TYPE property_sales_status AS ENUM ('SELLING', 'STOP_SELL', 'NOT_SELL');

CREATE TYPE conv_kind AS ENUM ('DM', 'CHANNEL', 'BOOKING');

CREATE TYPE part_role AS ENUM ('MEMBER', 'ADMIN');

CREATE TYPE notification_type AS ENUM ('ALERT', 'BOOKING_EVENT', 'CHAT_MENTION', 'CHAT_DM', 'SYSTEM');

2.2 refresh_tokens (E-01b) — NEW in Phase 04 ​

sql
CREATE TABLE refresh_tokens (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    jti         UUID NOT NULL UNIQUE,             -- JWT ID for token tracking
    user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    expires_at  TIMESTAMPTZ NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_refresh_tokens_user ON refresh_tokens(user_id);
CREATE INDEX idx_refresh_tokens_expires ON refresh_tokens(expires_at);

Purpose: Store issued refresh tokens for revocation and session tracking. Allows logout/password-change to invalidate all active sessions.

Fields:

  • jti - JWT ID (unique per token), matched against token payload for revocation check
  • user_id - Owner of token
  • expires_at - Token expiration (auto-cleanup possible)

Flow:

  1. Login: Create refresh_token row, include jti in JWT
  2. Refresh: Verify jti exists in DB, issue new token with new jti
  3. Logout: Delete row by jti → revocation
  4. Password change: Delete all user's rows → invalidate all sessions

2.2b password_reset_tokens (E-01c) — NEW in Phase 2026-02-13 ​

sql
CREATE TABLE password_reset_tokens (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    token_hash  VARCHAR(64) NOT NULL UNIQUE,      -- SHA-256 hash of random token
    user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    expires_at  TIMESTAMPTZ NOT NULL,              -- 1 hour expiry
    used_at     TIMESTAMPTZ,                       -- NULL = not yet used; set when token consumed
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_password_reset_tokens_user ON password_reset_tokens(user_id);
CREATE INDEX idx_password_reset_tokens_expires ON password_reset_tokens(expires_at);

Purpose: Store password reset tokens for admin-initiated and self-service password resets. Token hashes stored using SHA-256 for security.

Fields:

  • token_hash - SHA-256 hash of random 32-byte token (prevents plaintext token exposure)
  • user_id - User who can use this token
  • expires_at - Token expires after 1 hour
  • used_at - Timestamp when token was consumed (NULL = not yet used; one-time use enforced)

Security Features:

  • Rate limiting: Max 3 tokens per user per hour
  • Token hashing: SHA-256 prevents database compromise exposure
  • One-time use: is_used flag prevents reuse
  • Auto-cleanup: Daily cron job deletes expired/used tokens
  • Session invalidation: Password reset clears all refresh tokens

Admin Flows:

  1. Temp Password Reset: Admin sets temp password + mustChangePassword=true → User forced to change on next login
  2. Email Reset Link: Admin generates token → Email sent with reset link → User completes reset via public page

Self-Service Flow:

  1. Forgot password: User enters email → Token generated → Email sent
  2. User clicks link → Validates token → Sets new password
  3. Token marked as used, all sessions invalidated

2.2c roles (E-14) ​

sql
CREATE TABLE roles (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name        VARCHAR(50) NOT NULL UNIQUE,
    label       VARCHAR(100) NOT NULL,
    description TEXT,
    permissions JSONB NOT NULL DEFAULT '{}',   -- { "module": bitmask }
    is_system   BOOLEAN NOT NULL DEFAULT false,
    is_active   BOOLEAN NOT NULL DEFAULT true,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_roles_name ON roles(name);
CREATE INDEX idx_roles_active ON roles(is_active);

Purpose: Named roles with bitwise JSONB permissions per module. 7 preset roles seeded with is_system=true.

Permission Format:

json
{ "properties": 15, "bookings": 5, "users": 1 }

Bitmask: VIEW=1, CREATE=2, EDIT=4, DELETE=8

2.3 users (E-01) ​

sql
CREATE TABLE users (
    id                   UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email                VARCHAR(255) NOT NULL UNIQUE,
    password_hash        VARCHAR(255) NOT NULL,
    name                 VARCHAR(255) NOT NULL,
    role_id              UUID NOT NULL REFERENCES roles(id),
    country              VARCHAR(2),                 -- nullable (null = all countries/manager)
    locale               VARCHAR(5) NOT NULL DEFAULT 'en',  -- vi, id, ms, en
    date_format          VARCHAR(30) NOT NULL DEFAULT 'yyyy-MM-dd',
    company_id           UUID NOT NULL REFERENCES companies(id) ON DELETE RESTRICT,
    is_active            BOOLEAN NOT NULL DEFAULT true,
    must_change_password BOOLEAN NOT NULL DEFAULT false,
    created_at           TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at           TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_users_country ON users(country);
CREATE INDEX idx_users_role ON users(role_id);
CREATE INDEX idx_users_company ON users(company_id);

Key Fields:

  • role_id - FK to roles table (replaced old role enum)
  • company_id - FK to companies (org hierarchy, FR-59); NOT NULL enforced
  • date_format - User-specific date format preference (e.g., yyyy-MM-dd, dd/MM/yyyy)
  • must_change_password - When true, user is redirected to /change-password after login

2.3 properties (E-02) ​

sql
CREATE TABLE properties (
    id                   UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    internal_code        VARCHAR(10) NOT NULL UNIQUE,  -- {COUNTRY}-XXXXXX (e.g., MY-22846F)
    name                 VARCHAR(255) NOT NULL,
    country              VARCHAR(2) NOT NULL,  -- ISO 3166-1 alpha-2 (VN, ID, MY)
    timezone             VARCHAR(50) NOT NULL,  -- IANA timezone (Asia/Ho_Chi_Minh, Asia/Jakarta, Asia/Kuala_Lumpur)
    currency             VARCHAR(3) NOT NULL,   -- ISO 4217 (VND, IDR, MYR)
    address              TEXT,
    city                 VARCHAR(100),
    building_name        VARCHAR(255),
    manager_id           UUID REFERENCES users(id) ON DELETE SET NULL,
    company_id           UUID NOT NULL REFERENCES companies(id) ON DELETE RESTRICT,
    city_id              UUID REFERENCES cities(id) ON DELETE SET NULL,
    district_id          UUID REFERENCES districts(id) ON DELETE SET NULL,
    google_address_link  VARCHAR,
    sales_status         property_sales_status NOT NULL DEFAULT 'SELLING',
    latitude             FLOAT,
    longitude            FLOAT,
    is_active            BOOLEAN NOT NULL DEFAULT true,
    created_at           TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at           TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_properties_country ON properties(country);
CREATE INDEX idx_properties_active ON properties(is_active);
CREATE INDEX idx_properties_manager ON properties(manager_id);
CREATE INDEX idx_properties_company ON properties(company_id);
CREATE INDEX idx_properties_city ON properties(city_id);
CREATE INDEX idx_properties_district ON properties(district_id);

2.4 room_types (E-03) ​

sql
CREATE TABLE room_types (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    property_id     UUID NOT NULL REFERENCES properties(id) ON DELETE CASCADE,
    name            VARCHAR(255) NOT NULL,
    base_rate       INTEGER NOT NULL,        -- smallest currency unit
    total_rooms     INTEGER NOT NULL,
    max_occupancy   INTEGER NOT NULL DEFAULT 2,
    is_active       BOOLEAN NOT NULL DEFAULT true,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT uq_room_type_name UNIQUE (property_id, name)
);

CREATE INDEX idx_room_types_property ON room_types(property_id);

2.5 ota_accounts (E-04) — NEW ​

sql
CREATE TABLE ota_accounts (
    id                      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    ota_type                ota_type NOT NULL,
    label                   VARCHAR(255) NOT NULL,       -- e.g. "Booking.com - Vietnam North"
    credentials_encrypted   BYTEA NOT NULL,              -- AES-256 encrypted JSON
    two_factor_method       two_factor_method NOT NULL DEFAULT 'none',
    two_factor_secret       VARCHAR(255),                -- TOTP secret (encrypted)
    session_data            JSONB,                        -- cookies, tokens, headers
    status                  connection_status NOT NULL DEFAULT 'expired',
    last_session_refresh    TIMESTAMPTZ,
    user_id                 UUID NOT NULL REFERENCES users(id),  -- owner
    country_code            VARCHAR(2),                   -- VN, ID, MY (nullable for multi-country)
    created_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT uq_ota_account_label UNIQUE (user_id, ota_type, label)
);

CREATE INDEX idx_ota_accounts_user ON ota_accounts(user_id);
CREATE INDEX idx_ota_accounts_status ON ota_accounts(status);
CREATE INDEX idx_ota_accounts_country ON ota_accounts(country_code) WHERE country_code IS NOT NULL;

OTA Account Validation (Gated by COUNTRY_SCOPE_ENABLED flag): OTA account creation and country assignment are validated against an OTA×country availability matrix (VN/ID/MY) and caller scope permissions. This behavior is feature-flagged and requires COUNTRY_SCOPE_ENABLED to be active.

2.6 ota_connections (E-05) — SIMPLIFIED ​

sql
CREATE TABLE ota_connections (
    id                      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    property_id             UUID REFERENCES properties(id) ON DELETE CASCADE,
    ota_account_id          UUID NOT NULL REFERENCES ota_accounts(id) ON DELETE CASCADE,
    ota_property_id         VARCHAR(255) NOT NULL,       -- OTA's internal property ID
    is_active               BOOLEAN NOT NULL DEFAULT true,

    -- Lark Metadata (25 columns imported from source, schema-driven for resilience)
    ota_property_name       VARCHAR(255),
    ptx_sync                VARCHAR(50),
    property_link           VARCHAR(1000),
    bank                    VARCHAR(100),
    source_status           VARCHAR(100),
    ota_status              VARCHAR(100),
    rate_plan               VARCHAR(100),
    price_ranking           VARCHAR(100),
    competition_rate        VARCHAR(100),
    review_score            FLOAT,
    total_review            INT,
    check_in_way            VARCHAR(255),
    member                  VARCHAR(255),
    mobile_xpos             VARCHAR(255),
    basic_deal_custom       VARCHAR(255),
    package_deal            VARCHAR(255),
    campaign_layer_4        VARCHAR(255),
    sieu_campaign_layer_5   VARCHAR(255),
    lastmin                 VARCHAR(255),
    early_bird              VARCHAR(255),
    long_stay_min_3         VARCHAR(255),
    long_stay_min_5         VARCHAR(255),
    long_stay_min_7         VARCHAR(255),
    booking_links           TEXT,
    traffic_boost           FLOAT,

    created_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT uq_ota_account_ota_property UNIQUE (ota_account_id, ota_property_id)
);

CREATE INDEX idx_ota_conn_property ON ota_connections(property_id);
CREATE INDEX idx_ota_conn_account ON ota_connections(ota_account_id);
CREATE INDEX idx_ota_conn_active ON ota_connections(is_active);

Lark Metadata Note: The 25 Lark metadata columns are imported during OTA pull-sync and provide denormalized insights for reporting. These are read-only copies from the Lark source system and should not be edited directly in PTX CM.

2.7 ota_room_mappings (E-06) ​

sql
CREATE TABLE ota_room_mappings (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    room_type_id        UUID NOT NULL REFERENCES room_types(id) ON DELETE CASCADE,
    ota_connection_id   UUID NOT NULL REFERENCES ota_connections(id) ON DELETE CASCADE,
    ota_room_id         VARCHAR(255) NOT NULL,
    ota_rate_plan_id    VARCHAR(255) NOT NULL,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT uq_room_mapping UNIQUE (room_type_id, ota_connection_id)
);

CREATE INDEX idx_ota_mapping_connection ON ota_room_mappings(ota_connection_id);

2.7b ota_listing_details (Source List — NEW 2026-06-05) ​

sql
CREATE TABLE ota_listing_details (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    internal_code       VARCHAR(10) NOT NULL UNIQUE,    -- LD-xxxxxx, auto-generated
    name                VARCHAR(255) NOT NULL,
    description         TEXT,

    -- Relations
    room_type_id        UUID NOT NULL REFERENCES room_types(id) ON DELETE CASCADE,
    property_id         UUID NOT NULL REFERENCES properties(id) ON DELETE CASCADE,

    -- Pricing & Margins
    cost_wd             DECIMAL(12,2) NOT NULL,         -- Weekday cost
    cost_wk             DECIMAL(12,2) NOT NULL,         -- Weekend cost
    currency_name       VARCHAR(10) NOT NULL,           -- VND, IDR, MYR
    currency_rate       DECIMAL(12,4) NOT NULL,
    
    level_source        DECIMAL(5,2),                   -- Optional source level
    margin_low_ss       DECIMAL(5,4) NOT NULL,          -- Low season margin (0–1)
    margin_high_ss      DECIMAL(5,4) NOT NULL,          -- High season margin (0–1)
    margin_holi         DECIMAL(5,4) NOT NULL,          -- Holiday margin (0–1)

    -- Check-in & Policies
    check_in_file       VARCHAR(1000),
    restriction_rules   TEXT,
    check_in_way        VARCHAR(255),
    check_in_out_policy TEXT,
    child_policy        TEXT,
    cleaning_fee        DECIMAL(12,2),

    -- Specs
    room_size           DECIMAL(6,2),
    bed_type            VARCHAR(100),
    bathrooms           DECIMAL(3,1),
    max_pax             INTEGER,
    room_photos         JSONB,                          -- Array of photo URLs

    -- Amenities
    amenities           JSONB NOT NULL DEFAULT '{}',

    -- Soft delete
    is_active           BOOLEAN NOT NULL DEFAULT true,

    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_ota_listing_room_type ON ota_listing_details(room_type_id);
CREATE INDEX idx_ota_listing_property ON ota_listing_details(property_id);
CREATE INDEX idx_ota_listing_is_active ON ota_listing_details(is_active);

Purpose: Source listing inventory imported from Lark with full pricing, policies, and specifications. After initial import, the SYSTEM becomes the source of truth for edits (see Operational Note below). Entity code prefix: LD (auto-generated via generateFor('OtaListingDetail', 'LD')).

Key Fields:

  • internal_code - Unique LD-prefixed identifier for UI display and route params
  • cost_wd, cost_wk - Weekday/weekend base costs
  • margin_low_ss, margin_high_ss, margin_holi - Stored as fractions 0–1 (UI converts to/from percentage)
  • is_active - Soft-delete flag (DELETE sets false, restore via PATCH { isActive: true })

Operational Note:
After the initial Lark Source List import (via packages/database/prisma/import_lark_source_list.ts), the SYSTEM database becomes the authoritative source for ota_listing_details edits. The import script MUST NOT be re-run in overwrite/truncate mode against edited rows. Future re-imports may only INSERT new rows (preserving user edits on existing rows). This protects operational data integrity when listings are manually customized for pricing or restrictions.

Soft-Delete Behavior:

  • DELETE /api/v1/ota-listings/:id sets isActive=false
  • Restore via PATCH /api/v1/ota-listings/:id
  • List queries filter by status param (active|inactive|all; default active)

2.8 ota_formula_templates (E-23) ​

sql
CREATE TABLE ota_formula_templates (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    ota_type        VARCHAR(30) NOT NULL,
    name            VARCHAR(100) NOT NULL,
    description     VARCHAR(500),
    formula         TEXT NOT NULL,
    parameters      JSONB NOT NULL DEFAULT '[]',
    is_default      BOOLEAN NOT NULL DEFAULT false,
    is_active       BOOLEAN NOT NULL DEFAULT true,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_formula_template_ota ON ota_formula_templates(ota_type);

2.9 ota_rate_configs (E-24) ​

sql
CREATE TABLE ota_rate_configs (
    id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    property_id           UUID NOT NULL REFERENCES properties(id) ON DELETE CASCADE,
    ota_connection_id     UUID NOT NULL REFERENCES ota_connections(id) ON DELETE CASCADE,
    formula_template_id   UUID NOT NULL REFERENCES ota_formula_templates(id),
    parameter_values      JSONB NOT NULL DEFAULT '{}',
    is_active             BOOLEAN NOT NULL DEFAULT true,
    created_at            TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at            TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT uq_ota_rate_config_connection UNIQUE (ota_connection_id)
);

CREATE INDEX idx_ota_rate_config_property ON ota_rate_configs(property_id);

2.10 ota_rate_lines (E-25) ​

sql
CREATE TABLE ota_rate_lines (
    id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    rate_config_id    UUID NOT NULL REFERENCES ota_rate_configs(id) ON DELETE CASCADE,
    name              VARCHAR(100) NOT NULL,
    line_type         VARCHAR(30) NOT NULL DEFAULT 'base',
    discount_percent  DECIMAL(5,2) NOT NULL DEFAULT 0,
    sort_order        INTEGER NOT NULL DEFAULT 0,
    is_active         BOOLEAN NOT NULL DEFAULT true,
    created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at        TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_ota_rate_line_config ON ota_rate_lines(rate_config_id);

2.11 availability (E-07) ​

sql
CREATE TABLE availability (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    room_type_id    UUID NOT NULL REFERENCES room_types(id) ON DELETE CASCADE,
    date            DATE NOT NULL,
    total_rooms     INTEGER NOT NULL,
    booked_rooms    INTEGER NOT NULL DEFAULT 0,
    blocked_rooms   INTEGER NOT NULL DEFAULT 0,
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT uq_availability UNIQUE (room_type_id, date),
    CONSTRAINT chk_rooms_positive CHECK (
        total_rooms >= 0 AND booked_rooms >= 0 AND blocked_rooms >= 0
    )
);

CREATE INDEX idx_availability_room_date ON availability(room_type_id, date);
CREATE INDEX idx_availability_date ON availability(date);
-- Overbooking detection: find dates where booked >= total
CREATE INDEX idx_availability_overbooked ON availability(room_type_id, date)
    WHERE booked_rooms >= total_rooms;

2.9 bookings (E-08) ​

sql
CREATE TABLE bookings (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    internal_code       VARCHAR(10) NOT NULL UNIQUE,  -- BK-XXXXXX, auto-generated
    property_id         UUID NOT NULL REFERENCES properties(id),
    room_type_id        UUID NOT NULL REFERENCES room_types(id),
    supplier_id         UUID REFERENCES suppliers(id),              -- nullable supplier attribution
    customer_id         UUID REFERENCES customers(id),              -- nullable CRM link
    ota_type            ota_type NOT NULL,
    ota_booking_id      VARCHAR(255) NOT NULL,
    guest_name          VARCHAR(255) NOT NULL,
    guest_email         VARCHAR(255),
    guest_phone         VARCHAR(50),                    -- guest phone for CS contact
    guest_nationality   VARCHAR(2),                     -- ISO 3166-1 alpha-2
    check_in            DATE NOT NULL,
    check_out           DATE NOT NULL,
    checkin_time        TIMESTAMPTZ,                    -- actual check-in timestamp
    checkout_time       TIMESTAMPTZ,                    -- actual checkout timestamp
    num_rooms           INTEGER NOT NULL DEFAULT 1,
    num_guests          INTEGER NOT NULL DEFAULT 1,
    status              VARCHAR(50) NOT NULL DEFAULT 'not_started'  -- FK to booking_status_def.key
                        REFERENCES booking_status_def(key),
    payment_status      VARCHAR(50) NOT NULL DEFAULT 'pay_deposit',  -- 4-axis: payment track
    source_status       VARCHAR(50) NOT NULL DEFAULT 'src_confirmed', -- 4-axis: source track
    accounting_status   VARCHAR(50) NOT NULL DEFAULT 'acc_not_required', -- 4-axis: accounting track
    ota_status          VARCHAR(100),                   -- raw OTA status string (nullable)
    ota_status_updated_at TIMESTAMPTZ,                 -- when ota_status was last set
    total_amount        BIGINT NOT NULL,               -- integer, smallest currency unit
    cost_amount         BIGINT,                        -- cost price from host (nullable)
    margin              BIGINT,                        -- totalAmount - costAmount (nullable)
    currency            VARCHAR(3) NOT NULL,
    special_requests    TEXT,                          -- guest special requests
    raw_data            JSONB,                         -- original OTA response
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT uq_ota_booking UNIQUE (ota_type, ota_booking_id),
    CONSTRAINT chk_dates CHECK (check_out > check_in)
);

CREATE INDEX idx_bookings_property ON bookings(property_id);
CREATE INDEX idx_bookings_room_type ON bookings(room_type_id);
CREATE INDEX idx_bookings_supplier ON bookings(supplier_id);
CREATE INDEX idx_bookings_customer ON bookings(customer_id);
CREATE INDEX idx_bookings_ota_type ON bookings(ota_type);
CREATE INDEX idx_bookings_check_in ON bookings(check_in);
CREATE INDEX idx_bookings_status ON bookings(status);
CREATE INDEX idx_bookings_payment_status ON bookings(payment_status);
CREATE INDEX idx_bookings_source_status ON bookings(source_status);
CREATE INDEX idx_bookings_accounting_status ON bookings(accounting_status);
CREATE INDEX idx_bookings_ota_status ON bookings(ota_status);
CREATE INDEX idx_bookings_created ON bookings(created_at DESC);
-- For deduplication during polling
CREATE INDEX idx_bookings_ota_id ON bookings(ota_type, ota_booking_id);

Note: Status fields (status, payment_status, source_status, accounting_status) reference booking_status_def.key — a 4-axis workflow model allowing independent progression per department track (CS, Payment, Source, Accounting).

2.9b customers (E-19) — NEW ​

sql
CREATE TABLE customers (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    internal_code   VARCHAR(10) NOT NULL UNIQUE,  -- CU-XXXXXX, auto-generated
    name            VARCHAR(255) NOT NULL,
    email           VARCHAR(255),
    phone           VARCHAR(50),
    nationality     VARCHAR(2),                  -- ISO 3166-1 alpha-2
    notes           TEXT,
    is_active       BOOLEAN NOT NULL DEFAULT true,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_customers_name ON customers(name);
CREATE INDEX idx_customers_email ON customers(email);
CREATE INDEX idx_customers_active ON customers(is_active);

Purpose: Guest CRM records with contact information. Links to bookings via nullable bookings.customer_id for guest consolidation and history tracking.

Fields:

  • internal_code - Unique CU-prefixed identifier for UI display and route params
  • name - Customer full name
  • email - Optional email address (searched/matched on)
  • phone - Optional phone number
  • nationality - ISO country code (optional)
  • notes - Admin notes (internal use)
  • is_active - Soft-delete flag

Design Notes:

  • Soft-delete via is_active flag
  • Nullable FK in Booking allows gradual adoption (unlinked bookings stay null)
  • Fuzzy search on name/email for quick linking
  • Merge operation atomic: all bookings transferred, source soft-deleted

2.9c ota_status_def (E-20) — NEW ​

sql
CREATE TABLE ota_status_def (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    raw_status  VARCHAR(100) NOT NULL,
    ota_type    ota_type NOT NULL,
    label       VARCHAR(100) NOT NULL,
    color       VARCHAR(7) NOT NULL,           -- hex color, e.g. #22C55E
    sort_order  INTEGER NOT NULL DEFAULT 0,
    is_deleted  BOOLEAN NOT NULL DEFAULT false,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT uq_ota_status_def UNIQUE (raw_status, ota_type)
);

CREATE INDEX idx_ota_status_def_type ON ota_status_def(ota_type);
CREATE INDEX idx_ota_status_def_deleted ON ota_status_def(is_deleted);

Purpose: Maps raw OTA status strings to normalized display labels and colors for UI rendering (FR-40). Graceful fallback: if no mapping exists, raw string is shown as-is. Admin-managed via Master Data > OTA Statuses tab (S-24).

Fields:

  • raw_status - Exact status string from OTA (e.g. confirmed, cancelled_by_guest)
  • ota_type - Which OTA this mapping applies to
  • label - Human-readable display label (e.g. "Confirmed", "Guest Cancelled")
  • color - Hex color for badge rendering
  • sort_order - Display ordering within OTA type group
  • is_deleted - Soft-delete flag (unmapped statuses fall back to raw string display)

2.10 sync_jobs (E-09) ​

sql
CREATE TABLE sync_jobs (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    ota_connection_id   UUID NOT NULL REFERENCES ota_connections(id) ON DELETE CASCADE,
    job_type            sync_job_type NOT NULL,
    status              sync_job_status NOT NULL DEFAULT 'pending',
    error               TEXT,
    payload             JSONB,                 -- job-specific data
    started_at          TIMESTAMPTZ,
    completed_at        TIMESTAMPTZ,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_sync_jobs_connection ON sync_jobs(ota_connection_id);
CREATE INDEX idx_sync_jobs_status ON sync_jobs(status);
CREATE INDEX idx_sync_jobs_created ON sync_jobs(created_at DESC);
CREATE INDEX idx_sync_jobs_type_status ON sync_jobs(job_type, status);
-- For cleanup: find completed jobs older than 7 days
CREATE INDEX idx_sync_jobs_cleanup ON sync_jobs(completed_at)
    WHERE status IN ('completed', 'failed');

2.11 rates (E-10) ​

sql
CREATE TABLE rates (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    room_type_id        UUID NOT NULL REFERENCES room_types(id) ON DELETE CASCADE,
    ota_connection_id   UUID NOT NULL REFERENCES ota_connections(id) ON DELETE CASCADE,
    date                DATE NOT NULL,
    rate_amount         INTEGER NOT NULL,      -- smallest currency unit
    currency            VARCHAR(3) NOT NULL,
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT uq_rate UNIQUE (room_type_id, ota_connection_id, date),
    CONSTRAINT chk_rate_positive CHECK (rate_amount > 0)
);

CREATE INDEX idx_rates_room_date ON rates(room_type_id, date);
CREATE INDEX idx_rates_connection ON rates(ota_connection_id);

2.12 alerts (E-11) ​

sql
CREATE TABLE alerts (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    property_id     UUID NOT NULL REFERENCES properties(id),
    alert_type      alert_type NOT NULL,
    severity        alert_severity NOT NULL,
    message         TEXT NOT NULL,
    is_resolved     BOOLEAN NOT NULL DEFAULT false,
    resolved_by     UUID REFERENCES users(id),
    resolved_notes  TEXT,
    resolved_at     TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_alerts_property ON alerts(property_id);
CREATE INDEX idx_alerts_unresolved ON alerts(is_resolved, severity)
    WHERE is_resolved = false;
CREATE INDEX idx_alerts_created ON alerts(created_at DESC);

2.13 audit_logs (E-12) ​

sql
CREATE TABLE audit_logs (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    entity_type     VARCHAR(50) NOT NULL,     -- 'property', 'booking', etc.
    entity_id       VARCHAR(255) NOT NULL,    -- entity PK (UUID or string key)
    action          audit_action NOT NULL,
    old_value       JSONB,
    new_value       JSONB,
    performed_by    UUID REFERENCES users(id),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_audit_entity ON audit_logs(entity_type, entity_id);
CREATE INDEX idx_audit_created ON audit_logs(created_at DESC);
-- Partition by month for performance (optional)

2.13b activity_logs (E-18) ​

sql
CREATE TABLE activity_logs (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_email  VARCHAR(255),
    user_id     UUID REFERENCES users(id) ON DELETE SET NULL,
    method      VARCHAR(10) NOT NULL,
    path        VARCHAR(500) NOT NULL,
    status      INTEGER NOT NULL,
    screen      VARCHAR(100) NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_activity_logs_created ON activity_logs(created_at DESC);
CREATE INDEX idx_activity_logs_user_email ON activity_logs(user_email);

2.14 rate_rules (E-13) ​

sql
CREATE TABLE rate_rules (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    property_id         UUID NOT NULL REFERENCES properties(id) ON DELETE CASCADE,
    room_type_id        UUID REFERENCES room_types(id) ON DELETE CASCADE,  -- null = all
    ota_connection_id   UUID REFERENCES ota_connections(id) ON DELETE CASCADE,  -- null = all
    rule_type           rate_rule_type NOT NULL,
    value               NUMERIC(10, 2) NOT NULL,   -- percentage or fixed amount
    start_date          DATE,                        -- null = no start constraint
    end_date            DATE,                        -- null = no end constraint
    is_active           BOOLEAN NOT NULL DEFAULT true,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT chk_rule_dates CHECK (
        start_date IS NULL OR end_date IS NULL OR end_date >= start_date
    )
);

CREATE INDEX idx_rate_rules_property ON rate_rules(property_id);
CREATE INDEX idx_rate_rules_active ON rate_rules(is_active)
    WHERE is_active = true;

2.15b suppliers (E-16) ​

sql
CREATE TABLE suppliers (
    id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    supplier_code     VARCHAR(20) NOT NULL UNIQUE,
    name              VARCHAR(255) NOT NULL,
    country           VARCHAR(2) NOT NULL,
    phone             VARCHAR(50),
    email             VARCHAR(255),
    bank_name         VARCHAR(100),
    bank_account_no   VARCHAR(50),
    bank_account_name VARCHAR(255),
    airbnb_profile_url VARCHAR(500),
    notes             TEXT,
    is_active         BOOLEAN NOT NULL DEFAULT true,
    created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at        TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_suppliers_country ON suppliers(country);
CREATE INDEX idx_suppliers_active ON suppliers(is_active);

Purpose: Apartment/room suppliers (owners). Country-scoped. Linked to rooms via SupplierRoomAllocation.

2.15c supplier_room_allocations (E-17) ​

sql
CREATE TABLE supplier_room_allocations (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    supplier_id UUID NOT NULL REFERENCES suppliers(id) ON DELETE CASCADE,
    room_type_id UUID NOT NULL REFERENCES room_types(id) ON DELETE CASCADE,
    room_count  INTEGER NOT NULL,
    notes       TEXT,
    is_active   BOOLEAN NOT NULL DEFAULT true,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT uq_supplier_room_allocation UNIQUE (supplier_id, room_type_id)
);

CREATE INDEX idx_supplier_alloc_room ON supplier_room_allocations(room_type_id);
CREATE INDEX idx_supplier_alloc_active ON supplier_room_allocations(is_active);

Purpose: M:N junction between Supplier and RoomType with room count. Soft warning when SUM(roomCount) != totalRooms.

2.15d booking_status_def (E-08b) — UPDATED 2026-06-05 ​

sql
CREATE TABLE booking_status_def (
    key                  VARCHAR(50) PRIMARY KEY,
    label                VARCHAR(100) NOT NULL,
    color                VARCHAR(7) NOT NULL,           -- hex color
    icon                 VARCHAR(50),
    department           VARCHAR(20) NOT NULL DEFAULT 'cs',  -- cs, payment, source, accounting
    transitions          JSONB NOT NULL DEFAULT '[]',  -- [{toKey, label, allowedRoles, rules}]
    sort_order           INTEGER NOT NULL DEFAULT 0,
    is_default           BOOLEAN NOT NULL DEFAULT false,
    is_terminal          BOOLEAN NOT NULL DEFAULT false,
    is_deleted           BOOLEAN NOT NULL DEFAULT false,
    health_weight        VARCHAR(10) NOT NULL DEFAULT 'ok',  -- ok | warning | risk
    status_notes         TEXT,
    show_notes           BOOLEAN NOT NULL DEFAULT false,
    created_at           TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at           TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_booking_status_department ON booking_status_def(department);
CREATE INDEX idx_booking_status_sort ON booking_status_def(sort_order);
CREATE INDEX idx_booking_status_deleted ON booking_status_def(is_deleted);

Purpose: Configurable statuses per department (cs/payment/source/accounting) with color, icon, and state machine transitions. Booking.status / payment_status / source_status / accounting_status all FK reference this table via key. The department field indicates which axis/track this status belongs to.

Transitions Design:

  • Transitions stored as JSONB array (not separate table) for simplicity
  • Each transition object: { toKey, label, allowedRoles, rules }
  • Prevents hardcoded state machine logic in code

2.15e booking_status_transition (E-15) ​

Note: In the current implementation, transitions are stored as a JSONB array within BookingStatusDef.transitions rather than as a separate table. The DDL below represents the original planned schema. The Prisma model BookingStatusDef contains a transitions Json[] field.

sql
-- NOTE: Not a separate Prisma model. Transitions stored as JSONB in booking_status_def.
-- This DDL is kept for reference only.
CREATE TABLE booking_status_transition (
    id                 UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    from_key           VARCHAR(50) NOT NULL,
    to_key             VARCHAR(50) NOT NULL,
    department         VARCHAR(50) NOT NULL DEFAULT 'cs',
    allowed_roles      JSONB NOT NULL DEFAULT '[]',
    hooks              JSONB NOT NULL DEFAULT '[]',
    sort_order         INTEGER NOT NULL DEFAULT 0,
    is_active          BOOLEAN NOT NULL DEFAULT true,
    created_at         TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT fk_from_status FOREIGN KEY (from_key) REFERENCES booking_status_def(key),
    CONSTRAINT fk_to_status FOREIGN KEY (to_key) REFERENCES booking_status_def(key),
    CONSTRAINT uq_transition UNIQUE (from_key, to_key, department)
);

Purpose: State machine transitions with role-based access control and configurable hooks. In practice, transitions are stored as JSONB within BookingStatusDef for simplicity.

2.15f countries ​

sql
CREATE TABLE countries (
    code       VARCHAR(2) PRIMARY KEY,
    name       VARCHAR(100) NOT NULL,
    timezone   VARCHAR(50) NOT NULL,
    currency   VARCHAR(3) NOT NULL,
    bg_color   VARCHAR(7),                     -- pill background color
    text_color VARCHAR(7),                     -- pill text color
    sort_order INTEGER NOT NULL DEFAULT 0,
    is_active  BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_countries_active ON countries(is_active);
CREATE INDEX idx_countries_sort ON countries(sort_order);

Purpose: Country reference data (VN, ID, MY) with display customization (pill colors).

2.15g cities (E-33) ​

sql
CREATE TABLE cities (
    id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name         VARCHAR(100) NOT NULL,
    country_code VARCHAR(2) NOT NULL REFERENCES countries(code),
    is_active    BOOLEAN NOT NULL DEFAULT true,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    
    CONSTRAINT uq_city_country UNIQUE (name, country_code)
);

CREATE INDEX idx_cities_country ON cities(country_code);

Purpose: City master data per country. Referenced by properties and districts.

2.15h districts (E-34) ​

sql
CREATE TABLE districts (
    id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name       VARCHAR(100) NOT NULL,
    city_id    UUID NOT NULL REFERENCES cities(id) ON DELETE CASCADE,
    is_active  BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    
    CONSTRAINT uq_district_city UNIQUE (name, city_id)
);

CREATE INDEX idx_districts_city ON districts(city_id);

Purpose: District/sub-city divisions under a city. Hierarchical: Country → City → District.

2.15 settings (system config, single row) ​

sql
CREATE TABLE settings (
    id                          INTEGER PRIMARY KEY DEFAULT 1 CHECK (id = 1),  -- singleton
    booking_pull_minutes        INTEGER NOT NULL DEFAULT 3,
    verification_minutes        INTEGER NOT NULL DEFAULT 10,
    session_refresh_minutes     INTEGER NOT NULL DEFAULT 30,
    buffer_rooms                INTEGER NOT NULL DEFAULT 0,
    line_notify_token_encrypted BYTEA,
    smtp_host                   VARCHAR(255),
    smtp_port                   INTEGER,
    smtp_user                   VARCHAR(255),
    smtp_password_encrypted     BYTEA,
    alert_overbooking           BOOLEAN NOT NULL DEFAULT true,
    alert_sync_failure          BOOLEAN NOT NULL DEFAULT true,
    alert_session_expired       BOOLEAN NOT NULL DEFAULT true,
    audit_log_retention_days    INTEGER NOT NULL DEFAULT 90,    -- how many days to retain audit logs
    health_flag_rules           JSONB NOT NULL DEFAULT '[]',    -- [{name, severity, message, conditions}] — drives booking health computation (FR-51)
    updated_at                  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Seed default row
INSERT INTO settings (id) VALUES (1) ON CONFLICT DO NOTHING;

2.16 Org Hierarchy (✅ IMPLEMENTED 2026-04-19 — IM Common Master adaptation) ​

Source: plans/260418-2351-im-master-org-hierarchy/ (plan) / plans/reports/brainstorm-260418-2351-im-master-org-hierarchy.md (research)
Migrations: 20260418152942_add_org_hierarchy_tables/, 20260418153333_enforce_company_id_not_null/
Status: Applied to dev DB. Seeded: 1 Company "PTX", 4 tiers, 6 departments, 24 DepartmentRoles, 28 memberships. Period management + master-data i18n explicitly out of scope (YAGNI).

2.16.1 Enum ​

sql
CREATE TYPE department_function AS ENUM (
  'customer_support', 'purchasing', 'finance', 'ota',
  'sales', 'operations', 'other'
);

2.16.2 companies (E-28) ​

sql
CREATE TABLE companies (
    id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    code         VARCHAR(50) NOT NULL UNIQUE,
    name         VARCHAR(255) NOT NULL,
    country_code VARCHAR(2),
    timezone     VARCHAR(50) NOT NULL DEFAULT 'Asia/Ho_Chi_Minh',
    is_active    BOOLEAN NOT NULL DEFAULT true,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

2.16.3 company_tiers (E-29) ​

sql
CREATE TABLE company_tiers (
    id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    code       VARCHAR(50) NOT NULL,
    name       VARCHAR(100) NOT NULL,
    level      INTEGER NOT NULL,
    sort_order INTEGER NOT NULL DEFAULT 0,
    is_active  BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(company_id, code),
    UNIQUE(company_id, level)
);
CREATE INDEX idx_company_tiers_company ON company_tiers(company_id);

2.16.4 departments (E-30) ​

sql
CREATE TABLE departments (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id      UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    parent_id       UUID REFERENCES departments(id) ON DELETE SET NULL,
    code            VARCHAR(50) NOT NULL,
    name            VARCHAR(255) NOT NULL,
    function        department_function NOT NULL DEFAULT 'other',
    manager_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
    sort_order      INTEGER NOT NULL DEFAULT 0,
    is_active       BOOLEAN NOT NULL DEFAULT true,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(company_id, code)
);
CREATE INDEX idx_departments_company_parent ON departments(company_id, parent_id);
CREATE INDEX idx_departments_function ON departments(function);

2.16.5 department_roles (E-31) ​

sql
CREATE TABLE department_roles (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    department_id   UUID NOT NULL REFERENCES departments(id) ON DELETE CASCADE,
    company_tier_id UUID NOT NULL REFERENCES company_tiers(id) ON DELETE RESTRICT,
    title           VARCHAR(100) NOT NULL,
    is_active       BOOLEAN NOT NULL DEFAULT true,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(department_id, company_tier_id)
);
CREATE INDEX idx_department_roles_dept ON department_roles(department_id);

2.16.6 department_members (E-32) ​

sql
CREATE TABLE department_members (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id             UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    department_id       UUID NOT NULL REFERENCES departments(id) ON DELETE CASCADE,
    department_role_id  UUID NOT NULL REFERENCES department_roles(id) ON DELETE RESTRICT,
    company_tier_id     UUID NOT NULL REFERENCES company_tiers(id) ON DELETE RESTRICT,
    is_primary          BOOLEAN NOT NULL DEFAULT false,
    is_active           BOOLEAN NOT NULL DEFAULT true,
    assigned_at         TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(user_id, department_id)
);

-- Only ONE primary membership per user
CREATE UNIQUE INDEX uq_dept_member_primary
  ON department_members(user_id) WHERE is_primary = true;

CREATE INDEX idx_dept_members_dept_tier ON department_members(department_id, company_tier_id);
CREATE INDEX idx_dept_members_user_primary ON department_members(user_id, is_primary);

2.16.7 User / Property extensions ​

sql
ALTER TABLE users      ADD COLUMN company_id UUID REFERENCES companies(id) ON DELETE RESTRICT;
ALTER TABLE properties ADD COLUMN company_id UUID REFERENCES companies(id) ON DELETE RESTRICT;
-- After backfill: ALTER COLUMN company_id SET NOT NULL;
CREATE INDEX idx_users_company      ON users(company_id);
CREATE INDEX idx_properties_company ON properties(company_id);

2.16.8 App-level Invariants (not enforced by DB) ​

  • department_roles.company_tier.company_id MUST equal department_roles.department.company_id.
  • department_members.company_tier_id MUST equal department_members.department_role.company_tier_id.
  • Dept tree must be acyclic (verify on parent update).
  • Deleting a Department with active members returns 409 (service-level).

2.17 Internal Chat & Notifications (E-35..E-40) ​

2.17.1 conversations (E-35) ​

sql
CREATE TABLE conversations (
    id                      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    kind                    conv_kind NOT NULL,  -- DM, CHANNEL, BOOKING
    name                    VARCHAR(255),        -- only set for CHANNEL
    booking_id              UUID REFERENCES bookings(id) ON DELETE CASCADE,  -- only set for BOOKING
    parent_conversation_id  UUID REFERENCES conversations(id) ON DELETE SET NULL,  -- rescue chain
    created_by_id           UUID REFERENCES users(id) ON DELETE SET NULL,
    created_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_conv_kind ON conversations(kind);
CREATE INDEX idx_conv_booking ON conversations(booking_id);
CREATE INDEX idx_conv_parent ON conversations(parent_conversation_id);
CREATE INDEX idx_conv_created_by ON conversations(created_by_id);

Purpose: Thread container for internal team chat. DM = direct message between two users, CHANNEL = named group, BOOKING = thread auto-created per booking.

Rescue Pattern: When a cancelled booking is "rescued" by Lead/Manager, a new BOOKING-kind conversation is created with parent_conversation_id pointing to the previous root conversation. The active conversation is: findFirst({ bookingId, parentConversationId: null }, orderBy: { createdAt: DESC }).

2.17.2 participants (E-36) ​

sql
CREATE TABLE participants (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    conversation_id     UUID NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
    user_id             UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role                part_role NOT NULL DEFAULT 'MEMBER',  -- MEMBER, ADMIN
    notifications_muted BOOLEAN NOT NULL DEFAULT false,
    last_read_at        TIMESTAMPTZ,
    joined_at           TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT uq_participant UNIQUE (conversation_id, user_id)
);

CREATE INDEX idx_participant_user ON participants(user_id);

Purpose: User membership in conversation with role (MEMBER/ADMIN) and mute state.

2.17.3 messages (E-37) ​

sql
CREATE TABLE messages (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    conversation_id UUID NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
    sender_id       UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    body            TEXT NOT NULL,
    mentioned_ids   JSONB NOT NULL DEFAULT '[]',  -- JSON array of user UUIDs
    edited_at       TIMESTAMPTZ,
    deleted_at      TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_message_conv_created ON messages(conversation_id, created_at DESC);
CREATE INDEX idx_message_sender ON messages(sender_id);

Purpose: Individual messages within a conversation. Soft-delete via deleted_at.

2.17.4 attachments (E-38) ​

sql
CREATE TABLE attachments (
    id               UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    message_id       UUID REFERENCES messages(id) ON DELETE SET NULL,
    uploaded_by_id   UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    storage_key      VARCHAR(1000) NOT NULL,  -- chat/{userId}/{uuid}/{sanitizedFilename}
    filename         VARCHAR(500) NOT NULL,
    mime_type        VARCHAR(127) NOT NULL,
    size_bytes       INT NOT NULL,
    confirmed        BOOLEAN NOT NULL DEFAULT false,  -- true once client confirms R2 PUT
    confirmed_at     TIMESTAMPTZ,
    created_at       TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_attachment_message ON attachments(message_id);
CREATE INDEX idx_attachment_uploader ON attachments(uploaded_by_id);
CREATE INDEX idx_attachment_storage_key ON attachments(storage_key);

Purpose: File attachment metadata. Actual file stored in R2. confirmed tracks successful client upload.

2.17.5 notifications (E-39) ​

sql
CREATE TABLE notifications (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    actor_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
    type        notification_type NOT NULL,  -- ALERT, BOOKING_EVENT, CHAT_MENTION, CHAT_DM, SYSTEM
    title       VARCHAR(200) NOT NULL,
    body        TEXT,
    link        VARCHAR,
    entity_type VARCHAR(50),                 -- soft reference (no FK)
    entity_id   VARCHAR(255),                -- soft reference (no FK)
    data        JSONB,
    read_at     TIMESTAMPTZ,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_notif_user_read ON notifications(user_id, read_at);
CREATE INDEX idx_notif_user_created ON notifications(user_id, created_at DESC);

Purpose: In-app notification inbox. Soft entity references (no FKs) allow flexible producers (alerts, bookings, chat). Absence of a user_notification_preference row means user receives that type.

2.17.6 user_notification_preferences (E-40) ​

sql
CREATE TABLE user_notification_preferences (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id         UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    type            notification_type NOT NULL,
    in_app_enabled  BOOLEAN NOT NULL DEFAULT true,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT uq_user_notif_type UNIQUE (user_id, type)
);

CREATE INDEX idx_user_notif_pref_user ON user_notification_preferences(user_id);

Purpose: Per-user per-type mute flags. Default (noisy) = absence of a row means user receives. Only muted types are recorded.

3. Key Design Decisions ​

3.1 Currency as Integer ​

All monetary values stored as integers in smallest currency unit (D-05):

  • VND: dong (no decimal) → base_rate = 500000 for 500,000 VND
  • IDR: rupiah (no decimal) → base_rate = 750000 for 750,000 IDR
  • MYR: sen (1 MYR = 100 sen) → base_rate = 25000 for 250.00 MYR

BIGINT used for bookings.total_amount to handle large VND/IDR values.

3.1a Internal Codes (Entity Identity) ​

Property, Customer, Booking, and SupplierApartment tables each have internal_code (TEXT, UNIQUE, NOT NULL) — a human-readable short identifier for detail URLs and UI display (e.g., MY-22846F, CU-A3F92B, BK-7D21C4, SA-1B0C44).

Generation: Backfilled deterministically from UUID suffix during migration; on collision, 6-hex variant extended to 8-hex. Prefix is fixed per entity (CU/BK/SA) or country code for properties.

Resolver: Backend EntityCodeService converts route params (code OR UUID) to real UUID; old UUID URLs transparently resolve for backward compatibility.

3.2 Timestamps in UTC ​

All TIMESTAMPTZ columns store UTC (D-06). Application layer converts to property timezone for display using properties.timezone.

3.3 Credential Encryption ​

credentials_encrypted and notification tokens stored as BYTEA with AES-256-GCM encryption. Encryption key stored in environment variable, not in database.

3.4 OTA Account Model (D-08) ​

OtaAccount entity introduced as top-level credential container (E-04). One OTA login can manage multiple properties. OtaConnection (E-05) now simplified to lightweight link with no credentials/session—just property_id + ota_account_id + ota_property_id.

3.5 Country Scoping (D-10) ​

Users table includes nullable country field. Null = manager with all-country access. Staff scoped to their country code (VN/ID/MY). OTA accounts also include country_code for organization.

Note: CountryScopeGuard validates against ALLOWED_COUNTRIES = ['TH', 'VN', 'ID']. Ensure database and code stay in sync if allowed countries change.

3.6 Availability Unique Constraint ​

UNIQUE (room_type_id, date) ensures one row per room type per day. Upsert pattern used:

sql
INSERT INTO availability (room_type_id, date, total_rooms, booked_rooms)
VALUES ($1, $2, $3, $4)
ON CONFLICT (room_type_id, date)
DO UPDATE SET booked_rooms = EXCLUDED.booked_rooms, updated_at = NOW();

3.7 Booking Deduplication ​

UNIQUE (ota_type, ota_booking_id) prevents duplicate bookings during polling. Upsert on conflict to update status (e.g., confirmed → cancelled).

3.8 Soft Deletes ​

Properties and room types use is_active flag instead of hard delete. Referenced data (bookings, availability) remains intact.

3.9 Settings Singleton ​

settings table uses CHECK (id = 1) constraint to guarantee single row. Avoids per-user settings complexity for this internal tool.

3.10 Lifecycle Trigger Rules (E-27) — PLANNED, NOT IMPLEMENTED ​

Note: This table is planned (FR-57) but has not been implemented in the Prisma schema yet.
When implemented, it will enable data-driven auto-transitions of booking_lifecycle when department tracks change status.

3.10a Booking Health — Computed, Not Stored (FR-51) ​

Decision: booking_health and risk_flags are NOT columns on bookings. Instead:

  • Rules stored in settings.health_flag_rules (JSONB array) — configurable via Settings UI
  • Values computed at read time by BookingsService — applied to each booking in API responses as bookingHealth (ok/warning/risk) + healthFlags[]
  • Rationale: rules change frequently; avoids migrations + stale-data issues
  • Implementation: apps/api/src/modules/bookings/bookings.service.ts:663-716
  • UI: apps/web/components/workflows/health-flags-manager.tsx

3.10b RatePlan / RatePlanAdjustment — LIVE (not replaced) ​

Correction to prior SRD note: The rate_plans + rate_plan_adjustments tables are still active in the Prisma schema (lines 574–612) with a corresponding /api/v1/rate-plans NestJS module.
They coexist with the newer ota_rate_configs / ota_rate_lines (E-24/E-25) formula-based engine — different use cases:

  • RatePlan = simple per-room-type base plan with OTA-level price adjustments (markup/discount decimal %)
  • OtaRateConfig/Line = template-driven formula engine (commission/promote/surcharge variables)
    No deprecation scheduled. Both live.

3.10c ProcessType / ProcessInstance (E-21/E-22) — ❌ REMOVED ​

Status: Abandoned. E-21 and E-22 have been removed from the system.
Original intent (FR-44..FR-47 generalized BPM engine) dropped. Direct per-entity status engines (BookingStatusDef + BookingStatusTransition + OtaStatusDef) cover actual use cases.
Downstream impact:

  • SlaDefinition (E-26) — original dedicated-table design NOT built; superseded by condition-based Health Flag Rules (settings.healthFlagRules JSONB, see §3.10a). FR-52 partially realized via different architecture (no time-duration thresholds, no BullMQ scheduler).
  • Screens S-28 / S-29 abandoned
  • FRs FR-44/FR-45/FR-46/FR-47 deprecated
  • BPM_SPEC.md in docs/ is historical; do not treat as live spec

3.11 Org Hierarchy (E-28..E-32) — ✅ IMPLEMENTED 2026-04-19 ​

Source: IM Common Master selective adaptation. Plan: plans/260418-2351-im-master-org-hierarchy/.

Decisions:

  • Single-Company per deployment in v1; schema supports multi via company_id FKs on all org tables.
  • Configurable tier ladder — company_tiers at company level; departments reference tiers via department_roles.
  • Function enum on Department — categorizes dept by role (CS/Purchasing/Finance/OTA/Sales/Operations).
  • Orthogonal to existing RBAC — users.role_id (E-14 permissions) untouched; department_members.department_role_id is org tier (reporting/seniority), not authorization.
  • YAGNI skips: period management (term_start/term_end), master-data i18n tables, PublicGroup, Selectors. Re-evaluate when needed.
  • Primary membership uniqueness enforced via partial unique index rather than application logic to survive race conditions.

4. Indexes Strategy ​

PurposeIndexQuery Pattern
Loginidx_users_emailWHERE email = ?
User country filteridx_users_countryWHERE country = ?
Property listingidx_properties_country, idx_properties_activeWHERE country = ? AND is_active
Room types per propertyidx_room_types_propertyWHERE property_id = ?
OTA account statusidx_ota_accounts_statusWHERE status = 'active'
OTA account owneridx_ota_accounts_userWHERE user_id = ?
OTA connections by propertyidx_ota_conn_propertyWHERE property_id = ?
OTA connections by accountidx_ota_conn_accountWHERE ota_account_id = ?
Availability calendaridx_availability_room_dateWHERE room_type_id = ? AND date BETWEEN
Overbooking detectionidx_availability_overbookedWHERE booked_rooms >= total_rooms
Booking dedupidx_bookings_ota_idWHERE ota_type = ? AND ota_booking_id = ?
Recent bookingsidx_bookings_createdORDER BY created_at DESC LIMIT
Pending sync jobsidx_sync_jobs_type_statusWHERE job_type = ? AND status = 'pending'
Unresolved alertsidx_alerts_unresolvedWHERE is_resolved = false
Audit trailidx_audit_entityWHERE entity_type = ? AND entity_id = ?

5. Data Volume Estimates ​

TableGrowth Rate1-Year EstimateRetention
usersStatic~15-20 rowsPermanent
propertiesStatic~100+ rowsPermanent
room_typesStatic~500 rows (5/property)Permanent
ota_accountsStatic~12-20 rowsPermanent
ota_connectionsStatic~400+ rows (4/property)Permanent
ota_room_mappingsStatic~2,000 rowsPermanent
availabilityDaily per room type~183K rows (500 types x 365 days)2 years rolling
bookings~200-800/day~146K-292K rows/yearPermanent
sync_jobs~4,800/hour~42M rows/year7-day retention (cleanup)
ratesPer room type per OTA per day~730K rows (2K mappings x 365)1 year rolling
alerts~10-50/day~18K rows/year1 year
audit_logs~100-400/day~146K rows/year1 year

Sync jobs is the highest-volume table. The DELETE /sync-jobs/completed endpoint and scheduled cleanup (7-day retention) keeps it manageable.

6. Traceability ​

EntitySRD EntityScreensAPI EndpointsPurpose
rolesE-14S-24/roles/*Named roles with bitwise permissions
usersE-01S-01, S-24, S-18/auth/, /users/, /users/meUser accounts, authentication + org hierarchy
refresh_tokensE-01b— (backend)—Token revocation tracking
password_reset_tokensE-01cS-24, /forgot-password, /reset-password/users/:id/reset-password, /users/:id/send-reset-link, /auth/forgot-password, /auth/reset-passwordPassword reset tokens
countries—S-24/countries/*Country reference data with display config
citiesE-33— (Master)/cities/*, /countries/:code/citiesCity master data per country
districtsE-34— (Master)/districts/*, /cities/:id/districtsDistrict/sub-city divisions
companiesE-28— (Setup)/companies/*Tenant root for org hierarchy
company_tiersE-29— (Setup)/company-tiers/*Configurable tier ladder (STAFF/LEAD/MANAGER/DIRECTOR)
departmentsE-30— (Setup)/departments/*Org units with function classification (CS/Finance/Purchasing/etc.)
department_rolesE-31— (Setup)/department-roles/*Tier titles per department
department_membersE-32— (Setup)/department-members/*, /users/:id/membershipsUser × Department × Role assignment
suppliersE-16S-21, S-22/suppliers/*, /suppliers/:id/organizationRoom suppliers/owners with bank details
supplier_organizationsE-41— (Master)/supplier-organizations/*Master org for grouping suppliers
supplier_apartmentsE-42S-02, S-19/supplier-apartments/*, /supplier-apartments/:idSupplier property listings from Lark
supplier_room_allocationsE-17S-22, S-23/room-types/:id/supplier-allocations, /supplier-allocations/:idSupplier ↔ room type allocation
propertiesE-02S-03, S-04/properties/*Hotel properties with internal codes
room_typesE-03S-04/properties/:id/room-types/*Room inventory per property
ota_accountsE-04S-05, S-06, S-07/ota-accounts/*Encrypted OTA credentials
ota_connectionsE-05S-04/ota-connections/*Links properties to OTA accounts + Lark metadata
ota_inbound_messages—— (Webhook log)—Durable audit trail for OTA reservation notifications
ota_listing_details—S-19/ota-listings/*Source listing inventory (pricing, policies, specs)
ota_room_mappingsE-06S-07/ota-connections/:id/room-mappingsInternal room ↔ OTA room mapping
availabilityE-07S-02, S-10/availability/*Daily room availability
booking_status_defE-08bS-24, S-09, S-20, S-28/booking-status/*Configurable status definitions per department
booking_status_transitionE-15S-20, S-28/booking-status/:key/transitionsState machine transitions (stored as JSONB in BookingStatusDef)
bookingsE-08S-02, S-08, S-09, S-12/bookings/*, /bookings/:id/history, /bookings/:id/status, /bookings/:id/revertBooking records from OTAs with internal codes + 4-axis status
booking_escalationsE-45S-09, S-12/bookings/:id/escalationsBooking escalation event log per track (CS/Payment/Source/Accounting)
customersE-19S-25, S-26, S-27, S-28/customers/*, /customers/:id/link, /customers/:id/unlink, /customers/:id/mergeGuest CRM with contact info & booking consolidation
ota_status_defE-20S-08, S-09, S-24/ota-status/*OTA status normalization: raw string → display label + color
sync_jobsE-09S-17/sync-jobs/*, /sync-jobs/force-syncAsync job tracking
ratesE-10S-11, S-13/rates/*, /rates/parityRate per room/OTA/date
rate_plans—S-13/rate-plans/*Simple per-room-type base rate plans (coexists with formula engine)
rate_plan_adjustments—S-13/rate-plans/:id/adjustmentsOTA-level price adjustments (markup/discount %)
rate_rulesE-13S-15/rate-rules/*Markup/discount/seasonal rules (property/room/OTA level)
ota_formula_templatesE-23S-14/ota-formula-templates/*Rate formula templates (commission/promote/surcharge engine)
ota_rate_configsE-24S-14/ota-rate-configs/*Property-level formula application per OTA
ota_rate_linesE-25S-14/ota-rate-lines/*Formula execution line items
property_formula_configsE-43S-14/property-formula-configs/*Property-level formula configuration
alertsE-11S-02, S-30/alerts/*, /alerts/:id/resolveOverbooking, sync failure, and workflow risk notifications
audit_logsE-12S-09 (history)/bookings/:id/historyEntity change tracking + booking history
activity_logsE-18— (Monitoring)—HTTP request audit trail (user, method, path, status, screen)
settings—S-16/settings/*, /settings/test-notificationGlobal app configuration (sync intervals, SMTP, health rules)
conversationsE-35S-12/conversations/*, /conversations/:id/messagesInternal chat threads (DM/CHANNEL/BOOKING)
participantsE-36— (Backend)/conversations/:id/participantsUser membership + mute state in conversations
messagesE-37S-12/conversations/:id/messagesIndividual messages with soft-delete + mentions
attachmentsE-38— (Backend)/attachments/*, R2 storageFile attachments with R2 storage tracking
notificationsE-39S-29/notifications/*, /notifications/:id/readIn-app notification inbox (soft entity references)
user_notification_preferencesE-40— (Settings)/user-notification-preferences/*Per-user per-type mute flags
filter_viewsE-44S-02, S-19, etc./filter-views/*Saved filter presets per module (bookings, supplier-apartments)

Lead/Manager Permission Layer (added 2026-04-30) ​

See plans/260430-1658-lead-manager-role-override/ for full design + brainstorm.

BookingRisk — REMOVED (added 2026-04-30, dropped 2026-05-01) ​

Status: This model and the RiskCategory enum were dropped via migration 20260501101400_drop_booking_risk. The definition below is kept for historical context only — the risk-flag feature (and its /risks/* endpoints) no longer exist.

One row per booking once flagged for risk by a Lead/Manager. finalDecision was gated to MANAGER+ tier.

ColumnTypeNotes
idUUID PK
booking_idUUID UNIQUE FK → bookings(id) ON DELETE CASCADEOne risk row per booking
is_flaggedBooleanDefault false; set true on first POST
categoryRiskCategory enum nullablerefund_heavy / customer_cancel / many_incidents
management_noteText nullableLEAD+ free-form note
final_decisionText nullableMANAGER+ only
flagged_by_idUUID nullable FK → users(id) ON DELETE SET NULLFirst flag actor
flagged_atTimestamptz nullable
decided_by_idUUID nullable FK → users(id) ON DELETE SET NULLManager who closed
decided_atTimestamptz nullable
created_at, updated_atTimestamptz

Indexes: is_flagged, category, flagged_by_id, decided_by_id.

RiskCategory — REMOVED (dropped with BookingRisk) ​

refund_heavy        -- Refund nhiều
customer_cancel     -- Khách đòi hủy
many_incidents      -- Nhiều sự cố

Conversation deltas ​

  • DROPPED unique index conversations_booking_id_key — bookings now allow multiple conversations (rescue threads).
  • ADDED column parent_conversation_id UUID NULL with self-FK ON DELETE SET NULL.
  • ADDED index conversations_parent_conversation_id_idx.

A "rescued" conversation has parent_conversation_id set to the most recent prior root conversation for the same booking. The "active root" conversation is findFirst({ bookingId, parentConversationId: null }, orderBy: { createdAt: 'desc' }).

DepartmentFunction enum delta ​

Added value ground_ops (between operations and other).
Seed adds Department { code: 'GO', name: 'Ground Ops', function: 'ground_ops' }.

Identity / Authorization ​

No isLead / isManager columns added. Lead/Manager identity is derived from the existing tier ladder:

  • DepartmentMember.companyTier.code ∈ { STAFF, LEAD, MANAGER, DIRECTOR }
  • DIRECTOR auto-inherits MANAGER privileges (helper-level union of [MANAGER, DIRECTOR])
  • Cross-referenced with Department.function to determine "scope" (which threads / bookings the user has Lead/Manager authority over)

Audit auto-coverage ​

Conversation and Participant added to AUDITABLE_MODELS in apps/api/src/common/extensions/prisma-audit.extension.ts (BookingRisk was also added at the time but removed with the model). All create / update / delete operations performed via prisma.audited.* produce an AuditLog row with oldValue, newValue, performedBy.

Migration ​

packages/database/prisma/migrations/20260430084049_lead_manager_layer_phase01/migration.sql is fully additive (CREATE TYPE, CREATE TABLE, ADD COLUMN, DROP UNIQUE INDEX, CREATE INDEX, ADD FOREIGN KEY). No data migration; rows preserved.

2.18 SupplierOrganization (E-41) ​

sql
CREATE TABLE supplier_organizations (
    id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name       VARCHAR(100) NOT NULL UNIQUE,
    is_active  BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Purpose: Master organization for grouping suppliers (e.g., a corporate owner managing multiple supplier apartments).

2.19 SupplierApartment (E-42) ​

sql
CREATE TABLE supplier_apartments (
    id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    internal_code         VARCHAR(10) NOT NULL UNIQUE,  -- SA-XXXXXX, auto-generated
    supplier_apartment_code VARCHAR(100) NOT NULL UNIQUE,  -- Lark business code
    apartment_id          INT UNIQUE,                    -- Lark apartment ID
    source                VARCHAR(20) NOT NULL DEFAULT 'LARK',
    purchasing_specialist VARCHAR(100),
    specialist_id         UUID REFERENCES users(id) ON DELETE SET NULL,
    building_name         VARCHAR(255) NOT NULL,
    property_id           UUID REFERENCES properties(id) ON DELETE SET NULL,
    apartment_host_code   VARCHAR(255) NOT NULL,
    supplier_id           UUID REFERENCES suppliers(id) ON DELETE SET NULL,
    apt_photo             TEXT,
    apartment_block       VARCHAR(500),
    unit_number           VARCHAR(500) NOT NULL,
    room_type            VARCHAR(100) NOT NULL,
    host_name             VARCHAR(255),
    host_phone_number    VARCHAR(255),
    airbnb_link           VARCHAR(1000),
    rating                DECIMAL(3,2),
    request_remark        TEXT,
    input_error           VARCHAR(100),
    ota_listing_detail_id UUID REFERENCES ota_listing_details(id) ON DELETE SET NULL,
    is_active             BOOLEAN NOT NULL DEFAULT true,
    created_at            TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at            TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_supplier_apt_property ON supplier_apartments(property_id);
CREATE INDEX idx_supplier_apt_listing ON supplier_apartments(ota_listing_detail_id);
CREATE INDEX idx_supplier_apt_supplier ON supplier_apartments(supplier_id);
CREATE INDEX idx_supplier_apt_specialist ON supplier_apartments(specialist_id);
CREATE INDEX idx_supplier_apt_active ON supplier_apartments(is_active);

Purpose: Supplier property/apartment sourced from Lark. Links to internal properties and OTA listing details. Tracks procurement specialist and contact details.

Key Fields:

  • internal_code - Unique SA-prefixed identifier for UI display
  • supplier_apartment_code - Lark business/display code (stable across syncs)
  • source - Data source (currently LARK; extensible for future sources)
  • specialist_id - FK to purchasing specialist user
  • ota_listing_detail_id - Link to mapped OTA listing details (foreign key)

2.20 PropertyFormulaConfig (E-43) ​

sql
CREATE TABLE property_formula_configs (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    property_id         UUID NOT NULL REFERENCES properties(id) ON DELETE CASCADE,
    formula_template_id UUID NOT NULL REFERENCES ota_formula_templates(id),
    parameter_values    JSONB NOT NULL DEFAULT '{}',
    is_active           BOOLEAN NOT NULL DEFAULT true,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT uq_property_formula UNIQUE (property_id, formula_template_id)
);

CREATE INDEX idx_property_formula_property ON property_formula_configs(property_id);

Purpose: Property-level rate formula configuration. Applies a formula template to a specific property with configured parameters.

2.21 FilterView (E-44) ​

sql
CREATE TABLE filter_views (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    module      VARCHAR(100) NOT NULL,  -- 'supplier-apartments', 'bookings', etc.
    name        VARCHAR(255) NOT NULL,
    filters     JSONB NOT NULL,        -- FilterCondition[] (validated per module)
    is_shared   BOOLEAN NOT NULL DEFAULT false,
    created_by_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    company_id  UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_filter_view_company_module ON filter_views(company_id, module);
CREATE INDEX idx_filter_view_creator ON filter_views(created_by_id);

Purpose: Saved filter presets per module (supplier-apartments, bookings, etc.). Can be shared within company or private to creator.

2.22 BookingEscalation (E-45) ​

sql
CREATE TABLE booking_escalations (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    booking_id      UUID NOT NULL REFERENCES bookings(id) ON DELETE CASCADE,
    track_key       VARCHAR(20) NOT NULL,  -- 'cs' | 'payment' | 'source' | 'accounting'
    escalated_by_id UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    target_user_id  UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    target_tier_id  UUID NOT NULL REFERENCES company_tiers(id) ON DELETE RESTRICT,
    reason          TEXT,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_escalation_booking ON booking_escalations(booking_id);
CREATE INDEX idx_escalation_escalated_by ON booking_escalations(escalated_by_id);
CREATE INDEX idx_escalation_target_user ON booking_escalations(target_user_id);

Purpose: Booking escalation event log. Records when a user escalates a specific booking status track (CS/Payment/Source/Accounting) to a higher tier.

OTA Inbound Reservations (added 2026-06-05) ​

OtaInboundMessage (new model — durable audit trail for webhook events) ​

Stores every inbound OTA reservation notification (Trip.com HotelResRQ/Modify/Cancel XML). Provides immutable audit trail for compliance and debugging.

ColumnTypeNotes
idUUID PK
ota_typeOtaType enum'trip', 'booking', 'expedia', 'agoda_hotel', 'agoda_homes'
message_typeVarChar(30)'res_new', 'res_modify', 'res_cancel', 'unknown'
echo_tokenVarChar(64) nullableTrip.com echo token (for response matching)
ota_booking_idText nullableOTA booking ID extracted from XML (may be null if parse fails)
hotel_codeText nullableTrip.com hotel code from HotelCode element
raw_xmlTextFull XML payload with PAN structurally redacted (safe to log)
outcomeVarChar(20) default 'received''received', 'success', 'warning', 'error'
error_codeVarChar(20) nullableOTA error code if outcome=error (e.g., '12', '450')
processing_msInt nullableHandler execution time (excluding XML parse)
remote_ipVarChar(45) nullableRequest source IP for audit trail
created_atTimestamptzEvent timestamp (indexed for time-range queries)

Indexes: (ota_type, created_at), created_at.

Usage Pattern:

sql
-- Find all failed Trip.com inbound messages from last 24h
SELECT * FROM ota_inbound_messages
WHERE ota_type = 'trip' AND outcome = 'error' AND created_at > now() - interval '24 hours'
ORDER BY created_at DESC;

-- Retry specific message (parse raw_xml, dispatch to handler again)
SELECT raw_xml FROM ota_inbound_messages WHERE id = '...' AND outcome = 'error';

OtaListingDetail (new model — source listing inventory, v3.0.2) ​

Imported from Lark Source List table. Represents a supplier property listing with pricing, policies, and specs. System is source-of-truth after import (immutable except soft-delete + user edits).

ColumnTypeNotes
idUUID PK
internal_codeVarChar UNIQUELD-XXXXXX (6-8 hex) auto-generated, collision-retry
nameVarChar(255)Listing name
descriptionText nullableListing description
room_type_idUUID FK → room_types(id)Maps to internal room type (denormalized from import)
property_idUUID FK → properties(id)Maps to property (denormalized from import)
Pricing & Margins
cost_wdDecimal(12,2)Weekday cost (from Lark)
cost_wkDecimal(12,2)Weekend cost (from Lark)
currency_nameVarChar(10)Currency code (e.g., 'VND', 'IDR')
currency_rateDecimal(12,4)Exchange rate to base currency
level_sourceDecimal(5,2) nullableSource level (unknown origin)
margin_low_ssDecimal(5,4)Margin low season / standard stay
margin_high_ssDecimal(5,4)Margin high season / standard stay
margin_holiDecimal(5,4)Margin for holidays
Check-in & Policies
check_in_fileVarChar(1000) nullableCheck-in file/URL
restriction_rulesText nullableRestriction rules (free-form)
check_in_wayVarChar(255) nullableCheck-in method (e.g., 'Self-check-in')
check_in_out_policyText nullableCheck-in/out policy (free-form)
child_policyText nullableChild policy (free-form)
cleaning_feeDecimal(12,2) nullableCleaning fee if applicable
Specs
room_sizeDecimal(6,2) nullableRoom size in m²
bed_typeVarChar(100) nullableBed type (e.g., 'Queen', '2 Twin')
bathroomsDecimal(3,1) nullableNumber of bathrooms
max_paxInt nullableMaximum guests
room_photosJson nullablePhoto URLs array
amenitiesJson default '{}'Amenity list (key-value pairs)
Audit & State
is_activeBoolean default trueSoft-delete flag (false = archived, not deleted)
created_at, updated_atTimestamptz

Indexes: (property_id, room_type_id), is_active, internal_code.

Key Rules:

  • After Lark import, system database is source of truth
  • Re-imports only INSERT new rows (never OVERWRITE)
  • User edits (margins, policies) are preserved on UPDATE
  • Soft-delete: isActive=false hides from UI (PATCH to restore)
  • Unique internal_code allows shareable URLs: /ota-listings/LD-A1B2C3

Migration & Constraints ​

packages/database/prisma/migrations/20260605*/ added:

  • CREATE TABLE ota_inbound_messages with indexes
  • CREATE TABLE ota_listing_details with relations to room_types, properties
  • ALTER TABLE ota_connections ADD UNIQUE (otaAccountId, otaPropertyId) — dual-key ensures no duplicate connections per property
  • Migrate existing ota_connections to enforce new unique constraint (upsert logic in migration script)

PTX Channel Manager — Internal Documentation

© 2026 PTX Team