Migrations

Database migrations run via Drizzle ORM's migration runner. Separate commands target staging and production databases, with connection strings resolved from 1Password.

Commands

# Run migrations against staging
pnpm deploy:migrate:staging

# Run migrations against production
pnpm deploy:migrate:prod

# Run migrations locally (uses DATABASE_URL from .env)
pnpm db:migrate

How it works

The migration scripts at scripts/deploy/migrate.sh perform these steps:

1. Accept an environment argument (staging or prod).
2. Resolve the DATABASE_URL from the corresponding 1Password vault item.
3. Execute Drizzle migrations against the resolved database.

The database URL is read via op read:

# Resolved at runtime, never stored in files
DATABASE_URL=$(op read "op://octospark-services/staging/DATABASE_URL")

Migration files

Migrations are stored in packages/db/drizzle/migrations/ as SQL files generated by Drizzle Kit. Each migration is a sequential SQL file applied in order.

The schema definition lives in packages/db/src/schema.ts. When you change the schema, generate a new migration:

# Generate a migration from schema changes
pnpm --filter @tx-agent-kit/db db:generate

# Apply the migration locally
pnpm db:migrate

Database triggers

For database triggers, use the scaffold command to generate the trigger SQL and pgTAP test:

pnpm db:trigger:new <trigger-name>

This creates a new trigger migration file and a corresponding pgTAP test. Trigger tests run as part of pnpm test:db:pgtap.

Safety considerations

Migrations run in a transaction by default, so a failure rolls back cleanly without leaving partial state. Always test locally before running against staging. The staging environment is the proving ground. Run pnpm deploy:migrate:staging and verify before touching production. Destructive migrations (dropping columns or tables) should be multi-step: first deploy code that stops using the column, then deploy the migration that drops it.

Local development

For local development, pnpm db:migrate reads DATABASE_URL from the .env file (typically postgres://postgres:postgres@localhost:5432/tx_agent_kit). In worktrees, the URL includes a schema search path to isolate data.

To reset the local test database:

pnpm db:test:reset

This drops and recreates the database, then reruns all migrations. Use this when you need a clean slate.

Drizzle Studio

For visual database inspection during development:

pnpm db:studio

This launches Drizzle Studio, a web-based UI for browsing tables, running queries, and inspecting data.