Schema & Models

The database schema lives in a single file — apps/boilerplate/prisma/schema.prisma — and defines all tables, columns, relations, and indexes. Prisma reads this file to generate a fully typed TypeScript client, so every query in your application is type-checked at compile time.

This page walks through every model in the schema. For the architecture overview, see Database Overview. For how to change the schema, see Migrations & Seeding.

Schema Configuration

The top of the schema file configures the Prisma client generator and the PostgreSQL datasource:

prisma/schema.prisma — Configuration

generator client {
  provider        = "prisma-client-js"
  binaryTargets   = ["native", "rhel-openssl-3.0.x"]
  previewFeatures = ["postgresqlExtensions"]
}

datasource db {
  provider   = "postgresql"
  // Use pooled connection for queries (with pgbouncer)
  url        = env("DATABASE_URL")
  // Use direct connection for migrations (without pgbouncer)
  directUrl  = env("DIRECT_URL")
  extensions = [vector]
}

Key points:

binaryTargets — Includes rhel-openssl-3.0.x for deployment on containerized environments (Docker, AWS Lambda).
previewFeatures — Enables postgresqlExtensions so Prisma recognizes the vector extension in the datasource.
extensions = [vector] — Activates the pgvector extension for semantic search. This must also be enabled in Supabase via SQL (see Migrations & Seeding).
Dual URLs — url for pooled queries, directUrl for migrations. See the overview for details.

User Model

The User model is the central entity. It connects to Clerk via clerkId and supports Kit's dual pricing model — both credit-based and classic SaaS subscription fields coexist on the same model. Which pricing model is active depends on your configuration, not the schema.

prisma/schema.prisma — User Model

model User {
  id                 String              @id @default(uuid())
  clerkId            String              @unique
  email              String?             // Cached from Clerk for payment processing
  name               String?             // Cached from Clerk for payment processing
  hasUsedTrial       Boolean             @default(false) // Track if user has ever used a trial
  downgradeCount     Int                 @default(0) // Track downgrades during trial (max 2)
  lastPlanChangeAt   DateTime?           // Last plan change timestamp
  tier               String              @default("free") // Subscription tier: free, basic, pro, enterprise
  // ============================================
  // CREDIT-BASED MODEL FIELDS (nullable for lazy initialization)
  // ============================================
  creditBalance      Decimal?            @db.Decimal(10, 2) // Current available credits (null = not initialized, supports fractional: 0.2, 0.5, 1.5)
  creditsPerMonth    Decimal?            @db.Decimal(10, 2) // Monthly credit allocation (tier-based, supports fractional)
  creditsResetAt     DateTime?           // Last monthly reset timestamp
  bonusCredits       Decimal?            @db.Decimal(10, 2) // Bonus credits from purchases (top-ups)
  bonusCreditsAutoUse Boolean            @default(false) // Per-user bonus credit toggle (opt-in, like Claude Desktop "Extra Usage")
  // ============================================
  // CLASSIC SAAS MODEL FIELDS
  // ============================================
  isTrial            Boolean             @default(false) // User is currently in trial period
  trialStartDate     DateTime?           // Trial start timestamp
  trialEndDate       DateTime?           // Trial expiration timestamp
  isLocked           Boolean             @default(false) // Account locked after trial expiry
  // ============================================
  // SHARED FIELDS
  // ============================================
  createdAt          DateTime            @default(now())
  updatedAt          DateTime            @updatedAt
  // Relations
  emailLogs          EmailLog[]
  files              File[]
  subscription       Subscription?
  aiUsage            AIUsage[]           // AI usage tracking
  aiConversations    AIConversation[]    // AI conversation history
  creditTransactions CreditTransaction[] // Credit transaction log
  bonusCreditPurchases BonusCreditPurchase[] // Bonus credit purchase history (credit_based only)

  @@index([clerkId])
  @@index([tier])
}

Field Groups

Identity (synced from Clerk):

clerkId — Unique identifier from Clerk. This is how your app looks up users after authentication.
email, name — Cached from Clerk webhooks for payment processing and display. Not used for authentication.

Credit-Based Pricing:

creditBalance — Current available credits. Nullable for lazy initialization (credits are allocated on first use, not at signup).
creditsPerMonth — Monthly credit allocation based on the user's tier.
creditsResetAt — When the next monthly reset occurs.
bonusCredits — Extra credits purchased as top-ups.
bonusCreditsAutoUse — Per-user toggle for automatic bonus credit consumption (defaults to false). Only relevant when NEXT_PUBLIC_BONUS_CREDITS_USER_TOGGLE_ENABLED=true.

Credit fields use Decimal(10, 2) instead of Float to avoid floating-point precision errors. This is critical for financial calculations — a Float can turn 10.00 - 0.20 into 9.799999999999999, while Decimal keeps it as 9.80.

Classic SaaS Pricing:

isTrial, trialStartDate, trialEndDate — Trial period tracking.
isLocked — Account locked after trial expiry without conversion to paid.

Shared Fields:

tier — Current subscription tier: free, basic, pro, or enterprise.
hasUsedTrial, downgradeCount, lastPlanChangeAt — Business rule fields used by the payment system to prevent trial abuse and track plan changes.

Indexes

prisma

@@index([clerkId])  // Fast lookup after Clerk authentication
@@index([tier])     // Filter users by subscription tier (admin queries)

Subscription Model

The Subscription model tracks Lemon Squeezy payment state. Each user can have at most one active subscription (enforced by @unique on userId).

prisma/schema.prisma — Subscription Model

model Subscription {
  id                   String    @id @default(uuid())
  userId               String    @unique
  customerId           String    @unique
  subscriptionId       String    @unique
  productId            String
  variantId            String
  status               String
  currentPeriodEnd     DateTime?
  trialEndsAt          DateTime? // Renamed for consistency with Lemon Squeezy
  canceledAt           DateTime?
  previousVariantId    String?   // Track previous plan for downgrades
  planHistory          String[]  @default([]) // Track all plan changes
  metadata             Json?
  createdAt            DateTime  @default(now())
  updatedAt            DateTime  @updatedAt
  user                 User      @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@index([status])
  @@index([customerId])
  @@index([subscriptionId])
}

Key Fields

customerId, subscriptionId — Lemon Squeezy identifiers. Both are @unique for direct lookups from webhook events.
productId, variantId — Map to specific plans in your Lemon Squeezy dashboard. The variant determines the pricing tier.
status — Mirrors Lemon Squeezy states: active, on_trial, cancelled, expired, past_due, paused.
previousVariantId — Tracks the last plan before a change, enabling downgrade detection.
planHistory — Append-only array of all plan changes. Useful for analytics and customer support.
currentPeriodEnd — When the current billing period ends. Cancelled subscriptions still grant access until this date.
trialEndsAt — Trial period end timestamp. Set when a trial subscription is created via the subscription_created webhook.
canceledAt — Timestamp when the subscription was cancelled. Subscriptions remain active until currentPeriodEnd.
metadata — JSON metadata from the payment provider. Stores additional context from Lemon Squeezy webhook events.

The onDelete: Cascade on the user relation means deleting a user automatically deletes their subscription. This keeps the database consistent when users delete their accounts via Clerk.

File Model

The File model stores metadata for files uploaded to Vercel Blob. The actual file content lives in Blob storage — only the reference URL and metadata are in the database.

prisma

model File {
  id           String   @id @default(uuid())
  userId       String
  url          String          // Vercel Blob URL
  pathname     String          // Storage path
  originalName String          // Display name
  contentType  String?         // MIME type
  size         Int?            // File size in bytes
  metadata     Json?           // Custom metadata
  createdAt    DateTime @default(now())
  updatedAt    DateTime @updatedAt
  user         User     @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@index([userId])
  @@index([createdAt])
}

EmailLog Model

The EmailLog model tracks every email sent through Resend. It supports analytics (delivery rates, bounce tracking) and GDPR compliance (automated log cleanup).

prisma

model EmailLog {
  id        String    @id @default(uuid())
  userId    String?           // Nullable for system emails
  to        String
  from      String?
  subject   String
  type      String?           // "welcome", "password_reset", "invoice", etc.
  status    String            // "sent", "delivered", "bounced", "failed"
  provider  String    @default("resend")
  messageId String?           // Resend message ID for tracking
  metadata  Json?
  sentAt    DateTime?
  error     String?           // Error message if delivery failed
  createdAt DateTime  @default(now())
  user      User?     @relation(fields: [userId], references: [id])

  @@index([userId])
  @@index([to])
  @@index([status])
  @@index([type])
  @@index([createdAt])
}

The query module (queries/email-logs.ts) includes an anti-spam check — wasEmailRecentlySent() — that prevents duplicate sends within a configurable time window. It also provides deleteOldEmailLogs() for GDPR-compliant log retention.

AI Models

Three models track AI usage, conversations, and individual messages. They support Kit's multi-provider AI system (OpenAI, Anthropic, Google, xAI).

AIUsage

Tracks per-request AI consumption for cost management and analytics:

prisma/schema.prisma — AIUsage

model AIUsage {
  id        String   @id @default(uuid())
  userId    String?
  user      User?    @relation(fields: [userId], references: [id], onDelete: Cascade)
  sessionId String?  // For anonymous users or session-based tracking
  provider  String   // openai, anthropic, google, xai
  model     String   // gpt-5.2, claude-opus-4-6, gemini-2.5-pro, grok-4-1-fast, etc.
  tokens    Int      // Total tokens consumed (prompt + completion)
  cost      Float?   // Estimated cost in USD
  purpose   String   // faq, chat, completion, stream, embedding
  metadata  Json?    // Additional context (message count, temperature, etc.)
  createdAt DateTime @default(now())

  @@index([userId, createdAt])
  @@index([sessionId, createdAt])
  @@index([provider])
  @@index([purpose])
  @@index([createdAt])
}

The purpose field categorizes usage (faq, chat, completion, stream, embedding), enabling per-feature cost breakdowns in your admin dashboard.

AIConversation and AIMessage

Conversation threads and their messages:

prisma/schema.prisma — AIConversation & AIMessage

model AIConversation {
  id        String      @id @default(uuid())
  userId    String?
  user      User?       @relation(fields: [userId], references: [id], onDelete: Cascade)
  sessionId String      // For session-tracking (even for auth users)
  userTier  String?     // free, pro, enterprise - for rate limiting context
  title     String?     // Optional conversation title (first question)
  createdAt DateTime    @default(now())
  updatedAt DateTime    @updatedAt
  messages  AIMessage[]

  @@index([userId, createdAt])
  @@index([sessionId])
  @@index([createdAt])
}

model AIMessage {
  id             String         @id @default(uuid())
  conversationId String
  conversation   AIConversation @relation(fields: [conversationId], references: [id], onDelete: Cascade)
  role           String         // user, assistant, system
  content        String         @db.Text
  tokens         Int?           // Token count for cost tracking
  model          String?        // gpt-5.2, claude-opus-4-6, etc.
  provider       String?        // openai, anthropic, google, xai
  metadata       Json?          // Additional context (temperature, max_tokens, etc.)
  createdAt      DateTime       @default(now())

  @@index([conversationId, createdAt])
}

The sessionId field enables conversation tracking for both authenticated and anonymous users. The userTier field on conversations allows rate-limiting logic to check the user's plan without joining to the User table.

FAQChunk Model (RAG)

The FAQChunk model stores knowledge base entries with vector embeddings for semantic search. This is the foundation of Kit's RAG (Retrieval-Augmented Generation) system.

prisma/schema.prisma — FAQChunk

model FAQChunk {
  id         String   @id @default(uuid())
  // Content
  content    String   @db.Text // Full Q&A text
  question   String // Question headline
  answer     String   @db.Text // Answer preview (first 500 chars)
  category   String // "Getting Started", "Database", etc.
  // Vector embedding (OpenAI text-embedding-3-small = 1536 dimensions)
  embedding  Unsupported("vector(1536)")?
  // Metadata
  metadata   Json? // { tags: [], relatedTopics: [], keywords: [] }
  tokenCount Int? // For cost tracking
  // Timestamps
  createdAt  DateTime @default(now())
  updatedAt  DateTime @updatedAt

  @@index([category])
  @@index([createdAt])
  @@map("faq_chunks")
}

The Vector Field

The embedding field uses Unsupported("vector(1536)") — a Prisma escape hatch for PostgreSQL types that Prisma doesn't natively support. The 1536 dimension count matches OpenAI's text-embedding-3-small model.

Because embedding uses Unsupported(), Prisma cannot include it in standard queries. All vector operations must use raw SQL via prisma.$queryRaw. Kit handles this in apps/boilerplate/src/lib/ai/rag-search.ts using cosine similarity:

sql

SELECT id, content, question, answer, category,
  1 - (embedding <=> $1::vector) as similarity
FROM faq_chunks
WHERE 1 - (embedding <=> $1::vector) > $2
ORDER BY similarity DESC
LIMIT $3

The <=> operator computes cosine distance. 1 - distance converts it to a similarity score where 1.0 is identical and 0.0 is completely unrelated.

The @@map("faq_chunks") directive maps the PascalCase Prisma model name to a snake_case table name in PostgreSQL, matching the convention expected by the pgvector index.

Credit System Models

Two models support Kit's credit-based pricing — an immutable transaction ledger and a purchase tracker.

CreditTransaction

Every credit change is recorded as an immutable transaction. This provides a complete audit trail and enables balance reconstruction from history:

prisma/schema.prisma — CreditTransaction

model CreditTransaction {
  id           String   @id @default(uuid())
  userId       String
  user         User     @relation(fields: [userId], references: [id], onDelete: Cascade)
  amount       Decimal  @db.Decimal(10, 2) // Positive = credit added, Negative = credit deducted (supports fractional: 0.2, 0.5, 1.5)
  balanceAfter Decimal  @db.Decimal(10, 2) // Credit balance after this transaction (supports fractional)
  type         String // "usage", "refund", "monthly_reset", "purchase", "adjustment"
  operation    String? // "faq", "chat", "image_gen", "code_analysis", etc.
  metadata     Json? // Additional context: { model: "gpt-5.2", tokens: 1500, cost: 0.02 }
  createdAt    DateTime @default(now())

  @@index([userId, createdAt])
  @@index([type])
  @@index([createdAt])
}

Transaction types:

Type	Description	Amount
`usage`	Credits consumed by an AI operation	Negative
`refund`	Credits returned due to an error or cancellation	Positive
`monthly_reset`	Monthly credit allocation based on tier	Positive
`purchase`	Bonus credits bought via Lemon Squeezy	Positive
`adjustment`	Manual admin adjustment	Either

The operation field provides detail within a type — for example, a usage transaction might have operation chat or embedding.

BonusCreditPurchase

Tracks one-time credit top-up purchases through Lemon Squeezy:

prisma/schema.prisma — BonusCreditPurchase

model BonusCreditPurchase {
  id             String   @id @default(uuid())
  userId         String
  user           User     @relation(fields: [userId], references: [id], onDelete: Cascade)
  // Purchase Details
  credits        Int // Number of credits purchased
  price          Decimal  @db.Decimal(10, 2) // Price paid in currency
  expiresAt      DateTime? // Expiration date (null = never expires)
  // Lemon Squeezy Integration
  lemonSqueezyId String   @unique // Order ID from Lemon Squeezy
  variantId      String // Variant ID from Lemon Squeezy
  status         String // "completed", "refunded", "expired"
  // Timestamps
  createdAt      DateTime @default(now())

  @@index([userId])
  @@index([expiresAt])
  @@index([status])
  @@index([createdAt])
}

Relations Overview

All models connect through the User model as the central entity:

                              User
                               |
          +--------+--------+--+--+--------+--------+--------+
          |        |        |     |        |        |        |
    Subscription  File  EmailLog  |   AIConversation |  BonusCredit
                                  |        |         |   Purchase
                              AIUsage  AIMessage  CreditTransaction

Cascade behavior: All relations to User use onDelete: Cascade — deleting a user removes all their associated records automatically. The only exception is EmailLog, which has a nullable userId (system emails are not tied to a user).

One-to-one: User ↔ Subscription (each user has at most one subscription).

One-to-many: User → File, EmailLog, AIUsage, AIConversation, CreditTransaction, BonusCreditPurchase.

Nested one-to-many: AIConversation → AIMessage (messages belong to a conversation, which belongs to a user).

Indexing Strategy

Kit's indexes are designed for the most common query patterns:

Pattern	Index	Why
User lookup after auth	`User.clerkId` (unique)	Every authenticated request looks up the user by Clerk ID
Tier-based queries	`User.tier`	Admin dashboard filters users by plan
Subscription webhooks	`Subscription.customerId`, `subscriptionId` (unique)	Lemon Squeezy webhooks identify subscriptions by these fields
Time-series queries	Composite `(userId, createdAt)` on AIUsage, CreditTransaction	Usage analytics and transaction history for a specific user
Email analytics	`EmailLog.status`, `type`, `to`	Delivery rate calculations and per-recipient history
Subscription status queries	`Subscription.status`	Webhook handlers and admin queries filter by subscription status
AI session analytics	Composite `(sessionId, createdAt)` on AIUsage	Session-scoped usage tracking and quota checks
Vector search	IVFFlat on `faq_chunks.embedding`	Fast approximate nearest-neighbor search (see pgvector setup)

Composite indexes like @@index([userId, createdAt]) are ordered — they accelerate queries that filter by userId and then sort by createdAt. A query filtering only by createdAt would not benefit from this index. Kit adds standalone @@index([createdAt]) where needed for time-only queries.

Adding Your Own Models

To add a new model to the schema:

Define the model in schema.prisma

Add your model definition to apps/boilerplate/prisma/schema.prisma. Follow the existing conventions:

prisma

model BlogPost {
  id        String   @id @default(uuid())
  userId    String
  title     String
  content   String   @db.Text
  slug      String   @unique
  published Boolean  @default(false)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  user      User     @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@index([userId])
  @@index([slug])
  @@index([createdAt])
}

Then add the reverse relation to the User model:

prisma

model User {
  // ... existing fields
  blogPosts  BlogPost[]
}

Push or migrate the schema

In development, push the schema directly:

bash

cd apps/boilerplate && npx prisma db push

For production, create a migration:

bash

cd apps/boilerplate && npx prisma migrate dev --name add-blog-post

See Migrations & Seeding for the full workflow.

Regenerate the Prisma client

After any schema change, regenerate the TypeScript types:

bash

cd apps/boilerplate && npx prisma generate

This updates the @prisma/client package so your new model is available with full type safety.

Create a query module

Add a query file at apps/boilerplate/src/lib/db/queries/blog-posts.ts following the existing pattern:

typescript

import { prisma } from '@/lib/db/prisma'
import type { BlogPost } from '@prisma/client'

export async function getBlogPostsByUserId(userId: string): Promise<BlogPost[]> {
  return prisma.blogPost.findMany({
    where: { userId },
    orderBy: { createdAt: 'desc' }
  })
}

export async function getBlogPostBySlug(slug: string): Promise<BlogPost | null> {
  return prisma.blogPost.findUnique({
    where: { slug }
  })
}

Export it from apps/boilerplate/src/lib/db/queries/index.ts to keep imports clean.

Schema Configuration

User Model

Field Groups

Indexes

Subscription Model

Key Fields

File Model

EmailLog Model

AI Models

AIUsage

AIConversation and AIMessage

FAQChunk Model (RAG)

The Vector Field

Credit System Models

CreditTransaction

BonusCreditPurchase

Relations Overview

Indexing Strategy

Adding Your Own Models

Define the model in schema.prisma

Push or migrate the schema

Regenerate the Prisma client

Create a query module

Related