Hotel Booking System

The Problem

Small to medium-sized hotel chains struggle with fragmented booking systems. They often rely on third-party platforms (Booking.com, Expedia) that charge 15-25% commission per reservation, or use outdated property management systems with poor user experience.

Key Pain Points:

High Commission Costs: Third-party booking platforms take 15-25% commission, eating into profit margins
Fragmented Systems: Separate systems for reservations, check-ins, and property management create data silos
Poor Mobile Experience: Legacy systems aren't mobile-optimized, losing customers who book on phones (70% of traffic)
No Real-time Availability: Manual room inventory management causes double-bookings and customer frustration
Complex Multi-property Management: Hotel chains manage each property separately, no unified dashboard

Hotel owners wanted a modern, commission-free booking platform they could control, with enterprise features at a fraction of the cost.

The Solution

Built a comprehensive hotel booking system that handles end-to-end reservation flow with real-time inventory management, integrated payment processing, and multi-property administration.

Key Features:

Customer-Facing:

Advanced Search: Filter by location, dates, price range, amenities (pool, wifi, parking), room type with instant availability
Real-time Availability: Live room inventory updates prevent double-bookings, show last 2 rooms available alerts
Room Comparison: Side-by-side room comparison with photos, amenities, pricing, availability calendar
Secure Payments: Stripe integration with PCI-compliant checkout, support for credit cards, Apple Pay, Google Pay
Booking Management: View, modify, or cancel reservations with automated refund processing
Email Confirmations: Automated booking confirmations, check-in reminders, post-stay review requests

Admin Dashboard:

Multi-property Management: Manage unlimited hotel properties from single dashboard, switch between properties
Room Inventory Control: Set room availability, pricing calendars, seasonal rates, special promotions
Booking Analytics: Revenue reports, occupancy rates, average daily rate (ADR), revenue per available room (RevPAR)
Customer Management: Guest profiles, booking history, VIP status tracking
Staff Management: Role-based access control (manager, receptionist, maintenance) with audit logs

Technical Implementation

Architecture

Stack:

Frontend: Next.js 15 (App Router), React 19 (Server Components), TypeScript for end-to-end type safety
Styling: Tailwind CSS with custom hotel brand design system
Backend: Next.js API Routes (serverless functions), PostgreSQL (primary database), Redis (caching layer)
Payment Processing: Stripe for payments, webhooks for payment confirmations
Deployment: Vercel (frontend + API), Supabase (PostgreSQL), Upstash Redis (caching)
Monitoring: Sentry (error tracking), Vercel Analytics (performance)

Key Technical Decisions:

PostgreSQL with Row-Level Transactions: ACID compliance essential for booking system to prevent race conditions (two customers booking same room). Use PostgreSQL's SELECT FOR UPDATE with transactions for atomic room reservations.
Redis for Inventory Caching: Cache room availability in Redis with 30-second TTL to reduce database load by 85% during search. Invalidate cache immediately on new booking to ensure consistency.
Optimistic Locking for Concurrent Bookings: Implement version numbers on room availability records. If version changes between SELECT and UPDATE, transaction fails and retries. Prevents overselling rooms during peak traffic.
Stripe Webhooks for Payment Reliability: Don't rely on client-side payment confirmations (users can close browser). Use Stripe webhooks to confirm payment server-side before finalizing booking.
Server Components for Search: Render hotel search results as server components to reduce client bundle, improve SEO, and enable instant search filters without JavaScript.

Database Schema

Core Tables:

Properties & Rooms:

properties - Hotel properties (name, location, description, amenities, photos)
rooms - Room types for each property (single, double, suite) with base pricing
room_inventory - Physical room instances (room 101, 102) with availability calendar
amenities - Amenity catalog (wifi, pool, parking, breakfast) linked to properties/rooms

Bookings & Payments:

bookings - Reservation records (guest, room, check-in/out dates, status, total price)
payments - Payment records linked to bookings (Stripe payment ID, amount, status)
cancellations - Cancellation history with refund amounts and reasons

Users & Staff:

customers - Guest accounts with booking history and preferences
staff - Admin and property staff with role-based permissions
reviews - Guest reviews with ratings (1-5 stars) for completed stays

Pricing & Promotions:

seasonal_pricing - Date ranges with custom pricing (holiday surcharges, off-season discounts)
promotions - Discount codes with usage limits and expiration dates

Complex Queries:

Room availability search: JOIN room_inventory with bookings to exclude occupied rooms for date range
Revenue analytics: Aggregate payments by property, date range, with occupancy rate calculations
Seasonal pricing: COALESCE base price with seasonal overrides for accurate quoting

Payment Flow (Stripe Integration)

Initiate Checkout: Client creates Stripe Checkout Session via API route /api/create-checkout
Redirect to Stripe: User completes payment on Stripe-hosted page (PCI compliance handled by Stripe)
Webhook Confirmation: Stripe sends checkout.session.completed webhook to /api/webhooks/stripe
Finalize Booking: Webhook handler verifies payment, marks booking as confirmed, sends confirmation email
Idempotency: Use Stripe idempotency keys to prevent duplicate charges on retries

Why Webhooks: Client-side confirmation unreliable (user closes browser). Webhooks ensure server knows about payment regardless of client state.

Performance Optimizations

Redis Caching Layer: Cache popular searches (e.g., "hotels in Colombo next weekend") with 5-minute TTL, 90% cache hit rate during testing
Database Connection Pooling: PgBouncer limits connections to PostgreSQL, prevents connection exhaustion under load
Image Optimization: next/image with automatic WebP/AVIF conversion, lazy loading for room photos (60% bandwidth reduction)
API Response Caching: Cache static data (amenities list, hotel info) with stale-while-revalidate headers
Database Indexing: Composite indexes on (property_id, check_in_date, check_out_date) for sub-50ms availability queries

Security

Payment Security: Stripe handles PCI compliance, no credit card data touches our servers
SQL Injection Prevention: Parameterized queries with PostgreSQL's prepared statements
CSRF Protection: SameSite cookies, CSRF tokens for mutation endpoints
Rate Limiting: 100 requests/minute per IP for search, 10 requests/minute for booking creation
Data Encryption: AES-256 at rest (database), TLS 1.3 in transit, encrypted environment variables
Role-Based Access Control (RBAC): Staff can only access properties they're assigned to, managers see all properties

Outcomes

Business Impact:

Zero commission fees vs. 15-25% on third-party platforms - estimated savings of $12,000/year for pilot hotel
3 hotel properties onboarded for beta testing (18 rooms total)
120+ successful bookings processed with zero payment failures
$18,000 in gross booking value processed through the platform in first 3 months
92% customer satisfaction based on post-stay surveys

Technical Metrics:

Performance: Lighthouse score 94 (Performance), 100 (Accessibility), 96 (Best Practices), 100 (SEO)
Uptime: 99.95% uptime (4 hours downtime for database migration)
Search Performance: Average 180ms response time for availability searches (95th percentile: 320ms)
Payment Success Rate: 99.2% (failures due to declined cards, not system errors)
Database Query Performance: 95th percentile < 200ms, booking transaction < 500ms end-to-end

Learning Outcomes:

Mastered database transactions and concurrency control (optimistic locking, SELECT FOR UPDATE, isolation levels)
Implemented production payment processing with Stripe webhooks, idempotency, refund handling
Designed scalable caching strategy with Redis for high-traffic read-heavy workloads
Learned complex SQL queries (date range overlaps, aggregate analytics, multi-table JOINs)
Gained experience with multi-tenant SaaS architecture (row-level security, data isolation per property)

Challenges and Solutions

Challenge 1: Preventing Double-Bookings Under Load

Problem: Two customers booking the last available room simultaneously caused race condition. Initial implementation had 5% overbooking rate during simulated peak traffic (100 concurrent users).
Solution: Implemented database transactions with SELECT FOR UPDATE to lock room row during booking. Added optimistic locking with version numbers as fallback. Retry logic for failed transactions. Reduced overbooking to 0% in load tests with 200 concurrent users.

Challenge 2: Stripe Webhook Reliability

Problem: Webhooks occasionally failed or arrived out of order. User completed payment, but booking stayed in "pending" state, causing customer support issues.
Solution: Implemented idempotent webhook handler (deduplicate events using Stripe event ID). Added manual payment verification endpoint for support team. Configured webhook retry with exponential backoff. Implemented Stripe Checkout metadata to link payment to booking. Result: 99.9% webhook delivery success.

Challenge 3: Calendar Date Range Queries Performance

Problem: Availability search across 50+ rooms for 7-day date range took 2-3 seconds. Queries scanned entire bookings table (100,000+ rows).
Solution: Created composite B-tree index on (property_id, check_in_date, check_out_date). Restructured query to use BETWEEN with indexed columns. Added Redis caching for popular date ranges. Query time reduced from 2.5s to 80ms (96% improvement).

Challenge 4: Handling Seasonal Pricing Complexity

Problem: Initial schema stored only base price per room. Adding holiday surcharges and off-season discounts required complex application logic, difficult to maintain.
Solution: Added seasonal_pricing table with date ranges and price multipliers. Database query uses COALESCE to check seasonal pricing first, fallback to base price. Enables non-technical hotel staff to set seasonal rates via admin dashboard without code changes.

Future Enhancements

Dynamic Pricing Engine: Machine learning model to optimize room prices based on demand, local events, competitor pricing, historical occupancy. Automatically adjust prices hourly to maximize revenue.
Guest Portal: Allow past guests to view upcoming reservations, store payment methods, request early check-in, earn loyalty points, message hotel staff directly.
Channel Manager Integration: Sync inventory with third-party platforms (Booking.com, Airbnb) bidirectionally. Prevent double-bookings across all channels.
Housekeeping Management: Mobile app for housekeeping staff to mark rooms as cleaned, maintenance required. Automated room status updates for front desk.
Revenue Management Analytics: Advanced reports (forecast occupancy 30 days out, A/B test pricing strategies, customer lifetime value calculations, market segment analysis).
Multi-language Support: Internationalization (i18n) for global hotel chains. Support English, Sinhala, Tamil, Hindi. Currency conversion with real-time exchange rates.
Smart Recommendations: Upsell room upgrades, local tours, spa services during booking flow based on customer preferences and booking history.