Make a Nightly Job Safe to Run Manually with Idempotency and Locks

TL;DR — A nightly job is safe to run by hand only when duplicate execution is harmless or actively blocked. The single most reliable fix is: give the job a stable run key (usually the business date), acquire a lock for that key, record completion in durable storage, and make every external side effect idempotent against that same key. Reading time: ~7 min

What it is and where it sits

"Safe to run by hand" means an operator can execute the same nightly job outside its scheduler without causing double billing, duplicate emails, corrupted aggregates, or overlapping runs. In practice, this is not one feature; it is a small contract between the scheduler, the job code, the database, and any downstream systems.

In a typical setup, the scheduler used to be the only guardrail: cron, systemd timers, Kubernetes CronJob, CI scheduled pipeline, or a managed scheduler fires once per night and everyone assumes "once" means "exactly once." It does not. Schedulers retry, humans rerun, pods overlap, clocks drift, and jobs partially fail after committing some work.

What replaces that assumption is application-level control:

• A run key that identifies the intended business run, such as 2026-10-01.
• A lock that prevents two workers from processing the same run key concurrently.
• A job_runs table that records started, completed, failed, and metadata.
• Idempotent writes so rerunning the same run key does not create duplicate side effects.
• A manual entrypoint that takes the run key explicitly instead of silently using "now."

Architecture context:

Scheduler / human operator
          |
          v
   job entrypoint --(run_key=2026-10-01)--> lock acquisition
          |                                      |
          |                                      v
          |                               Postgres advisory lock
          v
    read source data ------------------> transform/aggregate
          |                                      |
          v                                      v
   write internal tables                  call external APIs/email
          |                                      |
          +------------ record status <----------+
                         in job_runs

Where it lives in the flow: usually off the request path, but often consuming the same production database and calling the same external systems your app uses. That is why mistakes are expensive: a "simple rerun" can resend invoices, re-export orders, or overwrite snapshots.

How it actually works

Take one realistic example: a nightly invoice-finalization job. Every night at 01:00 UTC, it finds all orders shipped on the previous business date, marks them invoiced, writes invoice rows, and sends them to an accounting API.

The unsafe version does this:

1. Computes target_date = yesterday().
2. Queries uninvoiced orders for that date.
3. Inserts invoices.
4. Calls accounting API.
5. Marks orders invoiced.

If you run it twice, step 3 or 4 can duplicate side effects depending on where the first run failed.

The safe version changes the mechanism.

Step 1: Require an explicit run key

The CLI accepts --date 2026-10-01. The scheduled invocation passes that date too. Manual runs do not get to rely on local timezone or "yesterday" defaults.

./bin/finalize-invoices --date 2026-10-01

If omitted, fail hard:

ERROR: missing required flag --date (expected YYYY-MM-DD)
exit status 2

This sounds small, but it removes a common operator mistake: rerunning at 00:30 local time and accidentally processing the wrong business day.

Step 2: Acquire a lock for that run key

Use a database lock keyed by the job name plus date. In Postgres, pg_try_advisory_lock is a good fit when all runners share the same database.

• If lock acquired: continue.
• If not: print a specific message and exit with a distinct code, commonly 75 (EX_TEMPFAIL) or 3 if your environment prefers app-specific codes.

Typical output:

2026-10-02T01:00:01Z INFO job=finalize_invoices run_key=2026-10-01 acquiring lock
2026-10-02T01:00:01Z ERROR job=finalize_invoices run_key=2026-10-01 lock already held

Shell sees:

echo $?
75

That lets schedulers retry intelligently while telling a human "someone else is already running this exact date."

Step 3: Insert a run record before doing work

Create or upsert a row in job_runs with job_name, run_key, status='started', started_at, triggered_by, and maybe a JSON args column. Put a unique constraint on (job_name, run_key).

Now you have durable state independent of logs. If a host dies mid-run, you can inspect the row and decide whether to rerun.

Step 4: Make internal writes idempotent

For invoice rows, use a natural uniqueness boundary such as (order_id, run_key) or (invoice_number) and write with INSERT ... ON CONFLICT ... DO NOTHING or ... DO UPDATE depending on semantics.

For aggregates, prefer deterministic recomputation for the run key over incremental append. For example, DELETE FROM daily_invoice_totals WHERE business_date = $1; INSERT ... SELECT ... WHERE business_date = $1; inside one transaction is often safer than "add today's delta" logic.

⚠️ If you use delete-and-rebuild, verify the transaction scope first. Running DELETE outside a transaction or against the wrong date can remove production reporting data.

Step 5: Make external side effects idempotent too

The accounting API call must carry the same idempotency key, for example finalize_invoices:2026-10-01:order_12345. If the API supports an Idempotency-Key header, send it. If it does not, persist an outbound ledger locally with a unique key and skip sends already recorded as successful.

Example request shape:

POST /api/invoices
Idempotency-Key: finalize_invoices:2026-10-01:order_12345

If the first run times out after the remote side committed, the rerun should either receive the same successful result or detect from your outbound ledger that this order was already exported.

Step 6: Mark completion only after all required side effects are durable

At the end, update job_runs.status='completed', completed_at, and counts like orders_seen, invoices_created, exports_sent, exports_skipped_existing.

A healthy rerun of an already-completed date should be boring:

2026-10-02T09:12:14Z INFO job=finalize_invoices run_key=2026-10-01 found existing completed run
2026-10-02T09:12:14Z INFO job=finalize_invoices run_key=2026-10-01 no-op; use --force-reconcile to verify downstream state

Exit code should be 0 if your policy treats "already completed" as success. That is usually the least surprising behavior for operators.

When to use it (and when not to)

Use these criteria, not vibes.

You probably do not need the full pattern if the job is both read-only and cheap to rerun, or if its output is a temporary artifact that gets overwritten atomically. Even then, explicit date arguments are still worth it.

Trade-offs

Every safety feature buys something and costs something.

• Run key and explicit CLI args buy reproducibility and operator clarity.
  • Cost: more parameter plumbing, timezone decisions become explicit, and old scripts that assumed "now" need updates.
• Database lock buys overlap protection.
  • Cost: coupling to a shared database; if the process crashes without releasing a non-session-scoped lock design, recovery gets trickier. Postgres advisory locks are session-scoped, which is usually good, but only if all runners use Postgres.
• job_runs table buys observability and auditability.
  • Cost: schema, retention policy, and one more thing to query during incidents.
• Idempotent internal writes buy safe retries.
  • Cost: unique constraints, conflict handling, and careful thought about what "same work" means.
• Idempotent external calls buy the ability to survive timeouts and partial failures.
  • Cost: not all vendors support it; you may need a local outbound ledger and reconciliation tooling.
• Dry-run mode buys confidence for manual operation.
  • Cost: code paths diverge unless you keep dry-run close to real execution; fake confidence is worse than no dry-run.

The main trade-off is complexity versus blast radius. If the job can charge money, notify customers, or alter financial state, the complexity is justified.

In practice

Example 1: Postgres schema and lock acquisition

CREATE TABLE job_runs (
  job_name text NOT NULL,
  run_key date NOT NULL,
  status text NOT NULL CHECK (status IN ('started', 'completed', 'failed')),
  triggered_by text NOT NULL,
  started_at timestamptz NOT NULL DEFAULT now(),
  completed_at timestamptz,
  details jsonb NOT NULL DEFAULT '{}'::jsonb,
  PRIMARY KEY (job_name, run_key)
);

-- Try to acquire a session-scoped advisory lock for this job/date.
-- In app code, hash these strings to int8 consistently.
SELECT pg_try_advisory_lock(hashtextextended('finalize_invoices', 0), hashtextextended('2026-10-01', 0));

This gives you one durable row per intended run and a lock to prevent overlap. Gotcha: advisory locks are tied to the database session; if your connection pool transparently swaps sessions, acquire the lock on a dedicated connection for the whole job.

Example 2: Bash entrypoint with explicit exit codes

#!/usr/bin/env bash
set -euo pipefail

usage() {
  echo "usage: $0 --date YYYY-MM-DD [--dry-run]" >&2
  exit 2
}

RUN_DATE=""
DRY_RUN=0
while [[ $# -gt 0 ]]; do
  case "$1" in
    --date) RUN_DATE="${2:-}"; shift 2 ;;
    --dry-run) DRY_RUN=1; shift ;;
    *) usage ;;
  esac
done

[[ -n "$RUN_DATE" ]] || usage
[[ "$RUN_DATE" =~ ^[0-9]{4}-[0-9]{2}-[0-9]{2}$ ]] || { echo "invalid --date: $RUN_DATE" >&2; exit 2; }

if ! psql "$DATABASE_URL" -Atqc "SELECT pg_try_advisory_lock(hashtextextended('finalize_invoices',0), hashtextextended('$RUN_DATE',0));" | grep -qx t; then
  echo "lock already held for finalize_invoices $RUN_DATE" >&2
  exit 75
fi

exec ./app finalize-invoices --date "$RUN_DATE" ${DRY_RUN:+--dry-run}

This is a minimal wrapper an operator can run from a shell or a scheduler. Gotcha: ${DRY_RUN:+--dry-run} expands to --dry-run whenever DRY_RUN is set and non-empty, including 0; in strict Bash you usually want if [[ "$DRY_RUN" -eq 1 ]]; then ... fi instead.

Example 3: Idempotent invoice insert and outbound ledger

CREATE TABLE invoice_exports (
  order_id bigint NOT NULL,
  run_key date NOT NULL,
  remote_system text NOT NULL,
  idempotency_key text NOT NULL,
  exported_at timestamptz,
  remote_reference text,
  PRIMARY KEY (remote_system, idempotency_key)
);

INSERT INTO invoices (order_id, run_key, amount_cents)
SELECT o.id, $1::date, o.amount_cents
FROM orders o
WHERE o.ship_date = $1::date
ON CONFLICT (order_id, run_key) DO NOTHING;

INSERT INTO invoice_exports (order_id, run_key, remote_system, idempotency_key)
VALUES ($2, $1::date, 'accounting_api', $3)
ON CONFLICT (remote_system, idempotency_key) DO NOTHING;

This separates "we intend to export" from "the remote confirmed export." Gotcha: if you insert the ledger row before the remote call, you need a status column or exported_at nullability to distinguish pending from successful; otherwise retries may incorrectly skip unfinished sends.

A practical manual run sequence looks like this:

./bin/finalize-invoices --date 2026-10-01 --dry-run
./bin/finalize-invoices --date 2026-10-01
psql "$DATABASE_URL" -c "SELECT job_name, run_key, status, details FROM job_runs WHERE job_name='finalize_invoices' AND run_key='2026-10-01';"

Expected output shape:

    job_name      |  run_key   |  status    |                         details
------------------+------------+------------+----------------------------------------------------------
 finalize_invoices| 2026-10-01 | completed  | {"orders_seen":1287,"invoices_created":1287,"exports_sent":1287}
(1 row)

If you see this on rerun:

ERROR: duplicate key value violates unique constraint "job_runs_pkey"
DETAIL: Key (job_name, run_key)=(finalize_invoices, 2026-10-01) already exists.

that is not a reason to remove the constraint. It means your code path is trying to INSERT without handling the existing run row. Fix it with INSERT ... ON CONFLICT ... DO UPDATE or a read-before-write policy.

Further reading

• PostgreSQL Documentation: Advisory Locks
• PostgreSQL Documentation: INSERT ... ON CONFLICT
• systemd.timer and systemd.service man pages
• Kubernetes Documentation: CronJob
• Designing Data-Intensive Applications, chapter on Batch Processing

This article was written by an AI system and published pending human review. Verify anything you intend to act on.