Committed Policy Convergence with SQLite and Raft

Philip O'Toole deserves enormous credit for the engineering behind rqlite. Combining SQLite with Raft in a compact, fault-tolerant relational database is an outstanding open-source contribution, particularly useful for systems that need durable coordination over a modest amount of mutable state. I am grateful to Neil O'Toole, creator of sq, for introducing me to rqlite. What I particularly admire about rqlite is the architectural opportunity it provides to build highly available services around SQLite while making the database's commitment model explicit.

An authorization control plane is a useful example. Its mutable state may consist of resource bindings, revocations, and sensitivity restrictions, yet an administrator changing that state needs to know when decision services have adopted it. Each region can host active administration and decision services, while Raft establishes one committed history of policy changes. Request processing can use local snapshots in memory.

This arrangement makes committed-policy convergence a cornerstone of the design. Database replication establishes agreement among database replicas, but authorization depends on the state actually loaded by application processes. The application needs a protocol connecting committed revisions to those snapshots, with evidence of installation across the expected fleet. Evaluating SQLite/Raft, Cassandra, or CockroachDB for this workload therefore requires examining both storage guarantees and the application protocol built above them.

Define the policy workload precisely

The database in this design stores a small mutable authorization overlay. It does not store the metrics, logs, or traces being protected, and quota accounting is separate. The size and write rate of the observability corpus consequently provide little guidance for sizing this policy database.

Roles from validated tokens supply capabilities. The overlay adds resource bindings, revocations, and sensitivity restrictions that influence whether a capability permits a particular operation. In the request path, Envoy performs authentication, invokes external authorization, applies quota enforcement, and then routes the request. The decision service reads policy from memory.

This separation gives policy administration and request processing different availability properties. A database interruption can prevent new policy commits while an already initialized decision process continues evaluating requests against its installed state. Whether that state remains acceptable is a freshness question that must be addressed explicitly; successful request processing alone does not establish that revocations have propagated.

Regional write entry and global commitment

An administrator can submit a change to an active service in any participating region. That service sends the write to its local rqlite endpoint. If the endpoint is a follower, rqlite transparently forwards the request to the elected leader and returns the leader's response. The client therefore does not need to track leadership itself.

All policy commits still pass through one Raft write authority. Regional entry points provide distributed access to that authority, but cannot independently commit conflicting histories during a partition. This arrangement supports highly available policy writes within the cluster's quorum and connectivity limits, with write latency affected by the communication needed to reach the leader and replicate the change.

Four voters require three votes. Losing one voter permits continued commitment if the remaining three can communicate and establish leadership. A two-versus-two partition prevents either side from committing policy changes. The fourth voter does not increase tolerated voter failures beyond one, and regional failure tolerance depends on how voters are placed.

Application availability must be assessed separately during such a partition. Initialized decision processes may retain usable snapshots even while policy writes are unavailable, but they cannot infer freshness from their ability to authorize requests. Processes that are starting, recovering, or missing required policy state present different conditions from processes already serving with an installed snapshot.

Connect committed revisions to local snapshots

A policy change passes through several independently observable stages. It commits in the database, becomes applied state on a local replica, and is loaded into a decision process before that process publishes its completed snapshot. Installation evidence reaches the status mechanism afterward. A write acknowledgment cannot establish that every subsequent stage has completed.

The revision identifies both a database incarnation and a generation within that incarnation. The incarnation distinguishes database histories, so that a generation from one history cannot be mistaken for the same number in another. Transactional triggers advance the generation when mutable overlay state changes, coupling the revision to the mutation within the database transaction.

A consumer loads revision metadata and overlay rows together in one coherent transaction. It constructs a completed snapshot and publishes it through an atomic local pointer swap. Reading metadata separately from policy rows could otherwise attach a newer revision to older data, producing misleading evidence even if every individual query succeeded.

Change data capture accelerates this process. One relay accompanies each voter, and every decision process subscribes to every relay because native CDC delivery follows the database leader. These subscriptions allow consumers to receive notifications through the relay associated with whichever voter currently leads.

A notification is a hint to investigate committed state. The relay resolves the committed generation through a linearizable metadata read, and consumers use that revision as a requirement for reconciliation. Policy rows continue to replicate through Raft; the relay does not establish another replication mechanism by replaying CDC row events into independent databases.

Each consumer retains the highest required generation within the current incarnation and loads from its local replica. If that replica has not yet applied the required revision, the requirement remains pending. Receiving a notification cannot make lagging rows available, so prompt notification does not eliminate replication delay.

The requirement must also be captured before a load begins. Suppose generation 40 is required when a consumer starts reading, and generation 41 is announced during the load. A coherent snapshot at generation 40 can still be installed, with 41 remaining pending for another reconciliation. Continually rejecting completed snapshots because a newer notification arrived could prevent installation under sustained updates.

Periodic reconciliation remains necessary alongside CDC. A 1500 ms interval repairs missed notifications and refreshes policy whose interpretation changes with time. That interval is a scheduling choice, not a hard convergence bound under failures, replica lag, or scheduling delays. Notifications reduce avoidable waiting when the relevant components are healthy.

Establish what installation evidence means

An installation acknowledgment identifies the authenticated workload, its process or boot session, an active lease, and the installed revision. The session distinguishes successive executions of the same workload identity. Lease validity and recent reporting keep historical acknowledgments from being treated indefinitely as evidence about a currently operating process.

Fleet status also requires an explicit expected inventory. Collecting acknowledgments from whichever processes happen to respond cannot establish that every expected decision process has installed a revision. A missing consumer must remain visible as missing evidence in the population being assessed.

These acknowledgments are trusted workload reports. They do not remotely attest that a process executed particular instructions, and they do not prevent another policy write immediately after the status check. A convergence result therefore describes the available installation evidence for a specified revision and expected population at the time of observation.

Snapshot installation also has a narrower meaning than linearizable authorization. Atomic publication makes a completed snapshot available, but the current decision reader does not pin one generation across every predicate evaluated for a request. Different predicates can consequently observe different published generations if installation occurs between their reads.

Revocation has a further boundary at work already admitted. Updating a snapshot influences subsequent authorization evaluation; it does not cancel operations already in flight. An application requiring cancellation or a stronger request consistency guarantee needs an additional mechanism, and installation acknowledgments should not be presented as providing either behavior.

Interpret the recorded qualification narrowly

The recorded local qualification used four voters and eight consumer instances, with the consumers running inside one Go test process on one Linux host. It exercised 1000 sequential revocations. For each sample, measurement began at write acknowledgment and ended when installation was observed in all eight consumer snapshots.

The recorded p50 was 125.2 ms, p95 was 210.2 ms, and p99 was 255.1 ms. The maximum was 342.4 ms, with 14 samples exceeding 250 ms. Observation used 5 ms polling and could incur additional scheduling delay. The endpoint was observed snapshot installation, not receipt of installation acknowledgments by a relay.

These results show the observed convergence distribution for that local qualification. They do not measure WAN behavior, authorization latency, or sustained write throughput, and they do not establish a production service-level objective. The raw samples were recalculated during preparation of the underlying analysis; the benchmark was not rerun while writing.

Sequential changes also leave concurrency and backlog behavior largely outside the measurement. A regional qualification would need to examine the intended network topology, failure conditions, consumer population, and policy size. The local result supports further evaluation of the protocol without supplying those missing measurements.

Implementing equivalent behavior with Cassandra

Cassandra 5.0 provides a materially different starting point for ordinary writes. Clients choose consistency levels, while timestamp-based conflict resolution reconciles competing mutations. With LOCAL_QUORUM, acknowledgments depend on a majority of replicas in the local datacenter. Consequently, isolated datacenters can each continue ordinary writes when each retains its required local majority. Cassandra's architecture documentation describes these consistency and versioning rules.

That behavior can be valuable for workloads designed to tolerate reconciliation between independently accepted updates. For globally governed policy transitions, however, the application must define how concurrent changes become one coherent published revision. Timestamp conflict resolution alone does not establish the same publication contract as a single Raft-ordered policy history. Comparing the two requires preserving this difference in partition behavior.

Lightweight transactions provide conditional operations backed by consensus and can participate in such a design. Their serial consistency scope must match the intended coordination boundary, and policy layout must respect partition boundaries. A logged batch spanning partitions does not supply globally isolated visibility of an entire policy update. The CQL documentation specifies isolation within a partition and distinguishes batches from SQL transactions.

One possible design would store immutable policy bundles and publish a manifest through a conditional update. Writers would construct a complete bundle, establish its required durability, and conditionally advance the manifest from the expected previous revision. Consumers would read the published manifest, load the referenced bundle, verify completeness, and then install it.

That proposal moves substantial responsibility into application engineering. The implementation must define globally appropriate serial coordination, safe concurrent publication, retry behavior, and retention of bundles that readers may still need. If publication is globally ordered, its availability must be evaluated under that coordination rule; ordinary LOCAL_QUORUM write availability does not establish availability of manifest publication.

Cassandra CDC introduces a separate set of operational concerns. It exposes node-local commit-log material for consumers, requiring management of consumption progress and retained data. Its CDC documentation describes the segment files, durable offsets, and storage limits involved. A policy notification service would need to account for duplicate observations, node changes, recovery, and database repair obligations.

Cassandra can support this policy system, especially where an established deployment already supplies operational expertise and capacity. The relevant comparison includes the effort needed to produce coherent published revisions and recover notification delivery. Consumer snapshot construction, periodic reconciliation, and authenticated installation evidence would still be required above the database.

Implementing equivalent behavior with CockroachDB

CockroachDB v26.3 is closer to the transactional requirements of this overlay. Serializable transactions can update policy rows and revision metadata together across tables, giving consumers a coherent database state to load. Applications must handle transaction retries where the database cannot retry transparently. The transaction documentation describes these guarantees and retry responsibilities.

Current trigger support also matters to the comparison. CockroachDB supports row-level triggers, which can maintain revision metadata when policy tables change. A transactional trigger design can therefore preserve policy-plus-revision atomicity without relying exclusively on each administrative caller to remember a separate update. The v26.3 trigger documentation describes the supported row-level operations.

Changefeeds can deliver notifications through HTTPS webhooks, so Kafka is not a mandatory dependency for this design. A webhook receiver could notify decision processes that their installed revision needs checking. The notification transport can remain relatively small when a broader streaming platform is unnecessary.

Changefeed semantics still require care. Delivery is at least once, and ordering applies per key to first emissions; there is no total or transactional ordering across all messages. Duplicates can arrive out of order. The changefeed message guarantees make these boundaries explicit. Watching the revision record and rereading a coherent policy snapshot avoids treating an arbitrary collection of row events as a completed policy transaction.

CockroachDB distributes data across ranges with Raft replication and leaseholders, providing scaling and locality options beyond a single replicated SQLite database. A single global generation row would nevertheless remain a contention point if every policy mutation updates it. Distributing storage does not remove coordination deliberately introduced by the application schema.

Regional survival must be compared using equivalent failure objectives and replica placement. Counting nodes without considering where range replicas reside would produce a misleading comparison with a four-voter policy cluster. Likewise, distributed SQL transactions do not establish that every decision process has refreshed its in-memory state; installation reporting and repair remain application responsibilities.

CockroachDB becomes particularly attractive when policy belongs within an existing relational platform or needs broader transactional relationships. A managed shared deployment may also lower incremental operational effort. Those advantages should be assessed alongside the actual policy workload and convergence protocol, without assuming that additional database capabilities are either necessary or wasteful in every environment.

Assess footprint and operating cost together

A compact store is a reasonable fit when the overlay remains bounded, full snapshots are inexpensive, and policy writes fit comfortably within one ordered authority. Keeping request-time reads in memory allows decision capacity to grow without requiring a database read for every authorization check. The policy store can consequently be sized around administration, replication, and reconciliation activity.

The source resource illustration assigned the four voters, including relays and exporters, aggregate scheduler requests of 0.32 CPU and 448 MiB. Including declared mesh resources raised those totals to 0.72 CPU and 960 MiB. Four 8 GiB volumes provided 32 GiB of logical allocation. These figures describe declared resources for that limited scope, without establishing measured usage, adequate production sizing, billed cost, or the footprint of the complete gateway.

A cost assessment must include compute, storage, I/O, network traffic, and backup retention, together with applicable licensing or support. Engineering and operational labor also matter. A small database still requires tested restoration, upgrades, capacity assessment, and maintenance of the convergence protocol; reducing the substrate does not remove those responsibilities.

For a new, bounded policy service, SQLite/Raft may avoid introducing a larger general-purpose database platform. Where Cassandra or CockroachDB already operates as a shared service, either could have the lowest marginal cost. No comparative benchmark or financial model here establishes a percentage saving or universal cost advantage.

I would choose the compact design when its write authority, snapshot model, and failure boundaries match the required service. An existing broader database may be preferable when shared operations or additional data relationships justify it. In either case, the engineering assessment must include how committed revisions reach decision processes, how missing installation evidence is represented, and what freshness the application can actually promise.

Full white paper

This post condenses the longer white paper, WP-01: Highly Available Observability Access with SQLite and Raft. The paper covers the full design in more depth than this post, including the policy workload, regional write entry over a single Raft write authority, the committed-revision to snapshot protocol with change data capture and periodic reconciliation, what installation evidence does and does not establish, the recorded local qualification and its limits, the Cassandra and CockroachDB comparisons, and footprint and operating cost.

The paper is a single self-contained HTML file with no scripts and no external assets; it opens in any browser from disk.