Short answer: for a nightly payment reconciliation cleanup, choose the least complex scheduler that can prove a run happened, retry it safely, and leave an audit trail. Use a database-native job for a small SQL-only deletion in one Postgres database. Use an app cron webhook when the cleanup policy belongs in Python or touches more than the database. Add a queue when recovery from a long or fanned-out run is the real problem.
The word “cheapest” is a trap here. A timer that costs little to start can cost much more to operate after a missed run, a duplicate request, or a partial deletion. The useful question is where recovery state lives.
This is the shape I use for an edtech payment system: a nightly trigger starts reconciliation, the application compares provider records with local records, and cleanup removes only artifacts that are both expired and safe to remove. The scheduler owns the clock. The application owns the policy. The database owns the transaction.
How should pg_cron, app cron, and webhook scheduling handle data cleanup recovery?
Start with a run record before choosing a timer. Give each scheduled attempt an identifier, a planned window, a status, and a cursor or batch marker. A successful HTTP response is not proof that every payment was reconciled; it only proves that the endpoint answered. Conversely, a timeout does not prove that no rows changed. The next attempt must be able to inspect the run record and safely continue.
For a small cleanup that affects one Postgres database, pg_cron can keep the schedule close to the data. A SQL statement can select expired reconciliation artifacts, delete a bounded set, and commit as one database transaction. This is attractive when the rule is genuinely relational and there are no external calls.
The boundary changes as soon as the rule includes provider state, tenant policy, legal holds, object storage, or application-level evaluation records. Those conditions are easier to test beside the Python application than inside a database scheduler. An app cron webhook can wake that code at a fixed time, while the handler records progress in the same system that owns the policy.
Neither choice removes recovery work. A database job can be triggered twice. A webhook can receive a retry after the first request already committed. Design for that fact.
Repeat it.
A small webhook with a bounded cleanup transaction
The example below is intentionally plain. It verifies an HMAC signature, creates a run identifier, claims one bounded batch, and records the result. The HMAC construction follows RFC 2104. In production, the endpoint would sit behind HTTPS and the run table would normally share the application database with the records it protects.
import hashlib
import hmac
import json
import os
import sqlite3
import uuid
from datetime import datetime, timezone
from http.server import BaseHTTPRequestHandler, ThreadingHTTPServer
DATABASE = os.environ.get("CLEANUP_DATABASE", "cleanup.db")
SECRET = os.environ["CLEANUP_WEBHOOK_SECRET"].encode("utf-8")
BATCH_SIZE = 100
def utc_now():
return datetime.now(timezone.utc).isoformat()
def reconcile_cleanup():
run_id = str(uuid.uuid4())
now = utc_now()
with sqlite3.connect(DATABASE) as connection:
connection.execute(
"CREATE TABLE IF NOT EXISTS cleanup_runs "
"(run_id TEXT PRIMARY KEY, started_at TEXT NOT NULL, "
"finished_at TEXT, deleted_count INTEGER NOT NULL DEFAULT 0)"
)
connection.execute(
"CREATE TABLE IF NOT EXISTS payment_artifacts "
"(id INTEGER PRIMARY KEY, expires_at TEXT NOT NULL, "
"reconciled INTEGER NOT NULL DEFAULT 0)"
)
connection.execute(
"INSERT INTO cleanup_runs (run_id, started_at) VALUES (?, ?)",
(run_id, now),
)
rows = connection.execute(
"SELECT id FROM payment_artifacts "
"WHERE expires_at <= ? AND reconciled = 1 "
"ORDER BY id LIMIT ?",
(now, BATCH_SIZE),
).fetchall()
ids = [row[0] for row in rows]
if ids:
marks = ",".join("?" for _ in ids)
connection.execute(
f"DELETE FROM payment_artifacts WHERE id IN ({marks})", ids
)
connection.execute(
"UPDATE cleanup_runs SET finished_at = ?, deleted_count = ? "
"WHERE run_id = ?",
(utc_now(), len(ids), run_id),
)
return run_id, len(ids)
class CleanupHandler(BaseHTTPRequestHandler):
def do_POST(self):
if self.path != "/internal/payment-cleanup":
self.send_error(404)
return
length = int(self.headers.get("Content-Length", "0"))
body = self.rfile.read(length)
supplied = self.headers.get("X-Cleanup-Signature", "")
expected = hmac.new(SECRET, body, hashlib.sha256).hexdigest()
if not hmac.compare_digest(supplied, expected):
self.send_error(401)
return
run_id, deleted = reconcile_cleanup()
payload = json.dumps({"run_id": run_id, "deleted": deleted}).encode()
self.send_response(200)
self.send_header("Content-Type", "application/json")
self.send_header("Content-Length", str(len(payload)))
self.end_headers()
self.wfile.write(payload)
ThreadingHTTPServer(("0.0.0.0", 8080), CleanupHandler).serve_forever()
The reconciled = 1 condition matters more than the timer. A payment artifact is eligible only after reconciliation has established that the local record is safe to remove. The deletion is bounded so a large backlog becomes several observable runs instead of one opaque request. I use 100 rows in this example because the limit is easy to inspect and change; it is an engineering parameter, not a universal setting.
Consider a school that retries a payment near midnight. The provider sends the record after the local reconciliation window has started, the first cleanup request selects the other expired artifacts, and the process is interrupted after its transaction commits. A retry must not infer that the missing record is safe to delete just because its expiry time has passed. The reconciliation flag is the gate: the later run sees the newly reconciled row only if it is eligible, while the run table shows that the earlier attempt finished with a bounded count. If two workers select overlapping batches, the transaction and stable ordering still need a claim strategy appropriate to the real database; the sample keeps that concern visible instead of pretending that a scheduler can solve row ownership. That is why recovery belongs in the data model, not in a dashboard label.
The code also shows what the endpoint must not claim. It returns the count for one transaction, not a promise that the whole dataset is clean. A follow-up run can inspect the remaining eligible rows. If the handler times out after committing, a repeated request creates another run but does not resurrect deleted rows. That is the basic idempotency property this workflow needs.
What belongs in the scheduler, application, and queue?
Keep the scheduler boring.
Its job is to fire at the agreed time and expose enough run history to detect a missing trigger. It should not contain tenant retention rules or payment-provider interpretation.
Put policy in the application: eligibility checks, reconciliation status, legal holds, and the decision to publish follow-up work. This keeps the notebook-to-prod path short. The same Python function can be exercised with fixtures before it is called by an HTTP handler, and an eval harness can assert that protected records never enter the deletion set.
Use a queue when one invocation cannot finish within a bounded transaction or when a school-by-school fan-out would make the webhook too slow. The webhook publishes small messages containing a run identifier, tenant identifier, and cursor. Workers perform idempotent batches and write their outcomes to the run record. A queue with at-least-once delivery requires consumers to tolerate duplicates; that is a normal delivery contract, not an exceptional event.
Pub/Sub is a useful reference model for this separation: publishers send messages, subscribers process them, and the application decides what completion means. The same design can be implemented with a self-hosted queue or another message system. The important part is the durable run state, acknowledgement policy, and replay decision.
There is a cost to every extra boundary. A webhook plus queue adds credentials, monitoring, worker deployment, and a way to reconcile messages with database rows. It is not suitable when the entire job is one local SQL statement and the team has no need for application policy. Stick with the database-native option in that case.
Failure modes worth testing before launch
Test the awkward cases, not only the happy nightly invocation. Send the same signed request twice. End the process after the delete but before the response. Run two workers against the same cursor. Mark a payment as unreconciled while a cleanup batch is being prepared. Then inspect whether the final state is safe and whether the run record explains what happened.
Authentication deserves an explicit test too. Compare the supplied signature with the expected digest using a constant-time comparison, as in the example, and reject a missing or invalid signature. The body being authenticated should be the exact bytes used to calculate the digest; parsing and re-serializing JSON first can change those bytes.
Watch for a different failure: a timer can keep returning success while the job does no useful work. Alert on stale run records, growing eligible-row counts, and a gap in expected run identifiers or windows. Logs should include the run identifier, batch bounds, tenant, row count, and final status. A scheduler’s execution history is a trigger log, not a deletion ledger.
I’m not sure a single retry policy fits every payment provider. Your mileage may vary with rate limits and reconciliation windows, so make backoff and retry ownership explicit in the run state. Never turn a transient response into an unbounded tight loop, and never treat a second invocation as permission to skip the eligibility check.
The decision rule for a nightly reconciliation
Choose pg_cron when the cleanup is local, SQL-only, short, and naturally transactional. Choose an app cron webhook when Python owns the rule or the job crosses service boundaries. Add a queue when the work needs fan-out, independent retries, or bounded worker batches.
The simplest option is the one with the fewest recovery states your team can actually observe. That might be one database transaction. It might be a signed webhook that records a run. It might be a scheduler feeding workers. The right answer is determined by the failure you can explain at 02:00, not by the timer’s label or an advertised price.
Before enabling the nightly schedule, verify the signature path, run-record transitions, bounded selection, duplicate delivery, process interruption, and alert for a missing run. Keep the retention rule under version control with the Python tests. Write down which component owns retry, acknowledgement, and final reconciliation. Then the operational choice remains reversible as the data flow grows.
Top comments (0)