2 files changed, 219 insertions, 0 deletions
diff --git a/internal/db/posts.go b/internal/db/posts.go
new file mode 100644
index 0000000..f6c23cc
--- /dev/null
+++ b/internal/db/posts.go
@@ -0,0 +1,139 @@
+package db
+
+import (
+	"database/sql"
+	"encoding/json"
+	"time"
+)
+
+type PostRecord struct {
+	Slug      string
+	Title     string
+	Date      string
+	Tags      []string
+	Draft     bool
+	Blocks    string // raw EditorJS JSON
+	UpdatedAt int64  // Unix microseconds
+}
+
+// UpsertPost inserts or updates a post record.
+func (m *MetaDB) UpsertPost(p PostRecord) error {
+	tags, _ := json.Marshal(p.Tags)
+	draft := 0
+	if p.Draft {
+		draft = 1
+	}
+	_, err := m.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
+}
+
+// GetPost retrieves a single post by slug.
+func (m *MetaDB) GetPost(slug string) (*PostRecord, error) {
+	row := m.db.QueryRow(
+		`SELECT 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.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
+}
+
+// ListPosts returns all posts, optionally including drafts.
+func (m *MetaDB) ListPosts(includeDrafts bool) ([]PostRecord, error) {
+	query := `SELECT slug, title, date, tags, draft, blocks, updated_at FROM posts`
+	if !includeDrafts {
+		query += ` WHERE draft = 0`
+	}
+	query += ` ORDER BY date DESC, slug`
+	rows, err := m.db.Query(query)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	return scanPosts(rows)
+}
+
+// DeletePost removes a post by slug.
+func (m *MetaDB) DeletePost(slug string) error {
+	_, err := m.db.Exec(`DELETE FROM posts WHERE slug = ?`, slug)
+	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.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()
+}
+
+// DB returns the underlying *sql.DB for advanced queries (e.g., transaction handling).
+func (m *MetaDB) DB() *sql.DB {
+	return m.db
+}
+
+// GetPostRawPath returns the path for indexing in the search database.
+func (p *PostRecord) GetPostRawPath() string {
+	return "/" + p.Slug
+}
+
+// GetUpdatedTime returns the updated_at as a time.Time for comparison.
+func (p *PostRecord) GetUpdatedTime() time.Time {
+	return time.UnixMicro(p.UpdatedAt).UTC()
+}
+
+// AddRedirect inserts or replaces a redirect from fromSlug to toSlug.
+func (m *MetaDB) AddRedirect(fromSlug, toSlug string) error {
+	_, err := m.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
+}
+
+// GetRedirect returns the slug that fromSlug redirects to, or sql.ErrNoRows if none.
+func (m *MetaDB) GetRedirect(fromSlug string) (string, error) {
+	var toSlug string
+	err := m.db.QueryRow(
+		`SELECT to_slug FROM redirects WHERE from_slug = ?`, fromSlug,
+	).Scan(&toSlug)
+	return toSlug, err
+}
+
+// CollapseRedirects updates all redirects pointing to oldSlug so they point to newSlug instead,
+// preventing redirect chains when a slug is renamed again.
+func (m *MetaDB) CollapseRedirects(oldSlug, newSlug string) error {
+	_, err := m.db.Exec(
+		`UPDATE redirects SET to_slug = ? WHERE to_slug = ?`, newSlug, oldSlug,
+	)
+	return err
+}
diff --git a/internal/db/posts_test.go b/internal/db/posts_test.go
new file mode 100644
index 0000000..ec596c3
--- /dev/null
+++ b/internal/db/posts_test.go
@@ -0,0 +1,80 @@
+package db
+
+import (
+	"database/sql"
+	"testing"
+)
+
+func openTestDB(t *testing.T) *MetaDB {
+	t.Helper()
+	m, err := OpenMeta(":memory:")
+	if err != nil {
+		t.Fatalf("open test db: %v", err)
+	}
+	t.Cleanup(func() { m.Close() })
+	return m
+}
+
+func TestAddAndGetRedirect(t *testing.T) {
+	m := openTestDB(t)
+
+	if err := m.AddRedirect("old-slug", "new-slug"); err != nil {
+		t.Fatalf("AddRedirect: %v", err)
+	}
+
+	got, err := m.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) {
+	m := openTestDB(t)
+
+	_, err := m.GetRedirect("missing")
+	if err != sql.ErrNoRows {
+		t.Errorf("expected sql.ErrNoRows, got %v", err)
+	}
+}
+
+func TestCollapseRedirects(t *testing.T) {
+	m := openTestDB(t)
+
+	// a -> b, then b -> c: collapse should make a -> c
+	if err := m.AddRedirect("a", "b"); err != nil {
+		t.Fatalf("AddRedirect: %v", err)
+	}
+	if err := m.CollapseRedirects("b", "c"); err != nil {
+		t.Fatalf("CollapseRedirects: %v", err)
+	}
+
+	got, err := m.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) {
+	m := openTestDB(t)
+
+	if err := m.AddRedirect("old", "first"); err != nil {
+		t.Fatalf("AddRedirect first: %v", err)
+	}
+	if err := m.AddRedirect("old", "second"); err != nil {
+		t.Fatalf("AddRedirect second: %v", err)
+	}
+
+	got, err := m.GetRedirect("old")
+	if err != nil {
+		t.Fatalf("GetRedirect: %v", err)
+	}
+	if got != "second" {
+		t.Errorf("got %q, want %q", got, "second")
+	}
+}