# Workflow automation example: approval, handoff and retry

This standalone PHP exercise models a fictional onboarding approval creating a delivery task. It uses two independent **in-memory SQLite databases** and synthetic identities. It calls the receiver directly within one process. It does not use a network, real login, webhook provider, queue service, concurrent worker or production data. All database state disappears when the process exits.

The source database owns approval and a pending handoff. The receiver owns a task identified by a stable action key. A simulated lost acknowledgment occurs **after the receiver commits**, leaving the source pending. Retrying the same key and payload must return the original task and record that task ID in the source outbox. Handoff delivery does not mean the task's business work is complete.

## Run the complete example

Requirements: PHP 8.3 with PDO and the PDO SQLite driver, or a compatible newer environment. The recorded execution used PHP 8.3.6 and SQLite 3.45.1. No Composer packages, Laravel bootstrap or application configuration are required.

Save the following PHP block as `outbox-demo.php` in a separate example directory, then run:

```sh
php outbox-demo.php
```

The checks throw on a mismatch and return a nonzero process exit. They do not depend on PHP's optional `assert()` setting.

```php
<?php

declare(strict_types=1);

// Fictional local exercise: two independent in-memory SQLite databases.
// Identities are synthetic. No HTTP, real login, provider, or concurrent workers.
function database(): PDO
{
    return new PDO('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
}

function keyFor(int $workspace, int $request, int $revision): string
{
    return "onboarding:$workspace:$request:$revision";
}

function payloadFor(array $fields): string
{
    ksort($fields); // This demonstration's payload is a flat object.
    return json_encode($fields, JSON_THROW_ON_ERROR);
}

function one(PDO $db, string $sql, array $params = []): array|false
{
    $query = $db->prepare($sql);
    $query->execute($params);
    return $query->fetch(PDO::FETCH_ASSOC);
}

function approve(PDO $sender, array $identity, int $workspace, int $request, int $revision, bool $abort = false): string
{
    if ($identity['workspace'] !== $workspace || $identity['role'] !== 'approver') {
        throw new DomainException('approval denied');
    }
    $sender->beginTransaction();
    try {
        $row = one($sender, 'SELECT * FROM requests WHERE workspace_id = ? AND id = ? AND revision = ?', [$workspace, $request, $revision]);
        if (!$row) {
            throw new DomainException('request missing');
        }
        $key = keyFor($workspace, $request, $revision);
        $payload = payloadFor(['workspace_id' => $workspace, 'request_id' => $request,
            'revision' => $revision, 'client_name' => $row['client_name']]);
        $sender->prepare('UPDATE requests SET state = ? WHERE workspace_id = ? AND id = ? AND revision = ?')
            ->execute(['approved', $workspace, $request, $revision]);
        $sender->prepare('INSERT INTO outbox (handoff_key, payload, state) VALUES (?, ?, ?) ON CONFLICT(handoff_key) DO NOTHING')
            ->execute([$key, $payload, 'pending']);
        $queued = one($sender, 'SELECT payload FROM outbox WHERE handoff_key = ?', [$key]);
        if ($queued['payload'] !== $payload) {
            throw new DomainException('approval payload conflict');
        }
        if ($abort) {
            throw new RuntimeException('simulated sender rollback');
        }
        $sender->commit();
        return $key;
    } catch (Throwable $error) {
        $sender->rollBack();
        throw $error;
    }
}

function receive(PDO $receiver, string $key, string $payload): int
{
    $fields = json_decode($payload, true, 512, JSON_THROW_ON_ERROR);
    $names = ['client_name', 'request_id', 'revision', 'workspace_id'];
    if (!is_array($fields)) {
        throw new DomainException('invalid payload');
    }
    ksort($fields);
    if (array_keys($fields) !== $names || !is_string($fields['client_name'])
        || !is_int($fields['request_id']) || !is_int($fields['revision']) || !is_int($fields['workspace_id'])) {
        throw new DomainException('invalid payload');
    }
    if ($key !== keyFor($fields['workspace_id'], $fields['request_id'], $fields['revision'])) {
        throw new DomainException('key does not match payload');
    }
    $canonical = payloadFor($fields);
    $hash = hash('sha256', $canonical);
    $receiver->beginTransaction();
    try {
        $existing = one($receiver, 'SELECT * FROM tasks WHERE handoff_key = ?', [$key]);
        if ($existing) {
            if (!hash_equals($existing['payload_hash'], $hash)) {
                throw new DomainException('payload conflict');
            }
            $receiver->commit();
            return (int) $existing['id'];
        }
        $receiver->prepare('INSERT INTO tasks (handoff_key, payload_hash, payload) VALUES (?, ?, ?)')
            ->execute([$key, $hash, $canonical]);
        $id = (int) $receiver->lastInsertId();
        $receiver->commit();
        return $id;
    } catch (Throwable $error) {
        $receiver->rollBack();
        throw $error;
    }
}

// This trusted worker calls the local receiver directly; it is not an HTTP client.
function deliver(PDO $sender, PDO $receiver, string $key, bool $loseAcknowledgment = false): int
{
    $message = one($sender, 'SELECT * FROM outbox WHERE handoff_key = ?', [$key]);
    if (!$message) {
        throw new DomainException('no queued handoff');
    }
    $id = receive($receiver, $key, $message['payload']);
    if ($loseAcknowledgment) {
        throw new RuntimeException('acknowledgment lost after receiver commit');
    }
    $sender->prepare('UPDATE outbox SET state = ?, receiver_task_id = ? WHERE handoff_key = ?')->execute(['delivered', $id, $key]);
    return $id;
}

$checks = 0;
function same(mixed $expected, mixed $actual, string $label): void
{
    global $checks;
    $checks++;
    if ($expected !== $actual) {
        throw new RuntimeException("FAIL: $label; expected " . json_encode($expected) . ', got ' . json_encode($actual));
    }
}

function rejects(callable $operation, string $class, string $message, string $label): void
{
    try {
        $operation();
    } catch (Throwable $error) {
        same([$class, $message], [get_class($error), $error->getMessage()], $label);
        return;
    }
    same('exception', 'no exception', $label);
}

function total(PDO $db, string $table): int
{
    return (int) $db->query("SELECT COUNT(*) FROM $table")->fetchColumn(); // Internal fixed table names only.
}

$sender = database();
$receiver = database();
$sender->exec('CREATE TABLE requests (workspace_id INTEGER, id INTEGER, revision INTEGER, client_name TEXT NOT NULL,
    state TEXT NOT NULL CHECK (state IN ("draft", "approved")), PRIMARY KEY (workspace_id, id, revision))');
$sender->exec('CREATE TABLE outbox (handoff_key TEXT NOT NULL PRIMARY KEY, payload TEXT NOT NULL, receiver_task_id INTEGER,
    state TEXT NOT NULL CHECK (state IN ("pending", "delivered")))');
$receiver->exec('CREATE TABLE tasks (id INTEGER PRIMARY KEY AUTOINCREMENT, handoff_key TEXT NOT NULL UNIQUE,
    payload_hash TEXT NOT NULL, payload TEXT NOT NULL)');
$seed = $sender->prepare('INSERT INTO requests VALUES (?, ?, ?, ?, ?)');
foreach ([[7, 42, 1, 'Example Client'], [7, 43, 1, 'Rollback Client'], [7, 44, 1, 'Later Client']] as $row) {
    $seed->execute([...$row, 'draft']);
}
$approver = ['workspace' => 7, 'role' => 'approver'];
$member = ['workspace' => 7, 'role' => 'member'];
$foreignApprover = ['workspace' => 9, 'role' => 'approver'];
$key = keyFor(7, 42, 1);

try {
    rejects(fn() => deliver($sender, $receiver, $key), DomainException::class, 'no queued handoff', 'unapproved request cannot dispatch');
    same(0, total($sender, 'outbox'), 'unapproved has no outbox');
    same(0, total($receiver, 'tasks'), 'unapproved has no task');
    foreach ([$member, $foreignApprover] as $identity) {
        rejects(fn() => approve($sender, $identity, 7, 42, 1), DomainException::class, 'approval denied', 'role or workspace denied');
        same('draft', one($sender, 'SELECT state FROM requests WHERE id = 42')['state'], 'denied request unchanged');
        same(0, total($sender, 'outbox'), 'denial did not queue');
        same(0, total($receiver, 'tasks'), 'denial did not deliver');
    }
    same($key, approve($sender, $approver, 7, 42, 1), 'stable handoff key');
    same('approved', one($sender, 'SELECT state FROM requests WHERE id = 42')['state'], 'approval recorded');
    same($key, approve($sender, $approver, 7, 42, 1), 'repeat approval keeps key');
    same(1, total($sender, 'outbox'), 'approval queues once');
    same(0, total($receiver, 'tasks'), 'approval is separate from delivery');

    rejects(fn() => approve($sender, $approver, 7, 43, 1, true), RuntimeException::class, 'simulated sender rollback', 'sender rollback occurs');
    same('draft', one($sender, 'SELECT state FROM requests WHERE id = 43')['state'], 'rollback undoes approval');
    same(false, one($sender, 'SELECT * FROM outbox WHERE handoff_key = ?', [keyFor(7, 43, 1)]), 'rollback undoes queue insert');
    same(1, total($sender, 'outbox'), 'rollback preserves earlier queue');
    same(0, total($receiver, 'tasks'), 'rollback creates no task');

    rejects(fn() => deliver($sender, $receiver, $key, true), RuntimeException::class,
        'acknowledgment lost after receiver commit', 'lost acknowledgment simulated');
    same(1, total($receiver, 'tasks'), 'receiver committed before lost acknowledgment');
    same('pending', one($sender, 'SELECT state FROM outbox WHERE handoff_key = ?', [$key])['state'], 'sender still pending');
    same(null, one($sender, 'SELECT receiver_task_id FROM outbox WHERE handoff_key = ?', [$key])['receiver_task_id'], 'lost acknowledgment leaves no recorded task id');
    $firstTask = one($receiver, 'SELECT * FROM tasks WHERE handoff_key = ?', [$key]);
    $firstId = (int) $firstTask['id'];
    same($firstId, deliver($sender, $receiver, $key), 'retry returns same task');
    same(1, total($receiver, 'tasks'), 'retry does not duplicate task');
    same('delivered', one($sender, 'SELECT state FROM outbox WHERE handoff_key = ?', [$key])['state'], 'retry completes sender');
    same($firstId, one($sender, 'SELECT receiver_task_id FROM outbox WHERE handoff_key = ?', [$key])['receiver_task_id'], 'retry records receiver task id');
    same($firstId, deliver($sender, $receiver, $key), 'duplicate processing returns same task');
    same(1, total($receiver, 'tasks'), 'duplicate processing keeps one task');
    same($firstId, one($sender, 'SELECT receiver_task_id FROM outbox WHERE handoff_key = ?', [$key])['receiver_task_id'], 'duplicate processing preserves recorded id');
    same($key, approve($sender, $approver, 7, 42, 1), 'approval repeated after delivery keeps key');
    same($firstId, one($sender, 'SELECT receiver_task_id FROM outbox WHERE handoff_key = ?', [$key])['receiver_task_id'], 'repeated approval preserves recorded id');
    same('delivered', one($sender, 'SELECT state FROM outbox WHERE handoff_key = ?', [$key])['state'], 'repeated approval preserves delivered state');

    $changed = json_decode($firstTask['payload'], true, 512, JSON_THROW_ON_ERROR);
    $changed['client_name'] = 'Different Client';
    rejects(fn() => receive($receiver, $key, payloadFor($changed)), DomainException::class, 'payload conflict', 'changed payload rejected');
    same($firstTask, one($receiver, 'SELECT * FROM tasks WHERE handoff_key = ?', [$key]), 'conflict preserves original task');
    same(1, total($receiver, 'tasks'), 'conflict adds no task');
    $laterKey = approve($sender, $approver, 7, 44, 1);
    $laterId = deliver($sender, $receiver, $laterKey);
    same(keyFor(7, 44, 1), $laterKey, 'distinct request has distinct key');
    same(true, $laterId !== $firstId, 'distinct request has new task');
    same(2, total($receiver, 'tasks'), 'two distinct requests have two tasks');
    same($firstTask, one($receiver, 'SELECT * FROM tasks WHERE handoff_key = ?', [$key]), 'later request preserves first task');
    echo "PASS: $checks assertions\n";
    echo "Requests: one approved handoff retried safely, one rolled back, one later handoff delivered.\n";
    echo "Receiver tasks: 2; simulated lost acknowledgment did not create a duplicate.\n";
    echo "Scope: local sequential PHP/PDO SQLite; no HTTP, provider, real authentication, or concurrency test.\n";
} catch (Throwable $error) {
    fwrite(STDERR, $error->getMessage() . "\n");
    exit(1);
}
```

## Recorded result

Executed on 29 September 2026 with PHP 8.3.6, PDO SQLite and SQLite 3.45.1. Exit code: 0. Standard error: empty.

```text
PASS: 42 assertions
Requests: one approved handoff retried safely, one rolled back, one later handoff delivered.
Receiver tasks: 2; simulated lost acknowledgment did not create a duplicate.
Scope: local sequential PHP/PDO SQLite; no HTTP, provider, real authentication, or concurrency test.
```

The 42 assertions include: an unapproved request cannot dispatch; a wrong role or workspace cannot approve; repeat approval keeps one handoff; a source failure rolls back approval and outbox insertion; a receiver commit can coexist with an unconfirmed source; retry and repeat delivery return the original task; the sender records the task ID; changed content under the same key is rejected without changing the existing task; and a later distinct request creates a different task.

Request 42 is the approved action in workspace 7, revision 1, key `onboarding:7:42:1`. Request 43 exercises source rollback. Request 44 is the later independent action. The final two receiver tasks correspond to requests 42 and 44. No task is created for request 43.

## Check that the assertions catch a duplicate

In a separate copy, remove both parts of the receiver's duplicate protection: its lookup/return branch and the receiver table's `UNIQUE` key constraint. Keep the input records and assertions unchanged. The exact source change used was:

```diff
--- outbox-demo.php
+++ outbox-demo-no-dedup-mutant.php
@@ -79,14 +79,7 @@
     $hash = hash('sha256', $canonical);
     $receiver->beginTransaction();
     try {
-        $existing = one($receiver, 'SELECT * FROM tasks WHERE handoff_key = ?', [$key]);
-        if ($existing) {
-            if (!hash_equals($existing['payload_hash'], $hash)) {
-                throw new DomainException('payload conflict');
-            }
-            $receiver->commit();
-            return (int) $existing['id'];
-        }
+        // Deliberate mutant: receiver prior-key lookup/conflict check removed.
         $receiver->prepare('INSERT INTO tasks (handoff_key, payload_hash, payload) VALUES (?, ?, ?)')
             ->execute([$key, $hash, $canonical]);
         $id = (int) $receiver->lastInsertId();
@@ -145,7 +138,7 @@
     state TEXT NOT NULL CHECK (state IN ("draft", "approved")), PRIMARY KEY (workspace_id, id, revision))');
 $sender->exec('CREATE TABLE outbox (handoff_key TEXT NOT NULL PRIMARY KEY, payload TEXT NOT NULL, receiver_task_id INTEGER,
     state TEXT NOT NULL CHECK (state IN ("pending", "delivered")))');
-$receiver->exec('CREATE TABLE tasks (id INTEGER PRIMARY KEY AUTOINCREMENT, handoff_key TEXT NOT NULL UNIQUE,
+$receiver->exec('CREATE TABLE tasks (id INTEGER PRIMARY KEY AUTOINCREMENT, handoff_key TEXT NOT NULL,
     payload_hash TEXT NOT NULL, payload TEXT NOT NULL)');
 $seed = $sender->prepare('INSERT INTO requests VALUES (?, ?, ?, ?, ?)');
 foreach ([[7, 42, 1, 'Example Client'], [7, 43, 1, 'Rollback Client'], [7, 44, 1, 'Later Client']] as $row) {
```

Run the modified copy with PHP. The recorded result is exit code 1, empty standard output and this standard error:

```text
FAIL: retry returns same task; expected 1, got 2
```

The failure occurs after the lost acknowledgment: the retry now creates task 2 instead of finding task 1. This shows that the existing assertions detect removal of receiver deduplication. It does not prove each check catches every possible fault or that a real deployment is safe.

## Boundaries before adapting it

- The transaction boundaries are local and independent. The sender atomically updates approval and its pending intent; the receiver atomically creates the task with its unique action key. There is no transaction spanning both databases.
- In-memory commits demonstrate the stated transaction and retry behavior within a running process. They do not prove persistence, process-crash recovery, disk durability, locking behavior or production database compatibility.
- Only the supplied trusted synthetic approver/worker path is exercised. Array identities are not authentication. A public receiver would need real authentication, authorization, input validation and provider-specific signature handling where applicable.
- The payload is deliberately a flat object with a fixed field set. Sorting its keys provides the example's canonical representation; this is not a general canonical-JSON implementation. The receiver key is explicitly non-null and unique. Same-key/different-payload requests are conflicts, not silent updates.
- The example has draft and approved source states. It does not define rejection, cancellation or changes to approved work. New revisions need an explicit business rule and a relationship to previous actions; generating a fresh key for every retry defeats deduplication.
- There is no worker claim/lease, concurrent-dispatch test, scheduled backoff, retry limit, rate-limit handling, credential rotation, dead-letter queue or operator UI. Those are separate implementation and acceptance decisions.
- A unique row does not deduplicate a separate email, payment or remote request. The example's business effect is the task row within its receiver transaction. Additional side effects need their own accountable delivery boundary.
- A production handoff needs persistent state, an operating dispatcher and exception ownership. A pending record alone does not guarantee eventual completion. Neither this code nor the outbox pattern establishes end-to-end exactly-once delivery.

Guide: https://nomadicsoft.io/blog/workflow-automation-example

Pilot brief: https://nomadicsoft.io/downloads/business-automation-pilot-brief.md

Sources: [AWS transactional outbox](https://docs.aws.amazon.com/prescriptive-guidance/latest/cloud-design-patterns/transactional-outbox.html), [SQLite transactions](https://www.sqlite.org/lang_transaction.html), [SQLite unique constraints](https://www.sqlite.org/lang_createtable.html#unique_constraints).