ai-resume/internal/search/search.go

// Package search builds the SQLite FTS5 index over the static pages.
//
// The index is written by the build step and opened read-only by the server, so
// a search never touches the content tree and works with every AI service
// stopped. The same index is the keyword half of Phase 4's hybrid retrieval:
// its schema is documented in docs/phase-2-notes.md and must not change shape
// without both phases agreeing.
package search

import (
	"database/sql"
	"fmt"
	"os"
	"path/filepath"
	"strings"
	"unicode"

	_ "modernc.org/sqlite"
)

// Doc is one indexable page.
type Doc struct {
	ID         string
	URL        string
	Title      string
	Area       string
	Level      int
	Confidence string
	Body       string
}

// Result is one hit.
type Result struct {
	URL        string  `json:"url"`
	Title      string  `json:"title"`
	Area       string  `json:"area"`
	Level      int     `json:"level"`
	Confidence string  `json:"confidence"`
	Snippet    string  `json:"snippet"`
	Score      float64 `json:"score"`
}

// Schema is the FTS5 table. Columns marked UNINDEXED are still selectable.
const schema = `
CREATE VIRTUAL TABLE pages USING fts5(
    id UNINDEXED,
    url UNINDEXED,
    title,
    area UNINDEXED,
    level UNINDEXED,
    confidence UNINDEXED,
    body,
    tokenize = 'porter unicode61 remove_diacritics 2'
);`

// Build writes a fresh index at path. Any existing file is replaced, so the
// index always describes exactly one build.
func Build(path string, docs []Doc) error {
	if err := os.MkdirAll(filepath.Dir(path), 0o755); err != nil {
		return err
	}
	if err := os.Remove(path); err != nil && !os.IsNotExist(err) {
		return err
	}
	db, err := sql.Open("sqlite", "file:"+path)
	if err != nil {
		return fmt.Errorf("open index %s: %w", path, err)
	}
	defer db.Close()
	db.SetMaxOpenConns(1)

	if _, err := db.Exec(`PRAGMA journal_mode = OFF; PRAGMA synchronous = OFF;`); err != nil {
		return err
	}
	if _, err := db.Exec(schema); err != nil {
		return fmt.Errorf("create fts5 table: %w", err)
	}
	tx, err := db.Begin()
	if err != nil {
		return err
	}
	stmt, err := tx.Prepare(`INSERT INTO pages (id, url, title, area, level, confidence, body) VALUES (?, ?, ?, ?, ?, ?, ?)`)
	if err != nil {
		return err
	}
	defer stmt.Close()
	for _, d := range docs {
		if _, err := stmt.Exec(d.ID, d.URL, d.Title, d.Area, d.Level, d.Confidence, d.Body); err != nil {
			return fmt.Errorf("index %s: %w", d.ID, err)
		}
	}
	if err := tx.Commit(); err != nil {
		return err
	}
	if _, err := db.Exec(`INSERT INTO pages (pages) VALUES ('optimize')`); err != nil {
		return err
	}
	return nil
}

// Index is a read-only handle on a built index.
type Index struct {
	db   *sql.DB
	path string
}

// Open opens a built index read-only.
func Open(path string) (*Index, error) {
	if _, err := os.Stat(path); err != nil {
		return nil, fmt.Errorf("search index %s: %w", path, err)
	}
	db, err := sql.Open("sqlite", "file:"+path+"?mode=ro")
	if err != nil {
		return nil, err
	}
	db.SetMaxOpenConns(1)
	if err := db.Ping(); err != nil {
		db.Close()
		return nil, err
	}
	return &Index{db: db, path: path}, nil
}

// Close releases the handle.
func (i *Index) Close() error { return i.db.Close() }

// Count returns the number of indexed pages.
func (i *Index) Count() (int, error) {
	var n int
	err := i.db.QueryRow(`SELECT count(*) FROM pages`).Scan(&n)
	return n, err
}

// Query runs a keyword search. Every term is matched as a prefix so "zigbee"
// finds "Zigbee" and "routing" finds "routers".
func (i *Index) Query(q string, limit int) ([]Result, error) {
	match := MatchExpression(q)
	if match == "" {
		return nil, nil
	}
	if limit <= 0 || limit > 50 {
		limit = 20
	}
	rows, err := i.db.Query(`
		SELECT url, title, area, CAST(level AS INTEGER), confidence,
		       snippet(pages, 6, '<mark>', '</mark>', '…', 18),
		       bm25(pages)
		FROM pages
		WHERE pages MATCH ?
		ORDER BY bm25(pages)
		LIMIT ?`, match, limit)
	if err != nil {
		return nil, fmt.Errorf("search %q: %w", q, err)
	}
	defer rows.Close()

	var out []Result
	for rows.Next() {
		var r Result
		if err := rows.Scan(&r.URL, &r.Title, &r.Area, &r.Level, &r.Confidence, &r.Snippet, &r.Score); err != nil {
			return nil, err
		}
		out = append(out, r)
	}
	return out, rows.Err()
}

// MatchExpression turns free text into a safe FTS5 query: bare words only, each
// as a prefix, all required. FTS5 operators in the input are never honoured, so
// a visitor cannot shape the query beyond words.
func MatchExpression(q string) string {
	terms := strings.FieldsFunc(strings.ToLower(q), func(r rune) bool {
		return !(unicode.IsLetter(r) || unicode.IsDigit(r) || r == '-')
	})
	if len(terms) == 0 {
		return ""
	}
	out := make([]string, 0, len(terms))
	for _, t := range terms {
		t = strings.Trim(t, "-")
		if t == "" {
			continue
		}
		out = append(out, `"`+t+`"*`)
	}
	return strings.Join(out, " AND ")
}