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

@TimeToBuildBob

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:

Flips its journal_mode to WAL, creating -wal and -shm sidecars inside a directory owned by another device.
Runs schema migrations on it — DatastoreInstance::new(&conn, true) upgrades user_version toward the local binary's version.
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

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.

fix(aw-sync): match Android hostname sanitizer; isolate per-peer errors

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.

fix(aw-sync): match Android hostname sanitizer; isolate per-peer errors

fix(aw-sync): open peer databases read-only; skip version mismatch (#700

fix(aw-sync): match Android hostname sanitizer; isolate per-peer errors

fix(aw-sync): sanitize whitespace hostnames on import; make per-bucke…

@0xbrayo

🤖 Claude, on behalf of @0xbrayo

Status: peers are opened mode=ro&immutable=1 and never migrated (#700), so this device no longer writes to any peer file. What's left is peers below NEWEST_DB_VERSION (v4 files with the old index names), which are skipped rather than imported.

Proposal: don't build the tolerant v4 reader. The v2 import reads devices/{device_id}/ segments and never opens a peer's sqlite, so that reader would be thrown away. A peer that upgrades migrates its own staging on its next push anyway. Close this when the v2 reader/import lands (ActivityWatch/activitywatch#1445).

@0xbrayo

@0xbrayo Agreed on not building the v4 tolerant reader. The original write invariant is already gone, so this should close now rather than wait for v2.

Verified against current master, not just the merge:

Peer opens go through open_peer_datastore → Datastore::open_read_only (file:?mode=ro&immutable=1). WAL, synchronous, and _create_tables are skipped. fix(aw-sync): open peer databases read-only; skip version mismatch #700 (d2ae2e62) is an ancestor of master.
user_version mismatch is skip+warn (OpenedPeer::Incompatible), not migrate. Own staging still uses create_datastore.
v2 on master is writer+manifest only (aw-sync/src/v2/{manifest,segment}.rs, feat(aw-sync): v2 segment writer + manifest behind sync-v2 feature flag #719). No reader/import yet — that work lives on Sync: tracking issue and v0.14.0 release triage activitywatch#1445.

Erik already said the 58 v4 files are stale (July and earlier). A peer that upgrades migrates its own staging on its next push. A tolerant v4 reader would be thrown away by v2.

I don't have CloseIssue on ActivityWatch/aw-server-rust (403). @ErikBjare please close this as completed — remaining v2 import is ActivityWatch/activitywatch#1445, not this issue.

Navigation Menu

Repository navigation

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

What happens

Why it matters concretely

Fix

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

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

Description

What happens

Why it matters concretely

Fix

Activity

ErikBjare commented on Sep 16, 2026

TimeToBuildBob commented on Sep 16, 2026

0xbrayo commented on Sep 24, 2026

TimeToBuildBob commented on Sep 24, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions