The 2 AM reconciliation script ran for months without incident. On the morning of October 13, the client's finance team sent a screenshot: two summaries for the same day, one entry apart in total.
1. First Suspect: a Bug in the Script
Half an hour into reading the script, the idempotency logic looked fine — a unique key on date plus store ID. Then the execution log showed it: one complete run at 2:00 and another at 2:01. Both finished.
2. The Same Line Pasted Twice in crontab
SSH in, run crontab -l — 30-odd lines on one screen, and lines 8 and 15 were identical. Weeks earlier, a temporary backfill job had been pasted in and never removed; the two runs were exactly 1 minute apart. By the time the second run started, the first batch was already in the database. The unique key blocked duplicate detail rows, but the summary table inserted one row 'per run' — so it gained an extra row.
3. Why the Unique Key Didn't Stop the Summary
The detail table had UNIQUE(date, store_id); the summary table had nothing. It stored the time, row count, and total of each run, designed on the assumption that 'it runs once a day' — an assumption enforced by no constraint at all. We added UNIQUE(run_date).
4. Add a File Lock
One line of flock at the top of the script: if /var/lock/daily_recon.lock can't be acquired, exit immediately and log it. Even if someone pastes a duplicate again, the second process can't get in. The lock file lives in /var/lock and clears on reboot, so it can't deadlock the system.
5. Every Run Starts by Taking a run_id
Now the script's first act is inserting a row into a run_log table and taking the auto-increment id; every subsequent write carries that run_id. When something goes wrong, one query by id shows which steps ran, which tables were written, when it started and when it ended. If we'd had this that morning, there'd have been nothing to guess.
6. crontab Goes into Git
Before, the server's crontab was edited by anyone who ssh'd in and ran crontab -e. Now the repo holds a crontab file; changes go through the deploy script, and the copy on the server is read-only. One diff shows exactly which line appeared.
Those Two Sheets
The client's finance team deleted the duplicate row; nothing else was affected. We logged October 13 in an internal notebook under a four-word title: don't trust defaults.
