On a mobile device, a set of operations has been saved in the local DB with a wrong date (because the system date was set in the future). Then the device regularly doing synchronisation with a server DB, the erroneous operations have been propagated to the server.Later the wrong date has been detected by the mobile user, and the system date set back correctly, and the user resumed his normal flow of work, pushing even more data to the server.What should be the best strategy to clean the data from this incorrect state. As suggested in Accountants Don't Use Erasers, a wrong entry (invoice for example) should be compensated by a negative corresponding entry (credit note).Then what if the problem is with the date of the entry ? How to compensate a wrong date ?More generally what is the best strategy to rollback a database longtime after data have been committed ? and how to rollback when database is synchronized over multiple systems ?NB : I posted first in stackoverflow.com but I believe here is a more appropriate place.
How can we rollback a database synchronized over multiple systems?
database;web services;sql server;sqlite
First you need to decide how to make this change. Either update all the necessary invoice date fields or cancel the invoice and copy/rebuild all the data with a different date and possibly invoice number.Once you can code making this change, you can then decide on how to apply it to many invoices. If you think the problem is going to continue to stem from mobile app imports, start attaching some sort of Batch ID to all the records imported from each device at each import. Date and time stamp along with any other device and/or user indicator you can include. You'll probably end up with a batch table and a batch id as a foreign key in your invoice table.Ideally, you could just filter a group of invoices out by user and some sort of date range and change them all from Date A to Date B.Someone with domain knowledge needs to get involved in this process on what to do and the ramifications and then some manager gets to make a decision.