Database Migrations

How VelaPay manages schema evolution for Cloudflare D1, the constraints D1 imposes, and the migration path to Postgres at PMF.

Inventory

Database	Repo	Schema source	Migration path
vela-dashboard-v19-devnet (D1, canonical devnet)	vela-dashboard	src/server/schema.ts + worker/src/db/schema.ts	canonical migrations 0000 through 0016
vela-dashboard-dev (D1, legacy dev)	rollback source only	legacy schema	do not migrate in place
vela-dashboard-staging (D1, staging)	same	same	same
vela-dashboard-prod (D1, prod)	same	same	same
(future) vela-dashboard-pg (Postgres, PMF)	same	same Drizzle schema, dialect=pg	TBD

vela-portal, vela-checkout, vela-admin, and vela-widget either go through the dashboard worker via service binding or use Cloudflare KV (vela-portal sessions). None hold their own SQL schema today.

The v1.9 rule is rebuild-and-copy, not replay-on-drift: create a fresh D1 from the complete migration chain, inventory and export the frozen legacy database, import in foreign-key order, then verify per-table counts, checksums, foreign keys, indexes, triggers, merchant ownership, and refund reservations. Keep the legacy database unchanged for rollback.

D1 Constraints That Shape Every Migration

No interactive transactions

D1 does not support BEGIN; ... COMMIT; transactions across multiple statements. Drizzle and Better Auth both adapt by using D1's batch() API, which executes a list of statements atomically — all or nothing — but you must construct the batch up front.

Implications:

• Multi-row writes that must be atomic (invoice + line items, mandate + initial billing event) must be a single db.batch([...]).
• Payment confirmation must batch Payment Intent, invoice, customer wallet, receipt, audit, and outbox writes.
• Refund confirmation must batch refund, payment, invoice, documents, audit, and outbox writes.
• db.transaction(async tx => …) from Drizzle does not work on D1. Use db.batch() instead.
• Migrations run as a series of batch() calls automatically by wrangler d1 migrations apply.

10 GB per-database limit (paid plan)

Plan partitioning before a single D1 hits the limit. Today the dashboard is well below; at PMF this is the trigger to migrate to Postgres.

Drizzle dialect = SQLite

D1 is SQLite-compatible. Some Postgres-only types (UUID, JSON ops, arrays) need shimming. The schema deliberately uses primitives that translate cleanly to Postgres: text for IDs, text for JSON blobs (with parsing at read time), integer for booleans.

Authoring a Migration

cd vela-dashboard/worker

# 1. Edit schema.ts (the Drizzle schema source of truth)
$EDITOR src/server/schema.ts

# 2. Generate the migration SQL
bunx drizzle-kit generate

# 3. Inspect the generated SQL — Drizzle is conservative but always read it
$EDITOR drizzle/migrations/<latest>.sql

# 4. Apply locally
bunx wrangler d1 migrations apply vela-dashboard-dev --local

# 5. Run the dashboard worker locally and exercise the affected paths
cd .. && bun run cf:dev

# 6. Test
bun test                                  # unit
bunx playwright test -c playwright.phaseNN.config.ts   # E2E for the affected feature

# 7. When ready, ship through the release pipeline (it will apply to staging then prod)

The generated migrations are committed to git and replayable.

Migration Naming

Drizzle's auto-generated names are kept (0009_webhook_dispatch.sql, 0010_chatty_kafka.sql), with a human override when the auto-name is unhelpful (e.g. 0007_phase30_invoicing.sql). The numeric prefix is the source of truth; do not renumber.

Atomicity Recipes

Invoice + line items

await db.batch([
  db.insert(invoices).values({ id, merchantId, mandateId, amount, ... }),
  ...lineItems.map(item =>
    db.insert(invoiceLineItems).values({ invoiceId: id, ...item })
  ),
  db.insert(billingEvents).values({ type: "invoice.created", invoiceId: id, ... }),
]);

Either every row commits or none — D1 batch atomicity covers all of them.

Webhook dispatch + dedup record

await db.batch([
  db.insert(webhookDispatches).values({ id, idempotencyKey, payload, ... }),
  db.insert(idempotencyKeys).values({ key: idempotencyKey, dispatchId: id }),
]);

If the idempotency key collides (UNIQUE constraint), the entire batch rolls back — no orphaned dispatch row. This is how the dispatcher avoids double-emitting.

Rollback Safety

D1 has no native point-in-time recovery. Rollback strategy:

1. Forward-only migrations. Never write a DROP TABLE migration unless you have a backup AND a test run on staging.
2. Pre-migration backup for any schema change touching mandates, plans, billing_events, webhook_dispatch:

   bunx wrangler d1 export vela-dashboard-prod --output backup-$(date +%Y%m%d).sql

3. Tested-on-staging gate. Every migration runs against vela-dashboard-staging first and the synthetic worker must stay green for at least one hour before promotion to prod.
4. Reverse migration ready. For destructive changes, write the inverse migration before applying the destructive one. Never trust that you can hand-write a rollback under pressure.

Schema Audit Queries

Run periodically (or after a suspect migration):

-- Orphaned line items (invoice gone)
SELECT * FROM invoice_line_items
WHERE invoice_id NOT IN (SELECT id FROM invoices);

-- Mandates without a corresponding plan
SELECT m.* FROM mandates m
LEFT JOIN plans p ON p.id = m.plan_id
WHERE p.id IS NULL;

-- Dispatch rows with no idempotency key
SELECT id FROM webhook_dispatches
WHERE idempotency_key IS NULL OR idempotency_key = '';

If any of these return rows, the schema or a migration introduced a constraint gap. Open an investigation before letting more writes happen.

Postgres Migration Path (PMF)

Drizzle's dialect abstraction means moving from D1 (SQLite) to Postgres mostly comes down to:

1. Spin up Postgres (Cloudflare Hyperdrive in front of Neon, RDS, or a managed Postgres).
2. Add dialect: "postgres" Drizzle config alongside the existing SQLite config.
3. Re-emit migrations under the Postgres dialect. Most translate cleanly; SQLite-specific quirks (TEXT-as-JSON, INTEGER-as-bool) are the only friction.
4. Backfill from D1 export → Postgres bulk import.
5. Cut over reads to Postgres while writing to both for a soak window.
6. Cut over writes.
7. Decommission D1.

This is not a near-term plan — D1 has plenty of headroom. Document choices made today (no Postgres-only types, no interactive-transaction APIs) so the migration stays cheap.

Related

• Drizzle docs (D1): https://orm.drizzle.team/docs/connect-cloudflare-d1
• D1 batch atomicity: https://developers.cloudflare.com/d1/sql-api/d1-client-api/
• Cloudflare Infrastructure →
• Verification Matrix →