Next.js 16 Prisma Schema for SaaS: Users, Subscriptions, Teams, and AI Chat

When you're building a SaaS product, your database schema is your foundation. Get it wrong and you're constantly fighting migrations, papering over design mistakes, and accruing technical debt before you've shipped. Get it right and the rest of the stack snaps into place.

This guide walks through a production-grade Prisma schema for a Next.js SaaS app — one that handles OAuth auth, Stripe billing, multi-user teams, and AI chat history. We'll cover every model, explain the design decisions behind them, and show you how everything connects.

---

Why Prisma for a SaaS Schema?

Prisma gives you type-safe database access, auto-generated migrations, and a schema-as-code approach that works naturally with TypeScript. In a Next.js 15/16 app using the App Router and server actions, Prisma fits cleanly into server components, API routes, and background jobs.

You also get Prisma Studio for debugging data in dev — something you'll use constantly when building billing flows.

---

The Full Schema

Let's walk through the models one by one.

datasource and generator

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

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

Postgres is the right call for SaaS. It handles JSON columns, full-text search, and row-level security — all things you'll want down the road.

---

User Model

The User model is the center of gravity for everything else.

model User {
  id            String    @id @default(cuid())
  email         String    @unique
  name          String?
  image         String?
  emailVerified DateTime?

  // Stripe
  stripeCustomerId       String?   @unique
  stripeSubscriptionId   String?   @unique
  stripePriceId          String?
  stripeCurrentPeriodEnd DateTime?

  // Soft delete
  deletedAt DateTime?

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  accounts      Account[]
  sessions      Session[]
  memberships   TeamMember[]
  chatSessions  ChatSession[]

  @@index([email])
  @@index([stripeCustomerId])
}

Why store Stripe fields directly on User?

You could model subscriptions in a separate table, and for complex billing (multiple products, add-ons, metered usage) that makes sense. But for most SaaS apps — one plan per user, monthly or annual — storing the Stripe IDs directly on the user record keeps queries simple and eliminates a join on every auth check.

stripeCurrentPeriodEnd is the key field: it tells you whether the subscription is still active without making an API call to Stripe. You sync this from Stripe webhooks and check it at the application layer.

Why soft deletes (deletedAt)?

Hard-deleting users breaks foreign key integrity, kills audit trails, and creates headaches when a customer emails support asking why their data is gone. Soft deletes let you mark a user as deleted while preserving the record. Filter WHERE deletedAt IS NULL in your queries (or use a Prisma middleware to do this globally).

---

Account Model (OAuth)

This is the NextAuth.js Account model — it stores OAuth provider tokens.

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])
  @@index([userId])
}

The @db.Text annotations matter here — OAuth tokens can be long and will overflow a standard VARCHAR(191) in some databases. The onDelete: Cascade means accounts are cleaned up automatically when a user is deleted.

The @@unique([provider, providerAccountId]) constraint prevents a user from being linked to the same OAuth account twice.

---

Session Model

model Session {
  id           String   @id @default(cuid())
  sessionToken String   @unique
  userId       String
  expires      DateTime

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

  @@index([userId])
}

model VerificationToken {
  identifier String
  token      String   @unique
  expires    DateTime

  @@unique([identifier, token])
}

This is the standard NextAuth.js session schema. The VerificationToken model handles magic link / email verification flows. If you're using JWT sessions instead of database sessions, you can drop the Session model — but database sessions give you the ability to revoke sessions server-side, which matters for security-conscious SaaS products.

---

Subscription Model (for Team Plans)

When you move beyond per-user billing to per-team billing, extract the Stripe fields into a dedicated Subscription model:

model Subscription {
  id                     String             @id @default(cuid())
  teamId                 String             @unique
  stripeCustomerId       String             @unique
  stripeSubscriptionId   String             @unique
  stripePriceId          String
  stripeCurrentPeriodEnd DateTime
  status                 SubscriptionStatus @default(TRIALING)
  cancelAtPeriodEnd      Boolean            @default(false)

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  team Team @relation(fields: [teamId], references: [id], onDelete: Cascade)

  @@index([stripeCustomerId])
  @@index([stripeSubscriptionId])
}

enum SubscriptionStatus {
  TRIALING
  ACTIVE
  CANCELED
  INCOMPLETE
  PAST_DUE
  UNPAID
}

The SubscriptionStatus enum maps directly to Stripe's subscription status values. cancelAtPeriodEnd lets you show "your subscription cancels on [date]" in the UI without immediately losing access — which is the behavior users expect.

---

Team / Organization Model

If your SaaS is B2B, you need teams. Here's a clean multi-tenant team model:

model Team {
  id        String   @id @default(cuid())
  name      String
  slug      String   @unique
  plan      String   @default("free")

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  deletedAt DateTime?

  members      TeamMember[]
  subscription Subscription?

  @@index([slug])
}

model TeamMember {
  id     String   @id @default(cuid())
  userId String
  teamId String
  role   TeamRole @default(MEMBER)

  joinedAt DateTime @default(now())

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

  @@unique([userId, teamId])
  @@index([teamId])
  @@index([userId])
}

enum TeamRole {
  OWNER
  ADMIN
  MEMBER
}

The slug field is critical for routing — /app/[teamSlug]/dashboard is a cleaner URL than /app/[teamId]/dashboard. Make it auto-generated from the team name but editable.

The @@unique([userId, teamId]) on TeamMember prevents duplicate memberships and gives you an efficient lookup for "is this user a member of this team?"

---

AI Chat Schema

If your SaaS has an AI feature (and in 2025, it probably should), you need to persist chat history. Here's a clean model:

model ChatSession {
  id        String   @id @default(cuid())
  userId    String
  title     String?
  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
  tokens        Int?

  createdAt DateTime @default(now())

  chatSession ChatSession @relation(fields: [chatSessionId], references: [id], onDelete: Cascade)

  @@index([chatSessionId])
}

enum MessageRole {
  USER
  ASSISTANT
  SYSTEM
}

Storing tokens on each message lets you implement usage tracking and rate limiting without calling the OpenAI API to count tokens retroactively. The model field on ChatSession lets you support multiple AI models and migrate users between them without losing history.

---

Key Design Decisions Summary

Decision	Why
cuid() for IDs	URL-safe, sortable, no sequential enumeration risk
Soft deletes on User and Team	Preserve audit trails, recover from mistakes
Stripe IDs indexed	You look these up constantly in webhook handlers
@db.Text for tokens	Prevents overflow on long OAuth/JWT tokens
Enum for subscription status	Type-safe, matches Stripe's status values exactly
cancelAtPeriodEnd boolean	Drives UI messaging without losing access prematurely
tokens on ChatMessage	Enables usage tracking without extra API calls

---

Syncing with Stripe Webhooks

The schema is only half the story. You need a webhook handler that keeps your database in sync with Stripe:

// app/api/webhooks/stripe/route.ts
export async function POST(req: Request) {
  const event = stripe.webhooks.constructEvent(/* ... */);

  switch (event.type) {
    case 'customer.subscription.updated':
    case 'customer.subscription.created': {
      const sub = event.data.object;
      await prisma.user.update({
        where: { stripeCustomerId: sub.customer as string },
        data: {
          stripeSubscriptionId: sub.id,
          stripePriceId: sub.items.data[0].price.id,
          stripeCurrentPeriodEnd: new Date(sub.current_period_end * 1000),
        },
      });
      break;
    }
    case 'customer.subscription.deleted': {
      const sub = event.data.object;
      await prisma.user.update({
        where: { stripeCustomerId: sub.customer as string },
        data: {
          stripeSubscriptionId: null,
          stripePriceId: null,
          stripeCurrentPeriodEnd: null,
        },
      });
      break;
    }
  }
}

This pattern — check stripeCurrentPeriodEnd locally, sync from webhooks — means you're never making synchronous Stripe API calls in your auth middleware.

---

Checking Access in Middleware

With this schema, an access check looks like:

export async function isSubscribed(userId: string): Promise<boolean> {
  const user = await prisma.user.findUnique({
    where: { id: userId },
    select: { stripeCurrentPeriodEnd: true },
  });

  if (!user?.stripeCurrentPeriodEnd) return false;
  return user.stripeCurrentPeriodEnd.getTime() > Date.now();
}

One DB query, no Stripe API call, no latency in the critical path.

---

Putting It Together

This schema gives you:

• ✅ NextAuth.js-compatible auth (OAuth + magic links)
• ✅ Stripe billing with webhook sync
• ✅ Soft deletes on critical models
• ✅ Multi-tenant teams with role-based access
• ✅ AI chat session persistence with token tracking

Building all of this from scratch — wiring up the migrations, the webhook handlers, the session checks, the Stripe integration — takes days. If you'd rather skip straight to shipping features, the full schema ships ready-to-go in LaunchKit, a Next.js 16 SaaS starter built on exactly these models.

---

The full schema — plus auth, billing, AI, and email wired up — ships in LaunchKit. $49 on Gumroad or browse the code on GitHub.