Skip to content

Commit d58cd66

Browse files
hugodutkahugodutka
committed
add ai tasks migrations
1 parent 949ab4b commit d58cd66

File tree

2 files changed

+180
-0
lines changed

2 files changed

+180
-0
lines changed

‎coderd/database/migrations/000335_ai_tasks.down.sql

Lines changed: 77 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,77 @@
1+
DROP VIEW workspace_build_with_user;
2+
3+
DROP VIEW template_version_with_user;
4+
5+
DROP INDEX idx_template_versions_has_ai_task;
6+
7+
ALTER TABLE
8+
template_versions DROP COLUMN has_ai_task;
9+
10+
ALTER TABLE
11+
workspace_builds DROP CONSTRAINT workspace_builds_ai_tasks_sidebar_app_id_fkey;
12+
13+
ALTER TABLE
14+
workspace_builds DROP COLUMN ai_tasks_sidebar_app_id;
15+
16+
ALTER TABLE
17+
workspace_builds DROP COLUMN has_ai_task;
18+
19+
-- Recreate `workspace_build_with_user` as defined in dump.sql
20+
CREATE VIEW workspace_build_with_user AS
21+
SELECT
22+
workspace_builds.id,
23+
workspace_builds.created_at,
24+
workspace_builds.updated_at,
25+
workspace_builds.workspace_id,
26+
workspace_builds.template_version_id,
27+
workspace_builds.build_number,
28+
workspace_builds.transition,
29+
workspace_builds.initiator_id,
30+
workspace_builds.provisioner_state,
31+
workspace_builds.job_id,
32+
workspace_builds.deadline,
33+
workspace_builds.reason,
34+
workspace_builds.daily_cost,
35+
workspace_builds.max_deadline,
36+
workspace_builds.template_version_preset_id,
37+
COALESCE(visible_users.avatar_url, '' :: text) AS initiator_by_avatar_url,
38+
COALESCE(visible_users.username, '' :: text) AS initiator_by_username,
39+
COALESCE(visible_users.name, '' :: text) AS initiator_by_name
40+
FROM
41+
(
42+
workspace_builds
43+
LEFT JOIN visible_users ON (
44+
(workspace_builds.initiator_id = visible_users.id)
45+
)
46+
);
47+
48+
COMMENT ON VIEW workspace_build_with_user IS 'Joins in the username + avatar url of the initiated by user.';
49+
50+
-- Recreate `template_version_with_user` as defined in dump.sql
51+
CREATE VIEW template_version_with_user AS
52+
SELECT
53+
template_versions.id,
54+
template_versions.template_id,
55+
template_versions.organization_id,
56+
template_versions.created_at,
57+
template_versions.updated_at,
58+
template_versions.name,
59+
template_versions.readme,
60+
template_versions.job_id,
61+
template_versions.created_by,
62+
template_versions.external_auth_providers,
63+
template_versions.message,
64+
template_versions.archived,
65+
template_versions.source_example_id,
66+
COALESCE(visible_users.avatar_url, '' :: text) AS created_by_avatar_url,
67+
COALESCE(visible_users.username, '' :: text) AS created_by_username,
68+
COALESCE(visible_users.name, '' :: text) AS created_by_name
69+
FROM
70+
(
71+
template_versions
72+
LEFT JOIN visible_users ON (
73+
(template_versions.created_by = visible_users.id)
74+
)
75+
);
76+
77+
COMMENT ON VIEW template_version_with_user IS 'Joins in the username + avatar url of the created by user.';

‎coderd/database/migrations/000335_ai_tasks.up.sql

Lines changed: 103 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,103 @@
1+
-- Determines if a coder_ai_task resource was included in a
2+
-- workspace build.
3+
ALTER TABLE
4+
workspace_builds
5+
ADD
6+
COLUMN has_ai_task BOOLEAN NOT NULL DEFAULT FALSE;
7+
8+
-- The app that is displayed in the ai tasks sidebar.
9+
ALTER TABLE
10+
workspace_builds
11+
ADD
12+
COLUMN ai_tasks_sidebar_app_id UUID DEFAULT NULL;
13+
14+
ALTER TABLE
15+
workspace_builds
16+
ADD
17+
CONSTRAINT workspace_builds_ai_tasks_sidebar_app_id_fkey FOREIGN KEY (ai_tasks_sidebar_app_id) REFERENCES workspace_apps(id);
18+
19+
-- Determines if a coder_ai_task resource is defined in a template version.
20+
ALTER TABLE
21+
template_versions
22+
ADD
23+
COLUMN has_ai_task BOOLEAN NOT NULL DEFAULT FALSE;
24+
25+
-- The Tasks tab will be rendered in the UI only if there's at least one template version with has_ai_task set to true.
26+
-- The query to determine this will be run on every UI render, and this index speeds it up.
27+
-- SELECT EXISTS (SELECT 1 FROM template_versions WHERE has_ai_task = TRUE);
28+
CREATE INDEX idx_template_versions_has_ai_task ON template_versions USING btree (has_ai_task);
29+
30+
DROP VIEW workspace_build_with_user;
31+
32+
-- We're adding the has_ai_task and ai_tasks_sidebar_app_id columns.
33+
CREATE VIEW workspace_build_with_user AS
34+
SELECT
35+
workspace_builds.id,
36+
workspace_builds.created_at,
37+
workspace_builds.updated_at,
38+
workspace_builds.workspace_id,
39+
workspace_builds.template_version_id,
40+
workspace_builds.build_number,
41+
workspace_builds.transition,
42+
workspace_builds.initiator_id,
43+
workspace_builds.provisioner_state,
44+
workspace_builds.job_id,
45+
workspace_builds.deadline,
46+
workspace_builds.reason,
47+
workspace_builds.daily_cost,
48+
workspace_builds.max_deadline,
49+
workspace_builds.template_version_preset_id,
50+
workspace_builds.has_ai_task,
51+
workspace_builds.ai_tasks_sidebar_app_id,
52+
COALESCE(
53+
visible_users.avatar_url,
54+
'' :: text
55+
) AS initiator_by_avatar_url,
56+
COALESCE(
57+
visible_users.username,
58+
'' :: text
59+
) AS initiator_by_username,
60+
COALESCE(visible_users.name, '' :: text) AS initiator_by_name
61+
FROM
62+
(
63+
workspace_builds
64+
LEFT JOIN visible_users ON (
65+
(
66+
workspace_builds.initiator_id = visible_users.id
67+
)
68+
)
69+
);
70+
71+
COMMENT ON VIEW workspace_build_with_user IS 'Joins in the username + avatar url of the initiated by user.';
72+
73+
DROP VIEW template_version_with_user;
74+
75+
-- We're adding the has_ai_task column.
76+
CREATE VIEW template_version_with_user AS
77+
SELECT
78+
template_versions.id,
79+
template_versions.template_id,
80+
template_versions.organization_id,
81+
template_versions.created_at,
82+
template_versions.updated_at,
83+
template_versions.name,
84+
template_versions.readme,
85+
template_versions.job_id,
86+
template_versions.created_by,
87+
template_versions.external_auth_providers,
88+
template_versions.message,
89+
template_versions.archived,
90+
template_versions.source_example_id,
91+
template_versions.has_ai_task,
92+
COALESCE(visible_users.avatar_url, '' :: text) AS created_by_avatar_url,
93+
COALESCE(visible_users.username, '' :: text) AS created_by_username,
94+
COALESCE(visible_users.name, '' :: text) AS created_by_name
95+
FROM
96+
(
97+
template_versions
98+
LEFT JOIN visible_users ON (
99+
(template_versions.created_by = visible_users.id)
100+
)
101+
);
102+
103+
COMMENT ON VIEW template_version_with_user IS 'Joins in the username + avatar url of the created by user.';

0 commit comments

Comments
 (0)