MemoryStore: persistent memory data layer (SQLite + FTS5)

[kovtcharov]

Summary

Build the core data layer for agent memory. Single SQLite database (~/.gaia/memory.db) with 3 tables, FTS5 search, and WAL mode.

Spec: docs/spec/agent-memory-architecture.md → "File 1: memory_store.py"

Supersedes gaia6: Replaces SharedAgentState (singleton + 2 DBs) with simpler MemoryStore (no singleton, 1 DB).

Schema

Tables

• conversations — every turn, persistent across sessions, FTS5 searchable, context-tagged
• knowledge — facts, preferences, errors, skills with confidence scoring, context scoping, entity linking, sensitivity classification, temporal awareness (due_at/reminded_at)
• tool_history — auto-logged tool calls with success/failure, duration, error messages
• schema_version — for future migrations

Key Features

• FTS5 with AND/OR fallback + BM25 ranking
• Deduplication: >80% word overlap (Szymkiewicz-Simpson) in same category+context → update
• Confidence: 0.0–1.0 scale, +0.02 on recall, ×0.9 decay for items unused >30 days
• Timestamps: ISO 8601 with timezone via Python (not SQLite datetime)
• Thread-safe: all operations protected by threading.Lock
• WAL mode for concurrent readers (dashboard)

Class Interface

class MemoryStore:
    # Conversations
    store_turn(), get_history(), search_conversations(), get_recent_conversations()

    # Knowledge (CRUD + search)
    store(), search(), get_by_category(), get_by_entity(), get_upcoming()
    update(), update_confidence(), delete()

    # Tool History
    log_tool_call(), get_tool_errors(), get_tool_stats()

    # Dashboard/Observability
    get_stats(), get_all_knowledge(), get_tool_summary()
    get_activity_timeline(), get_recent_errors()

    # Housekeeping
    apply_confidence_decay(), prune(), rebuild_fts(), close()

Knowledge Fields

• category: fact, preference, error, skill
• source: tool, heuristic, error_auto, user, discovery
• context: global, work, personal, project-x (user-defined)
• sensitive: 0/1 flag — excluded from system prompt
• entity: lightweight type:name linking (person:X, app:Y, project:Z)
• due_at / reminded_at: temporal awareness

Acceptance Criteria

• Single DB file at ~/.gaia/memory.db with WAL mode
• All 3 tables + schema_version created on first init
• FTS5 search with AND/OR fallback works correctly
• Dedup prevents >80% overlap entries in same category+context
• Confidence decay runs on session start
• ISO 8601 timestamps with timezone on all operations
• Thread-safe under concurrent access
• Unit tests for all MemoryStore methods