Proposal: Drizzle backend (openworkflow/drizzle) #880

Follow-up to #877 (item 4 and the Drizzle question). Here's the design we'd like to build so you can check it before we open a PR.

Desired Behavior

Apps that already use Drizzle can run OpenWorkflow in their existing database, through their existing driver and migrations:

• Tables come from a factory, and the app owns the migrations. createSqliteTables() and createPgTables() return Drizzle tables. The app exports them from its schema, so drizzle-kit generate puts the DDL in the app's normal migration stream. The factories take an optional prefix (applied to table, index and constraint names), which also covers the table-prefix request in Adopting OpenWorkflow in Last Light: feedback, and four proposals we'd like to contribute #877. createPgTables also takes an optional schema, like BackendPostgres today.
• The backend takes an injected db. BackendDrizzleSqlite and BackendDrizzlePg are built from { db, tables, namespaceId? }. They never open or close connections; stop() leaves the app's db alone.
• The DDL matches the existing backends column for column. That includes names, types, partial and DESC indexes, constraint names and ON DELETE rules. So an existing BackendPostgres or BackendSqlite database can switch to the Drizzle backend without a data migration.
• The queries are ports of the existing backends. Each method mirrors the matching BackendSqlite / BackendPostgres query, including FOR UPDATE SKIP LOCKED on Postgres, so review can compare them side by side. The SQLite backend serializes its own write transactions in-process, because async drivers like libsql can otherwise interleave them.

Packaging would follow openworkflow/postgres: a ./drizzle subpath export, with drizzle-orm as an optional peer dependency (>=0.45.0 <2). The code uses only the core query builder, with no relational queries and no drizzle-kit at runtime.

Example Usage

// db/schema.ts — drizzle-kit picks these up alongside the app's own tables
import { createSqliteTables } from "openworkflow/drizzle";

export const {
  workflowRuns: owWorkflowRuns,
  stepAttempts: owStepAttempts,
  workflowSignals: owWorkflowSignals,
} = createSqliteTables({ prefix: "ow_" });

// runtime
import { OpenWorkflow } from "openworkflow";
import { BackendDrizzleSqlite } from "openworkflow/drizzle";
import { drizzle } from "drizzle-orm/libsql";
import * as schema from "./db/schema";

const db = drizzle(client, { schema });
const backend = new BackendDrizzleSqlite({
  db,
  tables: {
    workflowRuns: schema.owWorkflowRuns,
    stepAttempts: schema.owStepAttempts,
    workflowSignals: schema.owWorkflowSignals,
  },
});
const ow = new OpenWorkflow({ backend });

Testing

• testBackend runs on libsql (SQLite) and postgres.js (Postgres), both unprefixed and with a prefix. The tables are created with drizzle-kit's pushSchema, so the test goes through the same path an app uses. The only new dev dependencies are drizzle-orm, drizzle-kit and @libsql/client, and no new CI services.
• A schema-parity test builds one database with the existing migrate() and one from the factory tables, introspects both, and checks they're identical.
• A second CI leg runs on the Drizzle 1.0 release candidate.

We'd also like to export the conformance suite as openworkflow/testing (with vitest as an optional peer dependency), so people with other drivers or their own backends can run it. That would be a separate commit in the PR, and we're happy to split it into its own PR.

What we've checked

We built the table factories and checked them with Drizzle 0.45 and drizzle-kit 0.31, and with the 1.0 release candidate:

• drizzle-kit picks up factory tables exported from an app schema. With a prefix, they sit next to the app's own workflow_runs table without colliding.
• The Postgres DDL is identical to migrate(), unprefixed and prefixed. That covers every column, index, constraint name and ON DELETE rule.
• The SQLite DDL is identical too, with one exception on drizzle-kit 0.31: it splits json_extract("context", '$.signal') on the comma, so the step_attempts_signal_wait_idx expression has to be written as "context" ->> '$.signal' there. The 1.0 release candidate handles json_extract correctly, and parity is exact. We'd document the 0.31 exception and drop it once 1.0 ships.
• On Postgres, .desc() emits DESC NULLS LAST, so the factory uses .desc().nullsFirst() to match the existing DESC indexes.
• The factories don't emit CREATE INDEX CONCURRENTLY. Drizzle migrations run inside a transaction, where CONCURRENTLY isn't allowed. Apps with large tables can edit the generated migration.

Questions

1. Does a subpath export inside openworkflow work for you, or would you prefer a separate package (e.g. @openworkflow/backend-drizzle)?
2. Any objection to exporting the conformance suite as openworkflow/testing?

We know Backend is still changing (concurrency, schedules). Keeping the Drizzle backend in this repo means testBackend checks it alongside the other two backends whenever the interface changes.