correx/docs/decisions/adr-0005-persistence-choice.md

---

name: "Adr 0005 Persistence Choice" description: "SQLite primary event store, deferred Redis/Elasticsearch" depth: 2 links: ["../index.md", "../modules/core-events-submodule-spec.md"

ADR 0005: persistence choice – SQLite primary event store with deferred Redis and Elasticsearch

status: accepted
date: 07.05.2026 (May) deciders: correx design team

---

context

Correx requires an append‑only event store as the system’s authoritative source of truth. The store must support:

• strict per‑session ordering and idempotent writes
• efficient replay via sequence‑range scans
• atomic multi‑event transactions
• zero‑setup, embedded operation for local‑first deployment
• a design that allows future migration to distributed backends

We evaluated SQLite, MongoDB, RocksDB, Redis, Kafka, and PostgreSQL against these requirements, with an emphasis on simplicity for v1 while preserving architectural flexibility.

decision

SQLite with WAL mode is the primary event store for correx v1.

All events, snapshots, and materialised projections will be stored in a single local SQLite database. The :core modules will define repository interfaces (EventStore, SnapshotStore); :infrastructure:persistence:sqlite will provide implementations.

rationale for SQLite

• Embedded, zero‑setup – critical for a local‑first tool; no external daemons.
• WAL mode – allows concurrent reads from multiple processes (e.g., CLI, dashboard) while the orchestrator writes.
• Single‑file portability – easy backup, archival, and replay.
• Sufficient write performance – session events are written sequentially and rarely exceed a few thousand per session; SQLite’s write throughput is more than adequate.
• Mature Kotlin/Java support via sqlite-jdbc and optional query libraries (Exposed).
• Familiarity – simpler mental model for contributors; SQL inspection during development is straightforward.

event storage schema

A single events table:

column	type	description
session_id	text	UUID of the session
sequence	integer	strict monotonic ordering within session
event_id	text	globally unique event UUID
timestamp	text	ISO‑8601 creation time
type	text	event category and name
payload	text	JSON blob (schema versioned)
causation_id	text	parent event UUID
correlation_id	text	execution lineage identifier
version	integer	event schema version

Index: (session_id, sequence) unique. Queries: range scans by session with cursor.

snapshotting

Trigger policy: semantic, not purely count‑based.

Snapshots are created after key “snapshot‑material” events:

• SessionInitialized
• StageCompleted
• ArtifactValidated
• ApprovalGranted
• SessionPaused

plus a fallback max‑event count (default 200) to prevent replay of unbounded gaps. A minimum interval (30 seconds) prevents snapshot storms.

Snapshots are stored in a dedicated table, keyed by (session_id, sequence) and contain the serialized current projection state. They are always an optimisation; the event stream remains authoritative. Replay always applies events after the snapshot cursor, including any destructive or compensating events, so no state is silently lost.

log‑event correlation

All external logs (tool stdout/stderr, inference debug) carry the corresponding event_id and correlation_id in their output. Tool receipts stored as ToolExecutionCompleted event payloads include a file path to the raw log. Drift between logs and events is prevented by design: events are written atomically first; logs reference those events.

future backends (v2+)

We explicitly note two additional data stores that are not part of v1 but are planned as optional infrastructure modules later:

• Redis – for in‑process pub/sub streaming of events to real‑time dashboards, CLI progress, and live inspection. Redis is not a store of record; events remain in SQLite.
• Elasticsearch – as a secondary read‑model for full‑text search across artifact summaries, rationales, and tool outputs, and for observability queries (e.g., “show all sessions where approval tier T3 was used”). Events are pushed asynchronously via a projection subscriber; Elasticsearch is not the primary store.

Both are optional, additive, and do not alter the core EventStore contract.

consequences

positive:

• zero‑setup local experience; the harness “just works” after install
• event store is inspectable with standard SQL tools during development and debugging
• semantic snapshotting ensures fast replay without capturing inconsistent intermediate states
• event‑first design prevents log/event drift
• clean migration path to PostgreSQL, RocksDB, or distributed backends via the EventStore interface

negative:

• SQLite’s single‑writer limitation means concurrent sessions must be serialised at the persistence layer (acceptable for single‑node v1; future versions will require a different backend for distributed workers)
• JSON payloads in SQLite lack native JSONB indexing; complex queries over event payloads are slower than in PostgreSQL or Elasticsearch (mitigated by keeping queries simple: always by session + sequence range)
• snapshot material events must be chosen conservatively; if an important event type is omitted, replay may be slower than ideal (adjustable via config)

alternatives considered

• RocksDB (embedded KV): excellent write throughput and natural append‑only model; rejected because SQLite’s inspection friendliness and ecosystem maturity better suit a small team iterating rapidly.
• MongoDB / PostgreSQL / Kafka: require external daemons, violating local‑first simplicity. They remain strong candidates for v2 distributed deployments.
• Redis as primary store: insufficient durability guarantees for source‑of‑truth event storage; retained as a pub/sub transport for real‑time events.

status

This decision governs the v1 persistence architecture. Re‑evaluation is expected when distributed workers, high‑volume concurrent sessions, or advanced full‑text search become hard requirements.