2022-04-11 16:06:15 -05:00
|
|
|
-- name: GetWorkspaceAgentByID :one
|
|
|
|
|
SELECT
|
|
|
|
|
*
|
|
|
|
|
FROM
|
|
|
|
|
workspace_agents
|
|
|
|
|
WHERE
|
2025-06-19 16:32:51 +03:00
|
|
|
id = $1
|
|
|
|
|
-- Filter out deleted sub agents.
|
|
|
|
|
AND deleted = FALSE;
|
2022-04-11 16:06:15 -05:00
|
|
|
|
2026-04-16 13:59:09 +02:00
|
|
|
-- name: GetWorkspaceAgentsByInstanceID :many
|
2022-04-01 15:45:23 -05:00
|
|
|
SELECT
|
|
|
|
|
*
|
|
|
|
|
FROM
|
|
|
|
|
workspace_agents
|
|
|
|
|
WHERE
|
|
|
|
|
auth_instance_id = @auth_instance_id :: TEXT
|
2026-04-16 13:59:09 +02:00
|
|
|
-- Filter out deleted agents.
|
2025-06-19 16:32:51 +03:00
|
|
|
AND deleted = FALSE
|
2026-02-19 16:56:29 +00:00
|
|
|
-- Filter out sub agents, they do not authenticate with auth_instance_id.
|
|
|
|
|
AND parent_id IS NULL
|
2022-04-01 15:45:23 -05:00
|
|
|
ORDER BY
|
|
|
|
|
created_at DESC;
|
|
|
|
|
|
2026-05-12 14:55:56 +02:00
|
|
|
-- name: GetWorkspaceBuildAgentsByInstanceID :many
|
|
|
|
|
SELECT
|
|
|
|
|
sqlc.embed(workspace_agents),
|
|
|
|
|
workspace_builds.id AS workspace_build_id,
|
|
|
|
|
sqlc.embed(workspaces)
|
|
|
|
|
FROM
|
|
|
|
|
workspace_agents
|
|
|
|
|
JOIN
|
|
|
|
|
workspace_resources
|
|
|
|
|
ON
|
|
|
|
|
workspace_resources.id = workspace_agents.resource_id
|
|
|
|
|
JOIN
|
|
|
|
|
workspace_builds
|
|
|
|
|
ON
|
|
|
|
|
workspace_builds.job_id = workspace_resources.job_id
|
|
|
|
|
JOIN
|
|
|
|
|
provisioner_jobs
|
|
|
|
|
ON
|
|
|
|
|
provisioner_jobs.id = workspace_builds.job_id
|
|
|
|
|
JOIN
|
|
|
|
|
workspaces
|
|
|
|
|
ON
|
|
|
|
|
workspaces.id = workspace_builds.workspace_id
|
|
|
|
|
WHERE
|
|
|
|
|
workspace_agents.auth_instance_id = @auth_instance_id :: TEXT
|
|
|
|
|
AND workspace_agents.deleted = FALSE
|
|
|
|
|
AND workspace_agents.parent_id IS NULL
|
|
|
|
|
AND provisioner_jobs.type = 'workspace_build'::provisioner_job_type
|
|
|
|
|
AND workspaces.deleted = FALSE
|
|
|
|
|
ORDER BY
|
|
|
|
|
workspace_agents.created_at DESC;
|
|
|
|
|
|
2022-04-11 16:06:15 -05:00
|
|
|
-- name: GetWorkspaceAgentsByResourceIDs :many
|
2022-04-01 15:45:23 -05:00
|
|
|
SELECT
|
|
|
|
|
*
|
|
|
|
|
FROM
|
|
|
|
|
workspace_agents
|
|
|
|
|
WHERE
|
2025-06-19 16:32:51 +03:00
|
|
|
resource_id = ANY(@ids :: uuid [ ])
|
|
|
|
|
-- Filter out deleted sub agents.
|
|
|
|
|
AND deleted = FALSE;
|
2022-04-01 15:45:23 -05:00
|
|
|
|
2022-06-17 00:26:40 -05:00
|
|
|
-- name: GetWorkspaceAgentsCreatedAfter :many
|
2025-06-19 16:32:51 +03:00
|
|
|
SELECT * FROM workspace_agents
|
|
|
|
|
WHERE
|
|
|
|
|
created_at > $1
|
|
|
|
|
-- Filter out deleted sub agents.
|
|
|
|
|
AND deleted = FALSE;
|
2022-06-17 00:26:40 -05:00
|
|
|
|
2022-04-01 15:45:23 -05:00
|
|
|
-- name: InsertWorkspaceAgent :one
|
|
|
|
|
INSERT INTO
|
|
|
|
|
workspace_agents (
|
|
|
|
|
id,
|
2025-05-13 00:01:31 +01:00
|
|
|
parent_id,
|
2022-04-01 15:45:23 -05:00
|
|
|
created_at,
|
|
|
|
|
updated_at,
|
2022-04-11 16:06:15 -05:00
|
|
|
name,
|
2022-04-01 15:45:23 -05:00
|
|
|
resource_id,
|
|
|
|
|
auth_token,
|
|
|
|
|
auth_instance_id,
|
2022-04-11 16:06:15 -05:00
|
|
|
architecture,
|
2022-04-01 15:45:23 -05:00
|
|
|
environment_variables,
|
2022-04-11 16:06:15 -05:00
|
|
|
operating_system,
|
2022-05-02 10:27:34 -05:00
|
|
|
directory,
|
2022-04-01 15:45:23 -05:00
|
|
|
instance_metadata,
|
2022-11-09 17:27:05 +02:00
|
|
|
resource_metadata,
|
|
|
|
|
connection_timeout_seconds,
|
2022-11-24 14:22:20 +02:00
|
|
|
troubleshooting_url,
|
2023-01-24 14:24:27 +02:00
|
|
|
motd_file,
|
2024-02-15 13:33:13 +01:00
|
|
|
display_apps,
|
2025-05-15 15:32:52 +01:00
|
|
|
display_order,
|
|
|
|
|
api_key_scope
|
2022-04-01 15:45:23 -05:00
|
|
|
)
|
|
|
|
|
VALUES
|
2025-05-15 15:32:52 +01:00
|
|
|
($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20) RETURNING *;
|
2022-04-01 15:45:23 -05:00
|
|
|
|
|
|
|
|
-- name: UpdateWorkspaceAgentConnectionByID :exec
|
|
|
|
|
UPDATE
|
|
|
|
|
workspace_agents
|
|
|
|
|
SET
|
|
|
|
|
first_connected_at = $2,
|
|
|
|
|
last_connected_at = $3,
|
2022-11-06 13:27:09 -08:00
|
|
|
last_connected_replica_id = $4,
|
|
|
|
|
disconnected_at = $5,
|
|
|
|
|
updated_at = $6
|
2022-04-01 15:45:23 -05:00
|
|
|
WHERE
|
|
|
|
|
id = $1;
|
2022-06-24 10:25:01 -05:00
|
|
|
|
2023-02-07 15:35:09 -06:00
|
|
|
-- name: UpdateWorkspaceAgentStartupByID :exec
|
2022-08-31 16:33:50 +01:00
|
|
|
UPDATE
|
|
|
|
|
workspace_agents
|
|
|
|
|
SET
|
2023-02-07 15:35:09 -06:00
|
|
|
version = $2,
|
2023-05-17 22:49:25 -05:00
|
|
|
expanded_directory = $3,
|
2023-10-31 10:08:43 +04:00
|
|
|
subsystems = $4,
|
|
|
|
|
api_version = $5
|
2022-08-31 16:33:50 +01:00
|
|
|
WHERE
|
|
|
|
|
id = $1;
|
2023-01-24 14:24:27 +02:00
|
|
|
|
2023-06-20 14:41:55 +03:00
|
|
|
-- name: GetWorkspaceAgentLifecycleStateByID :one
|
|
|
|
|
SELECT
|
|
|
|
|
lifecycle_state,
|
|
|
|
|
started_at,
|
|
|
|
|
ready_at
|
|
|
|
|
FROM
|
|
|
|
|
workspace_agents
|
|
|
|
|
WHERE
|
|
|
|
|
id = $1;
|
|
|
|
|
|
|
|
|
|
|
2023-01-24 14:24:27 +02:00
|
|
|
-- name: UpdateWorkspaceAgentLifecycleStateByID :exec
|
|
|
|
|
UPDATE
|
|
|
|
|
workspace_agents
|
|
|
|
|
SET
|
2023-06-20 14:41:55 +03:00
|
|
|
lifecycle_state = $2,
|
|
|
|
|
started_at = $3,
|
|
|
|
|
ready_at = $4
|
2023-01-24 14:24:27 +02:00
|
|
|
WHERE
|
|
|
|
|
id = $1;
|
2023-03-23 14:09:13 -05:00
|
|
|
|
2023-03-31 15:26:19 -05:00
|
|
|
-- name: InsertWorkspaceAgentMetadata :exec
|
|
|
|
|
INSERT INTO
|
|
|
|
|
workspace_agent_metadata (
|
|
|
|
|
workspace_agent_id,
|
|
|
|
|
display_name,
|
|
|
|
|
key,
|
|
|
|
|
script,
|
|
|
|
|
timeout,
|
2024-02-08 17:29:34 +01:00
|
|
|
interval,
|
|
|
|
|
display_order
|
2023-03-31 15:26:19 -05:00
|
|
|
)
|
|
|
|
|
VALUES
|
2024-02-08 17:29:34 +01:00
|
|
|
($1, $2, $3, $4, $5, $6, $7);
|
2023-03-31 15:26:19 -05:00
|
|
|
|
|
|
|
|
-- name: UpdateWorkspaceAgentMetadata :exec
|
2023-10-13 16:37:55 +03:00
|
|
|
WITH metadata AS (
|
|
|
|
|
SELECT
|
|
|
|
|
unnest(sqlc.arg('key')::text[]) AS key,
|
|
|
|
|
unnest(sqlc.arg('value')::text[]) AS value,
|
|
|
|
|
unnest(sqlc.arg('error')::text[]) AS error,
|
|
|
|
|
unnest(sqlc.arg('collected_at')::timestamptz[]) AS collected_at
|
|
|
|
|
)
|
2023-03-31 15:26:19 -05:00
|
|
|
UPDATE
|
2023-10-13 16:37:55 +03:00
|
|
|
workspace_agent_metadata wam
|
2023-03-31 15:26:19 -05:00
|
|
|
SET
|
2023-10-13 16:37:55 +03:00
|
|
|
value = m.value,
|
|
|
|
|
error = m.error,
|
|
|
|
|
collected_at = m.collected_at
|
|
|
|
|
FROM
|
|
|
|
|
metadata m
|
2023-03-31 15:26:19 -05:00
|
|
|
WHERE
|
2023-10-13 16:37:55 +03:00
|
|
|
wam.workspace_agent_id = $1
|
|
|
|
|
AND wam.key = m.key;
|
2023-03-31 15:26:19 -05:00
|
|
|
|
2026-01-22 22:47:49 -08:00
|
|
|
-- name: BatchUpdateWorkspaceAgentMetadata :exec
|
|
|
|
|
WITH metadata AS (
|
|
|
|
|
SELECT
|
|
|
|
|
unnest(sqlc.arg('workspace_agent_id')::uuid[]) AS workspace_agent_id,
|
|
|
|
|
unnest(sqlc.arg('key')::text[]) AS key,
|
|
|
|
|
unnest(sqlc.arg('value')::text[]) AS value,
|
|
|
|
|
unnest(sqlc.arg('error')::text[]) AS error,
|
|
|
|
|
unnest(sqlc.arg('collected_at')::timestamptz[]) AS collected_at
|
|
|
|
|
)
|
|
|
|
|
UPDATE
|
|
|
|
|
workspace_agent_metadata wam
|
|
|
|
|
SET
|
|
|
|
|
value = m.value,
|
|
|
|
|
error = m.error,
|
|
|
|
|
collected_at = m.collected_at
|
|
|
|
|
FROM
|
|
|
|
|
metadata m
|
|
|
|
|
WHERE
|
|
|
|
|
wam.workspace_agent_id = m.workspace_agent_id
|
|
|
|
|
AND wam.key = m.key;
|
|
|
|
|
|
2023-03-31 15:26:19 -05:00
|
|
|
-- name: GetWorkspaceAgentMetadata :many
|
|
|
|
|
SELECT
|
|
|
|
|
*
|
|
|
|
|
FROM
|
|
|
|
|
workspace_agent_metadata
|
|
|
|
|
WHERE
|
2023-10-13 16:37:55 +03:00
|
|
|
workspace_agent_id = $1
|
|
|
|
|
AND CASE WHEN COALESCE(array_length(sqlc.arg('keys')::text[], 1), 0) > 0 THEN key = ANY(sqlc.arg('keys')::text[]) ELSE TRUE END;
|
2023-03-31 15:26:19 -05:00
|
|
|
|
2023-07-28 09:57:23 -06:00
|
|
|
-- name: UpdateWorkspaceAgentLogOverflowByID :exec
|
2023-03-23 14:09:13 -05:00
|
|
|
UPDATE
|
|
|
|
|
workspace_agents
|
|
|
|
|
SET
|
2023-07-28 09:57:23 -06:00
|
|
|
logs_overflowed = $2
|
2023-03-23 14:09:13 -05:00
|
|
|
WHERE
|
|
|
|
|
id = $1;
|
|
|
|
|
|
2026-02-04 15:33:48 +00:00
|
|
|
-- name: UpdateWorkspaceAgentDisplayAppsByID :exec
|
|
|
|
|
UPDATE
|
|
|
|
|
workspace_agents
|
|
|
|
|
SET
|
|
|
|
|
display_apps = $2, updated_at = $3
|
|
|
|
|
WHERE
|
|
|
|
|
id = $1;
|
|
|
|
|
|
2026-04-10 10:11:22 -04:00
|
|
|
-- name: UpdateWorkspaceAgentDirectoryByID :exec
|
|
|
|
|
UPDATE
|
|
|
|
|
workspace_agents
|
|
|
|
|
SET
|
|
|
|
|
directory = $2, updated_at = $3
|
|
|
|
|
WHERE
|
|
|
|
|
id = $1;
|
|
|
|
|
|
2023-07-28 09:57:23 -06:00
|
|
|
-- name: GetWorkspaceAgentLogsAfter :many
|
2023-03-23 14:09:13 -05:00
|
|
|
SELECT
|
|
|
|
|
*
|
|
|
|
|
FROM
|
2023-07-28 09:57:23 -06:00
|
|
|
workspace_agent_logs
|
2023-03-23 14:09:13 -05:00
|
|
|
WHERE
|
|
|
|
|
agent_id = $1
|
|
|
|
|
AND (
|
|
|
|
|
id > @created_after
|
|
|
|
|
) ORDER BY id ASC;
|
|
|
|
|
|
2023-07-28 09:57:23 -06:00
|
|
|
-- name: InsertWorkspaceAgentLogs :many
|
2023-03-23 14:09:13 -05:00
|
|
|
WITH new_length AS (
|
|
|
|
|
UPDATE workspace_agents SET
|
2023-07-28 09:57:23 -06:00
|
|
|
logs_length = logs_length + @output_length WHERE workspace_agents.id = @agent_id
|
2023-03-23 14:09:13 -05:00
|
|
|
)
|
|
|
|
|
INSERT INTO
|
2023-09-25 16:47:17 -05:00
|
|
|
workspace_agent_logs (agent_id, created_at, output, level, log_source_id)
|
2023-03-23 14:09:13 -05:00
|
|
|
SELECT
|
|
|
|
|
@agent_id :: uuid AS agent_id,
|
2023-09-25 16:47:17 -05:00
|
|
|
@created_at :: timestamptz AS created_at,
|
2023-04-10 14:29:59 -05:00
|
|
|
unnest(@output :: VARCHAR(1024) [ ]) AS output,
|
2023-07-28 09:57:23 -06:00
|
|
|
unnest(@level :: log_level [ ]) AS level,
|
2023-09-25 16:47:17 -05:00
|
|
|
@log_source_id :: uuid AS log_source_id
|
2023-07-28 09:57:23 -06:00
|
|
|
RETURNING workspace_agent_logs.*;
|
2023-03-23 14:09:13 -05:00
|
|
|
|
2023-09-25 16:47:17 -05:00
|
|
|
-- name: InsertWorkspaceAgentLogSources :many
|
|
|
|
|
INSERT INTO
|
|
|
|
|
workspace_agent_log_sources (workspace_agent_id, created_at, id, display_name, icon)
|
|
|
|
|
SELECT
|
|
|
|
|
@workspace_agent_id :: uuid AS workspace_agent_id,
|
|
|
|
|
@created_at :: timestamptz AS created_at,
|
|
|
|
|
unnest(@id :: uuid [ ]) AS id,
|
|
|
|
|
unnest(@display_name :: VARCHAR(127) [ ]) AS display_name,
|
|
|
|
|
unnest(@icon :: text [ ]) AS icon
|
|
|
|
|
RETURNING workspace_agent_log_sources.*;
|
|
|
|
|
|
|
|
|
|
-- name: GetWorkspaceAgentLogSourcesByAgentIDs :many
|
|
|
|
|
SELECT * FROM workspace_agent_log_sources WHERE workspace_agent_id = ANY(@ids :: uuid [ ]);
|
|
|
|
|
|
2025-12-02 18:01:33 +02:00
|
|
|
-- If an agent hasn't connected within the retention period, we purge its logs.
|
2024-08-30 17:39:09 +01:00
|
|
|
-- Exception: if the logs are related to the latest build, we keep those around.
|
2023-03-23 14:09:13 -05:00
|
|
|
-- Logs can take up a lot of space, so it's important we clean up frequently.
|
2025-12-02 18:01:33 +02:00
|
|
|
-- name: DeleteOldWorkspaceAgentLogs :execrows
|
2024-08-30 17:39:09 +01:00
|
|
|
WITH
|
|
|
|
|
latest_builds AS (
|
|
|
|
|
SELECT
|
|
|
|
|
workspace_id, max(build_number) AS max_build_number
|
|
|
|
|
FROM
|
|
|
|
|
workspace_builds
|
|
|
|
|
GROUP BY
|
|
|
|
|
workspace_id
|
|
|
|
|
),
|
|
|
|
|
old_agents AS (
|
|
|
|
|
SELECT
|
|
|
|
|
wa.id
|
|
|
|
|
FROM
|
|
|
|
|
workspace_agents AS wa
|
|
|
|
|
JOIN
|
|
|
|
|
workspace_resources AS wr
|
|
|
|
|
ON
|
|
|
|
|
wa.resource_id = wr.id
|
|
|
|
|
JOIN
|
|
|
|
|
workspace_builds AS wb
|
|
|
|
|
ON
|
|
|
|
|
wb.job_id = wr.job_id
|
|
|
|
|
LEFT JOIN
|
|
|
|
|
latest_builds
|
|
|
|
|
ON
|
|
|
|
|
latest_builds.workspace_id = wb.workspace_id
|
|
|
|
|
AND
|
|
|
|
|
latest_builds.max_build_number = wb.build_number
|
|
|
|
|
WHERE
|
|
|
|
|
-- Filter out the latest builds for each workspace.
|
|
|
|
|
latest_builds.workspace_id IS NULL
|
|
|
|
|
AND CASE
|
|
|
|
|
-- If the last time the agent connected was before @threshold
|
|
|
|
|
WHEN wa.last_connected_at IS NOT NULL THEN
|
|
|
|
|
wa.last_connected_at < @threshold :: timestamptz
|
|
|
|
|
-- The agent never connected, and was created before @threshold
|
|
|
|
|
ELSE wa.created_at < @threshold :: timestamptz
|
|
|
|
|
END
|
|
|
|
|
)
|
|
|
|
|
DELETE FROM workspace_agent_logs WHERE agent_id IN (SELECT id FROM old_agents);
|
2023-03-31 00:24:51 +11:00
|
|
|
|
|
|
|
|
-- name: GetWorkspaceAgentsInLatestBuildByWorkspaceID :many
|
|
|
|
|
SELECT
|
|
|
|
|
workspace_agents.*
|
|
|
|
|
FROM
|
|
|
|
|
workspace_agents
|
|
|
|
|
JOIN
|
|
|
|
|
workspace_resources ON workspace_agents.resource_id = workspace_resources.id
|
|
|
|
|
JOIN
|
|
|
|
|
workspace_builds ON workspace_resources.job_id = workspace_builds.job_id
|
|
|
|
|
WHERE
|
|
|
|
|
workspace_builds.workspace_id = @workspace_id :: uuid AND
|
|
|
|
|
workspace_builds.build_number = (
|
|
|
|
|
SELECT
|
|
|
|
|
MAX(build_number)
|
|
|
|
|
FROM
|
|
|
|
|
workspace_builds AS wb
|
|
|
|
|
WHERE
|
|
|
|
|
wb.workspace_id = @workspace_id :: uuid
|
2025-06-19 16:32:51 +03:00
|
|
|
)
|
|
|
|
|
-- Filter out deleted sub agents.
|
|
|
|
|
AND workspace_agents.deleted = FALSE;
|
2023-08-21 15:49:26 +01:00
|
|
|
|
2026-07-14 13:50:25 +01:00
|
|
|
-- name: GetWorkspaceAgentsInLatestBuildByWorkspaceIDs :many
|
|
|
|
|
SELECT
|
|
|
|
|
workspace_builds.workspace_id,
|
2026-08-16 20:30:32 +02:00
|
|
|
workspace_builds.id AS build_id,
|
2026-07-14 13:50:25 +01:00
|
|
|
sqlc.embed(workspace_agents)
|
|
|
|
|
FROM
|
|
|
|
|
workspace_agents
|
|
|
|
|
JOIN
|
|
|
|
|
workspace_resources ON workspace_agents.resource_id = workspace_resources.id
|
|
|
|
|
JOIN
|
|
|
|
|
workspace_builds ON workspace_resources.job_id = workspace_builds.job_id
|
|
|
|
|
JOIN (
|
|
|
|
|
SELECT
|
|
|
|
|
workspace_id,
|
|
|
|
|
MAX(build_number) AS build_number
|
|
|
|
|
FROM
|
|
|
|
|
workspace_builds
|
|
|
|
|
WHERE
|
|
|
|
|
workspace_id = ANY(@workspace_ids :: uuid [ ])
|
|
|
|
|
GROUP BY
|
|
|
|
|
workspace_id
|
|
|
|
|
) AS latest_builds ON
|
|
|
|
|
latest_builds.workspace_id = workspace_builds.workspace_id AND
|
|
|
|
|
latest_builds.build_number = workspace_builds.build_number
|
|
|
|
|
WHERE
|
|
|
|
|
workspace_agents.deleted = FALSE;
|
|
|
|
|
|
2025-05-14 14:15:36 +02:00
|
|
|
-- name: GetWorkspaceAgentsByWorkspaceAndBuildNumber :many
|
|
|
|
|
SELECT
|
|
|
|
|
workspace_agents.*
|
|
|
|
|
FROM
|
|
|
|
|
workspace_agents
|
|
|
|
|
JOIN
|
|
|
|
|
workspace_resources ON workspace_agents.resource_id = workspace_resources.id
|
|
|
|
|
JOIN
|
|
|
|
|
workspace_builds ON workspace_resources.job_id = workspace_builds.job_id
|
|
|
|
|
WHERE
|
|
|
|
|
workspace_builds.workspace_id = @workspace_id :: uuid AND
|
2025-06-19 16:32:51 +03:00
|
|
|
workspace_builds.build_number = @build_number :: int
|
|
|
|
|
-- Filter out deleted sub agents.
|
|
|
|
|
AND workspace_agents.deleted = FALSE;
|
2025-05-14 14:15:36 +02:00
|
|
|
|
2026-06-04 15:28:13 -07:00
|
|
|
-- name: GetExternalAgentTokensByTemplateID :many
|
|
|
|
|
-- GetExternalAgentTokensByTemplateID returns the auth tokens for all
|
|
|
|
|
-- non-deleted external agents on the latest build of every running workspace
|
|
|
|
|
-- of the given template. "Running" means the latest build has
|
|
|
|
|
-- transition=start and job_status=succeeded (matches the workspace-status
|
|
|
|
|
-- definition used by coderd/database/queries/workspaces.sql).
|
|
|
|
|
-- An owner_id of '00000000-0000-0000-0000-000000000000' (uuid.Nil) means
|
|
|
|
|
-- "all owners"; any other value restricts results to workspaces owned by
|
|
|
|
|
-- that user.
|
|
|
|
|
SELECT
|
|
|
|
|
workspaces.id AS workspace_id,
|
|
|
|
|
workspaces.name AS workspace_name,
|
|
|
|
|
workspace_agents.id AS agent_id,
|
|
|
|
|
workspace_agents.name AS agent_name,
|
|
|
|
|
workspace_agents.auth_token AS agent_token
|
|
|
|
|
FROM
|
|
|
|
|
workspaces
|
|
|
|
|
JOIN (
|
|
|
|
|
-- latest build per workspace
|
|
|
|
|
SELECT DISTINCT ON (workspace_id)
|
|
|
|
|
id, workspace_id, job_id, transition, has_external_agent
|
|
|
|
|
FROM
|
|
|
|
|
workspace_builds
|
|
|
|
|
ORDER BY
|
|
|
|
|
workspace_id, build_number DESC
|
|
|
|
|
) AS latest_builds
|
|
|
|
|
ON
|
|
|
|
|
latest_builds.workspace_id = workspaces.id
|
|
|
|
|
JOIN
|
|
|
|
|
provisioner_jobs
|
|
|
|
|
ON
|
|
|
|
|
provisioner_jobs.id = latest_builds.job_id
|
|
|
|
|
JOIN
|
|
|
|
|
workspace_resources
|
|
|
|
|
ON
|
|
|
|
|
workspace_resources.job_id = latest_builds.job_id
|
|
|
|
|
JOIN
|
|
|
|
|
workspace_agents
|
|
|
|
|
ON
|
|
|
|
|
workspace_agents.resource_id = workspace_resources.id
|
|
|
|
|
WHERE
|
|
|
|
|
workspaces.template_id = @template_id
|
|
|
|
|
AND (
|
|
|
|
|
@owner_id :: uuid = '00000000-0000-0000-0000-000000000000' :: uuid
|
|
|
|
|
OR workspaces.owner_id = @owner_id
|
|
|
|
|
)
|
|
|
|
|
AND workspaces.deleted = FALSE
|
|
|
|
|
AND latest_builds.has_external_agent = TRUE
|
|
|
|
|
AND latest_builds.transition = 'start' :: workspace_transition
|
|
|
|
|
AND provisioner_jobs.job_status = 'succeeded' :: provisioner_job_status
|
|
|
|
|
AND workspace_agents.deleted = FALSE
|
|
|
|
|
AND workspace_agents.auth_instance_id IS NULL;
|
|
|
|
|
|
2026-01-21 15:18:43 +02:00
|
|
|
-- GetAuthenticatedWorkspaceAgentAndBuildByAuthToken returns an authenticated
|
|
|
|
|
-- workspace agent and its associated build. During normal operation, this is
|
|
|
|
|
-- the latest build. During shutdown, this may be the previous START build while
|
2026-10-19 23:06:41 +03:00
|
|
|
-- the STOP or DELETE build is executing, allowing shutdown scripts and final
|
|
|
|
|
-- connection reports to authenticate (see issue #19467). The agent connection
|
|
|
|
|
-- monitor in coderd/workspaceagentsrpc.go applies the same rule.
|
2026-01-21 15:18:43 +02:00
|
|
|
-- name: GetAuthenticatedWorkspaceAgentAndBuildByAuthToken :one
|
2023-08-21 15:49:26 +01:00
|
|
|
SELECT
|
2024-03-14 12:27:32 -04:00
|
|
|
sqlc.embed(workspaces),
|
2023-08-21 15:49:26 +01:00
|
|
|
sqlc.embed(workspace_agents),
|
2026-09-11 16:24:32 +02:00
|
|
|
sqlc.embed(workspace_build_with_user)
|
2024-03-14 12:27:32 -04:00
|
|
|
FROM
|
2024-03-28 19:38:16 +02:00
|
|
|
workspace_agents
|
|
|
|
|
JOIN
|
|
|
|
|
workspace_resources
|
|
|
|
|
ON
|
|
|
|
|
workspace_agents.resource_id = workspace_resources.id
|
|
|
|
|
JOIN
|
|
|
|
|
workspace_build_with_user
|
|
|
|
|
ON
|
|
|
|
|
workspace_resources.job_id = workspace_build_with_user.job_id
|
|
|
|
|
JOIN
|
|
|
|
|
workspaces
|
|
|
|
|
ON
|
|
|
|
|
workspace_build_with_user.workspace_id = workspaces.id
|
2023-08-21 15:49:26 +01:00
|
|
|
WHERE
|
2024-03-28 19:38:16 +02:00
|
|
|
-- This should only match 1 agent, so 1 returned row or 0.
|
|
|
|
|
workspace_agents.auth_token = @auth_token::uuid
|
|
|
|
|
AND workspaces.deleted = FALSE
|
2025-06-19 16:32:51 +03:00
|
|
|
-- Filter out deleted sub agents.
|
|
|
|
|
AND workspace_agents.deleted = FALSE
|
2026-01-21 15:18:43 +02:00
|
|
|
-- Filter out builds that are not the latest, with exception for shutdown case.
|
|
|
|
|
-- Use CASE for short-circuiting: check normal case first (most common), then shutdown case.
|
|
|
|
|
AND CASE
|
|
|
|
|
-- Normal case: Agent's build is the latest build.
|
|
|
|
|
WHEN workspace_build_with_user.build_number = (
|
|
|
|
|
SELECT
|
|
|
|
|
MAX(build_number)
|
|
|
|
|
FROM
|
|
|
|
|
workspace_builds
|
|
|
|
|
WHERE
|
|
|
|
|
workspace_id = workspace_build_with_user.workspace_id
|
|
|
|
|
) THEN TRUE
|
2026-10-19 23:06:41 +03:00
|
|
|
-- Shutdown case: Agent from previous START build during STOP or DELETE
|
|
|
|
|
-- build execution.
|
2026-01-21 15:18:43 +02:00
|
|
|
WHEN workspace_build_with_user.transition = 'start'
|
|
|
|
|
-- Agent's START build job succeeded.
|
|
|
|
|
AND (SELECT job_status FROM provisioner_jobs WHERE id = workspace_build_with_user.job_id) = 'succeeded'
|
2026-10-19 23:06:41 +03:00
|
|
|
-- Latest build is a STOP or DELETE build whose job is still active,
|
2026-01-21 15:18:43 +02:00
|
|
|
-- and agent's build is immediately previous.
|
|
|
|
|
AND EXISTS (
|
|
|
|
|
SELECT 1
|
|
|
|
|
FROM workspace_builds latest
|
|
|
|
|
JOIN provisioner_jobs pj ON pj.id = latest.job_id
|
|
|
|
|
WHERE latest.workspace_id = workspace_build_with_user.workspace_id
|
|
|
|
|
AND latest.build_number = workspace_build_with_user.build_number + 1
|
|
|
|
|
AND latest.build_number = (
|
|
|
|
|
SELECT MAX(build_number)
|
|
|
|
|
FROM workspace_builds l2
|
|
|
|
|
WHERE l2.workspace_id = latest.workspace_id
|
|
|
|
|
)
|
2026-10-19 23:06:41 +03:00
|
|
|
AND latest.transition IN ('stop', 'delete')
|
2026-01-21 15:18:43 +02:00
|
|
|
AND pj.job_status IN ('pending', 'running')
|
|
|
|
|
) THEN TRUE
|
|
|
|
|
ELSE FALSE
|
|
|
|
|
END
|
2024-03-14 12:27:32 -04:00
|
|
|
;
|
2024-09-24 10:51:49 +01:00
|
|
|
|
2024-10-14 09:31:03 -03:00
|
|
|
-- name: InsertWorkspaceAgentScriptTimings :one
|
2024-09-24 10:51:49 +01:00
|
|
|
INSERT INTO
|
|
|
|
|
workspace_agent_script_timings (
|
|
|
|
|
script_id,
|
|
|
|
|
started_at,
|
|
|
|
|
ended_at,
|
|
|
|
|
exit_code,
|
|
|
|
|
stage,
|
|
|
|
|
status
|
|
|
|
|
)
|
|
|
|
|
VALUES
|
2024-10-14 09:31:03 -03:00
|
|
|
($1, $2, $3, $4, $5, $6)
|
|
|
|
|
RETURNING workspace_agent_script_timings.*;
|
|
|
|
|
|
|
|
|
|
-- name: GetWorkspaceAgentScriptTimingsByBuildID :many
|
2024-11-01 13:29:00 -03:00
|
|
|
SELECT
|
2025-01-21 11:54:43 +00:00
|
|
|
DISTINCT ON (workspace_agent_script_timings.script_id) workspace_agent_script_timings.*,
|
2024-11-01 13:29:00 -03:00
|
|
|
workspace_agent_scripts.display_name,
|
|
|
|
|
workspace_agents.id as workspace_agent_id,
|
|
|
|
|
workspace_agents.name as workspace_agent_name
|
2024-10-14 09:31:03 -03:00
|
|
|
FROM workspace_agent_script_timings
|
|
|
|
|
INNER JOIN workspace_agent_scripts ON workspace_agent_scripts.id = workspace_agent_script_timings.script_id
|
|
|
|
|
INNER JOIN workspace_agents ON workspace_agents.id = workspace_agent_scripts.workspace_agent_id
|
|
|
|
|
INNER JOIN workspace_resources ON workspace_resources.id = workspace_agents.resource_id
|
|
|
|
|
INNER JOIN workspace_builds ON workspace_builds.job_id = workspace_resources.job_id
|
2025-01-21 11:54:43 +00:00
|
|
|
WHERE workspace_builds.id = $1
|
|
|
|
|
ORDER BY workspace_agent_script_timings.script_id, workspace_agent_script_timings.started_at;
|
2025-05-29 12:15:47 +01:00
|
|
|
|
|
|
|
|
-- name: GetWorkspaceAgentsByParentID :many
|
2025-06-19 16:32:51 +03:00
|
|
|
SELECT
|
|
|
|
|
*
|
|
|
|
|
FROM
|
|
|
|
|
workspace_agents
|
|
|
|
|
WHERE
|
|
|
|
|
parent_id = @parent_id::uuid
|
|
|
|
|
AND deleted = FALSE;
|
2025-05-29 12:15:47 +01:00
|
|
|
|
|
|
|
|
-- name: DeleteWorkspaceSubAgentByID :exec
|
2026-06-15 09:38:52 -07:00
|
|
|
-- Soft-deletes a single sub-agent (a child agent such as a devcontainer
|
|
|
|
|
-- agent). Called from the DeleteSubAgent RPC when a sub-agent is torn
|
|
|
|
|
-- down, which can happen mid-build without a full workspace rebuild.
|
|
|
|
|
--
|
|
|
|
|
-- Agent context rows are hard-deleted for the same reason as in
|
|
|
|
|
-- SoftDeletePriorWorkspaceAgents: they only describe live agents, the
|
|
|
|
|
-- rebuild-time soft-delete queries skip already-deleted agents, and
|
|
|
|
|
-- agents are never hard-deleted, so the rows would otherwise orphan
|
|
|
|
|
-- forever.
|
|
|
|
|
WITH soft_deleted_agents AS (
|
|
|
|
|
UPDATE workspace_agents
|
|
|
|
|
SET deleted = TRUE
|
|
|
|
|
WHERE id = @id
|
|
|
|
|
AND parent_id IS NOT NULL
|
|
|
|
|
AND deleted = FALSE
|
|
|
|
|
RETURNING id
|
|
|
|
|
), purged_context_resources AS (
|
|
|
|
|
DELETE FROM workspace_agent_context_resources
|
|
|
|
|
WHERE workspace_agent_id IN (SELECT id FROM soft_deleted_agents)
|
|
|
|
|
)
|
|
|
|
|
DELETE FROM workspace_agent_context_snapshots
|
|
|
|
|
WHERE workspace_agent_id IN (SELECT id FROM soft_deleted_agents);
|
2025-10-17 13:40:10 -07:00
|
|
|
|
|
|
|
|
-- name: GetWorkspaceAgentsForMetrics :many
|
|
|
|
|
SELECT
|
|
|
|
|
w.id as workspace_id,
|
|
|
|
|
w.name as workspace_name,
|
|
|
|
|
u.username as owner_username,
|
|
|
|
|
t.name as template_name,
|
|
|
|
|
tv.name as template_version_name,
|
|
|
|
|
sqlc.embed(workspace_agents)
|
|
|
|
|
FROM workspaces w
|
|
|
|
|
JOIN users u ON w.owner_id = u.id
|
|
|
|
|
JOIN templates t ON w.template_id = t.id
|
|
|
|
|
JOIN workspace_builds wb ON w.id = wb.workspace_id
|
|
|
|
|
LEFT JOIN template_versions tv ON wb.template_version_id = tv.id
|
|
|
|
|
JOIN workspace_resources wr ON wb.job_id = wr.job_id
|
|
|
|
|
JOIN workspace_agents ON wr.id = workspace_agents.resource_id
|
|
|
|
|
WHERE w.deleted = false
|
|
|
|
|
AND wb.build_number = (
|
|
|
|
|
SELECT MAX(wb2.build_number)
|
|
|
|
|
FROM workspace_builds wb2
|
|
|
|
|
WHERE wb2.workspace_id = w.id
|
|
|
|
|
)
|
|
|
|
|
AND workspace_agents.deleted = FALSE;
|
2026-01-19 12:36:33 +00:00
|
|
|
|
|
|
|
|
-- name: GetWorkspaceAgentAndWorkspaceByID :one
|
|
|
|
|
SELECT
|
|
|
|
|
sqlc.embed(workspace_agents),
|
|
|
|
|
sqlc.embed(workspaces),
|
|
|
|
|
users.username as owner_username
|
|
|
|
|
FROM
|
|
|
|
|
workspace_agents
|
|
|
|
|
JOIN
|
|
|
|
|
workspace_resources ON workspace_agents.resource_id = workspace_resources.id
|
|
|
|
|
JOIN
|
|
|
|
|
provisioner_jobs ON workspace_resources.job_id = provisioner_jobs.id
|
|
|
|
|
JOIN
|
|
|
|
|
workspace_builds ON provisioner_jobs.id = workspace_builds.job_id
|
|
|
|
|
JOIN
|
|
|
|
|
workspaces ON workspace_builds.workspace_id = workspaces.id
|
|
|
|
|
JOIN
|
|
|
|
|
users ON workspaces.owner_id = users.id
|
|
|
|
|
WHERE
|
|
|
|
|
workspace_agents.id = @id
|
|
|
|
|
AND workspace_agents.deleted = FALSE
|
|
|
|
|
AND provisioner_jobs.type = 'workspace_build'::provisioner_job_type
|
|
|
|
|
AND workspaces.deleted = FALSE
|
|
|
|
|
AND users.deleted = FALSE
|
|
|
|
|
LIMIT 1;
|
2026-05-18 08:33:29 -04:00
|
|
|
|
|
|
|
|
-- name: SoftDeletePriorWorkspaceAgents :exec
|
|
|
|
|
-- Marks agents from all prior builds of this workspace as deleted,
|
|
|
|
|
-- preserving only agents belonging to @current_build_id. Called from
|
|
|
|
|
-- provisionerdserver when a workspace build completes, after the new
|
|
|
|
|
-- build's agents have been inserted, so running agents are not
|
|
|
|
|
-- deleted while a build is still queued or provisioning.
|
2026-06-15 09:38:52 -07:00
|
|
|
--
|
|
|
|
|
-- Agent context rows (workspace_agent_context_snapshots and
|
|
|
|
|
-- workspace_agent_context_resources) only describe live agents, and
|
|
|
|
|
-- agents are never un-deleted, so they are hard-deleted here instead
|
|
|
|
|
-- of accumulating alongside the soft-deleted agent rows.
|
|
|
|
|
WITH soft_deleted_agents AS (
|
|
|
|
|
UPDATE workspace_agents
|
|
|
|
|
SET deleted = TRUE
|
|
|
|
|
WHERE id IN (
|
|
|
|
|
SELECT wa.id
|
|
|
|
|
FROM workspace_agents wa
|
|
|
|
|
JOIN workspace_resources wr ON wr.id = wa.resource_id
|
|
|
|
|
JOIN workspace_builds wb ON wb.job_id = wr.job_id
|
|
|
|
|
WHERE wb.workspace_id = @workspace_id
|
|
|
|
|
AND wb.id <> @current_build_id
|
|
|
|
|
AND wa.deleted = FALSE
|
|
|
|
|
)
|
|
|
|
|
RETURNING id
|
|
|
|
|
), purged_context_resources AS (
|
|
|
|
|
DELETE FROM workspace_agent_context_resources
|
|
|
|
|
WHERE workspace_agent_id IN (SELECT id FROM soft_deleted_agents)
|
|
|
|
|
)
|
|
|
|
|
DELETE FROM workspace_agent_context_snapshots
|
|
|
|
|
WHERE workspace_agent_id IN (SELECT id FROM soft_deleted_agents);
|
2026-05-18 08:33:29 -04:00
|
|
|
|
|
|
|
|
-- name: SoftDeleteWorkspaceAgentsByWorkspaceID :exec
|
|
|
|
|
-- Marks every non-deleted agent belonging to the given workspace as
|
|
|
|
|
-- deleted. Called alongside UpdateWorkspaceDeletedByID when a workspace
|
|
|
|
|
-- itself is soft-deleted, so the agent instance-identity auth path
|
|
|
|
|
-- (which filters on workspace_agents.deleted) doesn't keep seeing
|
|
|
|
|
-- orphaned rows.
|
2026-06-15 09:38:52 -07:00
|
|
|
--
|
|
|
|
|
-- Agent context rows are hard-deleted for the same reason as in
|
|
|
|
|
-- SoftDeletePriorWorkspaceAgents.
|
|
|
|
|
WITH soft_deleted_agents AS (
|
|
|
|
|
UPDATE workspace_agents
|
|
|
|
|
SET deleted = TRUE
|
|
|
|
|
WHERE id IN (
|
|
|
|
|
SELECT wa.id
|
|
|
|
|
FROM workspace_agents wa
|
|
|
|
|
JOIN workspace_resources wr ON wr.id = wa.resource_id
|
|
|
|
|
JOIN workspace_builds wb ON wb.job_id = wr.job_id
|
|
|
|
|
WHERE wb.workspace_id = @workspace_id
|
|
|
|
|
AND wa.deleted = FALSE
|
|
|
|
|
)
|
|
|
|
|
RETURNING id
|
|
|
|
|
), purged_context_resources AS (
|
|
|
|
|
DELETE FROM workspace_agent_context_resources
|
|
|
|
|
WHERE workspace_agent_id IN (SELECT id FROM soft_deleted_agents)
|
|
|
|
|
)
|
|
|
|
|
DELETE FROM workspace_agent_context_snapshots
|
|
|
|
|
WHERE workspace_agent_id IN (SELECT id FROM soft_deleted_agents);
|