🌟 TripSync - Enterprise Travel Management System

A comprehensive, enterprise-grade travel booking system with advanced transaction management, real-time reservations, and atomic payment processing.

🚀 Quick Start

# Clone the repository
git clone https://github.com/yourusername/TripSync.git
cd TripSync

# Install dependencies
npm install

# Set up database (see Database Setup section)
mysql -u root -p < tripsync_schema.sql
mysql -u root -p < transaction_system_setup.sql

# Start the development servers
npm run dev      # Backend (Port 3001)
npm run frontend # Frontend (Port 3000)

Demo Credentials:

• Email: test@example.com
• Password: password123

📋 Table of Contents

• 🌟 Features
• 🏗️ System Architecture
• 💻 Technology Stack
• 🗄️ Database Schema
• 🔄 Transaction System
• 📡 API Documentation
• 🚀 Installation & Setup
• 🧪 Testing
• 📁 Project Structure
• 🔧 Configuration
• 🤝 Contributing

🌟 Features

✅ Core Functionality

• 🎫 Holiday Package Booking - Browse and book complete travel packages
• 🏨 Hotel Reservations - Search and book accommodations
• ✈️ Transport Booking - Flights and train reservations
• 👤 User Management - Registration, authentication, profile management
• 🔐 Admin Panel - Package management and system administration

🚀 Advanced Features

• ⏱️ Real-Time Reservations - 10-minute temporary holds with countdown timers
• 🔒 Concurrency Control - Database row locking for high-demand bookings
• 💳 Atomic Transactions - Payment processing with complete rollback safety
• 📊 Inventory Management - Real-time availability tracking
• 🔄 Auto-Cleanup - Expired reservations automatically released
• 📈 Monitoring Dashboard - System statistics and performance metrics

🎨 User Experience

• 📱 Responsive Design - Mobile-first approach with Tailwind CSS
• ⚡ Real-Time Updates - Live inventory and booking status
• 🎯 Intuitive Interface - Step-by-step booking flow
• 🚨 Error Handling - Comprehensive user feedback and error recovery

🏗️ System Architecture

graph TB
    subgraph "Client Layer"
        UI[React Frontend]
        UI --> |HTTP/REST| API
    end

    subgraph "Application Layer"
        API[Express.js API Server]
        API --> TM[Transaction Manager]
        API --> RM[Reservation Manager] 
        API --> AUTH[Auth Controller]
        API --> BOOKING[Booking Controller]
    end

    subgraph "Business Logic Layer"
        TM --> |Atomic Operations| DB
        RM --> |Row Locking| DB
        RM --> |Auto-Cleanup| TIMER[Cleanup Timer]
    end

    subgraph "Data Layer"
        DB[(MySQL Database)]
        DB --> TABLES[Tables: users, packages, hotels, transport, reservations, payments, bookings]
    end

    subgraph "External Systems"
        PAYMENT[Payment Gateway]
        EMAIL[Email Service]
    end

    BOOKING --> PAYMENT
    API --> EMAIL

    style UI fill:#e1f5fe
    style API fill:#fff3e0
    style TM fill:#f3e5f5
    style RM fill:#e8f5e8
    style DB fill:#fff8e1

Loading

Transaction Flow Diagram

sequenceDiagram
    participant U as User
    participant F as Frontend
    participant API as API Server
    participant RM as Reservation Manager
    participant TM as Transaction Manager
    participant DB as Database

    U->>F: Select Package & Click Reserve
    F->>API: POST /api/transaction-bookings/reserve
    API->>RM: Create Reservation
    RM->>TM: Execute Transaction
    TM->>DB: BEGIN TRANSACTION
    TM->>DB: Lock Package (FOR UPDATE)
    TM->>DB: Validate Inventory
    TM->>DB: Create Reservation Record
    TM->>DB: COMMIT
    TM-->>RM: Success
    RM-->>API: Reservation Created
    API-->>F: 10-min Timer Started
    F-->>U: Show Countdown Timer

    Note over RM: Auto-cleanup timer running

    U->>F: Click "Confirm & Pay"
    F->>API: POST /api/transaction-bookings/confirm/:id
    API->>RM: Confirm Reservation
    RM->>TM: Execute Payment Transaction
    TM->>DB: BEGIN TRANSACTION
    TM->>DB: Update Inventory
    TM->>DB: Create Booking Record
    TM->>DB: Process Payment
    TM->>DB: Update Reservation Status
    TM->>DB: COMMIT
    TM-->>RM: Success
    RM-->>API: Booking Confirmed
    API-->>F: Payment Success
    F-->>U: Show Confirmation

Loading

💻 Technology Stack

Frontend

Technology	Version	Purpose
React	18.2.0	UI Framework
Vite	5.0.8	Build Tool & Dev Server
Tailwind CSS	3.4.17	Styling Framework
Lucide React	Latest	Icon Library
React Router	Latest	Client-side Routing

Backend

Technology	Version	Purpose
Node.js	18.x	Runtime Environment
Express.js	4.x	Web Framework
MySQL2	Latest	Database Driver
bcrypt	Latest	Password Hashing
jsonwebtoken	Latest	Authentication
cors	Latest	Cross-origin Requests
helmet	Latest	Security Headers

Database

Component	Technology	Purpose
RDBMS	MySQL 8.0	Primary Database
Connection Pooling	mysql2/promise	Connection Management
Transactions	InnoDB Engine	ACID Compliance
Indexing	B-Tree Indexes	Query Optimization

🗄️ Database Schema

Core Tables

erDiagram
    USER {
        int User_ID PK
        string Name
        string Email UK
        string Phone_No
        date Age_DOB
        int Age_Year
        enum Sex
        string Address_House_No
        string Address_City
        string Address_PIN
        string Password
        timestamp created_at
        timestamp updated_at
    }

    HOLIDAY_PACKAGE {
        int Package_Id PK
        string Package_Name
        int Ticket_no FK
        int Hotel_Id FK
        string Time_Period
        decimal Discounts
        decimal Total_Price
        int Max_Occupancy
        int Current_Bookings
        timestamp created_at
        timestamp updated_at
    }

    HOTELS {
        int Hotel_Id PK
        string Hotel_Name
        string Location
        int Vacancy_Total
        int Vacancy_Filled
        decimal Price_Per_Night
        int Star_Rating
        text Amenities
        timestamp created_at
        timestamp updated_at
    }

    TRANSPORT {
        int Transport_ID PK
        string Start_Location
        string Destn_Location
        time Timings
        decimal Price
        int Vacancy_Total
        int Vacancy_Filled
        enum Transport_Type
        timestamp created_at
        timestamp updated_at
    }

    PACKAGE_BOOKINGS {
        int Booking_ID PK
        int User_ID FK
        int Package_ID FK
        date Travel_Date
        int Number_of_Travelers
        decimal Total_Amount
        enum Status
        int Passenger_Count
        timestamp created_at
        timestamp updated_at
    }

    RESERVATIONS {
        int id PK
        string reservation_id UK
        int package_id FK
        int hotel_id FK
        int transport_id FK
        int user_id FK
        int passenger_count
        enum status
        datetime expires_at
        datetime created_at
        datetime confirmed_at
        datetime released_at
    }

    PAYMENTS {
        int Payment_ID PK
        int User_ID FK
        int Ticket_ID FK
        int Hotel_Id FK
        enum Payment_Status
        decimal Amount
        string Payment_Method
        string Transaction_ID
        timestamp created_at
        timestamp updated_at
    }

    USER ||--o{ PACKAGE_BOOKINGS : makes
    USER ||--o{ RESERVATIONS : creates
    USER ||--o{ PAYMENTS : processes
    HOLIDAY_PACKAGE ||--o{ PACKAGE_BOOKINGS : includes
    HOLIDAY_PACKAGE ||--o{ RESERVATIONS : reserves
    HOLIDAY_PACKAGE }o--|| HOTELS : includes
    RESERVATIONS }o--o| HOTELS : may_include
    RESERVATIONS }o--o| TRANSPORT : may_include

Loading

Key Relationships

• User: Central entity for all bookings and transactions
• Reservations: Temporary holds with 10-minute expiry
• Package_Bookings: Confirmed reservations converted to bookings
• Payments: Transaction records linked to bookings
• Inventory Management: Real-time tracking via Current_Bookings and Vacancy_Filled

🔄 Transaction System

Core Concepts

1. Reservation Phase (Step 1)

// 10-minute temporary hold with inventory lock
const reservation = await reservationManager.createReservation({
    packageId, hotelId, transportId, userId, passengerCount
});

What Happens:

• ✅ Database row locking (FOR UPDATE)
• ✅ Inventory validation
• ✅ Temporary reservation creation
• ✅ 10-minute expiry timer
• ✅ Auto-cleanup if expired

2. Confirmation Phase (Step 2)

// Atomic transaction: Payment + Booking + Inventory Update
const result = await reservationManager.confirmReservation(
    reservationId, paymentDetails
);

What Happens:

• ✅ Payment processing
• ✅ Inventory decrement
• ✅ Booking record creation
• ✅ Reservation status update
• ✅ Complete rollback on any failure

Transaction Safety Features

Feature	Implementation	Benefit
Atomicity	Database transactions with BEGIN/COMMIT/ROLLBACK	All-or-nothing operations
Consistency	Foreign key constraints and validation	Data integrity maintained
Isolation	Row-level locking with FOR UPDATE	No race conditions
Durability	InnoDB storage engine with write-ahead logging	Crash recovery

Concurrency Control

graph LR
    A[User A Requests] --> B[Row Lock Acquired]
    C[User B Requests] --> D[Waits for Lock]
    B --> E[Inventory Check]
    E --> F[Reservation Created]
    F --> G[Lock Released]
    G --> D
    D --> H[User B Processes]
    H --> I[Updated Inventory Check]

Loading

📡 API Documentation

Authentication Endpoints

POST /api/users/register
POST /api/users/login
GET  /api/users/profile
PUT  /api/users/profile

Transaction Booking Endpoints

# Create temporary reservation (10-minute hold)
POST /api/transaction-bookings/reserve
Content-Type: application/json
{
    "packageId": 1,
    "hotelId": 2,
    "transportId": 3,
    "passengerCount": 2,
    "userId": 1
}

# Confirm reservation with payment
POST /api/transaction-bookings/confirm/:reservationId
Content-Type: application/json
{
    "paymentMethod": "CREDIT_CARD",
    "cardDetails": {
        "cardNumber": "4111111111111111",
        "expiryDate": "12/25", 
        "cvv": "123"
    }
}

# Get reservation status
GET /api/transaction-bookings/reservation/:reservationId

# Cancel reservation
DELETE /api/transaction-bookings/cancel/:reservationId

# Get system statistics
GET /api/transaction-bookings/stats

# Check availability
GET /api/transaction-bookings/availability?type=package&id=1

Legacy Booking Endpoints

GET    /api/holiday-packages
GET    /api/holiday-packages/:id
POST   /api/bookings
GET    /api/bookings/user/:userId

Admin Endpoints

GET    /api/admin/packages
POST   /api/admin/packages
PUT    /api/admin/packages/:id
DELETE /api/admin/packages/:id

🚀 Installation & Setup

Prerequisites

• Node.js 18.x or higher
• MySQL 8.0 or higher
• npm or yarn package manager
• Git for version control

Step 1: Clone Repository

git clone https://github.com/yourusername/TripSync.git
cd TripSync

Step 2: Install Dependencies

npm install

Step 3: Database Setup

1. Create Database:

CREATE DATABASE tripsync;
USE tripsync;

2. Import Schema:

mysql -u root -p tripsync < tripsync_schema.sql
mysql -u root -p tripsync < transaction_system_setup.sql

3. Create Environment File:

cp .env.example .env

Edit .env:

# Database Configuration
DB_HOST=localhost
DB_USER=root
DB_PASSWORD=yourpassword
DB_NAME=tripsync
DB_PORT=3306

# Server Configuration
PORT=3001
CORS_ORIGIN=http://localhost:3000

# JWT Configuration
JWT_SECRET=your-super-secret-jwt-key
JWT_EXPIRES_IN=7d

# Application Settings
NODE_ENV=development

Step 4: Start Development Servers

# Terminal 1: Backend Server (Port 3001)
npm run dev

# Terminal 2: Frontend Server (Port 3000)  
npm run frontend

Step 5: Access Application

• Frontend: http://localhost:3000
• Backend API: http://localhost:3001
• Health Check: http://localhost:3001/health

🧪 Testing

Manual Testing Flow

1. Registration/Login

   • Register new user or use demo credentials
   • Verify JWT token generation

2. Browse Packages

   • View holiday packages with photos
   • Check real-time availability

3. Transaction Booking

   • Navigate to "Transaction Booking"
   • Select package and passenger count
   • Create reservation (10-minute timer starts)
   • Confirm payment before expiry
   • Verify booking confirmation

4. Concurrent Testing

   • Open multiple browser tabs
   • Try booking same package simultaneously
   • Verify inventory management

Database Verification

-- Check reservation creation
SELECT * FROM reservations ORDER BY created_at DESC LIMIT 5;

-- Check booking confirmation  
SELECT * FROM package_bookings ORDER BY created_at DESC LIMIT 5;

-- Check payment processing
SELECT * FROM payments ORDER BY created_at DESC LIMIT 5;

-- Check inventory updates
SELECT Package_Name, Max_Occupancy, Current_Bookings, 
       (Max_Occupancy - Current_Bookings) AS Available
FROM holiday_package;

API Testing with curl

# Test reservation creation
curl -X POST http://localhost:3001/api/transaction-bookings/reserve \
  -H "Content-Type: application/json" \
  -H "x-user-id: 1" \
  -d '{"packageId":1,"passengerCount":2,"userId":1}'

# Test system stats
curl http://localhost:3001/api/transaction-bookings/stats

📁 Project Structure

TripSync/
├── 📁 config/
│   ├── database.js              # Enhanced DB config with TransactionManager
│   └── kafka.js                 # Message broker configuration
├── 📁 controllers/
│   ├── bookingController.js     # Legacy booking logic  
│   ├── holidayPackageController.js
│   ├── transactionBookingController.js  # ⭐ New transaction system
│   └── userController.js        # Authentication & user management
├── 📁 middleware/
│   └── adminAuth.js             # Admin authentication middleware
├── 📁 routes/
│   ├── adminPackageRoutes.js
│   ├── adminRoutes.js
│   ├── bookingRoutes.js
│   ├── holidayPackageRoutes.js
│   ├── transactionBookingRoutes.js  # ⭐ New transaction endpoints
│   └── userRoutes.js
├── 📁 services/
│   ├── kafkaConsumerService.js
│   └── reservationManager.js    # ⭐ Core transaction logic
├── 📁 src/                      # Frontend React Application
│   ├── 📁 components/
│   │   ├── Layout.jsx           # Main layout with navigation
│   │   ├── 📁 admin/
│   │   │   └── AdminLayout.jsx
│   │   └── 📁 ui/               # Reusable UI components
│   │       ├── alert.jsx
│   │       ├── button.jsx
│   │       ├── card.jsx
│   │       └── ...
│   ├── 📁 hooks/
│   │   ├── useAuth.jsx          # Authentication hook
│   │   └── useAdminAuth.jsx
│   ├── 📁 lib/
│   │   ├── api.js               # API client
│   │   └── utils.js
│   └── 📁 pages/
│       ├── Home.jsx
│       ├── HolidayPackages.jsx
│       ├── Hotels.jsx
│       ├── Transport.jsx
│       ├── Login.jsx
│       ├── Register.jsx
│       ├── MyBookings.jsx
│       ├── TransactionBooking.jsx  # ⭐ New transaction interface
│       └── 📁 admin/
├── 📁 database/
│   ├── tripsync_schema.sql      # Base database schema
│   ├── transaction_system_setup.sql  # ⭐ Transaction tables
│   └── package_bookings_setup.sql
├── server.js                    # Main Express application
├── package.json
├── vite.config.js              # Frontend build configuration
├── tailwind.config.js          # Tailwind CSS configuration
└── README.md                   # ⭐ This file

🔧 Configuration

Environment Variables

Variable	Default	Description
DB_HOST	localhost	MySQL server host
DB_USER	root	Database username
DB_PASSWORD	-	Database password
DB_NAME	tripsync	Database name
DB_PORT	3306	Database port
PORT	3001	Backend server port
CORS_ORIGIN	http://localhost:3000	Frontend URL for CORS
JWT_SECRET	-	Secret key for JWT signing
JWT_EXPIRES_IN	7d	JWT token expiry
NODE_ENV	development	Environment mode

Database Configuration

// config/database.js
const dbConfig = {
    host: process.env.DB_HOST || 'localhost',
    user: process.env.DB_USER || 'root',
    password: process.env.DB_PASSWORD || '',
    database: process.env.DB_NAME || 'tripsync',
    port: process.env.DB_PORT || 3306,
    waitForConnections: true,
    connectionLimit: 15,           # Connection pool size
    queueLimit: 0,
    acquireTimeout: 60000,
    timeout: 60000,
    reconnect: true,
    supportBigNumbers: true,
    bigNumberStrings: true,
    timezone: '+00:00'             # UTC timezone
};

Transaction Configuration

// services/reservationManager.js
class ReservationManager {
    constructor() {
        this.RESERVATION_TIMEOUT = 10 * 60 * 1000;  # 10 minutes
        this.cleanupInterval = 30000;               # 30 seconds
    }
}

🔍 Monitoring & Logging

System Statistics Endpoint

GET /api/transaction-bookings/stats

Response:

{
    "success": true,
    "stats": {
        "reservations": {
            "total": 15,
            "active": 3,
            "expired": 2,
            "confirmed": 10
        },
        "activeTransactions": 1,
        "timestamp": "2025-09-08T03:15:42.123Z"
    }
}

Application Logs

The system provides comprehensive logging:

// Reservation Creation
🎫 Creating reservation res_1757281270892_fdbiy5adr for user 1
🔄 Starting transaction: res_1757281270892_fdbiy5adr
📊 Transaction res_1757281270892_fdbiy5adr: Started
⚙️  Transaction res_1757281270892_fdbiy5adr: Executing step 1/4
✅ Transaction res_1757281270892_fdbiy5adr: Step 1 completed
...
🎉 Transaction res_1757281270892_fdbiy5adr: Committed successfully

// Payment Confirmation
💳 Confirming booking for reservation: res_1757281270892_fdbiy5adr
🎉 Booking confirmed successfully: 1

🚀 Production Deployment

Docker Configuration

# Dockerfile
FROM node:18-alpine

WORKDIR /app

# Install dependencies
COPY package*.json ./
RUN npm ci --only=production

# Copy source code
COPY . .

# Build frontend
RUN npm run build

# Expose port
EXPOSE 3001

# Start application
CMD ["npm", "start"]

Docker Compose

version: '3.8'

services:
  app:
    build: .
    ports:
      - "3001:3001"
    environment:
      - NODE_ENV=production
      - DB_HOST=mysql
      - DB_USER=tripsync
      - DB_PASSWORD=${DB_PASSWORD}
      - DB_NAME=tripsync
    depends_on:
      - mysql

  mysql:
    image: mysql:8.0
    environment:
      - MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD}
      - MYSQL_DATABASE=tripsync
      - MYSQL_USER=tripsync
      - MYSQL_PASSWORD=${DB_PASSWORD}
    volumes:
      - mysql_data:/var/lib/mysql
      - ./database:/docker-entrypoint-initdb.d

volumes:
  mysql_data:

Performance Optimizations

1. Database Indexing

   • Primary keys on all tables
   • Foreign key indexes
   • Composite indexes on frequently queried columns

2. Connection Pooling

   • 15 concurrent connections
   • Automatic connection recycling
   • Query timeout handling

3. Caching Strategy

   • In-memory reservation cache
   • API response caching (future enhancement)
   • Static asset optimization

🤝 Contributing

Development Workflow

1. Fork the repository
2. Create feature branch: git checkout -b feature/amazing-feature
3. Make changes following code standards
4. Write tests for new functionality
5. Commit changes: git commit -m 'Add amazing feature'
6. Push to branch: git push origin feature/amazing-feature
7. Open Pull Request

Code Standards

• ESLint: Enforced code style
• Prettier: Code formatting
• JSDoc: Function documentation
• Conventional Commits: Commit message format

Testing Requirements

• Unit tests for new functions
• Integration tests for API endpoints
• Database transaction testing
• Error handling verification

📄 License

This project is licensed under the MIT License - see the LICENSE file for details.

🙏 Acknowledgments

• React Team for the incredible framework
• Express.js Community for the robust web framework
• MySQL Team for reliable database technology
• Tailwind CSS for beautiful styling system
• shadcn/ui for elegant component library

📞 Support

For support, email your-email@example.com or create an issue in this repository.

---

⭐ If this project helped you, please give it a star!

🔗 Connect with us:

• GitHub
• Documentation
• Issues

---

Built with ❤️ by the TripSync Team