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

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.