Database Patterns

Database schema conventions, migrations, and seeding for Toast.

Quick Reference

pnpm db:generate  # Generate migration from schema changes
pnpm db:migrate   # Apply pending migrations
pnpm db:studio    # Open Drizzle Studio to browse data
pnpm db:seed      # Seed development data

Schema Conventions

All tables in packages/db/src/schema.ts follow these conventions:

Required Fields

Every table should have:

export const myTable = pgTable('my_table', {
  id: uuid('id').defaultRandom().primaryKey(),
  siteId: uuid('site_id')
    .notNull()
    .references(() => sites.id),
  createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).defaultNow().notNull(),
});

Multi-tenancy

Every table must have site_id for multi-tenancy. This is non-negotiable.

siteId: uuid('site_id')
  .notNull()
  .references(() => sites.id),

Always add an index on site_id for query performance:

export const myTable = pgTable(
  'my_table',
  {
    // ... columns
  },
  (table) => [index('idx_my_table_site_id').on(table.siteId)]
);

Naming

Element	Convention	Example
Table names	snake_case	`user_sessions`
Column names	snake_case	`created_at`
TypeScript	camelCase	`createdAt`
Index names	`idx_{table}_{column}`	`idx_content_site_id`

Types

Use Case	PostgreSQL Type	Drizzle
Primary key	`uuid`	`uuid('id').defaultRandom().primaryKey()`
Foreign key	`uuid`	`uuid('site_id').references(() => ...)`
Timestamps	`timestamp with tz`	`timestamp('...', { withTimezone: true })`
Short text	`varchar(n)`	`varchar('name', { length: 255 })`
Long text	`text`	`text('body')`
JSON data	`jsonb`	`jsonb('metadata')`
Enums	PostgreSQL enum	`pgEnum('status', ['draft', 'published'])`

Enums

Define enums at the top of schema.ts:

export const contentStatusEnum = pgEnum('content_status', ['draft', 'published']);

export const content = pgTable('content', {
  status: contentStatusEnum('status').notNull().default('draft'),
});

Migrations

Creating Migrations

After editing schema.ts:

pnpm db:generate

This creates a new migration file in packages/db/migrations/.

Applying Migrations

# Local development
pnpm db:migrate

# Production (Railway)
# Runs automatically via preDeployCommand in railway.json

Migration Rules

Never edit existing migration files - Create a new migration instead
Make migrations additive - Add columns as nullable first, then backfill, then add NOT NULL
Test with production-like data - Migrations that work on empty tables may fail on real data
Keep migrations small - One logical change per migration

Rollback Strategy

Drizzle doesn't have automatic rollbacks. For breaking changes:

Create a "down" migration manually if needed
Test thoroughly in preview environments
Have a recovery plan before deploying

Seeding

Development Data

Fixtures live in packages/db/fixtures/:

packages/db/fixtures/
└── content.json    # Sample content in TipTap JSON format

Run the seed script:

pnpm db:seed

The seed script:

Creates a default site ("Default Site") if none exists
Creates an admin user for that site if none exists (with Better Auth-compatible password hashing)
Clears existing content for that site
Inserts all fixtures

Admin User Seeding

The seed script creates a default admin user for authentication testing. Configure via environment variables:

Variable	Default	Description
`SEED_ADMIN_EMAIL`	`admin@example.com`	Email for the admin user
`SEED_ADMIN_PASSWORD`	(generated)	Password - printed to console if omitted
`SEED_ADMIN_NAME`	`Admin`	Display name for the admin user

The script is idempotent - running it multiple times won't duplicate users:

# First run
pnpm db:seed
#   Site "Default Site" created (id: xxx)
#   Admin user created: admin@example.com
#   Password: <generated>

# Second run
pnpm db:seed
#   Site "Default Site" already exists (id: xxx)
#   Admin user admin@example.com already exists

Fixture Format

Content uses TipTap JSON structure:

{
  "title": "Example Post",
  "status": "published",
  "body": {
    "type": "doc",
    "content": [
      {
        "type": "paragraph",
        "content": [{ "type": "text", "text": "Hello world" }]
      }
    ]
  }
}

Querying

Using the Database Client

import { getDb } from '@toast/db';
import { content, sites } from '@toast/db';
import { eq, and } from 'drizzle-orm';

const db = getDb();

// Simple query
const items = await db.select().from(content);

// With conditions
const published = await db
  .select()
  .from(content)
  .where(and(eq(content.siteId, siteId), eq(content.status, 'published')));

// With joins
const withSite = await db.select().from(content).innerJoin(sites, eq(content.siteId, sites.id));

Repository Pattern

Don't access the database directly from services. Use repositories:

// Good - in repository
export async function findBySiteId(siteId: string) {
  const db = getDb();
  return db.select().from(content).where(eq(content.siteId, siteId));
}

// Bad - in service
const db = getDb(); // Don't do this in services

See ADR-004 for why.

Connection Management

The database client is initialized lazily:

// packages/db/src/client.ts
let db: PostgresJsDatabase | null = null;

export function getDb(): PostgresJsDatabase {
  if (!db) {
    db = drizzle(postgres(process.env.DATABASE_URL!));
  }
  return db;
}

For health checks:

import { checkConnection } from '@toast/db';

const isHealthy = await checkConnection(); // Returns boolean

Database Patterns

On this page