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

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 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 (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, 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 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 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:

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 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.

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 and 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 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.

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