> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cfosilvia.com/llms.txt
> Use this file to discover all available pages before exploring further.

# WEBHOOK DEDUPE AND BACKPRESSURE PLAN

# SnapTrade Webhook Dedupe and Backpressure Plan

## Context

On April 29, 2026, production saw a burst of SnapTrade-related background work. The logs showed many concurrent calls to:

* `/api/snaptrade/webhook`
* `/api/internal/process-snaptrade-holdings-sync`
* `/api/internal/process-snaptrade-transactions-sync`
* `/api/cron/process-stale-snaptrade`

The pattern was not a DDoS. It was an internal fanout problem:

1. SnapTrade sent multiple webhook events for accounts/connections.
2. The webhook handler created Supabase job rows and published QStash jobs.
3. QStash delivered many long-running Next.js workers concurrently.
4. Workers retried SnapTrade `429` and gateway errors by sleeping inside Vercel functions.
5. Long-lived workers saturated provider capacity and contributed to Vercel/Supabase timeouts on user-facing routes.

QStash was already in use, but it was acting as delivery/retry infrastructure only. It did not dedupe exact SnapTrade webhooks or coalesce many account events into a single active sync.

## Current Implementation

The fix adds Redis-backed guards around the existing QStash flow.

New helper:

* `lib/snaptrade-webhook-guards.ts`

Redis keys:

* `snaptrade:webhook:{webhookId}`: exact webhook dedupe, 24 hour TTL.
* `snaptrade:account:queued:{workType}:{accountId}`: one queued job per account/work type, 30 minute TTL.
* `snaptrade:account:lock:{workType}:{accountId}`: one active worker per account/work type, 30 minute TTL.

Updated routes:

* `app/api/snaptrade/webhook/route.ts`
* `app/api/internal/process-snaptrade-holdings-sync/route.ts`
* `app/api/internal/process-snaptrade-transactions-sync/route.ts`
* `app/api/cron/schedule-stale-snaptrade/route.ts`
* `supabase/migrations/20260501_add_snaptrade_webhook_processed_unique_index.sql`

### Webhook Intake

The SnapTrade webhook route now:

1. Validates the webhook secret.
2. Extracts the SnapTrade webhook id, including the legacy typo field `webookId`.
3. Claims `snaptrade:webhook:{webhookId}` in Redis using `SET NX EX`.
4. Acknowledges duplicate webhook ids without queueing additional work.
5. Claims `snaptrade:account:queued:{workType}:{accountId}` before creating another holdings or transactions job.
6. Coalesces duplicate holdings/transactions webhooks for the same account while a job is already queued.
7. Awaits QStash publishing for background jobs.

### Worker Execution

The holdings and transactions workers now:

1. Verifies the QStash signature.
2. Claims `snaptrade:account:lock:{workType}:{accountId}` before doing SnapTrade API work.
3. Skips duplicate delivered jobs for the same account.
4. Releases both active and queued Redis keys in `finally`.

### Durable Backstop

Redis is the hot-path guard, but the database now also enforces webhook intake idempotency with:

* `snaptrade_webhook_events_queued_webhook_id_unique`

The index is partial:

* `webhook_id IS NOT NULL`
* `webhook_id !~ '^error_'`
* `processed IS TRUE`

This allows skipped/failed duplicate audit rows to exist while preventing the same SnapTrade webhook id from being accepted for duplicate queueing more than once. The existing `processed` column is used by webhook intake to mean "accepted and queued" and by workers to mean "finished", so historical dashboards that count `processed = true` should treat the migration as a duplicate cleanup.

The migration uses regular `CREATE UNIQUE INDEX` for standard Supabase migration compatibility. If `snaptrade_webhook_events` is large in production, apply the equivalent index concurrently outside a transaction during a maintenance window.

### QStash Backpressure

SnapTrade QStash flow control was reduced from:

```ts theme={null}
flowControl: {
  key: 'snaptrade-handlers',
  parallelism: 200,
  rate: 200,
}
```

to:

```ts theme={null}
flowControl: {
  key: 'snaptrade-handlers',
  parallelism: 100,
  rate: 100,
}
```

This cap applies to webhook-triggered holdings/transactions work and scheduled stale SnapTrade refresh jobs.

Sized to stay under SnapTrade's documented per-customer rate limit of 4000 req/min:
100 concurrent × \~5 SnapTrade calls per handler / \~10 sec window
≈ 50 calls/sec sustained = 3000/min (75% of limit, 25% headroom).

Per-account locks (`claimSnapTradeAccountLock`) prevent same-account
concurrency, so `100` across DIFFERENT accounts is safe and gives
recovery throughput. Going lower (e.g., `5/5`) would starve the system
during a backlog because only 5 different accounts could sync at once.

## Why Redis Is Needed

Supabase job rows are still useful as the durable audit trail, but they are not ideal for hot-path webhook coordination.

Redis is better for:

* Atomic `SET NX` claims.
* Short-lived dedupe keys.
* Fast per-account coalescing.
* Expiring stale locks automatically.
* Avoiding extra Supabase load during webhook bursts.

QStash remains the queue. Redis is the traffic cop.

## Remaining Gaps

This implementation covers holdings sync first because holdings workers were the clearest pressure point in the incident logs.

Still recommended:

1. Replace in-function SnapTrade retry sleeps with delayed QStash requeue.
2. Add a Redis provider cooldown key after SnapTrade `429`, for example `snaptrade:provider:cooldown`.
3. Add dashboard/log metrics for:
   * Duplicate webhook ids skipped.
   * Account jobs coalesced.
   * Active-lock skips.
   * QStash publish failures.
   * SnapTrade 429s.
4. Consider active-lock refresh inside long-running workers if delayed QStash requeue is deferred and production jobs approach the 30 minute TTL.

## Operational Knobs

Current settings:

* SnapTrade QStash `parallelism`: `100`
* SnapTrade QStash `rate`: `100`
* Webhook dedupe TTL: `24h`
* Account queued TTL: `30m`
* Account active-lock TTL: `30m`

If SnapTrade `429` rate climbs again, the first knob to turn down is
`parallelism` (and `rate` together) -- step to `75/75`, then `50/50`,
etc. Per-account locks already prevent same-account concurrency so any
positive value is safe; lower values trade recovery throughput for
provider gentleness.

Do not jump back to `200`. SnapTrade explicitly told us they were
seeing 429s from us at that level (incident postmortem 2026-05-01).

## Verification

After implementation:

* `npm run lint` passed with existing repository warnings.
* `npx tsc --noEmit --pretty false` passed.

Suggested production checks after deploy:

1. Watch Vercel logs for `Duplicate webhook`, `coalescing webhook`, and `already active for account`.
2. Watch SnapTrade `429` frequency.
3. Watch Vercel function duration for SnapTrade workers.
4. Watch user-facing route latency during the next scheduled stale SnapTrade run.
5. Confirm QStash retries are not building an unbounded backlog.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.