A service writes a database row and then publishes to Kafka; occasionally only one succeeds. How would you fix it?

The problem described is the dual-write problem: the service writes to the database and then publishes to Kafka as two separate operations. If the database write succeeds and the Kafka publish fails, the event is lost. If the Kafka publish succeeds and the database write fails, the event is published but the database does not reflect it. The fix is to make the two operations transactionally coherent, and the standard solution is the transactional outbox pattern. Instead of publishing to Kafka directly, the service writes the event to an outbox table in the same database transaction as the business data. A separate relay process reads the outbox table and publishes the events to Kafka. Because the outbox write and the business write are in the same transaction, they either both commit or both roll back. The relay then publishes the events at least once, and consumers must be idempotent. This eliminates the dual-write problem: every committed business change produces an event, and no event is published without a corresponding business change. The trade-off is between consistency and complexity. The outbox pattern adds a table, a relay, and idempotency requirements on consumers, but it guarantees consistency.

The mechanism of the outbox pattern has three parts: the outbox table, the transaction, and the relay. The outbox table stores the event payload, the event type, the aggregate ID, and a timestamp. The application writes the business data and the outbox row in a single transaction. The relay reads the outbox table and publishes the events to Kafka. The relay can be implemented with polling or with CDC. Polling is simpler but adds latency and load to the database; CDC (with Debezium) is more efficient and near-real-time but requires access to the database's transaction log. After publishing, the relay marks the outbox row as published (for polling) or relies on the CDC offset (for CDC). For polling, you need a cleanup process to delete published rows. For CDC, the outbox table can be cleaned up after a retention period. The trade-off between polling and CDC is between simplicity and efficiency. Polling is easier to set up but does not scale as well; CDC is more complex but is the standard for high-volume systems. Version note: Debezium's outbox event router is a common way to implement the CDC-based outbox pattern. It transforms outbox rows into Kafka events with the correct key, type, and payload. Check the version and configuration when implementing.

A common mistake is to try to make the database and Kafka transactional together, which is not possible without a distributed transaction (which is complex and slow). Another mistake is to publish to Kafka inside the database transaction, which does not work because Kafka is not part of the database transaction. A third mistake is to use a database trigger to publish to Kafka, which reintroduces the dual-write problem because the trigger runs after the transaction and can fail independently. The trade-off is between consistency and latency. The outbox pattern adds a small latency between the database commit and the Kafka publish, but it guarantees consistency. For systems where event loss is unacceptable, this is the right trade-off. An alternative is to use a different architecture, such as event sourcing, where the event log is the source of truth and the database is a projection. In event sourcing, there is no dual write because the event is the primary record. But event sourcing is a bigger architectural change and is not always appropriate. Version note: if you use a managed database that does not expose the transaction log, CDC may not be available, and you may need to use polling. Check the database's capabilities before choosing the relay implementation.