Rotate your phone

Once that’s done you’ll be able to experience the FieldMotion website perfectly.

5 Jul 2018

Recovering previously-deleted job data

in Technical Blog by Kae Verens

I had already half-written this article, but when I was about half-way through it, the laptop suddenly turned off (a hardware issue). When I turned it back on, the work I had done already on the article was gone. LibreOffice offered to recover the file. Elation! It recovered a blank document. Meh.

The irony of this is that what I was writing about was how “undeletes” work in Fieldmotion. I’m writing this replacement in Vim, as LibreOffice is overkill for plain text.

So let’s imagine you run a physical shop. Every now and then one of the customers or staff manages to break something, and you need to do a cleanup. This is what we call “toil” in the software world – maintenance that happens today and will probably happen tomorrow as well, and there is little you can do about it but hire the staff to do the cleanup.

In the online world, though, we like to “scale up” our businesses, which means that we want to multiply the number of customers using the software, but we don’t want to multiply the number of staff managing those customers.

One software equivalent of items breaking in the physical store, is when a user or staff member deletes something (a file, or database entry, for example), and realises later that the item was actually needed.

There are three ways a company could respond to this:
1. we simply say that what is done is done. If you broke it, you bought it. If you deleted it, it is gone. This is not good customer service, but it has low admin overhead.
2. we do our best to recover what has been deleted. This involves recreating the file or database item through backups and logs. Not always easy, not always possible, and very heavy on man-hours.
3. we make sure that deleted items can be undeleted. <– this is the one we use

Converting an item so that it can be undeleted is not as simple as it sounds.

If all items were files, then it probably would be – simply move the file into a “trash” area, and record some meta data explaining where the file used to be.

However, databases are not simple files. You cannot simply move a database row into a “trash” area. Databases are very exact things, with precisely defined schemas. The trash area would need to be designed exactly like the original table the item came from. Also, if your database table is auto-incrementing, then “deleting” the highest-numbered row means that in some databases, the next created item gets the number that has just been deleted, meaning that you now have two rows in your database that use the same ID. Not ideal.

The simplest way to delete a file such that it can be undeleted is actually just to add a new field to the database table called “deleted”, which can contain either a 0 or a 1. If the value is 0, then the item is not deleted. If the item is 1, then the system should treat this item as if it is deleted. This is a method called “soft deletion”.

This solution is really easy to understand, but trickier to implement.

Generally, deletion from a database for any particular table happens in only one or two places in your software. You might have one function to handle cases where you are deleting a single row, and another function which handles cases where you are deleting multiple rows. This means there are only two places that you need to convert so that they just update the table row instead of deleting it.

The hard part, though, is that you then need to edit every single other function in your entire system that ever reads from that table, so that it only ever reads rows that have “deleted=0” in them.

In a recent conversion I did, this involved changing one delete function, and then editing more than 180 files that touched the table in some way. Then a day of tests (automated and manual) to confirm that the system treated the deleted item as really having been deleted.

After all of this work, we could safely delete items, knowing that they can be undeleted. But, how?

Well, there are three levels of maintenance that can be done here:
1. the dev team handles it
2. the customer service team handles it
3. the client handles it

We build services in that order. The ideal solution would be that we leap straight to step 3 in all cases, but there’s a lot of work involved in that so we generally stick with level 1 until the toil builds up enough that it’s easier for us to build a tool that the customer service can use. When customer service start complaining that they’re spending too much time using the tool, we then refine it, make it prettier and easier to use, and give it to the customer.

The major advantage to us in that is that we only need to build the refined customer-friendly tools for those items that really do happen quite a lot. Rarely-occuring issues are fine to handle either by hand (by simply updating the database entry so “deleted=1”), or with a rough-and-ready tool the CS team can use (a button saying “undelete last deleted item”, for example), leaving us with more time to work on issues that are higher priority for the client and ourselves.

From a system optimisation point of view, there are advantages and disadvantages to using a “deleted” flag in a table.

Changing all queries so that they include a check of the “deleted” flag means that your tables are no longer optimised, and you will need to change the indexes and keys, to add the deleted flag to them. If you don’t do this, then every single request that you make will involve actually reading the database entries, instead of the optimal case where only the index is read. It is very important that you update your indexes.

Because you are no longer deleting things for real, your database will grow. Our experience is that people generally avoid deleting things, so it will grow slowly, and storage is cheap, so this is not really a disadvantage. However, you will want to set up a cron-managed administration tool which occasionally purges items you have marked as “deleted=1”. This should be done only on items that were deleted more than x days ago (30, say, like how GMail handles their emails), and only deletes items that do not have the highest ID in the database. This means that if you create an item, then mark it deleted, and don’t create any new items for more than x days then the item you marked deleted will not purge until you have created a new item with a higher ID (to avoid possible problems to do with duplicate IDs, or IDs referenced elsewhere).

The most obvious advantage to this method is that you can rest easy, knowing that things that the user has deleted are safely undeletable, and you don’t have to do any digging in archives or recreation from logs if anything happens.

There is a database speed boost as well, because when you delete an item in a database, the database needs to reorder the remaining items, which can take time. If you are just doing a minor update, then the only thing that takes any time is the index update, and that update is very small and quick.