Skip to content

Commit 40c7cdb

Browse files
geokatgeokat
committed
Fetch user/group visible info for workspaces ACL actors through workspaces_expanded view
Instead of N+1 in coderd.
1 parent 675911e commit 40c7cdb

File tree

8 files changed

+212
-149
lines changed

8 files changed

+212
-149
lines changed

‎coderd/aitasks.go‎

Lines changed: 0 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -487,8 +487,6 @@ func (api *API) convertTasks(ctx context.Context, requesterID uuid.UUID, dbTasks
487487
requesterID,
488488
workspaces,
489489
data,
490-
nil,
491-
nil,
492490
)
493491
if err != nil {
494492
return nil, xerrors.Errorf("convert workspaces: %w", err)
@@ -572,8 +570,6 @@ func (api *API) taskGet(rw http.ResponseWriter, r *http.Request) {
572570
data.templates[0],
573571
api.Options.AllowWorkspaceRenames,
574572
appStatus,
575-
nil,
576-
nil,
577573
)
578574
if err != nil {
579575
httpapi.Write(ctx, rw, http.StatusInternalServerError, codersdk.Response{

‎coderd/database/dump.sql‎

Lines changed: 6 additions & 2 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/migrations/000402_workspaces_expanded_acl_actor_info.down.sql‎

Lines changed: 41 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,41 @@
1+
DROP VIEW workspaces_expanded;
2+
3+
-- Revert to passing through raw user_acl and group_acl columns.
4+
CREATE VIEW workspaces_expanded AS
5+
SELECT workspaces.id,
6+
workspaces.created_at,
7+
workspaces.updated_at,
8+
workspaces.owner_id,
9+
workspaces.organization_id,
10+
workspaces.template_id,
11+
workspaces.deleted,
12+
workspaces.name,
13+
workspaces.autostart_schedule,
14+
workspaces.ttl,
15+
workspaces.last_used_at,
16+
workspaces.dormant_at,
17+
workspaces.deleting_at,
18+
workspaces.automatic_updates,
19+
workspaces.favorite,
20+
workspaces.next_start_at,
21+
workspaces.group_acl,
22+
workspaces.user_acl,
23+
visible_users.avatar_url AS owner_avatar_url,
24+
visible_users.username AS owner_username,
25+
visible_users.name AS owner_name,
26+
organizations.name AS organization_name,
27+
organizations.display_name AS organization_display_name,
28+
organizations.icon AS organization_icon,
29+
organizations.description AS organization_description,
30+
templates.name AS template_name,
31+
templates.display_name AS template_display_name,
32+
templates.icon AS template_icon,
33+
templates.description AS template_description,
34+
tasks.id AS task_id
35+
FROM ((((workspaces
36+
JOIN visible_users ON ((workspaces.owner_id = visible_users.id)))
37+
JOIN organizations ON ((workspaces.organization_id = organizations.id)))
38+
JOIN templates ON ((workspaces.template_id = templates.id)))
39+
LEFT JOIN tasks ON ((workspaces.id = tasks.workspace_id)));
40+
41+
COMMENT ON VIEW workspaces_expanded IS 'Joins in the display name information such as username, avatar, and organization name.';

‎coderd/database/migrations/000402_workspaces_expanded_acl_actor_info.up.sql‎

Lines changed: 63 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,63 @@
1+
DROP VIEW workspaces_expanded;
2+
3+
-- Enrich group_acl and user_acl with the actors' display information.
4+
CREATE VIEW workspaces_expanded AS
5+
SELECT workspaces.id,
6+
workspaces.created_at,
7+
workspaces.updated_at,
8+
workspaces.owner_id,
9+
workspaces.organization_id,
10+
workspaces.template_id,
11+
workspaces.deleted,
12+
workspaces.name,
13+
workspaces.autostart_schedule,
14+
workspaces.ttl,
15+
workspaces.last_used_at,
16+
workspaces.dormant_at,
17+
workspaces.deleting_at,
18+
workspaces.automatic_updates,
19+
workspaces.favorite,
20+
workspaces.next_start_at,
21+
-- Enrich group_acl with group info
22+
COALESCE((
23+
SELECT jsonb_object_agg(
24+
acl.key,
25+
acl.value || jsonb_build_object(
26+
'name', g.name,
27+
'avatar_url', COALESCE(g.avatar_url, '')
28+
)
29+
)
30+
FROM jsonb_each(workspaces.group_acl) AS acl
31+
LEFT JOIN groups g ON g.id = acl.key::uuid
32+
), '{}'::jsonb) AS group_acl,
33+
-- Enrich user_acl with user info
34+
COALESCE((
35+
SELECT jsonb_object_agg(
36+
acl.key,
37+
acl.value || jsonb_build_object(
38+
'name', COALESCE(vu.name, ''),
39+
'avatar_url', COALESCE(vu.avatar_url, '')
40+
)
41+
)
42+
FROM jsonb_each(workspaces.user_acl) AS acl
43+
LEFT JOIN visible_users vu ON vu.id = acl.key::uuid
44+
), '{}'::jsonb) AS user_acl,
45+
visible_users.avatar_url AS owner_avatar_url,
46+
visible_users.username AS owner_username,
47+
visible_users.name AS owner_name,
48+
organizations.name AS organization_name,
49+
organizations.display_name AS organization_display_name,
50+
organizations.icon AS organization_icon,
51+
organizations.description AS organization_description,
52+
templates.name AS template_name,
53+
templates.display_name AS template_display_name,
54+
templates.icon AS template_icon,
55+
templates.description AS template_description,
56+
tasks.id AS task_id
57+
FROM ((((workspaces
58+
JOIN visible_users ON ((workspaces.owner_id = visible_users.id)))
59+
JOIN organizations ON ((workspaces.organization_id = organizations.id)))
60+
JOIN templates ON ((workspaces.template_id = templates.id)))
61+
LEFT JOIN tasks ON ((workspaces.id = tasks.workspace_id)));
62+
63+
COMMENT ON VIEW workspaces_expanded IS 'Joins in the display name information such as username, avatar, and organization name.';

‎coderd/database/queries.sql.go‎

Lines changed: 2 additions & 2 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/types.go‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -112,6 +112,8 @@ func (t WorkspaceACL) Value() (driver.Value, error) {
112112

113113
type WorkspaceACLEntry struct {
114114
Permissions []policy.Action `json:"permissions"`
115+
Name string `json:"name"`
116+
AvatarURL string `json:"avatar_url"`
115117
}
116118

117119
type ExternalAuthProvider struct {

0 commit comments

Comments
 (0)