limit job attempt history retention

[jrhizor]

Currently we don't limit job attempt retention. This can contribute to problems with archive exporting/importing. See: #4086

Any community input on this is appreciated. I would guess that something like 30 days is sufficient unless someone is using the history for something outside of the UI which we aren't aware of.

[jrhizor]

Maybe keep minimum 1 job that persisted state and minimum last 10 jobs for a connection. Then we can truncate at 30 days maybe?

Unclear if this should happen on a regular basis or only when we're exporting data.

[cgardens]

Some more context. In the output field in the attempts table is the data that is output from any job that is run. One of the fields in there is a state object. This is how a connector knows what data it has already synced so it doesn't have to start from the beginning on the next run.

(much of the below is a restatement of what Jared already mentioned)

I think our goal is to run a regularly recurring job (another thread in the SchedulerApp or just as part of JobRetrier that looks at old jobs in the database. I think these are the rules we should follow for pruning a job:

1. In order to prune a job it must be either > 30 days old OR the connection to which is it attached should contain more than 500 jobs.
2. A job cannot be pruned if it is one of the last 10 jobs for a connection (regardless of age)
3. A job cannot be pruned if it is the last job for a connection to contain a state in its JobOutput

The key here is that we never want to prune jobs such that future jobs can't run with the correct state information. So in the case where a job only runs once a month or something like that, we don't want to accidentally delete its state.

Jobs can output state even if they failed. so for the 3rd criterion we need to actually look into whether state exists or not.