---
name: db-schema
description: "Use when changing PostgreSQL tables, columns, constraints, indexes, enums, foreign keys, relations, migrations, or Drizzle-derived Zod schemas in a wcz-layout app."
metadata:
  type: convention
  library: wcz-layout
---

# Database schema patterns

## Rules

- One table per file, named after the table (e.g. `bookTable` lives in `book.ts`). Never put multiple tables in one file, even when they are related — a parent and its children each get their own file.
- Use `uuid` for application-table primary keys and `snakeCase` for generating tables.
- For `uuid` columns, do not generate random values in the schema. The client generates the id with `uuidv7()` from `wcz-layout/utils` when creating the record.
- Always add `withTimezone: true` to timestamp columns.
- Always declare `onDelete` explicitly on every foreign key — never rely on the default. Pick the behavior from the business rule: `"cascade"` when the child is meaningless without its parent, `"restrict"` when the parent must not be deleted while children exist, `"set null"` for optional references.
- Index every foreign key column. Postgres does not create one for you.
- Define relations in a central `relations.ts` file.
- Declare enums with `pgEnum` in the table's own file, export them, and reference `enumValues` everywhere else — never re-declare the values.
- Generate the full client record schema with `createSelectSchema` from `drizzle-orm/zod`. Use `createInsertSchema`; override fields with stricter rules (trim, min/max) where needed.
- The second argument of `createSelectSchema` refines **existing columns only**. Adding a key
  that is not a column is a type error. Attach related data with `.extend()` after it.
- Do not auto-generate migrations unless the user explicitly asks. When asked, run `npm run db:generate`; migrations apply automatically at app startup while `DATABASE_AUTO_MIGRATE=true`.

## Examples

```ts
// src/server/db/schemas/library.ts
import { snakeCase, text, uuid } from "drizzle-orm/pg-core";

export const libraryTable = snakeCase.table("libraries", {
  id: uuid().primaryKey(),
  name: text().notNull(),
});

// src/server/db/schemas/book.ts
import { index, pgEnum, snakeCase, text, timestamp, uuid } from "drizzle-orm/pg-core";
import { libraryTable } from "./library";

export const bookStatusEnum = pgEnum("book_status", ["draft", "published"]);

export const bookTable = snakeCase.table(
  "books",
  {
    id: uuid().primaryKey(),
    title: text().notNull(),
    status: bookStatusEnum().notNull().default("draft"),
    libraryId: uuid()
      .notNull()
      .references(() => libraryTable.id, { onDelete: "cascade" }),
    createdAt: timestamp({ withTimezone: true }).defaultNow().notNull(),
  },
  (table) => [index().on(table.libraryId)],
);

// src/server/db/schemas/relations.ts
import { defineRelations } from "drizzle-orm";
import { bookTable } from "./book";
import { libraryTable } from "./library";

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/<table>.ts
import { createSelectSchema } from "drizzle-orm/zod";
import { t } from "wcz-layout/utils";
import type { z } from "zod";
import { bookTable } from "~/server/db/schemas/book";
import { PageSchema } from "./page";

export const BookSchema = createSelectSchema(bookTable, {
  title: (schema) =>
    schema
      .trim()
      .min(1, t("Validation.Required"))
      .max(255, t("Validation.MaxLength", { length: 255 })),
}).extend({
  pages: PageSchema.array().min(1, t("Validation.Required")),
});

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