Opening case study…
Opening case study…
A multi-role ride-hailing simulation where the business rules live in MySQL — procedures, triggers and constraints — rather than in the Express layer above them.
Covers
Stack
A ride-hailing platform is a deceptively good relational problem. Two parties who do not know each other are matched by a third, money moves in one direction and settles in the other, and every step has to leave a record that can be argued about later.
So the project starts from the schema rather than the screens. Riders, drivers, vehicles, locations, fare rules and promotions exist before a ride does; rides produce payments, ratings and complaints; payments feed wallets, and wallets feed payouts. Sixteen tables, joined by twenty-nine foreign keys.
The states are declared rather than implied. A driver is pending, verified or rejected. A payment is pending, paid, failed or refunded. A complaint is open, under review, resolved or rejected. Writing those as enumerated columns means an impossible state is rejected by the store, not merely avoided by the code that happens to be writing at the time.
Above the schema sits a single Express service — 43 routes across roughly 2,500 lines — that handles sessions, checks the caller's role, serves the static front end and talks to MySQL through a connection pool.
It leans on four runtime dependencies and no ORM. Queries are SQL, and the calls that matter invoke stored procedures directly. On a project whose subject is relational design, an ORM would have hidden exactly the thing being demonstrated.
The front end is vanilla JavaScript organised as one shared application shell — API wrapper, auth flow, state, UI helpers, device detection — with per-role feature modules layered on top. Which audience a visitor gets is resolved from the path, so all three dashboards ship from one service rather than three deployments.
Rider surface
Booking
Driver surface
Trips
Admin surface
Oversight
Shared app shell
Audience by path
Express API
43 routes
Sessions & role checks
Per request
Procedures & triggers
Rules in SQL
MySQL 8
16 tables · 29 foreign keys
Reporting views
Six, read directly
Database roles
Four, distinct grants
Scheduled event
Nightly promo expiry
This is the decision the project is really about. The business rules are not in the Express layer; they are in MySQL, as procedures, triggers, constraints and views.
Two stored procedures carry the operations that must not be got wrong: calculating a fare, and requesting a ride. Eight triggers hold the invariants around them — validating a driver assignment before a ride row is written or updated, computing the driver's net share before a payment lands, finalising a payment once it is marked paid, recalculating rating averages when a rating arrives, maintaining trip counts, and archiving a ride into history when it reaches a terminal state.
The trigger names are worth a mention on their own: each one states its table, its timing, its operation and its purpose. Reading the list tells you what the database guarantees without opening a single body — which is the difference between logic that lives in the store and logic that merely happens to be stored there.
A scheduled event expires promotional codes overnight, so a promotion ending is a property of the data rather than a job somebody has to remember to run.
The lifecycle is where the design has to hold together. A rider requests a trip against a fare estimate; a driver is offered it and accepts, or the rejection is recorded so the same offer is not made again; the trip progresses through its live states; a payment settles and the platform's commission is split from the driver's share; both sides rate each other; and the completed ride is archived.
Every one of those transitions has something enforcing it underneath — a constraint, a trigger, or a procedure — rather than an application function that a second caller could bypass.
Known-data KPI test
The chain names the states a ride moves through, read from the schema itself. Nothing here is a performance, throughput or usage claim — the repository shows a design, not a workload.
The admin dashboard's numbers are not assembled in JavaScript. Six views define them in SQL: active rides, top drivers, a per-city driver leaderboard, revenue by city and day, revenue by payment method, and refund and dispute totals.
The benefit is that a definition exists once. "Revenue by city" means one thing, written in one place, and the reporting screen cannot quietly disagree with any other question asked of the same data.
Completed rides and their settled payments
City × day, and by payment method
Six views the admin dashboard reads directly
One definition, in SQL
A view means the meaning of a number is written once. The reporting screen cannot drift from any other question asked of the same rows, because there is no second implementation for it to drift away from.
fare − commission → driver netPutting the rules in the database is not free, and the project is a fair place to see both sides of it. Constraints and triggers apply to every caller — including a future one written by somebody who never read the API — and they survive an application bug rather than being defeated by it.
The cost is that the system's logic is split across two languages, that a trigger is markedly harder to unit-test than a function, and that debugging a rejected write means crossing a boundary to find out which layer said no.
For a project whose subject is relational design, the trade lands on the right side. For a system expected to change weekly, it would be a harder argument to make.
Rules in the database
What it costs
Each role gets its own dashboard rather than one interface with things hidden. A rider books and pays; a driver works and earns; an administrator verifies, prices, adjudicates and reports. The workflows barely overlap, and pretending otherwise would have produced a screen that served none of them well.
Underneath they share a shell, an API wrapper and a state module, so the three surfaces stay consistent without being the same page wearing different navigation.
Rider
Driver
Administrator
Money
Trust & safety
Shared shell
Three role surfaces served by one Express application. The repository is public, so this list is checkable rather than asserted.
The project was deployed as one Node service on Railway, connected over TLS to managed MySQL at Aiven, with the certificate supplied as configuration rather than committed to the repository.
A bundled initialization script applies the committed SQL — schema, then procedures and triggers, then the bootstrap reference data — against whichever database is configured. That is what makes the project restartable: the database is reproducible from source rather than from a backup somebody has to still be holding.
This is evidence that the project can be connected to hosted infrastructure. It is not a claim of production operations experience, and the hosted demo is currently offline.
Browser
Three role entry points
Railway
Single Node service
Express server
API and static files
Connection pool
TLS to the provider
Aiven
Managed database
MySQL 8
The order followed the dependency, not the demo. The schema and its constraints came before any endpoint existed, the procedures and triggers before the API that calls them, and the dashboards last — because a surface built over rules that are not yet settled has to be rebuilt when they are.
Sixteen tables and twenty-nine foreign keys, before any endpoint existed.
Procedures, triggers and constraints, so invalid state is rejected at the store.
An Express layer over the procedures, with sessions and role checks.
Rider, driver and admin dashboards over one shared client shell.
Views for revenue, leaderboards and disputes, read straight by the admin screens.
A single Node service on Railway against managed MySQL, reproducible from committed SQL.
Because the repository is public, the left column below is unusually strong for a portfolio: none of it has to be taken on trust. Anyone can open the SQL and count.
The right column is the honest other half. There are no automated tests — the committed verification is a file of SQL queries run by hand — nothing has been measured under load or concurrency, the hosted demo no longer answers, and the contribution split between the two authors is not written down anywhere, so it is not asserted here either.
Next proof
The repository, its schema, its SQL logic and its entity-relationship diagram are all public and available. The hosted demo is not: all three role entry points return a 404, which I checked rather than assumed.
The project can still be brought up from a clean start. The committed SQL and the bundled initialization script establish a fresh database, after which a local or hosted instance can be pointed at it.
The repository is public, so everything on this page can be checked directly: 16 tables joined by 29 foreign keys, two stored procedures, eight triggers, six reporting views, a scheduled event, four database roles with distinct grants, and 43 Express routes serving three role surfaces.
The hosted demo no longer responds. There are no automated tests — verification is a set of SQL queries run by hand — and no load, concurrency or scale testing exists anywhere in the project. The split of work between Rafay Khattak and Muhammad Umar Nadeem is not documented, so no division of credit is claimed here.
The project runs as a single Express service that serves three role surfaces and talks to MySQL 8 over TLS. Fare calculation, driver assignment, commission splits, rating averages and ride archival are stored procedures and triggers rather than application code. It was previously deployed on Railway against managed MySQL; that demo no longer responds, and the bundled initialization script can still bring up a fresh database from the committed SQL.