Before you reach for Kafka, Temporal, or a distributed queue, consider what SQLite already gives you: ACID guarantees, a write-ahead log, and zero network overhead — enough to build rock-solid durable

Why SQLite Is All You Need for Durable Workflow Orchestration

Most engineers reach for a distributed message broker the moment a workflow needs to survive a crash. Kafka, RabbitMQ, Temporal, or a managed queue service — the assumption is that durability requires infrastructure complexity. It often does not. For a surprisingly large class of problems, SQLite is the most reliable, operationally simple, and developer-friendly choice you have.

What "Durable Workflow" Actually Means

A durable workflow is one that can be interrupted — by a crash, a restart, a transient failure — and resume exactly where it left off without corrupting state or re-executing completed steps. The hard requirements are:

Atomicity: a step either completes fully or not at all.
Persistence: state survives process death.
Resumability: the engine can reconstruct current position from stored state.
Idempotency: replaying a step that already succeeded causes no harm.

None of these requirements says anything about distributed systems, message brokers, or even multiple machines. They are fundamentally about storage guarantees — which is precisely what a database provides.

SQLite's Underrated Strengths

SQLite is often dismissed as a toy — something you use for mobile apps or local dev environments. That reputation is badly outdated. Modern SQLite (3.x with WAL mode enabled) offers:

Full ACID transactions backed by a write-ahead log.
Single-writer, multi-reader concurrency that is safe across threads.
Synchronous durability — PRAGMA synchronous = NORMAL flushes to disk before returning.
Zero network latency — reads and writes are function calls, not round-trips.
A single file you can back up with cp or replicate with Litestream.

For a workflow engine running on one machine (or one container), these properties are stronger than what most teams actually get from a distributed broker once you account for misconfiguration, partial failures, and operational overhead.

Modeling a Durable Workflow in SQLite

The core idea is to treat the database as an event log. Every workflow instance is a row; every step transition is an INSERT or UPDATE wrapped in a transaction. If the process dies mid-step, the transaction rolls back, and on restart the engine reads the last committed state and retries from there.

A minimal schema looks like this:

CREATE TABLE workflow_runs (
  id          TEXT PRIMARY KEY,
  name        TEXT NOT NULL,
  status      TEXT NOT NULL DEFAULT 'pending', -- pending | running | done | failed
  current_step INTEGER NOT NULL DEFAULT 0,
  payload     TEXT,                            -- JSON input
  result      TEXT,                            -- JSON output
  created_at  INTEGER NOT NULL DEFAULT (unixepoch()),
  updated_at  INTEGER NOT NULL DEFAULT (unixepoch())
);

CREATE TABLE workflow_events (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  run_id      TEXT NOT NULL REFERENCES workflow_runs(id),
  step        INTEGER NOT NULL,
  event_type  TEXT NOT NULL,                   -- started | completed | failed | retried
  detail      TEXT,
  occurred_at INTEGER NOT NULL DEFAULT (unixepoch())
);

Each step the orchestrator executes begins a transaction, writes a started event, performs work, then writes a completed event and advances current_step — all atomically. A crash between the transaction open and commit leaves no trace. On restart, the engine queries for running runs whose last event is started (not completed) and retries them. Combined with idempotent step handlers, this gives you exactly-once semantics at the application level without a single external dependency.

Where This Pattern Genuinely Shines

Internal tooling and SaaS background jobs. Invoice generation, report compilation, email drip sequences, data export pipelines — these are long-running, step-based, and business-critical, yet almost never require sub-millisecond latency or multi-node fan-out. SQLite handles them cleanly.

Early-stage products. Before you know your scale, adding Temporal or Kafka introduces operational overhead, a learning curve, and cost. A SQLite-backed workflow engine you can understand in an afternoon is a better foundation than a distributed system you cannot debug at 2 a.m.

Edge and embedded deployments. If your application runs close to the user — on a Raspberry Pi, in a Cloudflare Worker via D1, or on a customer's on-premise server — a self-contained SQLite workflow store is often the only practical option.

The Honest Limitations

SQLite is not the right answer when:

Multiple writer processes need to coordinate across machines. SQLite's single-writer model breaks under distributed load. At that point you want PostgreSQL (which supports the same event-log pattern with advisory locks) or a purpose-built orchestrator.
Throughput exceeds ~10,000 writes/second. SQLite can saturate on heavy concurrent write workloads. For most SaaS applications this ceiling is never reached, but it is real.
You need workflow visibility, versioning, or replay tooling out of the box. Temporal and similar systems provide rich UIs, history querying, and workflow versioning. Building those on SQLite is possible but requires deliberate effort.

Replication Without the Pain

One common objection is disaster recovery — what happens if the disk fails? The answer is Litestream, an open-source tool that continuously streams SQLite WAL frames to S3, GCS, or any S3-compatible store. Recovery means downloading the latest snapshot and replaying recent frames. For most applications, this achieves recovery point objectives measured in seconds, not minutes — without running a database server.

What This Means for Software Teams

The broader lesson here is not "always use SQLite." It is resist infrastructure complexity until the problem actually demands it. A well-modelled relational schema with ACID transactions solves a huge range of reliability problems that engineers habitually delegate to distributed systems. Starting simple keeps your codebase readable, your infrastructure bill low, and your on-call rotation sane. When you genuinely outgrow SQLite, migrating the event-log pattern to Postgres is straightforward. The inverse — untangling an over-engineered broker topology — rarely is.

Why this matters for your project: Whether you are building a SaaS product, an internal automation platform, or a data pipeline, choosing the right persistence layer early shapes everything downstream. At Code!nk Technologies we routinely help teams right-size their architecture — avoiding premature complexity while leaving clear upgrade paths as scale demands. SQLite-backed workflows are one example of a simple foundation that earns its keep.

Source: SQLite is all you need for durable workflows — Hacker News