The Cleanup Job Was Correct—and Still Unsafe

The request sounded straightforward:

Delete stale records older than 90 days.

The first query was equally straightforward:

DELETE FROM payment_attempts
WHERE created_at < NOW() - INTERVAL '90 days';

The query followed the instruction exactly.

That was the problem.

In production, “stale” was not a timestamp. Some old records belonged to disputed transactions. Some were required for reconciliation. Some were under audit hold. A correct implementation of an incomplete instruction could destroy evidence at scale.

The job did not need a better prompt.

It needed explicit invariants, bounded authority, operational evidence, and a recovery path.

The Real Requirement

The actual requirement was closer to this:

Remove records that are old, terminal, unrelated to unresolved work, not required for audit, and safe to delete permanently.

That is a different specification.

Words such as stale, safe, unused, and done compress business context. A person may notice the missing context and ask a question. Automation applies the rule consistently, quickly, and across every matching record.

The danger is not ambiguity alone.

The danger is ambiguity combined with authority.

A read-only report can be wrong without destroying data. A scheduled deletion job with production permissions turns the same mistake into an incident.

Start With the Invariant

Before writing the destructive path, define what must remain true even if the selection logic is wrong.

For this workflow:

Records linked to unresolved transactions or audit holds must never be deleted.

That invariant belongs in code and data constraints, not in an operator’s memory.

The first executable step should be selection:

SELECT id, status, audit_hold, transaction_id
FROM payment_attempts
WHERE created_at < NOW() - INTERVAL '90 days'
  AND status IN ('completed', 'cancelled')
  AND audit_hold = false
  AND unresolved_transaction_id IS NULL;

This does not prove the records are safe to delete.

It creates a candidate set the system can inspect and validate.

Separate Selection From Execution

A production cleanup process should have two distinct phases:

identify candidates
  -> validate
  -> report
  -> approve or auto-authorize within policy
  -> delete

The separation makes several controls possible:

• dry-run mode
• candidate sampling
• comparison with previous runs
• policy checks before mutation
• independent review of the selection rule
• cancellation before irreversible execution

A single DELETE statement hides all of those decisions inside one action.

The shorter interface is not safer when the operation is destructive.

Cap the Blast Radius

Even a tested rule will eventually encounter unexpected data.

The job should not be allowed to delete an unlimited candidate set merely because the query returned it.

A bounded policy may look like this:

maximum candidates per run: 1,000
stop if volume exceeds 3x trailing average
stop if any candidate has an unknown status
stop if validation coverage is incomplete

A hard limit changes the failure shape.

Without it, one bad rule can remove millions of records.

With it, the same bad rule becomes a contained event that can be inspected before the next run.

Make Approval Useful

“Human in the loop” is weak when the human sees only a conclusion:

Delete 18,432 stale records?

The system has already framed the records as stale. The reviewer is being asked to approve a label, not assess evidence.

A useful review exposes the abnormal conditions:

Candidate records: 18,432
Previous run: 412
Increase: 4,374%
Excluded: unresolved transactions = 287
Excluded: audit holds = 91
Configured maximum: 1,000
Action: stopped automatically

The human should evaluate the exception, not rubber-stamp the automation’s conclusion.

Preserve Evidence

The job should record enough structured information to answer:

• which rule version ran
• when it ran
• which records qualified
• why each record qualified
• which validation checks passed
• whether a limit stopped execution
• who approved the action
• how many records were changed
• whether verification succeeded

A log line that says cleanup completed is not operational evidence.

The evidence must connect policy, candidates, execution, and result.

Delay Irreversibility

Permanent deletion should be the final step, not the first mutation.

A safer sequence is:

mark candidate
  -> move to quarantine
  -> retain for recovery window
  -> verify no downstream dependency breaks
  -> delete permanently

The recovery window buys time for support cases, reconciliation errors, or anomalous metrics to reveal a mistake.

Reversibility is not inefficiency. It is a production control.

The Same Failure Pattern Appears Elsewhere

The cleanup job is one example of a broader automation risk:

• a retry completes a payment twice
• a deployment script deletes a resource that still contains evidence
• a permission repair grants access more broadly than intended
• an AI coding agent “fixes” failing tests by weakening assertions
• a moderation workflow blocks an entire customer batch because one item is malformed

In each case, the instruction is smaller than the real intent.

The risk grows with speed, scale, persistence, and authority.

Production Checklist

Before an automation can create irreversible effects, I want answers to these questions:

1. What must remain true if the instruction is incomplete?
2. Can selection be inspected separately from execution?
3. Which actions are irreversible?
4. What is the maximum allowed blast radius?
5. Which conditions stop the operation automatically?
6. What evidence remains for investigation?
7. Can one failed unit be retried or restored independently?
8. Does approval expose evidence or only a conclusion?
9. Who owns the response when the automation stops?

The goal is not to make automation timid.

The goal is to let it move fast inside boundaries that keep one mistake from becoming a production incident.