sqlite migration 1.3.0 (project_id) never runs — 'already applied' check uses wrong column

[ViolinKaine]

Summary (Co-Authored by Claude code)

The 1.3.0 sqlite migration ("Project-level isolation support", which adds a
project_id column to memories/vectors/waypoints/temporal_facts) never
actually runs on any database that already has the 1.2.0 migration applied —
which is every database that's been through the 1.2.0 migration at all, since
run_sqlite_migration() uses the same "already applied" check for every migration
in the list, and that check is only correct for 1.2.0.

Root Cause

packages/openmemory-js/src/core/migrate.ts, in run_sqlite_migration():

const has_user_id = await check_column_exists_sqlite(db, "memories", "user_id");
if (has_user_id) {
    log(`Migration ${m.version} already applied (user_id exists), skipping`);
    await set_db_version_sqlite(db, m.version);
    return;
}

This function is called once per migration in the migrations array, for every
migration version, not just 1.2.0. But the presence-check is hardcoded to
user_id — the column 1.2.0 itself adds. So:

• Running 1.2.0 on a fresh db: user_id doesn't exist yet → migration runs → adds
  user_id. Correct.
• Running 1.3.0 right after: user_id now exists (from 1.2.0) → check passes →
  1.3.0 is marked "already applied" and skipped → its actual statements
  (ALTER TABLE memories ADD COLUMN project_id TEXT, etc.) never run.

The (presumably intended) equivalent postgres path has the same shape — worth
checking whether it has an analogous per-migration check or a real version-gated
one (I didn't fully trace that path, sqlite is what I hit).

Reproducer

1. Start with any sqlite db that has run migration 1.2.0 (i.e., any db that's ever
   had a user_id column added — this is essentially every db created before
   project-level isolation was added, or any db migrated incrementally).
2. Run migrations (however they get triggered — I called
   require('./dist/core/migrate.js').run_migrations() directly against a built
   image).
3. Log output:

   [MIGRATE] Running migration: 1.3.0 - Project-level isolation support
   [MIGRATE] Migration 1.3.0 already applied (user_id exists), skipping
   

4. Confirm the column is actually missing:

   sqlite> ALTER TABLE memories ...
   -- or just try to insert with project_id and get:
   SQLITE_ERROR: table memories has no column named project_id
   

5. Any code path that writes/reads project_id (e.g. /memory/add when
   project-scoping is used) fails with that SQL error.

Suggested Fix

run_sqlite_migration() needs a per-migration "already applied" check, not a
shared one hardcoded to user_id. Simplest options:

• Check for the specific column each migration actually adds (e.g. for 1.3.0,
  check project_id existence instead of user_id), possibly by adding a
  check_column field to each Migration object in the migrations array.
• Or rely solely on the stored db version (get_db_version_sqlite/
  set_db_version_sqlite) as the source of truth and drop the column-existence
  shortcut entirely, if the version tracking is reliable on its own.

Workaround (applied locally, not a real fix)

Manually ran the missing statements against the affected db:

ALTER TABLE memories ADD COLUMN project_id TEXT;
CREATE INDEX IF NOT EXISTS idx_memories_project ON memories(project_id);
ALTER TABLE vectors ADD COLUMN project_id TEXT;
CREATE INDEX IF NOT EXISTS idx_vectors_project ON vectors(project_id);
ALTER TABLE waypoints ADD COLUMN project_id TEXT;
CREATE INDEX IF NOT EXISTS idx_waypoints_project ON waypoints(project_id);
ALTER TABLE temporal_facts ADD COLUMN project_id TEXT;
CREATE INDEX IF NOT EXISTS idx_temporal_project ON temporal_facts(project_id);

Confirmed store/query work correctly afterward.

Environment

• openmemory-js v1.3.3 (also present on current main, same migration code)
• SQLite metadata backend
• Docker deployment via the repo's own docker-compose.yml