# Webhook ingester

> A self-hosted, open-source service that receives, verifies, and stores every MailBlastr webhook event in your own database — with signature verification, idempotent storage, and connectors for Postgres, MySQL, MongoDB, and data warehouses.

The **MailBlastr Webhook Ingester** is a small, self-hosted application that receives, verifies, and stores all of your webhook events in your own database. Deploy it to your own infrastructure and gain full control over your email event data.

You can always [build your own handler](https://www.mailblastr.com/docs/webhooks/store-data), but the ingester is a production-ready drop-in that handles the parts that are easy to get wrong: signature verification, deduplication, and durable storage.

> **Note:** For why you should keep your own copy of webhook data, see the [data storage guide](https://www.mailblastr.com/docs/webhooks/store-data).

## Why use the ingester?

Compared with hand-rolling a handler, the ingester ships with:

- **Signature verification** of the `mb-signature` / `svix-*` headers, so only authentic MailBlastr deliveries are stored.
- **Idempotent storage** keyed on the `svix-id` header, so duplicate deliveries (from retries) never create duplicate rows.
- **Multiple database connectors** including PostgreSQL, MySQL, MongoDB, and common data warehouses.
- **One-command deployment** to any container host — it is a single stateless HTTP service.

## Supported databases

Each connector is exposed as its own path. Register a webhook endpoint at `https://your-ingester.example.com/{connector}` and the ingester writes to the matching backend.

| Database | Endpoint | Best for |
| --- | --- | --- |
| Supabase | `/supabase` | Quick setup with managed Postgres |
| Neon | `/neon` | Serverless Postgres with branching |
| PostgreSQL | `/postgresql` | Self-hosted or managed Postgres |
| MySQL | `/mysql` | Self-hosted or managed MySQL |
| PlanetScale | `/planetscale` | Serverless MySQL |
| MongoDB | `/mongodb` | Document database (Atlas or self-hosted) |
| Snowflake | `/snowflake` | Data warehousing and analytics |
| BigQuery | `/bigquery` | Google Cloud analytics |
| ClickHouse | `/clickhouse` | High-performance analytics |

## Quick start

1. **Clone and install** — Pull the ingester source, then install dependencies with your package manager.
2. **Configure environment variables** — Copy the example env file and set MB_WEBHOOK_SECRET (your webhook signing secret from the dashboard) plus the connection string for your chosen database.
3. **Set up your database** — Apply the provided schema for your backend (the schema files live in the schemas/ directory). This creates the event tables described below.
4. **Deploy and register** — Deploy the service, then register the endpoint URL https://your-ingester.example.com/{connector} in the dashboard and select every event type you want to store.

**Configure**

```bash
# 1. install
git clone https://github.com/your-org/mailblastr-webhooks-ingester.git
cd mailblastr-webhooks-ingester
pnpm install

# 2. environment
cp .env.example .env.local
```

**.env.local**

```env
# Required: your webhook signing secret from the MailBlastr dashboard
MB_WEBHOOK_SECRET=whsec_xxxxxxxxxxxxxxxxxxxxx

# Database credentials (example for PostgreSQL)
POSTGRESQL_URL=postgresql://user:password@host:5432/database
```

> **Note:** Get your signing secret from the dashboard when you create the webhook. The secret is shown once and is used by the ingester to verify every delivery.

## Database schema

The ingester creates three tables, one per resource family of events:

| Table | Description |
| --- | --- |
| `mb_wh_emails` | All email events (e.g. `email.sent`, `email.delivered`, `email.bounced`, `email.opened`, `email.clicked`). |
| `mb_wh_contacts` | Contact lifecycle events (`contact.created`, `contact.updated`, `contact.deleted`). |
| `mb_wh_domains` | Domain lifecycle events (`domain.created`, `domain.updated`, `domain.deleted`). |

Every row includes a small set of common columns plus the event-specific payload:

- `svix_id` — the unique webhook delivery id, used as the idempotency key.
- `event_type` — the dotted event name, e.g. `email.delivered`.
- `event_created_at` — when the event occurred (from the payload `created_at`).
- `webhook_received_at` — when your ingester received the delivery.
- Event-specific fields — recipient, subject, bounce detail, clicked URL, and so on.

## Idempotency

The ingester deduplicates automatically. Every delivery carries a unique `svix-id` header, and the ingester upserts on that id. If MailBlastr retries a delivery after a transient failure, the duplicate is safely ignored without creating a second row.

## Example query

Once events are flowing, you can run analytics directly against the tables. For example, daily counts of each email event type:

**Email events by day (PostgreSQL)**

```sql
SELECT
  DATE(event_created_at) AS day,
  event_type,
  COUNT(*) AS count
FROM mb_wh_emails
GROUP BY DATE(event_created_at), event_type
ORDER BY day DESC, event_type;
```

## Data retention

By default the ingester keeps events indefinitely. To enforce a retention window, schedule a periodic delete. Most databases also support partitioning by date so you can drop old partitions cheaply.

**Delete events older than 90 days**

```sql
DELETE FROM mb_wh_emails
WHERE event_created_at < NOW() - INTERVAL '90 days';
```

## Troubleshooting

- **Signature verification fails** — confirm `MB_WEBHOOK_SECRET` matches the signing secret in the dashboard, that you verify against the **raw** request body, and that the secret has not been rotated.
- **Database connection errors** — verify your credentials, confirm the schema has been applied, and check that the database is reachable from where the ingester runs (firewall / network rules).
- **Webhooks not received** — confirm the endpoint URL is publicly reachable, check the endpoint status in the dashboard, and ensure your service responds `2xx` so the delivery is not retried indefinitely.