Rollups & Data Retention

diy-analytics keeps dashboards fast across years of traffic while keeping storage compact, through automated daily rollups and pruning.

Why daily rollups matter

Raw, unaggregated telemetry — individual pageviews, device user agents, custom event payloads — gives you granular audit trails, but querying millions of raw rows across a 12-month window slows dashboard rendering.

diy-analytics solves this with a two-tier storage architecture:

Raw event tier (recent data). Pageviews and custom events are stored at full granularity for real-time inspection, journey flow analysis, and drilldowns.
Rollup tier (long-range data). A background cron job compresses high-cardinality rows into daily pre-aggregated summaries. Long-range, multi-month charts query these rollups in under 10ms.

Cron configuration

The cron routine is configured in vercel.json and runs daily at 03:00 UTC:

json

{
  "crons": [
    {
      "path": "/api/cron/rollup",
      "schedule": "0 3 * * *"
    }
  ]
}

What the job does

When /api/cron/rollup runs:

Compresses the prior day. Aggregates all raw pageviews and custom events from the previous UTC day into pre-calculated daily summary rows.
Prunes expired raw records. Deletes raw pageview rows older than PAGEVIEW_RETENTION_DAYS (default: 90 days) and custom event rows older than EVENT_RETENTION_DAYS (default: 90 days).
Preserves rollups permanently. Aggregated daily summaries are never deleted, so your year-over-year growth charts stay accurate indefinitely.

Securing the cron endpoint

Set CRON_SECRET in your environment variables to prevent unauthorized manual triggers:

bash

CRON_SECRET=your_super_secret_32_character_token

On Vercel, Vercel Cron automatically passes this token in the Authorization: Bearer $CRON_SECRET header.

Triggering rollups manually

Useful after backfilling historical data:

bash

curl -X GET "https://analytics.yourdomain.com/api/cron/rollup" \
  -H "Authorization: Bearer YOUR_CRON_SECRET"

The endpoint returns a JSON summary of processed records and execution duration:

json

{
  "success": true,
  "rolledUpDays": 1,
  "prunedPageviews": 1420,
  "prunedEvents": 320,
  "durationMs": 412
}

Running the rollup manually re-aggregates and re-prunes the prior UTC day. Avoid triggering it more than once per day outside of a backfill.

Rollups & Data Retention

Why daily rollups matter

Cron configuration

What the job does

Securing the cron endpoint

Triggering rollups manually

Next steps

Migrating from MongoDB

Troubleshooting & CSP