Deletion via Google Sheets

[seasidesparrow]

We need functionality that simplifies the deletion of records via Google Sheets. Handling of this process is table specific and has to be thought out carefully, because many tables use foreign keys on other tables, especially master. If a master record is deleted it may result in deletions to several other tables (names, idents, titlehistory). If a master record is deleted, we have to ensure that

• the master record itself is backed up
• any records with the master record as a foreign key should also be deleted and backed up

The triggering of deletions should be as simple as possible, but not prone to accidental deletion. One way to achieve this is to export tables to Sheets with an additional column for deletion. If a value (not null) is found when reimporting this value, it triggers a deletion in the corresponding postgres table, rather than an upsert.

When rows are written to their corresponding [table]_hist table prior to deletion, we should consider whether to add an additional column to these tables that indicates the record was deleted. Currently you can determine what updates were made to a row by comparing the [table]id in table with the same [table]id in the _hist table, but you can only detect deletions by searching for records in the history that don't have a corresponding entry in [table]. This is awkward, but is also doable.