# Database Schema for Full-Text Search Using FTS5 in Claude-Mem

> Explore the Claude-Mem database schema for FTS5 full-text search. Discover how three mirrored FTS5 virtual tables enable fast O(log N) keyword lookups for efficient searching.

- Repository: [Alex Newman/claude-mem](https://github.com/thedotmack/claude-mem)
- Tags: internals
- Published: 2026-02-16

---

**Claude-Mem implements full-text search via three SQLite FTS5 virtual tables—`observations_fts`, `session_summaries_fts`, and `user_prompts_fts`—that mirror base tables and synchronize through database triggers for O(log N) keyword lookups.**

Claude-Mem is an open-source conversational memory system that stores interaction history in SQLite. To enable fast keyword-based retrieval alongside its primary vector search, the project implements a **database schema for full-text search using FTS5**, creating virtual tables that index text content from observations, session summaries, and user prompts.

## Overview of the FTS5 Virtual Tables

The schema creates three distinct virtual tables using SQLite's FTS5 extension. Each virtual table references its corresponding base table via the `content` and `content_rowid` parameters, ensuring the FTS index remains synchronized with the underlying data.

### observations_fts

The `observations_fts` virtual table indexes the `observations` table, capturing six text columns that describe stored knowledge:

- **title**
- **subtitle**
- **narrative**
- **text**
- **facts**
- **concepts**

The table definition includes `content='observations'` and `content_rowid='id'`, binding the virtual table's `rowid` to the observation's primary key.

### session_summaries_fts

The `session_summaries_fts` virtual table mirrors the `session_summaries` table, indexing six columns that capture session metadata:

- **request**
- **investigated**
- **learned**
- **completed**
- **next_steps**
- **notes**

This structure enables full-text search across session narratives and action items.

### user_prompts_fts

The `user_prompts_fts` virtual table provides full-text indexing for the `user_prompts` table, storing only the **prompt_text** column. This is the most recent addition to the schema, implemented in Migration 010.

## Schema Implementation and Migration Files

The FTS5 schema is established through TypeScript migration files that execute raw SQL against the SQLite database.

**Migration 006** in [`src/services/sqlite/migrations.ts`](https://github.com/thedotmack/claude-mem/blob/main/src/services/sqlite/migrations.ts) creates the initial FTS5 infrastructure. Lines 379-387 define `observations_fts`, while lines 420-428 define `session_summaries_fts`. The migration also populates these tables with existing data and establishes synchronization triggers.

For backward compatibility, `SessionSearch.ensureFTSTables()` in [`src/services/sqlite/SessionSearch.ts`](https://github.com/thedotmack/claude-mem/blob/main/src/services/sqlite/SessionSearch.ts) (starting at line 63) lazily creates these same virtual tables at runtime if they do not exist. This ensures older installations gain FTS capabilities without requiring a full migration rerun.

**Migration 010**, implemented in [`src/services/sqlite/migrations/runner.ts`](https://github.com/thedotmack/claude-mem/blob/main/src/services/sqlite/migrations/runner.ts), adds the `user_prompts_fts` table. Lines 416-424 contain the `CREATE VIRTUAL TABLE` statement, while lines 425-440 define the corresponding triggers.

## Synchronization Triggers

Each FTS virtual table maintains automatic synchronization with its base table through **INSERT**, **DELETE**, and **UPDATE** triggers. These triggers ensure the full-text index reflects the current state of the underlying data without requiring manual reindexing.

The triggers use the **"delete" pseudo-row technique** that FTS5 expects: when a row is deleted or updated in the base table, the trigger inserts a row with the literal value `'delete'` into the first column of the FTS table, followed by the `rowid` of the record to remove.

For `observations_fts`, these triggers are defined in [`src/services/sqlite/migrations.ts`](https://github.com/thedotmack/claude-mem/blob/main/src/services/sqlite/migrations.ts) at lines 398-415. The `session_summaries_fts` triggers appear at lines 440-456 in the same file. The `user_prompts_fts` triggers are located in [`src/services/sqlite/migrations/runner.ts`](https://github.com/thedotmack/claude-mem/blob/main/src/services/sqlite/migrations/runner.ts) at lines 425-440.

## Querying the FTS Schema

Applications interact with the FTS5 schema using SQL `JOIN` operations combined with the `MATCH` operator, or through the TypeScript helper class.

To perform a keyword search against observations, join the base table with the virtual table and use the `MATCH` operator:

```sql
SELECT o.*
FROM observations o
JOIN observations_fts fts ON o.id = fts.rowid
WHERE fts MATCH 'error'
ORDER BY fts.rank;

```

The `rank` column is a built-in relevance score provided by FTS5. The same pattern applies to `session_summaries_fts` and `user_prompts_fts` by substituting the appropriate table names.

For TypeScript applications, the `SessionSearch` class in [`src/services/sqlite/SessionSearch.ts`](https://github.com/thedotmack/claude-mem/blob/main/src/services/sqlite/SessionSearch.ts) provides a higher-level interface. The `searchObservations` method constructs the appropriate SQL query, while `buildOrderClause` (at line 227) determines whether to sort by relevance (`fts.rank`) or creation date.

```typescript
import { SessionSearch } from './services/sqlite/SessionSearch';

const search = new SessionSearch();
const results = search.searchObservations('error', {
  limit: 20,
  orderBy: 'relevance',
});

```

## Summary

Claude-Mem's full-text search capability relies on a carefully structured FTS5 schema that balances performance with data integrity:

- **Three virtual tables**—`observations_fts`, `session_summaries_fts`, and `user_prompts_fts`—index content from their respective base tables using the `content` and `content_rowid` parameters.
- **Migration files** in [`src/services/sqlite/migrations.ts`](https://github.com/thedotmack/claude-mem/blob/main/src/services/sqlite/migrations.ts) and [`src/services/sqlite/migrations/runner.ts`](https://github.com/thedotmack/claude-mem/blob/main/src/services/sqlite/migrations/runner.ts) establish the schema, while `SessionSearch.ensureFTSTables()` provides runtime backward compatibility.
- **Database triggers** automatically synchronize the FTS index with base table changes using the FTS5 "delete" pseudo-row technique.
- **Query patterns** use standard SQL `JOIN` with the `MATCH` operator and `fts.rank` for relevance scoring, abstracted by the `SessionSearch` TypeScript class.

## Frequently Asked Questions

### What is the difference between the FTS5 virtual tables and the base tables in Claude-Mem?

The base tables—`observations`, `session_summaries`, and `user_prompts`—store the actual relational data with primary keys and foreign key relationships. The **FTS5 virtual tables** are specialized SQLite structures that maintain inverted indexes of the text content, enabling O(log N) keyword searches via the `MATCH` operator. They reference the base tables through `content` and `content_rowid` parameters but do not store duplicate data.

### How does Claude-Mem keep the FTS index synchronized when data changes?

Claude-Mem uses **AFTER INSERT**, **AFTER DELETE**, and **AFTER UPDATE** triggers defined in the migration files. When a row changes in a base table, the trigger automatically updates the corresponding FTS virtual table. For deletions and updates, the triggers use the **"delete" pseudo-row technique**—inserting a row with the literal string `'delete'` into the FTS table to remove the stale index entry before inserting the new data.

### Can I query the FTS tables directly without using the SessionSearch class?

Yes. You can execute raw SQL against the SQLite database using standard FTS5 query syntax. Join the base table with its FTS virtual table on the `rowid` field, use the `MATCH` operator in the `WHERE` clause to specify keywords, and sort by `fts.rank` for relevance. The `SessionSearch` class in [`src/services/sqlite/SessionSearch.ts`](https://github.com/thedotmack/claude-mem/blob/main/src/services/sqlite/SessionSearch.ts) simply abstracts this pattern into TypeScript methods like `searchObservations`.

### Why does Claude-Mem use both FTS5 and ChromaDB for search?

Claude-Mem uses **ChromaDB** as its primary vector store for semantic similarity search, which finds conceptually related content based on embeddings. The **FTS5** schema remains for exact keyword matching and backward compatibility. FTS5 provides O(log N) performance for specific term lookups and supports boolean query syntax, making it ideal when users search for precise strings or when the system needs to filter results without embedding latency.