System Requirement Definition (SRD)
Project: PTX Channel Manager (ptx-cm)
Version: 2.7.0
Date: 2026-03-12
Type: Internal Tool (not SaaS)
1. System Overview
1.1 Purpose
PTX-CM is an internal OTA extranet automation tool that prevents overbookings by auto-syncing room availability across multiple OTA partner dashboards (Booking.com, Agoda, Traveloka, Expedia) from a centralized interface. It targets a hospitality operator managing 100+ properties across Southeast Asia (Vietnam, Indonesia, Malaysia).
1.2 Problem
Staff manually update 4 OTA extranets per property (~400+ sessions). When a booking arrives on one OTA, availability isn't reduced on others fast enough, causing multiple daily overbookings. Each overbooking damages guest trust, incurs relocation costs, and risks OTA penalties.
1.3 Solution
A unified dashboard that:
- Connects OTA accounts and auto-discovers properties from each OTA extranet
- Polls OTA extranets for new bookings every 2-3 minutes
- Automatically pushes updated availability to all connected OTAs
- Alerts staff on sync failures or potential overbookings
- Provides a single view of all bookings and availability, filterable by country
1.4 Technical Approach
Reverse-engineer OTA extranet API calls via HTTP request interception (Playwright). Replay captured requests to read bookings and write availability updates. No official OTA API partnerships required.
1.5 Constraints
- Solo developer
- Zero infrastructure budget (self-hosted on existing server)
- OTA extranet automation carries ToS risk
- Mixed 2FA methods across OTA accounts
- One OTA account may manage multiple properties
- 3 countries with different currencies and timezones
2. Actors (User Roles)
| ID | Role | Preset Key | Description | Access Level |
|---|---|---|---|---|
| U-01 | Super Admin | super_admin | System owner. Full access to all modules and all roles. Cannot be deleted. | Full access (country=null) |
| U-02 | Admin | admin | System administrator. Full access except cannot manage other SA users. | Full access (country=null) |
| U-03 | Manager | manager | Operations manager overseeing properties. Full CRUD on operational modules, view-only on settings/users. | Full or country-scoped |
| U-04 | OTA Operator | ota | Staff managing OTA accounts, connections, sync, room mappings. | Country-scoped |
| U-05 | Customer Service | cs | Handles bookings and alerts. View/edit bookings, resolve alerts. | Country-scoped |
| U-06 | Finance | fin | Views booking revenue, rates, availability. Read-only. | Country-scoped |
| U-07 | Purchasing | po | Placeholder for future ERP purchasing module. Minimal access. | Country-scoped |
Note: Roles are stored in a roles table (E-14) with bitwise permissions per module. Admins can create custom roles beyond these 7 presets. See FR-20–FR-23.
3. Functional Requirements (FR-xx)
Phase 1 — P1: Anti-Overbooking Core
| ID | Feature | Priority | Status | Description | Actor |
|---|---|---|---|---|---|
| FR-01 | OTA Account Management | P1 | ⚠️ PARTIAL | CRUD + encrypted credentials ✅. Test connection & refresh session are stubs (return hardcoded messages). No Playwright integration. | U-01 |
| FR-02 | Property Discovery & Import | P1 | ❌ NOT IMPL | OTA adapters are all stubs — fetchProperties() returns empty. No property discovery, no cross-OTA matching. Manual property CRUD works. | U-01 |
| FR-03 | Booking Pull | P1 | ⚠️ PARTIAL | BullMQ orchestration ✅, booking upsert ✅, dedup ✅. But OTA adapters fetchBookings() returns empty. Also always fetches from new Date(0) (no incremental pull). Room mapping uses first available instead of matching by OTA room ID. | System |
| FR-04 | Availability Auto-Sync | P1 | ⚠️ PARTIAL | Availability calculation logic ✅, overbooking detection ✅, BullMQ processors ✅. But OTA adapters pushAvailability() is a stub — never actually pushes to any OTA. | System |
| FR-05 | Property & Room Management | P1 | ✅ COMPLETE | CRUD for properties, room types, OTA connections, room mappings all working. | U-01, U-02 |
| FR-06 | Sync Status Dashboard | P1 | ✅ COMPLETE | KPI cards, sync status panel, recent bookings — all fetch real DB data. | U-01, U-02 |
| FR-07 | Overbooking Alert | P1 | ⚠️ PARTIAL | Alert creation on overbooking ✅, alert list/resolve ✅, dashboard banner ✅. LINE Notify not implemented (stub returns "not available in P1"). Email notification service exists but depends on SMTP config. | U-01, U-02 |
| FR-08 | Country-Scoped Access | P1 | ✅ COMPLETE | Country scope guard, decorator, helper all working. Staff locked to country, manager sees all. | U-01, U-02 |
Phase 2 — P2: Operational Efficiency
| ID | Feature | Priority | Status | Description | Actor |
|---|---|---|---|---|---|
| FR-09 | OTA Rate Formula Engine | P2 | ✅ IMPLEMENTED | v2.7.0: Replacing simple markup/discount adjustments with formula-template-based rate calculator. Each OTA type has configurable formula (commission%, promo%, surcharges). Multiple rate lines per OTA (member, campaign, Early Bird, Lastmin variants). Net price from Airbnb or manual → formula → BAR rate → derived lines. Auto-calculate on screen, manual push to OTAs. New entities: E-23 (OtaFormulaTemplate), E-24 (OtaRateConfig), E-25 (OtaRateLine). Replaces RatePlan/RatePlanAdjustment system. Rate Rules module (CRUD) retained for date-range overrides. Backend: ota-rate-configs module + formula-engine service (mathjs). UI: OTA cards with parameter inputs, live BAR calculation, rate line management (S-11). | U-02, U-03 |
| FR-10 | Availability Calendar | P2 | ❌ NOT IMPL | 14-day date grid (room types × dates) with color-coded cells showing available/total. Click cell → popover to block/unblock rooms → immediate OTA sync job. Property sidebar (same pattern as Rate Manager). Route: /availability. Backend: GET /availability + PUT /availability/block. Existing: Prisma model, AvailabilityCalcService, AvailabilitySyncProcessor. Missing: controller, frontend page+components. | U-03, U-04 |
| FR-11 | Booking Timeline | P2 | ❌ NOT IMPL | No route, no component, no page. Not built at all. | U-01, U-02 |
| FR-12 | Cancellation Sync | P2 | ❌ NOT IMPL | No cancelBooking method in OTA adapter interface. Booking status can be changed to "cancelled" via workflow engine, but no OTA-side detection or sync. | System |
Phase 2.5 — P2: User Profile & Preferences
| ID | Feature | Priority | Status | Description | Actor |
|---|---|---|---|---|---|
| FR-16 | User Profile Management | P2 | ✅ COMPLETE | Profile page with name/email/locale edit, live format preview. | U-01, U-02 |
| FR-17 | Password Change | P2 | ✅ COMPLETE | Current password verification, 8-char min, mustChangePassword flow. | U-01, U-02 |
| FR-18 | Auth Hydration | P2 | ✅ COMPLETE | GET /users/me on mount, 401 → redirect to login. | System |
| FR-19 | Client-Side Theme | P2 | ✅ COMPLETE | Dark/light toggle, localStorage persistence. | U-01, U-02 |
Phase 2.7 — P2: Role & Permission Management
| ID | Feature | Priority | Status | Description | Actor |
|---|---|---|---|---|---|
| FR-20 | Role CRUD | P2 | ✅ COMPLETE | CRUD with permission map, system role protection, 7 presets seeded. | U-01, U-02 |
| FR-21 | Bitwise Permission System | P2 | ✅ COMPLETE | JSONB bitmask per module, bitwise AND checks. | System |
| FR-22 | Permission-Based Access Control | P2 | ✅ COMPLETE | PermissionsGuard + @RequirePermission decorator. Frontend sidebar/button hiding. | System |
| FR-23 | Role Assignment | P2 | ✅ COMPLETE | Role selector in Users tab, JWT includes permissions. | U-01, U-02 |
Phase 2.9 — P2: Booking Status Workflow Engine
| ID | Feature | Priority | Status | Description | Actor |
|---|---|---|---|---|---|
| FR-25 | Booking Status Transitions | P2 | ✅ COMPLETE | Transition table with FK integrity, UNIQUE(from,to), allowedRoles, hooks. | U-01, U-02 |
| FR-26 | Booking Status Change | P2 | ✅ COMPLETE | PATCH with transition validation, role check, country scope, hook execution. | U-01–U-05 |
| FR-27 | Transition Hooks | P2 | ✅ COMPLETE | audit_log, update_availability, send_notification hooks execute on transitions. | System |
| FR-28 | Per-Status UI Config | P2 | ✅ COMPLETE | JSONB uiConfig on BookingStatusDef with per-role sections/buttons/editableFields. | U-01, U-02 |
| FR-29 | Workflow Config Page | P2 | ✅ COMPLETE | Settings workflow tab with status list, transitions CRUD, Mermaid preview. | U-01, U-02 |
| FR-30 | Workflow Visualization | P2 | ✅ COMPLETE | Mermaid stateDiagram-v2 with role annotations, client-side rendering. | U-01, U-02 |
Phase 2.8 — P2: Client-Side Data Optimization
| ID | Feature | Priority | Status | Description | Actor |
|---|---|---|---|---|---|
| FR-24 | Client-Side Data Caching | P2 | ✅ COMPLETE | SWR for all GETs, mutate on change, polling with tab-visibility pause, debounced filters. | System |
Phase 2.10 — P2: Supplier Room Allocation
| ID | Feature | Priority | Status | Description | Actor |
|---|---|---|---|---|---|
| FR-31 | Supplier Room Allocation | P2 | ✅ COMPLETE | M:N junction table (E-17) linking Supplier↔RoomType with roomCount. Soft warning when SUM(allocations) != totalRooms. Backend service with country scope validation. | U-01, U-02, U-04 |
| FR-32 | Supplier Allocation CRUD | P2 | ✅ COMPLETE | Create/update/delete allocations per room type via modal (S-23). Soft delete (isActive). Two controllers: room-type context (POST /room-types/:id/supplier-allocations) and standalone (PUT/DELETE /supplier-allocations/:id). | U-01, U-02, U-04 |
| FR-33 | Supplier Allocation Display | P2 | ✅ COMPLETE | Room type table shows supplier badges with room counts + AlertTriangle on mismatch. Supplier detail page shows allocations grouped by property with clickable rows. | U-01–U-07 |
| FR-34 | Booking Supplier Attribution | P2 | ✅ COMPLETE | PATCH /bookings/:id/supplier endpoint for assign/unassign. Supplier picker in Booking Detail Financial card with search dropdown. Audit logged. Permission: BOOKINGS.EDIT. | U-01–U-05 |
Phase 2.11 — P2: Activity Log Infrastructure
| ID | Feature | Priority | Status | Description | Actor |
|---|---|---|---|---|---|
| FR-35 | Activity Log Persistence | P2 | ✅ COMPLETE | Replace flat-file (logs/activity.log) with PostgreSQL activity_logs table (E-18). HTTP requests logged via middleware (fire-and-forget Prisma create). Client events batched via createMany. GET /activity-logs queries DB with orderBy createdAt desc, take N. Same API response shape — no frontend changes. ACTIVITY_LOG_ENABLED env kill-switch preserved. Visible in S-25 Activity Log tab. | System |
Phase 2.12 — P2: Customer Management
| ID | Feature | Priority | Status | Description | Actor |
|---|---|---|---|---|---|
| FR-36 | Customer Profile Management | P2 | ✅ COMPLETE | New Customer entity (E-19) with name, email, phone, nationality, notes. CRUD endpoints (GET/POST/PATCH/DELETE /customers). CS-enriched data layer independent of OTA booking data. Search by name/email/phone. Sidebar nav item with Contact icon. i18n (en/vi). | U-01–U-05 |
| FR-37 | Return Customer Linking | P2 | ✅ COMPLETE | Manual linking of bookings to customer profiles via POST /customers/:id/link and /unlink. Nullable customerId FK on Booking. Suggestion engine (GET /customers/suggestions) shows unlinked bookings matching name/email. Booking list shows return customer icon (UserCheck) + clickable link to customer detail. Booking detail shows customer chip or "Link to Customer" button. | U-01–U-05 |
| FR-38 | Customer Merge | P2 | ✅ COMPLETE | Merge duplicate customer profiles via POST /customers/:id/merge. Moves all bookings from source to target customer. Concatenates notes with ---\n[Merged from: sourceName] separator. Atomic $transaction. Audit logged. Requires DELETE permission. | U-01–U-03 |
Phase 2.13 — P2: OTA Status Tracking
| ID | Feature | Priority | Status | Description | Actor |
|---|---|---|---|---|---|
| FR-39 | OTA Status Storage | P2 | ✅ COMPLETE | otaStatus (varchar 100, nullable) and otaStatusUpdatedAt (timestamptz, nullable) added to Booking entity (E-08). Populated during upsertFromOta() sync. Existing bookings unaffected. View-only; no interaction with internal status workflow (FR-25–FR-30). | System |
| FR-40 | OTA Status Normalization | P2 | ✅ COMPLETE | OtaStatusDef entity (E-20) maps raw OTA status strings to display labels and colors per OTA type. CRUD via GET/POST/PATCH /ota-status + soft-delete/restore. Unique constraint on [rawStatus, otaType]. Graceful fallback: unknown statuses display raw string. | U-01, U-02 |
| FR-41 | OTA Status Display | P2 | ✅ COMPLETE | Bookings list (S-08) shows "OTA Status" column with colored badge (OtaStatusDef label/color when mapped, raw string fallback, "—" when null). Booking detail (S-09) shows OTA status + otaStatusUpdatedAt in Booking Info card. Independent otaStatus filter dropdown in bookings list. | U-01–U-06 |
| FR-42 | OTA Status Admin | P2 | ✅ COMPLETE | "OTA Statuses" tab in Master Data (S-24). CRUD for OtaStatusDef mappings: raw status, OTA type, normalized label, color, sort order. OTA type filter. Soft delete/restore. Same admin pattern as Booking Statuses tab. | U-01, U-02 |
Phase 2.14 — P2: List Page Layout Standardization
| ID | Feature | Priority | Status | Description | Actor |
|---|---|---|---|---|---|
| FR-43 | Unified List Page Layout | P2 | ✅ COMPLETE | Standardize all list pages (S-03, S-08, S-21, S-26) to bookings-style layout: fixed h-14 header bar with inline search + action buttons, filter bar with removable FilterChip tags + dropdown selectors + Clear All, sticky uppercase table headers, compact cell padding (px-3 py-1.5), full pagination footer with rows selector + page numbers. Extract shared FilterChip and ResizableHeader components. Add Active/Inactive status filter to Customers page. Checkboxes remain bookings-only. | U-01–U-07 |
Phase 2.15 — P2: BPM Process Management
| ID | Feature | Priority | Status | Description | Actor |
|---|---|---|---|---|---|
| FR-44 | Process Type Registry | P2 | ❌ NOT IMPL | ProcessType entity (E-21) defining available process types (booking, ota_sync, alert_resolution, customer_mgmt, ota_onboarding, property_setup). CRUD via admin API. Each type has key, label, entity_class, description. Seeded with 6 defaults matching BPM_SPEC.md P-01 to P-06. | U-01, U-02 |
| FR-45 | Generalized Status Engine | P2 | ❌ NOT IMPL | Extend BookingStatusDef and BookingStatusTransition with process_type_id FK discriminator. Same transition validation, role checks, hooks — now entity-type-aware. Backward compatible: booking workflow via process_type_id='booking' default. Existing /booking-status/* endpoints remain as aliases. New generic /process-status/* endpoints added. | System |
| FR-46 | Process Instance Tracking | P2 | ❌ NOT IMPL | ProcessInstance entity (E-22) tracking active process executions. Records: process_type, entity_id, current_status_key, started_at, started_by, completed_at. Status history via existing AuditLog (E-12). Dashboard widget showing active instances per process type. | System, U-01–U-05 |
| FR-47 | Process Visualization | P2 | ❌ NOT IMPL | Extend existing Mermaid workflow preview (S-20) to render any process type's state diagram. Process type selector dropdown in workflow config. BPMN-style Mermaid flowcharts for non-state-machine processes (P-02, P-05). Read-only process flow viewer accessible from sidebar. See BPM_SPEC.md for process definitions. | U-01, U-02 |
Phase 3 — P3: Intelligence
| ID | Feature | Priority | Status | Description | Actor |
|---|---|---|---|---|---|
| FR-13 | Rate Parity Checker | P3 | ❌ NOT IMPL | Not built. | U-01 |
| FR-14 | Revenue Analytics | P3 | ❌ NOT IMPL | Not built. | U-01 |
| FR-15 | Rate Rules Engine | P3 | ❌ NOT IMPL | Not built. DB models exist but no logic. | U-01 |
4. Screen List (S-xx)
| ID | Screen | Phase | Status | Description | Related FR |
|---|---|---|---|---|---|
| S-01 | Login | P1 | ✅ | Email/password authentication for staff | — |
| S-02 | Dashboard | P1 | ✅ | Sync status overview with country tabs, alerts, recent bookings, KPI cards | FR-06, FR-07, FR-08 |
| S-03 | Properties List | P1 | ✅ | List all properties with sync health indicators, filterable by country | FR-05 |
| S-04 | Property Detail | P1 | ✅ | Property info, room types, connected OTAs (via OTA accounts) | FR-05 |
| S-05 | OTA Accounts List | P1 | ⚠️ | List works. But links to /ota-accounts/[id] → 404 (detail page not built). Test/refresh are stubs. | FR-01 |
| S-06 | Connect OTA Account | P1 | ✅ | Multi-step wizard (select OTA → credentials → submit). No real Playwright test. | FR-01 |
| S-07 | Import Properties | P1 | ❌ | Not built. OTA adapter fetchProperties() is a stub. No import UI. | FR-02 |
| S-08 | Bookings List | P1 | ✅ | Filters, search, pagination, resizable columns, CSV export. OTA Status column + independent filter (FR-41). | FR-03, FR-41 |
| S-09 | Booking Detail | P1 | ✅ | Guest/stay/property info, status transitions, revert, audit history. OTA status in Booking Info card (FR-41). | FR-03, FR-26, FR-28, FR-41 |
| S-10 | Availability Calendar | P2 | ❌ | Route /availability in sidebar Operations group. Property sidebar + 14-day grid. Color cells + block/unblock popover. | FR-10 |
| S-11 | Rate Manager | P2 | ✅ | Rate manager form, preview table, bulk update with OTA adjustments. Route: /rates. | FR-09 |
| S-12 | Booking Timeline | P2 | ❌ | Not built. No route, no component. | FR-11 |
| S-13 | Rate Parity Report | P3 | ❌ | Not built. | FR-13 |
| S-14 | Analytics Dashboard | P3 | ❌ | Not built. | FR-14 |
| S-15 | Rate Rules Config | P3 | ❌ | Not built. | FR-15 |
| S-16 | Settings | P1 | ✅ | All 7 tabs working: Users, Roles, Booking Statuses, Workflow, Preferences, Notifications, System | FR-08 |
| S-17 | Sync Job Log | P1 | ⚠️ | List/filter/auto-refresh works. Missing "Force Sync" button (backend endpoint exists). | FR-04 |
| S-18 | User Profile | P2 | ✅ | Profile edit, password change, theme toggle | FR-16, FR-17, FR-19 |
| S-19 | Role Management | P2 | ✅ | Permission matrix editor for preset and custom roles. In Settings Roles tab. | FR-20, FR-21, FR-23 |
| S-20 | Workflow Config | P2 | ✅ | Transitions CRUD, hooks config, Mermaid preview. In Settings Workflow tab. | FR-25, FR-28, FR-29, FR-30 |
| S-21 | Suppliers List | P2 | ✅ | Paginated table: code, name, country, phone, email, total rooms allocated. Import CSV. Export CSV. | FR-31, FR-33 |
| S-22 | Supplier Detail | P2 | ✅ | Basic info + bank details cards. Room allocations table grouped by property (replaces linked properties). | FR-31, FR-33 |
| S-23 | Supplier Allocation Manager | P2 | ✅ | Modal on room type table row. Add/edit/remove supplier allocations per room type. Supplier search dropdown, inline edit, delete confirmation, mismatch warning. | FR-31, FR-32 |
| S-24 | Master Data | P2 | ✅ | Tabbed admin data management: Users (CRUD + role assign), Roles (list → S-19), Countries (color/sort edit), Booking Statuses (add/edit/soft-delete), OTA Statuses (FR-42). Route: /master-data. | FR-20, FR-23, FR-42 |
| S-25 | Logs | P2 | ✅ | Super-admin only. Two tabs: Activity Log (HTTP request stream, 5s auto-refresh, pause/resume, last 100 from activity_logs DB) and Audit Log (entity change history with diff viewer, filter by entityType/action, paginated 50/page from audit_logs DB). Route: /logs. | FR-35, FR-27 |
| S-26 | Customers List | P2 | ✅ | Searchable table: name, email, phone, nationality, booking count, last stay. Create/edit via modal. Route: /customers. | FR-36 |
| S-27 | Customer Detail | P2 | ✅ | Contact info cards (editable), notes, booking history table, link/unlink bookings, merge with another customer. Route: /customers/[id]. | FR-36, FR-37, FR-38 |
| S-28 | Process Management | P2 | ❌ | Process type list + per-type workflow config. Extends S-20 pattern with process type selector. Route: /settings/processes. | FR-44, FR-45, FR-47 |
| S-29 | Process Instances | P2 | ❌ | Active/completed process instance viewer with filters by type, status, date. Route: /process-instances. | FR-46 |
5. Entity List (E-xx)
| ID | Entity | Description | Key Fields |
|---|---|---|---|
| E-01 | User | Internal staff account | id, email, password_hash, name, role_id(FK→Role), country(nullable), locale(vi/id/ms/en, default:en), created_at |
| E-02 | Property | Hotel/lodging managed by operator | id, name, country(VN/ID/MY), timezone, currency, address, is_active. |
| E-03 | RoomType | Room category within a property | id, property_id, name, base_rate, total_rooms, max_occupancy, is_active |
| E-04 | OtaAccount | Top-level OTA login account (one per OTA credential set) | id, ota(booking/agoda/traveloka/expedia), label, credentials_encrypted, two_factor_method(none/totp/manual), two_factor_secret, session_data(jsonb), status(active/expired/error/requires_2fa), last_session_refresh, user_id, country_code, created_at |
| E-05 | OtaConnection | Lightweight link between property and OTA account | id, property_id, ota_account_id, ota_property_id, is_active, created_at |
| E-06 | OtaRoomMapping | Maps internal room type to OTA IDs | id, room_type_id, ota_connection_id, ota_room_id, ota_rate_plan_id |
| E-07 | Availability | Daily availability per room type | id, room_type_id, date, total_rooms, booked_rooms, blocked_rooms |
| E-08 | Booking | Reservation from any OTA | id, property_id, room_type_id, supplier_id(nullable FK→Supplier, future attribution), ota_type, ota_booking_id(unique), guest_name, guest_email, check_in, check_out, num_rooms, num_guests, status(confirmed/cancelled/no_show), ota_status(nullable varchar 100, raw OTA status), ota_status_updated_at(nullable timestamptz), total_amount, currency, raw_data(jsonb), created_at |
| E-09 | SyncJob | Track sync operations | id, ota_connection_id, job_type(pull_bookings/push_availability/push_rates/verify), status(pending/running/completed/failed), error, payload(jsonb), started_at, completed_at |
| E-10 | Rate | Daily rate per room type per OTA | id, room_type_id, ota_connection_id, date, rate_amount(integer), currency |
| E-11 | Alert | Overbooking and sync failure alerts | id, property_id, alert_type(overbooking/sync_failure/session_expired), severity(critical/warning/info), message, is_resolved, resolved_by, created_at, resolved_at |
| E-12 | AuditLog | Track all system changes | id, entity_type, entity_id, action(create/update/delete), old_value(jsonb), new_value(jsonb), performed_by, created_at |
| E-13 | RateRule | Automated rate adjustment rules | id, property_id, room_type_id, ota_connection_id, rule_type(markup/discount/seasonal), value, start_date, end_date, is_active |
| E-14 | Role | Named role with bitwise permissions per module | id, name(unique key), label(display), description, permissions(JSONB: {module: bitmask}), is_system(bool), is_active, created_at |
| E-15 | BookingStatusTransition | State machine transition rule between two statuses | id(UUID), from_key(FK→E-08b), to_key(FK→E-08b), allowed_roles(JSONB[]), hooks(JSONB[]), sort_order, is_active, created_at. UNIQUE(from_key, to_key), CHECK(from_key≠to_key) |
| E-16 | BookingStatusDef.uiConfig | Per-role UI visibility config (JSONB column on E-08b) | { "roleName": { sections: string[], buttons: string[], editableFields: string[] } }. Wildcard "*" as default. Controls booking detail page rendering per status×role |
| E-17 | SupplierRoomAllocation | M:N junction linking Supplier to RoomType with room count | id, supplier_id(FK→Supplier), room_type_id(FK→RoomType), room_count(int), notes(nullable), is_active(default true), created_at, updated_at. UNIQUE(supplier_id, room_type_id) |
| E-18 | ActivityLog | HTTP request activity log (replaces flat-file) | id(UUID), user_email(nullable varchar 255), user_id(nullable FK→User onDelete:SetNull), method(varchar 10: GET/POST/PUT/DELETE/PATCH/CLIENT), path(varchar 500), status(int: HTTP code or 0 for client events), screen(varchar 100: resolved UI screen name), created_at(timestamptz). Index: createdAt DESC, userEmail |
| E-19 | Customer | CS-managed guest profile (independent of OTA data) | id(UUID), name(varchar 255), email(nullable varchar 255), phone(nullable varchar 50), nationality(nullable varchar 2), notes(nullable text), is_active(bool default true), created_at, updated_at. Booking.customer_id nullable FK→Customer. Indexes: name, email, isActive |
| E-20 | OtaStatusDef | OTA status normalization mapping (raw→display) | id(UUID), raw_status(varchar 100), ota_type(enum: booking_com/agoda/traveloka/expedia), normalized_label(varchar 100), color(varchar 7, hex), sort_order(int default 0), is_deleted(bool default false), created_at, updated_at. UNIQUE(raw_status, ota_type). Indexes: otaType, isDeleted |
| E-21 | ProcessType | BPM process type registry | id(UUID), key(varchar 50 unique), label(varchar 100), description(text nullable), entity_class(varchar 100: Booking/OtaAccount/Property/Alert/Customer), is_active(bool default true), created_at. Seeded: booking, ota_sync, alert_resolution, customer_mgmt, ota_onboarding, property_setup |
| E-22 | ProcessInstance | Active process execution tracker | id(UUID), process_type_id(FK→E-21), entity_id(varchar 255: polymorphic ref), current_status_key(varchar 50), started_by(nullable FK→User), started_at(timestamptz), completed_at(nullable timestamptz), is_active(bool default true). Index: processTypeId+entityId, isActive |
| E-23 | OtaFormulaTemplate | System-level formula template per OTA type | id(UUID), ota_type(varchar 30), name(varchar 100), description(nullable varchar 500), formula(text: safe math expression e.g. net / ((1-promote/100) * (1-commission/100))), parameters(JSONB: [{name, label, type, default, min, max}]), is_default(bool), is_active(bool default true), created_at, updated_at. Index: otaType. 5 defaults seeded (trip_com, agoda, expedia, booking_com, traveloka) |
| E-24 | OtaRateConfig | Per-property×OTA rate configuration linking template to parameter values | id(UUID), property_id(FK→Property), ota_connection_id(FK→OtaConnection, UNIQUE), formula_template_id(FK→E-23), parameter_values(JSONB: {commission:15, promote:5, surcharge:0}), is_active(bool default true), created_at, updated_at. Index: propertyId. Replaces RatePlan+RatePlanAdjustment |
| E-25 | OtaRateLine | Rate line variant within OTA config (member, campaign, etc.) | id(UUID), rate_config_id(FK→E-24), name(varchar 100: "Basic"/"Member"/"Early Bird"), line_type(varchar 30: base/member/campaign), discount_percent(decimal 5,2: % off BAR, default 0), sort_order(int default 0), is_active(bool default true), created_at, updated_at. Index: rateConfigId |
6. Non-Functional Requirements
6.1 Performance
| Metric | Target |
|---|---|
| Booking detection latency | < 3 minutes (poll interval) |
| Availability push latency | < 60 seconds after detection |
| End-to-end sync | < 5 minutes |
| Dashboard load time | < 2 seconds |
| Concurrent OTA sessions | 400+ (100 properties x 4 OTAs, shared via OTA accounts) |
6.2 Security
| Requirement | Implementation |
|---|---|
| OTA credential storage | AES-256-GCM encryption at rest |
| Internal auth | bcrypt password hashing, JWT sessions |
| Session management | HttpOnly cookies, CSRF protection |
| Data protection | PDPA/PDPD/PP71 compliant (VN/ID/MY) |
| Access control | Bitwise permission-based (JSONB per module: V/C/E/D bitmask) + country scoping. 7 preset roles + custom roles |
| Audit trail | Entity mutations logged in E-12 (audit_logs). HTTP request activity logged in E-18 (activity_logs) |
6.3 Reliability
| Requirement | Implementation |
|---|---|
| Job retry | BullMQ exponential backoff, max 3 retries |
| Session recovery | Auto-detect expired sessions, alert for re-login |
| Data consistency | PostgreSQL transactions for availability updates |
| Monitoring | Sync failure alerts within 1 minute |
| Backup | Daily PostgreSQL backup |
6.4 Scalability
| Dimension | Target |
|---|---|
| Properties | 100+ |
| Countries | 3 (VN, ID, MY) |
| OTA channels | 4 |
| OTA accounts | ~12-20 (few accounts per OTA per country) |
| Total connections | ~400+ |
| Bookings/day | ~200-800 |
| Sync jobs/hour | ~4,800+ (staggered scheduling) |
6.5 Tech Stack
| Component | Technology |
|---|---|
| Backend | NestJS (TypeScript) |
| Frontend | Next.js 14+ (App Router, TypeScript) |
| Database | PostgreSQL 16 |
| Cache/Queue | Redis 7 + BullMQ |
| ORM | Prisma |
| Browser Automation | Playwright |
| Monorepo | Turborepo |
| Auth | Passport.js + JWT (httpOnly cookie) |
| Notifications | nodemailer (LINE Notify stub only) |
7. Key Decisions (D-xx)
| ID | Decision | Context | Chosen | Rationale |
|---|---|---|---|---|
| D-01 | OTA integration method | No official API access | HTTP request replay + Playwright fallback | Fastest path; replay intercepted extranet requests |
| D-02 | Backend language | Rust vs TypeScript | TypeScript (NestJS) | Solo dev; Playwright is JS-native; shared types with frontend |
| D-03 | Sync strategy | Push-only vs push+verify | Push with polling verification | Push alone unreliable; periodic verification catches silent failures |
| D-04 | Anti-overbooking | Optimistic vs pessimistic | Pessimistic locking + buffer rooms | Overbooking cost is high; prefer false negatives over overbookings |
| D-05 | Currency handling | Decimal vs integer | Integer (cents/smallest unit) | VND and IDR have no decimals; consistent integer math avoids float errors |
| D-06 | Timezone | Local vs UTC storage | UTC storage, local display | Indonesia has 3 timezones; UTC avoids conversion bugs |
| D-07 | PMS scope | Build vs buy | Defer (out of scope) | PMS is separate product; focus on channel management first |
| D-08 | OTA credential model | Per-property vs per-account | Per-account (OtaAccount entity) | One OTA login manages N properties; avoids duplicate credentials and sessions |
| D-09 | Onboarding flow | Property-first vs OTA-first | OTA-first | Matches real workflow: OTA accounts exist before properties are added to system |
| D-10 | Country scoping | Global access vs country-scoped | Country-scoped staff + global manager | 100+ properties across 3 countries; staff only needs their country |
| D-11 | Supported countries | TH/VN/ID vs VN/ID/MY | VN/ID/MY only | Thailand removed per business decision; Malaysia added |
| D-12 | OTA-country mapping | DB table vs config constant | Hardcoded config constant | YAGNI — 4 OTAs x 3 countries = 12 combos. Static config sufficient |
| D-13 | Permission model | Enum roles vs bitwise permissions | Bitwise JSONB permissions | System expanding to ERP; enum can't express per-module CRUD. JSONB {module: bitmask} is extensible, uses bitwise AND for checks, and supports unlimited modules without schema changes |
| D-14 | Permission storage | Single BigInt vs JSONB map | JSONB map per module | BigInt limited to 16 modules (64 bits / 4 bits). JSONB supports unlimited modules, is human-readable in queries, and PostgreSQL indexes JSONB efficiently |
| D-15 | Role presets | Hardcoded vs DB-stored | DB-stored with is_system flag | Presets seeded on deploy, protected from deletion. Custom roles addable by admin. Avoids code changes for new roles |
| D-16 | Client-side caching library | React Query vs SWR vs manual | SWR | 4KB bundle, hooks-only API, built by Vercel for Next.js. Stale-while-revalidate pattern fits this app's simple GET/mutate workflows. React Query is overkill for current needs |
| D-17 | Workflow data model | All-JSONB vs fully-normalized vs hybrid | Hybrid (transition table + JSONB uiConfig) | Transitions need FK integrity and queryability (relational). UI config is always read as blob (JSONB). Best balance of normalization and pragmatism |
| D-18 | Hooks location | Hooks on statuses vs transitions | Hooks on transitions | Same status pair can have different hooks depending on direction. E.g., confirmed→cancelled triggers availability restore, but cancelled→confirmed does not. Transition-level hooks are more precise |
| D-19 | Supplier model level | Property-level vs room-type-level | Room-type-level (M:N junction) | One supplier provides rooms across multiple properties. Same room type can be split across suppliers by count. Accounting-only — zero OTA sync impact |
| D-20 | Supplier allocation validation | Hard block vs soft warning | Soft warning | SUM(roomCount) != totalRooms shows warning in API response and UI badge. Not blocked — allows flexible allocation during data entry |
| D-21 | Supplier allocation delete | Hard delete vs soft delete | Soft delete (isActive) | Consistent with other entities. Inactive allocations excluded from SUM checks and display |
| D-22 | Activity log storage | Flat-file vs Kafka vs Postgres | Postgres (activity_logs table) | Flat-file not queryable, lost on redeploy. Kafka is over-engineering for this scale (single monolith, B2B tool). Postgres already present — zero new infrastructure, ACID, indexed, queryable. Same API response shape preserved so frontend unchanged |
| D-23 | Customer identification | Auto-match vs manual linking | Manual linking by CS | Auto-matching on name/email produces false positives (same name, different guest; OTAs use different emails). CS manually creates profiles and links bookings — zero false positives. Suggestion engine shows similar unlinked bookings to speed up workflow |
| D-24 | Customer scope | Country-scoped vs global | Global (no country scoping) | Guests travel across countries. CS needs cross-country view of guest history. Customer entity is not country-scoped |
| D-25 | Merge notes strategy | Keep target vs concatenate | Concatenate with separator | Append source notes below target with ---\n[Merged from: sourceName] separator. No data loss on merge |
| D-26 | OTA status tracking | Auto-map to internal vs view-only | View-only with optional normalization | OTA status stored as raw string, completely independent of internal workflow. Optional OtaStatusDef table normalizes raw strings to display labels/colors per OTA. Graceful fallback: unknown statuses display raw. Zero coupling with internal status engine |
| D-27 | BPM engine approach | Camunda vs bpmn-engine vs extend current | Extend current engine | Solo dev, zero budget, internal tool. Current state machine covers 90% of needs. Add entity_type discriminator for multi-process support. Full BPMN runtime is YAGNI. ISO 19510 referenced for documentation, not runtime compliance. See BPM_SPEC.md |
| D-28 | Process documentation format | BPMN XML vs Mermaid diagrams | Mermaid flowcharts | Already used for workflow preview (S-20). Renders inline in markdown. No external tooling needed. Covers 80% of BPMN notation visually |
| D-29 | Rate calculation approach | Visual formula builder vs expression input vs template+params | Template + configurable parameters | Real OTA formulas follow 2-3 patterns. Templates are system-managed (dev-reviewed), users only fill numeric parameter values. Covers 95%+ of cases. Visual builder is 2-4 weeks YAGNI; expression input is error-prone. mathjs used for safe evaluation (no eval()) |
| D-30 | Rate line derivation | Independent rates vs BAR-derived | BAR-derived with discount % | Calculate BAR once via formula, derive member/campaign lines as % discount from BAR. Simpler model, fewer parameters, matches real OTA pricing structure |
8. OTA-Country Availability Matrix
| OTA | VN | ID | MY |
|---|---|---|---|
| Booking.com | Yes | Yes | Yes |
| Agoda | Yes | Yes | Yes |
| Traveloka | Yes | Yes | Yes |
| Expedia | Yes | Yes | Yes |
All 4 OTAs available in all 3 countries. Matrix exists as guardrail for future additions.
9. Out of Scope
- PMS features (check-in/out, housekeeping, invoicing) — note: basic guest profiles (E-19) added for CS use
- Direct booking engine
- Mobile native app (responsive web sufficient)
- Multi-language UI (English-only, internal tool)
- Payment processing (handled by OTAs)
- Guest communication (handled by OTAs)
- SaaS/multi-tenant architecture
- Official OTA API partnerships (future migration path)
- Thailand market (removed)
10. Assumptions
| # | Assumption | Risk if Wrong |
|---|---|---|
| 1 | OTA extranet API calls can be captured and replayed | Entire approach fails |
| 2 | OTA sessions persist for hours/days | Frequent re-auth breaks automation |
| 3 | 3-5 min sync latency acceptable | Overbookings still occur within window |
| 4 | OTAs won't detect/block automation at low volume | Account ban risk |
| 5 | Staff will adopt the dashboard | Tool goes unused |
| 6 | Room type mappings between OTAs are stable | Mapping breaks cause wrong updates |
| 7 | One OTA account manages multiple properties | Schema design based on this |
11. Risks
| Risk | Severity | Likelihood | Mitigation |
|---|---|---|---|
| OTA detects automation, bans account | CRITICAL | Medium | Rate limiting, human-like request patterns, start with 2-3 test properties |
| OTA extranet API changes | HIGH | High | Isolated adapters per OTA, health monitoring, fast-fix pipeline |
| 2FA blocks automation | HIGH | High | TOTP: automate via secret. SMS/Email: manual login + long session persistence |
| Solo dev maintenance burden | HIGH | High | Start with 2 OTAs in Phase 1, stabilize before expanding |
| Overbooking within sync window | MEDIUM | Medium | Buffer rooms, pessimistic locking, instant alerts |
| Cross-OTA property matching error | MEDIUM | Low | Always show confirmation UI, allow undo, manual override |
| 400+ connections overwhelm server | MEDIUM | Medium | Country-scoped filtering reduces active load; staggered scheduling |