Building a Durable Execution Engine with SQLite

Summary

At the core of every DE engine there’s some form of persistent durable execution log. You can think of this a bit like the write-ahead log of a database. It captures the intent to execute a given flow step, which makes it possible to retry that step should it fail, using the same parameter values. Once successfully executed, a step’s result will also be recorded in the log, so that it can be replayed from there if needed, without having to actually re-execute the step itself. DE logs come in two flavours largely speaking; one is in the form of an external state store which is accessed via some sort of SDK. Example frameworks taking this approach include Temporal, Restate, Resonate, and Inngest. The other option is to persist DE state in the local database of a given application or (micro)service. One solution in this category is DBOS, which implements DE on top of Postgres. To keep things simple, I went with the local database model for Persistasaurus, using SQLite for storing the execution log. But as we’ll see later on, depending on your specific use case, SQLite actually might also be a great choice for a production scenario, for instance when building a self-contained agentic system. The structure of the execution log table in SQLite is straight-forward. It contains one entry for each durable execution step: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 CREATE TABLE IF NOT EXISTS execution_log ( flowId TEXT NOT NULL, (1) step INTEGER NOT NULL, (2) timestamp INTEGER NOT NULL, (3) class_name TEXT NOT NULL, (4) method_name TEXT NOT NULL, (5) delay INTEGER, (6) status TEXT (7) CHECK( status IN ('PENDING','WAITING_FOR_SIGNAL','COMPLETE') ) NOT NULL, attempts INTEGER NOT NULL DEFAULT 1, (8) parameters BLOB, (9) return_value BLOB, (10) PRIMARY KEY (flowId, step) ) 1 The UUID of the flow 2 The sequence number of the step within the flow, in the order of execution 3 The timestamp of first running this step 4 The name of the class defining the step method 5 The name of the step met...