Writing a migration

How migrations run

• Files live in src/db/migrations/, named NNNN-short-description.sql (0024-… next). They are plain SQL; there is no JavaScript migration step.
• src/db/migrate.js applies every file not yet listed in schema_migrations, in filename order, each in its own transaction together with its schema_migrations row. A failing file rolls back completely and the server does not start.
• src/db/index.js opens the database with journal_mode = WAL and foreign_keys = ON before running migrations. Inside the transaction PRAGMA foreign_keys cannot be changed — which is what a rebuild migration is for (rule 6).
• Each applied file’s sha256 (line endings normalised, so a Windows checkout agrees with the server) is stored in schema_migrations.checksum (migration 0020). On every boot the runner compares: a file changed after it was applied is a warning in development and a refusal to start in production, with the file named. A file renamed without changing its text is recognised by its checksum and its record follows the new name instead of running twice. scripts/restore.js uses the same records, so renaming a file does not make older backups “newer schema”.
• Migrations run at every startup of every installation: the first one, a school that updates from a release a year old, a brand-new empty database, and every test (createTestDb() in test/helpers/buildTestApp.js runs them on :memory:).

Rules

1. Forward only. There are no down migrations. To undo something, add a new file that corrects it. Never edit a file that has been released — installations that already applied it will never see the change, and the ones that did not will diverge. Since 0020 the runner enforces this: production refuses to start on a changed applied file. (0006, 0009 and the since-removed 0013 were edited one last time in the release that added the checksums — every installation records their final text on its first boot with 0020, so no warning follows.) Removing a file is allowed once every installation that needed it has applied it: the runner ignores an applied file that is gone (test/dbMigrateMissingFile.test.js). Never renumber to close the gap — that is how 0013 left the tree.

2. One transaction, one purpose. A file is atomic; keep it to one logical change so a failure is easy to reason about. Put a comment header on top saying what and why (see any existing migration).

3. Prefer additive changes. ALTER TABLE … ADD COLUMN, new tables, new indexes. A rollback of the application image keeps the newer database (see release-process.md), so an older release must still work against it: do not drop or rename a column that a released version still reads in the same release that stops reading it.

4. Work on any database. A fresh database has no pages, no settings and no users; an old one may have anything. Use INSERT OR IGNORE, WHERE NOT EXISTS, COALESCE, and never assume a row exists.

5. No installation-specific seeds. Nothing in a migration may write one school’s data into every school’s database, and no migration carries a particular installation’s content at all: a school’s texts belong in its own database, written through /admin or the setup wizard. 0006 and 0009 still hold the older guard for the first installation’s legacy content (the page ustanovchi-doc) — frozen with their text, not a pattern to copy. A generic seed, one every school gets, fills only what is absent (AND NOT EXISTS (…)). test/neutralTemplate.test.js checks a fresh database stays empty.

6. Rebuilding a table. SQLite cannot drop a column constraint or widen a CHECK in place, so a table is rebuilt: CREATE TABLE x_new → INSERT INTO x_new SELECT … → DROP TABLE x → ALTER TABLE x_new RENAME TO x → recreate indexes and triggers. 0019-pages-rebuild.sql is the worked example; 0022-library-groups.sql rebuilds pages once more (the page_group foreign key) and follows it step by step.

   Referenced tables need -- @rebuild. With foreign_keys = ON, DROP TABLE x is an implicit DELETE FROM x that fires every child’s ON DELETE (cascading posts, albums, people and menu items away) or fails on RESTRICT. pages, files, users, news and authors are all referenced. A file whose first non-empty line is -- @rebuild is run by the runner the way SQLite documents (the “twelve-step” procedure, https://www.sqlite.org/lang_altertable.html#otheralter): PRAGMA foreign_keys = OFF outside any transaction, the whole file and its schema_migrations row in one transaction ending with PRAGMA foreign_key_check — a violation the rebuild introduced rolls everything back — and foreign_keys back on in a finally. The log says mode: 'rebuild'. Without the directive the same file fails exactly as before. An unreferenced table (0008 rebuilt news when nothing referenced it yet) needs no directive, but it does no harm.

   Checklist for a rebuild file:

   • -- @rebuild as the very first line; the reason in the comment header below it.
   • The new table’s columns in the same order, types and defaults as PRAGMA table_info(x) on a migrated database (every ADD COLUMN since the table was created — read all later migrations), so SELECT * and INSERT … SELECT elsewhere do not shift. Keep every constraint you are not deliberately changing, and every REFERENCES … ON DELETE ….
   • Copy with an explicit column list and keep the ids — every foreign key and every search_index rowid points at them.
   • Recreate every index on the table (SELECT name, sql FROM sqlite_master WHERE tbl_name = 'x' AND type = 'index' on a migrated database; autoindexes come back with their UNIQUE/PRIMARY KEY constraints).
   • Search triggers: pages, news, documents and page_files carry three search_<table>_{ai,au,ad} triggers from 0017-search-index.sql, and files carries media_search_{ai,au,ad} from 0026-files-alt-text.sql; DROP TABLE takes them silently. Recreate them verbatim after the INSERT … SELECT (created before it, the insert trigger would index every row a second time). DROP TABLE does not fire the delete trigger, so with the ids kept the existing index rows still match; a guarded INSERT … WHERE NOT EXISTS for rows without one is cheap insurance (see 0019).
   • Other triggers or views that mention the table (SELECT … FROM sqlite_master WHERE sql LIKE '%x%').
   • test/dbRebuild.test.js: the trigger count (12 search_*), the explicit index list, foreign_key_check, row counts before/after on test/helpers/populatedDb.js, and the length() CHECKs (review D-08) — extend them for the table you rebuilt. When a table is rebuilt anyway, a limit the forms enforce may become a CHECK — but only after checking that no existing row breaks it, or the migration fails on that installation.

7. Store what the code expects. Dates as ISO YYYY-MM-DD text, timestamps as strftime('%Y-%m-%dT%H:%M:%fZ','now'), booleans as 0/1 with a CHECK. Put limits the forms enforce into CHECK constraints too (see albums, authors, menu_items).

8. Soft-deletable content gets deleted_at TEXT and deleted_by INTEGER REFERENCES users(id) ON DELETE SET NULL, and every read filters deleted_at IS NULL. Wire it into src/admin/lib/trash.js and purgeTrash.js.

9. A new column that points at files must be added to src/lib/fileUsages.js — to fileUsages() and to REFERENCED_IDS_SQL (the set collectFileReferences() builds) — or the media library will show the file as unused and the nightly sweep will bin it. Give it an index (partial, WHERE col IS NOT NULL, if nullable), as 0021 did for the others: SQLite checks every such column on each DELETE FROM files.

10. Settings are not migrated. A new setting key needs no migration: an absent key means the default, defined in code (src/config/siteSettings.js, theme.js, homeBlocks.js, backupSettings.js). Write a settings row in a migration only to move existing data.

Backups need nothing: the change hash and the manifest enumerate tables from sqlite_master.

Checklist

• Next free number, descriptive name, English comment header with the reason.
• Runs on an empty database and on a copy of a real one (restore a backup into a scratch DATA_DIR and start the app against it; in tests, buildLegacyDb() from test/helpers/populatedDb.js is a database at 0018 with rows in every table).
• No installation-specific values unguarded.
• Rebuilt tables: -- @rebuild if anything references them; the rule 6 checklist done.
• The file is final before it ships: once applied anywhere, its checksum is recorded.
• A test in test/db.test.js (or next to the feature) that asserts the new shape or the data move on a fresh in-memory database.
• CLAUDE.md “Data model” and data-model.md updated.