A productivity app keeps a full local database on each client and syncs deltas with the server. Why would a small team choose this over conventional REST, and how are optimistic updates, conflicts, permission changes and client schema migrations handled?

Why a small team chooses this

The motivation is user-perceived latency, and the argument is that it cannot be achieved any other way.

In a conventional request-response application, every interaction — opening an issue, changing a status, filtering a list — is a network round trip, and the user waits for it. Optimising that gets you to perhaps 100 ms on a good connection, and no amount of backend work makes it instant.

With the data already on the client, every read is local and every write is applied immediately. Interaction becomes instantaneous, offline works by construction, and the network moves off the critical path entirely.

The counterintuitive part is that this is a reasonable choice for a small team. A sync engine is a substantial one-off investment, and afterwards every feature is written against a local database — no endpoint design, no loading states, no cache invalidation, no pagination logic, no request waterfalls. The ongoing per-feature cost falls sharply, which is what makes the trade work at small scale.

Optimistic updates

The write is applied to the local database and rendered immediately, then sent to the server.

• A local mutation log holds unconfirmed changes, so they survive a reload.
• On acknowledgement, the mutation is removed from the log.
• On rejection, the local state must be rolled back and the user informed — and this is the case that is routinely under-built, because rejections are rare in testing and confusing in production.
• Subsequent reads must reflect pending mutations, so the local view is the confirmed state plus the optimistic log rather than either alone.

Conflicts

• Server authority with last-writer-wins per field handles the overwhelming majority. Two people editing different fields of the same record do not conflict at all when the granularity is per field rather than per record — which is the single most valuable design decision here.
• Operation-based sync for genuinely concurrent structures: a counter increment, a list reorder, an assignment. Sending the operation rather than the resulting value makes concurrent changes composable.
• Explicit conflict surfacing for long-form text, where silent last-writer-wins destroys work — the one place a user must be involved.
• A monotonic version per record, so a client can detect that its base was stale.

Permission changes made while offline

The genuinely hard case, and the one that distinguishes a real implementation.

A client holds a full local copy. If access is revoked while it is offline, the data is still on the device, and the client may have made changes to records it no longer has any right to touch.

• The server must re-authorise every mutation on arrival, never trusting that the client had permission when it made the change. The client's local state is a cache, not an authority.
• On reconnection, the server sends revocations and the client deletes the affected local data.
• Changes made to now-forbidden records are rejected, which must be surfaced honestly rather than silently discarded.
• Sync must be scoped to what the user may see, so the delta stream itself carries authorisation — which means the sync protocol is a permission-evaluation problem, not merely a data-transfer one.

Client schema migrations

Client databases persist across app versions, on devices you do not control, and some clients skip several versions or run an old one for months.

• Versioned local schema with forward migrations, run at startup.
• A minimum supported version, below which the client wipes and re-syncs — the escape hatch that must exist, because some migrations are not worth writing.
• Tolerate unknown fields, so a newer server's data does not break an older client.
• Test migrations against real accumulated local state, not a fresh database — the failures live in data that has been through several previous migrations.

When not to do this

When the dataset does not fit on the client. The model requires the user's working set to be local, so it suits bounded per-user data — issues, documents, tasks — and fails for large catalogues, analytics, or anything where the user may query across millions of records they do not own.