Skip to content
quarry Artifact [19a4f9a489]
Login

Artifact [19a4f9a489]

Check-ins Using Download Hex

Artifact 19a4f9a4896647190b228bb35cb9f15848906c77b94cadd9611748871dc3a789:

// Package index is the per-node derived index (0001 §5): SQLite tables
// folded from store artifacts, disposable and rebuildable by design —
// dropping the database file and re-ingesting must converge to identical
// state, and the disposability harness proves it. This package is a data
// plane; the arch gate restricts its importers.
package index

import (
	"database/sql"
	"fmt"
	"strings"

	"github.com/ncruces/go-sqlite3/driver"
	"github.com/ncruces/go-sqlite3/ext/fts5"

	"craftdesign.group/quarry/internal/artifact"
)

const schema = `
CREATE TABLE IF NOT EXISTS checkin (
	hash    TEXT PRIMARY KEY,
	date    TEXT NOT NULL,
	user    TEXT NOT NULL,
	comment TEXT NOT NULL,
	branch  TEXT NOT NULL DEFAULT 'trunk'
);
CREATE TABLE IF NOT EXISTS checkin_tag (
	src     TEXT NOT NULL, -- artifact that carries the T card
	date    TEXT NOT NULL, -- that artifact's D card: tag priority order
	checkin TEXT NOT NULL, -- tag target ("*" resolved to src for manifests)
	name    TEXT NOT NULL, -- without the sign prefix
	sign    TEXT NOT NULL, -- + - *
	value   TEXT NOT NULL,
	PRIMARY KEY (src, checkin, name)
);
CREATE TABLE IF NOT EXISTS checkin_parent (
	child  TEXT NOT NULL,
	parent TEXT NOT NULL,
	ord    INTEGER NOT NULL,
	PRIMARY KEY (child, ord)
);
-- Branch recompute walks parent->child; leaf detection asks "who lists
-- me as parent". Both need the reverse edge indexed.
CREATE INDEX IF NOT EXISTS checkin_parent_by_parent ON checkin_parent (parent);
CREATE INDEX IF NOT EXISTS checkin_tag_by_checkin ON checkin_tag (checkin, name, sign);
CREATE TABLE IF NOT EXISTS technote (
	hash     TEXT PRIMARY KEY, -- the technote artifact
	event    TEXT NOT NULL,    -- E-card id (stable across edits; latest wins)
	date     TEXT NOT NULL,    -- E-card timestamp the note is filed under
	edited   TEXT NOT NULL,    -- D-card edit time (dedup: newest edit wins)
	user     TEXT NOT NULL,
	mimetype TEXT NOT NULL,
	comment  TEXT NOT NULL DEFAULT '', -- C card: the timeline title
	body     TEXT NOT NULL             -- W card
);
CREATE INDEX IF NOT EXISTS technote_by_event ON technote (event, edited);
CREATE TABLE IF NOT EXISTS forum_post (
	hash    TEXT PRIMARY KEY, -- the forum-post artifact
	thread  TEXT NOT NULL,    -- root post hash (self for a thread opener)
	parent  TEXT NOT NULL,    -- I-card reply target ('' for the opener)
	title   TEXT NOT NULL,    -- H card (opener only)
	date    TEXT NOT NULL,
	user    TEXT NOT NULL,
	body    TEXT NOT NULL     -- W card
);
CREATE INDEX IF NOT EXISTS forum_post_by_thread ON forum_post (thread, date);
CREATE TABLE IF NOT EXISTS wiki_edit (
	hash     TEXT PRIMARY KEY,
	title    TEXT NOT NULL,
	date     TEXT NOT NULL,
	user     TEXT NOT NULL,
	mimetype TEXT NOT NULL,
	body     BLOB NOT NULL
);
CREATE TABLE IF NOT EXISTS ticket_change (
	hash   TEXT PRIMARY KEY,
	ticket TEXT NOT NULL,
	date   TEXT NOT NULL,
	user   TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS ticket_change_by_ticket ON ticket_change (ticket, date);
CREATE TABLE IF NOT EXISTS ticket_field (
	hash  TEXT NOT NULL,
	name  TEXT NOT NULL,
	value TEXT NOT NULL,
	PRIMARY KEY (hash, name)
);
CREATE TABLE IF NOT EXISTS search_doc (
	hash  TEXT PRIMARY KEY, -- artifact hash ("ticket:<id>" for folded ticket docs)
	kind  TEXT NOT NULL,    -- checkin | wiki | ticket
	ref   TEXT NOT NULL,    -- link target: checkin hash, wiki title, ticket id
	label TEXT NOT NULL,    -- human-readable result label
	text  TEXT NOT NULL
);
CREATE VIRTUAL TABLE IF NOT EXISTS search_fts USING fts5(
	text, content='search_doc', content_rowid='rowid'
);
CREATE TRIGGER IF NOT EXISTS search_doc_ai AFTER INSERT ON search_doc BEGIN
	INSERT INTO search_fts (rowid, text) VALUES (new.rowid, new.text);
END;
CREATE TRIGGER IF NOT EXISTS search_doc_ad AFTER DELETE ON search_doc BEGIN
	INSERT INTO search_fts (search_fts, rowid, text) VALUES ('delete', old.rowid, old.text);
END;
CREATE TRIGGER IF NOT EXISTS search_doc_au AFTER UPDATE ON search_doc BEGIN
	INSERT INTO search_fts (search_fts, rowid, text) VALUES ('delete', old.rowid, old.text);
	INSERT INTO search_fts (rowid, text) VALUES (new.rowid, new.text);
END;
`

// Index is one repository's derived index.
type Index struct {
	db *sql.DB
}

// Open opens (creating if needed) the index database at path.
func Open(path string) (*Index, error) {
	db, err := driver.Open("file:"+path, fts5.Register)
	if err != nil {
		return nil, err
	}
	// WAL keeps page reads flowing while a fold batch commits; the busy
	// timeout covers the brief checkpoint locks.
	if _, err := db.Exec(`PRAGMA journal_mode=WAL;
		PRAGMA synchronous=NORMAL;
		PRAGMA busy_timeout=5000;`); err != nil {
		db.Close()
		return nil, fmt.Errorf("index: pragmas: %w", err)
	}
	if _, err := db.Exec(schema); err != nil {
		db.Close()
		return nil, fmt.Errorf("index: schema: %w", err)
	}
	// Additive migrations for indexes created by an earlier schema. The
	// lighter mechanism than a rebuild: a duplicate-column error just
	// means this index already has it. Rows written before the column
	// existed stay empty until a reindex — derived data, so that is the
	// sanctioned repair.
	for _, alter := range []string{
		`ALTER TABLE technote ADD COLUMN comment TEXT NOT NULL DEFAULT ''`,
	} {
		if _, err := db.Exec(alter); err != nil && !strings.Contains(err.Error(), "duplicate column") {
			db.Close()
			return nil, fmt.Errorf("index: migrate: %w", err)
		}
	}
	return &Index{db: db}, nil
}

func (ix *Index) Close() error { return ix.db.Close() }

// Parsed pairs an artifact hash with its parsed form; Art is nil for
// opaque content blobs (folded as nothing — content is served on demand
// through zit, never copied into the index).
type Parsed struct {
	Hash string
	Art  *artifact.Artifact
}

// Fold applies one event batch atomically. Folds are idempotent upserts
// keyed by hash, so at-least-once event delivery converges. Ingest
// position is not stored here: the store cursor lives in NATS KV and the
// stream position in the durable consumer (0001 §4) — both advisory,
// both rebuilt by replaying stores.
// Fold reports whether the batch touched branch-bearing artifacts; the
// caller runs RecomputeBranches once after draining (recomputing per
// batch is quadratic over a bulk ingest).
func (ix *Index) Fold(batch []Parsed) (bool, error) {
	tx, err := ix.db.Begin()
	if err != nil {
		return false, err
	}
	defer tx.Rollback()
	branchy := false
	tickets := map[string]bool{}
	for _, p := range batch {
		if p.Art == nil {
			continue
		}
		if err := fold(tx, p.Hash, p.Art); err != nil {
			return false, fmt.Errorf("index: fold %s: %w", p.Hash, err)
		}
		switch p.Art.Type {
		case artifact.Manifest, artifact.Control:
			branchy = true
		case artifact.Ticket:
			tickets[p.Art.TicketID] = true
		}
	}
	// Ticket search docs hold the FOLDED state (one doc per ticket, keyed
	// by id), so snippets never show stale field values from early
	// changes (ticket 050). Once per touched ticket per batch — per
	// change is quadratic over a real ticket corpus.
	for id := range tickets {
		if err := refreshTicketDoc(tx, id); err != nil {
			return false, fmt.Errorf("index: ticket doc %s: %w", id, err)
		}
	}
	return branchy, tx.Commit()
}

// FoldNow folds one batch and recomputes branches immediately — the
// single-batch convenience for callers outside the drain loop.
func (ix *Index) FoldNow(batch []Parsed) error {
	branchy, err := ix.Fold(batch)
	if err != nil {
		return err
	}
	if branchy {
		return ix.RecomputeBranches()
	}
	return nil
}

// RecomputeBranches rewrites the branch column from recorded tag facts —
// once per fold round, after every batch has landed.
func (ix *Index) RecomputeBranches() error {
	tx, err := ix.db.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()
	if err := recomputeBranches(tx); err != nil {
		return fmt.Errorf("index: branches: %w", err)
	}
	return tx.Commit()
}

func fold(tx *sql.Tx, hash string, a *artifact.Artifact) error {
	switch a.Type {
	case artifact.Manifest:
		// INSERT OR REPLACE would clobber the computed branch column on
		// redelivery; upsert only the artifact-borne fields.
		if _, err := tx.Exec(`INSERT INTO checkin (hash, date, user, comment)
			VALUES (?, ?, ?, ?)
			ON CONFLICT (hash) DO UPDATE SET date=excluded.date,
				user=excluded.user, comment=excluded.comment`,
			hash, a.Date, a.User, a.Comment); err != nil {
			return err
		}
		for i, parent := range a.Parents {
			if _, err := tx.Exec(`INSERT OR REPLACE INTO checkin_parent (child, parent, ord)
				VALUES (?, ?, ?)`, hash, parent, i); err != nil {
				return err
			}
		}
		if err := foldTags(tx, hash, hash, a.Date, a.Tags); err != nil {
			return err
		}
	case artifact.Control:
		if err := foldTags(tx, hash, "", a.Date, a.Tags); err != nil {
			return err
		}
	case artifact.Wiki:
		if _, err := tx.Exec(`INSERT OR REPLACE INTO wiki_edit (hash, title, date, user, mimetype, body)
			VALUES (?, ?, ?, ?, ?, ?)`,
			hash, a.WikiTitle, a.Date, a.User, a.Mimetype, a.Body); err != nil {
			return err
		}
	case artifact.Ticket:
		if _, err := tx.Exec(`INSERT OR REPLACE INTO ticket_change (hash, ticket, date, user)
			VALUES (?, ?, ?, ?)`, hash, a.TicketID, a.Date, a.User); err != nil {
			return err
		}
		for _, f := range a.Fields {
			if _, err := tx.Exec(`INSERT OR REPLACE INTO ticket_field (hash, name, value)
				VALUES (?, ?, ?)`, hash, f.Name, f.Value); err != nil {
				return err
			}
		}
	case artifact.Technote:
		if _, err := tx.Exec(`INSERT OR REPLACE INTO technote (hash, event, date, edited, user, mimetype, comment, body)
			VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
			hash, a.EventID, a.EventTime, a.Date, a.User, a.Mimetype, a.Comment, string(a.Body)); err != nil {
			return err
		}
	case artifact.Forum:
		// A thread opener has no G card; it roots its own thread.
		thread := a.ThreadRoot
		if thread == "" {
			thread = hash
		}
		if _, err := tx.Exec(`INSERT OR REPLACE INTO forum_post (hash, thread, parent, title, date, user, body)
			VALUES (?, ?, ?, ?, ?, ?, ?)`,
			hash, thread, a.InReplyTo, a.ThreadTitle, a.Date, a.User, string(a.Body)); err != nil {
			return err
		}
	}
	// Ticket search docs are refreshed once per ticket per batch by the
	// caller (fold order within the batch doesn't matter — the refresh
	// reads the folded rows).
	if a.Type != artifact.Ticket {
		if kind, ref, label, text := searchDoc(hash, a); kind != "" {
			if _, err := tx.Exec(`INSERT OR REPLACE INTO search_doc (hash, kind, ref, label, text)
				VALUES (?, ?, ?, ?, ?)`, hash, kind, ref, label, text); err != nil {
				return err
			}
		}
	}
	// Control, cluster, technote, forum, attachment: folded when their
	// pages land (phase 2); skipping them is safe because a reindex
	// replays every artifact.
	return nil
}

// foldTags records an artifact's T cards as tag facts. self names the
// check-in a "*" target resolves to (the carrying manifest); control
// artifacts have no self and their "*" targets are illegal anyway
// (§5.4), so those rows are skipped.
func foldTags(tx *sql.Tx, src, self, date string, tags []artifact.Tag) error {
	for _, t := range tags {
		target := t.Target
		if target == "*" {
			if self == "" {
				continue
			}
			target = self
		}
		if _, err := tx.Exec(`INSERT OR REPLACE INTO checkin_tag (src, date, checkin, name, sign, value)
			VALUES (?, ?, ?, ?, ?, ?)`,
			src, date, target, strings.TrimLeft(t.Name, "+-*"), t.Name[:1], t.Value); err != nil {
			return err
		}
	}
	return nil
}

// recomputeBranches rewrites every check-in's branch from the recorded
// tag facts: a propagating branch tag names its check-in's branch, and
// the name flows down primary-parent edges until a descendant carries
// its own. A WHOLESALE recompute after each fold batch is what makes
// the column order-independent — the same tag facts and DAG yield the
// same branches no matter what order artifacts arrived in (the
// disposability property). ~4k check-ins recompute in milliseconds.
func recomputeBranches(tx *sql.Tx) error {
	// The walk lands in an indexed temp table first: the final UPDATE's
	// per-row probe against a bare materialized CTE is O(n²) at real-store
	// sizes (sqlite.org's repo: 36k check-ins).
	if _, err := tx.Exec(`
		CREATE TEMP TABLE IF NOT EXISTS br_walk (hash TEXT PRIMARY KEY, branch TEXT) WITHOUT ROWID;
		DELETE FROM br_walk;
		INSERT OR IGNORE INTO br_walk (hash, branch)
		WITH RECURSIVE br(hash, branch) AS (
			-- one seed per check-in: the branch tag from the LATEST
			-- carrying artifact wins (bare column with max())
			SELECT t.checkin, t.value FROM (
				SELECT checkin, value, max(date || src)
				FROM checkin_tag WHERE name = 'branch' AND sign = '*'
				GROUP BY checkin
			) t JOIN checkin c ON c.hash = t.checkin
			UNION
			SELECT p.child, br.branch FROM br
			JOIN checkin_parent p ON p.parent = br.hash AND p.ord = 0
			WHERE NOT EXISTS (SELECT 1 FROM checkin_tag t2
				WHERE t2.checkin = p.child AND t2.name = 'branch' AND t2.sign = '*')
		)
		SELECT hash, branch FROM br;
		UPDATE checkin SET branch = COALESCE(
			(SELECT branch FROM br_walk WHERE br_walk.hash = checkin.hash), 'trunk');`); err != nil {
		return err
	}
	return nil
}

// refreshTicketDoc rebuilds one ticket's search doc from its folded
// current state, inside the fold transaction.
func refreshTicketDoc(tx *sql.Tx, ticket string) error {
	rows, err := tx.Query(`SELECT f.name, f.value FROM ticket_change c
		JOIN ticket_field f ON f.hash = c.hash
		WHERE c.ticket = ? ORDER BY c.date, c.hash, f.name`, ticket)
	if err != nil {
		return err
	}
	defer rows.Close()
	current := map[string]string{}
	var order []string
	for rows.Next() {
		var name, value string
		if err := rows.Scan(&name, &value); err != nil {
			return err
		}
		if _, seen := current[name]; !seen {
			order = append(order, name)
		}
		current[name] = value // latest assignment wins
	}
	if err := rows.Err(); err != nil {
		return err
	}
	var b strings.Builder
	for _, name := range order {
		b.WriteString(current[name])
		b.WriteByte('\n')
	}
	label := current["title"]
	if label == "" && len(ticket) >= 10 {
		label = ticket[:10]
	}
	_, err = tx.Exec(`INSERT OR REPLACE INTO search_doc (hash, kind, ref, label, text)
		VALUES (?, 'ticket', ?, ?, ?)`, "ticket:"+ticket, ticket, label, b.String())
	return err
}

// searchDoc maps one immutable artifact to its search-corpus row; empty
// kind means "not searched here" (tickets go through refreshTicketDoc).
// Every wiki edit is indexed, so hits can land on historical revisions;
// queries dedupe by ref.
func searchDoc(hash string, a *artifact.Artifact) (kind, ref, label, text string) {
	switch a.Type {
	case artifact.Manifest:
		first, _, _ := strings.Cut(a.Comment, "\n")
		return "checkin", hash, first, a.Comment
	case artifact.Wiki:
		return "wiki", a.WikiTitle, a.WikiTitle, a.WikiTitle + "\n" + string(a.Body)
	case artifact.Technote:
		label := a.Comment // C card is the optional one-line title
		if label == "" {
			label, _, _ = strings.Cut(string(a.Body), "\n")
		}
		return "technote", a.EventID, label, string(a.Body)
	case artifact.Forum:
		label := a.ThreadTitle
		if label == "" {
			label, _, _ = strings.Cut(string(a.Body), "\n")
		}
		root := a.ThreadRoot
		if root == "" {
			root = hash
		}
		return "forum", root, label, string(a.Body)
	}
	return "", "", "", ""
}

// SearchResult is one hit, deduplicated by (kind, ref). Snippet marks
// matches with ⟦…⟧ — characters that cannot collide with markdown or
// bracket-bearing content (ticket 060); renderers turn them into <mark>.
type SearchResult struct {
	Kind    string `json:"kind"`
	Ref     string `json:"ref"`
	Label   string `json:"label"`
	Snippet string `json:"snippet"`
}

// Search runs an FTS5 query over the corpus. The user string is
// re-quoted token-by-token so FTS operator syntax cannot inject or
// error. Ranking is bm25 with ref as the deterministic tiebreak.
func (ix *Index) Search(query string, limit int) ([]SearchResult, error) {
	tokens := strings.Fields(query)
	if len(tokens) == 0 {
		return nil, nil
	}
	for i, tok := range tokens {
		tokens[i] = `"` + strings.ReplaceAll(tok, `"`, `""`) + `"`
	}
	// The MATERIALIZED hint stops SQLite flattening the CTE into the
	// aggregate query, where bm25() is not usable.
	rows, err := ix.db.Query(`
		WITH hits AS MATERIALIZED (
			SELECT d.kind AS kind, d.ref AS ref, d.label AS label,
				snippet(search_fts, 0, '⟦', '⟧', '…', 12) AS snip,
				bm25(search_fts) AS rank
			FROM search_fts JOIN search_doc d ON d.rowid = search_fts.rowid
			WHERE search_fts MATCH ?
		)
		SELECT kind, ref, label, snip, min(rank) AS best FROM hits
		GROUP BY kind, ref ORDER BY best, kind, ref LIMIT ?`,
		strings.Join(tokens, " "), limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []SearchResult
	for rows.Next() {
		var r SearchResult
		var rank float64
		if err := rows.Scan(&r.Kind, &r.Ref, &r.Label, &r.Snippet, &rank); err != nil {
			return nil, err
		}
		out = append(out, r)
	}
	return out, rows.Err()
}

// Reset drops all derived state — the reindex path (0001 §5). The next
// Fold rebuilds from a zero cursor.
func (ix *Index) Reset() error {
	for _, table := range []string{
		"checkin", "checkin_parent", "checkin_tag", "wiki_edit",
		"ticket_change", "ticket_field", "technote", "forum_post", "search_doc",
	} {
		if _, err := ix.db.Exec("DELETE FROM " + table); err != nil {
			return err
		}
	}
	return nil
}

// TimelineEntry is one event on the unified timeline: a check-in
// (kind "ci"), a wiki edit ("w"), a ticket change ("t"), a technote
// ("e"), or a forum post ("f"). Ref is the page target: the check-in
// hash, the wiki title, the ticket id, the technote event id, or the
// forum thread root.
type TimelineEntry struct {
	Kind    string   `json:"kind"`
	Hash    string   `json:"hash"`
	Ref     string   `json:"ref"`
	Date    string   `json:"date"`
	User    string   `json:"user"`
	Comment string   `json:"comment"`
	Branch  string   `json:"branch"`
	IsLeaf  bool     `json:"isLeaf"`
	IsMerge bool     `json:"isMerge"`
	Parents []string `json:"parents"`
}

// CheckinByPrefix resolves a hash prefix to exactly one check-in and its
// parents. ok is false when the prefix matches zero or several — an
// ambiguous prefix never resolves (the fossil rule).
func (ix *Index) CheckinByPrefix(prefix string) (TimelineEntry, []string, bool, error) {
	var e TimelineEntry
	rows, err := ix.db.Query(`SELECT hash, date, user, comment, branch FROM checkin
		WHERE hash LIKE ? || '%' LIMIT 2`, prefix)
	if err != nil {
		return e, nil, false, err
	}
	defer rows.Close()
	n := 0
	for rows.Next() {
		n++
		if n > 1 {
			return TimelineEntry{}, nil, false, nil // ambiguous
		}
		if err := rows.Scan(&e.Hash, &e.Date, &e.User, &e.Comment, &e.Branch); err != nil {
			return e, nil, false, err
		}
	}
	if err := rows.Err(); err != nil || n == 0 {
		return e, nil, false, err
	}
	prows, err := ix.db.Query(`SELECT parent FROM checkin_parent WHERE child = ? ORDER BY ord`, e.Hash)
	if err != nil {
		return e, nil, false, err
	}
	defer prows.Close()
	var parents []string
	for prows.Next() {
		var p string
		if err := prows.Scan(&p); err != nil {
			return e, nil, false, err
		}
		parents = append(parents, p)
	}
	return e, parents, true, prows.Err()
}

// WikiTitle is one page in the wiki listing: its name and latest edit.
type WikiTitle struct {
	Title string `json:"title"`
	Date  string `json:"date"`
	User  string `json:"user"`
}

// WikiTitles lists every wiki page by its most recent edit (fossil
// semantics: latest wins), sorted by title for determinism.
func (ix *Index) WikiTitles() ([]WikiTitle, error) {
	rows, err := ix.db.Query(`SELECT title, max(date || ' ' || hash), user FROM wiki_edit
		GROUP BY title ORDER BY title`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []WikiTitle
	for rows.Next() {
		var w WikiTitle
		var dateHash string
		if err := rows.Scan(&w.Title, &dateHash, &w.User); err != nil {
			return nil, err
		}
		w.Date, _, _ = strings.Cut(dateHash, " ")
		out = append(out, w)
	}
	return out, rows.Err()
}

// WikiEdit is one page's content at one revision.
type WikiEdit struct {
	Hash     string
	Title    string
	Date     string
	User     string
	Mimetype string
	Body     []byte
}

// WikiPage returns the latest edit of one page; ok is false when the
// page does not exist. Date ties break by hash, deterministically.
func (ix *Index) WikiPage(title string) (WikiEdit, bool, error) {
	return ix.WikiRevision(title, "")
}

// WikiRevision returns one edit of a page: the revision whose artifact
// hash starts with rev, or the latest when rev is empty. ok is false
// when nothing matches (an ambiguous prefix picks nothing).
func (ix *Index) WikiRevision(title, rev string) (WikiEdit, bool, error) {
	var w WikiEdit
	var err error
	if rev == "" {
		err = ix.db.QueryRow(`SELECT hash, title, date, user, mimetype, body FROM wiki_edit
			WHERE title = ? ORDER BY date DESC, hash DESC LIMIT 1`, title).
			Scan(&w.Hash, &w.Title, &w.Date, &w.User, &w.Mimetype, &w.Body)
	} else {
		rows, qerr := ix.db.Query(`SELECT hash, title, date, user, mimetype, body FROM wiki_edit
			WHERE title = ? AND hash LIKE ? || '%' LIMIT 2`, title, rev)
		if qerr != nil {
			return w, false, qerr
		}
		defer rows.Close()
		n := 0
		for rows.Next() {
			n++
			if n > 1 {
				return WikiEdit{}, false, nil // ambiguous
			}
			if err := rows.Scan(&w.Hash, &w.Title, &w.Date, &w.User, &w.Mimetype, &w.Body); err != nil {
				return w, false, err
			}
		}
		if n == 0 {
			return w, false, rows.Err()
		}
		return w, true, rows.Err()
	}
	if err == sql.ErrNoRows {
		return w, false, nil
	}
	return w, err == nil, err
}

// WikiRev is one entry in a page's edit history.
type WikiRev struct {
	Hash string `json:"hash"`
	Date string `json:"date"`
	User string `json:"user"`
}

// WikiHistory lists every recorded edit of one page, newest first.
func (ix *Index) WikiHistory(title string) ([]WikiRev, error) {
	rows, err := ix.db.Query(`SELECT hash, date, user FROM wiki_edit
		WHERE title = ? ORDER BY date DESC, hash DESC`, title)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []WikiRev
	for rows.Next() {
		var r WikiRev
		if err := rows.Scan(&r.Hash, &r.Date, &r.User); err != nil {
			return nil, err
		}
		out = append(out, r)
	}
	return out, rows.Err()
}

// Technote is one technote at its latest edit.
type Technote struct {
	Hash     string `json:"hash"`
	Event    string `json:"event"`
	Date     string `json:"date"`
	User     string `json:"user"`
	Mimetype string `json:"mimetype"`
	Comment  string `json:"comment"` // C card: the timeline/listing title
	Body     string `json:"body"`
}

// Technotes lists each technote at its latest edit, newest event first.
func (ix *Index) Technotes() ([]Technote, error) {
	rows, err := ix.db.Query(`SELECT hash, event, date, user, mimetype, comment, body FROM technote t
		WHERE edited = (SELECT max(t2.edited) FROM technote t2 WHERE t2.event = t.event)
		GROUP BY event
		ORDER BY date DESC, event`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Technote
	for rows.Next() {
		var n Technote
		if err := rows.Scan(&n.Hash, &n.Event, &n.Date, &n.User, &n.Mimetype, &n.Comment, &n.Body); err != nil {
			return nil, err
		}
		out = append(out, n)
	}
	return out, rows.Err()
}

// TechnoteByEvent returns the latest edit of one technote by event-id
// prefix; ok is false on zero or ambiguous matches.
func (ix *Index) TechnoteByEvent(prefix string) (Technote, bool, error) {
	rows, err := ix.db.Query(`SELECT hash, event, date, user, mimetype, comment, body FROM technote t
		WHERE event LIKE ? || '%'
		AND edited = (SELECT max(t2.edited) FROM technote t2 WHERE t2.event = t.event)
		GROUP BY event LIMIT 2`, prefix)
	if err != nil {
		return Technote{}, false, err
	}
	defer rows.Close()
	var found []Technote
	for rows.Next() {
		var n Technote
		if err := rows.Scan(&n.Hash, &n.Event, &n.Date, &n.User, &n.Mimetype, &n.Comment, &n.Body); err != nil {
			return Technote{}, false, err
		}
		found = append(found, n)
	}
	if len(found) != 1 {
		return Technote{}, false, rows.Err()
	}
	return found[0], true, rows.Err()
}

// ForumThread is a thread summary for the forum listing.
type ForumThread struct {
	Root    string `json:"root"`
	Title   string `json:"title"`
	Posts   int    `json:"posts"`
	Updated string `json:"updated"`
	User    string `json:"user"` // opener
}

// ForumThreads lists threads, most-recently-active first. The title is
// the opener's H card; a titleless thread falls back to its first line.
func (ix *Index) ForumThreads() ([]ForumThread, error) {
	rows, err := ix.db.Query(`SELECT p.thread,
			COALESCE((SELECT CASE WHEN o.title != '' THEN o.title
				ELSE substr(o.body, 1, 80) END FROM forum_post o WHERE o.hash = p.thread), ''),
			count(*), max(p.date),
			COALESCE((SELECT o.user FROM forum_post o WHERE o.hash = p.thread), '')
		FROM forum_post p
		GROUP BY p.thread
		ORDER BY max(p.date) DESC, p.thread`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []ForumThread
	for rows.Next() {
		var t ForumThread
		if err := rows.Scan(&t.Root, &t.Title, &t.Posts, &t.Updated, &t.User); err != nil {
			return nil, err
		}
		out = append(out, t)
	}
	return out, rows.Err()
}

// ForumPost is one post within a thread.
type ForumPost struct {
	Hash   string `json:"hash"`
	Parent string `json:"parent"`
	Date   string `json:"date"`
	User   string `json:"user"`
	Body   string `json:"body"`
}

// ForumThreadPosts returns every post of a thread resolved by root-hash
// prefix, oldest first (reading order); ok is false on no match.
func (ix *Index) ForumThreadPosts(prefix string) (title string, posts []ForumPost, ok bool, err error) {
	var root string
	err = ix.db.QueryRow(`SELECT DISTINCT thread FROM forum_post
		WHERE thread LIKE ? || '%' LIMIT 1`, prefix).Scan(&root)
	if err == sql.ErrNoRows {
		return "", nil, false, nil
	}
	if err != nil {
		return "", nil, false, err
	}
	ix.db.QueryRow(`SELECT CASE WHEN title != '' THEN title ELSE substr(body, 1, 80) END
		FROM forum_post WHERE hash = ?`, root).Scan(&title)
	rows, err := ix.db.Query(`SELECT hash, parent, date, user, body FROM forum_post
		WHERE thread = ? ORDER BY date, hash`, root)
	if err != nil {
		return "", nil, false, err
	}
	defer rows.Close()
	for rows.Next() {
		var p ForumPost
		if err := rows.Scan(&p.Hash, &p.Parent, &p.Date, &p.User, &p.Body); err != nil {
			return "", nil, false, err
		}
		posts = append(posts, p)
	}
	return title, posts, len(posts) > 0, rows.Err()
}

// TicketChange is one recorded change with the fields it set.
type TicketChange struct {
	Hash   string           `json:"hash"`
	Date   string           `json:"date"`
	User   string           `json:"user"`
	Fields []TicketFieldSet `json:"fields"`
}

// TicketFieldSet is one field assignment within a change.
type TicketFieldSet struct {
	Name  string `json:"name"`
	Value string `json:"value"`
}

// TicketState is a ticket's folded state: every field's latest value
// (fossil semantics), plus the change history in order.
type TicketState struct {
	ID      string           `json:"id"`
	Created string           `json:"created"`
	Updated string           `json:"updated"`
	Fields  []TicketFieldSet `json:"fields"`
	Changes []TicketChange   `json:"changes"`
}

// TicketSummary is the list-surface fold: current title and status
// without materializing change histories.
type TicketSummary struct {
	ID      string `json:"id"`
	Created string `json:"created"`
	Updated string `json:"updated"`
	Title   string `json:"title"`
	Status  string `json:"status"`
}

// TicketSummaries folds the list surface in SQL — newest-updated first,
// id as tiebreak so rebuilds order identically. before is a composite
// "updated~ticket" cursor (empty = first page); more reports whether an
// older page follows. The per-ticket field probes ride the
// (ticket, date) index; folding whole histories in Go took >1s on a
// real corpus (tcl: 8.9k tickets, 51k changes).
func (ix *Index) TicketSummaries(limit int, before string) ([]TicketSummary, bool, error) {
	if limit <= 0 {
		limit = 50 // the form codec defaults this; direct calls get the same
	}
	bu, bt, _ := strings.Cut(before, "~")
	rows, err := ix.db.Query(`
		SELECT c.ticket, min(c.date), max(c.date),
			COALESCE((SELECT f.value FROM ticket_change c2
				JOIN ticket_field f ON f.hash = c2.hash AND f.name = 'title'
				WHERE c2.ticket = c.ticket
				ORDER BY c2.date DESC, c2.hash DESC LIMIT 1), ''),
			COALESCE((SELECT f.value FROM ticket_change c2
				JOIN ticket_field f ON f.hash = c2.hash AND f.name = 'status'
				WHERE c2.ticket = c.ticket
				ORDER BY c2.date DESC, c2.hash DESC LIMIT 1), '')
		FROM ticket_change c
		GROUP BY c.ticket
		HAVING ?1 = '' OR max(c.date) < ?1 OR (max(c.date) = ?1 AND c.ticket > ?2)
		ORDER BY max(c.date) DESC, c.ticket
		LIMIT ?3`, bu, bt, limit+1)
	if err != nil {
		return nil, false, err
	}
	defer rows.Close()
	var out []TicketSummary
	for rows.Next() {
		var t TicketSummary
		if err := rows.Scan(&t.ID, &t.Created, &t.Updated, &t.Title, &t.Status); err != nil {
			return nil, false, err
		}
		out = append(out, t)
	}
	more := len(out) > limit
	if more {
		out = out[:limit]
	}
	return out, more, rows.Err()
}

// ticketStates folds change histories into current state for tickets
// matching the WHERE clause. Change order is (date, hash) so the fold is
// deterministic across rebuilds.
func (ix *Index) ticketStates(where string, args ...any) ([]TicketState, error) {
	rows, err := ix.db.Query(`
		SELECT c.ticket, c.hash, c.date, c.user, f.name, f.value
		FROM ticket_change c JOIN ticket_field f ON f.hash = c.hash
		`+where+`
		ORDER BY c.ticket, c.date, c.hash, f.name`, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var out []TicketState
	var cur *TicketState
	for rows.Next() {
		var ticket, hash, date, user, name, value string
		if err := rows.Scan(&ticket, &hash, &date, &user, &name, &value); err != nil {
			return nil, err
		}
		if cur == nil || cur.ID != ticket {
			out = append(out, TicketState{ID: ticket, Created: date})
			cur = &out[len(out)-1]
		}
		cur.Updated = date
		if n := len(cur.Changes); n == 0 || cur.Changes[n-1].Hash != hash {
			cur.Changes = append(cur.Changes, TicketChange{Hash: hash, Date: date, User: user})
		}
		ch := &cur.Changes[len(cur.Changes)-1]
		ch.Fields = append(ch.Fields, TicketFieldSet{Name: name, Value: value})
		// Fold: latest assignment wins.
		set := false
		for i := range cur.Fields {
			if cur.Fields[i].Name == name {
				cur.Fields[i].Value = value
				set = true
			}
		}
		if !set {
			cur.Fields = append(cur.Fields, TicketFieldSet{Name: name, Value: value})
		}
	}
	return out, rows.Err()
}

// TicketByPrefix folds one ticket resolved by id prefix; ok is false on
// zero or multiple matches.
func (ix *Index) TicketByPrefix(prefix string) (TicketState, bool, error) {
	all, err := ix.ticketStates(`WHERE c.ticket IN (
		SELECT DISTINCT ticket FROM ticket_change WHERE ticket LIKE ? || '%')`, prefix)
	if err != nil {
		return TicketState{}, false, err
	}
	var found *TicketState
	for i := range all {
		if strings.HasPrefix(all[i].ID, prefix) {
			if found != nil {
				return TicketState{}, false, nil // ambiguous
			}
			found = &all[i]
		}
	}
	if found == nil {
		return TicketState{}, false, nil
	}
	return *found, true, nil
}

// Timeline lists events newest first; ties break by hash so the
// listing is deterministic across rebuilds (the disposability
// property). kind filters to "ci", "w", or "t" ("" or "all" means
// every kind); a non-empty branch restricts to that branch's check-ins
// (fossil's rule). before is a cursor from a previous page's Next —
// "date~hash" — with a bare check-in hash prefix accepted for
// compatibility; more reports whether older events remain.
func (ix *Index) Timeline(limit int, before, branch, kind string) ([]TimelineEntry, bool, error) {
	conds, args := []string{}, []any{}
	if before != "" {
		bDate, bHash, ok := strings.Cut(before, "~")
		if !ok {
			b, _, found, err := ix.CheckinByPrefix(before)
			if err != nil {
				return nil, false, err
			}
			if !found {
				return nil, false, nil // unknown cursor: empty page, not an error
			}
			bDate, bHash = b.Date, b.Hash
		}
		conds = append(conds, `(date < ? OR (date = ? AND hash < ?))`)
		args = append(args, bDate, bDate, bHash)
	}
	ciWhere := ""
	if branch != "" {
		ciWhere = `WHERE branch = ?`
		kind = "ci" // a branch filter implies check-ins only
	}
	if kind != "" && kind != "all" {
		conds = append(conds, `kind = ?`)
	}
	where := ""
	if len(conds) > 0 {
		where = "WHERE " + strings.Join(conds, " AND ")
	}

	// Assemble args in placeholder order: ci-branch, cursor/kind, limit.
	var all []any
	if branch != "" {
		all = append(all, branch)
	}
	all = append(all, args...)
	if kind != "" && kind != "all" {
		all = append(all, kind)
	}
	all = append(all, limit+1)

	rows, err := ix.db.Query(`WITH ev AS (
			SELECT 'ci' AS kind, hash, hash AS ref, date, user, comment, branch,
				NOT EXISTS (SELECT 1 FROM checkin_parent lp WHERE lp.parent = checkin.hash) AS isleaf
			FROM checkin `+ciWhere+`
			UNION ALL
			SELECT 'w', hash, title, date, user, 'Wiki: ' || title, '', 0 FROM wiki_edit
			UNION ALL
			SELECT 't', c.hash, c.ticket, c.date, c.user,
				'Ticket [' || substr(c.ticket, 1, 10) || '] ' || COALESCE(d.label, ''), '', 0
			FROM ticket_change c LEFT JOIN search_doc d ON d.hash = 'ticket:' || c.ticket
			UNION ALL
			SELECT 'e', hash, event, date, user,
				'Technote: ' || CASE WHEN comment != '' THEN comment ELSE event END, '', 0 FROM technote
			UNION ALL
			SELECT 'f', hash, thread, date, user,
				'Forum: ' || CASE WHEN title != '' THEN title ELSE substr(body, 1, 60) END, '', 0
			FROM forum_post
		)
		SELECT kind, hash, ref, date, user, comment, branch, isleaf FROM ev
		`+where+` ORDER BY date DESC, hash DESC LIMIT ?`, all...)
	if err != nil {
		return nil, false, err
	}
	defer rows.Close()
	var out []TimelineEntry
	for rows.Next() {
		var e TimelineEntry
		if err := rows.Scan(&e.Kind, &e.Hash, &e.Ref, &e.Date, &e.User, &e.Comment, &e.Branch, &e.IsLeaf); err != nil {
			return nil, false, err
		}
		out = append(out, e)
	}
	if err := rows.Err(); err != nil {
		return nil, false, err
	}
	more := false
	if len(out) > limit {
		out, more = out[:limit], true
	}
	if err := ix.fillParents(out); err != nil {
		return nil, false, err
	}
	return out, more, nil
}

// fillParents attaches each entry's ordered parent list (primary first)
// and the merge flag — the graph's edge data.
func (ix *Index) fillParents(entries []TimelineEntry) error {
	byHash := map[string]*TimelineEntry{}
	for i := range entries {
		if entries[i].Kind != "" && entries[i].Kind != "ci" {
			continue // only check-ins have DAG edges
		}
		byHash[entries[i].Hash] = &entries[i]
	}
	for hash, e := range byHash {
		rows, err := ix.db.Query(`SELECT parent FROM checkin_parent
			WHERE child = ? ORDER BY ord`, hash)
		if err != nil {
			return err
		}
		for rows.Next() {
			var p string
			if err := rows.Scan(&p); err != nil {
				rows.Close()
				return err
			}
			e.Parents = append(e.Parents, p)
		}
		if err := rows.Err(); err != nil {
			rows.Close()
			return err
		}
		rows.Close()
		e.IsMerge = len(e.Parents) > 1
	}
	return nil
}

// CheckinCount reports the indexed check-in total — the fleet page's
// headline number.
func (ix *Index) CheckinCount() (int, error) {
	var n int
	err := ix.db.QueryRow(`SELECT count(*) FROM checkin`).Scan(&n)
	return n, err
}