---
name: db-schema
description: "Use when: defining or updating Drizzle ORM table schemas, relations, enums, migrations, or Zod schemas derived from database models."
---

# Database Schema Patterns

## Rules

- Always use `uuid` for primary keys, and `snakeCase` for generating tables.
- Always add `withTimezone: true` to timestamp columns.
- Always add `onDelete: "cascade"` on foreign keys for child/dependent tables.
- Define relations in a central `relations.ts` file.
- Generate Zod schemas with `createSelectSchema`; override fields with stricter rules (trim, min/max) where needed.
- Use `.transform()` on all Zod schemas that include related data to assign parent IDs.
- Do not auto-generate migrations unless the user explicitly asks.

## File Placement

```
drizzle-orm/pg-core             - table, column types, relations, and defineRelations utility
zod                             - Zod
src/server/db/schemas/          - Drizzle table schemas and relations
src/server/db/migrations/       - Drizzle migration files
src/lib/schemas/                - Zod schemas (shared between client and server)
wcz-layout/utils                - `t` translation function
```

## Examples

```ts
// src/server/db/schemas/<feature>.ts
export const bookTable = snakeCase.table("books", {
  id: uuid().primaryKey(),
  title: text().notNull(),
  libraryId: uuid()
    .notNull()
    .references(() => libraryTable.id, { onDelete: "cascade" }),
});

// src/server/db/schemas/relations.ts
export const relations = defineRelations(
  { libraryTable, bookTable },
  ({ one, many, libraryTable: library, bookTable: book }) => ({
    libraryTable: {
      books: many.bookTable(),
    },
    bookTable: {
      library: one.libraryTable({ from: book.libraryId, to: library.id }),
    },
  }),
);

// src/lib/schemas/<feature>.ts
export const BookSchema = createSelectSchema(bookTable, {
  title: (schema) =>
    schema
      .trim()
      .min(1, t("Validation.Required"))
      .max(255, t("Validation.MaxLength", { length: 255 })),
  pages: PageSchema.array().min(1, t("Validation.Required")),
}).transform((data) => ({
  ...data,
  pages: data.pages.map((page) => ({ ...page, bookId: data.id })),
}));

export type Book = z.infer<typeof BookSchema>;
```