orchard

  1package main
  2
  3import (
  4	"crypto/sha256"
  5	"database/sql"
  6	"encoding/hex"
  7	"encoding/json"
  8	"fmt"
  9	"html/template"
 10	"os"
 11	"path/filepath"
 12	"sort"
 13	"strings"
 14	"time"
 15
 16	_ "modernc.org/sqlite"
 17)
 18
 19// History is the questions and the answers, which is the one thing the cache
 20// deliberately does not hold.
 21//
 22// It is a second database rather than two more tables, for two reasons that are
 23// both Isaac's requirements rather than tidiness. Deleting every question has
 24// to be possible without throwing away the page archive, which is a personal
 25// library of public articles and the expensive thing to rebuild. And a separate
 26// file is one line to exclude from restic, which matters because a row deleted
 27// here is still in whatever snapshot already went to B2. Deletion is forward
 28// only, so the honest way to keep something out of a backup is to have never
 29// put it in one.
 30//
 31// secure_delete is on so a deleted question is zeroed rather than left in the
 32// free pages of the file, which is the difference between deleting a row and
 33// deleting the text.
 34type History struct {
 35	db *sql.DB
 36}
 37
 38const historySchema = `
 39CREATE TABLE IF NOT EXISTS answers (
 40  id          INTEGER PRIMARY KEY,
 41  asked_at    INTEGER NOT NULL,
 42  question    TEXT NOT NULL,
 43  standalone  TEXT NOT NULL DEFAULT '',
 44  shape       TEXT NOT NULL DEFAULT '',
 45  skill       TEXT NOT NULL DEFAULT '',
 46  answer      TEXT NOT NULL,
 47  queries     TEXT NOT NULL DEFAULT '[]',
 48  sources     TEXT NOT NULL DEFAULT '[]',
 49  warnings    TEXT NOT NULL DEFAULT '[]',
 50  support     REAL NOT NULL DEFAULT 0,
 51  checked     INTEGER NOT NULL DEFAULT 0,
 52  retried     INTEGER NOT NULL DEFAULT 0,
 53  elapsed_ms  INTEGER NOT NULL DEFAULT 0,
 54
 55  -- What produced it. Without these a month of thumbs cannot be read, since
 56  -- there is no telling which of them are about a prompt that has since been
 57  -- rewritten or a quant that has since been swapped.
 58  model       TEXT NOT NULL DEFAULT '',
 59  prompts     TEXT NOT NULL DEFAULT '',
 60  sampling    TEXT NOT NULL DEFAULT '',
 61  build       TEXT NOT NULL DEFAULT ''
 62);
 63CREATE INDEX IF NOT EXISTS answers_asked ON answers(asked_at DESC);
 64
 65-- One verdict per answer, so changing your mind replaces it rather than
 66-- stacking. What is wanted here is the current opinion, not its history.
 67CREATE TABLE IF NOT EXISTS feedback (
 68  answer_id INTEGER PRIMARY KEY REFERENCES answers(id) ON DELETE CASCADE,
 69  rated_at  INTEGER NOT NULL,
 70  verdict   INTEGER NOT NULL,
 71  reason    TEXT NOT NULL DEFAULT '',
 72  note      TEXT NOT NULL DEFAULT ''
 73);
 74
 75-- Domain reputation survives a history wipe on purpose. It holds no question
 76-- text and nothing that could be read back as one, and deleting a month of
 77-- questions for privacy should not also delete what the system learned from
 78-- answering them.
 79CREATE TABLE IF NOT EXISTS domains (
 80  site TEXT PRIMARY KEY,
 81  good INTEGER NOT NULL DEFAULT 0,
 82  bad  INTEGER NOT NULL DEFAULT 0
 83);
 84`
 85
 86func OpenHistory(dataDir string) (*History, error) {
 87	if err := os.MkdirAll(dataDir, 0o755); err != nil {
 88		return nil, err
 89	}
 90	db, err := sql.Open("sqlite", filepath.Join(dataDir, "history.db")+
 91		"?_pragma=journal_mode(WAL)&_pragma=busy_timeout(5000)&_pragma=foreign_keys(1)&_pragma=secure_delete(1)")
 92	if err != nil {
 93		return nil, err
 94	}
 95	if _, err := db.Exec(historySchema); err != nil {
 96		return nil, err
 97	}
 98	return &History{db: db}, nil
 99}
100
101func (h *History) Close() error { return h.db.Close() }
102
103// Entry is one logged answer, with its verdict when it has one.
104type Entry struct {
105	ID       int64
106	Asked    time.Time
107	Question string
108	Shape    string
109	Skill    string
110	Answer   string
111	Queries  []string
112	Sources  []Source
113	Warnings []string
114	Support  float64
115	Checked  int
116	Retried  bool
117	Elapsed  string
118	Model    string
119	Prompts  string
120	Sampling string
121	Verdict  int
122	Reason   string
123	Note     string
124}
125
126// Body renders the stored markdown for the history page. The HTML is not
127// stored beside it, since it is derived and storing both means keeping them in
128// step forever.
129func (e Entry) Body() template.HTML { return template.HTML(renderMarkdown(e.Answer)) }
130
131// Rated is whether a verdict has been given, since zero means unrated.
132func (e Entry) Rated() bool { return e.Verdict != 0 }
133
134// Good and Bad read better in a template than comparing to a number.
135func (e Entry) Good() bool { return e.Verdict > 0 }
136func (e Entry) Bad() bool  { return e.Verdict < 0 }
137
138// Log writes an answer. Incognito questions never reach here at all, which is
139// why there is no flag for it on the row: an unlogged question leaves nothing
140// to mark, and a row that said "this one was private" would itself be a record
141// that a private question was asked.
142func (h *History) Log(a *Answer, stamp Stamp) (int64, error) {
143	res, err := h.db.Exec(`
144		INSERT INTO answers
145		  (asked_at, question, standalone, shape, skill, answer, queries, sources,
146		   warnings, support, checked, retried, elapsed_ms, model, prompts, sampling, build)
147		VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)`,
148		time.Now().Unix(), a.Query, a.Standalone, string(a.Shape), a.Skill, a.Text,
149		asJSON(a.Queries), asJSON(a.Sources), asJSON(a.Warnings),
150		a.Support, countChecked(a.Citations), a.Retried, millis(a.Elapsed),
151		stamp.Model, stamp.Prompts, stamp.Sampling, stamp.Build)
152	if err != nil {
153		return 0, err
154	}
155	return res.LastInsertId()
156}
157
158// Rate records a verdict and moves the domains behind that answer with it. The
159// reputation is deliberately a count of answers rather than of sources, so a
160// page cited once in a good answer counts once.
161func (h *History) Rate(id int64, verdict int, reason, note string) error {
162	if verdict > 0 {
163		verdict = 1
164	} else if verdict < 0 {
165		verdict = -1
166	}
167	var raw string
168	if err := h.db.QueryRow(`SELECT sources FROM answers WHERE id = ?`, id).Scan(&raw); err != nil {
169		return err
170	}
171	var prev int
172	h.db.QueryRow(`SELECT verdict FROM feedback WHERE answer_id = ?`, id).Scan(&prev)
173
174	tx, err := h.db.Begin()
175	if err != nil {
176		return err
177	}
178	defer tx.Rollback()
179
180	if verdict == 0 {
181		if _, err := tx.Exec(`DELETE FROM feedback WHERE answer_id = ?`, id); err != nil {
182			return err
183		}
184	} else if _, err := tx.Exec(`
185		INSERT INTO feedback (answer_id, rated_at, verdict, reason, note)
186		VALUES (?,?,?,?,?)
187		ON CONFLICT(answer_id) DO UPDATE SET
188		  rated_at = excluded.rated_at, verdict = excluded.verdict,
189		  reason = excluded.reason, note = excluded.note`,
190		id, time.Now().Unix(), verdict, reason, note); err != nil {
191		return err
192	}
193
194	// Undo whatever the previous verdict did before applying the new one, so
195	// changing your mind does not leave both counted.
196	for _, site := range sitesOf(raw) {
197		if _, err := tx.Exec(`INSERT INTO domains (site) VALUES (?) ON CONFLICT(site) DO NOTHING`, site); err != nil {
198			return err
199		}
200		if err := bump(tx, site, prev, -1); err != nil {
201			return err
202		}
203		if err := bump(tx, site, verdict, 1); err != nil {
204			return err
205		}
206	}
207	return tx.Commit()
208}
209
210func bump(tx *sql.Tx, site string, verdict, by int) error {
211	switch {
212	case verdict > 0:
213		_, err := tx.Exec(`UPDATE domains SET good = MAX(0, good + ?) WHERE site = ?`, by, site)
214		return err
215	case verdict < 0:
216		_, err := tx.Exec(`UPDATE domains SET bad = MAX(0, bad + ?) WHERE site = ?`, by, site)
217		return err
218	}
219	return nil
220}
221
222// List returns the newest first. only may be "down", "up" or "" for everything,
223// since the reason to open this page is usually to find what went wrong.
224func (h *History) List(limit, offset int, only string) ([]Entry, error) {
225	where := ""
226	switch only {
227	case "down":
228		where = "WHERE f.verdict < 0"
229	case "up":
230		where = "WHERE f.verdict > 0"
231	case "unrated":
232		where = "WHERE f.verdict IS NULL"
233	}
234	rows, err := h.db.Query(`
235		SELECT a.id, a.asked_at, a.question, a.shape, a.skill, a.answer,
236		       a.queries, a.sources, a.warnings, a.support, a.checked, a.retried,
237		       a.elapsed_ms, a.model, a.prompts, a.sampling,
238		       COALESCE(f.verdict, 0), COALESCE(f.reason, ''), COALESCE(f.note, '')
239		FROM answers a LEFT JOIN feedback f ON f.answer_id = a.id
240		`+where+`
241		ORDER BY a.asked_at DESC LIMIT ? OFFSET ?`, limit, offset)
242	if err != nil {
243		return nil, err
244	}
245	defer rows.Close()
246
247	var out []Entry
248	for rows.Next() {
249		var (
250			e                       Entry
251			asked, ms               int64
252			queries, sources, warns string
253		)
254		if err := rows.Scan(&e.ID, &asked, &e.Question, &e.Shape, &e.Skill, &e.Answer,
255			&queries, &sources, &warns, &e.Support, &e.Checked, &e.Retried,
256			&ms, &e.Model, &e.Prompts, &e.Sampling,
257			&e.Verdict, &e.Reason, &e.Note); err != nil {
258			return nil, err
259		}
260		e.Asked = time.Unix(asked, 0)
261		e.Elapsed = (time.Duration(ms) * time.Millisecond).Round(100 * time.Millisecond).String()
262		json.Unmarshal([]byte(queries), &e.Queries)
263		json.Unmarshal([]byte(sources), &e.Sources)
264		json.Unmarshal([]byte(warns), &e.Warnings)
265		out = append(out, e)
266	}
267	return out, rows.Err()
268}
269
270func (h *History) Count() (total, rated int) {
271	h.db.QueryRow(`SELECT COUNT(*) FROM answers`).Scan(&total)
272	h.db.QueryRow(`SELECT COUNT(*) FROM feedback`).Scan(&rated)
273	return total, rated
274}
275
276func (h *History) Delete(id int64) error {
277	if _, err := h.db.Exec(`DELETE FROM answers WHERE id = ?`, id); err != nil {
278		return err
279	}
280	return h.vacuum()
281}
282
283// DeleteAll leaves the domain counts standing. They are what the site learned
284// rather than what was asked, and rebuilding them would need the questions
285// that are being deleted.
286func (h *History) DeleteAll() error {
287	if _, err := h.db.Exec(`DELETE FROM answers`); err != nil {
288		return err
289	}
290	return h.vacuum()
291}
292
293// vacuum is what actually removes the text rather than the row, and it takes
294// all three steps.
295//
296// secure_delete zeroes a deleted row where it lay and VACUUM rewrites the file
297// without it, but in WAL mode both of those write through the log, so the
298// question is still sitting in history.db-wal afterwards. Deleting a row and
299// then finding it in a text search of the directory is the whole failure this
300// page exists to prevent, so the checkpoint truncates the log as well. Tested
301// by reading the files, since that is the only way to know.
302func (h *History) vacuum() error {
303	if _, err := h.db.Exec(`VACUUM`); err != nil {
304		return err
305	}
306	_, err := h.db.Exec(`PRAGMA wal_checkpoint(TRUNCATE)`)
307	return err
308}
309
310// Reputation is the score per domain, for ranking. Laplace smoothed, so one
311// bad answer does not bury a site and a domain nobody has judged sits at the
312// neutral 0.5 rather than at zero.
313func (h *History) Reputation() map[string]float64 {
314	rows, err := h.db.Query(`SELECT site, good, bad FROM domains`)
315	if err != nil {
316		return nil
317	}
318	defer rows.Close()
319	out := map[string]float64{}
320	for rows.Next() {
321		var site string
322		var good, bad int
323		if err := rows.Scan(&site, &good, &bad); err != nil {
324			return out
325		}
326		out[site] = float64(good+1) / float64(good+bad+2)
327	}
328	return out
329}
330
331// Stamp is what produced an answer, recorded beside it. A thumb is only
332// readable next month if it says which model and which prompts it was about.
333type Stamp struct {
334	Model    string
335	Prompts  string
336	Sampling string
337	Build    string
338}
339
340// promptVersion hashes every instruction the model is given, so an edit to any
341// contract shows up as a different version without anyone remembering to bump
342// one. Sampling is separate, since changing a temperature is not changing a
343// prompt and the two are worth telling apart when reading the thumbs back.
344func promptVersion() string {
345	var parts []string
346	for _, s := range shapeEnum {
347		parts = append(parts, string(s), contractFor(s).Instruction, contractFor(s).Reminder)
348	}
349	parts = append(parts, houseStyle, answerFirst, datesArePast, datesAreFuture)
350	sort.Strings(parts)
351	sum := sha256.Sum256([]byte(strings.Join(parts, "\x00")))
352	return hex.EncodeToString(sum[:])[:12]
353}
354
355func samplingVersion() string {
356	return fmt.Sprintf("exact %.2f/%.2f/%d prose %.2f/%.2f/%d/%.2f/%.2f",
357		exact.Temperature, exact.TopP, exact.TopK,
358		prose.Temperature, prose.TopP, prose.TopK, prose.MinP, prose.PresencePenalty)
359}
360
361func asJSON(v any) string {
362	b, err := json.Marshal(v)
363	if err != nil {
364		return "[]"
365	}
366	return string(b)
367}
368
369// sitesOf pulls the domains out of a stored sources blob.
370func sitesOf(raw string) []string {
371	var srcs []Source
372	if json.Unmarshal([]byte(raw), &srcs) != nil {
373		return nil
374	}
375	seen := map[string]bool{}
376	var out []string
377	for _, s := range srcs {
378		host := hostname(s.URL)
379		if host == "" || seen[host] {
380			continue
381		}
382		seen[host] = true
383		out = append(out, host)
384	}
385	return out
386}
387
388func millis(elapsed string) int64 {
389	d, err := time.ParseDuration(elapsed)
390	if err != nil {
391		return 0
392	}
393	return d.Milliseconds()
394}