Skip to main content

​
0003. SQLite with the pure-Go modernc.org/sqlite driver

  • Status: Accepted
  • Date: 2026-09-30

​
Context

The app is single-user and runs as one process. It stores settings, login sessions, a watchlist, saved layouts, an append-only order audit log, fills, alerts, and a market metadata cache that the command palette searches. Development happens on Windows and deployment on Linux, later as a single Docker image. The search index must answer in well under 300 ms (Phase 1 exit gate).

​
Decision

  • Use SQLite through modernc.org/sqlite, a pure-Go driver that needs no cgo.
  • The database lives in the directory set by DATA_DIR (data/ in development, ignored by git).
  • SQL migrations are embedded in the binary.
  • market_cache uses FTS5 for palette search.
  • Only store.Writer writes to the database (see ADR 0001). Other code reads.
  • Timestamps are stored as Unix milliseconds (UTC). Column names match the API field names.

​
Consequences

  • No C toolchain is needed on Windows, and cross-compiling for Linux and building the Docker image work with plain go build.
  • No separate database server to install, run or secure.
  • A single writer avoids lock contention between writers.
  • The pure-Go driver is slower than the cgo driver for heavy write loads. The expected load (one user, audit rows, fills, cache refreshes) is well within its range.
  • SQLite does not scale to many concurrent writers or several app instances. The app does not need either.

​
Alternatives considered

  • mattn/go-sqlite3 (cgo). Faster, but needs a C compiler on Windows and complicates cross-compilation and the Docker build.
  • PostgreSQL. More capable, but a separate server to run and secure for one user.
  • An embedded key-value store (bbolt, Badger). No SQL and no full-text search, so the palette index would need separate code.
  • Plain JSON files. No transactions, which an append-only audit log needs.