Schema and migrations

Where the schema lives

One file per table in backend/src/db/, all re-exported from schema.ts. auth-schema.ts is generated by Better Auth — don't edit it by hand.

Change the schema

1. Edit or add a schema file. A new file needs an export line in schema.ts.

   import { pgTable, text, timestamp } from "drizzle-orm/pg-core";
   import { users } from "./auth-schema.js";
   
   export const notes = pgTable("notes", {
     id: text("id").primaryKey(),
     body: text("body").notNull(),
     authorId: text("author_id")
       .notNull()
       .references(() => users.id),
     createdAt: timestamp("created_at").defaultNow().notNull(),
   });
   

2. Generate the migration:

   yarn workspace backend db:generate
   

3. Read the SQL it wrote to backend/drizzle/, then commit it with the schema change.

4. Restart the backend. yarn backend:dev applies pending migrations before it starts.

CI regenerates migrations from the schema and fails if anything's missing.

Seed data

Data that must exist in every database — roles, features, grants — goes in a data-only migration:

cd backend && yarn drizzle-kit generate --custom --name my_seed

Write plain INSERTs into the file it creates. See Checking permissions for an example.

Query

import { eq } from "drizzle-orm";
import { db } from "../../db/index.js";
import { notes } from "../../db/schema.js";

const mine = await db.select().from(notes).where(eq(notes.authorId, ctx.session.user.id));

Use db.transaction() when two writes must succeed or fail together. See the Drizzle docs for the full query API.

Every write is logged automatically, tied to the request and user that made it. See Logging.