How to Extend the Claude-Mem Database Schema with New Migrations

To extend the Claude-Mem database schema, create a new private method in the MigrationRunner class at src/services/sqlite/migrations/runner.ts, register it in runAllMigrations(), and record the version in the schema_versions table to ensure idempotent execution.

Claude-Mem is an open-source memory system for Claude Desktop that persists conversation context in a local SQLite database. When you need to add new tables, columns, or indexes to support evolving features, the project provides a lightweight migration system that guarantees safe, ordered schema changes without data loss.

Understanding the Migration Architecture

The migration system centers on two critical components that handle schema versioning and execution.

The MigrationRunner Class

Located in src/services/sqlite/migrations/runner.ts, the MigrationRunner class orchestrates all database schema changes. It exposes runAllMigrations(), which calls individual migration methods in strict sequence. Each migration method follows a defensive pattern: check schema_versions for prior execution, inspect current table structure via PRAGMA table_info(), apply the ALTER or CREATE statements, and insert the version record.

Schema Version Tracking

Claude-Mem maintains a schema_versions table that records every applied migration. Before executing any change, the MigrationRunner queries this table to ensure idempotence—if the version number exists, the migration skips execution. This design protects against duplicate column creation errors during application restarts.

Step-by-Step Guide to Adding New Migrations

Follow this workflow when you need to extend the Claude-Mem database schema with structural changes.

1. Choose a Version Number

Identify the next integer after the highest existing migration. Inspect runAllMigrations() in src/services/sqlite/migrations/runner.ts to find the current maximum version (for example, if the last migration is version 20, use 21).

2. Implement the Migration Method

Add a new private method to the MigrationRunner class. The method must:

• Define a constant version number
• Query schema_versions to check for prior execution
• Use PRAGMA table_info() to verify the column or table does not already exist
• Execute the SQL statements
• Record the version in schema_versions

3. Register the Migration in Execution Order

Insert the method call into runAllMigrations() at the appropriate position. Place it after any migrations it depends on to maintain referential integrity.

4. Write Unit Tests

Create a test file in tests/services/sqlite/ that instantiates an in-memory SQLite database, runs MigrationRunner, and asserts that your new schema elements exist. Query PRAGMA table_info() or check index existence to verify the migration applied correctly.

Practical Example: Adding a New Column

Below is a complete implementation adding a source_url column to the observations table with an accompanying index.

Migration Method Implementation

// src/services/sqlite/migrations/runner.ts
private addSourceUrlToObservations(): void {
  // Migration version 21 – add `source_url` column
  const version = 21;
  const alreadyApplied = this.db.prepare('SELECT version FROM schema_versions WHERE version = ?')
    .get(version) as SchemaVersion | undefined;
  if (alreadyApplied) return;

  // Verify column does not already exist (defensive)
  const columns = this.db.query('PRAGMA table_info(observations)').all() as TableColumnInfo[];
  const hasSourceUrl = columns.some(col => col.name === 'source_url');

  if (!hasSourceUrl) {
    this.db.run('ALTER TABLE observations ADD COLUMN source_url TEXT');
    this.db.run('CREATE INDEX IF NOT EXISTS idx_observations_source_url ON observations(source_url)');
    logger.debug('DB', 'Added source_url column & index to observations');
  }

  // Record the migration so it won't run again
  this.db.prepare('INSERT OR IGNORE INTO schema_versions (version, applied_at) VALUES (?, ?)')
    .run(version, new Date().toISOString());
}

Registration in Execution Order

// src/services/sqlite/migrations/runner.ts
runAllMigrations(): void {
  this.initializeSchema();
  this.ensureWorkerPortColumn();
  this.ensurePromptTrackingColumns();
  this.removeSessionSummariesUniqueConstraint();
  this.addObservationHierarchicalFields();
  this.makeObservationsTextNullable();
  this.createUserPromptsTable();
  this.ensureDiscoveryTokensColumn();
  this.createPendingMessagesTable();
  this.renameSessionIdColumns();
  this.repairSessionIdColumnRename();
  this.addFailedAtEpochColumn();

  // <-- New migration goes here
  this.addSourceUrlToObservations();
}

Unit Test Verification

// tests/services/sqlite/migration-add-source-url.test.ts
import { Database } from 'bun:sqlite';
import { MigrationRunner } from '../../src/services/sqlite/migrations/runner.js';

test('migration 21 adds source_url column', () => {
  const db = new Database(':memory:');
  const runner = new MigrationRunner(db);
  runner.runAllMigrations();

  const columns = db.query('PRAGMA table_info(observations)').all();
  const hasSourceUrl = columns.some((c: any) => c.name === 'source_url');
  expect(hasSourceUrl).toBe(true);
});

Summary

• Extend the Claude-Mem database schema by adding private methods to the MigrationRunner class in src/services/sqlite/migrations/runner.ts.
• Always check schema_versions before applying changes to ensure idempotent execution.
• Register new migrations in runAllMigrations() in the correct sequential order.
• Verify schema changes with unit tests using in-memory SQLite databases in tests/services/sqlite/.
• Reference existing migrations like ensureWorkerPortColumn() and addFailedAtEpochColumn() as templates for defensive schema inspection.

Frequently Asked Questions

How does Claude-Mem prevent duplicate migration execution?

The MigrationRunner queries the schema_versions table before running any migration. If the version number already exists in the table, the method returns early without executing SQL statements. This idempotence pattern protects against errors when the application restarts or if migrations run multiple times.

Can I roll back a migration in Claude-Mem?

Claude-Mem's migration system implements up migrations only and does not provide automatic rollback functionality. While SQLite supports limited DROP COLUMN operations in newer versions, the codebase focuses on forward-only migrations. If you need to reverse a change, you would create a new migration that removes the column or table, effectively moving forward to a corrected schema state.

Where should I place unit tests for new migrations?

Create test files in the tests/services/sqlite/ directory following the naming convention migration-[description].test.ts. These tests should instantiate an in-memory SQLite database using new Database(':memory:'), run the MigrationRunner, and assert schema existence via PRAGMA table_info() queries or index inspection. This pattern mirrors existing tests like session_store.test.ts in the codebase.

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