Concept: The Idempotent Inbox Pattern

The idempotent inbox is the receiving counterpart to the transactional outbox. While the outbox ensures events are reliably published, the inbox ensures events are reliably received and processed exactly once — even when the same message arrives multiple times due to network retries, relay restarts, or at-least-once delivery semantics.

The Problem

In distributed systems, at-least-once delivery is the norm. Messages can be delivered more than once due to:

• Network timeouts (sender retries after no ack)
• Consumer crashes (message re-delivered after acknowledgment timeout)
• Relay restarts (last batch re-delivered)
• Partition rebalances (offset not committed)

If your service processes the same payment event twice, you might charge the customer twice. If it processes the same order event twice, you might ship duplicate items.

The Solution

The inbox table stores every received message with a unique identifier (deduplication key). Before processing, it checks whether the message has already been seen:

-- The relay writes incoming messages to the inbox
-- Duplicate dedup_keys are silently ignored (idempotent)
INSERT INTO tide.inbox_events (dedup_key, event_type, payload)
VALUES ('evt-123', 'order.created', '{"order_id": "ORD-001"}')
ON CONFLICT (dedup_key) DO NOTHING;

If the same message arrives again (same dedup_key), the INSERT silently does nothing. The message is not processed a second time.

Processing Workflow

1. Message arrives from source (Kafka, NATS, webhook, etc.)
2. Relay writes to inbox (ON CONFLICT DO NOTHING)
3. Application queries inbox for pending messages
4. Application processes the message within a transaction
5. Application marks the message as processed

-- Step 3: Query pending messages
SELECT id, event_type, payload 
FROM tide.inbox_pending('payment_events') 
LIMIT 10;

-- Step 4-5: Process within transaction
BEGIN;
  -- Your business logic here
  INSERT INTO payments (order_id, amount, status) 
  VALUES ('ORD-001', 149.99, 'captured');
  
  -- Mark as processed (atomically with business logic)
  SELECT tide.inbox_mark_processed('payment_events', 42);
COMMIT;

Because the mark-processed call is inside the same transaction as the business logic, either both succeed or both fail. If the transaction rolls back, the message remains pending and will be retried.

Deduplication Keys

The dedup key uniquely identifies a message. Common strategies:

pg_tide extracts the dedup key from the message based on the wire format configuration. For native format, it uses the message key. For Debezium, it uses the record key.

Failure Handling

If processing a message fails (business logic error, constraint violation), you have two options:

Retry later:

-- Leave as pending, it will be retried on next poll
-- Optionally record the error for monitoring
SELECT tide.inbox_mark_failed('payment_events', 42, 'Insufficient funds');

Skip permanently:

-- Mark as processed to advance past it
SELECT tide.inbox_mark_processed('payment_events', 42);

Exactly-Once Processing

The inbox provides exactly-once processing (not delivery) through this mechanism:

1. At-least-once delivery: The source may deliver the same message multiple times
2. Deduplication on write: The inbox's unique constraint prevents duplicate storage
3. Atomic processing: Business logic + mark-processed in one transaction

The combination ensures each unique message is processed exactly once, regardless of how many times it's delivered.

When to Use the Inbox

Use the inbox when:

• You receive events from external systems that may duplicate
• Processing has side effects (charging money, sending emails, updating state)
• You need a reliable buffer between message receipt and processing
• You want to decouple message consumption from message processing

Further Reading

• Transactional Outbox — The publishing counterpart
• SQL Reference: Inbox API — Complete function reference
• Message Guarantees — Delivery semantics