Soft-deleted workflows: never purged, and the name-uniqueness constraint that traps us

Summary

Merging a sandbox soft-deletes workflows on the target project. Two long-standing
gaps turn that into a class of merge-blocking bugs:

1. Soft-deleted workflows are never purged. There are purge workers for
   accounts, credentials, projects and webhook-auth-methods, but none for
   workflows (confirmed — no entry in the cleanup cron). As far as @stuartc and
   @elias-ba can tell (both predate Lightning), this was never a deliberate decision
   — it was simply never implemented. Result: ~900 soft-deleted workflows sitting in
   the prod DB indefinitely. Note this isn't just "the worker is missing": the other
   purgeable entities carry a scheduled_deletion timestamp that drives the purge,
   whereas workflows only have deleted_at — there is no scheduling field to even
   hang a purge off. So this is a half-built deletion model, not a missing cron line.
2. Legacy soft-deletes were never renamed. Those ~900 rows still hold their
   original names. A soft-deleted-but-still-named workflow is invisible in the UI but
   continues to occupy its name under the per-project uniqueness constraint. So when a
   sandbox containing a workflow of the same name is merged, the merge's own
   soft-delete collides with the hidden row and the system refuses to create the
   workflow — surfacing as a confusing validation error the user can't diagnose
   ("can't merge your workflow") with no visible cause.

The going-forward collision is already closed (see What's already handled below).
What this issue is for is the data left behind and the root coupling that made
it possible.

What's already handled (context, not scope)

• Rename-on-delete through the merge path — fixed in @elias-ba's #4842 (stacked on
  @josephjclark's merge PR #4835). Worth being precise: the standard delete path
  (Workflows.mark_for_deletion) already renames on soft-delete, suffixing _del,
  _del1, _del2, … (no hyphen). The bug was that the merge path soft-deleted
  without going through that rename, so #4842 brings merge into line with the path that
  already did the right thing — it's closing a gap, not inventing the suffix. This closes
  the collision class for all future deletes.
• Backfilling the ~900 legacy rows — deliberately not done. We rejected a
  migration that walks the table appending _del (#4844, now closed): it's a "lazy
  janitor" we'd be canonising in code, for an event that's now very rare (you'd need a
  name that exactly matches a legacy soft-deleted-but-unrenamed row). If it ever bites a
  production install, a one-off SQL update fixes it in minutes. The branch is kept.

The actual problem this issue captures

1. Workflows are never purged — and purging isn't free

There's no purge worker for soft-deleted workflows, and (per the Summary) no
scheduled_deletion field to drive one. The obvious fix ("add the Oban worker like
credentials/projects") is not straightforward, for two reasons:

• History cascade (@doc-han's catch, refined by the snapshot system). A soft-deleted
  workflow still appears in history — you can open it and view its past runs (you
  just can't act on it; manual runs are blocked with :workflow_deleted and its
  triggers are disabled). Snapshots preserve each run's job/trigger definitions, which
  is what lets you delete individual jobs without breaking old runs' rendering — but
  hard-deleting the workflow row cascades (on_delete: :delete_all) through work
  orders → runs → snapshots. So purging the workflow really does destroy its run history.
• Usage metrics depend on the rows — but in a bounded way. Lightning's
  usage_tracking generates a daily snapshot per report_date by re-querying live
  workflow rows, filtering deleted_at against that date, and POSTs it to the Impact
  Tracker (the public-dashboard service at https://impact.openfn.org/api/metrics).
  Once a day's report is generated it's frozen as JSON in usage_tracking_reports, so
  hard-deleting rows cannot corrupt already-submitted snapshots. The exposure is only
  to report dates not yet generated — the daily catch-up backlog and any
  resubmission/backfill of older dates — which would under-count if the rows are gone.
  This may be the real (if accidental) reason nobody ever purged. (Note: the commercial
  billing usage uses incremental counters, not row queries, so billing is unaffected by
  purging — it's only this Impact-Tracker feed that touches rows.)

So before we can purge, we have to answer a product question:

Do we want deleted workflows' runs to be explorable indefinitely? If yes, we
can't hard-delete the workflow without a different home for the history (and for the
usage numbers). If no (or after a retention window), purging becomes viable.

Clearer sub-case where there's no tension:

• Sandboxes should be hard-deleted. A merged/deleted sandbox is a whole project
  going away — there's no history worth preserving. These can and should be
  hard-deleted outright, not soft-deleted-and-retained.

2. The root coupling: one uniqueness constraint doing too much

The per-project workflow-name uniqueness constraint is ~4 years old. It's not wrong in
itself, but nothing was added around it, so it has quietly become load-bearing for
several unrelated things:

• Merge correctness (the collisions above)
• Impact-tracker counts (which is also why we don't want to casually delete rows)

That coupling is what corners us into rename hacks (_del) instead of just removing
dead data. We keep making decisions that all hinge on this single constraint.

The goal of this issue is to decide the ideal before implementing anything. Some
directions floated (not yet chosen — listing trade-offs, not picking):

• Uniqueness index ignores soft-deleted rows — concretely, make the current full
  unique_index(:workflows, [:name, :project_id]) a partial index with
  WHERE deleted_at IS NULL. This removes the need to rename on delete entirely — but
  then recovery (a by-hand, human process today) has to handle the case where the freed
  name was reused.
• Uniqueness excludes sandbox-derived rows — projects already have a parent_id
  (sandboxes are child projects), so the index could exclude rows belonging to a child
  project. Note the constraint is currently scoped to a single project and is blind to
  the parent/child hierarchy.
• A dedicated column to scope/partition uniqueness explicitly rather than
  inferring it.

The recovery angle is the constraint on all of these: we have no instant "undelete"
feature, so whatever we choose, restoring a workflow must not require undoing a rename
and resolving a fresh conflict by hand.

3. Adjacent smell — usage/impact metrics count live rows

Flagged for the record, probably a separate issue but related to "why we're scared to
delete rows". Two distinct systems here:

• Impact Tracker — a separate, external service (public dashboard at
  impact.openfn.org) that receives daily usage snapshots. Lightning's usage_tracking
  generates each day's snapshot by querying the operational tables at submission time
  (filtering deleted_at per date) and POSTs it. That's the entanglement with row
  deletion (see §1). The right shape long-term is event-based / telemetry tickers
  (anonymised increments) that don't depend on operational rows still existing.
• Billing usage (commercial layer) — uses incremental counters, not row counts,
  so it's already the right shape and is unaffected by purging. Not a concern here; noted
  only to keep the two from being confused.

Proposed shape of the work

1. Decide the retention/history policy for deleted workflows (product call):
   indefinitely-explorable runs, a retention window, or not at all.
2. Hard-delete sandboxes on merge/delete (no history to keep).
3. Decouple name uniqueness from the unrelated concerns it's currently carrying —
   pick one of the directions above based on (1) and the recovery constraint.
4. Purge worker for soft-deleted workflows, once (1) makes it safe. This also means
   giving workflows a scheduled_deletion-style field (or equivalent) — they only have
   deleted_at today — and respecting the existing global PURGE_DELETED_AFTER_DAYS
   gate (default 7 days; 0 disables purging entirely). The other purge workers use that
   window as a "save-our-ass" grace period.
5. Clean up the ~900 legacy rows as a one-off (manual SQL), not a committed
   migration.
6. (Possibly split out) move usage/impact metrics onto event/telemetry signals so
   workflow rows stop being load-bearing for billing-adjacent numbers (confirm the
   Impact-Tracker dependency window first — see §1).

Open questions

• What's the retention policy for deleted-workflow run history? (blocks everything else —
  and now also implicates historical usage metrics, not just UI history)
• Is there any reason we've kept workflows forever that we've forgotten? (@stuartc /
  @elias-ba couldn't think of one; the strongest candidates from the code are @doc-han's
  history-visibility point and usage-tracking's dependence on the rows — §1.)
• Which uniqueness-decoupling approach, given we have no instant undelete? (Partial index
  is the cheapest and removes the rename hack entirely — does recovery survive it?)

---

Verified against code (main, 2026-06-10)

• Workflow soft-delete + rename: lib/lightning/workflows.ex — mark_for_deletion/3
  (sets deleted_at, disables triggers) and resolve_name_for_pending_deletion/1
  (suffix _del, _del1, _del2, …).
• deleted_at field: lib/lightning/workflows/workflow.ex (workflows). Projects and
  credentials instead use scheduled_deletion (lib/lightning/projects/project.ex,
  lib/lightning/credentials/credential.ex). Jobs/Triggers/Runs/WorkOrders have neither.
• Uniqueness index (full, not partial):
  priv/repo/migrations/20220905153252_add_project_id_to_workflows.exs —
  create unique_index(:workflows, [:name, :project_id]).
• Sandbox parent link: lib/lightning/projects/project.ex — belongs_to :parent,
  has_many :sandboxes (foreign key parent_id).
• Purge cron + retention: lib/lightning/config/bootstrap.ex — cleanup cron has
  purge workers for Accounts / Credentials / Projects / WebhookAuthMethods, none for
  workflows; gated on PURGE_DELETED_AFTER_DAYS (default 7, 0 disables all purging).
  Worker bodies: lib/lightning/projects.ex, lib/lightning/credentials.ex
  (purge when scheduled_deletion <= now; credentials only if no run activity).
• History cascade on hard-delete: migrations set on_delete: :delete_all for jobs,
  triggers, edges, work orders, snapshots; runs cascade via work order. Snapshots
  (lib/lightning/runs/run.ex, belongs_to :snapshot) preserve definitions so jobs can
  be deleted without breaking old runs — but the workflow row's deletion still cascades.
• Manual run blocked on deleted workflow: lib/lightning/work_orders.ex —
  {:error, :workflow_deleted} when manual.workflow.deleted_at is set.
• Usage metrics → Impact Tracker: lib/lightning/usage_tracking/ —
  workflow_metrics_service.ex eligible_workflow?/2 filters on deleted_at per date;
  report_data.ex builds the daily snapshot (instance → projects → workflows with
  no_of_jobs/runs/steps/active_jobs, IDs hashed); client.ex POSTs to /api/metrics;
  snapshots stored frozen in usage_tracking_reports (report.ex). Endpoint configured
  via USAGE_TRACKER_HOST (default https://impact.openfn.org) in
  config/bootstrap.ex. The Impact Tracker itself (the public dashboard receiving
  these POSTs) is a separate external service, not in this repo.