Writing into a system somebody depends on

Reads forgive, writes do not

Everything up to here has been reading. A bad read wastes a run. A bad write puts wrong data into a system that other people, other automations and next quarter’s report all depend on, and it stays there.

Five habits cover almost all of it.

1. Let the database enforce uniqueness

The tempting pattern is: look for an existing row, and if there is none, insert. It works when you test it and fails in production, because between your SELECT and your INSERT another attempt (a retry, a second worker, the same webhook delivered twice) can run the same check and reach the same conclusion. Both insert. You now have two.

This is a race condition, and no amount of care in application code closes it. The fix is a constraint the database enforces atomically:

ALTER TABLE invoices ADD COLUMN source_ref text;
CREATE UNIQUE INDEX invoices_source_ref_idx ON invoices (source_ref);

INSERT INTO invoices (source_ref, supplier, total_minor, currency)
VALUES ($1, $2, $3, $4)
ON CONFLICT (source_ref) DO UPDATE
  SET total_minor = EXCLUDED.total_minor, updated_at = now();

source_ref is the id from wherever the record came from: the provider’s event id, the message id, a key you generated before the first attempt. Store it, index it uniquely, and duplicates become impossible rather than unlikely.

2. Never blind-update

An automation that writes UPDATE customers SET address = ... WHERE id = 42 will one day overwrite an address a person corrected four minutes earlier, and that person will stop trusting the system permanently. Trust is the expensive thing here; the row is recoverable and the relationship is not.

Use optimistic concurrency: read the row with its updated_at or a version number, and include it in the write.

UPDATE customers SET address = $1, updated_at = now()
WHERE id = $2 AND updated_at = $3;

Zero rows affected means somebody changed it since you looked. That is not an error to swallow: it is a case for review, and it is exactly the case a person should see.

3. Own your columns, and only yours

Decide explicitly which fields the automation writes and which belong to people. Record on every row it touches: which run wrote it, which prompt and model version, and when. Three small columns, and they turn “why does this say that” from an investigation into a query.

Where you can, write into your own columns and let a human decide whether to accept (suggested_category beside category) rather than writing over the field of record. This one change converts most write anxiety into a review queue, and review queues are survivable.

4. Dry run by default, with a cap

The flag should be --apply, off unless given. Without it the job does everything except the writes and prints the diff: fourteen updates, two inserts, one deletion, with before and after for each.

Then put a hard cap in the code. MAX_WRITES = 200; exceeding it aborts the run and alerts, rather than proceeding. A bug that would have rewritten forty thousand rows instead stops at two hundred and tells you, and the difference between those two mornings is enormous. Make the cap a number somebody had to type, not a percentage that scales with the mistake.

5. Keep an undo log

Before each write, append the row id, the before value and the after value to a file: one JSON object per line, one file per run:

{"run":"2026-04-03T02:11Z-8842","table":"customers","id":42,"field":"address","before":"12 Nehru Rd","after":"12 Nehru Road"}

Reversing a bad run is then a script that reads the file backwards. Without it, reversal means restoring a backup and losing everything else that happened since, which in practice means nobody reverses anything and the wrong data stays.

Systems that give you none of this

Spreadsheets, many CRMs and most SaaS APIs offer no transactions, no unique constraints and no optimistic concurrency. A batch of fifty updates can fail at update thirty-one, leaving thirty applied.

Work with it rather than against it. Make each write independently idempotent so re-running the batch is safe. Write an external id into a custom field and search on it before creating. Keep your own ledger of what you have written and reconcile it against the target on a schedule, because the target will not tell you when it has diverged.

And for a Google Sheet specifically: never write into the tab humans edit. Write to a separate tab and let a formula or a person pull it across. A script with edit access to a sheet that thirty people rely on is one bad range away from a very bad afternoon.