Skip to content

"Vibe-Trading: Your Personal Trading Agent"

22.1 KB
Raw Blame
"""SQLite FTS5 full-text search index for persistent memory (Tier 2).

Provides O(log n) search via inverted index, replacing the O(n) sequential
token-scan in PersistentMemory.find_relevant(). Falls back gracefully if
FTS5 is unavailable.

Feature flag: VT_MEMORY_FTS_INDEX (default off).
Database: ~/.vibe-trading/memory_index.db (separate from sessions.db).
"""

from __future__ import annotations

import logging
import re
import sqlite3
import threading
from dataclasses import dataclass
from pathlib import Path
from typing import List, Optional

from src.config.paths import get_runtime_root

logger = logging.getLogger(__name__)

_DEFAULT_DB_PATH = get_runtime_root() / "memory_index.db"

# Body text truncation limit (chars)
_MAX_BODY_LEN = 50_000

# CJK Unicode range boundaries for ideograph detection
_CJK_RANGE_MAIN = (0x4E00, 0x9FFF)
_CJK_RANGE_EXT_A = (0x3400, 0x4DBF)


def _is_cjk_char(char: str) -> bool:
    """Check if character is a CJK ideograph."""
    cp = ord(char)
    return (
        _CJK_RANGE_MAIN[0] <= cp <= _CJK_RANGE_MAIN[1]
        or _CJK_RANGE_EXT_A[0] <= cp <= _CJK_RANGE_EXT_A[1]
    )


def _expand_cjk_buffer(chars: list[str]) -> str:
    """Expand a sequence of CJK chars into unigrams + bigrams.

    Example: ['\u8bb0', '\u5fc6', '\u7cfb', '\u7edf'] -> '\u8bb0 \u5fc6 \u7cfb \u7edf \u8bb0\u5fc6 \u5fc6\u7cfb \u7cfb\u7edf'
    """
    parts: list[str] = []
    # Unigrams
    parts.extend(chars)
    # Bigrams (overlapping pairs)
    for i in range(len(chars) - 1):
        parts.append(chars[i] + chars[i + 1])
    return " ".join(parts)


def _cjk_query_tokens(chars: list[str]) -> list[str]:
    """Generate unigram + bigram tokens from consecutive CJK chars for queries."""
    tokens: list[str] = list(chars)
    for i in range(len(chars) - 1):
        tokens.append(chars[i] + chars[i + 1])
    return tokens


_MARKERS = ("...", ">>>", "<<<")


def _strip_markers(token: str) -> str:
    """Remove snippet() highlight/truncation markers to see the underlying text."""
    for marker in _MARKERS:
        token = token.replace(marker, "")
    return token


def _cjk_token_inner(token: str) -> Optional[str]:
    """The 1-2 CJK chars a unigram/bigram token carries, ignoring its markers.

    Returns ``None`` for anything else (plain non-CJK text, a bare "..."
    truncation marker, or a token longer than a bigram).
    """
    inner = _strip_markers(token)
    if 1 <= len(inner) <= 2 and all(_is_cjk_char(c) for c in inner):
        return inner
    return None


def _merge_cjk_group(raw_tokens: list[str], inners: list[str]) -> str:
    """Collapse one run of unigram/bigram tokens back into display text.

    ``_prepare_cjk`` emits a CJK run as its unigrams in order, then its
    bigrams in order, so the unigrams alone already reconstruct the run and
    the bigrams are pure duplicates -- dropped whenever at least one unigram
    is present (keeping each unigram's own highlight markers intact). A
    snippet() window can still land past where a run's unigrams end, in
    which case the group holds bigrams only; those are chained on their
    shared character instead.
    """
    unigrams = [raw for raw, inner in zip(raw_tokens, inners) if len(inner) == 1]
    if unigrams:
        return "".join(unigrams)

    chars: list[str] = []
    highlighted: list[bool] = []
    for raw, inner in zip(raw_tokens, inners):
        marked = ">>>" in raw or "<<<" in raw
        if chars and chars[-1] == inner[0]:
            highlighted[-1] = highlighted[-1] or marked
            chars.append(inner[1])
            highlighted.append(marked)
        else:
            chars.extend(inner)
            highlighted.extend([marked] * len(inner))
    rendered = "".join(f">>>{char}<<<" if marked else char
                       for char, marked in zip(chars, highlighted))
    return ("..." if raw_tokens[0].startswith("...") else "") + rendered + (
        "..." if raw_tokens[-1].endswith("...") else ""
    )


class _CleanPiece:
    """One reconstructed chunk of display text plus the raw whitespace run
    that followed it, so the final join can tell a real original space
    (``gap_after >= 2`` -- ``_prepare_cjk`` doubles it, see below) apart from
    the single space it inserts purely to delimit FTS5 tokens."""

    __slots__ = ("text", "gap_after")

    def __init__(self, text: str, gap_after: int) -> None:
        self.text = text
        self.gap_after = gap_after


def _segment_with_gaps(text: str) -> list[tuple[str, int]]:
    """Split ``text`` into (content, following-whitespace-run-length) pairs."""
    parts = re.split(r"( +)", text)
    segments: list[tuple[str, int]] = []
    for i in range(0, len(parts), 2):
        seg = parts[i]
        gap = len(parts[i + 1]) if i + 1 < len(parts) else 0
        if seg:
            segments.append((seg, gap))
        elif gap and segments:
            prev_seg, prev_gap = segments[-1]
            segments[-1] = (prev_seg, prev_gap + gap)
    return segments


def _group_cjk_pieces(text: str) -> list[_CleanPiece]:
    """Merge runs of unigram/bigram tokens in ``text`` into display pieces.

    A real original space between two separate CJK runs survives
    ``_prepare_cjk`` as an *extra* space on top of the single space that
    always separates stored tokens (its own run becomes a literal
    ``" "`` block, joined on both sides by the normal token separator), so
    two consecutive CJK tokens only belong in the same run when the gap
    between them is exactly 1; anything wider is a real space and starts a
    new piece.
    """
    pieces: list[_CleanPiece] = []
    group_raw: list[str] = []
    group_inner: list[str] = []
    group_gap_after = 0
    prev_gap: Optional[int] = None

    def flush_group() -> None:
        if group_raw:
            pieces.append(_CleanPiece(_merge_cjk_group(group_raw, group_inner), group_gap_after))
            group_raw.clear()
            group_inner.clear()

    for seg, gap in _segment_with_gaps(text):
        inner = _cjk_token_inner(seg)
        if inner is not None:
            if group_raw and prev_gap != 1:
                flush_group()
            group_raw.append(seg)
            group_inner.append(inner)
            group_gap_after = gap
        else:
            flush_group()
            pieces.append(_CleanPiece(seg, gap))
        prev_gap = gap
    flush_group()
    return pieces


@dataclass(frozen=True)
class MemoryMatch:
    """A single FTS5 search result.

    Attributes:
        entry_id: 6-char hex identifier of the memory entry.
        title: Memory title.
        snippet: FTS5 snippet with match highlights (>>> <<<).
        rank: FTS5 relevance rank (lower is better).
    """

    entry_id: str
    title: str
    snippet: str
    rank: float


class MemorySearchIndex:
    """SQLite FTS5 index for memory full-text search.

    Supports:
        - Indexing individual memory entries as they are created/updated
        - Full-text search with relevance ranking
        - Bulk reindex from in-memory entry data
        - Graceful degradation when FTS5 is unavailable
    """

    def __init__(self, db_path: Optional[Path] = None) -> None:
        """Initialize the search index.

        Args:
            db_path: Path to SQLite DB (default: ~/.vibe-trading/memory_index.db).
        """
        self.db_path = db_path or _DEFAULT_DB_PATH
        self.db_path.parent.mkdir(parents=True, exist_ok=True)
        self._conn: Optional[sqlite3.Connection] = None
        self._fts_available: bool = True
        self._auto_rebuilt: bool = False
        self._op_lock = threading.Lock()
        self._init_db()

    def _get_conn(self) -> sqlite3.Connection:
        """Get or create SQLite connection with 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 + FTS5 virtual table + auto-sync triggers."""
        conn = self._get_conn()
        conn.executescript("""
            CREATE TABLE IF NOT EXISTS memories (
                id TEXT PRIMARY KEY,
                title TEXT NOT NULL DEFAULT '',
                description TEXT NOT NULL DEFAULT '',
                keywords TEXT NOT NULL DEFAULT '',
                body TEXT NOT NULL DEFAULT ''
            );
        """)

        # FTS5 virtual table — separate try/except for graceful degradation
        try:
            conn.execute("""
                CREATE VIRTUAL TABLE IF NOT EXISTS memories_fts
                USING fts5(
                    title, description, keywords, body,
                    content=memories, content_rowid=rowid
                )
            """)
        except sqlite3.OperationalError as exc:
            logger.warning("FTS5 unavailable, search disabled: %s", exc)
            self._fts_available = False
            conn.commit()
            return

        # Auto-sync triggers
        for trigger_sql in [
            """CREATE TRIGGER IF NOT EXISTS memories_ai AFTER INSERT ON memories BEGIN
                INSERT INTO memories_fts(rowid, title, description, keywords, body)
                VALUES (new.rowid, new.title, new.description, new.keywords, new.body);
            END""",
            """CREATE TRIGGER IF NOT EXISTS memories_ad AFTER DELETE ON memories BEGIN
                INSERT INTO memories_fts(memories_fts, rowid, title, description, keywords, body)
                VALUES ('delete', old.rowid, old.title, old.description, old.keywords, old.body);
            END""",
            """CREATE TRIGGER IF NOT EXISTS memories_au AFTER UPDATE ON memories BEGIN
                INSERT INTO memories_fts(memories_fts, rowid, title, description, keywords, body)
                VALUES ('delete', old.rowid, old.title, old.description, old.keywords, old.body);
                INSERT INTO memories_fts(rowid, title, description, keywords, body)
                VALUES (new.rowid, new.title, new.description, new.keywords, new.body);
            END""",
        ]:
            try:
                conn.execute(trigger_sql)
            except sqlite3.OperationalError:
                pass
        conn.commit()

    def _is_index_populated(self) -> bool:
        """Check if the FTS5 index has any entries."""
        conn = self._get_conn()
        try:
            row = conn.execute("SELECT COUNT(*) FROM memories").fetchone()
            return row[0] > 0 if row else False
        except Exception:
            return False

    def index_entry(
        self,
        entry_id: str,
        title: str,
        description: str,
        keywords: str,
        body: str,
    ) -> None:
        """Upsert a memory entry into the index.

        Args:
            entry_id: 6-char hex identifier.
            title: Memory title.
            description: One-line description.
            keywords: Space-joined keywords.
            body: Full body text (truncated to 50k chars).
        """
        with self._op_lock:
            conn = self._get_conn()
            try:
                conn.execute(
                    "INSERT OR REPLACE INTO memories (id, title, description, keywords, body) "
                    "VALUES (?, ?, ?, ?, ?)",
                    (
                        entry_id,
                        self._prepare_cjk(title),
                        self._prepare_cjk(description),
                        self._prepare_cjk(keywords),
                        self._prepare_cjk(body[:_MAX_BODY_LEN]),
                    ),
                )
                conn.commit()
            except sqlite3.OperationalError as exc:
                logger.debug("index_entry failed for %s: %s", entry_id, exc)

    def remove_entry(self, entry_id: str) -> None:
        """Remove an entry from the index by ID."""
        with self._op_lock:
            conn = self._get_conn()
            try:
                conn.execute("DELETE FROM memories WHERE id = ?", (entry_id,))
                conn.commit()
            except sqlite3.OperationalError as exc:
                logger.debug("remove_entry failed for %s: %s", entry_id, exc)

    def search(self, query: str, max_results: int = 5) -> List[MemoryMatch]:
        """Full-text search with FTS5 MATCH.

        Args:
            query: User search query.
            max_results: Maximum results to return.

        Returns:
            List of MemoryMatch sorted by relevance. Empty list if FTS5
            unavailable or query produces no results.
        """
        if not self._fts_available:
            return []

        fts_query = self._sanitize_fts_query(query)
        if not fts_query or fts_query == '""':
            return []

        with self._op_lock:
            conn = self._get_conn()
            try:
                cursor = conn.execute(
                    """
                    SELECT
                        m.id,
                        m.title,
                        snippet(memories_fts, 3, '>>>', '<<<', '...', 64) AS snippet,
                        rank
                    FROM memories_fts
                    JOIN memories m ON m.rowid = memories_fts.rowid
                    WHERE memories_fts MATCH ?
                    ORDER BY rank
                    LIMIT ?
                    """,
                    (fts_query, max_results),
                )
            except sqlite3.OperationalError as exc:
                logger.warning("FTS5 search failed: %s", exc)
                return []

            results: List[MemoryMatch] = []
            for row in cursor.fetchall():
                results.append(
                    MemoryMatch(
                        entry_id=row[0],
                        title=self._clean_cjk(row[1] or ""),
                        snippet=self._clean_cjk(row[2] or ""),
                        rank=row[3],
                    )
                )
            return results

    def rebuild_all(self, entries_data: List[tuple]) -> int:
        """Full reindex from a list of (id, title, description, keywords, body) tuples.

        Clears existing data and re-inserts all entries. Use for bulk
        synchronization from the canonical PersistentMemory store.

        Args:
            entries_data: List of tuples (id, title, description, keywords, body).

        Returns:
            Number of entries indexed.
        """
        with self._op_lock:
            conn = self._get_conn()
            try:
                conn.execute("DELETE FROM memories")
                if self._fts_available:
                    conn.execute(
                        "INSERT INTO memories_fts(memories_fts) VALUES ('rebuild')"
                    )
            except sqlite3.OperationalError as exc:
                logger.debug("rebuild_all clear failed: %s", exc)
            conn.commit()

            count = 0
            for entry in entries_data:
                if len(entry) < 5:
                    continue
                entry_id, title, description, keywords, body = (
                    entry[0],
                    entry[1],
                    entry[2],
                    entry[3],
                    entry[4],
                )
                try:
                    conn.execute(
                        "INSERT OR REPLACE INTO memories "
                        "(id, title, description, keywords, body) VALUES (?, ?, ?, ?, ?)",
                        (
                            entry_id,
                            self._prepare_cjk(title or ""),
                            self._prepare_cjk(description or ""),
                            self._prepare_cjk(keywords or ""),
                            self._prepare_cjk((body or "")[:_MAX_BODY_LEN]),
                        ),
                    )
                    count += 1
                except sqlite3.OperationalError as exc:
                    logger.debug("rebuild_all entry %s failed: %s", entry_id, exc)
            conn.commit()
            return count

    @staticmethod
    def _prepare_cjk(text: str) -> str:
        """Insert spaces for CJK chars and generate bigrams for better matching.

        Example: "记忆系统" → "记 忆 系 统 记忆 忆系 系统"
        This allows both single-char and two-char phrase matching in FTS5.
        Non-CJK text is preserved as-is.
        """
        result: list[str] = []
        cjk_buffer: list[str] = []
        text_buffer: list[str] = []

        for char in text:
            if _is_cjk_char(char):
                if text_buffer:
                    result.append("".join(text_buffer))
                    text_buffer = []
                cjk_buffer.append(char)
            else:
                if cjk_buffer:
                    result.append(_expand_cjk_buffer(cjk_buffer))
                    cjk_buffer = []
                text_buffer.append(char)

        if cjk_buffer:
            result.append(_expand_cjk_buffer(cjk_buffer))
        if text_buffer:
            result.append("".join(text_buffer))

        return " ".join(result)

    @staticmethod
    def _clean_cjk(text: str) -> str:
        """Collapse unigram/bigram tokens back into readable CJK display text.

        ``_prepare_cjk`` stores a CJK run as unigrams-then-bigrams so FTS5 can
        match both single characters and 2-char phrases, and joins every
        block (CJK run or plain-text run) with a single space so FTS5 sees
        separate tokens; this reverses both for display. A ``snippet()``
        window can cut into a run anywhere and splice ``>>>``/``<<<``
        highlight markers or ``...`` truncation into it, so runs are merged
        token-by-token (see ``_merge_cjk_group``) rather than by collapsing
        whitespace and guessing a run's original length from its character
        count -- that guess silently did nothing whenever a window did not
        hold one complete, uninterrupted run, which left duplicated
        characters in search snippets. Separately, a plain CJK run sitting
        directly against non-CJK text (a ticker, a date, punctuation) with
        no space in the original got one inserted on read, because nothing
        told the two blocks' artificial join apart from a real one -- fixed
        by using the extra width a real space leaves behind (see
        ``_group_cjk_pieces``) wherever no highlight markers are involved.
        """
        pieces = _group_cjk_pieces(text)

        joined: list[str] = []
        for idx, piece in enumerate(pieces):
            joined.append(piece.text)
            if idx == len(pieces) - 1:
                continue
            nxt = pieces[idx + 1]
            stripped = _strip_markers(piece.text)
            next_stripped = _strip_markers(nxt.text)
            ends_cjk = bool(stripped) and _is_cjk_char(stripped[-1])
            next_starts_cjk = bool(next_stripped) and _is_cjk_char(next_stripped[0])

            if ends_cjk and next_starts_cjk:
                # Both sides read as CJK script once markers are stripped
                # (a plain run, a highlighted char, or both) -- the artificial
                # join is the single space FTS5 needed as a token delimiter,
                # so only a real original space (widened past that one by
                # _prepare_cjk) earns a space back.
                if piece.gap_after >= 2:
                    joined.append(" ")
            elif ends_cjk != next_starts_cjk:
                # A plain CJK run directly against plain non-CJK text (a
                # ticker, a date, punctuation) -- same artificial-join logic,
                # just across the script boundary instead of within it.
                if piece.gap_after >= 2:
                    joined.append(" ")
            else:
                # Either both sides are plain non-CJK text (their shared
                # space was always real, never doubled), or a highlight
                # marker sits against non-CJK text -- keep the space either
                # way.
                joined.append(" ")

        return re.sub(r"\s+", " ", "".join(joined)).strip()

    @staticmethod
    def _sanitize_fts_query(query: str) -> str:
        """Sanitize user query for FTS5 MATCH syntax.

        Extracts alphanumeric tokens (2+ chars) and CJK characters,
        generates bigrams for consecutive CJK chars, quotes each token
        and joins with OR to prevent FTS5 operator injection.

        Args:
            query: Raw user query string.

        Returns:
            FTS5-safe MATCH expression, or empty-quoted string if no tokens.
        """
        tokens: list[str] = []
        cjk_buffer: list[str] = []

        # Walk through pre-extracted raw tokens
        raw_tokens = re.findall(
            r"[a-zA-Z0-9_]{2,}|[\u4e00-\u9fff\u3400-\u4dbf]", query
        )
        for tok in raw_tokens:
            if len(tok) == 1 and _is_cjk_char(tok):
                cjk_buffer.append(tok)
            else:
                if cjk_buffer:
                    tokens.extend(_cjk_query_tokens(cjk_buffer))
                    cjk_buffer = []
                tokens.append(tok)

        if cjk_buffer:
            tokens.extend(_cjk_query_tokens(cjk_buffer))

        if not tokens:
            return '""'
        # Quote each token and join with OR for broader matching
        return " OR ".join(f'"{t}"' for t in tokens)

    def close(self) -> None:
        """Close the database connection."""
        if self._conn:
            self._conn.close()
            self._conn = None


# ---------------------------------------------------------------------------
# Singleton access
# ---------------------------------------------------------------------------

_shared_index: Optional[MemorySearchIndex] = None
_shared_lock = threading.Lock()


def get_shared_index(db_path: Optional[Path] = None) -> MemorySearchIndex:
    """Return a process-wide singleton MemorySearchIndex.

    Thread-safe via double-checked locking. Shared by memory indexing
    and search callers so they use one SQLite connection.

    Args:
        db_path: Optional override for the database path (only used on
                 first call when creating the singleton).

    Returns:
        The shared MemorySearchIndex instance.
    """
    global _shared_index
    if _shared_index is None:
        with _shared_lock:
            if _shared_index is None:
                _shared_index = MemorySearchIndex(db_path=db_path)
    return _shared_index