> ## Documentation Index
> Fetch the complete documentation index at: https://docs.sesameterminal.com/llms.txt
> Use this file to discover all available pages before exploring further.

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

# 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.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.