diff options
Diffstat (limited to 'schema.sql')
| -rw-r--r-- | schema.sql | 75 |
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; |
