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 })
}

• ID: campaign-analytics-attribution.reporting-pipeline.defined-refresh-schedule

• Severity: medium

• What to look for: Look for scheduled jobs, cron configurations, or database refresh jobs that update reporting aggregates. Check Vercel cron configuration (vercel.json crons array), database scheduled jobs, or queue-based refresh triggers. Assess whether the refresh cadence matches the reporting use case — for campaign performance, hourly or daily refreshes are typical. Also check whether the schedule is documented: if reports are "as of" a certain time, is that communicated to users?

• Pass criteria: A defined refresh schedule exists for reporting aggregates with a cadence of no more than 24 hours for campaign reporting. The schedule is in code (not a manual process). Reports display an "as of" timestamp so users understand data freshness. Count every pre-aggregated reporting table or materialized view — report the count even on pass.

• Fail criteria: No scheduled refresh found — reports appear to query live tables on every request but the live tables are not updated in real time. Or reports are refreshed manually with no automation. No "as of" timestamp shown on reports.

• Skip (N/A) when: Reports query live tables with real-time data that does not require pre-aggregation.

• Detail on fail: Example: "No cron or scheduled job found for report refresh — reports may show stale data indefinitely" or "Reports query pre-aggregated tables but no refresh schedule exists"

• Remediation: Add a defined refresh schedule:

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

  // app/api/cron/refresh-campaign-reports/route.ts
  export async function GET(req: Request) {
    const authHeader = req.headers.get('authorization')
    if (authHeader !== `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 })
  }