Reference

Commands, configuration, file layout, migration journal format, and troubleshooting for the migration system.

Commands

All commands are available from the monorepo root. They delegate to the @repo/db package via pnpm --filter.

Command	Description	When to Use
`pnpm db:generate`	Generate a migration SQL file from schema changes	After editing schema TypeScript files
`pnpm db:migrate`	Apply pending migrations to the database	After generating, or on a fresh database
`pnpm db:push`	Push schema directly without creating a migration file	Dev only -- quick iteration, not for commits
`pnpm db:seed`	Run all seed tiers appropriate for the environment	After migrating, or to refresh test data
`pnpm db:reset`	Run `db:migrate` then `db:seed`	Full local reset from scratch
`pnpm db:studio`	Open Drizzle Studio in the browser	Schema browsing and data inspection
`pnpm db:seed-research`	Run only the research data seed	When you need research test data only

Package-Level Scripts

These are defined in packages/db/package.json and invoked by the root scripts:

{
  "db:generate": "drizzle-kit generate",
  "db:migrate": "tsx --env-file-if-exists=../../.env src/migrate.ts",
  "db:push": "drizzle-kit push",
  "db:seed": "tsx --env-file-if-exists=../../.env src/seed.ts",
  "db:seed-research": "tsx --env-file-if-exists=../../.env src/seed-research.ts",
  "db:studio": "drizzle-kit studio",
  "db:reset": "tsx --env-file-if-exists=../../.env src/migrate.ts && tsx --env-file-if-exists=../../.env src/seed.ts"
}

Note the --env-file-if-exists=../../.env flag on tsx scripts. This loads the root .env file for DATABASE_URL. Drizzle Kit commands (generate, push, studio) read the environment through drizzle.config.ts instead.

Drizzle Kit Configuration

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,
});

Option	Value	Purpose
`dialect`	`"postgresql"`	Target database dialect
`schema`	`"./src/schema/index.ts"`	Entry point for all Drizzle schema definitions
`out`	`"./src/migrations"`	Output directory for generated SQL and snapshots
`verbose`	`true`	Print every generated SQL statement
`strict`	`true`	Require confirmation for destructive changes

File Layout

packages/db/src/
  migrations/
    0000_lying_agent_zero.sql        -- Initial auth schema + RLS
    0001_setup_role_grants.sql       -- GRANT authenticated role access
    0002_giant_scarlet_spider.sql    -- Personal access tokens
    ...
    0009_add-document-chunk-table.sql -- Latest migration
    meta/
      0000_snapshot.json             -- Schema snapshot after migration 0000
      0001_snapshot.json             -- (not present for hand-crafted migrations)
      ...
      _journal.json                  -- Migration index
  migrate.ts                         -- Migration runner script
  seed.ts                            -- Seed orchestrator
  seeds/                             -- Seed data files
  schema/                            -- Drizzle schema definitions
  client.ts                          -- Database client + withTenantContext

Migration Journal

The journal at src/migrations/meta/_journal.json is the source of truth for which migrations exist and their order. Format:

{
  "version": "7",
  "dialect": "postgresql",
  "entries": [
    {
      "idx": 0,
      "version": "7",
      "when": 1774410453496,
      "tag": "0000_lying_agent_zero",
      "breakpoints": true
    },
    {
      "idx": 1,
      "version": "7",
      "when": 1774410453497,
      "tag": "0001_setup_role_grants",
      "breakpoints": true
    }
  ]
}

Field	Description
`idx`	Zero-based migration index
`version`	Drizzle journal format version
`when`	Unix timestamp (milliseconds) when the migration was generated
`tag`	Migration filename without the `.sql` extension
`breakpoints`	Whether `--> statement-breakpoint` markers are used (always `true`)

Migration Naming

Drizzle Kit auto-generates migration names using the format NNNN_adjective_noun.sql (e.g., 0004_happy_rhodey.sql). For hand-crafted or renamed migrations, the project uses descriptive names:

0001_setup_role_grants.sql -- manual, describes purpose
0007_tro-101-research-improvements.sql -- ticket-prefixed for traceability
0008_add-correlation-id-and-embedding-count.sql -- descriptive of change
0009_add-document-chunk-table.sql -- descriptive of change

Both naming styles are valid. The sequence number prefix (0000_, 0001_, etc.) determines execution order.

Migration Tracking Table

Drizzle stores applied migration records in drizzle.__drizzle_migrations. This table is created automatically on the first migration run.

-- Check which migrations have been applied
SELECT * FROM drizzle.__drizzle_migrations ORDER BY created_at DESC;

-- Count applied migrations
SELECT count(*) FROM drizzle.__drizzle_migrations;

Migration Verification

Two independent verification layers ensure migrations are fully applied:

Layer 1: migrate.ts Pre/Post Count

The migration runner (src/migrate.ts) performs these checks:

Pre-migration: Reads _journal.json for expected count, queries the database for current count
Guard: Fails if the database has more migrations than the journal (database ahead of codebase)
Post-migration: Re-queries the database and fails if the count doesn't match expected

Migration state: 8 applied, 10 expected, 2 pending
Running migrations...
Migrations complete. Applied 2 new migration(s).
Verified: database has all 10 migrations.

Layer 2: CI Independent Verification

A separate GitHub Actions step after pnpm db:migrate independently reads the journal and queries the database:

EXPECTED=$(node -e "...read _journal.json entries.length...")
ACTUAL=$(psql ... -c "SELECT count(*) FROM drizzle.__drizzle_migrations")

This catches edge cases where the runner exits 0 without actually applying everything.

Why Two Layers?

A production incident revealed that Drizzle's built-in tracking provides no feedback when there are zero pending migrations. A "successful" run of zero migrations looks identical to a successful run of five migrations. The pre/post count check in migrate.ts and the independent CI step together ensure no migration is silently skipped.

Statement Breakpoints

Drizzle Kit uses --> statement-breakpoint markers between SQL statements. These markers tell the migration runner where to split the file into individual statements. Every statement must end with this marker (except the last statement in the file).

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");

Connecting to Production

For debugging migration issues in production, use the Cloud SQL Auth Proxy:

# Start the proxy (port 5434 to avoid local conflicts)
cloud-sql-proxy trovella-prod:us-central1:trovella-prod --port 5434

# Connect with psql
psql "postgresql://trovella-app:$(cd infra/environments/prod && \
  terraform output -raw database_password)@localhost:5434/trovella"

# Check migration status
SELECT * FROM drizzle.__drizzle_migrations ORDER BY created_at DESC;

Or from the production VM:

gcloud compute ssh trovella-prod-vm \
  --zone=us-central1-a --project=trovella-prod \
  --tunnel-through-iap

source /opt/trovella/.env
psql "$DATABASE_URL"

Environment Variable Loading

Context	How DATABASE_URL Is Loaded
Local scripts (`db:migrate`, `db:seed`)	`tsx --env-file-if-exists=../../.env` reads root `.env`
Drizzle Kit (`db:generate`, `db:studio`)	Reads `process.env` in `drizzle.config.ts`
Next.js app	Loaded from `.env` by Next.js automatically
CI quality job	Set from the ephemeral PG service container
CI migrate-prod job	Read from Secret Manager, rewritten for proxy
Production VM	Injected via Secret Manager into `/opt/trovella/.env`

Troubleshooting

"relation does not exist"

Migrations haven't been run. Run pnpm db:migrate.

"Database has N migrations but local journal only has M"

The database is ahead of the codebase. This means migrations were applied from a branch that hasn't been merged yet, or the journal was accidentally modified. Pull the latest main and re-check.

"Migration count mismatch: database has N but expected M"

The migration runner applied some but not all migrations. Check the CI logs for SQL errors in the partially applied migration.

RLS tests fail with "permission denied"

The authenticated role grants from migration 0001 may not have been applied. This migration must run before any RLS-dependent tests. Check: SELECT * FROM drizzle.__drizzle_migrations WHERE tag LIKE '%role_grants%';

Docker PostgreSQL not running

If pnpm db:migrate fails with ECONNREFUSED, Docker Desktop isn't running or the containers aren't up. Run pnpm docker:up.

Development Workflow -- the full generate-review-apply-test cycle
CI Deployment -- production migration pipeline
Seed Data -- seed commands and tiers