about summary refs log tree commit diff
path: root/schema.sql
diff options
context:
space:
mode:
authorPjotr Prins <pjotr.public01@thebird.nl>2026-11-11 22:05:35 +0000
committerPjotr Prins <pjotr.public01@thebird.nl>2026-11-11 22:05:35 +0000
commitaa6fe5369c7733e8bc881623ab6734cfef9cb66c (patch)
tree8d6d929282388b5b003f7f932765089e370de78a /schema.sql
downloadai-memory-aa6fe5369c7733e8bc881623ab6734cfef9cb66c.tar.gz
initial ai-memory: derive commits and symbols from git, author the why
commits, symbols and touches are derived from `git log`/`git show` by
`sync`; only reasoning is authored, and it is append-only.  Each entry
records a basis (how it was established) and may supersede an earlier
entry instead of rewriting it.

Symbols come from two signals: declarations added or removed on diff
lines (Odin and Rust), and the enclosing item named in git's own hunk
headers, which needs no parser and works for any language with a
userdiff driver.

Links SQLite through the odin-sqlite3 bindings; guix.scm builds both the
compiler and the tool.  doc/SKILL.md is the operating guide.
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;