Log order webhooks to Google Sheets with dead-letter queue and Gmail alerts

Quick overview

This workflow receives order-style webhooks with header authentication, validates and logs them, and upserts the event data into Google Sheets with retries, a dead-letter queue in n8n Data Tables, a status-check endpoint, scheduled replays, and daily/incident email notifications via Gmail.

How it works

1. Receives POST requests on a header-authenticated webhook endpoint, generates or reads an X-Request-ID, validates the JSON payload, and logs invalid requests to an n8n Data Table before returning a 400 response.
2. For valid requests, formats a row and upserts it into a Google Sheets tab using “Request ID” as the idempotent key to avoid duplicates.
3. If the Google Sheets write fails, retries up to two more times with exponential backoff before giving up.
4. After repeated failures, stores the full request payload in an n8n Data Table dead-letter queue, logs the request as queued, returns a 202 response, and intentionally raises a workflow error to trigger an alert.
5. If both Google Sheets and the dead-letter queue write fail, returns a 503 response with a Retry-After header and raises a critical error for alerting.
6. Runs every 15 minutes to load up to 50 pending dead-letter requests, rebuilds rows from the stored payloads, batch upserts them into Google Sheets, and marks queued items as resolved while logging the replay.
7. Exposes a GET status webhook that looks up the latest request log entry in an n8n Data Table and returns 200/404/400 depending on whether the request ID is present and known.
8. Runs daily at 07:00 to compute 24-hour ingest stats and queue backlog from n8n Data Tables and emails an HTML health report via Gmail, while a built-in error trigger emails incident alerts (or logs an INCIDENT entry if email fails).

Setup

1. Create and connect credentials for Header Auth (API key), Google Sheets, and Gmail in n8n.
2. Create a Google Spreadsheet with a tab named “Events” and update the spreadsheet ID in both Google Sheets upsert actions.
3. Create two n8n Data Tables named ingest_request_log and ingest_dead_letters with the string columns expected by the workflow (request_id, received_at, status, http_status, detail; and request_id, received_at, payload_json, last_error, attempts, status, resolved_at).
4. Update the report and alert recipient addresses in the workflow’s settings values (report_email_to and alert_email_to) and adjust the “stuck” threshold if needed.
5. Activate the workflow, then copy the webhook URLs and configure your source system to POST to /webhook/ingest (and optionally query /webhook/ingest-status?request_id=… using the same API key).