Webhook Idempotency Implementation

Overview

This document describes the atomic webhook processing system that ensures webhooks are processed exactly once, even with retries or duplicate deliveries from external providers.

Problem Statement

Webhook providers (Yelp, Facebook, Mailgun, etc.) use at-least-once delivery guarantees, meaning:

• The same webhook can be delivered multiple times
• Concurrent deliveries of the same webhook can occur
• Network issues can cause retries
• Without proper handling, this leads to:
  • Duplicate messages in the database
  • Duplicate notifications to users
  • Duplicate auto-responses
  • Data inconsistency

Solution: WebhookEvent Table + Idempotency Service

Architecture

┌─────────────────┐
│  Webhook POST   │
│   from Provider │
└────────┬────────┘
         │
         ▼
┌─────────────────────────────────┐
│ webhookIdempotencyService       │
│ .processWebhook()               │
└────────┬────────────────────────┘
         │
         ▼
┌─────────────────────────────────┐
│ Check WebhookEvent table        │
│ for eventId (unique)            │
└────────┬────────────────────────┘
         │
    ┌────┴────┐
    │         │
Already│    Not Found
Exists │         │
    │         ▼
    │    ┌──────────────────────┐
    │    │ Create record with   │
    │    │ status='processing'  │
    │    │ (atomic lock)        │
    │    └──────┬───────────────┘
    │           │
    │           ▼
    │    ┌──────────────────────┐
    │    │ Execute webhook      │
    │    │ handler logic        │
    │    └──────┬───────────────┘
    │           │
    │      ┌────┴────┐
    │      │         │
    │   Success   Failure
    │      │         │
    │      ▼         ▼
    │   Mark as   Mark as
    │ 'completed' 'failed'
    │           + error msg
    │           + retry count
    │
    ▼
Return result
(alreadyProcessed: true/false)

Database Schema

CREATE TABLE "WebhookEvent" (
    "id" TEXT PRIMARY KEY,
    "eventId" TEXT NOT NULL UNIQUE,  -- Provider's unique event ID
    "source" TEXT NOT NULL,           -- yelp, facebook, mailgun, etc.
    "eventType" TEXT NOT NULL,        -- message.created, review.updated, etc.
    "payload" JSONB NOT NULL,         -- Raw webhook payload
    "status" TEXT DEFAULT 'pending',  -- pending, processing, completed, failed
    "processedAt" TIMESTAMP,          -- When successfully completed
    "failedAt" TIMESTAMP,             -- When failed
    "errorMessage" TEXT,              -- Error details
    "retryCount" INTEGER DEFAULT 0,   -- Number of retry attempts
    "createdAt" TIMESTAMP DEFAULT NOW(),
    "updatedAt" TIMESTAMP
);

CREATE UNIQUE INDEX ON "WebhookEvent"("eventId");
CREATE INDEX ON "WebhookEvent"("source");
CREATE INDEX ON "WebhookEvent"("eventType");
CREATE INDEX ON "WebhookEvent"("status");

Implementation Details

Service: webhookIdempotencyService

Location: /lib/services/webhooks/webhookIdempotencyService.ts

Key Methods:

interface WebhookEventInput {
  eventId: string; // Provider's unique event ID
  source: string; // yelp, facebook, mailgun, etc.
  eventType: string; // message.created, review.updated, etc.
  payload: unknown; // Raw webhook payload
}

interface WebhookProcessingResult {
  success: boolean;
  alreadyProcessed?: boolean; // true if webhook was already handled
  error?: string;
  [key: string]: unknown; // Additional data from handler
}

// Main processing method
processWebhook(
  event: WebhookEventInput,
  handler: () => Promise<WebhookProcessingResult>
): Promise<WebhookProcessingResult>

// Utility methods
getEventStatus(eventId: string)
getFailedEvents(limit = 100)
cleanupOldEvents(daysToKeep = 30)

Integration Pattern

Before (No Idempotency)

export async function POST(request: NextRequest) {
  const body = await request.json();

  // Check for duplicate manually
  const existing = await prisma.message.findFirst({
    where: { externalId: body.message_id },
  });

  if (existing) {
    return NextResponse.json({ alreadyProcessed: true });
  }

  // Process webhook
  const result = await processNewMessage(body);

  return NextResponse.json(result);
}

Issues:

• ❌ Race condition: Two concurrent requests can both pass the duplicate check
• ❌ No retry tracking
• ❌ No failure audit trail
• ❌ Duplicate checks scattered across codebase

After (With Idempotency)

import { webhookIdempotencyService } from '@/lib/services/webhooks';

export async function POST(request: NextRequest) {
  const body = await request.json();

  // Extract unique event ID from payload
  const eventId = body.message?.message_id || `fallback-${Date.now()}`;

  // Process with idempotency guarantees
  const result = await webhookIdempotencyService.processWebhook(
    {
      eventId: `yelp:${eventId}`, // Prefix with source for global uniqueness
      source: 'yelp',
      eventType: body.event_type,
      payload: body,
    },
    async () => {
      // Your webhook processing logic here
      return await processNewMessage(body);
    }
  );

  return NextResponse.json(result);
}

Benefits:

• ✅ Atomic duplicate check via unique constraint
• ✅ Race condition protected (Prisma P2002 error = concurrent processing)
• ✅ Automatic retry tracking
• ✅ Full audit trail of all webhook events
• ✅ Centralized error handling

Updated Webhook Handlers

1. Yelp Webhook (/app/api/webhooks/yelp/route.ts)

Event ID Extraction:

• message.created: body.message.message_id
• message.delivered: body.message_id
• message.read: body.message_id
• review.updated: body.review.review_id

Event ID Format: yelp:{provider_event_id}

2. Facebook Webhook (/app/api/webhooks/facebook/route.ts)

Event ID Extraction:

• Messages: event.message.mid
• Comments: value.comment_id

Event ID Format: facebook:{provider_event_id}

Note: Facebook webhooks are processed in handleMessagingEvent and handleChangeEvent functions, which are called for each entry in the webhook batch.

3. Mailgun Webhook (/app/api/webhooks/mailgun/route.ts)

Event ID Extraction:

• Inbound emails: inbound.providerMessageId (Mailgun Message-Id)
• Event webhooks: Already handled by provider

Event ID Format: mailgun:{provider_event_id}

4. Twilio Status Webhook (/app/api/webhooks/twilio/status/route.ts)

Event ID Extraction:

• Status callbacks: MessageSid + MessageStatus
• Each status transition (queued → sent → delivered) is a separate event

Event ID Format: twilio-status:{MessageSid}:{MessageStatus}

Note: The status is included in the event ID because the same message can receive multiple status callbacks as it progresses through the delivery lifecycle.

5. Sentry Webhook (/app/api/webhooks/sentry/route.ts)

Event ID Extraction:

• Issue webhooks: project.slug + issue.shortId + action

Event ID Format: sentry:{project_slug}:{issue_short_id}:{action}

Note: The action (created, resolved, assigned, etc.) is included to allow the same issue to be processed for different actions.

6. WhatsApp Webhook (/app/api/webhooks/whatsapp/route.ts)

Event ID Extraction:

• Incoming messages: message.id
• Status updates: status.id + status.status

Event ID Format:

• Messages: whatsapp:message:{message_id}
• Status: whatsapp:status:{status_id}:{status}

Note: Status updates (sent, delivered, read, failed) include the status in the event ID to track each status transition separately.

Testing Idempotency

Unit Test Pattern

import { webhookIdempotencyService } from '@/lib/services/webhooks';

describe('Webhook Idempotency', () => {
  it('should process webhook only once', async () => {
    const eventId = 'test-event-123';
    let processCount = 0;

    const handler = async () => {
      processCount++;
      return { success: true, processed: true };
    };

    // First call - should process
    const result1 = await webhookIdempotencyService.processWebhook(
      {
        eventId,
        source: 'test',
        eventType: 'test.event',
        payload: {},
      },
      handler
    );

    expect(result1.success).toBe(true);
    expect(result1.alreadyProcessed).toBeUndefined();
    expect(processCount).toBe(1);

    // Second call - should skip
    const result2 = await webhookIdempotencyService.processWebhook(
      {
        eventId,
        source: 'test',
        eventType: 'test.event',
        payload: {},
      },
      handler
    );

    expect(result2.success).toBe(true);
    expect(result2.alreadyProcessed).toBe(true);
    expect(processCount).toBe(1); // Not incremented!
  });

  it('should handle concurrent webhook deliveries', async () => {
    const eventId = 'concurrent-event-456';
    let processCount = 0;

    const handler = async () => {
      await new Promise((resolve) => setTimeout(resolve, 100)); // Simulate work
      processCount++;
      return { success: true };
    };

    // Fire 5 concurrent requests
    const promises = Array(5)
      .fill(null)
      .map(() =>
        webhookIdempotencyService.processWebhook(
          {
            eventId,
            source: 'test',
            eventType: 'test.concurrent',
            payload: {},
          },
          handler
        )
      );

    const results = await Promise.all(promises);

    // Only ONE should have processed
    const processedCount = results.filter((r) => !r.alreadyProcessed).length;
    expect(processedCount).toBe(1);
    expect(processCount).toBe(1);
  });
});

Integration Test Pattern

describe('POST /api/webhooks/yelp', () => {
  it('should handle duplicate webhook deliveries', async () => {
    const webhookPayload = {
      event_type: 'message.created',
      message: {
        message_id: 'test-msg-789',
        business_id: 'test-biz',
        conversation_id: 'test-conv',
        sender: { id: 'customer-1', type: 'customer' },
        recipient: { id: 'business-1', type: 'business' },
        content: 'Test message',
        timestamp: Date.now(),
      },
    };

    // First delivery
    const response1 = await POST(
      new NextRequest('http://localhost/api/webhooks/yelp', {
        method: 'POST',
        body: JSON.stringify(webhookPayload),
      })
    );

    const data1 = await response1.json();
    expect(data1.success).toBe(true);

    // Second delivery (duplicate)
    const response2 = await POST(
      new NextRequest('http://localhost/api/webhooks/yelp', {
        method: 'POST',
        body: JSON.stringify(webhookPayload),
      })
    );

    const data2 = await response2.json();
    expect(data2.success).toBe(true);
    expect(data2.alreadyProcessed).toBe(true);

    // Verify only one message was created
    const messages = await prisma.message.findMany({
      where: { externalId: 'test-msg-789' },
    });
    expect(messages).toHaveLength(1);
  });
});

Maintenance

Cleanup Old Events

Run periodically (e.g., daily cron job):

import { webhookIdempotencyService } from '@/lib/services/webhooks';

// Delete completed events older than 30 days
const deletedCount = await webhookIdempotencyService.cleanupOldEvents(30);
console.log(`Cleaned up ${deletedCount} old webhook events`);

Retry Failed Events

import { webhookIdempotencyService } from '@/lib/services/webhooks';

// Get failed events for retry
const failedEvents = await webhookIdempotencyService.getFailedEvents(100);

for (const event of failedEvents) {
  // Re-process by triggering webhook endpoint
  // or manually invoke handler based on source/eventType
}

Monitor Webhook Health

// Query webhook event statistics
const stats = await prisma.webhookEvent.groupBy({
  by: ['source', 'status'],
  _count: true,
  where: {
    createdAt: {
      gte: new Date(Date.now() - 24 * 60 * 60 * 1000), // Last 24 hours
    },
  },
});

// Example output:
// [
//   { source: 'yelp', status: 'completed', _count: 1234 },
//   { source: 'yelp', status: 'failed', _count: 5 },
//   { source: 'facebook', status: 'completed', _count: 567 },
//   { source: 'mailgun', status: 'completed', _count: 89 }
// ]

Migration Guide

Running the Migration

# Apply migration to create WebhookEvent table
npx prisma migrate deploy

# Or for development:
npx prisma migrate dev

Rollback Plan

If issues arise, the webhook handlers will continue to work without the idempotency service (though without duplicate protection). To fully rollback:

1. Remove idempotency service calls from webhook handlers
2. Restore original duplicate check logic
3. Drop WebhookEvent table:

DROP TABLE "WebhookEvent";

Performance Considerations

Database Load

• Each webhook creates one row in WebhookEvent table
• Indexes on eventId, source, eventType, status ensure fast lookups
• Regular cleanup prevents table bloat

Latency Impact

• Additional ~10-50ms per webhook for idempotency check
• Trade-off: Small latency increase for guaranteed correctness
• Most webhook providers expect <30s response time, so well within limits

Scalability

• Unique constraint on eventId provides database-level locking
• No application-level distributed lock needed
• Scales horizontally with read replicas for queries

Best Practices

1. Always prefix eventId with source: {source}:{provider_event_id}
2. Use provider's unique ID when available: Message ID, review ID, etc.
3. Fallback to timestamp only when no unique ID exists
4. Return success for already-processed events: Don't return errors for duplicates
5. Log idempotent skips: Important for debugging webhook delivery issues
6. Clean up old events regularly: Prevent table bloat
7. Monitor failed events: Set up alerts for high failure rates

Security Considerations

• Webhook signature validation happens before idempotency check
• Invalid signatures are rejected without creating WebhookEvent records
• Payload is stored in JSONB field for debugging, but should not contain secrets
• Failed event error messages are truncated to 500 chars to prevent log spam

Future Enhancements

1. Dead Letter Queue: Move permanently failed events to separate table
2. Automatic Retry: Built-in exponential backoff retry logic
3. Webhook Replay: Admin UI to replay failed/completed events
4. Rate Limiting: Per-source rate limiting to prevent abuse
5. Metrics Dashboard: Real-time webhook processing stats
6. Event Sourcing: Use WebhookEvent as source of truth for rebuilding state

References

• Migration: /prisma/migrations/20251212153330_add_webhook_event_table/migration.sql
• Service: /lib/services/webhooks/webhookIdempotencyService.ts
• Yelp Webhook: /app/api/webhooks/yelp/route.ts
• Facebook Webhook: /app/api/webhooks/facebook/route.ts
• Mailgun Webhook: /app/api/webhooks/mailgun/route.ts
• Twilio Status Webhook: /app/api/webhooks/twilio/status/route.ts
• Sentry Webhook: /app/api/webhooks/sentry/route.ts
• WhatsApp Webhook: /app/api/webhooks/whatsapp/route.ts