Conflict detection design for DB editing

[zachlipton]

Since multiple clients will be performing DB operations in the future, I believe we need a bit of a locking mechanism to avoid overwriting changes (e.g. if a bot changes something while a human is in the process of editing the same state, the human's changes could overwrite the bot's). I wanted to write up a quick proposal and get feedback. @julia326 @space-buzzer

• When retrieving data to make an edit, clients like the Multi-Edit Tool note the most recent batch ID associated with that data.
  • e.g. CA is to be edited, and the highest batch ID value in states/daily for any CA row is 123. 123 is stored in the spreadsheet as the source version.
• The Multi-Edit Tool continues to enforce it's "each state can only have one editing tab at a time" rule, which prevents human-human conflicts
• When submitting an edit batch to edit_states_daily, the source version can be specified in the context
• (the slightly tricky bit in terms of getting the query right to do this) edit_states_daily checks to see whether a potentially conflicting edit has occurred since the source version and rejects with a conflict error if that's taken place
  • a potentially conflicting edit is the existence of any batch with batch ID > source version that: 1. touches the state being edited, but 2. isn't a regular daily push (edit batches can overlap the pub shift without any issues since those just add new rows)
• If the client gets a conflict error, the user gets an error message that boils down to "sorry, you have to make your edits again."

Having written all that out, I'm also very ok working around this problem with human rules (i.e. nobody runs bots that touch a state while humans are editing that state) or doing that for now and saving the automated solution for another day because I'm not sure this needs to be a launch blocker as long as we're aware of the problem. @space-buzzer can advise on the practicality of that.