PTX Channel Manager

English

Tiếng Việt

English

Tiếng Việt

Appearance

Database Design (DB_DESIGN) ​

ProjectPTX Channel Manager (ptx-cm)
Version2.1.0
Date2026-02-20
DatabasePostgreSQL 16
ORMPrisma (implementation detail, schema is standard SQL)

1. ER Diagram ​

2. Table Definitions (DDL) ​

2.1 Enums ​

sql
CREATE TYPE user_role AS ENUM ('manager', 'staff');

CREATE TYPE ota_type AS ENUM ('booking', 'agoda', 'traveloka', 'expedia');

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

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

-- booking_status enum removed — replaced by booking_status_def table (dynamic statuses)

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');

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');

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',
    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);

Key Fields:

  • role_id - FK to roles table (replaced old role enum)
  • 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(),
    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,
    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);

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;

2.6 ota_connections (E-05) — SIMPLIFIED ​

sql
CREATE TABLE ota_connections (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    property_id         UUID NOT NULL 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,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT uq_property_ota_account UNIQUE (property_id, ota_account_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);

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.8 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(),
    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
    ota_type        ota_type NOT NULL,
    ota_booking_id  VARCHAR(255) NOT NULL,
    guest_name      VARCHAR(255) NOT NULL,
    guest_email     VARCHAR(255),
    check_in        DATE NOT NULL,
    check_out       DATE NOT NULL,
    num_rooms       INTEGER NOT NULL DEFAULT 1,
    num_guests      INTEGER NOT NULL DEFAULT 1,
    status          VARCHAR(50) NOT NULL DEFAULT 'confirmed'    -- FK to booking_status_def.key
                    REFERENCES booking_status_def(key),
    total_amount    BIGINT NOT NULL,           -- integer, smallest currency unit
    currency        VARCHAR(3) NOT NULL,
    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_ota 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_created ON bookings(created_at DESC);
-- For deduplication during polling
CREATE INDEX idx_bookings_ota_id ON bookings(ota_type, ota_booking_id);

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.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) ​

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),
    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,
    status_notes TEXT,
    show_notes   BOOLEAN NOT NULL DEFAULT false,
    ui_config    JSONB,                          -- per-role UI config
    created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

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 booking statuses with color, icon, display config. Booking.status FK references this table.

2.15e booking_status_transition (E-15) ​

sql
CREATE TABLE booking_status_transition (
    id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    from_key     VARCHAR(50) NOT NULL REFERENCES booking_status_def(key),
    to_key       VARCHAR(50) NOT NULL REFERENCES booking_status_def(key),
    allowed_roles JSONB NOT NULL DEFAULT '[]',
    hooks        JSONB NOT NULL DEFAULT '[]',    -- ["audit_log", "update_availability", "send_notification"]
    sort_order   INTEGER NOT NULL DEFAULT 0,
    is_active    BOOLEAN NOT NULL DEFAULT true,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT uq_transition UNIQUE (from_key, to_key)
);

CREATE INDEX idx_transition_from ON booking_status_transition(from_key);

Purpose: State machine transitions with role-based access control and configurable hooks.

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.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
    updated_at                  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

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

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.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.

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
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
suppliersE-16S-21, S-22/suppliers/*Room suppliers/owners with bank details
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
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
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/booking-status/*Configurable booking status definitions
booking_status_transitionE-15S-20/booking-status-transitions/*State machine transitions with role control
bookingsE-08S-02, S-08, S-09, S-12/bookings/*, /bookings/:id/history, /bookings/:id/status, /bookings/:id/revertBooking records from OTAs
sync_jobsE-09S-17/sync-jobs/*, /sync-jobs/force-syncAsync job tracking
ratesE-10S-11, S-13/rates/*, /rates/parityRate per room/OTA/date
alertsE-11S-02/alerts/*, /alerts/:id/resolveOverbooking & sync failure notifications
audit_logsE-12S-09 (history)/bookings/:id/historyEntity change tracking + booking history
rate_rulesE-13S-15/rate-rules/*Markup/discount/seasonal rules
settings—S-16/settings/*, /settings/test-notificationGlobal app configuration

PTX Channel Manager — Internal Documentation

© 2026 PTX Team