Skip to content
Python
26.0 KB
Raw Blame
"""
Agent Memory V2 - SqliteCache

基于 Python 标准库 sqlite3 的零依赖审计/观测落库后端。
单机开箱即用。

设计模式跟随 history_store.py:
- 标准库 sqlite3 + _run_in_sqlite_pool()
- WAL 模式 + check_same_thread=False
- 每实例 threading.RLock 保护共享连接的全部操作与关闭

三张表:
- memory_operations / pipeline_logs / system_metrics: 审计与观测
"""

import concurrent.futures
import functools
import json
import logging
import os
import sqlite3
import threading
from datetime import datetime
from typing import Optional, List, Dict, Any

from .cache_base import CacheBase
from ..config import MemoryConfig
from ..utils.async_executor import run_in_executor_guarded

logger = logging.getLogger(__name__)

# SQLite 独立线程池(不与 VDB/Graph 竞争)
_SQLITE_POOL_SIZE = 32
_sqlite_executor = concurrent.futures.ThreadPoolExecutor(
    max_workers=_SQLITE_POOL_SIZE, thread_name_prefix="sqlite"
)


async def _run_in_sqlite_pool(func, *args, **kwargs):
    """在 SQLite 独立线程池中执行同步函数"""
    call = functools.partial(func, *args, **kwargs) if args or kwargs else func
    return await run_in_executor_guarded(_sqlite_executor, call)


# DDL
# 表名 / 列与 MySQL 分区版对齐(含 created_date),SQLite 不支持分区,仅普通建表。
_CREATE_TABLES_SQL = """
CREATE TABLE IF NOT EXISTS memory_operations_v2 (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    request_id   TEXT NOT NULL,
    user_id      TEXT NOT NULL,
    agent_id     TEXT NOT NULL DEFAULT '',
    op           TEXT NOT NULL,       -- ADD / UPDATE
    memory_id    TEXT NOT NULL,       -- 操作后的节点 ID(ADD: 新节点 ID;UPDATE: 新节点 ID)
    old_memory_id TEXT,               -- UPDATE 时的旧节点 ID
    content      TEXT NOT NULL,       -- 最终写入的内容
    layer        TEXT NOT NULL DEFAULT '',
    reason       TEXT NOT NULL DEFAULT '',
    supersedes   TEXT NOT NULL DEFAULT '',
    created_at   TEXT NOT NULL,
    created_date TEXT NOT NULL DEFAULT ''
);
CREATE INDEX IF NOT EXISTS idx_memop_request ON memory_operations_v2(request_id);
CREATE INDEX IF NOT EXISTS idx_memop_memory  ON memory_operations_v2(memory_id);
CREATE INDEX IF NOT EXISTS idx_memop_user    ON memory_operations_v2(user_id);

CREATE TABLE IF NOT EXISTS pipeline_logs_v2 (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    request_id   TEXT NOT NULL,
    namespace    TEXT NOT NULL DEFAULT '',
    user_id      TEXT NOT NULL,
    agent_id     TEXT NOT NULL DEFAULT '',
    session_id   TEXT NOT NULL DEFAULT '',
    step         TEXT NOT NULL,       -- EXTRACT / SEARCH_QUERY / RECONCILE / SUMMARY
    prompt       TEXT NOT NULL,       -- LLM prompt 原文
    response     TEXT NOT NULL,       -- LLM response 原文
    parsed       TEXT NOT NULL DEFAULT '',  -- 解析后的结构化结果 (JSON)
    memory_ids   TEXT NOT NULL DEFAULT '',  -- 关联的 memory_id 列表 (JSON array),reconcile 阶段有值
    elapsed_ms   REAL NOT NULL DEFAULT 0,
    prompt_tokens    INTEGER NOT NULL DEFAULT 0,
    completion_tokens INTEGER NOT NULL DEFAULT 0,
    total_tokens     INTEGER NOT NULL DEFAULT 0,
    event_at_ms  INTEGER NOT NULL DEFAULT 0,  -- step 发生时刻(epoch 毫秒,与 JSONL log 同源,消费方自行转可读)
    created_at   TEXT NOT NULL,
    created_date TEXT NOT NULL DEFAULT ''
);
CREATE INDEX IF NOT EXISTS idx_plog_request ON pipeline_logs_v2(request_id);
CREATE INDEX IF NOT EXISTS idx_plog_user    ON pipeline_logs_v2(user_id);

CREATE TABLE IF NOT EXISTS write_outcomes (
    request_id   TEXT PRIMARY KEY,
    user_id      TEXT NOT NULL,
    agent_id     TEXT NOT NULL DEFAULT '',
    session_id   TEXT NOT NULL DEFAULT '',
    operation    TEXT NOT NULL,
    status       TEXT NOT NULL,
    result_json  TEXT NOT NULL DEFAULT '{}',
    created_at   TEXT NOT NULL,
    updated_at   TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_write_outcomes_owner
ON write_outcomes(user_id, agent_id, session_id);

CREATE TABLE IF NOT EXISTS system_metrics (
    minute_ts  TEXT PRIMARY KEY,
    data       TEXT NOT NULL,
    created_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_metrics_ts ON system_metrics(minute_ts);


-- Episode(内部会话)状态:跨 write 批次把"一件事"归到同一个 episode_id。
-- 只存三个值;划分规则(时间间隔)在 writer 侧,此处不含业务逻辑。
CREATE TABLE IF NOT EXISTS episode_state (
    episode_key    TEXT PRIMARY KEY,   -- user::agent::session
    user_id        TEXT NOT NULL DEFAULT '',
    agent_id       TEXT NOT NULL DEFAULT '',
    session_id     TEXT NOT NULL DEFAULT '',
    episode_id     TEXT NOT NULL DEFAULT '',
    last_write_ts  REAL NOT NULL DEFAULT 0,
    memory_count   INTEGER NOT NULL DEFAULT 0,
    updated_at     TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_episode_user ON episode_state(user_id);
"""


class SqliteCache(CacheBase):
    """
    SQLite 审计/观测落库后端(memory_operations / pipeline_logs / system_metrics)。

    零依赖本地后端,适用于单机 / 开发环境。
    使用标准库 sqlite3,通过 _run_in_sqlite_pool() 包装为异步。
    """

    def __init__(self, config: MemoryConfig):
        self.config = config
        self._conn: Optional[sqlite3.Connection] = None
        # One connection is shared by the executor workers. Use the same lock
        # for reads, writes, initialization and close so close() cannot free
        # the connection while another worker is inside sqlite3.
        self._lock = threading.RLock()

        # 读取 db_path
        cache_cfg = getattr(config, "cache", None)
        if cache_cfg and getattr(cache_cfg, "db_path", None):
            self._db_path = cache_cfg.db_path
        else:
            from ..config import _default_data_dir
            self._db_path = os.getenv(
                "MEMORY_CACHE_DB_PATH",
                os.path.join(_default_data_dir(), "data", "cache.db"),
            )

    # ================================================================
    # 生命周期
    # ================================================================

    async def initialize(self) -> None:
        await _run_in_sqlite_pool(self._init_sync)
        logger.debug(f"SqliteCache initialized: {self._db_path}")

    def _init_sync(self) -> None:
        with self._lock:
            db_dir = os.path.dirname(self._db_path)
            if db_dir:
                os.makedirs(db_dir, exist_ok=True)
            self._conn = sqlite3.connect(self._db_path, check_same_thread=False)
            self._conn.row_factory = sqlite3.Row
            self._conn.execute("PRAGMA journal_mode=WAL;")
            self._conn.executescript(_CREATE_TABLES_SQL)
            self._migrate_pipeline_logs_columns()
            self._conn.commit()

    def _migrate_pipeline_logs_columns(self) -> None:
        """给旧库的 pipeline_logs_v2 补 namespace/session/event time 列。"""
        with self._lock:
            try:
                cols = {
                    row["name"]
                    for row in self._conn.execute("PRAGMA table_info(pipeline_logs_v2)")
                }
                _text_cols = ("namespace", "session_id")
                for col in _text_cols:
                    if col not in cols:
                        self._conn.execute(
                            f"ALTER TABLE pipeline_logs_v2 ADD COLUMN {col} TEXT NOT NULL DEFAULT ''"
                        )
                if "event_at_ms" not in cols:
                    self._conn.execute(
                        "ALTER TABLE pipeline_logs_v2 ADD COLUMN event_at_ms INTEGER NOT NULL DEFAULT 0"
                    )
            except Exception as e:
                logger.debug(f"[sqlite] pipeline_logs_v2 column migration skipped: {e}")

    async def close(self) -> None:
        if self._conn is not None:
            await _run_in_sqlite_pool(self._close_sync)
        logger.info("SqliteCache closed")

    def _close_sync(self) -> None:
        with self._lock:
            if self._conn:
                self._conn.close()
                self._conn = None

    # ================================================================
    # 统计
    # ================================================================

    async def get_stats(self) -> Dict[str, Any]:
        return {
            "backend": "sqlite",
            "db_path": self._db_path,
        }

    # ================================================================
    # Memory Operations Log
    # ================================================================

    async def store_memory_operation(
        self,
        request_id: str,
        user_id: str,
        agent_id: str,
        op: str,
        memory_id: str,
        content: str,
        layer: str = "",
        old_memory_id: Optional[str] = None,
        reason: str = "",
        supersedes: Optional[List[str]] = None,
    ) -> bool:
        """记录一条知识库变动操作(ADD / EVOLVE)"""
        from ..utils.pipeline_observability import is_memory_operations_enabled
        if not is_memory_operations_enabled():
            return True
        try:
            import json as _json
            from datetime import datetime as dt
            now = dt.now()
            created_at = now.isoformat()
            created_date = now.strftime("%Y-%m-%d")
            supersedes_str = _json.dumps(supersedes or [], ensure_ascii=False)

            def _insert():
                with self._lock:
                    self._conn.execute(
                        """INSERT INTO memory_operations_v2
                           (request_id, user_id, agent_id, op, memory_id, old_memory_id,
                            content, layer, reason, supersedes, created_at, created_date)
                           VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)""",
                        (request_id, user_id, agent_id, op, memory_id,
                         old_memory_id, content, layer, reason, supersedes_str,
                         created_at, created_date),
                    )
                    self._conn.commit()

            await _run_in_sqlite_pool(_insert)
            return True
        except Exception as e:
            logger.warning(f"store_memory_operation failed: {e}")
            return False

    async def get_memory_operations(
        self,
        request_id: Optional[str] = None,
        memory_id: Optional[str] = None,
        user_id: Optional[str] = None,
        limit: int = 100,
    ) -> List[Dict[str, Any]]:
        """
        查询知识库变动记录,支持按 request_id / memory_id / user_id 过滤。
        至少指定一个过滤条件。
        """
        try:
            def _query():
                with self._lock:
                    conditions = []
                    params = []
                    if request_id:
                        conditions.append("request_id = ?")
                        params.append(request_id)
                    if memory_id:
                        conditions.append("memory_id = ?")
                        params.append(memory_id)
                    if user_id:
                        conditions.append("user_id = ?")
                        params.append(user_id)

                    if not conditions:
                        return []

                    where = " AND ".join(conditions)
                    rows = self._conn.execute(
                        f"SELECT * FROM memory_operations_v2 WHERE {where} "
                        f"ORDER BY id DESC LIMIT ?",
                        params + [limit],
                    ).fetchall()
                    result = []
                    for r in rows:
                        row = dict(r)
                        # 反序列化 supersedes JSON 字符串 → list
                        import json as _json
                        raw_sup = row.get("supersedes", "")
                        try:
                            row["supersedes"] = _json.loads(raw_sup) if raw_sup else []
                        except Exception:
                            row["supersedes"] = []
                        result.append(row)
                    return result

            return await _run_in_sqlite_pool(_query)
        except Exception as e:
            logger.warning(f"get_memory_operations failed: {e}")
            return []

    # ================================================================
    # Pipeline Logs (LLM 调用链中间结果)
    # ================================================================

    async def store_pipeline_log(
        self,
        request_id: str,
        user_id: str,
        agent_id: str,
        step: str,
        prompt: str,
        response: str,
        parsed: str = "",
        memory_ids: Optional[List[str]] = None,
        elapsed_ms: float = 0,
        prompt_tokens: int = 0,
        completion_tokens: int = 0,
        total_tokens: int = 0,
        session_id: str = "",
        event_at_ms: int = 0,
    ) -> bool:
        """
        记录一条 pipeline 中间结果(EXTRACT / SEARCH_QUERY / RECONCILE / SUMMARY)。

        user_id 按 "__" 拆出 namespace 单独落库;session_id 与 user/agent 同为隔离维度。
        event_at_ms: step 发生时刻(epoch 毫秒,与 JSONL log timestamp 同源);缺省回落入库时刻。
        """
        try:
            from datetime import datetime as dt
            from ..utils.pipeline_observability import split_namespace_user
            now = dt.now()
            created_at = now.isoformat()
            created_date = now.strftime("%Y-%m-%d")
            event_at_ms = int(event_at_ms) if event_at_ms else int(now.timestamp() * 1000)
            mem_ids_json = json.dumps(memory_ids or [])
            namespace, uid = split_namespace_user(user_id)

            def _insert():
                with self._lock:
                    self._conn.execute(
                        """INSERT INTO pipeline_logs_v2
                           (request_id, namespace, user_id, agent_id, session_id,
                            step, prompt, response,
                            parsed, memory_ids, elapsed_ms,
                            prompt_tokens, completion_tokens, total_tokens,
                            event_at_ms, created_at, created_date)
                           VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)""",
                        (request_id, namespace, uid, agent_id, session_id or "",
                         step, prompt, response,
                         parsed, mem_ids_json, elapsed_ms,
                         prompt_tokens, completion_tokens, total_tokens,
                         event_at_ms, created_at, created_date),
                    )
                    self._conn.commit()

            await _run_in_sqlite_pool(_insert)
            return True
        except Exception as e:
            logger.warning(f"store_pipeline_log failed: {e}")
            return False

    async def get_pipeline_logs(
        self,
        request_id: Optional[str] = None,
        user_id: Optional[str] = None,
        step: Optional[str] = None,
        limit: int = 100,
    ) -> List[Dict[str, Any]]:
        """
        查询 pipeline 中间结果日志。

        支持按 request_id / user_id / step 过滤,至少指定一个。
        """
        try:
            def _query():
                with self._lock:
                    conditions = []
                    params = []
                    if request_id:
                        conditions.append("request_id = ?")
                        params.append(request_id)
                    if user_id:
                        conditions.append("user_id = ?")
                        params.append(user_id)
                    if step:
                        conditions.append("step = ?")
                        params.append(step)

                    if not conditions:
                        return []

                    where = " AND ".join(conditions)
                    rows = self._conn.execute(
                        f"SELECT * FROM pipeline_logs_v2 WHERE {where} "
                        f"ORDER BY id ASC LIMIT ?",
                        params + [limit],
                    ).fetchall()
                    out = [dict(r) for r in rows]
                    # 派生可读的 event_at(ISO ms)供查询接口直接用;event_at_ms 为 0 时留空
                    from datetime import datetime as _dt
                    for _r in out:
                        _ms = _r.get("event_at_ms") or 0
                        _r["event_at"] = (
                            _dt.fromtimestamp(_ms / 1000.0).isoformat(timespec="milliseconds")
                            if _ms else ""
                        )
                    return out

            return await _run_in_sqlite_pool(_query)
        except Exception as e:
            logger.warning(f"get_pipeline_logs failed: {e}")
            return []

    # ================================================================
    # Durable write outcomes
    # ================================================================

    async def claim_write_outcome(
        self,
        request_id: str,
        user_id: str,
        agent_id: str,
        session_id: str,
        operation: str,
    ) -> Optional[Dict[str, Any]]:
        def _claim():
            with self._lock:
                now = datetime.now().isoformat()
                cursor = self._conn.execute(
                    """
                    INSERT OR IGNORE INTO write_outcomes (
                        request_id, user_id, agent_id, session_id, operation,
                        status, result_json, created_at, updated_at
                    ) VALUES (?, ?, ?, ?, ?, 'running', '{}', ?, ?)
                    """,
                    (request_id, user_id, agent_id, session_id, operation, now, now),
                )
                self._conn.commit()
                if cursor.rowcount == 1:
                    return None
                row = self._conn.execute(
                    "SELECT * FROM write_outcomes WHERE request_id = ?",
                    (request_id,),
                ).fetchone()
                return dict(row) if row else None

        existing = await _run_in_sqlite_pool(_claim)
        if existing is None:
            return None
        return await self.get_write_outcome(request_id)

    async def finish_write_outcome(
        self,
        request_id: str,
        status: str,
        result: Dict[str, Any],
    ) -> bool:
        def _finish():
            with self._lock:
                cursor = self._conn.execute(
                    """
                    UPDATE write_outcomes
                    SET status = ?, result_json = ?, updated_at = ?
                    WHERE request_id = ?
                    """,
                    (
                        status,
                        json.dumps(result, ensure_ascii=False, default=str),
                        datetime.now().isoformat(),
                        request_id,
                    ),
                )
                self._conn.commit()
                return cursor.rowcount == 1

        return await _run_in_sqlite_pool(_finish)

    async def get_write_outcome(self, request_id: str) -> Optional[Dict[str, Any]]:
        def _get():
            with self._lock:
                row = self._conn.execute(
                    "SELECT * FROM write_outcomes WHERE request_id = ?",
                    (request_id,),
                ).fetchone()
                return dict(row) if row else None

        row = await _run_in_sqlite_pool(_get)
        if row is None:
            return None
        try:
            row["result"] = json.loads(row.pop("result_json") or "{}")
        except (TypeError, json.JSONDecodeError):
            row["result"] = {}
        return row

    # ================================================================
    # System Metrics(分钟粒度落盘)
    # ================================================================

    async def store_metrics_minute(self, minute_ts: str, data: dict) -> None:
        """存储一个分钟桶的增量指标数据(UPSERT: 同一分钟多次写入会合并)"""
        try:
            def _store():
                with self._lock:
                    now = datetime.now().isoformat()
                    # UPSERT: 如果同一分钟已存在,合并数据
                    existing = self._conn.execute(
                        "SELECT data FROM system_metrics WHERE minute_ts = ?",
                        (minute_ts,),
                    ).fetchone()
                    if existing:
                        import json as _json
                        old_data = _json.loads(existing["data"])
                        merged = self._merge_metric_buckets(old_data, data)
                        self._conn.execute(
                            "UPDATE system_metrics SET data = ?, created_at = ? WHERE minute_ts = ?",
                            (_json.dumps(merged, default=str), now, minute_ts),
                        )
                    else:
                        self._conn.execute(
                            "INSERT INTO system_metrics (minute_ts, data, created_at) VALUES (?, ?, ?)",
                            (minute_ts, json.dumps(data, default=str), now),
                        )
                    self._conn.commit()
            await _run_in_sqlite_pool(_store)
        except Exception as e:
            logger.warning(f"store_metrics_minute failed: {e}")

    async def load_metrics_range(self, start_ts: str, end_ts: str) -> list:
        """读取指定时间范围内的分钟指标数据"""
        try:
            def _load():
                with self._lock:
                    rows = self._conn.execute(
                        "SELECT data FROM system_metrics WHERE minute_ts >= ? AND minute_ts <= ? ORDER BY minute_ts",
                        (start_ts, end_ts),
                    ).fetchall()
                    results = []
                    for row in rows:
                        try:
                            results.append(json.loads(row["data"]))
                        except (json.JSONDecodeError, TypeError):
                            pass
                    return results
            return await _run_in_sqlite_pool(_load)
        except Exception as e:
            logger.warning(f"load_metrics_range failed: {e}")
            return []

    async def cleanup_old_metrics(self, before_ts: str) -> None:
        """删除指定时间之前的 metrics 数据"""
        try:
            def _cleanup():
                with self._lock:
                    self._conn.execute(
                        "DELETE FROM system_metrics WHERE minute_ts < ?",
                        (before_ts,),
                    )
                    self._conn.commit()
            await _run_in_sqlite_pool(_cleanup)
        except Exception as e:
            logger.warning(f"cleanup_old_metrics failed: {e}")

    # ================================================================
    # Summary Buffer
    # ================================================================




    async def get_episode_state(self, episode_key: str) -> Optional[Dict[str, Any]]:
        try:
            def _query():
                with self._lock:
                    row = self._conn.execute(
                        "SELECT episode_id, last_write_ts, memory_count "
                        "FROM episode_state WHERE episode_key = ?",
                        (episode_key,),
                    ).fetchone()
                return dict(row) if row else None

            row = await _run_in_sqlite_pool(_query)
            if not row:
                return None
            return {
                "episode_id": row.get("episode_id") or "",
                "last_write_ts": float(row.get("last_write_ts") or 0),
                "memory_count": int(row.get("memory_count") or 0),
            }
        except Exception as e:
            logger.warning(f"get_episode_state failed: {e}")
            return None

    async def upsert_episode_state(
        self,
        episode_key: str,
        *,
        user_id: str,
        agent_id: str,
        session_id: str,
        episode_id: str,
        last_write_ts: float,
        memory_count: int,
    ) -> bool:
        try:
            from datetime import datetime as _dt

            def _write():
                with self._lock:
                    self._conn.execute(
                        """
                        INSERT INTO episode_state (
                            episode_key, user_id, agent_id, session_id,
                            episode_id, last_write_ts, memory_count, updated_at
                        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
                        ON CONFLICT(episode_key) DO UPDATE SET
                            episode_id = excluded.episode_id,
                            last_write_ts = excluded.last_write_ts,
                            memory_count = excluded.memory_count,
                            updated_at = excluded.updated_at
                        """,
                        (episode_key, user_id, agent_id, session_id, episode_id,
                         float(last_write_ts), int(memory_count),
                         _dt.now().isoformat()),
                    )
                    self._conn.commit()
                return True

            return await _run_in_sqlite_pool(_write)
        except Exception as e:
            logger.warning(f"upsert_episode_state failed: {e}")
            return False

    @staticmethod
    def _merge_metric_buckets(old: dict, new: dict) -> dict:
        """合并两个 metric bucket(同一分钟多次 flush)"""
        merged = dict(old)
        for key in ("sys1_started", "sys1_completed", "sys1_failed",
                    "sys2_started", "sys2_completed", "sys2_failed",
                    "vdb_ops", "graph_ops"):
            merged[key] = merged.get(key, 0) + new.get(key, 0)
        for key in ("vdb_ops_sum_ms", "graph_ops_sum_ms"):
            merged[key] = merged.get(key, 0) + new.get(key, 0)
        # timing sums
        for bucket_key in ("sys1_timing_sums", "sys2_timing_sums"):
            old_sums = merged.get(bucket_key, {})
            new_sums = new.get(bucket_key, {})
            for k, v in new_sums.items():
                old_sums[k] = old_sums.get(k, 0) + v
            merged[bucket_key] = old_sums
        return merged