Hey, I'm building a Flutter app that works offline and syncs to an ASP.NET Core Web API when connectivity is restored. The app is for a pretty critical use case so I want to get the sync architecture right.
Here's what I'm thinking:
- On the device, pending operations are stored in a local SQLite DB with the intent type, payload, rowVersion, and timestamp
- When the device comes back online, it POSTs all pending ops to a dedicated `/sync` endpoint
- Each operation is dispatched in chronological order — if one conflicts (rowVersion mismatch), the queue stops there and the client gets back a conflict code + the current server rowVersion
A few things I'm not 100% sure about:
Is a dedicated sync endpoint the right call, or is it cleaner to just replay individual requests against existing endpoints?
Is `sp_getapplock` a reasonable mutex here or is there a better pattern for SQL Server?
How are you handling partial queue failures — do you let the user resolve conflicts manually or do you try to auto-merge?
Any experience with this in high-latency / unreliable network environments ?
Would love to hear how others have tackled this, especially if you've dealt with multi-device concurrency on the same record. Thanks