Database Schema

The Next.js Drizzle SaaS Kit uses a multi-tenant database schema with organization-based isolation. All tables are defined using Drizzle ORM in TypeScript, giving you full type safety from schema definition to queries.

Schema Files

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.

Any changes to core.ts will be overwritten the next time you run schema:generate. Always add your custom tables to schema.ts.

Extending the Schema

Add your application tables to 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:

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:

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.

Sessions

The table session stores authentication sessions.

Accounts

The table account stores OAuth provider accounts and password credentials.

Verifications

The table verification stores email verification and password reset tokens.

Two Factors

The table two_factor stores TOTP 2FA secrets and backup codes.

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.

See: Rate Limit Service

Organization Tables

Organizations

The table organization stores team/workspace containers.

Members

The table member links users to organizations with roles.

Invitations

The table invitation tracks pending organization invitations.

Organization Roles

The table organization_role stores custom RBAC permission definitions per organization.

Billing Tables

Subscriptions

The table subscription stores billing and subscription data.

Multi-Tenancy Model

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

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:

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

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:

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:

Index Best Practices

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

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

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.

Next: Migrations