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.

• ID: data-quality-list-hygiene.data-decay.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:

  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.