Evolving The Data Model

Evolving the Trilium data model

Migrations are not dated .sql files — start here

The intuitive guess — dated SQL files under apps/server plus a matching schema.sql there — is wrong on both counts. Neither of these paths exists:

Verified absent: Glob apps/server/src/migrations/** and Glob apps/server/src/assets/db/schema.sql both return nothing.

Ground truth:

• Migrations — packages/trilium-core/src/migrations/migrations.ts. One MIGRATIONS array of { version, sql, ignoreErrors? } (SQL) or { version, module } (JS) objects, kept in DESCENDING version order (newest first). Top entry today is version: 240 (migrations.ts:15).
• Schema for fresh installs — packages/trilium-core/src/assets/schema.sql.
• dbVersion is auto-derived, never hand-bumped: getMaxMigrationVersion() returns MIGRATIONS[0].version (migrations.ts:6-8) → appInfo.dbVersion (app_info.ts:11) → isDbUpToDate() compares dbVersion >= appInfo.dbVersion (migration.ts:120-130, the comparison at :123).

The two un-guarded hazards (most data-model bugs are one of these)

migrations.spec.ts already asserts unique + descending versions (migrations.spec.ts:5-18), so a misordered/duplicate version IS caught at CI if you run the tests. The genuinely silent traps are:

1. schema.sql parity. Fresh installs run sql.executeScript(schema) and never replay migrations (sql_init.ts:154, also :288 for sync setup). A column added only via ALTER TABLE in a migration is MISSING on every freshly-created DB unless you hand-mirror it into schema.sql. No test compares fresh-vs-migrated schema.
2. becca_loader positional misalignment. notes, branches, attributes load via sql.getRawRows(...) (positional arrays) fed to new BAttribute().update(row), whose destructure order must match the SELECT column order exactly (becca_loader.ts:53 ↔ battribute.ts:57). Add a column to one and not the other (or in the wrong position) and every loaded entity silently gets shifted field values — no compile error, no test failure.

A third, deliberate-only hazard: changing hashedProperties breaks cross-instance sync — see below.

Decision: what are you doing?

Recipe: add a migration (SQL)

1. Append a new object at the TOP of MIGRATIONS with version: <current MIGRATIONS[0].version + 1> (so today, 241).
2. For ALTER TABLE ... ADD COLUMN, set ignoreErrors: true (re-running over an already-patched DB must not halt the whole transaction — see 238/236, the only two entries that set it, and the ignoreErrors doc at migrations.ts:370-371).
3. Mirror the column/table change into schema.sql so fresh installs match.
4. No appInfo bump — dbVersion reads MIGRATIONS[0] automatically.

// migrations.ts — TOP of the MIGRATIONS array
{
    version: 241,
    sql: /*sql*/`
        ALTER TABLE notes ADD COLUMN color TEXT DEFAULT '' NOT NULL;
    `,
    ignoreErrors: true
},

Recipe: add a migration (JS/TS)

Use this only when the transform needs becca/note APIs (content, attachments, relations). Pure SQL? Stay in SQL.

1. Create packages/trilium-core/src/migrations/0NNN__<desc>.ts with a default-exported function.
2. Wrap all becca/note work in getContext().init(() => { becca_loader.load(); ... }) — without CLS context note.save()/setContent() throw. Models: 0233__migrate_geo_map_to_collection.ts:6-8, 0234__migrate_ai_chat_to_code.ts:5-7.
3. Reference it from MIGRATIONS with { version: N, module: async () => import("./0NNN__<desc>.js") } — note the .js extension (ESM output), exactly like migrations.ts:73-74.
4. Add a .spec.ts (see the harness below).

The runner preloads JS modules (prepareMigrations, migration.ts:80-101) then runs everything inside one sql.transactional (migration.ts:46); a failure without ignoreErrors crashes the app so the user can stay on the old version.

Spec harness — don't reinvent it

Two proven styles, both already in-tree:

• Per-migration unit test — fixture test/fixtures/document.db, insert rows, run the migration fn, assert post-state. Model: migrations/0233__migrate_geo_map_to_collection.spec.ts.
• End-to-end — migration.migrateIfNecessary() against test/fixtures/document_v214.db, assert a post-migration count. Model: services/migration.spec.ts (asserts SELECT count(*) FROM blobs is 118).

Three harness traps, each load-bearing (copy assets/migration-spec.template.ts):

• Resolve getSql() inside beforeEach, not at describe-collection time — describe callbacks run before the suite's initializeCore beforeAll, so capturing it eagerly throws "SQL not initialized" (0233__*.spec.ts:33-39 has the explanatory comment).
• sql.rebuildFromBuffer(readFileSync(fixture)) per test to avoid cross-test leakage (0233__*.spec.ts:43-44).
• Call becca_loader.load() after raw INSERTs and again after running the migration, all inside cls.getContext().init(...), so becca reflects the new DB state (0233__*.spec.ts:112 and :130).

Run a single migration spec:

pnpm --filter server test packages/trilium-core/src/migrations/0233__migrate_geo_map_to_collection.spec.ts

For writing the assertions themselves, see the writing-unit-tests skill (real-DB vs mocked-becca, the cls.init pattern, single-file runner footguns) and analyzing-coverage for chasing the migration's uncovered branches.

Reference map