Skip to content

Drizzle — Migrations

OWNER: urszula, maxim ALSO_USED_BY: boris, floris, hugo, jasper, arjan LAST_VERIFIED: 2026-03-26 GE_STACK_VERSION: drizzle-kit 0.31.x

Overview

Drizzle Kit generates SQL migration files from schema changes. GE uses the generate + migrate workflow for production. The push command is for local development ONLY. Agents must understand both flows and know when each is appropriate.

Migration Workflow

Development (local iteration)

# Direct push — applies schema changes without generating migration files
npx drizzle-kit push

CHECK: Agent is iterating on schema design locally. IF: Schema is not yet committed or reviewed. THEN: Use drizzle-kit push for fast iteration. IF: Schema is finalized and ready for review. THEN: Switch to generate workflow.

Production (GE standard)

# Step 1: Generate migration SQL from schema diff
npx drizzle-kit generate

# Step 2: Review the generated SQL file
cat drizzle/migrations/NNNN_*.sql

# Step 3: Apply migration
npx drizzle-kit migrate

CHECK: Agent is deploying schema changes to staging or production. IF: Using drizzle-kit push. THEN: STOP. Push is forbidden outside local dev. Use generate + migrate.

Migration File Conventions

Naming

Drizzle Kit auto-numbers migrations: 0000_*.sql, 0001_*.sql, etc. GE renames migration files to be descriptive:

0000_add_offline_status.sql
0001_abnormal_the_stranger.sql          # drizzle-kit auto-name (acceptable)
0002_knowledge_and_dag_tables.sql       # GE descriptive name (preferred)
0003_commercialization_core.sql
0006_security_database_fixes.sql
0007_team_routing.sql

CHECK: Agent has generated a new migration. IF: The auto-generated name is meaningless (e.g., 0009_dizzy_falcon.sql). THEN: Rename to describe the change: 0009_add_incident_tables.sql. THEN: Update the corresponding entry in drizzle/migrations/meta/_journal.json.

Migration Structure

Every GE migration follows this structure:

-- Migration: {descriptive title}
-- Date: {YYYY-MM-DD}
-- Author: {agent name} ({agent role})
-- Source: {what triggered this — ticket, audit, discussion}
--
-- Implements:
-- - {change 1}
-- - {change 2}
--
-- Rollback: See {NNNN}_*_rollback.sql

-- ============================================================================
-- PART 1: {section title}
-- ============================================================================

{SQL statements}

-- ============================================================================
-- PART 2: {section title}
-- ============================================================================

{SQL statements}

-- ============================================================================
-- VERIFICATION QUERIES
-- ============================================================================

{Queries to verify the migration worked}

CHECK: Agent is writing a migration. IF: The migration has no header comment. THEN: Add one. Migrations are reviewed by Boris and must be traceable.

Rollback Strategy

Rollback Files

For every non-trivial migration, create a companion rollback file:

0006_security_database_fixes.sql
0006_security_database_fixes_rollback.sql

-- Rollback: 0006_security_database_fixes
-- WARNING: This drops security constraints. Use only in emergencies.

ALTER TABLE audit_log DISABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS audit_append_only ON audit_log;
DROP POLICY IF EXISTS audit_read ON audit_log;
-- ...

CHECK: Agent is writing a migration that alters existing tables. IF: The migration adds constraints, modifies columns, or drops objects. THEN: Write a rollback file. Additive-only migrations (new tables) do not require rollback files.

Rollback Execution

# Manual rollback — NEVER automated
psql -f drizzle/migrations/NNNN_*_rollback.sql

ANTI_PATTERN: Automating rollback in CI/CD pipelines. FIX: Rollbacks are manual, human-approved operations. Boris or the on-call engineer executes them after assessing impact.

Safe Migration Patterns

Adding a Column

-- SAFE: Adding a nullable column with a default
ALTER TABLE agents ADD COLUMN IF NOT EXISTS provider text DEFAULT 'claude';

CHECK: Agent is adding a column. IF: The column is NOT NULL without a default. THEN: STOP. This locks the table and rewrites all rows. THEN: Instead, add as nullable first, backfill, then add NOT NULL constraint.

Adding NOT NULL to Existing Column (3-step)

-- Step 1: Add default value
ALTER TABLE agents ALTER COLUMN provider SET DEFAULT 'claude';

-- Step 2: Backfill existing rows (in batches for large tables)
UPDATE agents SET provider = 'claude' WHERE provider IS NULL;

-- Step 3: Add NOT NULL constraint
ALTER TABLE agents ALTER COLUMN provider SET NOT NULL;

Adding an Index

-- SAFE: Concurrent index creation (does not lock table)
CREATE INDEX CONCURRENTLY IF NOT EXISTS agents_provider_idx ON agents (provider);

CHECK: Agent is adding an index to a production table. IF: The table has more than 10,000 rows. THEN: Use CREATE INDEX CONCURRENTLY. Standard CREATE INDEX locks the table for the entire build duration.

ANTI_PATTERN: CREATE INDEX agents_provider_idx ON agents (provider); on production. FIX: CREATE INDEX CONCURRENTLY ... — non-blocking.

Adding an Enum Value

-- PostgreSQL 10+: Adding enum values is safe
ALTER TYPE task_status ADD VALUE IF NOT EXISTS 'paused';

ANTI_PATTERN: Removing or renaming enum values. FIX: Enum values in PostgreSQL are append-only. To "remove" a value, add the new value, migrate data, then leave the old value unused.

Renaming a Column

-- Step 1: Add new column
ALTER TABLE agents ADD COLUMN display_name text;

-- Step 2: Backfill
UPDATE agents SET display_name = name;

-- Step 3: Update application code to use new column
-- Step 4: Drop old column (NEXT migration, after deployment)
ALTER TABLE agents DROP COLUMN name;

CHECK: Agent is renaming a column. IF: Using ALTER TABLE ... RENAME COLUMN. THEN: STOP. This breaks running application instances. THEN: Use the add-backfill-switch-drop pattern across two migrations.

Zero-Downtime Migration Rules

GE deploys with rolling updates. During deployment, BOTH the old and new application versions run simultaneously. Migrations MUST be backward-compatible with the previous application version.

Safe operations (single migration): - Add a new table - Add a nullable column - Add a column with a default - Add an index (CONCURRENTLY) - Add an enum value

Unsafe operations (require multi-step migration): - Add NOT NULL constraint to existing column - Rename a column - Remove a column - Change a column type - Drop a table

CHECK: Agent is writing a migration. IF: The migration contains any "unsafe" operation. THEN: Split into multiple migrations deployed across multiple releases.

GE Migration Workflow

  1. Agent modifies drizzle/schema/{domain}.ts.
  2. Agent runs npx drizzle-kit generate.
  3. Agent reviews the generated SQL — verifies it matches intent.
  4. Agent renames migration file to be descriptive.
  5. Agent writes rollback file if migration alters existing objects.
  6. Agent adds header comments with author, date, and source.
  7. Agent adds verification queries at the bottom.
  8. Boris reviews the migration SQL before merge.
  9. Deployment applies migration via drizzle-kit migrate in init container.

Drizzle Kit Configuration

// drizzle.config.ts
import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  schema: './drizzle/schema.ts',
  out: './drizzle/migrations',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL || 'postgresql://localhost:5432/admin_ui',
  },
});

CHECK: Agent is modifying drizzle.config.ts. IF: Changing schema or out paths. THEN: Verify all existing migrations still resolve. IF: Changing dialect. THEN: STOP. GE uses PostgreSQL exclusively. Dialect is always postgresql.

Troubleshooting

Migration out of sync

# Check migration status
npx drizzle-kit check

# If schema and migrations diverge, generate a new migration
npx drizzle-kit generate

Drizzle Kit metadata corruption

The drizzle/migrations/meta/ directory contains _journal.json and snapshot files. If corrupted:

# Regenerate from existing migration files
npx drizzle-kit generate --custom

ANTI_PATTERN: Manually editing _journal.json without understanding the format. FIX: Let drizzle-kit manage its metadata. Only edit if renaming a migration file.

Cross-References

READ_ALSO: wiki/docs/stack/drizzle/index.md READ_ALSO: wiki/docs/stack/drizzle/schema-design.md READ_ALSO: wiki/docs/stack/drizzle/pitfalls.md READ_ALSO: wiki/docs/stack/postgresql/indexing.md READ_ALSO: wiki/docs/stack/postgresql/security.md