Webhooks de Postmark para eventos de email

/**

• FlowSync — Postmark Webhook Handler
• Endpoint: POST /api/webhooks/postmark?token=
• Eventos procesados: Bounce, SpamComplaint, Open, Click, Delivery, SubscriptionChange
• Persistencia: Supabase (PostgreSQL)
• Idempotencia: deduplicación por MessageID en tabla email_events
• Uso:
• 1. Copia este archivo en tu proyecto Express/Next.js (pages/api o app/api)
• 1. Define POSTMARK_WEBHOOK_TOKEN y SUPABASE_URL + SUPABASE_SERVICE_ROLE en .env
• 1. En el dashboard de Postmark, configura la webhook URL:

•  https://app.flowsync.io/api/webhooks/postmark?token=<POSTMARK_WEBHOOK_TOKEN>
  

• 1. Activa los eventos: Bounce, SpamComplaint, Open, Click, Delivery, SubscriptionChange */

import express from 'express'; import { createClient } from '@supabase/supabase-js';

// ─── Inicialización ──────────────────────────────────────────────────────────

const router = express.Router();

const supabase = createClient( process.env.SUPABASE_URL, process.env.SUPABASE_SERVICE_ROLE, );

// ─── Middleware: autenticación por token en query param ──────────────────────

function authenticateWebhook(req, res, next) { const token = req.query.token;

if (!token || token !== process.env.POSTMARK_WEBHOOK_TOKEN) { console.warn('[postmark-webhook] Unauthorized attempt:', { ip: req.ip, token: token ? '[present but invalid]' : '[missing]', }); // Devolvemos 200 de todas formas para no revelar si el endpoint existe // Internamente descartamos el payload return res.sendStatus(200); }

next(); }

// ─── Middleware: validación básica de payload ────────────────────────────────

function validatePayload(req, res, next) { const { RecordType, MessageID } = req.body;

if (!RecordType || !MessageID) { console.warn('[postmark-webhook] Invalid payload — missing RecordType or MessageID'); return res.status(400).json({ error: 'Invalid payload structure' }); }

next(); }

// ─── Helpers ─────────────────────────────────────────────────────────────────

/**

• Guarda el evento raw en email_events.
• Retorna false si el MessageID+RecordType ya existe (idempotencia). */ async function logEvent(messageId, recordType, email, payload) { // Comprueba si ya procesamos este evento exacto const { data: existing } = await supabase .from('email_events') .select('id') .eq('message_id', messageId) .eq('record_type', recordType) .maybeSingle();

if (existing) { console.log([postmark-webhook] Duplicate event skipped: ${recordType} / ${messageId}); return false; // ya procesado }

const { error } = await supabase.from('email_events').insert({ message_id: messageId, record_type: recordType, email, payload, processed_at: new Date().toISOString(), });

if (error) { console.error('[postmark-webhook] Error inserting email_event:', error.message); }

return true; // nuevo evento }

// ─── Handlers por tipo de evento ─────────────────────────────────────────────

async function handleBounce(event) { const { Email, Type, TypeCode, Description, MessageID } = event;

console.log([postmark-webhook] Bounce: ${Email} — ${Type} (${TypeCode}));

// Marcar contacto como undeliverable const { error } = await supabase .from('contacts') .update({ email_status: 'undeliverable', bounce_type: Type, bounce_description: Description, last_bounce_at: new Date().toISOString(), }) .eq('email', Email.toLowerCase());

if (error) { console.error('[postmark-webhook] Error updating contact (bounce):', error.message); }

// Hard bounce (TypeCode 1 = hard bounce): también desactivar envíos futuros if (TypeCode === 1) { await supabase .from('contacts') .update({ can_email: false }) .eq('email', Email.toLowerCase());

console.log(`[postmark-webhook] Hard bounce — contact disabled: ${Email}`);

} }

async function handleSpamComplaint(event) { const { Email, BouncedAt } = event;

console.log([postmark-webhook] SpamComplaint: ${Email});

const { error } = await supabase .from('contacts') .update({ email_status: 'spam_blocked', can_email: false, spam_reported_at: BouncedAt ?? new Date().toISOString(), }) .eq('email', Email.toLowerCase());

if (error) { console.error('[postmark-webhook] Error updating contact (spam):', error.message); } }

async function handleOpen(event) { const { Email, ReceivedAt, Platform, UserAgent, MessageID } = event;

console.log([postmark-webhook] Open: ${Email} at ${ReceivedAt} (${Platform}));

// Incrementar open_count en la campaña asociada al MessageID // Primero buscamos a qué campaña pertenece el mensaje const { data: sent } = await supabase .from('email_sends') .select('campaign_id') .eq('postmark_message_id', MessageID) .maybeSingle();

if (sent?.campaign_id) { await supabase.rpc('increment_campaign_opens', { campaign_id: sent.campaign_id }); }

// Actualizar last_open en el contacto await supabase .from('contacts') .update({ last_email_open_at: ReceivedAt, email_client: Platform, }) .eq('email', Email.toLowerCase()); }

async function handleClick(event) { const { Email, ClickedAt, OriginalLink, MessageID } = event;

console.log([postmark-webhook] Click: ${Email} → ${OriginalLink});

// Registrar el clic en email_clicks const { error } = await supabase.from('email_clicks').insert({ email: Email.toLowerCase(), postmark_message_id: MessageID, url: OriginalLink, clicked_at: ClickedAt ?? new Date().toISOString(), });

if (error) { console.error('[postmark-webhook] Error inserting email_click:', error.message); }

// Actualizar last_click en el contacto await supabase .from('contacts') .update({ last_email_click_at: ClickedAt ?? new Date().toISOString() }) .eq('email', Email.toLowerCase()); }

async function handleDelivery(event) { const { Email, DeliveredAt, MessageID } = event;

console.log([postmark-webhook] Delivery confirmed: ${Email} at ${DeliveredAt});

// Actualizar registro de envío con fecha de entrega const { error } = await supabase .from('email_sends') .update({ delivered_at: DeliveredAt ?? new Date().toISOString() }) .eq('postmark_message_id', MessageID);

if (error) { console.error('[postmark-webhook] Error updating email_send (delivery):', error.message); } }

async function handleSubscriptionChange(event) { const { Email, ChangedAt, SuppressionReason } = event; const isUnsubscribe = !!SuppressionReason; // si hay razón → se dio de baja

console.log( [postmark-webhook] SubscriptionChange: ${Email} — ${isUnsubscribe ? 'UNSUBSCRIBED' : 'RESUBSCRIBED'}, );

const { error } = await supabase .from('contacts') .update({ subscribed: !isUnsubscribe, subscription_changed_at: ChangedAt ?? new Date().toISOString(), unsubscribe_reason: isUnsubscribe ? SuppressionReason : null, can_email: !isUnsubscribe, }) .eq('email', Email.toLowerCase());

if (error) { console.error('[postmark-webhook] Error updating contact (subscription):', error.message); } }

// ─── Router principal ─────────────────────────────────────────────────────────

router.post( '/api/webhooks/postmark', express.json(), authenticateWebhook, validatePayload, async (req, res) => { // Respondemos 200 inmediatamente para que Postmark no reintente res.sendStatus(200);

const event = req.body;
const { RecordType, MessageID, Email } = event;

// Idempotencia + log del evento raw
const isNew = await logEvent(MessageID, RecordType, Email, event);
if (!isNew) return; // ya procesado, salimos

// Dispatch según tipo
try {
  switch (RecordType) {
    case 'Bounce':
      await handleBounce(event);
      break;
    case 'SpamComplaint':
      await handleSpamComplaint(event);
      break;
    case 'Open':
      await handleOpen(event);
      break;
    case 'Click':
      await handleClick(event);
      break;
    case 'Delivery':
      await handleDelivery(event);
      break;
    case 'SubscriptionChange':
      await handleSubscriptionChange(event);
      break;
    default:
      console.log(`[postmark-webhook] Unhandled event type: ${RecordType}`);
  }
} catch (err) {
  // No lanzamos el error hacia Postmark (ya respondimos 200)
  // Pero lo registramos para alertas en Sentry/PostHog
  console.error(`[postmark-webhook] Error processing ${RecordType}:`, err);
}

}, );

export default router;

// ─── SQL: tablas necesarias en Supabase ────────────────────────────────────── /* -- email_events: log raw + deduplicación CREATE TABLE email_events ( id BIGSERIAL PRIMARY KEY, message_id TEXT NOT NULL, record_type TEXT NOT NULL, email TEXT, payload JSONB, processed_at TIMESTAMPTZ DEFAULT NOW(), UNIQUE (message_id, record_type) );

-- email_clicks: historial de clics CREATE TABLE email_clicks ( id BIGSERIAL PRIMARY KEY, email TEXT NOT NULL, postmark_message_id TEXT NOT NULL, url TEXT, clicked_at TIMESTAMPTZ );

-- Campos a añadir en contacts (si no existen): ALTER TABLE contacts ADD COLUMN IF NOT EXISTS email_status TEXT DEFAULT 'active'; ALTER TABLE contacts ADD COLUMN IF NOT EXISTS bounce_type TEXT; ALTER TABLE contacts ADD COLUMN IF NOT EXISTS bounce_description TEXT; ALTER TABLE contacts ADD COLUMN IF NOT EXISTS last_bounce_at TIMESTAMPTZ; ALTER TABLE contacts ADD COLUMN IF NOT EXISTS can_email BOOLEAN DEFAULT TRUE; ALTER TABLE contacts ADD COLUMN IF NOT EXISTS spam_reported_at TIMESTAMPTZ; ALTER TABLE contacts ADD COLUMN IF NOT EXISTS last_email_open_at TIMESTAMPTZ; ALTER TABLE contacts ADD COLUMN IF NOT EXISTS email_client TEXT; ALTER TABLE contacts ADD COLUMN IF NOT EXISTS last_email_click_at TIMESTAMPTZ; ALTER TABLE contacts ADD COLUMN IF NOT EXISTS subscribed BOOLEAN DEFAULT TRUE; ALTER TABLE contacts ADD COLUMN IF NOT EXISTS subscription_changed_at TIMESTAMPTZ; ALTER TABLE contacts ADD COLUMN IF NOT EXISTS unsubscribe_reason TEXT;

-- Función para incrementar opens en campañas CREATE OR REPLACE FUNCTION increment_campaign_opens(campaign_id BIGINT) RETURNS VOID AS $$ UPDATE campaigns SET open_count = open_count + 1 WHERE id = campaign_id; $$ LANGUAGE SQL; */