ADR-002: Pure Go SQLite via modernc.org

Status: Accepted
Date: 2026-05-09

Context

Embercore’s engine needs a local, embedded database for persisting plans, checkpoints, runs, and artifacts. The storage layer (Hestia agent + internal/store/) requires:

• Zero-config setup (no external database server — local-first philosophy)
• Concurrent write support (multiple agents may record results simultaneously)
• Cross-platform distribution (single binary for Linux, macOS, Windows)

The two main Go SQLite options are:

1. mattn/go-sqlite3 — CGo binding to the C SQLite library. Fast, battle-tested, but requires a C compiler and CGo enabled.
2. modernc.org/sqlite — Pure Go translation of the SQLite C source. No CGo, slightly slower, but fully portable.

Decision

We use modernc.org/sqlite v1.50.0 as our SQLite driver (registered as "sqlite" for database/sql).

Key configuration in internal/store/store.go:

db, err := sql.Open("sqlite", dbPath)
// Enable WAL mode for concurrent agent writes
db.Exec("PRAGMA journal_mode=WAL")
// Enforce referential integrity
db.Exec("PRAGMA foreign_keys=ON")

WAL mode

Write-Ahead Logging allows concurrent reads and writes without full database locks. This is critical because:

• Hermes may be executing plan steps while Hestia records checkpoint state
• The web dashboard reads plan/run status while the engine writes progress
• Multiple tool handlers in the MCP server may write artifacts concurrently

Migration system

Schema is managed via internal/store/migrations.go with a migrations table tracking applied versions. Tables include:

Table	Purpose
plans	Serialized plan specs and metadata
checkpoints	Step-level approval/rejection records
runs	Execution runs with state tracking
artifacts	Generated output files and content
migrations	Schema version tracking

Consequences

Benefits:

• CGO_ENABLED=0 go build produces a fully static binary — no shared library dependencies
• Cross-compilation works out of the box (GOOS=linux GOARCH=arm64 go build)
• No C compiler required in CI or on contributor machines
• WAL mode provides adequate concurrency for local-first, single-user workloads

Trade-offs:

• ~10–20% slower than CGo mattn/go-sqlite3 for write-heavy workloads (acceptable for our use case — plan/checkpoint writes are infrequent relative to LLM API latency)
• modernc.org/sqlite is a mechanically translated C-to-Go codebase, making upstream debugging harder
• WAL mode requires filesystem support (won’t work on certain network filesystems)

Related decisions:

• ADR-001 — Cross-compilation is a key benefit of the Go + monorepo choice
• ADR-005 — Checkpoints and plan state are stored in SQLite