[Spec] Sync multi-machine emdash

[64ix]

Problem Statement

I run emdash on two machines (a macOS machine and a Windows machine). Today each machine has its own isolated SQLite database, so my tasks, project definitions, portable settings, prompt library, and conversation metadata live only on whichever machine I last used. When I switch machines I lose my context: the tasks I created, the projects I was working on, the settings I tuned. I have to re-add projects and re-create state by hand. Meanwhile the machine-specific data (paths, credentials, SSH connections, worktrees, editor buffers) genuinely does belong per machine — I don't want that synced. I need the portable part of my state to follow me between machines automatically, without corrupting either local database, without sending credentials to any third party, and without me having to remember a master password or trust a sync vendor.

Solution

A fork-only, personal multi-machine sync: a small self-operated relay (Cloudflare Worker + D1, free tier) that stores encrypted, versioned row-bodies for a private space; the emdash app on each machine syncs a precise allowlist of Portable Data (projects minus their local paths, task states, portable settings, conversation metadata, prompt library, automation configs) to the relay. Machines attach to the same space via device-to-device pairing (a high-entropy secret copied from one machine to the other). Row bodies are encrypted end-to-end (AES-256-GCM) before they leave the machine, so the relay can never read them; it only applies last-write-wins ordering on plaintext metadata. The path of a local project is Machine-Specific Data and never travels: on the second machine a Project arrives Unattached and is re-attached (auto-detected by matching remote URLs, or manually). Sync is near-continuous while the app runs (long-poll), runs at launch, and has an always-visible Sync now button; when offline, writes stay local and are pushed on reconnect. Conflicts are resolved silently by last-write-wins.

User Stories

1. As a personal user, I want to pair my second machine to my first with a single copy-pasted secret, so that I do not have to create an account or trust a sync vendor.
2. As a personal user, I want my tasks and their states to appear on my second machine, so that I can pick up work where I left off on the other machine.
3. As a personal user, I want my projects to appear in my sidebar on the second machine, so that I can find the projects I was working on without re-adding them by hand.
4. As a personal user, I want a Project that arrives on a new machine to be automatically attached to a matching local directory when one exists, so that I do not have to re-add it.
5. As a personal user, I want to manually attach an Unattached Project to a local directory, so that I can use it even when the auto-detection finds nothing.
6. As a personal user, I want my portable settings (theme, keyboard shortcuts, default agent, notification preferences, etc.) to be identical on both machines, so that I do not reconfigure each machine.
7. As a personal user, I want my prompt library to be identical on both machines, so that my saved prompts follow me.
8. As a personal user, I want my automation configurations to be present on both machines, so that I can schedule work from either.
9. As a personal user, I want an imported automation to arrive disabled on the other machine, so that it does not fire twice.
10. As a personal user, I want my conversation metadata (titles, timestamps, provider) to appear on the second machine, so that I can see what a task was doing there.
11. As a personal user, I want to see a conversation that cannot be resumed on this machine clearly marked as non-resumable, so that I am not confused when I cannot continue it.
12. As a personal user, I want sync to happen continuously while the app runs, so that both machines are up to date without my intervention.
13. As a personal user, I want sync to happen at launch, so that a machine that was off catches up when it starts.
14. As a personal user, I want an always-visible Sync now button, so that I can force a sync when I want.
15. As a personal user, I want a small status indicator showing syncing / up-to-date / offline-with-pending / error, so that I can tell the state of the sync at a glance.
16. As a personal user, I want to see the last successful sync time and any errors, so that I can diagnose problems.
17. As a personal user, I want my local edits to be preserved when I am offline and pushed automatically when I reconnect, so that I never lose work.
18. As a personal user, I want my row bodies encrypted end-to-end, so that neither the relay nor Cloudflare can read my task content, repo names, or issue titles.
19. As a personal user, I want my credentials and secrets to never sync, so that no secret ever leaves its machine.
20. As a personal user, I want my per-machine paths (project path, worktree path, SSH keys) to stay local, so that the other machine never sees a path that does not exist there.
21. As a personal user, I want to add another device later by generating a new pairing secret, so that a third machine can join the same space.
22. As a personal user, I want to see my paired devices and revoke one, so that I can un-pair a lost machine.
23. As a personal user, I want the pairing secret to be backed up at space creation, so that I can recover if I lose a device.
24. As a personal user, I want an SSH project's remote path to travel (it is valid on both machines when they use the same host) while its connection is re-attached per machine, so that SSH projects survive the move.
25. As a personal user, I want a synced task whose workspace does not exist locally to be provisionable on demand, so that I can open it and work on the second machine.

Implementation Decisions

This spec is the product of a wayfinder effort on the fork 64ix/emdash; all decisions below are settled in the map (issues #110-#119). The seam for testing the whole feature is the main-process sync engine: a SyncEngine in a new src/main/core/sync/ module that reads/writes the local SQLite DB and talks to the relay only through an injectable RelayTransport (HTTP client) interface. Everything behavioural (push/pull, tombstones, conflict ordering, encryption, pairing) is testable against this seam with a fake or in-process relay and a real (temp) SQLite DB. The renderer stays thin: it observes a SyncStatus via typed events and calls a small sync RPC namespace.

Architecture

• Relay (server-side): a Cloudflare Worker + D1, operated by the fork owner, free tier. Stateless endpoints, plaintext metadata only, never parses row bodies. The client is the source of truth; the relay is an ordering relay.
• Protocol: two sync endpoints + a notification channel.
  • POST /v1/sync/pull {cursor} → {cursor, patches:[{space, table, pk, version, op, deleted}]} (opaque row bodies; server returns rows with version > cursor, ordered).
  • POST /v1/sync/push {mutations:[{table, pk, body, op}]} → assigns a new server version per row and acknowledges; applies last-write-wins by server receipt order (never rejects a stale push). Clients never send a version: the server-side counter is the only ordering authority (each row's client_seq travels inside the opaque body envelope, not in relay metadata — see Encryption).
  • Notification: long-poll (HTTP), reconnect with backoff. Durable Objects / WebSocket are out of scope (2 devices, free tier).
• Per-space monotonic version: a counter row per space in D1, incremented and stamped transactionally in the same batch as the row writes (UPDATE counter SET version = version + 1 RETURNING version inside db.batch()). Never use client timestamps or a bare AUTOINCREMENT for ordering (Replicache-documented failure modes).
• Server storage: generic KV rows (space_id, table, pk, body, version, deleted, updated_at) with an index on (space_id, version). No schema mirror — the relay does not know the schema, which tolerates version skew between machines.
• Tables synced (allowlist): projects, project_settings, project_remotes (initial-only, see below), tasks, conversations (metadata only), automations, kv:prompt-library, portable app_settings keys. Everything else is out of scope (see Out of Scope).
• Per-table sync mode: continuous for most; project_remotes is initial-only (carried once with the project's creation/attach payload as the auto-attach hint, then each machine maintains its own from live git) to avoid a delete-sweep write war between machines.

Schema changes (local DB)

• projects.path becomes nullable (migration). ⚠️ Hand-writing the SQL is NOT the fix by itself: the migration runner wraps all pending migrations in one transaction with foreign_keys=ON (where PRAGMA foreign_keys=OFF is a no-op), and the SQLite table-rebuild pattern (CREATE __new → copy → DROP TABLE projects → RENAME) cascade-deletes the children of projects (tasks, conversations, terminals, editor buffers, settings, remotes) in that environment even with hand-written SQL (verified empirically). The migration must avoid the rebuild inside the runner's transaction, which requires runner support: a designated non-transactional migration step executed with foreign_keys=OFF, asserting PRAGMA foreign_key_check clean before continuing. This deviates from the repo's "never hand-edit numbered migrations" guardrail — the PR must document the escape hatch per agents/risky-areas/database.md and keep the drizzle journal/meta consistent. Preserve the unique index on path (multiple NULLs are legal in SQLite). Add a data-preservation migration test with seeded children.
• Sync clock by trigger: add a sync_ts INTEGER (ms) column to each continuously-synced portable table, maintained by AFTER INSERT/UPDATE triggers (not by patching writers). Push detection = WHERE sync_ts > lastPushed per table. updatedAt is not used for this (mixed formats, stale values, missing on some tables). Pre-existing rows are NOT backfilled: the first sync after a space is created or joined is a bootstrap push — a full-table enumeration of the allowlist, independent of the sync_ts watermark, owned by the engine — so months of pre-pairing history actually reach the second machine (without this, stories 2 and 3 break for every real user).
• Applied-version side table sync_row_state(table, pk, server_version, client_seq, dirty, quarantined) — created by the schema ticket, maintained by the sync engine — used for the client-side LWW guard, dirty-row tracking, the per-row client_seq write counter (see Encryption), and the decrypt-failure quarantine list.
• Versioned JSON columns: tasks.linkedIssues, automations.*Config, conversations.config are transported as raw JSON strings and applied with guarded writes — never re-serialize a column that parsed as future-version on this machine (round-trips destroy it; callers read null).
• tasks.boardRank is excluded from the synced payload (derived fractional-index state, machine-local like view-state).
• Dead columns never sent: tasks.workspaceProviderData, tasks.workspaceIntent (always NULL in current builds).
• Machine-Specific data excluded from the payload — two distinct mechanisms (they are not all columns; verified against the schema):
  • Real columns, excluded outright: projects.path (and projects.repositoryWorkspaceId is nulled at import); conversations.sessionId/agentStatus/agentStatusSeen; automations.enabled (local, defaults disabled at import). The app_settings keys localProject and providerConfigs are whole rows and are excluded outright — providerConfigs contains a provider env map and must be treated as credential material (story 19), with an explicit exclusion test.
  • JSON sub-fields, redacted on push and preserved on apply: worktreeDirectory and workspaceProvider live inside project_settings.baseProjectSettingsJson, and customSoundPath / defaultShell live inside the app_settings rows notifications / terminal respectively — none of these is an addressable column/key. The engine strips these sub-fields from the JSON before transport, and on apply merges the pulled JSON while keeping the receiving machine's own values for exactly these sub-fields (whole-row LWW everywhere else).

Remapping / attachment

• A synced Project arrives Unattached (no local path for local projects; no SSH connection for SSH projects). It is a first-class state, distinct from path-not-found (which means "directory was deleted").
• Local projects: path is machine-local and never travels. Auto-attach attempts at import by scanning the machine's localProject.defaultProjectsDirectory for a repo whose live remotes match the Project's remotes (normalized remote URL); on a match, attach silently. Otherwise it stays Unattached with an "Attach" action.
• SSH projects: the remote path travels (valid when both machines use the same host — assumed by default); only sshConnectionId is machine-local and re-attached per machine. Attach = pick a local connection (updateProjectConnection).
• Merge at attach: matching uses the live remotes of the picked directory (the (remoteName, normalizedUrl) pair set) scoped by type (local vs SSH, same connection). If the picked repo matches an existing local Project of the same type, merge into it (the local row wins; one sidebar entry). If a remote URL matches both a local and an SSH Project, ask the user.
• New attach RPC (update path + re-run ensureRepositoryWorkspace), reusing the existing inspectProjectPath/existingProject short-circuit. Re-attach must respect the unique path index (dedupe).
• SSH merge key cross-machine: fingerprint (host, port, username) + path, not path + connectionId (connection ids are machine-local).

Identity & pairing

• Zero-dependency device-to-device pairing (no emdash account, no upstream infra). The relay knows only spaces and devices.
• Model: spaces(space_id) and tokens(id, space_id, device_id, name, sha256, created_at, last_seen_at, revoked_at) in D1. A token is scoped to exactly one space. v1 deliberately collapses "revoke token" (auth) and "remove device" (membership) into a single Remove device action: it sets revoked_at and drops the device from the listed membership (rows retained for audit).
• Device identity: a dedicated deviceId (UUID) in a machine-local KV namespace (device), plus a human device name. Not the telemetry instanceId (coupled to telemetry lifecycle).
• Client token: 32 random bytes base64url with a prefix + checksum, stored in app_secrets (safeStorage) via the encryptedAppSecretsStore idiom. Only SHA-256 of the token is stored on the relay; compare with timingSafeEqual (available on Workers).
• Pairing flow: POST /v1/space creates the space and returns the first device token; the app then generates K0 (32 random bytes — the space data key, see Encryption) locally. Adding a device is uniform (a third device later works exactly like the second): an existing, authenticated device generates a fresh random 16-byte join_token, registers SHA-256(join_token) with the relay via POST /v1/pairings (single-use, TTL 15 minutes, per-pairing attempt budget stored and decremented transactionally in D1 — no TOCTOU), and displays the pairing secret = base32(join_token ‖ K0 ‖ checksum), where a 4-byte truncated-SHA-256 checksum covers the whole payload — a transcription error in either half fails at entry, instead of surfacing later as a mysterious decrypt failure. The new machine decodes the secret, verifies the checksum, calls POST /v1/join {join_hash: SHA-256(join_token)} → {device_token, space_id}, and stores K0. The relay only ever sees hashes; K0 never transits except inside the user-carried secret. Copy-paste (with a copy button) is the primary flow; the emdash://join?secret=… deep link requires OS-level custom-protocol support that does not exist in the app today — electron-builder protocols config plus open-url (macOS) / second-instance argv (Windows/Linux) handling — and is in scope of the pairing ticket.
• Devices UI: a Settings "Devices" tab (pattern of SshConnectionsSettingsCard): list, add (mint secret), and a single Remove device action with confirmation (revokes the token and removes membership).

Encryption (E2E)

• Row bodies are encrypted end-to-end; the relay never reads them.
• Space data key K0: 32 random bytes generated locally at space creation (randomBytes(32), repo pattern). There is no HKDF secret-splitting: join tokens are independent random values minted per pairing (see Identity & pairing) — a deterministic derivation cannot be "fresh per mint", so the earlier two-half HKDF design is withdrawn. K0 never transits except embedded in the user-carried pairing secret.
• Algorithm: AES-256-GCM. A per-row key is derived HKDF(K0, salt=table‖pk, info="row-v1") with a random 96-bit nonce per encryption (kills multi-device nonce-reuse risk). Envelope is versioned: {alg, key_id, client_seq, nonce, ct}. AAD = space_id ‖ table ‖ pk ‖ key_id ‖ client_seq. The server-assigned version is deliberately NOT in the AAD — it cannot be: it is assigned by the relay's counter at push time and does not exist when the client encrypts. Integrity therefore works as: the AAD pins a ciphertext to its space/table/pk (the relay cannot swap bodies across rows, tables, or spaces), and client_seq — a per-row monotonic write counter maintained in sync_row_state — lets the receiver drop any pulled row whose client_seq is lower than the last applied one for that row, so a relay replaying an old body under a fresh server version is detected. Encryption runs in the main process (node:crypto, Node 24 in Electron 40).
• Envelope for the relay: plaintext metadata (space, table, pk, version, op, deleted) + opaque encrypted body (which internally carries the envelope header incl. client_seq). The relay applies LWW on metadata without reading content.
• Key storage: K0 in encryptedAppSecretsStore (safeStorage → app_secrets), pattern of emdash-account-token. No escrow by design. At space creation the app offers a recovery code to save: base32(K0 ‖ checksum), labeled honestly — it decrypts relay data but does not grant relay access; in a total-device-loss scenario relay access is restored by the operator (you) inserting a pairing row directly in D1 (runbook in the relay README). Note: db:reset or a fresh EMDASH_DB_FILE wipes app_secrets (device identity, token, K0) — re-pairing is the documented recovery; the relay copy is unaffected.
• Rekey is out of scope for v1 (documented follow-up: new K0, re-encrypt all rows, key_id changes; no routine rotation). Accepted residual risk, stated plainly: removing a device revokes relay access only — a device that held K0 keeps the ability to decrypt data it already pulled (and any ciphertext it later obtains) until a rekey capability exists.
• Decrypt failure mid-pull = quarantine-and-continue: a pulled row that fails AAD/GCM verification (or carries an unknown key_id) is recorded as quarantined in sync_row_state with its server version; the cursor still advances (sync never wedges on one bad row), the row is retried on every subsequent sync, and a persistent error surfaces in SyncStatus (visible in the widget popover). Rows are never silently dropped.
• TLS is required regardless (protects the plaintext metadata and the join flow) — provided by the Cloudflare Worker.

Conflict model

• Last-write-wins, whole row, silent (mono-user; conflicts are rare). Server-assigned versions are authoritative; never order by client timestamps. No CRDTs, no per-column merge (YAGNI). The only exception is the documented machine-local JSON sub-fields (see Schema changes), which the apply step preserves on the receiving machine.
• Tombstones: a delete is a row with deleted flag at a new version. LWW is uniform: a delete beats an older edit, a newer edit resurrects. GC is owned by the relay: it records each device's last-pull cursor and hard-deletes a tombstone only once every non-removed device's cursor has passed it, with a 90-day safety cap. app_settings reset-to-default is a normal tombstone. Note automations are soft-deleted via a column in today's app (no SQL DELETE is issued), so they sync as ordinary row updates; tombstones apply to tables that really delete rows.
• Push-then-pull ordering: local dirty rows (not yet pushed) are never overwritten by a pull; order is strict push (ack) → pull. A stale push is never rejected (accept-and-overwrite); every push is acknowledged to avoid retry loops.
• Version skew: the opaque relay + per-machine local migrations + the existing future-version handling in versioned-schema (a row from a newer app version is preserved, never overwritten by an older local value).
• Accepted residual risks (personal, self-operated threat model — named here so they are chosen, not discovered later): the relay can withhold rows or serve a frozen-but-valid snapshot (only per-row client_seq regression is detectable, not omission); the relay operator sees metadata (table names, pks, row sizes, write timing); whole-row LWW can lose one side of a near-simultaneous edit of the same row on two live machines; free-tier Workers/D1 quotas are assumed sufficient for a 2-device personal load and are not analyzed further.

UX

• Engine lifecycle (main process, owned by the engine ticket): the SyncEngine starts only when a space is configured and the sync.enabled app setting is on (the kill switch — onboarding/pairing turns it on, Settings can turn it off); it performs an at-launch catch-up that never blocks app boot (window creation does not wait on the relay; a launch-time relay outage degrades to offline-with-pending); it subscribes to the relay's long-poll channel with reconnect + backoff; and it pushes on local writes (debounced). This loop is what delivers stories 12 and 13 — it is engine scope, not widget scope.
• Status widget in SidebarFooter (pattern of the provider-usage gauge): state icon (syncing / up-to-date / offline-with-pending / error), an always-visible "Sync now" action, and a popover with last-successful-sync time and errors. Driven by a sync:status typed event and a sync RPC namespace (getSyncStatus, syncNow), with a useSyncExternalStore store.
• Offline: writes stay local (local-first); the widget shows a pending badge; reconnect triggers an automatic push+pull.
• Onboarding on a new machine: on first run with no space, a prompt "Join an existing space (paste the secret)" is the primary action; "Start from scratch" is secondary.
• Unattached Project: a row indicator ("Unattached") with an "Attach" action; auto-attach already attempted at import.
• Non-resumable conversations: a synced conversation is shown with an explicit "not resumable on this device" state.
• Imported automations: a badge "imported, disabled" (add a source field to the Automation type).
• Background: the sync service runs in the main process; sync:status is guarded by window liveness. On Windows/Linux the app quits on window close (no tray), so out-of-window sync is covered by sync-at-launch; on macOS the app keeps running and the service stays active.

API contract (relay endpoints)

• POST /v1/space (create) → {space_id, device_token} (the pairing secret is generated client-side; see Pairing flow)
• POST /v1/pairings {join_hash} (authenticated) → registers a pending pairing for the caller's own space (single-use, TTL 15 min, transactional attempt budget). This is the endpoint that lets an existing device add the next one — without it, only the very first join is possible.
• POST /v1/join {join_hash} → {device_token, space_id} (matches a pending pairing; single-use, TTL, attempt-limited)
• GET /v1/devices, POST /v1/devices/remove {device_id} (single action: revokes the token and removes membership)
• POST /v1/sync/pull {cursor}, POST /v1/sync/push {mutations}, plus a long-poll notification channel.
• POST /v1/space/delete (authenticated) → deletes all relay-side data for the space ("delete my data").
• All requests except join carry a device token (Bearer); the relay verifies SHA-256(token) + space_id scope per request.

Testing Decisions

• What makes a good test: only external behaviour of the SyncEngine (what it reads/writes in the local DB and what it sends to / receives from the relay), never internal details. Tests drive the engine against a temp SQLite DB (the repo's existing temp-repo/worktree test style) and a fake in-process RelayTransport.
• Modules tested:
  • SyncEngine: push/pull/tombstone/conflict ordering, dirty-row preservation, push-then-pull order, bootstrap full-table push on first attach, client_seq regression drop, version-skew handling, boardRank/column exclusions + JSON sub-field redaction/preservation, guarded versioned-JSON writes.
  • CryptoHelper (GCM): envelope format, per-row key derivation, AAD binding (incl. cross-table/pk/space swap and replay), decrypt failure modes, unknown-key_id quarantine.
  • PairingService: secret minting/encoding, checksum verification, join-hash derivation, single-use/TTL/attempt-budget enforcement (against a fake relay).
  • project_remotes initial-only mode: no continuous churn between two engines sharing a fake relay.
  • Relay handlers: pull cursor semantics, push LWW, per-space counter monotonicity under two concurrent pushers, token auth, pairing single-use/TTL/attempt budget, tombstone GC against per-device cursors, long-poll channel. The relay lives at packages/sync-relay with handlers written as pure functions over an injected storage interface, so the suite runs in plain vitest with a SQLite-backed D1 fake — no workerd/wrangler/miniflare in the agent gate; the wrangler config exists for the human deployment only.
• Prior art in the repo: existing DB integration tests in src/main/db/tests/migrations/ (data-preservation migration tests), service tests with injected fakes (e.g. PR sync, board sync), and the temp-repo worktree test pattern under os.tmpdir(). The renderer status widget mirrors the existing provider-usage gauge tests (browser/Playwright) — not-run when infra is absent.

Out of Scope

• Machine-Specific Data (never syncs): app_secrets (all secrets), ssh_connections, workspaces, terminals, editor_buffers, messages (defunct), kv namespaces other than prompt-library (telemetry, host-dep, PR cursors, issues-sync, ghost-cards, view-state, account, integration connections, transient backfill keys).
• Out-of-scope tables: automation_runs, pull_requests + derived (pull_request_users/labels/assignees/checks), workspace_file_index_meta / workspace_file_index (machine-local search cache).
• Conversation transcripts — not stored by the app; only metadata syncs.
• Board order (tasks.boardRank), view-state, and other derived UI state.
• Autostart / background sync while the app is closed on Windows/Linux — the destination is sync while the app runs, at launch, and manual; no tray.
• Multi-user / multi-space — a personal mono-user, single-space feature.
• Sync vendors (Turso, PowerSync, Electric, file-sync, Replicache) — rejected by facts (see the map).
• CRDTs and per-column merge — YAGNI for a mono-user.
• Rekey / key rotation — v1 ships without it; the residual risk is documented in the Encryption section.

Further Notes

• This is fork-only (64ix/emdash). The relay, identity, and E2E are independent of upstream (generalaction/emdash); the emdash account (auth.emdash.sh) is upstream infra and is deliberately NOT used for sync identity.
• Version skew between machines is handled by the opaque relay + per-machine local migrations + future-version preservation; schema migrations remain per-app-launch.
• The Automation type gains a source field; the workspace-config schema is bumped to v3 (repository-instance.workspaceId becomes optional, resolved at run time against the mounted project's repositoryWorkspaceId, failing before commit if the project is unattached).
• The relay lives in the monorepo at packages/sync-relay (pure handler functions + wrangler deployment config; deploying is an operator action, never CI). Relay code version vs app version: the relay's opaque storage means most app changes need no relay change; when the relay itself changes, the operator redeploys manually — endpoints are versioned under /v1/ so a newer app can detect an older relay.
• The sync subsystem gets its own agents/ topic page (added with the engine ticket) and a CONTEXT.md update.
• The spec is the assembled output of wayfinder map Carte Wayfinder — Sync multi-machine emdash #109 (issues Recherche : paysage des mécanismes de sync #110-Rédiger le spec multi-machine sync #119); implementation follows the repo's spec conventions and the fork's branch model (fork-main). Amended on 2026-08-09 after a plan review (see the review comment below): pairing/crypto model reworked (random join tokens + client_seq AAD), bootstrap push added, JSON sub-field redaction specified, migration approach corrected, relay home + GC + lifecycle ownership assigned.

Emdash-Task: b653f3b3-110e-4ed5-8e19-ef2fe1039f20