Soft delete

Soft delete means that instead of deleting a record physically from the database, it is marked as deleted instead. Rows that have NULL or our beginning-of-time value (which in SuperOffice is 1.1.1760) are considered active; rows that have a different date are considered deleted. When a contact or person is deleted in SuperOffice it gets the deletedDate set, and when a request is deleted it sets the status of the ticket to Deleted and updates last_changed. This removes it from the UI and puts it in the Recycle Bin. What happens behind the scenes is that when the application code searches for a record in for example the person-table, this code will appendAND (deletedDate IS NULL OR deletedDate = 1.1.1760) to the query (for each table that has soft-delete). As a result, such rows disappear from the results and are never given back to the application - which in practice is the same as deleting them.

​

GDPR

GDPR places limits on keeping irrelevant information, and soft-deleted records are no exception. Therefore, after a time - which will default to 14 days unless specifically set in System preference >> Retain deleted data - a periodic background process will really delete soft-deleted records that are older than the “retention time”. Directly dependent records such as email or phone are deleted. Other tables have independent reasons to exist: a meeting still happened, even if no longer have a valid person reference, so there we just zero the appointment.person_id. For those using the APIs at any level, the interception code will change it into an Update automatically. Search for something through the API, and the filtering conditions will be appended. For those who work directly with the database, it’s recommended switching to an API approach for this reason.

​

Searching for soft-deleted records

In some scenarios it might be useful to search for information which has been soft-deleted. It exists 3 ArchiveProviders, for each of the 3 entitites, for this purpose:

• RecycleContact
• RecyclePerson
• RecycleTicket

​

Database Mirroring and Travel

Database Mirroring and Travel will both replicate the soft delete as the update it really is. Mirroring will also replicate the deep delete that happens later, but Travel will not. The reason is that the deep delete, being a real consistency cleanup, needs to work with whatever is actually in the database. Only the code local to the receiving database can know that, so we use the same strategy as when replicating a Move/Merge operation. The initial operation (update deletedDate) is replicated; and the consequence in the form of a deep delete are recreated on the receiving database, after the proper interval, using whatever data is present at the time.

​

Hard delete

As explained above the soft-delete does not actually remove the records from the database. Physical deletion happens after the retention time, and is described as a hard-delete.

​

Relation Action

The Relation Action describes the type of edit is being done, and what it entails.

​

Hard-delete of Contact

When a hard-delete occurs there is a lot of information that gets edited based on the Relation Action

​

DeleteRecord

​

ZeroForeignKey

​

Ignore

​

Hard-delete of Person

When a hard-delete occurs there is a lot of information that gets edited based on the Relation Action.

​

DeleteRecord

​

ZeroForeignKey

​

Ignore

​

Hard-delete Request

The following tables are affected by a hard-delete of a request

• TICKET
• EJ_MESSAGE
• TICKET_LOG
• TICKET_LOG_ACTION
• TICKET_LOG_CHANGE
• TICKET_CUSTOMERS
• FAVOURITE
• MESSAGE_HEADER
• TICKET_ATTACHMENT
• ATTACHMENT
• INVOICE_ENTRY
• MESSAGE_CUSTOMERS

​

Summary

For the person, contact and ticket tables, a delete through any API becomes an update to a date field; and any select automatically gets conditions that make such rows disappear. A background process will periodically clean up soft-deleted records that are too old. For users, it means that delete operations can be undone. For DBAs, the deep delete means a more consistent database.