Skip to content
TruePPM

Offline Sync

TruePPM’s mobile client uses WatermelonDB as a local SQLite database. The sync endpoint provides a two-way delta protocol compatible with WatermelonDB’s synchronize() helper: GET pulls server changes since a watermark, and POST uploads a batch of local task mutations.

Pull endpoint

Section titled “Pull endpoint”
GET /api/v1/projects/{project_id}/sync/?since={cursor}
Authorization: Bearer <token>

Any project member (Viewer+) may call this endpoint.

ParameterTypeDefaultDescription
sinceinteger0Opaque cursor from a previous response’s timestamp. Returns rows changed strictly after it. Use 0 for a full sync. Keep constant for the whole paging session.
cursorstring—Opaque continuation token for the next page. Omit on the first request; pass the previous response’s next_cursor on each subsequent one.
page_sizeintegerTRUEPPM_SYNC_PULL_PAGE_SIZE (1000)Maximum rows returned across all collections in one page. Clamped to TRUEPPM_SYNC_PULL_MAX_PAGE_SIZE (5000).

Response

Section titled “Response”
{
  "changes": {
    "projects":               { "created": [], "updated": [...], "deleted": [...] },
    "tasks":                  { "created": [], "updated": [...], "deleted": [...] },
    "dependencies":           { "created": [], "updated": [...], "deleted": [...] },
    "calendars":              { "created": [], "updated": [...], "deleted": [...] },
    "memberships":            { "created": [], "updated": [...], "deleted": [...] },
    "risks":                  { "created": [], "updated": [...], "deleted": [...] },
    "sprints":                { "created": [], "updated": [...], "deleted": [...] },
    "sprint_retros":          { "created": [], "updated": [...], "deleted": [...] },
    "retro_action_items":     { "created": [], "updated": [...], "deleted": [...] },
    "task_suggested_assignees": { "created": [], "updated": [...], "deleted": [...] },
    "task_links":             { "created": [], "updated": [...], "deleted": [...] },
    "task_recurrence_rules":  { "created": [], "updated": [...], "deleted": [...] },
    "time_entries":           { "created": [], "updated": [...], "deleted": [...] }
  },
  "timestamp": 42,
  "next_cursor": "eyJpIjoxLCJ2IjoxLCJpZCI6Ii4uLiIsInciOjQyfQ==",
  "has_more": true
}
  • created is always empty — WatermelonDB uses upsert semantics
  • updated — full row objects for live (non-deleted) rows
  • deleted — string IDs of soft-deleted rows (tombstones)
  • timestamp — high-water mark to adopt as since after the delta is fully drained. Pinned when the session’s first page is served, so it is identical on every page of that session
  • next_cursor — opaque continuation token, or null when the delta is exhausted
  • has_more — true while more pages remain for this since session

Pagination

Section titled “Pagination”

The pull is cursor-paginated so a cold start (since=0) on a large project never materializes the whole project into one unbounded response. Each page carries at most page_size rows across all collections; the client loops until the delta is drained:

  1. Request the first page with since (no cursor).
  2. Apply the page’s changes.
  3. If has_more is true, request again with the same since and the returned next_cursor.
  4. When has_more is false (next_cursor is null), the session is complete — adopt timestamp as the since for the next sync.

The cursor is a compound keyset on (collection, sync_seq, id), not a scalar sync_seq ceiling. Rows written together share a sync_seq, and a scalar cursor could not split a page between two rows of the same value without either dropping rows or leaving the page unbounded. Keying on the row id (a unique UUID) as a tiebreak makes every page boundary unambiguous — no row is skipped and no row is duplicated, even when thousands of rows share a value. Because the cursor only ever increases, a row edited mid-session moves forward in the stream and is either delivered later or re-delivered under upsert — never lost.

The checkpoint belongs to the session, not the page

Section titled “The checkpoint belongs to the session, not the page”

timestamp is computed once, when the first (cursor-less) page of a session is served, and carried inside the opaque cursor. Every continuation page echoes that same value; it is safe to read the checkpoint off any page of the drain.

That pinning is what makes step 4 sound. Collections are drained in a fixed order, so once the cursor has passed a collection the session can no longer deliver anything written into it. If each page recomputed the checkpoint, the last page would report a value above such a mid-drain write, and the client adopting it would filter that row out of every future pull — the edit would be lost silently, with no error and no retry path. Holding the checkpoint at the first page’s value errs the safe way instead: the mid-drain row stays above the adopted since and arrives on the next pull, and any row the session did deliver above the pin is simply re-delivered under upsert.

The practical rule for clients is unchanged — keep since constant, loop until has_more is false, then adopt timestamp. Because the pin lives in the cursor, a continuation token from an older server is rejected with a 400; restart the drain at the same since, which loses nothing.

Retro rows are visibility-filtered (ADR-0071): a Viewer pulling a project whose retros are team-only does not receive the retro’s raw notes or action-item text — those rows are excluded at the queryset level, and tombstones are delivered for visibility-removed rows so the local database drops them.

Two version fields, two jobs

Section titled “Two version fields, two jobs”

Every synced model carries two counters. They look interchangeable and are not.

server_version — the per-row save count. Starts at 1 on INSERT, increments atomically on every UPDATE (no lost-update races), and gets one final increment on soft delete. This is the optimistic-lock token: it is what you send back as X-Base-Version, and what a conflict response reports.

sync_seq — the delta cursor. Drawn from the project’s own monotonic sequence, so every synced row in a project is ordered against every other one. This is what since filters on and what timestamp reports. It is not serialized on rows — treat timestamp as an opaque checkpoint and echo it back, which is what the protocol has always asked for.

since=0 returns all rows.

Conflict resolution

Section titled “Conflict resolution”

By default a stale write resolves last-writer-wins on server_version: whoever saves last wins the whole row. For the records people most often co-edit — task, project, and risk — the API instead does field-level merge so two people editing different fields of the same record both keep their work (ADR-0217).

A client opts in by sending the version it last saw as the X-Base-Version request header (or a base_version body key) on a PATCH:

  • If no one else has changed the record since that version, the write applies normally.
  • If someone changed different fields, the edits merge: the write applies, and the response carries an X-Merged-Concurrent-Fields header naming what the other writer changed so the client can reconcile its cache.
  • If someone changed the same field, the API returns 409 Conflict with a structured body — the client shows a “Someone else changed this” prompt with a Reload action rather than silently discarding either edit:
{
  "code": "sync_conflict",
  "detail": "Someone else changed this. Reload to see their changes.",
  "conflict_fields": ["name"],
  "server_value": { "name": "Their edit" },
  "client_value": { "name": "My edit" },
  "server_version": 6
}

Omitting X-Base-Version preserves the legacy last-writer-wins behavior, so the change is fully backward compatible. server_value is drawn from the record’s representation for the requesting user, so it never exposes a field the caller cannot otherwise read.

Reordering

Section titled “Reordering”

Board-card order is computed server-side under a row lock via POST /api/v1/tasks/{id}/reorder/ with a single anchor — {"before_id": "…"}, {"after_id": "…"}, or {"to_end": true}. Because the new position is computed while the sibling group is locked, two simultaneous drag-reorders serialize into a deterministic order instead of crisscrossing. Reordering requires Team Member+ (a Viewer cannot reorder).

TOCTOU safety

Section titled “TOCTOU safety”

The server snapshots the project’s cursor before running the delta queries. This prevents the race where a write lands between the checkpoint read and the row queries, causing a row to be included in updated but the timestamp to be set too low — making the client miss it on the next sync. On a paginated pull the snapshot is taken once for the whole session and echoed on every continuation page (see The checkpoint belongs to the session).

Cursor allocation takes the project row’s write lock, so concurrent writes to one project serialize and commit in allocation order. Without that a transaction could take a cursor value, a later one take the next value and commit first, and a pull in between would report the higher checkpoint and permanently skip the lower row.

Soft delete

Section titled “Soft delete”

Deleting a resource sets is_deleted = True, increments server_version, draws a fresh sync_seq, and records deleted_version. The row is never physically removed. On the next sync, the ID appears in deleted; WatermelonDB removes the local record.

Task deletion cascades: all Dependency rows where the task is predecessor or successor are also soft-deleted. Mobile clients receive tombstones for both.

Upload endpoint

Section titled “Upload endpoint”
POST /api/v1/projects/{project_id}/sync/
Authorization: Bearer <token>

Uploads a WatermelonDB-shaped delta batch (ADR-0082). The request body is an envelope:

{
  "client_batch_id": "<uuid>",
  "last_pulled_at": 42,
  "changes": {
    "tasks": { "created": [...], "updated": [...], "deleted": [...] }
  }
}
  • Writable surface — only the tasks collection may be uploaded in v1; any other collection key is rejected with 400. All other mutations go via REST.
  • Idempotent replay — client_batch_id is a client-generated UUID. The first request to apply the batch records it and its response atomically; a retry carrying the same id (within the retention window, default 24 hours) replays the stored response without re-applying. Safe against flaky mobile networks.
  • All-or-nothing — the whole batch applies inside one transaction. A row that fails validation or RBAC rejects the entire batch.
  • Same rules as REST — apply reuses the same serializer as PATCH /tasks/{id}/, so an upload can never do something the caller could not do over REST. Requires at least the Team Member role; archived projects are rejected.
  • Conflict resolution — plain last-writer-wins; each row is applied unconditionally and server_version is bumped.
  • Limits — the POST path is rate-throttled, and a batch is capped at 500 rows by default (TRUEPPM_SYNC_BATCH_MAX_ROWS).

WatermelonDB usage

Section titled “WatermelonDB usage”
import { synchronize } from '@nozbe/watermelondb/sync';


await synchronize({
  database,
  pullChanges: async ({ lastPulledAt }) => {
    // Loop the cursor until the delta is drained, merging each page's buckets.
    const since = lastPulledAt ?? 0;
    const merged: Record<string, { created: unknown[]; updated: unknown[]; deleted: string[] }> = {};
    let cursor: string | null = null;
    let timestamp = since;
    do {
      const url = new URL(`/api/v1/projects/${projectId}/sync/`, location.origin);
      url.searchParams.set('since', String(since));
      if (cursor) url.searchParams.set('cursor', cursor);
      const res = await fetch(url, { headers: { Authorization: `Bearer ${token}` } });
      const page = await res.json();
      for (const [collection, bucket] of Object.entries(page.changes)) {
        const dst = (merged[collection] ??= { created: [], updated: [], deleted: [] });
        dst.updated.push(...bucket.updated);
        dst.deleted.push(...bucket.deleted);
      }
      timestamp = page.timestamp;
      cursor = page.has_more ? page.next_cursor : null;
    } while (cursor);
    return { changes: merged, timestamp };
  },
  pushChanges: async ({ changes, lastPulledAt }) => {
    // Only the `tasks` collection is uploadable in v1; other mutations go via REST.
    await fetch(`/api/v1/projects/${projectId}/sync/`, {
      method: 'POST',
      headers: {
        Authorization: `Bearer ${token}`,
        'Content-Type': 'application/json',
      },
      body: JSON.stringify({
        // Persist the batch id with the queued batch so a retry after a
        // network failure replays idempotently instead of re-applying.
        client_batch_id: crypto.randomUUID(),
        last_pulled_at: lastPulledAt ?? 0,
        changes: { tasks: changes.tasks },
      }),
    });
  },
});

Collections

Section titled “Collections”
Server modelWatermelonDB collectionUploadable
Projectprojects—
Tasktasks✅
Dependencydependencies—
Calendarcalendars—
ProjectMembershipmemberships—
Riskrisks—
Sprintsprints—
SprintRetrosprint_retros—
RetroActionItemretro_action_items—
TaskSuggestedAssigneetask_suggested_assignees—
TaskLinktask_links—
TaskRecurrenceRuletask_recurrence_rules—
TimeEntrytime_entries—