# Database Schema

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

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

---

The TanStack Start Drizzle SaaS Kit uses a multi-tenant database schema with organization-based isolation. All tables are defined using [Drizzle ORM](https://orm.drizzle.team) in TypeScript, giving you full type safety from schema definition to queries.

## Schema Files

| File | Purpose |
|------|---------|
| `packages/database/src/schema/core.ts` | Better Auth tables (auto-generated, do not edit) |
| `packages/database/src/schema/schema.ts` | Your application tables (extend here) |

The `core.ts` file is generated by Better Auth and contains authentication tables. When you run `pnpm --filter @kit/better-auth schema:generate`, it regenerates this file based on your auth configuration.

{% callout type="warning" title="Never edit core.ts" %}
Any changes to `core.ts` will be overwritten the next time you run `schema:generate`. Always add your custom tables to `schema.ts`.
{% /callout %}

## Extending the Schema

Add your application tables to `packages/database/src/schema/schema.ts`:

```typescript {% title="packages/database/src/schema/schema.ts" %}
export * from './core';

// Add your own tables below
```

### Example: Adding a Projects Table

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

```typescript {% title="packages/database/src/schema/schema.ts" %}
import { relations, sql } from 'drizzle-orm';
import { index, pgTable, text, timestamp } from 'drizzle-orm/pg-core';

import { organization, user } from './core';

export * from './core';

export const projects = pgTable(
  'projects',
  {
    id: text('id')
      .primaryKey()
      .default(sql`gen_random_uuid()`),
    name: text('name').notNull(),
    description: text('description'),
    organizationId: text('organization_id')
      .notNull()
      .references(() => organization.id, { onDelete: 'cascade' }),
    createdById: text('created_by_id')
      .references(() => user.id, { onDelete: 'set null' }),
    createdAt: timestamp('created_at').defaultNow().notNull(),
    updatedAt: timestamp('updated_at')
      .defaultNow()
      .$onUpdate(() => new Date())
      .notNull(),
  },
  (table) => [
    index('projects_organizationId_idx').on(table.organizationId),
    index('projects_createdById_idx').on(table.createdById),
  ],
);

export const projectsRelations = relations(projects, ({ one }) => ({
  organization: one(organization, {
    fields: [projects.organizationId],
    references: [organization.id],
  }),
  createdBy: one(user, {
    fields: [projects.createdById],
    references: [user.id],
  }),
}));
```

**Key patterns:**

1. **UUID primary keys** with `gen_random_uuid()` (PostgreSQL built-in, requires PostgreSQL 13+)
2. **Foreign keys with cascade** for organization (delete org = delete projects)
3. **Indexes on foreign keys** for query performance
4. **Relations** for type-safe joins with the Query API
5. **`$onUpdate`** for automatic timestamp updates

After adding a table, generate and apply migrations:

```bash
pnpm --filter @kit/database drizzle:generate
pnpm --filter @kit/database drizzle:migrate
```

## Authentication Tables

The authentication tables are defined in Better Auth and provide the foundation for user management.

### Users

The 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 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 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 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 table `two_factor` 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) |

### Rate Limit

The table `rate_limit` 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 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 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 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 table `organization_role` 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 |
| createdAt | timestamp | Organization role creation date |
| updatedAt | timestamp | Organization role last update date |

## Billing Tables

### Subscriptions

The 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 | Date when sub will be cancelled |
| canceledAt | timestamp | Canceled at date |
| endedAt | timestamp | Ended at date |
| cancelAtPeriodEnd | boolean | Boolean to indicate if the subscription should be canceled at the end of the current period |

## Multi-Tenancy Model

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

```typescript
export const project = pgTable('project', {
  id: text('id').primaryKey(),
  organizationId: text('organization_id').notNull()
    .references(() => organization.id, { onDelete: 'cascade' }),
  name: text('name').notNull(),
});
```

**Always filter queries by organizationId:**

```typescript
// Using auth context (recommended)
const ctx = createOrgAuthContext(userId, organizationId, role);
const docs = await db.select().from(documents).where(ctx.org(documents));

// Manual filter
const docs = await db
  .select()
  .from(documents)
  .where(eq(documents.organizationId, organizationId));
```

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

## Defining Relations

Relations enable type-safe joins with Drizzle's Query API. Define them after your table:

```typescript
export const projectsRelations = relations(projects, ({ one, many }) => ({
  // Many-to-one
  organization: one(organization, {
    fields: [projects.organizationId],
    references: [organization.id],
  }),
  // One-to-many
  tasks: many(tasks),
}));
```

**Rules for relations:**

1. Define relations in `schema.ts`, not in `core.ts`
2. Both sides of a relation should be defined for bidirectional queries
3. Relations don't create database constraints (use `references()` for that)

With relations defined, you can use the Query API:

```typescript
const projectWithTasks = await db.query.projects.findFirst({
  where: eq(projects.id, projectId),
  with: {
    organization: true,
    tasks: {
      limit: 10,
      orderBy: [desc(tasks.createdAt)],
    },
  },
});
```

## Column Types Reference

Common column types for PostgreSQL:

| Drizzle Type | PostgreSQL Type | Use Case |
|--------------|-----------------|----------|
| `text('name')` | `TEXT` | Strings (prefer over varchar) |
| `integer('count')` | `INTEGER` | Whole numbers |
| `bigint('amount', { mode: 'number' })` | `BIGINT` | Large numbers, timestamps in ms |
| `boolean('active')` | `BOOLEAN` | True/false |
| `timestamp('created_at')` | `TIMESTAMP` | Dates with time |
| `json('metadata')` | `JSON` | Structured data (use JSONB for queries) |
| `jsonb('config')` | `JSONB` | Queryable structured data |

## Index Best Practices

The core tables already include primary key and foreign key indexes. Add indexes based on your query patterns:

```typescript
import { index, uniqueIndex } from 'drizzle-orm/pg-core';

export const projects = pgTable(
  'projects',
  {
    // columns...
  },
  (table) => [
    // Index on foreign key (always do this)
    index('projects_organizationId_idx').on(table.organizationId),

    // Composite index for common query pattern
    index('projects_org_status_idx').on(table.organizationId, table.status),

    // Unique constraint
    uniqueIndex('projects_org_slug_uidx').on(table.organizationId, table.slug),
  ],
);
```

### Index Recommendations

| Table | Suggested Index | Purpose |
|-------|-----------------|---------|
| `account` | `unique(provider_id, account_id)` | Prevent duplicate OAuth accounts |
| `organization` | `index(stripe_customer_id)` | Webhook lookups |
| `member` | `unique(organization_id, user_id)` | Prevent duplicate memberships |
| `invitation` | `index(email, status)` | Accept flow lookups |
| `subscription` | `index(reference_id)` | App subscription lookups |

## Common Mistakes to Avoid

**Missing organizationId on tenant tables**: Every table that stores tenant data needs an `organizationId` column with a foreign key to `organization`. Without it, you can't filter by tenant.

**Forgetting to index foreign keys**: PostgreSQL doesn't automatically index foreign keys. Always add an index on columns used in JOINs or WHERE clauses.

**Using varchar instead of text**: In PostgreSQL, there's no performance difference between `text` and `varchar(n)`. Use `text` unless you need to enforce a maximum length at the database level.

**Defining relations but not foreign keys**: Relations are Drizzle metadata for the Query API. They don't create database constraints. Use `references()` for data integrity, relations for convenient querying. You typically want both.

{% faq
   title="Frequently Asked Questions"
   items=[
     {"question": "Can I modify the core.ts schema file?", "answer": "No, core.ts is auto-generated by Better Auth when you run schema:generate. Any manual changes will be overwritten. Add your custom tables and relations to schema.ts instead."},
     {"question": "What is the difference between relations and foreign keys?", "answer": "Foreign keys (defined with references()) create database constraints that enforce referential integrity. Relations are Drizzle-specific metadata that enable the Query API 'with' syntax for type-safe joins. You typically want both: foreign keys for data integrity, relations for convenient querying."},
     {"question": "Should I use text or varchar for strings?", "answer": "Prefer text for most string columns in PostgreSQL. There is no performance difference, and text has no length limit. Use varchar only when you need to enforce a maximum length at the database level."},
     {"question": "How do I add a unique constraint across multiple columns?", "answer": "Use uniqueIndex in the table function: uniqueIndex('org_slug_uidx').on(table.organizationId, table.slug). This creates a composite unique constraint ensuring no two rows have the same combination of values."},
     {"question": "Why use gen_random_uuid() instead of application-generated UUIDs?", "answer": "gen_random_uuid() is a PostgreSQL built-in function (13+) that generates UUIDs on the database server. This is slightly more efficient than generating in the application, and ensures the ID exists even if you insert directly via SQL."}
   ]
/%}

---

**Next:** [Migrations](./migrations)