General database deletion strategy

[io7m]

Deletion of content should be undoable for a given duration.

Something like:

CREATE TABLE x (
  data integer not null,
  deleted timestamp with time zone
)

Reads on the table will not return any rows where deleted is non-null. The maintenance service will delete rows where deleted describes a time older than a configurable retention period. Undoing a deletion means setting deleted back to null.

How are foreign key constraints handled?

[io7m]

This is too complex and heavyweight in the general case. Deleting an entry from one table might entail deleting an entry from almost every other table...

[io7m]

Deleting a row from repositories will result in deleting from all of the following transitive dependencies:

• repository_commits
• repository_commit_tags
• repository_commit_branches
• archives
• repository_commit_tags
• assignments
• assignment_executions
• work_items
• work_item_logs
• assignment_execution_logs
• repository_keys

[io7m]

This is going to be addressed case by case. Deleting an agent label, for example, will quietly cascade to a deletion of that label from all associated agents.

Deleting an assignment will quietly cascade to deleting all execution records associated with it.

Deleting a repository, on the other hand, won't cascade and won't be allowed until all of the associated assignments are deleted. There needs to be an API to return a list of the objects referring to a given repository.

[io7m]

In cases where there's a unique identifier that exists on other systems (like agent IDs), the object in question is marked as deleted and internal data is redacted, instead of actually deleting database rows. That prevents the re-use of the identifier whilst keeping the rest of the object effectively invisible.