Skip to content

Solution for long term memory for agent coding CLIs and to facilitate handoff between different agent vendors

Rust
5.1 KB
Raw Blame
-- Initial ai-memory schema.
-- Identity is the 3-tuple (workspace_id, project_id, path) on every domain
-- table, baked in from M1 so we never inherit basic-memory's v0.20 retrofit
-- pain (issues #783, #834 and friends).

CREATE TABLE workspaces (
    id            BLOB PRIMARY KEY NOT NULL,
    name          TEXT NOT NULL UNIQUE,
    created_at    INTEGER NOT NULL                       -- microseconds since epoch
);

CREATE TABLE projects (
    id            BLOB PRIMARY KEY NOT NULL,
    workspace_id  BLOB NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
    name          TEXT NOT NULL,
    repo_path     TEXT,                                  -- absolute filesystem path, for cwd matching
    created_at    INTEGER NOT NULL,
    UNIQUE (workspace_id, name)
);

CREATE INDEX idx_projects_workspace ON projects(workspace_id);

CREATE TABLE pages (
    id                  BLOB PRIMARY KEY NOT NULL,
    workspace_id        BLOB NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
    project_id          BLOB NOT NULL REFERENCES projects(id)   ON DELETE CASCADE,
    path                TEXT NOT NULL,                  -- POSIX path relative to wiki/
    title               TEXT NOT NULL,
    tier                TEXT NOT NULL CHECK (tier IN ('working','episodic','semantic','procedural')),
    body                TEXT NOT NULL,
    body_sha256         BLOB NOT NULL,                  -- 32 bytes
    frontmatter_json    TEXT NOT NULL DEFAULT '{}',
    is_latest           INTEGER NOT NULL DEFAULT 1 CHECK (is_latest IN (0,1)),
    supersedes          BLOB REFERENCES pages(id) ON DELETE SET NULL,
    pinned              INTEGER NOT NULL DEFAULT 0 CHECK (pinned IN (0,1)),
    created_at          INTEGER NOT NULL,
    updated_at          INTEGER NOT NULL,
    -- Embedding columns are NULL through v1 (M9 fills them in).
    embedding_provider  TEXT,
    embedding_model     TEXT,
    embedding_dim       INTEGER
);

-- One latest version per (workspace, project, path). Older versions stay in
-- the table with is_latest=0, linked via supersedes.
CREATE UNIQUE INDEX idx_pages_latest_path
    ON pages(workspace_id, project_id, path)
    WHERE is_latest = 1;

CREATE INDEX idx_pages_updated ON pages(updated_at DESC);
CREATE INDEX idx_pages_supersedes ON pages(supersedes) WHERE supersedes IS NOT NULL;

-- FTS5 over title + body. `content='pages'` keeps the FTS table contentless
-- (the source row owns the text); the triggers below propagate changes.
CREATE VIRTUAL TABLE pages_fts USING fts5(
    title, body,
    content='pages',
    content_rowid='rowid',
    tokenize="unicode61 tokenchars '/_-'"
);

CREATE TRIGGER pages_fts_ai AFTER INSERT ON pages BEGIN
    INSERT INTO pages_fts(rowid, title, body)
        VALUES (new.rowid, new.title, new.body);
END;

CREATE TRIGGER pages_fts_ad AFTER DELETE ON pages BEGIN
    INSERT INTO pages_fts(pages_fts, rowid, title, body)
        VALUES ('delete', old.rowid, old.title, old.body);
END;

CREATE TRIGGER pages_fts_au AFTER UPDATE ON pages BEGIN
    INSERT INTO pages_fts(pages_fts, rowid, title, body)
        VALUES ('delete', old.rowid, old.title, old.body);
    INSERT INTO pages_fts(rowid, title, body)
        VALUES (new.rowid, new.title, new.body);
END;

CREATE TABLE sessions (
    id               BLOB PRIMARY KEY NOT NULL,
    workspace_id     BLOB NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
    project_id       BLOB NOT NULL REFERENCES projects(id)   ON DELETE CASCADE,
    agent_kind       TEXT NOT NULL CHECK (agent_kind IN ('claude-code','codex','open-code','other')),
    cwd              TEXT,
    started_at       INTEGER NOT NULL,
    ended_at         INTEGER,
    summary_page_id  BLOB REFERENCES pages(id) ON DELETE SET NULL
);

CREATE INDEX idx_sessions_recent ON sessions(workspace_id, project_id, started_at DESC);

CREATE TABLE observations (
    id            BLOB PRIMARY KEY NOT NULL,
    session_id    BLOB NOT NULL REFERENCES sessions(id)   ON DELETE CASCADE,
    workspace_id  BLOB NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
    project_id    BLOB NOT NULL REFERENCES projects(id)   ON DELETE CASCADE,
    kind          TEXT NOT NULL,
    title         TEXT NOT NULL,
    body          TEXT NOT NULL,
    importance    INTEGER NOT NULL DEFAULT 5 CHECK (importance BETWEEN 1 AND 10),
    created_at    INTEGER NOT NULL
);

CREATE INDEX idx_observations_session ON observations(session_id, created_at);

CREATE TABLE links (
    from_page_id  BLOB NOT NULL REFERENCES pages(id) ON DELETE CASCADE,
    to_page_id    BLOB REFERENCES pages(id) ON DELETE SET NULL,
    to_path       TEXT NOT NULL,                       -- raw target, may be unresolved
    link_type     TEXT NOT NULL DEFAULT 'references',
    PRIMARY KEY (from_page_id, to_path, link_type)
);

CREATE INDEX idx_links_to ON links(to_page_id) WHERE to_page_id IS NOT NULL;

CREATE TABLE audit_log (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    at            INTEGER NOT NULL,
    op            TEXT NOT NULL,
    workspace_id  BLOB,
    project_id    BLOB,
    page_id       BLOB,
    detail        TEXT NOT NULL DEFAULT '{}'
);

CREATE INDEX idx_audit_recent ON audit_log(at DESC);