Our nightly cleanup job deleted the wrong things. Here's how I'd build it today

Every app eventually gets one: the cleanup job. Delete expired sessions. Purge soft-deleted rows after 30 days. Remove unconfirmed sign-ups. Trim old logs.

It's usually written in an afternoon and scheduled at 3am:

-- cleanup.sql, run nightly by cron DELETE FROM sessions WHERE created_at < now() - interval '30 days'; DELETE FROM users WHERE confirmed = false AND created_at < now() - interval '7 days'; DELETE FROM events WHERE created_at < now() - interval '90 days';

It's the most boring code in the system, and it's the one scheduled job where a bug can't be fixed with a retry. A failed email job sends late. A failed report job reruns. A cleanup job that deletes the wrong thing is just... done.

Here's what I'd do differently, built from the classic ways these jobs go wrong.

1. Never write a bare DELETE in a scheduled job

The users line above looks fine until someone adds an "import users from CSV" feature that creates accounts with confirmed = false by default, for users who log in via a different flow. A week later, the cleanup job quietly removes every imported customer.

Today I'd select first, check, then delete by ID:

def cleanup_unconfirmed_users(db, dry_run=False, max_rows=500): rows = db.fetch_all(""" SELECT id, email, created_at FROM users WHERE confirmed = false AND source = 'self_signup' -- be explicit about WHICH users AND created_at < now() - interval '7 days' LIMIT %s """, [max_rows + 1]) if len(rows) > max_rows: raise RuntimeError( f"Refusing to delete: more than {max_rows} candidates. " "Investigate before raising the limit." ) ids = [r.id for r in rows] log.info("cleanup_unconfirmed_users candidates=%d dry_run=%s", len(ids), dry_run) if dry_run or not ids: return len(ids) db.execute("DELETE FROM users WHERE id = ANY(%s)", [ids]) return len(ids)

Two changes that matter:

An explicit positive condition (source = 'self_signup') instead of "everything that isn't confirmed". Delete what you meant, not "whatever matches".
A circuit breaker. If the job suddenly wants to delete 10x more than usual, something changed. Stop and alert instead.

2. Soft delete first, hard delete later

The safest cleanup is two-stage:

-- Stage 1 (nightly): mark UPDATE users SET pending_purge_at = now() WHERE id = ANY(%s) AND pending_purge_at IS NULL; -- Stage 2 (nightly, separate job): purge what has been marked for 7+ days DELETE FROM users WHERE pending_purge_at < now() - interval '7 days';

Now a bad rule gives you a week to notice and run UPDATE users SET pending_purge_at = NULL WHERE .... Stage 2 can also refuse to run if stage 1 marked an unusual number of rows.

3. Delete in batches, not one giant statement

DELETE FROM events WHERE created_at < now() - interval '90 days';

On a big table, this can hold locks for a long time, bloat the database, fill up replication lag and make the 3am job collide with the 6am traffic. Batch it:

while True: n = db.execute(""" DELETE FROM events WHERE id IN ( SELECT id FROM events WHERE created_at < now() - interval '90 days' ORDER BY id LIMIT 5000 ) """) if n == 0: break time.sleep(0.2) # let replicas and other queries breathe

For very large, time-based tables, partitioning by month and dropping old partitions is usually much better than deleting rows at all.

4. Pin "now" at the start of the run

If the job runs for 40 minutes and every statement calls now() separately (or every batch computes its own cutoff), different parts of the job use different cutoffs. Compute the cutoff once and pass it everywhere:

cutoff = job_scheduled_time - timedelta(days=90) # not "now" at each step

Using the scheduled time rather than the start time also makes reruns deterministic: if the job is retried two hours later, it deletes the same set it would have deleted originally.

5. Log what you deleted, not just that you deleted

"cleanup ok" in a log is useless during an incident. I want:

cleanup_unconfirmed_users cutoff=2026-10-27T03:00Z candidates=37 deleted=37 dry_run=false cleanup_events cutoff=2026-08-05T03:00Z batches=12 deleted=58211

And for anything user-facing, I'd write the deleted IDs (not the data) to an audit table. When support asks "what happened to this account?", you have an answer.

6. Treat retention rules as product decisions, not code details

"Delete unconfirmed users after 7 days" is a business rule. So is "keep invoices for 7 years", which may be a legal requirement. The person writing the cron job shouldn't be the only person who knows these numbers.

Today I'd keep retention rules in one reviewed config file:

retention: sessions: { keep_days: 30 } unconfirmed_users: { keep_days: 7, source: self_signup } events: { keep_days: 90 } invoices: { keep_days: 2555, note: "legal requirement, do not change without review" }

7. Make sure the job actually runs

The opposite failure is just as common: the cleanup job silently stopped months ago. The database grows, privacy promises ("we delete X after 30 days") quietly become false, and nobody notices until disk alerts fire.

Whatever runs it (cron, a cloud scheduler, or a hosted scheduler like delay.run), add a check that it ran and that it found a plausible number of rows. Zero deletions for a month is as suspicious as ten times the normal amount.