"Vibe-Trading: Your Personal Trading Agent"
Python ai-agentalgorithmic-tradingbacktestingfintechllmmcpmulti-agentpythonquantitative-financetrading
"""SQLite FTS5 session search index for cross-session full-text search.
Stores an inverted index of all conversation messages. The primary data
remains in the file-based SessionStore; this module provides a fast search
layer on top.
Database location: ``sessions.db`` under the runtime root (default
``~/.vibe-trading``; WAL mode for concurrent reads).
"""
from __future__ import annotations
import json
import logging
import re
import sqlite3
import threading as _threading
import time
from bisect import bisect_left
from collections.abc import Callable
from dataclasses import dataclass
from datetime import datetime
from functools import wraps
from pathlib import Path
from typing import Any
from src.config.paths import get_runtime_root
logger = logging.getLogger(__name__)
_DB_PATH = get_runtime_root() / "sessions.db"
# Ideographs, kana (including half-width), Hangul syllables and jamo.
_EAST_ASIAN = "\u3400-\u4dbf\u4e00-\u9fff\u3040-\u30ff\u31f0-\u31ff\uff66-\uff9f\uac00-\ud7af\u1100-\u11ff\u3130-\u318f"
_EAST_ASIAN_CHAR = re.compile(f"[{_EAST_ASIAN}]")
_QUERY_TERM = re.compile(rf"[{_EAST_ASIAN}]+|[^\W_{_EAST_ASIAN}]+")
def _prepare_search_text(text: str) -> str:
"""Separate East Asian characters for FTS5 without changing display text."""
return _EAST_ASIAN_CHAR.sub(lambda match: f" {match[0]} ", text)
def _search_snippet(
conn: sqlite3.Connection, message_id: int, content: str, fts_query: str
) -> str:
"""Map native FTS match spans back to the original, bounded excerpt."""
# Markers cannot be mistaken for literal source text. Their brackets also
# avoid partial overlaps when a control-character run borders a match.
opening, closing = "<\x01>", "<\x02>"
while opening in content or closing in content:
opening = "<" + opening[1:-1] * 2 + ">"
closing = "<" + closing[1:-1] * 2 + ">"
row = conn.execute(
"SELECT highlight(messages_fts, 0, ?, ?) FROM messages_fts "
"WHERE messages_fts MATCH ? AND rowid = ?",
(opening, closing, fts_query, message_id),
).fetchone()
highlighted = row[0] if row else ""
# Only inserted spaces need offsets; Latin messages allocate no per-char
# mapping. A boundary loses one position for each insertion before it.
insertions = []
for index, match in enumerate(_EAST_ASIAN_CHAR.finditer(content)):
position = match.start() + 2 * index
insertions.extend((position, position + 2))
spans = []
cursor = prepared_offset = 0
while True:
marker_start = highlighted.find(opening, cursor)
if marker_start < 0:
break
prepared_offset += marker_start - cursor
match_start = marker_start + len(opening)
marker_end = highlighted.find(closing, match_start)
if marker_end < 0:
break
prepared_end = prepared_offset + marker_end - match_start
start = prepared_offset - bisect_left(insertions, prepared_offset)
end = prepared_end - bisect_left(insertions, prepared_end)
if start < end:
spans.append((start, end))
prepared_offset = prepared_end
cursor = marker_end + len(closing)
start = max(0, spans[0][0] - 100) if spans else 0
end = min(len(content), start + 400)
pieces = []
cursor = start
for match_start, match_end in spans:
if match_start >= end:
break
match_start, match_end = max(match_start, start), min(match_end, end)
if match_end <= cursor:
continue
pieces.extend(
(content[cursor:match_start], ">>>", content[match_start:match_end], "<<<")
)
cursor = match_end
pieces.append(content[cursor:end])
return (
("..." if start else "")
+ "".join(pieces)
+ ("..." if end < len(content) else "")
)
@dataclass(frozen=True)
class SearchMatch:
"""A single search result from the FTS5 index.
Attributes:
session_id: Session ID.
title: Session title.
started_at: Human-readable start time.
message_count: Total messages in the session.
snippet: FTS5 snippet with match highlights (>>> <<<).
rank: FTS5 relevance rank (lower is better).
"""
session_id: str
title: str
started_at: str
message_count: int
snippet: str
rank: float
def to_dict(self) -> dict:
"""Serialize to JSON-safe dict."""
return {
"session_id": self.session_id,
"title": self.title,
"started_at": self.started_at,
"message_count": self.message_count,
"snippet": self.snippet,
}
def _serialized_operation(fn: Callable[..., Any]) -> Callable[..., Any]:
"""Keep each shared-connection operation and its cursor consumption together."""
@wraps(fn)
def wrapped(self: SessionSearchIndex, *args: Any, **kwargs: Any) -> Any:
with self._operation_lock:
return fn(self, *args, **kwargs)
return wrapped
class SessionSearchIndex:
"""SQLite FTS5 index for cross-session search.
Supports:
- Indexing individual messages as they arrive
- Full-text search with relevance ranking
- Bulk reindex from the file-based SessionStore
"""
def __init__(self, db_path: Path = _DB_PATH) -> None:
"""Initialize the search index.
Args:
db_path: Path to SQLite database file.
"""
self._operation_lock = _threading.RLock()
self.db_path = db_path
self.db_path.parent.mkdir(parents=True, exist_ok=True)
self._conn: sqlite3.Connection | None = None
self._init_db()
def _get_conn(self) -> sqlite3.Connection:
"""Get or create the SQLite connection (WAL mode)."""
if self._conn is None:
self._conn = sqlite3.connect(str(self.db_path), check_same_thread=False)
self._conn.execute("PRAGMA journal_mode=WAL")
self._conn.execute("PRAGMA synchronous=NORMAL")
return self._conn
def _init_db(self) -> None:
"""Create tables and FTS5 virtual table if they don't exist."""
conn = self._get_conn()
conn.executescript("""
CREATE TABLE IF NOT EXISTS sessions (
id TEXT PRIMARY KEY,
title TEXT NOT NULL DEFAULT '',
started_at REAL NOT NULL,
message_count INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE IF NOT EXISTS messages (
id INTEGER PRIMARY KEY AUTOINCREMENT,
session_id TEXT NOT NULL,
role TEXT NOT NULL,
content TEXT NOT NULL,
tool_name TEXT,
timestamp REAL NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_messages_session
ON messages(session_id);
""")
# Serialize upgrade across processes. Keep original messages for readable
# snippets; rebuild the derived index once, including historical rows.
conn.create_function(
"prepare_session_text", 1, _prepare_search_text, deterministic=True
)
with conn:
conn.execute("BEGIN IMMEDIATE")
columns = {row[1] for row in conn.execute("PRAGMA table_info(messages)")}
migrating = "search_content" not in columns
if migrating:
for name in ("messages_ai", "messages_ad", "messages_au"):
conn.execute(f"DROP TRIGGER IF EXISTS {name}")
conn.execute(
"ALTER TABLE messages ADD COLUMN search_content TEXT NOT NULL DEFAULT ''"
)
conn.execute(
"UPDATE messages SET search_content = prepare_session_text(content)"
)
conn.execute("DROP TABLE IF EXISTS messages_fts")
conn.execute("""
CREATE VIRTUAL TABLE IF NOT EXISTS messages_fts
USING fts5(search_content, content=messages, content_rowid=id)
""")
conn.execute(
"""CREATE TRIGGER IF NOT EXISTS messages_ai AFTER INSERT ON messages BEGIN
INSERT INTO messages_fts(rowid, search_content) VALUES (new.id, new.search_content);
END"""
)
conn.execute(
"""CREATE TRIGGER IF NOT EXISTS messages_ad AFTER DELETE ON messages BEGIN
INSERT INTO messages_fts(messages_fts, rowid, search_content)
VALUES ('delete', old.id, old.search_content);
END"""
)
if migrating:
conn.execute(
"INSERT INTO messages_fts(messages_fts) VALUES ('rebuild')"
)
@_serialized_operation
def index_session(
self,
session_id: str,
title: str = "",
ts: float | None = None,
) -> None:
"""Upsert a session record.
Args:
session_id: Session ID.
title: Session title.
ts: Session start time as a Unix epoch. When ``None``, an
existing row keeps its ``started_at`` and a new row is
stamped with ``time.time()``. Passing an explicit value
(e.g. parsed from the on-disk ``created_at``) overwrites
the stored timestamp so that bulk reindex restores the
true session start time rather than the reindex moment.
"""
conn = self._get_conn()
# Preserve the existing started_at when the caller does not supply
# one — otherwise INSERT OR REPLACE would overwrite the original
# session start time on every re-upsert, breaking date-sort/filter.
conn.execute(
"INSERT OR REPLACE INTO sessions (id, title, started_at, message_count) "
"VALUES ("
" ?, ?,"
" COALESCE(?, (SELECT started_at FROM sessions WHERE id = ?), ?),"
" COALESCE((SELECT message_count FROM sessions WHERE id = ?), 0)"
")",
(session_id, title, ts, session_id, time.time(), session_id),
)
conn.commit()
@_serialized_operation
def index_message(
self, session_id: str, role: str, content: str, tool_name: str | None = None
) -> None:
"""Index a single message.
Args:
session_id: Session ID.
role: Message role (user/assistant/tool).
content: Message text.
tool_name: Tool name if this is a tool result.
"""
if not content or not content.strip():
return
conn = self._get_conn()
content = content[:50_000]
prepared = _prepare_search_text(content)
conn.execute(
"INSERT INTO messages (session_id, role, content, search_content, tool_name, timestamp) "
"VALUES (?, ?, ?, ?, ?, ?)",
(session_id, role, content, prepared, tool_name, time.time()),
)
conn.execute(
"UPDATE sessions SET message_count = message_count + 1 WHERE id = ?",
(session_id,),
)
conn.commit()
@_serialized_operation
def remove_session(self, session_id: str) -> None:
"""Remove a session and all of its messages from the index.
The ``messages_ad`` trigger propagates each message deletion into
``messages_fts``, so the derived index clears with the base rows.
Args:
session_id: Session ID.
"""
conn = self._get_conn()
with conn:
conn.execute("DELETE FROM messages WHERE session_id = ?", (session_id,))
conn.execute("DELETE FROM sessions WHERE id = ?", (session_id,))
@staticmethod
def _sanitize_fts_query(query: str) -> str:
"""Sanitize a user query for FTS5 MATCH syntax.
- Separates Unicode words and East Asian character runs; the latter
become adjacent-token phrases, matching shorter text accurately
- Joins with OR so any-word-matches (not all-words-required)
- Quotes each token to prevent FTS5 operator interpretation
Args:
query: Raw user query string.
Returns:
FTS5-safe MATCH expression.
"""
tokens = [
_prepare_search_text(term).strip() for term in _QUERY_TERM.findall(query)
]
return " OR ".join(f'"{token}"' for token in tokens) if tokens else '""'
@_serialized_operation
def search(self, query: str, max_sessions: int = 3) -> list[SearchMatch]:
"""Full-text search across all sessions.
Args:
query: Search query (keywords or phrase).
max_sessions: Maximum number of distinct sessions to return.
Returns:
List of SearchMatch results, grouped by session, ranked by relevance.
"""
conn = self._get_conn()
fts_query = self._sanitize_fts_query(query)
try:
cursor = conn.execute(
"""
SELECT
m.session_id,
s.title,
s.started_at,
s.message_count,
m.content,
rank,
m.id
FROM messages_fts
JOIN messages m ON m.id = messages_fts.rowid
JOIN sessions s ON s.id = m.session_id
WHERE messages_fts MATCH ?
ORDER BY rank
""",
(fts_query,),
)
except sqlite3.OperationalError as exc:
logger.warning("FTS5 search failed: %s", exc)
return []
seen: dict[str, SearchMatch] = {}
for row in cursor:
sid = row[0]
if sid in seen:
continue
seen[sid] = SearchMatch(
session_id=row[0],
title=row[1] or "(untitled)",
started_at=self._format_time(row[2]),
message_count=row[3],
snippet=_search_snippet(conn, row[6], row[4] or "", fts_query),
rank=row[5],
)
if len(seen) >= max_sessions:
break
return list(seen.values())
def reindex_from_store(self, store_base_dir: Path) -> int:
"""Rebuild the entire index from file-based session store.
Args:
store_base_dir: Root directory of the SessionStore (contains session subdirs).
Returns:
Number of messages indexed.
"""
if not store_base_dir.exists():
return 0
# Only SQLite work is serialized; source-file reads remain outside.
with self._operation_lock:
conn = self._get_conn()
conn.execute("DELETE FROM messages")
conn.execute("DELETE FROM sessions")
try:
conn.execute(
"INSERT INTO messages_fts(messages_fts) VALUES ('rebuild')"
)
except sqlite3.OperationalError:
pass
conn.commit()
count = 0
for session_dir in store_base_dir.iterdir():
if not session_dir.is_dir():
continue
session_file = session_dir / "session.json"
messages_file = session_dir / "messages.jsonl"
if not session_file.exists():
continue
try:
session_data = json.loads(session_file.read_text(encoding="utf-8"))
sid = session_data.get("session_id", session_dir.name)
title = session_data.get("title", "")
try:
ts = datetime.fromisoformat(
session_data.get("created_at", "")
).timestamp()
except (ValueError, TypeError):
ts = time.time()
self.index_session(sid, title, ts=ts)
if messages_file.exists():
# JSONL uses LF as its record separator. ``splitlines``
# incorrectly treats U+2028/U+2029 inside message text as
# separators and drops the affected record during rebuild.
for line in messages_file.read_text(encoding="utf-8").strip().split("\n"):
if not line.strip():
continue
try:
msg = json.loads(line)
role = msg.get("role", "")
content = msg.get("content", "")
if content and role in ("user", "assistant"):
self.index_message(sid, role, content)
count += 1
except json.JSONDecodeError:
continue
# One corrupt session must not abort the remaining reindex.
except Exception as exc: # noqa: BLE001
logger.warning("Failed to index session %s: %s", session_dir.name, exc)
return count
@staticmethod
def _format_time(epoch: float) -> str:
"""Format epoch timestamp to human-readable string."""
try:
return datetime.fromtimestamp(epoch).strftime("%Y-%m-%d %H:%M")
except (OSError, ValueError):
return "unknown"
@_serialized_operation
def close(self) -> None:
"""Close the database connection."""
if self._conn:
self._conn.close()
self._conn = None
_shared_index: SessionSearchIndex | None = None
_shared_lock = _threading.Lock()
def get_shared_index() -> SessionSearchIndex:
"""Return a process-wide singleton SessionSearchIndex.
Thread-safe via double-checked locking. Used by both
SessionService (indexing) and SessionSearchTool (searching)
so they share one SQLite connection.
"""
global _shared_index
if _shared_index is None:
with _shared_lock:
if _shared_index is None:
_shared_index = SessionSearchIndex()
return _shared_index