sis/docs/plans/2026-07-15-sqlite-database.md

SQLite Database Support Implementation Plan

Goal: Add SQLite database support using sqlite-simple, opening a database file on startup with WAL mode and foreign keys enabled.

Architecture: A single new module Sis.Database provides openDatabase, which opens/creates a SQLite file, enables WAL journal mode, and enables foreign keys. The server opens the DB on startup via a --db-path CLI option. No tables or queries yet.

Tech Stack: Haskell, sqlite-simple, sqlite-simple isn't yet in the project, optparse-applicative

---

Task 1: Add sqlite-simple dependency

Files:

• Modify: package.yaml

• Step 1: Add sqlite-simple to library dependencies

Add sqlite-simple to the library → dependencies section of package.yaml, in alphabetical order. The dependency block currently ends with - orb. Add before that line:

  - sqlite-simple

The relevant section should read:

library:
  source-dirs: src
  dependencies:
    - orb
    - sqlite-simple

• Step 2: Regenerate cabal file

Run: ./hs hpack Expected: exits 0, sis-server.cabal is regenerated with sqlite-simple in the library build-depends.

• Step 3: Verify it builds

Run: ./hs stack build Expected: successful build (though sqlite-simple may need to be fetched/built). If the build fails with "could not find module", the dependency may need to be in extra-deps in stack.yaml. If so, add it there.

• Step 4: Commit

git add package.yaml sis-server.cabal
git commit -m "deps: add sqlite-simple"

---

Task 2: Create Sis.Database module

Files:

• Create: src/Sis/Database.hs

• Step 1: Create the module

{- | SQLite database support for Sis.

Opens (and creates if missing) a SQLite database with WAL journal
mode and foreign keys enabled.
-}
module Sis.Database (
    openDatabase,
) where

import Database.SQLite.Simple qualified as SQL

-- | Open (or create) a SQLite database at the given path.
--
-- Enables WAL journal mode for concurrent read performance and
-- enables foreign key enforcement.
openDatabase :: FilePath -> IO SQL.Connection
openDatabase path = do
    conn <- SQL.open path
    SQL.execute_ conn "PRAGMA journal_mode=WAL"
    SQL.execute_ conn "PRAGMA foreign_keys=ON"
    pure conn

• Step 2: Build to verify compilation

Run: ./hs stack build Expected: builds successfully.

• Step 3: Commit

git add src/Sis/Database.hs
git commit -m "feat: add Sis.Database module with openDatabase"

---

Task 3: Wire --db-path CLI option and open database on startup

Files:

• Modify: app/Main.hs

• Step 1: Add import and --db-path option

In app/Main.hs, add the import:

import Sis.Database qualified as Sis

In the Options record, add a new field:

data Options = Options
  { optPort :: Int
  , optStaticDir :: FilePath
  , optDbPath :: FilePath
  }

In optionsParser, add the optDbPath parser after optStaticDir:

    <*> Opt.strOption
      ( Opt.long "db-path"
          <> Opt.metavar "PATH"
          <> Opt.help "Path to the SQLite database file"
          <> Opt.value "data/sis.db"
          <> Opt.showDefault
      )

• Step 2: Open database in main

In main, after opts <- ... and before let waiApp, add:

  _db <- Sis.openDatabase (optDbPath opts)

Note: the _db prefix suppresses the unused-binding warning. The connection is opened but not yet used — that comes in a later task when tables and routes are added.

• Step 3: Build and verify compilation

Run: ./hs stack build Expected: builds successfully with no warnings (all -Werror).

• Step 4: Commit

git add app/Main.hs
git commit -m "feat: add --db-path CLI option and open SQLite db on startup"

---

Task 4: Integration smoke test

Files:

• No new files

• Step 1: Start the server and verify database creation

Run: ./scripts/run

Expected output includes [sis] listening on port 8080.

In another terminal, verify the database file was created:

Run: ls -la data/sis.db Expected: file exists.

• Step 2: Shut down and restart — verify no error on existing DB

Stop the server (Ctrl+C), then restart:

Run: ./scripts/run

Expected: starts successfully, no errors. The existing data/sis.db is re-opened without issue.

• Step 3: Stop the server, clean up, and commit

Stop the server.

rm data/sis.db
git add -A && git status  # just to confirm no lingering changes
git commit -m "test: smoke test SQLite startup, DB file creation, and re-open" --allow-empty

Note: the --allow-empty flag is used since this task is a manual verification step with no code changes. If you prefer to skip committing manual verification, you can omit this commit.