# Last-engagement timestamp maintained

- **Pattern:** `ab-000869` (`data-quality-list-hygiene.data-decay.last-engagement-maintained`)
- **Severity:** high
- **Lifecycle:** active
- **Last modified:** 2026-04-18
- **Canonical URL:** https://auditbuffet.com/patterns/ab-000869
- **License:** CC-BY-4.0 — attribute to AuditBuffet Pattern Catalog (https://auditbuffet.com/patterns/ab-000869)

## Why it matters

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.

## Severity rationale

High because a non-updating engagement timestamp produces incorrect staleness classifications across the entire list, making re-verification and engagement-scoring logic unreliable for all contacts.

## Remediation

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:

```ts
// 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.

## Detection

- **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:

  ```ts
  // 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.

---

## External references

- iso-25010 functional-correctness

Taxons: data-integrity

HTML version: https://auditbuffet.com/patterns/ab-000869