Query and aggregation engine

Context

The engine that turns a ReportBlock.query config (#9) into numbers: time-bucketed aggregations over the events stream (#6), safely and fast. Built on bun-query-builder - no string-built SQL anywhere; when the builder cannot express something (time bucketing, filtered aggregates, percentiles), extend bun-query-builder upstream (~/Code/Libraries/bun-query-builder / ~/Code/Tools/bun-query-builder) and bun run release:patch.

Scope

• app/Reports/engine.ts (or a packages/ module): input = validated block query config + project + time range; output = a typed result set { series: [{ key, points: [{ t, value }] }], total, comparison }.
• Measures: count, count_unique(user_key), sum(value), avg(value), min, max; funnel step counts for the funnel block.
• Time grains: hour, day, week, month, with timezone-correct bucketing and gap-filling (empty buckets emit zero points; charts must not connect across gaps silently).
• Dimensions: group by a bounded-cardinality property with a top-N + "other" rollup.
• Filters: property equals/in/contains, event-name sets, user_key presence; all parameterized through the builder.
• Comparison ranges: same-length previous period, computed server-side.
• Rollups: pre-aggregated daily rollup table (from a model, per #5) maintained by a queued job (app/Jobs/), used automatically for coarse grains over long ranges; raw events used for fine grains. Correctness test: rollup answer == raw answer on seeded data.
• Caching: @stacksjs/cache cache-aside on (project, config-hash, range) with short TTL; explicit bust on new-event thresholds is fine to defer.
• HTTP surface: POST /api/projects/{id}/query (bearer auth, validated config) used by viewer, builder preview, shares, and scheduled renders.
• Bench note in the repo: seeded 1M-event project, p95 for a 30-day daily line query - with the number recorded, not hand-waved.

Acceptance

• Engine unit tests per measure/grain/filter; rollup-vs-raw equivalence test; the seeded demo reports (#9) return correct series verified against hand-computed fixtures.

Depends on #6, #9. Blocks #12, #13, #14, #15. Part of #1 (M3 Reporting core). Ground rules: see #1.

Engine is in and tested (99917d9); rollups are not, and I have deliberately left this open for them. 171 tests.

runQuery returns the same shape whatever a block asked for, so a chart component never branches on which measure produced it. Every measure, both grains of dimension handling, filters, comparisons and funnels go through it, and it is the one endpoint the viewer, the builder preview, a share page and a scheduled render all use, so they cannot disagree about what a block means.

Two layers keep client strings away from SQL. The field allowlist (#9) refuses anything that is not a known event column or a well-formed properties. key, and a property column then compiles to json_extract(properties, '$.' || ?) with the key bound. So properties.plan') or 1=1 -- is refused outright, and a key that passes the allowlist can still only ever be a key that does not exist. Both are tested.

Timezone reasoning was the fiddly part. Ranges resolve in the project's zone, never the reader's or the server's, because two people comparing screens have to agree on what "yesterday" means. Days are added as calendar parts rather than milliseconds (a day is 23 or 25 hours long twice a year), and the offset is read per instant rather than treated as constant, since Europe/Lisbon is +0 or +1 depending on the date. There is a test that adding one day to 2026-03-28 in Lisbon lands on the 29th.

A few decisions that will show up in the charts:

• Empty buckets are emitted as zero. A chart that skips them draws a line straight across, which reads as "steady" when it means "nothing happened".
• A dimension's tail folds into Other rather than being dropped, so the chart still adds up to the big number beside it.
• A comparison against a zero previous period reports null, not a percentage. "Up from nothing" is not one, and rendering it is how a dashboard claims a 4000% increase because yesterday happened to be zero.
• Funnel steps count sessions that reached the previous step, not events. Counting events lets one session that viewed six products report a 600% first step.

A real bug the tests caught: is_not, exists and not_exists name the column twice, and a property column carries its own placeholder, so the key has to be bound twice. Binding it once threw SQLite query expected 7 values, received 6. The column is now emitted through a closure that binds as it goes, so placeholders and parameters cannot drift apart however the fragment is written later.

Benchmarks, measured rather than estimated and written up in docs/engine-benchmarks.md with what they mean:

One million events, ten runs after warm-up, no cache. The same queries against the 1,603-event demo seed run in 11 to 23ms, so this is the shape of the data rather than fixed overhead.

The honest read: a single block is fine and a page of blocks is not. Eight blocks at roughly half a second each is a page that takes several seconds to fill. The 5-second cache-aside layer that shipped with this covers a second reader and a tab switch, but the first person to open a report still pays in full. Grouping by a JSON property is the most expensive shape, because json_extract runs per row and cannot use the index, which is exactly what a rollup fixes.

The cache key work is worth a look in review: it hashes a canonicalised query with sorted keys, because JSON.stringify is not stable across key order and two literals meaning the same thing would otherwise cache twice, and a refactor reordering one would silently miss every entry. The project id sits in the key in the clear as well as inside the hash, so even a hash collision cannot cross a tenant boundary. Every cache failure falls through to the engine, because an analytics page showing an error when Redis is unreachable has turned a performance feature into an availability problem.

Remaining on this issue: the daily rollup table, a queued job to maintain it, and the engine choosing it automatically for day, week and month grains over long ranges. Two things it needs when it lands, both noted in the benchmarks doc:

• an equivalence test asserting the rollup answer matches the raw answer for every measure, because a rollup that is subtly wrong is worse than none: it is wrong quickly and consistently, which reads as correct;
• care with count_unique, which does not compose from daily totals, since summing daily uniques double-counts anyone appearing on two days. A rollup can only narrow the range that measure runs over, not answer it.

Rollups landed in 6d8aad7, so this is now complete. 204 tests.

467ms to 55ms p95 for a 30-day daily line over a million events, and 445ms to 67ms with a comparison. Building the rollups for a million events takes 0.9 seconds and produces 36 rows, because the table is one row per project, day and event name. A report of eight blocks goes from several seconds to well under one.

The scope is deliberately narrow, and each exclusion is a case where a pre-aggregate would be subtly wrong rather than merely slower. Dimensions are not rolled up, because a row per property value is larger than the events it summarises for anything high-cardinality. Filters go raw, because a filtered question is a different question nobody pre-computed. count_unique always goes raw, because summing daily uniques double-counts anyone who appears on two days.

A single-day count_unique could be answered exactly from the stored figure, and I wrote that exception and then deleted it: one branch, reachable for exactly one range width, whose failure mode is a plausible number rather than an error. The raw path is correct for every width and a one-day query is the cheapest raw query there is.

The tests caught a bug that would have shipped silent zeros, and it is the one worth reviewing. A day with no events stores no rollup rows by design, so the engine could not distinguish "quiet week" from "never built" and answered an unbuilt project with a confident, fast, wrong nothing. Exactly the failure this design set out to avoid.

Coverage is now recorded explicitly in rollup_states and checked before the pre-aggregate is read. Three details in that check matter:

• The timezone is part of coverage. day is a project-local date, so rows built in one zone shift every boundary if read in another. A project that changes timezone falls back to raw until it is rebuilt.
• Coverage extends rather than replaces. The ten-minute job rebuilds a trailing three days; if that shrank the window, every older query would drop back to the raw path each time it ran.
• The check compares against the last day the range actually reads, not to. to is exclusive, so comparing against it would demand coverage of a day the query never touches and refuse the rollups for every range ending today.

Fifteen equivalence tests hold the two paths together: five measures across day, week and month grains, each comparing rollup output to raw output on the same data rather than to a constant, so a bug that broke both identically still fails. The fixture is deliberately uneven (one to four orders a day, values that vary with the day) so a wrong sum or average cannot coincide with the right one. There is also a test that avg ignores events carrying no value on both paths, which is what separates dividing by value_count from dividing by event_count.

Rebuilds are idempotent and cover a trailing window rather than tracking dirty days. Events arrive late: an SDK buffers, a mobile client reconnects after a day offline, and a backfill can land events 30 days back. A dirty-day ledger would have to be written on the ingest path, which is the one path that must stay cheap, and it would still miss anything that landed while the ledger was being read. A rebuild is one grouped scan over an indexed range, so recomputing three days every ten minutes costs less than the bookkeeping to avoid it.

One small note for whoever touches the scheduler: it runs every ten minutes rather than fifteen because the fluent scheduler has no everyFifteenMinutes, despite Every.FifteenMinutes existing in the cron enum. Ten is the nearest interval it offers and errs in the right direction for freshness. Worth closing that gap upstream when stacks can release again (blocked by stacksjs/stacks#2322).

docs/engine-benchmarks.md now carries both tables, the before and after, plus what still goes raw and why.