Exactly-Once Publishing on Cloudflare D1 Without Transactions

I run a small daily game, Costcodle, where everyone guesses the price of the same Costco item each day. Just after midnight UTC, a scheduled job publishes the answer page of every puzzle that has finished. It is a tiny job, and for a while it was the part of the system I trusted least.

The reason was a migration. I was merging two Cloudflare Workers, one for the website and one for scheduled jobs, into a single Worker. Until the old one was deleted, both would fire the same cron string at the same moment, against the same database. I wanted three guarantees to survive that overlap:

1. Every answer page is published exactly once, and the site's publication revision goes up exactly once per page.
2. Once a page is published, no edge location serves the old version again.
3. None of this depends on purging a cache.

The database is Cloudflare D1, and that is where it gets interesting.

Why D1 Makes "Exactly Once" Harder

D1 gives you batch(). The documentation is clear about what it does: "Batched statements are SQL transactions," and if a statement in the sequence fails, D1 "aborts or rolls back the entire sequence" (D1 Database API).

That is a strong guarantee about errors. It says nothing about statements that succeed and do nothing. An UPDATE whose WHERE clause matches zero rows is not an error, so the batch carries on and commits whatever the other statements did.

What the Worker binding does not give you is an interactive transaction: open it, read a row, decide in your own code, then write, all under one lock. So the natural pattern, "check that I still hold the lease, then write," has a gap in it. Between the read that checks and the batch that writes, another job can take over. If the batch then runs three statements and only some of their conditions still hold, you get exactly the state you were trying to avoid: an item marked published with no page, or a page with no revision bump.

The fix I ended up with has two parts. A lease with a fencing token decides who may publish. A guard repeated inside every statement of the final batch makes sure a writer that lost the lease changes nothing at all.

Leases With Fencing Tokens, in a Table

Leases live in a job_leases table: a key such as publication:1121, an owner id, an expiry and a fence counter. Taking a lease is a single upsert (simplified from the real repository):

const rows = await db.insert(jobLeases).values({ key, ownerId, fence: 1, expiresAt, updatedAt: now, }).onConflictDoUpdate({ target: jobLeases.key, set: { ownerId, fence: sql`${jobLeases.fence} + 1`, expiresAt, updatedAt: now }, // Only an expired lease can change hands. setWhere: sql`${jobLeases.expiresAt} <= ${now}`, }).returning(); return rows[0] ?? null; // null: someone else holds it 

If nobody holds the key, the insert creates it with fence 1. If the current lease has expired, the conflict branch hands it to the new owner and increments the fence. If the lease is still live, the setWhere clause matches nothing, no row comes back, and the caller skips that item for now. Each run gets a fresh random owner id, so two Workers firing the same cron look like two independent owners competing for the same keys. My leases last 180 seconds.

A lease alone is not enough, and the reason is well known. Martin Kleppmann's How to do distributed locking puts it plainly: "you need to include a fencing token with every write request to the storage service." A holder can stall long enough for its lease to expire, someone else takes over, and then the first holder wakes up and writes anyway. HackerNoon has two good deep dives on this failure, The Fencing Gap: Why Your Distributed Lock Isn't Safe and Building Distributed Leases With Consensus, Heartbeats, and Fencing Tokens. Both arrive at the same rule: the storage layer has to check the token itself.

In my case the storage layer is the same SQLite database that holds the lease, which makes that check unusually cheap. It can be part of the write.

A Guard in Every Statement

Every write that depends on the lease carries this condition:

EXISTS (SELECT 1 FROM job_leases WHERE key = ? AND owner_id = ? AND fence = ? AND expires_at > ?) 

If another job has taken over, the fence no longer matches, and the condition is false inside the very statement that would have written.

The final publish batch takes this further. Its first statement inserts or updates the page with INSERT ... SELECT ... WHERE <guard>, and the guard re-checks everything the job verified earlier, in SQL: the lease, the ledger item's state, the question bank version, that the image is ready, and that the page is still at the content version the job expected (plain optimistic concurrency). That statement also writes a random commit_token. Every statement after it is conditioned on that token having landed:

// Simplified from the publish commit. const token = crypto.randomUUID(); const changed = sql`EXISTS (SELECT 1 FROM answer_posts WHERE puzzle_id = ${id} AND commit_token = ${token})`; const result = await db.batch([ // 1. The page itself: written only when the guard holds. db.insert(answerPosts).select(sql`SELECT ${id}, ..., ${token} WHERE ${guard}`) .onConflictDoUpdate({ target: answerPosts.puzzleId, set: { ...page, commitToken: token }, setWhere: sql`${answerPosts.contentVersion} = ${expectedVersion}` }), // 2. The ledger item, only if statement 1 wrote this token. db.update(publicationItems).set({ state: "published" }) .where(and(eq(publicationItems.puzzleId, id), changed)), // 3. The site revision, under the same condition. db.update(siteState).set({ publicationRevision: sql`${siteState.publicationRevision} + 1` }) .where(and(eq(siteState.id, 1), changed)), // 4. Read back what happened. db.select().from(answerPosts).where(eq(answerPosts.puzzleId, id)), ]); 

If the guard is false, statement 1 writes nothing, so the token never lands, so statements 2 and 3 match zero rows as well. The zero propagates through the whole batch. The job reads the page back, sees a different token, and reports lease_lost or conflict without having changed anything.

The same guard enforces two one-way rules I care about. A page that search engines may index never goes back to noindex, and a page that has its product photo never loses it. Both are expressed as conditions on the existing row, so a stale or confused writer cannot undo them.

There is a test for the exact gap described earlier. It lets the job finish its read batch, then has a second repository take over the lease before the final batch runs. The call returns lease_lost, and the test then checks the database: no page row, a publication revision still at 0, and the ledger item still marked as processing.

A Revision Number Instead of a Purge

Statement 3 above is the second half of the design. Every change to published content, a new page, an updated page or a withdrawal, bumps one global publicationRevision in the same atomic batch that made the change. A rerun that changes nothing public leaves it alone.

The edge cache uses that number as part of every key for content that depends on publications:

export function cacheKey(scope: CacheScope, ...parts: (string | number)[]): Request { const path = [scope.environment, scope.origin, scope.buildVersion, `c${CONTRACT_VERSION}`, ...parts] .map((part) => encodeURIComponent(String(part))).join("/"); return new Request(`https://cache.costcodle.internal/${path}`); } // For an answer page: cacheKey(scope, utcDay, "content", `r${revision}`, "/answers/1121") 

Each request reads the current revision from the primary database first and only then looks in the cache. After a publication, every request builds a key that has never been stored, misses, renders the fresh page and stores it under the new key. The old entries are still there. Nothing will ever ask for them again, and they age out on their own.

This matters more on Workers than it might elsewhere. The Cache API documentation notes that "the contents of the cache do not replicate outside of the originating data center," and that cache.delete "only purges content of the cache in the data center that the Worker was invoked." A purge from inside a Worker is a local operation. Changing the key is global by construction.

A few rules keep this honest:

• Browsers and shared caches only ever see Cache-Control: public, max-age=0, must-revalidate. The longer lifetime applies to the internal Cache API copy.
• That internal copy lives at most 300 seconds for content pages and 60 seconds for the home page.
• If the revision cannot be read, the cache layer steps aside and the page reports the failure itself. A database fault never turns into a stale 200.

The general pattern of versioned cache keys is covered well in The Many Layers of Caching. What surprised me was the one case it did not cover.

The change that is not a publication

In my game, yesterday's puzzle stays playable for a 10 minute grace window after midnight UTC, and its answer must not become public until that window ends. At 00:10 UTC, yesterday's answer becomes publishable. No publication has happened at that instant, so the revision has not changed.

If a "not published yet" response had been cached at 00:09 with a 300 second lifetime, it would keep answering until 00:14 under a perfectly valid key. So content entries get a lifetime of the smaller of 300 seconds and the time left until 00:10 UTC. If your data has any moment where visibility changes without a write, a revision-keyed cache needs the same treatment: find that moment and make sure no entry lives across it.

The price of the whole scheme is one database read per cacheable request, to learn the revision. For a site of this size that is a good trade.

The Same Idea in One Statement

The pattern of putting every condition inside the write also works without a lease. After each game, the client can report the result for the day's shared stats. The rules are: one row per game session, at most 50,000 rows per puzzle, and a dense slot number for each row. All three are handled by one statement:

INSERT INTO daily_results (puzzle_id, session_id, slot, outcome, guesses_used, created_at) SELECT ?, ?, next.slot, ?, ?, ? FROM (SELECT coalesce((SELECT max(slot) FROM daily_results WHERE puzzle_id = ?), 0) + 1 AS slot) AS next WHERE next.slot <= 50000 ON CONFLICT (puzzle_id, session_id) DO NOTHING 

SQLite runs a single statement atomically and D1 serializes writes to a database, so two concurrent reports cannot take the same slot, a duplicate session inserts nothing, and a full puzzle inserts nothing. There is no window between the check and the write because there is no separate check.

One detail took a test to get right. The bare max(slot) sits alone in its own scalar subquery. SQLite's MIN/MAX optimization can answer a single MAX() over the leading column of an index "by doing a single index lookup rather than by scanning the entire table," and wrapping it in a larger expression loses that. A test seeds 49,999 rows for one puzzle and asserts that the next insert reads fewer than 20 rows. For the ON CONFLICT clauses used throughout, the SQLite UPSERT documentation is the reference.

Proving It on Real Infrastructure

Unit tests cover each piece: concurrent lease claims, two concurrent compare-and-set commits where only one wins, and the takeover between preflight and commit. The test I trust most runs on the real stack. An integration run starts the actual Worker with D1 and R2 and fires the publish cron twice at the same instant, the way the old and new Workers would during the merge. Then it fires the hourly compensation job twice at once, several rounds in a row.

The result: one daily quota row, six pages each published exactly once at content version 1, a publication revision of exactly 6, and seven source images each downloaded and stored exactly once. Two more simultaneous compensation runs afterwards find nothing to do and change nothing.

You can see the pages this pipeline publishes in the Costcodle answers list.

What I Would Reuse Anywhere

• Put the condition inside the write. "Check, then write" across two round trips is a race, even with a lease.
• Chain a batch with a token. When your atomic unit only rolls back on errors, make every later statement depend on a value only the first statement could have written. A zero then propagates through the batch.
• Let the storage check the fence. A fencing token that only the client looks at protects nothing.
• Version the key instead of purging. A monotonically increasing revision, bumped in the same transaction as the content, makes invalidation instant everywhere and needs no purge API.
• Hunt for changes that are not writes. Time-based visibility, embargoes and grace windows change what a URL should return without touching the revision. Cap cache lifetimes at those instants.

How do you get exactly-once behavior out of a store that only offers batches? I would like to hear what other people have built on D1 or plain SQLite.

Costcodle is an independent fan game. It is not affiliated with, endorsed by, or sponsored by Costco Wholesale Corporation.