# 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"
```