# SQL Migrations Design

**Date:** 2026-04-07
**Status:** Approved

## Goal

Replace the inline `CREATE TABLE IF NOT EXISTS` block in `internal/db/db.go` with versioned SQL migration files managed by `golang-migrate/migrate`.

## Constraints

- Migrations run automatically at startup (inside `db.Open`) — no CLI subcommand needed.
- Migration files are embedded into the binary via `embed.FS`.
- Continue using the existing `modernc.org/sqlite` driver.
- Existing databases can be discarded (clean start acceptable).

## Migration Files

New directory `internal/db/migrations/` holds numbered SQL files:

```
internal/db/migrations/
  000001_init.up.sql    — full current schema (tables, indexes, FTS5 virtual table)
  000001_init.down.sql  — DROP TABLE / DROP INDEX statements in reverse order
```

Future schema changes each get their own numbered pair. golang-migrate applies them in order and records applied versions in a `schema_migrations` table it manages automatically.

## Embedding

An `//go:embed migrations` directive in `internal/db/db.go` (package `db`) exposes the directory as an `embed.FS` variable `migrationsFS`.

## Changes to `db.Open`

After `sql.Open`, the existing inline `Exec` block is removed and replaced with:

```go
src, err := iofs.New(migrationsFS, "migrations")
// handle err
driver, err := sqlitedriver.WithInstance(sqldb, &sqlitedriver.Config{})
// handle err
m, err := migrate.NewWithInstance("iofs", src, "sqlite", driver)
// handle err
if err := m.Up(); err != nil && err != migrate.ErrNoChange {
    return nil, err
}
```

`ErrNoChange` is silently ignored (DB is already at latest version).

## New Dependencies

| Package | Purpose |
|---|---|
| `github.com/golang-migrate/migrate/v4` | Core migration engine |
| `github.com/golang-migrate/migrate/v4/database/sqlite` | modernc.org/sqlite driver adapter |
| `github.com/golang-migrate/migrate/v4/source/iofs` | `embed.FS` migration source |

## File Changes

| File | Change |
|---|---|
| `internal/db/db.go` | Add `//go:embed migrations`, replace inline Exec with migrate wiring |
| `internal/db/migrations/000001_init.up.sql` | New — current schema |
| `internal/db/migrations/000001_init.down.sql` | New — reverse of init |
| `go.mod` / `go.sum` | Add three golang-migrate packages |