Repository Public
1<!--2SP Notes Core3File: docs/MIGRATION-V2.md4Author: Simon-Pierre Boucher5Contact: contact@spboucher.ai6-->7# Local database: migration from schema v1 to v289Schema v2 ships with SPNotesCore 2.0. It is the **first migration ever run on users' files** (1.x created v1 and never changed10it). This page says what it does, how it is protected, how it was tested, and what the apps must do.1112## 1. What changes1314| | v1 → v2 |15|---|---|16| v1 tables (`pages`, `page_bases`, `tags`, `page_tags`, `pages_fts`, `attachments`, `ops`, `conflicts`, `local_versions`, `reminders`, `meta`) | **untouched**: no column added, no index changed, no row rewritten |17| New tables | `entities`, `entity_ops`, `ops_parked`, `canvases`, `events`, `kv_cache`, `sync_cursors` (with their indexes), all empty |18| `PRAGMA user_version` | 1 → 2 |1920The step is `Schema.v2` (`Sources/SPNotesStore/Schema.swift`). It is additive only, so it cannot lose data by construction;21the tests below check it anyway. Roles of the tables: see `STORE_API.md` §12.2223## 2. How it runs24251. `NotesStore.init(url:)` / `NotesStore.open(url:)` opens the file and reads `user_version`.262. **Backup.** When the file holds data of an older version (0 < version < 2) a consistent copy is written next to it as27 `<file>.backup-v<old version>` (for the apps: `notes.sqlite.backup-v1`) with `VACUUM INTO` — one read transaction, so the28 copy includes what is still in the write-ahead log and is itself a complete, healthy v1 database. It is written to29 `…backup-v1.partial` and renamed, so a half-written copy never has the final name. If a backup of that version already30 exists (an earlier attempt failed after it), it is kept: it is the pristine one. If the copy cannot be written31 (`StoreError.backup` → `StoreOpenError.backupFailed`), **the migration is not attempted** and the file is unchanged.323. **Migration.** The DDL and the version bump run in one `BEGIN IMMEDIATE` transaction. Any error rolls everything back: the33 file is exactly the v1 file it was. Two processes opening at the same time are serialized by the write lock; the second one34 sees version 2 and does nothing.354. **Removal of the backup.** A later open that finds the file already at the current version, passes `PRAGMA quick_check`, and36 finds the backup older than `backupRetention` (600 s by default) deletes it. The retention exists because a widget or a share37 extension may open the file seconds after the app migrated it: that is not "the next launch". A read-only connection never38 deletes it. `openReport` says what happened (`migratedFrom`, `backupURL`, `removedBackups`).3940A read-only connection (widget) neither migrates nor backs up: on a v1 file it fails with `StoreError.schema` /41`StoreOpenError.outdatedReadOnly` until the app has been opened once — as in 1.x for any future version.4243## 3. Failure modes and what the user sees4445| Situation | Result | File |46|---|---|---|47| Disk full while writing the backup | `backupFailed` (transient) | unchanged, v1 |48| Error inside the migration (disk full, I/O, unexpected object) | `migrationFailed(from: 1, to: 2, reason, backupURL)` | rolled back to v1; backup kept |49| File written by a newer app | `newerSchema(found:supported:)` | unchanged; no backup made |50| Damaged file | `corrupt` | unchanged |51| Locked by another process for more than 8 s | `locked` (transient) | unchanged |5253`StoreOpenError.userMessage` is the French text to show. **Apps must show it in a blocking screen and must not fall back to54`NotesStore.inMemory()`** (the macOS 1.0.2 behaviour: the user sees an empty library and loses what is typed). Adopt55`NotesStore.open(url:)`, or keep `init(url:)` and map the thrown error with `NotesStore.openError(for:url:)`.5657## 4. Going back5859- **A 1.x app cannot open a v2 file**: it refuses any file newer than its own schema (by design, since 1.0). Do not ship a build60 with Core 2.0 to users who may go back to a 1.x build without knowing this; TestFlight users who downgrade must restore.61- **Hand-written inverse.** `Schema.v2Down` (tested) is:6263 ```sql64 BEGIN IMMEDIATE;65 DROP TABLE IF EXISTS sync_cursors;66 DROP TABLE IF EXISTS kv_cache;67 DROP TABLE IF EXISTS events;68 DROP TABLE IF EXISTS canvases;69 DROP TABLE IF EXISTS ops_parked;70 DROP TABLE IF EXISTS entity_ops;71 DROP TABLE IF EXISTS entities;72 PRAGMA user_version = 1;73 COMMIT;74 ```7576 It restores exactly the v1 schema and leaves every v1 row as it is. It **discards** what only v2 holds: unsent entity77 operations, offline canvas edits, cached events and read models, parked-op markers (the parked ops themselves stay in `ops`,78 where a 1.x build would then drop them — the defect 2.0 fixes). It is a support tool; no app calls it.79- **Restoring the backup**: quit the app, replace `notes.sqlite` by `notes.sqlite.backup-v1` (delete `notes.sqlite-wal` and80 `notes.sqlite-shm`), reopen with the 1.x build. Everything written after the migration is lost, except what was synchronized.8182## 5. How it was tested8384`Tests/SPNotesStoreTests/Fixtures/v1.sqlite` is a real v1 database written by the shipped 1.0 code path (commit `267a673`, before85any v2 code existed; generator kept in `V1FixtureGenerator.swift`): 6 pages (synced, dirty, never pushed, trashed, in conflict),863 base copies, tags, 2 attachments, 3 queued ops including one of a kind no build knows, a conflict copy, a local version, a87reminder, meta and the sync cursor. `MigrationV2Tests` (17 tests) checks on copies of it:8889- every row of every v1 table and of the full-text index is identical before and after; typed reads through the 2.0 API agree;90- the seven new tables exist and are empty; a fresh database and a migrated one have the same schema;91- opening three times, and running the migrator again, changes neither schema nor rows (idempotent);92- a failure after the whole v2 DDL plus writes into old and new tables, and a failure in the middle of a DDL script, both leave93 version 1, the v1 schema and every row intact; the real migration then succeeds on the same file;94- a real failure (a conflicting table) is reported as `migrationFailed` with the backup, and leaves a v1 file;95- the backup is a healthy v1 database with the pre-migration rows, includes writes that were only in the WAL, is kept by an open96 within the retention and by read-only opens, and is removed by a later healthy open;97- newer file, damaged file, unreachable location, read-only connection: typed error, file untouched;98- `v2Down` restores the exact v1 schema and rows; the file migrates again afterwards.99100Not tested: a real power loss or a full disk during the migration (the transaction guarantees are SQLite's), and the migration101of a multi-gigabyte database (the step itself only creates empty tables; the backup copy is proportional to the file size).102