OpenWork den-db MySQL Schema: Complete Drizzle ORM Reference

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), 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 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 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) – Stores diagnostic events per worker and organization.
• TelegramPairingTable (telegram.ts) – Chat integration pairings.
• TeamTable (teams.ts) – Team definitions and member associations.
• SystemTable (system.ts) – Key-value storage for feature flags and configuration.
• SubscriptionTable (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.

// 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, 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:

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, outputting SQL files to the drizzle/ directory.

Practical Query Examples

Insert a New Organization

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

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

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)

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 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 specifies the schema source path and migration output folder.

Does den-db support PlanetScale?

Yes. The Drizzle client in 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 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.

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