Make a scheduled job idempotent
Retries, overlapping runs, and re-runs after a crash all mean the same run can execute more than once. Design every job so running it twice produces the same state as running it once.
Procedure
- Name the unit of work precisely: a day, a tenant, a block range, an order id. The job's effect must be keyed to that unit, not to "whatever is new".
- Derive a deterministic run key from the unit, e.g.
reconcile:2026-10-07 or settle:order:8812, and write it to a job_runs table with a unique constraint before doing the work:
INSERT INTO job_runs(run_key, started_at) VALUES($1, now())
ON CONFLICT (run_key) DO NOTHING RETURNING run_key;
A returned row means you own the run; no row means someone already did it — exit 0.
- Prefer upsert over insert.
INSERT ... ON CONFLICT (id) DO UPDATE makes a re-run overwrite the same row instead of failing on a duplicate key.
- Make the work a pure function of input state: read the source for the unit, compute, write the result. Do not depend on "the last run" for correctness; re-derive from the source.
- Move external side effects (email, payment, webhook) behind the run key too, or record that they fired, so a replay does not send the same invoice twice.
- For batch jobs, process per-unit and commit per-unit. A crash halfway leaves finished units committed; the next run skips them and finishes the rest.
- Record progress, not just success: advance a
last_processed_at watermark inside the same transaction as the data write.
- Handle the "already in progress" case explicitly. If a second copy finds the run key open, it exits 0 quietly rather than erroring into the alert channel.
- Test the property, don't assume it: run the job twice against the same fixture and diff the destination. The diff must be empty.
- For destructive steps (delete, truncate, send), make them keyed and guarded; a re-run must find nothing left to do and say so.
- Log the run key, the unit, and the row count on every run so a replay is auditable against the ledger.
Pitfalls
- A job that does
DELETE ... WHERE date = yesterday then re-inserts; a second run deletes its own output.
- Using "not yet processed" as the query predicate: the second run sees different rows, not the same ones.
- Sending notifications outside the transaction, so a retried run double-emails.
- Assuming the scheduler fires once —
concurrencyPolicy may allow overlap.
- Committing everything at the end of a batch, so a mid-batch crash replays the whole batch.
- Guarding the run in memory (
const seen = new Set()) which vanishes on restart.
Verification
# run the same unit twice and compare the destination
./job.sh --unit 2026-10-07 && ./job.sh --unit 2026-10-07
psql -c "select count(*) from job_runs where run_key='reconcile:2026-10-07'" # 1
# pass: second run exits 0, job_runs has one row, destination checksum unchanged
Report the run key, the statement guarding it, and the empty diff from the double-run test.
1---2name: make-a-scheduled-job-idempotent3description: Use when a job may run twice from retries, overlap, or a manual re-run and doing its work twice would corrupt data or double-charge. Makes each run safe to repeat.4metadata:5 source: https://skillmd.com/skills/jiayaoqijia/make-a-scheduled-job-idempotent6---78# Make a scheduled job idempotent910Retries, overlapping runs, and re-runs after a crash all mean the same run can execute more than once. Design every job so running it twice produces the same state as running it once.1112## Procedure13141. Name the unit of work precisely: a day, a tenant, a block range, an order id. The job's effect must be keyed to that unit, not to "whatever is new".152. Derive a deterministic run key from the unit, e.g. `reconcile:2026-10-07` or `settle:order:8812`, and write it to a `job_runs` table with a unique constraint before doing the work:16 INSERT INTO job_runs(run_key, started_at) VALUES($1, now())17 ON CONFLICT (run_key) DO NOTHING RETURNING run_key;18 A returned row means you own the run; no row means someone already did it — exit 0.193. Prefer upsert over insert. `INSERT ... ON CONFLICT (id) DO UPDATE` makes a re-run overwrite the same row instead of failing on a duplicate key.204. Make the work a pure function of input state: read the source for the unit, compute, write the result. Do not depend on "the last run" for correctness; re-derive from the source.215. Move external side effects (email, payment, webhook) behind the run key too, or record that they fired, so a replay does not send the same invoice twice.226. For batch jobs, process per-unit and commit per-unit. A crash halfway leaves finished units committed; the next run skips them and finishes the rest.237. Record progress, not just success: advance a `last_processed_at` watermark inside the same transaction as the data write.248. Handle the "already in progress" case explicitly. If a second copy finds the run key open, it exits 0 quietly rather than erroring into the alert channel.259. Test the property, don't assume it: run the job twice against the same fixture and diff the destination. The diff must be empty.2610. For destructive steps (delete, truncate, send), make them keyed and guarded; a re-run must find nothing left to do and say so.2711. Log the run key, the unit, and the row count on every run so a replay is auditable against the ledger.2829## Pitfalls3031- A job that does `DELETE ... WHERE date = yesterday` then re-inserts; a second run deletes its own output.32- Using "not yet processed" as the query predicate: the second run sees different rows, not the same ones.33- Sending notifications outside the transaction, so a retried run double-emails.34- Assuming the scheduler fires once — `concurrencyPolicy` may allow overlap.35- Committing everything at the end of a batch, so a mid-batch crash replays the whole batch.36- Guarding the run in memory (`const seen = new Set()`) which vanishes on restart.3738## Verification3940 # run the same unit twice and compare the destination41 ./job.sh --unit 2026-10-07 && ./job.sh --unit 2026-10-0742 psql -c "select count(*) from job_runs where run_key='reconcile:2026-10-07'" # 143 # pass: second run exits 0, job_runs has one row, destination checksum unchanged4445Report the run key, the statement guarding it, and the empty diff from the double-run test.