Where PostgreSQL and Kafka stop being atomic in Blackbox

In Blackbox, starting a workflow writes a WorkflowInstance to PostgreSQL and creates a WorkflowCreated.v1 event for Kafka. The failure I wanted to contain is specific: PostgreSQL can commit the workflow, then the API process can stop before Kafka receives the event. A second write in the request handler would leave the system with a committed workflow and no event.

Blackbox is a private .NET portfolio prototype for order workflows. The code paths and test record below describe that prototype; they are not claims about a production deployment.

The request path has one database commit

WorkflowOrchestrator.StartWorkflowAsync first looks up the correlation ID. If it already exists, the method compares the JSON payload: an equivalent retry returns the existing workflow, while a changed payload raises an invariant error. For a new request, the method creates the workflow and serializes its domain events into outbox rows through the same EF Core context and PostgreSQL transaction.

The commit boundary is short and explicit:

await using var transaction = await _dbContext.Database.BeginTransactionAsync(ct);
var workflow = WorkflowInstance.Create(correlationId, payloadJson);
await _dbContext.Workflows.AddAsync(workflow, ct);

foreach (var domainEvent in workflow.DomainEvents)
{
    var outboxMsg = OutboxMessage.Create(
        topic: "blackbox.workflow.events",
        messageKey: workflow.Id.ToString(),
        eventType: domainEvent.EventType,
        payload: JsonSerializer.Serialize(domainEvent, domainEvent.GetType()));
    await _dbContext.OutboxMessages.AddAsync(outboxMsg, ct);
}

await _dbContext.SaveChangesAsync(ct);
await transaction.CommitAsync(ct);

This is a shortened excerpt of the request path; the real implementation adds one outbox row for each domain event. If the transaction rolls back, neither the workflow nor its publication intent is committed. If it commits, the worker has a durable row to retry after the API process exits.

The EF Core model maps each outbox row to an ID, topic, message key, event type, jsonb payload, status, retry count, error and creation/publication timestamps. The project bootstraps this demo schema with EnsureCreated; it has no versioned migration path. The model also defines no explicit index for the worker’s status plus created_at lookup, so I would measure that query against a growing table before presenting the polling path as scalable.

The worker trades a long transaction for a simple claim

OutboxProcessor.ProcessPendingMessagesAsync loops up to 50 times, but each iteration claims just one row in its own transaction:

SELECT * FROM outbox_messages
WHERE status = 'Pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;

SKIP LOCKED lets another worker select a different pending row instead of waiting on this row. In the current implementation, the worker marks the entity Processing, calls Kafka’s ProduceAsync, then calls SaveChangesAsync and commits. Because the state change is saved after the broker call, Processing is not a durable lease during that call: the transaction still holds the database connection and row lock, while other transactions continue to see the committed row as Pending.

That is easy to recover after a worker crash: PostgreSQL rolls back the open transaction and releases the lock, so the row can be tried again. The cost is that slow Kafka acknowledgement keeps the transaction and connection open. There is also no explicit poll-query index in the EF model. I have not measured lock duration, connection-pool pressure, table growth or poll latency, so those are risks to test—not observed production incidents.

The duplicate window is after Kafka accepts the event

The publisher awaits Kafka’s acknowledgement before the worker commits Published in PostgreSQL. If Kafka accepts the message and the worker then loses its database commit, PostgreSQL restores the row to Pending; the next attempt can publish the same logical event again. The outbox closes the lost-intent gap, but it does not make a Kafka acknowledgement and a PostgreSQL commit one atomic operation.

There are two different identifiers in the current path. The serialized domain event has an EventId, while the Inbox consumer records topic:partition:offset as its MessageId. The publisher sends the workflow ID as the Kafka key and the serialized payload as the value. Therefore, the Inbox can recognize the same Kafka record if that offset is redelivered. It does not deduplicate the same logical event if the outbox worker publishes it again and Kafka assigns a new offset. A business consumer would need to use the event ID as its idempotency key and commit its business effect together with that receipt.

The current Inbox only stores a receipt. It does not apply an order-side business operation. Calling this “exactly once processing” would describe a guarantee the code does not provide.

Failure cases and what the code says

The table separates tests that were run from failure behavior inferred from transaction order. That distinction matters: a happy-path publish test does not prove recovery from a process stop between broker acknowledgement and database commit.

One verification failure was in the runner

The project’s 14 October 2026 status record documents an initial Playwright run with 27 passes and 5 failures. Compose had started the requested isolated API on an alternate port, but the verification script still checked the default port 5000 and sent requests to an already-running API instance. I changed the runner so its readiness URL follows the Compose port override, then reran the suite against a fresh Compose database: all 32 API scenarios passed.

That was a test-harness bug, not evidence that the outbox itself failed. Keeping the two results separate prevented a stale service from being mistaken for the build under test. The same status record reports 71 unit tests and 6 PostgreSQL integration tests passing, and a demo run with 83 outbox rows marked Published and 83 Inbox receipts. Those counts describe one local run; they are not throughput, latency or durability measurements.

The checks exercise workflow creation, duplicate and concurrent requests, API lifecycle and retry scenarios. They do not demonstrate crash recovery at every transaction boundary, load behavior, or production availability. The demo uses .NET 10.0.401, PostgreSQL and Kafka in Docker Compose, EF Core/Npgsql, Confluent.Kafka, xUnit integration tests and Playwright API tests.

What I would change next

First, I would add a failure-injection test that lets Kafka acknowledge a publish and then forces the PostgreSQL transaction to roll back. The assertion should show both facts: Kafka can contain a duplicate record, and the repeated payload retains the same domain EventId.

Then I would pass that event ID through the consumer contract, put a unique constraint on (consumer_name, event_id), and make a real business update plus its Inbox receipt one PostgreSQL transaction. A replay with a new Kafka offset should then leave the business effect unchanged. That is the test that would establish idempotent business processing for this consumer.

For the worker itself, I would add retry classification and a persisted next-attempt time, implement operator-controlled Outbox dead-letter replay, and create a migration-based schema upgrade path. I would only choose a partial polling index after testing EXPLAIN and poll time with representative pending and published row counts. A lease design could reduce time spent holding a database transaction across Kafka I/O, but it would need expiry, ownership and stale-worker fencing rules before it is safer than the current lock.

On the evidence available, Blackbox provides atomic publication intent and at-least-once dispatch. Exactly-once business processing remains unproven until a consumer applies an event once and safely ignores a retry that arrives under a new Kafka offset. For a related client-side retry contract, read how FieldCore handles an event that arrives twice.

Documentation behind the design

• PostgreSQL SELECT locking clause explains that SKIP LOCKED skips rows locked by other transactions and is suited to queue-like consumers.
• EF Core transaction control documents explicit transactions around multiple database operations.
• Apache Kafka delivery semantics describes the delivery behavior that makes the acknowledgement/commit gap relevant.

← All notes