{"course":{"slug":"agents-and-automation","title":"Agents and Automation","url":"https://addaly.com/learn/agents-and-automation","level":"intermediate","track":"build"},"lesson":{"slug":"writing-back-safely","title":"Writing into a system somebody depends on","url":"https://addaly.com/learn/agents-and-automation/writing-back-safely","position":28,"of":72,"minutes":10,"answer":"Uniqueness must be enforced by a database constraint rather than by checking first and inserting after, because between the check and the insert another attempt can succeed.","keyIdea":"Uniqueness must be enforced by a database constraint rather than by checking first and inserting after, because between the check and the insert another attempt can succeed."},"body":{"format":"markdown","text":"## Reads forgive, writes do not\n\nEverything 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.\n\nFive habits cover almost all of it.\n\n## 1. Let the database enforce uniqueness\n\nThe 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.\n\nThis is a race condition, and no amount of care in application code closes it. The fix is a constraint the database enforces atomically:\n\n```sql\nALTER TABLE invoices ADD COLUMN source_ref text;\nCREATE UNIQUE INDEX invoices_source_ref_idx ON invoices (source_ref);\n\nINSERT INTO invoices (source_ref, supplier, total_minor, currency)\nVALUES ($1, $2, $3, $4)\nON CONFLICT (source_ref) DO UPDATE\n  SET total_minor = EXCLUDED.total_minor, updated_at = now();\n```\n\n`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.\n\n## 2. Never blind-update\n\nAn 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.\n\nUse optimistic concurrency: read the row with its `updated_at` or a version number, and include it in the write.\n\n```sql\nUPDATE customers SET address = $1, updated_at = now()\nWHERE id = $2 AND updated_at = $3;\n```\n\nZero 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.\n\n## 3. Own your columns, and only yours\n\nDecide 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.\n\nWhere 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.\n\n## 4. Dry run by default, with a cap\n\nThe 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.\n\nThen 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.\n\n## 5. Keep an undo log\n\nBefore 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:\n\n```json\n{\"run\":\"2026-04-03T02:11Z-8842\",\"table\":\"customers\",\"id\":42,\"field\":\"address\",\"before\":\"12 Nehru Rd\",\"after\":\"12 Nehru Road\"}\n```\n\nReversing 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.\n\n## Systems that give you none of this\n\nSpreadsheets, 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.\n\nWork 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.\n\nAnd 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."},"check":{"question":"A webhook handler checks whether an order already exists and inserts it if not. Under load, duplicate orders appear. What is the mechanism, and what actually removes it?","options":[{"text":"Two concurrent attempts can both pass the existence check before either inserts; only a unique constraint enforced by the database closes the gap.","correct":true},{"text":"The webhook provider is delivering the same event twice, so the handler should deduplicate on the event id in memory.","correct":false,"tempting":"Duplicate delivery is the usual trigger, but an in-memory set does not help across processes or restarts: the gap between check and insert is still open."},{"text":"The check is reading from a replica that lags behind the primary, so a fresher read fixes it.","correct":false,"tempting":"Replica lag widens the window but is not required to produce it; the race exists even when every read hits the primary."},{"text":"The handler needs a lock around the check-and-insert so only one request can run it at a time.","correct":false,"tempting":"An application lock helps within one process but has to be distributed to be correct, which is a database constraint with extra steps and more ways to fail."}]},"prev":{"slug":"embeddings-in-practice","title":"What an embedding actually is","url":"https://addaly.com/learn/agents-and-automation/embeddings-in-practice"},"next":{"slug":"how-agents-fail","title":"The six ways a run goes wrong","url":"https://addaly.com/learn/agents-and-automation/how-agents-fail"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'Writing into a system somebody depends on', in Agents and Automation. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/agents-and-automation/writing-back-safely (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"Writing into a system somebody depends on","courseTitle":"Agents and Automation","module":{"index":3,"slug":"data-plumbing","title":"The data underneath: sources, documents, records"},"answer":null,"keyIdea":"Uniqueness must be enforced by a database constraint rather than by checking first and inserting after, because between the check and the insert another attempt can succeed.","hero":{"kind":"plate","image":null,"alt":null,"caption":null},"blocks":[{"t":"h2","id":"reads-forgive-writes-do-not","runs":[{"text":"Reads forgive, writes do not"}]},{"t":"p","runs":[{"text":"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."}]},{"t":"p","runs":[{"text":"Five habits cover almost all of it."}]},{"t":"h2","id":"1-let-the-database-enforce-uniqueness","runs":[{"text":"1. Let the database enforce uniqueness"}]},{"t":"p","runs":[{"text":"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."}]},{"t":"p","runs":[{"text":"This is a race condition, and no amount of care in application code closes it. The fix is a constraint the database enforces atomically:"}]},{"t":"code","lang":"sql","label":"SQL","text":"ALTER TABLE invoices ADD COLUMN source_ref text;\nCREATE UNIQUE INDEX invoices_source_ref_idx ON invoices (source_ref);\n\nINSERT INTO invoices (source_ref, supplier, total_minor, currency)\nVALUES ($1, $2, $3, $4)\nON CONFLICT (source_ref) DO UPDATE\n  SET total_minor = EXCLUDED.total_minor, updated_at = now();"},{"t":"p","runs":[{"text":"source_ref","code":true},{"text":" 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."}]},{"t":"h2","id":"2-never-blind-update","runs":[{"text":"2. Never blind-update"}]},{"t":"p","runs":[{"text":"An automation that writes "},{"text":"UPDATE customers SET address = ... WHERE id = 42","code":true},{"text":" 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."}]},{"t":"p","runs":[{"text":"Use optimistic concurrency: read the row with its "},{"text":"updated_at","code":true},{"text":" or a version number, and include it in the write."}]},{"t":"code","lang":"sql","label":"SQL","text":"UPDATE customers SET address = $1, updated_at = now()\nWHERE id = $2 AND updated_at = $3;"},{"t":"p","runs":[{"text":"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."}]},{"t":"h2","id":"3-own-your-columns-and-only-yours","runs":[{"text":"3. Own your columns, and only yours"}]},{"t":"p","runs":[{"text":"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."}]},{"t":"p","runs":[{"text":"Where you can, write into your own columns and let a human decide whether to accept ("},{"text":"suggested_category","code":true},{"text":" beside "},{"text":"category","code":true},{"text":") rather than writing over the field of record. This one change converts most write anxiety into a review queue, and review queues are survivable."}]},{"t":"h2","id":"4-dry-run-by-default-with-a-cap","runs":[{"text":"4. Dry run by default, with a cap"}]},{"t":"p","runs":[{"text":"The flag should be "},{"text":"--apply","code":true},{"text":", 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."}]},{"t":"p","runs":[{"text":"Then put a hard cap in the code. "},{"text":"MAX_WRITES = 200","code":true},{"text":"; 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."}]},{"t":"h2","id":"5-keep-an-undo-log","runs":[{"text":"5. Keep an undo log"}]},{"t":"p","runs":[{"text":"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:"}]},{"t":"code","lang":"json","label":"JSON","text":"{\"run\":\"2026-04-03T02:11Z-8842\",\"table\":\"customers\",\"id\":42,\"field\":\"address\",\"before\":\"12 Nehru Rd\",\"after\":\"12 Nehru Road\"}"},{"t":"p","runs":[{"text":"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."}]},{"t":"h2","id":"systems-that-give-you-none-of-this","runs":[{"text":"Systems that give you none of this"}]},{"t":"p","runs":[{"text":"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."}]},{"t":"p","runs":[{"text":"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."}]},{"t":"p","runs":[{"text":"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."}]}],"check":{"question":[{"text":"A webhook handler checks whether an order already exists and inserts it if not. Under load, duplicate orders appear. What is the mechanism, and what actually removes it?"}],"options":[{"text":[{"text":"Two concurrent attempts can both pass the existence check before either inserts; only a unique constraint enforced by the database closes the gap."}],"correct":true,"tempting":null},{"text":[{"text":"The webhook provider is delivering the same event twice, so the handler should deduplicate on the event id in memory."}],"correct":false,"tempting":[{"text":"Duplicate delivery is the usual trigger, but an in-memory set does not help across processes or restarts: the gap between check and insert is still open."}]},{"text":[{"text":"The check is reading from a replica that lags behind the primary, so a fresher read fixes it."}],"correct":false,"tempting":[{"text":"Replica lag widens the window but is not required to produce it; the race exists even when every read hits the primary."}]},{"text":[{"text":"The handler needs a lock around the check-and-insert so only one request can run it at a time."}],"correct":false,"tempting":[{"text":"An application lock helps within one process but has to be distributed to be correct, which is a database constraint with extra steps and more ways to fail."}]}],"order":[1,3,2,0],"why":null},"prev":{"slug":"embeddings-in-practice","title":"What an embedding actually is","minutes":10},"next":{"slug":"how-agents-fail","title":"The six ways a run goes wrong","minutes":9}}}