Database

One port for billing storage, and a schema the deploy converges.

paykit keeps its event log, receipts, state and read models in SQL. The database is a port: the model asks for an executor, and a deployment chooses the target. Nothing in the model imports a driver, check() still answers from one local read, and a Worker never runs a migration.

One port, two obligations

Paykit.Database is the port. A target supplies one DatabaseService:

interface DatabaseService {
  dialect: "sqlite" | "postgres" | "mysql";
  schema?: string; // the Postgres schema, when the target has one
  runtime: Effect<SqlExecutor, never, RuntimeContext | Scope.Scope>; // per execution
  migrate?: {
    // absent when the target cannot migrate
    identity: Input<Record<string, unknown>>; // non-secret identity of the target
    connect: Effect<Effect<SqlExecutor, MigrationApplyError, Scope.Scope>, never, RuntimeContext>;
  };
}

runtime is opened per execution and closed when the event settles, which is the only pooling shape workerd allows. migrate.connect is a separate obligation because a migration needs one dedicated session: the registry’s bookkeeping queries are unqualified, so Postgres scopes search_path around them, and a pooled connection would spread the statements across sessions.

The two connections are also separate because they exist in different places. A Hyperdrive connection string is only valid inside the Worker, so the deploy cannot use it; migrations take the origin URL, or the target has no migrate at all. identity is the non-secret identity of the target — a URL digest on Postgres, a filename on SQLite — and never the URL itself, because it is persisted in the migration Action’s input. schema names the Postgres schema (billing by default; public is never touched) and is ignored by SQLite.

Choose a target

The layers ship one per target and are imported from their own export condition, never the barrel. The driver is a peer dependency, so a Worker that imports the package must not pull pg or bun:sqlite into its bundle, and a Bun test must not pull a D1 binding.

Provide one layer where the deployment builds its world, and declare migrate on the billing stack:

import { Paykit } from "@potion.run/paykit";
import { Postgres } from "@potion.run/paykit/Database/Postgres";
import * as Config from "effect/Config";
import * as Effect from "effect/Effect";

Effect.gen(function* () {
  const url = yield* Config.Redacted("POSTGRES_URL");

  yield* Paykit.BillingStack("Billing", { ...billing, migrate: true }).pipe(
    Effect.provide(Postgres(url, { schema: "billing" })),
  );
});

The boolean mirrors BetterAuth({ migrate }): true is explicit, absent means “migrate when a layer is provided”, and false opts out — the application’s own pipeline owns the tables. A request later reads the executor from Paykit.Database.

SQLite is the development and test target. One file, one process, migrations and requests share the same connection, and there is nothing to provision. It is what the SQL tests run.

D1 reaches the database through the Worker’s native binding at runtime, and the D1 HTTP API — including the local simulator under alchemy dev — for migrations. No migrations directory is involved: the deploy applies migrationFiles() and converges it, like every other target. A Worker host provides Cloudflare.D1.QueryDatabaseBinding; the stack does not need it.

import * as Cloudflare from "@alchemy.run/cloudflare";

export const BillingDatabase = Cloudflare.D1.Database("BillingDatabase", {
  migrations: "./migrations",
});

D1 has no interactive transactions: one batch is the closest it offers. That is fine for the session and small-read workload it usually carries, but a store or plugin that needs a real transaction belongs on Postgres.

Postgres is the target for a durable deployment. In a Worker it is reached through Hyperdrive, which pools the connections workerd would otherwise open per request; a test or a long-lived service uses pg directly. When the runtime and the deploy use different credentials — the usual Hyperdrive arrangement — migrate.connect names the origin connection and runtime keeps the binding. Neon, Aurora and a plain connection string are the same two obligations with a different driver.

MySQL is a third dialect with three consequences worth knowing. A key or index column renders as VARCHAR(191) because MySQL rejects TEXT in a key or an index without a prefix length; convergence checks information_schema.statistics before creating an index because MySQL 8 has no CREATE INDEX IF NOT EXISTS; and every upsert spells ON DUPLICATE KEY UPDATE col = VALUES(col) instead of ON CONFLICT ... excluded, from one helper. MySQL’s namespace is the connection’s database, so schema stays undefined and no table is schema-qualified.

Drizzle is a connection, not a schema owner. Database/Drizzle unwraps the pool behind db.$client and every store statement runs on it, so an application keeps one pool and one transaction boundary. BILLING_SCHEMA is still the source: letting Drizzle generate the tables would make the ORM the source and the description a generator, which ADR 0049 rejects. Pass migrate: false if the application’s own pipeline applies paykit’s tables.

The baseline, the description, and the delta

BILLING_SCHEMA is the schema as typed data: tables, columns and indexes. A second, frozen description — BILLING_SCHEMA_V1 — is the first release, and migrationFiles() renders it for a dialect. That split is what makes the history safe without a file on disk: the registry keys every applied row by the hash of the baseline’s rendering, so the baseline must never move. A schema change edits BILLING_SCHEMA, and convergence applies it.

import { migrationFiles } from "@potion.run/paykit";

const [init] = migrationFiles({ dialect: "postgres", schema: "billing" });

A schema change moves through one pipeline:

1. Describe. Change BILLING_SCHEMA, building it from BILLING_SCHEMA_V1. A type is stated once; the renderer owns INTEGER versus BIGINT for epoch millis, AUTOINCREMENT versus BIGSERIAL versus AUTO_INCREMENT, and identifier quoting.
2. Apply. The deploy runs the frozen baseline through @alchemy.run/sql/Migrations, which owns the bookkeeping table and the content hashes.
3. Converge. The deploy reads the live catalog and adds the tables, columns and indexes the description states and the database lacks.

The baseline is frozen and its rendered hash is pinned by a test. Convergence is the delta for a database that already has a history. A new table, column or index is applied without editing what a database already recorded. Each step is independently idempotent, so a crash mid-deploy converges on the next run.

What a deploy guarantees, and what it refuses

Paykit.BillingStack("Billing", { ...billing, migrate: true }) registers one Action per migrated target, with this input:

{ identity, schemaFingerprint, migrationFingerprint, dialect }

A deploy therefore:

• applies the frozen baseline once, in order, and records its content hash;
• re-runs the Action when the description changes or the target database is replaced — and does no database work when neither did;
• runs only at apply: never at plan, never inside the deployed runtime;
• binds the Action’s output into the host environment, so the Worker depends on it and cannot start against a half-migrated database;
• converges idempotently — a missing table, column or index is added, and a present one is left alone.

It refuses:

Auth and billing own their own tables

Better Auth and paykit are two products with two migrators. Neither reads or writes the other’s tables, so one database with two disjoint table families and two databases are both supported — and a version bump in one never rewrites the other’s history.

The split matters most on D1. Sessions are a small, transactional read/write workload that D1 serves well; the billing store is the event log, and a plugin that needs interactive transactions has no way to get one there. Put those on Postgres, either in a second database or in the same one under a different table family. examples/auth/test/coexistence.test.ts runs both migrators against one SQLite handle: Better Auth’s getMigrations creates user/session/account/verification, Paykit.sync creates the billing_* family and records one billing_schema_version row, and neither migrator touches the other’s tables or history.

The decisions are ADR 0049 for the port and the convergence, and ADR 0003 for why a deploy retains what a bill references.

See Auth for the session side, including what a deployment must preserve across deploys.

Each target is a page: D1, Postgres, MySQL, Sqlite, and Drizzle.