Next.js 16 SaaS Database Schema with Prisma: Users, Subscriptions, and AI Chat

#prisma #nextjs #database #tutorial

A good database schema is the foundation of any SaaS. Here's the complete Prisma schema I use for every new project — handling users, OAuth accounts, subscriptions, and AI chat.

The full 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[]
}

model Account {
  userId            String
  type              String
  provider          String
  providerAccountId String
  refresh_token     String?
  access_token      String?
  expires_at        Int?
  token_type        String?
  scope             String?
  id_token          String?
  session_state     String?
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  user User @relation(fields: [userId], references: [id], onDelete: Cascade)
  @@id([provider, providerAccountId])
}

model Session {
  sessionToken String   @unique
  userId       String
  expires      DateTime
  user User @relation(fields: [userId], references: [id], onDelete: Cascade)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

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)
}

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 Role { USER ADMIN }
enum Plan { FREE PRO ENTERPRISE }
enum SubscriptionStatus { ACTIVE INACTIVE PAST_DUE CANCELED }
enum MessageRole { USER ASSISTANT SYSTEM }

Why this structure?

User + Account (Auth.js pattern)

The User model holds core identity. Account stores OAuth provider data (Google tokens, GitHub tokens). This is the standard Auth.js v5 pattern — one user can have multiple OAuth providers linked.

Subscription (Stripe sync)

The Subscription model mirrors Stripe state. Key design choices:

userId is @unique — one subscription per user. Simplifies queries.
stripeCustomerId is @unique — one-to-one with Stripe customers.
status enum — not a string. Forces valid states.
plan enum — maps to feature limits in your code, not Stripe price IDs.

Keep Stripe as the source of truth. Sync via webhooks, never trust client state.

Conversation + Message (AI chat)

The Conversation model groups messages. This lets users have multiple chat threads and see history.

role enum — USER, ASSISTANT, SYSTEM. Maps directly to OpenAI message roles.
Cascade deletes — deleting a conversation deletes all messages.

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({
    log: process.env.NODE_ENV === "development" ? ["query"] : [],
  });

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

The singleton prevents creating multiple Prisma clients during hot reload in development.

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" } } },
});

// Create message pair (user + assistant)
await db.message.createMany({
  data: [
    { conversationId, role: "USER", content: userInput },
    { conversationId, role: "ASSISTANT", content: aiResponse },
  ],
});