Database Schema

Database schema structure for the TanStack Start Prisma SaaS Kit: tables, relationships, multi-tenancy, and extending the schema.

The TanStack Start Prisma SaaS Kit uses a multi-tenant database schema with organization-based isolation. All models are defined with Prisma in a single schema file, giving you full type safety from schema definition to queries.

This guide is part of the Database Configuration documentation.

Definition

Multi-tenancy is an architecture where a single database serves multiple isolated customers (tenants) by filtering data on a tenant identifier like organizationId. Each organization sees only their own data.

Schema File

The entire data model lives in a single Prisma schema file:

File	Purpose
`packages/database/src/prisma/schema.prisma`	All models: auth tables (managed by Better Auth) and your application tables

The auth-related models are kept in sync by Better Auth. Running pnpm --filter @kit/better-auth schema:generate regenerates the auth models inside schema.prisma based on your auth configuration.

Do not hand-edit generated auth models

The auth models in schema.prisma are regenerated by Better Auth's schema:generate. Add your own application models to the same file, but avoid manually editing the auth models — they may be overwritten.

Extending the Schema

Add your application models to packages/database/src/prisma/schema.prisma.

Example: Adding a Projects Model

Here's a complete example of adding a new model with foreign keys, indexes, and relations:

packages/database/src/prisma/schema.prisma

model Project {
  id             String       @id
  name           String
  description    String?
  organizationId String
  createdById    String?
  createdAt      DateTime     @default(now())
  updatedAt      DateTime     @updatedAt
  organization   Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)
  createdBy      User?        @relation(fields: [createdById], references: [id], onDelete: SetNull)
  @@index([organizationId])
  @@index([createdById])
  @@map("project")
}

Then add the reverse relation fields to the related models:

model Organization {
  // existing fields...
  projects Project[]
}
model User {
  // existing fields...
  projects Project[]
}

Key patterns:

Explicit String @id primary keys (application- or Better Auth-generated ids)
Foreign keys with onDelete: Cascade for organization (delete org = delete projects)
Indexes on foreign keys with @@index for query performance
Relations on both sides for type-safe include
@@map to control the database table name

After changing the schema, generate the client and create/apply a migration:

pnpm --filter @kit/database prisma:generate
pnpm --filter @kit/database prisma:migrate

Authentication Tables

The authentication models are managed by Better Auth and provide the foundation for user management.

Users

The User model (table user) stores user accounts and profile data.

Column	Type	Description
id	text	Primary key
name	text	Display name
email	text	Unique email address
createdAt	timestamp	User creation date
updatedAt	timestamp	User last update date
banReason	text	Reason for ban
banExpires	timestamp	Ban expiration date
emailVerified	boolean	Email verification status
image	text	Profile picture URL
twoFactorEnabled	boolean	2FA enabled
role	text	Admin role (for admin features)
banned	boolean	Ban status
stripeCustomerId	text	Stripe customer ID

Sessions

The Session model (table session) stores authentication sessions.

Column	Type	Description
id	text	Primary key
token	text	Unique session token
userId	text	FK to users (cascade delete)
expiresAt	timestamp	Session expiration
createdAt	timestamp	Session creation date
updatedAt	timestamp	Session last update date
ipAddress	text	Client IP address
userAgent	text	Browser user agent
impersonatedBy	text	Admin user ID if impersonating
activeOrganizationId	text	Current organization context

Accounts

The Account model (table account) stores OAuth provider accounts and password credentials.

Column	Type	Description
id	text	Primary key
providerId	text	Provider name (google, github, credential)
accountId	text	Provider's account ID
userId	text	FK to users (cascade delete)
accessToken	text	OAuth access token
refreshToken	text	OAuth refresh token
password	text	Hashed password (for credential auth)
idToken	text	OAuth ID token
accessTokenExpiresAt	timestamp	OAuth access token expiration
refreshTokenExpiresAt	timestamp	OAuth refresh token expiration
scope	text	OAuth scope
createdAt	timestamp	Account creation date
updatedAt	timestamp	Account last update date

Verifications

The Verification model (table verification) stores email verification and password reset tokens.

Column	Type	Description
id	text	Primary key
identifier	text	Email or phone number
value	text	Verification code/token
expiresAt	timestamp	Token expiration
createdAt	timestamp	Verification creation date
updatedAt	timestamp	Verification last update date

Two Factors

The TwoFactor model (table twoFactor) stores TOTP 2FA secrets and backup codes.

Column	Type	Description
id	text	Primary key
secret	text	TOTP secret
backupCodes	text	Backup codes
userId	text	FK to users (cascade delete)
verified	boolean	Whether the factor is verified
failedVerificationCount	integer	Failed verification attempts
lockedUntil	timestamp	Lockout expiration

Rate Limit

The RateLimit model (table rateLimit) stores rate limiting state for the sliding window algorithm. Used by Better Auth and the custom rate limit service.

Column	Type	Description
id	text	Primary key (equals the rate limit key)
key	text	Rate limit key (e.g., `api:upload:user123`)
count	integer	Request count in current window
lastRequest	bigint	Timestamp of last request (ms)

See: Rate Limit Service

Organization Tables

Organizations

The Organization model (table organization) stores team/workspace containers.

Column	Type	Description
id	text	Primary key
name	text	Organization name
slug	text	Unique URL-friendly slug
logo	text	Logo URL
metadata	text	JSON metadata
createdAt	timestamp	Organization creation date
stripeCustomerId	text	Stripe customer ID

Members

The Member model (table member) links users to organizations with roles.

Column	Type	Description
id	text	Primary key
organizationId	text	FK to organizations (cascade delete)
userId	text	FK to users (cascade delete)
role	text	Role (owner, admin, member)
createdAt	timestamp	Member creation date

Invitations

The Invitation model (table invitation) tracks pending organization invitations.

Column	Type	Description
id	text	Primary key
organizationId	text	FK to organizations (cascade delete)
email	text	Invitee's email
role	text	Role to assign when accepted
status	text	pending, accepted, or declined
expiresAt	timestamp	Invitation expiration
inviterId	text	FK to users (cascade delete)
createdAt	timestamp	Invitation creation date

Organization Roles

The OrganizationRole model (table organizationRole) stores custom RBAC permission definitions per organization.

Column	Type	Description
id	text	Primary key
organizationId	text	FK to organizations (cascade delete)
role	text	Role name
permission	text	Permission name
metadata	text	JSON metadata
createdAt	timestamp	Organization role creation date
updatedAt	timestamp	Organization role last update date

Billing Tables

Subscriptions

The Subscription model (table subscription) stores billing and subscription data.

Column	Type	Description
id	text	Primary key
plan	text	Plan identifier
referenceId	text	Organization/user reference
stripeCustomerId	text	Stripe customer ID
stripeSubscriptionId	text	Stripe subscription ID
status	text	active, trialing, canceled, etc.
periodStart	timestamp	Current billing period start
periodEnd	timestamp	Current billing period end
seats	integer	Number of seats
trialStart	timestamp	Trial start date
trialEnd	timestamp	Trial end date
cancelAt	timestamp	Scheduled cancellation date
canceledAt	timestamp	When subscription was canceled
endedAt	timestamp	When subscription ended
cancelAtPeriodEnd	boolean	Cancel at end of current period flag

Multi-Tenancy Model

All tenant data is isolated by organization. When adding custom models, always include an organizationId foreign key:

model Project {
  id             String       @id
  organizationId String
  name           String
  organization   Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)
  @@index([organizationId])
  @@map("project")
}

Always filter queries by organizationId to ensure data isolation:

// Correct - respects multi-tenancy
const projects = await db.project.findMany({
  where: { organizationId: currentOrgId },
});

The onDelete: Cascade ensures that when an organization is deleted, all related records are automatically removed.

Defining Relations

Relations enable type-safe include in Prisma queries. Define both sides of the relation:

model Project {
  id             String       @id
  organizationId String
  organization   Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)
  tasks          Task[]
  @@index([organizationId])
  @@map("project")
}
model Task {
  id        String  @id
  projectId String
  project   Project @relation(fields: [projectId], references: [id], onDelete: Cascade)
  @@index([projectId])
  @@map("task")
}

Rules for relations:

Define the relation field with @relation on the side that holds the foreign key
Add the reverse relation field (e.g. tasks Task[]) on the other model, or Prisma will fail schema validation
Use onDelete to control cascade behavior at the database level

With relations defined, you can use include:

const projectWithTasks = await db.project.findFirst({
  where: { id: projectId },
  include: {
    organization: true,
    tasks: {
      take: 10,
      orderBy: { createdAt: 'desc' },
    },
  },
});

Column Types Reference

Common Prisma scalar types for PostgreSQL:

Prisma Type	PostgreSQL Type	Use Case
`String`	`TEXT`	Strings
`Int`	`INTEGER`	Whole numbers
`BigInt`	`BIGINT`	Large numbers, timestamps in ms
`Boolean`	`BOOLEAN`	True/false
`DateTime`	`TIMESTAMP`	Dates with time
`Json`	`JSONB`	Queryable structured data

Index Best Practices

The core tables already include primary key, unique, and foreign key indexes. Add indexes based on your query patterns with @@index and @@unique:

model Project {
  id             String @id
  organizationId String
  slug           String
  status         String
  // Composite index for a common query pattern
  @@index([organizationId, status])
  // Unique constraint across two columns
  @@unique([organizationId, slug])
  @@map("project")
}

Index Recommendations

If your query patterns match, consider adding:

Table	Suggested Index	Purpose
`account`	`@@unique([providerId, accountId])`	Prevent duplicate OAuth accounts
`organization`	`@@index([stripeCustomerId])`	Webhook lookups
`member`	`@@unique([organizationId, userId])`	Prevent duplicate memberships
`invitation`	`@@index([email, status])`	Accept flow lookups
`subscription`	`@@index([referenceId])`	App subscription lookups

Common Mistakes to Avoid

Missing organizationId on tenant tables: Every model that stores tenant data needs an organizationId field with a relation to Organization. Without it, you can't filter by tenant.

Forgetting to index foreign keys: PostgreSQL doesn't automatically index foreign keys. Add @@index on columns used in include or where clauses.

Forgetting reverse relations: Adding a @relation field without the reverse field on the other model causes schema validation errors. Always add both sides.

Missing onDelete: Without onDelete: Cascade (or SetNull), deleting a parent can fail or leave orphaned rows. Choose the behavior that fits the data.

Frequently Asked Questions

Where do I add my own models?

Add your application models directly to packages/database/src/prisma/schema.prisma. The auth models in that file are managed by Better Auth's schema:generate, but your custom models live alongside them.

What is the difference between a relation and a foreign key in Prisma?

In Prisma the @relation attribute both defines the type-safe relation for include and, together with fields/references and onDelete, creates the database foreign key constraint. You get referential integrity and convenient querying from the same declaration.

How do I add a unique constraint across multiple columns?

Use @@unique in the model block, e.g. @@unique([organizationId, slug]). This creates a composite unique constraint ensuring no two rows share the same combination of values.

Why do table names differ from model names?

Prisma models use PascalCase (User, OrganizationRole) while the actual database tables use the names set with @@map (user, organizationRole). This keeps the generated TypeScript API idiomatic while controlling SQL table names.

Next: Migrations →