Booking SystemsQuestion #3ADVANCED

IRCTC: High-Scale Train Booking, RAC/WL & Tatkal

Massive burst concurrency reservation architecture handling Tatkal surge bookings, complex RAC/Waitlist allocation state machines, and chart preparation.

Category: Category 2: Booking Systems•Updated: 2026-08-30
#Booking#Train Tickets#RAC/WL#Tatkal Surge#Chart Preparation

Design a Ticket Booking System like IRCTC

Author: System Design Expert | Date: 2026-08-28 Domain: Ultra-High-Concurrency Railway Ticket Reservation System


Table of Contents

  1. Requirements Clarification
  2. Back-of-Envelope Calculations
  3. System Interface Design
  4. High-Level Architecture
  5. Core Component Design
  6. Data Model
  7. Key Design Decisions
  8. Concurrency Control
  9. Scalability Strategy
  10. Curveball Scenarios
  11. Failure Modes and Mitigations

1. Requirements Clarification

1.1 Domain Context

IRCTC (Indian Railway Catering and Tourism Corporation) handles ticket booking for Indian Railways — one of the world’s largest rail networks. Key characteristics that differentiate this from a movie booking system:

  • Massive scale: 100M+ registered users, 30M+ daily page views
  • Extreme burst patterns: Tatkal quota opens at 10 AM / 11 AM with millions of concurrent users
  • Complex inventory: Berths (not seats), multiple classes, quotas, routes with intermediate stations
  • Government-critical: Cannot afford data loss; strict audit requirements
  • T+1 booking window: Tickets open exactly 120 days in advance at a specific time

1.2 Functional Requirements

ID Requirement Priority
FR-01 Users can search trains by source, destination, date, class Must
FR-02 Users can view train schedule, route, and running status Must
FR-03 Users can check seat/berth availability in real-time Must
FR-04 Users can book tickets (Confirmed / RAC / Waiting List) Must
FR-05 Support multiple quotas: General, Tatkal, Premium, Ladies, Senior Citizen, Defence Must
FR-06 Support multiple classes: 1A, 2A, 3A, 3E, SL, 2S, CC, EC Must
FR-07 Dynamic fare calculation (distance-based, class-based, quota-based, surcharges) Must
FR-08 Chart preparation and final confirmation before departure Must
FR-09 Ticket cancellation with refund based on time-to-departure rules Must
FR-10 PNR (Passenger Name Record) status tracking Must
FR-11 E-ticket generation with QR code for TTE verification Must
FR-12 TTE (Train Ticket Examiner) app for on-board verification Must
FR-13 Current booking (boarding at intermediate stations) Should
FR-14 Train running status tracking (live GPS-based) Should
FR-15 Auto-cancellation of unpaid bookings after timeout Must
FR-16 Support for group booking and bulk tickets Could
FR-17 Waitlist confirmation prediction and notification Should

1.3 Non-Functional Requirements

ID Requirement Target
NFR-01 Availability 99.9% uptime (43.8 min downtime/month max)
NFR-02 Latency - Search < 1s p95 for train search
NFR-03 Latency - Availability < 2s p95 for availability check
NFR-04 Latency - Booking < 3s p95 for booking confirmation
NFR-05 Throughput - Normal 10,000 bookings/hour
NFR-06 Throughput - Tatkal 100,000 booking attempts in first 5 minutes
NFR-07 Consistency Strong consistency for berth allocation (zero overbooking)
NFR-08 Durability Zero data loss for confirmed bookings (financial transactions)
NFR-09 Security PCI-DSS, government data protection compliance
NFR-10 Fairness No user should be systematically disadvantaged during Tatkal
NFR-11 Auditability Complete audit trail for all booking operations

2. Back-of-Envelope Calculations

2.1 Assumptions and Scale Estimates

Parameter Conservative Target (IRCTC-scale) Aggressive
Registered Users 50M 200M 500M
Daily Active Users 2M 10M 30M
Peak Concurrent (Tatkal) 500K 5M 10M
Trains in network 5,000 12,000 15,000
Stations 3,000 7,000 8,000
Coaches per train (avg) 15 20 22
Berths per coach (avg) 72 72 72
Berths per train (avg) 1,080 1,440 1,584
Daily train-runs 10,000 25,000 30,000
Total berths/day 10.8M 36M 47.5M
Bookings per day 500K 2M 5M
Tatkal bookings/day 50K 200K 500K

2.2 Storage Estimates

Train Schedule Data

Trains: 12,000 x 10 KB (schedule, route, coaches) = 120 MB
Train-runs (daily): 25,000 x 5 KB x 120 days (booking window) = 15 GB
Historical runs: 25,000 x 5 KB x 365 = 45.6 GB/year

Berth Inventory (Hot Data)

Active train-runs at any time: 25,000 x 120 days = 3M train-runs
Berths per train-run: 1,440
Total active berth records: 3M x 1,440 = 4.32B records
Per berth record: ~150 bytes
Total hot inventory: 4.32B x 150B = ~648 GB

BUT: Not all train-runs are equally hot.
  - Next 7 days: 25,000 x 7 = 175K train-runs -> 252M berths -> 38 GB
  - Next 30 days: 750K train-runs -> 1.08B berths -> 162 GB
  - Remaining 90 days: 2.25M train-runs -> cold storage

Booking Records

Bookings: 2M/day x 2 KB x 365 = 1.46 TB/year
With 7-year retention: ~10 TB
Passenger details per booking: 2M x 2.5 passengers x 500B = 2.5 GB/day

PNR Status Cache

Active PNRs: 50M concurrent
Per PNR cache entry: 500 bytes
Total PNR cache: 50M x 500B = 25 GB

Total Storage (Year 1)

Train schedules: ~50 GB
Berth inventory (hot, 30 days): ~162 GB
Booking history: ~1.46 TB
Passenger data: ~900 GB
PNR cache: ~25 GB
Logs/Audit: ~5 TB
Total: ~7.6 TB

2.3 Bandwidth Estimates

Inbound (reads):
- Train search: 50 KB x 5M searches/day = 250 GB/day
- Availability check: 20 KB x 20M checks/day = 400 GB/day
- PNR status: 5 KB x 30M checks/day = 150 GB/day
- Total inbound: ~800 GB/day

Outbound (writes):
- Booking creation: 5 KB x 2M bookings/day = 10 GB/day
- PNR updates: 2 KB x 5M updates/day = 10 GB/day
- Total outbound: ~20 GB/day

Tatkal peak (10 AM - 10:05 AM):
- 5M concurrent users
- Search + availability: 5M x 50 KB = 250 GB in 5 minutes
- Bandwidth: 250 GB / 300 sec = 833 MB/s = 6.7 Gbps read
- Booking attempts: 100K in 5 min = 333 attempts/sec

2.4 QPS Estimates

Operation Avg QPS Peak QPS (Tatkal) Notes
Train search 200 10,000 Read-heavy, cacheable
Availability check 500 50,000 Read-heavy, partially cacheable
PNR status check 300 5,000 Read-heavy, highly cacheable
Booking creation 5 333 Write-heavy, bursty
Payment processing 3 200 External dependency
Cancellation 1 50 Low volume
Chart preparation 0.1 100 Batch before departure
TTE verification 5 200 At stations/on-board

Critical insight: Tatkal creates a 66,000x spike in booking QPS. The availability check spike is 100x. This is even more extreme than movie booking.


3. System Interface Design

3.1 API Definitions

Train Search Service

GET    /api/v1/trains/search                     # Search trains
GET    /api/v1/trains/{train_number}             # Train details
GET    /api/v1/trains/{train_number}/schedule    # Train schedule
GET    /api/v1/trains/{train_number}/running-status # Live running status
GET    /api/v1/stations                          # Station list (autocomplete)

Availability Service

GET    /api/v1/availability                      # Check availability
GET    /api/v1/availability/fare                 # Fare calculation
GET    /api/v1/pnr/{pnr_number}                  # PNR status

Booking Service

POST   /api/v1/bookings/initiate                 # Initiate booking (reserve berths)
POST   /api/v1/bookings/{booking_id}/passengers  # Add passenger details
POST   /api/v1/bookings/{booking_id}/confirm     # Confirm booking with payment
GET    /api/v1/bookings/{booking_id}             # Booking details
GET    /api/v1/users/{user_id}/bookings          # User booking history
POST   /api/v1/bookings/{booking_id}/cancel      # Cancel booking
GET    /api/v1/bookings/{booking_id}/ticket      # E-ticket with QR

Chart Service

POST   /api/v1/charts/{train_run_id}/prepare     # Prepare chart
GET    /api/v1/charts/{train_run_id}/status      # Chart status
GET    /api/v1/charts/{train_run_id}/final       # Final chart

TTE Service

POST   /api/v1/tte/verify                        # Verify ticket (QR scan)
POST   /api/v1/tte/current-booking               # Current booking
POST   /api/v1/tte/penalize                      # Penalize fare evasion

Key Request/Response Examples

Train Search:

GET /api/v1/trains/search?from=NDLS&to=HYB&date=2026-09-15&quota=GENERAL

Response 200:
{
  "trains": [
    {
      "train_number": "12624",
      "train_name": "Hazrat Nizamuddin - Hyderabad SF Express",
      "departure": "16:55",
      "arrival": "08:45+1",
      "duration": "15h 50m",
      "availability": {
        "1A": { "status": "AVAILABLE", "count": 18, "fare": 2850 },
        "2A": { "status": "AVAILABLE", "count": 42, "fare": 1720 },
        "3A": { "status": "WL-12", "count": 0, "fare": 1240 },
        "SL": { "status": "AVAILABLE", "count": 156, "fare": 455 }
      }
    }
  ]
}

Initiate Booking:

POST /api/v1/bookings/initiate
Headers:
  X-Idempotency-Key: uuid-v4
  Authorization: Bearer <jwt>

{
  "train_number": "12624",
  "train_date": "2026-09-15",
  "from_station": "NDLS",
  "to_station": "HYB",
  "quota": "TATKAL",
  "passengers": [
    {
      "name": "Rahul Sharma",
      "age": 30,
      "gender": "MALE",
      "berth_preference": "LOWER",
      "food_preference": "VEG"
    },
    {
      "name": "Priya Sharma",
      "age": 28,
      "gender": "FEMALE",
      "berth_preference": "LOWER",
      "food_preference": "VEG"
    }
  ],
  "class_preference": ["3A", "2A", "SL"]
}

Response 201:
{
  "booking_id": "BK-2026-ABC1234567",
  "pnr_number": "ABC1234567",
  "train_number": "12624",
  "allocated_class": "3A",
  "berths": [
    { "passenger_index": 0, "coach": "B3", "berth_number": 12, "berth_type": "LOWER" },
    { "passenger_index": 1, "coach": "B3", "berth_number": 13, "berth_type": "MIDDLE" }
  ],
  "status": "CNF",
  "total_fare": 2480,
  "payment_timeout": "2026-08-28T10:05:00Z",
  "payment_url": "/api/v1/payments/initiate?booking_id=BK-2026-ABC1234567"
}

4. High-Level Architecture

graph TB
    subgraph Clients["Client Layer"]
        Web[Web Portal]
        App[Mobile App]
        TTE[TTE App]
        Admin[Admin/IRCTC Portal]
        Partners[Partner APIs<br/>MakeMyTrip, etc.]
    end

    subgraph Edge["Edge Layer"]
        CDN[CDN + WAF]
        GSLB[Global Server Load Balancer]
        BotDetect[Bot Detection Layer]
    end

    subgraph APIGateway["API Gateway"]
        GW[API Gateway Cluster]
        Auth[Auth Service<br/>JWT + 2FA]
        RateLimit[Adaptive Rate Limiter]
    end

    subgraph CoreServices["Core Services"]
        subgraph SearchDomain["Search Domain"]
            TrainSearch[Train Search Service]
            StationSvc[Station Service]
            RunningStatus[Running Status Service]
        end

        subgraph BookingDomain["Booking Domain"]
            AvailSvc[Availability Service]
            FareSvc[Fare Calculation Service]
            BookingEngine[Booking Engine]
            BerthAlloc[Berth Allocation Service]
            PNRService[PNR Service]
        end

        subgraph ChartDomain["Chart Domain"]
            ChartSvc[Chart Preparation Service]
            WLProcessor[Waitlist Processor]
        end

        subgraph PaymentDomain["Payment Domain"]
            PaySvc[Payment Service]
            RefundSvc[Refund Service]
            Reconcile[Reconciliation Service]
        end

        subgraph VerificationDomain["Verification Domain"]
            TTESvc[TTE Verification Service]
            QRSvc[QR Code Service]
        end
    end

    subgraph DataLayer["Data Layer"]
        subgraph CacheTier["Cache Tier"]
            RedisAvail[Redis Cluster - Availability<br/>Berth state + locks]
            RedisPNR[Redis Cluster - PNR Cache]
            RedisSess[Redis Cluster - Sessions]
        end

        subgraph DBTier["Database Tier"]
            MySQLBooking[MySQL Cluster - Bookings<br/>Sharded by PNR]
            MySQLTrain[MySQL Cluster - Train Master<br/>Schedules, Routes]
            MySQLUser[MySQL Cluster - Users<br/>Sharded by user_id]
            TSDB[TimeSeries DB - Running Status]
        end

        subgraph MessagingTier["Messaging Tier"]
            Kafka[Kafka Cluster]
        end
    end

    subgraph External["External Systems"]
        PayGateways[Payment Gateways<br/>Bank APIs]
        SMS_GW[SMS Gateway]
        Email_GW[Email Gateway]
        CTIS[CTIS - Centralized Train<br/>Information System]
        GPS[GPS Tracking System]
    end

    Web --> CDN
    App --> CDN
    TTE --> CDN
    Admin --> CDN
    Partners --> CDN
    CDN --> GSLB
    GSLB --> BotDetect
    BotDetect --> GW
    GW --> Auth
    Auth --> RateLimit

    RateLimit --> TrainSearch
    RateLimit --> StationSvc
    RateLimit --> RunningStatus
    RateLimit --> AvailSvc
    RateLimit --> FareSvc
    RateLimit --> BookingEngine
    RateLimit --> PNRService
    RateLimit --> ChartSvc
    RateLimit --> PaySvc
    RateLimit --> TTESvc

    TrainSearch --> RedisPNR
    TrainSearch --> MySQLTrain
    RunningStatus --> TSDB
    RunningStatus --> GPS

    AvailSvc --> RedisAvail
    FareSvc --> MySQLTrain
    BookingEngine --> RedisAvail
    BookingEngine --> BerthAlloc
    BerthAlloc --> RedisAvail
    PNRService --> RedisPNR
    PNRService --> MySQLBooking

    ChartSvc --> MySQLBooking
    WLProcessor --> MySQLBooking

    PaySvc --> PayGateways
    RefundSvc --> PayGateways
    Reconcile --> PayGateways

    TTESvc --> MySQLBooking
    QRSvc --> MySQLBooking

    Kafka --> WLProcessor
    Kafka --> ChartSvc
    Kafka --> SMS_GW
    Kafka --> Email_GW

    BookingEngine --> Kafka
    PaySvc --> Kafka
    ChartSvc --> Kafka

5. Core Component Design

5.1 Berth Allocation Service (The Critical Component)

This is the most complex component — more so than movie seat allocation because of:

  1. Berth types: Lower, Middle, Upper, Side Lower, Side Upper, Side Middle, Window, Non-Window
  2. Quota management: General, Tatkal, Ladies, Senior Citizen, Defence — each with reserved berths
  3. RAC (Reservation Against Confirmation): Partial confirmation when fully booked
  4. Waiting List: Ordered queue for over-demand
  5. Berth grouping: Family/passenger grouping preferences
  6. Gender segregation: Female-only berths in certain configurations

Coach Layout Model

General Sleeper Class Coach (72 berths):

  Door                                              Door
  ┌─────────────────────────────────────────────────┐
  │  SL-1  │  SU-1  │           │  SL-2  │  SU-2   │
  │  ML-1  │  MU-1  │  AISLE   │  ML-2  │  MU-2   │
  │  UL-1  │  UU-1  │           │  UL-2  │  UU-2   │
  ├────────┼────────┤           ├────────┼─────────┤
  │  ...   │  ...   │           │  ...   │  ...    │
  ├────────┼────────┤           ├────────┼─────────┤
  │  SL-8  │  SU-8  │           │  SL-9  │  SU-9   │
  │  ML-8  │  MU-8  │  AISLE   │  ML-9  │  MU-9   │
  │  UL-8  │  UU-8  │           │  UL-9  │  UU-9   │
  └─────────────────────────────────────────────────┘

  SL = Side Lower,  SU = Side Upper
  ML = Middle,      MU = Middle Upper
  UL = Lower,       UU = Upper
  16 bays x 4 berths + 8 side berths = 72 berths

Berth Allocation Algorithm

flowchart TD
    Start[Receive Booking Request] --> CheckQuota{Check Quota<br/>Availability}
    CheckQuota -->|Available| AllocBerth[Allocate Berths]
    CheckQuota -->|Full, RAC available| AddRAC[Add to RAC]
    CheckQuota -->|Full, no RAC| AddWL[Add to Waiting List]

    AllocBerth --> ApplyPrefs{Apply<br/>Preferences?}
    ApplyPrefs -->|Yes| TryPref[Try Preferred Berth Type]
    ApplyPrefs -->|No| AnyBerth[Allocate Any Available]
    TryPref -->|Found| Confirm[Confirm Allocation]
    TryPref -->|Not Found| AnyBerth

    AddRAC --> AssignRAC[Assign RAC Number]
    AddWL --> AssignWL[Assign WL Number]

    Confirm --> LockBerth[Lock Berths in Redis]
    AssignRAC --> LockRAC[Lock RAC Slot]
    AssignWL --> LockWL[Lock WL Position]

    LockBerth --> EmitEvent[Emit BOOKING_CONFIRMED]
    LockRAC --> EmitRAC[Emit BOOKING_RAC]
    LockWL --> EmitWL[Emit BOOKING_WL]

    EmitEvent --> Return[Return PNR + Details]
    EmitRAC --> Return
    EmitWL --> Return

Allocation Priority Rules

Berth Allocation Priority (for confirmed bookings):

1. Quota-specific berths first
   - Ladies quota -> Ladies berths (even-numbered bays)
   - General quota -> Remaining berths

2. Berth type preference
   - Requested: LOWER -> Try LOWER -> Try MIDDLE -> Try UPPER
   - No preference -> Any available

3. Group allocation
   - Try to allocate adjacent berths for same booking
   - Family: Same bay preferred
   - Couples: Adjacent bays

4. Gender considerations
   - Unaccompanied females: Prefer lower berths
   - Mixed gender groups: Separate bays if possible

5.2 Availability Service

Availability States

stateDiagram-v2
    [*] --> AVAILABLE
    AVAILABLE --> RAC: All berths booked, RAC open
    RAC --> WL: RAC full
    WL --> WL_CLOSED: WL limit reached
    AVAILABLE --> BOOKED: Berth allocated
    RAC --> AVAILABLE: Cancellation frees berth
    WL --> RAC: Cancellation moves WL to RAC
    RAC --> BOOKED: Chart prepared, RAC confirmed
    BOOKED --> AVAILABLE: Post-cancellation
    WL_CLOSED --> [*]

Availability Data Structure (Redis)

Key: avail:{train_run_id}:{class}:{quota}
Type: Hash

Fields:
  confirmed_count: 45        # Berths already confirmed
  rac_count: 8               # Current RAC count
  wl_count: 23               # Current WL count
  total_berths: 72           # Total berths in this class
  rac_limit: 20              # Max RAC allowed
  wl_limit: 50               # Max WL allowed
  status: "AVAILABLE"        # AVAILABLE / RAC / WL / WL_CLOSED
  version: 1234              # For optimistic locking
  last_updated: timestamp

5.3 Fare Calculation Service

Fare Components

Total Fare = Base Fare + Reservation Charge + Superfast Charge +
             Tatkal Surcharge + Dynamic Surcharge + GST +
             E-ticket Charge + Insurance (optional)

Base Fare:
  = Rate per km × Distance
  Rates by class:
    1A: ₹0.90/km
    2A: ₹0.60/km
    3A: ₹0.45/km
    SL: ₹0.25/km

Tatkal Surcharge:
  AC Classes: 10% of base fare (min ₹100, max ₹400)
  Non-AC:     Flat ₹100 per passenger

Dynamic Surcharge:
  Applied when demand > supply by certain threshold
  Capped at 2x base fare

5.4 Chart Preparation Service

Chart Preparation Flow

sequenceDiagram
    participant Sys as System
    participant Chart as Chart Service
    participant DB as MySQL
    participant Kafka as Kafka
    participant Notify as Notification
    participant TTE as TTE App

    Note over Sys: 4 hours before departure
    Sys->>Chart: Trigger chart preparation
    Chart->>DB: Lock train-run for charting
    Chart->>DB: Process RAC -> CONFIRMED
    Note over Chart: RAC passengers get<br/>confirmed berths
    Chart->>DB: Finalize WL (no change)
    Chart->>DB: Mark chart as PREPARED
    Chart->>Kafka: Emit CHART_PREPARED
    Kafka->>Notify: Notify RAC->CNF passengers
    Kafka->>TTE: Push final chart to TTE
    Note over Sys: Chart is now final<br/>(no more changes)

5.5 Waitlist Processing Service

WL Confirmation Logic

When a confirmed booking is cancelled:
  1. Release the berth back to inventory
  2. If RAC exists:
     a. Move highest-priority RAC passenger to CONFIRMED
     b. Assign the freed berth
  3. If no RAC but WL exists:
     a. Move highest-priority WL passenger to RAC
  4. Repeat cascade until no more movements

Priority within WL/RAC:
  1. Earlier booking time (FIFO)
  2. Quota priority (Defence > Senior Citizen > Ladies > General)
  3. Longer distance preference (for certain quotas)

5.6 TTE Verification Service

On-Board Verification Flow

sequenceDiagram
    participant TTE as TTE Officer
    participant App as TTE App
    participant Svc as TTE Service
    participant DB as Booking DB
    participant Offline as Offline Cache

    TTE->>App: Scan PNR QR Code
    App->>Svc: Verify PNR
    alt Online
        Svc->>DB: Fetch booking + chart
        DB-->>Svc: Booking details
        Svc-->>App: Valid/Invalid + Details
    else Offline
        Svc->>Offline: Check local cache
        Offline-->>Svc: Cached booking
        Svc-->>App: Valid/Invalid (cached)
        Note over App: Sync when back online
    end

    App-->>TTE: Display passenger info
    TTE->>App: Mark as checked
    App->>Svc: Record check-in

6. Data Model

6.1 Relational Schema (MySQL)

-- Train Master Data
CREATE TABLE trains (
    train_number    INT PRIMARY KEY,
    train_name      VARCHAR(255) NOT NULL,
    train_type      ENUM('MAIL_EXPRESS', 'SUPERFAST', 'SHATABDI', 'RAJDHANI',
                         ' DURONTO', 'Humsafar', 'PASSENGER', 'MEMU') NOT NULL,
    running_days    SET('MON','TUE','WED','THU','FRI','SAT','SUN') NOT NULL,
    origin_station  INT NOT NULL,
    destination_station INT NOT NULL,
    total_distance  INT NOT NULL,          -- km
    duration_min    INT NOT NULL,
    is_superfast    BOOLEAN DEFAULT FALSE,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_type (train_type),
    INDEX idx_route (origin_station, destination_station)
) ENGINE=InnoDB;

-- Stations
CREATE TABLE stations (
    station_code    VARCHAR(5) PRIMARY KEY,
    station_name    VARCHAR(255) NOT NULL,
    state           VARCHAR(100),
    zone            VARCHAR(50),           -- Railway zone
    latitude        DECIMAL(10, 8),
    longitude       DECIMAL(11, 8),
    code_int        INT UNIQUE,            -- For FK references
    INDEX idx_name (station_name),
    INDEX idx_zone (zone)
) ENGINE=InnoDB;

-- Train Schedule (route with all stops)
CREATE TABLE train_schedule (
    schedule_id     BIGINT PRIMARY KEY AUTO_INCREMENT,
    train_number    INT NOT NULL,
    station_code    VARCHAR(5) NOT NULL,
    halt_sequence   INT NOT NULL,          -- Order of stop
    arrival_time    TIME,
    departure_time  TIME NOT NULL,
    distance_from_origin INT NOT NULL,     -- km
    halt_duration_min INT DEFAULT 0,
    day_count       INT DEFAULT 0,         -- Day 0, 1, 2...
    FOREIGN KEY (train_number) REFERENCES trains(train_number),
    FOREIGN KEY (station_code) REFERENCES stations(station_code),
    UNIQUE KEY uk_train_station (train_number, station_code),
    INDEX idx_sequence (train_number, halt_sequence)
) ENGINE=InnoDB;

-- Train Runs (a specific date's run of a train)
CREATE TABLE train_runs (
    train_run_id    BIGINT PRIMARY KEY AUTO_INCREMENT,
    train_number    INT NOT NULL,
    run_date        DATE NOT NULL,
    status          ENUM('SCHEDULED', 'RUNNING', 'COMPLETED',
                         'CANCELLED', 'DIVERTED') DEFAULT 'SCHEDULED',
    chart_status    ENUM('OPEN', 'PREPARING', 'PREPARED', 'FINALIZED')
                    DEFAULT 'OPEN',
    actual_departure TIMESTAMP NULL,
    actual_arrival  TIMESTAMP NULL,
    FOREIGN KEY (train_number) REFERENCES trains(train_number),
    UNIQUE KEY uk_train_date (train_number, run_date),
    INDEX idx_date (run_date, status),
    INDEX idx_chart (chart_status, run_date)
) ENGINE=InnoDB;

-- Coach Composition (which coaches run on which train)
CREATE TABLE coach_composition (
    composition_id  BIGINT PRIMARY KEY AUTO_INCREMENT,
    train_number    INT NOT NULL,
    run_date_from   DATE NOT NULL,
    run_date_to     DATE NOT NULL,
    coach_position  INT NOT NULL,          -- Position in train
    coach_number    VARCHAR(10) NOT NULL,  -- e.g., 'B3', 'H1'
    coach_type      VARCHAR(10) NOT NULL,  -- 'SL', '3A', '2A', '1A'
    total_berths    INT NOT NULL,
    ladies_berths   INT DEFAULT 0,
    FOREIGN KEY (train_number) REFERENCES trains(train_number),
    INDEX idx_train_date (train_number, run_date_from, run_date_to)
) ENGINE=InnoDB;

-- Bookings (PNR records)
CREATE TABLE bookings (
    booking_id      VARCHAR(20) PRIMARY KEY,
    pnr_number      VARCHAR(10) UNIQUE NOT NULL,
    user_id         BIGINT NOT NULL,
    train_run_id    BIGINT NOT NULL,
    train_number    INT NOT NULL,
    run_date        DATE NOT NULL,
    from_station    VARCHAR(5) NOT NULL,
    to_station      VARCHAR(5) NOT NULL,
    travel_class    VARCHAR(10) NOT NULL,
    quota           ENUM('GENERAL', 'TATKAL', 'PREMIUM', 'LADIES',
                         'SENIOR_CITIZEN', 'DEFENCE', 'FOREIGNer') NOT NULL,
    passenger_count INT NOT NULL,
    status          ENUM('CONFIRMED', 'RAC', 'WL', 'CNF_AFTER_CHART',
                         'CANCELLED', 'CHART_PREPARED', 'NO_SHOW')
                    DEFAULT 'CONFIRMED',
    wl_number       INT NULL,              -- WL position (null if not WL)
    rac_number      INT NULL,              -- RAC position (null if not RAC)
    total_fare      DECIMAL(10, 2) NOT NULL,
    base_fare       DECIMAL(10, 2) NOT NULL,
    reservation_charge DECIMAL(10, 2) NOT NULL,
    surcharge       DECIMAL(10, 2) DEFAULT 0,
    gst             DECIMAL(10, 2) NOT NULL,
    payment_status  ENUM('PENDING', 'PAID', 'FAILED', 'REFUNDED')
                    DEFAULT 'PENDING',
    payment_id      VARCHAR(64) NULL,
    chart_status    ENUM('OPEN', 'PREPARED', 'FINALIZED') DEFAULT 'OPEN',
    cancellation_reason VARCHAR(255) NULL,
    refund_amount   DECIMAL(10, 2) NULL,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    payment_timeout TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (train_run_id) REFERENCES train_runs(train_run_id),
    INDEX idx_pnr (pnr_number),
    INDEX idx_user (user_id, status),
    INDEX idx_train_run (train_run_id, status),
    INDEX idx_payment (payment_status, payment_timeout),
    INDEX idx_chart (chart_status, train_run_id)
) ENGINE=InnoDB;

-- Passengers
CREATE TABLE passengers (
    passenger_id    BIGINT PRIMARY KEY AUTO_INCREMENT,
    booking_id      VARCHAR(20) NOT NULL,
    pnr_number      VARCHAR(10) NOT NULL,
    passenger_seq   INT NOT NULL,          -- Order in booking
    name            VARCHAR(255) NOT NULL,
    age             INT NOT NULL,
    gender          ENUM('MALE', 'FEMALE', 'TRANSGENDER') NOT NULL,
    id_type         VARCHAR(50) NULL,         -- Aadhaar, Passport, etc.
    id_number       VARCHAR(50) NULL,
    berth_allocated VARCHAR(20) NULL,     -- e.g., 'B3/12/L'
    coach_allocated VARCHAR(10) NULL,
    berth_type      VARCHAR(20) NULL,     -- LOWER, MIDDLE, UPPER, etc.
    berth_preference VARCHAR(20) NULL,    -- Requested preference
    food_preference ENUM('VEG', 'NON_VEG', 'NO_PREF') DEFAULT 'NO_PREF',
    status          ENUM('CONFIRMED', 'RAC', 'WL', 'CANCELLED')
                    DEFAULT 'CONFIRMED',
    check_in_status BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (booking_id) REFERENCES bookings(booking_id),
    INDEX idx_pnr (pnr_number),
    INDEX idx_booking (booking_id)
) ENGINE=InnoDB;

-- Berth Inventory (per train-run, per coach)
CREATE TABLE berth_inventory (
    inventory_id    BIGINT PRIMARY KEY AUTO_INCREMENT,
    train_run_id    BIGINT NOT NULL,
    coach_number    VARCHAR(10) NOT NULL,
    berth_number    INT NOT NULL,         -- 1-72
    berth_type      VARCHAR(20) NOT NULL, -- LOWER, MIDDLE, UPPER, etc.
    quota           ENUM('GENERAL', 'LADIES', 'TATKAL') NOT NULL,
    status          ENUM('AVAILABLE', 'ALLOCATED', 'BLOCKED', 'RAC_ALLOCATED')
                    DEFAULT 'AVAILABLE',
    booking_id      VARCHAR(20) NULL,
    passenger_id    BIGINT NULL,
    version         BIGINT DEFAULT 1,     -- Optimistic lock version
    updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (train_run_id) REFERENCES train_runs(train_run_id),
    FOREIGN KEY (booking_id) REFERENCES bookings(booking_id),
    UNIQUE KEY uk_berth (train_run_id, coach_number, berth_number, quota),
    INDEX idx_status (train_run_id, status),
    INDEX idx_booking (booking_id)
) ENGINE=InnoDB;

-- Users
CREATE TABLE users (
    user_id         BIGINT PRIMARY KEY AUTO_INCREMENT,
    username        VARCHAR(100) UNIQUE NOT NULL,
    email           VARCHAR(255) UNIQUE NOT NULL,
    phone           VARCHAR(15) UNIQUE NOT NULL,
    password_hash   VARCHAR(255) NOT NULL,
    full_name       VARCHAR(255) NOT NULL,
    aadhaar_number  VARCHAR(14) NULL,     -- Encrypted
    status          ENUM('ACTIVE', 'SUSPENDED', 'DELETED') DEFAULT 'ACTIVE',
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_phone (phone),
    INDEX idx_email (email)
) ENGINE=InnoDB;

-- Audit Log
CREATE TABLE booking_audit_log (
    log_id          BIGINT PRIMARY KEY AUTO_INCREMENT,
    pnr_number      VARCHAR(10) NOT NULL,
    booking_id      VARCHAR(20) NOT NULL,
    action          VARCHAR(50) NOT NULL,
    old_status      VARCHAR(20),
    new_status      VARCHAR(20),
    details         JSON,
    user_id         BIGINT,
    ip_address      VARCHAR(45),
    user_agent      VARCHAR(500),
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_pnr (pnr_number),
    INDEX idx_booking (booking_id),
    INDEX idx_time (created_at),
    INDEX idx_action (action, created_at)
) ENGINE=InnoDB;

6.2 Redis Data Structures

# Berth availability per train-run/class/quota
Key:   avail:{train_run_id}:{class}:{quota}
Type:  confirmed_count: 45
  rac_count: 8
  wl_count: 23
  total_berths: 72
  rac_limit: 20
  wl_limit: 50
  status: "AVAILABLE"
  version: 1234

# Individual berth lock
Key:   lock:berth:{train_run_id}:{coach}:{berth}:{quota}
Value: {booking_id}
TTL:   300 seconds (5 min for payment)

# PNR cache
Key:   pnr:{pnr_number}
Value: JSON {
    "pnr_number": "ABC1234567",
    "train_number": 12624,
    "from": "NDLS",
    "to": "HYB",
    "date": "2026-09-15",
    "class": "3A",
    "status": "CNF",
    "passengers": [
        {"name": "Rahul S.", "berth": "B3/12/L", "status": "CNF"},
        {"name": "Priya S.", "berth": "B3/13/M", "status": "CNF"}
    ]
}
TTL:   60 seconds (refreshed on any change)

# Tatkal rate limiting
Key:   tatkal:limit:{date}:{train_number}
Value: current_count
TTL:   Until next day

# User session
Key:   session:{user_id}
Value: JSON { booking_state, timeout, ... }
TTL:   300 seconds

# Chart preparation lock
Key:   lock:chart:{train_run_id}
Value: "PREPARING"
TTL:   3600 seconds

6.3 Kafka Topics

Topic: booking.events
  Partition key: train_run_id
  Events: BOOKING_INITIATED, BOOKING_CONFIRMED, BOOKING_RAC, BOOKING_WL,
          BOOKING_CANCELLED, BOOKING_NO_SHOW

Topic: berth.events
  Partition key: train_run_id
  Events: BERTH_ALLOCATED, BERTH_RELEASED, BERTH_RAC_ALLOCATED

Topic: chart.events
  Partition key: train_run_id
  Events: CHART_PREPARING, CHART_PREPARED, CHART_FINALIZED,
          WL_UPGRADED, RAC_CONFIRMED

Topic: payment.events
  Partition key: booking_id
  Events: PAYMENT_INITIATED, PAYMENT_SUCCESS, PAYMENT_FAILED,
          PAYMENT_TIMEOUT, REFUND_INITIATED, REFUND_COMPLETED

Topic: notification.events
  Partition key: user_id
  Events: SMS_PNR_STATUS, SMS_CONFIRMATION, SMS_CANCELLATION,
          EMAIL_TICKET, PUSH_WL_UPGRADE

Topic: running_status.events
  Partition key: train_number
  Events: TRAIN_DEPARTED, TRAIN_ARRIVED, TRAIN_DELAYED,
          TRAIN_DIVERTED, GPS_UPDATE

7. Key Design Decisions

7.1 Decision Matrix

Decision Option A Option B Chosen Rationale
Berth state storage DB-only Redis + DB sync Redis + DB sync Tatkal needs sub-100ms availability checks; DB too slow at 50K QPS
Booking atomicity Single DB transaction Two-phase (Redis lock + DB commit) Two-phase Single TX would hold DB locks too long; two-phase reduces lock duration
WL/RAC management Real-time cascade Batch cascade Hybrid Real-time for single cancellation; batch for mass cancellations
Tatkal fairness First-come-first-served Lottery/random selection FCFS with anti-bot FCFS is expected; fairness enforced via bot detection and rate limiting
Chart preparation Real-time Scheduled batch Scheduled batch Chart is a bulk operation; 4 hours before departure is sufficient
PNR status delivery DB query Cache-first Cache-first (Redis) PNR checks are 10x more frequent than bookings; cache is essential
Sharding strategy By train_number By user_id Hybrid Bookings by user_id; berth inventory by train_run_id
Payment timeout Fixed 10 min Adaptive Adaptive 5 min normal, 15 min Tatkal (higher pressure), extendable once

7.2 Trade-off Analysis

Consistency Model

Berth Inventory: Strong Consistency (CP)
  - Cannot overbook a physical berth
  - Redis acts as the authoritative source during active booking
  - Async replication to MySQL for durability

PNR Status: Eventual Consistency (AP acceptable)
  - Stale PNR status for a few seconds is acceptable
  - Cache with short TTL + invalidation on state change

Train Schedule: Strong Consistency
  - Schedule changes are rare but must be consistent
  - MySQL is source of truth; cache with long TTL

Booking Flow: Synchronous vs. Asynchronous

Synchronous (user-facing, must be fast):
  Search -> Availability -> Berth Allocation -> Payment Initiation
  Total latency budget: < 5 seconds

Asynchronous (backend, can be delayed):
  Payment Confirmation -> PNR Update -> Notification -> Chart Update
  Via Kafka event streaming

Critical path optimization:
  - Berth allocation happens BEFORE payment (reserve berths)
  - If payment fails, berths are released (not confirmed)
  - This means berth allocation must be fast and reliable

8. Concurrency Control

8.1 The Core Challenge

Tatkal Scenario:
  - 100 berths in 3A class
  - 500,000 users try to book at 10:00:00 AM
  - Without control: 500,000 allocations for 100 berths = 5000x overbooking

  Additional complexity:
  - WL/RAC cascade on every cancellation
  - Multiple quotas sharing the same physical berths
  - Chart preparation locks the entire train-run

8.2 Multi-Layer Concurrency Strategy

graph TB
    subgraph L1["Layer 1: Bot Detection & Rate Limiting"]
        Bot[Bot Detection<br/>Behavioral Analysis]
        RL[Adaptive Rate Limiter<br/>Per-user + Global]
    end

    subgraph L2["Layer 2: Quota-Level Atomic Counter"]
        QuotaCounter[Redis DECR for quota<br/>availability count]
    end

    subgraph L3["Layer 3: Berth-Level Distributed Lock"]
        BerthLock[Redis SETNX per berth<br/>coach + berth + quota]
    end

    subgraph L4["Layer 4: DB Optimistic Locking"]
        OLock[UPDATE berth_inventory<br/>SET version=version+1<br/>WHERE version=expected]
    end

    subgraph L5["Layer 5: Idempotency & Audit"]
        Idem[Idempotency Key]
        Audit[Audit Log Entry]
    end

    Request[Booking Request] --> Bot --> RL --> QuotaCounter --> BerthLock --> OLock --> Idem --> Audit --> Success[PNR Generated]
    Bot -->|Bot detected| Block1[403 Forbidden]
    RL -->|Rate exceeded| Block2[429 Too Many Requests]
    QuotaCounter -->|Quota full| Block3[409 Quota Full]
    BerthLock -->|Berth locked| Block4[409 Berth Unavailable]
    OLock -->|Version conflict| Retry[Retry with fresh state]
    Idem -->|Duplicate| Existing[Return existing PNR]

8.3 Quota-Level Atomic Counter (Fast Rejection)

class QuotaAvailabilityManager:
    """
    First line of defense: atomic counter at quota level.
    Rejects 99% of requests before expensive berth allocation.
    """

    def check_and_decrement(self, train_run_id: int, travel_class: str,
                            quota: str, count: int = 1) -> bool:
        """
        Atomically check if quota has availability and decrement.
        Uses Redis Lua script for atomicity.
        """
        key = f"avail:{train_run_id}:{travel_class}:{quota}"

        lua_script = """
        local key = KEYS[1]
        local requested = tonumber(ARGV[1])

        local data = redis.call('HMGET', key,
            'confirmed_count', 'rac_count', 'wl_count',
            'total_berths', 'rac_limit', 'wl_limit', 'status', 'version')

        if not data[1] then
            return {0, 'NOT_FOUND'}
        end

        local confirmed = tonumber(data[1])
        local rac = tonumber(data[2])
        local wl = tonumber(data[3])
        local total = tonumber(data[4])
        local rac_limit = tonumber(data[5])
        local wl_limit = tonumber(data[6])
        local status = data[7]
        local version = tonumber(data[8])

        if status == 'WL_CLOSED' then
            return {0, 'WL_CLOSED'}
        end

        local available_berths = total - confirmed

        if available_berths >= requested then
            -- Confirmed allocation
            redis.call('HINCRBY', key, 'confirmed_count', requested)
            redis.call('HSET', key, 'version', version + 1)
            if available_berths - requested == 0 then
                if rac < rac_limit then
                    redis.call('HSET', key, 'status', 'RAC')
                else
                    redis.call('HSET', key, 'status', 'WL')
                end
            end
            return {1, 'CONFIRMED'}
        elseif rac < rac_limit then
            -- RAC allocation
            redis.call('HINCRBY', key, 'rac_count', requested)
            redis.call('HSET', key, 'version', version + 1)
            redis.call('HSET', key, 'status', 'RAC')
            return {1, 'RAC'}
        elseif wl < wl_limit then
            -- WL allocation
            redis.call('HINCRBY', key, 'wl_count', requested)
            redis.call('HSET', key, 'version', version + 1)
            redis.call('HSET', key, 'status', 'WL')
            return {1, 'WL'}
        else
            return {0, 'FULL'}
        end
        """

        result = self.redis.eval(lua_script, 1, key, count)
        return result[0] == 1, result[1]

8.4 Berth-Level Lock Implementation

class BerthLockManager:
    """
    Second line of defense: per-berth distributed lock.
    Ensures no two bookings can allocate the same physical berth.
    """

    def try_lock_berths(self, train_run_id: int, coach: str,
                        berth_numbers: list, quota: str,
                        booking_id: str) -> tuple[bool, list]:
        """
        Atomically lock specific berths.
        Returns (success, locked_berth_details).
        """
        pipe = self.redis.pipeline(True)
        lock_keys = []

        for berth_num in berth_numbers:
            lock_key = f"lock:berth:{train_run_id}:{coach}:{berth_num}:{quota}"
            lock_keys.append(lock_key)
            lock_value = f"{booking_id}:{int(time.time())}"
            pipe.set(lock_key, lock_value, nx=True, ex=300)

        results = pipe.exec()

        locked = []
        for i, result in enumerate(results):
            if result:
                locked.append(berth_numbers[i])
            else:
                # Release all acquired locks
                self._release_berth_locks(lock_keys[:i], booking_id)
                return False, []

        return True, locked

    def release_berth_locks(self, train_run_id: int, coach: str,
                            berth_numbers: list, quota: str,
                            booking_id: str):
        """Release berth locks with ownership check."""
        for berth_num in berth_numbers:
            lock_key = f"lock:berth:{train_run_id}:{coach}:{berth_num}:{quota}"
            # Lua script for atomic check-and-delete
            script = """
            if redis.call("GET", KEYS[1]) and
               string.find(redis.call("GET", KEYS[1]), ARGV[1]) then
                return redis.call("DEL", KEYS[1])
            end
            return 0
            """
            self.redis.eval(script, 1, lock_key, booking_id)

8.5 Complete Booking Flow with Concurrency Control

class BookingEngine:
    def create_booking(self, request: BookingRequest,
                       idempotency_key: str) -> BookingResult:
        """
        End-to-end booking with full concurrency control.
        """
        # Step 1: Idempotency check
        existing = self.idempotency_repo.check(idempotency_key)
        if existing:
            return existing

        # Step 2: Quota-level atomic check (fast rejection)
        has_availability, allocation_type = self.quota_manager.check_and_decrement(
            request.train_run_id, request.travel_class, request.quota,
            request.passenger_count
        )

        if not has_availability:
            raise QuotaFullError(f"Quota {request.quota} is {allocation_type}")

        # Step 3: Berth allocation with locking
        berth_plan = self.berth_allocator.allocate(
            train_run_id=request.train_run_id,
            coach_composition=self.get_coach_composition(request.train_run_id),
            passenger_count=request.passenger_count,
            preferences=request.berth_preferences,
            quota=request.quota
        )

        # Step 4: Lock specific berths
        success, locked_berths = self.berth_lock.try_lock_berths(
            request.train_run_id,
            berth_plan.coach,
            berth_plan.berth_numbers,
            request.quota,
            booking_id=generate_booking_id()
        )

        if not success:
            # Rollback quota counter
            self.quota_manager.increment(
                request.train_run_id, request.travel_class, request.quota,
                request.passenger_count
            )
            raise BerthUnavailableError("Berths became unavailable")

        # Step 5: Create booking record (DB transaction)
        with self.db.transaction() as tx:
            booking = self.booking_repo.create(
                user_id=request.user_id,
                train_run_id=request.train_run_id,
                status=allocation_type,  # CONFIRMED, RAC, or WL
                total_fare=request.total_fare,
                payment_timeout=datetime.utcnow() + timedelta(minutes=5)
            )

            # Allocate berths in DB
            for i, berth in enumerate(locked_berths):
                self.berth_repo.allocate(
                    train_run_id=request.train_run_id,
                    coach=berth_plan.coach,
                    berth_number=berth,
                    booking_id=booking.booking_id,
                    passenger_seq=i + 1
                )

            # Save passengers
            for i, pax in enumerate(request.passengers):
                self.passenger_repo.create(
                    booking_id=booking.booking_id,
                    passenger_seq=i + 1,
                    **pax.dict()
                )

        # Step 6: Record idempotency
        self.idempotency_repo.record(idempotency_key, booking.booking_id)

        # Step 7: Emit events
        self.event_bus.publish(BookingCreatedEvent(booking))

        return BookingResult(
            booking_id=booking.booking_id,
            pnr_number=booking.pnr_number,
            status=allocation_type,
            berths=locked_berths,
            payment_timeout=booking.payment_timeout
        )

8.6 The Overbooking Prevention Invariant

INVARIANT: For any physical berth B in train-run TR:
  count(bookings where train_run_id = TR and berth = B and status in
       ('CONFIRMED', 'RAC', 'PENDING_PAYMENT'))
  <= 1

Enforcement layers:
  1. Redis quota counter (prevents exceeding total capacity)
  2. Redis berth lock (prevents double-allocation of specific berth)
  3. DB unique constraint (train_run_id, coach, berth_number, quota)
  4. Application validation (final check before PNR generation)
  5. Reconciliation job (detects and fixes any violations)

9. Scalability Strategy

9.1 Horizontal Scaling Architecture

graph TB
    subgraph Region1["Region 1 (Primary)"]
        GW1[API Gateway x 20]
        Svc1[Services x 100]
        Redis1[Redis Cluster<br/>10 shards + replicas]
        MySQL1[MySQL Primary + 20 replicas]
    end

    subgraph Region2["Region 2 (Read/DR)"]
        GW2[API Gateway x 10]
        Svc2[Services x 50]
        Redis2[Redis Cluster<br/>Read replicas]
        MySQL2[MySQL Read replicas]
    end

    subgraph Shared["Shared Services"]
        Kafka[Kafka Cluster<br/>Cross-region replication]
        CDN[Global CDN]
    end

    Users[Users] --> CDN
    CDN --> GW1
    CDN --> GW2
    GW1 --> Svc1
    GW2 --> Svc2
    Svc1 --> Redis1
    Svc1 --> MySQL1
    Svc2 --> Redis2
    Svc2 --> MySQL2
    Redis1 <-->|Async Replication| Redis2
    MySQL1 <-->|Async Replication| MySQL2

9.2 Scaling Dimensions

Component Scaling Strategy Trigger Max Scale
API Gateway Add instances CPU > 60% 100+ instances
Booking Engine 1 per Redis shard Memory > 70% # of shards
Redis Cluster Add shards (consistent hashing) Memory > 70% or ops > 100K/sec 500+ shards
MySQL Read replicas + sharding Connections > 70% 100+ replicas
Kafka Add partitions + brokers Consumer lag > 5K Unlimited
Notification Add workers Queue depth > 50K Unlimited

9.3 Caching Strategy

Cache Hierarchy:
┌─────────────────────────────────────────────┐
│ L1: In-process cache                         │  TTL: 2s
│     Train schedule, station data             │
├─────────────────────────────────────────────┤
│ L2: Redis - PNR Cache                        │  TTL: 60s
│     PNR status, booking details              │
├─────────────────────────────────────────────┤
│ L3: Redis - Availability Cache               │  TTL: 5s (normal)
│     Berth counts, quota status               │  TTL: 1s (Tatkal)
├─────────────────────────────────────────────┤
│ L4: Redis - Seat Map Cache                   │  TTL: 30s
│     Full coach layout with berth states      │
├─────────────────────────────────────────────┤
│ L5: CDN                                      │  TTL: 1 hour
│     Static assets, train images              │
├─────────────────────────────────────────────┤
│ L6: MySQL (source of truth)                  │  No TTL
└─────────────────────────────────────────────┘

Cache Invalidation Rules:
- Booking created/modified -> Invalidate PNR cache + availability cache
- Berth allocated -> Invalidate availability cache for that train-run/class
- Chart prepared -> Invalidate all caches for that train-run
- Cancellation -> Invalidate PNR + availability + cascade WL/RAC

9.4 Database Sharding Strategy

Bookings Table:
  Sharding key: user_id (hash mod N)
  Reason: "My bookings" queries are common; all user data co-located

Berth Inventory:
  Sharding key: train_run_id (hash mod N)
  Reason: All berths for a train-run are co-located;
          booking operations are single-shard

Train Master Data:
  No sharding needed (12K trains, small dataset)
  Replicated to all nodes

Users Table:
  Sharding key: user_id (same as bookings shard)
  Reason: Join bookings with user data without cross-shard

9.5 Tatkal-Specific Scaling

Pre-Tatkal Preparation (9:30 AM - 10:00 AM):
  1. Pre-warm Redis with all Tatkal train availability
  2. Scale up booking engine instances (auto-scaling policy)
  3. Pre-allocate connection pools
  4. Enable enhanced rate limiting
  5. Activate bot detection rules

During Tatkal (10:00 AM - 10:30 AM):
  1. All traffic routed to Tatkal-optimized instances
  2. Availability cache TTL reduced to 1 second
  3. Quota counter checked before any DB interaction
  4. Non-critical services deprioritized

Post-Tatkal (10:30 AM+):
  1. Gradual scale-down
  2. Reconciliation of all Tatkal bookings
  3. Cache TTL restored to normal

10. Curveball Scenarios

Curveball 1: “What if 5 million users hit Tatkal at exactly 10:00 AM?”

Scenario: Tatkal opens at 10:00 AM sharp. 5 million users have been waiting on the page and click “Book” simultaneously.

Impact Analysis:

5M users / 1 second = 5,000,000 QPS on booking endpoint
Even with 10-second spread: 500,000 QPS
Redis cluster capacity: ~500K ops/sec (large cluster)
DB capacity: ~10K writes/sec (clustered)

Without mitigation: Complete system collapse within seconds

Multi-Layer Mitigation:

Layer Strategy Capacity After
Pre-Tatkal Queue Virtual waiting room opens at 9:45 AM Distributes 5M over 15 min = 5,555/sec entry
Bot Filtering Block known bots, headless browsers, suspicious patterns Removes ~30% = 3.5M legitimate users
Rate Limiting 3 Tatkal attempts per user per train 3.5M / 3 = effective 1.17M attempts
Quota Counter Atomic Redis DECR rejects instantly if full Only users within quota proceed
Staggered Release Different trains open at slightly different times Spreads load across 10-minute window
Circuit Breaker If error rate > 50%, pause and queue Prevents cascading failure

Virtual Waiting Room for Tatkal:

sequenceDiagram
    participant U as User
    participant VR as Virtual Waiting Room
    participant Token as Token Service
    participant BE as Booking Engine

    Note over U: 9:45 AM - Queue opens
    U->>VR: Join Tatkal queue
    VR->>Token: Get queue position
    Token-->>VR: Position #234,567
    VR-->>U: "Position #234,567. ETA: ~8 minutes"

    Note over VR: Processes 500 users/sec into booking phase

    Note over U: 9:53 AM - User's turn
    VR->>Token: Issue booking token (valid 3 min)
    Token-->>VR: Token issued
    VR-->>U: "You can now book! Token valid for 3 minutes"

    U->>BE: Book with token
    BE->>BE: Process booking normally

Expected Outcome:

  • 5M users enter queue over 15 minutes
  • 500 users/sec get booking access
  • Each user gets 3 minutes to complete booking
  • System handles sustainable 500 booking QPS instead of 5M burst
  • Fairness: Queue position is random (not first-click-wins) to prevent bot advantage

Curveball 2: “What if a train is cancelled 2 hours before departure?”

Scenario: Due to flooding, 50 trains are cancelled. Each train has ~800 passengers. Total: 40,000 bookings need immediate cancellation and refund.

Design:

graph TB
    subgraph MassCancellation["Mass Cancellation System"]
        Trigger[Cancellation Trigger<br/>Admin/API]
        Validator[Validation Engine<br/>Confirm cancellation]
        BatchCancel[Batch Cancellation Processor]
        RefundBatch[Batch Refund Processor]
        NotifyBatch[Batch Notification Processor]
        AltBooking[Alternative Booking Suggester]
    end

    Trigger --> Validator
    Validator -->|Confirmed| BatchCancel
    BatchCancel --> RefundBatch
    BatchCancel --> NotifyBatch
    RefundBatch --> AltBooking

Processing Steps:

Step 1: Lock affected train-runs (prevent new bookings)
  - SET chart_status = 'CANCELLED' for each train-run
  - Block all booking attempts for these runs

Step 2: Batch cancellation (1000 bookings/batch)
  - UPDATE bookings SET status = 'CANCELLED'
    WHERE train_run_id IN (...) AND status IN ('CONFIRMED', 'RAC', 'WL')
  - Release all berth locks in Redis
  - Update availability counters

Step 3: Batch refund processing
  - Calculate refund per booking (based on time-to-departure)
  - < 4 hours: 100% refund (train cancellation)
  - Process via payment gateway in batches
  - Track refund status per booking

Step 4: Mass notification
  - SMS to all affected passengers
  - Email with refund details
  - Push notification (if app installed)

Step 5: Alternative booking suggestions
  - Suggest alternative trains on same route
  - Offer priority booking on alternatives
  - Auto-rebook option (with user consent)

Refund Calculation:

Train Cancellation Refund Rules:
┌──────────────────────────┬────────────┬──────────────────┐
│ Condition                │ Refund %   │ Processing Time  │
├──────────────────────────┼────────────┼──────────────────┤
│ Train cancelled          │ 100%       │ 7-10 business days│
│ Train delayed > 3 hours  │ 100%       │ 7-10 business days│
│ Passenger cancels > 4hr  │ 100% - ₹60 │ 7-10 business days│
│ Passenger cancels 3-4hr  │ 50% - ₹60  │ 7-10 business days│
│ Passenger cancels 1-3hr  │ 25% - ₹60  │ 7-10 business days│
│ Passenger cancels < 1hr  │ 0%         │ No refund        │
└──────────────────────────┴────────────┴──────────────────┘

Curveball 3: “What if the WL needs to cascade for 10,000 passengers?”

Scenario: A popular train has 500 confirmed, 50 RAC, and 5,000 WL passengers. A group booking of 500 seats is cancelled. The WL needs to cascade: 500 WL -> RAC, 50 RAC -> CONFIRMED, and assign berths.

Design:

sequenceDiagram
    participant Cancel as Cancellation
    participant Cascade as WL Cascade Engine
    participant Redis as Redis (Availability)
    participant DB as MySQL (Bookings)
    participant Berth as Berth Allocator
    participant Notify as Notification

    Cancel->>Cascade: 500 berths freed
    Cascade->>Redis: Lock train-run for cascade
    Cascade->>DB: Fetch top 500 WL passengers (ordered by priority)

    loop For each WL passenger (batch of 100)
        Cascade->>DB: Move WL -> RAC (or CONFIRMED if berth available)
        Cascade->>Berth: Allocate berth if CONFIRMED
        Berth->>Redis: Lock specific berth
        Berth->>DB: Update berth allocation
        Cascade->>Redis: Update availability counter
    end

    Cascade->>Redis: Release train-run lock
    Cascade->>Notify: Notify all upgraded passengers
    Notify->>Notify: SMS + Email + Push

Cascade Algorithm:

class WaitlistCascadeEngine:
    def process_cascade(self, train_run_id: int, freed_berths: list):
        """
        Process WL/RAC cascade when berths are freed.
        Must be atomic and handle partial failures.
        """
        # Step 1: Lock the train-run for cascade
        self.lock_train_run(train_run_id)

        try:
            # Step 2: Get current availability state
            avail = self.get_availability(train_run_id)

            # Step 3: Process RAC -> CONFIRMED first
            rac_passengers = self.get_rac_passengers(train_run_id,
                                                     limit=len(freed_berths))
            for pax in rac_passengers:
                if freed_berths:
                    berth = freed_berths.pop(0)
                    self.confirm_rac_passenger(pax, berth)

            # Step 4: Process WL -> RAC
            remaining_rac_slots = avail.rac_limit - avail.rac_count
            if remaining_rac_slots > 0:
                wl_passengers = self.get_wl_passengers(
                    train_run_id, limit=remaining_rac_slots
                )
                for pax in wl_passengers:
                    self.move_wl_to_rac(pax)

            # Step 5: Update availability counters
            self.update_availability(train_run_id)

        finally:
            # Step 6: Always release lock
            self.unlock_train_run(train_run_id)

        # Step 7: Notify affected passengers (async)
        self.notify_cascade_results(train_run_id)

Key Considerations:

  • Atomicity: Entire cascade is one transaction (all or nothing)
  • Priority: FIFO within same quota; quota priority across quotas
  • Performance: Batch processing (100 passengers per batch)
  • Notification: Async notification after cascade completes
  • Audit: Every movement logged for dispute resolution

Curveball 4: “What if we need to support chart preparation for 10,000 trains simultaneously?”

Scenario: At 4 AM daily, chart preparation runs for all trains departing in the next 4-8 hours. ~10,000 trains need chart preparation within a 2-hour window.

Design:

Chart Preparation Pipeline:

Phase 1 (4:00 AM): Lock and prepare
  - Lock all train-runs departing 8:00-12:00 AM
  - No more bookings allowed for these trains

Phase 2 (4:00-4:30 AM): RAC -> CONFIRMED processing
  - Process in parallel (1000 trains per worker)
  - Each worker handles one batch of trains
  - Allocate berths to RAC passengers

Phase 3 (4:30-5:00 AM): Final validation
  - Cross-check berth allocations
  - Verify no conflicts
  - Generate final chart data

Phase 4 (5:00 AM): Publish and notify
  - Push charts to TTE apps
  - Notify passengers of status changes
  - Release locks

Parallel Chart Preparation:

class ChartPreparationOrchestrator:
    def prepare_charts(self, departure_window: timedelta):
        """Prepare charts for all trains in the departure window."""
        cutoff_time = datetime.utcnow() + departure_window

        # Get all train-runs needing chart preparation
        train_runs = self.train_run_repo.find_needing_chart(cutoff_time)

        # Split into batches for parallel processing
        batches = chunk(train_runs, batch_size=1000)

        # Process batches in parallel
        with ThreadPoolExecutor(max_workers=10) as executor:
            futures = []
            for batch in batches:
                future = executor.submit(self._prepare_batch, batch)
                futures.append(future)

            # Wait for all to complete
            for future in as_completed(futures):
                result = future.result()
                self.log_batch_result(result)

        # Final validation
        self.validate_all_charts(train_runs)

        # Publish results
        self.publish_charts(train_runs)

Curveball 5: “What if we need to detect and refund fraud?”

Scenario: A fraud ring is using stolen credit cards to book thousands of tickets and then cancelling them (earning cancellation incentives or reselling).

Detection System:

graph TB
    subgraph FraudDetection["Fraud Detection System"]
        RealTime[Real-Time Scorer<br/>Per booking]
        Batch[Batch Analyzer<br/>Daily patterns]
        ML[ML Model<br/>Anomaly detection]
        Rules[Rule Engine<br/>Hard rules]
        Action[Action Engine]
    end

    subgraph Signals["Input Signals"]
        UserBehavior[User Behavior<br/>Browse pattern, speed]
        DeviceInfo[Device Info<br/>Fingerprint, location]
        PaymentInfo[Payment Info<br/>Card, bank, history]
        BookingPattern[Booking Pattern<br/>Frequency, amount]
        NetworkInfo[Network Info<br/>IP, proxy detection]
    end

    UserBehavior --> RealTime
    DeviceInfo --> RealTime
    PaymentInfo --> RealTime
    BookingPattern --> RealTime
    NetworkInfo --> RealTime

    UserBehavior --> Batch
    DeviceInfo --> Batch
    PaymentInfo --> Batch
    BookingPattern --> Batch
    NetworkInfo --> Batch

    RealTime --> Rules
    RealTime --> ML
    Batch --> ML
    Rules --> Action
    ML --> Action
    Action -->|Low risk| Allow[Allow booking]
    Action -->|Medium risk| Review[Flag for review]
    Action -->|High risk| Block[Block + alert]

Fraud Signals and Actions:

Signal Threshold Action
> 5 bookings/hour from same device Hard limit Block device
> 3 failed payments Pattern Require 2FA
New account + high-value booking Risk score > 0.8 Manual review
IP from known proxy/VPN Detected Require verification
Booking pattern matches known fraud ML score > 0.9 Block + alert
Same card, different accounts Detected Link accounts, block all
Cancellation rate > 50% Per user Flag account

Curveball 6: “What if we need to support current booking (boarding at intermediate stations)?”

Scenario: A passenger boards at an intermediate station without a ticket and wants to book a “current ticket” from the TTE on board.

Design:

Current Booking Flow:
  1. TTE scans passenger's ID
  2. TTE app queries: Is there availability from current station to destination?
  3. System checks real-time availability (berths freed by cancellations)
  4. If available: Allocate berth, collect payment, issue e-ticket
  5. If not available: Issue current ticket (no berth, standing/sitting)

Special considerations:
  - TTE app must work offline (tunnel areas, remote stations)
  - Offline bookings sync when connectivity restored
  - Fare calculation uses actual distance traveled
  - Penalty fare for travel without ticket (if caught before booking)

11. Failure Modes and Mitigations

11.1 Failure Mode Analysis

Failure Mode Probability Impact Detection Mitigation
Redis cluster failure Medium Critical Health check timeout Redis Cluster with 3 replicas; automatic failover in < 30s
MySQL primary failure Low Critical Connection pool exhaustion MHA automatic failover; read replicas promote in < 60s
Payment gateway timeout Medium High HTTP timeout / 5xx Circuit breaker; multiple gateways; queue + retry with extended hold
Berth overbooking Very Low Critical Reconciliation job (every 5 min) 5-layer concurrency control; unique DB constraints; alert on any violation
Reservation leak Medium Medium Orphan lock detector (every 1 min) Redis TTL auto-expiry; background cleanup; payment timeout enforcement
Kafka broker failure Low Medium Consumer lag spike Replication factor 3; automatic leader election; local buffer
Tatkal DDoS Medium Critical Traffic anomaly detection WAF; rate limiting; bot detection; CDN DDoS protection
Clock skew Low Medium NTP drift > 100ms NTP sync on all servers; use server time only; TTL buffer
Chart preparation failure Low High Chart status timeout Retry with exponential backoff; manual override capability
Data corruption Very Low Critical Checksum mismatch Regular backups; point-in-time recovery; dual-write verification

11.2 Disaster Recovery

RPO (Recovery Point Objective): 30 seconds
  - MySQL: Semi-sync replication
  - Redis: AOF persistence (everysec) + RDB snapshots
  - Kafka: Replication factor 3, acks=all

RTO (Recovery Time Objective): 10 minutes
  - Automated failover for Redis and MySQL
  - DR site in different geographic region
  - Runbook for manual intervention
  - Quarterly DR drills

Backup Strategy:
  - MySQL: Full backup daily, binary log continuous
  - Redis: RDB snapshot every hour, AOF continuous
  - Kafka: Retention 7 days, cross-region replication
  - Offsite backup: Daily to cloud storage (encrypted)

11.3 Monitoring and Alerting

Critical Metrics:
├── Berth Overbooking Count (PAGE ALERT if > 0)
├── Booking Success Rate (ALERT if < 90%)
├── Payment Success Rate (ALERT if < 85%)
├── Tatkal Booking Latency p99 (ALERT if > 5s)
├── Redis Memory Usage (ALERT if > 80%)
├── DB Connection Pool Usage (ALERT if > 75%)
├── Kafka Consumer Lag (ALERT if > 50K)
├── Error Rate by Endpoint (ALERT if > 3%)
├── Active Reservation Leaks (ALERT if > 100)
└── Chart Preparation SLA (ALERT if not done 4hr before departure)

Dashboards:
├── Real-time Booking Funnel (Search -> Avail -> Book -> Pay -> Confirm)
├── Tatkal War Room (live metrics during Tatkal hours)
├── Berth Utilization Heatmap (by train, class, quota)
├── Payment Gateway Health
├── WL/RAC Movement Tracker
└── System Resource Utilization

11.4 Reconciliation Jobs

Job: Berth Inventory Reconciliation
Frequency: Every 5 minutes
Logic:
  1. For each active train-run:
     a. Count berths in Redis by status
     b. Count bookings in MySQL by status
     c. Verify: Redis(AVAILABLE) + Redis(ALLOCATED) = Total Berths
  2. If mismatch:
     a. Log discrepancy with severity
     b. PAGE alert if overbooking detected
     c. Auto-correct if safe (release orphaned reservations)

Job: Payment Reconciliation
Frequency: Every 30 minutes
Logic:
  1. Fetch all payments from each gateway for last hour
  2. Compare with local payment records
  3. Reconcile mismatches:
     - Payment succeeded but booking not confirmed -> Auto-confirm
     - Booking confirmed but payment failed -> Flag for review
     - Duplicate refunds detected -> Alert finance team

Job: Unpaid Booking Cleanup
Frequency: Every 1 minute
Logic:
  1. Find all bookings with payment_status = 'PENDING'
     AND payment_timeout < NOW()
  2. For each:
     a. Release berth locks
     b. Update booking status to 'CANCELLED'
     c. Update availability counters
     d. Trigger WL/RAC cascade if needed
     e. Notify user of cancellation

Job: WL/RAC Consistency Check
Frequency: Every 15 minutes
Logic:
  1. For each train-run with WL/RAC:
     a. Verify WL numbers are sequential (no gaps)
     b. Verify RAC count <= RAC limit
     c. Verify WL count <= WL limit
     d. Fix any inconsistencies

Appendix A: Glossary

Term Definition
PNR Passenger Name Record — unique 10-character booking reference
Tatkal Emergency booking quota opened 1 day before travel at 10 AM / 11 AM
RAC Reservation Against Confirmation — partial confirmation (2 RAC share 1 berth)
WL Waiting List — ordered queue for over-demand
Chart Final passenger manifest prepared before departure
TTE Train Ticket Examiner — on-board staff who verify tickets
Current Booking Booking made while on the train (at intermediate station)
Quota Allocation category (General, Tatkal, Ladies, Senior Citizen, Defence)
Berth Sleeping accommodation in a train (Lower, Middle, Upper, Side)
Train Run A specific date’s operation of a train number

Appendix B: Technology Stack Recommendations

Component Recommended Technology Alternatives
API Gateway Kong / APISIX AWS API Gateway, Envoy
Service Framework Go (high throughput) / Java Spring Boot Rust, Node.js
Cache / Lock Redis Cluster (7.0+) Dragonfly, KeyDB
Primary DB MySQL 8.0 (InnoDB) PostgreSQL
TimeSeries DB TimescaleDB / InfluxDB Prometheus TSDB
Message Queue Apache Kafka Apache Pulsar
Search Elasticsearch OpenSearch
Container Orchestration Kubernetes ECS
Monitoring Prometheus + Grafana + Alertmanager Datadog
Tracing OpenTelemetry + Jaeger Zipkin
Bot Detection Custom ML + Cloudflare Bot Management PerimeterX

Appendix C: Comparison with BookMyShow

Aspect BookMyShow (Movie) IRCTC (Railway)
Inventory unit Seat (fixed per showing) Berth (shared via RAC, cascading WL)
Booking window Hours to days 120 days in advance
Peak pattern Show start time Tatkal (10 AM) + T+1 opening
Concurrency spike 500x average 66,000x average
Cancellation Simple refund Complex rules + WL cascade
Post-booking QR check-in Chart preparation + TTE verification
Quota system None Multiple quotas with reserved berths
Partial confirmation No Yes (RAC)
Waiting list Optional Core feature with cascade
Offline operation Not needed TTE app must work offline
Regulatory Commercial Government-critical, strict audit

Appendix D: Estimated Infrastructure Costs (Monthly, IRCTC Scale)

Resource Quantity Monthly Cost (USD)
API Gateway instances 30 $6,000
Application servers 150 $30,000
Redis Cluster (50 shards + replicas) 100 nodes $40,000
MySQL Primary + 30 replicas 31 instances $15,500
Kafka Cluster (10 brokers) 10 nodes $6,000
TimeSeries DB (3 nodes) 3 nodes $1,500
CDN + WAF - $15,000
Load Balancers 10 $2,000
Monitoring/Logging - $5,000
Bot Detection Service - $3,000
Total ~$124,000/month

Document Version: 1.0 | Last Updated: 2026-08-28