How to verify recovery after a database commit and external side effect diverge

The symptoms

• •Database row reflects the committed state, but downstream observers (queue consumer, search index, cache entry, webhook receiver) still report the pre-commit state.
• •Replay or reconciliation produces duplicate downstream effects after recovery: a second search index write, a second email, or a second webhook delivery appears in receiver logs.
• •Metrics show a growing gap between commit rate and downstream effect rate, with the gap closing only after a manual replay job runs.
• •Idempotency tokens are present on the database side but absent or mismatched on the external side, producing inconsistent deduplication windows.
• •Operator dashboards flag a "stuck outbox" or "unpublished events" counter that stays non-zero even after the producing service reports healthy.

Likely causes

• •Outbox or change-data-capture publication is asynchronous; the publish step failed, was retried beyond the producer's retry budget, or was deduplicated away by a stricter publisher filter than the database allowed.
• •Two-phase or saga commit reached the local commit but the compensating/external step lost its lease, token, or session, so the side effect was never acknowledged by the external system.
• •Network partition isolated the database from the external service long enough for the database to commit, after which the external service recovered without the queued event.
• •Clock or ordering skew between the database commit timestamp and the external service's accepted-order timestamp, so the external side rejected or reordered the effect.
• •Consumer-side deduplication key is derived from a field that the database does not constrain (for example, client-supplied UUID), allowing two semantically identical commits to be treated as distinct.

First ten minutes

1. 01Confirm the symptom is divergence and not a read-replica lag: query the primary (writable) node for the committed row and capture the commit LSN or transaction id; compare to what the external observer last acknowledged.
2. 02Capture the divergence window: record the earliest commit whose downstream effect is missing and the latest commit whose downstream effect is present, using database transaction timestamps.
3. 03Inventory the boundary: list every external side effect produced from the same code path (queue topic, index name, cache key prefix, webhook URL) so recovery is scoped, not partial.
4. 04Read-only safety check: snapshot, do not mutate. Stop new writes only if the application protocol supports a maintenance flag, otherwise proceed with read-only inspection.
5. 05Decide the verification mode: replay-with-idempotency (preferred) or compensating action; choose before touching any data so the proof-of-fix criteria are unambiguous.

Evidence to collect

• •Database transaction id (xid), commit LSN, and commit timestamp for each row in the divergence window, from the primary node's catalog or statistics views.
• •Outbox or CDC publication log: event id, intended destination, attempt count, last error, and last successful publish offset.
• •External side-effect receipts: queue offsets, index document versions, cache key existence proofs, webhook delivery acks, all timestamped.
• •Idempotency tokens: the token stored next to the database row versus the token seen by the consumer, including any redaction or hashing applied.
• •Configuration snapshot: retry policy, dedup window, ordering key, and any publisher-side filters active at the time of divergence.

Where to look

• •The database-primary boundary: pg_stat_activity, pg_stat_replication, and the commit log of the primary node, not replicas, to obtain authoritative commit timestamps.
• •The outbox or CDC publisher boundary: the publication log table, the publisher's offset checkpoint, and the publisher's dead-letter or retry queue.
• •The external service ingress boundary: the queue or topic's last-acknowledged offset, the search index's update log, the cache's last-writer timestamp per key, the webhook receiver's delivery log.
• •The application boundary: the code path that writes both the row and the outbox event in the same transaction, and the code path that constructs the idempotency token.

Diagnostic steps

1. 01For a sample row in the divergence window, compare the primary's commit LSN and timestamp to the outbox row's status: if the outbox row is still unpublished, the divergence is a publisher fault, not a consumer fault.
2. 02For rows whose outbox row is published, compare the published event id to what the external service recorded; a mismatch points to a token-derivation or serialization bug, not a network fault.
3. 03Compute the gap size by counting committed rows whose corresponding external receipts are absent within the SLA window; this isolates whether divergence is bounded or open-ended.
4. 04Classify each missing effect as transient (retry will succeed) versus terminal (external side permanently rejected, for example a deleted index document or expired idempotency window).
5. 05Determine whether a prior replay already partially closed the gap, by checking the publisher's offset against the earliest still-missing commit; partial closure changes the proof-of-fix criteria.

Common mistakes

• •Reading commit timestamps from a read replica; replica lag masquerades as divergence and produces a false recovery signal.
• •Replaying events without verifying idempotency tokens, which turns a divergence into a duplication incident on the external side.
• •Closing the outbox by deleting unpublished rows, which destroys the evidence needed to prove the gap was bounded and repaired.
• •Trusting client-supplied dedup keys from the database row; the producer and consumer may derive idempotency from different fields and appear to agree while actually diverging.
• •Restarting the publisher to "clear" the gap without first capturing the offset checkpoint, losing the ability to distinguish recovered events from retried events.

Safe fixes

• •If the divergence is bounded and idempotency tokens are intact on both sides, run a replay job that re-publishes only the outbox rows in the divergence window, scoped by primary commit LSN, and verify each external receipt before advancing.
• •If the external side permanently rejected an effect (for example an expired idempotency window), do not replay the original event; instead emit a corrective event tagged with a new idempotency token derived from a server-controlled field, and record the mapping.
• •If the publisher's retry budget was exhausted, raise the budget and lower the batch size for the divergence window only; do not change global retry policy as part of recovery.
• •If a network partition is still suspected, defer recovery until the partition is independently confirmed closed by an out-of-band control-plane check, not by the publisher's own success signal.
• •If the gap is open-ended (cannot bound it), pause new commits at the application boundary before replay, so the proof-of-fix check is not invalidated by new divergence.

Prove the fix

1. 01For every commit LSN in the original divergence window, the external service now reports a receipt with an idempotency token that matches the database row's stored token, within a defined tolerance window.
2. 02The count of unpublished outbox rows for the affected code path returns to its pre-incident baseline and remains there for at least one full retry cycle, observed from the publisher's metrics.
3. 03A replayed sample event, instrumented with a canary token, appears in the external receipt log exactly once across all consumers, proving idempotency held through the recovery path.
4. 04New commits produced after recovery begin appearing in the external receipt log within the documented SLA, measured from primary commit timestamp, confirming the divergence no longer recurs.
5. 05The pre-incident gap metric (committed minus externally acknowledged) closes to zero and a synthetic divergence injection (read-only, non-destructive test) is correctly detected by the same verification procedure.

Prevention and next steps

• •Co-locate the outbox insert and the database commit in the same transaction, and treat the outbox row as the source of truth for downstream delivery, not the application code's return value.
• •Derive idempotency tokens from a server-controlled, database-constrained field (for example, a primary key combined with a server-assigned version), never from client-supplied input.
• •Emit a divergence counter per code path that compares committed rows to externally acknowledged effects on a fixed schedule, with an alert threshold tied to the SLA, not to absolute counts.
• •Version the publisher configuration (retry budget, batch size, dedup window) so a recovery change can be reverted by reverting configuration, not by editing data.
• •Document a recovery runbook that names the primary node, the outbox table, and the external receipt log as the three evidence sources, so on-call engineers do not improvise boundaries under pressure.

Safe commands and checks

SELECT datname, pid, state, query_start, xact_start FROM pg_stat_activity WHERE state = 'active' AND xact_start IS NOT NULL;
SELECT pid, usesysid, application_name, client_addr, state, sync_state, sent_lsn, replay_lsn FROM pg_stat_replication;
SELECT relname, n_tup_ins, n_tup_upd, n_tup_del, n_live_tup FROM pg_stat_user_tables WHERE relname = '<outbox_table>';
SELECT datname, checkpoints_timed, checkpoints_req, checkpoint_write_time, checkpoint_sync_time FROM pg_stat_bgwriter;