OpenWork Database Schema: MySQL Structure and Drizzle ORM Implementation

OpenWork uses a MySQL database managed through Drizzle ORM, with TypeScript schema definitions in ee/packages/den-db/src/schema/ and versioned migration files in ee/packages/den-db/drizzle/.

The database schema powering the OpenWork platform (available at different-ai/openwork) is designed to support multi-tenant organizations, plugin-driven extensibility, and external connector integrations. It is implemented using Drizzle ORM helpers such as mysqlTable, index, and uniqueIndex, then compiled into sequential SQL migration scripts. This architecture separates logical concerns into distinct table groups while maintaining referential integrity across the entire system.

Core Architecture and Technology Stack

OpenWork persists all core data in MySQL, accessed through a type-safe Drizzle ORM layer. The schema definitions are written in TypeScript and organized by functional domain, while the actual database structure is applied through numbered migration files.

Schema Definition Files

TypeScript schema files define tables using Drizzle ORM primitives:

• ee/packages/den-db/src/schema/org.ts – Defines organizations, roles, members, invitations, and workspace lifecycle tables
• ee/packages/den-db/src/schema/desktop-policies.ts – Contains DesktopHandoffGrantTable and DesktopConnectGrantTable for secure desktop client handshakes
• ee/packages/den-db/src/schema/sharables/plugin-arch.ts – Implements the plugin architecture including plugin, plugin_config_object, and access control tables
• ee/packages/den-db/src/schema/connector.ts – Models external integrations with tables for accounts, instances, targets, mappings, and sync events
• ee/packages/den-db/src/schema/telemetry.ts – Captures analytics data through telemetry_event and telemetry_session_dimension tables

Migration Strategy

Database migrations live in ee/packages/den-db/drizzle/ and follow sequential numbering. Key migration files include:

• 0001_desktop_handoff_grants.sql – Creates short-lived grant tables for desktop authentication
• 0010_plugin_arch.sql – Establishes the plugin and configuration object hierarchy

Logical Table Groups in the OpenWork Schema

The schema is partitioned into logical groups that mirror the platform's core concepts.

Organization and Membership

These tables handle multi-tenancy and access control:

• organization – Root tenant entity
• organization_role – Custom role definitions per organization
• member – Links users to organizations with role assignments
• invitation – Pending membership invitations
• workspace_bootstrap and workspace_claim – Manage provisioning flows for new workspaces

Desktop Handoff and Connect

Short-lived security grants for desktop client authentication:

• desktop_handoff_grant – Temporary tokens allowing desktop clients to establish sessions
• desktop_connect_grant – Connection authorization grants

These are defined in src/schema/desktop-policies.ts and support the secure handshake flow between desktop applications and the OpenWork server.

Plugins and Configuration Objects

The extensibility layer uses the following tables defined in src/schema/sharables/plugin-arch.ts:

• plugin – Registered plugins (skills, agents, tools)
• plugin_config_object – Links plugins to their configurable objects
• plugin_access_grant – Fine-grained access control for plugin usage
• config_object and config_object_version – Versioned configuration storage
• config_object_access_grant – Access rights to specific configuration objects

Connector Integration

External system integrations (such as GitHub) are modeled in src/schema/connector.ts:

• connector_account – Authenticated external service accounts
• connector_instance – Specific connector configurations
• connector_target – Endpoints or repositories being synced
• connector_mapping – Relationships between external and OpenWork entities
• connector_sync_event – Audit trail of synchronization operations
• connector_source_binding and connector_source_tombstone – Handle source attachment and deletion tracking

Telemetry and Diagnostics

Analytics and debugging tables track platform usage:

• telemetry_event – Individual usage events
• telemetry_session_dimension – Session metadata and attributes
• diagnostics – System health and error data

SCIM and Team Management

Enterprise provisioning support includes:

• scim_group – External identity provider group mappings
• team – Internal team structures
• scim_sync_events – Audit logs for SCIM provisioning operations

Additional Shared Resources

Supporting tables for platform capabilities:

• plugin_arch – Plugin architecture metadata
• llm_providers – Large language model provider configurations
• capability_credentials – Stored credentials for platform capabilities

Querying the OpenWork Schema with Drizzle ORM

The following examples demonstrate practical usage patterns from the OpenWork codebase. These assume a Drizzle client exported from ee/packages/den-db/src/drizzle.ts.

Fetching Organization Data

import { db } from "@openwork/den-db";
import { organization, member, eq, and, isNull } from "@openwork/den-db/src/schema";

// Retrieve an organization by its unique slug
export async function getOrgBySlug(slug: string) {
  const org = await db
    .select()
    .from(organization)
    .where(eq(organization.slug, slug))
    .limit(1);
  
  return org[0] ?? null;
}

// List active members excluding removed accounts
export async function listActiveMembers(orgId: string) {
  return db
    .select()
    .from(member)
    .where(
      and(
        eq(member.organizationId, orgId),
        isNull(member.removedAt)
      )
    );
}

Managing Desktop Handoff Grants

import { db } from "@openwork/den-db";
import { DesktopHandoffGrantTable } from "@openwork/den-db/src/schema/desktop-policies";

// Create a short-lived handoff token for desktop authentication
export async function createHandoffGrant(params: {
  id: string;
  userId: string;
  sessionToken: string;
  expiresAt: Date;
}) {
  await db.insert(DesktopHandoffGrantTable).values({
    id: params.id,
    user_id: params.userId,
    session_token: params.sessionToken,
    expires_at: params.expiresAt,
    created_at: new Date(),
  });
}

Registering Plugins and Configuration

import { db } from "@openwork/den-db";
import { plugin } from "@openwork/den-db/src/schema";

// Register a new plugin within an organization
export async function registerPlugin(pluginData: {
  id: string;
  organizationId: string;
  name: string;
  description?: string;
}) {
  await db.insert(plugin).values({
    id: pluginData.id,
    organization_id: pluginData.organizationId,
    name: pluginData.name,
    description: pluginData.description ?? null,
    status: "active",
    created_at: new Date(),
    updated_at: new Date(),
  });
}

Summary

• OpenWork uses MySQL as its primary data store, managed through Drizzle ORM for type-safe database operations.
• Schema definitions are organized by domain in ee/packages/den-db/src/schema/, covering organizations, desktop policies, plugins, connectors, and telemetry.
• The migration system uses sequentially numbered SQL files in ee/packages/den-db/drizzle/ to version database changes.
• Logical table groups separate concerns for multi-tenancy (organization, member), extensibility (plugin, config_object), and external integrations (connector_account, connector_sync_event).
• Desktop handoff grants provide secure, short-lived authentication tokens for desktop client connections.

Frequently Asked Questions

What database does OpenWork use?

OpenWork uses MySQL as its relational database. The schema is defined using Drizzle ORM, which provides TypeScript type safety and generates the underlying SQL migrations applied to the MySQL instance.

Where are the Drizzle ORM schema definitions located?

TypeScript schema definitions are located in ee/packages/den-db/src/schema/. Key files include org.ts for membership data, desktop-policies.ts for authentication grants, sharables/plugin-arch.ts for plugin storage, and connector.ts for external integrations.

How does OpenWork handle database migrations?

Migrations are stored as numbered SQL files in ee/packages/den-db/drizzle/ (such as 0001_desktop_handoff_grants.sql and 0010_plugin_arch.sql). These files are applied sequentially to evolve the database schema while maintaining data integrity across deployments.

What tables store plugin configuration in OpenWork?

Plugin configuration is stored across several tables in the plugin architecture group: plugin stores the plugin registry, config_object and config_object_version handle versioned configurations, plugin_config_object links plugins to their objects, and plugin_access_grant manages permission controls. These definitions are found in ee/packages/den-db/src/schema/sharables/plugin-arch.ts.

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