# Database Schema

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

*Canonical: https://makerkit.dev/docs/tanstack-prisma/database/schema*

---

The TanStack Start Prisma SaaS Kit uses a multi-tenant database schema with organization-based isolation. All models are defined with [Prisma](https://www.prisma.io) in a single schema file, giving you full type safety from schema definition to queries.

This guide is part of the [Database Configuration](./overview) documentation.

{% callout title="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.
{% /callout %}

## 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.

{% callout type="warning" title="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.
{% /callout %}

## 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:

```prisma {% title="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:

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

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

**Key patterns:**

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

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

```bash
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](./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:

```prisma
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:**

```typescript
// 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:

```prisma
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:**

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

With relations defined, you can use `include`:

```typescript
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`:

```prisma
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.

{% faq
   title="Frequently Asked Questions"
   items=[
     {"question": "Where do I add my own models?", "answer": "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."},
     {"question": "What is the difference between a relation and a foreign key in Prisma?", "answer": "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."},
     {"question": "How do I add a unique constraint across multiple columns?", "answer": "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."},
     {"question": "Why do table names differ from model names?", "answer": "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 →](./migrations)