Designing Scalable PostgreSQL Schemas with Prisma ORM: Lessons from Real Projects

When building web applications for businesses with appointment calendars and transactional billing, data integrity is paramount. While document databases like MongoDB offer rapid initial prototyping, relational databases like PostgreSQL remain the gold standard when your data inherently consists of relations.

Why Relational Integrity Still Wins

Consider an appointment booking model. An appointment connects a customer, a staff member, a specific room or chair, and multiple services. If a staff member leaves or a service price changes, your historical invoices must remain immutable while future availability updates instantly. Foreign key constraints and relational schemas make this trivial to enforce.

Preventing Double-Bookings at the Database Level

Never rely solely on frontend validation to prevent double-bookings. If two operators book the same slot at the exact same millisecond, both frontend checks will pass.

In PostgreSQL, we enforce concurrency safety through composite unique constraints or exclusion constraints:

model Appointment {
  id          String   @id @default(cuid())
  staffId     String
  startTime   DateTime
  endTime     DateTime

staff Staff @relation(fields: [staffId], references: [id])

@@index([staffId, startTime]) } ```

Before creating a new appointment row, a single transactional query checks for overlapping intervals:

const hasOverlap = await tx.appointment.findFirst({
  where: {
    staffId,
    status: { not: "CANCELLED" },
    AND: [
      { startTime: { lt: requestedEnd } },
      { endTime: { gt: requestedStart } }
    ]
  }

if (hasOverlap) { throw new Error("Stylist is already booked for this time window"); } ```

Indexing What Actually Matters

It is tempting to throw an index on every single column in Prisma schema, but indexes slow down write operations. Only index: 1. Foreign key columns used in frequent `JOIN` operations. 2. Filter columns queried in your dashboard date ranges (e.g. `createdAt`, `startTime`). 3. Unique lookups like `slug`, `email`, or `phone`.

Safe Migrations

Always inspect generated SQL with `npx prisma migrate dev --create-only` before applying migrations to production. Reviewing the raw SQL guarantees no accidental table drops or unintentional column renames occur.