# OpenWork den-db MySQL Schema: Complete Drizzle ORM Reference

> Explore the den-db MySQL schema in OpenWork. This Drizzle ORM reference details type-safe tables for workers, organizations, and telemetry with custom IDs and JSON support.

- Repository: [Different AI/openwork](https://github.com/different-ai/openwork)
- Tags: api-reference
- Published: 2026-08-04

---

**OpenWork's `den-db` package defines a type-safe MySQL schema using Drizzle ORM, organizing data into modular tables for workers, organizations, and telemetry with custom prefixed ID generation and native JSON support.**

OpenWork is an open-source workspace orchestration platform that persists its state in a MySQL database. The schema is defined in the `@openwork-ee/den-db` package using **Drizzle ORM**, providing fully typed database access across the enterprise edition codebase located at `dev/ee/packages/den-db/src/schema/`.

## Schema Architecture Overview

The den-db schema follows a modular file structure where each domain logic area resides in its own TypeScript file. All tables are constructed using `mysqlTable` from `drizzle-orm/mysql-core` and aggregated into a single schema object for client initialization.

Key architectural components include:

- **Custom ID generation** via `denTypeIdColumn` (defined in [`dev/ee/packages/den-db/src/columns.ts`](https://github.com/different-ai/openwork/blob/main/dev/ee/packages/den-db/src/columns.ts)), which produces deterministic, prefixed identifiers such as `worker_`, `org_`, or `member_`.
- **Standardized timestamps** through a reusable `timestamps` constant that appends `created_at`, `updated_at`, and `deleted_at` columns with millisecond precision.
- **Flexible storage types** leveraging MySQL's native `JSON` columns for metadata and `MEDIUMBLOB` for binary assets.
- **Explicit indexing** using `index()` and `uniqueIndex()` on all foreign key columns and frequently queried fields to optimize lookup performance.

## Core Tables and Relationships

### Worker Management (workers.ts)

The [`dev/ee/packages/den-db/src/schema/workers.ts`](https://github.com/different-ai/openwork/blob/main/dev/ee/packages/den-db/src/schema/workers.ts) file defines the compute infrastructure layer:

- **`WorkerTable`** – The central registry for worker nodes with columns `org_id`, `created_by_user_id`, `name`, `description`, `destination` (enum: `['local','cloud']`), `status` (enum: `['provisioning','healthy','failed','stopped']`), `image_version`, `workspace_path`, and heartbeat tracking fields. It includes indexes on `org_id`, `status`, and `last_heartbeat_at`.

- **`WorkerInstanceTable`** – Tracks individual instances with `worker_id`, `provider`, `region`, `url`, and instance-specific status.

- **`DaytonaSandboxTable`** – Stores sandbox metadata including `worker_id` (unique), `sandbox_id` (unique), `workspace_volume_id`, `data_volume_id`, and signed preview URLs with expiration timestamps.

- **`WorkerTokenTable`** – Manages authentication tokens with `worker_id`, `scope` (enum: `['client','host','activity']`), and a unique `token` hash indexed for lookup.

- **`WorkerBundleTable`** – Records deployment artifacts linking `worker_id` to `storage_url` and bundle status.

- **`AuditEventTable`** – Immutable event log with `org_id`, optional `worker_id`, `actor_user_id`, `action` string, and `payload` stored as JSON.

### Organization & Access Control (org.ts)

The [`dev/ee/packages/den-db/src/schema/org.ts`](https://github.com/different-ai/openwork/blob/main/dev/ee/packages/den-db/src/schema/org.ts) file governs multi-tenant access and membership:

- **`OrganizationTable`** – Tenant root with `name`, unique `slug`, `logo` URL, `allowedEmailDomains` (JSON array), and `desktopAppRestrictions` (JSON object). Indexed on `createdAt` for pagination.

- **`MemberTable`** – Links users to organizations via `organizationId` and `userId` (unique composite index), tracking `role`, `joinedAt`, and soft-delete fields `removedAt`/`removedByOrgMember`.

- **`InvitationTable`** – Pending membership requests with `organizationId`, `email`, `role`, `status`, optional `teamId`, and a unique `inviteToken` for secure acceptance URLs.

- **`OrganizationRoleTable`** – Role-based access control (RBAC) definitions storing `organizationId`, `role` name, and `permission` strings with a unique composite index on (`organizationId`,`role`).

- **`WorkspaceBootstrapTable`** & **`WorkspaceClaimTable`** – Device enrollment flow tables managing `devicePublicKey`, `deviceKeyFingerprint`, and temporary claim tokens (`tokenHash` unique).

- **`InstallLinkTable`** – Desktop app installation links with unique `tokenHash`, expiration, and revocation tracking.

- **`DesktopHandoffGrantTable`** & **`DesktopConnectGrantTable`** – Short-lived authorization grants for desktop session handoffs, storing `session_token`, `codeHash`, and JSON `claims` with expiration.

- **`OrganizationBrandAssetTable`** – Binary storage for logos and icons with `organizationId`, `kind`, `version`, `extension` (unique composite index), and `bytes` (MEDIUMBLOB).

### Telemetry, Integrations, and System

Additional schema files handle specialized domains:

- **`TelemetryTable`** ([`telemetry.ts`](https://github.com/different-ai/openwork/blob/main/telemetry.ts)) – Stores diagnostic events per worker and organization.
- **`TelegramPairingTable`** ([`telegram.ts`](https://github.com/different-ai/openwork/blob/main/telegram.ts)) – Chat integration pairings.
- **`TeamTable`** ([`teams.ts`](https://github.com/different-ai/openwork/blob/main/teams.ts)) – Team definitions and member associations.
- **`SystemTable`** ([`system.ts`](https://github.com/different-ai/openwork/blob/main/system.ts)) – Key-value storage for feature flags and configuration.
- **`SubscriptionTable`** ([`subscriptions.ts`](https://github.com/different-ai/openwork/blob/main/subscriptions.ts)) – Billing and plan entitlement records.
- **Sharables** (`sharables/*.ts`) – Plugin capabilities and LLM provider credentials.

## Key Schema Design Patterns

### Custom ID Generation

All primary keys use the `denTypeIdColumn` helper to generate prefixed, lexicographically sortable identifiers. This ensures type safety at the application layer and prevents ID collisions across tables.

```typescript
// dev/ee/packages/den-db/src/columns.ts
export const denTypeIdColumn = (prefix: string) =>
  varchar("id", { length: 128 })
    .$defaultFn(() => generatePrefixedId(prefix))
    .primaryKey();

```

### Enumerated Types and Constraints

Constrained values use `mysqlEnum` rather than foreign key lookups to reduce join overhead:

- Worker `destination`: `['local', 'cloud']`
- Worker `status`: `['provisioning', 'healthy', 'failed', 'stopped']`
- Token `scope`: `['client', 'host', 'activity']`

### Indexing Strategy

Every foreign key column (e.g., `org_id`, `worker_id`, `userId`) and query predicate field (e.g., `status`, `email`, `expires_at`) receives an explicit index. Unique constraints protect critical fields like `slug`, `token`, and `inviteToken`.

### Relations and Joins

Though MySQL foreign keys are not enforced at the database level, the schema defines logical relationships using Drizzle's `relations()` helper in [`drizzle.ts`](https://github.com/different-ai/openwork/blob/main/drizzle.ts), enabling type-safe joins in queries.

## Instantiating the Drizzle Client

The schema is consumed by creating a Drizzle client instance in [`dev/ee/packages/den-db/src/client.ts`](https://github.com/different-ai/openwork/blob/main/dev/ee/packages/den-db/src/client.ts):

```typescript
import { drizzle } from "drizzle-orm/mysql2";
import mysql from "mysql2/promise";
import * as schema from "./schema";

const connection = await mysql.createConnection({
  host: process.env.DB_HOST,
  database: process.env.DB_NAME,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
});

export const db = drizzle(connection, { schema, mode: "default" });

```

Migrations are managed via `drizzle-kit` using the configuration in [`dev/ee/packages/den-db/drizzle.config.ts`](https://github.com/different-ai/openwork/blob/main/dev/ee/packages/den-db/drizzle.config.ts), outputting SQL files to the `drizzle/` directory.

## Practical Query Examples

### Insert a New Organization

```typescript
import { db } from "@openwork-ee/den-db/client";
import { OrganizationTable } from "@openwork-ee/den-db/src/schema/org";

await db.insert(OrganizationTable).values({
  id: "org_01abcdef",
  name: "Acme Corp",
  slug: "acme-corp",
  allowedEmailDomains: JSON.stringify(["acme.com"]),
});

```

### Query Active Workers by Organization

```typescript
import { WorkerTable } from "@openwork-ee/den-db/src/schema/workers";
import { eq, and } from "drizzle-orm";

const healthyWorkers = await db
  .select()
  .from(WorkerTable)
  .where(
    and(
      eq(WorkerTable.org_id, "org_01abcdef"),
      eq(WorkerTable.status, "healthy")
    )
  );

```

### Create a Worker Authentication Token

```typescript
import { WorkerTokenTable } from "@openwork-ee/den-db/src/schema/workers";

await db.insert(WorkerTokenTable).values({
  id: "workerToken_xyz123",
  worker_id: "worker_abc456",
  scope: "client",
  token: "secure-random-token-string",
  created_at: new Date(),
});

```

### Fetch Organization with Members (Join)

```typescript
import { OrganizationTable, MemberTable } from "@openwork-ee/den-db/src/schema/org";
import { eq } from "drizzle-orm";

const orgWithMembers = await db
  .select()
  .from(OrganizationTable)
  .leftJoin(MemberTable, eq(MemberTable.organizationId, OrganizationTable.id))
  .where(eq(OrganizationTable.id, "org_01abcdef"));

```

## Summary

- **OpenWork den-db** defines its MySQL schema using **Drizzle ORM** with modular files in `dev/ee/packages/den-db/src/schema/`.
- **Custom ID generation** via `denTypeIdColumn` creates prefixed, type-safe primary keys for all entities.
- **Core tables** include `WorkerTable`, `OrganizationTable`, `MemberTable`, and `AuditEventTable`, with explicit indexes on all foreign keys and query fields.
- **Flexible data types** such as `JSON` and `MEDIUMBLOB` store unstructured metadata and binary assets.
- **Relations** are defined using Drizzle's `relations()` helper for type-safe joins, though foreign key constraints are handled logically in the application layer.
- **Database client** initialization occurs in [`client.ts`](https://github.com/different-ai/openwork/blob/main/client.ts) using `drizzle()` with the full schema export and supports both standard MySQL and PlanetScale serverless modes.

## Frequently Asked Questions

### Where are the database migrations stored?

Migrations are generated by `drizzle-kit` and stored in the `dev/ee/packages/den-db/drizzle/` directory as SQL files. The configuration in [`drizzle.config.ts`](https://github.com/different-ai/openwork/blob/main/drizzle.config.ts) specifies the schema source path and migration output folder.

### Does den-db support PlanetScale?

Yes. The Drizzle client in [`client.ts`](https://github.com/different-ai/openwork/blob/main/client.ts) can be configured with `mode: "planetscale"` when using the PlanetScale serverless driver instead of the standard `mysql2` connection, allowing the same schema definitions to work with both standard MySQL and Vitess-based serverless deployments.

### How are foreign key relationships enforced?

The schema uses **logical relationships** via the `relations()` helper and TypeScript types rather than database-level foreign key constraints. This approach is required for compatibility with PlanetScale, which does not support foreign keys, while maintaining type safety through Drizzle's query builder.

### What is the purpose of `denTypeIdColumn`?

`denTypeIdColumn` is a custom column builder defined in [`columns.ts`](https://github.com/different-ai/openwork/blob/main/columns.ts) that automatically generates prefixed, lexicographically sortable identifiers (e.g., `worker_`, `org_`) using ULIDs. This provides globally unique IDs that are collision-resistant and carry semantic meaning about the entity type.