Scheduled jobs: separate a logical run from its attempts

First principles

A nightly export is usually about a particular data window, not about the process that happens to run at midnight. The scheduler may be delayed, the worker may restart, and an operator may rerun the job the next morning. All three attempts can represent the same logical export. If each attempt derives its identity from its own startup time, recovery creates a new business action rather than finishing the old one. Reliable automation starts by naming the intended work independently of its execution history.

Define a logical key from a job name and a canonical scheduled window. Define a separate attempt identifier for logging and diagnosis. The logical key belongs in the system that owns the durable business effect; the attempt identifier belongs in telemetry and operational records. A uniqueness constraint on the logical key can serialize competing attempts. However, a record saying an attempt started is not equivalent to a record saying the export completed. Track state transitions and the location of the committed output.

The schedule's timezone is another part of this contract. Midnight in a region can correspond to different UTC instants over a year. A retry should preserve the selected window rather than recomputing yesterday from the retry machine's current local date. Persist the selected UTC bounds or the business date plus its authoritative zone and resolution policy. The key must capture the actual business partition, particularly when a daily job spans an unusual daylight-saving transition.

Runnable example

This Python 3 example uses SQLite from the standard library. It models a small ledger whose effect and completion record share one transaction. Run it locally; the in-memory database intentionally disappears afterward. The selected window is supplied explicitly by the scheduler instead of inferred from today's clock.

import sqlite3

database = sqlite3.connect(":memory:")
database.executescript("""
CREATE TABLE completed_runs (
    job TEXT NOT NULL,
    window_start TEXT NOT NULL,
    attempt TEXT NOT NULL,
    PRIMARY KEY (job, window_start)
);
CREATE TABLE exports (
    run_key TEXT PRIMARY KEY,
    row_count INTEGER NOT NULL
);
""")

def run_export(window_start, attempt):
    run_key = "invoice-export:" + window_start
    with database:
        inserted = database.execute(
            "INSERT INTO completed_runs VALUES (?, ?, ?) ON CONFLICT DO NOTHING",
            ("invoice-export", window_start, attempt),
        ).rowcount
        if inserted == 0:
            return "already completed"
        database.execute(
            "INSERT INTO exports VALUES (?, ?)", (run_key, 12)
        )
    return "completed"

window = "2026-10-05T00:00:00Z"
assert run_export(window, "attempt-one") == "completed"
assert run_export(window, "attempt-two") == "already completed"
assert database.execute("SELECT COUNT(*) FROM exports").fetchone()[0] == 1
print("one logical export, two attempts")

The record's name is accurate because inserting the effect and completion marker commits atomically. If the effect insert fails, the context manager rolls back the marker as well. This approach would be misleading if it first committed the marker and then uploaded a file to an external destination. A database transaction cannot atomically commit an unrelated remote operation. That boundary needs a durable outbox, destination-side idempotency, or reconciliation of ambiguous outcomes.

Contextual failure case

A billing export runs late after a runner outage. Its script uses the current date to create an output filename and marks that filename as complete. A manual retry after midnight selects a different date, producing overlapping exports while leaving the originally scheduled partition unfinished. Operators see two successful executions and assume recovery worked. The mistake is not cron syntax; it is substituting attempt time for work identity. Include both values in logs so the discrepancy is visible.

Another failure comes from locking with an empty file and never clearing it after a crash. A lock can coordinate live execution, but a stale lock is neither evidence of completion nor a recovery protocol. Lease-based claims require expiry, owner identity, and protection against a former owner continuing after its lease expires. A single-machine demonstration should not pretend that creating a lock file solves multi-host scheduling or guarantees exactly-once delivery.

Verification

The example prints one logical export, two attempts. Make the effect insert fail intentionally and verify no completion row survives. Then rerun with a valid effect and confirm it completes. Use cron inspection to check the intended firing pattern and date calculations to inspect window boundaries with explicit timezone assumptions. Those tools help reason about inputs; they do not execute a job or make an external scheduler reliable.

Exercise

Extend the ledger with an attempts table containing start time, outcome, and logical key. Record two attempts for the same window without creating a second effect. Add a different window and prove it creates a distinct export. Explain how your schema distinguishes never attempted, failed before commit, completed, and remote outcome unknown. Do not treat an unknown external outcome as safely retryable until the destination's duplicate-handling behavior is known.

Safety and caveats

Keep database credentials and exported customer data out of runner logs. A uniqueness key must include tenant or account scope when windows belong to multiple customers. Choose an explicit retention policy for completion records; deleting them too early can make old retries duplicate effects. Backfills need bounded concurrency so recovery does not overwhelm dependencies. Changing schedule timezone is a business change to partition selection, not merely an infrastructure setting, and deserves the same review as changing export logic.