Skip to main content

Database Schema

Overview of the database schema and relationships.

Core Tables​

User​

Base user account table.

Fields:

  • id (UUID) - Primary key
  • phone (String) - Unique phone number
  • role (Enum) - PROVIDER, SERVICE_SEEKER, ADMIN
  • status (Enum) - ACTIVE, SUSPENDED, BANNED
  • onesignalSubscriptionId - Push notification ID
  • lastConnectedAt - Last active timestamp

Relationships:

  • Has one ServiceSeeker or Provider profile
  • Has many Jobs
  • Has many Reviews
  • Has many Conversations
  • Has many Locations

ServiceSeeker​

Service seeker profile.

Fields:

  • id, userId, firstName, lastName, email, profilePhoto

Relationships:

  • Belongs to User

Provider​

Provider profile.

Fields:

  • id, userId, firstName, lastName, email, profilePhoto
  • isVerified - Verification status
  • isOnline - Online/offline status
  • businessName - Business name
  • serviceLocation, serviceType

Relationships:

  • Belongs to User
  • Has many Jobs
  • Has many Reviews
  • Has many Qualifications
  • Has many ProviderServices

Job​

Job/booking record.

Fields:

  • id, userId, providerId, serviceId, categoryId
  • description, location, dateTime
  • status - Job status enum
  • estimatedCost, actualCost
  • isCallOut - Boolean
  • completedAt

Relationships:

  • Belongs to User (service seeker)
  • Belongs to Provider (optional)
  • Has many Reviews

Conversation & Message​

Chat messaging system.

Conversation:

  • id, jobId (optional), createdAt, updatedAt

Message:

  • id, conversationId, senderId, content, sentAt, readAt

Otp​

OTP verification codes.

Fields:

  • id, phone, code, expiresAt, verified, createdAt

SmsRateLimit​

SMS rate limiting tracking.

Fields:

  • id, phone, count, lastSentAt, resetDate, createdAt, updatedAt

Constraints:

  • Unique constraint on phone + resetDate

Entity Relationship Diagram​

User
├── ServiceSeeker (optional)
├── Provider (optional)
├── Jobs[]
├── Reviews[] (as rater and rated)
├── Conversations[]
├── Locations[]
└── Documents[]

Provider
├── Jobs[]
├── Reviews[]
├── Qualifications[]
└── ProviderServices[]

Job
├── User (service seeker)
├── Provider (optional)
└── Reviews[]

Conversation
├── Participants[]
└── Messages[]

Category
└── Services[]

Indexes​

Key indexes for performance:

  • phone on User (unique)
  • userId, providerId, serviceId on Job
  • phone, code, verified on Otp
  • phone, resetDate on SmsRateLimit

Migration Strategy​

  1. Prisma migrations track schema changes
  2. All migrations stored in prisma/migrations/
  3. Run migrations before deploying new code
  4. Test migrations on staging first