Report data has a defined refresh schedule

Pre-aggregated campaign reports without a defined refresh schedule show users numbers that may be hours or days out of date — but without an 'as of' timestamp, users have no way to know. A campaign manager who reads an open rate of 18% at 3pm and acts on it may be looking at data last refreshed at 6am. Decisions about pausing underperforming campaigns, triggering follow-up sequences, or adjusting send frequency are all time-sensitive. The iso-25010:2011 reliability taxon is the exact failure: the system appears operational but does not guarantee freshness, making it unreliable for time-sensitive decisions.

Define a cron-triggered refresh for every pre-aggregated reporting table and display the refresh timestamp on the report UI. For Vercel deployments, add to vercel.json:

{
  "crons": [
    { "path": "/api/cron/refresh-campaign-reports", "schedule": "0 * * * *" }
  ]
}

The handler should verify the CRON_SECRET header, refresh all materialized views, and update a report_metadata row so the UI can display 'as of HH:MM':

// app/api/cron/refresh-campaign-reports/route.ts
export async function GET(req: Request) {
  if (req.headers.get('authorization') !== `Bearer ${process.env.CRON_SECRET}`)
    return new Response('Unauthorized', { status: 401 })
  const refreshedAt = new Date()
  await db.$executeRaw`REFRESH MATERIALIZED VIEW CONCURRENTLY campaign_funnel`
  await db.reportMetadata.upsert({
    where:  { report_name: 'campaign_funnel' },
    update: { refreshed_at: refreshedAt },
    create: { report_name: 'campaign_funnel', refreshed_at: refreshedAt }
  })
  return Response.json({ ok: true, refreshed_at: refreshedAt })
}