Something went wrong. Try again.
MCP based ACP agent
Something went wrong. Try again.
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234"""Full-text search seam — the ONLY place dialect-specific search SQL lives.
SQLite: FTS5 virtual table + bm25 ranking.PostgreSQL: messages_fts side table with a tsvector column + GIN index,to_tsvector/plainto_tsquery under the 'simple' config (keyword parity withFTS5 — no stemming surprises). The side table is maintained from Python inthe same transaction as the message row (see writes.add_message), NOT by atrigger: the searchable text is computed by messages.message_text(), andthat extraction logic stays in Python.
Contract on both backends: search_fts returns (rowid, rank) best-first withrank LOWER = better (postgres ts_rank is negated to hold the contract; theMCP display layer negates it back).
Two entry points, and the difference is where filtering happens:``search_fts`` returns ids and leaves the caller to filter afterwards, whichmeans filtering AFTER the LIMIT — a scoped search then comes back short orempty even when the scope is full of matches, because the global top-Ncrowded it out. ``search_rows`` joins messages and agents and puts the samefilters in the WHERE, so ``limit`` means "this many matches"."""
import json
from sqlalchemy import bindparam, text
_SQLITE_DDL = ( "CREATE VIRTUAL TABLE IF NOT EXISTS messages_fts USING fts5(" "agent_id UNINDEXED, role UNINDEXED, fork_idx UNINDEXED, text)")_PG_DDL = ( "CREATE TABLE IF NOT EXISTS messages_fts (" "rowid BIGINT PRIMARY KEY, tsv tsvector NOT NULL)")_PG_INDEX = ( "CREATE INDEX IF NOT EXISTS idx_messages_fts_tsv " "ON messages_fts USING gin(tsv)")
def _is_postgres(engine) -> bool: return engine.dialect.name == "postgresql"
def create_fts(conn, engine) -> None: """Create the keyword index (called from create_database).""" if _is_postgres(engine): conn.execute(text(_PG_DDL)) conn.execute(text(_PG_INDEX)) else: conn.execute(text(_SQLITE_DDL))
def insert_fts( conn, engine, row_id: int, agent_id: str, role: str, fork_idx: int, searchable: str) -> None: """Index one message row — same transaction as the message insert.""" if _is_postgres(engine): conn.execute( text( "INSERT INTO messages_fts(rowid, tsv) " "VALUES (:r, to_tsvector('simple', :t))" ), {"r": row_id, "t": searchable}, ) else: conn.execute( text( "INSERT INTO messages_fts(rowid, agent_id, role, fork_idx, text) " "VALUES (:r, :a, :role, :f, :t)" ), {"r": row_id, "a": agent_id, "role": role, "f": fork_idx, "t": searchable}, )
def delete_fts(conn, row_ids) -> None: """Drop message rows from the keyword index, in the caller's transaction.
The index is a table of its own on both backends — an FTS5 virtual table, a postgres side table — so nothing cascades to it. A message deleted without this leaves a rowid that :func:`search_fts` still ranks and :func:`search_rows`' join then throws away, which reads to a user as a search that silently comes up short. Same SQL on both dialects, which is why this one takes no engine. """ ids = list(row_ids) if not ids: return conn.execute( text("DELETE FROM messages_fts WHERE rowid IN :ids").bindparams( bindparam("ids", expanding=True) ), {"ids": ids}, )
def search_fts(conn, engine, query: str, limit: int) -> list[tuple[int, float]]: """(rowid, rank) best-first; rank lower = better on both backends.""" if _is_postgres(engine): if not query.strip(): return [] # plainto_tsquery ANDs the lexemes and is safe on raw user input. rows = conn.execute( text( "SELECT rowid, -ts_rank(tsv, q) AS rank " "FROM messages_fts, plainto_tsquery('simple', :q) q " "WHERE tsv @@ q ORDER BY rank LIMIT :lim" ), {"q": query, "lim": limit}, ).fetchall() return [(r[0], float(r[1])) for r in rows] # Quote each token so arbitrary user input stays a valid FTS5 query # (implicit AND of phrases). match = " ".join(f'"{t}"' for t in query.split() if t) if not match: return [] rows = conn.execute( text( "SELECT rowid, bm25(messages_fts) AS rank FROM messages_fts " "WHERE messages_fts MATCH :q ORDER BY rank LIMIT :lim" ), {"q": match, "lim": limit}, ).fetchall() return [(r[0], float(r[1])) for r in rows]
# Same query, joined to messages and agents so the filters below can live in# the WHERE instead of in Python after the LIMIT. bm25() needs the FTS table# in the FROM; postgres negates ts_rank to hold the lower-is-better contract._SEARCH_ROWS = { "sqlite": ( "SELECT m.id, m.agent_id, a.session_id, a.agent_idx, a.fork_idx, " "m.role, m.created_at, m.data, bm25(messages_fts) AS rank " "FROM messages_fts " "JOIN messages m ON m.id = messages_fts.rowid " "JOIN agents a ON a.agent_id = m.agent_id " "WHERE messages_fts MATCH :q{filters} ORDER BY rank LIMIT :lim" ), "postgresql": ( "SELECT m.id, m.agent_id, a.session_id, a.agent_idx, a.fork_idx, " "m.role, m.created_at, m.data, -ts_rank(f.tsv, q) AS rank " "FROM messages_fts f " "JOIN messages m ON m.id = f.rowid " "JOIN agents a ON a.agent_id = m.agent_id " "CROSS JOIN plainto_tsquery('simple', :q) q " "WHERE f.tsv @@ q{filters} ORDER BY rank LIMIT :lim" ),}
def search_rows( conn, engine, query: str, limit: int, roles: list[str] | tuple[str, ...] | None = None, session_id: str | None = None, agent_idx: int | None = None, agent_ids: set[str] | None = None, include_forks: bool = True,) -> list[dict]: """Full message rows for a keyword query, best-first, filters in the SQL.
Every filter is a WHERE clause, so ``limit`` is the number of MATCHES returned and never the number of rows scanned — the difference between a session-scoped search that works and one that returns the intersection of "top 20 globally" with "in this session", which for a common term is usually empty.
``include_forks=False`` drops fork agents' own rows (``a.fork_idx = 1``); a fork reads the trunk's prefix through the view in reads.py, it does not copy it, so this hides only what the fork itself said.
Returns dicts of id, agent_id, session_id, agent_idx, fork_idx, role, created_at, data (always a dict — sqlite hands back the JSON column as text, postgres as jsonb) and score, lower = better on both backends. """ clauses: list[str] = [] params: dict[str, object] = {"lim": limit} expanding: list[str] = [] if roles: clauses.append("m.role IN :roles") params["roles"] = tuple(roles) expanding.append("roles") if session_id is not None: clauses.append("a.session_id = :sid") params["sid"] = session_id if agent_idx is not None: clauses.append("a.agent_idx = :aidx") params["aidx"] = agent_idx if agent_ids is not None: clauses.append("m.agent_id IN :aids") params["aids"] = tuple(agent_ids) expanding.append("aids") if not include_forks: clauses.append("a.fork_idx = 1")
if _is_postgres(engine): if not query.strip(): return [] sql = _SEARCH_ROWS["postgresql"] params["q"] = query else: # Quote each token so arbitrary input stays a valid FTS5 query # (implicit AND of phrases) — same escaping as search_fts. match = " ".join(f'"{t}"' for t in query.split() if t) if not match: return [] sql = _SEARCH_ROWS["sqlite"] params["q"] = match
stmt = text( sql.format(filters=(" AND " + " AND ".join(clauses)) if clauses else "") ).bindparams(*(bindparam(name, expanding=True) for name in expanding)) out = [] for row in conn.execute(stmt, params): data = row.data out.append( { "id": row.id, "agent_id": row.agent_id, "session_id": row.session_id, "agent_idx": row.agent_idx, "fork_idx": row.fork_idx, "role": row.role, "created_at": row.created_at, # Raw SQL bypasses the ORM's JSON type, so sqlite returns the # column as text while postgres returns jsonb already decoded. "data": json.loads(data) if isinstance(data, str) else (data or {}), "score": float(row.rank), } ) return out