Storing Webhook Events in a Database

In this tutorial, you will learn about Storing Webhook Events in a Database. We cover key concepts, practical examples, and best practices to help you master this topic.

Persisting webhook events in a database enables audit trails, event replay, debugging, and compliance. Whether you are a provider storing delivery history or a consumer storing received events, a well-designed schema supports querying, deduplication, and retention policies. This lesson covers database design patterns for webhook event storage.

What You'll Learn

• Design a normalized schema for storing webhook events and deliveries
• Implement event deduplication at the database level
• Build event replay and audit query capabilities
• Manage data retention and archival strategies

Why It Matters

Webhook events are a record of business activity. Losing them means losing auditability. Storing events properly enables you to replay missed deliveries, investigate integration issues, comply with regulatory requirements, and provide customer support with event-level visibility.

Real-World Use

• Stripe stores all webhook delivery attempts for 30 days, accessible via dashboard
• GitHub provides a delivery history UI for each webhook, showing payloads and responses
• Shopify allows merchants to view recent webhook deliveries and manually replay them
• Financial platforms store webhook events for years to meet compliance requirements

Mermaid Flow

graph LR
    A[Webhook Event] --> B[Normalize]
    B --> C[Events Table]
    B --> D[Payload Table]
    C --> E[Deliveries Table]
    E --> F[Delivery Attempts Table]
    C --> G[Event Archive]
    E --> H[Query: Recent Events]
    E --> I[Query: Failed Deliveries]
    C --> J[Replay Function]
    J --> K[Re-queue Delivery]

Teacher's Corner

Emphasize normalization: separate the event definition from its delivery attempts. One event can have multiple delivery attempts. Use JSON columns for flexible payloads. Discuss the trade-off between storing raw payloads (simple, debuggable) vs. storing normalized fields (queryable, harder to maintain schema changes).

Code Examples

Example 1: PostgreSQL Schema for Webhook Events

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE webhook_events (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    event_type VARCHAR(255) NOT NULL,
    event_id VARCHAR(255) NOT NULL UNIQUE,
    source VARCHAR(255) NOT NULL,
    payload JSONB NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    expires_at TIMESTAMPTZ NOT NULL
);

CREATE TABLE webhook_deliveries (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    event_id UUID NOT NULL REFERENCES webhook_events(id),
    subscriber_url TEXT NOT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'pending',
    status_code INTEGER,
    response_body TEXT,
    attempt_count INTEGER NOT NULL DEFAULT 0,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    completed_at TIMESTAMPTZ
);

CREATE INDEX idx_events_type ON webhook_events(event_type);
CREATE INDEX idx_events_created ON webhook_events(created_at DESC);
CREATE INDEX idx_deliveries_status ON webhook_deliveries(status);
CREATE INDEX idx_deliveries_event ON webhook_deliveries(event_id);

Expected Output: Two related tables with proper indexes for querying events and their delivery attempts.

Example 2: Insert Event with Deduplication

import psycopg2
from psycopg2.extras import Json

def insert_event(conn, event_type, event_id, source, payload, ttl_days=30):
    with conn.cursor() as cur:
        try:
            cur.execute("""
                INSERT INTO webhook_events
                    (event_type, event_id, source, payload, expires_at)
                VALUES (%s, %s, %s, %s, NOW() + INTERVAL %s DAY)
                ON CONFLICT (event_id) DO NOTHING
                RETURNING id
            """, (event_type, event_id, source, Json(payload), ttl_days))
            result = cur.fetchone()
            conn.commit()
            if result:
                return {"status": "inserted", "id": result[0]}
            return {"status": "duplicate", "id": None}
        except Exception as e:
            conn.rollback()
            return {"status": "error", "error": str(e)}

conn = psycopg2.connect("dbname=webhooks")
print(insert_event(conn, "order.created", "evt-001", "shopify", {"order_id": 123}))
print(insert_event(conn, "order.created", "evt-001", "shopify", {"order_id": 123}))
conn.close()

Expected Output: First call returns {"status": "inserted", "id": "..."}. Second call returns {"status": "duplicate", "id": null}.

Example 3: Replay Failed Deliveries

import psycopg2
from datetime import datetime, timedelta

def find_failed_deliveries(conn, since_hours=24):
    with conn.cursor() as cur:
        cur.execute("""
            SELECT e.event_id, e.payload, d.subscriber_url, d.id as delivery_id
            FROM webhook_deliveries d
            JOIN webhook_events e ON d.event_id = e.id
            WHERE d.status IN ('failed', 'pending')
            AND d.created_at >= NOW() - INTERVAL %s HOUR
            ORDER BY d.created_at DESC
        """, (since_hours,))
        return cur.fetchall()

def replay_event(conn, delivery_id):
    with conn.cursor() as cur:
        cur.execute("""
            UPDATE webhook_deliveries
            SET status = 'pending', attempt_count = attempt_count + 1
            WHERE id = %s
        """, (delivery_id,))
        conn.commit()

failed = find_failed_deliveries(conn, 48)
for event_id, payload, url, delivery_id in failed:
    print(f"Re-queuing {event_id} to {url}")
    replay_event(conn, delivery_id)

Expected Output: Lists all failed deliveries and resets their status to pending for reprocessing.

Common Mistakes

1. Not setting a UNIQUE constraint on the provider's event ID, allowing duplicate storage
2. Storing payloads as TEXT instead of JSONB, losing the ability to query inside the payload
3. Not indexing timestamp columns, causing slow queries on large event tables
4. Deleting events immediately after delivery instead of implementing retention policies
5. Not separating event records from delivery attempt records (single table design)
6. Storing large response bodies without truncation, inflating database size
7. Not using table Partitioning for high-volume webhook event tables

Practice Questions

1. Why should events and deliveries be stored in separate tables?
2. How does ON CONFLICT DO NOTHING help with webhook deduplication?
3. What are the benefits of using JSONB over TEXT for payload storage?
4. How would you implement a 90-day retention policy with automatic cleanup?
5. Challenge: Design a database schema for a webhook provider that supports per-subscriber retry policies, delivery logging with response bodies truncated to 1KB, event archiving to cold storage after 30 days, and querying delivery success rates per subscriber per day.

FAQ

Mini Project

Build a Flask application with PostgreSQL that: (1) accepts webhook events at /events and stores them with deduplication, (2) simulates delivery attempts and stores results in the deliveries table, (3) provides /events endpoint to list events with delivery status, (4) provides /events/replay endpoint to re-queue failed deliveries, and (5) provides /stats endpoint showing daily success rates. Use Docker to run PostgreSQL.

What's Next

Now that you can store webhook events, learn how to handle dead letter queues for events that cannot be delivered after all retries.