App reference

ADR-0021: Dual-backend storage — SQLite default, PostgreSQL optional

Metadata

  • Status: Accepted
  • Date: 2026-08-08
  • Deciders: Eagraí Clainne Team
  • Related: ADR-0001 (JSONB storage and query convention — amended by this ADR), ADR-0003 (transactional balance checks), ADR-0016 (act_as containment scrub)

Context

Eagraí Clainne uses its database as an indexed document store: every table is uid PRIMARY KEY, data JSON, created_at, updated_at, the data model lives in protobuf, and the SQL surface is a handful of generic primitives in internal/database. For that shape, PostgreSQL brings a heavy runtime — the CNPG operator, a replicated cluster, credentials secrets, and a port-forward for the integration suite — that a single family's data does not need. An embedded engine makes the server self-contained: one process, one file, backup is a file copy, and per-family instances become cheap.

PostgreSQL still fits larger or already-provisioned deployments, and the existing unit-test suites pin its exact SQL. So the choice is not a migration but a second engine.

Decision

Storage grows a dialect seam (internal/database/dialect.go). Queries stay authored once, in Postgres placeholder style; a Dialect owns everything the engines disagree on:

  • SQLite is the default engine, via modernc.org/sqlite — pure Go, so the static distroless image and multi-arch release builds stay CGO-free. Config.Driver selects the engine ("sqlite" default, "postgres" opt-in).
  • Placeholders rebind. SQLite's $N parameters are named (numbered by first textual occurrence), not positional — SearchAudit binds $1 after $2..$4 — so the sqlite dialect rewrites $N to the truly positional ?N. The postgres dialect passes queries through byte-identically, which keeps every sqlmock suite green.
  • FOR UPDATE becomes the transaction lock. SQLite has no row locks (FOR UPDATE is a parse error). The DSN carries _txlock=immediate, so every transaction takes the database write lock at BEGIN — strictly stronger serialisation than Postgres row locks, so ADR-0003's balance check and every other locking read stay correct. busy_timeout(10000) parks a second writer instead of surfacing SQLITE_BUSY; WAL keeps readers concurrent. Should BUSY errors ever appear anyway, SetMaxOpenConns(1) is the documented fallback.
  • Containment translates per path. @> has no SQLite equivalent. The sqlite dialect walks the probe JSON and emits json_extract equality for object paths and EXISTS (SELECT 1 FROM json_each(...)) per array element — exact and case-sensitive, preserving ADR-0001's containment contract. Callers and probe shapes are untouched.
  • GIN indexes are dropped, scans accepted. SQLite cannot index into arrays without generated columns. Containment probes run as table scans; they only guard deletes and role checks on family-sized tables, so the cost is milliseconds. The expression indexes carry over verbatim (SQLite ≥3.38 ships Postgres-style ->/->>).
  • Schema per dialect. schema_sqlite.sql mirrors schema.sql with TEXT columns and CURRENT_TIMESTAMP defaults. Schema application is serialised by the dialect: the advisory lock on Postgres, a no-op on SQLite (the file write lock covers it).
  • JSON binds as TEXT. The sqlite dialect binds protojson payloads as strings; a []byte would land as a BLOB, which SQLite's JSON operators reject.

Known, accepted divergences:

  • lower() in SQLite folds ASCII only, while Postgres is locale-aware. The convention applies it to emails and UUIDs, where ASCII folding is enough.
  • SQLite deployments are single-writer: one server replica, Recreate deploy strategy, database file on a persistent volume.
  • The existing production install starts fresh on SQLite (reseeded); no data migration tooling is built.

Consequences

  • make deploy no longer needs the CNPG operator or a database cluster; the server mounts a PVC and owns its file. Backup is copying the file.
  • The integration suite runs hermetically against a temp SQLite file by default — no cluster, no port-forward; the Postgres path stays exercised via EAG_TEST_DB_DRIVER=postgres.
  • Real-SQLite behavioural specs (sqlite_db_test.go) cover what sqlmock cannot: the schema actually applies, rebound placeholders bind, locking reads carry no FOR UPDATE, and containment matches @> semantics.
  • Every future query must be authored in Postgres placeholder style and go through the dialect's Rebind; engine-specific SQL belongs in a dialect method, never in a primitive.

Alternatives considered

  • Full replacement (drop Postgres). Simplest long-term, but discards a working production path and the sqlmock suites' value as a pinned SQL contract; larger deployments may still want Postgres.
  • Go-side containment scans (fetch rows, match in Go). Simpler than the translator and equivalent at family scale, but it moves matching semantics into application code and gives up SQL-side counting; the translator keeps the probe contract in one place and the data in the database.
  • mattn/go-sqlite3. Battle-tested and faster, but CGO would break the static distroless image and complicate multi-arch release builds.
  • Generated columns + indexes for containment paths. Restores index service for probes, at the cost of schema churn per new probe shape — not worth it while probes only guard deletes.