about summary refs log tree commit diff
path: root/schema.sql
diff options
context:
space:
mode:
Diffstat (limited to 'schema.sql')
-rw-r--r--schema.sql75
1 files changed, 75 insertions, 0 deletions
diff --git a/schema.sql b/schema.sql
new file mode 100644
index 0000000..ecdbe1d
--- /dev/null
+++ b/schema.sql
@@ -0,0 +1,75 @@
+-- ai-memory schema, version 3.
+--
+-- 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),
+	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);
+
+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 = 3;