diff --git a/.gitignore b/.gitignore index aa1ea3b..a95a0c8 100644 --- a/.gitignore +++ b/.gitignore @@ -3,7 +3,6 @@ .tmp task_manager.json tools/*_data.json -navi.db session_files/ workspace/* !workspace/.keep diff --git a/docs/tech_debt_review_2026-04-29.md b/docs/tech_debt_review_2026-04-29.md index 2e1ae85..396962c 100644 --- a/docs/tech_debt_review_2026-04-29.md +++ b/docs/tech_debt_review_2026-04-29.md @@ -26,7 +26,7 @@ | # | Problem | File | Line | Explanation | |---|---------|------|------|-------------| | 7 | **[FIXED] Unbounded WebSocket replay buffer** | `navi/api/websocket.py` | 44-45 | `_AgentRun.events` is a plain `list[dict]` with no size limit. Large tool results (e.g. 5 MB terminal output) accumulate. Every reconnect replays the entire buffer, causing memory and bandwidth spikes. | -| 8 | **[LEGACY] SQLite opens a new connection per operation** | `navi/core/sqlite_session_store.py` | 68, 79, 89, 101, 111, 121, 129 | Every `create`, `get`, `save`, etc. does `async with aiosqlite.connect(...)`. No WAL, no busy timeout, no pooling. Under concurrent sessions this causes "database is locked" errors. | +| 8 | **[REMOVED] SQLite opens a new connection per operation** | `navi/core/sqlite_session_store.py` | 68, 79, 89, 101, 111, 121, 129 | SQLite support was removed entirely. PostgreSQL is now the only supported database. `aiosqlite` dependency removed, `db_path` config removed. | | 9 | **[FIXED] Memory extraction task storm** | `navi/api/routes/sessions.py` | 50 | Every `POST /sessions` spawns a fire-and-forget `asyncio.create_task(_process_stale_sessions(...))`. No deduplication or rate-limiting. Rapid session creation launches many concurrent LLM calls, overwhelming the backend. | | 10 | **[FIXED] Permanent blacklisting with no recovery** | `navi/llm/fallback.py` | 38-39 | `_dead_servers` and `_dead_models` are module-level sets. A transient network blip permanently blacklists the server until Python process restart. | | 11 | **[FIXED] Subagent exception isolation missing** | `navi/core/agent.py` | 512 | Inside `run_ephemeral()`, tools execute with bare `await tool.execute()`. No `_run_with_sentinel` wrapper. If a tool crashes, the subagent dies and all partial progress is lost. | diff --git a/navi/api/deps.py b/navi/api/deps.py index 699a9e9..c769b2e 100644 --- a/navi/api/deps.py +++ b/navi/api/deps.py @@ -12,26 +12,24 @@ PgSessionStore, ProfileRegistry, SessionStore, - SqliteSessionStore, ToolRegistry, build_default_registries, ) from navi.llm.ollama import OllamaBackend from navi.memory import MemoryStore -from navi.memory.sqlite_store import SqliteMemoryStore from navi.workers import Worker, build_default_workers def _make_session_store() -> SessionStore: - if settings.database_url: - return PgSessionStore(settings.database_url) - return SqliteSessionStore(settings.db_path) + if not settings.database_url: + raise RuntimeError("DATABASE_URL is required. SQLite support has been removed.") + return PgSessionStore(settings.database_url) def _make_memory_store() -> MemoryStore: - if settings.database_url: - return MemoryStore(settings.database_url) - return SqliteMemoryStore(settings.db_path) + if not settings.database_url: + raise RuntimeError("DATABASE_URL is required. SQLite support has been removed.") + return MemoryStore(settings.database_url) _memory_store: MemoryStore | None = None diff --git a/navi/config.py b/navi/config.py index 93d3ff9..6657258 100644 --- a/navi/config.py +++ b/navi/config.py @@ -44,11 +44,8 @@ # When set, overrides ollama_host / ollama_api_key and enables server+model fallback. ollama_backends_file: str = "" - # Database - # Set DATABASE_URL to use PostgreSQL: postgresql://user:pass@host:port/db - # Leave empty to fall back to SQLite (db_path). + # Database — PostgreSQL is required (SQLite support removed). database_url: str = "" - db_path: str = "navi.db" log_level: str = "INFO" diff --git a/navi/core/__init__.py b/navi/core/__init__.py index a675862..5d5a2b8 100644 --- a/navi/core/__init__.py +++ b/navi/core/__init__.py @@ -3,7 +3,6 @@ from .registry import BackendRegistry, ProfileRegistry, ToolRegistry, build_default_registries from navi.context_providers._loader import ContextProviderRegistry from .session import InMemorySessionStore, Session, SessionStore -from .sqlite_session_store import SqliteSessionStore from .pg_session_store import PgSessionStore __all__ = [ @@ -23,6 +22,5 @@ "Session", "SessionStore", "InMemorySessionStore", - "SqliteSessionStore", "PgSessionStore", ] diff --git a/navi/core/sqlite_session_store.py b/navi/core/sqlite_session_store.py deleted file mode 100644 index f386613..0000000 --- a/navi/core/sqlite_session_store.py +++ /dev/null @@ -1,150 +0,0 @@ -"""SQLite-backed session store — sessions survive server restarts.""" - -import json -import sqlite3 -from datetime import datetime, timezone - -import aiosqlite - -from navi.llm.base import Message - -from .session import Session, SessionStore - -_CREATE_TABLE = """ -CREATE TABLE IF NOT EXISTS sessions ( - id TEXT PRIMARY KEY, - profile_id TEXT NOT NULL, - messages TEXT NOT NULL DEFAULT '[]', - context TEXT NOT NULL DEFAULT '', - pinned INTEGER NOT NULL DEFAULT 0, - created_at TEXT NOT NULL, - last_active TEXT NOT NULL, - context_token_count INTEGER NOT NULL DEFAULT 0 -) -""" - - -def _serialize(messages: list[Message]) -> str: - return json.dumps( - [m.model_dump(mode='json', exclude_none=True) for m in messages], - ensure_ascii=False, - ) - - -def _parse_dt(value: str) -> datetime: - """Parse ISO datetime string, always returning a timezone-aware datetime.""" - dt = datetime.fromisoformat(value) - if dt.tzinfo is None: - dt = dt.replace(tzinfo=timezone.utc) - return dt - - -def _deserialize(raw: str) -> list[Message]: - if not raw: - return [] - return [Message.model_validate(m) for m in json.loads(raw)] - - -class SqliteSessionStore(SessionStore): - def __init__(self, db_path: str = "navi.db") -> None: - self._db_path = db_path - with sqlite3.connect(db_path) as conn: - conn.execute(_CREATE_TABLE) - for migration in [ - "ALTER TABLE sessions ADD COLUMN pinned INTEGER NOT NULL DEFAULT 0", - "ALTER TABLE sessions ADD COLUMN context TEXT NOT NULL DEFAULT ''", - "ALTER TABLE sessions ADD COLUMN context_token_count INTEGER NOT NULL DEFAULT 0", - "ALTER TABLE sessions ADD COLUMN name TEXT", - "ALTER TABLE sessions ADD COLUMN planning_logs TEXT NOT NULL DEFAULT '[]'", - ]: - try: - conn.execute(migration) - except sqlite3.OperationalError: - pass # column already exists - conn.commit() - - async def create(self, profile_id: str) -> Session: - session = Session(profile_id=profile_id) - async with aiosqlite.connect(self._db_path) as db: - await db.execute( - "INSERT INTO sessions " - "(id, profile_id, messages, context, pinned, created_at, last_active, context_token_count) " - "VALUES (?, ?, '[]', '', 0, ?, ?, 0)", - (session.id, session.profile_id, - session.created_at.isoformat(), session.last_active.isoformat()), - ) - await db.commit() - return session - - async def get(self, session_id: str) -> Session | None: - async with aiosqlite.connect(self._db_path) as db: - async with db.execute( - "SELECT id, profile_id, messages, context, pinned, created_at, last_active, context_token_count, name, planning_logs " - "FROM sessions WHERE id = ?", - (session_id,), - ) as cur: - row = await cur.fetchone() - return self._row_to_session(row) if row else None - - async def save(self, session: Session) -> None: - session.last_active = datetime.now(timezone.utc) - async with aiosqlite.connect(self._db_path) as db: - await db.execute( - "UPDATE sessions SET profile_id = ?, messages = ?, context = ?, " - "last_active = ?, context_token_count = ?, planning_logs = ? WHERE id = ?", - (session.profile_id, _serialize(session.messages), _serialize(session.context), - session.last_active.isoformat(), session.context_token_count, - json.dumps(session.planning_logs, ensure_ascii=False), session.id), - ) - await db.commit() - - async def set_pinned(self, session_id: str, pinned: bool) -> bool: - async with aiosqlite.connect(self._db_path) as db: - cur = await db.execute( - "UPDATE sessions SET pinned = ? WHERE id = ?", - (1 if pinned else 0, session_id), - ) - await db.commit() - return cur.rowcount > 0 - - async def set_name(self, session_id: str, name: str) -> bool: - async with aiosqlite.connect(self._db_path) as db: - cur = await db.execute( - "UPDATE sessions SET name = ? WHERE id = ?", - (name, session_id), - ) - await db.commit() - return cur.rowcount > 0 - - async def list_all(self) -> list[Session]: - async with aiosqlite.connect(self._db_path) as db: - async with db.execute( - "SELECT id, profile_id, messages, context, pinned, created_at, last_active, context_token_count, name, planning_logs " - "FROM sessions ORDER BY pinned DESC, last_active DESC" - ) as cur: - rows = await cur.fetchall() - return [self._row_to_session(r) for r in rows] - - async def delete(self, session_id: str) -> bool: - async with aiosqlite.connect(self._db_path) as db: - cur = await db.execute("DELETE FROM sessions WHERE id = ?", (session_id,)) - await db.commit() - return cur.rowcount > 0 - - def _row_to_session(self, row: tuple) -> Session: - id_, profile_id, messages_json, context_json, pinned, created_at, last_active, context_token_count, name, planning_logs_json = row - messages = _deserialize(messages_json) - context = _deserialize(context_json) if context_json else list(messages) - planning_logs = json.loads(planning_logs_json) if planning_logs_json else [] - return Session( - id=id_, - profile_id=profile_id, - messages=messages, - context=context, - pinned=bool(pinned), - name=name, - created_at=_parse_dt(created_at), - last_active=_parse_dt(last_active), - context_token_count=context_token_count or 0, - planning_logs=planning_logs, - ) diff --git a/navi/memory/sqlite_store.py b/navi/memory/sqlite_store.py deleted file mode 100644 index 00c93c6..0000000 --- a/navi/memory/sqlite_store.py +++ /dev/null @@ -1,188 +0,0 @@ -"""SQLite-backed memory store — used when DATABASE_URL is not set. - -SQLite does not support pgvector, so semantic search is disabled. -New columns are added for schema compatibility but embeddings are always NULL. -""" - -import sqlite3 -import uuid -from datetime import datetime, timezone - -import aiosqlite - -_DDL = """ -CREATE TABLE IF NOT EXISTS memory_facts ( - id TEXT PRIMARY KEY, - category TEXT NOT NULL, - key TEXT NOT NULL, - value TEXT NOT NULL, - created_at TEXT NOT NULL, - updated_at TEXT NOT NULL, - source_session_id TEXT, - source TEXT NOT NULL DEFAULT 'conversation', - confidence INTEGER NOT NULL DEFAULT 70, - expires_at TEXT, - last_verified_at TEXT, - source_context TEXT, - UNIQUE(category, key) -); - -CREATE TABLE IF NOT EXISTS memory_summary ( - id INTEGER PRIMARY KEY DEFAULT 1, - content TEXT NOT NULL, - generated_at TEXT NOT NULL -); - -CREATE TABLE IF NOT EXISTS session_memory_state ( - session_id TEXT PRIMARY KEY, - extracted_at TEXT NOT NULL -); -""" - - -class SqliteMemoryStore: - def __init__(self, db_path: str) -> None: - self._db_path = db_path - with sqlite3.connect(db_path) as conn: - conn.executescript(_DDL) - conn.commit() - - # ── Facts ──────────────────────────────────────────────────────────────── - - async def upsert_fact( - self, - category: str, - key: str, - value: str, - source_session_id: str | None = None, - source: str = "conversation", - confidence: int = 70, - expires_at: datetime | None = None, - source_context: str = "", - ) -> None: - now = datetime.now(timezone.utc).isoformat() - expires_str = expires_at.isoformat() if expires_at else None - async with aiosqlite.connect(self._db_path) as db: - await db.execute( - """INSERT INTO memory_facts - (id, category, key, value, created_at, updated_at, source_session_id, - source, confidence, expires_at, source_context) - VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) - ON CONFLICT(category, key) DO UPDATE SET - value = excluded.value, - updated_at = excluded.updated_at, - source_session_id = excluded.source_session_id, - source = excluded.source, - confidence = excluded.confidence, - expires_at = excluded.expires_at, - source_context = excluded.source_context""", - ( - str(uuid.uuid4()), category, key, value, now, now, - source_session_id, source, confidence, expires_str, source_context, - ), - ) - await db.commit() - - async def search_facts(self, query: str, limit: int = 15) -> list[dict]: - terms = [t for t in query.lower().split() if len(t) > 1] - if not terms: - return await self.get_all_facts(limit=limit) - - conditions = " OR ".join( - ["(LOWER(category) LIKE ? OR LOWER(key) LIKE ? OR LOWER(value) LIKE ?)"] * len(terms) - ) - params: list = [f"%{t}%" for t in terms for _ in range(3)] - - async with aiosqlite.connect(self._db_path) as db: - async with db.execute( - f"SELECT id, category, key, value, updated_at, source, confidence, " - f"expires_at, source_context FROM memory_facts " - f"WHERE {conditions} ORDER BY updated_at DESC LIMIT ?", - params + [limit], - ) as cur: - rows = await cur.fetchall() - return [_row_to_dict(r) for r in rows] - - async def delete_fact(self, key: str, category: str | None = None) -> int: - async with aiosqlite.connect(self._db_path) as db: - if category: - cur = await db.execute( - "DELETE FROM memory_facts WHERE LOWER(key)=LOWER(?) AND LOWER(category)=LOWER(?)", - (key, category), - ) - else: - cur = await db.execute( - "DELETE FROM memory_facts WHERE LOWER(key)=LOWER(?)", (key,) - ) - await db.commit() - return cur.rowcount - - async def get_all_facts(self, limit: int | None = None) -> list[dict]: - q = ( - "SELECT id, category, key, value, updated_at, source, confidence, " - "expires_at, source_context FROM memory_facts ORDER BY category, updated_at DESC" - ) - if limit: - q += f" LIMIT {limit}" - async with aiosqlite.connect(self._db_path) as db: - async with db.execute(q) as cur: - rows = await cur.fetchall() - return [_row_to_dict(r) for r in rows] - - async def fact_count(self) -> int: - async with aiosqlite.connect(self._db_path) as db: - async with db.execute("SELECT COUNT(*) FROM memory_facts") as cur: - row = await cur.fetchone() - return row[0] if row else 0 - - # ── Summary ─────────────────────────────────────────────────────────────── - - async def get_summary(self) -> str | None: - async with aiosqlite.connect(self._db_path) as db: - async with db.execute("SELECT content FROM memory_summary WHERE id=1") as cur: - row = await cur.fetchone() - return row[0] if row else None - - async def set_summary(self, content: str) -> None: - now = datetime.now(timezone.utc).isoformat() - async with aiosqlite.connect(self._db_path) as db: - await db.execute( - """INSERT INTO memory_summary (id, content, generated_at) VALUES (1, ?, ?) - ON CONFLICT(id) DO UPDATE SET content=excluded.content, generated_at=excluded.generated_at""", - (content, now), - ) - await db.commit() - - # ── Session extraction tracking ─────────────────────────────────────────── - - async def mark_session_extracted(self, session_id: str) -> None: - now = datetime.now(timezone.utc).isoformat() - async with aiosqlite.connect(self._db_path) as db: - await db.execute( - """INSERT INTO session_memory_state (session_id, extracted_at) VALUES (?, ?) - ON CONFLICT(session_id) DO UPDATE SET extracted_at=excluded.extracted_at""", - (session_id, now), - ) - await db.commit() - - async def get_extracted_at(self, session_id: str) -> str | None: - async with aiosqlite.connect(self._db_path) as db: - async with db.execute( - "SELECT extracted_at FROM session_memory_state WHERE session_id=?", (session_id,) - ) as cur: - row = await cur.fetchone() - return row[0] if row else None - - -def _row_to_dict(row: tuple) -> dict: - return { - "id": row[0], - "category": row[1], - "key": row[2], - "value": row[3], - "updated_at": row[4], - "source": row[5] if len(row) > 5 else "conversation", - "confidence": row[6] if len(row) > 6 else 70, - "expires_at": row[7] if len(row) > 7 else None, - "source_context": row[8] if len(row) > 8 else "", - } diff --git a/pyproject.toml b/pyproject.toml index e735ecd..f0d7d49 100644 --- a/pyproject.toml +++ b/pyproject.toml @@ -14,7 +14,6 @@ "uvicorn[standard]>=0.29", "python-multipart>=0.0.9", "aiofiles>=23.0", - "aiosqlite>=0.20", "asyncpg>=0.29", # LLM backends diff --git a/requirements.txt b/requirements.txt index a7b9e85..a805453 100644 --- a/requirements.txt +++ b/requirements.txt @@ -1,6 +1,5 @@ fastapi uvicorn -aiosqlite pydantic websockets pgvector