# OpenWork Database Schema and Migration Strategy: A Complete Technical Guide

> Explore the OpenWork database schema and migration strategy. Learn how Drizzle ORM and drizzle-kit manage TypeScript-defined tables and state-based migrations for your MySQL database.

- Repository: [Different AI/openwork](https://github.com/different-ai/openwork)
- Tags: architecture
- Published: 2026-07-31

---

**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`](https://github.com/different-ai/openwork/blob/main/ee/packages/den-db/src/schema/index.ts), which aggregates definitions from modular files like [`workers.ts`](https://github.com/different-ai/openwork/blob/main/workers.ts), [`audit.ts`](https://github.com/different-ai/openwork/blob/main/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`](https://github.com/different-ai/openwork/blob/main/ee/packages/den-db/src/schema/workers.ts) demonstrates the standard structure:

```typescript
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`](https://github.com/different-ai/openwork/blob/main/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`](https://github.com/different-ai/openwork/blob/main/ee/packages/den-db/drizzle.config.ts):

```typescript
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`](https://github.com/different-ai/openwork/blob/main/_journal.json))
- **dbCredentials**: Resolved from environment variables (`DATABASE_URL` or individual `DATABASE_HOST`, `DATABASE_USERNAME`, etc.) via [`load-env.ts`](https://github.com/different-ai/openwork/blob/main/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`](https://github.com/different-ai/openwork/blob/main/.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

```typescript
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

```bash

# 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

```bash
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`](https://github.com/different-ai/openwork/blob/main/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`](https://github.com/different-ai/openwork/blob/main/.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`](https://github.com/different-ai/openwork/blob/main/workers.ts) contain specific table definitions, while [`schema/index.ts`](https://github.com/different-ai/openwork/blob/main/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.