Development Workflow

Step-by-step process for creating, reviewing, and applying Drizzle migrations during local development.

The migration workflow follows a strict generate, review, apply, test cycle. This discipline was established as a Phase 0 hard requirement after prior experience with AI-generated migrations causing deployment failures.

Step 1: Edit the Schema

All schema changes start in TypeScript files under packages/db/src/schema/. See Schema Design — Adding a Table for the full pattern, including RLS setup.

Key schema files:

File	Contents
`base.ts`	`tenantColumns`, `baseColumns`, `tenantPolicies()` helper
`enums.ts`	PostgreSQL enum types
`auth.ts`	Better Auth tables (user, session, account, organization, member, invitation)
`ai.ts`	AI model registry and pricing
`research.ts`	Research plans, steps, artifacts, outputs, feedback
`search.ts`	Document chunks with pgvector embeddings
`index.ts`	Re-exports all schemas (Drizzle Kit reads this)

After editing, export any new tables from src/schema/index.ts.

Step 2: Generate the Migration

pnpm db:generate

This runs drizzle-kit generate with the configuration from packages/db/drizzle.config.ts:

import { defineConfig } from "drizzle-kit";

export default defineConfig({
  dialect: "postgresql",
  schema: "./src/schema/index.ts",
  out: "./src/migrations",
  dbCredentials: {
    url: process.env["DATABASE_URL"] ?? "",
  },
  verbose: true,
  strict: true,
});

The strict: true flag forces Drizzle Kit to ask for confirmation before generating destructive changes (column drops, type changes). The verbose: true flag prints every SQL statement it plans to generate.

Drizzle Kit creates two outputs:

A new SQL file in src/migrations/ (e.g., 0010_some_name.sql)
An updated snapshot in src/migrations/meta/ plus a _journal.json entry

Step 3: Review the Generated SQL

Always review the generated SQL before applying. This is the most important step. Check for:

Unexpected DROP statements — Drizzle generates drops for renamed columns. If you renamed a column, verify the DROP + ADD is correct, or consider a manual ALTER TABLE ... RENAME COLUMN instead.
Missing IF NOT EXISTS — Early Drizzle-generated migrations didn't include existence checks. Later migrations (0007+) use IF NOT EXISTS / IF EXISTS guards for idempotence. Add them manually if Drizzle omits them.
RLS policies present — For new tenant-scoped tables, verify that four CRUD policies were generated (select, insert, update, delete) targeting the authenticated role.
Index names — Drizzle auto-generates index names. Verify they're reasonable and won't collide.
Foreign key ON DELETE behavior — The project convention is cascade for most foreign keys. Check that Drizzle didn't default to no action where cascade is expected.

# View the latest migration
ls -t packages/db/src/migrations/*.sql | head -1 | xargs cat

When to Hand-Edit Generated Migrations

Drizzle Kit cannot generate everything. You must manually add:

GRANT statements — Role grants for the authenticated role (see migration 0001)
Generated columns — tsvector GENERATED ALWAYS AS (...) columns (Drizzle 0.45 lacks generated column support)
Custom index types — HNSW vector indexes, GIN indexes for tsvector
CREATE EXTENSION — Enabling PostgreSQL extensions like pgvector
Enum additions with IF NOT EXISTS — Drizzle generates CREATE TYPE without existence checks; wrap in DO $$ BEGIN ... END $$

When hand-editing, append your SQL after the generated content and include a comment header explaining the addition:

-- Manual addition: enable pgvector extension
CREATE EXTENSION IF NOT EXISTS vector;--> statement-breakpoint

-- Manual addition: HNSW index for vector similarity search
CREATE INDEX IF NOT EXISTS "document_chunk_embedding_idx"
  ON "document_chunk" USING hnsw ("embedding" halfvec_ip_ops);

If the Generated SQL Is Wrong

If Drizzle generated something incorrect:

Delete the new migration file and its snapshot from src/migrations/meta/
Remove the entry from _journal.json
Fix the schema
Re-run pnpm db:generate

Do not fix the SQL in place unless you're adding manual additions (above). The snapshot and journal must stay in sync with the SQL file.

Step 4: Apply Locally

pnpm db:migrate

This runs packages/db/src/migrate.ts, which:

Reads _journal.json to determine the expected migration count
Queries drizzle.__drizzle_migrations for the current count
Logs the state: Migration state: N applied, M expected, P pending
Runs the Drizzle migrator against ./src/migrations
Verifies the post-migration count matches the expected count
Fails with a non-zero exit if counts don't match

The migration runner connects to whatever DATABASE_URL is in the environment. For local development, this is the Docker PostgreSQL container on port 5433.

Step 5: Run Tests

pnpm test                        # All tests
pnpm --filter @repo/db test      # Just RLS integration tests

If you added a new tenant-scoped table, the RLS integration tests in packages/db/src/__tests__/rls.test.ts verify cross-tenant isolation. These tests:

Connect as superuser and seed test data
SET ROLE authenticated to exercise RLS
Verify each org only sees its own rows
Verify cross-tenant INSERT/UPDATE/DELETE are rejected

Step 6: Run Pre-Commit Checks

Before committing, run the full CI check suite:

pnpm ci:check

This runs format, lint, dep-cruise, dead code detection, duplication check, typecheck, tests, and doc-update-detection. The doc-update-detection step may flag migration-related doc pages if you changed files under packages/db/src/migrations/ or packages/db/src/schema/.

Step 7: Commit

Commit the schema change and migration file together in the same commit. Both the .sql file and the meta/ snapshot must be committed — they are tracked by Git and required for the migration runner to work.

git add packages/db/src/schema/
git add packages/db/src/migrations/
git commit -m "Add document_chunk table with pgvector HNSW index (TRO-58)"

On push to main, the CI deployment pipeline takes over.