Database

This boilerplate uses Prisma as the ORM (Object-Relational Mapping) layer for PostgreSQL, providing type-safe database access with excellent developer experience.

Overview

The database setup includes:

• PostgreSQL - Production-ready relational database.
• Prisma ORM - Type-safe database client.
• Pre-configured schemas - Users, sessions, payments, subscriptions, and more.
• Migrations - Version control for your database schema.
• UUID primary keys - Database-generated UUIDs for new tables.

Database schema

The complete Prisma schema is located in prisma/schema.prisma. The template includes a comprehensive data model for building production-ready applications.

What's included

Authentication (better-auth schema)

• User - User accounts with email verification
• Session - Active user sessions with token management
• Account - OAuth provider accounts and credentials
• Verification - Email and password reset verification codes

These tables are auto-generated by better-auth and follow their standard schema structure.

Payments & monetization (Stripe-ready)

• Payment - Transaction records with processor integration
• Subscription - Recurring billing with plan management
• Price - Product pricing tiers
• Product - Product catalog
• WebhookEvent - Reliable webhook processing with idempotency
• Works in both authenticated and guest/authless modes

Application features

• Chat & Message - Multi-user chat/messaging system
• File - S3 file storage with user ownership
• UserData - Extended user profiles and preferences

User feedback

• BugReport - Bug tracking from users
• FeatureRequest - Feature suggestion collection

Key models

Here are the core models that power the template. All custom tables use database-generated UUIDs (@db.Uuid) for primary keys. Auth tables use String IDs as required by better-auth.

User model

model User {
  id            String         @id
  name          String
  email         String
  emailVerified Boolean
  image         String?
  createdAt     DateTime
  updatedAt     DateTime
  sessions      Session[]
  accounts      Account[]
  userData      UserData?
  payments      Payment[]
  subscriptions Subscription[]
  chats         Chat[]
  files         File[]

  @@unique([email])
  @@map("user")
}

Payment model

The payment system supports both authenticated users and guest checkouts:

model Payment {
  id                  String        @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid

  // Customer identification (works for both auth and authless modes)
  customerEmail       String
  customerName        String?
  userId              String?       // Populated in auth mode, null in authless
  user                User?         @relation(fields: [userId], references: [id], onDelete: SetNull)

  // Processor info
  processor           String        @default("stripe")
  processorCustomerId String
  processorPaymentId  String?       @unique
  processorInvoiceId  String?
  processorPriceId    String?

  // Payment details
  amount              Int           // Amount in cents
  currency            String        @default("usd")
  status              String        // succeeded, pending, failed, refunded

  // Optional subscription reference
  subscriptionId      String?       @db.Uuid
  subscription        Subscription? @relation(fields: [subscriptionId], references: [id], onDelete: SetNull)

  // Metadata & license generation
  metadata            Json?
  licenseKeys         LicenseKey[]

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

  @@index([userId])
  @@index([customerEmail])
  @@index([processorCustomerId])
  @@index([subscriptionId])
  @@index([status])
  @@map("payment")
}

Working with Prisma

Using the Prisma client

The Prisma client is available at lib/prisma.ts and uses the singleton pattern to prevent connection issues during development. Import it anywhere in your server code:

import prisma from '@@/lib/prisma'

export default defineEventHandler(async (event) => {
  const user = await prisma.user.findUnique({
    where: { email: 'user@example.com' },
  })

  return user
})

Common patterns

The template follows consistent patterns for database operations. Here are the key conventions used throughout the codebase.

User-scoped queries

Always filter by userId for user-owned resources using the authenticated user from context:

import prisma from '@@/lib/prisma'

export default defineEventHandler(async (event) => {
  const userId = await requireAuth(event)

  const chats = await prisma.chat.findMany({
    where: { userId },
    orderBy: { createdAt: 'desc' },
  })

  return chats
})

Including relations

Use include to load related data efficiently:

const chat = await prisma.chat.findFirst({
  where: {
    id: chatId,
    userId: userId,  // Always scope to user
  },
  include: {
    messages: {
      orderBy: { createdAt: 'asc' },
    },
  },
})

Service layer pattern

For complex queries or reusable logic, use server services (server/services/):

import { getProducts } from '@@/server/services/products-server-service'

export default defineEventHandler(async (event) => {
  const products = await getProducts({ page: 1, limit: 10 })
  return products
})

For complete Prisma documentation, see the official Prisma docs.

Database setup

Initial setup

The template includes a complete schema in prisma/schema.prisma. To initialize your database:

# Development - creates migration history
pnpm prisma migrate dev --name init

# Or use db push for rapid prototyping (no migration files)
pnpm prisma db push

This creates all tables (users, sessions, payments, subscriptions, chats, files, etc.) in your database.

Extending the schema

When you add new models or modify existing ones:

1. Update prisma/schema.prisma
2. Create a migration:

pnpm prisma migrate dev --name your_change_description

3. The Prisma client auto-regenerates with type-safe access to your changes

For detailed migration workflows, see the Prisma migrations documentation.

Schema conventions

The template follows these naming conventions for consistency:

Table naming

• Prisma model names: PascalCase (e.g., UserData, BugReport)
• Database table names: snake_case (e.g., user_data, bug_report)

model BugReport {
  id String @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  // ... fields
  @@map("bug_report")
}

Field naming

• Field names: camelCase (e.g., userId, createdAt, processorCustomerId)
• Primary keys: Typically id, though some models use descriptive names (e.g., priceId)
• Timestamps: Use createdAt for all tables; add updatedAt for frequently modified data

ID generation

• Auth tables (User, Session, Account): String IDs managed by better-auth
• Application tables: Database-generated UUIDs

When adding tables that belong to users, follow this structure:

model YourNewTable {
  id        String   @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  userId    String   // Foreign key to User
  createdAt DateTime @default(now())
  updatedAt DateTime @default(now()) @updatedAt
  
  user User @relation(fields: [userId], references: [id], onDelete: Cascade)
  
  @@index([userId])
  @@map("your_new_table")
}

Development tools

Prisma Studio

View and edit your database visually:

pnpm prisma studio

Opens at http://localhost:5555 - useful for debugging and testing during development.

Environment variables

Configure your database connection in .env:

DATABASE_URL="postgresql://user:password@localhost:5432/mydb"

Additional resources

For comprehensive Prisma documentation including transactions, error handling, performance optimization, and advanced queries:

• Prisma documentation
• Prisma Client API reference
• PostgreSQL documentation