​
Schema Overview

Our database schema is designed to support authentication, user management, and subscription handling.

​
User Model

model User {
  id               String         @id @map("_id")
  name             String
  email            String
  emailVerified    Boolean
  image            String?
  createdAt        DateTime
  updatedAt        DateTime
  sessions         Session[]
  accounts         Account[]
  stripeCustomerId String?
  subscription     subscription[]

  @@unique([email])
  @@map("user")
}
The User model is the central entity in our application, storing essential user information and relationships.

​
Session Model

model Session {
  id        String   @id @map("_id")
  expiresAt DateTime
  token     String
  createdAt DateTime
  updatedAt DateTime
  ipAddress String?
  userAgent String?
  userId    String
  user      User     @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@unique([token])
  @@map("session")
}
Sessions track user authentication states and provide security features.

​
Account Model

model Account {
  id                    String    @id @map("_id")
  accountId             String
  providerId            String
  userId                String
  user                  User      @relation(fields: [userId], references: [id], onDelete: Cascade)
  accessToken           String?
  refreshToken          String?
  idToken               String?
  accessTokenExpiresAt  DateTime?
  refreshTokenExpiresAt DateTime?
  scope                 String?
  password              String?
  createdAt             DateTime
  updatedAt             DateTime

  @@map("account")
}
The Account model handles OAuth and authentication provider connections.

​
Subscription Model

model subscription {
  id                   String    @id
  provider             Provider  @default(STRIPE)
  plan                 String
  referenceId          String?
  stripeCustomerId     String?
  stripeSubscriptionId String?
  polarSubscriptionId  String?
  polarCustomerId      String?
  productId            String?
  status               String?
  seats                Int?
  periodStart          DateTime?
  periodEnd            DateTime?
  cancelAtPeriodEnd    Boolean?
  trialStart           DateTime?
  trialEnd             DateTime?
  metadata             Json?
  user                 User?     @relation(fields: [userId], references: [id], onDelete: Cascade)
  userId               String?

  @@index([stripeCustomerId], name: "stripe_customer_id_idx")
}
The subscription model manages user subscriptions with support for both Stripe and Polar payment providers.

​
Setting Up the Database

For detailed instructions on setting up and configuring your database, please refer to: These guides provide step-by-step instructions for installing dependencies, initializing Supabase, configuring environment variables, and managing database schema changes.

​
Best Practices

  • Always use meaningful field names
  • Include proper indexes for frequently queried fields
  • Use appropriate data types
  • Add comments to complex fields
  • Follow the naming conventions (camelCase for fields, PascalCase for models)

​
Database Operations

​
Using Prisma Client

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

// Create a user
const user = await prisma.user.create({
  data: {
    name: 'John Doe',
    email: 'john@example.com',
    emailVerified: false,
  },
})

// Query users
const users = await prisma.user.findMany({
  include: {
    subscription: true,
  },
})

​
Common Operations

  • Create: prisma.model.create()
  • Read: prisma.model.findUnique(), prisma.model.findMany()
  • Update: prisma.model.update()
  • Delete: prisma.model.delete()

​
Security Considerations

  1. Always use environment variables for sensitive data
  2. Implement proper access control in your API routes
  3. Use Prisma’s built-in security features
  4. Regularly backup your database
  5. Monitor database performance

​
Troubleshooting

​
Common Issues

  1. Connection Issues
    • Verify your connection strings
    • Check network connectivity
    • Ensure proper firewall rules
  2. Migration Problems
    • Check for conflicting changes
    • Verify schema syntax
    • Ensure proper backup before migrations
  3. Performance Issues
    • Check indexes
    • Monitor query performance
    • Use Prisma’s query optimization features

​
Additional Resources