Storage Without Transactions

This is the post where the platform stopped being a nicer Lambda and started demanding real distributed-systems thinking. The first three posts in this series were about unlearning habits. This one is about the day the habits fought back—because for fifteen years, my answer to “what if this multi-step write gets interrupted” was BEGIN … COMMIT, and on this platform that answer does not exist.

This is part 4 of 10 in the RORBT field notes—dispatches from building a governance platform on Cloudflare. The whole series lives on the RORBT case study.

The system has two storage engines with no transaction spanning them:

• D1—SQLite at the edge. Governance (proposals, votes, the hash-chained transparency log), messages, notifications, rate-limit counters.
• R2—object storage. Orbit records, encrypted member envelopes, assemblies. Optimistic concurrency via etag / onlyIf, no transactions at all.

Coming from Postgres, where I’d wrap anything nontrivial in BEGIN … COMMIT without thinking—genuinely without thinking, the way you don’t think about breathing—this reshaped the entire data layer. Not decorated it. Reshaped it.

What atomicity you actually get

D1 batch() is atomic. An array of prepared statements, all-or-nothing. Real, and I use it. But it carries a constraint that quietly eliminates most of the places you’d want it: no data dependencies between statements. You can’t use the result of statement 1 in statement 2.

I initially believed this would fix several places. It fixed exactly one. That ratio—the gap between “this looks like a transaction” and “this behaves like a transaction”—is the honest summary of the whole post.

Why the others failed:

// castVote—NOT batchable.
// Inserts the vote, then RE-READS to recompute tallies from source rows.
// The recompute is deliberate (drift-proof under races), and it's a data
// dependency, so no batch.
await database.insert(proposalVotes).values({ … });
const tally = await database.select({ vote: …, c: count() })
  .from(proposalVotes).where(eq(proposalVotes.proposalId, id)).groupBy(…);

// appendLog—CAN NEVER batch.
// The transparency log is a hash chain: each entry hashes the previous tip.
// You must read the tip, compute, then insert. That's inherently sequential.
const tip = await database.select({ currentHash: … }).orderBy(desc(seq)).limit(1);
const currentHash = await sha256Hex([id, actionType, tip.currentHash, ts, desc].join('|'));
await database.insert(transparencyLog).values({ …, previousHash: tip.currentHash, currentHash });

The one place it worked was account erasure—four independent deletes:

const [tokens, notes] = await db.batch([
  db.delete(pushTokens).where(eq(pushTokens.userId, id)).returning({ token: … }),
  db.delete(notifications).where(eq(notifications.userId, id)).returning({ id: … }),
  db.delete(messageState).where(eq(messageState.userId, id)),
  db.delete(authUser).where(eq(authUser.id, id)),   // cascades sessions
]);

Partial erasure is the worst available outcome—the account row gone but the notifications left behind, with nobody remaining to ask for a fix. That’s the one call site where all-or-nothing with no dependencies is exactly the shape of the problem. Worth the batch.

R2 gives you compare-and-set, not transactions. onlyIf: { etagMatches } on put, with a retry loop. That’s enough for single-object safety and nothing more, and pretending otherwise is how the rest of this post happened.

The durability hole I found in my own code

This is the most instructive bug of the day, and I want to walk through it slowly because I wrote it, I reviewed it, and I was proud of it.

The proposal engine resolves lazily—every read runs open proposals through maybeResolve, which flips status when the math settles. To ensure exactly-once execution under concurrent readers, it used a guarded update:

const flipped = await database
  .update(proposals)
  .set({ status: outcome, resolvedAt })
  .where(and(eq(proposals.id, p.id), eq(proposals.status, 'ACTIVE')))  // ← guard
  .returning({ id: proposals.id });

if (flipped.length === 0) return resolved;  // lost the race; winner owns the rest

// winner executes side effects: assign the role, suspend the member, amend the MVOS…

Correct for concurrency. Fatal for crashes. That sentence took me an embarrassingly long time to be able to write.

If the winning caller died between the status flip and the execution—a CPU limit, an isolate eviction, a thrown error in a downstream R2 write—then:

1. The proposal is PASSED and resolved_at is set.
2. The effects never happened. No role assigned, no member promoted.
3. No future reader will ever retry, because the guard requires status = 'ACTIVE' and it no longer is.

Members see a vote that passed and did nothing. Permanently. There is no error, no alert, no queue with a stuck message—the system believes it’s finished.

The worst failure state isn’t the system that knows it’s broken. It’s the system that believes it’s finished.

sequenceDiagram
  participant R as Reader
  participant D as D1
  participant S as R2 / stores
  R->>D: UPDATE … WHERE status='ACTIVE' RETURNING
  D-->>R: 1 row (won the race)
  Note over R: 💥 crash / CPU limit / eviction
  R--xS: assignRole() never runs
  Note over D: status=PASSED, resolved_at set,<br/>effects never applied
  Note over R,D: Every later read short-circuits<br/>on status !== 'ACTIVE'.<br/>Stranded forever.

The fix: separate decided from carried out

ALTER TABLE proposals ADD COLUMN executed_at integer;

• The flip sets status and resolved_at—not executed_at.
• Execution runs, then claims executed_at under its own guard.
• Any later read seeing status='PASSED' AND executed_at IS NULL re-drives.

async function maybeResolve(env, database, p) {
  // A PASSED proposal that never finished executing is unfinished work, not
  // history. The guard below stops the original caller running twice; it used
  // to also stop anyone finishing what a crashed caller started.
  if (p.status === 'PASSED' && p.executedAt === null) {
    return carryOut(env, database, p, 'PASSED');
  }
  if (p.status !== 'ACTIVE') return p;
  …
}

Two details make this safe rather than merely different.

Execution must be idempotent, and it already was—assignRole, promoteToFull, setRosterMemberState, and updateOrbit are all no-ops when already applied. Re-driving costs nothing. I’d like to claim this was foresight; it was closer to habit, and it’s the habit that saved me.

The receipt is claimed separately, so concurrent re-drives apply the same idempotent effects but exactly one of them writes to the log:

const claimed = await database.update(proposals)
  .set({ executedAt })
  .where(and(eq(proposals.id, p.id), isNull(proposals.executedAt)))
  .returning({ id: proposals.id });
if (claimed.length === 0) return settled;   // someone else logged it
await appendLog(database, { … });

FAILED is stamped in the same statement as the flip—it has no side effects, so there’s nothing to re-drive.

And the migration had to be honest about what it couldn’t know:

-- Backfill: every existing resolved row is assumed executed, because until now
-- resolution and execution were the same step and there is no way to tell them
-- apart retrospectively. Only rows written after this migration can be trusted
-- to distinguish the two.
UPDATE proposals SET executed_at = resolved_at WHERE resolved_at IS NOT NULL;

I like that comment more than most code I’ve written. It admits, in the schema’s permanent record, that the past is unauditable—which is the correct thing to admit rather than paper over, particularly in a system whose entire purpose is auditable governance.

The saga: 17 writes across two stores

Splitting an orbit at its 25-member cap does roughly this:

1. Spawn a new orbit (R2 write)
2. Trim the original’s roster (R2 write, etag-guarded)
3. Repoint ~12 member records (R2 write each)
4. Create an assembly + add the second orbit (2 R2 writes)
5. Append receipts to two transparency chains (D1 writes)

No transaction covers that. Nothing could cover that—it spans two storage engines that have never heard of each other. A crash anywhere leaves a partial state, and here’s the part that took me a while to see: the most dangerous thing isn’t the partial write. It’s what a naive retry would do to it.

planSplit is a deterministic function of the roster. Same roster, same split—that’s what makes it auditable. But after step 2, the roster has changed. A resume that re-planned would deal members into different halves than the ones already moved, stranding people between two orbits. No error. No exception. Just a group quietly split wrong.

Freeze the plan before you touch the world, because the world you’d re-plan against is the one you already changed.

So the plan is frozen in a journal before anything is touched:

export const operations = sqliteTable('operations', {
  id: text('id').primaryKey(),
  orbitId: text('orbit_id').notNull(),
  kind: text('kind').notNull(),                 // 'split'
  status: text('status').notNull().default('pending'),
  step: text('step'),                           // cursor: last completed step
  payloadJson: text('payload_json'),            // the FROZEN plan
  attempts: integer('attempts').notNull().default(0),
  lastError: text('last_error'),
  …
}, (t) => [
  // At most one live operation of a kind per orbit. Two readers that both
  // decide an orbit is due to split race here, and the loser's INSERT fails
  // instead of spawning a second orbit.
  uniqueIndex('operations_one_pending_per_orbit')
    .on(t.orbitId, t.kind).where(sql`status = 'pending'`),
]);

That partial unique index is the concurrency control—a database constraint doing the job a distributed lock would do on AWS. It costs nothing, it can’t be forgotten, and it can’t be misconfigured at 2 a.m., which is more than I can say for any lock service I’ve ever operated.

Each step is guarded by the cursor:

const done = (step: SplitStep) =>
  SPLIT_STEPS.indexOf(step) <= SPLIT_STEPS.indexOf(op.step as SplitStep);

if (!done('trimmed')) {
  await saveOrbit(env, { ...fresh.orbit, roster: stay, … }, fresh.etag);
  op = await advance(env, op, 'trimmed');
}

stateDiagram-v2
  [*] --> pending: beginOperation<br/>(plan frozen)
  pending --> spawned: new orbit written
  spawned --> trimmed: original roster cut
  trimmed --> repointed: member records moved
  repointed --> federated: assembly created
  federated --> logged: receipts appended
  logged --> done: completeOperation
  spawned --> pending: crash → resume from cursor
  trimmed --> pending: crash → resume from cursor
  repointed --> pending: crash → resume from cursor
  pending --> failed: attempts ≥ 5
  done --> [*]
  failed --> [*]: needs a human

After 5 attempts the row parks as failed so a permanently broken operation stops being retried on every read—and becomes exactly the row an operations dashboard should surface. A stuck saga is a page waiting to be written, not an infinite loop waiting to be discovered.

A test caught a real gap

I wrote a failure test assuming a bogus spawnedId would break the resume. Instead it completed silently—nothing after the spawn step verified the orbit existed, so a corrupted payload would have repointed members at a nonexistent orbit and marked itself done. The fix:

if (done('spawned')) {
  // Resuming past the spawn: the orbit the earlier attempt created must still
  // be there. Without this a journal row carrying a bad id would sail through
  // the remaining steps and mark itself done.
  if (!(await loadOrbit(env, spawnedId))) {
    throw new Error(`split resume: spawned orbit ${spawnedId} is missing`);
  }
}

I expected the test to confirm the code was right. It confirmed the code was wrong, which is the better outcome and the only one worth paying for. Writing the test found the bug. That’s the entire argument for post 06.

The layered defence

Three mechanisms, each covering exactly what the others don’t:

Mechanism	Protects against	Doesn’t cover
batch()	Partial D1 writes	Anything involving R2, or data dependencies
Idempotency + journal	Crashes mid-operation	Two callers starting at once
Durable Object (post 05)	Concurrency—interleaving	Crashes; cross-store atomicity

The instinct to reach for one silver bullet is wrong, and I had it. A DO doesn’t replace the journal—its transactional storage covers its own storage, not R2 and D1. You need both, and the table is the argument: read the third column.

What I’d tell a Postgres native

Assume every multi-write operation will be interrupted halfway, because without transactions it will be. Design the resume path first, not after the first incident. The resume path is the design; the happy path is the special case where it wasn’t needed.

Idempotency is not a nice property, it’s the load-bearing one. Every step in that saga is safe to repeat, which is the only reason resuming is possible at all. Everything else in this post is scaffolding around that fact.

Freeze inputs that later steps depend on. The single subtlest bug here was re-planning against mutated state—and it would have silently split a group wrong rather than erroring. Silent and wrong beats loud and wrong for worst possible failure, every time.

A guarded update that prevents double-execution also prevents recovery. That symmetry is easy to miss, and it’s how I shipped a permanent-data-loss bug without noticing—while feeling clever about the guard.

The transaction was never the feature. The feature was not having to think about what happens halfway through—and that bill doesn’t disappear, it just arrives without an envelope.

---

Previous: Pages Functions vs a Cluster of Microservices · Next: Durable Objects Are the Actor Model Wearing a Database Costume