Aggregate metrics reconcile with individual campaign data

Incrementally-updated aggregate counters — open_count bumped by a database trigger on each incoming webhook — diverge from the raw event log whenever a webhook is delivered twice, a contact is suppressed after the fact, or a timezone offset causes an event to land in the wrong reporting bucket. Because the aggregate and raw events are now out of sync, every metric derived from the aggregate (open rate, CTR, RPE) is wrong by an unknown amount. The iso-25010:2011 functional-suitability taxon names this precisely: the reporting function exists but produces systematically incorrect values with no mechanism to detect or correct the drift.

Run a nightly reconciliation job that compares each campaign's cached aggregate against the authoritative count from the raw event table and alerts on any discrepancy:

async function reconcileAggregates() {
  const campaigns = await db.campaigns.findMany({ where: { status: 'sent' } })
  const discrepancies: Array<{ campaign_id: string; field: string; aggregate: number; actual: number }> = []

  for (const campaign of campaigns) {
    const actualOpens = await db.emailEvents.count({
      where: { campaign_id: campaign.id, event_type: 'open', machine_open: false }
    })
    if (campaign.cached_unique_opens !== actualOpens) {
      discrepancies.push({
        campaign_id: campaign.id,
        field: 'unique_opens',
        aggregate: campaign.cached_unique_opens,
        actual: actualOpens
      })
    }
  }

  if (discrepancies.length > 0) {
    console.error('Aggregate reconciliation failures:', discrepancies)
    // Send to your alerting/monitoring system
  }
  return discrepancies
}

Schedule this via a Vercel cron or database scheduled job on a nightly cadence.