Modify database tables foreign key relationships so that they use CASCADE DELETE

[travisaustin]

Currently, it's not easy to delete a historical workflow after it is completed. You first have to delete all related rows from the tables that store signals, timers, exceptions, logs, and relationships.

My project has a lot of job (and soon workflow) activity, and I anticipate that I will need to regularly prune the workflows from the database to keep the size at bay. It would be much simpler to implement the pruning process if I modified the foreign key relationships in the database so that deleting a workflow cascades that delete statement to all logs, exceptions, signals, timers, and child workflows. This would allow deleting the parent workflow, and it would clean up all related database entries automatically.

I intend to make this change for my project, but is there any interest in incorporating this into the project as a whole? If so, I'll submit a PR.

[rmcdaniel]

@travisaustin Unfortunately CASCADE DELETE isn't supported by all the databases that Laravel supports. The migrations are published and tracked as part of your project which allows you to add those extra constraints if you need them. Maybe instead we should just add a note in the documentation somewhere?

[travisaustin]

I appreciate (and hadn't considered) your desire to support all Laravel-supported databases.

I know I can add migrations (or edit the default migrations) in my own project. If this is something that others may want, I was considering sharing it with the project for the sake of others.

The question behind my question is how to keep the size of historical workflow entries at bay. I don't care if CASCADE DELETE is added, or if there is another method to clean history. Is this something the project has considered or discussed yet?

[rmcdaniel]

@travisaustin One option is to use prunable models.

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Prunable;
use Workflow\Models\StoredWorkflow as BaseStoredWorkflow;

class StoredWorkflow extends BaseStoredWorkflow
{
    use Prunable;

    public function prunable(): Builder
    {
        return static::where('status', 'completed')
            ->where('created_at', '<=', now()->subMonth())
            ->whereDoesntHave('parents');
    }

    protected function pruning(): void
    {
        $this->recursivePrune($this);
    }

    protected function recursivePrune(StoredWorkflow $workflow): void
    {
        $workflow->children()->each(function ($child) {
            $this->recursivePrune($child);
        });

        $workflow->parents()->detach();
        $workflow->exceptions()->delete();
        $workflow->logs()->delete();
        $workflow->signals()->delete();
        $workflow->timers()->delete();

        if ($workflow->id !== $this->id) {
            $workflow->delete();
        }
    }
}

Here is an example that prunes all workflows and their child workflows 1 month or older.

[travisaustin]

Oh, amazing. I didn't realize Prunable was an available trait (how did I not know that?!).

I'm going to need to retire some more internal code now in favor of this trait in other parts of my project.

You're the best - thanks!