Q45. Design a workflow that consumes Kafka and updates an external database while minimizing duplicate or lost effects.

The core challenge is that Kafka and the external database are two separate systems with no shared transaction. You cannot have a single atomic commit across both. The design goal is therefore effectively-once: at-least-once delivery from Kafka plus idempotent writes to the database, with a reconciliation mechanism to detect and repair any residual inconsistency. The standard pattern is the inbox pattern combined with idempotent operations. The consumer reads a batch from Kafka, and for each record, it writes an inbox row containing the event ID (topic, partition, offset) and applies the business effect in the same database transaction. The inbox table has a unique constraint on event ID. If the transaction commits, both the inbox row and the effect are durable. If it rolls back, neither is. On retry, the unique constraint prevents reprocessing. This gives you at-least-once delivery with effectively-once effects, as long as the business operation itself is idempotent or guarded by the inbox.

The mechanism matters because the order of operations and the transaction boundary determine where duplicates or loss can occur. If you write the effect first and then the inbox row, a crash between them causes the effect to be applied again on retry, which may be acceptable if the effect is idempotent but not if it is not. If you write the inbox row first and then the effect, a crash between them causes the inbox to say processed but the effect is missing, which is worse. So the correct order is: begin transaction, insert inbox row (which will fail on duplicate), apply effect, commit. If the insert fails with a duplicate key, you skip the effect and commit or roll back without harm. This is why the unique constraint is essential; without it, two concurrent consumers can both insert and both apply the effect. For very high throughput, the inbox table can become a bottleneck; partitioning by event key and using a compacted Kafka topic for deduplication are alternatives, but they add complexity.

A common mistake is to commit the Kafka offset before the database transaction commits. If the database commit then fails, the offset is already advanced and the event is lost. The fix is to commit the Kafka offset only after the database transaction succeeds. But even then, a crash between the database commit and the Kafka offset commit causes the event to be re-delivered, which is why the inbox is needed. Another mistake is to use a separate deduplication store (like Redis) without a transaction with the database; this reintroduces the race window. The trade-off is between latency, throughput, and storage. The inbox table grows over time and needs a cleanup job or TTL. For a compacted-topic approach, you get durability and replayability but higher latency. In Kafka 3.x, you could also consider Kafka transactions if the database write is replaced by a Kafka write, but for an external database, the inbox pattern remains the standard. Always add a reconciliation job that compares the database state with a replay of the Kafka topic to detect missed or duplicated effects.