[FEATURE]: Add periodic batch job to clean up old terminal workflow history

The feature, motivation and pitch

Describe the Feature Request

In long-running Conductor deployments, terminal workflows (COMPLETED / FAILED / TERMINATED / TIMED_OUT) accumulate indefinitely in both the execution store (e.g. MySQL workflow / task tables) and the search index (conductor_workflow, conductor_task). Over time this causes:

• Storage bloat and rising query latency on the execution DB
• Larger primary shards and degraded search performance on the index
• Higher operational cost for retaining data well past its usefulness window

Conductor today ships ArchivingWorkflowStatusListener (in conductor-community), which archives a workflow at the moment it reaches a terminal state. That is useful for the "hot path", but it does not address:

1. Backfill — workflows that completed before the listener was enabled, or that were missed due to event delivery failures, are never cleaned up.
2. Retention policy — operators typically want to keep workflows for N days after completion for debugging/audit and then drop them. An event listener can only archive immediately, not on a delay.
3. Idempotency / retry — a failed archive attempt at the listener has no second chance.

I'd like to propose a complementary, opt-in scheduled batch component that periodically deletes terminal workflows older than a configurable retention window, from both the execution store and the search index, using only existing Conductor abstractions.

Related issues / discussions:

• #679 — Audit logging for workflow deletions
• #596 — Delete Workflow Execution History via UI causing duplicated task execution
• #272 — Workflow removal does not remove all children
• Netflix/conductor#1315 — Best practice: cleanup of redis and elasticsearch
• Netflix/conductor#1294 — Elasticsearch rollover & workflow archival

Describe Preferred Solution

Add a scheduled component (e.g. WorkflowHistoryCleaner) that periodically removes terminal workflows older than a configurable retention threshold, with the following properties:

Built on existing abstractions — no new persistence APIs required.

• Discover candidates via IndexDAO#searchArchivableWorkflows(indexName, ttlDays)
• Delete via ExecutionDAOFacade#removeWorkflow(id, archive), which already cleans both the execution store (sync) and the search index (async)

Pluggable distributed locking — depends only on com.netflix.conductor.core.sync.Lock (already used by WorkflowSweeper), so any concrete implementation (Redis / JDBC / ZooKeeper / Local / Noop) works. A separate Lock bean is used so cleanup TTL is independent of the sweeper's lock.

Single-instance-at-a-time execution — Lock.acquireLock with timeToTry=0; runs are skipped (not queued) when another instance holds the lock.

Configurable retention & cadence — Spring @Scheduled cron + retention days + a per-day catch-up window for backfill.

Graceful shutdown — implements LifecycleAwareComponent and re-checks isRunning() between batches; in-flight runs abort cleanly on JVM shutdown.

Disabled by default — conductor.workflow-history-cleanup.enabled=false so existing deployments are unaffected.

Safe iteration — bounded maxIterationsPerDay, a recent-id cache to avoid spinning on async index-delete lag, and a configurable pause between iterations to limit DB pressure.

Proposed properties (illustrative)

Key	Default	Purpose
conductor.workflow-history-cleanup.enabled	false	Master switch
conductor.workflow-history-cleanup.cron	0 0 * * * *	Schedule
conductor.workflow-history-cleanup.retention-days	30	Days after terminal state before deletion
conductor.workflow-history-cleanup.catch-up-days	7	Sliding window to also process older buckets per run (backfill)
conductor.workflow-history-cleanup.lock-lease-time	2h	Cleanup-lock TTL
conductor.workflow-history-cleanup.max-iterations-per-day	100	Safety cap per (day × run)
conductor.workflow-history-cleanup.batch-pause	1s	Pause between iterations

Alternatives

Describe Alternatives

• Index-level TTL only (e.g. OpenSearch ISM / Elasticsearch ILM) — only addresses the search index; the execution store keeps growing.
• Manual cleanup scripts — error-prone, not coordinated across instances, no observability.
• Extending ArchivingWorkflowStatusListener — event-driven; cannot backfill historical data or honor a delayed retention window.
• External cron + API calls — possible, but ties the operator to a specific scheduler, reimplements distributed locking, and loses access to Conductor's internal metrics and lifecycle hooks.

Additional context

No response

Start with ArchivingWorkflowStatusListener in conductor-community and WorkflowSweeper to understand existing lifecycle and locking patterns. Trace IndexDAO#searchArchivableWorkflows and ExecutionDAOFacade#removeWorkflow, then verify the proposed scheduled cleaner can provide opt-in retention, distributed locking, bounded batches, and graceful shutdown without new persistence APIs.