Event Sourcing on SQLite in Production: What 155,000 Events Taught Me

Most writing about event sourcing with SQLite is either a library README or a tutorial that stops at the second event. This is neither. 8examples.com has been event sourced on SQLite since the day it took its first payment, and the same pattern runs two other products I maintain. What follows is the actual schema, the actual projection code, the background-job pattern that replaced a queue, and a production outage on August 24, 2026 that taught me precisely where the limits are. If you are deciding whether SQLite is enough for an event-sourced system, this is the evidence I wish I had read first.

Why SQLite for an event store

The application is a Next.js server in one Docker container on one machine, with a volume mounted at /app/data. There is no database server to run, patch, or pay for. Backups are file copies. The whole store fits in the page cache. And better-sqlite3, the driver, is synchronous, which sounds like a limitation and turns out to be the feature that makes the concurrency story trivially correct. I will come back to that, twice: once as a gift and once as the cause of the outage.

The trade is that there is exactly one writer, and it is the web process. If you need more than one process writing events, this is not your architecture. For a small business system with a few hundred customers, it is close to ideal.

The schema is one table

CREATE TABLE IF NOT EXISTS events (
  id        TEXT PRIMARY KEY,   -- uuid
  type      TEXT NOT NULL,      -- snake_case, past tense: claw_assigned
  data      TEXT NOT NULL,      -- JSON
  timestamp TEXT NOT NULL       -- ISO 8601
);
CREATE INDEX IF NOT EXISTS idx_events_type ON events(type);

-- Appending is the only write the application ever does.
INSERT INTO events (id, type, data, timestamp) VALUES (?, ?, ?, ?);

That is the entire storage design. Events are facts, named in the past tense, and nothing updates or deletes them. There is no aggregate column and no version column, because the guard against conflicting commands is not a version check. It is the transaction.

Commands replay, then append, inside one transaction

Assigning a worker to a paying customer is the most contested command in the system: twenty accounts, a Stripe checkout completing, and a fulfillment job that might be running at the same time. The command folds the current inventory from the stream, picks a free account, and appends claw_assigned, all inside db.transaction(). Because the driver is synchronous and the process is single-threaded, nothing else in the process can run between the fold and the append. Two purchases cannot both see the same free slot. The command is also idempotent per checkout session, so a retried webhook or a refreshed confirmation page finds its own earlier assignment and returns it.

I want to be precise about what this buys. It is not optimistic concurrency and it is not a lock table. It is the absence of concurrency, which for a single-process system is the cheapest correct answer there is.

Projections are folds, not tables

There are no read models. Every projection is a query by type followed by a reduce in JavaScript:

export function foldClaws(db, product = "all") {
  const rows = db.prepare(
    "SELECT type, data FROM events WHERE type IN ('claw_added','claw_assigned','claw_released','claw_retired','claw_reinstated') ORDER BY rowid"
  ).all();
  const claws = new Map();
  for (const row of rows) {
    const d = JSON.parse(row.data);
    if (row.type === "claw_added")      claws.set(d.username, { ...d, assigned: false, retired: false });
    else if (row.type === "claw_assigned") claws.get(d.username).assigned = true;
    else if (row.type === "claw_released") claws.get(d.username).assigned = false;
    else if (row.type === "claw_retired")  claws.get(d.username).retired = true;
    else if (row.type === "claw_reinstated") claws.get(d.username).retired = false;
  }
  return [...claws.values()];
}

Three things about this shape matter more than they look.

• Order by rowid, not timestamp. Rowid is the append order. Timestamps are strings produced by the application and can tie or, after a clock correction, go backwards. The stream’s truth is the order in which facts were recorded.
• Select by type, always. A projection names the handful of event types it cares about. It never reads the whole table. This is the property that makes the index on type the single most important line in the schema, and I will show you what happens without it.
• Later events can undo earlier ones without touching them. claw_reinstated exists because a customer who pays late during a trial’s grace period needs their cancelled worker back. The retirement stays in the stream, the reinstatement follows it, and the fold gets the right answer. No update, no delete, full history.

Everything else derives the same way. A customer’s dashboard is claw_assigned events matching their email. The runtime environment for a customer’s website is derived by replay at deploy time and never stored. The Xero connection for a worker is the last xero_connection_authorized for it, updated by any xero_token_refreshed after it, cleared by a xero_connection_revoked. When a projection needs a snapshot, I will add one. None does yet.

Side effects are events too

The pattern that replaced every queue I would otherwise have needed: work discovery is a query, and completion is a marker event. A new customer should get a welcome email. The job asks for every claw_assigned without a matching claw_welcome_email_sent, sends, and appends the marker. If the mail server is down the marker is never written and the next run tries again. If the process crashes after sending and before appending, the customer gets the email twice, which is the at-least-once trade I will take every time over at-most-once.

The newsletter that emails subscribers about new posts uses the same shape at a finer grain: the sent marker is per (blastId, email), so a crash halfway through a batch resumes at the next address rather than restarting. Recap emails, trial reminders, login codes, domain registration, mailbox provisioning, the owner notifications on every purchase: every one of them is a query for facts lacking a marker. There is no job table, no queue, no retry count column. Retry caps are event counts: a job that has appended three newsletter_email_failed events for an address gives up on it.

Background jobs without cron

The jobs need something to run them, and I did not want a scheduler. Instead, the fulfillment pump runs whenever traffic arrives that can afford it: the inventory endpoint the sales page polls, and every telemetry heartbeat from the fleet of workers, which post every ten minutes. Traffic drives the jobs. A machine with no visitors still has its own workers phoning home, so the jobs never starve.

That design has a sharp edge, and the outage below is what it looks like when you find it. The guard that now wraps the pump:

export function createThrottledJob(run, minIntervalMs, now = Date.now) {
  let inFlight = null;
  let lastStartedAt = -Infinity;
  return (options = {}) => {
    if (inFlight) return inFlight;                       // join the run already going
    if (!options.force && now() - lastStartedAt < minIntervalMs) return Promise.resolve();
    lastStartedAt = now();
    inFlight = run().finally(() => { inFlight = null; });
    return inFlight;
  };
}

export const pumpFulfillment = createThrottledJob(runFulfillmentPump, 60_000);

Two guards, and both are necessary. Concurrent callers join the in-flight run instead of starting another. A new run cannot start within sixty seconds of the last one no matter how fast heartbeats arrive. A command that needs the jobs immediately, such as a signup that wants its confirmation email now, passes force and still cannot overlap.

The outage: 155,000 events and no index

On August 24, 2026 the site stopped answering. Not the API, not a page: everything, including static files. The container was up. The process was alive. It was just not serving.

Here is what had happened, in the order I understood it. The fleet telemetry, one small JSON imprint per worker every ten minutes, had been going into the same events table as everything else, as claw_telemetry. It seemed harmless: it is an event, it has a type, why not. After a few weeks there were 155,000 rows in the table and 154,000 of them were telemetry. One thousand were the business.

The table had no index on type. I had never needed one; the primary key is the uuid, and every query filters by type, which the primary-key index cannot serve. So every projection, every one of those "select by type and fold" reads, was a full scan of 155,000 rows and a JSON parse of the ones it wanted. At a thousand rows that is nothing. At 155,000 it is tens of milliseconds per projection, and a single pump runs dozens of projections.

Now add the synchronous driver. better-sqlite3 does its work on the event loop thread. A pump that takes seconds of CPU is seconds during which the process cannot accept a connection, serve a stylesheet, or answer a health check. And the pump was being kicked by every telemetry heartbeat, with no in-flight guard, so pumps overlapped and queued behind each other. Each heartbeat made the next one slower. The process was not dead. It was busy, forever.

The tell, in hindsight, was that the outage was total rather than partial. A slow database gives you slow pages. A blocked event loop gives you nothing at all, which is a different signature, and now I know it.

Three fixes, in the order they mattered

1. The index

CREATE INDEX IF NOT EXISTS idx_events_type ON events(type);

One idempotent line in the function that opens the database. It builds in well under a second even on the large table, and it turns every projection from a scan into a range read. If you take one thing from this post: an event store that selects by type needs an index on type on day one, not on the day you notice.

2. High-volume machine streams get their own table

The deeper mistake was letting a machine-written stream share a table with human-scale business facts. Telemetry is written by a script on a timer, it grows without bound, and no business projection ever reads it. It now lives in telemetry_events with the worker name as a real indexed column, and the two reads it serves, a single worker’s timeline and the fleet overview, are index lookups. The fleet overview, which used to fold every imprint in JavaScript to find the newest per worker, is now a grouped query in SQLite:

SELECT t.claw, t.data, t.timestamp, g.points
FROM telemetry_events t
JOIN (SELECT claw, COUNT(*) AS points, MAX(rowid) AS newest
      FROM telemetry_events GROUP BY claw) g
  ON t.rowid = g.newest;

That read went from 272 milliseconds to 6 at production scale. The migration that moved the historical rows runs once at boot, inside a transaction, with INSERT OR IGNORE so it resumes safely if the process dies halfway, and it finds nothing to do on every boot after the first. It moved 154,000 rows in about 600 milliseconds. The iPhone app’s location reports, another every-five-minutes machine stream, went into their own table for the same reason before they had the chance to repeat the lesson.

The rule I now apply: the events table holds facts a person would care about. Anything a machine writes on a timer gets its own table with its own indexes. The two can share a file. They cannot share a table.

3. Throttle the pump

The createThrottledJob wrapper above. Concurrent kicks collapse into one run, and runs are at least a minute apart. With the index and the table split, a pump is milliseconds of CPU, so this guard is now about bounding the worst case rather than surviving the normal one. It stays, because the next unbounded stream is always one convenient decision away.

What I would tell you before you build this

• Index the type column in the same statement that creates the table. Make it IF NOT EXISTS and run it on every open.
• Decide, per stream, whether a person or a machine writes it. Machine streams get their own table before the first row.
• Order by rowid. Reserve timestamps for display and for time-window queries.
• Every side effect pairs with a marker event. Discover work by querying for facts without markers. Never store a "pending" flag you would have to update.
• Let one process own the file. Run it with WAL mode on. If you ever need a second writer, that is the day to consider Postgres, and the events table will move unchanged.
• Wrap anything that runs on traffic in a throttle. Collapse concurrent runs and set a floor. The synchronous driver means an unbounded job is a total outage, not a slow one.
• Encrypt what needs encrypting before it goes in the stream. The Xero access and refresh tokens are AES-256-GCM ciphertext inside their events; the gateway keys are stored only as an HMAC. An append-only log is forever, and "we will rotate it later" is not a plan.
• Test against an in-memory database with the same schema. Every job here takes its clock and its mail sender as parameters, so a test can run a thirty-day trial in a millisecond and assert on the exact events appended.

Event sourcing has a reputation for needing heavy infrastructure. It needs a table, an index, and the discipline to never update a row. SQLite is a fine place to learn that discipline, and for a single-process business system it is a fine place to keep it. Just do not let the machines write into the same table as the people.

Frequently asked questions

Is SQLite good enough for event sourcing in production?

For a single-process application it is more than enough. One append-only table with an index on the event type has run a real business here for months, including payments, provisioning, and audit logs. The limits are operational rather than about correctness: one writer process, and a synchronous driver that will freeze the server if you let a query scan a large table.

Do you need snapshots or read models?

Not at first. Every projection here is a query by event type followed by a fold in JavaScript, and none of them needs a snapshot at a few thousand business events. Add a read model when a specific projection is measurably slow, and move any machine-written high-volume stream into its own table before it gets anywhere near the business events.

How do you handle concurrency with SQLite event sourcing?

By having one process. Commands replay state and append inside a single better-sqlite3 transaction, and because the driver is synchronous and the process is single-threaded, two purchases can never both see the same free slot. That is not a workaround; it is the reason to pick this stack for a small system.

Doesn't better-sqlite3 block the Node.js event loop?

Yes, and that is the failure mode to design around. A full-table scan of 155,000 rows blocked the loop for seconds and the process stopped serving even static files. The fix was an index on the event type, moving the high-volume telemetry stream into its own table, and throttling the background job so overlapping runs collapse into one.

When would you move from SQLite to Postgres?

When there is a second writer process. Multiple app servers, a separate worker fleet, or a need for the database to outlive the machine are all reasons. Nothing about the events table would change; the transaction that guards commands would need to become a database-level lock or an optimistic version check.

Related

• The DiscRepublic case study: the same approach, modelled first with Event Modeling swimlanes.
• JetBrains HTTP files against an event-sourced .NET API: the pattern on a different stack.
• How I host 100+ OpenClaw instances: the fleet whose telemetry filled the table.