Database

The database layer is Integrated with Cloudflare D1 (serverless SQLite) and Drizzle ORM's D1 driver. The repository includes auth and payment schemas plus a generated SQL migration.

Important files

Request-time D1 binding

src/lib/db/index.ts imports env from cloudflare:workers and creates the client with drizzle(env.DB, { schema }). The exported db is a lazy Proxy, so env.DB is first read during a request rather than during module evaluation.

SQLite schema conventions

Both schema files use drizzle-orm/sqlite-core:

• Primary keys and most identifiers are text; application-created payment IDs use crypto.randomUUID() through $defaultFn.
• Booleans use integer(..., { mode: "boolean" }).
• Dates use integer(..., { mode: "timestamp_ms" }) and app-side Date defaults through $defaultFn.
• Constrained string values such as payment status, provider, and plan interval use SQLite text enums for TypeScript inference.
• User-dependent rows reference user.id with onDelete: "cascade".
• Unique constraints and explicit indexes cover session tokens, email, order numbers, user payment lookup, and subscription status.

The auth schema must continue to match the fields expected by Better Auth and its admin plugin. When changing users, sessions, accounts, or verification, update src/lib/auth.ts and the adapter schema in the same review.

Migration workflow

pnpm db:generate

pnpm db:migrate:local

pnpm db:migrate

Drizzle configuration

drizzle.config.ts sets:

export default defineConfig({
	out: "./drizzle",
	schema: "./src/lib/db/schema",
	dialect: "sqlite",
	driver: "d1-http",
	// dbCredentials: Cloudflare account, database, and D1-scoped token
});

The HTTP credentials are only for optional Drizzle Kit operations that contact remote D1. The config loads .env.local; shell exports also work. Migration application uses the Wrangler scripts above.

Transactions and multi-statement writes

D1 does not support the interactive transaction model expected by a typical long-lived SQL connection. Do not build product logic around db.transaction(async (tx) => ...). Prepare independent Drizzle statements and use db.batch([...]) for atomic batched execution, keeping external network calls outside the batch.

Design idempotent writes for retried requests and webhooks. The payment webhook, for example, upserts a subscription on the unique userId key so duplicate deliveries converge.

Production considerations

• Apply and verify migrations in a non-production D1 database first.
• Export or back up production data before a destructive migration; define a restore procedure and retention policy.
• Check current D1 database size, query, and batch limits against your workload in Cloudflare before launch.
• Keep indexes aligned with real query patterns and inspect slow or high-volume paths through Workers observability.
• Be deliberate about D1 read consistency if you enable read replication; a user flow that immediately reads its own write may require a primary/session consistency strategy.
• Treat migration SQL as application code: review it and never edit a migration that has already been applied to a shared environment.

Verify

• pnpm db:generate reports no unexpected schema drift.
• pnpm db:migrate:local succeeds from a fresh local database.
• Registration writes user/session/auth records to local D1.
• A test checkout creates a payment row and a signed webhook updates it.
• The same reviewed migration applies to a staging remote D1 database.

Related pages