// 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"
"errors"
"fmt"
"slices"
"strings"
"sync"
"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);
-- Branch listing and per-branch tip lookups group by branch and take the
-- newest date; without this they degrade to a full scan per branch.
CREATE INDEX IF NOT EXISTS checkin_by_branch ON checkin (branch, date);
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,
mimetype TEXT NOT NULL DEFAULT '', -- N card ('' = Fossil wiki, per fossil)
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 attachment (
hash TEXT PRIMARY KEY, -- the attachment artifact (A card)
name TEXT NOT NULL, -- filename as attached
target TEXT NOT NULL, -- ticket id, wiki page name, or technote event id
src TEXT NOT NULL, -- content artifact; '' is fossil's DELETION form
date TEXT NOT NULL,
user TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS attachment_by_target ON attachment (target, name, 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)
);
-- ticket is the folded CURRENT state of each ticket, one row per ticket.
--
-- The list page derived this per request: GROUP BY over every change row
-- (tk: 35,903), plus two correlated subqueries per ticket to find the
-- newest title and status — 11,618 of them, each with its own temp
-- b-tree — and a HAVING that filtered after grouping, so paging saved
-- nothing. 200ms on tk with no zit call in sight.
--
-- Same lesson as the timeline: fold it once at write time, read it by
-- index. The fold already computes exactly this state for the search
-- doc, so the summary is written from the same pass rather than derived
-- a second way that could disagree with it.
CREATE TABLE IF NOT EXISTS ticket (
id TEXT PRIMARY KEY,
created TEXT NOT NULL,
updated TEXT NOT NULL,
title TEXT NOT NULL,
status TEXT NOT NULL
);
-- The list's ordering: newest-updated first, id as the tiebreak so a
-- rebuild pages identically.
CREATE INDEX IF NOT EXISTS ticket_by_updated ON ticket (updated DESC, id);
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'
);
-- event is the timeline, denormalized at WRITE time.
--
-- It was assembled at read time from a five-way UNION ALL over checkin,
-- wiki_edit, ticket_change, technote and forum_post. That plan ends in
-- USE TEMP B-TREE FOR ORDER BY: every request materialized and sorted
-- every event in the repository to return fifty. On tk (61k events) the
-- timeline cost 536ms and the repository list — which asks each store
-- for its newest event — cost 399ms for eleven repos.
--
-- Fossil solved this long ago and this is its shape: one table with a
-- type column and an index on time (event_i1 ON event(mtime)), so the
-- timeline is an index walk that stops at the limit. Fossil renders the
-- same page in 3-4ms whether the repository holds 503 check-ins or
-- 36,702, which is the behaviour worth copying.
--
-- The comment for a TICKET is deliberately not stored here: its label
-- comes from the folded ticket doc, which is refreshed after the change
-- rows land, so a value written now would be stale. It is joined for the
-- returned page only, at most one page of rows, by primary key.
CREATE TABLE IF NOT EXISTS event (
hash TEXT PRIMARY KEY, -- the artifact; globally unique across kinds
kind TEXT NOT NULL, -- ci | w | t | e | f
ref TEXT NOT NULL, -- link target: check-in hash, wiki title, ticket id…
date TEXT NOT NULL,
user TEXT NOT NULL,
comment TEXT NOT NULL,
branch TEXT NOT NULL DEFAULT ''
);
-- The timeline's orderings. DESC in the index matters: the page reads
-- newest-first, and a mismatched direction costs the sort we are here to
-- remove.
CREATE INDEX IF NOT EXISTS event_by_date ON event (date DESC, hash DESC);
CREATE INDEX IF NOT EXISTS event_by_user ON event (user);
CREATE INDEX IF NOT EXISTS event_by_kind ON event (kind, date DESC, hash DESC);
CREATE INDEX IF NOT EXISTS event_by_branch ON event (branch, date DESC, hash DESC);
-- Maintained by trigger rather than by the fold, for the same reason
-- search_fts is: five call sites that each remember to write a second
-- table is five chances for the timeline to disagree with the tables it
-- summarizes, and that disagreement would be invisible until someone
-- noticed a missing row.
-- contributor is the fleet's people surface, folded at write time for
-- the same reason the event table is: GROUP BY user over tcl's event
-- table is a 395ms scan with a temp B-tree, and an index only gets it
-- to 98ms because the work is the grouping, not the lookup. Across the
-- fleet that was half a second on a landing page.
--
-- Refreshed per batch for the users a batch touched, NOT by counting
-- triggers: SQLite does not fire DELETE triggers for rows a REPLACE
-- displaces unless recursive_triggers is on, so an increment/decrement
-- pair silently double-counts every event that is ever rewritten. A
-- recount over one user is exact whatever the writer did, and
-- event_by_user makes it an index scan.
CREATE TABLE IF NOT EXISTS contributor (
user TEXT PRIMARY KEY,
events INTEGER NOT NULL,
first TEXT NOT NULL,
last TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS contributor_by_events ON contributor (events DESC, user);
CREATE TRIGGER IF NOT EXISTS event_ci_ai AFTER INSERT ON checkin BEGIN
INSERT OR REPLACE INTO event (hash, kind, ref, date, user, comment, branch)
VALUES (new.hash, 'ci', new.hash, new.date, new.user, new.comment, new.branch);
END;
CREATE TRIGGER IF NOT EXISTS event_ci_au AFTER UPDATE ON checkin BEGIN
INSERT OR REPLACE INTO event (hash, kind, ref, date, user, comment, branch)
VALUES (new.hash, 'ci', new.hash, new.date, new.user, new.comment, new.branch);
END;
CREATE TRIGGER IF NOT EXISTS event_ci_ad AFTER DELETE ON checkin BEGIN
DELETE FROM event WHERE hash = old.hash;
END;
CREATE TRIGGER IF NOT EXISTS event_w_ai AFTER INSERT ON wiki_edit BEGIN
INSERT OR REPLACE INTO event (hash, kind, ref, date, user, comment, branch)
VALUES (new.hash, 'w', new.title, new.date, new.user, 'Wiki: ' || new.title, '');
END;
CREATE TRIGGER IF NOT EXISTS event_w_ad AFTER DELETE ON wiki_edit BEGIN
DELETE FROM event WHERE hash = old.hash;
END;
CREATE TRIGGER IF NOT EXISTS event_t_ai AFTER INSERT ON ticket_change BEGIN
INSERT OR REPLACE INTO event (hash, kind, ref, date, user, comment, branch)
VALUES (new.hash, 't', new.ticket, new.date, new.user, '', '');
END;
CREATE TRIGGER IF NOT EXISTS event_t_ad AFTER DELETE ON ticket_change BEGIN
DELETE FROM event WHERE hash = old.hash;
END;
CREATE TRIGGER IF NOT EXISTS event_e_ai AFTER INSERT ON technote BEGIN
INSERT OR REPLACE INTO event (hash, kind, ref, date, user, comment, branch)
VALUES (new.hash, 'e', new.event, new.date, new.user,
'Technote: ' || CASE WHEN new.comment != '' THEN new.comment ELSE new.event END, '');
END;
CREATE TRIGGER IF NOT EXISTS event_e_ad AFTER DELETE ON technote BEGIN
DELETE FROM event WHERE hash = old.hash;
END;
CREATE TRIGGER IF NOT EXISTS event_f_ai AFTER INSERT ON forum_post BEGIN
INSERT OR REPLACE INTO event (hash, kind, ref, date, user, comment, branch)
VALUES (new.hash, 'f', new.thread, new.date, new.user,
'Forum: ' || CASE WHEN new.title != '' THEN new.title ELSE substr(new.body, 1, 60) END, '');
END;
CREATE TRIGGER IF NOT EXISTS event_f_ad AFTER DELETE ON forum_post BEGIN
DELETE FROM event WHERE hash = old.hash;
END;
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
// countMu guards a memo of Counts. The section counts go in the
// repository nav, so they are read on EVERY page render, and on the
// largest store in the corpus (tk: 147k artifacts) computing them
// costs ~110ms — dominated by the ticket and check-in scans. Paying
// that per request would put a tenth of a second on every page in
// the forge to render a handful of numbers.
//
// They only move when a fold does, so the memo is invalidated there
// rather than expired on a timer: a nav count that lags a write is
// the kind of small lie that makes a reader distrust the rest.
countMu sync.Mutex
counts *Counts
}
// Counts is a repository's per-section content census — what the nav
// needs to say how much of each kind a store holds, and to tell an empty
// section from a full one.
type Counts struct {
Checkins int `json:"checkins"`
WikiPages int `json:"wikiPages"`
Tickets int `json:"tickets"`
// ForumThreads counts THREADS, not posts: the forum page lists
// threads, so a count of posts would not match what a reader finds
// when they follow the link.
ForumThreads int `json:"forumThreads"`
Technotes int `json:"technotes"`
// Contributors is the number of distinct users who have authored
// anything. "Is anybody working on this" is a question a listing can
// answer and a commit message cannot.
Contributors int `json:"contributors"`
}
// invalidateCounts drops the memo. Called wherever folded state changes.
func (ix *Index) invalidateCounts() {
ix.countMu.Lock()
ix.counts = nil
ix.countMu.Unlock()
}
// Counts returns the per-section census, computing it at most once per
// fold.
func (ix *Index) Counts() (Counts, error) {
ix.countMu.Lock()
defer ix.countMu.Unlock()
if ix.counts != nil {
return *ix.counts, nil
}
var c Counts
for _, q := range []struct {
sql string
at *int
}{
{`SELECT count(*) FROM checkin`, &c.Checkins},
{`SELECT count(DISTINCT title) FROM wiki_edit`, &c.WikiPages},
{`SELECT count(DISTINCT ticket) FROM ticket_change`, &c.Tickets},
{`SELECT count(*) FROM forum_post WHERE hash = thread`, &c.ForumThreads},
{`SELECT count(*) FROM technote`, &c.Technotes},
{`SELECT count(DISTINCT user) FROM event`, &c.Contributors},
} {
if err := ix.db.QueryRow(q.sql).Scan(q.at); err != nil {
return Counts{}, err
}
}
ix.counts = &c
return c, nil
}
// 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 ''`,
`ALTER TABLE forum_post ADD COLUMN mimetype 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)
}
}
ix := &Index{db: db}
if err := ix.backfillEvents(); err != nil {
db.Close()
return nil, fmt.Errorf("index: backfill events: %w", err)
}
if err := ix.backfillContributors(); err != nil {
ix.db.Close()
return nil, fmt.Errorf("index: backfill contributors: %w", err)
}
if err := ix.backfillTickets(); err != nil {
db.Close()
return nil, fmt.Errorf("index: backfill tickets: %w", err)
}
return ix, nil
}
// refreshContributor recomputes one person's row from the event table.
// A recount rather than an adjustment: it is correct no matter how the
// event rows were written, and with event_by_user it touches only that
// person's rows. A person whose events all disappeared loses their row
// rather than lingering at zero.
func refreshContributor(tx *sql.Tx, user string) error {
if _, err := tx.Exec(`INSERT OR REPLACE INTO contributor (user, events, first, last)
SELECT user, count(*), min(date), max(date) FROM event
WHERE user = ? GROUP BY user`, user); err != nil {
return err
}
_, err := tx.Exec(`DELETE FROM contributor WHERE user = ?
AND NOT EXISTS (SELECT 1 FROM event WHERE user = ?)`, user, user)
return err
}
// backfillContributors folds the people table from an event table
// written before it existed — same reasoning as backfillEvents, and
// same idempotence: it runs only when the table is empty and events
// are not.
func (ix *Index) backfillContributors() error {
var have, events int
if err := ix.db.QueryRow(`SELECT count(*) FROM contributor`).Scan(&have); err != nil {
return err
}
if err := ix.db.QueryRow(`SELECT count(*) FROM event`).Scan(&events); err != nil {
return err
}
if have > 0 || events == 0 {
return nil
}
_, err := ix.db.Exec(`INSERT OR REPLACE INTO contributor (user, events, first, last)
SELECT user, count(*), min(date), max(date) FROM event GROUP BY user`)
return err
}
// backfillEvents populates the timeline table from indexes written
// before it existed. The triggers only fire on new writes, so without
// this an upgraded index serves an EMPTY timeline until someone happens
// to reindex — derived data, technically repairable, but a blank
// timeline reads as data loss and nobody would connect it to an upgrade.
//
// Cheap and idempotent: it runs only when the table is empty and the
// sources are not, and INSERT OR REPLACE keyed by hash makes a repeat
// harmless.
func (ix *Index) backfillEvents() error {
var events, checkins int
if err := ix.db.QueryRow(`SELECT count(*) FROM event`).Scan(&events); err != nil {
return err
}
if err := ix.db.QueryRow(`SELECT count(*) FROM checkin`).Scan(&checkins); err != nil {
return err
}
if events > 0 || checkins == 0 {
return nil
}
tx, err := ix.db.Begin()
if err != nil {
return err
}
defer tx.Rollback()
for _, q := range []string{
`INSERT OR REPLACE INTO event (hash,kind,ref,date,user,comment,branch)
SELECT hash,'ci',hash,date,user,comment,branch FROM checkin`,
`INSERT OR REPLACE INTO event (hash,kind,ref,date,user,comment,branch)
SELECT hash,'w',title,date,user,'Wiki: '||title,'' FROM wiki_edit`,
`INSERT OR REPLACE INTO event (hash,kind,ref,date,user,comment,branch)
SELECT hash,'t',ticket,date,user,'','' FROM ticket_change`,
`INSERT OR REPLACE INTO event (hash,kind,ref,date,user,comment,branch)
SELECT hash,'e',event,date,user,
'Technote: '||CASE WHEN comment!='' THEN comment ELSE event END,'' FROM technote`,
`INSERT OR REPLACE INTO event (hash,kind,ref,date,user,comment,branch)
SELECT hash,'f',thread,date,user,
'Forum: '||CASE WHEN title!='' THEN title ELSE substr(body,1,60) END,'' FROM forum_post`,
} {
if _, err := tx.Exec(q); err != nil {
return err
}
}
return tx.Commit()
}
// backfillTickets populates the folded ticket table for indexes written
// before it existed, for the same reason backfillEvents does: the fold
// only writes it on new changes, so an upgraded index would show an
// EMPTY ticket list until someone reindexed.
//
// This runs the expensive grouped query ONCE, at open, instead of on
// every request — which is the whole point of the table.
func (ix *Index) backfillTickets() error {
var tickets, changes int
if err := ix.db.QueryRow(`SELECT count(*) FROM ticket`).Scan(&tickets); err != nil {
return err
}
if err := ix.db.QueryRow(`SELECT count(*) FROM ticket_change`).Scan(&changes); err != nil {
return err
}
if tickets > 0 || changes == 0 {
return nil
}
_, err := ix.db.Exec(`
INSERT OR REPLACE INTO ticket (id, created, updated, title, status)
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`)
return err
}
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{}
people := 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)
}
if p.Art.User != "" {
people[p.Art.User] = true
}
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)
}
}
// Same shape as the ticket docs above: once per touched person per
// batch, recounted from the event table rather than adjusted.
for user := range people {
if err := refreshContributor(tx, user); err != nil {
return false, fmt.Errorf("index: contributor %s: %w", user, err)
}
}
if err := tx.Commit(); err != nil {
return false, err
}
// The memo is dropped AFTER the commit, so a concurrent reader can
// never repopulate it from the pre-commit state and cache that.
ix.invalidateCounts()
return branchy, nil
}
// 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.Attachment:
// Fossil counts an attachment as an event on whatever it targets,
// which is why a ticket-change census that ignores them reads
// short. An A card with no source is a DELETION of that name on
// that target, not a malformed record, so the row is kept and the
// query resolves it.
if at := a.Attachment; at != nil {
if _, err := tx.Exec(`INSERT OR REPLACE INTO attachment (hash, name, target, src, date, user)
VALUES (?, ?, ?, ?, ?, ?)`,
hash, at.Name, at.Target, at.Source, a.Date, a.User); 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, mimetype, body)
VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
hash, thread, a.InReplyTo, a.ThreadTitle, a.Date, a.User, a.Mimetype, 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]
}
if _, err := tx.Exec(`INSERT OR REPLACE INTO search_doc (hash, kind, ref, label, text)
VALUES (?, 'ticket', ?, ?, ?)`, "ticket:"+ticket, ticket, label, b.String()); err != nil {
return err
}
// The list surface, folded from the same pass. Dates come from the
// (ticket, date) index rather than the rows above, which are ordered
// for field folding and would need a second traversal to bound.
var created, updated string
if err := tx.QueryRow(`SELECT COALESCE(min(date),''), COALESCE(max(date),'')
FROM ticket_change WHERE ticket = ?`, ticket).Scan(&created, &updated); err != nil {
return err
}
_, err = tx.Exec(`INSERT INTO ticket (id, created, updated, title, status)
VALUES (?, ?, ?, ?, ?)
ON CONFLICT (id) DO UPDATE SET created=excluded.created,
updated=excluded.updated, title=excluded.title, status=excluded.status`,
ticket, created, updated, current["title"], current["status"])
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"`
// Rank is bm25, lower being better. It is not serialized: a score
// is only meaningful against the corpus that produced it, so
// publishing it would invite callers to compare scores from
// different repositories as if they were one scale. A fleet-wide
// merge uses it in-process and says plainly that it interleaves.
Rank float64 `json:"-"`
}
// SearchKinds are the document kinds Search can restrict to, which is
// every kind searchDoc produces. A caller naming anything else gets an
// error rather than an empty result, because silently matching nothing
// is indistinguishable from a query that genuinely found nothing.
var SearchKinds = []string{"checkin", "wiki", "ticket", "forum", "technote"}
// 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.
//
// An empty kind searches everything; naming one restricts to it, which
// is what lets a search box mean "in this section" on a section page.
func (ix *Index) Search(query, kind string, limit int) ([]SearchResult, error) {
tokens := strings.Fields(query)
if len(tokens) == 0 {
return nil, nil
}
if kind != "" && !slices.Contains(SearchKinds, kind) {
return nil, fmt.Errorf("index: unknown search kind %q", kind)
}
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
WHERE (? = '' OR kind = ?)
GROUP BY kind, ref ORDER BY best, kind, ref LIMIT ?`,
strings.Join(tokens, " "), kind, kind, limit)
if err != nil {
return nil, err
}
defer rows.Close()
var out []SearchResult
for rows.Next() {
var r SearchResult
if err := rows.Scan(&r.Kind, &r.Ref, &r.Label, &r.Snippet, &r.Rank); err != nil {
return nil, err
}
out = append(out, r)
}
return out, rows.Err()
}
// SearchCount is how many distinct documents a query matches. The
// fleet-wide facet needs a true count, not the number of rows that fit
// under a limit: a facet reading "10" for every repository because ten
// was the page size tells a reader nothing about where to look.
func (ix *Index) SearchCount(query, kind string) (int, error) {
tokens := strings.Fields(query)
if len(tokens) == 0 {
return 0, nil
}
if kind != "" && !slices.Contains(SearchKinds, kind) {
return 0, fmt.Errorf("index: unknown search kind %q", kind)
}
for i, tok := range tokens {
tokens[i] = `"` + strings.ReplaceAll(tok, `"`, `""`) + `"`
}
var n int
err := ix.db.QueryRow(`
SELECT count(*) FROM (
SELECT 1 FROM search_fts JOIN search_doc d ON d.rowid = search_fts.rowid
WHERE search_fts MATCH ? AND (? = '' OR d.kind = ?)
GROUP BY d.kind, d.ref
)`, strings.Join(tokens, " "), kind, kind).Scan(&n)
return n, 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", "ticket", "technote", "forum_post", "search_doc",
// Folded tables last: the source deletes above cascade into
// them through triggers, and clearing them explicitly afterwards
// is what makes a rebuild exact rather than merely consistent —
// a rebuild recomputes them from the source artifacts.
"event", "contributor",
} {
if _, err := ix.db.Exec("DELETE FROM " + table); err != nil {
return err
}
}
ix.invalidateCounts()
return nil
}
// Contributor is one person's activity in a repository, folded at write
// time. Events counts every kind the timeline shows, not just check-ins:
// somebody who only files tickets is a contributor.
type Contributor struct {
User string `json:"user"`
Events int `json:"events"`
First string `json:"first"`
Last string `json:"last"`
}
// Contributors returns the repository's people, busiest first. Reading a
// folded table, so this is an index walk rather than the half-second
// GROUP BY it replaced.
func (ix *Index) Contributors(limit int) ([]Contributor, error) {
if limit <= 0 {
limit = 100
}
rows, err := ix.db.Query(`SELECT user, events, first, last FROM contributor
ORDER BY events DESC, user LIMIT ?`, limit)
if err != nil {
return nil, err
}
defer rows.Close()
var out []Contributor
for rows.Next() {
var c Contributor
if err := rows.Scan(&c.User, &c.Events, &c.First, &c.Last); err != nil {
return nil, err
}
out = append(out, c)
}
return out, rows.Err()
}
// 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"`
// CI is the recorded verdict for this check-in — "ci-pass",
// "ci-fail", or empty when none was written. It comes from a tag
// artifact in the store (0001 §6), not from the engine, so it
// survives cloning and does not require the engine to be reachable.
CI string `json:"ci,omitempty"`
}
// 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"`
Mimetype string `json:"mimetype"`
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, mimetype, 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.Mimetype, &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 is the folded scalar state — status, priority, resolution
// and the rest. Per-change plumbing is NOT here: see Comments.
Fields []TicketFieldSet `json:"fields"`
// Description is the ticket's body (fossil's `comment` field) and
// DescriptionMimetype is how to render it (`cmimetype`).
Description string `json:"description"`
DescriptionMimetype string `json:"descriptionMimetype"`
// Comments is the discussion, oldest first. Fossil keeps each change's
// `icomment` on the CHANGE and never folds it into ticket state; a
// fold would leave one comment standing where a conversation was.
Comments []TicketComment `json:"comments"`
Changes []TicketChange `json:"changes"`
}
// TicketComment is one entry in a ticket's discussion.
type TicketComment struct {
Hash string `json:"hash"`
Date string `json:"date"`
// Login is the authenticated account; Username is what the commenter
// typed. Fossil shows both when they differ ("claiming to be"),
// because on an open tracker they need not agree.
Login string `json:"login"`
Username string `json:"username"`
Mimetype string `json:"mimetype"`
Body string `json:"body"`
}
// perChange names the ticket fields that describe ONE change rather than
// the ticket's state. Folding them produces a field table full of
// plumbing and, worse, collapses a whole discussion into whichever
// comment happened to be last.
// cmimetype is deliberately absent: it describes the FOLDED comment, so
// it is ticket state and folds with it.
var perChange = map[string]bool{
"icomment": true, "mimetype": true, "login": true, "username": true,
}
// 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 reads the folded ticket table — newest-updated first,
// id as tiebreak so rebuilds page identically. before is a composite
// "updated~ticket" cursor (empty = first page); more reports whether an
// older page follows.
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, "~")
// An index walk over the folded summaries, stopping at the limit.
// The cursor is a WHERE, not a HAVING, so paging actually skips work
// instead of grouping the whole table and discarding most of it.
rows, err := ix.db.Query(`
SELECT id, created, updated, title, status FROM ticket
WHERE ?1 = '' OR updated < ?1 OR (updated = ?1 AND id > ?2)
ORDER BY updated DESC, id
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})
if perChange[name] {
continue // gathered per change below, never folded
}
// A leading + is fossil's APPEND form: "J +comment more" adds to
// the existing comment rather than replacing it, which is how a
// description grows over a ticket's life. Treating it as a
// distinct field named "+comment" both loses the append and puts
// a nonsense row in the field table.
target, appending := strings.CutPrefix(name, "+")
set := false
for i := range cur.Fields {
if cur.Fields[i].Name != target {
continue
}
if appending {
cur.Fields[i].Value += value
} else {
cur.Fields[i].Value = value
}
set = true
}
if !set {
cur.Fields = append(cur.Fields, TicketFieldSet{Name: target, Value: value})
}
}
// Second pass: the discussion, and the description lifted out of the
// field table so a page can render it as prose rather than a cell.
for i := range out {
t := &out[i]
for _, ch := range t.Changes {
var body, mimetype, login, username string
for _, f := range ch.Fields {
switch f.Name {
case "icomment":
body = f.Value
case "mimetype":
mimetype = f.Value
case "login":
login = f.Value
case "username":
username = f.Value
}
}
if body == "" {
continue // fossil lists only changes that said something
}
if login == "" {
login = ch.User
}
t.Comments = append(t.Comments, TicketComment{
Hash: ch.Hash, Date: ch.Date, Login: login,
Username: username, Mimetype: mimetype, Body: body,
})
}
kept := t.Fields[:0]
for _, f := range t.Fields {
switch f.Name {
case "comment":
t.Description = f.Value
case "cmimetype":
t.DescriptionMimetype = f.Value
default:
kept = append(kept, f)
}
}
t.Fields = kept
}
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) {
// Conditions and their arguments are appended TOGETHER so the two
// lists cannot drift out of step. The previous form built them in
// separate passes and then reassembled the arguments in a third
// place, in an order a comment had to explain.
var conds []string
var all []any
if branch != "" {
conds = append(conds, `e.branch = ?`)
all = append(all, branch)
kind = "ci" // a branch filter implies check-ins only
}
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, `(e.date < ? OR (e.date = ? AND e.hash < ?))`)
all = append(all, bDate, bDate, bHash)
}
if kind != "" && kind != "all" {
conds = append(conds, `e.kind = ?`)
all = append(all, kind)
}
where := ""
if len(conds) > 0 {
where = "WHERE " + strings.Join(conds, " AND ")
}
all = append(all, limit+1)
// One index walk over the denormalized timeline, stopping at the
// limit. The two per-row extras are evaluated for the RETURNED rows
// only, which is the whole difference from the old plan:
//
// isleaf — a check-in is a leaf when nothing lists it as a parent.
// Storing it would mean maintaining a derived boolean that flips
// whenever a child arrives; ~50 indexed lookups per page is
// cheaper than that bookkeeping and cannot go stale.
// ticket label — lives in the folded ticket doc, refreshed after
// the change rows land, so it is joined here rather than written
// by the trigger, which would capture a stale value.
rows, err := ix.db.Query(`
SELECT e.kind, e.hash, e.ref, e.date, e.user,
CASE WHEN e.kind = 't'
THEN 'Ticket [' || substr(e.ref, 1, 10) || '] ' || COALESCE(d.label, '')
ELSE e.comment END,
e.branch,
CASE WHEN e.kind = 'ci'
THEN NOT EXISTS (SELECT 1 FROM checkin_parent lp WHERE lp.parent = e.hash)
ELSE 0 END
FROM event e
LEFT JOIN search_doc d ON e.kind = 't' AND d.hash = 'ticket:' || e.ref
`+where+` ORDER BY e.date DESC, e.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
}
if err := ix.AttachCI(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
}
// LastEvent returns the newest event's date — what a forge listing sorts
// activity on. One index walk stopping at the first row.
func (ix *Index) LastEvent() (string, bool, error) {
var date string
err := ix.db.QueryRow(`SELECT date FROM event ORDER BY date DESC LIMIT 1`).Scan(&date)
if errors.Is(err, sql.ErrNoRows) {
return "", false, nil
}
return date, err == nil, err
}
// 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
}
// Attachment is one live attachment on a ticket, wiki page or technote.
type Attachment struct {
Hash string `json:"hash"`
Name string `json:"name"`
Src string `json:"src"`
Date string `json:"date"`
User string `json:"user"`
}
// AttachmentsFor returns the attachments currently live on target,
// newest first. Fossil's model is a log, not a set: each A card names a
// filename on a target, and a card with NO source removes it. So the
// answer is the latest card per filename, minus the ones whose latest
// card is a deletion — an attachment replaced twice and then deleted
// must not come back.
func (ix *Index) AttachmentsFor(target string) ([]Attachment, error) {
rows, err := ix.db.Query(`
SELECT a.hash, a.name, a.src, a.date, a.user
FROM attachment a
JOIN (SELECT name, max(date) AS d FROM attachment
WHERE target = ? GROUP BY name) latest
ON latest.name = a.name AND latest.d = a.date
WHERE a.target = ? AND a.src <> ''
ORDER BY a.date DESC, a.name`, target, target)
if err != nil {
return nil, err
}
defer rows.Close()
var out []Attachment
for rows.Next() {
var at Attachment
if err := rows.Scan(&at.Hash, &at.Name, &at.Src, &at.Date, &at.User); err != nil {
return nil, err
}
out = append(out, at)
}
return out, rows.Err()
}
// CountAttachments reports how many attachment artifacts are indexed —
// the census figure to compare against fossil's own attachment table.
func (ix *Index) CountAttachments() (int, error) {
var n int
err := ix.db.QueryRow(`SELECT count(*) FROM attachment`).Scan(&n)
return n, err
}
// AttachmentName resolves a content hash to the filename it is attached
// under, and reports whether it is a LIVE attachment. This is the gate
// on serving attachment bytes: without it the download route would
// serve any artifact in the store by hash, including check-in manifests
// and file blobs that were never attached to anything.
//
// A hash whose newest card is a deletion answers false — the file was
// taken down, and taken-down content must not stay reachable by URL.
func (ix *Index) AttachmentName(src string) (string, bool, error) {
var name, target string
err := ix.db.QueryRow(`SELECT name, target FROM attachment
WHERE src = ? ORDER BY date DESC LIMIT 1`, src).Scan(&name, &target)
if err == sql.ErrNoRows {
return "", false, nil
}
if err != nil {
return "", false, err
}
// Live means: the newest card for this (target, name) still carries a
// source. Re-checking by name rather than trusting the row above is
// what makes a later deletion win.
var newest string
if err := ix.db.QueryRow(`SELECT src FROM attachment
WHERE target = ? AND name = ? ORDER BY date DESC LIMIT 1`,
target, name).Scan(&newest); err != nil {
return "", false, err
}
if newest == "" {
return "", false, nil
}
return name, true, nil
}
// Branch is one branch and the check-in at its tip.
//
// zit permits two branches to carry the SAME name (0002 §1: forgejo
// assumes names are unique and quarry cannot), so a selector keyed on
// name alone would silently pick one. Tip disambiguates them.
type Branch struct {
Name string `json:"name"`
Tip string `json:"tip"`
Date string `json:"date"`
}
// Branches lists each branch with its newest check-in, most recently
// active first.
func (ix *Index) Branches() ([]Branch, error) {
// One grouped scan, not a correlated subquery per row. SQLite's
// bare-column rule picks the row that produced max(date), so the
// hash belongs to the tip it reports.
//
// The first form of this cost 434 SECONDS on tcl (42,074 check-ins,
// 1,855 branches): the subquery re-scanned the table once per row.
// It is the reason checkin_by_branch exists.
rows, err := ix.db.Query(`
SELECT branch, hash, max(date) AS d FROM checkin
GROUP BY branch ORDER BY d DESC`)
if err != nil {
return nil, err
}
defer rows.Close()
var out []Branch
for rows.Next() {
var b Branch
if err := rows.Scan(&b.Name, &b.Tip, &b.Date); err != nil {
return nil, err
}
out = append(out, b)
}
return out, rows.Err()
}
// TipOf returns the newest check-in on a branch.
func (ix *Index) TipOf(branch string) (Branch, bool, error) {
var b Branch
err := ix.db.QueryRow(`SELECT branch, hash, date FROM checkin
WHERE branch = ? ORDER BY date DESC LIMIT 1`, branch).Scan(&b.Name, &b.Tip, &b.Date)
if err == sql.ErrNoRows {
return Branch{}, false, nil
}
return b, err == nil, err
}
// CheckinsAfter returns check-ins newer than the named one, oldest
// first, so a consumer processes them in the order they were made.
//
// Ordering is by date, not by DAG reachability: a watermark is "how far
// have I looked", and a check-in that arrives by sync with an older
// date than the watermark is genuinely older work. Walking descendants
// instead would re-run branches that merged later.
//
// An unknown watermark returns only the tip — the safe answer, because
// the alternative is treating the entire history as new.
func (ix *Index) CheckinsAfter(hash string, limit int) ([]TimelineEntry, error) {
if limit <= 0 {
limit = 20
}
var date string
if hash != "" {
if err := ix.db.QueryRow(`SELECT date FROM checkin WHERE hash = ?`, hash).Scan(&date); err != nil {
date = ""
}
}
q := `SELECT hash, date, user, comment, branch FROM checkin ORDER BY date DESC LIMIT 1`
args := []any{}
if date != "" {
q = `SELECT hash, date, user, comment, branch FROM checkin
WHERE date > ? ORDER BY date ASC LIMIT ` + itoa(limit)
args = append(args, date)
}
rows, err := ix.db.Query(q, args...)
if err != nil {
return nil, err
}
defer rows.Close()
var out []TimelineEntry
for rows.Next() {
var e TimelineEntry
if err := rows.Scan(&e.Hash, &e.Date, &e.User, &e.Comment, &e.Branch); err != nil {
return nil, err
}
e.Kind = "ci"
out = append(out, e)
}
return out, rows.Err()
}
func itoa(n int) string {
if n == 0 {
return "0"
}
var b [20]byte
i := len(b)
for n > 0 {
i--
b[i] = byte('0' + n%10)
n /= 10
}
return string(b[i:])
}
// ciVerdicts returns the recorded CI verdict per check-in hash, for the
// hashes given. One query rather than one per row: a timeline page asks
// about fifty check-ins at once.
//
// The newest verdict wins — a re-run records a fresh tag, and the last
// word is the current answer.
func (ix *Index) ciVerdicts(hashes []string) (map[string]string, error) {
if len(hashes) == 0 {
return nil, nil
}
q := `SELECT checkin, name FROM checkin_tag
WHERE name IN ('ci-pass','ci-fail') AND checkin IN (?` +
strings.Repeat(",?", len(hashes)-1) + `) ORDER BY date ASC`
args := make([]any, 0, len(hashes))
for _, h := range hashes {
args = append(args, h)
}
rows, err := ix.db.Query(q, args...)
if err != nil {
return nil, err
}
defer rows.Close()
out := map[string]string{}
for rows.Next() {
var checkin, name string
if err := rows.Scan(&checkin, &name); err != nil {
return nil, err
}
out[checkin] = name // ascending date: the last row seen is newest
}
return out, rows.Err()
}
// AttachCI fills the CI field on timeline entries.
func (ix *Index) AttachCI(entries []TimelineEntry) error {
hashes := make([]string, 0, len(entries))
for _, e := range entries {
if e.Kind == "ci" || e.Kind == "" {
hashes = append(hashes, e.Hash)
}
}
verdicts, err := ix.ciVerdicts(hashes)
if err != nil || len(verdicts) == 0 {
return err
}
for i := range entries {
if v, ok := verdicts[entries[i].Hash]; ok {
entries[i].CI = v
}
}
return nil
}