# Help-desk ingestion that proves nothing went missing

> A Go service that mirrors Zendesk tickets into Postgres from webhooks, then keeps comparing the mirror with Zendesk so dropped events are found and repaired without anyone looking.

Source: https://simasrazinskas.com/work/idempotent-webhook-ingestion  
Published: 2026-06-30  
Reviewed: 2026-10-07

## At a glance

- **Problem**: Support automation for a consumer subscription business read tickets from a Postgres mirror of Zendesk fed by webhooks. When a webhook never arrived, the ticket was simply absent and nothing noticed.
- **Constraint**: Zendesk can drop, repeat and reorder webhooks, and the mirror had to stay correct through deploys, crashes and API rate limits without manual checking.
- **What I built**: A Go service where the webhook table is the durable work queue, ticket writes are guarded upserts on the help-desk ID, one reconciler compares the mirror with Zendesk on three schedules, and 46 anomaly checks alert the team.
- **Result**: A dropped webhook now becomes a gap that is found and refilled within hours, and retries survive deploys because they live in the database.
- **Stack**: Go · Postgres · Zendesk API · Prometheus · Slack

## Overview

A consumer subscription business runs its customer support in Zendesk, across several brands and thousands of tickets a day.
An AI support agent, dashboards and routing rules all read a Postgres mirror of those tickets.
The mirror was fed by Zendesk webhooks, first through n8n and then through a Go service I wrote to replace it.

Webhooks are a weak contract.
Zendesk retries them, sends several per ticket within seconds, delivers them out of order, and occasionally does not send them at all.
A missing webhook leaves no trace on the receiving side, so the mirror can stay wrong indefinitely with every dashboard looking healthy.

The goal was a mirror that is correct by default and that checks itself, so nobody has to go looking for lost tickets.

## The events table is the queue

Every request is authenticated twice before anything is stored.
An API key header is compared in constant time, then the Zendesk signature is verified: HMAC-SHA256 over the signature timestamp plus the raw body, with timestamps more than 5 minutes off rejected.
The handler then inserts the raw payload into an events table and answers 202.

The first version handed events to workers through an in-memory channel and scheduled retries with in-process timers.
A deploy or crash dropped both.
In a staged redesign the events table became the queue itself, and retries moved into the row as a `next_attempt_at` timestamp.

```sql
UPDATE events e
SET status = 'processing', started_at = now(), attempts = e.attempts + 1
WHERE e.id IN (
  SELECT id FROM events
  WHERE (status = 'queued' AND (next_attempt_at IS NULL OR next_attempt_at <= now()))
     OR (status = 'processing' AND started_at < now() - interval '5 minutes')
  ORDER BY received_at
  LIMIT $1
  FOR UPDATE SKIP LOCKED
)
RETURNING e.id, e.ticket_id, e.attempts, e.raw_payload;
```

*Claiming work: the second condition is the whole crash-recovery mechanism*

A row stuck in `processing` for longer than the 5-minute lease is claimable again, so a worker that died mid-pipeline needs no separate recovery loop.
Failures back off from 2 seconds, doubling with up to 20% jitter, capped at 5 minutes, for at most 5 attempts.
Zendesk validation and permission errors fail at once instead of spending the retry budget.
Failed rows stay in the table and can be replayed from an admin endpoint.

## Coalescing, and the deadlock it hid

A single ticket update often produces a burst of webhooks.
The claim query picks the oldest ready row as a lead, waits 10 seconds after that ticket's first event, and folds every other ready row for the same ticket into one batch.
Tickets already being processed in the same process are excluded.

A batch does not trust the webhook body.
It fetches the ticket and its comments from the Zendesk API and upserts them keyed on Zendesk's own IDs.
The ticket upsert only applies when the incoming `updated_at` is not older than the stored one, so a late or repeated batch cannot move a ticket backwards.
The webhook payload contributes only two signals the API fetch cannot supply: whether a customer posted a public comment, and whether the status changed.

A 20-way concurrent-claimer stress test then exposed a deadlock, reproducible in about 1 run in 20.
During a deploy overlap two processes can each take a different lead row for the same ticket.
Each then tried to lock the other's rows while folding siblings, and Postgres killed one of them.
The fix locks the sibling set with a second `FOR UPDATE SKIP LOCKED` pass, so a row already claimed elsewhere is skipped instead of waited on.

## Webhooks that never arrived

The incident that shaped the rest of the design was a batch of tickets that existed in Zendesk and had zero rows in the events table.
Zendesk had silently skipped their creation webhooks.
A sync job existed, but it only ran after a period with no webhooks at all, and with webhooks arriving every few seconds it never ran.

Four fixes shipped together: a forced incremental sweep every hour, a missing-ID detector, a paging check on missing IDs, and a backfill command that refreshes a file of ticket IDs to clean up the backlog.
The silence-based trigger was later deleted, because a full webhook outage already has its own alert.

The detectors then merged into one reconciler that ticks every 5 minutes and runs three sources on their own cadences:

- Every hour, a sweep of Zendesk's incremental tickets API from a persisted cursor.
- Every 6 hours, a `generate_series` over the recent ID range (48 hours back), anti-joined against the tickets table, which finds tickets Zendesk never announced.
- Every 4 hours, a recheck of open, new and pending tickets whose local copy has not changed for 72 hours.

All three feed the same refresh pipeline with shared concurrency, and a ticket refreshed by one source is skipped by the others in the same tick.

## Tombstones for IDs that were never ours

The anti-join assumed ticket IDs are contiguous within one Zendesk account.
They are not: Zendesk allocates IDs across a shared cluster of accounts, so 1–2% of IDs in any range belong to someone else and return 404 forever.
Add genuinely deleted tickets, and the missing-ID gauge sat permanently at the 100-ID batch cap, the alert never cleared, and the detector made about 200 pointless API calls every 6 hours.

Any path that sees a 404 for a ticket ID now writes a tombstone row, recording first and last sighting, a sighting count and which path wrote it.
The anti-join excludes tombstoned IDs.
Tombstones are permanent; a restored ticket needs its row cleared or a one-off backfill.
A separate check alerts if tombstones suddenly grow faster than the usual handful a day.

## 46 checks that alert people

The service runs 46 anomaly checks in-process.
The fast tier runs every tick and reads deltas of the service's own Prometheus counters plus cheap probes: webhook silence, webhook lag, rate-limited Zendesk calls, retry storms, stuck `processing` rows, queue backlog.
The integrity tier runs hourly over a recent window of live tables, with a full sweep at boot and every 24 hours, and looks for orphaned comments, inverted or future timestamps, malformed arrays and projection drift.
Each check is classed as page, digest or log.
Page and digest results go to Slack, open anomalies are acknowledged by an operator, and resolved ones are acknowledged automatically.

The checks alert people and repair nothing.
Repair is the reconciler's job, and it runs on its schedule whether or not anything fired.
The suite started at 21 checks; two were later deleted after it turned out they queried columns that never existed in production.

Rate limits are handled in the Zendesk client: 429 and 5xx responses are retried up to 4 times, waiting for the `Retry-After` header when Zendesk sends one.
The backfill command defaults to 4 concurrent fetchers to stay inside the account's API budget.

## Limitations

- Detection is periodic. A ticket whose webhook was dropped can be missing for up to 6 hours, or longer if it falls outside the 48-hour window and needs a manual backfill.
- The reconciler has no cross-process lock. A deploy overlap can run two passes at once, which is safe only because every refresh is an idempotent upsert.
- The monotonic guard protects ticket rows. Ordering across tickets, and between a ticket and its derived tables, is not guaranteed.
- Thresholds are tuned by hand. Two checks shipped broken, which means the checks themselves need review like any other code.
- A correctly signed webhook carrying wrong data is stored faithfully. Nothing here validates Zendesk's content.
- With hindsight, the gap detector should have shipped with the first version. The silence-based trigger looked like coverage and was not.

## Related case studies

- [A supervised AI support agent grounded in real customer data](https://simasrazinskas.com/work/ai-customer-support-agent): Support staff approve ready-made replies instead of writing every answer from scratch.
- [One job queue instead of five ways to pick up work](https://simasrazinskas.com/work/durable-job-queue): Background work survives deploys and crashes, and retries no longer repeat customer-facing actions.

## Start with one process

A one-hour call costs €80. Afterwards you get a written plan, whether or not we work together.

[Book a call](https://simasrazinskas.com/book-me) · [simas@simasrazinskas.com](mailto:simas@simasrazinskas.com)