aw-sync publishes aw-server-rust's private sqlite schema as the wire format (unreadable by aw-server-python, unversioned, WAL-mutable)

[ErikBjare]

The files aw-sync publishes into the shared folder are sqlite databases in aw-server-rust's internal datastore schema. That schema is undocumented, unversioned as an interchange format, migration-mutable, and — the part that surprised me — not readable by ActivityWatch's own Python server.

The two schemas are structurally incompatible

aw-server-rust/aw-datastore (what lands in the sync folder, read from a real staging db):

CREATE TABLE buckets (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT UNIQUE NOT NULL, type TEXT NOT NULL, client TEXT NOT NULL,
    hostname TEXT NOT NULL, created TEXT NOT NULL
, data_deprecated TEXT DEFAULT '{}', data TEXT NOT NULL DEFAULT '{}');

CREATE TABLE events (
    id INTEGER PRIMARY KEY AUTOINCREMENT, bucketrow INTEGER NOT NULL,
    starttime INTEGER NOT NULL, endtime INTEGER NOT NULL,
    data TEXT NOT NULL,
    FOREIGN KEY (bucketrow) REFERENCES buckets(id));

aw-core/aw_datastore/storages/sqlite.py:

CREATE TABLE IF NOT EXISTS buckets (
    rowid INTEGER PRIMARY KEY AUTOINCREMENT,
    id TEXT UNIQUE NOT NULL, name TEXT, type TEXT NOT NULL, client TEXT NOT NULL,
    hostname TEXT NOT NULL, created TEXT NOT NULL, datastr TEXT NOT NULL);

CREATE TABLE IF NOT EXISTS events (
    id INTEGER PRIMARY KEY AUTOINCREMENT, bucketrow INTEGER NOT NULL,
    starttime INTEGER NOT NULL, endtime INTEGER NOT NULL,
    datastr TEXT NOT NULL,
    FOREIGN KEY (bucketrow) REFERENCES buckets(rowid));

Differences that matter:

• The bucket's string key is buckets.name in Rust and buckets.id in Python. name exists in both and means opposite things — the key in one, a nullable display name in the other.
• The bucket's integer PK is id in Rust, rowid in Python, so events.bucketrow points at a differently-named column.
• The JSON payload column is data in Rust and datastr in Python, on both tables.

These are not subtle: a query written against one raises "no such column" against the other.

Consequences

1. A user running aw-server-python cannot read their own sync folder with their own server's storage layer. The bundle still ships aw-server-python as an option, so this is a live split.
2. The wire format is one implementation's private schema. It changes under migrations — user_version is already at 5 — and the published files carry the evidence: every staging database in my sync folder contains a column literally named data_deprecated, internal migration residue being shipped as the interchange format.
3. Third-party tooling has to reverse-engineer it. There is no spec, no version marker in the sync folder, and no compatibility statement. sync.rs even carries // TODO: Check for compatible remote db version before opening — so a peer db from a future schema is opened blind.
4. Related: the filename. Every database in the folder is called test.db (see aw-sync: leftovers after #685/#686 — orphaned 2-level staging db, stale -synced-from- buckets, walker enters dot-dirs #689 item 4). A user opening the folder finds N identical filenames in a format nothing outside aw-server-rust can read.

Also: WAL

Measured on a live sync folder — two of three staging databases report journal_mode = wal, and aw-datastore/src/worker.rs enables it deliberately. aw-sync closes the datastore at the end of a pass, so sqlite checkpoints and removes the -wal sidecar; in the steady state the published file is self-contained, which is why no -wal files are currently visible in the folder.

The exposure is the write window. A push into a 273 MB staging database is not instant, and the file syncer is watching the directory: it can begin transferring test.db while a -wal exists and the main file is mid-checkpoint. There is already evidence of the syncer observing concurrent modification here — erb-main3/5a5df0f8-…/test.sync-conflict-20241125-052022-GRUSU5T.db and a second conflict file from the same day.

Publishing a mutable, in-place-updated sqlite file into a directory whose whole purpose is that an external process copies it whenever it changes is a structural mismatch, independent of the schema question.

What this is really asking

Whether the sync folder should contain sqlite at all, versus an explicit, versioned, documented interchange format that is written once and never mutated. That question is under active design review; this issue exists to record the concrete constraints any answer has to satisfy:

• readable by both server implementations, and by third-party tools, without reimplementing a private schema
• explicitly versioned in the folder, so a peer can refuse or adapt rather than opening blind
• safe to publish into a directory an external syncer copies at arbitrary moments
• self-describing filenames

Related: #689 (test.db naming, orphaned dbs), #684 (observability), ActivityWatch/activitywatch#1445.

cc @TimeToBuildBob — filing this as a constraints record rather than a proposal; the format decision should wait for the design review.

[TimeToBuildBob]

Read and understood. These constraints are well-documented.

The three structural constraints that matter most for the design review:

1. Schema incompatibility is fundamental — it's not just column naming; the bucket's string key (name in Rust vs id in Python) and JSON column (data in Rust vs datastr in Python) differ structurally, so there's no lightweight translation layer.
2. WAL write-window is the acute safety issue — the in-place update/checkpoint pattern means the sync folder can see a torn file during a push. The conflict files already in the folder confirm this is happening in practice.
3. The versioning gap is independent of format choice — whether the replacement is SQLite with a stable schema or a different format, the sync folder needs an explicit version marker so peers can refuse rather than open blind.

I'll hold off on design proposals until you're ready to drive that conversation; this records the constraints I'll be working from. Happy to dig into any of the three areas above — SQLite-vs-something-else trade-offs, a version-marker scheme, or a migration path for existing sync folders — whenever you want to kick off the design review.

[TimeToBuildBob]

Design review on ActivityWatch/activitywatch#1445 settled the format question this issue was holding: sqlite is the wrong wire format. v0.14.0 does not change it.

Constraint mapping after that review and the v0.14.0 slices:

• Schema incompatibility (Rust name/data vs Python id/datastr) — still open. No translation layer. Lands with the segment format.
• WAL write-window on files we publish — still open. Own staging is still WAL + in-place update. Mitigations in the current code are fighting the transport; the format change is what actually removes this.
• Versioning gap — partially closed by fix(aw-sync): open peer databases read-only; skip version mismatch #700: a peer with user_version != NEWEST is skipped rather than opened blind. Still no folder-level marker; that is the manifest v field, sequenced after feat(aw-sync): return and persist a SyncReport from each pass #699.
• Peer-side WAL/migration writes (aw-sync writes to peer databases on every pull: WAL flip + schema migration on files it does not own #693) — fix(aw-sync): open peer databases read-only; skip version mismatch #700 opens peers mode=ro&immutable=1 and never migrates. Ready in the current merge order (fix(aw-sync): open peer databases read-only; skip version mismatch #700 → feat(sync): reconcile owner-originated event edits #678 → fix(aw-sync): sanitize whitespace hostnames on import; make per-bucket errors non-fatal #697).

v2 sequence from ActivityWatch/activitywatch#1445: #699 SyncReport → manifest → segment writer. I will not start the writer until those land. Legacy test.db is tolerated indefinitely; v2 never deletes what it did not write.