# Contact age tracked per record

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

## Why it matters

A contact record with no engagement timestamp is indistinguishable from one acquired yesterday versus three years ago. Without per-record age tracking, list hygiene jobs cannot identify stale contacts for re-verification, re-engagement campaigns cannot target the right cohort, and sender reputation metrics cannot explain why open rates are declining. A generic `updated_at` column conflates admin edits (which don't signal engagement) with recipient activity, making it useless for deliverability decisions. Many ESPs now enforce inactivity-based suppression at their end — without local engagement timestamps, your system cannot anticipate or mirror those suppressions.

## Severity rationale

High because contacts without engagement timestamps cannot be triaged for stale-list cleanup, causing disengaged addresses to accumulate on the active send list and silently drag down open rates and sender reputation.

## Remediation

Add a dedicated engagement timestamp column separate from the system `updated_at`:

```sql
ALTER TABLE contacts
  ADD COLUMN last_engaged_at TIMESTAMPTZ,
  ADD COLUMN engagement_score INTEGER NOT NULL DEFAULT 0;
```

Write to `last_engaged_at` only from ESP webhook events (opens, clicks), not from admin UI actions:

```ts
// In your ESP webhook handler:
if (['open', 'click'].includes(event.event_type)) {
  await db.contact.updateMany({
    where: { email: event.email.toLowerCase().trim() },
    data: { last_engaged_at: new Date(event.timestamp) }
  })
}
```

Keep `updated_at` for record-level change tracking and `last_engaged_at` for deliverability decisions — the two serve different purposes and must not be merged into one column.

## Detection

- **ID:** `contact-age-tracked`
- **Severity:** `high`
- **What to look for:** Examine the contact table schema. Count all timestamp columns on the contact record — list all by name. Verify that at minimum `created_at` and `updated_at` timestamps are present per record. Preferably, check for a `last_engaged_at` or `last_activity_at` column that is updated whenever the contact opens, clicks, or otherwise interacts — distinct from system-level `updated_at` (which changes on any field update, including admin edits). A generic `updated_at` that conflates admin edits with recipient engagement does not count as pass.
- **Pass criteria:** Contacts have at least 2 timestamp columns: a `created_at` timestamp and a separate engagement-based timestamp (`last_engaged_at` or equivalent) that updates only on recipient activity, not on admin edits. Quote the actual column names found.
- **Fail criteria:** Contacts have no timestamp, only a generic `updated_at` that conflates admin edits with recipient engagement, or contacts have `created_at` but no engagement-based timestamp.
- **Skip (N/A) when:** The system sends only transactional email to authenticated users; engagement tracking is managed externally by the ESP.
- **Cross-reference:** Check `data-quality-list-hygiene.data-decay.last-engagement-maintained` — the timestamp column is useless if nothing writes to it from ESP webhook events.
- **Detail on fail:** Example: `"Contacts table has updated_at only — no distinction between admin edits and recipient engagement"` or `"No timestamp columns on contact records"`
- **Remediation:** Add engagement-specific timestamp columns:

  ```sql
  ALTER TABLE contacts
    ADD COLUMN last_engaged_at TIMESTAMPTZ,
    ADD COLUMN engagement_score INTEGER NOT NULL DEFAULT 0;

  -- Update engagement timestamp on open/click events:
  UPDATE contacts
  SET last_engaged_at = now(),
      engagement_score = engagement_score + 1
  WHERE id = $1;
  ```

  Ensure this update is triggered by webhook events from your ESP (open, click) — not by internal admin updates to the contact record.

## External references

- iso-25010 functional-correctness

Taxons: data-integrity

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