Securing Your Roasting Plans

Today I had two contacts with very different stories connected to the concept of deletion. In one of these, had the company been using Typica it's likely that the problem never would have happened. In the other, a design decision in Typica makes correcting a mistake a little more difficult than it probably should be.

Roast Profile Destruction

The first of these stories involves a roaster quitting and destroying roasting plans on the way out. I'm not going to condone the behavior of that roaster. It was highly unprofessional and likely criminal. But I also have to ask, why was this even possible?

If you add up the time that it takes to design roasting plans across a product line, it's a significant investment. It should be one that pays off when amortized over the amount of coffee sold. While redeveloping a roasting plan should go faster than doing it the first time around simply because you know what the end point should look and taste like, I know that if I lost all of my roasting plans I'd have about a week where most of my work would be recreating those. Even without malicious behavior there are ways that this sort of data can be lost. Because this data is so vital to operating a roasting company, it's important to take steps to mitigate the risk that you will lose it.

The first step is something that you should be able to do regardless of how you're keeping roasting records. Back up your data. If you're storing your roasting plans on paper, periodically make a copy of those and store them securely off site. If you're storing those in files on a computer, make sure those files are part of a regular automated backup. If you're using Typica, PostgreSQL comes with utilities for backing up your database. Learn one and use it. If you're using something that doesn't allow you to create your own backups, you should probably switch to Typica (a good idea anyway), but at the very least you should periodically print out your roasting plans so that if the computers fail you can still roast.

Typica intentionally does not have any built in way to delete roasting data. The legitimate uses for such a function are rarely encountered and it at least raises the level of technical sophistication required for a malicious actor to destroy data. In the worst case, if you followed the advice in the previous paragraph you still have a backup that you can restore which should have all of your current roasting plans. Most of the things that look like deleting data in Typica is really just creating a new transaction in a log, setting a flag, or otherwise doing something that is explicitly not deletion. There's also generally an interface in Typica where that change can be undone. Even if not, since the data still exists it's not hard to undo those changes and get back to business.

It is, however, still possible for someone who is authorized to access the database to connect to it without going through Typica and deleting records that way. That shouldn't affect your backups, but if your roaster has little enough supervision that they can log into the database and start deleting things directly without that being noticed, it might be a good idea to take further steps to secure the data.

One of the pieces of setup that I recommend is not using the database user that's created by default, and instead creating a new role for Typica users. It's possible to reduce what that other role is capable of doing. For example, all profile data is stored in a table called files. If you had a role called typicausers, you could:

This can be repeated for the vast majority of tables that Typica creates and will never perform a DELETE operation on. Other permissions can also be revoked, though if you're doing this it's important to be aware of what Typica will do in normal operation and not revoke permissions that are genuinely required. There are also quite a few options available to provide additional logging on updates that can be used to make it possible roll back malicious data alterations. These are not things that should be done without an expert on site.

Revoking permissions like this shouldn't be needed at most companies, but it's good to know that it's an option. I've been working on another set of features for a future release of Typica that decouples the notions of Typica user from database user and will make it possible to set up Typica in such a way that someone at the roaster cannot gain direct access to the database, further enhancing the security of this vital business data.

Duplicate Entry

The other story involves accidentally entering an invoice twice. This is a case where the lack of a delete function in Typica makes things more annoying, but because Typica stores this data in a PostgreSQL database it's possible to log into the database directly and delete the relevant records.

When deleting entries from a database, it's possible for small typos to have disasterous results. It's a good idea to wrap this sort of destructive operation in a transaction so that if something goes wrong it's possible to roll that back, leaving your data untouched.

In this example, you want to first find the internal invoice ID that you want to delete. If it's the most recent invoice, that's easy.

If it's an older one, you can search by the invoice number that you entered in Typica or the invoice date. Next, you need to delete any transactions that have occurred on items on that invoice. Ideally this is just the purchase transactions.

You'll need to replace that ID with the number that you discovered in the previous SELECT.

Now you can delete the green coffee items that were created. Again, replacing ID with the same number as above:

Now the entries in invoice_items we've been using can be deleted, still replacing ID.

And finally, the entry in invoices can be deleted.

If you do something in any of these DELETE operations like leaving out the WHERE clause and accidentally deleting the entire table, you'll get a response from the server showing more rows deleted than you were expecting. In that case, you can roll back the transaction, start a new one, and try again.

If everything went according to plan, you can commit the transaction. At this point it is no longer possible to undo the change so make sure that you really want to do this.

Now you might notice some skips in internal ID numbers that Typica uses for items and invoices, but otherwise everything will look as if that extra invoice had never been entered. It probably makes sense to allow Typica to delete an invoice if there aren't any transactions beyond the initial purchase on any of the items represented by that invoice, but inventory handling is something that's going to be massively overhauled in Typica 2.0.