I read this article, and if you find any of my explanations obscure, please refer to it. Basically, the idea is that you assign a last_modified field to each document that gets updated with the date it was accessed or modified, whenever you add or edit a document. Whenever the Firestore database is queried, the date is saved on the user's device to lastQueried. You query the Firestore database for documents where the last_modified > lastQueried, and that should return only the documents that have been added or modified. the results of the query are stored in the user's local database.
Now for the issue at hand. Let's say you delete a document from the Firestore database. The user's local database would not be able to detect that deletion unless it reads the whole Firestore collection.
The solution I came up with is to add a deleted or the more professional archived Boolean field to the documents, and this would allow me to remove any documents from the local database with an archived: true. This is not an optimal solution; however, because while it maintains the main theme of "reading cost reduction", the documents would still remain in the database, and this is not great for a database that requires a lot of deletions.
I'm not an expert in databases, but I would assume that this database design is a standard. So, how would you go about this problem? Is my initial design flawed or not optimal for my use case?
Any help would be greatly appreciated.
how would you go about this problem?
You don't really have an alternative to the provided solution. In order to implement this scheme, you must accept that the only way to "delete" a document, and propagate that event to the client, you must use a field to identify as "deleted" instead of actually deleting it.
If you don't want to accept these "tombstone" documents in your database forever, then it's up to you to figure out when to actually delete them, and how to notify all of the relevant client applications that this happened. Otherwise, the clients might become out of sync, thinking that there is a document present when it is not actually present at all.
We can't tell you how often to delete these tombstone documents. That's up to you to decide, based on how much cost and error you are willing to accept.
If you choose to delete the tombstone documents, the only 100% accurate way to get the client back in sync with the database is to have the client periodically query the entire collection and reconcile any missing documents in the results. You will have to accept that the client might not know about some recently deleted documents until this sync happens. At the very least, the client needs a list of document IDs that are not deleted.
Again: much of this is entirely up to you to decide how much error and cost you are willing to accept, and we can't tell you what exactly you should do.