emailTracking.js

Go to the documentation of this file.

/**
 * @file Email Tracking Routes
 * @module routes/emailTracking
 * @description
 * Email open tracking via transparent 1x1 pixel GIF. Records email opens with IP address,
 * user agent, and timestamp for delivery verification and engagement metrics.
 * 
 * **Features:**
 * - Transparent 1x1 pixel GIF tracking
 * - Records first and subsequent opens
 * - Captures IP address and user agent
 * - Identifies potential fake addresses (no opens)
 * - Provides metrics dashboard data
 * 
 * **Endpoints:**
 * - GET /api/email/track/:trackingId/pixel.gif - Tracking pixel (returns 1x1 GIF)
 * - GET /api/email/tracking/stats - Email tracking statistics
 * - GET /api/email/tracking/:trackingId - Get specific tracking record
 * 
 * **Privacy Notes:**
 * - Only tracks email opens (not link clicks unless separately implemented)
 * - IP addresses stored for bounce detection, not user profiling
 * - Compliant with CAN-SPAM (transactional emails)
 * 
 * @requires express
 * @requires ../services/db
 */
 
const express = require('express');
const router = express.Router();
const pool = require('../services/db');
const authenticateToken = require('../middleware/auth');
 
// 1x1 transparent GIF (base64 encoded)
const TRACKING_PIXEL_GIF = Buffer.from(
  'R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7',
  'base64'
);
 
/**
 * GET /api/email/track/:trackingId/pixel.gif
 * @description Tracking pixel endpoint. Returns 1x1 transparent GIF and records email open.
 * No authentication required (embedded in emails sent to customers).
 * 
 * **Tracking Logic:**
 * 1. First open: Sets opened=true, opened_at=now, first_user_agent, first_ip
 * 2. Subsequent opens: Increments open_count, updates last_opened_at, last_user_agent, last_ip
 * 3. Multiple opens may indicate forwarding or genuine re-reads
 * 
 * **Response:**
 * - Always returns 200 with 1x1 GIF (even if tracking fails)
 * - Cache-Control: no-store (prevent browser caching to track every open)
 */
router.get('/track/:trackingId/pixel.gif', async (req, res) => {
  const { trackingId } = req.params;
  
  // Always return pixel regardless of tracking success (don't break emails)
  res.set({
    'Content-Type': 'image/gif',
    'Cache-Control': 'no-store, no-cache, must-revalidate, proxy-revalidate',
    'Pragma': 'no-cache',
    'Expires': '0'
  });
  
  try {
    // Get client IP (handle proxies)
    const ipAddress = req.headers['x-forwarded-for']?.split(',')[0]?.trim() 
      || req.headers['x-real-ip'] 
      || req.connection.remoteAddress 
      || req.socket.remoteAddress;
    
    const userAgent = req.headers['user-agent'] || 'Unknown';
    
    // Check if this is the first open
    const existing = await pool.query(
      'SELECT opened FROM email_tracking WHERE tracking_id = $1',
      [trackingId]
    );
    
    if (existing.rows.length === 0) {
      // Tracking ID not found (deleted or invalid), still return pixel
      console.warn(`[EmailTracking] Invalid tracking ID: ${trackingId}`);
      return res.send(TRACKING_PIXEL_GIF);
    }
    
    const isFirstOpen = !existing.rows[0].opened;
    
    if (isFirstOpen) {
      // First open: set all initial fields
      await pool.query(
        `UPDATE email_tracking 
         SET opened = true,
             opened_at = NOW(),
             open_count = 1,
             last_opened_at = NOW(),
             first_user_agent = $1,
             last_user_agent = $1,
             first_ip_address = $2,
             last_ip_address = $2,
             delivery_status = 'delivered',
             updated_at = NOW()
         WHERE tracking_id = $3`,
        [userAgent, ipAddress, trackingId]
      );
      console.log(`[EmailTracking] First open: ${trackingId} from ${ipAddress}`);
    } else {
      // Subsequent open: increment counter and update last_* fields
      await pool.query(
        `UPDATE email_tracking 
         SET open_count = open_count + 1,
             last_opened_at = NOW(),
             last_user_agent = $1,
             last_ip_address = $2,
             updated_at = NOW()
         WHERE tracking_id = $3`,
        [userAgent, ipAddress, trackingId]
      );
      console.log(`[EmailTracking] Repeat open: ${trackingId} from ${ipAddress}`);
    }
  } catch (error) {
    // Log error but still return pixel (don't break email display)
    console.error('[EmailTracking] Tracking error:', error);
  }
  
  res.send(TRACKING_PIXEL_GIF);
});
 
 
/**
 * GET /api/email/tracking/stats
 * @description Get email tracking statistics for the authenticated tenant
 * @apiPermission Authenticated users
 * 
 * **Metrics Returned:**
 * - total_sent: Total emails sent
 * - total_opened: Emails opened at least once
 * - open_rate: Percentage opened (opened/sent)
 * - avg_open_count: Average opens per email (includes forwarding)
 * - never_opened: Emails never opened (potential fake addresses)
 * - recent_24h: Emails sent in last 24 hours
 * - opened_24h: Emails opened in last 24 hours
 * 
 * **Query Parameters:**
 * - email_type: Filter by email type (invoice, ticket, notification, etc.)
 * - days: Limit to last N days (default: 30)
 */
router.get('/tracking/stats', authenticateToken, async (req, res) => {
  try {
    const tenantId = req.user.tenantId;
    const { email_type, days = 30 } = req.query;
    
    let query = `
      SELECT 
        COUNT(*) as total_sent,
        COUNT(*) FILTER (WHERE opened = true) as total_opened,
        ROUND(COUNT(*) FILTER (WHERE opened = true) * 100.0 / NULLIF(COUNT(*), 0), 2) as open_rate,
        ROUND(AVG(open_count) FILTER (WHERE opened = true), 2) as avg_open_count,
        COUNT(*) FILTER (WHERE opened = false AND sent_at < NOW() - INTERVAL '24 hours') as never_opened,
        COUNT(*) FILTER (WHERE sent_at > NOW() - INTERVAL '24 hours') as recent_24h,
        COUNT(*) FILTER (WHERE opened = true AND opened_at > NOW() - INTERVAL '24 hours') as opened_24h,
        COUNT(*) FILTER (WHERE delivery_status = 'failed') as failed,
        COUNT(*) FILTER (WHERE delivery_status = 'bounced') as bounced
      FROM email_tracking
      WHERE tenant_id = $1
        AND sent_at > NOW() - INTERVAL '${parseInt(days)} days'
    `;
    
    const params = [tenantId];
    
    if (email_type) {
      query += ' AND email_type = $2';
      params.push(email_type);
    }
    
    const result = await pool.query(query, params);
    res.json(result.rows[0]);
  } catch (error) {
    console.error('[EmailTracking] Stats error:', error);
    res.status(500).json({ error: 'Failed to fetch tracking stats' });
  }
});
 
 
/**
 * GET /api/email/tracking/:trackingId
 * @description Get detailed tracking information for a specific email
 * @apiPermission Authenticated users (must own the tracking record via tenant_id)
 * 
 * **Returns:**
 * - tracking_id: UUID
 * - recipient_email: Email address
 * - subject: Email subject
 * - sent_at: When email was sent
 * - opened: Boolean (has been opened)
 * - opened_at: First open timestamp
 * - open_count: Number of times opened
 * - last_opened_at: Most recent open
 * - first_user_agent: User agent of first open
 * - first_ip_address: IP of first open
 * - delivery_status: sent, delivered, bounced, failed
 * - email_type: Type of email
 * - reference_type/reference_id: Related record
 */
router.get('/tracking/:trackingId', authenticateToken, async (req, res) => {
  try {
    const { trackingId } = req.params;
    const tenantId = req.user.tenantId;
    
    const result = await pool.query(
      `SELECT 
        tracking_id,
        recipient_email,
        recipient_name,
        subject,
        sent_at,
        opened,
        opened_at,
        open_count,
        last_opened_at,
        first_user_agent,
        last_user_agent,
        first_ip_address,
        last_ip_address,
        email_type,
        reference_type,
        reference_id,
        provider,
        provider_message_id,
        delivery_status,
        delivery_error
      FROM email_tracking
      WHERE tracking_id = $1 AND tenant_id = $2`,
      [trackingId, tenantId]
    );
    
    if (result.rows.length === 0) {
      return res.status(404).json({ error: 'Tracking record not found' });
    }
    
    res.json(result.rows[0]);
  } catch (error) {
    console.error('[EmailTracking] Get tracking error:', error);
    res.status(500).json({ error: 'Failed to fetch tracking record' });
  }
});
 
 
/**
 * GET /api/email/tracking
 * @description List email tracking records for the authenticated tenant with pagination
 * @apiPermission Authenticated users
 * 
 * **Query Parameters:**
 * - page: Page number (default: 1)
 * - limit: Results per page (default: 50, max: 200)
 * - email_type: Filter by type
 * - opened: Filter by opened status (true/false)
 * - reference_type: Filter by reference type
 * - reference_id: Filter by reference ID
 * - search: Search by recipient email or subject
 */
router.get('/tracking', authenticateToken, async (req, res) => {
  try {
    const tenantId = req.user.tenantId;
    const {
      page = 1,
      limit = 50,
      email_type,
      opened,
      reference_type,
      reference_id,
      search
    } = req.query;
    
    const offset = (parseInt(page) - 1) * parseInt(limit);
    const limitNum = Math.min(parseInt(limit), 200);
    
    let whereConditions = ['tenant_id = $1'];
    let params = [tenantId];
    let paramIndex = 2;
    
    if (email_type) {
      whereConditions.push(`email_type = $${paramIndex}`);
      params.push(email_type);
      paramIndex++;
    }
    
    if (opened !== undefined) {
      whereConditions.push(`opened = $${paramIndex}`);
      params.push(opened === 'true');
      paramIndex++;
    }
    
    if (reference_type) {
      whereConditions.push(`reference_type = $${paramIndex}`);
      params.push(reference_type);
      paramIndex++;
    }
    
    if (reference_id) {
      whereConditions.push(`reference_id = $${paramIndex}`);
      params.push(parseInt(reference_id));
      paramIndex++;
    }
    
    if (search) {
      whereConditions.push(`(recipient_email ILIKE $${paramIndex} OR subject ILIKE $${paramIndex})`);
      params.push(`%${search}%`);
      paramIndex++;
    }
    
    const whereClause = whereConditions.join(' AND ');
    
    // Get total count
    const countResult = await pool.query(
      `SELECT COUNT(*) as total FROM email_tracking WHERE ${whereClause}`,
      params
    );
    const total = parseInt(countResult.rows[0].total);
    
    // Get paginated results
    const result = await pool.query(
      `SELECT 
        tracking_id,
        recipient_email,
        subject,
        sent_at,
        opened,
        opened_at,
        open_count,
        email_type,
        reference_type,
        reference_id,
        delivery_status
      FROM email_tracking
      WHERE ${whereClause}
      ORDER BY sent_at DESC
      LIMIT $${paramIndex} OFFSET $${paramIndex + 1}`,
      [...params, limitNum, offset]
    );
    
    res.json({
      data: result.rows,
      pagination: {
        page: parseInt(page),
        limit: limitNum,
        total,
        totalPages: Math.ceil(total / limitNum)
      }
    });
  } catch (error) {
    console.error('[EmailTracking] List error:', error);
    res.status(500).json({ error: 'Failed to fetch tracking records' });
  }
});
 
 
module.exports = router;