---
name: database-schema
description: >
  Design PostgreSQL tables with Drizzle ORM pgTable and derive Zod validation
  schemas via createSelectSchema from drizzle-orm/zod. Covers uuid primary keys,
  column types, Zod refinements (trim, min, max), and type inference. Drizzle
  tables live in src/lib/db/schemas/, Zod schemas in src/schemas/. Activate
  when creating or modifying database tables or validation schemas.
type: core
library: wcz-layout
library_version: "7.6.1"
sources:
  - "wcz-layout:src/lib/db/schemas/"
  - "wcz-layout:src/schemas/"
  - "drizzle-orm:docs/zod.md"
---

# Database Schema Design

## Setup

Database schema files live in two directories:

```
src/lib/db/schemas/todo.ts       # Drizzle pgTable definition
src/schemas/todo.ts              # Zod schema derived from the table
```

## Core Patterns

### Drizzle table definition

```typescript
// src/lib/db/schemas/todo.ts
import { boolean, pgTable, text, timestamp, uuid } from "drizzle-orm/pg-core";

export const todoTable = pgTable("todos", {
  id: uuid().primaryKey(),
  name: text().notNull(),
  description: text(),
  isCompleted: boolean().notNull().default(false),
  createdBy: text().notNull(),
  createdAt: timestamp().notNull().defaultNow(),
  updatedAt: timestamp().notNull().defaultNow(),
});
```

All tables use `uuid().primaryKey()`. IDs are generated client-side with `uuidv7()`.

### Deriving Zod schema from Drizzle

```typescript
// src/schemas/todo.ts
import { createSelectSchema } from "drizzle-orm/zod";
import { todoTable } from "~/lib/db/schemas/todo";

export const TodoSchema = createSelectSchema(todoTable, {
  name: (schema) => schema.trim().min(1),
  description: (schema) => schema.trim().nullish(),
});

export type Todo = typeof TodoSchema._type;
```

`createSelectSchema` generates a Zod schema matching the table columns. The second argument lets you refine individual fields — add `.trim()`, `.min()`, `.max()`, or replace the schema entirely.

### Insert schema for mutations

```typescript
// src/schemas/todo.ts
import { createInsertSchema } from "drizzle-orm/zod";

export const TodoInsertSchema = createInsertSchema(todoTable, {
  name: (schema) => schema.trim().min(1),
});
```

Use `createInsertSchema` when you need a schema for insert operations that omits fields with defaults (like `id`, `createdAt`).

### Type inference

```typescript
import type { InferSelectModel, InferInsertModel } from "drizzle-orm";
import { todoTable } from "~/lib/db/schemas/todo";

type Todo = InferSelectModel<typeof todoTable>;
type NewTodo = InferInsertModel<typeof todoTable>;
```

Prefer the Zod `_type` for runtime-validated types. Use Drizzle inference types when you need the raw DB shape without validation.

## Common Mistakes

### CRITICAL Writing Zod schemas manually instead of deriving from Drizzle

Wrong:

```typescript
import { z } from "zod";

export const TodoSchema = z.object({
  id: z.string().uuid(),
  name: z.string().trim().min(1),
  isCompleted: z.boolean(),
});
```

Correct:

```typescript
import { createSelectSchema } from "drizzle-orm/zod";
import { todoTable } from "~/lib/db/schemas/todo";

export const TodoSchema = createSelectSchema(todoTable, {
  name: (schema) => schema.trim().min(1),
});
```

Manual Zod schemas drift from the database when columns are added or renamed. `createSelectSchema` keeps them in sync automatically.

Source: maintainer interview

### HIGH Placing schema files in wrong directory

Wrong:

```
src/lib/db/schemas/todo.ts      # Contains both pgTable AND Zod schema
```

Correct:

```
src/lib/db/schemas/todo.ts      # Only pgTable definition
src/schemas/todo.ts             # Zod schema derived from the table
```

Drizzle table definitions and Zod validation schemas live in separate directories. Mixing them in one file violates the project structure convention.

Source: consumer project structure

### HIGH Forgetting uuid primary key convention

Wrong:

```typescript
import { serial, pgTable, text } from "drizzle-orm/pg-core";

export const todoTable = pgTable("todos", {
  id: serial().primaryKey(),
  name: text().notNull(),
});
```

Correct:

```typescript
import { uuid, pgTable, text } from "drizzle-orm/pg-core";

export const todoTable = pgTable("todos", {
  id: uuid().primaryKey(),
  name: text().notNull(),
});
```

All tables use `uuid().primaryKey()`. IDs are generated client-side with `uuidv7()` for optimistic inserts through TanStack DB collections.

Source: consumer project example

### HIGH Tension: Type safety vs. rapid prototyping

Deriving Zod schemas from Drizzle tables and wiring them through forms requires more setup than quick `useState` + manual validation. Always use the enforced pattern — `createSelectSchema` + `useLayoutForm` — even for simple forms.

See also: skills/forms-validation/SKILL.md § Core Patterns

---

See also:

- skills/api-routes/SKILL.md — Schemas feed into validationMiddleware(Schema).
- skills/tanstack-db-collections/SKILL.md — Same Zod schema used as collection schema option.
- skills/forms-validation/SKILL.md — Zod schemas used as form validators.