Every analytics product eventually hits the same wall: the queries that were instant with a week of data get slow with a year of it. A "top pages, last 90 days" query over raw pageview rows means scanning millions of rows to produce a 25-row table. You can throw indexes at it for a while, but the real fix is the boring, classic one: precompute.

This post is about how Tracely does that — the rollup tables, the daily aggregator, and (the part I care most about) the commands that can regenerate every aggregate from raw data at any time.

Two layers of truth

Tracely stores analytics in two layers.

Raw fact tables. Every pageview, custom event, and performance metric lands as its own row, written by queued ingest jobs so traffic spikes don't block the dashboards. These rows are the source of truth: paths, referrers, UTM fields, hashed visitor and session identifiers, timings.

Rollup tables. On top of the raw rows sit precomputed aggregates at two grains:

Hourly rollups, one row per site per hour per dimension value. The main ones today are pageview_path_hourly_stats (per-path traffic), pageview_dimension_hourly_stats (device/browser-style breakdowns), event_name_hourly_stats (per-event counts), performance_metric_path_hourly_stats (Web Vitals per path), and a11y_summary_hourly_stats.
Daily site stats, one row per site per day in daily_site_stats: pageviews, distinct visitors, distinct sessions, bounces, plus pageview counts split by coarse traffic channel (pv_direct, pv_search, pv_social, pv_links, pv_internal).

Hot dashboard reads that fit these shapes hit the rollups instead of scanning facts. A 90-day path breakdown becomes a sum over at most 2,160 hourly rows per path instead of a scan over every pageview.

The daily aggregator

The daily grain is maintained by a small service, DailySiteStatsAggregator, driven by an Artisan command:

php artisan tracely:aggregate-daily-site-stats
    {--date= : YYYY-MM-DD in app timezone (default: yesterday)}
    {--from= : Start date for backfill range (YYYY-MM-DD)}
    {--to=   : End date for backfill range (YYYY-MM-DD, default: yesterday)}

For each site and day it runs one grouped query over the raw pageviews — count, distinct visitors, distinct sessions, bounce count, and the five channel splits — and upserts the result keyed on (site_id, stat_date). Sites are processed in chunks of 100 so the command behaves the same with ten sites or ten thousand.

The design choice that matters is the upsert. Because the command writes with update-or-create semantics, running it twice for the same day is harmless, and running it with --from=2026-01-01 quietly recomputes four months of history. There is no "already aggregated" flag to get out of sync, no incremental cursor to corrupt. The raw rows are the state; the rollup is a pure function of them.

Rebuilding the hourly rollups

The hourly rollups get the same treatment. Each family has a rebuild service, fronted by commands:

php artisan tracely:rebuild-pageview-path-hourly-stats
    {site? : Site ULID (omit with --all)}
    {--days=90 : Days of raw pageviews to include}
    {--all : Rebuild rollups for every site}

php artisan tracely:rebuild-performance-metric-path-hourly-stats
    {site? : Site ULID (omit with --all)}
    {--days=90 : Days of raw performance_metrics to include}
    {--all : Rebuild rollups for every site}

Both exist, per their own help text, to fix "missing rollups after imports/seeds" — but they're equally the recovery path for anything else that leaves rollups wrong.

Then there's the umbrella command that rebuilds everything for one site or the whole deployment:

php artisan tracely:rebuild-all-site-rollups
    {site? : Site ULID (omit with --all)}
    {--days=90 : Days of raw analytics rows to include}
    {--all : Rebuild rollups for every site}
    {--resume : Continue a previous --all run using the saved checkpoint}
    {--fresh : Discard checkpoint and rebuild every site from the start (use with --all)}

A full --all rebuild can take a while on a big deployment, and long-running commands get interrupted — deploys happen, servers restart, someone hits Ctrl-C. So the command checkpoints. It writes a small JSON file (storage/framework/tracely-rebuild-all-site-rollups.json) recording the lookback window and every site it has finished, updating it after each site. If the run dies, the next invocation refuses to silently start over; it tells you a checkpoint exists and offers the two honest options:

php artisan tracely:rebuild-all-site-rollups --all --resume   # continue
php artisan tracely:rebuild-all-site-rollups --all --fresh    # discard checkpoint and start over

--resume also validates that you passed the same --days the checkpoint was built with, because resuming a 90-day rebuild with a 30-day window would leave you with a Frankenstein dataset that looks fine and isn't. Little guardrails like that are cheap to build and expensive to wish you had.

What a run looks like

The umbrella command narrates as it goes, one site at a time:

Tracely rollup rebuild — 90 day lookback. 12 site(s) in this run, 0 already done, 12 total in database.

[1/12] example.com
  → pageview_path_hourly_stats
     3421 rollup row(s) — 4.2s
  → pageview_dimension_hourly_stats
     ...

Each phase reports how many rollup rows it wrote and how long it took, which turns out to be surprisingly useful operational feedback — a site whose path rollup takes ten times longer than its neighbors is a site worth looking at (bot traffic, a path-cardinality explosion, an ingest misconfiguration). The checkpoint is updated after each site completes, so the blast radius of an interruption is at most one site's worth of repeated work.

Failure handling follows the same philosophy. If a site's rebuild throws, the command stops, tells you the error, and reminds you that the checkpoint was saved:

Stopped with error: <message>
Checkpoint saved. Re-run with --all --resume to continue after fixing the issue.

No partial-progress ambiguity, no "did it finish?" archaeology in the logs. Either a site is in the checkpoint's completed list or its rollups get rebuilt next run — and since rebuilds replace rather than increment, rebuilding a site twice produces the same rows as rebuilding it once.

That idempotency is doing quiet work everywhere in this design. The daily aggregator upserts on (site_id, stat_date); the hourly rebuilds regenerate a site's window wholesale. There is deliberately no code path that adds to an aggregate outside of normal ingest, because increment-style repair is where double-counting bugs live.

Why rebuildability is the actual feature

It would be easy to treat rollups as write-once: increment counters at ingest time and hope. Plenty of systems do, and it works until it doesn't. I've committed to a stricter rule: every aggregate in Tracely must be reproducible from raw data with one command. Three reasons.

Schema evolution. Rollup tables grow columns. When daily_site_stats gained the coarse channel splits, existing rows didn't have them — but the fix wasn't a migration backfill script written under pressure; it was tracely:aggregate-daily-site-stats --from=<launch date>. The aggregation code is the backfill script, and it's the same code that runs every night, so it's tested by existence.

Bug recovery. Aggregation code has bugs like any other code. If a counting bug ships and skews a week of hourly rollups, the raw rows are still correct — the bug only poisoned a cache. Fix the code, rerun the rebuild for the affected window, done. Without rebuildability, a bug in aggregation is data loss; with it, it's an inconvenience.

Imports and seeds. Sites arrive with history — an import from another deployment, a data transfer bundle, a local seed. Raw rows show up without ever passing through live ingest, so no rollups exist for them. One rebuild command later, the dashboards don't know the difference.

The general principle: raw events are truth, aggregates are cache, and cache you can't regenerate is just truth you've forked. Analytics systems accumulate subtle disagreements between their layers over years; making regeneration a first-class, resumable, idempotent operation is how I plan to avoid that fate.

Where this goes next

The current rollup grains cover paths, dimensions, events, and performance. As more dashboards ship, more read paths will earn their own rollups — the rule is that a rollup is added when a real query needs it, and it must come with its rebuild path in the same commit. If the schedule of nightly aggregation and on-demand rebuilds ever gets more interesting than "run the command," I'll write about it here — the feed is here if you want to follow along.

Rollups, or how the dashboards stay fast