aw-sync writes to peer databases on every pull: WAL flip + schema migration on files it does not own

[ErikBjare]

The core invariant aw-sync's whole conflict-free story rests on — stated in aw-sync/README.md as "each device only writes to files in the sync folder they own, and other devices may not modify them" — is violated on every pull.

What happens

create_datastore opens a peer database read-write:

pub fn create_datastore(path: &Path) -> Result<Datastore, String> {
    ...
    Ok(Datastore::new(pathstr.to_string(), false))
}

and Datastore::new (aw-datastore/src/worker.rs) unconditionally:

let journal_mode: String = conn
    .pragma_update_and_check(None, "journal_mode", "WAL", |row| row.get(0))
    .expect("Failed to query journal_mode");
...
conn.pragma_update(None, "synchronous", "FULL")
...
let mut ds = DatastoreInstance::new(&conn, true).unwrap();

So reading a peer's file:

1. Flips its journal_mode to WAL, creating -wal and -shm sidecars inside a directory owned by another device.
2. Runs schema migrations on it — DatastoreInstance::new(&conn, true) upgrades user_version toward the local binary's version.
3. Writes synchronous = FULL.

All three are writes to a file this device does not own, performed by a read-only operation, in a directory an external file syncer is actively replicating.

Why it matters concretely

• Version skew makes it a real migration, not a no-op. Peer databases in a live sync folder sit at different user_versions, below current master's. An older peer's file gets silently upgraded by whichever device pulls it first — and that device may be running a newer aw-server-rust than the peer that owns the file. The owner then reopens a database migrated by someone else's binary.
• It manufactures sync conflicts. Two devices pulling the same peer, or a pull racing the owner's push, are concurrent writers to one file. My folder already contains the fingerprints: erb-main3/5a5df0f8-…/test.sync-conflict-20241125-052022-GRUSU5T.db plus a second from the same day.
• The -wal/-shm sidecars are themselves replicated, and a syncer that delivers a main file and its WAL at different moments produces exactly the torn snapshot the sidecars were meant to prevent.
• It compounds aw-sync: leftovers after #685/#686 — orphaned 2-level staging db, stale -synced-from- buckets, walker enters dot-dirs #689's finding that pulls also create empty staging directories inside peers' folders. Between the two, a pull writes to a peer's directory in three different ways.

Fix

Open peer databases read-only and side-effect-free:

• sqlite3_open_v2 with SQLITE_OPEN_READONLY, or a file:…?mode=ro URI
• never run migrations on a file this device does not own — if user_version is unrecognised, refuse that peer and report it (there is already a // TODO: Check for compatible remote db version before opening in sync.rs), rather than upgrading it
• do not set journal_mode or synchronous on peer files
• if a read-only open is impractical for a WAL-mode peer, copy the file to a scratch location first and open the copy — never the original

Note today's Datastore::new also spawns a worker thread and min/max-scans every bucket on open, which is heavy for what a pull needs; a lightweight read-only open path would help #684's status command too.

Credit: found in a design review of aw-sync; the code path above is verified against origin/master, the conflict files are from a live sync folder.

Related: #689, #691 (sqlite as wire format), #682.

cc @TimeToBuildBob

[ErikBjare]

Measured on a real sync folder, and it makes this a gate on #685 rather than a cleanup.

user_version distribution across peer staging dbs (NEWEST_DB_VERSION = 6):
  v4     58 files
  (unreadable/empty)  5 files

58 of 63 peer databases are at v4. Two consequences, both verified against origin/master:

1. Migration-on-open is not a small leak at this version spread — it is a fleet-wide rewrite. Today the daemon never pulls (#682), so this is latent. #685 makes every daemon pull for the first time, on the first release shipping datastore v6, into a fleet of v4 files. Release day becomes every upgraded device simultaneously running a v4→v6 migration (index rebuild, WAL header flip, -wal/-shm sidecars) on files it does not own, inside a directory a file syncer is replicating.

2. A read-only open is not a flag flip. Two blockers:

// datastore.rs:447 — exact equality, so migrate_enabled=false REFUSES every v4 peer
} else if db_version != NEWEST_DB_VERSION {
    return Err(DatastoreError::OldDbVersion(...));
}

// datastore.rs:1039,1044 — hard index hints
FROM events INDEXED BY events_bucketrow_endtime_starttime_index
FROM events INDEXED BY events_bucketrow_starttime_endtime_index

A v4 file contains events_bucketrow_index, events_starttime_index, events_endtime_index — neither hinted index exists, so reads fail with "no such index" even if the version check is relaxed.

So R1 needs a genuine tolerant reader: accept user_version <= NEWEST_DB_VERSION, drop the INDEXED BY hints when reading an older file, and never call _create_tables on a database this device does not own. That is a real work item, not a one-line change — worth budgeting before assuming #685 can ship alongside it.

Credit for spotting the version-equality and index-hint dependency: design review; the 58-file measurement is from a live folder.

[TimeToBuildBob]

v0.14.0 slice is #700: peer opens use file:?mode=ro&immutable=1, never migrate, skip+warn on user_version mismatch. Own staging is unchanged.

Not in that PR: the tolerant reader for old index names. v4 peers are skipped, not imported.