SQLite on the edge

I wanted a concrete answer to a question I kept guessing at: when do you put SQLite in D1, and when do you put it in a Durable Object?

The excuse is a shared expense ledger. Small groups, low write volume, everyone reads the same rows. That shape makes the tradeoff visible instead of theoretical.

The split I settled on

D1 holds the durable record. A Durable Object per ledger holds the hot working set and serialises writes.

export class Ledger {
	constructor(state: DurableObjectState, env: Env) {
		this.state = state;
		this.env = env;
	}
 
	async addExpense(expense: Expense) {
		await this.state.blockConcurrencyWhile(async () => {
			const current = (await this.state.storage.get<Expense[]>("pending")) ?? [];
			await this.state.storage.put("pending", [...current, expense]);
		});
	}
}

The rule I am testing: anything that needs a read-modify-write under concurrency goes in the object. Anything that is append-only or read-mostly goes in D1.

Still unresolved

Compaction. The object accumulates pending writes and flushes them to D1 on an alarm, but I have not worked out what happens when a flush fails halfway. Currently the whole batch is retried, which is correct but can double-apply if the failure was after the commit.

I am fairly sure the answer is an idempotency key per batch. I have not convinced myself it is worth the complexity at this scale.

Status

Running for two weeks against my own expenses. It has not lost a row, which is a low bar and the only one I can currently claim.