The Perfect Prisma Schema for a SaaS App (Next.js 16 + PostgreSQL)

#prisma #nextjs #database #saas

Every SaaS app needs the same core models: users, auth accounts, subscriptions, and (in 2026) AI conversations. Here's a battle-tested Prisma schema you can use as your starting point.

The Schema

generator client {
  provider = "prisma-client-js"
}

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

model User {
  id            String    @id @default(cuid())
  name          String?
  email         String    @unique
  emailVerified DateTime?
  image         String?
  password      String?
  role          Role      @default(USER)
  createdAt     DateTime  @default(now())
  updatedAt     DateTime  @updatedAt

  accounts      Account[]
  sessions      Session[]
  subscription  Subscription?
  conversations Conversation[]
}

Why these design choices

cuid() over uuid()

cuid() generates IDs that are:

URL-safe (no special characters)
Sortable by creation time
Shorter than UUIDs
Collision-resistant

For a SaaS app, this is ideal. UUIDs work too, but cuids are more practical.

Optional password field

When users sign up via OAuth (Google, GitHub), they don't have a password. Making it optional lets you support both OAuth and email/password auth without separate user tables.

Role enum

enum Role {
  USER
  ADMIN
}

Keep it simple. Two roles cover 90% of SaaS needs. If you need more granular permissions later, add a Permission model — but don't over-engineer day one.

The Subscription Model

model Subscription {
  id                     String   @id @default(cuid())
  userId                 String   @unique
  stripeCustomerId       String   @unique
  stripeSubscriptionId   String?  @unique
  stripePriceId          String?
  stripeCurrentPeriodEnd DateTime?
  status                 SubscriptionStatus @default(INACTIVE)
  plan                   Plan     @default(FREE)
  createdAt              DateTime @default(now())
  updatedAt              DateTime @updatedAt

  user User @relation(fields: [userId], references: [id], onDelete: Cascade)
}

enum Plan { FREE PRO ENTERPRISE }

enum SubscriptionStatus { ACTIVE INACTIVE PAST_DUE CANCELED }

Key decisions:

One subscription per user (@unique on userId) — simplifies billing logic
Store Stripe IDs — your database should mirror Stripe's state
Enum for plan — no magic strings, TypeScript catches typos
stripeCurrentPeriodEnd — lets you check if access should be revoked without calling Stripe

AI Conversation Models

model Conversation {
  id        String    @id @default(cuid())
  userId    String
  title     String    @default("New Chat")
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt

  user     User      @relation(fields: [userId], references: [id], onDelete: Cascade)
  messages Message[]
}

model Message {
  id             String      @id @default(cuid())
  conversationId String
  role           MessageRole
  content        String
  createdAt      DateTime    @default(now())

  conversation Conversation @relation(fields: [conversationId], references: [id], onDelete: Cascade)
}

enum MessageRole { USER ASSISTANT SYSTEM }

This supports:

Multiple conversations per user
Full message history for context
System messages for custom instructions
Cascade delete (user deletion cleans up everything)

The Prisma Client Singleton

// src/lib/db.ts
import { PrismaClient } from "@prisma/client";

const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined;
};

export const db = globalForPrisma.prisma ?? new PrismaClient();

if (process.env.NODE_ENV !== "production") globalForPrisma.prisma = db;

Without the singleton, Next.js hot reload creates a new Prisma client on every file change, eventually exhausting your database connection pool.

Common queries

// Get user with subscription
const user = await db.user.findUnique({
  where: { id: userId },
  include: { subscription: true },
});

// Check if user is on Pro plan
const isPro = user?.subscription?.plan === "PRO" 
  && user.subscription.status === "ACTIVE";

// Get conversation with messages
const chat = await db.conversation.findUnique({
  where: { id: conversationId },
  include: { messages: { orderBy: { createdAt: "asc" } } },
});

// Count AI messages this month (for plan limits)
const messageCount = await db.message.count({
  where: {
    conversation: { userId },
    role: "USER",
    createdAt: { gte: startOfMonth },
  },
});