Time-Series Rollups

Owlmetry maintains pre-computed daily and hourly rollup tables for the four event-shaped data sources (events, metric_events, funnel_events, questionnaire_responses). These power the subtle sparkline charts on dashboard cards today and any time-range trend query going forward.

Why pre-aggregate?

Raw event tables grow to many millions of rows. Re-scanning them every time the dashboard renders a sparkline would be wasteful and slow. The rollup tables store one row per (project, app, is_dev, bucket, kind-specific dimensions), so a 30-day sparkline is at most ~30 rows — a single index seek per project.

The same tables also support arbitrary date-range analytics. "Events per day for the past year across this one app" hits a daily rollup with WHERE day BETWEEN ... AND ... AND app_id = .... No raw event scan, no time-range limits.

The eight tables

Every table also carries team_id, project_id, app_id (nullable), and is_dev.

Project rollup vs per-app rows

For each bucket, the aggregator writes two rows:

• A per-app row with app_id set — used when a query is filtered to one app.
• A project-rollup row with app_id IS NULL — used when a query spans every app in a project.

The rollup row's unique_users and unique_sessions are project-level distincts: a user active on two apps within the same project counts once in the rollup but twice across the per-app rows. Reads always hit a single row, never a SUM at query time.

Day boundaries are UTC

day is a UTC calendar date; hour is the UTC start of the hour. No user-timezone setting is applied. This keeps aggregations cheap and consistent across deployments — there's nothing to recompute when the user changes locale.

Current bucket exclusion

The read API defaults to excluding_current=true, dropping the in-progress UTC day or hour. A partial bucket has fewer events than a complete one and would render as a misleading dip on the sparkline. Pass excluding_current=false if you specifically want the in-flight bucket included.

Non-additive distincts

unique_users and unique_sessions are per-bucket COUNT(DISTINCT ...) values. They cannot be summed across buckets: adding seven daily distinct-user counts does not give true weekly distinct users (a single user active every day counts seven times, not one).

The dashboard cards plot the additive event_count column for the trend line. Multi-bucket distinct queries that need exact answers should fall back to scanning raw events.

Aggregation schedule

Two background jobs maintain the tables:

• stats_aggregate_hourly runs every hour at :05 UTC. Default behavior: re-aggregates the trailing 3 hours.
• stats_aggregate_daily runs every day at 00:30 UTC. Default behavior: re-aggregates the trailing 3 days.

The 3-bucket re-aggregation window absorbs late-arriving events from offline mobile SDKs. Events arriving later than that need a manual backfill (see below).

Backfill

Backfilling the rollup tables is an operator action with no public API, CLI, or MCP surface — by design. The trigger-job route rejects stats_aggregate_* system jobs to keep a team-scoped agent key from kicking off system-wide work.

To run a backfill, SSH into the production VPS and invoke the npm script directly against the database:

pnpm backfill

That re-aggregates the trailing 365 days (both daily and hourly grains) in one pass. Idempotent: each per-kind aggregator runs DELETE + two INSERT … SELECT … GROUP BY passes (per-app + project-rollup) inside a single transaction per bucket range, so re-running over the same window replaces the rollup rows in place. Stale rows from deleted source events don't survive a re-run.

Retention

The rollup tables are not pruned. They're explicitly excluded from retention_cleanup and soft_delete_cleanup. The counts are anonymous (no user IDs, no session IDs — just COUNT and COUNT DISTINCT integer outputs), so there's no PII to age out, and long-lived sparklines / year-views need this history to survive raw-event retention.

Raw event partitions still age out per the project's retention_days_events, retention_days_metrics, and retention_days_funnels settings — that just doesn't cascade-delete from the rollups.

Reading aggregates

Two endpoint variants, one route handler:

• Project-scoped: GET /v1/projects/:projectId/stats/:kind/:grain
• Team-scoped: GET /v1/stats/:kind/:grain?team_id=...

Valid :kinds: events, users, sessions, metric_completions, funnel_completions, questionnaire_responses. Valid :grains: daily, hourly.

Response shape:

{
  "kind": "events",
  "grain": "daily",
  "from": "2026-04-20",
  "to": "2026-05-19",
  "data": [
    { "bucket": "2026-04-20", "value": 1240 },
    { "bucket": "2026-04-21", "value": 1310 },
    ...
  ]
}

Missing buckets are zero-padded so consumers can render a continuous line without gap-filling logic.