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

```yaml
  - sqlite-simple
```

The relevant section should read:

```yaml
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**

```bash
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**

```haskell
{- | 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**

```bash
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:

```haskell
import Sis.Database qualified as Sis
```

In the `Options` record, add a new field:

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

In `optionsParser`, add the `optDbPath` parser after `optStaticDir`:

```haskell
    <*> 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:

```haskell
  _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**

```bash
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.

```bash
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.