Why PostgreSQL Over MongoDB?

Commute Matcher is a relationship-driven platform. Its core entities are closely connected:

• A user can participate as a driver or passenger.
• A driver creates a route.
• A passenger books a seat on a route.
• A booking belongs to both a passenger and a specific route.
• A route is connected to the driver who created it.
• Payments, verification records, reviews, and future notifications will connect to users, routes, and bookings.

The platform therefore needs to maintain clear and reliable relationships between its records.

MongoDB was considered because it supports flexible document structures and can be useful when application data is unstructured or likely to change frequently.

However, most of Commute Matcher's core data is structured and relational. The product benefits more from clearly defined relationships and constraints than from document flexibility.

PostgreSQL allows the system to enforce relationships through foreign keys.

For example, the created_by field in the routes table references an existing record in the users table. This prevents a route from being associated with a user who does not exist.

This is particularly important because Commute Matcher is a trust-focused product. The system must be able to determine:

• who created a route;
• who booked a seat;
• who made or received a payment;
• who completed verification; and
• who participated in a commute.

Maintaining these relationships supports accountability, auditing, safety investigations, and future dispute resolution.

PostgreSQL also supports the platform's matching requirements. Route matching involves filtering and joining data based on origin, destination, departure time, user role, seat availability, and eventually verification status.

SQL provides a clear and reliable way to perform these operations across related tables.

Choosing PostgreSQL introduces a more structured schema. Changes to the data model may require database migrations and more careful planning than they would in a flexible document database.

MongoDB could make it easier to store rapidly changing or loosely structured data. However, that flexibility could also make it more difficult to consistently enforce relationships between users, routes, bookings, and payments.

For the MVP, stronger consistency and relational integrity were considered more important than schema flexibility.

The database is organised around related tables such as:

• users
• routes
• bookings
• payments
• verification records

Foreign keys are used to connect these records. For example:

• routes.created_by references users.id;
• a booking references both a passenger and a route; and
• payment records can be connected to the relevant booking and user.

These constraints help prevent incomplete, invalid, or disconnected records from entering the system.