Majestic Transport

Appearance

Database Schema (Tables & Entities) ​

The system uses a relational PostgreSQL database managed through Drizzle ORM. Below is the mapping of core tables and their corresponding business entities.

🏗️ Core Tables ​

organization (Organizations) ​

The root multi-tenancy entity. All users and data belong to a specific organization.

  • id: (UUID) Primary Key.
  • name: (Text) Organization name.
  • slug: (Text) Unique URL identifier.

companies (Companies & Partners) ​

Represents business units or subcontracting partners within an organization.

  • id: (UUID) Primary Key.
  • name: (Text) Full company name.
  • address: (JSONB) Structured object containing line_1, city, postcode, state, and country.
  • logo: (UUID) Reference to the attachments table.

user & drivers (Personnel) ​

A driver is an extension of a standard user account.

  • user: Stores core identity (name, email, role, phone, national ID).
  • drivers: Stores driver-specific compliance data (License expiry, bank QR).

cars (Fleet Assets) ​

Physical vehicles used in the system.

  • plateNumber: (Text) Unique identifier.
  • ownerId: (UUID) Links to the companies table.
  • type: (Enum) premium or regular.
  • insuranceInfo & roadTaxInfo: (Text) Compliance fields.

trips (Operations) ​

The primary transaction table representing a transport service.

  • status: (Enum) unassigned, assigned, in_progress, done, cancelled.
  • pickupAt: (Timestamp) Scheduled time.
  • guestId: (UUID) Reference to the guest being transported.
  • routeId: (UUID) Reference to pre-defined route data.
  • carId & driverId: (UUID) The resources currently assigned.

car_handovers (Accountability) ​

Logs the physical transfer of a vehicle.

  • type: (Enum) pickup or drop-off.
  • tripId: (UUID) Optional link to the relevant trip.
  • carId & driverId: (UUID) Who took/returned which car.

🔒 Shared Metadata (Lifecycle) ​

Nearly all operational tables include the following standard fields:

  • id: (UUID) Unique Identifier.
  • createdAt: (Timestamp) Record creation time.
  • updatedAt: (Timestamp) Last modification time.
  • deletedAt: (Timestamp) Soft-delete flag (record exists but is hidden from the UI).