Database Schema

MokoSuiteTaxi uses 8 database tables, all prefixed with #__mokosuitetaxi_.

Entity Relationship Overview

vehicles ──── drivers ──── shifts
                │
          ┌─────┼─────┐
          │     │     │
        rides  dispatch ratings
          │
        zones ──── fares

Tables

vehicles

Fleet inventory with document tracking.

Column	Type	Description
id	INT PK	Auto-increment
plate_number	VARCHAR(20) UNIQUE	License plate
vin	VARCHAR(17)	Vehicle identification number
make	VARCHAR(50)	Manufacturer
model	VARCHAR(50)	Model name
year	SMALLINT	Model year
color	VARCHAR(30)	Vehicle color
vehicle_type	ENUM	sedan, suv, van, luxury, economy
capacity	TINYINT	Passenger capacity
fuel_type	ENUM	gasoline, diesel, electric, hybrid
status	ENUM	active, maintenance, retired, suspended
insurance_expiry	DATE	Insurance expiration date
inspection_expiry	DATE	Inspection expiration date

drivers

Driver profiles linked to CRM contacts.

Column	Type	Description
id	INT PK	Auto-increment
contact_id	INT FK	Link to CRM contact
name	VARCHAR(100)	Driver display name
phone	VARCHAR(20)	Contact phone
license_number	VARCHAR(50) UNIQUE	Driving license
vehicle_id	INT FK	Assigned vehicle
status	ENUM	pending, active, suspended, inactive
rating	DECIMAL(3,2)	Average rider rating (1.00-5.00)
total_rides	INT	Completed ride count
total_earnings	DECIMAL(12,2)	Lifetime earnings
commission_rate	DECIMAL(5,2) NULL	Override commission (NULL = use global)

zones

Geographic zones with GeoJSON boundaries.

Column	Type	Description
id	INT PK	Auto-increment
name	VARCHAR(100)	Zone display name
zone_type	ENUM	city, airport, suburb, rural, special
boundary_geojson	JSON	GeoJSON polygon boundary
center_lat	DECIMAL(10,7)	Center latitude
center_lng	DECIMAL(10,7)	Center longitude
radius_km	DECIMAL(8,2)	Radius fallback when no polygon
fare_multiplier	DECIMAL(3,2)	Zone-wide fare multiplier
active_hours_start	TIME	Operating hours start
active_hours_end	TIME	Operating hours end

fares

Fare pricing rules by zone and vehicle type.

Column	Type	Description
id	INT PK	Auto-increment
vehicle_type	VARCHAR(20)	Target vehicle type or "all"
zone_id	INT FK NULL	Zone-specific rule (NULL = default)
base_fare	DECIMAL(10,2)	Base ride charge
per_km	DECIMAL(10,2)	Per-kilometer rate
per_minute	DECIMAL(10,2)	Per-minute rate
minimum_fare	DECIMAL(10,2)	Minimum total fare
booking_fee	DECIMAL(10,2)	Fixed booking fee
peak_multiplier	DECIMAL(3,2)	Peak hours multiplier
peak_hours_start	TINYINT	Peak window start hour (0-23)
peak_hours_end	TINYINT	Peak window end hour (0-23)
night_surcharge	DECIMAL(10,2)	Night hours surcharge (22:00-06:00)
airport_surcharge	DECIMAL(10,2)	Airport pickup/dropoff surcharge
effective_from	DATE NULL	Rule effective start
effective_to	DATE NULL	Rule effective end

rides

Core ride records with full lifecycle tracking.

Column	Type	Description
id	INT PK	Auto-increment
ride_ref	VARCHAR(20) UNIQUE	Human-readable reference (TX + hex)
rider_contact_id	INT FK NULL	CRM contact for rider
rider_name	VARCHAR(100)	Rider display name
rider_phone	VARCHAR(20)	Rider phone
driver_id	INT FK NULL	Assigned driver
vehicle_id	INT FK NULL	Assigned vehicle
status	ENUM	requested, dispatched, accepted, arriving, in_progress, completed, cancelled, no_driver
ride_type	ENUM	on_demand, scheduled, airport, corporate
pickup_address	VARCHAR(255)	Pickup location text
pickup_lat/lng	DECIMAL(10,7)	Pickup coordinates
pickup_zone_id	INT FK NULL	Detected pickup zone
dropoff_address	VARCHAR(255)	Dropoff location text
dropoff_lat/lng	DECIMAL(10,7)	Dropoff coordinates
dropoff_zone_id	INT FK NULL	Detected dropoff zone
base_fare	DECIMAL(10,2)	Fare breakdown: base
distance_charge	DECIMAL(10,2)	Fare breakdown: distance
time_charge	DECIMAL(10,2)	Fare breakdown: time
surge_multiplier	DECIMAL(3,2)	Applied surge multiplier
surcharges	DECIMAL(10,2)	Total surcharges
total_fare	DECIMAL(12,2)	Final fare amount
driver_payout	DECIMAL(10,2)	Driver earnings
platform_fee	DECIMAL(10,2)	Platform commission
distance_km	DECIMAL(8,2)	Actual distance traveled
duration_minutes	DECIMAL(8,2)	Actual ride duration
route_polyline	TEXT	Encoded route polyline

ratings

Bidirectional rider/driver ratings.

Column	Type	Description
id	INT PK	Auto-increment
ride_id	INT FK	Associated ride
rated_by	ENUM	rider, driver
rating	TINYINT	Score 1-5
comment	TEXT	Free-text feedback
tags	VARCHAR(255)	Comma-separated feedback tags

dispatch

Dispatch attempt tracking.

Column	Type	Description
id	INT PK	Auto-increment
ride_id	INT FK	Target ride
driver_id	INT FK	Offered driver
status	ENUM	offered, accepted, rejected, expired
attempt_number	INT	Sequential attempt count
offered_at	DATETIME	When offer was made
responded_at	DATETIME NULL	When driver responded
distance_to_pickup	DECIMAL(8,2) NULL	Driver distance to pickup
eta_minutes	DECIMAL(5,1) NULL	Estimated time of arrival

shifts

Driver shift management.

Column	Type	Description
id	INT PK	Auto-increment
driver_id	INT FK	Driver on shift
vehicle_id	INT FK	Vehicle used
status	ENUM	active, completed, cancelled
start_time	DATETIME	Shift start
end_time	DATETIME NULL	Shift end
start_lat/lng	DECIMAL(10,7)	Start location
end_lat/lng	DECIMAL(10,7) NULL	End location
total_rides	INT	Rides completed during shift
total_earnings	DECIMAL(10,2)	Earnings during shift
total_distance_km	DECIMAL(8,2)	Distance driven during shift

Status ENUMs

Ride Status

requested -- Rider has requested, waiting for dispatch
dispatched -- Sent to a driver, awaiting acceptance
accepted -- Driver accepted, en route to pickup
arriving -- Driver near pickup location
in_progress -- Ride underway
completed -- Ride finished, fare calculated
cancelled -- Cancelled by rider or system
no_driver -- No driver available, can retry

Driver Status

pending -- Awaiting approval
active -- Available for dispatch
suspended -- Temporarily blocked
inactive -- Deactivated

Vehicle Status

active -- Available for use
maintenance -- Under repair/service
retired -- Permanently removed from fleet
suspended -- Temporarily unavailable

Pages