ON DELETE triggers to preserve database integrity after admin experimentation

[bitjson]

While this doesn't matter during normal operation, it's currently possible for an administrator of a Chaingraph instance to delete parts of a transaction or block without deleting the whole block. If this happens, Chaingraph can start reporting partially incorrect information. (E.g. if a single output is deleted, transaction.output_count will report one less, encoded_hex will be incorrect such that signatures wouldn't validate, etc.)

When syncing, the Chaingraph agent doesn't attempt to validate the contents of blocks and transactions in the database. If some part of block or transaction has been corrupted, the corruption will never be detected or corrected. This isn't a security issue (the administrator can do whatever they want with their database), but it's potentially a sharp edge for administrators experimenting with SQL queries against a live database.

It would be nice if Chaingraph came with "safety rails" in the form of ON DELETE triggers which refuse to delete portions of either transactions or blocks. So if a user attempted to delete an input, they would need to also delete the full transaction which contains that input. In order to delete a transaction which has already been mined in a block, the containing block must also be deleted.

This way, it's possible to delete data from the database, but very difficult to leave the database in an unrecoverable state. If an unmined transaction is deleted, it may show up in a later block (or it might never matter). If a block is deleted, it can be re-synced correctly when the Chaingraph agent restarts. In neither case will Chaingraph report incorrect information, it will instead simply be missing the entire record.