✈️ XaprAir - Flight Management System

Modern Full-Stack Flight Booking and Reservation Platform

---

📋 About The Project

XaprAir is a comprehensive, enterprise-grade flight booking and reservation system developed as a full-stack web application. This project demonstrates real-world database management, API design, and modern frontend development practices.

✨ Key Features

• 🌍 Multi-language Support - 8 languages (EN, TR, DE, ES, FR, AR, ZH, JA, RU)
• 💱 Multi-currency - 7 currencies (USD, EUR, TRY, GBP, JPY, CNY, AED)
• 🔐 JWT Authentication - Secure token-based authentication
• 👥 Role-Based Access Control - Customer, Manager, Admin roles
• 💺 Dynamic Seat Management - Real-time availability tracking
• 🎫 Online Check-in - Automated check-in 24 hours before departure
• 💳 Payment Processing - Mock payment gateway integration
• 📊 Admin Dashboard - Comprehensive management interface
• 🎨 10 Color Themes - Customizable UI themes

🎯 Technology Stack

Layer	Technology	Version
Frontend	React + TypeScript + Vite	18.2.0
Backend	Express.js + Node.js	5.1.0 + 24.9.0
Database	Microsoft SQL Server	2022
ORM	Sequelize	6.37.7
Authentication	JWT + bcryptjs	9.0.2 + 3.0.2
Payment	Mock Gateway	N/A

---

📁 Monorepo Structure

xaprair-flight-management-system/
├── backend/                   # Express.js Backend API
│   ├── config/                # Database configuration
│   ├── controllers/           # 7 controllers (admin, auth, flight, reservation, manage, payments, preferences)
│   ├── routes/                # 7 route modules
│   ├── middleware/            # Authentication, roles, localization
│   ├── services/              # Database, payment, currency, i18n
│   ├── utils/                 # Helper functions, validators, constants
│   ├── tests/                 # 11 REST Client test files
│   ├── server.js              # Express server entry point
│   ├── db.js                  # SQL Server connection
│   ├── test-connection.js     # Database health check
│   ├── package.json           # Backend dependencies
│   ├── .env.example           # Environment variables template
│   └── README.md              # Backend documentation
│
├── frontend/                  # React Frontend (Vite + TypeScript)
│   ├── public/                # Static assets (icons, favicons)
│   ├── src/
│   │   ├── components/        # React components (Navbar, Footer, etc.)
│   │   ├── locales/           # i18n translation files
│   │   ├── pages/             # Page components (FlightSearch, Preferences, etc.)
│   │   ├── services/          # Frontend-backend API integration
│   │   ├── styles/            # CSS files (10 themes)
│   │   ├── utils/             # Frontend utility functions
│   │   ├── App.tsx            # Main App component
│   │   ├── i18n.ts            # i18n multi-language configuration
│   │   ├── main.tsx           # Frontend entry point
│   │   └── vite-env.d.ts      # Vite TypeScript definitions
│   ├── .env.example           # Frontend environment variables template
│   ├── index.html             # HTML template
│   ├── package.json           # Frontend dependencies
│   ├── tsconfig.json          # TypeScript configuration
│   └── vite.config.ts         # Vite configuration
│
├── database/                  # SQL Server Database Scripts
│   ├── schema/                # Table creation, constraints, indexes (13 tables)
│   ├── functions/             # 4 scalar functions (pricing, cancellation, concatenation)
│   ├── views/                 # 8 views (flight availability, reports)
│   ├── procedures/            # 38 stored procedures (reservations, payments, management)
│   │   ├── admin/             # Admin procedures (13)
│   │   ├── customer/          # Customer procedures (7)
│   │   ├── manager/           # Manager procedures (7)
│   │   └── maintenance/       # Maintenance procedures (5)
│   ├── triggers/              # 7 triggers (audit logs, status updates, validations)
│   ├── seed/                  # Seed scripts + JS generators
│   ├── indexes/               # 20+ index creation scripts
│   └── jobs/                  # 5 SQL Agent job scripts
│
├── package.json               # Workspace root package.json
├── .gitignore                 # Git ignore rules
├── LICENSE                    # License
└── README.md                  # This file

---

🚀 Quick Start

Prerequisites

• Node.js: v18+ (recommended v20+)
• Microsoft SQL Server: 2022 or later
• npm: 9+

1️⃣ Installation

# Clone the repository
git clone https://github.com/xaprier/xaprair-flight-management-system.git
cd xaprair-flight-management-system

# Install all dependencies (root + backend + frontend)
npm run install:all

2️⃣ Environment Configuration

# Backend .env configuration
cd backend
cp .env.example .env

# Edit the .env file with your database credentials
nano .env  # or use any text editor

Backend .env example:

# Database
DB_SERVER=localhost
DB_PORT=1433
DB_NAME=DB_EXAMPLE_NAME
DB_USER=sa
DB_PASS=YourStrongPassword123!

# JWT
JWT_SECRET=change-this-to-a-secure-random-string-in-production
JWT_EXPIRES_IN=24h

# Server
PORT=5000
NODE_ENV=development
FRONTEND_URL=http://localhost:3000

# Payment Gateway
PAYMENT_MODE=mock  # mock (simulated)

Frontend .env example:

# API Configuration
VITE_API_URL=http://localhost:5000/api

3️⃣ Database Setup

Important: All SQL scripts in the database/ directory use USE DB_EXAMPLE_NAME. You can either:

• Use this database name as-is, or
• Find and replace all occurrences with your preferred database name before running scripts

-- 1. Create database in SQL Server (you can use any name you prefer)
CREATE DATABASE YourDatabaseName;  -- Or use: DB_EXAMPLE_NAME
GO

USE YourDatabaseName;
GO

-- 2. If you used a different database name, update your backend .env:
--    DB_NAME=YourDatabaseName

-- 3. Run schema files in order
-- See database/README.md for detailed setup instructions

Quick database setup using scripts:

cd database

# Option 1: Run all scripts manually in SQL Server Management Studio (SSMS)
# Follow the order: schema -> functions -> procedures -> triggers -> views -> seed

# Option 2: Use the automated script runner
node run-scripts.js

# Generate sample data
cd seed
node generate-flights.js      # Creates flights for 7 days back, 203 days forward
node generate-reservations.js 500  # Creates 500 random reservations

4️⃣ Running the Application

Backend Server:

cd backend

# Production mode
npm start

# Development mode (with nodemon auto-reload)
npm run dev

# Test database connection
npm run test

Backend server runs at: http://localhost:5000

Frontend Application:

cd frontend

# Development server
npm run dev

# Production build
npm run build

Frontend dev server runs at: http://localhost:3000

Full-Stack Development (Monorepo):

# From root directory - runs both backend and frontend concurrently
npm run dev

# Or separately:
npm run backend   # Backend only
npm run frontend  # Frontend only

---

� Screenshots

🎨 Theme Showcase

XaprAir offers 10 beautiful color themes with both light and dark modes:

Light Themes

Default	Blue	Green

Orange	Purple	Aqua

Dark Themes

Default	Blue	Green

Orange	Purple	Aqua

✈️ Customer Features

Flight Search	Search Results

Flight Status	My Reservations

Login/Register	Profile Menu

👨‍💼 Manager Features

Flight Management	Reservation Management

👨‍💻 Admin Features

Dashboard	User Management

Airline Management	Airport Management

Audit Logs

---

�📚 Documentation

Detailed documentation for each component:

• Database
  • Database Schema
  • Stored Procedures Documentation
  • Functions
  • Views
  • Triggers
  • SQL Agent Jobs
• Backend
  • Backend API Documentation
  • API Endpoints Reference
  • Authentication Guide
• Frontend
  • Frontend Architecture
  • Component Structure

---

🎨 Features in Detail

Multi-Language Support (i18n)

• 🇬🇧 English
• 🇹🇷 Turkish
• 🇩🇪 German
• 🇪🇸 Spanish
• 🇫🇷 French
• 🇸🇦 Arabic
• 🇨🇳 Chinese
• 🇯🇵 Japanese
• 🇷🇺 Russian

Multi-Currency Support

• 💵 USD (US Dollar)
• 💶 EUR (Euro)
• 💷 GBP (British Pound)
• 🇹🇷 TRY (Turkish Lira)
• 💴 JPY (Japanese Yen)
• 🇨🇳 CNY (Chinese Yuan)
• 🇦🇪 AED (UAE Dirham)

User Roles & Permissions

👤 Customer

• Search and book flights
• Manage reservations
• Online check-in
• View booking history
• Update profile and preferences

👨‍💼 Manager

• View flight statistics
• Manage flight schedules
• Access reservation reports
• Monitor system performance

👨‍💻 Admin

• Full system access
• User management
• Airline and airport management
• System configuration
• Audit log access

---

🏗️ Architecture Overview

┌─────────────────────────────────────────────────────────┐
│              Frontend (React + TypeScript)              │
│  - Multi-language UI (i18n)                            │
│  - 10 Color Themes                                      │
└────────────────────────────────────────────────────────┘
                           ↓ REST API
┌─────────────────────────────────────────────────────────┐
│           Backend API (Express.js + Node.js)            │
│  - JWT Authentication                                   │
│  - Role-Based Access Control                            │
│  - Input Validation                                     │
│  - Error Handling                                       │
└─────────────────────────────────────────────────────────┘
                           ↓ Stored Procedures
┌─────────────────────────────────────────────────────────┐
│        Database Layer (Microsoft SQL Server)            │
│  - 13 Normalized Tables                                 │
│  - 38 Stored Procedures                                 │
│  - 4 Scalar Functions                                   │
│  - 8 Views                                              │
│  - 7 Triggers                                           │
│  - 5 SQL Agent Jobs                                     │
└─────────────────────────────────────────────────────────┘

---

🗃️ Database Design

Entity-Relationship Diagram

Complete database schema showing relationships between all 14 tables

Key Tables

• Users - Customer, Manager, Admin accounts
• Airlines - Airline information and logos
• Airports - Airport codes, names, locations
• Aircraft - Aircraft models and seat configurations
• Flights - Flight schedules and routes
• FlightSegments - Multi-leg flight support
• Reservations - Booking information
• Passengers - Passenger details per reservation
• Payments - Payment transactions
• Seats - Seat availability and assignments
• AuditLog - Complete audit trail

Advanced Features

• ✅ ACID Compliance - Full transaction support
• ✅ Referential Integrity - Foreign key constraints
• ✅ 3NF Normalization - Optimized schema design
• ✅ Audit Trail - All changes logged
• ✅ Concurrent Booking - Row-level locking
• ✅ Dynamic Pricing - Seat class × passenger type
• ✅ Automated Jobs - Status updates, check-in, cleanup

---

🔒 Security Features

• JWT Token Authentication - Secure stateless authentication
• Password Hashing - bcrypt with salt rounds
• Role-Based Authorization - Granular access control
• SQL Injection Prevention - Parameterized queries
• XSS Protection - Input sanitization
• CORS Configuration - Restricted origin policy
• Environment Variables - Sensitive data protection
• Audit Logging - Complete activity tracking

---

🧪 Testing

Note: Some test cases may be based on older scenarios and might not be fully up-to-date with the current implementation. You may need to adjust test data or parameters to match your current database state.

# Backend API tests (REST Client)
cd backend/tests

# Test files available:
# - test-auth.http (Authentication)
# - test-flights.http (Flight search)
# - test-reservations.http (Booking)
# - test-payments.http (Payment processing)
# - test-admin.http (Admin operations)
# - test-manage.http (Manager operations)
# and more...

---

📦 Deployment

Backend Deployment

cd backend
npm start      # Production mode

Frontend Deployment

cd frontend
npm run build  # Creates optimized production build in dist/

# Serve the dist folder with any static file server
# nginx, Apache, or cloud platforms (Vercel, Netlify, etc.)

Database Deployment

1. Set up SQL Server 2022 instance
2. Run all schema scripts in order
3. Execute stored procedures, functions, views, and triggers
4. Configure SQL Agent jobs for automated maintenance
5. Load seed data

---

📝 License

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

---

👨‍💻 Author

Seymen 'xaprier' Kalkan

• GitHub: @xaprier
• LinkedIn: Seymen Kalkan

---

🙏 Acknowledgments

• Developed as a Database Management Systems (DBMS) course project
• Demonstrates real-world application of database design principles
• Implements modern full-stack development practices

---

Made with ❤️ by xaprier

⭐ Star this repo if you find it helpful!

👨‍💻 Author

Seymen 'xaprier' Kalkan

---

Part of the XaprAir Flight Management System

Backend | Frontend | Database

⬆ Back to Top