# How SQLite FTS5 Full-Text Search Works in agentsview

> Discover how agentsview leverages SQLite FTS5 for efficient full-text search. Learn about virtual tables, triggers, and Unicode tokenization for relevance-ranked results.

- Repository: [Kenn Software/agentsview](https://github.com/kenn-io/agentsview)
- Tags: internals
- Published: 2026-06-12

---

**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`](https://github.com/kenn-io/agentsview/blob/main/internal/db/db.go), the schema definition `schemaFTS` creates the virtual table with explicit mappings to the source table:

```go
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`](https://github.com/kenn-io/agentsview/blob/main/internal/db/db.go) that propagate changes from `messages` to `messages_fts` automatically:

```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_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`](https://github.com/kenn-io/agentsview/blob/main/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`:

```go
// 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`](https://github.com/kenn-io/agentsview/blob/main/search.go) lines 183-186, the function wraps matched terms in `<mark>` tags:

```go
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`](https://github.com/kenn-io/agentsview/blob/main/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`](https://github.com/kenn-io/agentsview/blob/main/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`](https://github.com/kenn-io/agentsview/blob/main/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`](https://github.com/kenn-io/agentsview/blob/main/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.