Data Access

Database package layout, schema conventions, repositories, and multi-tenant query patterns.

Toast splits database work across two packages:

Package	Location	Purpose
`@toast/db`	`shared/db/`	Domain schema, migrations, seed data
`@toast/drizzle`	`packages/drizzle/`	Client creation, helper utilities, Drizzle re-exports

The rule of thumb:

if you are changing Toast’s domain data, you are in shared/db/
if you are changing generic Drizzle infrastructure, you are in packages/drizzle/

Where schema lives today

Toast’s table definitions currently live in a single file:

shared/db/src/schema.ts

When you add a new table, update that file and export the new table from there.

This may be split again later if the file grows too large, but contributors should treat schema.ts as the canonical location today.

Required table conventions

Every multi-tenant table must have:

siteId
an index on siteId
createdAt
updatedAt

Example:

export const newsletters = pgTable(
  'newsletters',
  {
    id: uuid('id').defaultRandom().primaryKey(),
    siteId: uuid('site_id')
      .notNull()
      .references(() => sites.id, { onDelete: 'cascade' }),
    name: varchar('name', { length: 255 }).notNull(),
    createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
    updatedAt: timestamp('updated_at', { withTimezone: true })
      .defaultNow()
      .notNull()
      .$onUpdate(() => new Date()),
  },
  (table) => [index('idx_newsletters_site_id').on(table.siteId)]
);

Naming rules:

Thing	Convention	Example
tables	`snake_case`	`newsletter_subscribers`
DB columns	`snake_case`	`created_at`
TS fields	`camelCase`	`createdAt`
indexes	`idx_{table}_{column}`	`idx_newsletters_site_id`

Repository pattern

Repositories receive the Drizzle database instance as a parameter. They do not call getDb() and they do not shape HTTP responses.

import { and, eq, type PostgresJsDatabase } from '@toast/drizzle';
import { newsletters } from '@toast/db';

export function createNewsletterRepository(db: PostgresJsDatabase) {
  return {
    findBySiteId(siteId: string) {
      return db.select().from(newsletters).where(eq(newsletters.siteId, siteId));
    },

    findById(siteId: string, id: string) {
      return db
        .select()
        .from(newsletters)
        .where(and(eq(newsletters.siteId, siteId), eq(newsletters.id, id)));
    },
  };
}

Repository rules:

every query is scoped by siteId
repositories are the only layer that imports table definitions
repositories return rows, not API response shapes

Migrations

After changing shared/db/src/schema.ts:

td db generate add-my-change
td db migrate

Migration rules:

never edit an existing migration file
make destructive changes in multiple steps where needed
test schema changes with real data before assuming they are safe

See Migrations for the workflow details.

Seeding and local data

Seed fixtures live under:

shared/db/src/fixtures/

There are two profiles:

default — single admin user from SEED_ADMIN_*
development — richer sample data with fixed demo users

Profile selection is handled by the seed loader, but in normal local setup you can rely on the documented default login:

admin@ghost.org
admin

See Seeding for the full fixture and profile behavior.

Quick commands

td db generate <name>
td db migrate
td db studio
td db seed
td schema
td migrations
td seed:status
td query "SELECT COUNT(*) FROM content"