1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
|
# 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 |
|
