about summary refs log tree commit diff
path: root/schema.sql
blob: 1d38f7a77542ce18d1cf4abac56efdcffb29f921 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
-- ai-memory schema, version 6.
--
-- commits  : one row per git commit, derived from `git log' by `sync'.
-- symbols  : one declared identifier per row (proc, fn, type, const, var).
-- touches  : which symbols a commit added, modified, removed or enclosed.
-- reasoning: appends the *why*; may exist before the commit does, in which
--            case commit_hash is NULL until `attach' sets it.  `basis' records
--            how the entry was established; `supersedes' points at the entry a
--            correction replaces, so nothing is ever overwritten.

create table if not exists commits (
	hash      text primary key,
	author_ts integer not null,
	subject   text not null,
	body      text
);

create table if not exists symbols (
	id       integer primary key,
	name     text not null unique,   -- qualified: <file base>.<identifier>
	basename text,                   -- the bare identifier, indexed for lookups
	file     text,
	kind     text not null           -- proc | fn | type | const | var
);

create table if not exists touches (
	commit_hash text not null references commits(hash) on delete cascade,
	symbol_id   integer not null references symbols(id),
	change      text not null,   -- add | mod | remove
	primary key (commit_hash, symbol_id, change)
);

create table if not exists reasoning (
	id          integer primary key,
	commit_hash text references commits(hash) on delete cascade,
	session_id  text,
	step        integer,
	created_at  integer not null,
	kind        text not null,   -- thought | conclusion | question
	-- How the entry was established: "read at src/lib.rs:120-140",
	-- "from commit message", "measured on the bulklmm instance", ...
	basis       text,
	-- A correction points at the entry it replaces instead of editing it.
	supersedes  integer references reasoning(id),
	-- One line: the claim itself, written when the context is in hand.  Listings
	-- show this by default; the body is the long form.
	gist        text,
	-- Whether that one-line claim was ASSESSED (written with --gist, or set by
	-- a reader who knew what the entry claims) or DERIVED (the body's first
	-- sentence, a fallback).  Only derived gists may ever be rewritten: an
	-- assessed gist is a judgement and is frozen for the life of the entry.
	gist_source text,
	body        text not null,
	unique (session_id, step, kind)
);

create index if not exists reasoning_superseded_idx on reasoning (supersedes);

create index if not exists touch_symbol_idx on touches (symbol_id);
create index if not exists reasoning_commit_idx on reasoning (commit_hash);
-- Unqualified lookups (`why open_store') are an index seek, not a LIKE scan.
create index if not exists symbol_basename_idx on symbols (basename);
-- `log' walks commits newest-first; the index lets it stop after LIMIT rows.
create index if not exists commit_ts_idx on commits (author_ts);

-- blobs: the C/C++ declaration cache.  One row per git blob that ctags has
-- parsed, keyed by object id, holding that blob's tags as "<ctags kind>\t<name>"
-- lines.  A blob's content cannot change, so a row can never go stale; this is
-- purely a speed cache -- drop the table and the next `sync' re-derives it in a
-- couple of ctags runs per new file version instead of one per commit per file.
create table if not exists blobs (
	blob text primary key,
	tags text not null
);

create virtual table if not exists reasoning_fts using fts5 (
	body, content = 'reasoning', content_rowid = 'id'
);

create trigger if not exists reasoning_after_insert after insert on reasoning begin
	insert into reasoning_fts (rowid, body) values (new.id, new.body);
end;

create trigger if not exists reasoning_after_delete after delete on reasoning begin
	insert into reasoning_fts (reasoning_fts, rowid, body) values ('delete', old.id, old.body);
end;

create trigger if not exists reasoning_after_update after update on reasoning begin
	insert into reasoning_fts (reasoning_fts, rowid, body) values ('delete', old.id, old.body);
	insert into reasoning_fts (rowid, body) values (new.id, new.body);
end;

pragma user_version = 6;