Database schema

Tripfy Africa uses MySQL 8.0+ with the utf8mb4 character set and the InnoDB storage engine. All schema changes are managed through Laravel migrations located in database/migrations/. Engine: MySQL 8.0+ · Charset: utf8mb4_unicode_ci · Storage engine: InnoDB

Core tables

users

Stores every registered traveler account. A user record is the root identity for bookings, transactions, reviews, and guide profiles.

Column	Type	Notes
`id`	`bigint unsigned`	Primary key
`firstname`	`varchar(255)`
`lastname`	`varchar(255)`
`username`	`varchar(255)`	Nullable
`email`	`varchar(191)`	Unique
`email_verified_at`	`timestamp`	Nullable
`password`	`varchar(255)`	Bcrypt hash
`phone`	`varchar(255)`	Nullable
`country`	`varchar(255)`	Nullable
`country_code`	`varchar(255)`	Nullable
`balance`	`decimal(11,2)`	Wallet balance, default `0.00`
`image`	`varchar(255)`	Nullable
`image_driver`	`varchar(50)`	Storage driver (local / S3 / etc.)
`role`	`tinyint`	`0` = traveller, `1` = vendor/guide
`status`	`boolean`	`1` = active, `0` = suspended
`identity_verify`	`tinyint`	`0` not applied · `1` applied · `2` approved · `3` rejected
`address_verify`	`tinyint`	Same enum as `identity_verify`
`two_fa`	`boolean`	Two-factor auth enabled
`email_verification`	`boolean`	Email OTP required
`sms_verification`	`boolean`	SMS OTP required
`provider`	`varchar(191)`	OAuth provider (google / facebook)
`language_id`	`int`	FK → `languages.id`
`referral_id`	`int`	Self-referential referral
`last_login`	`datetime`
`last_seen`	`datetime`
`deleted_at`	`timestamp`	Soft-delete support
`created_at` / `updated_at`	`timestamp`

CREATE TABLE `users` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `role` tinyint NOT NULL DEFAULT 0 COMMENT '0=traveller, 1=vendor',
  `firstname` varchar(255) DEFAULT NULL,
  `lastname` varchar(255) DEFAULT NULL,
  `username` varchar(255) DEFAULT NULL,
  `email` varchar(191) NOT NULL,
  `password` varchar(255) NOT NULL,
  `balance` decimal(11,2) DEFAULT 0.00,
  `status` boolean NOT NULL DEFAULT 1,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

guides

A guide is a registered tour operator with their own profile, rating, and packages. Each guide record is independent of the users table but is linked via vendor_infos.

Column	Type	Notes
`id`	`bigint unsigned`	Primary key
`name`	`varchar(255)`	Guide display name
`code`	`varchar(255)`	Unique public code
`slug`	`varchar(255)`	URL-friendly identifier
`designation`	`varchar(255)`	Job title / role
`email`	`varchar(255)`	Unique login email
`phone`	`varchar(255)`	Nullable
`image`	`varchar(255)`	Nullable
`driver`	`varchar(255)`	Storage driver
`status`	`tinyint`	`1` = active, `0` = inactive
`years_of_experience`	`int`	Nullable
`tour_completed`	`int`	Nullable
`rating`	`decimal(10,2)`	Calculated average
`created_by`	`bigint unsigned`	Added by migration `2026_01_01_000001`
`created_at` / `updated_at`	`timestamp`

vendor_infos

Extends a guide’s account with subscription plan details and listing quota tracking.

Column	Type	Notes
`id`	`bigint unsigned`	Primary key
`vendor_id`	`int`	FK → `users.id` (the vendor user account)
`active_plan`	`int`	FK → `plans.id`
`current_plan_purchase_date`	`date`	Nullable
`current_plan_expiry_date`	`date`	Nullable
`posted_listing`	`int`	Total package listings created
`current_plan_posted_listing`	`int`	Listings created under the current plan
`badge_id`	`int`	Nullable
`auto_renew_current_plan`	`tinyint`	`1` = enabled, `0` = disabled
`avg_rating`	`decimal(10,2)`	Vendor average rating, updated by migration `2026_01_01_000001`
`facebook_link`	`varchar(255)`	Nullable social link
`twitter_link`	`varchar(255)`	Nullable social link
`linkedin_link`	`varchar(255)`	Nullable social link
`instagram_link`	`varchar(255)`	Nullable social link

packages

Tour packages created and owned by guides. Packages are the core product unit that travelers browse and book.

Column	Type	Notes
`id`	`bigint unsigned`	Primary key
`uid`	`varchar(255)`	Unique public identifier
`owner_id`	`int`	FK → `users.id` (vendor who owns the package)
`title`	`varchar(255)`
`slug`	`varchar(255)`	Unique URL slug
`category_id`	`bigint unsigned`	FK → `package_categories.id`
`destination_id`	`bigint unsigned`	FK → destinations
`price`	`decimal(10,2)`	Base price
`discount_price`	`decimal(10,2)`	Nullable
`max_persons`	`int`	Default `1`
`duration`	`int`	Trip length in days
`amenities`	`json`	Array of amenity IDs
`imagesUrl`	`json`	Array of image paths
`thumbnail`	`varchar(255)`
`status`	`tinyint`	`1` = active, `0` = inactive
`is_featured`	`tinyint`	`1` = featured
`avg_rating`	`decimal(3,2)`	Denormalised average
`total_reviews`	`int`	Denormalised count
`meta_title` / `meta_description` / `meta_keywords`	various	SEO fields

A FULLTEXT index covers title and description to power the search bar.

bookings

Records every booking made by a traveler for a package.

Column	Type	Notes
`id`	`bigint unsigned`	Primary key
`uid`	`varchar(255)`	Unique booking reference
`package_id`	`bigint unsigned`	FK → `packages.id`
`user_id`	`bigint unsigned`	FK → `users.id` (traveler)
`guide_id`	`bigint unsigned`	FK → `guides.id`
`total_person`	`int`	Default `1`
`total_price`	`decimal(10,2)`
`date`	`date`	Tour date
`time`	`time`	Nullable
`status`	`enum`	`pending` · `confirmed` · `completed` · `cancelled` · `refunded`
`payment_status`	`enum`	`pending` · `paid` · `failed` · `refunded`
`payment_method`	`varchar(50)`	Gateway code
`transaction_id`	`varchar(255)`	External reference
`cancelled_at`	`timestamp`	Nullable
`cancellation_reason`	`text`	Nullable
`confirmed_at`	`timestamp`	Nullable
`completed_at`	`timestamp`	Nullable

reviews

One review per user per package. Adding a review updates the avg_rating and total_reviews counters on the packages row.

Column	Type	Notes
`id`	`bigint unsigned`	Primary key
`package_id`	`bigint unsigned`	FK → `packages.id`
`user_id`	`bigint unsigned`	FK → `users.id`
`guide_id`	`bigint unsigned`	FK → `guides.id`
`rating`	`int`	1–5; `CHECK (rating BETWEEN 1 AND 5)`
`comment`	`text`	Nullable
`images`	`json`	Array of uploaded image paths
`status`	`tinyint`	`1` = published, `0` = hidden

A unique composite key on (package_id, user_id) prevents duplicate reviews.

Commerce tables

transactions

Audit ledger for every financial movement on the platform (bookings, deposits, payouts, refunds, commissions).

Column	Type	Notes
`id`	`bigint unsigned`	Primary key
`user_id`	`bigint unsigned`	FK → `users.id`
`transactional_id`	`int`	Polymorphic FK
`transactional_type`	`varchar`	Polymorphic type (e.g. `App\Models\Booking`)
`amount`	`double`	Transaction amount
`balance`	`double`	User balance after transaction
`amount_in_base`	`double`	Amount in platform base currency
`charge`	`decimal(11,2)`	Fee applied
`trx_type`	`varchar`	`+` credit or `-` debit
`remarks`	`varchar`	Human-readable description
`trx_id`	`varchar`	Unique transaction reference

deposits

Records wallet top-up requests from users via payment gateways.

Column	Type	Notes
`id`	`bigint unsigned`	Primary key
`user_id`	`bigint unsigned`	FK → `users.id`
`payment_method_id`	`bigint unsigned`	FK → `gateways.id`
`payment_method_currency`	`varchar`	Currency code used
`amount`	`decimal(18,8)`	Amount in base currency
`percentage_charge`	`decimal(18,8)`
`fixed_charge`	`decimal(18,8)`
`payable_amount`	`decimal(18,8)`	Actual amount charged to user
`btc_amount` / `btc_wallet`	various	For crypto gateways
`trx_id`	`varchar`	Transaction reference
`status`	`tinyint`	`0` pending · `1` success · `2` request · `3` rejected
`note`	`text`	Admin note

payouts

Records guide withdrawal requests from their platform balance.

Column	Type	Notes
`id`	`bigint unsigned`	Primary key
`user_id`	`bigint unsigned`	FK → `users.id`
`payout_method_id`	`bigint unsigned`	FK → `payout_methods.id`
`payout_currency_code`	`varchar`
`amount`	`decimal(18,8)`	Requested amount
`charge`	`decimal(18,8)`	Fee deducted
`net_amount`	`decimal(18,8)`	Amount received after fees
`amount_in_base_currency`	`decimal(18,8)`
`trx_id`	`varchar(50)`	Reference
`status`	`boolean`	`0` = pending, `1` = processed
`feedback`	`text`	Admin feedback

gateways

Configuration record for each supported payment gateway (Stripe, Razorpay, manual, etc.).

Column	Type	Notes
`id`	`bigint unsigned`	Primary key
`code`	`varchar(191)`	Unique gateway code
`name`	`varchar(191)`	Display name
`sort_by`	`int`	Display order
`image`	`varchar(191)`	Logo path
`status`	`tinyint`	`0` inactive · `1` active
`parameters`	`text`	JSON of API keys / credentials
`currencies`	`text`	Supported currency list
`environment`	`enum`	`test` or `live`
`is_sandbox`	`tinyint`	`1` = sandbox mode
`is_manual`	`tinyint`	`1` = manual bank-transfer gateway

coupons

Discount coupons that can be applied at checkout.The Coupon model (app/Models/Coupon.php) manages coupon validation. Key fields include the coupon code, discount type (percentage or fixed), minimum order amount, usage limits, and expiry date.

Content tables

blogs and blog_categories

blogs stores each article’s metadata and SEO settings. Localised content (title, body) lives in the companion blog_details table.

Column	Type	Notes
`id`	`bigint unsigned`	Primary key
`category_id`	`int`	FK → `blog_categories.id`
`blog_image`	`varchar`	Cover image path
`blog_image_driver`	`varchar`	Storage driver
`status`	`boolean`	`1` = published
`page_title`	`varchar`
`meta_title` / `meta_keywords` / `meta_description`	various	SEO

blog_categories has only id, name (varchar 100), and slug (varchar 100).blog_comments links blog_id and user_id with a comment string and a status flag (1 published, 0 unpublished).

pages and page_details

pages holds static page shells (home, about, contact, etc.). The page_details table stores the localised section content for each page, keyed by page_id and language_id.

languages and contents

languages holds each supported locale (code, name, is_default, status). The contents / content_details tables store every translatable UI string, keyed by a data_key and language_id.

System tables

admins

Separate admin accounts with role-based access control, independent of the users table.

Column	Type	Notes
`id`	`bigint unsigned`	Primary key
`name`	`varchar(100)`
`username`	`varchar(50)`	Unique
`email`	`varchar(191)`	Unique
`password`	`varchar(191)`
`image` / `image_driver`	varchar	Avatar
`admin_access`	`text`	JSON permissions
`status`	`tinyint`	`1` = active
`last_login`	`varchar(50)`
`last_seen`	`datetime`

Default admin: [email protected] (set by database/seeders/AdminSeeder.php).

basic_controls

Single-row site-wide configuration table. Settings include branding, currency, notification toggles, reCAPTCHA, analytics, and more.

Column	Notes
`site_title`	Platform name shown in the browser tab
`primary_color` / `secondary_color`	Hex values; changing these and saving regenerates `tripfy.css`
`base_currency` / `currency_symbol`	e.g. `USD` / `$`
`sender_email` / `sender_email_name`	Default From address for outgoing email
`registration`	`1` = public registration open
`push_notification` / `email_notification` / `sms_notification`	Channel toggles
`email_verification` / `sms_verification`	Require verification on sign-up
`google_recaptcha`	`1` = reCAPTCHA active on public forms
`is_maintenance_mode`	`1` = shows maintenance page
`is_force_ssl`	`1` = redirect all HTTP to HTTPS
`tawk_id` / `tawk_status`	Tawk.to live chat
`logo` / `favicon` / `admin_logo`	Asset paths with driver columns

notification_templates

Editable templates for every platform notification event. Each row covers email, SMS, in-app, and push channels.

Column	Type	Notes
`id`	`bigint unsigned`	Primary key
`language_id`	`bigint unsigned`	FK → `languages.id`
`name`	`varchar`	Human-readable template name
`email_from`	`varchar`	Sender address for this template
`template_key`	`varchar`	Unique identifier (e.g. `NEW_BOOKING_RECEIVED`)
`subject`	`text`	Email subject line
`short_keys`	`text`	JSON map of `[[variable]]` placeholders
`email` / `sms` / `in_app` / `push`	`text`	Channel-specific body
`status`	`text`	JSON: `{"mail":"1","sms":"1","in_app":"1","push":"1"}`
`notify_for`	`boolean`	`0` = user notification · `1` = admin notification

Templates added by migration 2026_01_01_000001_tripfy_africa_fixes.php:

NEW_BOOKING_RECEIVED — sent to guide when a traveler books
BOOKING_ACCEPTED — sent to traveler when guide accepts
BOOKING_REJECTED — sent to traveler when guide declines
TOUR_COMPLETED — sent to traveler prompting a review

plans

Subscription plan definitions for guide accounts. Linked via vendor_infos.active_plan.The Plan model (app/Models/Plan.php) and PlanPurchase model (app/Models/PlanPurchase.php) handle plan-to-vendor associations and billing history.

Logging tables

activity_logs

The ActivityLog model (app/Models/ActivityLog.php) records significant user and admin actions for auditing. Common fields include user_id, log_type, message, and created_at.

user_logins

Records every successful login with geolocation and device data.

Column	Type	Notes
`id`	`bigint unsigned`	Primary key
`user_id`	`bigint unsigned`	FK → `users.id`
`longitude` / `latitude`	`varchar`	Geolocation
`country_code` / `country`	`varchar`
`location`	`varchar`	City / region string
`ip_address`	`varchar`
`browser` / `os`	`varchar`	User agent parsed values
`get_device`	`varchar`	Device type

support_tickets

Help-desk ticketing. Each ticket has a thread of messages (support_ticket_messages) and optional file attachments (support_ticket_attachments).

Column	Type	Notes
`id`	`bigint unsigned`	Primary key
`user_id`	`bigint unsigned`	FK → `users.id`
`ticket`	`varchar`	Unique ticket number
`subject`	`text`
`status`	`tinyint`	`0` open · `1` answered · `2` replied · `3` closed
`last_reply`	`timestamp`	Nullable

Entity relationships

Key relationship rules

One-to-many: A guide creates many packages; a user makes many bookings; a booking generates many transactions.
One-to-one: A guide has one vendor_infos record.
Polymorphic: transactions.transactional_id / transactional_type can reference any model (Booking, Deposit, Payout).
Cascade deletes: Foreign keys on packages, bookings, and transactions are defined with ON DELETE CASCADE so removing a parent record cleans up child rows automatically.
Soft deletes: The users table has a deleted_at column; records are never hard-deleted from production.

Get Started

Requirements & Design

Backend

Frontend

Database

Deployment

Troubleshooting

Core tables

Commerce tables

Content tables

System tables

Logging tables

Entity relationships

Key relationship rules

Build docs developers (and LLMs) love

Get Started

Requirements & Design

Backend

Frontend

Database

Deployment

Troubleshooting

​Core tables

​Commerce tables

​Content tables

​System tables

​Logging tables

​Entity relationships

​Key relationship rules

Build docs developers (and LLMs) love

Core tables

Commerce tables

Content tables

System tables

Logging tables

Entity relationships

Key relationship rules