Direction for the user-analytics precompute: per-day delta + near-real-time freshness
Two design points we'd like to adopt as this moves forward.
- Store a per-day delta, not a cumulative snapshot
The current daily aggregation is cumulative — each row holds the all-time total as of that date (the query aggregates the whole study_history with no per-day filter). Proposal: scope each row to the activity of that day only
(WHERE start_time >= current_date AND start_time < current_date + 1), so a row means "what this user did on this day."
Why this is better:
- Roll-ups become trivial. Weekly / monthly / yearly are just SUM(spent_time), SUM(done_exercises) and COUNT(*) of daily rows over the period. No need for separate weekly/monthly/yearly tables — they're derived from the daily
table (a view/query is enough; materialize later only if measurements demand it).
- study_days drops out of the table. "Study days in a month" is simply the count of daily rows in that month, so it stops being a stored column and becomes a derived count.
- spent_time and done_exercises sum cleanly across days; first_done / last_done are per-day first/last activity and are combined with MIN/MAX over a period (not SUM).
All-time values that the cumulative snapshot used to hold (lifetime first study, total time, etc.) move into a separate running per-user row (user_lifetime_analytics), upserted rather than re-scanned.
- Keep the current day fresh via event-driven incremental updates
Instead of only filling analytics nightly, update today's delta row immediately when an exercise is completed:
- Hook on study_history commit via @TransactionalEventListener(phase = AFTER_COMMIT) + @async — so it never adds latency to the exercise-completion request.
The current daily aggregation is cumulative — each row holds the all-time total as of that date (the query aggregates the whole study_history with no per-day filter). Proposal: scope each row to the activity of that day only
(WHERE start_time >= current_date AND start_time < current_date + 1), so a row means "what this user did on this day."
Why this is better:
- Roll-ups become trivial. Weekly / monthly / yearly are just SUM(spent_time), SUM(done_exercises) and COUNT(*) of daily rows over the period. No need for separate weekly/monthly/yearly tables — they're derived from the daily
table (a view/query is enough; materialize later only if measurements demand it).
- study_days drops out of the table. "Study days in a month" is simply the count of daily rows in that month, so it stops being a stored column and becomes a derived count.
- spent_time and done_exercises sum cleanly across days; first_done / last_done are per-day first/last activity and are combined with MIN/MAX over a period (not SUM).
All-time values that the cumulative snapshot used to hold (lifetime first study, total time, etc.) move into a separate running per-user row (user_lifetime_analytics), upserted rather than re-scanned.
- Keep the current day fresh via event-driven incremental updates
Instead of only filling analytics nightly, update today's delta row immediately when an exercise is completed:
- Hook on study_history commit via @TransactionalEventListener(phase = AFTER_COMMIT) + @async — so it never adds latency to the exercise-completion request.
- Apply an atomic increment in the DB, not a read-modify-write, to stay safe under concurrent completions:
INSERT INTO user_daily_analytics (snapshot_date, user_id, role_name, done_exercises, spent_time, first_done, last_done)
VALUES (current_date, :uid, :role, 1, :sec, :ts, :ts)
ON CONFLICT (user_id, role_name, snapshot_date)
DO UPDATE SET done_exercises = user_daily_analytics.done_exercises + 1,
spent_time = user_daily_analytics.spent_time + EXCLUDED.spent_time,
last_done = GREATEST(user_daily_analytics.last_done, EXCLUDED.last_done);
The nightly job then changes role from filler to reconciler: once a night it recomputes each day's delta from the raw history and overwrites it, self-healing any drift from missed or duplicated events. Net result: incremental
updates give intra-day freshness, the nightly recompute guarantees correctness (belt-and-suspenders).
Note this only works cleanly with the delta model — with a cumulative snapshot, an incremental update would mean recomputing all-time aggregates on every exercise. The delta model makes it a simple +1 / + seconds.
Rollout order: delta migration for user_daily_analytics → event-driven incremental listener → user_lifetime_analytics for all-time columns → read views for week/month/year.
Direction for the user-analytics precompute: per-day delta + near-real-time freshness
Two design points we'd like to adopt as this moves forward.
The current daily aggregation is cumulative — each row holds the all-time total as of that date (the query aggregates the whole study_history with no per-day filter). Proposal: scope each row to the activity of that day only
(WHERE start_time >= current_date AND start_time < current_date + 1), so a row means "what this user did on this day."
Why this is better:
table (a view/query is enough; materialize later only if measurements demand it).
All-time values that the cumulative snapshot used to hold (lifetime first study, total time, etc.) move into a separate running per-user row (user_lifetime_analytics), upserted rather than re-scanned.
Instead of only filling analytics nightly, update today's delta row immediately when an exercise is completed:
The current daily aggregation is cumulative — each row holds the all-time total as of that date (the query aggregates the whole study_history with no per-day filter). Proposal: scope each row to the activity of that day only
(WHERE start_time >= current_date AND start_time < current_date + 1), so a row means "what this user did on this day."
Why this is better:
table (a view/query is enough; materialize later only if measurements demand it).
All-time values that the cumulative snapshot used to hold (lifetime first study, total time, etc.) move into a separate running per-user row (user_lifetime_analytics), upserted rather than re-scanned.
Instead of only filling analytics nightly, update today's delta row immediately when an exercise is completed:
INSERT INTO user_daily_analytics (snapshot_date, user_id, role_name, done_exercises, spent_time, first_done, last_done)
VALUES (current_date, :uid, :role, 1, :sec, :ts, :ts)
ON CONFLICT (user_id, role_name, snapshot_date)
DO UPDATE SET done_exercises = user_daily_analytics.done_exercises + 1,
spent_time = user_daily_analytics.spent_time + EXCLUDED.spent_time,
last_done = GREATEST(user_daily_analytics.last_done, EXCLUDED.last_done);
The nightly job then changes role from filler to reconciler: once a night it recomputes each day's delta from the raw history and overwrites it, self-healing any drift from missed or duplicated events. Net result: incremental
updates give intra-day freshness, the nightly recompute guarantees correctness (belt-and-suspenders).
Note this only works cleanly with the delta model — with a cumulative snapshot, an incremental update would mean recomputing all-time aggregates on every exercise. The delta model makes it a simple +1 / + seconds.
Rollout order: delta migration for user_daily_analytics → event-driven incremental listener → user_lifetime_analytics for all-time columns → read views for week/month/year.