masaruk Logo

Data Architecture – PostgreSQL

Relational data architecture for the MASARUK platform using PostgreSQL

1. Purpose of This Document

This document defines the relational data architecture for the MASARUK platform using PostgreSQL. It describes:

  • How entities are structured
  • How relationships are enforced
  • Naming conventions (aligned with guidelines)
  • Constraints and indexes
  • Lifecycles and soft-deletion
  • How the DB aligns with the domain model and backend Node.js API (Prisma ORM)

The data model here matches exactly what was extracted from all raw UI screens (Admin + Provider + Mobile + Web).

2. Architectural Principles

2.1 Normalized Relational Schema (3NF+)

  • No duplicated data
  • Foreign keys enforce relationships
  • Junction tables for many-to-many
  • Referential integrity is mandatory

2.2 Naming Conventions

  • Tables → snake_case plural
  • Fields → snake_case
  • Primary key → id (bigint)
  • Foreign key → {entity}_id
  • Status fields → enums (text-based or check constraints)
  • Timestamps → created_at, updated_at, deleted_at

2.3 Soft Delete

All business-critical tables support soft delete: deleted_at TIMESTAMP NULL

2.4 Auditing

created_byupdated_by

Used for Admin & Provider accountability.

3. Core Entities & Tables

Below is the minimal canonical set derived from all screens + flows.

3.1 users

Represents end users + admin + provider staff.

idfull_nameemailphonepassword_hashrole (enum: SUPER_ADMIN, ADMIN, PROVIDER_ADMIN, PROVIDER_STAFF, CUSTOMER)provider_id (nullable: only for provider accounts)avatar_urlcreated_at / updated_at / deleted_at

3.2 providers

Tour companies (B2B portal entities).

idnamelogo_urlcr_numbercontact_phoneowner_namestatus (ACTIVE / INACTIVE)created_at / updated_at / deleted_at

3.3 trips

The core marketplace product.

idprovider_idnametype (UMRAH / TOURISM)destinationdaysnightsdescriptionprice_per_persondiscounted_price (nullable)start_dateend_datestatus (ACTIVE / INACTIVE / ARCHIVED)rating_average (computed, nullable)created_at / updated_at / deleted_at

Relations:

  • belongs_to provider
  • has_many bookings
  • has_many trip_hotels
  • has_many trip_buses
  • has_many trip_rest_stops
  • has_many ratings

3.4 hotels

idprovider_idnamecitystars (integer: 1–7)phoneaddressmaps_urlstatus (ACTIVE / INACTIVE)created_at / updated_at / deleted_at

3.5 buses

idprovider_idmodelyearplate_numberseat_capacitydriver_namedriver_phonestatus (ACTIVE / INACTIVE / UNDER_MAINTENANCE)created_at / updated_at / deleted_at

3.6 bookings

iduser_idtrip_idbooking_referencestatus (PENDING / CONFIRMED / CANCELLED / COMPLETED)passengers_counttotal_amountvat_amountcontact_phonecontact_emailspecial_requestscreated_at / updated_at / deleted_at

3.7 payment_transactions

idbooking_id (nullable)ad_campaign_id (nullable)amountcurrencymethodprovider (hyperpay/stcpay/etc.)status (PENDING / PAID / FAILED / REFUNDED)gateway_referencecreated_at

4. Key Relationships

  • users → providers (provider_id FK)
  • trips → providers (provider_id FK)
  • bookings → users, trips (FKs)
  • booking_passengers → bookings (booking_id FK)
  • payment_transactions → bookings / ad_campaigns
  • hotels, buses, rest_stops → providers
  • trip_hotels, trip_buses, trip_rest_stops → junction tables
  • ratings → bookings, trips, hotels, buses, rest_stops

5. Indexes & Performance

  • Primary indexes on all id fields
  • Foreign key indexes (provider_id, user_id, trip_id, booking_id)
  • Status fields indexed for filtering
  • Composite indexes for common queries (provider_id + status, trip_id + status)
  • Full-text search indexes where needed

8. Summary

The MASARUK data architecture is:

PostgreSQL-basedFully normalized (3NF+)Consistent with domain modelSoft-delete enabledAudit-readyIndexed for performanceReady for scale