Staged Drizzle Migration for Better Auth and D1

The project is D1-first and not currently Drizzle-backed

wrangler.jsonc defines a D1 binding named DB with migrations in ./migrations. package.json does not currently include drizzle-orm, drizzle-kit, or a Better Auth Drizzle adapter dependency.

Evidence: wrangler.jsonc contains d1_databases[{ binding: "DB" }]; scripts currently run wrangler d1 migrations apply; dependencies include better-auth but not Drizzle packages.

Existing app queries are concentrated but extensive

The main application data layer lives in src/lib/order-service-data.ts. It uses env.DB.prepare(...).bind(...).run/all/first and db.batch(...) throughout. It also carries an in-code SCHEMA_SQL bootstrap that duplicates and extends the migration files.

Evidence: source review found hundreds of direct D1 statements in src/lib/order-service-data.ts, plus additional direct D1 usage in src/email-status-durable-object.ts.

Better Auth is currently a placeholder that must change anyway

src/lib/auth.ts currently imports better-sqlite3 and creates a module-level database with new Database(). That is not suitable for the Cloudflare Worker D1 binding and should be replaced whether the implementation uses direct D1/Kysely or Drizzle.

Evidence: src/lib/auth.ts uses database: new Database() and does not receive request-scoped env.DB.

Migration ownership can split across too many tools

Better Auth's Drizzle path generates Drizzle schema, then drizzle-kit generates/applies migrations. The project currently applies SQL migrations with Wrangler. If both are used independently, schema history can diverge between local, preview, and production databases.

Auth tables use Better Auth naming conventions

Better Auth core tables are normally user, session, account, and verification, with camelCase columns such as emailVerified, createdAt, and userId. The app's existing schema uses snake_case columns such as created_at, service_date, and order_json.

Drizzle in Workers must remain request-env scoped

The Drizzle client for D1 must be created from the current request's env.DB binding, not from a module-level database connection. The same constraint applies to Better Auth: export a createAuth(env) factory rather than a static singleton.

import { drizzle } from "drizzle-orm/d1";

export const createDb = (env: Env) => drizzle(env.DB, { schema });

Direct D1 and Drizzle transaction semantics differ

Existing code often uses db.batch() for grouped writes. Drizzle provides its own transaction and batch patterns, but D1 support and behavior should be validated in the Worker runtime before rewriting critical write flows such as publishing an order, recording hymn plays, or updating email delivery status.

Evidence: direct D1 batching appears in seed/bootstrap paths, order save/delete paths, hymn-play updates, email settings, and team membership writes.

Generated auth schema must track plugins

Better Auth plugin changes can add tables, columns, or relations. A Drizzle-first auth implementation should treat npx auth@latest generate as part of the auth-change workflow and should review the generated diff before applying it to D1.

Phase 0: Decide migration boundaries

1. Keep existing app tables and direct D1 queries unchanged.
2. Add Drizzle only for Better Auth and only under auth/database files such as src/db/auth-schema.ts and src/db/client.ts.
3. Keep applied migrations in migrations/ via Wrangler until a later explicit migration-runner switch.

Phase 1: Better Auth through Drizzle

1. Add dependencies: drizzle-orm, drizzle-kit as a dev dependency, and use the Better Auth Drizzle adapter export available in Better Auth 1.6.23 (better-auth/adapters/drizzle) or the dedicated package path if chosen after install testing.
2. Generate the Better Auth Drizzle schema from the final auth config with npx auth@latest generate.
3. Add an auth migration under migrations/0009_... and apply it locally/remotely with the existing Wrangler scripts.
4. Replace src/lib/auth.ts with an env-scoped createAuth(env) factory that wraps drizzle(env.DB) in the Drizzle adapter.
5. Route /api/auth/* to createAuth(env).handler(request) and validate GET /api/auth/ok.

Phase 2: Model existing tables without rewriting queries

Add Drizzle table definitions for the current app schema, but do not switch production query paths yet. This gives compile-time schema feedback and exposes naming/type mismatches while leaving behavior unchanged.

• Model service_types, orders_of_service, hymns, teams, team_members, and join tables in Drizzle.
• Add type aliases such as InferSelectModel and compare them to existing hand-written TypeScript interfaces.
• Do not generate destructive migrations from these table definitions until the schema exactly matches the existing D1 database.

Phase 3: Migrate app queries feature by feature

Convert one bounded area at a time. Good candidates are low-risk lookup/read paths first, then write paths with tests.

1. Reference data reads: service types, statuses, activity types.
2. Simple settings and email recipient reads/writes.
3. Hymn library reads and hymn file metadata writes.
4. Team and team-member CRUD.
5. Order-of-service save/publish/delete flows, which are higher risk because they combine JSON payloads, uniqueness checks, hymn play updates, and PDF/email side effects.

Phase 4: Consolidate migration tooling

Once most app tables are represented in Drizzle, decide whether to switch fully to drizzle-kit generated migrations or continue generating SQL and applying through Wrangler. Avoid indefinitely using both as independent sources of truth.

Schema modeling strategy

Model existing tables exactly first. Do not rename columns or normalize JSON fields as part of the first Drizzle pass. Examples:

import { sqliteTable, text, integer, uniqueIndex } from "drizzle-orm/sqlite-core";

export const ordersOfService = sqliteTable(
    "orders_of_service",
    {
        id: text("id").primaryKey(),
        title: text("title").notNull(),
        serviceTypeId: text("service_type_id").notNull(),
        serviceDate: text("service_date").notNull(),
        status: text("status").notNull().default("Planning"),
        templateId: text("template_id"),
        orderJson: text("order_json").notNull(),
        pdfObjectKey: text("pdf_object_key"),
        publishedAt: text("published_at"),
        createdAt: text("created_at").notNull().default("CURRENT_TIMESTAMP"),
        updatedAt: text("updated_at").notNull().default("CURRENT_TIMESTAMP"),
    },
    (table) => [
        uniqueIndex("orders_of_service_service_date_unique_idx").on(table.serviceDate),
    ]
);

The snippet is illustrative; defaults and foreign keys should be validated against Drizzle's current SQLite/D1 APIs before use.

Coexistence pattern during migration

Use a shared request-scoped database factory, but let each module choose whether it still needs direct D1 or Drizzle.

export const createDb = (env: Env) => drizzle(env.DB, { schema });
export const getD1 = (env: Env) => env.DB;

This keeps the boundary explicit and makes feature-by-feature migration possible without an all-at-once rewrite.