Database Schema Overview

Understand the database schema structure, tables, relationships, and multi-tenancy model.

The SaaS Kit uses a multi-tenant database schema with organization-based isolation. All tables are defined using Drizzle ORM.

Core Schema

The core schema is defined in the packages/database/src/schema/core.ts file, where the kit defines the core tables and relationships.

Extending the Schema

You can extend the schema by adding your own Drizzle ORM definitions to the packages/database/src/schema/schema.ts file:

packages/database/src/schema/schema.ts

export * from './core';
// Add your own tables here

Authentication Tables

The authentication tables contain the tables for the authentication system as defined in Better Auth.

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

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.

For example, the table project could be defined as follows, where organizationId is a foreign key to the organization table:

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

Note: Always make sure to filter queries by organizationId to ensure data isolation:

// Correct - respects multi-tenancy
const projects = await db.from(project)
  .where(eq(project.organizationId, currentOrgId));

Index Suggestions

Core tables already include PK/unique/FK indexes. If query patterns match, consider adding:

Table	Suggested index	Why
`account`	`unique(provider_id, account_id)`	Fast provider lookup, prevent dup accounts
`verification`	`index(identifier, value)`	Fast token lookup by identifier + code
`organization`	`index(stripe_customer_id)`	Resolve org from billing webhooks
`organization_role`	`index(organization_id, role)` or `unique(organization_id, role, permission)`	Role permission fetch + dedup
`member`	`unique(organization_id, user_id)`	Fast membership lookup, prevent dup
`invitation`	`index(organization_id, status)` (opt: `email, organization_id, status`)	Pending invites list + accept flow
`subscription`	`index(reference_id)`, `index(stripe_customer_id)`, `index(stripe_subscription_id)`	App + webhook lookups
`rate_limit`	`unique(key)`	Upsert by key, avoid full scan

Next: Migrations →