claude-mem/docs/architecture/database.md

Database Architecture

Claude-Mem uses SQLite 3 with the better-sqlite3 native module for persistent storage and FTS5 for full-text search.

Database Location

• Current: ~/.claude-mem/claude-mem.db

Note: Despite the README claiming v4.0.0+ moved the database to ${CLAUDE_PLUGIN_ROOT}/data/, the actual implementation still uses ~/.claude-mem/.

Database Implementation

Primary Implementation: better-sqlite3 (native SQLite module)

• Used by: SessionStore and SessionSearch
• Format: Synchronous API with better performance
• Note: Database.ts (using bun:sqlite) is legacy code

Core Tables

1. sdk_sessions

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_id
• idx_sdk_sessions_project on project
• idx_sdk_sessions_status on status
• idx_sdk_sessions_created_at on created_at_epoch DESC

2. observations

Individual 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 decisions
• bugfix - Bug fixes and corrections
• feature - New features or capabilities
• refactor - Code refactoring and cleanup
• discovery - Learnings about the codebase
• change - General changes and modifications

Indexes:

• idx_observations_session on session_id
• idx_observations_sdk_session on sdk_session_id
• idx_observations_project on project
• idx_observations_tool_name on tool_name
• idx_observations_created_at on created_at_epoch DESC
• idx_observations_type on type

3. session_summaries

AI-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_id
• idx_session_summaries_project on project
• idx_session_summaries_created_at on created_at_epoch DESC

4. user_prompts

Raw 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_id
• idx_user_prompts_project on project
• idx_user_prompts_created_at on created_at_epoch DESC

Legacy Tables

• sessions: Legacy session tracking (v3.x)
• memories: Legacy compressed memory chunks (v3.x)
• overviews: Legacy session summaries (v3.x)

FTS5 Full-Text Search

SQLite FTS5 (Full-Text Search) virtual tables enable fast full-text search across observations, summaries, and user prompts.

FTS5 Virtual Tables

observations_fts

CREATE VIRTUAL TABLE observations_fts USING fts5(
  title,
  subtitle,
  narrative,
  text,
  facts,
  concepts,
  content='observations',
  content_rowid='id'
);

session_summaries_fts

CREATE VIRTUAL TABLE session_summaries_fts USING fts5(
  request,
  investigated,
  learned,
  completed,
  next_steps,
  notes,
  content='session_summaries',
  content_rowid='id'
);

user_prompts_fts

CREATE VIRTUAL TABLE user_prompts_fts USING fts5(
  prompt_text,
  content='user_prompts',
  content_rowid='id'
);

Automatic Synchronization

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 Query Syntax

FTS5 supports rich query syntax:

• Simple: "error handling"
• AND: "error" AND "handling"
• OR: "bug" OR "fix"
• NOT: "bug" NOT "feature"
• Phrase: "'exact phrase'"
• Column: title:"authentication"

Security

As of v4.2.3, all FTS5 queries are properly escaped to prevent SQL injection:

• Double quotes are escaped: query.replace(/"/g, '""')
• Comprehensive test suite with 332 injection attack tests

Database Classes

SessionStore

CRUD operations for sessions, observations, summaries, and user prompts.

Location: src/services/sqlite/SessionStore.ts

Methods:

• createSession()
• getSession()
• updateSession()
• createObservation()
• getObservations()
• createSummary()
• getSummaries()
• createUserPrompt()

SessionSearch

FTS5 full-text search with 8 specialized search methods.

Location: src/services/sqlite/SessionSearch.ts

Methods:

• searchObservations() - Full-text search across observations
• searchSessions() - Full-text search across summaries
• searchUserPrompts() - Full-text search across user prompts
• findByConcept() - Find by concept tags
• findByFile() - Find by file references
• findByType() - Find by observation type
• getRecentContext() - Get recent session context
• advancedSearch() - Combined filters

Migrations

Database schema is managed via migrations in src/services/sqlite/migrations.ts.

Migration History:

• Migration 001: Initial schema (sessions, memories, overviews, diagnostics, transcript_events)
• Migration 002: Hierarchical memory fields (title, subtitle, facts, concepts, files_touched)
• Migration 003: SDK sessions and observations
• Migration 004: Session summaries
• Migration 005: Multi-prompt sessions (prompt_counter, prompt_number)
• Migration 006: FTS5 virtual tables and triggers
• Migration 007-010: Various improvements and user prompts table

Performance Considerations

• Indexes: All foreign keys and frequently queried columns are indexed
• FTS5: Full-text search is significantly faster than LIKE queries
• Triggers: Automatic synchronization has minimal overhead
• Connection Pooling: better-sqlite3 reuses connections efficiently
• Synchronous API: better-sqlite3 uses synchronous API for better performance

Troubleshooting

See Troubleshooting - Database Issues for common problems and solutions.