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

> Extend the Claude-Mem database schema safely with new migrations. Learn how to create, register, and record migration versions for your project at thedotmack/claude-mem.

- Repository: [Alex Newman/claude-mem](https://github.com/thedotmack/claude-mem)
- Tags: how-to-guide
- Published: 2026-02-16

---

**To extend the Claude-Mem database schema, create a new private method in the `MigrationRunner` class at [`src/services/sqlite/migrations/runner.ts`](https://github.com/thedotmack/claude-mem/blob/main/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`](https://github.com/thedotmack/claude-mem/blob/main/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`](https://github.com/thedotmack/claude-mem/blob/main/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

```typescript
// 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

```typescript
// 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

```typescript
// 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`](https://github.com/thedotmack/claude-mem/blob/main/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`](https://github.com/thedotmack/claude-mem/blob/main/session_store.test.ts) in the codebase.