⚡
SaaS Template

Database

Learn how RankThis uses PostgreSQL with Prisma ORM for data management, migrations, transactions, and key database patterns.

Database Overview

RankThis uses PostgreSQL as the primary database with Prisma ORM for type-safe database operations and automated migrations.

✅ Technology Stack

  • • PostgreSQL database
  • • Prisma ORM with type safety
  • • Automated schema migrations
  • • Connection pooling
  • • Database transactions
  • • Event logging system

🔧 Key Features

  • • User and subscription management
  • • Authentication tables (NextAuth.js)
  • • Subscription event tracking
  • • Waitlist management
  • • Admin analytics data
  • • Audit logging

Database Schema

Core Models

The database schema includes these primary models:

prisma/schema.prisma (Core Models)
 1// User Management
 2model User {
 3  id            String    @id @default(cuid())
 4  name          String?
 5  email         String    @unique
 6  emailVerified DateTime?
 7  image         String?
 8  
 9  // Relations
10  accounts      Account[]
11  sessions      Session[]
12  subscription  Subscription?
13  
14  createdAt DateTime @default(now())
15  updatedAt DateTime @updatedAt
16  
17  @@map(name: "users")
18}
19

20// Subscription Management
21model Subscription {
22  id                       String   @id @default(cuid())
23  userId                   String   @unique
24  user                     User     @relation(fields: [userId], references: [id], onDelete: Cascade)
25  
26  // Stripe Integration
27  stripeCustomerId         String?
28  stripeSubscriptionId     String?  @unique
29  stripeSubscriptionItemId String?
30  stripePriceId           String?
31  stripeCurrentPeriodEnd  DateTime?
32  
33  // Subscription Status
34  status            String?  // active, canceled, incomplete, etc.
35  cancelAtPeriodEnd Boolean  @default(false)
36  
37  createdAt DateTime @default(now())
38  updatedAt DateTime @updatedAt
39  
40  @@map("subscriptions")
41}
42

43// Event Tracking
44model SubscriptionEvent {
45  id             String   @id @default(cuid())
46  subscriptionId String
47  subscription   Subscription @relation(fields: [subscriptionId], references: [id])
48  
49  eventType      String   // created, updated, canceled, payment_succeeded
50  stripeEventId  String?  @unique
51  eventData      Json?    // Full Stripe event data
52  
53  createdAt DateTime @default(now())
54  
55  @@map("subscription_events")
56}
Authentication Models

NextAuth.js requires these models for session management:

prisma/schema.prisma (Auth Models)
 1// NextAuth.js Required Models
 2model Account {
 3  id                String  @id @default(cuid())
 4  userId            String
 5  type              String
 6  provider          String
 7  providerAccountId String
 8  refresh_token     String? @db.Text
 9  access_token      String? @db.Text
10  expires_at        Int?
11  token_type        String?
12  scope             String?
13  id_token          String? @db.Text
14  session_state     String?
15  
16  user User @relation(fields: [userId], references: [id], onDelete: Cascade)
17  
18  @@unique([provider, providerAccountId])
19  @@map("accounts")
20}
21

22model Session {
23  id           String   @id @default(cuid())
24  sessionToken String   @unique
25  userId       String
26  expires      DateTime
27  user         User     @relation(fields: [userId], references: [id], onDelete: Cascade)
28  
29  @@map("sessions")
30}
31

32model VerificationToken {
33  identifier String
34  token      String   @unique
35  expires    DateTime
36  
37  @@unique([identifier, token])
38  @@map("verificationtokens")
39}

Database Operations

Database Connection

Centralized database connection with connection pooling:

src/server/db.ts
 1import { PrismaClient } from "@prisma/client";
 2

 3const globalForPrisma = globalThis as unknown as {
 4  prisma: PrismaClient | undefined;
 5};
 6

 7export const db = globalForPrisma.prisma ?? new PrismaClient({
 8  log: process.env.NODE_ENV === "development" ? ["query", "error", "warn"] : ["error"],
 9});
10

11if (process.env.NODE_ENV !== "production") {
12  globalForPrisma.prisma = db;
13}

The connection singleton prevents too many connections in development with hot reloading.

Common Queries

Examples of common database operations used throughout the app:

Database operation examples
 1import { db } from "~/server/db";
 2

 3// Get user with subscription
 4export async function getUserWithSubscription(userId: string) {
 5  return await db.user.findUnique({
 6    where: { id: userId },
 7    include: { 
 8      subscription: true 
 9    },
10  });
11}
12

13// Create subscription with event logging
14export async function createSubscription(data: {
15  userId: string;
16  stripeCustomerId: string;
17  stripeSubscriptionId: string;
18  stripePriceId: string;
19}) {
20  return await db.$transaction(async (tx) => {
21    // Create subscription
22    const subscription = await tx.subscription.create({
23      data: {
24        userId: data.userId,
25        stripeCustomerId: data.stripeCustomerId,
26        stripeSubscriptionId: data.stripeSubscriptionId,
27        stripePriceId: data.stripePriceId,
28        status: "active",
29      },
30    });
31

32    // Log the event
33    await tx.subscriptionEvent.create({
34      data: {
35        subscriptionId: subscription.id,
36        eventType: "created",
37        eventData: data,
38      },
39    });
40

41    return subscription;
42  });
43}
44

45// Get subscription analytics
46export async function getSubscriptionAnalytics() {
47  const [totalSubscriptions, activeSubscriptions, monthlyRevenue] = await Promise.all([
48    db.subscription.count(),
49    db.subscription.count({
50      where: { status: "active" },
51    }),
52    db.subscription.aggregate({
53      where: { 
54        status: "active",
55        stripeCurrentPeriodEnd: {
56          gte: new Date(),
57        },
58      },
59      _count: true,
60    }),
61  ]);
62

63  return {
64    totalSubscriptions,
65    activeSubscriptions,
66    churnRate: ((totalSubscriptions - activeSubscriptions) / totalSubscriptions) * 100,
67  };
68}

Schema Migrations

Development Workflow

Use these commands for schema changes during development:

# Make schema changes in prisma/schema.prisma, then:


# Generate Prisma client (after schema changes)
pnpm db:generate


# Push schema to development database (no migration file)
pnpm db:push


# Create a migration file (before committing)
pnpm db:migrate dev --name your_migration_name


# Reset database (development only)
pnpm db:reset

⚠️ Development vs Production

Use db:push for rapid prototyping in development. Always create migrations with db:migrate dev before committing.

Production Deployment

Apply migrations to production databases:

# Production migration workflow


# 1. Generate migration files locally
pnpm db:migrate dev --name add_new_feature


# 2. Commit schema.prisma and migration files
git add prisma/
git commit -m "Add new feature migration"


# 3. Deploy to production (Vercel/server)
pnpm db:migrate deploy


# 4. Generate Prisma client for production
pnpm db:generate

Production migrations are automatically applied during deployment if configured in your build pipeline.

Database Transactions

Transaction Patterns

Use transactions for operations that must succeed or fail together:

Transaction examples
 1import { db } from "~/server/db";
 2

 3// Subscription cancellation with event logging
 4export async function cancelSubscription(subscriptionId: string) {
 5  return await db.$transaction(async (tx) => {
 6    // Update subscription status
 7    const subscription = await tx.subscription.update({
 8      where: { id: subscriptionId },
 9      data: {
10        status: "canceled",
11        canceledAt: new Date(),
12        cancelAtPeriodEnd: true,
13      },
14    });
15

16    // Log cancellation event
17    await tx.subscriptionEvent.create({
18      data: {
19        subscriptionId: subscription.id,
20        eventType: "canceled",
21        eventData: {
22          canceledAt: subscription.canceledAt,
23          reason: "user_requested",
24        },
25      },
26    });
27

28    // Send cancellation email (if needed)
29    // await sendCancellationEmail(subscription.userId);
30

31    return subscription;
32  });
33}
34

35// Complex analytics calculation
36export async function calculateMRR() {
37  return await db.$transaction(async (tx) => {
38    const activeSubscriptions = await tx.subscription.findMany({
39      where: { status: "active" },
40      include: { user: true },
41    });
42

43    const monthlyRevenue = activeSubscriptions.reduce((total, sub) => {
44      // Calculate monthly revenue based on plan
45      const monthlyAmount = calculateMonthlyAmount(sub.stripePriceId);
46      return total + monthlyAmount;
47    }, 0);
48

49    // Cache the result
50    await tx.analyticsCache.upsert({
51      where: { metric: "mrr" },
52      update: {
53        value: monthlyRevenue,
54        calculatedAt: new Date(),
55      },
56      create: {
57        metric: "mrr",
58        value: monthlyRevenue,
59        calculatedAt: new Date(),
60      },
61    });
62

63    return monthlyRevenue;
64  });
65}

Event Logging

Subscription Events

Track all subscription changes for analytics and debugging:

Event logging patterns
 1// Event logging utility
 2export async function createSubscriptionEvent(
 3  subscriptionId: string,
 4  eventType: string,
 5  eventData?: any,
 6  stripeEventId?: string
 7) {
 8  return await db.subscriptionEvent.create({
 9    data: {
10      subscriptionId,
11      eventType,
12      eventData: eventData ? JSON.stringify(eventData) : null,
13      stripeEventId,
14    },
15  });
16}
17

18// Usage in webhook handlers
19export async function handleStripeWebhook(event: Stripe.Event) {
20  switch (event.type) {
21    case "customer.subscription.updated":
22      const subscription = event.data.object as Stripe.Subscription;
23      
24      // Update subscription in database
25      await db.subscription.update({
26        where: { stripeSubscriptionId: subscription.id },
27        data: {
28          status: subscription.status,
29          stripeCurrentPeriodEnd: new Date(subscription.current_period_end * 1000),
30        },
31      });
32      
33      // Log the event
34      await createSubscriptionEvent(
35        subscription.id,
36        "updated",
37        {
38          status: subscription.status,
39          periodEnd: subscription.current_period_end,
40        },
41        event.id
42      );
43      break;
44  }
45}
46

47// Analytics query using events
48export async function getSubscriptionMetrics(days = 30) {
49  const startDate = new Date();
50  startDate.setDate(startDate.getDate() - days);
51

52  const events = await db.subscriptionEvent.groupBy({
53    by: ["eventType"],
54    where: {
55      createdAt: {
56        gte: startDate,
57      },
58    },
59    _count: {
60      eventType: true,
61    },
62  });
63

64  return events.reduce((acc, event) => {
65    acc[event.eventType] = event._count.eventType;
66    return acc;
67  }, {} as Record<string, number>);
68}
🛠️ Database Tools & Commands

Development

  • • pnpm db:studio - Open Prisma Studio
  • • pnpm db:push - Push schema changes
  • • pnpm db:reset - Reset database
  • • pnpm db:seed - Run seed script

Production

  • • pnpm db:generate - Generate client
  • • pnpm db:migrate - Apply migrations
  • • Always backup before migrations
  • • Test migrations in staging first
💡 Database Best Practices

Performance

  • • Use indexes on frequently queried fields
  • • Include relations carefully to avoid N+1
  • • Use connection pooling for production
  • • Cache expensive analytical queries

Data Integrity

  • • Use transactions for multi-step operations
  • • Add constraints at database level
  • • Log important events for audit trails
  • • Validate data before database operations

Database Setup Complete!

Your database layer is configured with type safety and best practices. Next, set up the email system.

Setup Email System →Learn Admin Panel