Synced Databases

A profile can live in your account and be kept the same on every device you sign in on: logged on the phone, reviewed on the computer. Every device keeps a full copy of the profile in its own SQLite file and reads only that copy, so every screen is as fast offline as on, and logging with no signal works. Nothing answers a query from the network. A relay on Cloudflare keeps the agreed history of changes, passes them between devices, and can bring a new device up from nothing. A profile that lives on this device only never syncs and pays nothing for it. A synced profile belongs to one account: sync keeps one person’s profile the same across their devices, and never shares a profile between people or shows two people’s data together. See Profiles.

flowchart LR
  subgraph phone["Phone"]
    pw["A write"] --> pt["Trigger"] --> po["Outbox"]
  end
  subgraph cloud["Cloudflare"]
    w["Worker: session and ownership"] --> d["Durable Object: the profile's change log"]
  end
  subgraph desktop["Computer"]
    dm["Merge"] --> df["Its own SQLite file"]
  end
  po -- "push" --> w
  d -- "pulled after its cursor, on a nudge" --> dm

Ids made on the device

Every table’s id is a UUIDv7 text id made on the device by newId() in @datler/core: 48 bits of milliseconds, a 12-bit sequence, then random bits. Ids made by one process strictly increase, even within a millisecond, so ids sort by creation, and two devices offline can never make the same one. Ids stored inside JSON (a derived trackable’s references, a field’s catalog, a template line) are the same strings. The schema gives an id column no SQL default: Drizzle inserts get one from newId, and a raw INSERT that forgets its id fails rather than inventing one.

Capture

Changes travel as rows, never as whole files: two devices that both edited offline would otherwise overwrite each other’s work. Each table is declared synced or device-local, and a test fails on one that is neither. Everything a person made syncs: trackables, options, zones, achievements, entries, tags (memberships), catalogs and their items, templates, visualizations, Rundown layers, schedules, module installs, issue dismissals and settings. Three settings stay on the device because they describe one screen rather than the person: Home’s layout, its widgets, and which view each card shows. Sync’s own bookkeeping (_sync_state, _sync_outbox, _sync_row, _sync_expect, _sync_parked) is never sent.

• Triggers capture each change into the outbox, inside the statement that made the write, so no transaction is needed (the SQL plugin has none) and no repository has to remember to call anything. An insert records every column, an update the columns that changed (a changed key is a delete and an insert), a delete a tombstone. The triggers are generated from the Drizzle schema, so a new column cannot be missed, and are reinstalled at boot whenever the schema’s signature changes, since rebuilding a table drops its triggers. Before any migration runs, the Migration Runner drops them, so a migration that drops a column they name can run.
• Capture is off until a profile is linked. Linking turns it on and writes every existing row into the outbox, which is the profile’s first upload. Capture goes on before that snapshot is taken, so a write landing in between is recorded twice rather than not at all.
• A demo profile is never linked. The app offers no way to put one in an account and refuses to, and a demo found linked loses its link on the next round, leaving the account’s copy as it was. Demos never pay for capture.
• Each change carries a hybrid logical clock: milliseconds × 1024 plus a counter, one integer, with the device id breaking ties. Decimals travel as 17-digit text, which round-trips every double (82.1 kg is stored as 82100.00000000001 g, and a 15-digit copy would differ).

Merge

applyChanges in @datler/core merges a batch from another device. Applying a change twice, or a batch again after a crash halfway through, ends in the same rows. Later-write-wins per field, with deletes winning, is the usual default for this (Figma, Linear, PowerSync, Realm and cr-sqlite take it), and it suits one person on several devices, where a real conflict is rare because entries are mostly added rather than edited.

• Each column is later-write-wins on its own, so renaming Weight on one device and recolouring it on another both survive. A JSON column (a trackable’s capture or presentation settings) is one column, so two changes to different keys inside it keep only the later.
• A row exists while its latest insert is later than its latest delete, and an edit names the insert it was made on. So a delete wins over any edit made without having seen it, and a key that can come back (a tag on a trackable, an option, a setting) can be re-added and then edited without the edit being lost.
• Parents before children. A child whose parent is deleted is dropped; one whose parent has not arrived yet waits in _sync_parked and is retried with every later batch. Deletes go children first. An entry that names a catalog item deleted elsewhere shows the item as missing rather than failing.
• An entry is checked as a local write is. An entry that its trackable would refuse as the merge leaves it (a choice the trackable does not have yet, a value past its limit) waits like a child without its parent, since the trackable’s change that makes it valid may come in a later batch.
• Other merged state is accepted as it is. Two devices can each make half of something one device would refuse (each putting one tag under the other, which makes a cycle); sync converges on it rather than rejecting it.
• A merge does not capture its own writes. Every row the batch names is listed in _sync_expect while it applies, and the triggers skip a listed row. So a write the person makes meanwhile, to a listed row, would not be captured either, and the merge runs under the driver’s write hold instead (applyChanges takes SqlDriver.hold; withWriteHold in packages/core/src/write-hold.ts makes one, and the app’s driver is built on it): the app’s writes wait while a merge runs, the merge waits for writes already under way, and reads are never held. A transaction would not do, since the desktop’s driver runs on a pool of connections and has none. A test writes to a row the merge names in the middle of the merge and finds the write captured and sent.
• A change made under an older schema still applies. Columns the table no longer has are ignored, and columns the change does not carry keep their defaults.

A property test makes random interleaved edits on two files through the real repositories, delivers the changes shuffled, in random batches and partly held back, and asserts both files end with identical rows; it fails if any of the merge rules above is removed.

The service

apps/sync is a Hono Worker over D1 that holds accounts, sessions, each account’s list of profiles and its entitlements, and no tracking data. It runs on Cloudflare because the rest of Jeliroco’s web services do, through one deployment workflow with staging and production, so sync adds no second vendor, account or deploy path. Every error is { error: { code, message } }, the shape Jeliroco Mail uses.

• Sign-in is a six-digit code by email, the only way in, so the service stores no password, sent through Jeliroco Mail: valid ten minutes, five wrong tries, one live code per address, stored hashed with the address as salt. Asking for a code answers the same way whether or not the address has an account, and verifying a code makes the account if there is none. An address the mail service has suppressed (bounced, marked as spam) is refused as undeliverable (422) rather than told a code is coming. Codes are limited to five an hour per address and twenty an hour per IP, and guesses to thirty per quarter hour per IP. In development the message is kept in a table that only a development server shows (/dev/mail), which is how local sign-ins and the tests read the code.
• A device is a session: a 256-bit token sent as a bearer header, so the desktop and Android apps work like the browser; stored hashed; valid 180 days from its last use, which is recorded at most hourly. Each session also has a public id, so the device list can sign one out without exposing anything usable.
• Deleting a profile or the account empties the profile’s relay first, so a failure leaves the profile listed rather than its changes stranded. Deleting the account needs its address typed back.
• CORS answers the app’s own origins only: the Tauri WebViews everywhere, and the dev servers on development and staging.

route
GET /health	the environment’s name
POST /v1/auth/code, POST /v1/auth/verify	ask for a code; verify it with a device name, getting a token
GET /v1/me	the account, its devices, its profiles and what it may do
POST /v1/auth/signout, DELETE /v1/devices/:id	sign this device or another one out
GET, POST /v1/profiles; PATCH, DELETE /v1/profiles/:id	the account’s profiles
POST, GET /v1/profiles/:id/changes	push changes; pull after a cursor
GET /v1/profiles/:id/live	the live socket
DELETE /v1/account	delete the account and everything synced under it
GET /v1/weather	a place’s days of weather, from the shared cache of NASA POWER (Weather from NASA POWER)

The relay

One Durable Object per profile holds that profile’s change log in its own SQLite storage, reached by the profile’s id once the Worker has checked the session and that the account owns the profile; another account’s profile answers like a missing one. One object per profile is the same unit as one file per profile, caps every query at one profile’s rows, and can hold a WebSocket. The data is not kept in a D1 database shared by every user, because D1 bills every row a query scans, and one missed index there would scan everyone’s entries.

• The log is one table, change(seq, device, hlc, tbl, pk, body), numbered as changes arrive, a change’s payload (body) kept apart from what the relay needs to route it, so encrypting payloads end to end would not change the protocol; (device, hlc, tbl, pk) is unique, so a batch sent twice is stored once. A push of up to 1,000 changes is one transaction, and every change in it must come from the device pushing.
• A pull returns up to 1,000 changes after the device’s cursor, skipping the device’s own while moving the cursor past them. A device joining a profile pulls from 0: linking put every row into the log, so the log starts with the whole profile and no separate snapshot is needed.
• Schema versions: a device’s version is the number of core migrations it has. The first push sets the profile’s, a newer push raises it, and a push or pull from an older version is refused (409 schema_behind): that device has to update first.
• The live socket hibernates while idle, carries the token beside the datler-sync subprotocol in Sec-WebSocket-Protocol (a browser cannot set headers on one), and after a push tells every other device { type: "changes", upTo }. A ping is answered without waking the object.

Backups

Every synced profile’s log is exported to R2 once a day, at 04:17 UTC, on staging and in production (backUpAll in apps/sync/src/backup.ts, run by the Worker’s scheduled handler). An export is the whole log in JSON lines: a header with the profile’s schema version and its last seq, then each change as the relay keeps it, its payload copied as text and never read (RelayLog.exportLines). It lives at <profile id>/<day>.jsonl in the environment’s bucket (datler-staging-backups, datler-backups), with the log’s last seq beside it, so a profile whose log has not grown since its last export is not written again.

• Kept: the newest 14 days, then the first export of each month for the newest 12 months (KEEP); the rest are deleted the same day.
• A deleted profile’s exports go at the next run: any export whose profile is no longer in the database is deleted, so deleted data outlives the delete by a day at most.
• A restore puts an export back into an empty relay, every change at its own seq, so each device’s cursor still means what it meant (RelayLog.importLines); it refuses a relay that holds anything. The service has no route to run one yet.
• One profile’s export failing is logged and the others still run.

Entitlements

Keeping profiles on your own devices is free; syncing one through your account is an entitlement, since sync is the one part of Datler that costs money to run, the relay and the account’s copy. One function, access(account), is the only reader, so moving entitlements to a shared service changes one file. An entitlement is a span with a start and an end (none for lifetime), and the reader carries a run of spans on from one that starts as another ends, which is how time bought ahead adds up. Nothing in the service sells time: the grant command below starts a span where access still running ends, or now with none (grantSpan), so time granted adds up. Without sync, the account cannot make a synced profile, push, or open the live socket (402 no_sync), while pulls, renames and deletes stay open, so a lapse never holds data hostage. The account’s copy stays a year after a lapse, and any device can still pull it until then (Retention, below). A development server treats every account as entitled; pnpm --filter @datler/sync grant <email> month|3months|year|lifetime --env … grants one, making the account if needed, and with no duration lists the address’s grants.

Retention

An account’s copy of its profiles is kept a year after its sync lapses, then deleted. A daily job runs before the backups (enforceRetention in apps/sync/src/retention.ts, from the Worker’s scheduled handler):

• Who: an account that had sync and has none now, lapsed at the end of its last span (lapsedAt in apps/sync/src/entitlements.ts), with at least one profile in the account. An account with sync, lifetime or running, is never in the list, and the job asks access again before it deletes anything.
• The warnings, by email to the account’s address: one at the lapse, saying when the copy will be deleted, then one 30 days and one 7 days before (retentionMessage in apps/sync/src/mail.ts). Each is sent once per lapse and recorded in retention_notice; of the warnings due on a day, only the latest goes, so a missed day never sends three at once. Every warning says what stays: the profiles on the devices, and downloading and exporting the account’s copy until the date.
• The day: the same date a year after the lapse (keptUntil), and never sooner than the date the 7-day warning named: the copy goes only once that warning has been sent and its date has come, so a lapse found late still gets its week, and a later warning never names an earlier date than one sent. A send that fails is tried again the next day, and the copy waits for it.
• What goes: each profile, through the one delete path (deleteProfile in apps/sync/src/delete-profile.ts, which deleting a profile and deleting an account use too); its backups go at the same run’s backup pass, which deletes those of profiles no longer listed. The account itself stays, so it can sign in and sync again.
• Report-only until switched on: the job sends and deletes only where the Worker’s RETENTION variable is act; anything else logs what it would warn of and delete ([retention] in the Worker’s log) and changes nothing. Staging and production are set to report in apps/sync/wrangler.jsonc.

Granting sync again before the day ends the lapse, and the copy is kept; a later lapse starts its own year and its own warnings.

In the app

• One sign-in covers every profile on the device: the token, the address and the account id are kept together. In the desktop and Android app they are in the platform’s secure store, Windows’ Credential Manager or, on the phone, a private SharedPreferences file encrypted with a key held in the Android Keystore, through the shell’s read_sync_session and write_sync_session (apps/desktop/src-tauri/src/keychain.rs, over the keyring crates). The app reads them once before anything else (restoreSession in apps/renderer/src/sync/session.ts), and a session kept in localStorage by an earlier version moves there then. Where the store cannot be reached, and in the browser build, they are kept in localStorage. Losing them means signing in again and loses no data, since each profile’s link lives in its own file. The service is localhost:4290 in a dev build, staging.app.datler.ca in a Tauri debug build (the kind the phone runs) and app.datler.ca in a release build, the desktop installer’s and the browser build’s, unless the build sets VITE_SYNC_URL (serviceFor in apps/renderer/src/sync/session.ts); pnpm run dev:staging runs the dev app against staging that way. A test fails when the desktop app’s content security policy leaves one of the three out.
• Each profile’s link lives in its own file, in _sync_state: which account profile it syncs with, its cursor, when it last synced and the last error. Which profiles are in the account is also kept in the profile list (account on each), and the runner keeps the open file’s link matching it.
• The runner runs one round at a time, for the open profile: when the app opens, two seconds after a local write, when the window regains focus, when the live socket says another device pushed, and every five minutes. A “local write” is any statement that writes, whichever driver call ran it; the runner’s own writes do not count. A write made while a round ran is sent straight after it. The socket reconnects with backoff up to a minute and pings every 30 seconds. A round sending more than one batch of 1,000 shows its progress on the Account settings button, “1,000 of 4,800 sent”, counted by syncOnce after each batch reaches the relay (onPush).
• After a round, only what changed is read again: the merge returns the rows it may have written, entries are read again by id and the small lists whole, and a trackable deleted elsewhere reads the entries again whole. See Entry Cache.
• Errors are said in plain words: signed out; this profile is no longer in your account; another device has a newer version of the app, so update this one; syncing is not included right now and nothing is lost; the service cannot be reached, and either a round will try again or, for signing in and the account’s own actions, the person should (describeSyncError). A token the service no longer knows signs this device out.

Where a profile lives is chosen when it is made (this device, or your account), and changed by actions on it: move the open profile to your account, download one of the account’s profiles not yet on this device, remove one from this device (its file is deleted; the account keeps it), and delete one everywhere. The switcher marks synced profiles and lists the account’s others under “In your account · not on this device”; Settings → Account shows who is signed in, whether syncing is included and until when, this profile’s place, and the account’s profiles; the first-launch screen offers signing in and picking one. A new device joins by downloading a profile, never by merging two that grew apart.

The browser build

The browser build (--mode web) opens profiles with SQLite compiled to WebAssembly in a worker, storing files in the origin private file system through the opfs-sahpool VFS, which needs no cross-origin isolation. It asks for persistent storage. It installs as an app and opens with no signal: a web manifest with the mark’s icons, and a service worker that keeps the built files, the SQLite engine among them, and serves them first, updating itself on the next open (VitePWA in apps/renderer/vite.config.ts, for --mode web only; the city list loads when a place is picked). One tab holds the browser’s data at a time: the tab that opens it holds a Web Lock for as long as it lives (holdDatabase in apps/renderer/src/db/web/tab-lock.ts), taken before anything touches the storage, and a second tab says Datler is open in another tab, then opens by itself once that tab closes. The page remembers which files it has opened, so a file that has gone missing gets a screen saying its data is gone, with a button to start that profile again, empty, or to open another, instead of an empty profile made in its place. The pool keeps four slots spare beyond the files in use before opening one, since a writing database’s journal takes a slot, and deleting a profile deletes its file.

Deployment

The service deploys through ship-kit: promoting develop onto main deploys staging at staging.app.datler.ca, and an app-v* tag deploys production at app.datler.ca, each with its own D1 database and Durable Object namespace. Each also serves the browser build, so the app and its service are one origin: the deploy builds it first (build-command, staging’s pointed at staging with VITE_SYNC_URL), the Worker’s own paths (/v1/*, /health, /dev/*) go to it, any other is a file of the build, and a path that is no file is the app’s page (assets in apps/sync/wrangler.jsonc). A change to the app redeploys staging as a change to the service does. Both are custom domains rather than workers.dev, which sits behind a Cloudflare Access gate an app cannot pass. The Public Site’s three Workers deploy the same way, on their own site-v* tags. The desktop app’s content security policy lists the local, staging and production service for HTTP and WebSocket (Distribution).

Code

• packages/core/src/id.ts, sync-capture.ts, sync-apply.ts, sync-engine.ts (syncOnce, SYNC_SCHEMA), sync-link.ts, sync-client.ts.
• apps/sync/src/app.ts, auth.ts, mail.ts, entitlements.ts, relay-log.ts, relay.ts; apps/sync/scripts/grant.ts; apps/sync/wrangler.jsonc.
• apps/renderer/src/sync/ — session.ts, runner.ts, useSync.ts, profile-actions.ts; apps/renderer/src/components/sync/; apps/renderer/src/db/web/.

Related

Profiles · Entry Cache · Repository Seam · Migration Runner · Architecture Overview · Distribution