Implement Soft Delete and Background Hard Delete for Workflow Instances

[sfmskywalker]

Introduce a soft delete mechanism for workflow instances by adding a "deleted" flag, enabling quick UI feedback and reducing backend noise during bulk delete operations. A background job will process these soft-deleted instances in batches to perform the actual hard delete.

Background

Bulk deletion of workflow instances is currently time-consuming due to the deletion of numerous related entities (e.g., blobs). The absence of immediate UI feedback has led users to repeatedly initiate the deletion process, resulting in multiple delete attempts. This cascade generates unnecessary backend errors and warnings when attempting to delete non-existent entities.

Proposed Solution

1. Soft Delete Flag

   • Add a boolean "deleted" flag to the workflow instance record.
   • Add a "deleted at" timestamp as well.
   • When a bulk delete is initiated, update the flag for the targeted records.
   • This operation is fast and allows immediate UI feedback, reducing duplicate delete attempts.

2. UI Feedback

   • Update the UI to display progress (e.g., a brief loading indicator) when the soft delete is performed.
   • This will inform users that the deletion process has been initiated and is in progress.

3. Background Hard Delete Job

   • Develop a recurring background job to process soft-deleted workflow instances.
   • Load soft-deleted instances in manageable batches.
   • For each batch, perform the hard delete of all related entities, including blobs.
   • Ensure the job handles errors gracefully, logging only critical issues.

Benefits

• Performance: Quick flag update ensures rapid UI response.
• User Experience: Clear UI indication prevents duplicate deletion attempts.
• Error Reduction: Eliminates noise from repeated backend errors due to multiple delete attempts.

Considerations

• Batch Size: Determine optimal batch size for the background job to balance performance and system load.
• Error Handling: Implement robust error handling in the background job to ensure consistency and reliability.
• Testing: Thoroughly test the end-to-end process, including UI behavior and background processing, to ensure smooth operation under various conditions.

Acceptance Criteria

• Bulk delete operation updates the "deleted" flag and provides immediate UI feedback.
• Background job successfully hard-deletes workflow instances in batches.
• No duplicate or cascade delete attempts are triggered by the UI.
• Backend logs are cleaned of warnings related to non-existent blob deletions.

Additional Notes

• Ensure that any API or service consumers of the workflow instances are updated to respect the "deleted" flag.
• Review and update documentation as necessary to reflect the new deletion process.
• As an example, when deleting a workflow definition, all of its instances should be soft-deleted to maintain consistency.
• When bookmarks are attempted to be resumed for soft-deleted workflow instances, instead of simply failing, the bookmark resumption logic should instead be able to "see" the workflow instance and notice that it has been soft-deleted. The handling should then simply cease as if the bookmark itself never existed in the first place.