Your sync cursor cannot be a timestamp, because timestamps are not unique

The mobile app could queue writes offline and replay them. It could not pull changes down incrementally. Every refresh was a full refetch of every list, which is fine on a desk and useless on a phone in a warehouse with two bars of signal.

The reason it had never been built turned out to be a hard block rather than a missing afternoon. The API had no way to ask for changes since a point in time. After a strict query-parameter guard was introduced, an unrecognised parameter no longer got silently ignored — it returned an error — so attempts to pass a since-timestamp or a cursor failed loudly on every resource. Even the crude fallback was shut: sorting by last-updated was outside the sort allow-list on the two resources that mattered most.

That was checked against the running system rather than assumed, and then written up as required API work. The alternative — faking incremental sync by refetching everything and pretending — would have shipped a feature that was a lie about its own cost.

The obvious design, and why it was refused

The obvious design is to add a since-parameter and a cursor to the list endpoints that already exist. One route per resource, already permissioned, already tenant-scoped. It looks like less work and less surface.

It was rejected, and the reasoning was written into the service rather than into a commit message, because it is the kind of decision someone will otherwise reverse in six months on aesthetic grounds.

Offset pagination and cursor pagination are mutually exclusive contracts. A list endpoint takes a page number and a sort field. A sync endpoint takes an opaque cursor over a fixed ordering. Merge them and a request combining a sort with a cursor becomes syntactically valid and silently wrong — it will return rows, in an order the cursor does not describe, and skip an unpredictable subset. The worst failures are the ones the type system accepts.

Sync must return deleted rows. Lists must never. A client that pulls changes needs to learn that a record went away. Every ordinary read must exclude soft-deleted records without exception. If that difference is a query-parameter flag, then the question of whether an endpoint returns dead records is one typo away from yes on the busiest read in the system.

The response envelope is different. Pagination metadata describes totals and pages. Sync metadata describes a cursor and whether more remains. Cramming both into one shape means every consumer checks which one it got.

Three parameters declared on one route beat the same three declared on five routes. Under a strict parameter guard, a route that forgot to declare one of them rejects a legitimate client request. One route cannot forget.

So sync is its own route family, sharing the scope helpers and the permission key of each resource’s ordinary list, so that the two can never disagree about who may read what.

The cursor problem, stated properly

The naive cursor is the last update timestamp you saw. Ask for everything greater than it. This is wrong, and the reason is worth understanding because it applies to every keyset pagination scheme anybody writes.

Update timestamps are not unique. In PostgreSQL the usual default is the transaction start time, which means every row written by one transaction carries the identical timestamp to the microsecond. A bulk import writes four hundred rows with one timestamp. A cascade writes a parent and its children with one timestamp.

Now suppose a page boundary lands in the middle of such a group:

• With a strictly-greater cursor, every remaining member of that group is skipped. Permanently. The client will never see them, and nothing about the sync reports an error.
• With a greater-or-equal cursor, the group repeats at the start of every subsequent page, and if the group is larger than the page size the sync never advances at all. An infinite loop that looks like a network problem.

Neither is acceptable and there is no third option using the timestamp alone.

The fix is to order by a pair and compare it as a pair: the update timestamp and the row identifier, with a genuine row comparison in the predicate rather than two chained conditions on separate columns. The pair is unique because the identifier is, so the ordering is total, and a cursor holding both resumes exactly where it stopped regardless of how many rows share a timestamp.

The supporting index is on the tenant, the update timestamp and the identifier, in that order. Deliberately not a partial index excluding deleted rows — every other index in the system excludes them, and sync is the one path that must see them.

Tombstones, and what a cursor should carry

A deletion cannot be expressed by absence. A client that pulls changes and receives nothing about a record has learned nothing about that record.

So deletions travel as tombstones: the identifier, the update timestamp, a deleted flag, and no payload. The client applies it by removing its local copy. This is why the sync path reads soft-deleted rows and why the ordinary lists must not — the same row is a fact to one consumer and noise to the other.

The cursor itself is opaque, and it carries two things beyond its position: a format version, and the resource it belongs to. The resource key exists for one specific failure. Without it, a cursor from one resource replayed against another is structurally valid — it is just a timestamp and an identifier — and produces a plausible, wrong delta. With it, the request is rejected. An opaque token should carry enough context to refuse being used in the wrong place, because the wrong place is where it will eventually be used.

There is also a discovery route that lists which resources the caller is permitted to sync, so a client with a limited role does not have to attempt seven pulls and interpret three refusals.

One narrowing decision is worth naming because it went the other way from the rest. The catalogue resources — products, variants, price lists — apply no branch narrowing on sync, deliberately, because they have no branch column and their ordinary lists apply none either. Inventing a narrowing for the sync path alone would have given a field rep a synced catalogue that disagreed with the catalogue the app shows them online, which is a worse outcome than the one it was protecting against.

Testing a sync is testing for both errors

Sync has two failure modes and they are opposites, so a test suite that only checks one of them passes on a broken implementation.

The checks that earned their place: a paged walk that asserts every row is delivered exactly once — not at least once, and not at most once. A row modified in the middle of the walk, tested both when the modification lands before the current position and when it lands after, because those exercise different halves of the cursor logic. Tombstone delivery. A malformed cursor and a cursor from the wrong resource, both rejected.

And data scope proved in both directions. A scoped caller must not see rows outside their scope, and must see the rows inside it. Only checking the exclusion half is the trap, because an implementation that returns nothing at all passes it perfectly.

The limit I did not close

Transaction start time is not the same as commit time. A long transaction can commit a row whose update timestamp is earlier than a watermark a client has already passed, and that row is then invisible to that client forever.

For the write patterns in this system that is theoretical rather than observed, and the proper fix is a commit-order cursor rather than an update-timestamp one. It is written down as a known hazard with the shape of its solution, and it is not fixed. Recording a hazard you have chosen not to close is worth more than either fixing it prematurely or forgetting it, and it is the only honest thing to do with a problem you cannot currently justify the cost of.