Contact age tracked per record

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.

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

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:

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