SQLite Database & Schema
Relevant source files
The following files were used as context for generating this wiki page:
The SQLite database serves as the primary persistent storage layer for Claude-mem, enabling cross-session memory by storing tool usage observations, session metadata, and semantic summaries. It is located at ~/.claude-mem/claude-mem.db and is managed by the Bun runtime.
Sources: CLAUDE.md:14, README.md:144, README.md:164
Core Storage Architecture
The database architecture is designed to support a multi-layered memory retrieval system. It works in tandem with the Worker Service to capture data from five lifecycle hooks: SessionStart, UserPromptSubmit, PostToolUse, Summary, and SessionEnd. While SQLite handles relational data and full-text search (FTS5), Chroma serves as the vector database for semantic search.
Sources: CLAUDE.md:11-12, README.md:154, README.md:167
Session Management
Claude-mem tracks sessions using a specific table structure that links content IDs from the AI agent to internal memory IDs. This allows the system to maintain continuity even when the underlying AI session ID changes.
The diagram above shows the schema for session tracking within the SQLite database.
Data Models & Schema
The primary entities stored in the database include sessions, observations, and summaries. The schema supports categorization of observations into specific types and concepts to facilitate filtered retrieval.
SDK Sessions Table
This table stores the high-level metadata for every interaction session.
| Field | Type | Description |
|---|---|---|
id | INTEGER | Primary key, autoincrementing. |
content_session_id | TEXT | Unique identifier from the Claude Code session. |
memory_session_id | TEXT | Unique identifier for the persistent memory session. |
project | TEXT | The name of the project/repository. |
status | TEXT | Default is 'active'. |
started_at_epoch | INTEGER | Millisecond timestamp of session start. |
Sources: tests/validate_sql_update.test.ts:12-25
Observation Classification
Observations are categorized to help Claude filter relevant information efficiently. The system defines six standard types and several knowledge concepts.
Observation Types:
bugfix: Fixes for broken functionality.feature: New capabilities.refactor: Restructuring without behavioral changes.change: Generic modifications (docs, config).discovery: Learning about the system.decision: Architectural/design choices.
Sources: plugin/modes/code.json:6-45
Knowledge Concepts:
how-it-works: Mechanism understanding.why-it-exists: Rationale.what-changed: Modifications.problem-solution: Issue-fix mapping.gotcha: Edge cases or traps.
Sources: plugin/modes/code.json:48-77
Data Flow & Integration
The database is populated through a series of asynchronous hooks. When a tool is used or a file is edited, the data is sent to the Worker Service, which then executes the necessary SQL commands.
This flow represents how data moves from lifecycle hooks into the SQLite storage layer.
Sources: cursor-hooks/REVIEW.md:53-150, CLAUDE.md:14
Implementation Example: Updating Session IDs
The following SQL pattern is used to link a memory session ID to an existing content session after initialization:
-- Sources: [tests/validate_sql_update.test.ts:44-45]
UPDATE sdk_sessions
SET memory_session_id = ?
WHERE content_session_id = ?;
Search Capabilities
The SQLite implementation utilizes FTS5 (Full-Text Search 5) to provide high-performance keyword searching over past work. This is part of a hybrid search strategy where SQLite handles exact matches and metadata filtering, while Chroma handles semantic vector search.
Sources: README.md:154, README.md:167
The SQLite database and its schema form the backbone of Claude-mem's persistence layer. By structuring sessions and observations with robust metadata and supporting high-speed full-text indexing, it enables the "mem-search" skill to retrieve context with high precision and low token overhead.