# How FTS5 Full-Text Search Indexing Works in AgentsView

> Discover how AgentsView uses SQLite FTS5 for lightning-fast, ranked full-text search on AI agent conversations. Learn about mirrored tables, triggers, and snippet highlighting.

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

---

**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`](https://github.com/kenn-io/agentsview/blob/main/internal/db/db.go). When the database initializes, the schema creation code executes the following DDL statement (lines 39-45):

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

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

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

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

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

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

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

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