Skip to content

ADR 0006: SQLite as the store

  • Status: Accepted
  • Date: 2026-10-27 (proposed)
  • Accepted: 2026-10-28

Context

Horae must remember runs (inputs, step outputs, intents, applied changes, cost and status), keep a journal for undo, cache LLM responses for the $0 path and exact replays, and track tasks and notification state (§3.1). Its tenets ask for reproducibility and boring operations: "One container, SQLite, a CLI, /healthz, and push monitors for missed runs" (§2.1 tenets 6–7). The reference deployment is a small home server running Coolify, with horae as a Docker Compose resource that uses the Horae image plus plugin-livesync (§7).

Decision

Horae stores all run state in one SQLite database in the instance state directory; there is no database server.

  1. The store is SQLite, in the instance's state directory (§3.1). It holds:
  2. runs: one run record per execution;
  3. the journal: per-run record of applied changes, enough to undo them;
  4. the LLM cache, keyed on (skill version, model, normalized input);
  5. the task index and notification state.
  6. No database server. This follows the "One container, SQLite" tenet (§2.1 tenet 7); plugins run as subprocesses until M5.
  7. Backups of the state directory (with the vault) are taken with SQLite's online backup API or with the container stopped, with an offsite copy (§7).
  8. Privacy: run records keep LLM inputs locally only, with a configurable retention (§9).

The stored documents have contracts: the run record (schemas/run-record/v1.json, generated from src/horae/contracts/run.py) carries a schema_version, so rows written by one version can be read and migrated by the next. It records the inputs snapshot, per-step records, LLM calls (request, response, cached), proposed intents, applied changes, violations, cost and status (ok, noop, degraded, failed, undone), plus digests of the pipeline and, optionally, the lockfile and config. The models validate both JSON text and already-parsed data such as SQLite rows (schemas README). The table layout and the journal's undo format are not contracts yet; they belong to the M1 store story (M1).

Consequences

Positive

  • Zero operations: a file, no server, no credentials, no network; works on plain Docker and on Coolify.
  • Transactions keep the run record, journal and cache consistent. File changes in the vault are outside the transaction, so the engine still has to journal before it applies.
  • A backup is a consistent snapshot of one database file (plus the vault).
  • Python ships sqlite3, so no extra dependency is needed.

Negative

  • A single writer: concurrent runs (daily-brief and remind every 5 minutes, plus /act requests) serialize on writes and must handle busy timeouts.
  • The database file must live on local storage, not a network filesystem, and be backed up consistently (online backup API or a stopped container), not by copying a live file.
  • No multi-host scaling, which is acceptable because multi-user and SaaS hosting are non-goals (§2.3).
  • Recorded inputs and LLM requests contain personal data at rest; retention and the threat model (secret leaks into logs, §9) must cover the store.

Open questions

Alternatives considered

  • PostgreSQL. Concurrent writers and richer queries, but a second service to run, secure and back up, against the "one container" tenet.
  • Plain files (JSON per run). Simple and diffable, but no transactions, so journal-then-apply and the cache would need their own locking.
  • An embedded key-value store. Fast, but no SQL for horae runs, audit or the task index, and an extra dependency.

References