If you're building a SaaS app with Next.js 16, one of the most important decisions you'll make early on is your database schema. Get it right and everything from billing to AI features slots in cleanly. Get it wrong and you'll be writing migrations for months.
In this guide, I'll walk you through a production-ready Prisma schema that covers users, subscriptions, and AI chat history — the core entities in virtually every modern SaaS.
Prerequisites
- Next.js 16 app (App Router)
- Prisma 5+
- PostgreSQL (local or Supabase/Neon)
- Node.js 20+
Why Prisma for SaaS?
Prisma gives you:
- Type-safe queries — no raw SQL foot-guns
- Auto-generated migrations — schema-first workflow
- Excellent Next.js integration — works great with React Server Components and API routes
- Prisma Studio — a visual DB editor you'll actually use
Step 1: Install and Initialize Prisma
npm install prisma @prisma/client
npx prisma init --datasource-provider postgresql
This creates a prisma/schema.prisma file and a .env with a DATABASE_URL placeholder.
Set your database URL:
# .env
DATABASE_URL="postgresql://user:password@localhost:5432/myapp"
Step 2: The Full SaaS Schema
Here's the complete schema covering all three domains:
// prisma/schema.prisma
generator client {
provider = "prisma-client-js"
}
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}
// ─────────────────────────────────
// USERS & AUTH
// ─────────────────────────────────
model User {
id String @id @default(cuid())
email String @unique
name String?
image String?
emailVerified DateTime?
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
// Auth.js v5 relations
accounts Account[]
sessions Session[]
// SaaS relations
subscription Subscription?
chatSessions ChatSession[]
@@index([email])
}
model Account {
id String @id @default(cuid())
userId String
type String
provider String
providerAccountId String
refresh_token String? @db.Text
access_token String? @db.Text
expires_at Int?
token_type String?
scope String?
id_token String? @db.Text
session_state String?
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
@@unique([provider, providerAccountId])
}
model Session {
id String @id @default(cuid())
sessionToken String @unique
userId String
expires DateTime
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
}
model VerificationToken {
identifier String
token String @unique
expires DateTime
@@unique([identifier, token])
}
// ─────────────────────────────────
// SUBSCRIPTIONS & BILLING
// ─────────────────────────────────
enum SubscriptionStatus {
ACTIVE
TRIALING
PAST_DUE
CANCELED
UNPAID
INCOMPLETE
INCOMPLETE_EXPIRED
PAUSED
}
enum PlanTier {
FREE
PRO
TEAM
ENTERPRISE
}
model Subscription {
id String @id @default(cuid())
userId String @unique
stripeCustomerId String? @unique
stripeSubscriptionId String? @unique
stripePriceId String?
stripeCurrentPeriodEnd DateTime?
status SubscriptionStatus @default(TRIALING)
tier PlanTier @default(FREE)
cancelAtPeriodEnd Boolean @default(false)
trialEndsAt DateTime?
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
@@index([stripeCustomerId])
@@index([stripeSubscriptionId])
}
// ─────────────────────────────────
// AI CHAT
// ─────────────────────────────────
enum MessageRole {
USER
ASSISTANT
SYSTEM
}
model ChatSession {
id String @id @default(cuid())
userId String
title String @default("New Chat")
model String @default("gpt-4o")
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
messages ChatMessage[]
@@index([userId])
}
model ChatMessage {
id String @id @default(cuid())
chatSessionId String
role MessageRole
content String @db.Text
promptTokens Int?
completionTokens Int?
createdAt DateTime @default(now())
chatSession ChatSession @relation(fields: [chatSessionId], references: [id], onDelete: Cascade)
@@index([chatSessionId])
}
Step 3: Run Your First Migration
npx prisma migrate dev --name init
This creates the SQL migration in prisma/migrations/ and applies it to your database.
Step 4: Create a Singleton Prisma Client
In development, Next.js hot-reloads can cause multiple Prisma instances. Prevent that:
// lib/prisma.ts
import { PrismaClient } from "@prisma/client";
const globalForPrisma = globalThis as unknown as {
prisma: PrismaClient | undefined;
};
export const prisma =
globalForPrisma.prisma ??
new PrismaClient({
log: process.env.NODE_ENV === "development" ? ["query", "error", "warn"] : ["error"],
});
if (process.env.NODE_ENV !== "production") globalForPrisma.prisma = prisma;
Step 5: Querying in React Server Components
With the schema in place, querying is type-safe and ergonomic:
// app/dashboard/page.tsx
import { prisma } from "@/lib/prisma";
import { auth } from "@/lib/auth";
export default async function DashboardPage() {
const session = await auth();
if (!session?.user?.id) redirect("/login");
const user = await prisma.user.findUnique({
where: { id: session.user.id },
include: {
subscription: true,
chatSessions: {
orderBy: { updatedAt: "desc" },
take: 5,
},
},
});
return (
<div>
<h1>Welcome, {user?.name}</h1>
<p>Plan: {user?.subscription?.tier ?? "FREE"}</p>
<h2>Recent Chats</h2>
{user?.chatSessions.map((s) => (
<div key={s.id}>{s.title}</div>
))}
</div>
);
}
Step 6: Handling Stripe Webhooks
When a Stripe subscription event fires, update the database:
// app/api/webhooks/stripe/route.ts
import { prisma } from "@/lib/prisma";
import Stripe from "stripe";
const stripe = new Stripe(process.env.STRIPE_SECRET_KEY!);
export async function POST(req: Request) {
const body = await req.text();
const sig = req.headers.get("stripe-signature")!;
let event: Stripe.Event;
try {
event = stripe.webhooks.constructEvent(
body,
sig,
process.env.STRIPE_WEBHOOK_SECRET!
);
} catch {
return new Response("Webhook signature failed", { status: 400 });
}
if (event.type === "customer.subscription.updated") {
const sub = event.data.object as Stripe.Subscription;
await prisma.subscription.update({
where: { stripeSubscriptionId: sub.id },
data: {
status: sub.status.toUpperCase() as any,
stripeCurrentPeriodEnd: new Date(sub.current_period_end * 1000),
cancelAtPeriodEnd: sub.cancel_at_period_end,
},
});
}
return new Response("OK");
}
Common Schema Patterns Worth Noting
1. Always cascade deletes from User
Every relation from User uses onDelete: Cascade. If a user is deleted, all their data goes with them. GDPR-friendly and no orphaned rows.
2. Store Stripe IDs, not just statuses
stripeCustomerId and stripeSubscriptionId are your sync anchors. Index them — you'll query by them constantly in webhooks.
3. Track token usage in chat messages
promptTokens and completionTokens let you build usage dashboards and rate-limit at the DB level. Worth having from day one.
4. Enum for roles, not strings
Using MessageRole enum instead of raw strings gives you type safety in Prisma queries and prevents typo bugs.
Extending the Schema
Once this foundation is in place, common extensions include:
-
Teams/Organizations — add an
Organizationmodel and join table -
API keys — add
ApiKeymodel with hashed key storage -
Usage limits — add
UsageRecordto track monthly consumption -
Audit logs — add
AuditEventfor compliance
Each of these follows the same pattern: relate to User or Subscription, use cascades, index your foreign keys.
Skip the Setup Grind
This schema is one piece of a larger puzzle. If you want auth, Stripe billing, AI chat, and this database layer all pre-wired and production-ready, check out LaunchKit — a Next.js 16 SaaS starter kit that ships with this exact schema, plus Prisma migrations, webhook handlers, and a full UI already built.
The source is also on GitHub: huangyongshan46-a11y/launchkit-saas
Got questions about scaling this schema or adding multi-tenancy? Drop them in the comments.
Top comments (0)