API Database Schema

Database: walkthroughnepalORM: Prisma 7.1 + @prisma/adapter-pgMigrations: 17 migrations in prisma/migrations/

Models

Admin

Administrator accounts for the main platform admin panel (supplier approval, activity moderation, user management).

Field	Type	Notes
id	Int (PK, autoincrement)
username	String (unique)	Login username
email	String (unique)	Login email
password	String	bcrypt-hashed
role	AdminType enum	SUPERADMIN, MODERATOR, or SUPPORT
createdAt	DateTime
updatedAt	DateTime

User

Registered travelers who can book activities, leave reviews, and manage wishlists.

Field	Type	Notes
id	Int (PK, autoincrement)
role	String	Default "USER"
email	String (unique)	Login email
password	String	bcrypt-hashed
phone	String?
dateOfBirth	DateTime?
emailVerified	Boolean	Default false
name	String	Display name
username	String (unique)	Public username
image	String?	Avatar URL
bio	String?
meta	Json?	Additional metadata

Relations: Booking[], Review[], wishlists[]

Supplier

Activity providers who register and manage their offerings.

Field	Type	Notes
supplierId	Int (PK, autoincrement)
type	SupplierType enum	INDIVIDUAL or COMPANY
fullName	String	Display name
email	String (unique)	Login email
password	String	bcrypt-hashed
phone	String
address	String
legalName	String	Business legal name
professionalLicenseNumber	String
companyDescription	String
companyLogo	String?
isVerified	Boolean	Default false — admin must verify
meta	Json?
bio	String?
license	String?	License document URL
languages	String[]	Spoken languages

Relations: activities[]

Activity

The core product — treks, tours, and activities available for booking.

Field	Type	Notes
id	Int (PK, autoincrement)
title	String
shortDescription	String?	Brief summary
fullDescription	String?	Detailed HTML description
highlights	String[]	Key features
locations	String[]
keywords	String[]	SEO keywords
inclusions	String[]	What's included
exclusions	String[]	What's excluded
price	Float?	Base price
published	Boolean	Default false
status	ActivityStatus enum	APPROVED, REJECTED, or PENDING_APPROVAL
duration	String?	e.g., "5 days"
guestCapacity	Int?	Maximum group size
images	String[]	Array of image URLs
itinerary	Json?	Day-by-day plan
meetingPoint	String?
whatToBring	String[]
additionalInfo	Json?
difficultyLevel	DifficultyLevel?	EASY, MODERATE, HARD, or EXTREME
isFeatured	Boolean
popularityScore	Int	Computed by cron job
views	Int	Page view counter
averageRating	Float	Computed from reviews
bookingsCount	Int	Incremented on confirmed booking
canonicalPath	String?	SEO canonical URL
urlHistory	String[]	Old URLs for redirects

Relations: city, region, supplier, tripCategory, tripType, availability, Booking[], Review[], wishlists, featuredTags[]

Indexes: published, status, supplierId, cityId, regionId, tripCategoryId, tripTypeId, difficultyLevel, popularityScore, averageRating, views, isFeatured

Booking

A traveler's booking for a specific activity.

Field	Type	Notes
id	Int (PK, autoincrement)
activityId	Int (FK → Activity)
userId	Int (FK → User)
startDate	DateTime	Trip start
days	Int	Duration
groupSize	Int	Number of travelers
totalPrice	Decimal
status	BookingStatus enum	PENDING_PAYMENT, CONFIRMED, CANCELLED, or FAILED
endDate	DateTime	Computed from startDate + days

Unique constraint: [userId, activityId, startDate] Relations: activity, user, payment

Payment

Payment records linked to bookings.

Field	Type	Notes
id	Int (PK, autoincrement)
bookingId	Int (FK, unique → Booking)	One payment per booking
amount	Decimal
currency	CURRENCY enum	USD or NPR
status	PaymentStatus enum	PENDING, SUCCESS, FAILED, or REFUNDED
transactionId	String?
provider	PaymentProvider enum	STRIPE or PAYPAL (only Stripe implemented)
stripePaymentIntentId	String?	Stripe reference

Review

Post-booking reviews left by users.

Field	Type	Notes
id	Int (PK, autoincrement)
rating	Int	1–5
text	String	Review content
reviewerId	Int (FK → User)
activityId	Int (FK → Activity)

Availability

Activity availability calendar.

Field	Type	Notes
id	String (cuid)
unavailableDates	DateTime[]	Dates the activity is not available
availableRange	Json?	Optional date range for availability
activityId	Int (FK, unique → Activity)	One availability record per activity

TripCategory

Classification for activities (e.g., Trekking, Tour, Cultural).

Field	Type	Notes
id	String (cuid)
categoryHandle	String (unique)	URL-safe slug
categoryName	String?	Display name
categoryImage	String?

TripType

Activity type (e.g., Group, Private).

Field	Type	Notes
id	String (cuid)
tripTypeHandle	String (unique)	URL-safe slug
tripTypeName	String?	Display name
tripTypeImage	String?

City

Cities where activities operate. Also used by Blog (legacy) — now shared with CMS.

Field	Type	Notes
id	String (cuid)
cityName	String
cityHandle	String (unique)	URL-safe slug
cityImage	String?

Region

Geographic regions containing cities.

Field	Type	Notes
id	String (cuid)
regionName	String
regionHandle	String
regionImage	String?

NewsletterSubscriber

Email newsletter subscriptions.

Field	Type	Notes
id	String (cuid)
email	String (unique)
unsubSecret	String (unique)	For one-click unsubscribe
isActive	Boolean	Default true
unsubscribedAt	DateTime?

Wishlist

User-saved activities.

Field	Type	Notes
id	String (cuid)
itemId	Int (FK, unique → Activity)
userId	Int (FK → User)
createdAt	DateTime

Unique constraint: [userId, itemId]

FeaturedTag

Tags for promotional/featured content placement.

Field	Type	Notes
id	String (cuid)
name	String
slug	String (unique)
description	String?

ActivityFeaturedTag

Junction table for M:N relationship between Activity and FeaturedTag.

Field	Type	Notes
activityId	Int (FK → Activity)	Composite PK
featuredTagId	String (FK → FeaturedTag)	Composite PK

Media

Media library for uploaded files.

Field	Type	Notes
id	String (cuid)
url	String
filename	String
mimeType	String?
fileSize	Int?
category	MediaCategory enum	UNRELATED or SUPPLIER_DOC
relatedId	String?	Polymorphic reference

LegalDocument

Legal and policy documents.

Field	Type	Notes
id	String (cuid)
category	LegalCategory enum (unique)	One of: TERMS_AND_CONDITIONS, PRIVACY_POLICY, CANCELLATION_POLICY, SUPPLIER_AGREEMENT, COMMUNITY_GUIDELINES, PLATFORM_TERMS_OF_SERVICE, GUEST_TERMS_AND_CONDITIONS
title	String?
content	String	HTML content
slug	String? (unique)
published	Boolean
meta	Json?

Enums

enum AdminType { SUPERADMIN, MODERATOR, SUPPORT }
enum SupplierType { INDIVIDUAL, COMPANY }
enum DifficultyLevel { EASY, MODERATE, HARD, EXTREME }
enum ActivityStatus { APPROVED, REJECTED, PENDING_APPROVAL }
enum BookingStatus { PENDING_PAYMENT, CONFIRMED, CANCELLED, FAILED }
enum PaymentStatus { PENDING, SUCCESS, FAILED, REFUNDED }
enum CURRENCY { USD, NPR }
enum PaymentProvider { STRIPE, PAYPAL }
enum MediaCategory { UNRELATED, SUPPLIER_DOC }
enum LegalCategory { TERMS_AND_CONDITIONS, PRIVACY_POLICY, CANCELLATION_POLICY, SUPPLIER_AGREEMENT, COMMUNITY_GUIDELINES, PLATFORM_TERMS_OF_SERVICE, GUEST_TERMS_AND_CONDITIONS }

Removed Models (Editorial Migration)

The following models existed in the API schema but were removed during the editorial migration. The tables remain in the database but are no longer managed by Prisma:

• Blog — replaced by Post in CMS database
• BlogCategory — replaced by PostCategory in CMS database
• Author — migrated to CMS database
• BlogGenerationImport — replaced by GenerationBatch in CMS
• BlogGenerationImportRow — replaced by simplified generation tracking
• BlogGenerationJob — replaced by simplified generation tracking
• BusinessLink — feature removed
• PostType enum — replaced by category-based type system
• ImportStatus, GenerationStatus, JobMode, JobStatus enums — no longer needed