Skip to content

v0.2: Session Store And Recall [FIRST SLICE]

Implementation Status

First slice shipped in 3708d7e:

  • Added SessionSQLiteStore at backend/agent/g_agent/session/sqlite_store.py with sessions, messages, tool_calls, media_refs, external-content FTS5, WAL, schema idempotency, and write retry.
  • Kept JSONL readable and added dual-write through SessionManager.save().
  • Added SQLite cleanup for /new/archive/delete so reused session keys do not retain stale searchable rows.
  • Added session_search and /history recall while preserving /search as web search.
  • Added URL/path/command fallback search for punctuation-heavy terms that FTS5 tokenization can miss.
  • Added tests in test_session_sqlite_store.py and updated test_session_new_command.py.

Remaining follow-up:

  • Backfill/import historical JSONL sessions into SQLite explicitly.
  • Add richer context windows around each hit instead of raw excerpts only.
  • Add owner-facing migration/status commands if the JSONL-to-SQLite transition needs manual inspection.

Goal

Give G-Agent a real memory substrate before adding self-improvement.

User Outcome

The owner can ask what happened in old sessions, search old channel messages, and recover decisions, paths, commands, URLs, and unresolved work.

Scope

  • Replace scattered/per-channel transcript assumptions with one SQLite session store.
  • Store sessions, messages, source channel, chat id, user id, model, token/cost counters, tool calls, media refs, and titles.
  • Enable WAL and write-lock retry for concurrent gateway/channel use.
  • Add FTS5 search across messages.
  • Add session_search tool.
  • Add /search, /history, and /sessions command aliases.
  • Add tests for persistence, FTS search, concurrent writes, and channel/source filtering.

Module Targets

  • backend/agent/g_agent/session/sqlite_store.py
  • backend/agent/g_agent/session/manager.py
  • backend/agent/g_agent/agent/tools/session_search.py
  • backend/agent/g_agent/channels/
  • backend/agent/g_agent/cli/commands.py
  • backend/agent/tests/test_session_sqlite_store.py
  • backend/agent/tests/test_session_new_command.py

Data Model

  • sessions: id, title, character id, channel, chat id, user id, parent session id, created at, updated at, metadata.
  • messages: id, session id, role, content, content type, provider, model, token counters, timestamps, metadata.
  • message_fts: full-text index for message content.
  • tool_calls: tool name, arguments, result summary, status, timestamps.
  • media_refs: kind, path, mime type, hash, metadata.

Acceptance Criteria

  • CLI and at least one chat channel write inbound and outbound messages into SQLite.
  • Search returns grouped results by session with short context windows.
  • Search preserves commands, paths, URLs, decisions, and unresolved items.
  • Media is referenced by path/hash, not stored as raw blobs.
  • Concurrent writes do not corrupt the database.

Tests

  • Schema creation and migration are idempotent.
  • WAL is enabled.
  • Message writes and reads are stable.
  • FTS search finds exact terms, paths, URLs, and command text.
  • Search filters by channel and session.
  • Lock retry works under concurrent writes.

References

  • hermes-agent-ref/hermes_state.py
  • hermes-agent-ref/tools/session_search_tool.py

Agent Handoff

Current G-Agent State

  • backend/agent/g_agent/session/manager.py keeps the public JSONL-compatible session API and dual-writes saved sessions into backend/agent/g_agent/session/sqlite_store.py.
  • SessionManager.get_or_create(session_key) returns a Session with in-memory messages and a SQLite session id when available.
  • AgentLoop._process_message() currently calls:
  • session = self.sessions.get_or_create(msg.session_key)
  • session.get_history()
  • session.add_message("user", clean_user_content)
  • session.add_message("assistant", log_content)
  • self.sessions.save(session)
  • /new and /compact in channels/slash_commands.py instantiate SessionManager directly and expect archive/clear behavior. /new now also removes matching SQLite rows before a reused key writes new history.
  • test_session_new_command.py includes SQLite consistency coverage; preserve that behavior when changing archive/reset flows.

Reference Details To Inspect

  • hermes_state.py
  • CREATE TABLE IF NOT EXISTS sessions
  • CREATE TABLE IF NOT EXISTS messages
  • FTS5 table/triggers
  • append_message
  • list_sessions_rich
  • write retry handling
  • session_search_tool.py
  • recent sessions mode when query is empty
  • current session exclusion
  • parent chain resolution
  • grouped search result summaries
  • raw preview fallback

Implementation Strategy

Do not delete JSONL sessions immediately. Add SQLite behind a new store and keep the old SessionManager public API stable until callers migrate.

Recommended shape:

  • session/sqlite_store.py: low-level SQLite schema and queries.
  • session/search.py: FTS query and result formatting.
  • agent/tools/session_search.py: tool wrapper around search.
  • session/manager.py: keep existing API, optionally delegate persistence to SQLite or dual-write during migration.

Implementation Slices

  1. Add SQLite store class.
  2. SessionSQLiteStore(path: Path)
  3. init_schema()
  4. append_message(...)
  5. get_history(session_key, max_messages)
  6. list_sessions(...)
  7. search_messages(query, limit, channel=None)
  8. Add FTS5.
  9. Use external content table and triggers, like Hermes.
  10. If FTS5 is unavailable, fail with a clear error in tests.
  11. Add dual-write from SessionManager.save() or AgentLoop.
  12. Prefer dual-write in SessionManager.save() first to minimize caller changes.
  13. Keep JSONL files during v0.2 for rollback.
  14. Add session search tool.
  15. Name: session_search.
  16. Query empty means recent sessions.
  17. Query non-empty searches FTS.
  18. Return session id/key, channel, chat id, timestamps, excerpts.
  19. Summarization can be deferred; raw grouped excerpts are enough for first slice.
  20. Add command routing.
  21. Existing /search is web search. Do not silently change that behavior.
  22. Add /history <query> for session search first.
  23. Later decide whether /search becomes local recall and /web becomes web search.
  24. Add tests and migration docs.

Data Model Details

Required minimum:

  • sessions(id TEXT PRIMARY KEY, key TEXT UNIQUE, channel TEXT, chat_id TEXT, user_id TEXT, title TEXT, parent_session_id TEXT, created_at TEXT, updated_at TEXT, message_count INTEGER, metadata_json TEXT)
  • messages(id INTEGER PRIMARY KEY AUTOINCREMENT, session_id TEXT, role TEXT, content TEXT, content_type TEXT, provider TEXT, model TEXT, created_at TEXT, input_tokens INTEGER, output_tokens INTEGER, metadata_json TEXT)
  • tool_calls(id INTEGER PRIMARY KEY AUTOINCREMENT, session_id TEXT, message_id INTEGER, tool_name TEXT, arguments_json TEXT, result_summary TEXT, status TEXT, created_at TEXT)
  • media_refs(id INTEGER PRIMARY KEY AUTOINCREMENT, session_id TEXT, message_id INTEGER, kind TEXT, path TEXT, mime_type TEXT, sha256 TEXT, metadata_json TEXT)

Tests To Add

  • test_session_sqlite_store.py
  • schema creation
  • WAL enabled
  • append/read history
  • list sessions sorted by updated time
  • write retry on locked DB if feasible
  • test_session_search.py
  • FTS finds path, URL, command, Indonesian text
  • current session can be excluded
  • channel filter works
  • empty query returns recent sessions
  • Update test_session_new_command.py
  • /new archives or resets without losing SQLite consistency.

Migration Guardrails

  • Existing JSONL session files must remain readable.
  • First implementation may dual-write; do not require migration before runtime starts.
  • Search should include only SQLite-backed sessions until migration is explicit.
  • Do not store raw media blobs in SQLite.

First PR Boundary

Build the smallest vertical slice:

  1. SQLite schema + append/read.
  2. Dual-write assistant/user messages from existing session save path.
  3. /history <query> against SQLite.
  4. Tests for schema, append, search, and command output.