SyncEngine : cœur push/pull/LWW/tombstones

[64ix]

Parent

#130

What to build

The main-process sync engine that moves the portable allowlist of rows between the local SQLite database and the relay through an injectable transport. It pushes rows whose sync clock advanced past the last-pushed watermark, pulls rows newer than the pull cursor, applies server-authoritative last-write-wins, tracks dirty rows (push-then-pull, never overwriting unpushed local edits), and propagates deletions as tombstones. It honours the per-table allowlist and sync mode (continuous vs initial-only), excludes derived/machine-specific and dead columns from the payload, redacts machine-local JSON sub-fields on push and preserves them on apply (worktreeDirectory/workspaceProvider inside project_settings.baseProjectSettingsJson; customSoundPath/defaultShell inside the app_settings notifications/terminal rows — these are not columns), transports versioned JSON columns as raw strings with guarded writes, and nulls the repository workspace reference at import.

The engine also owns the sync lifecycle (spec stories 12/13): it starts only when a space is configured and the sync.enabled app setting is on (kill switch), performs an at-launch catch-up that never blocks app boot, subscribes to the relay's long-poll channel with reconnect + backoff, pushes on local writes (debounced), and performs a bootstrap push — a full-table enumeration of the allowlist, independent of sync_ts watermarks — the first time a space is created or joined. Pre-existing rows never advanced the sync clock; without the bootstrap, a long-lived database syncs nothing.

Bodies are transported as opaque blobs from day one (the engine serializes a row payload and hands bytes to the transport), with each row's client_seq (from sync_row_state) carried in the body envelope, so #134 wraps encryption around the byte boundary without protocol changes. On pull, a row whose client_seq is lower than the last applied one for that row is dropped (stale/replay guard).

Sizing note: this is the largest unit of the spec and it blocks four downstream tickets. Delivering it as a short stack of PR-units (core push/pull/LWW/tombstones → per-table rules & exclusions → lifecycle/scheduler) is acceptable and preferable to one giant PR.

Acceptance criteria

• An engine pushes/pulls the allowlisted tables via an injectable transport against a fake relay. The allowlist is exactly projects, project_settings, project_remotes (initial-only), tasks, conversations (metadata only), automations, kv:prompt-library, and the portable app_settings keys — enumerated in one place in code.
• LWW follows server versions; tombstones propagate deletes; a stale push never corrupts state; local dirty rows survive a pull; a pulled row with a regressed client_seq is dropped.
• First attach to a space triggers a bootstrap push covering rows that pre-date the sync_ts triggers; an explicit test seeds rows before pairing and asserts they arrive on the second engine.
• Lifecycle: engine start is gated on space-configured + sync.enabled (kill switch); the at-launch catch-up does not block boot; the long-poll subscription reconnects with backoff; local writes trigger a debounced push.
• project_remotes syncs only on project create/attach (initial-only), with no continuous churn.
• The payload excludes boardRank, dead columns, and machine-specific columns (providerConfigs and localProject whole-row exclusions have explicit tests — providerConfigs carries an env map, i.e. credential material); the machine-local JSON sub-fields listed above are redacted on push and the receiving machine's own values preserved on apply; versioned JSON columns are transported raw with guarded writes, and a future-version row is never re-serialized or overwritten by an older value (version-skew test).
• projects.repositoryWorkspaceId is nulled on import so the receiving machine regenerates its own.
• Integration test: two engines with separate temp DBs converge through an in-process relay (task statuses, settings, prompt library, conversation metadata) — running in the main-db vitest project (real better-sqlite3).

Blocked by

• 1 (relay), 2 (schema)