OpenWork Database Schema and Migration Strategy: A Complete Technical Guide

OpenWork uses Drizzle ORM with drizzle-kit to manage a MySQL-compatible database schema (PlanetScale by default) through TypeScript-defined tables and state-based migrations.

The different-ai/openwork repository implements a robust data layer for its enterprise edition using modern TypeScript tooling. Understanding the database schema and migration strategy is essential for contributors deploying new features or managing self-hosted instances.

Schema Definition with Drizzle ORM

OpenWork defines its entire database schema in TypeScript using Drizzle ORM's MySQL-core builders, providing compile-time type safety and automatic SQL generation.

Package Structure and Location

All database definitions reside in the @openwork-ee/den-db package under ee/packages/den-db/src/schema/. The main entry point exports all tables from ee/packages/den-db/src/schema/index.ts, which aggregates definitions from modular files like workers.ts, audit.ts, and organization-related schemas.

Table Definition Patterns

Each table follows a consistent pattern using Drizzle's builder API. For example, the WorkerTable in ee/packages/den-db/src/schema/workers.ts demonstrates the standard structure:

export const WorkerTable = mysqlTable(
  "worker",
  {
    id: denTypeIdColumn("worker", "id").notNull().primaryKey(),
    org_id: denTypeIdColumn("org", "org_id").notNull(),
    created_by_user_id: denTypeIdColumn("user", "created_by_user_id"),
    name: varchar("name", { length: 255 }).notNull(),
    description: varchar("description", { length: 1024 }),
    destination: mysqlEnum("destination", WorkerDestination).notNull(),
    status: mysqlEnum("status", WorkerStatus).notNull(),
    // … other columns …
    ...timestamps,
  },
  (table) => [
    index("worker_org_id").on(table.org_id),
    index("worker_created_by_user_id").on(table.created_by_user_id),
    index("worker_status").on(table.status),
    // … additional indexes …
  ],
);

Key architectural patterns in the OpenWork schema include:

• Type-safe columns using varchar, mysqlEnum, json, and timestamp builders
• Primary keys generated via the helper denTypeIdColumn for consistent ID formatting
• Performance indexes declared explicitly through index() or uniqueIndex() calls
• Timestamp management via the timestamps helper that adds created_at and updated_at to all tables

Core Tables and Relationships

The schema exports from ee/packages/den-db/src/schema/index.ts include tables for workers (WorkerTable, WorkerInstanceTable, DaytonaSandboxTable, WorkerTokenTable, WorkerBundleTable), audit logging (AuditEventTable), and organizational features (Org, Teams, Subscriptions, Memory, Inference, Telemetry, DesktopPolicies, Auth), plus a set of sharable tables for LLM providers and plugin architecture.

Drizzle Configuration and Database Connection

The database connection and migration settings are centralized in ee/packages/den-db/drizzle.config.ts:

export default defineConfig({
  dialect: "mysql",
  schema: "./src/schema.ts",
  out: "./drizzle",
  dbCredentials: resolveDrizzleDbCredentials(),
});

Configuration details:

• dialect: Set to "mysql" for PlanetScale and MySQL-compatible databases
• schema: Points to the TypeScript file re-exporting all table definitions
• out: Specifies ./drizzle as the directory for generated SQL files and the migration journal (_journal.json)
• dbCredentials: Resolved from environment variables (DATABASE_URL or individual DATABASE_HOST, DATABASE_USERNAME, etc.) via load-env.ts, with fallback to localhost for offline schema commands

Migration Strategy and Workflow

OpenWork employs a state-based migration approach using drizzle-kit push while maintaining generated SQL files for audit trails and reproducibility.

State-Based Migrations with drizzle-kit

The migration workflow treats the TypeScript schema as the source of truth. When schema changes occur, developers use drizzle-kit generate or drizzle-kit export to produce raw SQL files in the drizzle/ directory for code review. During deployment, drizzle-kit push reads the current database state and applies only the necessary changes to align the live database with the TypeScript definitions.

Migration Scripts and Bootstrapping

Located in ee/packages/den-db/scripts/, two critical scripts handle edge cases:

• bootstrap.ts: Creates the __drizzle_migrations table if missing and runs pending migrations using drizzle-orm/mysql2 for direct client connections
• baseline-migrations.ts: Seeds essential tables on fresh PlanetScale instances without requiring a full migration history, useful for new deployments

Both scripts utilize readMigrationFiles to load generated .sql files and apply them in order.

CI/CD Integration

The .github/workflows/den-db-migrate.yml GitHub Actions workflow automates schema deployment:

1. Checks out the repository
2. Installs dependencies via pnpm
3. Executes pnpm exec drizzle-kit push --config drizzle.config.ts against the production database

If migrations fail, the workflow aborts immediately to prevent deployment of incompatible schema changes.

Practical Usage Examples

Initializing a Drizzle Client

import { drizzle } from "drizzle-orm/mysql2";
import { schema } from "@openwork-ee/den-db/drizzle";

export const db = drizzlePlanetScale(
  {
    host: process.env.DATABASE_HOST!,
    user: process.env.DATABASE_USERNAME!,
    password: process.env.DATABASE_PASSWORD ?? "",
    database: process.env.DATABASE_NAME!,
    ssl: { rejectUnauthorized: true },
  },
  { schema }
);

Running Migrations Locally

# Generate SQL files for inspection

pnpm exec drizzle-kit generate --config drizzle.config.ts

# Apply pending migrations to local MySQL

pnpm exec drizzle-kit push --config drizzle.config.ts

Bootstrapping a New PlanetScale Database

pnpm exec ts-node scripts/baseline-migrations.ts   # Creates essential tables

pnpm exec ts-node scripts/bootstrap.ts             # Ensures __drizzle_migrations exists

Summary

• OpenWork stores data in MySQL-compatible databases (PlanetScale default) and defines schemas using Drizzle ORM TypeScript builders in ee/packages/den-db/src/schema/
• Tables follow consistent patterns with type-safe columns, custom ID generators via denTypeIdColumn, explicit indexes, and shared timestamp helpers
• drizzle-kit generates SQL files to ee/packages/den-db/drizzle/ and applies state-based migrations via push commands
• bootstrap.ts and baseline-migrations.ts handle fresh database initialization and migration table setup
• The den-db-migrate.yml GitHub Actions workflow automates production migrations during CI/CD, aborting on failure to ensure schema consistency

Frequently Asked Questions

What database does OpenWork use?

OpenWork uses a MySQL-compatible database with PlanetScale as the default provider. According to the drizzle.config.ts in the repository, the dialect is explicitly set to "mysql", and connection credentials resolve from environment variables like DATABASE_URL or individual DATABASE_HOST and DATABASE_USERNAME variables.

How does OpenWork handle schema changes in production?

OpenWork handles schema changes through state-based migrations using drizzle-kit push. During CI/CD, the .github/workflows/den-db-migrate.yml workflow automatically applies pending migrations against the live database. The process aborts if any migration fails, preventing partial or broken schema deployments.

Where are the database tables defined in the OpenWork codebase?

Database tables are defined in the @openwork-ee/den-db package under ee/packages/den-db/src/schema/. Individual domain files like workers.ts contain specific table definitions, while schema/index.ts aggregates and exports all tables for use across the application.

How do I run database migrations locally in OpenWork?

To run migrations locally, use pnpm exec drizzle-kit push --config drizzle.config.ts to apply pending changes against your local database. For SQL inspection before applying changes, run pnpm exec drizzle-kit generate --config drizzle.config.ts to generate migration files in the drizzle/ directory.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}