How FTS5 Full-Text Search Indexing Works in AgentsView

AgentsView implements SQLite FTS5 full-text search by mirroring the messages table into a virtual table with automatic triggers, enabling fast, ranked searches across AI agent conversations with snippet highlighting.

AgentsView is an open-source tool for managing AI agent conversations stored in SQLite. To make thousands of chat messages searchable in real-time, the application leverages FTS5 full-text search indexing through a virtual table architecture that stays synchronized automatically. This implementation in the kenn-io/agentsview repository demonstrates production-grade full-text search without external dependencies.

Creating the FTS5 Virtual Table

The foundation of the search capability lies in the messages_fts virtual table defined in internal/db/db.go. When the database initializes, the schema creation code executes the following DDL statement (lines 39-45):

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

This configuration creates several important relationships:

• content='messages' designates the source table that FTS5 will index
• content_rowid='id' maps each FTS5 row to the primary key of the source table
• tokenize='porter unicode61' applies the Porter stemming algorithm with Unicode 6.1 support, ensuring that variations like "search" and "searching" match correctly

Synchronizing the Index with Database Triggers

To maintain index consistency without application-level coordination, AgentsView employs three SQLite triggers that automatically update messages_fts whenever the messages table changes. All triggers are defined in internal/db/db.go and use IF NOT EXISTS for idempotency.

After Insert Trigger (messages_ai)

Located at lines 47-50, this trigger populates the index when new messages arrive:

INSERT INTO messages_fts(rowid, content) VALUES (new.id, new.content);

After Update Trigger (messages_au)

Located at lines 51-55, this trigger handles message edits by deleting the old entry and inserting the updated content:

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);

The special 'delete' token instructs the FTS5 virtual table to remove the obsolete row before indexing the new version.

After Delete Trigger (messages_ad)

Located at lines 33-36, this trigger cleans up the index when messages are removed:

INSERT INTO messages_fts(messages_fts, rowid, content) VALUES('delete', old.id, old.content);

These triggers ensure the FTS5 index remains synchronized with the source table, eliminating the need for manual reindexing or batch updates.

Querying the Full-Text Search Index

The high-level search API resides in the (*DB).Search method within internal/db/search.go (lines 17-34). This implementation executes a sophisticated query combining FTS5 matching, ranking, and snippet generation.

Query Preparation and Execution

The search flow follows four distinct steps:

1. Query Sanitization: The PrepareFTSQuery function escapes user input to handle punctuation and special characters literally
2. FTS Matching: The query uses the MATCH operator against messages_fts to find relevant content
3. Ranking and Deduplication: A ROW_NUMBER() window function selects the best-ranked message per session
4. Snippet Generation: The built-in snippet() function extracts context around matches with automatic highlighting

The implementation also filters system-generated messages using SystemPrefixSQL (lines 67-84) and combines results with a name-only search branch via UNION ALL to match session titles.

The generated SQL structure resembles:

SELECT session_id, project, agent, name,
       session_ended_at, ordinal, snippet, rank, match_pos
FROM (
    SELECT ... FROM messages_fts ... WHERE messages_fts MATCH ? ...
    
    UNION ALL
    
    SELECT ... FROM sessions ... WHERE ... LIKE ? ...
)
ORDER BY rank ASC, match_pos ASC
LIMIT ? OFFSET ?

Session-Specific Search Capabilities

For searching within individual conversations, the SearchSession method (lines 436-464 in internal/db/search.go) provides targeted lookup. While this method currently uses LIKE patterns on messages.content and tool_calls.result_content, it leverages the same SystemPrefixSQL filtering logic to exclude internal system messages from results.

Frontend Integration

The search functionality exposes through an HTTP GET endpoint at /api/search. The server handler (implicit in internal/server/search.go) forwards parameters to db.Search, which returns a SearchPage containing:

• Session IDs and metadata
• Highlighted snippets with mark tags around matched terms
• Pagination cursors for result sets

The frontend client (frontend/src/lib/search.ts) renders these snippets directly, allowing users to jump to specific matched messages within conversations.

Implementation Examples

Example 1: Automatic Indexing on Message Insert

When you insert a message into the database, the trigger handles indexing automatically:

db, _ := db.Open("data.db")
msg := Message{
    SessionID: "s123",
    Role:      "assistant",
    Content:   "The quick brown fox jumps over the lazy dog.",
}
_, err := db.getWriter().Exec(`
    INSERT INTO messages (session_id, role, content) VALUES (?, ?, ?)`,
    msg.SessionID, msg.Role, msg.Content)
if err != nil {
    log.Fatal(err)
}
// The messages_ai trigger automatically updates messages_fts

Example 2: Executing a Full-Text Search

Search across all conversations using the high-level API:

ctx := context.Background()
results, err := db.Search(ctx, db.SearchFilter{
    Query:   "quick fox",
    Project: "",
    Sort:    "relevance",
    Cursor:  0,
    Limit:   10,
})
if err != nil {
    log.Fatal(err)
}
for _, r := range results.Results {
    fmt.Printf("Session %s – snippet: %s\n", r.SessionID, r.Snippet)
}

Example 3: Searching Within a Specific Session

Find specific messages within a single conversation:

ordinals, err := db.SearchSession(ctx, "s123", "lazy")
if err != nil {
    log.Fatal(err)
}
fmt.Println("Message ordinals containing 'lazy':", ordinals)

Summary

• AgentsView creates an FTS5 virtual table named messages_fts in internal/db/db.go that mirrors the messages table content
• Three database triggers (messages_ai, messages_au, messages_ad) maintain automatic synchronization between the source table and the FTS5 index
• The porter unicode61 tokenizer configuration enables robust matching of stemmed words and Unicode text
• The (*DB).Search method in internal/db/search.go combines FTS5 MATCH queries with snippet() generation and ROW_NUMBER() ranking for relevant results
• System messages are filtered out using SystemPrefixSQL to prevent noise from internal agent instructions
• The architecture supports both global search across all sessions and targeted search within individual conversations

Frequently Asked Questions

How does AgentsView handle updates to existing messages in the FTS5 index?

When a message updates in the messages table, the messages_au trigger fires automatically. This trigger first inserts a "delete" command into messages_fts to remove the old content using the special 'delete' token and the original row ID, then inserts the new content with the same row ID. This two-step process ensures the full-text index remains consistent without requiring manual reindexing.

What tokenizer configuration does AgentsView use for FTS5 and why?

The implementation uses tokenize='porter unicode61' as defined in internal/db/db.go. The Porter stemmer reduces words to their root forms (matching "running" to "run"), while the Unicode 61 tokenizer properly handles international characters and punctuation. This combination ensures robust search capabilities across diverse AI agent conversation content.

How does the search ranking determine which results appear first?

The search query uses SQLite's built-in bm25 ranking implicitly through the MATCH operator, combined with a ROW_NUMBER() window function that selects the highest-ranked message per session. Results sort by rank ascending (best matches first), then by match position, ensuring the most relevant conversation snippets surface at the top of the result set.

Can I search for messages within a single conversation session only?

Yes. The SearchSession method in internal/db/search.go provides scoped searching within a specific session ID. While this method currently uses LIKE patterns for content matching, it maintains consistency with the global search by applying the same SystemPrefixSQL filters to exclude system-generated messages from results.

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