Skip to main content

Database

ThePlugg uses PostgreSQL as the primary database with Prisma ORM.

Schema Overview​

Core Models​

  • User - Base user account
  • ServiceSeeker - Service seeker profile
  • Provider - Provider profile
  • Job - Job listings and bookings
  • Review - Ratings and reviews
  • Category - Service categories
  • Service - Individual services
  • Conversation - Chat conversations
  • Message - Chat messages
  • Otp - OTP verification codes
  • SmsRateLimit - SMS rate limiting tracking

Relationships​

  • User has one ServiceSeeker or Provider profile
  • User can have many Jobs (as service seeker)
  • Provider can have many Jobs (as provider)
  • Job has one Provider and one User (service seeker)
  • User can have many Reviews (as rater or rated)
  • Job can have Reviews

Prisma Setup​

Generate Client​

npm run prisma:generate

Run Migrations​

npm run prisma:migrate

Open Prisma Studio​

npm run prisma:studio

Schema File​

The database schema is defined in:

prisma/schema.prisma

Common Queries​

Find User by Phone​

const user = await prisma.user.findUnique({
  where: { phone: '+264812345678' },
  include: {
    serviceSeeker: true,
    provider: true,
  },
});

Get Provider Jobs​

const jobs = await prisma.job.findMany({
  where: { providerId: 'provider-id' },
  include: {
    user: {
      include: { serviceSeeker: true },
    },
  },
});

Get User Conversations​

const conversations = await prisma.conversation.findMany({
  where: {
    participants: {
      some: { userId: 'user-id' },
    },
  },
  include: {
    participants: {
      include: { user: true },
    },
    messages: {
      orderBy: { sentAt: 'desc' },
      take: 50,
    },
  },
});

Migrations​

All migrations are stored in:

prisma/migrations/

Each migration contains SQL files that modify the database schema.