feat: per-device sync logs, with rollback and upgrade across schema versions

Start with the existing sync and schema migration code, especially the BackupV2 handling, SchemaMigration, sync_state, and the referenced #1499 rollback work; the issue does not name source file paths. Then map the current sync flow and migration tests against the proposed logs, version rules, and rollback behavior. Done means implementing and testing this cross-cutting design, including concurrent sync, upgrade re-reads, rollback, and the listed failure cases.

Problem

• Every sync is a full snapshot. Each edit uploads and downloads the whole BackupV2.
• Concurrent uploads overwrite each other. WebDAV, iCloud and Gist have no locking; the later upload replaces the earlier one until the next merge recovers it.
• The sync format is tied to the schema. BackupV2.formatVer is SchemaVersion.current, so any schema step, even an added column, makes an older build refuse the remote (SchemaTooNewException) and stop syncing. Uploading from it would be worse: it re-uploads the whole snapshot without the fields it does not know.
• The local store cannot go back (#1499): data written by a newer build shows SchemaTooNewPage, and nothing older can import its copy.

tombstone, sync_state ("this device's id and how far it has read each peer's log") and per-row updated_at exist since v5, but no sync code uses sync_state.

Proposal

One design for sync, rollback and upgrade:

1. Each device appends its own changes to its own log on the remote, and reads everyone else's.
2. Every log entry and schema step says which reader can understand it, so an older build reads what it can and keeps its place at what it cannot.
3. #1499's rollback, built on the same version information.

Design

Remote layout

sync/<deviceId>/snapshot-<seq>
sync/<deviceId>/<seq>.log

• One writer per directory. A device writes only its own directory, so no upload overwrites another device's.
• Upload: rows changed since the last upload and tombstones, each entry carrying only the changed fields, the row's clock and the entry's minReader. Written under a temporary name, then renamed.
• Download: list peers' directories, read logs after the cursor kept in sync_state. A gap in seq stops reading that peer until the next sync, never skipped (WebDAV partial writes, iCloud listing lag).
• Compaction: a device periodically writes a snapshot, then deletes its older logs. A snapshot holds the merged state, not only the device's own changes, together with the cursor it was taken at for every peer. A reader whose next log is gone reads that peer's snapshot instead. A new device reads every peer's latest snapshot, then the logs after it.
• Tombstones are pruned once every known peer's cursor has passed them. A peer not seen for N days is no longer waited for.
• Every file is encrypted with the sync password and carries its key version. A password change re-encrypts the device's own files; peers holding the old key ask for the new one.

Retention

Nothing is deleted by age alone: a device not seen for a year may hold changes that exist nowhere else.

What	Deleted when
A device's own logs	it has written a snapshot covering them
A device's own snapshots	all but the latest two
A gone device's directory	it has not written for a year (configurable) and a live device's snapshot has read it to its last seq
Tombstones	every live device has read them

Backups the user exports by hand are outside sync/ and never deleted automatically.

Intermediate states

State	Handling
A local edit saved, not yet queued for upload	the edit and its outbox row are written in one transaction
Killed mid-upload	a seq is committed locally only after the upload succeeds; the next run uploads the same seq again (idempotent). A device that never returns leaves a gap that readers stop waiting on once a later snapshot of that device covers it
One change spanning rows (a server and its server_pve)	a log file is the unit: a reader applies all of it or none
Killed mid-compaction	the snapshot is written (temporary name, then rename) before any log is deleted
Killed mid-apply of remote logs	a batch and the cursor it advances are one transaction
Killed mid-rollback	rollback runs on a copy; the swap is a rename after the copy opens

Storage limits

The data is small (servers, snippets, keys, settings); a snapshot is about the size of today's BackupV2.

• Compaction thresholds: a device compacts when its logs exceed a count or their total exceeds its last snapshot's size.
• Gist: the API truncates file contents over 1 MB and lists at most 300 files, so compaction is more aggressive there. A truncated file is fetched in full, never parsed as it came.
• Quota errors (WebDAV 507, iCloud full): uploads stop and the user is told; the outbox keeps every change locally.
• Settings shows how much the sync directory uses.

Merge

• Clock: a hybrid logical clock, not wall time. A device whose clock runs ahead would otherwise win every conflict.
• Last writer wins per field group, tie broken by deviceId, so every device picks the same winner. Fields that depend on each other (for example ssh_enabled / monitor_enabled under the "reached somehow" CHECK) form one group and win or lose together; a merge result that still breaks a constraint takes the newer side's whole row.
• Foreign keys: a batch is applied in one transaction with defer_foreign_keys, so a child arriving before its parent from another peer's log is not rejected.
• The sync layer enforces no constraint. Uniqueness, required references and CHECKs hold locally and are repaired after a merge (the field-group rule above, a dangling reference set to null), never assumed of what peers send. CloudKit draws the same line: synced models may have no unique constraints and only optional relationships, since peers sync concurrently and in no guaranteed order.

Versions

• The sync format is additive only, as a CloudKit production schema is: a field or record type may be added, never removed, renamed or given a new meaning. A change that would break that adds a new record type instead, and the old one stays readable. So an older reader can always read, and the local schema migrates freely behind a mapping to this format. The cost is fields that outlive their use.
• It still has its own version, independent of SchemaVersion, for the rare exception. Each entry carries minReader, normally the version that introduced its record type.
• appMinVersion on the remote (as in Joplin's info.json): the lowest build allowed to sync with it, raised by the user from a newer device, or by a build that has to break the additive rule. A build below it does not sync and says which version is needed, which is easier to explain than entries skipped one by one.
• An older reader applies entries it can read and ignores fields it does not know. At the first entry whose minReader is above it, it stops reading that peer and keeps the cursor there; it never advances past an entry it could not apply. Its own changes are written to its own log as usual and do not touch other devices' data.
• Upgrading re-reads. Cursors are stored together with the format version they were read at. After this device's format version rises, it re-reads every peer from the latest snapshot, so fields it skipped while older are filled in. Without this, the device keeps defaults where peers have values, silently.
• Older entries are converted forward only for the exceptions to the additive rule: such a change ships a converter from the previous entry shape (as BackupV2 already does for files), so an entry written by an older peer is converted before it is applied.

Local rollback (#1499)

• The build that applies a step writes its reverse. SchemaMigration gains a down; applying a step stores the down SQL in the same transaction in schema_down(version INTEGER PRIMARY KEY, sql TEXT NOT NULL). A step that cannot be expressed as SQL declares itself irreversible and stores no row.
• Steps that an older build can read as they are (an added column with a default, a removed kv key) declare minReader and need no rollback: an older build opens the store directly. Only steps above minReader need schema_down.
• Rollback requires every row from current + 1 to the stored version; otherwise the rescue page is shown as today.
• Dry run on a copy (DbRescue.exportTo): run the downs, compare row counts per table, show what is lost, ask. The live store is replaced only after the copy opens at this build's version; the original is kept until then.
• After a rollback the device is an older reader as above; upgrading again re-reads (see Versions), which restores the columns the down dropped.

Device identity

deviceId lives in secure storage (keychain), not in the database. Restoring a backup or copying store.db to another device must not give two devices the same id, which would write the same seq into the same directory.

Transition

New builds also write a legacy BackupV2 snapshot for builds that predate the logs. TODO: drop it a few releases later.

Security

• Down SQL is read from the database and executed. That is acceptable only for the device's own store.db, encrypted under the keychain key. It must never run for a file from elsewhere (an imported or rescue .db): a crafted file could use ATTACH to create files anywhere the sandbox allows.
• Log entries are data, never SQL. Applying one goes through the same typed writes as a local edit.

Tests

• Upgrade re-read: an older reader applies entries with unknown fields; after upgrading, the device holds the peers' values, not defaults.
• Rollback then upgrade: down, edit, up again; the dropped columns come back from the peers.
• Two devices writing concurrently: both changes survive; three devices converge on the same rows regardless of read order.
• Clock skew: a device whose wall clock runs ahead does not win later edits from others.
• A field group that would break a constraint takes one side as a whole.
• A child arriving before its parent applies in one batch.
• An entry above minReader stops the cursor and is applied after upgrading.
• A missing seq, a truncated file and a compacted log are handled as above.
• Killing the app at each intermediate state above loses nothing and leaves no device waiting forever.
• A gone device's directory is deleted only after a live snapshot covers it; a new device then reads the same rows as before the deletion.
• A quota error keeps every change in the outbox and uploads it once space is back.
• A restored backup gets a new deviceId.
• Every step with a down: up then down against the fixture of the previous release, comparing schema and rows. Fixtures are never regenerated to make a test pass.
• A missing schema_down row refuses rollback; a cancelled dry run leaves the live store byte-identical; rollback never executes SQL from a file other than the live store.
• A schema step declares minReader, and either a down or that it is irreversible; forgetting either fails a test. The three edits a step needs (CLAUDE.md) become five.
• The sync format stays additive: a test compares every record type's fields against the previous release's and fails on a removed or retyped field.
• A build below the remote's appMinVersion neither uploads nor applies anything.

Not covered

• An older reader stopped at an unreadable entry also waits on that peer's later, readable entries until it upgrades. Nothing is lost.
• Two branches that assign different steps to the same version numbers: a branch must renumber before merging.
• Merging is per field group, not by intent: two edits to the same snippet keep one of them.
• Steps up to v33 stay as they are: irreversible, and with no minReader.

Prior art

• SwiftData / Core Data + CloudKit: local SQLite as the source of truth, persistent history exported as one CloudKit record per object, change tokens for deltas, last writer wins per record. The schema is additive only once in production; no unique constraints, optional relationships only.
• Joplin (WebDAV, Dropbox, ...): one file per item, info.json with the sync version and appMinVersion; SYNC and EXCLUSIVE lock files with refresh and timeout, needed because every client writes the same files. A client older than the target refuses to sync.
• KeePass: one file, merged by entry UUID and modification time, with deleted objects and per-entry history.
• Anki: incremental sync; a schema change forces a one-way full sync.
• cr-sqlite: column-level last-writer-wins in SQLite, a version and site id per column, causal length for delete and resurrect, changes exchanged through crsql_changes. Closest to this design; a reference rather than a dependency, since it is a loadable extension and the app links SQLite3MultipleCiphers.

Supersedes #1499.