Direct Hotel Booking Engine: Inventory Locks, OTA Sync & Unit Economics

Operating workflow and system boundaries

Guest search → nightly inventory check → expiring hold → payment authorisation → confirmed reservation → outbox event → OTA channel acknowledgement. Room-type inventory and assigned physical rooms are separate concerns: pooled capacity needs nightly atomic updates, while physical-room allocations need non-overlap constraints.

Domain-specific PostgreSQL schema

Illustrative design excerpt. Final tables, constraints and security policies depend on discovery and review.

CREATE TABLE room_types (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  property_id bigint NOT NULL,
  code text NOT NULL,
  UNIQUE (property_id, code)
);
CREATE TABLE rate_plans (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  room_type_id bigint NOT NULL REFERENCES room_types(id),
  currency char(3) NOT NULL,
  cancellation_policy text NOT NULL
);
CREATE TABLE inventory_calendar (
  room_type_id bigint NOT NULL REFERENCES room_types(id),
  stay_date date NOT NULL,
  capacity integer NOT NULL CHECK (capacity >= 0),
  held integer NOT NULL DEFAULT 0 CHECK (held >= 0),
  booked integer NOT NULL DEFAULT 0 CHECK (booked >= 0),
  PRIMARY KEY (room_type_id, stay_date),
  CHECK (held + booked <= capacity)
);
CREATE TABLE reservations_lock (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  room_type_id bigint NOT NULL REFERENCES room_types(id),
  check_in date NOT NULL,
  check_out date NOT NULL,
  rooms integer NOT NULL CHECK (rooms > 0),
  expires_at timestamptz NOT NULL,
  state text NOT NULL CHECK (state IN ('held','confirmed','expired','cancelled')),
  payment_idempotency_key text NOT NULL UNIQUE,
  CHECK (check_out > check_in)
);
CREATE TABLE ota_channel_sync (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  channel text NOT NULL,
  external_event_id text NOT NULL,
  reservation_id bigint REFERENCES reservations_lock(id),
  inventory_version bigint NOT NULL,
  sync_state text NOT NULL,
  attempt_count integer NOT NULL DEFAULT 0,
  UNIQUE (channel, external_event_id)
);

Acquire inventory_calendar rows for every stay date in a consistent order inside one database transaction. Reject the whole hold if any night lacks capacity. Confirming or expiring a hold must update both the reservation and nightly counters atomically. A scheduled expiry worker must recheck reservation state under lock. Payment success after expiry enters reconciliation; it must not silently recreate inventory.

Failure scenarios and acceptance evidence

• Race two guests for the last room and prove exactly one hold succeeds.
• Deliver the same payment callback twice and confirm one reservation.
• Delay an OTA acknowledgement, retry the same event and expose the unresolved state to staff.
• Test cancellation, partial refund, date change and property timezone boundaries.
• Alert when channel inventory versions diverge; do not claim zero overselling without accounting for external-channel latency.

Operational economics with explicit assumptions

Illustrative annual model: 20 rooms × 365 nights × 65% occupancy × $100 average daily rate = $474,500 room revenue. Moving 15 percentage points of revenue from OTA to direct shifts $71,175. At an assumed 18% OTA commission, avoided commission is $12,811.50. An average three-night stay implies about 237 shifted bookings; at $15 blended acquisition cost per booking, acquisition costs $3,558.75. Subtract $1,423.50 in incremental direct payment costs (assumed 2%) and $2,400 annual maintenance: approximately $5,429.25 contribution before build cost and tax. A $6,000 build would take about 13.3 months to recover at that annual run rate. Assumptions change with channel mix and existing payment fees.

Separate first-time paid acquisition from repeat guests. If 30% of shifted bookings cost $5 to reactivate and 70% cost $20 to acquire, blended CAC is $15.50, not zero. Track direct-booking share, cancellations, net room revenue, acquisition spend and repeat rate by cohort. A guest who would already have booked direct is not an incremental commission saving.

Migration and release review

Import a representative sample into staging and reconcile source identifiers, record counts and exceptions. Run a supervised pilot, record sign-off criteria, rehearse restoration, and agree the cutover owner. The final migration and rollback procedure must fit this operating workflow.

Technical references and project scope

Primary technical reference. Review the relevant software scope or plan an indicative budget. Fixed-milestone billing uses direct milestone invoicing, with optional third-party escrow by agreement for international contracts. Hosting, API fees and ongoing maintenance are scoped separately.