feat: store session usage per app and family in template usage stats - #29109

Do we need this table? My instinct is that in the database we should only need to record per app rather than family. The family grouping seems more like a display-time thing to me that we do in code. We need the individual counts anyway to provide the breakdown in the dashboard, right? So eventually this table and associated queries would go unused.

I think the reduction in query complexity would be worth it, unless summing families is prohibitively expensive or something.

Dropped it. Folding in Go is within noise of the family table on reads, and the rollup gets ~20% faster with ~30% less storage. Folding in SQL is the slow path, that's the cost worth avoiding. Can share numbers.

The registry is out of SQL now (@app_families and its dbauthz validation are gone) and attribution applies to all history instead of freezing at rollup time. I also grouped GetTemplateInsightsByTemplate by app so Prometheus and the API agree.

Semantic change: a family total is the sum of its apps, not distinct minutes, so VS Code + Cursor in the same minute counts twice. No-op today since agents report one name per family, starts mattering with #28338. The report's rows already overlap and the UI divides by the row sum, so this keeps families consistent with the per-app breakdown.

Awesome, that makes sense. Honestly, I think it might make sense to eventually always report per-app everywhere and not even bother with families, except where space is very limited like the dashboard status bar.

We could perhaps group the unknown ones into ssh, might be better than losing the data altogether, especially since this list will inevitably become out of date.

A family total sums its apps, so a minute two apps of one family share counts twice.

Nit, but this is just an Go code implementation detail, no? One could also sum them up in a way that does not double-count. Either way though, this comment might not belong here, since the DB has no mention of families otherwise.

Edit: ah well I guess it could not be summed up in a way that does not double count because we return the data across a range, not across a single bucket unless the range is for one bucket, so no way for the consumer to actually tally them up that way.

Huh OK so the grouping sets gives us multiple kinds of rows in one query, sorta, and we can distinguish between the two types of rows using is_user_row (although looks like we could also do it based on user_id <> null since that will be null on the template rows?)

Just talking out loud to make sure I got it right lol

This is faster than two sub-queries I imagine.

Why no end time? I suppose our buckets are always 30 minutes so it would be OK but it does seem a departure from template_usage_stats.

If we change the interval for example then it seems like it would retroactively change the meaning of all these rows (well I guess we could still get the end time from joining on template_usage_stats).

I might be missing something but why do we need a separate multi user and single/multiple app views and filter here, etc?

If I understand correctly, we want a list of users, a sum of the total usage (up to 30 mins per bucket), and then also a sum of each app (each individually up to 30 mins per bucket), and then also get a list of all the templates, and a per-app list of templates.

e.g. could this query be a bit more simply (WHERE clauses omitted, and using the grouping sets assuming they are more performant than multiple sub-queries):

WITH
-- Get usage by bucket, unique template ids, and unique users.
total_usage AS (
  SELECT
    start_time,
	  user_id,
	  template_id,
	  COUNT(*) AS templates,
	  LEAST(SUM(usage_mins), 30) * 60 AS usage_seconds
  FROM template_usage_stats
  GROUP BY GROUPING SETS ((start_time), (template_id), (user_id))
),
-- Get app usage by bucket and unique template ids per app.
app_usage AS (
  SELECT
    template_id,
    app_name,
    LEAST(SUM(usage_mins), 30) * 60 as usage_seconds
  FROM template_usage_stats_session_apps
  GROUP BY GROUPING SETS ((app_name, start_time), (app_name, template_id))
),
-- Sum app usage (separate query since aggregates cannot be nested).
app_totals AS (
  SELECT app_name, SUM(usage_seconds) as usage_seconds
  FROM app_usage
  WHERE template_id IS NULL
  GROUP BY app_name
),
-- Aggregate template ids (separate query since aggregates cannot be nested).
app_templates AS (
  SELECT app_name, array_agg(template_id) as template_ids
  FROM app_usage
  WHERE template_id IS NOT NULL
  GROUP BY app_name
)
SELECT
  COALESCE((SELECT array_agg(template_id) FROM total_usage WHERE template_id IS NOT NULL), '{}')::uuid[] AS template_ids,
  COALESCE(COUNT(user_id), 0)::bigint AS active_users,
  COALESCE((SELECT SUM(usage_seconds) FROM total_usage WHERE start_time IS NOT NULL), 0)::bigint AS usage_total_seconds,
  COALESCE((SELECT jsonb_object_agg(app_name, usage_seconds) FROM app_totals), '{}'::jsonb)::jsonb AS session_app_usage_seconds,
  COALESCE((SELECT jsonb_object_agg(app_name, template_ids) FROM app_templates), '{}'::jsonb)::jsonb AS session_app_template_ids
FROM
  total_usage;

Trying to understand the minute mask. Why do we not just select on the minute (the original date_part('minute', created_at) ? The bit/mask feels like an extra step to get to the same thing.

Oh wait is this by design? We expect to have app stats without any connection count?

And then here I would think something like a count over the minute column to get the total minutes for each 30 min bucket.

So we must be calling this over time frames that were already rolled up?

But wait does this not already handle the case where the apps already exist and are unchanged? So if no apps have changed, this ends up doing nothing. Do we even need to calculate a hash, then? Feels like we can just rely on the conflict semantics here to do the work for us.