Webhook Support

[Entropy-rgb]

Problem Statement

No way to trigger external actions when query results meet conditions (e.g., "alert me when sales drop below $10K"). Users must manually monitor their data and cannot integrate BoloDB with external systems.

Proposed Solution

Implement webhook system that sends HTTP requests to external URLs when specified conditions are met.

Acceptance Criteria

• Webhook configuration in Settings
• Event triggers: query result, anomaly detected, schedule completed
• Payload customization (JSON template)
• Retry logic with exponential backoff
• Webhook testing (send test payload)
• Delivery log with success/failure status
• Secret signing for payload verification
• Rate limiting per webhook

Technical Approach

Backend Changes

1. Webhook model (backend/app/pgdatabase/models.py):

class Webhook(Base):
    __tablename__ = "webhooks"
    id = Column(UUID, primary_key=True)
    user_id = Column(UUID, ForeignKey("users.id"))
    workspace_id = Column(UUID, ForeignKey("workspaces.id"))
    name = Column(String, nullable=False)
    url = Column(String, nullable=False)
    secret = Column(String)  # For HMAC signing
    events = Column(JSON, default=[])  # ["query.result", "anomaly.detected"]
    is_active = Column(Boolean, default=True)
    payload_template = Column(JSON)  # Custom payload template
    last_triggered = Column(DateTime)
    created_at = Column(DateTime, default=func.now())

class WebhookDelivery(Base):
    __tablename__ = "webhook_deliveries"
    id = Column(UUID, primary_key=True)
    webhook_id = Column(UUID, ForeignKey("webhooks.id"))
    event = Column(String, nullable=False)
    payload = Column(JSON, nullable=False)
    status = Column(String, nullable=False)  # success, failed, pending
    response_code = Column(Integer)
    response_body = Column(Text)
    attempts = Column(Integer, default=0)
    next_retry = Column(DateTime)
    created_at = Column(DateTime, default=func.now())

2. Webhook service (backend/app/services/webhooks.py):

import httpx
import hmac
import hashlib
from datetime import datetime, timedelta

class WebhookService:
    def __init__(self, db):
        self.db = db
    
    async def trigger_webhooks(self, event: str, payload: dict):
        """Trigger all webhooks subscribed to an event."""
        webhooks = self.db.query(Webhook).filter(
            Webhook.is_active == True,
            Webhook.events.contains([event])
        ).all()
        
        for webhook in webhooks:
            await self._deliver(webhook, event, payload)
    
    async def _deliver(self, webhook: Webhook, event: str, payload: dict):
        """Deliver webhook payload."""
        # Build payload
        delivery_payload = {
            "event": event,
            "timestamp": datetime.utcnow().isoformat(),
            "data": payload
        }
        
        # Apply custom template if exists
        if webhook.payload_template:
            delivery_payload = self._apply_template(webhook.payload_template, delivery_payload)
        
        # Create delivery record
        delivery = WebhookDelivery(
            webhook_id=webhook.id,
            event=event,
            payload=delivery_payload,
            status="pending"
        )
        self.db.add(delivery)
        
        try:
            # Sign payload if secret exists
            headers = {"Content-Type": "application/json"}
            if webhook.secret:
                signature = self._sign_payload(webhook.secret, delivery_payload)
                headers["X-Webhook-Signature"] = signature
            
            # Send request
            async with httpx.AsyncClient(timeout=10) as client:
                response = await client.post(
                    webhook.url,
                    json=delivery_payload,
                    headers=headers
                )
            
            delivery.status = "success" if response.status_code < 400 else "failed"
            delivery.response_code = response.status_code
            delivery.response_body = response.text[:1000]
            
        except Exception as e:
            delivery.status = "failed"
            delivery.response_body = str(e)[:1000]
            delivery.next_retry = datetime.utcnow() + timedelta(minutes=5)
        
        webhook.last_triggered = datetime.utcnow()
        await self.db.commit()
    
    def _sign_payload(self, secret: str, payload: dict) -> str:
        """Sign payload with HMAC-SHA256."""
        payload_bytes = json.dumps(payload, sort_keys=True).encode()
        return hmac.new(secret.encode(), payload_bytes, hashlib.sha256).hexdigest()
    
    async def retry_failed_deliveries(self):
        """Retry failed deliveries with exponential backoff."""
        deliveries = self.db.query(WebhookDelivery).filter(
            WebhookDelivery.status == "failed",
            WebhookDelivery.next_retry <= datetime.utcnow(),
            WebhookDelivery.attempts < 3
        ).all()
        
        for delivery in deliveries:
            webhook = self.db.query(Webhook).get(delivery.webhook_id)
            if webhook:
                delivery.attempts += 1
                delivery.next_retry = datetime.utcnow() + timedelta(minutes=5 * delivery.attempts)
                await self._deliver(webhook, delivery.event, delivery.payload)

3. API routes (backend/app/routes/webhooks.py):

• GET /api/webhooks - List webhooks
• POST /api/webhooks - Create webhook
• PATCH /api/webhooks/{id} - Update webhook
• DELETE /api/webhooks/{id} - Delete webhook
• POST /api/webhooks/{id}/test - Send test payload
• GET /api/webhooks/{id}/deliveries - Delivery history

4. Event system - Trigger webhooks on events:

• query.result - After query execution
• anomaly.detected - When anomaly detected
• schedule.completed - After scheduled query runs

Frontend Changes

1. Webhook manager (frontend/src/routes/settings/webhooks/+page.svelte):

<script>
    let webhooks = [];
    let showCreateModal = false;
    
    async function testWebhook(id) {
        await apiCall(`/api/webhooks/${id}/test`, { method: 'POST' });
        alert('Test payload sent!');
    }
</script>

<div class="webhook-manager">
    <h1>Webhooks</h1>
    
    <button on:click={() => showCreateModal = true}>Add Webhook</button>
    
    <table>
        <thead>
            <tr>
                <th>Name</th>
                <th>URL</th>
                <th>Events</th>
                <th>Status</th>
                <th>Actions</th>
            </tr>
        </thead>
        <tbody>
            {#each webhooks as webhook}
                <tr>
                    <td>{webhook.name}</td>
                    <td><code>{webhook.url}</code></td>
                    <td>{webhook.events.join(', ')}</td>
                    <td>{webhook.is_active ? 'Active' : 'Inactive'}</td>
                    <td>
                        <button on:click={() => testWebhook(webhook.id)}>Test</button>
                        <button on:click={() => editWebhook(webhook)}>Edit</button>
                        <button on:click={() => deleteWebhook(webhook.id)}>Delete</button>
                    </td>
                </tr>
            {/each}
        </tbody>
    </table>
</div>

{#if showCreateModal}
    <div class="modal">
        <h2>Create Webhook</h2>
        <form on:submit|preventDefault={createWebhook}>
            <input bind:value={newWebhook.name} placeholder="Webhook Name" />
            <input bind:value={newWebhook.url} placeholder="https://example.com/webhook" />
            
            <label>Events</label>
            <div class="checkboxes">
                <label><input type="checkbox" value="query.result" bind:group={newWebhook.events} /> Query Result</label>
                <label><input type="checkbox" value="anomaly.detected" bind:group={newWebhook.events} /> Anomaly Detected</label>
                <label><input type="checkbox" value="schedule.completed" bind:group={newWebhook.events} /> Schedule Completed</label>
            </div>
            
            <label>Secret (for HMAC signing)</label>
            <input bind:value={newWebhook.secret} type="password" />
            
            <button type="submit">Create</button>
        </form>
    </div>
{/if}

2. Delivery log - Show delivery history with status

3. Payload builder - Visual payload template editor

Key Files

• backend/app/pgdatabase/models.py - Webhook models
• backend/app/services/webhooks.py - Webhook service (new)
• backend/app/routes/webhooks.py - API routes (new)
• frontend/src/routes/settings/webhooks/+page.svelte - Webhook manager

Related Issues

• Part of Feature Roadmap (Feature Roadmap: Next Iteration of BoloDB #71)
• Related to Anomaly Alerts (Anomaly Alerts #231) for event triggers
• Related to Scheduled Queries (Scheduled Queries + Email Delivery #225) for schedule events