1 files changed, 231 insertions, 0 deletions
diff --git a/docs/superpowers/plans/2026-04-07-sql-migrations.md b/docs/superpowers/plans/2026-04-07-sql-migrations.md
new file mode 100644
index 0000000..60b2848
--- /dev/null
+++ b/docs/superpowers/plans/2026-04-07-sql-migrations.md
@@ -0,0 +1,231 @@
+# SQL Migrations Implementation Plan
+
+> **For agentic workers:** REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development (recommended) or superpowers:executing-plans to implement this plan task-by-task. Steps use checkbox (`- [ ]`) syntax for tracking.
+
+**Goal:** Replace the inline `CREATE TABLE IF NOT EXISTS` block in `internal/db/db.go` with versioned SQL files managed by `golang-migrate/migrate`, applied automatically at startup.
+
+**Architecture:** Migration files live in `internal/db/migrations/` and are embedded into the binary via `embed.FS`. `db.Open` calls `migrate.Up()` after opening the database, ensuring the schema is always current before any caller uses `*DB`. golang-migrate tracks applied versions in a `schema_migrations` table it manages automatically.
+
+**Tech Stack:** `github.com/golang-migrate/migrate/v4`, `database/sqlite` (modernc.org/sqlite adapter), `source/iofs` (embed.FS source)
+
+---
+
+## File Map
+
+| File | Action | Purpose |
+|---|---|---|
+| `internal/db/migrations/000001_init.up.sql` | Create | Full current schema |
+| `internal/db/migrations/000001_init.down.sql` | Create | Drop all tables in reverse order |
+| `internal/db/db.go` | Modify | Add `//go:embed`, wire up migrate, remove inline `Exec` block |
+| `go.mod` / `go.sum` | Modify | Add golang-migrate packages |
+
+---
+
+### Task 1: Add golang-migrate dependency
+
+**Files:**
+- Modify: `go.mod`, `go.sum`
+
+- [ ] **Step 1: Fetch the packages**
+
+```bash
+go get github.com/golang-migrate/migrate/v4
+go get github.com/golang-migrate/migrate/v4/database/sqlite
+go get github.com/golang-migrate/migrate/v4/source/iofs
+```
+
+- [ ] **Step 2: Verify**
+
+```bash
+go list -m github.com/golang-migrate/migrate/v4
+```
+
+Expected output: `github.com/golang-migrate/migrate/v4 v4.x.x`
+
+- [ ] **Step 3: Commit**
+
+```bash
+git add go.mod go.sum
+git commit -m "chore: add golang-migrate dependency"
+```
+
+---
+
+### Task 2: Create migration SQL files
+
+**Files:**
+- Create: `internal/db/migrations/000001_init.up.sql`
+- Create: `internal/db/migrations/000001_init.down.sql`
+
+- [ ] **Step 1: Create the up migration**
+
+`internal/db/migrations/000001_init.up.sql`:
+
+```sql
+CREATE TABLE pages (
+    id         INTEGER PRIMARY KEY AUTOINCREMENT,
+    path       TEXT NOT NULL UNIQUE,
+    html_path  TEXT NOT NULL,
+    title      TEXT NOT NULL DEFAULT '',
+    date       TEXT DEFAULT '',
+    tags       TEXT DEFAULT '[]',
+    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
+);
+CREATE INDEX idx_pages_path ON pages(path);
+CREATE INDEX idx_pages_date ON pages(date);
+
+CREATE TABLE posts (
+    id         INTEGER PRIMARY KEY AUTOINCREMENT,
+    slug       TEXT NOT NULL UNIQUE,
+    title      TEXT NOT NULL DEFAULT '',
+    date       TEXT DEFAULT '',
+    tags       TEXT DEFAULT '[]',
+    draft      INTEGER NOT NULL DEFAULT 0,
+    blocks     TEXT NOT NULL DEFAULT '[]',
+    updated_at INTEGER NOT NULL DEFAULT (cast(strftime('%s','now') * 1000000 as integer))
+);
+CREATE INDEX idx_posts_slug ON posts(slug);
+CREATE INDEX idx_posts_date ON posts(date);
+
+CREATE TABLE redirects (
+    from_slug TEXT PRIMARY KEY,
+    to_slug   TEXT NOT NULL
+);
+
+CREATE TABLE settings (
+    key   TEXT PRIMARY KEY,
+    value TEXT NOT NULL DEFAULT ''
+);
+
+CREATE VIRTUAL TABLE pages_fts USING fts5(
+    path      UNINDEXED,
+    title,
+    content,
+    tokenize = 'porter unicode61'
+);
+```
+
+Note: `IF NOT EXISTS` is intentionally absent — golang-migrate guarantees each migration runs exactly once.
+
+- [ ] **Step 2: Create the down migration**
+
+`internal/db/migrations/000001_init.down.sql`:
+
+```sql
+DROP TABLE IF EXISTS pages_fts;
+DROP TABLE IF EXISTS settings;
+DROP TABLE IF EXISTS redirects;
+DROP TABLE IF EXISTS posts;
+DROP TABLE IF EXISTS pages;
+```
+
+- [ ] **Step 3: Commit**
+
+```bash
+git add internal/db/migrations/
+git commit -m "feat: add initial schema migration files"
+```
+
+---
+
+### Task 3: Wire golang-migrate into db.Open
+
+**Files:**
+- Modify: `internal/db/db.go`
+
+- [ ] **Step 1: Run existing tests to confirm baseline**
+
+```bash
+go test ./internal/db/... -v
+```
+
+Expected: all tests pass.
+
+- [ ] **Step 2: Replace db.go**
+
+Full new content of `internal/db/db.go`:
+
+```go
+package db
+
+import (
+	"database/sql"
+	"embed"
+
+	"github.com/golang-migrate/migrate/v4"
+	sqlitedriver "github.com/golang-migrate/migrate/v4/database/sqlite"
+	"github.com/golang-migrate/migrate/v4/source/iofs"
+	_ "modernc.org/sqlite"
+)
+
+//go:embed migrations
+var migrationsFS embed.FS
+
+type DB struct {
+	db *sql.DB
+}
+
+func Open(path string) (*DB, error) {
+	sqldb, err := sql.Open("sqlite", path)
+	if err != nil {
+		return nil, err
+	}
+	// SQLite works best with a single writer; also ensures in-memory DBs
+	// (used in tests) share one connection across the migrate driver and
+	// all application queries.
+	sqldb.SetMaxOpenConns(1)
+
+	if err := runMigrations(sqldb); err != nil {
+		sqldb.Close()
+		return nil, err
+	}
+	return &DB{db: sqldb}, nil
+}
+
+func runMigrations(sqldb *sql.DB) error {
+	src, err := iofs.New(migrationsFS, "migrations")
+	if err != nil {
+		return err
+	}
+	driver, err := sqlitedriver.WithInstance(sqldb, &sqlitedriver.Config{})
+	if err != nil {
+		return err
+	}
+	m, err := migrate.NewWithInstance("iofs", src, "sqlite", driver)
+	if err != nil {
+		return err
+	}
+	if err := m.Up(); err != nil && err != migrate.ErrNoChange {
+		return err
+	}
+	return nil
+}
+
+func (d *DB) Close() error { return d.db.Close() }
+
+// RawDB returns the underlying *sql.DB for advanced queries.
+func (d *DB) RawDB() *sql.DB { return d.db }
+```
+
+- [ ] **Step 3: Run tests**
+
+```bash
+go test ./internal/db/... -v
+```
+
+Expected: all tests pass (the `openTestDB` helper uses `:memory:`, `SetMaxOpenConns(1)` ensures the migrate driver and app queries share the same connection).
+
+- [ ] **Step 4: Build to confirm no compile errors**
+
+```bash
+go build ./cmd/iblog
+```
+
+Expected: exits with code 0, no output.
+
+- [ ] **Step 5: Commit**
+
+```bash
+git add internal/db/db.go
+git commit -m "feat: wire golang-migrate into db.Open, remove inline schema"
+```