Skip to content
Tutorials Center
My learning

Topics

Lesson 29 of 33 ยท Projects

Project: Local-First Event Log and Job Queue

Build an append-only event log and a SQLite-backed job queue: claim work atomically with UPDATE RETURNING, retry with backoff and dedupe with upsert.

Many applications need two plumbing pieces: a record of what happened, and a list of work still to do. Small apps often reach for a message broker or a hosted queue, but SQLite can do both jobs well for a single machine: desktop and mobile apps, command-line tools, small servers, background workers that live next to the app. You get transactions, durability and a query language for free, and nothing extra to deploy.

In this project you build an append-only event log that nobody can edit, then a job queue whose workers claim jobs with a single atomic UPDATE ... RETURNING, retry failures with a growing delay, and avoid duplicate work with an upsert. It draws on UPSERT and RETURNING, triggers, transactions and JSON functions.

Two tables, two habits

Event log Facts are only ever appended.
Job queue Work is claimed, then finished.

Same database file, same transactions.

1 / 2

Part 1: an append-only event log

An event log records facts in the order they happened: "item added to cart", "order shipped". Rows are never changed or deleted, so you can always replay history, audit what happened or rebuild a summary. Three design points:

  • Id order is the order. An INTEGER PRIMARY KEY is assigned in increasing order, so id is a natural cursor ("everything after event 41"). Without AUTOINCREMENT a new id is normally larger than every earlier one, but ids can be reused after the latest row is deleted, and since we never delete, that cannot happen here.
  • JSON payloads, validated. A CHECK (json_valid(payload)) stops bad data at the door while keeping the schema flexible.
  • Immutability enforced by the database. Two triggers abort any UPDATE or DELETE, so even buggy code cannot rewrite history.

Current state is just a query over the events: group, sum, and filter.

SQL Append events, then derive the current state

Press Run (or Ctrl + Enter) to see the result here.

The cart query shows cart:1 holding 1 unit of A1 and 1 of B2, and cart:2 holding 5 of A1: removed units are subtracted, and anything that nets out to zero is hidden by HAVING. The second query shows the last event per stream with a window function. The final statement ends the run with the trigger's message, events are append-only, which is exactly the point. To correct a mistake in an event log you append a compensating event (such as ItemRemoved), never edit the old one.

Consumers and cursors

Other parts of the program (a mailer, a search indexer, a sync job) each need to process every event once. Give each consumer a row in a small consumers table that stores the last event id it handled. The consumer reads WHERE id > last_id, does its work, and advances the cursor with an upsert. Taking the max makes the update safe even if it is repeated.

SQL A consumer cursor advanced with an upsert

Press Run (or Ctrl + Enter) to see the result here.

The first batch returns events 1 to 4. After the cursor moves to 4 (and stays there even when the stale value 2 arrives), the next read returns only events 5 and 6. If the consumer crashes after doing the work but before saving the cursor, it will see the same events again, so consumers should be idempotent (safe to run twice). This "at least once" delivery is the normal trade-off.

Part 2: a job queue

A job queue holds work to be done later, by a worker that may be another thread or process. Each job moves through a small set of states.

flowchart LR
  Q["queued"] -->|"claim"| R["running"]
  R -->|"success"| D["done"]
  R -->|"fails, retries left<br/>(run_after moves later)"| Q
  R -->|"fails, no retries left"| X["dead"]

A job's life: a failed job returns to the queue with a delay, until it runs out of attempts.

The columns carry the whole design: status, attempts and max_attempts count tries, run_after is a Unix time before which the job must not start (used for delays), locked_by and locked_at say which worker holds it, and dedupe_key is UNIQUE so the same logical job cannot be queued twice. The partial index covers only queued rows, so finding the next job stays fast no matter how many finished jobs pile up.

Enqueue without duplicates

When a user double-clicks, or a webhook is delivered twice, the same job may be enqueued twice. A unique dedupe_key plus ON CONFLICT DO NOTHING makes the second attempt a no-op, and RETURNING tells you whether a row was created, because a skipped insert returns nothing.

SQL Enqueue with a dedupe key

Press Run (or Ctrl + Enter) to see the result here.

The first insert returns job 1. The second returns no row at all, and the third bumps job 1's priority to 5 because it was still queued. The final listing shows four jobs: a NULL dedupe key is allowed many times (each NULL counts as distinct in a UNIQUE column), so jobs that do not need deduplication leave it empty.

Claim a job atomically

The heart of a queue is claiming: "give me the next job and make sure nobody else gets it". The tempting approach is a SELECT followed by an UPDATE, but two workers can select the same job before either updates it. In SQLite the fix is one statement that selects and marks the job in a single write, and returns it with RETURNING. SQLite allows only one writer at a time, so two such statements can never claim the same row.

SQL Two workers claim jobs, one fails and retries

Press Run (or Ctrl + Enter) to see the result here.

Worker A gets build_report (priority 9) and worker B gets job 1, send_email (lowest id among equal priorities); the two never overlap. After A's success the job is done. B's failure puts job 1 back to queued with run_after 130, because the first retry waits 30 seconds. The two checks at the end show that at time 110 it is still hidden, and at time 130 it reappears alongside the others. The AND locked_by = 'worker-b' guards ensure that only the worker holding a job can finish it.

Two details to copy into your own code:

  • The 1 << (attempts - 1) shift doubles the delay with every failure. Add a random jitter (a few seconds) in real systems so many failed jobs do not all return in the same second.
  • Check how many rows each update changed. A worker whose claim returned no row simply found the queue empty.

Recover stuck jobs

A worker can crash after claiming a job, leaving it running forever. A periodic "reaper" statement returns jobs whose lock is too old back to the queue. The attempt was already counted, so a job that keeps killing its worker eventually goes dead instead of looping forever.

SQL Reap stale locks

Press Run (or Ctrl + Enter) to see the result here.

stalled returns to the queue and poison is declared dead because it used all three attempts; fresh is untouched because its lock is only ten seconds old. Pick the lock timeout longer than your slowest job, or have workers refresh locked_at as a heartbeat.

Operating notes

  • One database file, many workers. Open it in WAL mode with a busy_timeout so a worker waits instead of failing when another is writing (see PRAGMAs and Tuning and Concurrency, Locking and WAL).
  • Keep transactions short. Claim, commit, then do the slow work outside any transaction. Holding a transaction during an email send blocks every other writer.
  • Write the result and the next job together. When finishing a job also inserts events or enqueues a follow-up, do both in one transaction so you never record one without the other.
  • Clean up. Delete old done rows on a schedule (or move them into the event log) so the table stays small.
  • Know the limits. This design suits one machine. If workers on many servers must share a queue, use a client-server database or a real broker.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why should a worker claim a job with one UPDATE ... RETURNING instead of a SELECT followed by an UPDATE?

    Doing the pick and the mark in a single write statement makes the claim atomic, so two workers can never receive the same job.
  2. 2. What happens with INSERT ... ON CONFLICT (dedupe_key) DO NOTHING RETURNING id when the key already exists?

    A skipped insert produces no result row, which is how the caller can tell that the job already existed.
  3. 3. Why is an event log protected with BEFORE UPDATE and BEFORE DELETE triggers that RAISE(ABORT)?

    An append-only log is only trustworthy if rows cannot change. Corrections are new events that cancel or amend earlier ones.
  4. 4. A worker crashes right after claiming a job. How does the queue recover?

    SQLite has no per-row locks held by workers, so the application keeps lock timestamps and periodically returns old running jobs to the queue.
  5. 5. What does the retry expression run_after = now + 30 * (1 << (attempts - 1)) produce for attempts 1, 2 and 3?

    Shifting 1 left by attempts minus one gives 1, 2, 4, so the delay doubles each time. That is exponential backoff.

Summary

  • An event log is an append-only table; enforce that with triggers, derive state with queries, and fix mistakes by appending corrections.
  • Consumers keep a cursor (last handled id) and advance it with an upsert; process events idempotently.
  • A job queue needs status, attempt counters, run_after and lock columns, plus a partial index on queued rows.
  • Claim work with a single UPDATE ... WHERE id = (SELECT ... LIMIT 1) RETURNING, which is atomic under SQLite's single writer.
  • Retry with exponential backoff, dedupe with a UNIQUE key and ON CONFLICT, and requeue stale locks with a reaper.

Next: Project: Analyzing a CSV Dataset, turning raw rows into answers.