Current D1 Data Layer to Full Drizzle ORM

D1 is already the real database platform

wrangler.jsonc defines a D1 database binding named DB for vbc-order-of-service, and package scripts already apply D1 migrations locally and remotely with Wrangler.

Evidence: d1_databases[0].binding is DB, migrations_dir is ./migrations, and scripts include db:migrate:local and db:migrate:remote.

Drizzle is installed but not integrated

package.json includes drizzle-orm and drizzle-kit at 1.0.0-rc.4, and src/lib/db.ts exports drizzle(env.DB). However, there is no drizzle.config.ts, no Drizzle schema file, and the client export is not used by the application data layer.

Evidence: source search found one Drizzle import in src/lib/db.ts; all substantive app queries are still in src/lib/order-service-data.ts and src/email-status-durable-object.ts through direct D1 APIs.

The current schema has two sources of truth

The project has checked-in D1 migrations, but src/lib/order-service-data.ts also contains a large SCHEMA_SQL string and runs it at request time through ensureDatabase(). That runtime bootstrap also handles a duplicate-column case for pdf_object_key, while migration 0004 is a no-op.

Evidence: SCHEMA_SQL is about 5 KB, and ensureDatabase() splits and applies it with db.prepare(statement).run() before seeding reference data.

The raw D1 surface is concentrated but broad

The main data module has roughly 105 .prepare() calls, 11 .batch() calls, 22 .first() calls, 26 .all() calls, and 17 .run() calls. The Durable Object email sender also reads and updates D1 directly.

Evidence: counted direct D1 API usages in src/lib/order-service-data.ts and reviewed src/email-status-durable-object.ts.

The auth database path is not Cloudflare-ready

src/lib/auth.ts creates Better Auth with new Database() from better-sqlite3. A full Drizzle + D1 migration should replace this with the Better Auth Drizzle adapter and the request/env-scoped D1 Drizzle client.

Evidence: current Better Auth Drizzle adapter docs use drizzleAdapter(db, { provider: "sqlite" }) and require generated migrations for Better Auth tables and plugin schemas.

D1 platform limits shape migration scripts

Current D1 docs list practical limits that affect generated SQL and data backfills: 100 bound parameters per query, 100 KB maximum SQL statement length, 2 MB maximum string/BLOB/row size, and a 30 second SQL query duration limit. Individual batch statements are subject to individual query limits.

Evidence: retrieved /d1/platform/limits/ on 2026-07-02. The docs also state each individual D1 database processes queries one at a time, so migration backfills should be chunked.

Repository state should be stabilized before schema work

The working tree already has uncommitted database-related changes, including package.json, pnpm-lock.yaml, src/lib/auth.ts, and src/lib/db.ts. There are also both package-lock.json and pnpm-lock.yaml files.

Evidence: git status --short showed modified and untracked files. Pick one package manager and commit or discard scaffolding before generating Drizzle migrations.

Drizzle schema and request/env-scoped clients

Put table definitions in src/db/schema.ts and expose a factory instead of a global singleton that hard-codes cloudflare:workers environment access.

import { drizzle } from "drizzle-orm/d1";
import * as schema from "./schema";

export const createDb = (binding: D1Database) =>
    drizzle(binding, { schema });

Server functions can call createDb(env.DB) or a small wrapper around the current cloudflare:workers env. Durable Objects should use createDb(this.env.DB).

Code-first schema after a database-first baseline

Start database-first by pulling or manually matching the existing D1 schema. After the baseline is reviewed, switch future changes to code-first Drizzle schema and Drizzle-generated migrations.

import "dotenv/config";
import { defineConfig } from "drizzle-kit";

export default defineConfig({
    schema: "./src/db/schema.ts",
    out: "./drizzle",
    dialect: "sqlite",
    driver: "d1-http",
    dbCredentials: {
        accountId: process.env.CLOUDFLARE_ACCOUNT_ID!,
        databaseId: process.env.CLOUDFLARE_DATABASE_ID!,
        token: process.env.CLOUDFLARE_D1_TOKEN!,
    },
});

If the team keeps Wrangler as the migration executor, configure migrations_dir and, for nested Drizzle layouts, migrations_pattern. Do not have both Wrangler and Drizzle Kit applying the same new migrations to the same database.

Schema modules by domain

Keep the schema maintainable by grouping definitions by feature and exporting them through a single schema index.

• src/db/schema/reference.ts: service/activity/hymn source tables.
• src/db/schema/orders.ts: templates, orders, hymn plays, email deliveries.
• src/db/schema/hymns.ts: hymns and hymn files.
• src/db/schema/teams.ts: teams, members, memberships.
• src/db/schema/settings.ts: app settings and recipients.
• src/db/schema/auth.ts: Better Auth tables generated or reviewed for Drizzle.

Freeze, back up, and pick the migration authority

1. Commit or stash current DB-related work so generated migrations are reviewable.
2. Export the remote D1 database before any migration rehearsal: npx wrangler d1 export vbc-order-of-service --remote --output ./backups/vbc-order-of-service-YYYYMMDD.sql.
3. Create or identify a staging D1 database and bind it through a Wrangler environment.
4. Decide whether future migrations are applied by drizzle-kit migrate or by wrangler d1 migrations apply against Drizzle-generated SQL. The recommended full-Drizzle path is drizzle-kit generate plus drizzle-kit migrate, with the current migrations/ directory retained as historical bootstrap material until a fresh-D1 bootstrap is proven.

Baseline the existing D1 schema in Drizzle

1. Add drizzle.config.ts using dialect: "sqlite" and driver: "d1-http".
2. Pull or manually author a Drizzle schema that exactly matches the deployed D1 tables, indexes, unique constraints, foreign keys, default values, and column names.
3. Use drizzle-kit pull --init on staging or a disposable remote copy if using Drizzle Kit as the migration runner. Verify it produces a bootstrap path for fresh databases; if it does not, create an explicit baseline migration and mark it applied only for existing environments.
4. Model text JSON columns such as template_json, order_json, and app_settings.value with typed helpers, but keep their physical storage as TEXT for a no-data-move migration.
5. Add Drizzle relations for teams, team members, hymns, templates, orders, and Better Auth tables where relation queries are useful.

Add the real Drizzle client without changing behavior

1. Replace src/lib/db.ts with an env-scoped client factory and import the full schema.
2. Add a small getAppDb() wrapper for TanStack Start server functions if keeping cloudflare:workers env access.
3. Keep existing raw D1 queries live for this phase. The acceptance check is that the application builds with Drizzle schema and client present but no app behavior changes.

Migrate Better Auth onto Drizzle + D1

1. Remove the better-sqlite3 auth path and create an auth factory that accepts the Drizzle D1 client.
2. Configure drizzleAdapter(db, { provider: "sqlite" }) and pass schema mappings if Better Auth model names differ from table names.
3. Generate Better Auth Drizzle schema/migrations after selecting plugins. Re-run generation whenever plugins change.
4. Add /api/auth/* route handling and verify GET /api/auth/ok before protecting application routes.

Port app queries by low-risk domain first

1. Start with reference reads: service_types, activity_types, and hymn_sources.
2. Move templates and orders next, preserving mapper outputs and the unique orders_of_service.service_date behavior.
3. Move hymns and hymn files, keeping R2 writes/deletes separate from D1 writes and compensating for partial failures.
4. Move teams and team members, including the many-to-many team_member_teams table and hierarchy validation.
5. Move email settings and delivery records, then update OrderEmailStatusDurableObject to use the same createDb(this.env.DB) path.

Remove runtime schema bootstrap and raw D1 access

1. Delete SCHEMA_SQL and remove DDL from ensureDatabase() once migrations fully own schema.
2. Convert seed data to idempotent migrations or a dedicated seed command that is not run on every request.
3. Search for remaining .prepare(, .batch(, and env.DB usages. The only allowed direct access should be inside Drizzle client construction or exceptional documented raw SQL helpers.
4. Update scripts and README so the team has one migration workflow and one package manager workflow.

Cut over in staging, then production

1. Apply migrations to staging, deploy Drizzle-backed code, and run the full parity checklist.
2. Export production immediately before production migration.
3. Apply only additive migrations for the first production cutover. Delay column drops or data reshapes until a later cleanup release.
4. Deploy the Worker, monitor logs and D1 errors, then keep the previous Worker version ready for rollback.

Migration table split-brain

Wrangler records applied D1 migrations in d1_migrations. Drizzle Kit migrate uses its own migration log. Use one authority per environment after the baseline, or document a strict split where one tool only generates SQL and the other only applies it.

Generated schema drift from hand-authored SQL

The current SQL uses raw SQLite features, idempotent seeds, JSON stored as text, manual timestamps, and no-op migration history. Generated Drizzle schema should be reviewed table by table before any migration is applied.

Raw SQL will not disappear completely

Some queries may remain clearer as typed sql fragments inside Drizzle, especially aggregate dashboard queries and SQLite date expressions. The goal should be no direct env.DB.prepare() usage in application modules, not zero SQL strings anywhere.

Open questions, resolved

What was implemented

• Drizzle schema (src/db/schema/): reference.ts, hymns.ts, orders.ts, teams.ts, settings.ts, auth.ts, and an index.ts aggregator. Columns, defaults, nullability, unique constraints, indexes and foreign keys mirror migrations/0001–0009.
• Client factory (src/db/client.ts): createDb(binding) and getAppDb(). The old src/lib/db.ts singleton was removed (nothing imported it).
• Better Auth (src/lib/auth.ts): createAuth(env) using drizzleAdapter(createDb(env.DB), { provider: "sqlite" }), replacing the non-functional better-sqlite3 in-memory stub. Mounted in src/worker.ts (the Worker fetch routes /api/auth/* to auth.handler before delegating to TanStack Start — Start 1.168 has no createServerFileRoute).
• Data layer ported (src/lib/order-service-data.ts): all 47 server functions and the helpers moved off raw D1. The OrderEmailStatusDurableObject now uses createDb(this.env.DB). Zero .prepare()/.bind()/env.DB remain in application modules.
• Runtime bootstrap removed: the ~5 KB SCHEMA_SQL, ensureDatabase(), databaseInitialized, and every await ensureDatabase() call are gone. Seeds already live in the migrations, so nothing is seeded at request time.
• Migration 0004 fixed: it was a no-op (SELECT 1) because ensureDatabase added pdf_object_key at runtime. It now does the real ALTER TABLE … ADD COLUMN so fresh, Wrangler-only databases get the column. Existing databases already have 0004 recorded as applied, so it will not re-run.
• New migration 0009_add_auth_tables.sql: Better Auth user/session/account/verification tables.

Decisions that differ from a naive reading of the plan

• drizzle-orm 1.0.0-rc.4 dropped the schema option from the D1 drizzle() config (its v1 relational API uses defineRelations). We therefore use the core query builder with explicitly-passed tables plus typed sql fragments, and do not use the db.query.* relational API or Drizzle relations. This fully meets the "no env.DB.prepare()" goal.
• Hybrid porting (deliberate). Straight CRUD, upserts (onConflictDoUpdate/onConflictDoNothing), batches and existence checks use the query builder. Aggregate/dashboard reads, the times_played_last_6_months correlated subquery, GROUP_CONCAT, date('now','-6 months') and CAST(NULLIF(…)) ordering are kept as typed sql fragments (via db.all/get/run(sql\`…\`)) — the plan explicitly allows this ("no direct env.DB.prepare()", not "zero SQL strings"). Because those fragments return rows keyed by the original snake_case column names, the existing mapper functions are unchanged, which minimizes regression risk.
• Unique-constraint detection hardened. isServiceDateUniqueConstraintError now walks the error cause chain, because Drizzle wraps the underlying D1 error.
• Auth mounted at the Worker edge rather than as a framework server route, for robustness against the evolving TanStack Start server-route API.

Validation performed

• tsc --noEmit: clean.
• vitest run: 65/65 pass (teams-logic.test.ts).
• pnpm build (vite build && tsc): success; no better-sqlite3 in the bundle.
• ultracite check: no findings in changed files (one pre-existing, unrelated finding in src/routes/planner.tsx).
• Fresh-database rehearsal in an isolated --persist-to state dir: all 9 migrations apply cleanly; verified tables, seed counts (2 statuses / 4 sources / 10 activity types / 1 template / 13 teams / 315 hymns), the pdf_object_key column, the auth tables, and that the complex sql fragments (dashboard counts, times_played_last_6_months, TEAM_SUMMARY self-join, GROUP_CONCAT) execute and return the expected snake_case keys. The team's real local dev DB was not touched.

Remaining steps for the team (staging → production)

1. Set auth secrets: wrangler secret put BETTER_AUTH_SECRET (use openssl rand -base64 32) and wrangler secret put BETTER_AUTH_URL.
2. Back up production before migrating: npx wrangler d1 export vbc-order-of-service --remote --output ./backups/vbc-order-of-service-YYYYMMDD.sql.
3. Rehearse on staging (or a disposable remote copy): pnpm run db:migrate:remote against the staging binding, deploy, and run the parity/functional checklist in the Validation section.
4. Production cutover — the only new DDL is additive (0009 auth tables; 0004 is inert on the existing DB): pnpm run db:migrate:remote then pnpm run deploy. Keep the previous Worker version ready for rollback and monitor D1 errors/logs.
5. Re-run pnpm db:generate (and add a new migrations/000N_*.sql) whenever the Drizzle schema or the set of Better Auth plugins changes.