-
Notifications
You must be signed in to change notification settings - Fork 1.2k
Expand file tree
/
Copy pathworkspacebuilds.sql
More file actions
293 lines (277 loc) · 7.17 KB
/
workspacebuilds.sql
File metadata and controls
293 lines (277 loc) · 7.17 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
-- name: GetWorkspaceBuildByID :one
SELECT
*
FROM
workspace_build_with_user AS workspace_builds
WHERE
id = $1
LIMIT
1;
-- name: GetWorkspaceBuildByJobID :one
SELECT
*
FROM
workspace_build_with_user AS workspace_builds
WHERE
job_id = $1
LIMIT
1;
-- name: GetWorkspaceBuildsCreatedAfter :many
SELECT * FROM workspace_build_with_user WHERE created_at > $1;
-- name: GetWorkspaceBuildByWorkspaceIDAndBuildNumber :one
SELECT
*
FROM
workspace_build_with_user AS workspace_builds
WHERE
workspace_id = $1
AND build_number = $2;
-- name: GetWorkspaceBuildsByWorkspaceID :many
SELECT
*
FROM
workspace_build_with_user AS workspace_builds
WHERE
workspace_builds.workspace_id = $1
AND workspace_builds.created_at > @since
AND CASE
-- This allows using the last element on a page as effectively a cursor.
-- This is an important option for scripts that need to paginate without
-- duplicating or missing data.
WHEN @after_id :: uuid != '00000000-0000-0000-0000-000000000000'::uuid THEN (
-- The pagination cursor is the last ID of the previous page.
-- The query is ordered by the build_number field, so select all
-- rows after the cursor.
build_number > (
SELECT
build_number
FROM
workspace_builds
WHERE
id = @after_id
)
)
ELSE true
END
ORDER BY
build_number desc OFFSET @offset_opt
LIMIT
-- A null limit means "no limit", so 0 means return all
NULLIF(@limit_opt :: int, 0);
-- name: GetLatestWorkspaceBuildByWorkspaceID :one
SELECT
*
FROM
workspace_build_with_user AS workspace_builds
WHERE
workspace_id = $1
ORDER BY
build_number desc
LIMIT
1;
-- name: GetLatestWorkspaceBuildsByWorkspaceIDs :many
SELECT
DISTINCT ON (workspace_id)
*
FROM
workspace_build_with_user AS workspace_builds
WHERE
workspace_id = ANY(@ids :: uuid [ ])
ORDER BY
workspace_id, build_number DESC -- latest first
;
-- name: InsertWorkspaceBuild :exec
INSERT INTO
workspace_builds (
id,
created_at,
updated_at,
workspace_id,
template_version_id,
"build_number",
transition,
initiator_id,
job_id,
provisioner_state,
deadline,
max_deadline,
reason,
template_version_preset_id
)
VALUES
($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14);
-- name: UpdateWorkspaceBuildCostByID :exec
UPDATE
workspace_builds
SET
daily_cost = $2
WHERE
id = $1;
-- name: UpdateWorkspaceBuildDeadlineByID :exec
UPDATE
workspace_builds
SET
deadline = @deadline::timestamptz,
max_deadline = @max_deadline::timestamptz,
updated_at = @updated_at::timestamptz
FROM
workspaces
WHERE
workspace_builds.id = @id::uuid
AND workspace_builds.workspace_id = workspaces.id
-- Prebuilt workspaces (identified by having the prebuilds system user as owner_id)
-- are managed by the reconciliation loop, not the lifecycle executor which handles
-- deadline and max_deadline
AND workspaces.owner_id != 'c42fdf75-3097-471c-8c33-fb52454d81c0'::UUID;
-- name: UpdateWorkspaceBuildProvisionerStateByID :exec
UPDATE
workspace_builds
SET
provisioner_state = @provisioner_state::bytea,
updated_at = @updated_at::timestamptz
WHERE id = @id::uuid;
-- name: GetActiveWorkspaceBuildsByTemplateID :many
SELECT wb.*
FROM (
SELECT
workspace_id, MAX(build_number) as max_build_number
FROM
workspace_build_with_user AS workspace_builds
WHERE
workspace_id IN (
SELECT
id
FROM
workspaces
WHERE
template_id = $1
)
GROUP BY
workspace_id
) m
JOIN
workspace_build_with_user AS wb
ON m.workspace_id = wb.workspace_id AND m.max_build_number = wb.build_number
JOIN
provisioner_jobs AS pj
ON wb.job_id = pj.id
WHERE
wb.transition = 'start'::workspace_transition
AND
pj.completed_at IS NOT NULL;
-- name: GetWorkspaceBuildStatsByTemplates :many
SELECT
w.template_id,
t.name AS template_name,
t.display_name AS template_display_name,
t.organization_id AS template_organization_id,
COUNT(*) AS total_builds,
COUNT(CASE WHEN pj.job_status = 'failed' THEN 1 END) AS failed_builds
FROM
workspace_build_with_user AS wb
JOIN
workspaces AS w ON
wb.workspace_id = w.id
JOIN
provisioner_jobs AS pj ON
wb.job_id = pj.id
JOIN
templates AS t ON
w.template_id = t.id
WHERE
wb.created_at >= @since
AND pj.completed_at IS NOT NULL
GROUP BY
w.template_id, template_name, template_display_name, template_organization_id
ORDER BY
template_name ASC;
-- name: GetFailedWorkspaceBuildsByTemplateID :many
SELECT
tv.name AS template_version_name,
u.username AS workspace_owner_username,
w.name AS workspace_name,
w.id AS workspace_id,
wb.build_number AS workspace_build_number
FROM
workspace_build_with_user AS wb
JOIN
workspaces AS w
ON
wb.workspace_id = w.id
JOIN
users AS u
ON
w.owner_id = u.id
JOIN
provisioner_jobs AS pj
ON
wb.job_id = pj.id
JOIN
templates AS t
ON
w.template_id = t.id
JOIN
template_versions AS tv
ON
wb.template_version_id = tv.id
WHERE
w.template_id = $1
AND wb.created_at >= @since
AND pj.completed_at IS NOT NULL
AND pj.job_status = 'failed'
ORDER BY
tv.name ASC, wb.build_number DESC;
-- name: UpdateWorkspaceBuildFlagsByID :exec
UPDATE
workspace_builds
SET
has_ai_task = @has_ai_task,
has_external_agent = @has_external_agent,
updated_at = @updated_at::timestamptz
WHERE id = @id::uuid;
-- name: GetWorkspaceBuildMetricsByResourceID :one
-- Returns build metadata for e2e workspace build duration metrics.
-- Also checks if all agents are ready and returns the worst status.
SELECT
wb.created_at,
wb.transition,
t.name AS template_name,
o.name AS organization_name,
(w.owner_id = 'c42fdf75-3097-471c-8c33-fb52454d81c0') AS is_prebuild,
-- All agents must have ready_at set (terminal startup state)
COUNT(*) FILTER (WHERE wa.ready_at IS NULL) = 0 AS all_agents_ready,
-- Latest ready_at across all agents (for duration calculation)
MAX(wa.ready_at)::timestamptz AS last_agent_ready_at,
-- Worst status: error > timeout > ready
CASE
WHEN bool_or(wa.lifecycle_state = 'start_error') THEN 'error'
WHEN bool_or(wa.lifecycle_state = 'start_timeout') THEN 'timeout'
ELSE 'success'
END AS worst_status
FROM workspace_builds wb
JOIN workspaces w ON wb.workspace_id = w.id
JOIN templates t ON w.template_id = t.id
JOIN organizations o ON t.organization_id = o.id
JOIN workspace_resources wr ON wr.job_id = wb.job_id
JOIN workspace_agents wa ON wa.resource_id = wr.id AND wa.parent_id IS NULL
WHERE wb.job_id = (SELECT job_id FROM workspace_resources WHERE workspace_resources.id = $1)
GROUP BY wb.created_at, wb.transition, t.name, o.name, w.owner_id;
-- name: GetWorkspaceBuildProvisionerStateByID :one
-- Fetches the provisioner state of a workspace build, joined through to the
-- template so that dbauthz can enforce policy.ActionUpdate on the template.
-- Provisioner state contains sensitive Terraform state and should only be
-- accessible to template administrators.
SELECT
workspace_builds.provisioner_state,
templates.id AS template_id,
templates.organization_id AS template_organization_id,
templates.user_acl,
templates.group_acl
FROM
workspace_builds
INNER JOIN
workspaces ON workspaces.id = workspace_builds.workspace_id
INNER JOIN
templates ON templates.id = workspaces.template_id
WHERE
workspace_builds.id = @workspace_build_id;