I have a system that uses CouchDB as DB, and the clients connect through PouchDB, having a local copy of the database for offline use. The app has no backend API, it connects directly to the DB.
Many databases on the system contain one or more fields that should be unique (no other documents should have the same value). Since CouchDB doesn't really have a "unique costraint" for fields, the uniqueness of the documents is managed through code on the client side. The issue comes from the offline synchronization from PouchDB.
Let's say there is a pages object in the system that has two fields that should be unique, name and slug. Through code we make sure that before posting a new page, those two fields do not exist in the DB. Then let's say one PC goes offline for a day, and creates a page with the slug "homepage", while the same day, a PC that was online created another page with the slug "homepage" now saved on the remote DB. When PC 1 goes online, it will sync the local and remote DBs skipping the validation code and adding a second "homepage" page.
One workaroung to this is to set the must-be-unique field as the _id of the document and manage syncing conflicts, but that is not possible in a reasonable way for more than one unique element. (I would still appreciate a response that only takes into account a single unique field tho).
Also in some cases it is less than ideal to use the _id as the unique field. For example, in a POS system, cashiers have a pin to check in with when taking an order. Using a 4 number pin as _id does not seem ideal.
Another option is to ask an action from the user before syncing when noticing a conflict. But that would require a pre-syncing phase that checks the whole database, and interrupts the user. I'm not sure how to implement it in a seamless way in the system regarding user experience tho.
Any suggestions on how to handle this massive issue?