Database

ShipKit stores everything in one Cloudflare D1 database, which is SQLite run by Cloudflare, and talks to it through Drizzle ORM. There is no connection string and no database password: the Worker reaches D1 through a binding named DB, declared in wrangler.jsonc. In development, bun run dev gives you a local copy of the same database, kept under .wrangler/state (gitignored).

The trade-off is plain: D1 only exists inside Cloudflare Workers. Moving to another host means moving to another database.

Querying

Server code gets the Drizzle client from getDb():

import { desc, eq } from 'drizzle-orm'
import { getDb } from '@/core/db'
import { creditLedger } from '../schema'

const rows = await getDb()
  .select()
  .from(creditLedger)
  .where(eq(creditLedger.userId, userId))
  .orderBy(desc(creditLedger.createdAt))
  .limit(20)

getDb() creates the client on first use, so the binding is only touched when a request is running, never during the build. Call it from server functions, API routes, event handlers and jobs; never from client code. Pages reach the database through server functions.

Where tables live

Each feature defines its own tables in its own schema.ts: credits in src/features/credits/schema.ts, billing in src/features/billing/schema.ts, and so on. src/core/db/schema.ts is only a barrel that re-exports them:

export * from '@/core/authz/schema'
export * from '@/core/payment/schema'
export * from '@/features/auth/schema'
export * from '@/features/billing/schema'
export * from '@/features/credits/schema'
// ...one line per feature

Drizzle reads this barrel to generate migrations. Deleting a feature means removing its line, after which bun run db:generate writes the migration that drops its tables. A new feature adds a line here; see Adding features.

The auth tables are the exception to editing by hand. src/features/auth/schema.ts is generated from the better-auth config by bun run auth:generate; see Authentication.

Column conventions

The existing tables follow the same conventions, and new ones should too.

A creation timestamp usually defaults in SQL, so a row gets one even when inserted by hand:

createdAt: integer('created_at', { mode: 'timestamp_ms' })
  .default(sql`(cast(unixepoch('subsecond') * 1000 as integer))`)
  .notNull(),

Column names are snake_case in SQL and camelCase in TypeScript. Add an index for the queries a table serves; credit_ledger in src/features/credits/schema.ts has a commented example of an index built for its hottest query.

Changing the schema

Migrations are SQL files in drizzle/, generated by drizzle-kit and applied by wrangler.

# 1. edit a feature's schema.ts, then generate the SQL
bun run db:generate

# 2. apply it to your local database
bun run db:migrate:local

# 3. before deploying code that needs it, apply it to production
bun run db:migrate:remote

Commit the new file in drizzle/ together with drizzle/meta/, which drizzle-kit uses to work out the next migration. CI regenerates the migrations and fails if the schema changed without one being committed.

Production migrations are always run by hand, from your machine. CI never migrates; when it is set up to deploy, it first checks for unapplied migrations and refuses to deploy until you have run bun run db:migrate:remote. Run it before you push, and write migrations that the currently deployed code can survive, because for a short while the old code runs against the new schema. Deploy has the full order of steps.

A data-only migration, such as a backfill, is a hand-written SQL file in drizzle/. Since it does not change the schema, it needs no snapshot change.

Seeding and inspecting local data

bun run db:seed fills the local database with 90 days of fake users, subscriptions, orders, credit ledger rows and audit entries, so the admin console and its charts have something to show:

bun run db:seed            # create or refresh the seed rows
bun run db:seed --reset    # remove them again

Every seed row's id starts with seed-, and seed rows only reference seed users, so --reset never touches data you created yourself. The data is deterministic and its dates are relative to today, so re-running it keeps the charts current. The script also takes --remote, which exists only for a public demo deployment (see More modules); never point it at a real production database, because the fake orders would land in your revenue numbers.

To run SQL against the local database directly, use wrangler:

bunx wrangler d1 execute DB --local \
  --command "UPDATE user SET role='admin' WHERE email='you@example.com'"

Writing without interactive transactions

D1 does not support interactive transactions: db.transaction() throws. You cannot read a value, decide in JavaScript, and write back atomically. ShipKit uses two tools instead.

Several writes that must succeed together: db.batch()

db.batch([...]) sends a list of statements that D1 runs as one unit: all of them apply, or none. Use it when one action writes to several tables. Account deletion in src/features/auth/server/fns.ts is an example:

const db = getDb()
await db.batch([
  db.update(user).set({ deletedAt: now }).where(eq(user.id, me.id)),
  db.delete(session).where(eq(session.userId, me.id)),
  db.delete(apikey).where(eq(apikey.referenceId, me.id)),
])

The statements are fixed before the batch runs, so a later statement cannot depend on what an earlier one read.

A check and a write together: one conditional statement

When a write depends on current data, such as "spend 10 credits only if the balance covers it", put the check inside the statement that writes. A single SQL statement is atomic in D1, so two concurrent requests cannot both pass the check.

spendCredits in src/features/credits/server/credits.ts works this way: its INSERT ... SELECT only produces a row when the summed balance covers the amount, and it reports whether anything was written. Checking the balance first and inserting afterwards would let two parallel requests overdraw the account.

Idempotency with unique keys

Payment webhooks and cron jobs can run more than once, so writes they trigger are keyed. The credit ledger has a unique ref_id, and grants insert with onConflictDoNothing():

const result = await getDb()
  .insert(creditLedger)
  .values({ userId, delta, reason, refId: orderId })
  .onConflictDoNothing()
return result.meta.changes > 0 // false: this order was already credited

Webhook deduplication works the same way, through a unique index on the webhook_events table. Only do follow-up side effects, such as a notification, when the write actually happened.

Testing against a real D1

Tests that must prove what a SQL statement does, above all the payment and credit paths, run against an in-memory D1 with every migration in drizzle/ applied. startD1() in src/test/d1.ts sets it up; src/core/payment/money-path.test.ts is the main example. bun run test runs them with the rest of the unit tests.