Filter webhooks by event type in database query instead of memory

[DeFiVC]

Bug Description

The dispatchWebhook function in src/services/webhook-dispatcher.ts (lines 129-172) fetches ALL active webhooks from the database, then filters by event type in application code. This is inefficient and could be a performance issue at scale.

Location

src/services/webhook-dispatcher.ts lines 132-140

// Fetches ALL active webhooks
const activeWebhooks = await db
    .select()
    .from(webhooks)
    .where(eq(webhooks.active, true));

// Filters in memory
const listenersForEvent = activeWebhooks.filter((w) =>
    (w.events as string[]).includes(payload.event)
);

The Problem

1. Every webhook dispatch queries ALL active webhooks, regardless of event type
2. The filtering happens in JavaScript, not in the database
3. If there are 100 webhooks but only 2 listen for "enrollment.created", we still fetch all 100
4. The events column is a JSONB array, so we can use PostgreSQL's @> (contains) operator

Recommended Fix

Use PostgreSQL's JSONB containment operator to filter at the database level:

const listenersForEvent = await db
    .select()
    .from(webhooks)
    .where(
        and(
            eq(webhooks.active, true),
            sql`${webhooks.events} @> ${JSON.stringify([payload.event])}::jsonb`
        )
    );

This pushes the filtering to PostgreSQL, which can use a GIN index on the events column for efficient lookups.

Acceptance Criteria

• Filter webhooks by event type in the database query
• Consider adding a GIN index on the events column for performance
• Verify that only webhooks listening for the specific event are queried

Severity

low - Performance optimization, not a functional bug. Impact increases with webhook count.

[guptakumarranjeet150]

Hello @maintainers,

I can tackle Filter webhooks by event type in database query instead of memory. Proposed implementation roadmap:

• Analysis: Inspect current behavior and edge cases in ChainLearnOfficial/chainlearn-api
• Core Implementation: Implement backend logic for Filter webhooks by event type in database query instead of memory with strict schema validation and idempotent execution.
• Quality Assurance: Verify API response schemas, authentication middleware, and database rollback safety.

Looking forward to collaborating on this—please assign!