Building a filesystem-based agent memory on SurrealDB

surreal start --user root --pass root memory

surreal start --user root --pass root rocksdb://agent.db

DEFINE TABLE OVERWRITE file SCHEMAFULL;

DEFINE FIELD OVERWRITE name   ON file TYPE string;
DEFINE FIELD OVERWRITE parent ON file TYPE option<record<file>> DEFAULT NONE;

-- A composite UNIQUE index on (parent, name) would let every root-level file
-- through unchecked: SurrealDB skips a unique index when one of its fields is
-- NONE. `parent_key` mirrors `parent` with the root spelled 'root' so the
-- index has something to compare. It cannot be COMPUTED -- SurrealDB refuses
-- to index a computed field -- so it is a stored VALUE, re-evaluated on write.
DEFINE FIELD OVERWRITE parent_key ON file TYPE string
    VALUE IF $this.parent IS NONE { 'root' } ELSE { <string>$this.parent };

-- <string> guards against $this.name being NONE mid-delete.
DEFINE FIELD OVERWRITE path ON file COMPUTED (
    ($this.parent.path || '') + '/' + <string>$this.name
) TYPE string;

-- A folder is a row with no content and no embedding -- nothing else marks it.
DEFINE FIELD OVERWRITE is_folder ON file COMPUTED (
    $this.content IS NONE
);

DEFINE FIELD OVERWRITE content    ON file TYPE option<string> DEFAULT NONE;
DEFINE FIELD OVERWRITE embedding ON file TYPE option<array<float>> DEFAULT NONE;
DEFINE FIELD OVERWRITE updated_at ON file TYPE datetime VALUE time::now();

-- Serves both the uniqueness constraint and "everything under this parent" --
-- the hottest lookup here (ls, path resolution). The reverse field order
-- would force a table scan for that query.
DEFINE INDEX OVERWRITE child_unique ON TABLE file FIELDS parent_key, name UNIQUE;

-- Full-text search over file contents. HIGHLIGHTS is what makes
-- search::highlight/search::offsets return real snippets instead of the
-- untouched content -- it stores term positions, so it's opt-in.
DEFINE ANALYZER OVERWRITE file_analyzer
    TOKENIZERS blank, class, camel, punct
    FILTERS snowball(english);

DEFINE INDEX OVERWRITE file_content_fts ON file
    FIELDS content
    FULLTEXT ANALYZER file_analyzer BM25(1.2, 0.75) HIGHLIGHTS;

-- Semantic search over the same field. Real embedders return 1536+ dimensions
-- (e.g. OpenAI's text-embedding-3-small); this tutorial uses toy 4-dimensional
-- vectors so the numbers stay readable -- the index and the query are identical
-- either way, just change DIMENSION to match your embedder.
DEFINE INDEX OVERWRITE file_embedding_hnsw ON file
    FIELDS embedding
    HNSW DIMENSION 4 DIST COSINE TYPE F32;

surreal sql --endpoint http://localhost:8000 \
  --user root --pass root --ns agent --db memory \
  --multi < schema.surql

LET $notes = (CREATE ONLY file SET name = 'notes.md', content =
    "# Working notes\n- Building an internal tool to track deploys.\n" +
    "- User prefers terse commit messages, no emoji.");

LET $preferences = (CREATE ONLY file SET name = 'preferences');
LET $editor = (CREATE ONLY file SET name = 'editor.md', parent = $preferences.id, content =
    "# Editor preferences\nUses Neovim with a 2-space indent for everything. " +
    "Reviews diffs before every commit, never squashes without asking.");

LET $projects = (CREATE ONLY file SET name = 'projects');
LET $launch = (CREATE ONLY file SET name = 'launch', parent = $projects.id);
LET $todo = (CREATE ONLY file SET name = 'todo.md', parent = $launch.id, content =
    "# Launch checklist\n- [x] Write the changelog\n- [ ] Cut the release branch\n" +
    "- [ ] Announce in #eng-updates");

-- Toy embeddings standing in for a real embedder -- see the note in schema.surql.
UPDATE $editor.id SET embedding = [0.9, 0.1, 0.0, 0.0];
UPDATE $todo.id   SET embedding = [0.0, 0.1, 0.9, 0.2];
UPDATE $notes.id SET embedding = [0.6, 0.3, 0.3, 0.1];

SELECT path, is_folder FROM file ORDER BY path;

[
  { path: '/notes.md', is_folder: false },
  { path: '/preferences', is_folder: true },
  { path: '/preferences/editor.md', is_folder: false },
  { path: '/projects', is_folder: true },
  { path: '/projects/launch', is_folder: true },
  { path: '/projects/launch/todo.md', is_folder: false }
]

/
├── notes.md
├── preferences/
│   └── editor.md
└── projects/
    └── launch/
        └── todo.md

SELECT path, is_folder FROM file WHERE parent_key = 'root' ORDER BY path;

[
  { path: '/notes.md', is_folder: false },
  { path: '/preferences', is_folder: true },
  { path: '/projects', is_folder: true }
]

SELECT content FROM file WHERE path = '/notes.md';

[{ content: '# Working notes\n- Building an internal tool to track deploys.\n- User prefers terse commit messages, no emoji.' }]

SELECT path, search::score(1) AS score FROM file
    WHERE content @1@ 'commit' ORDER BY score DESC;

[
  { path: '/notes.md', score: 0.4417339265346527 },
  { path: '/preferences/editor.md', score: 0.4106069505214691 }
]

SELECT path, search::score(1) AS score, search::highlight('**', '**', 1) AS snippet
    FROM file WHERE content @1@ 'commit' ORDER BY score DESC;

[
  { path: '/notes.md', score: 0.4417339265346527,
    snippet: '# Working notes\n- Building an internal tool to track deploys.\n- User prefers terse **commit** messages, no emoji.' },
  { path: '/preferences/editor.md', score: 0.4106069505214691,
    snippet: '# Editor preferences\nUses Neovim with a 2-space indent for everything. Reviews diffs before every **commit**, never squashes without asking.' }
]

LET $launch = (SELECT VALUE id FROM ONLY file WHERE path = '/projects/launch');
UPDATE $launch SET name = 'v2-launch';
SELECT path FROM file WHERE parent_key = <string>$launch OR id = $launch;

[
  { path: '/projects/v2-launch' },
  { path: '/projects/v2-launch/todo.md' }
]

LET $todo = (SELECT VALUE id FROM ONLY file WHERE path = '/projects/v2-launch/todo.md');
UPDATE $todo SET content = string::replace(
    content, '[ ] Cut the release branch', '[x] Cut the release branch'
);

SELECT path, vector::distance::knn() AS distance FROM file
    WHERE embedding <|3, 40|> [0.1, 0.1, 0.8, 0.3]
    ORDER BY distance ASC;

[
  { path: '/projects/v2-launch/todo.md', distance: 0.0163 },
  { path: '/notes.md', distance: 0.4395 },
  { path: '/preferences/editor.md', distance: 0.8725 }
]

async def search(db: AsyncSurreal, query: str) -> str:
    """Full-text search over every file's content, best match first."""
    rows = await _q(
        db,
        "SELECT path, search::score(1) AS score, "
        "search::highlight('**', '**', 1) AS snippet FROM file "
        "WHERE content @1@ $query ORDER BY score DESC",
        {"query": query},
    )
    if not rows:
        return "(no matches)"
    return "\n".join(
        f"{r['path']}: {next((l for l in r['snippet'].splitlines() if '**' in l), r['snippet'])}"
        for r in rows
    )

>>> write_file('/preferences/editor.md', ...)
Wrote 50 bytes to /preferences/editor.md

>>> ls('/')
    87  notes.md
     -  preferences/

>>> cat('/preferences/editor.md')
Uses Neovim. Reviews every diff before committing.

>>> edit('/preferences/editor.md', 'Uses Neovim', 'Uses Neovim with a 2-space indent')
--- a/preferences/editor.md
+++ b/preferences/editor.md
@@ -1 +1 @@
-Uses Neovim. Reviews every diff before committing.
+Uses Neovim with a 2-space indent. Reviews every diff before committing.

>>> search('changelog')
/notes.md: # Working notes

You have a persistent filesystem; treat it as your memory. Record what you
learn about the user's preferences under /preferences/. Give each project a
folder under /projects/<name>/. Search before you create — the file you want
may already exist.

DEFINE FIELD OVERWRITE owner ON TABLE file TYPE option<record<user>> DEFAULT $auth.id;

DEFINE TABLE OVERWRITE file SCHEMAFULL
    PERMISSIONS FOR select, create, update, delete WHERE owner = $auth.id;

"""The smallest version of agent memory: bash-shaped tools over one SurrealDB table.

An LLM has seen millions of `ls`, `cat`, `mkdir -p` and `grep -r` sessions, so a
tool named `ls` that returns text needs no explanation in a system prompt --
the shape is already in the weights. This module is that idea with nothing
else in it: five functions, each one SurrealQL statement against the `file`
table from schema.surql, each returning plain text a model can read.

Wiring these into an actual tool-calling loop is small -- hand a dict of
{name: function} to whatever provider SDK you use, one branch per tool call.
`surrealfs` (github.com/surrealdb/surrealfs) is the version of this with
error recovery, glob, binary files, and three ready-made integrations
(pydantic-ai, raw Anthropic/OpenAI schemas, a plain async API); its
`examples/anthropic_loop.py` is ~40 lines end to end.

    surreal start --user root --pass root memory   # in one terminal
    uv run --with surrealdb python fs_tools.py      # runs the self-check below
"""

from __future__ import annotations

import asyncio
import difflib
from pathlib import Path, PurePosixPath
from typing import Any

from surrealdb import AsyncSurreal

SCHEMA = (Path(__file__).parent / "schema.surql").read_text()


async def _q(db: AsyncSurreal, sql: str, vars: dict[str, Any] | None = None) -> Any:
    """Run one or more statements, return the last one's result."""
    return await db.query(sql, vars or {})


async def _resolve(db: AsyncSurreal, path: str) -> dict[str, Any] | None:
    if path == "/":
        return None
    row = await _q(db, "SELECT * FROM ONLY file WHERE path = $path LIMIT 1", {"path": path})
    return row


async def ls(db: AsyncSurreal, path: str = "/") -> str:
    """List a folder: one line per entry, `-` size for folders."""
    if path != "/":
        folder = await _resolve(db, path)
        if folder is None or not folder["is_folder"]:
            return f"Error: not a directory: {path}"
        key = str(folder["id"])
    else:
        key = "root"
    rows = await _q(
        db,
        "SELECT name, is_folder, "
        "IF content IS NOT NONE THEN string::len(content) ELSE 0 END AS size "
        "FROM file WHERE parent_key = $key ORDER BY name",
        {"key": key},
    )
    if not rows:
        return "(empty)"
    return "\n".join(
        f"{'-' if r['is_folder'] else r['size']:>6}  {r['name']}{'/' if r['is_folder'] else ''}"
        for r in rows
    )


async def cat(db: AsyncSurreal, path: str) -> str:
    """Read a file's full content."""
    entry = await _resolve(db, path)
    if entry is None:
        return f"Error: no such file: {path}"
    if entry["is_folder"]:
        return f"Error: is a directory: {path}"
    return entry["content"]


async def write_file(db: AsyncSurreal, path: str, content: str) -> str:
    """Create or replace a file, creating missing parent folders."""
    parts = PurePosixPath(path).parts[1:]  # drop the leading "/"
    if not parts:
        return "Error: cannot write to the root directory"

    parent_id = None
    for segment in parts[:-1]:
        existing = await _q(
            db,
            "SELECT * FROM ONLY file WHERE parent_key = $key AND name = $name LIMIT 1",
            {"key": "root" if parent_id is None else str(parent_id), "name": segment},
        )
        if existing is None:
            existing = await _q(
                db,
                "CREATE ONLY file SET name = $name, parent = $parent",
                {"name": segment, "parent": parent_id},
            )
        elif not existing["is_folder"]:
            return f"Error: not a directory: {existing['path']}"
        parent_id = existing["id"]

    name = parts[-1]
    existing = await _resolve(db, path)
    if existing is not None:
        if existing["is_folder"]:
            return f"Error: is a directory: {path}"
        await _q(db, "UPDATE $id SET content = $content", {"id": existing["id"], "content": content})
        return f"Wrote {len(content)} bytes to {path}"
    await _q(
        db,
        "CREATE file SET name = $name, parent = $parent, content = $content",
        {"name": name, "parent": parent_id, "content": content},
    )
    return f"Wrote {len(content)} bytes to {path}"


async def edit(db: AsyncSurreal, path: str, old: str, new: str) -> str:
    """Replace the first occurrence of `old` with `new`, returning a diff."""
    entry = await _resolve(db, path)
    if entry is None:
        return f"Error: no such file: {path}"
    current = entry["content"] or ""
    if old not in current:
        return f"Error: text not found in {path}: {old!r}"
    updated = current.replace(old, new, 1)
    await _q(db, "UPDATE $id SET content = $content", {"id": entry["id"], "content": updated})
    diff = difflib.unified_diff(
        current.splitlines(), updated.splitlines(), fromfile=f"a{path}", tofile=f"b{path}", lineterm=""
    )
    return "\n".join(diff)


async def search(db: AsyncSurreal, query: str) -> str:
    """Full-text search over every file's content, best match first."""
    rows = await _q(
        db,
        "SELECT path, search::score(1) AS score, "
        "search::highlight('**', '**', 1) AS snippet FROM file "
        "WHERE content @1@ $query ORDER BY score DESC",
        {"query": query},
    )
    if not rows:
        return "(no matches)"
    return "\n".join(
        f"{r['path']}: {next((l for l in r['snippet'].splitlines() if '**' in l), r['snippet'])}"
        for r in rows
    )


async def demo() -> None:
    """Self-check: a short bash-shaped session against a scratch namespace."""
    db = AsyncSurreal("ws://127.0.0.1:8010/rpc")
    await db.signin({"username": "root", "password": "root"})
    await db.use("tutorial", "fs_tools_demo")
    for statement in SCHEMA.split(";\n\n"):
        if statement.strip():
            await db.query(statement)

    assert "Wrote" in await write_file(db, "/preferences/editor.md", "Uses tabs.")
    assert await cat(db, "/preferences/editor.md") == "Uses tabs."
    assert "preferences/" in await ls(db, "/")
    diff = await edit(db, "/preferences/editor.md", "tabs", "2-space indent")
    assert "2-space indent" in diff
    hit = await search(db, "indent")
    assert "editor.md" in hit
    assert "**indent**" in hit  # fails if the index loses HIGHLIGHTS
    assert await cat(db, "/missing.md") == "Error: no such file: /missing.md"
    print("all checks passed")
    await db.close()


if __name__ == "__main__":
    asyncio.run(demo())