Claude Memory MCP Server

/** * Database schema definitions and migrations for Memory MCP */ import type { DbDriver } from './db-driver.js'; export const SCHEMA_VERSION = 3; /** * Initialize database schema */ export function initializeSchema(db: DbDriver): void { // Enable foreign keys db.pragma('foreign_keys = ON'); // Create schema version table db.exec(` CREATE TABLE IF NOT EXISTS schema_version ( version INTEGER PRIMARY KEY, applied_at INTEGER NOT NULL ); `); // Check current version const currentVersion = db .prepare('SELECT version FROM schema_version ORDER BY version DESC LIMIT 1') .pluck() .get() as number | undefined; if (!currentVersion || currentVersion < SCHEMA_VERSION) { applyMigrations(db, currentVersion || 0); } } /** * Apply migrations from current version to latest */ function applyMigrations(db: DbDriver, fromVersion: number): void { const migrations = [ // Migration 1: Initial schema (db: DbDriver) => { db.exec(` -- Memories table: Core memory storage CREATE TABLE IF NOT EXISTS memories ( id TEXT PRIMARY KEY, content TEXT NOT NULL, type TEXT NOT NULL CHECK(type IN ('fact', 'entity', 'relationship', 'self')), importance REAL NOT NULL CHECK(importance >= 0 AND importance <= 10), embedding BLOB, created_at INTEGER NOT NULL, last_accessed INTEGER NOT NULL, expires_at INTEGER, metadata TEXT NOT NULL DEFAULT '{}', is_deleted INTEGER NOT NULL DEFAULT 0 CHECK(is_deleted IN (0, 1)) ); -- Indexes for memories CREATE INDEX IF NOT EXISTS idx_memories_type ON memories(type); CREATE INDEX IF NOT EXISTS idx_memories_importance ON memories(importance); CREATE INDEX IF NOT EXISTS idx_memories_last_accessed ON memories(last_accessed); CREATE INDEX IF NOT EXISTS idx_memories_expires_at ON memories(expires_at); CREATE INDEX IF NOT EXISTS idx_memories_is_deleted ON memories(is_deleted); -- Entities table: Named entities CREATE TABLE IF NOT EXISTS entities ( id TEXT PRIMARY KEY, name TEXT NOT NULL UNIQUE, type TEXT NOT NULL, metadata TEXT NOT NULL DEFAULT '{}', created_at INTEGER NOT NULL ); -- Index for entities CREATE INDEX IF NOT EXISTS idx_entities_name ON entities(name); CREATE INDEX IF NOT EXISTS idx_entities_type ON entities(type); -- Memory-Entity link table CREATE TABLE IF NOT EXISTS memory_entities ( memory_id TEXT NOT NULL, entity_id TEXT NOT NULL, created_at INTEGER NOT NULL, PRIMARY KEY (memory_id, entity_id), FOREIGN KEY (memory_id) REFERENCES memories(id) ON DELETE CASCADE, FOREIGN KEY (entity_id) REFERENCES entities(id) ON DELETE CASCADE ); -- Indexes for memory_entities CREATE INDEX IF NOT EXISTS idx_memory_entities_memory_id ON memory_entities(memory_id); CREATE INDEX IF NOT EXISTS idx_memory_entities_entity_id ON memory_entities(entity_id); -- Provenance table: Audit trail CREATE TABLE IF NOT EXISTS provenance ( id TEXT PRIMARY KEY, memory_id TEXT NOT NULL, operation TEXT NOT NULL CHECK(operation IN ('create', 'update', 'delete', 'access', 'restore')), timestamp INTEGER NOT NULL, source TEXT NOT NULL, context TEXT, user_id TEXT, changes TEXT, FOREIGN KEY (memory_id) REFERENCES memories(id) ON DELETE CASCADE ); -- Indexes for provenance CREATE INDEX IF NOT EXISTS idx_provenance_memory_id ON provenance(memory_id); CREATE INDEX IF NOT EXISTS idx_provenance_timestamp ON provenance(timestamp); CREATE INDEX IF NOT EXISTS idx_provenance_operation ON provenance(operation); `); // Record migration db.prepare('INSERT INTO schema_version (version, applied_at) VALUES (?, ?)').run( 1, Date.now() ); }, // Migration 2: Add summary and access_count fields for v2.0 optimization (db: DbDriver) => { // Add summary column (TEXT, will be NOT NULL after backfill) db.exec(` ALTER TABLE memories ADD COLUMN summary TEXT; `); // Add access_count column for frequency tracking db.exec(` ALTER TABLE memories ADD COLUMN access_count INTEGER NOT NULL DEFAULT 0; `); // Generate summaries for existing memories (first 100 characters as fallback) const memories = db.prepare('SELECT id, content FROM memories').all() as Array<{ id: string; content: string; }>; const updateSummary = db.prepare('UPDATE memories SET summary = ? WHERE id = ?'); for (const memory of memories) { // Simple summary: first 100 characters or first sentence let summary = memory.content; const firstSentence = memory.content.match(/^[^.!?]+[.!?]/); if (firstSentence && firstSentence[0].length <= 100) { summary = firstSentence[0].trim(); } else if (memory.content.length > 100) { summary = memory.content.substring(0, 97) + '...'; } updateSummary.run(summary, memory.id); } // Now make summary NOT NULL (SQLite doesn't support ALTER COLUMN, so we verify all are filled) const nullSummaries = db .prepare('SELECT COUNT(*) FROM memories WHERE summary IS NULL') .pluck() .get() as number; if (nullSummaries > 0) { throw new Error( `Migration 2 failed: ${nullSummaries} memories still have NULL summaries` ); } // Record migration db.prepare('INSERT INTO schema_version (version, applied_at) VALUES (?, ?)').run( 2, Date.now() ); }, // Migration 3: Add FTS5 for keyword search (replaces vector embeddings) (db: DbDriver) => { // Create FTS5 virtual table for full-text search on memory content db.exec(` CREATE VIRTUAL TABLE IF NOT EXISTS memories_fts USING fts5( memory_id UNINDEXED, content, summary, tokenize = 'porter unicode61' ); `); // Populate FTS table with existing memories db.exec(` INSERT INTO memories_fts (memory_id, content, summary) SELECT id, content, summary FROM memories WHERE is_deleted = 0; `); // Create triggers to keep FTS index synchronized db.exec(` -- Trigger: Insert into FTS when memory created CREATE TRIGGER IF NOT EXISTS memories_fts_insert AFTER INSERT ON memories WHEN NEW.is_deleted = 0 BEGIN INSERT INTO memories_fts (memory_id, content, summary) VALUES (NEW.id, NEW.content, NEW.summary); END; -- Trigger: Update FTS when memory content/summary updated CREATE TRIGGER IF NOT EXISTS memories_fts_update AFTER UPDATE OF content, summary ON memories WHEN NEW.is_deleted = 0 BEGIN DELETE FROM memories_fts WHERE memory_id = NEW.id; INSERT INTO memories_fts (memory_id, content, summary) VALUES (NEW.id, NEW.content, NEW.summary); END; -- Trigger: Delete from FTS when memory soft-deleted CREATE TRIGGER IF NOT EXISTS memories_fts_delete AFTER UPDATE OF is_deleted ON memories WHEN NEW.is_deleted = 1 BEGIN DELETE FROM memories_fts WHERE memory_id = NEW.id; END; -- Trigger: Add back to FTS if memory restored (is_deleted changed to 0) CREATE TRIGGER IF NOT EXISTS memories_fts_restore AFTER UPDATE OF is_deleted ON memories WHEN NEW.is_deleted = 0 AND OLD.is_deleted = 1 BEGIN INSERT INTO memories_fts (memory_id, content, summary) VALUES (NEW.id, NEW.content, NEW.summary); END; `); // Make embedding column nullable (no longer required) // Note: SQLite doesn't support DROP COLUMN or ALTER COLUMN, // so we just make it optional going forward // Record migration db.prepare('INSERT INTO schema_version (version, applied_at) VALUES (?, ?)').run( 3, Date.now() ); }, ]; // Apply each migration in sequence for (let i = fromVersion; i < migrations.length; i++) { const migration = migrations[i]; if (migration) { db.transaction(() => { migration(db); })(); } } } /** * Create optimized views for common queries */ export function createViews(db: DbDriver): void { // View: Active memories with entity counts db.exec(` CREATE VIEW IF NOT EXISTS v_active_memories AS SELECT m.*, COUNT(me.entity_id) as entity_count FROM memories m LEFT JOIN memory_entities me ON m.id = me.memory_id WHERE m.is_deleted = 0 AND (m.expires_at IS NULL OR m.expires_at > unixepoch() * 1000) GROUP BY m.id; `); // View: Memory provenance chain db.exec(` CREATE VIEW IF NOT EXISTS v_memory_provenance AS SELECT m.id, m.content, m.type, p.operation, p.timestamp, p.source, p.context, p.user_id FROM memories m LEFT JOIN provenance p ON m.id = p.memory_id ORDER BY m.id, p.timestamp DESC; `); } /** * Optimize database for performance */ export function optimizeDatabase(db: DbDriver): void { // Analyze tables for query optimization db.exec('ANALYZE;'); // Set performance pragmas db.pragma('journal_mode = WAL'); // Write-Ahead Logging for better concurrency db.pragma('synchronous = NORMAL'); // Balance safety and speed db.pragma('cache_size = -64000'); // 64MB cache db.pragma('temp_store = MEMORY'); // Temp tables in memory db.pragma('mmap_size = 30000000000'); // 30GB memory-mapped I/O } /** * Get database statistics */ export interface DatabaseStats { total_memories: number; active_memories: number; deleted_memories: number; expired_memories: number; total_entities: number; total_provenance_records: number; database_size_bytes: number; memory_avg_importance: number; oldest_memory_age_days: number; } export function getDatabaseStats(db: DbDriver): DatabaseStats { const now = Date.now(); const stats = { total_memories: db.prepare('SELECT COUNT(*) FROM memories').pluck().get() as number, active_memories: db .prepare('SELECT COUNT(*) FROM memories WHERE is_deleted = 0 AND (expires_at IS NULL OR expires_at > ?)') .pluck() .get(now) as number, deleted_memories: db .prepare('SELECT COUNT(*) FROM memories WHERE is_deleted = 1') .pluck() .get() as number, expired_memories: db .prepare('SELECT COUNT(*) FROM memories WHERE is_deleted = 0 AND expires_at IS NOT NULL AND expires_at <= ?') .pluck() .get(now) as number, total_entities: db.prepare('SELECT COUNT(*) FROM entities').pluck().get() as number, total_provenance_records: db.prepare('SELECT COUNT(*) FROM provenance').pluck().get() as number, database_size_bytes: db .prepare("SELECT page_count * page_size as size FROM pragma_page_count(), pragma_page_size()") .pluck() .get() as number, memory_avg_importance: (db .prepare('SELECT AVG(importance) FROM memories WHERE is_deleted = 0') .pluck() .get() as number) || 0, oldest_memory_age_days: (() => { const oldest = db .prepare('SELECT MIN(created_at) FROM memories WHERE is_deleted = 0') .pluck() .get() as number | null; return oldest ? Math.floor((now - oldest) / (1000 * 60 * 60 * 24)) : 0; })(), }; return stats; }