Init

6 files changed, 667 insertions, 0 deletions

diff --git a/internal/db/db.go b/internal/db/db.go
new file mode 100644
index 0000000..d348b96
--- /dev/null
+++ b/internal/db/db.go
@@ -0,0 +1,66 @@
+package db
+
+import (
+	"database/sql"
+
+	_ "modernc.org/sqlite"
+)
+
+type DB struct {
+	db *sql.DB
+}
+
+func Open(path string) (*DB, error) {
+	sqldb, err := sql.Open("sqlite", path)
+	if err != nil {
+		return nil, err
+	}
+	_, err = sqldb.Exec(`
+		CREATE TABLE IF NOT EXISTS 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 IF NOT EXISTS idx_pages_path ON pages(path);
+		CREATE INDEX IF NOT EXISTS idx_pages_date ON pages(date);
+		CREATE TABLE IF NOT EXISTS 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 IF NOT EXISTS idx_posts_slug ON posts(slug);
+		CREATE INDEX IF NOT EXISTS idx_posts_date ON posts(date);
+		CREATE TABLE IF NOT EXISTS redirects (
+			from_slug TEXT PRIMARY KEY,
+			to_slug   TEXT NOT NULL
+		);
+		CREATE TABLE IF NOT EXISTS settings (
+			key   TEXT PRIMARY KEY,
+			value TEXT NOT NULL DEFAULT ''
+		);
+		CREATE VIRTUAL TABLE IF NOT EXISTS pages_fts USING fts5(
+			path      UNINDEXED,
+			title,
+			content,
+			tokenize = 'porter unicode61'
+		);
+	`)
+	if err != nil {
+		return nil, err
+	}
+	return &DB{db: sqldb}, 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 }
diff --git a/internal/db/pages.go b/internal/db/pages.go
new file mode 100644
index 0000000..fc048c0
--- /dev/null
+++ b/internal/db/pages.go
@@ -0,0 +1,84 @@
+package db
+
+import (
+	"database/sql"
+	"encoding/json"
+	"strings"
+	"time"
+)
+
+type PageMeta struct {
+	Path      string
+	HTMLPath  string
+	Title     string
+	Date      string
+	Tags      []string
+	UpdatedAt time.Time
+}
+
+func (d *DB) UpsertPage(p PageMeta) error {
+	tags, _ := json.Marshal(p.Tags)
+	_, err := d.db.Exec(`
+		INSERT INTO pages (path, html_path, title, date, tags, updated_at)
+		VALUES (?, ?, ?, ?, ?, ?)
+		ON CONFLICT(path) DO UPDATE SET
+			html_path  = excluded.html_path,
+			title      = excluded.title,
+			date       = excluded.date,
+			tags       = excluded.tags,
+			updated_at = excluded.updated_at
+	`, p.Path, p.HTMLPath, p.Title, p.Date, string(tags), p.UpdatedAt.UTC())
+	return err
+}
+
+func (d *DB) DeletePage(path string) error {
+	_, err := d.db.Exec(`DELETE FROM pages WHERE path = ?`, path)
+	return err
+}
+
+func (d *DB) GetPage(path string) (*PageMeta, error) {
+	row := d.db.QueryRow(
+		`SELECT path, html_path, title, date, tags FROM pages WHERE path = ?`, path)
+	var p PageMeta
+	var tagsJSON string
+	if err := row.Scan(&p.Path, &p.HTMLPath, &p.Title, &p.Date, &tagsJSON); err != nil {
+		return nil, err
+	}
+	_ = json.Unmarshal([]byte(tagsJSON), &p.Tags)
+	return &p, nil
+}
+
+func (d *DB) ListPages() ([]PageMeta, error) {
+	rows, err := d.db.Query(
+		`SELECT path, html_path, title, date, tags FROM pages ORDER BY date DESC, path`)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	return scanPages(rows)
+}
+
+func (d *DB) ListByTag(tag string) ([]PageMeta, error) {
+	needle := `%"` + strings.ReplaceAll(tag, `"`, `\"`) + `"%`
+	rows, err := d.db.Query(
+		`SELECT path, html_path, title, date, tags FROM pages WHERE tags LIKE ? ORDER BY date DESC`, needle)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	return scanPages(rows)
+}
+
+func scanPages(rows *sql.Rows) ([]PageMeta, error) {
+	var pages []PageMeta
+	for rows.Next() {
+		var p PageMeta
+		var tagsJSON string
+		if err := rows.Scan(&p.Path, &p.HTMLPath, &p.Title, &p.Date, &tagsJSON); err != nil {
+			return nil, err
+		}
+		_ = json.Unmarshal([]byte(tagsJSON), &p.Tags)
+		pages = append(pages, p)
+	}
+	return pages, rows.Err()
+}
diff --git a/internal/db/posts.go b/internal/db/posts.go
new file mode 100644
index 0000000..3c90b5f
--- /dev/null
+++ b/internal/db/posts.go
@@ -0,0 +1,248 @@
+package db
+
+import (
+	"database/sql"
+	"encoding/json"
+	"time"
+)
+
+type PostRecord struct {
+	Id        string
+	Slug      string
+	Title     string
+	Date      string
+	Tags      []string
+	Draft     bool
+	Blocks    string // raw EditorJS JSON
+	UpdatedAt int64  // Unix microseconds
+}
+
+func (d *DB) UpsertPost(p PostRecord) error {
+	tags, _ := json.Marshal(p.Tags)
+	draft := 0
+	if p.Draft {
+		draft = 1
+	}
+	_, err := d.db.Exec(`
+		INSERT INTO posts (slug, title, date, tags, draft, blocks, updated_at)
+		VALUES (?, ?, ?, ?, ?, ?, ?)
+		ON CONFLICT(slug) DO UPDATE SET
+			title      = excluded.title,
+			date       = excluded.date,
+			tags       = excluded.tags,
+			draft      = excluded.draft,
+			blocks     = excluded.blocks,
+			updated_at = excluded.updated_at
+	`, p.Slug, p.Title, p.Date, string(tags), draft, p.Blocks, p.UpdatedAt)
+	return err
+}
+
+func (d *DB) GetPostBySlug(slug string) (*PostRecord, error) {
+	row := d.db.QueryRow(
+		`SELECT id, slug, title, date, tags, draft, blocks, updated_at FROM posts WHERE slug = ?`, slug)
+	var p PostRecord
+	var tagsJSON string
+	var draft int
+	if err := row.Scan(&p.Id, &p.Slug, &p.Title, &p.Date, &tagsJSON, &draft, &p.Blocks, &p.UpdatedAt); err != nil {
+		return nil, err
+	}
+	p.Draft = draft != 0
+	_ = json.Unmarshal([]byte(tagsJSON), &p.Tags)
+	if p.Tags == nil {
+		p.Tags = []string{}
+	}
+	return &p, nil
+}
+
+func (d *DB) GetPostById(id string) (*PostRecord, error) {
+	row := d.db.QueryRow(
+		`SELECT id, slug, title, date, tags, draft, blocks, updated_at FROM posts WHERE id = ?`, id)
+	var p PostRecord
+	var tagsJSON string
+	var draft int
+	if err := row.Scan(&p.Id, &p.Slug, &p.Title, &p.Date, &tagsJSON, &draft, &p.Blocks, &p.UpdatedAt); err != nil {
+		return nil, err
+	}
+	p.Draft = draft != 0
+	_ = json.Unmarshal([]byte(tagsJSON), &p.Tags)
+	if p.Tags == nil {
+		p.Tags = []string{}
+	}
+	return &p, nil
+}
+
+func (d *DB) ListPosts(includeDrafts bool) ([]PostRecord, error) {
+	query := `SELECT id, slug, title, date, tags, draft, blocks, updated_at FROM posts`
+	if !includeDrafts {
+		query += ` WHERE draft = 0`
+	}
+	query += ` ORDER BY date DESC, slug`
+	rows, err := d.db.Query(query)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	return scanPosts(rows)
+}
+
+func (d *DB) DeletePostBySlug(slug string) error {
+	_, err := d.db.Exec(`DELETE FROM posts WHERE slug = ?`, slug)
+	return err
+}
+
+func (d *DB) DeletePostById(id string) error {
+	_, err := d.db.Exec(`DELETE FROM posts WHERE id = ?`, id)
+	return err
+}
+
+func scanPosts(rows *sql.Rows) ([]PostRecord, error) {
+	var posts []PostRecord
+	for rows.Next() {
+		var p PostRecord
+		var tagsJSON string
+		var draft int
+		if err := rows.Scan(&p.Id, &p.Slug, &p.Title, &p.Date, &tagsJSON, &draft, &p.Blocks, &p.UpdatedAt); err != nil {
+			return nil, err
+		}
+		p.Draft = draft != 0
+		_ = json.Unmarshal([]byte(tagsJSON), &p.Tags)
+		if p.Tags == nil {
+			p.Tags = []string{}
+		}
+		posts = append(posts, p)
+	}
+	return posts, rows.Err()
+}
+
+func (p *PostRecord) GetPostRawPath() string {
+	return "/" + p.Slug
+}
+
+func (p *PostRecord) GetUpdatedTime() time.Time {
+	return time.UnixMicro(p.UpdatedAt).UTC()
+}
+
+func (d *DB) AddRedirect(fromSlug, toSlug string) error {
+	_, err := d.db.Exec(
+		`INSERT INTO redirects (from_slug, to_slug) VALUES (?, ?)
+		 ON CONFLICT(from_slug) DO UPDATE SET to_slug = excluded.to_slug`,
+		fromSlug, toSlug,
+	)
+	return err
+}
+
+func (d *DB) GetRedirect(fromSlug string) (string, error) {
+	var toSlug string
+	err := d.db.QueryRow(
+		`SELECT to_slug FROM redirects WHERE from_slug = ?`, fromSlug,
+	).Scan(&toSlug)
+	return toSlug, err
+}
+
+func (d *DB) CollapseRedirects(oldSlug, newSlug string) error {
+	_, err := d.db.Exec(
+		`UPDATE redirects SET to_slug = ? WHERE to_slug = ?`, newSlug, oldSlug,
+	)
+	return err
+}
+
+func (d *DB) RenamePost(oldSlug, newSlug string) error {
+	if oldSlug == newSlug {
+		return nil
+	}
+	tx, err := d.db.Begin()
+	if err != nil {
+		return err
+	}
+	defer tx.Rollback()
+
+	var p PostRecord
+	var tagsJSON string
+	var draft int
+	row := tx.QueryRow(
+		`SELECT slug, title, date, tags, draft, blocks, updated_at FROM posts WHERE slug = ?`, oldSlug)
+	if err := row.Scan(&p.Slug, &p.Title, &p.Date, &tagsJSON, &draft, &p.Blocks, &p.UpdatedAt); err != nil {
+		return err
+	}
+	p.Draft = draft != 0
+	_ = json.Unmarshal([]byte(tagsJSON), &p.Tags)
+
+	tags, _ := json.Marshal(p.Tags)
+	draftInt := 0
+	if p.Draft {
+		draftInt = 1
+	}
+	_, err = tx.Exec(`
+		INSERT INTO posts (slug, title, date, tags, draft, blocks, updated_at)
+		VALUES (?, ?, ?, ?, ?, ?, ?)`,
+		newSlug, p.Title, p.Date, string(tags), draftInt, p.Blocks, p.UpdatedAt,
+	)
+	if err != nil {
+		return err
+	}
+
+	if _, err = tx.Exec(`DELETE FROM posts WHERE slug = ?`, oldSlug); err != nil {
+		return err
+	}
+
+	if _, err = tx.Exec(
+		`UPDATE redirects SET to_slug = ? WHERE to_slug = ?`, newSlug, oldSlug,
+	); err != nil {
+		return err
+	}
+
+	if _, err = tx.Exec(`
+		INSERT INTO redirects (from_slug, to_slug) VALUES (?, ?)
+		ON CONFLICT(from_slug) DO UPDATE SET to_slug = excluded.to_slug`,
+		oldSlug, newSlug,
+	); err != nil {
+		return err
+	}
+
+	return tx.Commit()
+}
+
+func (d *DB) RenameAndUpsertPost(oldSlug string, p PostRecord) error {
+	if oldSlug == p.Slug {
+		return d.UpsertPost(p)
+	}
+	tx, err := d.db.Begin()
+	if err != nil {
+		return err
+	}
+	defer tx.Rollback()
+
+	tags, _ := json.Marshal(p.Tags)
+	draftInt := 0
+	if p.Draft {
+		draftInt = 1
+	}
+
+	if _, err = tx.Exec(`
+		INSERT INTO posts (slug, title, date, tags, draft, blocks, updated_at)
+		VALUES (?, ?, ?, ?, ?, ?, ?)`,
+		p.Slug, p.Title, p.Date, string(tags), draftInt, p.Blocks, p.UpdatedAt,
+	); err != nil {
+		return err
+	}
+
+	if _, err = tx.Exec(`DELETE FROM posts WHERE slug = ?`, oldSlug); err != nil {
+		return err
+	}
+
+	if _, err = tx.Exec(
+		`UPDATE redirects SET to_slug = ? WHERE to_slug = ?`, p.Slug, oldSlug,
+	); err != nil {
+		return err
+	}
+
+	if _, err = tx.Exec(`
+		INSERT INTO redirects (from_slug, to_slug) VALUES (?, ?)
+		ON CONFLICT(from_slug) DO UPDATE SET to_slug = excluded.to_slug`,
+		oldSlug, p.Slug,
+	); err != nil {
+		return err
+	}
+
+	return tx.Commit()
+}
diff --git a/internal/db/posts_test.go b/internal/db/posts_test.go
new file mode 100644
index 0000000..804e47d
--- /dev/null
+++ b/internal/db/posts_test.go
@@ -0,0 +1,186 @@
+package db
+
+import (
+	"database/sql"
+	"testing"
+)
+
+func openTestDB(t *testing.T) *DB {
+	t.Helper()
+	d, err := Open(":memory:")
+	if err != nil {
+		t.Fatalf("open test db: %v", err)
+	}
+	t.Cleanup(func() { d.Close() })
+	return d
+}
+
+func TestAddAndGetRedirect(t *testing.T) {
+	d := openTestDB(t)
+
+	if err := d.AddRedirect("old-slug", "new-slug"); err != nil {
+		t.Fatalf("AddRedirect: %v", err)
+	}
+
+	got, err := d.GetRedirect("old-slug")
+	if err != nil {
+		t.Fatalf("GetRedirect: %v", err)
+	}
+	if got != "new-slug" {
+		t.Errorf("got %q, want %q", got, "new-slug")
+	}
+}
+
+func TestGetRedirect_NotFound(t *testing.T) {
+	d := openTestDB(t)
+
+	_, err := d.GetRedirect("missing")
+	if err != sql.ErrNoRows {
+		t.Errorf("expected sql.ErrNoRows, got %v", err)
+	}
+}
+
+func TestCollapseRedirects(t *testing.T) {
+	d := openTestDB(t)
+
+	if err := d.AddRedirect("a", "b"); err != nil {
+		t.Fatalf("AddRedirect: %v", err)
+	}
+	if err := d.CollapseRedirects("b", "c"); err != nil {
+		t.Fatalf("CollapseRedirects: %v", err)
+	}
+
+	got, err := d.GetRedirect("a")
+	if err != nil {
+		t.Fatalf("GetRedirect: %v", err)
+	}
+	if got != "c" {
+		t.Errorf("got %q, want %q", got, "c")
+	}
+}
+
+func TestAddRedirect_Upsert(t *testing.T) {
+	d := openTestDB(t)
+
+	if err := d.AddRedirect("old", "first"); err != nil {
+		t.Fatalf("AddRedirect first: %v", err)
+	}
+	if err := d.AddRedirect("old", "second"); err != nil {
+		t.Fatalf("AddRedirect second: %v", err)
+	}
+
+	got, err := d.GetRedirect("old")
+	if err != nil {
+		t.Fatalf("GetRedirect: %v", err)
+	}
+	if got != "second" {
+		t.Errorf("got %q, want %q", got, "second")
+	}
+}
+
+func TestRenamePost(t *testing.T) {
+	d := openTestDB(t)
+
+	original := PostRecord{
+		Slug:      "original-slug",
+		Title:     "My Post",
+		Date:      "2026-04-04",
+		Tags:      []string{"go"},
+		Draft:     false,
+		Blocks:    `[{"type":"paragraph","data":{"text":"hello"}}]`,
+		UpdatedAt: 1000000,
+	}
+	if err := d.UpsertPost(original); err != nil {
+		t.Fatalf("UpsertPost: %v", err)
+	}
+
+	if err := d.RenamePost("original-slug", "new-slug"); err != nil {
+		t.Fatalf("RenamePost: %v", err)
+	}
+
+	got, err := d.GetPostBySlug("new-slug")
+	if err != nil {
+		t.Fatalf("GetPost new-slug: %v", err)
+	}
+	if got.Title != "My Post" {
+		t.Errorf("title: got %q, want %q", got.Title, "My Post")
+	}
+
+	_, err = d.GetPostBySlug("original-slug")
+	if err == nil {
+		t.Error("expected old slug to be deleted, but GetPost returned no error")
+	}
+
+	toSlug, err := d.GetRedirect("original-slug")
+	if err != nil {
+		t.Fatalf("GetRedirect: %v", err)
+	}
+	if toSlug != "new-slug" {
+		t.Errorf("redirect: got %q, want %q", toSlug, "new-slug")
+	}
+}
+
+func TestRenameAndUpsertPost(t *testing.T) {
+	d := openTestDB(t)
+
+	if err := d.UpsertPost(PostRecord{Slug: "old", Title: "Old Title", Date: "2026-01-01", Blocks: "[]", UpdatedAt: 1}); err != nil {
+		t.Fatalf("UpsertPost: %v", err)
+	}
+
+	updated := PostRecord{
+		Slug:      "new",
+		Title:     "New Title",
+		Date:      "2026-04-04",
+		Tags:      []string{"go"},
+		Blocks:    "[]",
+		UpdatedAt: 2,
+	}
+	if err := d.RenameAndUpsertPost("old", updated); err != nil {
+		t.Fatalf("RenameAndUpsertPost: %v", err)
+	}
+
+	got, err := d.GetPostBySlug("new")
+	if err != nil {
+		t.Fatalf("GetPost new: %v", err)
+	}
+	if got.Title != "New Title" {
+		t.Errorf("title: got %q, want %q", got.Title, "New Title")
+	}
+	if got.Date != "2026-04-04" {
+		t.Errorf("date: got %q, want %q", got.Date, "2026-04-04")
+	}
+
+	if _, err := d.GetPostBySlug("old"); err == nil {
+		t.Error("expected old slug to be deleted")
+	}
+
+	toSlug, err := d.GetRedirect("old")
+	if err != nil {
+		t.Fatalf("GetRedirect: %v", err)
+	}
+	if toSlug != "new" {
+		t.Errorf("redirect: got %q, want %q", toSlug, "new")
+	}
+}
+
+func TestRenamePost_CollapsesChain(t *testing.T) {
+	d := openTestDB(t)
+
+	if err := d.UpsertPost(PostRecord{Slug: "b", Title: "B", Blocks: "[]", UpdatedAt: 1}); err != nil {
+		t.Fatalf("UpsertPost: %v", err)
+	}
+	if err := d.AddRedirect("a", "b"); err != nil {
+		t.Fatalf("AddRedirect: %v", err)
+	}
+	if err := d.RenamePost("b", "c"); err != nil {
+		t.Fatalf("RenamePost: %v", err)
+	}
+
+	got, err := d.GetRedirect("a")
+	if err != nil {
+		t.Fatalf("GetRedirect a: %v", err)
+	}
+	if got != "c" {
+		t.Errorf("chain collapse: got %q, want %q", got, "c")
+	}
+}
diff --git a/internal/db/search.go b/internal/db/search.go
new file mode 100644
index 0000000..1214e2d
--- /dev/null
+++ b/internal/db/search.go
@@ -0,0 +1,62 @@
+package db
+
+type SearchPage struct {
+	Path    string
+	Title   string
+	Content string
+}
+
+type SearchResult struct {
+	Path    string
+	Title   string
+	Snippet string
+}
+
+func (d *DB) IndexPage(p SearchPage) error {
+	tx, err := d.db.Begin()
+	if err != nil {
+		return err
+	}
+	defer tx.Rollback()
+
+	if _, err = tx.Exec(`DELETE FROM pages_fts WHERE path = ?`, p.Path); err != nil {
+		return err
+	}
+	if _, err = tx.Exec(
+		`INSERT INTO pages_fts (path, title, content) VALUES (?, ?, ?)`,
+		p.Path, p.Title, p.Content,
+	); err != nil {
+		return err
+	}
+	return tx.Commit()
+}
+
+func (d *DB) UnindexPage(path string) error {
+	_, err := d.db.Exec(`DELETE FROM pages_fts WHERE path = ?`, path)
+	return err
+}
+
+// Search runs a full-text query and returns up to 20 results with snippets.
+func (d *DB) Search(query string) ([]SearchResult, error) {
+	rows, err := d.db.Query(`
+		SELECT path, title,
+			snippet(pages_fts, 2, '<mark>', '</mark>', '...', 20)
+		FROM pages_fts
+		WHERE pages_fts MATCH ?
+		ORDER BY rank
+		LIMIT 20
+	`, query)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	var results []SearchResult
+	for rows.Next() {
+		var r SearchResult
+		if err := rows.Scan(&r.Path, &r.Title, &r.Snippet); err != nil {
+			return nil, err
+		}
+		results = append(results, r)
+	}
+	return results, rows.Err()
+}
diff --git a/internal/db/settings.go b/internal/db/settings.go
new file mode 100644
index 0000000..6b2a007
--- /dev/null
+++ b/internal/db/settings.go
@@ -0,0 +1,21 @@
+package db
+
+import "database/sql"
+
+func (d *DB) GetSetting(key string) (string, error) {
+	var val string
+	err := d.db.QueryRow(`SELECT value FROM settings WHERE key = ?`, key).Scan(&val)
+	if err == sql.ErrNoRows {
+		return "", nil
+	}
+	return val, err
+}
+
+func (d *DB) SetSetting(key, value string) error {
+	_, err := d.db.Exec(
+		`INSERT INTO settings (key, value) VALUES (?, ?)
+		 ON CONFLICT(key) DO UPDATE SET value = excluded.value`,
+		key, value,
+	)
+	return err
+}