Last-engagement timestamp maintained

A last_engaged_at column that is never updated is worse than no column at all: it creates false confidence that engagement is being tracked while every contact's timestamp stays frozen at creation date. Stale-contact jobs keyed on last_engaged_at will then incorrectly classify all contacts as disengaged simultaneously, suppressing the entire list. The root cause is typically a webhook handler that logs events to an analytics table but does not write back to the contacts table — a common pattern in systems where the analytics pipeline was added after the contact model was built.

Wire ESP webhook open and click events directly to a contact last_engaged_at update — verify this write exists in the webhook handler, not just in an analytics sink:

// POST /api/webhooks/esp
export async function POST(req: Request) {
  const events = await req.json()

  for (const event of events) {
    if (['open', 'click'].includes(event.event)) {
      const email = event.email?.toLowerCase().trim()
      if (email) {
        await db.contact.updateMany({
          where: { email },
          data: {
            last_engaged_at: new Date(event.timestamp * 1000),
            engagement_tier: 'active'
          }
        })
      }
    }
  }

  return Response.json({ ok: true })
}

Validate HMAC signatures before processing events. After deploying, spot-check by triggering a test open through your ESP and confirming last_engaged_at updates on the corresponding contact row within minutes.

• ID: last-engagement-maintained

• Severity: high

• What to look for: Enumerate all ESP webhook event types handled (open, click, delivery, bounce, complaint, unsubscribe). Count how many event types update the last_engaged_at timestamp. Verify that the last_engaged_at (or equivalent) timestamp is actually updated when engagement events occur. Check whether your ESP webhook handler (for opens, clicks, deliveries) writes back to the contacts table. An engagement timestamp column is useless if nothing writes to it. Look for webhook routes that receive open/click events and the SQL/ORM update they trigger.

• Pass criteria: At least 2 engagement event types (opens and clicks) trigger a webhook that updates last_engaged_at on the corresponding contact record within an acceptable time window (minutes, not days).

• Fail criteria: last_engaged_at column exists but is never updated after initial contact creation, or engagement webhooks exist but do not write back to the contact record.

• Skip (N/A) when: Engagement tracking is managed entirely by the external ESP and your system does not store engagement timestamps locally.

• Detail on fail: Example: "last_engaged_at column exists but ESP webhook handler does not update it — column stays at contact creation date for all contacts" or "Webhook route /api/webhooks/esp exists but only logs the event, does not update contact record"

• Remediation: Wire ESP webhooks to update the contact engagement timestamp:

  // POST /api/webhooks/esp
  export async function POST(req: Request) {
    const events = await req.json() // Parse ESP event payload
  
    for (const event of events) {
      if (['open', 'click'].includes(event.event)) {
        const email = event.email?.toLowerCase().trim()
        if (email) {
          await db.contact.updateMany({
            where: { email },
            data: {
              last_engaged_at: new Date(event.timestamp * 1000),
              engagement_tier: 'active'
            }
          })
        }
      }
    }
  
    return Response.json({ ok: true })
  }
  

  Verify webhook authenticity (HMAC signature) before processing. See the Deliverability Engineering Audit for complete webhook security coverage.