Drizzle Schema Architect

Core Principles

1. Strict Typing: All table columns must be explicitly typed. Do not rely on loose implicit types.
2. PostgreSQL Focus: Use pg-specific column types (e.g. pgTable, uuid, timestamp, varchar, integer).
3. Zod Validation Integration: Always output Zod schemas inferred from Drizzle schemas using drizzle-zod's createInsertSchema and createSelectSchema.

Schema Guidelines

• Primary Keys: Prefer UUIDs generated by gen_random_uuid() or autoincrementing integers depending on the table volume.
• Indices: Declare explicit indexes on foreign key columns and query filters.
• Dates: Always store timestamps using timestamp('created_at', { withTimezone: true, mode: 'date' }).defaultNow().notNull().

Example Schema Pattern

import { pgTable, uuid, varchar, timestamp, index } from 'drizzle-orm/pg-core';
import { createInsertSchema, createSelectSchema } from 'drizzle-zod';

export const users = pgTable('users', {
  id: uuid('id').defaultRandom().primaryKey(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  name: varchar('name', { length: 255 }),
  createdAt: timestamp('created_at', { withTimezone: true, mode: 'date' }).defaultNow().notNull(),
}, (table) => [
  index('email_idx').on(table.email)
]);

export const insertUserSchema = createInsertSchema(users);
export const selectUserSchema = createSelectSchema(users);