Session Recap — Hotel Booking API Design¶

Starting Point¶

Second problem in the API + Application Design pillar. Same skills as URL shortener — resource modelling, endpoints, contracts, schema — but with added complexity: multiple actors, state transitions, and a spatial query requirement.

What We Did¶

Step 1 — Domain and actors first¶

HOTEL - id
ROOM  - id, hotelId
USER  - id
BOOKING - id, roomId, userId

ACTORS:
USER   → search rooms, create booking, cancel booking, view history
HOTEL  → view orders, approve, reject, mark prepared

This is the correct interview starting point — define domain entities and who does what before touching endpoints.

Step 2 — Endpoints¶

#USER
GET    /hotels
GET    /hotels/{hotelId}/rooms?checkIn=&checkOut=&occupancy=
POST   /bookings
GET    /users/me/bookings
PATCH  /bookings/{id}               → simple field updates only

#USER + HOTEL (role-based)
POST   /bookings/{id}/cancel
POST   /bookings/{id}/approve
POST   /bookings/{id}/reject

#ADMIN
GET    /admin/users/{userId}/bookings

Step 3 — State transitions¶

Approve/reject/cancel are action endpoints, not PATCH. Reason: these transitions have cross-aggregate side effects — notifications, payment processing, availability updates, audit logs. PATCH is for simple field updates with no side effects.

Rule: if the state transition has cross-aggregate effects, use an action endpoint. If it's just a field update, use PATCH.

Cancel is only valid from created state. Attempting cancel from any other state returns 422 Unprocessable Entity.

Step 4 — Schema¶

HOTEL(id PK, gps_location GEOGRAPHY(POINT))
ROOM(id PK, hotel_id FK, occupancy_type)
USER(id PK, role, created_at)
BOOKING(id PK, room_id FK, user_id FK, start_date, end_date, status, created_at)

Indexes:
HOTEL: GIST idx(gps_location)
BOOKING: idx(room_id, start_date, end_date)

Step 5 — Availability SQL¶

Find rooms in a hotel with no conflicting bookings for the requested dates:

SELECT * FROM rooms r
WHERE r.hotel_id = @hotelId
AND r.id NOT IN (
    SELECT b.room_id FROM bookings b
    WHERE b.start_date < @checkOut
    AND b.end_date > @checkIn
)

The overlap formula — memorise this:

Two date ranges overlap if: start1 < end2 AND end1 > start2

Equivalently: a booking does NOT conflict if existingEnd <= checkIn OR existingStart >= checkOut. Flip it for conflict detection.

Concepts Covered¶

Nested resources¶

GET /hotels/{hotelId}/rooms — rooms belong to a hotel. The parent resource goes in the path, not in a query param. GET /hotels/rooms is wrong — which hotel?

/me convention¶

GET /users/me/bookings — /me means "the currently authenticated user." Server extracts user ID from JWT. Client never passes their own userId in the URL — that's a security surface area. Standard convention used by GitHub, Spotify, Slack.

/admin namespace¶

GET /admin/users/{userId}/bookings — separate namespace for admin-scoped routes. Different middleware, different auth checks, different audit logging. Not because admin is a resource — it's a route namespace.

PATCH vs action endpoints¶

PATCH: status update with no side effects — update a booking's special requests note. Action endpoint: state transition with cross-aggregate effects — approve triggers payment + notification + availability update.

422 vs 409¶

409 Conflict — implies a collision between two resources (e.g. duplicate creation)
422 Unprocessable Entity — request is syntactically valid but invalid for current state (e.g. cancelling an already-approved booking)

Use 422 for invalid state transitions.

Date overlap formula¶

Wrong instinct: startDate >= checkIn AND endDate <= checkOut — this only catches bookings fully contained within the window. Correct: start_date < @checkOut AND end_date > @checkIn — catches all overlap cases including partial overlaps and fully enclosing bookings.

Composite index for availability query¶

Query: WHERE room_id = X AND start_date < @checkOut AND end_date > @checkIn

Index: idx(room_id, start_date, end_date) - room_id first — eliminates most rows instantly - start_date next — most bookings are short so this is highly selective - end_date as covering column — avoids table lookup entirely

Spatial queries (PostGIS — conceptual only)¶

For GPS-based hotel search, naive lat/lng columns can't be indexed efficiently. Use: - Column type: GEOGRAPHY(POINT) via PostGIS extension - Index type: GIST (spatial B-tree) - Query: ST_DWithin(location, ST_MakePoint(@lng, @lat), 5000) — 5000 metres radius

Interview answer if you haven't used it: "I'd use PostgreSQL's PostGIS extension with a GEOGRAPHY column and a GIST spatial index. I know the primitives — ST_DWithin for radius queries, GIST for spatial lookups — but haven't used it in production."

What We Messed Up¶

GET /hotels/rooms — missing hotel context Initial endpoint had no way to specify which hotel. Fix: GET /hotels/{hotelId}/rooms. Room is a child resource of hotel — parent goes in the path.

Date overlap condition Initial attempt: startDate >= checkIn AND endDate <= checkOut. This only finds bookings fully inside the window. Misses bookings that start before or end after the window. Correct formula: start_date < checkOut AND end_date > checkIn.

Key Values and Config to Remember¶

Item	Value
Availability overlap formula	`start_date < @checkOut AND end_date > @checkIn`
Invalid state transition code	`422`
Conflict code	`409`
Authenticated user convention	`/me`
Admin namespace	`/admin/...`
Spatial index type	`GIST`
Spatial column type	`GEOGRAPHY(POINT)`
Spatial function	`ST_DWithin(location, ST_MakePoint(lng, lat), radiusMetres)`

Unanswered Questions / Things to Investigate¶

PostGIS in depth — ST_DWithin, ST_MakePoint, GIST index internals. Needs a dedicated session.
Booking pagination — GET /users/me/bookings with many bookings. Not designed. Apply keyset pagination (same as URL shortener list endpoint).
Payment on booking — not scoped. If added, cancel after payment triggers refund flow — cross-aggregate, definitely an action endpoint not PATCH.

What's Next¶

Hotel Booking is complete. Move to Food Delivery problem for more reps, or Concepts recap for pagination, idempotency, error contracts, versioning, rate limiting.