Feature: Memory Storage Migration — Flat Files to SQLite with Scope, Importance & Timestamps

[teknium1]

Overview

Migrate Hermes Agent's memory from flat text files (MEMORY.md / USER.md) to SQLite-backed storage with structured fields. This is the foundational step that enables all cognitive memory operations (#509).

Currently memory is bounded by hard character limits (2200/1375 chars), has no importance scoring, no scoping, no categories, and no timestamps beyond file modification time. This issue adds the storage layer without changing the agent-facing API.

Parent tracking issue: #509
Complements: #346 (this issue focuses on minimal migration; #346 covers the full structured memory vision including graph edges)

---

What Changes

New SQLite Schema

Add a memories table to state.db (or a dedicated memory.db):

CREATE TABLE memories (
    id TEXT PRIMARY KEY,
    content TEXT NOT NULL,
    target TEXT NOT NULL DEFAULT 'memory',  -- 'memory' or 'user' (backward compat)
    scope TEXT NOT NULL DEFAULT '/',
    categories TEXT DEFAULT '[]',           -- JSON array
    importance REAL NOT NULL DEFAULT 0.5,
    created_at REAL NOT NULL,
    updated_at REAL NOT NULL,
    last_accessed_at REAL,
    source TEXT,                            -- 'cli', 'telegram', etc.
    forgotten INTEGER DEFAULT 0            -- soft-delete
);

CREATE INDEX idx_memories_scope ON memories(scope);
CREATE INDEX idx_memories_target ON memories(target);
CREATE INDEX idx_memories_importance ON memories(importance DESC);

Memory Tool Changes

• add action: stores to SQLite instead of flat file. Accepts optional scope, importance parameters (defaults to / and 0.5).
• replace action: updates existing row, sets updated_at.
• remove action: soft-deletes (sets forgotten=1) instead of hard delete.
• System prompt injection: reads from SQLite, ordered by importance DESC, renders same format as today.
• No char limit — importance-based truncation replaces hard limits. System prompt gets top-N entries by importance that fit within a configurable token budget.

Migration

On first run with new code:

1. Read existing MEMORY.md and USER.md entries
2. Insert each as a row with target='memory' or target='user', importance=0.5, scope='/'
3. Keep MEMORY.md/USER.md as read-only backups (don't delete)
4. All future writes go to SQLite

Backward Compatibility

• Existing add/replace/remove actions work identically
• System prompt output looks the same (same format, same content)
• New optional parameters (scope, importance) are additive
• Security scanning (_scan_memory_content) preserved

---

Files to Change

• tools/memory_tool.py — MemoryStore class: replace file I/O with SQLite
• hermes_state.py — Add memories table schema (or new memory.db)
• tests/tools/test_memory_tool.py — Update tests for SQLite backend

---

Acceptance Criteria

• All existing memory tool tests pass (backward compatible)
• New memories stored in SQLite with scope, importance, timestamps
• Existing MEMORY.md/USER.md entries auto-migrated on first run
• System prompt injection works from SQLite (same output format)
• No more hard character limits — importance-based truncation
• Soft-delete instead of hard delete on remove
• Security scanning preserved for all writes