How FTS5 Full-Text Search Works in the AgentsView Codebase

AgentsView implements SQLite FTS5 full-text search by creating a virtual table messages_fts that shadows the messages table, using Porter stemming for tokenization, and maintaining index synchronization through database triggers.

The open-source AgentsView project stores AI agent conversation history in SQLite and requires fast, relevance-ranked searching across millions of messages. According to the source code in kenn-io/agentsview, the application leverages the FTS5 extension to provide token-aware full-text search with automatic index maintenance and graceful degradation when FTS5 is unavailable.

Virtual Table Schema and Tokenization

In internal/db/db.go, the application defines the FTS5 virtual table schema that maps to the existing messages table:

const schemaFTS = `
CREATE VIRTUAL TABLE IF NOT EXISTS messages_fts USING fts5(
    content,
    content='messages',
    content_rowid='id',
    tokenize='porter unicode61'
);

The content table mapping (content='messages' and content_rowid='id') instructs FTS5 to reference the original rows from the messages table rather than storing a separate copy. This design keeps the index lightweight while maintaining referential integrity.

The Porter stemmer (tokenize='porter unicode61') normalizes English words during indexing, ensuring that searches for "running" match "run" and "runs". This linguistic processing happens automatically during both index creation and query execution.

Automatic Index Maintenance with Triggers

To keep the full-text index synchronized with the underlying messages, the schema includes three database triggers defined in internal/db/db.go:

CREATE TRIGGER IF NOT EXISTS messages_ai AFTER INSERT ON messages BEGIN
    INSERT INTO messages_fts(rowid, content) VALUES (new.id, new.content);
END;

CREATE TRIGGER IF NOT EXISTS messages_ad AFTER DELETE ON messages BEGIN
    INSERT INTO messages_fts(messages_fts, rowid, content)
        VALUES('delete', old.id, old.content);
END;

CREATE TRIGGER IF NOT EXISTS messages_au AFTER UPDATE ON messages BEGIN
    INSERT INTO messages_fts(messages_fts, rowid, content)
        VALUES('delete', old.id, old.content);
    INSERT INTO messages_fts(rowid, content) VALUES (new.id, new.content);
END;

These triggers handle inserts, deletes, and updates automatically. When a message is modified, the triggers queue deletion of the old content and insertion of the new content, ensuring the FTS index remains consistent without application-level coordination.

Search Query Architecture

The search implementation in internal/db/search.go combines two distinct search strategies using a UNION ALL query:

1. FTS Branch: Matches tokenized content using messages_fts MATCH ?
2. Name Branch: Matches session metadata using LIKE patterns on display_name, session_name, or first_message

The Search function constructs a single SQL query that joins both branches:

func (db *DB) Search(ctx context.Context, f SearchFilter) (SearchPage, error) {
    // Builds a UNION query combining FTS and name matching
    query := fmt.Sprintf(`
        SELECT session_id, project, agent, name, session_ended_at, 
               ordinal, snippet, rank, match_pos
        FROM (
            -- FTS branch: token-aware content search
            SELECT ... snippet(messages_fts, 0, '<mark>', '</mark>', '...', %d) AS snippet,
                best.best_rank AS rank,
                instr(LOWER(m.content), LOWER(best.best_query)) AS match_pos
            FROM ... WHERE messages_fts MATCH ? AND s2.deleted_at IS NULL ...

            UNION ALL

            -- Name branch: pattern matching on session metadata
            SELECT ... CASE ... END AS snippet,
                0.0 AS rank,
                0 AS match_pos
            FROM sessions s
            WHERE (COALESCE(s.display_name, s.session_name) LIKE ? ESCAPE '\'
                OR s.first_message LIKE ? ESCAPE '\')
                AND s.deleted_at IS NULL
                AND s.id NOT IN (SELECT session_id FROM ... /* exclude FTS matches */)
        )
        ORDER BY %s
        LIMIT ? OFFSET ?`,
        snippetTokenLength,
        orderBy,
    )
    // Execute query and scan results...
}

Ranking and Snippet Generation

The implementation uses window functions to select the best-matching message per session:

ROW_NUMBER() OVER (
    PARTITION BY m2.session_id 
    ORDER BY rank
) AS rn

This ensures only the single most relevant snippet returns for each session, even when multiple messages match the query terms.

SQLite's built-in snippet() function generates highlighted excerpts:

snippet(messages_fts, 0, '<mark>', '</mark>', '...', 32)

The function wraps matching terms in <mark> tags, which the frontend renders as highlighted text. The rank value returned by FTS5 is negative (lower values indicate better matches), so the query orders results by rank ASC to prioritize strong content matches over name-only matches.

Availability Checking and Fallback

The codebase gracefully handles environments where FTS5 is unavailable. The HasFTS method in internal/db/db.go probes for the module at runtime:

func (db *DB) HasFTS() bool {
    _, err := db.getReader().Exec("SELECT 1 FROM messages_fts LIMIT 1")
    return err == nil
}

When HasFTS() returns false, the application degrades to standard LIKE searches on the name branch only. This check also determines whether to rebuild the index during bulk operations:

if db.HasFTS() {
    db.DropFTS()      // Fast bulk delete
    db.RebuildFTS()   // Re-create from existing messages
}

The DropFTS and RebuildFTS utilities reside in internal/db/messages.go and provide index maintenance capabilities for data migrations or corruption recovery.

API Usage Examples

To search for error messages across all projects:

db, _ := db.Open("path/to/agentsview.sqlite")
filter := db.SearchFilter{
    Query:   "error",
    Project: "",
    Sort:    "relevance",
    Limit:   20,
}
page, err := db.Search(context.Background(), filter)

Via the HTTP API defined in internal/server/search.go:

curl "http://localhost:8080/api/search?query=failed+to+connect&sort=relevance&limit=10"

The JSON response includes session_id, snippet with <mark> highlighting, and rank values for relevance sorting.

Summary

• Virtual Table Mapping: The messages_fts virtual table uses content='messages' to reference the original table without data duplication, backed by the Porter stemmer for linguistic normalization.
• Automatic Synchronization: Database triggers (messages_ai, messages_ad, messages_au) maintain index consistency automatically on DML operations.
• Hybrid Search Strategy: The Search function in internal/db/search.go combines FTS5 token matching with LIKE pattern matching on session metadata via a UNION query.
• Relevance Ranking: Window functions select the best match per session, while FTS5's built-in rank and snippet functions provide relevance scoring and highlighted excerpts.
• Graceful Degradation: Runtime detection via HasFTS() allows the application to function without the FTS5 module, falling back to metadata-only searches.

Frequently Asked Questions

How does AgentsView handle message updates in the FTS index?

When a message is updated, the messages_au trigger fires automatically, inserting a delete operation for the old content followed by an insert of the new content. This two-step process ensures the FTS5 index remains synchronized without requiring manual reindexing in application code.

What happens if the SQLite binary doesn't support FTS5?

If the HasFTS() check fails (indicating the fts5 module is unavailable), the application continues to function using the Name branch only. This branch searches session display_name, session_name, and first_message columns using standard LIKE patterns, though content searching within message bodies becomes unavailable.

Why does the search use both FTS5 and LIKE queries?

The hybrid approach maximizes recall by matching not only message content via FTS5 but also session metadata (names and first messages) via LIKE patterns. This ensures users find sessions even when search terms appear only in the session title rather than in the conversation body. The UNION query combines both result sets while the ranking logic prioritizes FTS matches.

How are search snippets generated with highlighting?

The implementation uses SQLite's snippet() function with custom tags: snippet(messages_fts, 0, '<mark>', '</mark>', '...', 32). This extracts a portion of the matching text and wraps query terms in <mark> HTML tags, which the frontend renders as highlighted text. The function automatically handles ellipsis insertion when matches occur mid-content.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}