Storing Raw Paystack Webhook Payloads for Audit

Most developers process Paystack webhooks by extracting the fields they need, updating their database, and discarding the original payload. This works until it does not. Here are the situations where stored raw payloads save you:

Debugging production issues. A customer says they paid but their order is still pending. With stored payloads, you can search by email, reference, or amount and see exactly what Paystack sent. Without them, you are guessing based on your processed records, which may be incomplete or incorrect if the handler had a bug.

Replaying failed events. Your webhook handler crashed due to a deployment bug, and three hours of webhooks were received but not processed. If you stored the raw payloads, you can replay them once the bug is fixed. If you did not, those events are lost. Paystack retries, but only for a limited time. See Replaying Failed Webhooks Safely for the replay pattern.

Compliance and audit. Financial regulators in Nigeria (CBN), Kenya (CBK), and other African markets require businesses that process payments to maintain transaction records. Raw webhook payloads are strong evidence that a transaction occurred and when your system was notified. Auditors want to see the original data, not your interpretation of it.

Reconciliation. When your database says one thing and Paystack's dashboard says another, the raw payload is the tiebreaker. It tells you exactly what Paystack reported, byte for byte.

Schema changes. Paystack occasionally adds new fields to webhook payloads. If you only extract the fields your current code knows about, you lose the new data. Stored raw payloads preserve everything, so when you add support for a new field later, the historical data is already there.

Create a dedicated table for raw webhook payloads. Here is the schema in PostgreSQL (the most common choice for Paystack integrations in production):

CREATE TABLE webhook_events (
    id            BIGSERIAL PRIMARY KEY,
    event_type    VARCHAR(100) NOT NULL,
    reference     VARCHAR(255),
    raw_body      JSONB NOT NULL,
    headers       JSONB,
    signature     VARCHAR(128),
    processed     BOOLEAN DEFAULT FALSE,
    processed_at  TIMESTAMPTZ,
    error_message TEXT,
    received_at   TIMESTAMPTZ DEFAULT NOW(),
    created_at    TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_webhook_events_event_type ON webhook_events(event_type);
CREATE INDEX idx_webhook_events_reference ON webhook_events(reference);
CREATE INDEX idx_webhook_events_processed ON webhook_events(processed);
CREATE INDEX idx_webhook_events_received_at ON webhook_events(received_at);

Column explanations:

• event_type. Extracted from the payload ("charge.success", "transfer.failed", etc.). Stored as a separate column for fast filtering without querying into the JSONB.
• reference. The transaction or transfer reference, extracted from the payload. This is the most common lookup key when debugging customer issues.
• raw_body. The complete webhook payload as JSONB. Using JSONB instead of TEXT lets you query into the payload using Postgres JSON operators without parsing it in your application.
• headers. The HTTP headers from the request. Useful for verifying the signature later if needed.
• signature. The x-paystack-signature header value. Stored separately for quick access.
• processed. Whether this event has been successfully processed by your handler. Starts as false, set to true after processing completes.
• processed_at. When processing completed. Lets you calculate processing delay (time between received_at and processed_at).
• error_message. If processing failed, store the error message here for debugging.

For MySQL, use JSON type instead of JSONB. MySQL's JSON type is not as powerful for querying as Postgres JSONB, but it works for storage and basic lookups.

The order of operations matters. Store the raw payload first, then process it. Never process first and store later. If processing crashes, you want the stored record to be there.

Here is the pattern in Express (Node.js):

const express = require('express');
const crypto = require('crypto');
const { Pool } = require('pg');

const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const app = express();

app.post(
  '/webhook/paystack',
  express.raw({ type: 'application/json' }),
  async (req, res) => {
    // 1. Verify signature
    const signature = req.headers['x-paystack-signature'];
    const secret = process.env.PAYSTACK_SECRET_KEY;
    const hash = crypto.createHmac('sha512', secret).update(req.body).digest('hex');

    if (hash !== signature) {
      return res.status(401).send('Invalid signature');
    }

    const payload = JSON.parse(req.body.toString());
    const reference = payload.data.reference || payload.data.transfer_code || null;

    // 2. Store the raw payload FIRST
    const result = await pool.query(
      'INSERT INTO webhook_events (event_type, reference, raw_body, signature) ' +
      'VALUES ($1, $2, $3, $4) RETURNING id',
      [payload.event, reference, JSON.stringify(payload), signature]
    );

    const webhookId = result.rows[0].id;

    // 3. Return 200 immediately
    res.sendStatus(200);

    // 4. Process in the background (or dispatch to a queue)
    try {
      await processWebhookEvent(payload);

      await pool.query(
        'UPDATE webhook_events SET processed = TRUE, processed_at = NOW() WHERE id = $1',
        [webhookId]
      );
    } catch (err) {
      await pool.query(
        'UPDATE webhook_events SET error_message = $1 WHERE id = $2',
        [err.message, webhookId]
      );
    }
  }
);

Notice that we return 200 before processing starts. If you are using a queue (BullMQ, Celery, Laravel Queues), the processing step becomes a job dispatch instead of an inline call. Either way, the raw payload is already in the database before any processing happens.

If even the database insert fails (your database is down), return a 500 error. Paystack will retry the webhook, and when your database comes back, the next attempt will succeed. This is one of the rare cases where returning a non-200 status is the right thing to do.

The real value of stored payloads shows up when something goes wrong and you need to investigate. Here are the queries you will use most often:

Find all events for a specific transaction:

SELECT id, event_type, processed, received_at, error_message
FROM webhook_events
WHERE reference = 'TXN_abc123'
ORDER BY received_at;

This shows you the complete timeline: when Paystack sent the event, whether it was processed, and if it failed, why.

Find unprocessed events (events that were received but never processed):

SELECT id, event_type, reference, received_at, error_message
FROM webhook_events
WHERE processed = FALSE
  AND received_at < NOW() - INTERVAL '10 minutes'
ORDER BY received_at;

The 10-minute buffer avoids flagging events that are still being processed. Any event older than 10 minutes that is still unprocessed is worth investigating.

Find events by customer email (querying into the JSONB):

SELECT id, event_type, reference, received_at
FROM webhook_events
WHERE raw_body -> 'data' -> 'customer' ->> 'email' = 'customer@example.com'
ORDER BY received_at DESC
LIMIT 20;

This is where JSONB pays for itself. You can search by any field in the payload without having extracted it to a separate column.

Find duplicate events (same event sent multiple times by Paystack retries):

SELECT reference, event_type, COUNT(*) as delivery_count
FROM webhook_events
WHERE event_type = 'charge.success'
GROUP BY reference, event_type
HAVING COUNT(*) > 1
ORDER BY delivery_count DESC;

This query surfaces transactions where Paystack delivered the same event multiple times. If your handler is idempotent, this is fine. If not, these duplicates might have caused double-processing.

The webhook_events table doubles as an idempotency store. Before processing an event, check if the same event was already processed successfully:

async function processWebhookEvent(payload) {
  const reference = payload.data.reference;
  const eventType = payload.event;

  // Check if this exact event was already processed
  const existing = await pool.query(
    'SELECT id FROM webhook_events ' +
    'WHERE reference = $1 AND event_type = $2 AND processed = TRUE ' +
    'LIMIT 1',
    [reference, eventType]
  );

  if (existing.rows.length > 0) {
    console.log('Event already processed, skipping: ' + eventType + ' ' + reference);
    return;
  }

  // Process the event
  switch (eventType) {
    case 'charge.success':
      await handleChargeSuccess(payload.data);
      break;
    // ... other event types
  }
}

This approach handles both Paystack retries (the same event delivered multiple times) and queue retries (your worker processing the same job more than once). The combination of reference + event_type is the natural idempotency key because Paystack uses unique references for each transaction.

For the complete idempotency pattern including race condition handling with database locks, see Idempotent Webhook Handlers: The Complete Pattern.

Webhook payloads accumulate fast. If you process 500 transactions per day, that is about 15,000 records per month. Each record is small (a few KB), but over years, the table grows. You need a retention policy.

Regulatory requirements. Financial regulations in most African jurisdictions require keeping transaction records for 5 to 7 years. In Nigeria, the CBN guidelines specify a minimum of 5 years. In Kenya, the CBK requires 7 years. Check the specific requirements for your jurisdiction and payment license type.

Practical retention tiers:

• Hot (0 to 90 days). Keep in your primary database table with full indexes. This covers active debugging, customer support, and recent reconciliation needs.
• Warm (90 days to 2 years). Archive to a separate table or partition with fewer indexes. Still queryable, but queries are slower because the data is less frequently accessed.
• Cold (2 to 7 years). Export to cheap storage (S3, Google Cloud Storage) as compressed JSON files. Retrievable for compliance audits but not for daily operations.

Implement the hot-to-warm transition with a scheduled job:

-- Run monthly: move records older than 90 days to archive
INSERT INTO webhook_events_archive
SELECT * FROM webhook_events
WHERE received_at < NOW() - INTERVAL '90 days';

DELETE FROM webhook_events
WHERE received_at < NOW() - INTERVAL '90 days';

For the cold tier, use COPY to export to CSV, compress it, and upload to object storage. Tag the files by month so you can retrieve specific periods during an audit.

Never delete webhook records for processed payments that are still within the regulatory retention window. Even if you think you will never need them, an audit, a legal dispute, or a chargeback investigation can appear years later.

Webhook payloads contain sensitive data: customer emails, phone numbers, partial card details, and transaction amounts. Storing them creates a data protection obligation.

Access control. Restrict who can query the webhook_events table. In most applications, only backend services and senior engineers should have access. Do not expose this data through admin dashboards without authentication and authorization checks.

Encryption at rest. Enable database-level encryption. Postgres and MySQL both support transparent data encryption (TDE) or storage-level encryption. If you are on a managed database (RDS, Cloud SQL, Neon), encryption at rest is usually enabled by default.

Masking in logs. When you log webhook payloads for debugging, mask sensitive fields like customer email and card details. Log the event type and reference, which are enough for debugging without exposing personal data.

PCI compliance note. Paystack handles PCI compliance on their end, and their webhook payloads do not contain full card numbers. However, they do include the last four digits, card brand, and bank name. These are not PCI-scoped data, but they are still personal information that deserves protection under data privacy regulations like NDPR (Nigeria) and the Kenya Data Protection Act.

Redaction for old records. When records move to cold storage, consider redacting personal information that is no longer needed. Keep the transaction reference, amount, and event type (which you need for financial records), but replace the customer email and phone number with hashed values.

If you are using Django, create a model for the webhook events table:

# payments/models.py
from django.db import models


class WebhookEvent(models.Model):
    event_type = models.CharField(max_length=100, db_index=True)
    reference = models.CharField(max_length=255, null=True, blank=True, db_index=True)
    raw_body = models.JSONField()
    headers = models.JSONField(null=True, blank=True)
    signature = models.CharField(max_length=128, null=True, blank=True)
    processed = models.BooleanField(default=False, db_index=True)
    processed_at = models.DateTimeField(null=True, blank=True)
    error_message = models.TextField(null=True, blank=True)
    received_at = models.DateTimeField(auto_now_add=True)

    class Meta:
        ordering = ['-received_at']
        indexes = [
            models.Index(fields=['event_type', 'reference']),
        ]

For Laravel, create a migration:

// database/migrations/create_webhook_events_table.php
Schema::create('webhook_events', function (Blueprint $table) {
    $table->id();
    $table->string('event_type', 100)->index();
    $table->string('reference', 255)->nullable()->index();
    $table->json('raw_body');
    $table->json('headers')->nullable();
    $table->string('signature', 128)->nullable();
    $table->boolean('processed')->default(false)->index();
    $table->timestamp('processed_at')->nullable();
    $table->text('error_message')->nullable();
    $table->timestamp('received_at')->useCurrent();
    $table->timestamps();
});

Both approaches give you the same storage capabilities. The important thing is that the table exists and your webhook handler writes to it before doing anything else.

Storing raw payloads also lets you detect when Paystack sends an event that your handler does not process. Run a scheduled job (daily or hourly) that checks for unprocessed records:

-- Alert query: unprocessed events older than 30 minutes
SELECT COUNT(*) as unprocessed_count
FROM webhook_events
WHERE processed = FALSE
  AND received_at < NOW() - INTERVAL '30 minutes';

If this count is greater than zero, something is wrong. Either your handler crashed, your queue worker is down, or there is a bug in your processing logic. Set up an alert that fires when the count exceeds zero and keeps firing until it is resolved.

You can also cross-reference your stored payloads against the Paystack API. Once a day, fetch recent transactions from the Paystack Transaction List API and compare them against your webhook_events table. Any transaction that exists in Paystack but not in your table is a webhook you never received. This could indicate a network issue, a misconfigured webhook URL, or a Paystack infrastructure problem.

For the full reconciliation approach, see Reconciling Paystack Settlements Against Your Database.