RFC: Insights page for Coder admins

[BrunoQuaresma]

I have had discussions with a few users to gather valuable insights on the data they find interesting to have. Primarily, they are focused on Coder engagement and detecting failures/errors. To enhance visibility in these areas, we can have a dashboard with the following components:

• DAU Chart: To provide a clear view of user engagement.
• Inactive Users List: To track user inactivity, I have incorporated a list that highlights inactive users.

In terms of error/failure detection, we can implement the following features:

• Error Charts: Display errors by day and by template, helping identify patterns and trends.
• Most Used Templates by Status: This feature presents the templates that are most commonly utilized and shows the percentage of failure, canceled, and success statuses for each template.
• Recent Audit Logs: The audit logs have defaulted to display the most recent entries filtered specifically for errors or failed actions.

These additions aim to provide valuable insights and facilitate the identification of engagement patterns and potential issues for our customers. A preview of how it should look like:

The mentioned features are just the initial features we want to have, but we also expect to have in a second version the following features:

• User usage by IDE: This feature will track and display the usage of different IDEs by users.
• List users with higher latency: This feature will provide a list of users experiencing higher latency.

These additions will be included in the second version to further enhance our insights and improve the overall user experience.

Back-end

I will wait until we have approval from @bpmct and @mtojek regarding the feature proposal to describe the requirements from the back-end (BE) to develop this screen.

[bpmct]

I like this first iteration. It is pretty clear to me what the first steps are and next steps. What do you think about also switching the order of items in the sidebar? (failed actions are next to failed builds, active users are next to DAU chart)

[BrunoQuaresma]

Makes total sense.

[kylecarbs]

Should this be per-template instead of global? Or maybe we allow for a filter...

[BrunoQuaresma]

Idk, I still think the insights belong to the deployment and not to the template. Eg. If I want to see the user activity I would like to see it per deployment and not per template, if I want to see the number of workspaces in a failed state I would do it for deployment so I can see of this is specific to a template, etc. I see value on scope by template, I just think deployment metrics give the user a better view on what is happening.

[BrunoQuaresma]

@bpmct what popular parameters are?

[bpmct]

Like what rich parameters are most used with a template. Specifically:

• What images are users using with a template?
• What shell do users use with a template?
• What repos do use with a template?

All are rich parameters at the moment but there's no way to see an aggregate / summary of what people use

[BrunoQuaresma]

Ahhhhh I see. But this would be by template right?

[bpmct]

Yes. Per template :)

[kylecarbs]

Here's a query that returns the connection latency in milliseconds for all users grouped by template:

SELECT
	user_id,
	template_id,
	coalesce((PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY connection_median_latency_ms)), -1)::FLOAT AS workspace_connection_latency_50,
	coalesce((PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY connection_median_latency_ms)), -1)::FLOAT AS workspace_connection_latency_95
FROM
	workspace_agent_stats WHERE connection_median_latency_ms > 0 GROUP BY user_id, template_id;

This would let us easily understand which users are having a bad experience with specific templates. It's important to group by template because some templates might be region limited.

[BrunoQuaresma]

Since this is planned to be an improvement to be made after the first release we can put some thought on this later. cc.: @bpmct Maybe this should live inside of a "insights" page inside of the template page and not in the "deployment" insights.

[mtojek]

I like the initial concept for the Insights page. We decide to add more visualizations later, but the graphs you placed on the mockup are good candidates for the MVP of the feature. I'm wondering if we should make them draggable, so DevOps can rearrange or show/hide a few of them.

I will wait until we have approval from @bpmct and @mtojek regarding the feature proposal to describe the requirements from the back-end (BE) to develop this screen.

👍

What you presented here is good enough to start drafting these requirements. I'm curious if we should pack all relevant endpoints behind the API family /api/v2/insights 🤔

[BrunoQuaresma]

Make them draggable adds complexity because you would have to be able to resize the areas as well.

I like to put the insights endpoint under the insights route.

[BrunoQuaresma]

So what I'm understanding is we want to have two insights page, one for the deployment and another one for a given template. Is that correct @bpmct @kylecarbs ? If yes, do you think we could start by developing the one related to the deployment since we already have the mock?

[matifali]

I prefer bars instead of lines for 1st and 3rd plots.
But overall looks great. Good choice of colors.

[mtojek]

As part of this RFC, I'd like to see a draft for public backend APIs as early as possible.

(posting here not to forget)

[Emyrk]

Agreed. In v1 we wrote ad hoc sql queries for the few metrics we supported. In v2, we are seemingly doing the same. Metrics are gathered and queried on a case by case basis.

It would be ideal to leverage similar apis to prometheus or another standard time series db. Maybe we can even leverage some library that sits ontop of postgres.

Whatever we come up with should be easy to expand and adapt to new requirements (adding labels for filters, changing the query to say daily vs weekly etc). The sql queries in v1 were very hard to maintain as they were long and difficult to follow and test.

[mafredri]

This is a proposal for the backend API of RFC: Insights page for Coder admins.

This proposal introduces a single endpoint for reporting template insights (or deployment wide, given no template filter). The motivation behind this is to simplify the API and reduce the number of requests needed to get all the data for the insights page. Outside the scope of the proposal, this format can also help ensure data consistency between weekly/daily intervals (for instance when viewing this week and new data came in between the two requests). This also lets us handle concurrency on the server-side instead of the client performing multiple concurrent requests.

We would introduce the following endpoint, request and response:

GET /api/v2/insights/templates?start=01-07-2023&end=08-07-2023&interval=day&templates=name1,name2

{
    "report": {
        "start_time": "2023-07-01T00:00:00.000000Z",
        "end_time": "2023-07-08T00:00:00.000000Z",
        "templates": ["uuid1", "uuid2"],
        "active_users": 22,
        "user_latency": [
            {
                "user_id": "fcb9f5c7-ad6d-4515-b12e-496bc04ca116", // Optional, useful for linking.
                "name": "John Doe",
                "connection_latency_ms": {
                    "P50": 5.601,
                    "P95": 16.352049999999984
                }
            },
            {
                "user_id": "aee4bef9-479f-488e-abb4-b2bce2bf9e0d",
                "name": "Jane Doe",
                "connection_latency_ms": {
                    "P50": 31.312,
                    "P95": 119.832
                }
            }
        ],
        "usage_builtin": {
            "vscode": {
                // TODO: Name + icon here too, to simplify the UI?
                "seconds": 54000
            },
            "jetbrains": {
                "seconds": 900
            },
            "web-terminal": {
                "seconds": 5400
            },
            "ssh": {
                "seconds": 10800
            }
        },
        "usage_apps": [
            {
                // As long as name/slug/icon match, we can merge these between multiple templates.
                "display_name": "code-server",
                "slug": "code-server",
                "icon": "/icon/code.svg",
                "seconds": 10800,
            }
        ]
        "usage_parameters": [
            {
                // As long as name/slug match, we can merge these between multiple templates.
                "display_name": "Coder Repository Directory",
                "name": "coder_repository_directory",
                "values": [
                    {
                        "value": "~/coder",
                        "icon": "",
                        "count": 10
                    },
                    {
                        "value": "~/coder.com",
                        "icon": "",
                        "count": 2
                    }
                ]
            },
            {
                "display_name": "Dotfiles URL",
                "name": "dotfiles_url",
                "values": [
                    {
                        "value": "~/usr/.file",
                        "icon": "",
                        "count": 10
                    },
                    {
                        "value": null,
                        "icon": "",
                        "count": 2
                    }
                ]
            },
            {
                "display_name": "Region",
                "name": "region",
                "values": [
                    {
                        "value": "Pittsburgh",
                        "icon": "/icon/flag1.svg",
                        "count": 8
                    },
                    {
                        "value": "Helsinki",
                        "icon": "/icon/flag1.svg",
                        "count": 2
                    },
                    {
                        "value": "Sydney",
                        "icon": "/icon/flag3.svg",
                        "count": 1
                    },
                    {
                        "value": "Sao Paulo",
                        "icon": "/icon/flag4.svg",
                        "count": 1
                    }
                ]
            }
        ]
    },
    "interval_reports": [
        {
            "start_time": "2023-07-01T00:00:00.000000Z",
            "end_time": "2023-07-02T00:00:00.000000Z",
            "templates": ["uuid1", "uuid2"],
            "interval": "day",
            "active_users": 19
        },
        {
            "start_time": "2023-07-02T00:00:00.000000Z",
            "end_time": "2023-07-03T00:00:00.000000Z",
            ...
        },
        { ... },
        { ... },
        { ... },
        { ... },
        { ... }
    ]
}

Note: One logical split that could be done here is to separate active_users, user_latencies and (maybe) usage_builtin into their own endpoint (/users, with template filter). This could make sense if we don't want to support aggregate usage of apps/parameters for the deployment or across multiple templates.

For now, our interval reporting requirements are slim, and we only need this data for day or perhaps hour. For this reason the interval report field only has one KPI, although we could duplicate all data from the main report as well.

We can introduce this endpoint in stages where we start with a single or a few KPIs, and expand upon it as we go. The first stage would be to introduce the endpoint with the following KPIs (they are all based on the same existing data source):

• Daily active users
• User latency
• Usage builtin

This data is available, but we need to write queries to pull it out:

• Parameter usage

We currently don't track the following, which will require storing the data and querying it:

• Apps usage

[BrunoQuaresma]

The interface looks good!

• I think we will never need to have data aggregate hourly.
• It is nice to support multiple templates for aggregation but for now, in the UI, we are only going to support one template per time. If having this, aggregating multiple templates, are easy... that is awesome for extensibility in the future.
• I'm ok with your plan to move by stages 👍 we can make it experimental in the UI until we have a good enough first version
• Another thing to consider is, when apps usage is done, it probably should be merged with the usage built-in.
• How to collect app usage sounds like a mystery. Do you have any ideas on how to make that?

[mafredri]

I think we will never need to have data aggregate hourly.

👍🏻

It is nice to support multiple templates for aggregation but for now, in the UI, we are only going to support one template per time. If having this, aggregating multiple templates, are easy... that is awesome for extensibility in the future.

Ok, this is good to know. One question comes to mind: If this was a view of deployment, would we want to be able to show app/parameter usage there as well? If yes, I think it makes sense keeping as is, but if no, then we can simplify this data to be for one template only.

I'm ok with your plan to move by stages 👍 we can make it experimental in the UI until we have a good enough first version

Sounds good 👍🏻

Another thing to consider is, when apps usage is done, it probably should be merged with the usage built-in.

I think this is sensible. Maybe we should change this from the get-go? Like this:

        "usage": [
            {
                "type": "builtin",
                "display_name": "Visual Studio Code", // Could be omitted/let frontend decide.
                "slug": "vscode",
                "icon": "/icon/vscode.svg",  // Could be omitted/let frontend decide.
                "seconds": 54000,
            },
            {
                "type": "app",
                "display_name": "code-server",
                "slug": "code-server",
                "icon": "/icon/code.svg",
                "seconds": 10800,
            }
        ],

This format is conducive to introducing new data in the UI without needing frontend changes, the backend can simply add to the array and it would show up.

How to collect app usage sounds like a mystery. Do you have any ideas on how to make that?

I was thinking we could track activity on the proxied app URL (e.g. /@user/workspace/apps/app-name/) with a type of debounce, as long as there's a long-running request (e.g. websocket) or any another other request in, say, 5 minutes, usage is increased. I can't say for sure how well this will work in practice, though.

[mtojek]

Thanks for flashing out the API sketch! I reviewed it, and here is a set of questions I have:

1. user_latency: what if we have 1000 active users? Will it blow up the JSON?
2. usage_apps: same question about apps
3. usage_parameters:
   3.1. Is there a risk of leaking something sensitive?
   3.2. Should we link them with templates/template revisions?
   3.3. Some parameters are lists of strings. In the future we may want to support full objects. I'm not sure if we need to expose all these.
4. we only need this data for day or perhaps hour - I would expose only the aggregations we need

[mafredri]

Thanks for the feedback @mtojek!

1. A good observation. I doubt it will be a problem for a while, but if we want to limit the impact we can change this response to be top_10/bottom_10 latencies, alternatively we split it to it's own endpoint so we can request with sorting and limit. Any preference @BrunoQuaresma? Latter could support pagination which seems like it'd be ideal for this data type.
2. Apps won't scale like users, there's only so many apps/templates and unique app names, so I think we'll be fine here
3. • I suppose we should follow the same rules as the dashboard here, i.e. if I can't view a users workspace settings/params, I shouldn't be able to see them in the statistics (or they'll be in the stats but [REDACTED], simpler to omit for now though)
   • If we limit this endpoint to be single template insights, then no, but if we support aggregates, we can add template_version_ids: [...] as a field to the parameter.
   • We can support formatting all types of parameter values to strings, a list could become "item1, item2"
4. Definitely 👍🏻

[mafredri]

Here is an updated proposal, based on the feedback:

The template_ids fields have been added to more sections so that it can be inferred which template(s) contributed to the data. This is mostly a "for free" field that can be removed if we feel it adds too much noise.

GET /api/v2/insights/templates?start_time=01-07-2023&end_time=08-07-2023&interval=day&templates=name1,name2

{
    "report": {
        "start_time": "2023-07-01T00:00:00.000000Z",
        "end_time": "2023-07-08T00:00:00.000000Z",
        "template_ids": ["uuid1", "uuid2"],
        "active_users": 22,
        "usage_apps": [
            {
                "template_ids": ["uuid1", "uuid2"],
                "type": "builtin",
                "display_name": "Visual Studio Code",
                "slug": "vscode",
                "icon": "/icon/vscode.svg",
                "seconds": 54000,
            },
            {
                "template_ids": ["uuid1", "uuid2"],
                "type": "builtin",
                "display_name": "JetBrains",
                "slug": "jetbrains",
                "icon": "/icon/jetbrains.svg",
                "seconds": 900,
            },
            {
                "template_ids": ["uuid1", "uuid2"],
                "type": "builtin",
                "display_name": "Web Terminal",
                "slug": "web-terminal",
                "icon": "/icon/terminal.svg",
                "seconds": 5400,
            },
            {
                "template_ids": ["uuid1", "uuid2"],
                "type": "builtin",
                "display_name": "SSH",
                "slug": "ssh",
                "icon": "/icon/ssh.svg",
                "seconds": 10800,
            },
            {
                "template_ids": ["uuid1", "uuid2"],
                "type": "app",
                "display_name": "code-server",
                "slug": "code-server",
                "icon": "/icon/code.svg",
                "seconds": 10800,
            }
        ],
        "usage_parameters": [
            {
                "template_ids": ["uuid1", "uuid2"],
                "display_name": "Coder Repository Directory",
                "name": "coder_repository_directory",
                "values": [
                    {
                        "value": "~/coder",
                        "icon": "",
                        "count": 10
                    },
                    {
                        "value": "~/coder.com",
                        "icon": "",
                        "count": 2
                    }
                ]
            },
            {
                "template_ids": ["uuid2"],
                "display_name": "Dotfiles URL",
                "name": "dotfiles_url",
                "values": [
                    {
                        "value": "~/usr/.file",
                        "icon": "",
                        "count": 10
                    },
                    {
                        "value": null,
                        "icon": "",
                        "count": 2
                    }
                ]
            },
            {
                "template_ids": ["uuid1"],
                "display_name": "Region",
                "name": "region",
                "values": [
                    {
                        "value": "Pittsburgh",
                        "icon": "/icon/flag1.svg",
                        "count": 8
                    },
                    {
                        "value": "Helsinki",
                        "icon": "/icon/flag1.svg",
                        "count": 2
                    },
                    {
                        "value": "Sydney",
                        "icon": "/icon/flag3.svg",
                        "count": 1
                    },
                    {
                        "value": "Sao Paulo",
                        "icon": "/icon/flag4.svg",
                        "count": 1
                    }
                ]
            }
        ]
    },
    "interval_reports": [
        {
            "start_time": "2023-07-01T00:00:00.000000Z",
            "end_time": "2023-07-02T00:00:00.000000Z",
            "template_ids": ["uuid1", "uuid2"],
            "interval": "day",
            "active_users": 19
        },
        {
            "start_time": "2023-07-02T00:00:00.000000Z",
            "end_time": "2023-07-03T00:00:00.000000Z",
            ...
        },
        { ... },
        { ... },
        { ... },
        { ... },
        { ... }
    ]
}

User latency is it's own endpoint that supports filtering on template, this allows us to easily support pagination as needed.

GET /api/v2/insights/user-latency?start_time=01-07-2023&end_time=08-07-2023&templates=name1,name2&order_by=P50&order=asc

{
    "report": {
        "start_time": "2023-07-01T00:00:00.000000Z",
        "end_time": "2023-07-08T00:00:00.000000Z",
        "template_ids": ["uuid1", "uuid2"],
        "latency": [
            {
                "template_ids": ["uuid1"],
                "user_id": "fcb9f5c7-ad6d-4515-b12e-496bc04ca116",
                "name": "John Doe",
                "connection_latency_ms": {
                    "P50": 5.601,
                    "P95": 16.352049999999984
                }
            },
            {
                "template_ids": ["uuid2"],
                "user_id": "aee4bef9-479f-488e-abb4-b2bce2bf9e0d",
                "name": "Jane Doe",
                "connection_latency_ms": {
                    "P50": 31.312,
                    "P95": 119.832
                }
            }
        ],
    }
}

[matifali]

We send many metrics on Prometheus, so why are we adding this natively to Coder? Can't a user create their dashboard on Grafana using our Prometheus?
If yes, then we should provide some sample/starter Grafana dashboards.

[mtojek]

If yes, then we should provide some sample/starter Grafana dashboards.

link

[matifali]

I know about this. But I was thinking about the need and motivation to do this nativity.

[mafredri]

Something that's not possible via Prometheus, for example, is giving the number of unique active users for a certain time-frame (something that's to be shown in the proposed insights page). Prometheus can show how many unique there are at any certain time, but if we want the count for a day we can't simply add these values.

[BrunoQuaresma]

A good observation. I doubt it will be a problem for a while, but if we want to limit the impact we can change this response to be top_10/bottom_10 latencies, alternatively we split it to it's own endpoint so we can request with sorting and limit. Any preference @BrunoQuaresma? Latter could support pagination which seems like it'd be ideal for this data type.

Sounds reasonable, so we can paginate these results.