Postgres to D1, with the rollback timed first

The brief for the port put one rule in bold: the money path keeps its guarantees. The trouble is that D1 has no interactive transactions. TipBop’s two most dangerous pieces of code, the one that reconciles a tip with Stripe and the one that pays a worker out, both started with a Postgres advisory lock and finished with a commit. On D1 neither exists.

The outage post covers why we moved on 20 October. This one is how. The port started at 10:37 and the cutover finished at 14:42.

A lease row instead of a lock

The brief proposed a compare-and-swap on a version column: read the row, call Stripe, then update only if the version has not moved. We wrote the concurrency tests first and threw that design out. A version check cannot span a Stripe call. Two reconcilers can both read version 7, both ask Stripe for a refund, and only then find out that one of them lost.

What we built is a lease: one table, one row per thing being worked on, holding a random token and an expiry. Taking the lease is a single statement that either inserts the row or takes over an expired one, and tells you whether you got it.

/* Take the lease for one tip or one worker. Illustrative names.
   ?1 = key, ?2 = a fresh random token, ?3 = now in seconds. */
INSERT INTO lease (key, token, expires_at)
VALUES (?1, ?2, ?3 + 120)
ON CONFLICT (key) DO UPDATE
  SET token = excluded.token,
      expires_at = excluded.expires_at
  WHERE lease.expires_at < ?3   /* only if the last holder's lease ran out */
RETURNING token;
/* A row back with our token: we hold it.
   No row: someone else does. Wait and retry. */

/* Give it back, but only if it is still ours. */
DELETE FROM lease WHERE key = ?1 AND token = ?2;

The lease lasts 120 seconds and a caller waits up to 20 for it before giving up with a busy error. It wraps exactly the two sections that call Stripe. Everything else that used to open a transaction became one guarded statement or one atomic D1 batch.

The obvious objection is the expiry. What if a section runs past 120 seconds and someone else takes over halfway through? Every Stripe call inside carries an idempotency key, so the worst an expired lease can do is repeat a call Stripe has already de-duplicated. That is the property we actually needed: not “nobody else can ever enter”, but “entering twice cannot move money twice”.

The proof is a test that runs three reconcilers against one payment and expects one refund, and two cash-outs against one worker and expects one payout. We checked the test itself by deleting the lease and watching it fail.

Keeping 3,600 tests honest

The rest of the port was about changing as little as possible that a test could not see.

One schema, not 37 migrations. Postgres had 37 migrations of history. D1 got a single baseline, generated from a template whose declared column types (timestamps as text, booleans as integers, JSON as text) are also the source for the code that converts rows. The obvious risk was a rarely taken branch with a Postgres-only query in it, so a test runs EXPLAIN on every SQL template in the codebase against the new schema. A Postgres-ism anywhere fails the gate, not a customer.

Handlers untouched. Workers speak fetch Request and Response. TipBop’s roughly 100 route handlers spoke Node’s req and res. Rather than rewrite them, we wrote a small Node-shaped facade over the fetch objects. The handlers and their route tests stayed byte-identical, so the existing 3,600-test suite was still proving the code that would run.

A database per test file. Integration tests used to share one Postgres. They moved to a fresh Miniflare D1 per file, which runs in the normal test command and needs no database service in CI.

Replaying a day before trusting it

Tests prove the code. They do not prove the data, the configuration or the edge. For that we loaded staging with a copy of production taken at about 12:30 that day: 23 tables, each checked against a fingerprint, plus a money checksum over every tip and payout. Staging has no email, no real push and test-mode Stripe, so nothing in that copy could reach a real person.

Then we replayed 24 hours of real production GET requests against it and compared every response.

The nine are worth reading because none is a bug in the port. Two were health endpoints that report live state. Five came from staging being staging: its own origin, a test publishable key, no web push key, no passkey domain, a different build id. One was the privacy notice, rewritten for Cloudflare. One was a redirect that came back without a content type. The tip pages, handle pages and photos built from the copied rows were identical.

Cutover and rollback are the same move

The design rule we cared about most: never two writers. Cloud Run kept running untouched. The production Worker was deployed parked, answering only a canary hostname, with its payment sweepers switched off, because two payout sweepers on one database is the failure this whole exercise avoids.

The cutover script runs preflight, maintenance mode, the host move, a final copy with checksums, photos, a real-data check on the canary, then lifts maintenance and verifies. Any failure up to the canary check swaps the hosts back and stops. We found one surprise on the canary host rather than a real one: Cloudflare refuses to repoint a hostname that is already a Worker’s custom domain unless the call says override_existing_origin, which is exactly what both cutover and rollback do.

We rehearsed the full undo on staging. Every table went back from D1 into a scratch Postgres built from production’s schema, each fingerprint-checked, money checksum equal. While the hosts swapped back and forth, a probe hit the canary every 150 ms, 381 times, with no connection errors in either direction.

Attempt one, at 13:52, rolled itself back in about three seconds: five redirect checks tested the real hostnames while they were deliberately in maintenance. We fixed the check and ran it again. Attempt two moved all ten hostnames in 230 seconds with every checksum matching.

Backups we have restored

D1 keeps 30 days of point-in-time recovery, but inside the same Cloudflare account. So the Worker also takes its own nightly dump: one consistent D1 batch, compressed and written to storage outside Cloudflare, then restored into a scratch D1 and checked with per-table counts and the money checksum. A heartbeat records that the restore passed. We did not wait for the first 03:15 run; we triggered it by hand on staging before the cutover. A backup we have restored is the only kind we count.

The database was created with its jurisdiction set to the EU, because it holds personal data and that setting cannot be changed after creation. By that evening the Node server, the 37 Postgres migrations and the Postgres driver were deleted from the repo. The old code lives on a git tag, and that is the only place we expect to need it.