Database Migrations

Relevant source files

• apps/sim/app/api/auth/oauth/utils.test.ts
• apps/sim/app/api/auth/oauth/utils.ts
• apps/sim/app/api/files/presigned/route.test.ts
• apps/sim/app/api/files/presigned/route.ts
• apps/sim/app/api/logs/route.ts
• apps/sim/app/api/users/me/settings/route.ts
• apps/sim/app/api/v1/logs/route.ts
• apps/sim/app/workspace/[workspaceId]/w/[workflowId]/components/panel/components/editor/components/sub-block/components/credential-selector/components/oauth-required-modal.tsx
• apps/sim/hooks/queries/general-settings.ts
• apps/sim/lib/auth/auth.ts
• apps/sim/lib/core/bullmq/connection.ts
• apps/sim/lib/core/config/env.ts
• apps/sim/lib/core/config/feature-flags.ts
• apps/sim/lib/oauth/oauth.ts
• apps/sim/lib/oauth/types.ts
• apps/sim/lib/oauth/utils.ts
• apps/sim/lib/uploads/index.ts
• apps/sim/tools/salesforce/create_account.ts
• apps/sim/tools/salesforce/delete_account.ts
• apps/sim/tools/salesforce/get_accounts.ts
• apps/sim/tools/salesforce/update_account.ts
• packages/db/migrations/meta/_journal.json
• packages/db/schema.ts

This page covers the database migration system used in Sim, which manages schema evolution for the PostgreSQL database using Drizzle ORM. Database migrations ensure that schema changes are applied consistently across all deployment environments, from local development to production clusters.

---

Overview

Sim uses Drizzle ORM for database schema management and migrations. The migration system handles:

• Schema versioning: Tracking and applying incremental schema changes using a journaled migration history packages/db/migrations/meta/_journal.json1-315
• PostgreSQL with pgvector: Managing both relational tables and vector extension requirements for AI capabilities packages/db/schema.ts19-20
• Type Safety: Synchronizing TypeScript interfaces with the physical database structure via pgTable definitions packages/db/schema.ts32-180
• Data Integrity: Enforcing foreign key constraints and indexes during schema transitions packages/db/schema.ts60-63

Sources: packages/db/migrations/meta/_journal.json1-20 packages/db/schema.ts1-20

---

Migration Architecture

Migration Execution Flow

Description: Migration execution workflow. The drizzle-kit utility compares the current state of schema.ts against the migration journal to ensure the physical database matches the code definitions.

Sources: packages/db/schema.ts1-180 packages/db/migrations/meta/_journal.json1-315

---

Schema Evolution and Journaling

Sim maintains a rigorous history of schema changes in the _journal.json file. Each migration is tagged with a unique identifier and a timestamp to ensure linear progression.

Migration Journal Structure

The journal tracks every incremental change to the database:

Index	Tag	Version	When (Timestamp)
0	0000_careless_black_knight	7	1739697832964
1	0001_foamy_dakota_north	7	1739773751302
...	...	...	...
44	0044_uneven_killer_shrike	7	1750369626350

Sources: packages/db/migrations/meta/_journal.json4-312

---

Core Schema Components

The migration system evolves several critical entities defined in the Drizzle schema.

System Entity Mapping

Description: Association between high-level system concepts and their underlying Drizzle ORM table definitions.

Code Entity	File Path	Role
user	packages/db/schema.ts32	Core user profile, email verification, and RBAC roles
session	packages/db/schema.ts48	Active user sessions, tokens, and organization context
workflow	packages/db/schema.ts144	Workflow metadata, deployment status, and variable definitions
workflowBlocks	packages/db/schema.ts180	Canvas elements, logic configuration, and execution outputs
account	packages/db/schema.ts72	OAuth connections, access/refresh tokens, and provider IDs

Sources: packages/db/schema.ts32-205

---

Migration Generation and Execution

Drizzle Kit Commands

Command	Description
bunx drizzle-kit generate	Compares packages/db/schema.ts against the latest migration in _journal.json and generates a new SQL file.
bunx drizzle-kit migrate	Executes pending SQL migrations against the target database.

Advanced Type Support

The migration system supports specialized PostgreSQL types required for the platform's advanced features:

• pgvector: Used for AI knowledge base embeddings via the vector type packages/db/schema.ts19-20
• tsvector: Custom type implemented for full-text search capabilities packages/db/schema.ts24-30
• jsonb: Efficient storage for complex nested data like subBlocks and outputs packages/db/schema.ts202-203

Sources: packages/db/schema.ts19-30 packages/db/schema.ts202-203

---

Database State and API Integration

Migrations directly impact how the API layer queries data. For example, the LogsAPI performs complex joins across tables managed by the migration system to provide execution history.

Data Flow: Execution Logs

Description: The LogsAPI apps/sim/app/api/logs/route.ts124-135 relies on the relational integrity established by migrations between execution logs, workflows, and deployment versions.

Sources: apps/sim/app/api/logs/route.ts124-143 packages/db/schema.ts144-177

---

Local Development Workflow

Modifying the Schema

1. Edit: Modify definitions in packages/db/schema.ts.
2. Generate: Run bunx drizzle-kit generate. This adds a new entry to _journal.json packages/db/migrations/meta/_journal.json309-311
3. Review: Check the generated SQL file in the migrations folder to ensure no destructive changes (like dropping columns) were inferred incorrectly.
4. Apply: Run bun run db:migrate to update your local PostgreSQL instance.

Sources: packages/db/schema.ts1-205 packages/db/migrations/meta/_journal.json1-315