Migrations Overview

Trovella uses Drizzle Kit to generate forward-only SQL migrations from TypeScript schema definitions. Migrations are committed to Git, reviewed in PRs, tested against ephemeral CI databases, and applied to production Cloud SQL via the migrate-prod CI job.

Key Principles

• Schema-driven: All changes start in packages/db/src/schema/ TypeScript files. Drizzle Kit diffs the schema against the previous snapshot and generates SQL.
• Forward-only: There is no built-in rollback command. Reversing a change means writing a new migration that undoes it.
• Idempotent guards: Later migrations use IF NOT EXISTS / IF EXISTS / DO $$ BEGIN ... END $$ blocks so they can be safely re-run without error.
• RLS co-migrated: CREATE POLICY statements are generated alongside CREATE TABLE, keeping tenant isolation in sync with schema changes. See Schema Design — Tenant Scoping for the RLS pattern.
• Verification at two layers: Both the migration runner (migrate.ts) and a separate CI step independently verify that the database migration count matches the local journal after applying.

How It Works

Drizzle Kit maintains a meta/ directory inside packages/db/src/migrations/ containing JSON snapshots of the schema at each migration point and a _journal.json that indexes all migrations. When you run pnpm db:generate, Drizzle compares the current TypeScript schema against the latest snapshot and emits a new .sql file plus an updated snapshot.

The migration runner (packages/db/src/migrate.ts) reads _journal.json to determine how many migrations should exist, queries the drizzle.__drizzle_migrations table for the current count, applies any pending migrations, and then verifies the counts match.

Migration File Anatomy

Each migration is a plain SQL file with --> statement-breakpoint markers between statements. Drizzle Kit uses these markers to split the file into individual statements for execution. A typical migration contains:

• CREATE TABLE / ALTER TABLE for schema changes
• CREATE INDEX for new indexes
• ALTER TABLE ... ENABLE ROW LEVEL SECURITY for tenant-scoped tables
• CREATE POLICY for RLS policies
• ALTER TABLE ... ADD CONSTRAINT for foreign keys

Example from 0008_add-correlation-id-and-embedding-count.sql:

-- TRO-58: Add correlationId and embeddingCount to ai_usage
ALTER TABLE "ai_usage" ADD COLUMN IF NOT EXISTS "correlation_id" uuid
  DEFAULT gen_random_uuid() NOT NULL;--> statement-breakpoint
ALTER TABLE "ai_usage" ADD COLUMN IF NOT EXISTS "embedding_count" integer;--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "ai_usage_correlationId_idx"
  ON "ai_usage" USING btree ("correlation_id");

Current Migration History

The project has 10 migrations (0000--0009) as of the current codebase:

Migration	Purpose
0000_lying_agent_zero	Initial schema: auth tables (user, session, account, organization, member, invitation), RLS policies
0001_setup_role_grants	Grant authenticated role access to all tables + default privileges for future tables
0002_giant_scarlet_spider	Personal access tokens table
0003_abandoned_ender_wiggin	AI model registry and pricing tables
0004_happy_rhodey	Research engine tables (research_plan, plan_step, research_artifact, plan_audit_log) with RLS
0005_marvelous_violations	Skill execution tracking table
0006_milky_dreadnoughts	AI usage tracking table
0007_tro-101-research-improvements	Research output, feedback, MCP tool call log tables; plan_step column changes
0008_add-correlation-id-and-embedding-count	AI usage correlation ID and embedding count columns
0009_add-document-chunk-table	pgvector extension, document_chunk table with HNSW index

Pages in This Topic

• Development Workflow — Step-by-step process for creating and applying migrations locally
• CI Deployment — How the migrate-prod job applies migrations to production
• Rollback Strategy — How to reverse a migration when something goes wrong
• Seed Data — Reference data, development data, and the tiered seeding system
• Reference — Commands, configuration, file layout, and the migration journal