orchard

  1package main
  2
  3// Searching what was said in earlier conversations.
  4//
  5// A tool result dies with the turn that fetched it and only the answer
  6// survives, so a question about something settled last week had nothing to
  7// reach for. Isaac asked for this twice on 2026-09-08 and it is the other half
  8// of memory: memory keeps one sentence facts about him, and this keeps what was
  9// actually said.
 10//
 11// Scored in Go for the same reason memory is. The candidate rows come out of
 12// SQLite with a LIKE per term so the whole table never lands in memory, and the
 13// ranking is the part that has to be right, which is easier to test as a
 14// function than to argue about as a query.
 15
 16import (
 17	"fmt"
 18	"sort"
 19	"strings"
 20	"time"
 21)
 22
 23// Hit is one earlier exchange that matched.
 24type Hit struct {
 25	ConvID   string    `json:"conversation_id"`
 26	Title    string    `json:"title"`
 27	When     time.Time `json:"when"`
 28	Question string    `json:"question"`
 29	Answer   string    `json:"answer"`
 30}
 31
 32// searchExcerpt is how much of a message goes back to the model. Long enough to
 33// carry the substance of an exchange, short enough that ten of them do not take
 34// the window past the compaction threshold on their own.
 35const searchExcerpt = 700
 36
 37// SearchHistory finds earlier exchanges matching a query. It returns whole
 38// exchanges rather than single messages, since an answer with no question in
 39// front of it reads as an assertion from nowhere.
 40func (s *Store) SearchHistory(query string, limit int) ([]Hit, error) {
 41	want := terms(query)
 42	if len(want) == 0 {
 43		return nil, fmt.Errorf("nothing to search for in %q", query)
 44	}
 45	if limit < 1 || limit > 25 {
 46		limit = 8
 47	}
 48
 49	// One LIKE per term, ORed, which narrows to rows worth scoring without
 50	// pretending to be the ranking. SQLite has no index for this and does not
 51	// need one at the size this database ever reaches.
 52	var where []string
 53	var args []any
 54	for w := range want {
 55		where = append(where, "(m.content LIKE ? OR m.display LIKE ?)")
 56		args = append(args, "%"+w+"%", "%"+w+"%")
 57	}
 58	args = append(args, maxSearchRows)
 59
 60	rows, err := s.db.Query(`
 61		SELECT m.id, m.conv_id, m.content, m.display, m.at, c.title
 62		FROM messages m JOIN conversations c ON c.id = m.conv_id
 63		WHERE `+strings.Join(where, " OR ")+`
 64		ORDER BY m.id DESC LIMIT ?`, args...)
 65	if err != nil {
 66		return nil, err
 67	}
 68	defer rows.Close()
 69
 70	type row struct {
 71		id          int64
 72		conv        string
 73		text, title string
 74		at          time.Time
 75		score       int
 76	}
 77	var found []row
 78	for rows.Next() {
 79		var r row
 80		var content, display string
 81		var at int64
 82		if err := rows.Scan(&r.id, &r.conv, &content, &display, &at, &r.title); err != nil {
 83			return nil, err
 84		}
 85		r.text = display
 86		if r.text == "" {
 87			r.text = content
 88		}
 89		r.at = time.Unix(at, 0)
 90		r.score = overlap(want, terms(r.text))
 91		if r.score > 0 {
 92			found = append(found, r)
 93		}
 94	}
 95	if err := rows.Err(); err != nil {
 96		return nil, err
 97	}
 98
 99	// One hit per conversation. Ten rows off one long thread is the same answer
100	// ten times and crowds out every other conversation that matched.
101	best := map[string]row{}
102	for _, r := range found {
103		if b, seen := best[r.conv]; !seen || r.score > b.score {
104			best[r.conv] = r
105		}
106	}
107	ranked := make([]row, 0, len(best))
108	for _, r := range best {
109		ranked = append(ranked, r)
110	}
111	sort.SliceStable(ranked, func(i, j int) bool {
112		if ranked[i].score != ranked[j].score {
113			return ranked[i].score > ranked[j].score
114		}
115		// A tie goes to the more recent exchange, since the later word on a
116		// subject is usually the one that still holds.
117		return ranked[i].at.After(ranked[j].at)
118	})
119	if len(ranked) > limit {
120		ranked = ranked[:limit]
121	}
122
123	out := make([]Hit, 0, len(ranked))
124	for _, r := range ranked {
125		h := Hit{ConvID: r.conv, Title: r.title, When: r.at}
126		h.Question, h.Answer = s.exchangeAround(r.id)
127		out = append(out, h)
128	}
129	return out, nil
130}
131
132// maxSearchRows caps what the LIKE hands back before anything is scored. A
133// single common word against a long history would otherwise pull the whole
134// table in to rank it.
135const maxSearchRows = 400
136
137// exchangeAround returns the question and the answer either side of a matching
138// message, so a hit carries what was asked as well as what was said.
139func (s *Store) exchangeAround(id int64) (question, answer string) {
140	get := func(q string, args ...any) string {
141		var content, display string
142		if err := s.db.QueryRow(q, args...).Scan(&content, &display); err != nil {
143			return ""
144		}
145		if display != "" {
146			return trim(display, searchExcerpt)
147		}
148		return trim(content, searchExcerpt)
149	}
150	const prior = `SELECT content, display FROM messages
151		WHERE conv_id=(SELECT conv_id FROM messages WHERE id=?) AND role=? AND id<=?
152		ORDER BY id DESC LIMIT 1`
153	const next = `SELECT content, display FROM messages
154		WHERE conv_id=(SELECT conv_id FROM messages WHERE id=?) AND role=? AND id>=?
155		ORDER BY id ASC LIMIT 1`
156
157	// The same pair of queries covers both roles. A matching user message is
158	// its own question and the reply after it is the answer, and a matching
159	// assistant message is its own answer with the question before it.
160	return get(prior, id, string(RoleUser), id), get(next, id, string(RoleAssistant), id)
161}