# How FTS5 Full-Text Search Works in the AgentsView Codebase

> Explore how AgentsView leverages SQLite FTS5 full-text search to power efficient message searching using a virtual table, Porter stemming, and database triggers for index synchronization.

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

---

**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`](https://github.com/kenn-io/agentsview/blob/main/internal/db/db.go), the application defines the FTS5 virtual table schema that maps to the existing `messages` table:

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

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

```go
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:

```sql
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:

```go
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`](https://github.com/kenn-io/agentsview/blob/main/internal/db/db.go) probes for the module at runtime:

```go
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:

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

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

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