Recovering an Ambiguous Workflow Timeout: A Python and SQLite Exercise

AI disclosure: This article and its code example were generated by an AI agent for Autom Mate. The example was executed locally with the automated checks described below. It is an illustrative tutorial, not a report of a production deployment or a claim of human review.

A workflow calls an application to grant access. The application commits the change, but the response never reaches the caller. The workflow reports a timeout. Running the entire sequence again is an attractive shortcut, but the error does not tell you whether the change happened.

This tutorial turns that ambiguity into a small recovery exercise. The destination is a local SQLite table, so there are no accounts, credentials or real access changes. The interesting boundary is between the destination committing a side effect and the caller receiving confirmation.

The operating context comes from Autom Mate's service desk recovery runbook. Here we focus on one operation that can be inspected independently.

Make the recovery decision before writing the retry loop

Microsoft's Retry pattern guidance distinguishes transient failures from persistent problems and warns about repeating operations with side effects. A timeout is a reason to investigate the result, not proof that the destination rejected the request.

A useful decision table is:

Destination evidence	Request still authorised?	Recovery decision
Matching action exists	Yes	Reconcile the result and continue
No matching action exists	Yes	Attempt the missing action under the original operation key
Matching action exists	No	Hold for an explicit access decision
No matching action exists	No	Hold without granting access
Key points to a different payload	Either	Stop and investigate the conflict

The last row matters. An idempotency key should not be reused to turn an approved read-only grant into administrator access. Matching only the operation identifier would hide that change.

Give the example a deliberately narrow contract

The fake destination stores one immutable grant per operation key. A repeated call with the same person and access returns the existing outcome. A call reusing that key with a different payload fails.

That is a capability of this example's destination. A local caller-side table cannot impose the same guarantee on an arbitrary remote API. Before applying the pattern elsewhere, verify whether the destination supports an appropriate idempotency key or an authoritative lookup.

The example also receives a simple authorisation boolean. A real service must derive that decision from its current policy and request state, with a design for changes that arrive during execution.

Run the exercise

Save the following as recovery_demo.py. It uses Python's standard library and creates only temporary test data.

"""Local recovery exercise. Uses only temporary SQLite data in tests."""
import sqlite3
import tempfile
import unittest
from pathlib import Path


def connect(path):
    db = sqlite3.connect(path)
    db.execute("CREATE TABLE IF NOT EXISTS grants "
               "(operation TEXT PRIMARY KEY, person TEXT, access TEXT)")
    return db


def lookup(db, operation):
    return db.execute(
        "SELECT person, access FROM grants WHERE operation = ?",
        (operation,),
    ).fetchone()


def grant(db, operation, person, access, lose_response=False):
    with db:
        db.execute("INSERT OR IGNORE INTO grants VALUES (?, ?, ?)",
                   (operation, person, access))
        if lookup(db, operation) != (person, access):
            raise ValueError("operation key reused for different payload")
    if lose_response:
        raise TimeoutError("committed, but acknowledgement was lost")


def recover(db, operation, person, access, authorised):
    actual = lookup(db, operation)
    if actual is not None and actual != (person, access):
        return "conflict"
    if not authorised:
        return "hold-existing" if actual else "hold-empty"
    if actual is not None:
        return "reconciled"
    grant(db, operation, person, access)
    return "completed"


class RecoveryTests(unittest.TestCase):
    def test_lost_response_and_restart(self):
        with tempfile.TemporaryDirectory() as folder:
            path = Path(folder) / "destination.db"
            db = connect(path)
            with self.assertRaises(TimeoutError):
                grant(db, "tenant-a:req-7:grant", "person-9", "reader", True)
            db.close()
            db = connect(path)
            self.assertEqual(
                recover(db, "tenant-a:req-7:grant", "person-9", "reader", True),
                "reconciled",
            )
            self.assertEqual(db.execute("SELECT COUNT(*) FROM grants").fetchone()[0], 1)
            db.close()

    def test_new_request_repeat_and_conflict(self):
        db = connect(":memory:")
        self.assertEqual(recover(db, "op-1", "p-1", "reader", True), "completed")
        self.assertEqual(recover(db, "op-1", "p-1", "reader", True), "reconciled")
        self.assertEqual(recover(db, "op-1", "p-1", "admin", True), "conflict")
        self.assertEqual(lookup(db, "op-1"), ("p-1", "reader"))
        db.close()

    def test_cancelled_request_does_not_grant_or_silently_revoke(self):
        db = connect(":memory:")
        self.assertEqual(recover(db, "op-1", "p-1", "reader", False), "hold-empty")
        self.assertIsNone(lookup(db, "op-1"))
        grant(db, "op-1", "p-1", "reader")
        self.assertEqual(recover(db, "op-1", "p-1", "reader", False), "hold-existing")
        self.assertEqual(lookup(db, "op-1"), ("p-1", "reader"))
        db.close()


if __name__ == "__main__":
    unittest.main()

Run:

python recovery_demo.py -v

The first test commits a grant and deliberately raises a timeout afterwards. It closes the database connection, opens a new connection to the same file and performs recovery. The assertion checks both the returned decision and the count of destination records. Recovery must recognise the completed effect without creating another grant.

The other tests exercise a new request, repeated recovery, conflicting payload reuse and a cancelled request. They passed with Python 3.12.14 during preparation. These checks demonstrate the listed local behaviours; they are not concurrency, availability or security tests.

Why cancellation produces a hold

Suppose a starter is cancelled after the destination granted access but before the workflow received confirmation. Treating that request as complete would ignore the cancellation. Deleting the grant automatically would invent a recovery policy.

The hold-existing result preserves both facts: something happened and the request is no longer authorised to continue. An accountable operator must decide the next step under the service's access policy. In a real runbook, include the evidence reference, recovery owner and next update time.

Likewise, a conflicting payload should not be “fixed” by overwriting the stored row. First determine whether the original operation key was reused incorrectly or the business request changed. Those cases need different decisions.

Keep these limits visible

This model deliberately leaves out several production concerns:

• It assumes an immutable destination record; subsequent manual changes need additional reconciliation rules.
• It does not coordinate multiple workers or lock the current authorisation decision across a remote action.
• It does not model eventually consistent reads, rate limits, unavailable lookups or ambiguous search results.
• It does not implement secret handling, customer access controls or a retained operational audit trail.

If the real destination cannot tell you whether an action occurred, return an unresolved state and give it an owner. Do not convert uncertainty into an automatic repeat merely to clear the queue.

For remote systems, also distinguish a stable business-operation key from an individual delivery attempt. A retry should retain the former while receiving its own attempt reference. That makes it possible to explain which business action several attempts were trying to complete.

Turn the result into an operator instruction

A practical instruction names the customer, original request, intended action, current authorisation and authoritative evidence source. It then identifies a permitted next step and a stop condition.

For example: inspect the grant associated with the original request; if it matches and the request remains valid, reconcile completion; if absent, execute only the missing operation through the destination's supported mechanism; if uncertain or conflicting, hold and escalate.

Keep the recovery outcome distinct from the whole service outcome. A confirmed access grant does not prove that equipment is ready or that the employee can complete their work.

The useful next step is to take one existing workflow failure and write this table for its actual destination. Use the full service desk recovery runbook as a worksheet.