How SQLite FTS5 Full-Text Search Works in agentsview

TLDR: agentsview implements fast, relevance-ranked full-text search by maintaining a virtual SQLite FTS5 table named messages_fts that mirrors the content column of the messages table, kept synchronized via database triggers, and queried using Porter stemming with Unicode-aware tokenization.

The agentsview repository stores every AI-assistant message in a regular SQLite table. To enable efficient token-based search across these messages, the codebase leverages SQLite's built-in FTS5 extension rather than implementing external search engines. This integration is handled entirely within the internal/db package, providing a lightweight yet powerful search capability that remains automatically synchronized with the underlying data.

Virtual Table Schema and Tokenization

The foundation of agentsview's search capability rests on a virtual FTS5 table that indexes message content while preserving the original data in the standard relational table.

Creating the messages_fts Virtual Table

In internal/db/db.go, the schema definition schemaFTS creates the virtual table with explicit mappings to the source table:

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

The content='messages' and content_rowid='id' parameters establish an external-content table relationship, telling FTS5 that the actual data lives in the messages table while the virtual table only maintains the search index. This design prevents data duplication and ensures the index references the correct rows via the id primary key.

Unicode-Aware Tokenization with Porter Stemmer

The tokenize='porter unicode61' option configures a sophisticated text processing pipeline. Unicode61 handles case-folding and accent removal, making searches case-insensitive and accent-insensitive across international text. Porter stemming reduces words to their root forms, ensuring that queries for "running" match messages containing "run" or "runs". This combination provides natural language search capabilities without requiring external dependencies.

Automatic Index Synchronization via Triggers

To maintain index consistency without manual re-indexing, agentsview employs three database triggers defined in internal/db/db.go that propagate changes from messages to messages_fts automatically:

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

The AFTER INSERT trigger (messages_ai) adds new rows to the FTS index. The AFTER UPDATE trigger (messages_au) performs a delete-then-insert sequence to handle modified content. The AFTER DELETE trigger (messages_ad) removes obsolete entries. This trigger-based approach guarantees that the FTS index remains current in real-time without application-level coordination.

Querying with FTS5 MATCH and Ranking

The search implementation in internal/db/search.go combines FTS5's boolean full-text queries with SQL window functions to deliver relevance-ordered results.

The Search Method Implementation

The public (*DB).Search method constructs a compound query that unions two distinct search branches. The FTS branch uses the MATCH operator against messages_fts:

// Conceptual query structure from search.go lines 83-99
SELECT ... FROM messages_fts 
WHERE messages_fts MATCH ? 
ORDER BY rank

Before executing any search, the code verifies FTS5 availability via db.HasFTS(), which checks for the existence of the virtual table. This prevents cryptic SQLite errors if the table has been corrupted or manually dropped.

The query employs ROW_NUMBER() OVER (PARTITION BY session_id ORDER BY rank) to select only the best-matching message per session, ensuring result diversity across conversation threads. Results can be sorted by relevance (rank ASC) or recency (julianday(session_ended_at) DESC), with pagination handled via LIMIT and OFFSET clauses.

Snippet Generation and Highlighting

FTS5 provides built-in snippet generation through the snippet() function, which agentsview leverages to create highlighted excerpts. As implemented in search.go lines 183-186, the function wraps matched terms in <mark> tags:

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

This generates a concise text excerpt with search terms highlighted, ready for frontend rendering. The rank value exposed in SearchResult.Rank represents FTS5's internal relevance score, where lower values indicate stronger matches.

Fallback Strategies and Session-Level Search

For cases where FTS5 returns no results, the search query includes a fallback branch that performs LIKE pattern matching against session display names and first messages, gated by a NOT IN subquery to prevent duplicates. This hybrid approach ensures users find relevant sessions even when full-text content matching fails.

For the in-session "find" feature, the (*DB).SearchSession method (lines 310-328 in search.go) uses a simpler case-insensitive LIKE search on both messages.content and tool_calls.result_content, avoiding the overhead of FTS ranking for targeted single-session queries.

Additionally, the searchContentFTS helper in internal/db/search_content.go (lines 20-30) provides raw FTS queries that return complete message bodies rather than snippets, used by CLI tools that require full text for processing.

Summary

• Virtual Table: agentsview creates messages_fts as an external-content FTS5 table indexing the messages.content column with Porter stemming and Unicode normalization.
• Automatic Sync: Three triggers (messages_ai, messages_au, messages_ad) in internal/db/db.go keep the FTS index synchronized with CRUD operations on the primary table.
• Relevance Ranking: The Search method uses FTS5's built-in rank function combined with SQL window functions to return the best match per session, with optional recency sorting.
• Snippet Highlighting: The snippet() function generates highlighted excerpts with <mark> tags for frontend display.
• Resilient Design: The HasFTS() check ensures graceful degradation when the virtual table is unavailable, while LIKE fallbacks cover edge cases.

Frequently Asked Questions

What tokenization rules does agentsview use for FTS5?

agentsview configures the tokenizer as 'porter unicode61', which combines the Unicode61 tokenizer for case-insensitive, accent-insensitive text processing with the Porter stemmer for word stemming. This allows searches to match variations of words (e.g., "searching" matches "search") regardless of case or diacritical marks.

How does agentsview keep the FTS index synchronized with the messages table?

The database schema includes three triggers defined in internal/db/db.go: messages_ai (after insert), messages_au (after update), and messages_ad (after delete). These triggers automatically propagate changes to the messages_fts virtual table, ensuring the index remains current without requiring manual re-indexing or application-level coordination.

Why does the search query use ROW_NUMBER() when querying the FTS table?

The ROW_NUMBER() OVER (PARTITION BY session_id ORDER BY rank) window function ensures that when multiple messages in a single session match the query, only the most relevant one (lowest rank) is returned. This prevents result flooding from verbose sessions while still identifying which sessions contain relevant content.

What happens if the FTS5 virtual table is missing or corrupted?

Before executing any MATCH query, agentsview calls db.HasFTS() to verify table existence. If the messages_fts table is missing, the search functions return a clear error rather than a generic SQLite error code. Additionally, the search implementation includes a LIKE-based fallback branch that searches session names when FTS content matching returns no results.

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