docs: add SQL migrations design spec

1 files changed, 66 insertions, 0 deletions

diff --git a/docs/superpowers/specs/2026-04-07-sql-migrations-design.md b/docs/superpowers/specs/2026-04-07-sql-migrations-design.md
new file mode 100644
index 0000000..8ce8622
--- /dev/null
+++ b/docs/superpowers/specs/2026-04-07-sql-migrations-design.md
@@ -0,0 +1,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 |