Commit data and the work it creates.

Durable job intent with explicit idempotency and an atomic handoff to the SQL queue.

One connection, one transaction

Inject Database and Outbox from the same request scope. record() requires an active transaction on that exact connection. A rollback removes both the business write and its job intent. This adapter targets the built-in PDO database; a separate Doctrine EntityManager connection is not part of that transaction. Direct DatabaseQueue::push() inside the same PDO transaction is also atomic when no separate outbox record is needed.

example

use Icom\Database\Database;
use Icom\Queue\{JobDefinition, Outbox};

// In a controller/service receiving these scoped dependencies:
$db->transaction(function () use ($db, $outbox, $noteId, $title) {
    $db->table('notes')->where('id', $noteId)->update(['title' => $title]);
    $outbox->record(
        'note:'.$noteId.':revision:2',
        new JobDefinition('notes.index', ['note_id' => $noteId], 'search')
    );
});
// Register notes.index in config/jobs.php and run its queue worker.

Idempotency is an application contract

Choose a stable, non-secret printable ASCII key of 1–200 bytes for a business operation. Only its SHA-256 digest is stored. Reusing a key with the same normalized JSON job, queue, attempts and absolute availability returns the same message ID. Conflicting reuse throws; let it roll back the business transaction. Payload array ordering matters. Reserve the schedule: prefix for scheduler-generated keys. A published tombstone remains after its job completes, so a producer retry cannot enqueue again within retention. Dedupe of job intent does not itself prevent a duplicated business write.

Relay and operate

Each handoff inserts one queue row and clears the outbox payload/marks it published in the same short database transaction. Multiple relay processes share row locks. A failed/uncertain database operation propagates; the next run reads the durable state. Internal database-only transactions retry confirmed InnoDB deadlocks or SQLite BUSY after rollback, at most four times with jitter. JSON/name/fingerprint corruption is quarantined and valid peers continue.

example

php bin/icom outbox:relay 100
php bin/icom outbox:status
php bin/icom outbox:failed 50
# Repair the original malformed row privately, then:
php bin/icom outbox:retry MESSAGE_ID
php bin/icom queue:work search 100 300 --stop-when-empty

Delay, retention and private data

record(key, job, availableAt: UNIX_SECONDS) accepts an absolute availability; zero means immediate. Relay may hand off future jobs early, but queue reservation waits until due. Job JSON is limited to 65,535 bytes and depth 64, with arrays/scalars normalized before hashing. Payloads are private database data, not encrypted by this module. Put identifiers in jobs; load current authorized data in handlers. Do not enqueue passwords, recovery tokens or other secrets. Status/failure commands omit payloads and exception messages. Published records lose their payload immediately; failed/pending records are never automatically pruned.

example

php bin/icom outbox:prune 30 1000
# Deletes at most 1,000 published tombstones older than 30 days.
# This ends deduplication for those keys; retain longer when required.

Delivery boundaries

The atomic guarantee covers handoff into the same SQL database. Queue execution remains at least once: a handler can finish an external effect and die before acknowledgment. Use an application idempotency ledger or a provider-supported key for that effect. No exactly-once email/payment promise, remote broker, automatic after-commit listener or global event ordering is provided. The application owns transaction retry policy and clock synchronization. A producer with prior SQLite reads or competing InnoDB inserts may encounter a lock/deadlock or MariaDB snapshot conflict (1020): retry the entire business transaction only when its rollback outcome is known, never just record(). Pending and failed rows require monitoring and capacity planning.