Database & Prisma

Overview

This app uses PostgreSQL hosted on Supabase, and Prisma ORM for modeling data, migrations, and type-safe access from the backend.

Prisma is configured via:

prisma/schema.prisma

It generates a client with full TypeScript types, powered by:

import { PrismaClient } from "@prisma/client";
const db = new PrismaClient();

Datasource

datasource db {
  provider  = "postgresql"
  url       = env("DATABASE_URL")
  directUrl = env("DIRECT_URL")
}

• DATABASE_URL is optimized for pooling (used at runtime)
• DIRECT_URL is used for migrations and seeding

Key Models

User

Represents app users. Auth is managed by Supabase, but user data is extended via this model.

model User {
  id             String   @id @default(cuid())
  email          String   @unique
  name           String?
  image          String?
  createdAt      DateTime @default(now())
  ...
}

Listing

A marketplace item that can be booked.

model Listing {
  id          String   @id @default(cuid())
  title       String
  description String
  userId      String
  user        User     @relation(fields: [userId], references: [id])
  ...
}

Each listing belongs to a User.

Booking

Represents a user’s booking for a listing.

model Booking {
  id        String   @id @default(cuid())
  userId    String
  listingId String
  status    BookingStatus
  ...
}

Relationships:

• userId → User
• listingId → Listing

Category

Used to categorize listings. Each listing references a categoryId.

Payment

Handles payment records:

model Payment {
  id          String
  amount      Float
  status      PaymentStatus
  bookingId   String
  ...
}

Other Supporting Models

• ListingPricingOptions: Handles per-hour/lump-sum pricing rules
• ListingOperatingHours: Availability per weekday
• ListingHealthAndSafety: Metadata for safety rules
• Message: For user-to-user conversations
• Review: For post-booking feedback
• BillingAddress: Related to payments or user data

Migrations

Prisma tracks DB changes in the /prisma/migrations folder.

To push schema updates:

yarn prisma db push

Or use migrations:

yarn prisma migrate dev

To reset the database:

yarn prisma migrate reset

Seeding

If a seed.ts script is present:

yarn prisma db seed

You can define mock users, categories, or listings for local testing.

Summary

• All data is modeled in schema.prisma
• Prisma connects to Supabase via environment variables
• Relationships are explicitly defined via @relation
• You get fully typed DB access and auto-generated input/output types

Entity Relationship Design

One-to-Many

• User → Listing, Booking, Message, Review
• Listing → Booking, Review, Message
• Category → Listing

One-to-One

• User → BillingAddress
• Listing → ListingPricingOptions, ListingOperatingHours, ListingHealthAndSafety

This structure supports extensibility while maintaining relational integrity.

Model Highlights

Reviews

After a booking, users can leave reviews for listings or hosts.

model Review {
  id        String   @id @default(cuid())
  userId    String
  listingId String
  rating    Int
  comment   String?
  createdAt DateTime @default(now())
}

This enables community feedback and quality control.

Messages

Messages enable buyer–seller chat:

model Message {
  id        String   @id @default(cuid())
  senderId  String
  receiverId String
  content   String
  createdAt DateTime @default(now())
}

Indexes or composite keys can be added for chat thread performance.

Listing Meta Tables

• ListingPricingOptions: allows dynamic pricing rules
• ListingOperatingHours: supports weekday-based time blocks
• ListingHealthAndSafety: useful for niche compliance or tags

These use one-to-one relationships to avoid bloating the base Listing table.

Tips for Scaling

• Add indexes to common filters (e.g., listingId, userId)
• Use enum fields (BookingStatus, PaymentStatus) for strict type control
• Use @updatedAt to track last activity (e.g., messaging, booking)
• Consider separating AuditLog or Activity tables if user events are tracked
• Archive old records using deletedAt + soft delete pattern

Example Usage in Backend

// Create a new listing for current user
await db.listing.create({
  data: {
    title: "New Item",
    description: "Sample description",
    userId: user.id,
  },
});

Example Zod Validation Schema

const CreateListingSchema = z.object({
  title: z.string().min(3),
  description: z.string().max(500),
  price: z.number().positive(),
});

Supabase Considerations

Although Supabase manages authentication, all Prisma logic assumes a backend connection using service_role and row-level security (RLS) is turned off or handled carefully.

Make sure to scope actions by userId from the Supabase session:

const user = await getUserOrThrow(ctx);
const listings = await db.listing.findMany({
  where: { userId: user.id },
});

Summary

• Strong relational design supports a real-world booking marketplace
• Extensible sub-models handle specialized listing attributes
• Prisma + Supabase combo enables rapid iteration with safety
• Auth via Supabase, logic via Prisma = full stack synergy