Repository Public
1# SP Note Next Generation — migration plan23Principle: every step is additive, tested before it runs on real data, and reversible. Production stays up;4nothing is rebuilt from scratch; no existing document is rewritten by a migration.56## 1. Server database (PostgreSQL, `note_spboucher` on M4M64c)78| Migration | Adds | Destroys | Down file | Tested |9|---|---|---|---|---|10| `0003_next_foundation` | `rev` + triggers + tombstones on tasks, databases, properties, views, rows, edges, references, canvases, concepts; `knowledge_events`; graph 2.0 columns; `concepts`, `page_concepts`, `knowledge_insights`; `canvases`; `workspace_id` on database children | nothing | yes | up → down → up on a copy: schema dump after down identical to before (1 251 lines), rows intact |11| `0004_next_ai` | provider columns (`kind`, `base_url`, `key_env`, `label`), `ai_models.creator/route`, `ai_synopses`, `inbox_suggestions`, `audio_transcripts`, `ai_property_runs` | nothing | yes | unit suites on a migrated scratch database |12| `0005_next_productivity` | `database_row_templates`, `database_row_ops`, index `db_rows_db_pos_idx` | nothing | yes | unit suites on a migrated scratch database |1314Back-fill done by 0003: one `page.created` event per existing page and one `page.version` per existing15version, all flagged `backfilled` (shown as "reconstructed" in the timeline). No other history is invented.1617Behaviour changes on existing data (not schema):18- List items receive a block id the first time a page is indexed or saved (ids are stable across saves).19- Purging a page now also deletes its attachments (unless used elsewhere) and the sub-pages trashed with it;20 live or separately trashed children are kept and moved to the top level.21- Parent cycles are refused (409); malformed ids answer 400 instead of 500; soft-deleted files are no longer served.2223### Deployment sequence (production)241. `scripts/backup.sh` (encrypted, copied off-node) and record the current commit (`~/note-pre-next-commit`).252. Restore drill of that backup into a throw-away database (`scripts/restore.sh --latest --verify-only`).263. Rehearsal: restore the same backup into a scratch database, run migrations 0003–0005 on it, run the27 read-only checks (row counts per table before/after, a full `/api/sync` walk, health endpoint).284. Build the release in a staging directory (never in `~/apps`), then fast-forward `main`, `pnpm db:migrate`,29 swap the build, `pm2 restart note-web note-worker`, `scripts/smoke.sh https://note.spboucher.ai`.305. Rollback (if any check fails): `pm2 stop`, apply `0005`, `0004`, `0003` down files in that order, check out31 the recorded commit and its build, restart. Data written by 3.0-only features (events, canvases,32 typed relations) is lost by the down files; pages, attachments, tasks and databases are not touched.3334Compatibility window: the shipped native apps 1.x keep working against the 3.0 server (the 2.x answer of35`/api/sync` is byte-compatible without `kinds`; they preserve unknown nodes). A voice capture made on the36web appears in a 1.x native app as an "unknown block" card until that app is updated.3738## 2. Native local store (SQLite, SPNotesCore schema v1 → v2)3940- First migration ever run on shipped data. Purely additive (7 new tables, no v1 table altered),41 transactional, preceded by a backup copy of the database file (`VACUUM INTO`, includes WAL content).42- Tested on a real v1 file written by the shipped 1.0 code: every row and the FTS index identical after43 migration; reopening is idempotent; a failure mid-way rolls back to an intact v1.44- A 1.x build refuses a v2 file: downgrade = restore the `.backup-v1` file (kept 30 days on macOS).45- If the store cannot be opened, the apps now show a blocking error instead of continuing on an in-memory46 store (1.x could silently lose what was typed in that state).47- First sync of a 2.0 app against a 3.0 server re-reads the whole feed once (the cursor is tied to the set48 of entity kinds).4950## 3. Clients5152- Web: one deployment; the editor now refuses to autosave a document it cannot fully parse (read-only mode),53 so an older cached client can no longer blank a page that contains newer blocks.54- Web offline replica: IndexedDB `note-local` v1 → v2 (adds the replica and the mutation journal; existing55 drafts and the capture outbox are kept). Wiped on lock/sign-out as before.56- macOS 1.0.2 → 2.0.0 and iOS 1.0.0 → 2.0.0: in-place update, same bundle ids, same passkeys and sessions.57 macOS moves the session token to the data-protection keychain when the signed build has the access group58 (read old → copy → verify → delete old, falling back to the old item).5960## 4. Order6162Server first (it serves both 1.x and 2.0 clients), then the native apps. Never the reverse: a 2.0 app against63a 2.x server behaves like 1.0 (features are gated on `/api/meta`), but nothing new is available.64