docs/public/architecture/database.mdx
Claude-Mem uses SQLite 3 with the bun:sqlite native module for persistent storage and FTS5 for full-text search.
Path: ~/.claude-mem/claude-mem.db
The database uses SQLite's WAL (Write-Ahead Logging) mode for concurrent reads/writes.
Primary Implementation: bun:sqlite (native SQLite module)
Tracks active and completed sessions.
CREATE TABLE sdk_sessions (
id INTEGER PRIMARY KEY AUTOINCREMENT,
sdk_session_id TEXT UNIQUE NOT NULL,
claude_session_id TEXT,
project TEXT NOT NULL,
prompt_counter INTEGER DEFAULT 0,
status TEXT NOT NULL DEFAULT 'active',
created_at TEXT NOT NULL,
created_at_epoch INTEGER NOT NULL,
completed_at TEXT,
completed_at_epoch INTEGER,
last_activity_at TEXT,
last_activity_epoch INTEGER
);
Indexes:
idx_sdk_sessions_claude_session on claude_session_ididx_sdk_sessions_project on projectidx_sdk_sessions_status on statusidx_sdk_sessions_created_at on created_at_epoch DESCIndividual tool executions with hierarchical structure.
CREATE TABLE observations (
id INTEGER PRIMARY KEY AUTOINCREMENT,
session_id TEXT NOT NULL,
sdk_session_id TEXT NOT NULL,
claude_session_id TEXT,
project TEXT NOT NULL,
prompt_number INTEGER,
tool_name TEXT NOT NULL,
correlation_id TEXT,
-- Hierarchical fields
title TEXT,
subtitle TEXT,
narrative TEXT,
text TEXT,
facts TEXT,
concepts TEXT,
type TEXT,
files_read TEXT,
files_modified TEXT,
created_at TEXT NOT NULL,
created_at_epoch INTEGER NOT NULL,
FOREIGN KEY (sdk_session_id) REFERENCES sdk_sessions(sdk_session_id)
);
Observation Types:
decision - Architectural or design decisionsbugfix - Bug fixes and correctionsfeature - New features or capabilitiesrefactor - Code refactoring and cleanupdiscovery - Learnings about the codebasechange - General changes and modificationsIndexes:
idx_observations_session on session_ididx_observations_sdk_session on sdk_session_ididx_observations_project on projectidx_observations_tool_name on tool_nameidx_observations_created_at on created_at_epoch DESCidx_observations_type on typeAI-generated session summaries (multiple per session).
CREATE TABLE session_summaries (
id INTEGER PRIMARY KEY AUTOINCREMENT,
sdk_session_id TEXT NOT NULL,
claude_session_id TEXT,
project TEXT NOT NULL,
prompt_number INTEGER,
-- Summary fields
request TEXT,
investigated TEXT,
learned TEXT,
completed TEXT,
next_steps TEXT,
notes TEXT,
created_at TEXT NOT NULL,
created_at_epoch INTEGER NOT NULL,
FOREIGN KEY (sdk_session_id) REFERENCES sdk_sessions(sdk_session_id)
);
Indexes:
idx_session_summaries_sdk_session on sdk_session_ididx_session_summaries_project on projectidx_session_summaries_created_at on created_at_epoch DESCRaw user prompts with FTS5 search (as of v4.2.0).
CREATE TABLE user_prompts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
sdk_session_id TEXT NOT NULL,
claude_session_id TEXT,
project TEXT NOT NULL,
prompt_number INTEGER,
prompt_text TEXT NOT NULL,
created_at TEXT NOT NULL,
created_at_epoch INTEGER NOT NULL,
FOREIGN KEY (sdk_session_id) REFERENCES sdk_sessions(sdk_session_id)
);
Indexes:
idx_user_prompts_sdk_session on sdk_session_ididx_user_prompts_project on projectidx_user_prompts_created_at on created_at_epoch DESCSQLite FTS5 (Full-Text Search) virtual tables enable fast full-text search across observations, summaries, and user prompts.
CREATE VIRTUAL TABLE observations_fts USING fts5(
title,
subtitle,
narrative,
text,
facts,
concepts,
content='observations',
content_rowid='id'
);
CREATE VIRTUAL TABLE session_summaries_fts USING fts5(
request,
investigated,
learned,
completed,
next_steps,
notes,
content='session_summaries',
content_rowid='id'
);
CREATE VIRTUAL TABLE user_prompts_fts USING fts5(
prompt_text,
content='user_prompts',
content_rowid='id'
);
FTS5 tables stay in sync via triggers:
-- Insert trigger example
CREATE TRIGGER observations_ai AFTER INSERT ON observations BEGIN
INSERT INTO observations_fts(rowid, title, subtitle, narrative, text, facts, concepts)
VALUES (new.id, new.title, new.subtitle, new.narrative, new.text, new.facts, new.concepts);
END;
-- Update trigger example
CREATE TRIGGER observations_au AFTER UPDATE ON observations BEGIN
INSERT INTO observations_fts(observations_fts, rowid, title, subtitle, narrative, text, facts, concepts)
VALUES('delete', old.id, old.title, old.subtitle, old.narrative, old.text, old.facts, old.concepts);
INSERT INTO observations_fts(rowid, title, subtitle, narrative, text, facts, concepts)
VALUES (new.id, new.title, new.subtitle, new.narrative, new.text, new.facts, new.concepts);
END;
-- Delete trigger example
CREATE TRIGGER observations_ad AFTER DELETE ON observations BEGIN
INSERT INTO observations_fts(observations_fts, rowid, title, subtitle, narrative, text, facts, concepts)
VALUES('delete', old.id, old.title, old.subtitle, old.narrative, old.text, old.facts, old.concepts);
END;
FTS5 supports rich query syntax:
"error handling""error" AND "handling""bug" OR "fix""bug" NOT "feature""'exact phrase'"title:"authentication"As of v4.2.3, all FTS5 queries are properly escaped to prevent SQL injection:
query.replace(/"/g, '""')CRUD operations for sessions, observations, summaries, and user prompts.
Location: src/services/sqlite/SessionStore.ts
Methods:
createSession()getSession()updateSession()createObservation()getObservations()createSummary()getSummaries()createUserPrompt()FTS5 full-text search with 8 specialized search methods.
Location: src/services/sqlite/SessionSearch.ts
Methods:
searchObservations() - Full-text search across observationssearchSessions() - Full-text search across summariessearchUserPrompts() - Full-text search across user promptsfindByConcept() - Find by concept tagsfindByFile() - Find by file referencesfindByType() - Find by observation typegetRecentContext() - Get recent session contextadvancedSearch() - Combined filtersDatabase schema is managed via migrations in src/services/sqlite/migrations.ts.
Migration History:
See Troubleshooting - Database Issues for common problems and solutions.