A Version Check Won't Recover a Lost Response

A developer published a Python/SQLite reference implementation showing how to make tool-call retries idempotent by storing a receipt keyed on workspace, principal, tool revision and request_id alongside each committed row change. The handler checks the receipt before the optimistic version condition, so a retry whose original response was lost recovers the committed result instead of failing a stale-version check, while current access checks still run first. The example ships with 12 tests and is explicitly a local demonstration, not an MCP transport or production code.

Both attempts used version 1. The first changed the row to version 2. Its response never reached the client. The retry now fails the version check. That is a useful refusal: the old request cannot overwrite another edit. But it doesn't answer the client's question: did this operation succeed? A conflict alone cannot identify which request produced the newer version. The previous example https://dev.to/smtasker-official/stop-stale-tool-calls-from-overwriting-newer-changes-1bjo put an expected version inside the database write. This follow-up adds a saved result for one intended operation. It keeps the version check for new operations and recovers the original result for recognized retries. The example is a local Python/SQLite handler with 12 tests. It does not implement an MCP transport or describe SMTasker's production code. Consider a hypothetical tool that changes an automation's interval: client saves request id = edit-42 client sends: id=1, expected version=1, interval minutes=30 server commits: interval=30, version=2 response is lost client resends the same request id and the same arguments The second attempt should recover the result of edit-42 . It should not create a new intent, repeat the write, or quietly change expected version to whatever the database contains now. An expected version and a request identifier solve different problems: | Value | Question it answers | |---|---| | expected version | Is this new edit based on the version the caller intended? | | request id | Have we already committed this caller's intended operation? | Neither value grants permission. The handler still checks the trusted principal and the resource's workspace. The handler stores a small receipt alongside the row change: php workspace, principal, tool revision, request id - validated arguments + committed result The caller creates and persists the request ID before the first send . Retries reuse it. Two genuinely separate edits get different IDs, even if their arguments happen to match. The service binds that key to a narrow, validated payload. Reusing a key with a different target, expected version or interval is an error. Sorting these three integer fields is sufficient for this example; it is not a general JSON normalization scheme. For an authorized, accessible resource, the transaction does this: The receipt lookup comes before the version condition. A successful original request has already advanced the version, so testing the old version first would reject the very retry we want to recognize. Current access checks come before the receipt lookup. Moving the record to another workspace or deleting it causes a refusal, even if an old receipt remains. This policy prioritizes current access over replay availability. A real service should choose and document its own equivalent policy. Save the following as retry receipt.py and run: python retry receipt.py It requires Python 3.12+ and SQLite 3.35+ for RETURNING . It uses the standard library and temporary databases beneath a local test-db directory. The interval range is an illustrative domain rule, not a product limit. Principal represents trusted request context. Authentication code must supply the actual caller, workspace and current permission decision. This example does not validate tokens, resolve project membership, or implement revocation propagation. """Local SQLite demonstration, not an MCP server or a product implementation. Run with Python 3.12+ and SQLite 3.35+: python retry receipt.py Only temporary databases beneath this file's test-db directory are used. """ from concurrent.futures import ThreadPoolExecutor from contextlib import closing from dataclasses import dataclass, replace import json from pathlib import Path import re import sqlite3 import sys import tempfile from threading import Barrier import unittest TOOL = "set interval.v1" @dataclass frozen=True class Principal: Created by trusted authentication/authorization code, never tool arguments. user id: str workspace id: str scopes: frozenset str @dataclass frozen=True class Reply: result: dict replayed: bool class Refused Exception : pass class KeyReuse Exception : pass def connect path : db = sqlite3.connect path, timeout=5, autocommit=True db.row factory = sqlite3.Row return db def one db, sql, params= : cursor = db.execute sql, params try: return cursor.fetchone finally: cursor.close def set interval path, principal, request id, automation id, expected version, interval minutes : if "automation:write" not in principal.scopes: raise Refused "write not permitted" if not isinstance request id, str or not re.fullmatch r" A-Za-z0-9 - {1,64}", request id : raise ValueError "request id must be 1-64 letters, digits, or -" if any type v is not int or v < 1 for v in automation id, expected version : raise ValueError "id and version must be positive integers" if type interval minutes is not int or not 5 <= interval minutes <= 1440: raise ValueError "illustrative interval range is 5-1440 minutes" This narrow schema has three integer fields. No arbitrary-JSON hashing. payload = json.dumps {"id": automation id, "version": expected version, "interval": interval minutes}, sort keys=True, separators= ",", ":" key = principal.workspace id, principal.user id, TOOL, request id with closing connect path as db: try: SQLite serializes writers, including duplicate first attempts. db.execute "BEGIN IMMEDIATE" An old receipt does not grant current access to a resource. if one db, "SELECT 1 FROM automations WHERE id=? AND workspace id=?", automation id, principal.workspace id is None: raise Refused "resource unavailable" old = one db, """SELECT payload, response FROM receipts WHERE workspace id=? AND user id=? AND tool=? AND request id=?""", key if old is not None: if old "payload" = payload: raise KeyReuse "request id reused with different intent" result = json.loads old "response" db.execute "COMMIT" return Reply result, replayed=True row = one db, """UPDATE automations SET interval minutes=?, version=version+1 WHERE id=? AND workspace id=? AND version=? RETURNING id, interval minutes, version""", interval minutes, automation id, principal.workspace id, expected version if row is None: raise Refused "version conflict; reload before a new intent" result = {"id": row "id" , "interval minutes": row "interval minutes" , "version": row "version" } db.execute "INSERT INTO receipts VALUES ?,?,?,?,?,? ", key, payload, json.dumps result, sort keys=True The row change and the receipt become visible together. db.execute "COMMIT" return Reply result, replayed=False except BaseException: if db.in transaction: db.execute "ROLLBACK" raise SCHEMA = """ CREATE TABLE automations id INTEGER PRIMARY KEY, workspace id TEXT NOT NULL, interval minutes INTEGER NOT NULL, version INTEGER NOT NULL ; CREATE TABLE receipts workspace id TEXT NOT NULL, user id TEXT NOT NULL, tool TEXT NOT NULL, request id TEXT NOT NULL, payload TEXT NOT NULL, response TEXT NOT NULL, PRIMARY KEY workspace id,user id,tool,request id ; INSERT INTO automations VALUES 1,'team-a',15,1 , 2,'team-a',15,1 , 20,'team-b',15,1 ; """ ALICE = Principal "alice", "team-a", frozenset {"automation:write"} class ReceiptTests unittest.TestCase : def setUp self : base = Path file .resolve .parent / "test-db" base.mkdir exist ok=True self.temp = tempfile.TemporaryDirectory prefix="receipt-", dir=base assert Path self.temp.name .resolve .is relative to base.resolve self.addCleanup self.temp.cleanup self.path = Path self.temp.name / "demo.sqlite" with closing connect self.path as db: db.executescript SCHEMA def query self, sql, params= : with closing connect self.path as db: cursor = db.execute sql, params try: return tuple row for row in cursor.fetchall finally: cursor.close def call self, key="edit-42", , who=ALICE, target=1, version=1, minutes=30 : return set interval self.path, who, key, target, version, minutes def test version only control cannot recover its own result self : sql = """UPDATE automations SET interval minutes=30,version=version+1 WHERE id=1 AND workspace id='team-a' AND version=1 RETURNING version""" self.assertEqual self.query sql , 2, self.assertEqual self.query sql , same retry now conflicts self.assertEqual self.query "SELECT count FROM receipts" , 0, def test commit then lost response replays from fresh connection self : def transport : self.call handler has committed and closed its connection raise TimeoutError "simulated response loss after commit" with self.assertRaises TimeoutError : transport reply = self.call self.assertTrue reply.replayed self.assertEqual reply.result, {"id": 1, "interval minutes": 30, "version": 2} self.assertEqual self.query "SELECT interval minutes,version FROM automations WHERE id=1" , 30, 2 self.assertEqual self.query "SELECT count FROM receipts" , 1, def test replay returns historical result without undoing newer edit self : original = self.call later = self.call "edit-43", version=2, minutes=60 retry = self.call self.assertEqual retry.result, original.result self.assertTrue retry.replayed self.assertEqual later.result "version" , 3 self.assertEqual self.query "SELECT interval minutes,version FROM automations WHERE id=1" , 60, 3 def test same key cannot change target version or value self : self.call for changes in {"minutes": 60}, {"version": 2}, {"target": 2} : with self.subTest changes=changes , self.assertRaises KeyReuse : self.call changes self.assertEqual self.query "SELECT count FROM receipts" , 1, self.assertEqual self.query "SELECT interval minutes,version FROM automations WHERE id=2" , 15, 1 def test new key does not bypass old version self : self.call with self.assertRaises Refused : self.call "another-key", minutes=60 self.assertEqual self.query "SELECT count FROM receipts" , 1, def test same text key is scoped to principal and workspace self : self.call bob = replace ALICE, user id="bob" other team = replace ALICE, workspace id="team-b" self.assertFalse self.call who=bob, version=2, minutes=60 .replayed self.assertFalse self.call who=other team, target=20 .replayed self.assertEqual self.query "SELECT count FROM receipts" , 3, def test current scope is checked even for a saved receipt self : self.call revoked = replace ALICE, scopes=frozenset with self.assertRaises Refused : self.call who=revoked self.assertEqual self.query "SELECT version FROM automations WHERE id=1" , 2, def test moved or deleted resource does not replay or resurrect self : self.call self.query "UPDATE automations SET workspace id='team-b' WHERE id=1" with self.assertRaises Refused : self.call self.query "DELETE FROM automations WHERE id=1" with self.assertRaises Refused : self.call self.assertEqual self.query "SELECT count FROM automations WHERE id=1" , 0, self.assertEqual self.query "SELECT count FROM receipts" , 1, def test receipt failure rolls back the row change self : self.query """CREATE TRIGGER fail receipt BEFORE INSERT ON receipts BEGIN SELECT RAISE ABORT,'receipt store unavailable' ; END""" with self.assertRaises sqlite3.IntegrityError : self.call self.assertEqual self.query "SELECT interval minutes,version FROM automations WHERE id=1" , 15, 1 self.assertEqual self.query "SELECT count FROM receipts" , 0, self.query "DROP TRIGGER fail receipt" self.assertFalse self.call .replayed def test simultaneous duplicate calls share one result self : start = Barrier 2 def submit : start.wait timeout=5 return self.call with ThreadPoolExecutor max workers=2 as pool: futures = pool.submit submit for in range 2 replies = f.result timeout=10 for f in futures self.assertEqual replies 0 .result, replies 1 .result self.assertEqual sorted r.replayed for r in replies , False, True self.assertEqual self.query "SELECT version FROM automations WHERE id=1" , 2, self.assertEqual self.query "SELECT count FROM receipts" , 1, def test simultaneous key mismatch has one winner self : start = Barrier 2 def submit minutes : start.wait timeout=5 try: return self.call minutes=minutes except KeyReuse: return "mismatch" with ThreadPoolExecutor max workers=2 as pool: futures = pool.submit submit, m for m in 30, 60 outcomes = f.result timeout=10 for f in futures self.assertEqual sum isinstance x, Reply for x in outcomes , 1 self.assertEqual outcomes.count "mismatch" , 1 winner = next x for x in outcomes if isinstance x, Reply self.assertEqual self.query "SELECT interval minutes,version FROM automations WHERE id=1" , winner.result "interval minutes" , 2 self.assertEqual self.query "SELECT count FROM receipts" , 1, def test invalid or inaccessible requests do not write self : for changes in {"target": True}, {"version": 0}, {"minutes": 1}, {"minutes": True}, {"target": "1 OR 1=1"} : with self.subTest changes=changes , self.assertRaises ValueError : self.call changes for key in "", "x" 65, None, "unsafe key" : with self.subTest key=key , self.assertRaises ValueError : self.call key for target in 20, 999 : with self.subTest target=target , self.assertRaises Refused : self.call target=target self.assertEqual self.query "SELECT count FROM receipts" , 0, self.assertEqual self.query "SELECT version FROM automations ORDER BY id" , 1, , 1, , 1, if name == " main ": print f"Python {sys.version.split 0 } / SQLite {sqlite3.sqlite version}", flush=True unittest.main verbosity=2 Local run: Python 3.12.14, SQLite 3.53.1; all 12 tests passed. These are local tests, not a live MCP interoperability or power-loss durability test. The first test is the control: a version-scoped update succeeds once and conflicts on its own retry. There is no saved result to recover. The lost-response test calls the real handler, lets it commit and close its connection, then raises a simulated transport timeout. Retrying through a new connection returns the saved version-2 result. The row stays at version 2 and there is one receipt. The later-edit test is just as important: edit-42 commits interval=30, version=2 edit-43 commits interval=60, version=3 retry edit-42 returns its original interval=30, version=2 result the database remains interval=60, version=3 That replay result is historical evidence about an operation , not a fresh resource snapshot. The replayed flag makes the distinction visible. A client must not use the old result to replace newer displayed state without its own version check or a fresh read. Two barrier-based tests release requests from separate threads; each handler opens its own SQLite connection. Identical requests yield one new result and one replay. Different payloads under the same scoped key yield one accepted request and one mismatch. Which payload wins is deliberately unspecified. The rollback test installs a trigger that rejects receipt insertion after the update has executed. The handler rolls the transaction back: no row change and no receipt remain. This tests the failure between the two writes, rather than assuming that a successful happy path proves atomicity. The remaining cases cover key scope, rejected input, a new key with a stale version, removed write scope, and moved or deleted resources. The scope test supplies newly restricted trusted context; it does not prove that a production identity system detects revocation. SQLite permits one writer at a time. BEGIN IMMEDIATE obtains the write transaction before the lookup, so competing first attempts cannot both pass an unprotected lookup. That is a small-database implementation choice, not a high-throughput design recommendation. Keep this transaction short and handle lock contention explicitly in the surrounding service. There is no external API call inside the transaction. Saving a receipt and sending an email, publishing a post or charging a card are not automatically one atomic operation. Those workflows need a recoverable handoff and a downstream deduplication or reconciliation strategy. This example makes no end-to-end exactly-once claim. There is also no receipt expiry policy. Deleting receipts removes the ability to recover their results. In this versioned example, an old version normally still prevents a repeated write, but that is not a substitute for a replay contract. Define retention, backup/restore behavior, stable identities and what clients should do after the supported retry window. Reusing a deleted record ID or resetting versions needs additional protection. The client has work to do too: persist the operation ID, preserve the original arguments, distinguish a retry from a new user decision, and avoid an unbounded retry loop. A request ID regenerated after every timeout defeats the contract. BEGIN IMMEDIATE and contention. Which lost response would be hardest to reconcile in your tool integration: changing a setting, starting a job, or calling an external service? Leave the failure sequence in the comments, including what evidence the client still has after the timeout.