# Pitfalls of a dead-man's switch (heartbeat monitor) that keeps job heartbeats in Google Sheets, and how to fix them ## Setup - Scheduled jobs (on a PC or in the cloud) send `{job, slot, status: started|ok|failed, summary}` to a GAS Web App. - GAS upserts one row per `job + slot` in a sheet (columns: job, slot, at, status, summary, checks). - A GAS time trigger checks the expected table `[{job, slot, due}]` against the sheet. Anything missing past `due`, left as `started`, or `failed` is posted as an alert. ## Pitfall 1: the sheet turns slot `0930` into the number 930 - The sender writes `'0930'`, but `setValues` / `appendRow` stores it as the number 930. Reading it back gives `'930'`. - The expected table says `'0930'`, so the lookup fails and the job is reported as "missing" even though it sent its heartbeat. - **Fix**: normalize the key on both sides (sender and evaluator) before comparing. Do the same normalization on reads and on the existing-row search inside upsert. ```js function normalizeSlot(slot) { if (slot === null || slot === undefined) return slot; var s = String(slot).trim(); return /^\d{1,4}$/.test(s) ? ('0000' + s).slice(-4) : s; // 930 -> "0930", "daily" stays as is } ``` - An alternative is to set the column to plain text format (`@`). That alone does not fix rows already stored as numbers, so you still need normalization. ## Pitfall 2: duplicate rows from before the fix hide the latest status - While the keys did not match, upsert could not find the existing row and kept appending. The sheet now has several rows for the same job::slot. - If the evaluator does "a later row overwrites an earlier one", an old row further down the sheet hides the newest ok. The job looks failed even after the fix. - **Fix**: (a) for duplicate keys, the evaluator keeps the row with the latest `at`. (b) after each write, upsert deletes the other rows with the same job::slot. Delete from the bottom up so row indices do not shift. ## Pitfall 3: mixing "did the monitor run?" with "is the business healthy?" - If a heartbeat's `status` also counts the following as failure, the monitor stays red every day and people stop reading it: - **Business validity warnings** (for example, "no new orders for 14 days"). These are a state to report, not a monitor failure. - **Expected "could not confirm" by design** (for example, a value is deliberately not used because it cannot be verified, or a channel has no target configured). - **Failures of other jobs** (follow-on review jobs or probes). They have their own heartbeat slots. - **Fix**: record findings in three separate lists: - `issues`: the monitor could not collect its own evidence. Only this sets the heartbeat to `failed`. - `unconfirmed`: not used, by design. Still included in the notification. - `validityWarnings`: the business state. Show it as a separate notification section, and do not change `status`. - Write the same split into the status file the watchdog reads (such as `ok`). If the status file and the heartbeat disagree, nobody can tell which part is broken. ## Pitfall 4: setting retries to zero - A GAS Web App occasionally returns a one-off non-JSON response or `Unauthorized`. With zero retries on read-only calls, one blip causes a whole-run failure (every item becomes unknown). - **Fix**: allow 2–3 retries on read-only calls with backoff. Keep writes at zero retries (idempotency is not guaranteed). ## Pitfall 5: parsing LLM output fenced in ```json - With `stdout.replace(/^```json\s*|\s*```$/g,'').trim()`, a trailing newline after the closing fence stops `$` from matching, and `JSON.parse` fails every time. - Providers that return no fence get through, which hides the bug. - **Fix**: run `trim()` first, then strip the fences. ## Pitfall 6: the scheduled task runs a different checkout - If a nightly job is set up to `fetch` and then `checkout --detach origin/main` in a pinned copy, heartbeat code added to the working tree has no effect at night until it lands on main. - **Fix**: add a wrapper that sends heartbeats before and after the run, and point the task at the wrapper. The computation itself stays the pinned version. ## Verification checklist 1. Have the sender send `0930`. Confirm that the read-back slot is `0930` and that it is not reported as missing. 2. Add a test with duplicate keys in both orders, new→old and old→new, and confirm the newest row wins. 3. Run once with only a business validity warning present. The heartbeat must be `ok` and the warning must still be in the notification. 4. Check that the status file and the heartbeat agree. ---