Transactional storage contract and embedded SQLite backend

State: discussion
Authors: Ryusen contributors
Discussion: chaba-dev/ryusen#1
Labels: storage transactions sqlite foundationdb conformance benchmarks performance
Implemented: 68%Checklist

Goal

Give workflow algorithms one transactional authority while preserving an offline, single-binary development server. The same observable state-machine semantics must be implementable by embedded SQLite locally and FoundationDB in hosted deployments.

Storage contract

The engine depends on a narrow ordered transactional key-value contract rather than database-specific queue, pause, or run repositories. It provides:

serializable read-only and read-write transactions;
atomic reads, writes, deletes, and ordered range scans;
optimistic conflict detection or equivalent serialized write behavior;
unique, strictly ordered commit positions for durable mutation commits, with no position for a read-only write outcome;
a commit-ordered journal append, so a transaction can record an ordered change that is stamped with its own commit position;
a read snapshot that reports the inclusive upper bound of the commit positions it can observe;
distinct retryable-not-committed and ambiguous-commit errors, so generic retries never replay a transaction that may already be durable;
a post-commit wake-up hook that is never required for correctness;
bounded transaction size and retryable conflict errors.

Workflow state, immutable history, application-manifest generations, immutable workspace routing generations, active routing heads, connected release ordering, ready work, timers, pause registrations, generation changes, and outbox records may therefore change in one transaction. Domain algorithms must not depend on FoundationDB versionstamps, watches, tuple types, or SQLite SQL outside their backend adapters.

Notifications are hints. Every consumer periodically scans durable indexes, so a process crash or missed notification delays work but cannot lose it.

Commit-ordered journal

A write transaction can append a record to a journal partition. The backend stamps the record with the enclosing transaction’s commit position, so a scan of one partition returns entries in exact commit order on every backend and the order survives restart. Entries appended in one transaction keep their append order. How the position is produced stays inside the adapter: SQLite keys the append by the sequence the transaction is about to commit, and FoundationDB uses a versionstamped key. A versionstamped key never collides with another transaction’s, so no shared mutable key scales with journal traffic.

A position identifies one entry, not one commit: it is the commit position plus the entry’s append order within that commit. Several entries routinely share a commit — a run materialization appends a dispatch change and a run change for each matched run — so a caller that resumed from a bare commit position would skip every remaining entry of that commit. A scan therefore takes an entry position and returns entries strictly after it, and a caller that stops in the middle of a transaction’s entries continues with that transaction’s next entry. A snapshot cursor is the position after every entry of the commit the snapshot observed, which is the same bound as before: a commit that races the read is always strictly after the cursor.

The partition namespace is a caller-supplied byte string that only has to be unambiguous; the shared tuple codec provides that, and a partition is wrapped so no partition is a prefix of another. An append is itself a durable mutation, so a transaction that only appends still commits a position.

A read transaction reports the inclusive upper bound of the commit positions visible in its snapshot. A caller that reads several projections and this bound from one transaction therefore holds one authoritative cursor for exactly the state it read. On SQLite the bound is the committed sequence value; on FoundationDB it is the transaction read version, because a transaction reading at version R observes exactly the commits whose version is at most R.

A cursor is only meaningful while the entries after it are retained, so each journal partition has a durable retained floor. Every entry at or below the floor may have been removed and every entry above it is retained. The floor is one key per partition under the reserved ~journal-floor namespace, written only by compaction, so the append path gains no shared mutable key. A partition that was never compacted reports the journal origin.

Compaction removes at most a bounded number of entries (MAX_JOURNAL_COMPACTION_ENTRIES, 64) at or below a target position in one transaction and raises the floor past them in that same transaction, so no reader observes a removed entry above the floor. The floor never moves backwards. A step that stops at its bound stores the floor it reached, and the next step resumes there, so compaction is resumable without holding a long transaction. A target no entry occupies is valid: the floor then covers a range that is already empty. A target ahead of the transaction’s snapshot is refused. Compaction is written against the backend-neutral journal scan and key operations, and the shared conformance suite covers it.

A reader compares its position with the floor in the same snapshot it scans. A position at or above the floor reads everything after it. A position below the floor is refused as expired, never advanced past the removed range. RFD 7 turns that refusal into a resynchronization response.

No retention policy calls compaction yet. The local development server retains the whole journal, so its growth is unbounded and every cursor it issued stays valid; there is no archival copy or production retention service.

Journal cost and open concerns

The journal is cheap to reason about but it is not free, and the local proof of concept does not yet measure it:

Write cost. An append is an extra durable record in the enclosing transaction, so a journaled mutation commits more bytes and conflicts on one more key than the same mutation without journaling. On SQLite the append adds write volume to a path that BEGIN IMMEDIATE already serializes rather than new writer contention. On FoundationDB the append adds a versionstamped write to the transaction’s conflict set and no shared mutable key, so concurrent appends from different transactions never contend with each other.
Read cost. A consumer scans its partition strictly after its cursor, bounded per scan by the page limit. A caught-up consumer re-scans on its own cadence whether or not anything changed, so an idle consumer’s cost is roughly independent of the change rate. A consumer with changes to read scans again immediately, and a notification wakes an idle consumer early, so while changes are flowing the cost tracks the change rate as well as the number of active consumers and their cadence. An application-scoped consumer scanning a workspace partition may need several bounded scans to advance past entries it filters out, because a page can be filled entirely by non-matching entries.
Growth. Nothing calls compaction yet, so journal keys and values grow with total journaled mutations for the life of the database.

The structural gates in the performance contract below cover event submission, routing, queue mutation, and fan-out. They do not cover the journal append, the bootstrap snapshot read, or the catch-up scan, so a regression in those paths would not fail a gate today. Adding that workload is tracked in the implementation checklist.

Key encoding

Keys are byte strings ordered lexicographically. A shared tuple codec encodes namespaces, organization or tenant, workspace, application, and run identifiers, signed ordering fields, and string or byte components without prefix ambiguity. Workspace event and routing prefixes include workspace scope. Manifest, release, endpoint, run, and SDK-work prefixes also include application scope so an unscoped identifier cannot alias another application. Prefix ranges must have a canonical exclusive end calculation. Values are explicitly versioned records.

The keyspace and codec are engine internals, but fixtures are shared by all backends to prevent ordering drift.

SQLite backend

The local backend stores ordered keys in one SQLite database using a BLOB primary key on a WITHOUT ROWID table. Write transactions use BEGIN IMMEDIATE to serialize writers. WAL mode provides crash recovery and allows concurrent readers. A transactional sequence supplies commit positions.

After commit, the backend signals an in-process notifier. A periodic scan is the fallback. The database file contains all initial-version control-plane data so ryusen dev remains offline and self-contained. Ephemeral tests may use a temporary database but must exercise the same backend implementation.

The SQLite transaction sequence is encoded into the backend-independent opaque commit position. SQLite’s global writer serialization is acceptable for development and tests; it is not presented as a horizontally scalable hosted backend. Because BEGIN IMMEDIATE serializes writers, the sequence value a transaction will commit at is read before its closure runs, so a journal append inside the closure can key itself by that position without a second round trip. The counter still advances only for a transaction that mutated something, so a read-only outcome keeps both its absent position and its absent notification.

The shared catalog stores each complete workspace routing map as a structurally shared immutable tree and records its root in the routing generation. This keeps activation work bounded by tree depth on both SQLite and FoundationDB. Existing version-1 materialized routing records remain readable and are converted to the tree representation by the next application publication.

FoundationDB compatibility

FoundationDB is a later backend for the same contract. Backend conformance does not imply identical performance or failure distribution. FDB-specific queue sharding, tenant fairness, and watches may be added beneath or beside the common contract only if correctness remains expressible through durable transactions and scans.

Hosted key layouts avoid global read-modify-write keys and broad mutable range reads. Application manifests and workspace routing generations are built under immutable generation prefixes. A workspace has one small active routing-head key. Publishing an application manifest creates a new routing generation that replaces that application’s reference, then parent-fences and atomically swaps the application and workspace heads. A conflict caused by an unrelated app activation is retried by rebuilding over the newer workspace parent. A same-application conflict fails its stale expected parent rather than replacing that app’s newer manifest.

Event transactions read the workspace routing head serializably, then write uniquely addressed event, idempotency claim, and fan-out work. Concurrent events with different scoped idempotency keys therefore write disjoint keys and do not conflict with one another; they only read the same head. Submissions racing on the same key intentionally conflict and converge on one claim. A routing activation may correctly cause an in-flight first submission to retry against the newly serialized generation; later identical retries return that committed event without rereading the active head.

The logical routing generation is a complete immutable map from application to manifest generation. The shared implementation stores an immutable, structurally shared persistent tree and places its root reference in the generation record. Fan-out traverses that frozen catalog and each referenced manifest in bounded transactions. Both backends expose the same complete-snapshot semantics.

Ready work is sharded by stable tenant and run identity. Authoritative work state and its ready-index locator change in one transaction; no global sequence or queue-head key is required. Hosted dequeue may follow the FoundationDB-backed QuiCK pattern of lower-level queues, transactionally discoverable non-empty queue pointers, leases, and randomized bounded candidate selection. Backend versionstamps may optimize ordering inside the FDB adapter without becoming a domain requirement.

Transactions remain short and bounded. An event with unbounded cross-application trigger fan-out does not attempt to materialize every run in one commit. It commits one durable fan-out obligation referencing an immutable workspace routing generation, then expands application references and trigger bindings in deterministic, idempotent chunks. This preserves "no acknowledged event can lose its routing obligation" without exceeding FoundationDB transaction limits.

Concurrent events write disjoint event, fan-out, and sharded ready-work keys.

The optional hosted adapter uses the shared ordered transaction contract and a small audited process-global network boundary. libfdb_c remains dynamically linked only in FDB-enabled builds; production deployment packaging remains deferred until the local conflict and benchmark profiles prove the contract. The adapter uses the complete ten-byte FDB transaction versionstamp as its opaque commit position, preserving the intra-version transaction batch order without introducing a shared mutable sequence key. A journal append uses the same versionstamp inside a versionstamped key, which is the FDB-native commit-ordered append and needs no shared sequence. A read snapshot derives its bound from the transaction read version, so the boundary reflects the commits that snapshot can actually observe rather than the wall clock.

Performance and benchmark contract

One seeded workload harness exercises the storage contract and engine operations against SQLite and FDB profiles. It emits machine-readable results and records workload parameters, source revision, backend and FDB versions, dataset size, payload distribution, concurrency, duration, warm-up, machine topology, and process placement. A result without that profile is diagnostic only and cannot support a capacity or regression claim.

Local and CI runs prove structural behavior and detect regressions:

backend-independent workload implementations measure immutable routing catalog updates, trigger lookup, fan-out chunk construction, and bounded transactions;
SQLite profiles measure event acknowledgement, bounded fan-out, queue mutation, and backlog recovery using the production local adapter;
optional FDB profiles run those shared workloads plus conflict and transaction-boundary scenarios against a separately installed single-machine cluster. It is never bundled with or required by ryusen dev, and its throughput is not a hosted-capacity claim.

The backend-independent structural gates are:

unique event submissions, in the absence of a routing-head write, have no write conflicts with each other in the routing path;
no mutable key receives writes proportional to aggregate unique-event throughput; event, idempotency, fan-out, and ready-work writes remain scoped to the event or a configured shard;
trigger-page reads, fan-out mutation bytes, and mutation counts are bounded by the configured chunk size rather than total manifest bindings; selecting the next application follows the immutable routing tree and grows logarithmically with workspace application count;
intentional contention on one idempotency claim converges, and retry counts and completion latency remain observable;
application activation races never lose an app update, while event retries caused by the workspace-head write are measured separately from unrelated transaction conflicts.

Hosted performance claims require an isolated multi-machine FDB cluster that matches the intended production version, redundancy mode, storage engine, process classes, machine sizes, and network or availability-zone arrangement. Load generators run separately from database processes. The profile includes steady and burst event ingestion, deployment churn, concurrent app activation, low and extreme fan-out, deliberate same-key contention, and process or machine failure with recovery.

Every hosted run records accepted operations per second; p50, p95, and p99 acknowledgement and commit latency; transaction attempts, conflicts, retries, timeouts, duration, bytes read, and bytes written; writes by key range; and recovery throughput. Absolute throughput and latency gates belong to a named topology profile and must be set before claiming that profile is production ready. Local measurements use an explicit revision-relative regression budget; they must not be presented as context-free QPS promises.

Hosted evaluation proceeds in stages. First, one large machine establishes the client-concurrency curve with separate client and FDB processes, reserved or reported CPU placement, and concurrency points at 1, 2, 4, 8, 16, 24, 32, 64, and 128 where the machine supports them. Client concurrency is the number of in-flight workers, not a CPU allocation: workers may wait on reads and commits, and the client network thread and FDB process also consume cores. Every point therefore records client and server CPU utilization, memory, disk throughput and latency, network throughput, transaction attempts, conflicts, retries, and timeouts. The useful operating knee is where marginal throughput falls while tail latency or resource consumption rises, not necessarily the maximum QPS.

Second, a production-like profile separates load generators from a three- or five-machine FDB cluster, uses dedicated provisioned SSDs, and records replication, process classes, fault domains, and zone placement. It runs steady and burst ingestion, ordered disjoint writes, event acknowledgement, queue lifecycle and recovery, complete run execution, routing activation, low and extreme fan-out, same-key contention, and process or machine failure with recovery. Dataset size and run duration must be sufficient to exceed caches and expose steady-state storage and tail-latency behavior.

AWS and GCP profiles use the same revision, seed, workload parameters, and FDB version. Comparisons match vCPU count, memory, disk class and provisioned IOPS, network capability, and fault-domain count as closely as each provider permits; they record exact instance types, regions, zones, images, and disk settings. Ryusen establishes one named reference topology before mirroring it on the other provider, avoiding claims based on incomparable default configurations. Raw JSON results and provider monitoring exports accompany every published comparison.

External projections

The transactional backend is authoritative for active and immutable application manifest generations and workspace routing generations. The initial implementation retains every generation. A hosted deployment may atomically append an archive obligation when activating a generation and export canonical content, hashes, authorship, and diffs to ClickHouse or object storage. Export is idempotent and asynchronous; its delay or failure cannot affect event routing or execution.

PostgreSQL may later own management-plane concerns such as accounts, authentication, workspace membership, team roles, billing, API keys, or deployment approvals. It does not independently own the active workspace routing head, application manifest heads, trigger bindings, event associations, run state, or ready work. Routing heads must remain in the same transactional authority that acknowledges events and creates fan-out. Local development requires neither PostgreSQL nor ClickHouse.

PostgreSQL may own management data, but never independent routing or workflow authority.

Conformance

One backend suite verifies transaction atomicity, read-your-writes, rollback, range ordering and limits, conflict retries, commit-position uniqueness and strict ordering, restart durability, and correctness when every notification is dropped. It also verifies journal append commit ordering, per-transaction append order, strict catch-up semantics, partition isolation including a partition that is a packed prefix of another, page bounds with continuation, rollback of an appended entry, and that one snapshot bound reports exactly the commits that snapshot observes.

The suite also pages one transaction’s entries at every boundary, so a page that cuts a multi-entry commit must still deliver every entry exactly once, and it requires each entry of one commit to have its own position. Reading the journal through a write transaction after appending inside it is deliberately outside the contract, because SQLite can observe its own uncommitted insert while FoundationDB cannot read back a versionstamped key it has not committed.

The suite does not yet exercise concurrent snapshot interleaving or a backend restart mid-scan. The live catch-up fixtures that page and reconnect run on SQLite; the shared suite covers the storage primitive both backends implement.

The benchmark suite verifies the structural gates locally and publishes its profile with every result. FDB backend acceptance additionally requires the production-like hosted profile, its explicit SLO and regression budget, and raw artifacts sufficient to reproduce the reported percentiles and conflict rates.

Engine-level crash tests terminate execution after each transaction boundary and reopen the same database. They belong to the engine RFD but are required to validate this boundary in practice.

Alternatives considered

Separate domain repositories per database would permit backend-specific schema design but duplicate workflow algorithms and invite semantic drift. Pebble and bbolt are natural embedded ordered stores, but SQLite offers familiar tooling, crash recovery, and a sufficiently strong serialized transaction model for the local server. Pub/Sub or an embedded queue cannot be authoritative because it would recreate a non-transactional handoff.

Non-goals

This RFD does not define workflow records, the complete queue algorithm, production ClickHouse schemas, large-payload object storage, online schema migration, or one context-free throughput target shared by unlike deployment topologies.

Implementation

The interface, codec, SQLite adapter, and shared tests are tracked in RFD 3 implementation checklist.