makemigrations - SKILL.md Agent Skill

name: makemigrations description: Create SQLite migrations for MemoryLane storage schema changes. Use when interfaces or persisted fields change in src/main/storage/index.ts, when a new column/index/table/trigger is needed, or when the user asks to add a migration similar to 0001_initial_schema.ts.

Create Storage Migration

Purpose

Keep runtime storage schema in sync with src/main/storage/index.ts by adding forward-only migrations in src/main/storage/migrations/.

When to Use

Use this skill when any persisted shape changes, especially:

StoredActivity or ActivitySummary field changes in src/main/storage/index.ts
SQL statements in StorageService change selected/inserted columns
New filters require indexes
Legacy table/data needs migration into current tables

Rules

Never edit or reorder existing migration files after they ship.
Always add a new migration with a monotonically increasing 4-digit prefix (0003_*, 0004_*, ...).
Migration must be idempotent where possible (IF EXISTS / IF NOT EXISTS).
Migration must be safe on partially migrated user data.
Register the new migration in src/main/storage/migrations/index.ts.

Workflow

Copy this checklist and execute in order:

Migration Progress
- [ ] 1) Identify schema delta from index.ts changes
- [ ] 2) Create next migration file in src/main/storage/migrations/
- [ ] 3) Implement migration.up(db) with safe SQL
- [ ] 4) Register migration in src/main/storage/migrations/index.ts
- [ ] 5) Add/update storage migration tests
- [ ] 6) Run tests and lint for touched files

1) Identify schema delta

Map TypeScript/API changes to SQLite changes:

Added persisted field -> ALTER TABLE ... ADD COLUMN (or backfill strategy)
Removed field -> usually keep column; stop reading/writing it first, remove later with explicit migration plan
Renamed field -> add new column + backfill + dual-read/write strategy if needed
New query pattern -> add index
New text search shape -> update FTS table/trigger strategy
Vector shape/dimension changes -> explicit data migration strategy

2) Create migration file

Name pattern:

File: src/main/storage/migrations/000X_descriptive_name.ts
Export: export const migration: Migration
name must match file stem exactly (example: 0003_add_window_title_index)

Template:

import Database from 'better-sqlite3'
import type { Migration } from '../migrator'

export const migration: Migration = {
  name: '000X_descriptive_name',
  up(db: Database.Database): void {
    // schema/data updates
  },
}

3) Implement safe SQL

Preferred patterns:

db.exec(...) for DDL batches
db.prepare(...).run() for parameterized DML
Guard checks using sqlite_master when needed
Wrap multi-step data migrations in db.transaction(() => { ... })()

For data moves:

Use INSERT OR IGNORE where duplicate IDs may exist
Backfill defaults for new NOT NULL columns
Drop legacy objects only after successful copy

4) Register migration

Update src/main/storage/migrations/index.ts:

Add import for the new migration
Append to migrations array at the end (preserve order)

5) Tests

Update src/main/storage/storage.test.ts (or related tests) to cover:

Fresh DB applies all migrations successfully
Existing DB at previous schema upgrades correctly
Backfilled/default values are correct
Expected indexes/tables/triggers exist after migration

6) Validate

Run relevant checks:

npm test -- src/main/storage/storage.test.ts
npm run lint

If tests are broad or expensive, run targeted tests first, then full suite when requested.

Examples in Repo

Baseline schema setup: src/main/storage/migrations/0001_initial_schema.ts
Legacy data migration + cleanup: src/main/storage/migrations/0002_migrate_context_events.ts