SQLite and D1

npm install tollstile @tollstile/sqlite

• Bring your own driver. You pass two functions, execute and transaction. No runtime dependencies.
• Same behavior as memoryLedger(). Both run one conformance suite, including reservation accounting across reserved → settling → settled → refunded and unknown.
• Batch transactions only. The ledger never reads between the statements of a transaction, so D1, whose transactions are batches, is supported by design.

Quick start with node:sqlite

import { DatabaseSync } from "node:sqlite";
import { createTollstile, testRail } from "tollstile";
import { sqliteLedger, sqliteSchema } from "@tollstile/sqlite";

const db = new DatabaseSync("ledger.db");
db.exec("PRAGMA journal_mode = WAL; PRAGMA busy_timeout = 5000;");
db.exec(sqliteSchema); // once, or through your migration tool

const all = (sql: string, params: readonly (string | number | null)[]) => db.prepare(sql).all(...params);

const ledger = sqliteLedger({
  execute: all,
  transaction: (statements) => {
    db.exec("BEGIN IMMEDIATE");
    try {
      const results = statements.map(({ sql, params }) => all(sql, params));
      db.exec("COMMIT");
      return results;
    } catch (error) {
      db.exec("ROLLBACK");
      throw error;
    }
  },
});

export const toll = createTollstile({ rails: [testRail()], ledger });

Applying the schema

sqliteSchema is a string of plain DDL: four STRICT tables, their indexes, and a header comment. It needs SQLite 3.38 or later. Every statement uses IF NOT EXISTS.

• At startup: db.exec(sqliteSchema).

• With a migration tool, including D1: print the DDL once and commit it as a migration.

  npx wrangler d1 migrations create DB tollstile
  node --input-type=module -e "import('@tollstile/sqlite').then((m) => console.log(m.sqliteSchema))" > migrations/0001_tollstile.sql
  npx wrangler d1 migrations apply DB

• Another table prefix: sqliteSchema.replaceAll("tollstile_", "billing_") is the DDL for tablePrefix: "billing_".

Schema changes in later releases ship as separate, additive migrations. The ledger never alters tables itself.

Options

The driver contract

execute(sql, params) runs one statement with anonymous ? parameters and returns its rows as objects keyed by column name, synchronously or as a promise.

transaction(statements) runs { sql, params } statements in order inside one write transaction and returns each statement's rows, in order. All take effect or none do.

Parameters are only strings, numbers, and null. Money is bound as decimal text, cast to INTEGER in SQL, and read back as TEXT, so it never passes through a JavaScript number. Timestamps and counts may come back as number or bigint; a number beyond Number.MAX_SAFE_INTEGER is refused.

better-sqlite3

import Database from "better-sqlite3";

const db = new Database("ledger.db");
db.pragma("journal_mode = WAL");
db.exec(sqliteSchema);

// better-sqlite3 refuses .all() on statements that return no rows.
const all = (sql: string, params: readonly (string | number | null)[]) => {
  const statement = db.prepare(sql);
  return statement.reader ? statement.all(...params) : (statement.run(...params), []);
};
const ledger = sqliteLedger({
  execute: all,
  transaction: (statements) => db.transaction(() => statements.map(({ sql, params }) => all(sql, params))).immediate(),
});

bun:sqlite

import { Database } from "bun:sqlite";

const db = new Database("ledger.db");
db.exec("PRAGMA journal_mode = WAL;");
db.exec(sqliteSchema);

const all = (sql: string, params: readonly (string | number | null)[]) => db.query(sql).all(...params);
const ledger = sqliteLedger({
  execute: all,
  transaction: (statements) => db.transaction(() => statements.map(({ sql, params }) => all(sql, params))).immediate(),
});

Cloudflare D1

D1 has no interactive transactions: db.batch() runs a list of statements atomically, which is exactly what transaction asks for.

const ledger = sqliteLedger({
  execute: async (sql, params) => (await env.DB.prepare(sql).bind(...params).all()).results,
  transaction: async (statements) =>
    (await env.DB.batch(statements.map(({ sql, params }) => env.DB.prepare(sql).bind(...params)))).map((result) => result.results),
});

D1 returns every INTEGER as a JavaScript number, which is why money is read as TEXT. Full Worker: Charge per call on Cloudflare Workers.

Why batches

A transaction that reads, decides, and writes needs a connection held across awaits. Synchronous drivers share one connection across concurrent requests, and D1 cannot hold one open at all. So every decision is expressed in SQL:

• createCharge is one batch. Its first statement computes the status (exists, missing, expired, an unstorable amount, a currency mismatch, busy, insufficient, or created) in a single CASE; the insert, reservation, and history row are conditioned on the same expression.
• transitionCharge is one batch. Every statement carries the same compare-and-set condition on the charge's id and both axes, and only the last one changes the charge, so the accounting update, history row, and charge update all match or none do.
• replaceAuthorizationData is one UPDATE, called by core with the rail's redact output when a single-use charge becomes final.
• openAuthorization is INSERT … ON CONFLICT DO NOTHING RETURNING plus a SELECT. claim is one INSERT … ON CONFLICT DO UPDATE … WHERE expired RETURNING.

Because SQLite runs one write transaction at a time, concurrent createCharge calls cannot over-reserve an authorization: for a single-use authorization, one wins and the rest are busy.

Tables

• Money is integer micros in a 64-bit INTEGER with a currency code. Amounts outside 0 … 2^63 − 1 are refused with INVALID_AMOUNT, and another currency than the authorization's with CURRENCY_MISMATCH. STRICT tables refuse SQLite's silent overflow to REAL, so an overflowing total fails the transaction.
• Timestamps are INTEGER milliseconds since the Unix epoch, UTC.
• JSON is TEXT, checked with json_valid.

-- Expired claims can be deleted at any time
DELETE FROM tollstile_claims WHERE expires_at < (unixepoch() - 86400) * 1000;

Verification status

Tested with node:sqlite (SQLite 3.50.4) on Node 22:

• The shared conformance suite, twice: with the synchronous adapter above, and through an adapter that behaves like D1 (every call resolves on a later turn, INTEGER columns returned as bigint). It covers every createCharge status, concurrent charges on one authorization, compare-and-set conflicts, accounting through every payment state, currency and amount refusals, replaceAuthorizationData, history, pendingCharges, spendSince, claim expiry, and amounts up to 2^63 − 1.
• End-to-end flows through createTollstile, rollback of a whole batch, two connections sharing one WAL file, index usage, and overflow refusal.

Not verified:

• D1, better-sqlite3, and bun:sqlite. Their adapters above were written from documentation and not executed. To verify, run test/ledger-conformance.ts from the repository against each adapter; for D1, inside @cloudflare/vitest-pool-workers or wrangler dev with a local database.
• Multi-process contention on one file, which relies on SQLite's file locking and busy_timeout.

Idempotency records

The charge ID encodes the payer, idempotency key, and attempt number when a key is present. Repeated creation of that ID returns the existing charge. All instances serving the same payer must share the ledger; a per-process memory ledger cannot deduplicate across instances or restarts.

Ledgers persist both fields; transitions can update resultRef. Keep charge records for the period in which you need retry protection. See Idempotency.

Existing databases

The exported schema creates new tables; CREATE TABLE IF NOT EXISTS does not add columns to existing tables. If your database predates request_hash and result_ref, add the missing nullable text columns through a reviewed migration before running the current ledger. Do not invent request hashes for historical charges: their original requests were not recorded.