Data Models¶

All entities, enums, and relationships. The database is PostgreSQL 15 with PostGIS 3.3. JPA/Hibernate manages the schema.

Entity relationships¶

erDiagram
    USER ||--o| DRIVER : "has"
    USER ||--o| CUSTOMER : "has"
    DRIVER ||--o{ VEHICLE : "owns"
    DRIVER ||--o{ DOCUMENT : "submits"
    DRIVER ||--o| VEHICLE : "active_vehicle"
    DRIVER ||--o| DRIVER_LOCATION : "location"
    RIDE }o--|| CUSTOMER : "requested_by"
    RIDE }o--o| DRIVER : "assigned_to"
    RIDE }o--o| VEHICLE : "uses"
    RATING }o--|| RIDE : "for"
    RATING }o--|| USER : "rater"
    RATING }o--|| USER : "ratee"
    DOCUMENT }o--o| USER : "reviewed_by"
    TOKEN }o--|| USER : "belongs_to"

User¶

Table: user

Field	Type	Constraints	Notes
`id`	bigint	PK, auto
`email`	varchar	unique, not null	Login identifier
`password`	varchar	not null	BCrypt encoded
`first_name`	varchar	not null
`last_name`	varchar
`phone_number`	varchar
`role`	varchar	not null	Enum: `CUSTOMER`, `DRIVER`, `ADMIN`
`status`	varchar		Enum: `PENDING`, `ACTIVE`
`created_at`	timestamp
`updated_at`	timestamp

Driver¶

Table: driver

Field	Type	Constraints	Notes
`id`	bigint	PK, auto
`user_id`	bigint	FK → user	One-to-one
`driver_license_number`	varchar	not null
`total_rides`	int		Incremented on completion
`online_status`	varchar		Enum: `ONLINE`, `OFFLINE`
`last_updated`	timestamp
`active_vehicle_id`	bigint	FK → vehicle	Nullable

Customer¶

Table: customer

Field	Type	Constraints	Notes
`id`	bigint	PK, auto
`user_id`	bigint	FK → user	One-to-one
`total_rides`	int

Ride¶

Table: ride

Field	Type	Constraints	Notes
`id`	bigint	PK, auto
`customer_id`	bigint	FK → customer, not null
`driver_id`	bigint	FK → driver, nullable	Set on acceptance
`vehicle_id`	bigint	FK → vehicle, nullable
`pickup_lat`	double
`pickup_lng`	double
`dropoff_lat`	double
`dropoff_lng`	double
`service_type`	varchar		Enum: `STANDARD`, `XL`, `POOL`, `PREMIUM`
`status`	varchar		Enum: see below
`estimated_fare`	decimal(10,2)		Set at request time
`final_fare`	decimal(10,2)		Set at completion
`currency`	varchar		e.g. `CAD`
`requested_at`	timestamp
`started_at`	timestamp
`ended_at`	timestamp

Vehicle¶

Table: vehicle

Field	Type	Constraints	Notes
`id`	bigint	PK, auto
`driver_id`	bigint	FK → driver
`make`	varchar
`model`	varchar
`registration_number`	varchar	unique, not null
`year`	int
`registered_at`	date
`seating_capacity`	int
`interior_type`	varchar
`color`	varchar
`service_type`	varchar		Enum: `STANDARD`, `XL`, `POOL`, `PREMIUM`

Document¶

Table: document

Field	Type	Constraints	Notes
`id`	bigint	PK, auto
`driver_id`	bigint	FK → driver, not null
`type`	varchar		Enum: `LICENSE`, `INSURANCE`, `REGISTRATION`
`status`	varchar		Enum: `PENDING`, `APPROVED`, `REJECTED`
`file_name`	varchar
`file_url`	varchar		S3 object key
`file_size`	bigint		Bytes
`uploaded_at`	timestamp
`reviewed_by`	bigint	FK → user, nullable	Admin who reviewed
`reviewed_at`	timestamp
`rejection_reason`	varchar(500)		Set on rejection

Rating¶

Table: ratings

Unique constraint: (ride_id, rater_id) — one rating per rater per ride.

Field	Type	Constraints	Notes
`id`	bigint	PK, auto
`ride_id`	bigint	FK → ride
`rater_id`	bigint	FK → user	Who gave the rating
`ratee_id`	bigint	FK → user	Who received the rating
`score`	int	1–5
`comment`	varchar(500)
`created_at`	timestamp

Token¶

Table: token

Field	Type	Constraints	Notes
`id`	bigint	PK, auto
`token`	varchar		JWT string
`refresh_token`	varchar	unique	Opaque UUID
`refresh_token_expires_at`	timestamp		30 days from issue
`expired`	boolean		True when access token expired
`revoked`	boolean		True when explicitly invalidated
`user_id`	bigint	FK → user

DriverLocation¶

Table: driver_location

One row per driver. Upserted on each location update.

Field	Type	Notes
`id`	bigint (= driver_id)	PK
`driver_id`	bigint	FK → driver (one-to-one)
`location`	geometry(Point, 4326)	PostGIS, SRID 4326 (WGS84). Stored as `Coordinate(lon, lat)`
`updated_at`	timestamptz	Last update time

Enums¶

`RideStatus`¶

SEARCHING → ACCEPTED → ARRIVED → IN_PROGRESS → COMPLETED SEARCHING → CANCELLED ACCEPTED → CANCELLED

Value	Description
`SEARCHING`	Async matching in progress, 30s timer running
`ACCEPTED`	Driver assigned, timer cancelled
`ARRIVED`	Driver at pickup location
`IN_PROGRESS`	Customer on board, location streaming active
`COMPLETED`	Ride finished
`CANCELLED`	Timer expired or manually cancelled

`ServiceType`¶

Value	Description
`STANDARD`	Standard 4-door sedan
`XL`	SUV or van, 6+ seats
`POOL`	Shared ride
`PREMIUM`	Luxury vehicle

`DocumentType`¶

Value	Description
`LICENSE`	Driver's licence
`INSURANCE`	Vehicle insurance certificate
`REGISTRATION`	Vehicle registration document

`DocumentStatus`¶

Value	Description
`PENDING`	Awaiting admin review
`APPROVED`	Accepted by admin
`REJECTED`	Rejected with reason

`OnlineStatus`¶

Value	Description
`ONLINE`	Driver visible in matching and nearby search
`OFFLINE`	Driver hidden from all searches

`Role`¶

Value	Description
`CUSTOMER`	Can request rides, rate drivers
`DRIVER`	Can accept rides, manage fleet, go online
`ADMIN`	Can review and approve driver documents