michal/tit
Browse tree · Show commit · Download archive
Blob: docs/adr/0001-sqlite-storage.md
# Architectural decision record 0001: SQLite storage Status: Provisional Date: 2026-07-22 ## Context `tit` needs one inspectable metadata database in the instance directory. The database must have transactions, constraints, indexes, online backup, and a clear recovery procedure. The executable must not need an installed database server or an installed SQLite shared library. ## Decision Use `rusqlite` with the `bundled` and `backup` features. Keep SQL and `rusqlite` types in the `store` module. Use numbered SQL migrations and `PRAGMA user_version`. Apply all pending migrations in one exclusive transaction. Create an online backup before an automatic migration of an existing database. Use WAL mode, `synchronous=FULL`, a five-second busy timeout, and foreign-key enforcement on each connection. Verify each setting. Use `tit.sqlite3` as the metadata database name. Require a local filesystem until a platform gate proves that a different filesystem has correct WAL behavior. `tit doctor` opens an existing database without the create flag. It does not migrate the database. It verifies the schema version, SQLite integrity, and foreign keys. ## Evidence The local gate used Rust 1.96.0 on arm64 macOS 27.0 and a local APFS filesystem. It killed child processes during writes and schema migrations. After each kill, the database contained the initial full state or the new full state. Tests also proved constraints, indexed scans, concurrent reads, serialized writes, busy handling, rollback, WAL checkpoint, vacuum, online backup, restore, and migration from each committed fixture. The release workload used 10,000 parent records as issues and 1,000,000 child records as events. The measured database size was 113,926,144 bytes. A safe migration, including its required backup, took 774 ms. A subsequent online backup took 509 ms. For 1,000 indexed queries that each read 25 events, the 50th, 95th, and 99th percentile times were 4, 5, and 8 microseconds. The workload gate has these limits: - the database must not be larger than 1 GiB. - migration and backup must each complete in 120 seconds. - the 99th percentile query time must not be more than 250 ms. The GitHub Actions release matrix runs the functional and workload gates on Linux and macOS. This decision stays provisional until both hosted runners pass. BSD and non-local filesystem support stay outside the current supported platform set. ## Consequences SQLite adds a small amount of explicit SQL, but it gives standard inspection and recovery tools and a stable file format. Bundled SQLite increases the executable size, but it removes variation in the runtime SQLite version. The application has one serialized SQLite writer. WAL lets readers continue during a write. If the measured workload becomes too large for this model, the project must review the evidence before it adds a database server, a connection pool, or another persistence abstraction.