RideFlow — Ride-Hailing Database Management System

A full-stack ride-hailing platform built as a Database Systems course project. RideFlow demonstrates professional-grade database design across the complete stack: a normalized MySQL schema with stored procedures, triggers, views, and events; a Node.js/Express backend with role-based access control; and real-time communication via Socket.io.

Inspired by Uber, Careem, and InDrive.

---

Tech Stack

Layer	Technology
Backend	Node.js, Express.js
Database	MySQL 8.0+ (Stored Procedures, Triggers, Views, Events)
Real-time	Socket.io
Frontend	EJS, Bootstrap 5, Leaflet.js
Auth	bcryptjs, express-session

---

Quick Start

Prerequisites

• Node.js v16+
• MySQL Server 8.0+

1. Clone & Install

git clone <repo-url>
cd rideflow
npm install

2. Configure Environment

cp .env.example .env

Open .env and fill in your MySQL password and any other values.

3. Initialize the Database

The seed script creates the schema, fare rules, and demo accounts in one step:

npm run seed

Alternatively, import the SQL file manually:

mysql -u root -p < schema.sql

4. Run

npm start          # production
npm run dev        # development (nodemon, auto-restart)

Open http://localhost:5000 in your browser.

---

Demo Accounts

Role	Email	Password	Access
Admin	admin@rideflow.com	admin123	Reports, Driver verification, Fare config
Rider	rider@rideflow.com	rider123	Book rides, Wallet, Promo codes
Driver	ahmed@email.com	driver123	Accept rides, Earnings dashboard

---

Database Design

Schema at a Glance

9 core entities across 5 modules: Users, Drivers, Vehicles, Rides, Payments, Ratings, PromoCodes, Complaints, Locations

Constraint	Count	Constraint	Count
PRIMARY KEY	9	CHECK	35+
FOREIGN KEY	13	UNIQUE	19+

All foreign keys use ON DELETE RESTRICT to preserve referential integrity.

Views

View	Purpose
vw_active_rides	Live trips with driver and rider details
vw_top_drivers	Drivers with average rating > 4.5
vw_low_rated_drivers	Drivers with average rating < 3.5
vw_revenue_summary	Revenue breakdown by city and payment method

Stored Procedures

Procedure	Description
sp_calculate_fare	Computes fare from distance, duration, and surge multiplier
sp_find_nearest_driver	Returns available verified drivers filtered by vehicle type
sp_complete_ride	Finalizes ride, deducts platform fee, updates driver status

Triggers

Trigger	Fires On	Action
trg_payment_paid	Payment marked Paid	Sets ride status to Completed
trg_low_driver_rating	Rating inserted	Flags driver and notifies admin if avg < 3.5
trg_promo_usage_count	Promo applied to ride	Increments promo usage counter

Event Scheduler

Event	Schedule	Action
evt_expire_promocodes	Daily at midnight	Marks expired promo codes inactive

Indexes

Strategic indexes on rider_id, driver_id, ride_status, and city for performance on frequent WHERE and JOIN operations.

Role-Based Access Control (DCL)

Role	Permissions
Rider	SELECT/INSERT on Rides, Payments, Ratings
Driver	SELECT/UPDATE on Rides, Earnings
Admin	Full system access

---

Financial Model

RideFlow uses a flat-fee platform model rather than a percentage commission:

• Rider side: +25 PKR platform fee added to every fare
• Driver side: −25 PKR platform fee deducted from earnings
• Platform profit: 50 PKR per completed ride, tracked automatically in Admin Reports

Fare formula: Base + (Distance × Per-KM rate) + (Duration × Per-Min rate) × Surge multiplier

---

Project Structure

rideflow/
├── config/
│   └── database.js          # MySQL connection pool
├── middleware/
│   └── auth.js              # Session auth & role guards
├── routes/
│   ├── auth.js              # Login, register, logout
│   ├── rider.js             # Ride booking, history, wallet
│   ├── driver.js            # Availability, trip acceptance, earnings
│   └── admin.js             # User management, reports, fare config
├── views/
│   ├── rider/               # Rider dashboards (EJS)
│   ├── driver/              # Driver dashboards (EJS)
│   └── admin/               # Admin dashboards (EJS)
├── scripts/
│   ├── seed.js              # DB init + demo data
│   └── start-cluster.js     # Cluster mode launcher
├── static/
│   └── uploads/             # Runtime file uploads (git-ignored)
├── schema.sql               # Full DDL + DML + DCL
├── server.js                # Express app entry point + Socket.io
├── package.json
├── .env.example             # Environment variable template
└── .gitignore

---

Features

Ride Lifecycle

Requested → Accepted → En Route → In Progress → Completed

Driver matching is based on availability, vehicle type, and location. Scheduled advance booking is supported.

Driver Verification

Pending → Verified → Rejected — controlled by Admin. Unverified drivers cannot be matched to rides.

Payment Methods

Cash, Wallet, and Card. Wallet balance tracked per rider. Promo codes with usage limits and expiry dates.

Rating System

Mutual 1–5 star rating after ride completion. Drivers falling below 3.5 average are auto-flagged.

---

npm Scripts

npm start            # Start server
npm run dev          # Start with nodemon (auto-restart on changes)
npm run seed         # Initialize DB schema + insert demo data
npm run test-cluster # Run in cluster mode

---

Project Details

• Course: Database Systems Lab (AI & DS) — Spring 2026
• Institution: FAST NUCES Islamabad

Authors

• Imtishal Haq
• Harram Javed

Acknowledgements

• Miss Sidra Fayyaz
• Miss Ayesha Kamran