Drizzle — Schema Design¶

OWNER: urszula, maxim ALSO_USED_BY: boris, floris, hugo, jasper, marco, stef LAST_VERIFIED: 2026-03-26 GE_STACK_VERSION: drizzle-orm 0.45.x

Overview¶

Schema design is the foundation of every GE project. Agents writing table definitions MUST follow these patterns. The admin-ui schema at admin-ui/drizzle/schema/ is the reference implementation.

Table Definitions¶

Basic Pattern¶

import {
  pgTable,
  text,
  timestamp,
  uuid,
  index,
} from 'drizzle-orm/pg-core';

export const clients = pgTable('clients', {
  id: uuid('id').primaryKey().defaultRandom(),
  name: text('name').notNull(),
  email: text('email'),
  status: clientStatusEnum('status').default('active').notNull(),
  createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).defaultNow().notNull(),
}, (table) => ({
  statusIdx: index('clients_status_idx').on(table.status),
  nameIdx: index('clients_name_idx').on(table.name),
}));

CHECK: Agent is defining a new table. IF: The table has no createdAt or updatedAt columns. THEN: Add them. Every GE table gets both.

CHECK: Agent is defining a primary key. IF: The entity is user-facing or cross-referenced. THEN: Use uuid('id').primaryKey().defaultRandom(). IF: The entity is an agent identity. THEN: Use text('id').primaryKey() with the agent's slug name.

Naming Conventions¶

Column Names (BINDING)¶

TypeScript property	SQL column	Rule
`camelCase`	`snake_case`	Column string MUST be snake_case
`createdAt`	`created_at`	Timestamps follow convention
`vatNumber`	`vat_number`	Multi-word always underscored
`isActive`	`is_active`	Boolean prefix `is_` / `has_`

// CORRECT
name: text('name').notNull(),
contactEmail: text('contact_email'),
isActive: boolean('is_active').default(true),

// WRONG — SQL column not snake_case
contactEmail: text('contactEmail'),   // NEVER
isActive: boolean('isActive'),         // NEVER

ANTI_PATTERN: Using camelCase in the SQL column name string. FIX: Always pass snake_case as the column name argument to Drizzle column helpers.

Table Names (BINDING)¶

Plural: clients, agents, tasks (not client, agent, task).
snake_case: work_packages, audit_log, session_learnings.
No prefixes: agents (not ge_agents, not tbl_agents).

Index Names (BINDING)¶

Pattern: {table}_{column(s)}_idx

statusIdx: index('clients_status_idx').on(table.status),
agentTeamIdx: index('agents_team_idx').on(table.team),

Enums¶

All PostgreSQL enums live in drizzle/schema/enums.ts.

import { pgEnum } from 'drizzle-orm/pg-core';

export const taskStatusEnum = pgEnum('task_status', [
  'pending',
  'running',
  'completed',
  'failed',
  'cancelled'
]);

CHECK: Agent is adding a new status or type field. IF: The field has a fixed set of values. THEN: Define a pgEnum in enums.ts and import it in the domain schema. IF: The field values may change frequently or are user-defined. THEN: Use text with application-level validation instead.

ANTI_PATTERN: Defining enums inline in domain schema files. FIX: Centralize ALL enums in drizzle/schema/enums.ts.

ANTI_PATTERN: Using string unions instead of pgEnum for fixed status values. FIX: PostgreSQL enums enforce data integrity at the database level. Use them.

JSONB Columns¶

Use JSONB for flexible structured data. Always type it with $type<>():

import { jsonb } from 'drizzle-orm/pg-core';

skills: jsonb('skills').$type<string[]>(),
metadata: jsonb('metadata').$type<{
  source: string;
  version: number;
}>(),
config: jsonb('config').$type<Record<string, unknown>>(),

CHECK: Agent is adding a JSONB column. IF: The structure is well-known and stable. THEN: Define a TypeScript interface and use $type<MyInterface>(). IF: The structure is truly dynamic. THEN: Use $type<Record<string, unknown>>() — never any.

ANTI_PATTERN: Using jsonb('data') without $type<>(). FIX: Always attach a type annotation. Untyped JSONB defeats TypeScript strict mode.

ANTI_PATTERN: Storing normalized relational data in JSONB. FIX: If you query, filter, or join on the data — it belongs in its own table. READ_ALSO: wiki/docs/stack/postgresql/pitfalls.md (JSONB vs normalized)

Foreign Keys and References¶

import { uuid, text } from 'drizzle-orm/pg-core';

export const learnings = pgTable('learnings', {
  id: uuid('id').primaryKey().defaultRandom(),
  agentId: text('agent_id')
    .references(() => agents.id, { onDelete: 'cascade' })
    .notNull(),
  // ...
});

CHECK: Agent is defining a foreign key. IF: The parent entity deletion should cascade to children. THEN: Use { onDelete: 'cascade' }. IF: The parent entity deletion should be blocked when children exist. THEN: Use { onDelete: 'restrict' } (Drizzle default). IF: The reference should allow orphans (soft-delete parent). THEN: Use { onDelete: 'set null' } and make the column nullable.

Relations (for Query API)¶

Drizzle has two query modes: SQL-like and relational. Relations are needed ONLY for the relational query API (db.query.*):

import { relations } from 'drizzle-orm';

export const agentsRelations = relations(agents, ({ many }) => ({
  learnings: many(learnings),
  tasks: many(tasks),
}));

export const learningsRelations = relations(learnings, ({ one }) => ({
  agent: one(agents, {
    fields: [learnings.agentId],
    references: [agents.id],
  }),
}));

CHECK: Agent is using db.query.tableName.findMany({ with: { ... } }). IF: Relations are not defined for the involved tables. THEN: Define them. The relational query API silently returns empty arrays without relations — it does NOT throw.

ANTI_PATTERN: Defining relations but never using db.query. FIX: Relations have zero runtime cost if unused. But don't add them speculatively — add when needed.

Partial Indexes¶

Use partial indexes for queries that filter on a known condition:

import { sql } from 'drizzle-orm';

assignedTeamIdx: index('clients_assigned_team_idx')
  .on(table.assignedTeam)
  .where(sql`assigned_team IS NOT NULL`),

CHECK: Agent is adding an index on a nullable or status-filtered column. IF: Most queries filter to a subset (e.g., WHERE status = 'active'). THEN: Use a partial index with .where(sql\...`)`.

Custom Types and sql`` Templates¶

For PostgreSQL-specific features not covered by Drizzle helpers:

import { sql } from 'drizzle-orm';
import { customType } from 'drizzle-orm/pg-core';

// Custom CITEXT type
const citext = customType<{ data: string }>({
  dataType() {
    return 'citext';
  },
});

// Default value with SQL expression
updatedAt: timestamp('updated_at', { withTimezone: true })
  .defaultNow()
  .notNull()
  .$onUpdate(() => new Date()),

Domain Module Organization¶

GE splits schemas by business domain. Current admin-ui domains:

File	Domain	Key tables
`enums.ts`	Shared enums	All pgEnum definitions
`auth.ts`	Authentication	credentials, sessions
`agents.ts`	Agent system	agents, learnings
`tasks.ts`	Task execution	tasks
`clients.ts`	Client management	clients
`projects.ts`	Project tracking	projects
`teams.ts`	Team scaling	team_capacity
`billing.ts`	Commercialization	wallets, budgets, consumption
`financial.ts`	Invoicing	invoices, line_items
`knowledge.ts`	Learning pipeline	session_learnings, knowledge_patterns
`collaboration.ts`	Multi-agent	discussions, votes
`workpackages.ts`	DAG execution	work_packages, work_package_deps
`communication.ts`	Chat/messages	messages, chat_sessions
`metrics.ts`	Observability	agent_metrics
`hitl.ts`	Human-in-the-loop	questions, escalations
`system.ts`	System config	audit_log, billing_config

CHECK: Agent is creating a table that crosses domains. IF: The table is a join table between two domains. THEN: Place it in the domain that "owns" the relationship. IF: Unclear ownership. THEN: Create a new domain module. Do NOT stuff unrelated tables together.

Multi-Tenant Considerations¶

CHECK: Agent is designing a client-facing table. IF: The table stores client-specific data. THEN: Include a tenant_id or client_id column with a foreign key to clients.id. THEN: Add a composite index that includes the tenant column as the first key. READ_ALSO: wiki/docs/stack/postgresql/multi-tenant.md

export const projectFiles = pgTable('project_files', {
  id: uuid('id').primaryKey().defaultRandom(),
  clientId: uuid('client_id').references(() => clients.id).notNull(),
  projectId: uuid('project_id').references(() => projects.id).notNull(),
  // ...
}, (table) => ({
  clientProjectIdx: index('project_files_client_project_idx')
    .on(table.clientId, table.projectId),
}));

GE-Specific Conventions¶

One enum file — all enums in enums.ts, imported where needed.
Barrel exports — schema/index.ts re-exports all domain modules.
Agent IDs are text — 'urszula' not UUID. Human-readable by design.
No ORMs within the ORM — do not build abstraction layers on top of Drizzle.
Explicit index names — never rely on auto-generated names.
Always withTimezone: true on timestamps.
notNull() by default — only omit for genuinely optional columns.

Cross-References¶

READ_ALSO: wiki/docs/stack/drizzle/index.md READ_ALSO: wiki/docs/stack/drizzle/queries.md READ_ALSO: wiki/docs/stack/drizzle/migrations.md READ_ALSO: wiki/docs/stack/drizzle/pitfalls.md READ_ALSO: wiki/docs/stack/postgresql/indexing.md READ_ALSO: wiki/docs/stack/postgresql/multi-tenant.md READ_ALSO: wiki/docs/development/standards/naming.md