sis/docs/specs/2026-07-15-sqlite-database-design.md

SQLite Database Support

Overview

Add SQLite database support to Sis using the sqlite-simple library. On startup, the server opens a SQLite database (creating the file if it doesn't exist), enables WAL mode and foreign keys, and makes the connection available. No tables are created yet — that comes in a later task.

Architecture

New dependency

• sqlite-simple added to package.yaml.

New module: Sis.Database

Exposes one function:

openDatabase :: FilePath -> IO Connection

Behavior:

• Opens (or creates) the SQLite database file at the given path.
• Enables WAL journal mode (PRAGMA journal_mode=WAL).
• Enables foreign keys (PRAGMA foreign_keys=ON).
• Returns the Connection.

No tables are created in this task. The file will contain only the empty SQLite schema.

Server wiring

• New CLI option --db-path in app/Main.hs with default value data/sis.db.
• main opens the database connection via Sis.Database.openDatabase before starting the Warp server.
• The connection is not yet passed into the Orb app — that wiring happens when the first table and routes are added in subsequent tasks.

Data flow

startup → parseOptions → openDatabase (creates file, sets pragmas)
                       → start Warp server (connection held, unused for now)

Error handling

• If openDatabase fails (e.g., unwritable path, disk full), the exception propagates and the server fails to start. This is correct — the server cannot function without its database.

Testing

• The existing spec test suite does not require changes since there are no new routes or business logic.
• Integration-level tests for database operations will be added when tables and queries are introduced in subsequent tasks.