marky

database.go (31262B)

---

 package internal
 
 import (
 	"context"
 	"database/sql"
 	"embed"
 	"errors"
 	"fmt"
 	"io/fs"
 	"os"
 	"path/filepath"
 	"sort"
 	"strings"
 	"time"
 	"unicode/utf8"
 
 	_ "modernc.org/sqlite"
 )
 
 //go:embed migrations/*.sql
 var migrationsFS embed.FS
 
 // Bookmark represents a single bookmark row.
 type Bookmark struct {
 	ID          int64
 	URL         string
 	Title       string
 	Description sql.NullString
 	Referrer    sql.NullString
 	Public      bool
 	UserID      int64
 	CreatedAt   time.Time
 	UpdatedAt   time.Time
 }
 
 // SearchResult is a bookmark match with an optional excerpt snippet.
 type SearchResult struct {
 	Bookmark Bookmark
 	Excerpt  sql.NullString
 }
 
 // DB wraps *sql.DB with helpers.
 type DB struct {
 	sql *sql.DB
 	dsn string
 }
 
 // execer abstracts *sql.DB and *sql.Tx for executing SQL with context.
 type execer interface {
 	ExecContext(context.Context, string, ...any) (sql.Result, error)
 }
 
 // DefaultPath returns the default SQLite file path: ~/.marky/bookmarks.sql
 func DefaultPath() (string, error) {
 	home, err := os.UserHomeDir()
 	if err != nil {
 		return "", fmt.Errorf("resolve home dir: %w", err)
 	}
 	dir := filepath.Join(home, ".marky")
 	if err := os.MkdirAll(dir, 0o755); err != nil {
 		return "", fmt.Errorf("mkdir %s: %w", dir, err)
 	}
 	return filepath.Join(dir, "bookmarks.sql"), nil
 }
 
 // Open opens (and initializes) the database at the default path.
 func Open(ctx context.Context) (*DB, error) {
 	p, err := DefaultPath()
 	if err != nil {
 		return nil, err
 	}
 	return OpenAtPath(ctx, p)
 }
 
 // OpenAtPath opens (and initializes) the database at a specific path.
 func OpenAtPath(ctx context.Context, path string) (*DB, error) {
 	// Use a file: DSN and apply sensible pragmas after open.
 	dsn := fmt.Sprintf("file:%s", filepath.ToSlash(path))
 
 	sqldb, err := sql.Open("sqlite", dsn)
 	if err != nil {
 		return nil, fmt.Errorf("open sqlite: %w", err)
 	}
 
 	// Connection settings.
 	// WAL improves concurrency; busy_timeout makes writes friendlier.
 	pragmas := []string{
 		"PRAGMA journal_mode=WAL",
 		"PRAGMA busy_timeout=5000",
 		"PRAGMA foreign_keys=ON",
 	}
 	for _, p := range pragmas {
 		if _, execErr := sqldb.ExecContext(ctx, p); execErr != nil {
 			_ = sqldb.Close()
 			return nil, fmt.Errorf("apply pragma %q: %w", p, execErr)
 		}
 	}
 
 	// Migrate schema.
 	if err := applyMigrations(ctx, sqldb); err != nil {
 		_ = sqldb.Close()
 		return nil, err
 	}
 
 	return &DB{sql: sqldb, dsn: dsn}, nil
 }
 
 // Close closes the database.
 func (db *DB) Close() error { return db.sql.Close() }
 
 // applyMigrations executes embedded SQL migration files in order.
 func applyMigrations(ctx context.Context, sqldb *sql.DB) error {
 	if _, err := sqldb.ExecContext(ctx, `
         CREATE TABLE IF NOT EXISTS schema_migrations (
             version TEXT PRIMARY KEY,
             applied_at TEXT NOT NULL
         );
     `); err != nil {
 		return fmt.Errorf("create schema_migrations: %w", err)
 	}
 
 	entries, err := fs.ReadDir(migrationsFS, "migrations")
 	if err != nil {
 		return fmt.Errorf("read migrations: %w", err)
 	}
 
 	// Sort by filename ascending.
 	sort.Slice(entries, func(i, j int) bool { return entries[i].Name() < entries[j].Name() })
 
 	for _, e := range entries {
 		name := e.Name()
 		if e.IsDir() || !strings.HasSuffix(name, ".sql") {
 			continue
 		}
 
 		// Skip if applied.
 		var exists int
 		if err := sqldb.QueryRowContext(ctx, `SELECT 1 FROM schema_migrations WHERE version = ?`, name).Scan(&exists); err != nil {
 			if !errors.Is(err, sql.ErrNoRows) {
 				return fmt.Errorf("check migration %s: %w", name, err)
 			}
 		} else {
 			// already applied
 			continue
 		}
 
 		// Read SQL.
 		b, err := migrationsFS.ReadFile(filepath.Join("migrations", name))
 		if err != nil {
 			return fmt.Errorf("read migration %s: %w", name, err)
 		}
 
 		tx, err := sqldb.BeginTx(ctx, nil)
 		if err != nil {
 			return fmt.Errorf("begin tx for %s: %w", name, err)
 		}
 		if _, err := tx.ExecContext(ctx, string(b)); err != nil {
 			_ = tx.Rollback()
 			return fmt.Errorf("exec migration %s: %w", name, err)
 		}
 		if _, err := tx.ExecContext(ctx, `INSERT INTO schema_migrations(version, applied_at) VALUES(?, ?)`, name, time.Now().UTC().Format(time.RFC3339Nano)); err != nil {
 			_ = tx.Rollback()
 			return fmt.Errorf("record migration %s: %w", name, err)
 		}
 		if err := tx.Commit(); err != nil {
 			return fmt.Errorf("commit migration %s: %w", name, err)
 		}
 	}
 	return nil
 }
 
 // CreateBookmark inserts a new bookmark and returns it.
 func (db *DB) CreateBookmark(ctx context.Context, userID int64, url, title string, description *string) (*Bookmark, error) {
 	if strings.TrimSpace(url) == "" || strings.TrimSpace(title) == "" {
 		return nil, fmt.Errorf("url and title are required")
 	}
 	now := time.Now().UTC()
 	var desc sql.NullString
 	if description != nil {
 		desc = sql.NullString{String: *description, Valid: true}
 	}
 	res, err := db.sql.ExecContext(ctx, `
         INSERT INTO bookmarks(url, title, description, public, user_id, created_at, updated_at)
         VALUES(?, ?, ?, 0, ?, ?, ?)
     `, url, title, nullString(desc), userID, now.Format(time.RFC3339Nano), now.Format(time.RFC3339Nano))
 	if err != nil {
 		return nil, fmt.Errorf("insert bookmark: %w", err)
 	}
 	id, err := res.LastInsertId()
 	if err != nil {
 		return nil, fmt.Errorf("last insert id: %w", err)
 	}
 	// Replace FTS row (empty content until archive exists)
 	ftsContent := ""
 	if c, _, ok, _ := db.GetArchive(ctx, id); ok {
 		ftsContent = c
 	}
 	if err := ftsReplace(ctx, db.sql, id, title, url, nullString(desc), ftsContent); err != nil {
 		return nil, fmt.Errorf("fts replace after create: %w", err)
 	}
 	return &Bookmark{
 		ID:          id,
 		URL:         url,
 		Title:       title,
 		Description: desc,
 		Public:      false,
 		UserID:      userID,
 		CreatedAt:   now,
 		UpdatedAt:   now,
 	}, nil
 }
 
 // CreateBookmarkNoArchive inserts a new bookmark with the desired public flag
 // and initializes the FTS row with empty content (no archive entry).
 func (db *DB) CreateBookmarkNoArchive(ctx context.Context, userID int64, url, title string, description *string, public bool) (*Bookmark, error) {
 	if strings.TrimSpace(url) == "" || strings.TrimSpace(title) == "" {
 		return nil, fmt.Errorf("url and title are required")
 	}
 	now := time.Now().UTC()
 	var desc sql.NullString
 	if description != nil {
 		desc = sql.NullString{String: *description, Valid: true}
 	}
 	res, err := db.sql.ExecContext(ctx, `
         INSERT INTO bookmarks(url, title, description, public, user_id, created_at, updated_at)
         VALUES(?, ?, ?, ?, ?, ?, ?)
     `, url, title, nullString(desc), publicToInt(public), userID, now.Format(time.RFC3339Nano), now.Format(time.RFC3339Nano))
 	if err != nil {
 		return nil, fmt.Errorf("insert bookmark: %w", err)
 	}
 	id, err := res.LastInsertId()
 	if err != nil {
 		return nil, fmt.Errorf("last insert id: %w", err)
 	}
 	// Initialize FTS row with empty content (no archive).
 	if err := ftsReplace(ctx, db.sql, id, title, url, nullString(desc), ""); err != nil {
 		return nil, fmt.Errorf("fts replace after create(no-archive): %w", err)
 	}
 	return &Bookmark{
 		ID:          id,
 		URL:         url,
 		Title:       title,
 		Description: desc,
 		Public:      public,
 		UserID:      userID,
 		CreatedAt:   now,
 		UpdatedAt:   now,
 	}, nil
 }
 
 // GetBookmark returns a bookmark by ID.
 func (db *DB) GetBookmark(ctx context.Context, id int64) (*Bookmark, error) {
 	var b Bookmark
 	var created, updated string
 	if err := db.sql.QueryRowContext(ctx, `
         SELECT id, url, title, description, referrer, public, user_id, created_at, updated_at
         FROM bookmarks WHERE id = ?
     `, id).Scan(&b.ID, &b.URL, &b.Title, &b.Description, &b.Referrer, &b.Public, &b.UserID, &created, &updated); err != nil {
 		if errors.Is(err, sql.ErrNoRows) {
 			return nil, nil
 		}
 		return nil, fmt.Errorf("select bookmark: %w", err)
 	}
 	var err error
 	if b.CreatedAt, err = time.Parse(time.RFC3339Nano, created); err != nil {
 		return nil, fmt.Errorf("parse created_at: %w", err)
 	}
 	if b.UpdatedAt, err = time.Parse(time.RFC3339Nano, updated); err != nil {
 		return nil, fmt.Errorf("parse updated_at: %w", err)
 	}
 	return &b, nil
 }
 
 // GetBookmarkByURL returns a bookmark by exact URL.
 func (db *DB) GetBookmarkByURL(ctx context.Context, userID int64, u string) (*Bookmark, error) {
 	var b Bookmark
 	var created, updated string
 	if err := db.sql.QueryRowContext(ctx, `
         SELECT id, url, title, description, referrer, public, user_id, created_at, updated_at
         FROM bookmarks WHERE url = ? AND user_id = ?
     `, u, userID).Scan(&b.ID, &b.URL, &b.Title, &b.Description, &b.Referrer, &b.Public, &b.UserID, &created, &updated); err != nil {
 		if errors.Is(err, sql.ErrNoRows) {
 			return nil, nil
 		}
 		return nil, fmt.Errorf("select bookmark by url: %w", err)
 	}
 	var err error
 	if b.CreatedAt, err = time.Parse(time.RFC3339Nano, created); err != nil {
 		return nil, fmt.Errorf("parse created_at: %w", err)
 	}
 	if b.UpdatedAt, err = time.Parse(time.RFC3339Nano, updated); err != nil {
 		return nil, fmt.Errorf("parse updated_at: %w", err)
 	}
 	return &b, nil
 }
 
 // ListBookmarks returns bookmarks ordered by updated_at desc.
 func (db *DB) ListBookmarks(ctx context.Context, userID int64, limit, offset int) ([]Bookmark, error) {
 	if limit <= 0 {
 		limit = 50
 	}
 	rows, err := db.sql.QueryContext(ctx, `
         SELECT id, url, title, description, referrer, public, user_id, created_at, updated_at
         FROM bookmarks
         WHERE user_id = ?
         ORDER BY updated_at DESC
         LIMIT ? OFFSET ?
     `, userID, limit, offset)
 	if err != nil {
 		return nil, fmt.Errorf("query bookmarks: %w", err)
 	}
 	defer rows.Close()
 	out := make([]Bookmark, 0, limit)
 	for rows.Next() {
 		var b Bookmark
 		var created, updated string
 		if err := rows.Scan(&b.ID, &b.URL, &b.Title, &b.Description, &b.Referrer, &b.Public, &b.UserID, &created, &updated); err != nil {
 			return nil, fmt.Errorf("scan bookmark: %w", err)
 		}
 		if b.CreatedAt, err = time.Parse(time.RFC3339Nano, created); err != nil {
 			return nil, fmt.Errorf("parse created_at: %w", err)
 		}
 		if b.UpdatedAt, err = time.Parse(time.RFC3339Nano, updated); err != nil {
 			return nil, fmt.Errorf("parse updated_at: %w", err)
 		}
 		out = append(out, b)
 	}
 	if err := rows.Err(); err != nil {
 		return nil, fmt.Errorf("iterate bookmarks: %w", err)
 	}
 	return out, nil
 }
 
 // CountBookmarks returns the total number of bookmarks for a user.
 func (db *DB) CountBookmarks(ctx context.Context, userID int64) (int, error) {
 	var n int
 	if err := db.sql.QueryRowContext(ctx, `SELECT COUNT(1) FROM bookmarks WHERE user_id = ?`, userID).Scan(&n); err != nil {
 		return 0, fmt.Errorf("count bookmarks: %w", err)
 	}
 	return n, nil
 }
 
 // UpdateBookmark updates fields for a given bookmark.
 func (db *DB) UpdateBookmark(ctx context.Context, id int64, url, title string, description *string, public bool) (*Bookmark, error) {
 	if strings.TrimSpace(url) == "" || strings.TrimSpace(title) == "" {
 		return nil, fmt.Errorf("url and title are required")
 	}
 	now := time.Now().UTC()
 	var desc sql.NullString
 	if description != nil {
 		desc = sql.NullString{String: *description, Valid: true}
 	}
 	res, err := db.sql.ExecContext(ctx, `
         UPDATE bookmarks
         SET url = ?, title = ?, description = ?, public = ?, updated_at = ?
         WHERE id = ?
     `, url, title, nullString(desc), publicToInt(public), now.Format(time.RFC3339Nano), id)
 	if err != nil {
 		return nil, fmt.Errorf("update bookmark: %w", err)
 	}
 	n, err := res.RowsAffected()
 	if err != nil {
 		return nil, fmt.Errorf("rows affected: %w", err)
 	}
 	if n == 0 {
 		return nil, nil
 	}
 	// Update FTS metadata while preserving content
 	var descForFTS sql.NullString
 	if description != nil {
 		descForFTS = sql.NullString{String: *description, Valid: true}
 	}
 	ftsContent := ""
 	if c, _, ok, _ := db.GetArchive(ctx, id); ok {
 		ftsContent = c
 	}
 	if err := ftsReplace(ctx, db.sql, id, title, url, nullString(descForFTS), ftsContent); err != nil {
 		return nil, fmt.Errorf("fts replace after update: %w", err)
 	}
 	return db.GetBookmark(ctx, id)
 }
 
 // UpdateBookmarkWithReferrer updates fields including referrer for a given bookmark.
 func (db *DB) UpdateBookmarkWithReferrer(ctx context.Context, id int64, url, title string, description *string, public bool, referrer string) (*Bookmark, error) {
 	if strings.TrimSpace(url) == "" || strings.TrimSpace(title) == "" {
 		return nil, fmt.Errorf("url and title are required")
 	}
 	now := time.Now().UTC()
 	var desc sql.NullString
 	if description != nil {
 		desc = sql.NullString{String: *description, Valid: true}
 	}
 	_, err := db.sql.ExecContext(ctx, `
         UPDATE bookmarks
         SET url = ?, title = ?, description = ?, public = ?, referrer = ?, updated_at = ?
         WHERE id = ?
     `, url, title, nullString(desc), publicToInt(public), referrer, now.Format(time.RFC3339Nano), id)
 	if err != nil {
 		return nil, fmt.Errorf("update bookmark with referrer: %w", err)
 	}
 	// Update FTS metadata while preserving content
 	var descForFTS sql.NullString
 	if description != nil {
 		descForFTS = sql.NullString{String: *description, Valid: true}
 	}
 	ftsContent := ""
 	if c, _, ok, _ := db.GetArchive(ctx, id); ok {
 		ftsContent = c
 	}
 	if err := ftsReplace(ctx, db.sql, id, title, url, nullString(descForFTS), ftsContent); err != nil {
 		return nil, fmt.Errorf("fts replace after update: %w", err)
 	}
 	return db.GetBookmark(ctx, id)
 }
 
 // TouchBookmark updates only the updated_at timestamp.
 func (db *DB) TouchBookmark(ctx context.Context, id int64) error {
 	_, err := db.sql.ExecContext(ctx, `UPDATE bookmarks SET updated_at = ? WHERE id = ?`, time.Now().UTC().Format(time.RFC3339Nano), id)
 	if err != nil {
 		return fmt.Errorf("touch bookmark: %w", err)
 	}
 	return nil
 }
 
 // DeleteBookmark deletes a bookmark by ID. Returns true if a row was deleted.
 func (db *DB) DeleteBookmark(ctx context.Context, id int64) (bool, error) {
 	res, err := db.sql.ExecContext(ctx, `DELETE FROM bookmarks WHERE id = ?`, id)
 	if err != nil {
 		return false, fmt.Errorf("delete bookmark: %w", err)
 	}
 	n, err := res.RowsAffected()
 	if err != nil {
 		return false, fmt.Errorf("rows affected: %w", err)
 	}
 	if n > 0 {
 		if err := ftsDelete(ctx, db.sql, id); err != nil {
 			return false, fmt.Errorf("delete fts row: %w", err)
 		}
 	}
 	return n > 0, nil
 }
 
 // HasArchive reports whether an archive row exists for the given bookmark ID.
 func (db *DB) HasArchive(ctx context.Context, id int64) (bool, error) {
 	var one int
 	err := db.sql.QueryRowContext(ctx, `SELECT 1 FROM archives WHERE bookmark_id = ? LIMIT 1`, id).Scan(&one)
 	if err != nil {
 		if errors.Is(err, sql.ErrNoRows) {
 			return false, nil
 		}
 		return false, fmt.Errorf("has archive: %w", err)
 	}
 	return true, nil
 }
 
 // DeleteArchive removes the archive row for a bookmark and clears its FTS
 // content by reindexing the metadata with empty content.
 func (db *DB) DeleteArchive(ctx context.Context, id int64) error {
 	if _, err := db.sql.ExecContext(ctx, `DELETE FROM archives WHERE bookmark_id = ?`, id); err != nil {
 		return fmt.Errorf("delete archive: %w", err)
 	}
 	// Refresh FTS row with empty content
 	b, err := db.GetBookmark(ctx, id)
 	if err != nil {
 		return fmt.Errorf("get bookmark for fts after archive delete: %w", err)
 	}
 	if b == nil {
 		return nil
 	}
 	if err := ftsReplace(ctx, db.sql, id, b.Title, b.URL, nullString(b.Description), ""); err != nil {
 		return fmt.Errorf("fts replace after archive delete: %w", err)
 	}
 	return nil
 }
 
 func nullString(ns sql.NullString) any {
 	if ns.Valid {
 		return ns.String
 	}
 	return nil
 }
 
 func publicToInt(v bool) int {
 	if v {
 		return 1
 	}
 	return 0
 }
 
 // CreateBookmarkWithArchive creates a bookmark and saves its plaintext archive
 // within a single transaction to ensure consistency.
 func (db *DB) CreateBookmarkWithArchive(ctx context.Context, userID int64, url, title string, description *string, public bool, archiveContent string) (*Bookmark, error) {
 	if strings.TrimSpace(url) == "" || strings.TrimSpace(title) == "" {
 		return nil, fmt.Errorf("url and title are required")
 	}
 	now := time.Now().UTC()
 	var desc sql.NullString
 	if description != nil {
 		desc = sql.NullString{String: *description, Valid: true}
 	}
 
 	tx, err := db.sql.BeginTx(ctx, nil)
 	if err != nil {
 		return nil, fmt.Errorf("begin tx: %w", err)
 	}
 	res, err := tx.ExecContext(ctx, `
         INSERT INTO bookmarks(url, title, description, public, user_id, created_at, updated_at)
         VALUES(?, ?, ?, ?, ?, ?, ?)
     `, url, title, nullString(desc), publicToInt(public), userID, now.Format(time.RFC3339Nano), now.Format(time.RFC3339Nano))
 	if err != nil {
 		_ = tx.Rollback()
 		return nil, fmt.Errorf("insert bookmark: %w", err)
 	}
 	id, err := res.LastInsertId()
 	if err != nil {
 		_ = tx.Rollback()
 		return nil, fmt.Errorf("last insert id: %w", err)
 	}
 	if _, err := tx.ExecContext(ctx, `
         INSERT INTO archives(bookmark_id, content, archived_at)
         VALUES(?, ?, ?)
     `, id, archiveContent, now.Format(time.RFC3339Nano)); err != nil {
 		_ = tx.Rollback()
 		return nil, fmt.Errorf("insert archive: %w", err)
 	}
 	// Replace FTS row within the same transaction
 	if err := ftsReplace(ctx, tx, id, title, url, nullString(desc), archiveContent); err != nil {
 		_ = tx.Rollback()
 		return nil, fmt.Errorf("insert fts row: %w", err)
 	}
 	if err := tx.Commit(); err != nil {
 		return nil, fmt.Errorf("commit: %w", err)
 	}
 	return &Bookmark{
 		ID:          id,
 		URL:         url,
 		Title:       title,
 		Description: desc,
 		Public:      public,
 		UserID:      userID,
 		CreatedAt:   now,
 		UpdatedAt:   now,
 	}, nil
 }
 
 // SaveArchive upserts the plaintext archive content for a bookmark.
 func (db *DB) SaveArchive(ctx context.Context, bookmarkID int64, content string) error {
 	now := time.Now().UTC().Format(time.RFC3339Nano)
 	_, err := db.sql.ExecContext(ctx, `
         INSERT INTO archives(bookmark_id, content, archived_at)
         VALUES(?, ?, ?)
         ON CONFLICT(bookmark_id) DO UPDATE SET
             content=excluded.content,
             archived_at=excluded.archived_at
     `, bookmarkID, content, now)
 	if err != nil {
 		return fmt.Errorf("save archive: %w", err)
 	}
 	// Replace into FTS with latest content and metadata
 	b, err := db.GetBookmark(ctx, bookmarkID)
 	if err != nil {
 		return fmt.Errorf("fetch bookmark for fts: %w", err)
 	}
 	if b != nil {
 		if err := ftsReplace(ctx, db.sql, bookmarkID, b.Title, b.URL, nullString(b.Description), content); err != nil {
 			return fmt.Errorf("fts replace after archive: %w", err)
 		}
 	}
 	return nil
 }
 
 // GetArchive returns archived plaintext and timestamp for a bookmark.
 // ok is false if no archive exists.
 func (db *DB) GetArchive(ctx context.Context, bookmarkID int64) (content string, archivedAt time.Time, ok bool, err error) {
 	var ts string
 	err = db.sql.QueryRowContext(ctx, `
         SELECT content, archived_at FROM archives WHERE bookmark_id = ?
     `, bookmarkID).Scan(&content, &ts)
 	if err != nil {
 		if errors.Is(err, sql.ErrNoRows) {
 			return "", time.Time{}, false, nil
 		}
 		return "", time.Time{}, false, fmt.Errorf("get archive: %w", err)
 	}
 	at, parseErr := time.Parse(time.RFC3339Nano, ts)
 	if parseErr != nil {
 		return content, time.Time{}, true, fmt.Errorf("parse archived_at: %w", parseErr)
 	}
 	return content, at, true, nil
 }
 
 // SearchBookmarks performs a full-text search using FTS5 and returns matching bookmarks
 // ordered by a simple relevance score derived from offsets() length.
 func (db *DB) SearchBookmarks(ctx context.Context, userID int64, query string, limit, offset int) ([]Bookmark, error) {
 	if limit <= 0 {
 		limit = 50
 	}
 	rows, err := db.sql.QueryContext(ctx, `
         SELECT b.id, b.url, b.title, b.description, b.public, b.user_id, b.created_at, b.updated_at
         FROM bookmarks_fts AS f
         JOIN bookmarks AS b ON b.id = f.rowid
         WHERE bookmarks_fts MATCH ? AND b.user_id = ?
         ORDER BY bm25(bookmarks_fts)
         LIMIT ? OFFSET ?
     `, query, userID, limit, offset)
 	if err != nil {
 		return nil, fmt.Errorf("fts query: %w", err)
 	}
 	defer rows.Close()
 	out := make([]Bookmark, 0, limit)
 	for rows.Next() {
 		var b Bookmark
 		var created, updated string
 		if err := rows.Scan(&b.ID, &b.URL, &b.Title, &b.Description, &b.Public, &b.UserID, &created, &updated); err != nil {
 			return nil, fmt.Errorf("scan fts row: %w", err)
 		}
 		if b.CreatedAt, err = time.Parse(time.RFC3339Nano, created); err != nil {
 			return nil, fmt.Errorf("parse created_at: %w", err)
 		}
 		if b.UpdatedAt, err = time.Parse(time.RFC3339Nano, updated); err != nil {
 			return nil, fmt.Errorf("parse updated_at: %w", err)
 		}
 		out = append(out, b)
 	}
 	if err := rows.Err(); err != nil {
 		return nil, fmt.Errorf("iterate fts: %w", err)
 	}
 	return out, nil
 }
 
 // SearchBookmarksWithExcerpt performs an FTS5 search and also returns a snippet excerpt
 // from the archived content column highlighting matches.
 func (db *DB) SearchBookmarksWithExcerpt(ctx context.Context, userID int64, query string, limit, offset int) ([]SearchResult, error) {
 	if limit <= 0 {
 		limit = 50
 	}
 	rows, err := db.sql.QueryContext(ctx, `
         SELECT b.id, b.url, b.title, b.description, b.referrer, b.public, b.user_id, b.created_at, b.updated_at
         FROM bookmarks_fts AS f
         JOIN bookmarks AS b ON b.id = f.rowid
         WHERE bookmarks_fts MATCH ? AND b.user_id = ?
         ORDER BY bm25(bookmarks_fts)
         LIMIT ? OFFSET ?
     `, query, userID, limit, offset)
 	if err != nil {
 		return nil, fmt.Errorf("fts query: %w", err)
 	}
 	defer rows.Close()
 	out := make([]SearchResult, 0, limit)
 	for rows.Next() {
 		var b Bookmark
 		var created, updated string
 		if err := rows.Scan(&b.ID, &b.URL, &b.Title, &b.Description, &b.Referrer, &b.Public, &b.UserID, &created, &updated); err != nil {
 			return nil, fmt.Errorf("scan fts row: %w", err)
 		}
 		if b.CreatedAt, err = time.Parse(time.RFC3339Nano, created); err != nil {
 			return nil, fmt.Errorf("parse created_at: %w", err)
 		}
 		if b.UpdatedAt, err = time.Parse(time.RFC3339Nano, updated); err != nil {
 			return nil, fmt.Errorf("parse updated_at: %w", err)
 		}
 		// Build excerpt from archives content in Go for portability
 		if content, _, ok, _ := db.GetArchive(ctx, b.ID); ok {
 			ex := sql.NullString{String: makeExcerpt(content, query), Valid: true}
 			out = append(out, SearchResult{Bookmark: b, Excerpt: ex})
 		} else {
 			out = append(out, SearchResult{Bookmark: b})
 		}
 	}
 	if err := rows.Err(); err != nil {
 		return nil, fmt.Errorf("iterate fts: %w", err)
 	}
 	return out, nil
 }
 
 // CountSearchBookmarks returns the total number of FTS matches for the user.
 func (db *DB) CountSearchBookmarks(ctx context.Context, userID int64, query string) (int, error) {
 	var n int
 	err := db.sql.QueryRowContext(ctx, `
         SELECT COUNT(1)
         FROM bookmarks_fts AS f
         JOIN bookmarks AS b ON b.id = f.rowid
         WHERE bookmarks_fts MATCH ? AND b.user_id = ?
     `, query, userID).Scan(&n)
 	if err != nil {
 		return 0, fmt.Errorf("count fts matches: %w", err)
 	}
 	return n, nil
 }
 
 // SearchPublicBookmarksWithExcerpt performs an FTS5 search limited to public bookmarks
 // and also returns a snippet excerpt from the archived content column highlighting matches.
 func (db *DB) SearchPublicBookmarksWithExcerpt(ctx context.Context, userID int64, query string, limit, offset int) ([]SearchResult, error) {
 	if limit <= 0 {
 		limit = 50
 	}
 	rows, err := db.sql.QueryContext(ctx, `
         SELECT b.id, b.url, b.title, b.description, b.referrer, b.public, b.user_id, b.created_at, b.updated_at
         FROM bookmarks_fts AS f
         JOIN bookmarks AS b ON b.id = f.rowid
         WHERE bookmarks_fts MATCH ? AND b.user_id = ? AND b.public = 1
         ORDER BY bm25(bookmarks_fts)
         LIMIT ? OFFSET ?
     `, query, userID, limit, offset)
 	if err != nil {
 		return nil, fmt.Errorf("fts public query: %w", err)
 	}
 	defer rows.Close()
 	out := make([]SearchResult, 0, limit)
 	for rows.Next() {
 		var b Bookmark
 		var created, updated string
 		if err := rows.Scan(&b.ID, &b.URL, &b.Title, &b.Description, &b.Referrer, &b.Public, &b.UserID, &created, &updated); err != nil {
 			return nil, fmt.Errorf("scan fts public row: %w", err)
 		}
 		if b.CreatedAt, err = time.Parse(time.RFC3339Nano, created); err != nil {
 			return nil, fmt.Errorf("parse created_at: %w", err)
 		}
 		if b.UpdatedAt, err = time.Parse(time.RFC3339Nano, updated); err != nil {
 			return nil, fmt.Errorf("parse updated_at: %w", err)
 		}
 		// Build excerpt from archives content in Go for portability
 		if content, _, ok, _ := db.GetArchive(ctx, b.ID); ok {
 			ex := sql.NullString{String: makeExcerpt(content, query), Valid: true}
 			out = append(out, SearchResult{Bookmark: b, Excerpt: ex})
 		} else {
 			out = append(out, SearchResult{Bookmark: b})
 		}
 	}
 	if err := rows.Err(); err != nil {
 		return nil, fmt.Errorf("iterate fts public: %w", err)
 	}
 	return out, nil
 }
 
 // CountSearchPublicBookmarks returns the total number of FTS matches for public bookmarks only.
 func (db *DB) CountSearchPublicBookmarks(ctx context.Context, userID int64, query string) (int, error) {
 	var n int
 	err := db.sql.QueryRowContext(ctx, `
         SELECT COUNT(1)
         FROM bookmarks_fts AS f
         JOIN bookmarks AS b ON b.id = f.rowid
         WHERE bookmarks_fts MATCH ? AND b.user_id = ? AND b.public = 1
     `, query, userID).Scan(&n)
 	if err != nil {
 		return 0, fmt.Errorf("count fts public matches: %w", err)
 	}
 	return n, nil
 }
 
 // SearchPrivateBookmarksWithExcerpt performs an FTS5 search limited to private bookmarks
 // and also returns a snippet excerpt from the archived content column highlighting matches.
 func (db *DB) SearchPrivateBookmarksWithExcerpt(ctx context.Context, userID int64, query string, limit, offset int) ([]SearchResult, error) {
 	if limit <= 0 {
 		limit = 50
 	}
 	rows, err := db.sql.QueryContext(ctx, `
         SELECT b.id, b.url, b.title, b.description, b.referrer, b.public, b.user_id, b.created_at, b.updated_at
         FROM bookmarks_fts AS f
         JOIN bookmarks AS b ON b.id = f.rowid
         WHERE bookmarks_fts MATCH ? AND b.user_id = ? AND b.public = 0
         ORDER BY bm25(bookmarks_fts)
         LIMIT ? OFFSET ?
     `, query, userID, limit, offset)
 	if err != nil {
 		return nil, fmt.Errorf("fts private query: %w", err)
 	}
 	defer rows.Close()
 	out := make([]SearchResult, 0, limit)
 	for rows.Next() {
 		var b Bookmark
 		var created, updated string
 		if err := rows.Scan(&b.ID, &b.URL, &b.Title, &b.Description, &b.Referrer, &b.Public, &b.UserID, &created, &updated); err != nil {
 			return nil, fmt.Errorf("scan fts private row: %w", err)
 		}
 		if b.CreatedAt, err = time.Parse(time.RFC3339Nano, created); err != nil {
 			return nil, fmt.Errorf("parse created_at: %w", err)
 		}
 		if b.UpdatedAt, err = time.Parse(time.RFC3339Nano, updated); err != nil {
 			return nil, fmt.Errorf("parse updated_at: %w", err)
 		}
 		if content, _, ok, _ := db.GetArchive(ctx, b.ID); ok {
 			ex := sql.NullString{String: makeExcerpt(content, query), Valid: true}
 			out = append(out, SearchResult{Bookmark: b, Excerpt: ex})
 		} else {
 			out = append(out, SearchResult{Bookmark: b})
 		}
 	}
 	if err := rows.Err(); err != nil {
 		return nil, fmt.Errorf("iterate fts private: %w", err)
 	}
 	return out, nil
 }
 
 // CountSearchPrivateBookmarks returns the total number of FTS matches for private bookmarks only.
 func (db *DB) CountSearchPrivateBookmarks(ctx context.Context, userID int64, query string) (int, error) {
 	var n int
 	err := db.sql.QueryRowContext(ctx, `
         SELECT COUNT(1)
         FROM bookmarks_fts AS f
         JOIN bookmarks AS b ON b.id = f.rowid
         WHERE bookmarks_fts MATCH ? AND b.user_id = ? AND b.public = 0
     `, query, userID).Scan(&n)
 	if err != nil {
 		return 0, fmt.Errorf("count fts private matches: %w", err)
 	}
 	return n, nil
 }
 
 // makeExcerpt returns a short excerpt from content highlighting the first
 // occurrence of any query token using [brackets]. If no token is found, returns
 // the leading portion of the content.
 func makeExcerpt(content, query string) string {
 	c := content
 	if len(c) == 0 {
 		return ""
 	}
 	lc := strings.ToLower(c)
 	// Split query to tokens by whitespace; ignore empty tokens
 	fields := strings.Fields(strings.ToLower(query))
 	// Find first occurrence index among tokens
 	idx := -1
 	token := ""
 	for _, t := range fields {
 		if t == "" {
 			continue
 		}
 		if i := strings.Index(lc, t); i >= 0 && (idx == -1 || i < idx) {
 			idx = i
 			token = t
 		}
 	}
 	// Window
 	const pre = 80
 	const post = 120
 	if idx == -1 {
 		end := len(c)
 		if end > pre+post {
 			end = pre + post
 		}
 		s := c[:end]
 		if end < len(c) {
 			s += "…"
 		}
 		return s
 	}
 	start := idx - pre
 	if start < 0 {
 		start = 0
 	}
 	end := idx + len(token) + post
 	if end > len(c) {
 		end = len(c)
 	}
 	// Align start/end to UTF-8 rune boundaries to avoid broken characters.
 	// Move start backward to the beginning of a rune.
 	for start > 0 {
 		b := c[start]
 		if (b & 0xC0) != 0x80 { // not a continuation byte
 			break
 		}
 		start--
 	}
 	// Move end forward to the end of the current rune if we cut in the middle.
 	for end < len(c) {
 		b := c[end]
 		if (b & 0xC0) != 0x80 { // at a rune boundary already
 			break
 		}
 		end++
 	}
 	s := c[start:end]
 	if start > 0 {
 		s = "…" + s
 	}
 	if end < len(c) {
 		s = s + "…"
 	}
 	// Highlight all tokens (basic, case-insensitive) using guillemets « ».
 	// Iterate by runes to avoid breaking multi-byte characters while matching
 	// tokens by byte-slices at rune boundaries.
 	ls := strings.ToLower(s)
 	var b strings.Builder
 	for i := 0; i < len(s); {
 		// Try to match any token starting at this byte position
 		matched := false
 		for _, t := range fields {
 			if t == "" {
 				continue
 			}
 			if i+len(t) <= len(s) && ls[i:i+len(t)] == t {
 				b.WriteRune('«')
 				b.WriteString(s[i : i+len(t)])
 				b.WriteRune('»')
 				i += len(t)
 				matched = true
 				break
 			}
 		}
 		if matched {
 			continue
 		}
 		r, size := utf8.DecodeRuneInString(s[i:])
 		if r == utf8.RuneError && size == 1 {
 			// Write raw byte on decode error to avoid infinite loop
 			b.WriteByte(s[i])
 			i++
 			continue
 		}
 		b.WriteRune(r)
 		i += size
 	}
 	return b.String()
 }
 
 // ftsReplace replaces a row in the contentless FTS5 table using DELETE + INSERT,
 // because UPSERT/REPLACE is not implemented for virtual tables.
 func ftsReplace(ctx context.Context, ex execer, rowid int64, title, url string, description any, content string) error {
 	// Try regular DELETE first (works for contentful FTS); if it fails, fall back to special delete.
 	if _, err := ex.ExecContext(ctx, `DELETE FROM bookmarks_fts WHERE rowid = ?`, rowid); err != nil {
 		// Fallback to special delete for contentless/external-content tables
 		_, _ = ex.ExecContext(ctx, `INSERT INTO bookmarks_fts(bookmarks_fts, rowid) VALUES('delete', ?)`, rowid)
 	}
 	_, err := ex.ExecContext(ctx, `
         INSERT INTO bookmarks_fts(rowid, title, url, description, content)
         VALUES(?, ?, ?, ?, ?)
     `, rowid, title, url, description, content)
 	return err
 }
 
 func ftsDelete(ctx context.Context, ex execer, rowid int64) error {
 	if _, err := ex.ExecContext(ctx, `DELETE FROM bookmarks_fts WHERE rowid = ?`, rowid); err == nil {
 		return nil
 	}
 	// Fallback to special delete form
 	_, err := ex.ExecContext(ctx, `INSERT INTO bookmarks_fts(bookmarks_fts, rowid) VALUES('delete', ?)`, rowid)
 	return err
 }