Files
coder/coderd/database/queries/workspaceagents.sql
Faur Ioan-AurelandClaude Opus 5.5 1a65385a4b fix: log workspace shutdown reasons for SSH connections (#30515)
Workspace stops and deletes closed active SSH connections with generic
shutdown or process-exit reasons. This obscured the workspace action
that ended a connection and made related logs harder to correlate.

The agent’s SSH tracking and logging now identify connections closed by
the SSH server as server_shutdown, while preserving earlier client or
network disconnects. The agent includes the client session ID when
available for ordinary sessions and connections used only for port
forwarding.

Coderd now enriches shutdown reasons when it processes connection
reports. It translates server_shutdown into workspace_stopped when the
latest build is a running or successful stop or delete requested before
the connection closed. Coderd persists the enriched reason and logs the
build reason, transition, and build ID alongside the client session ID.
Unavailable or unrelated build state retains the generic shutdown
reason.

The CLI now logs shutdown context once per stop or delete build through
its existing workspace watcher. This includes the client session ID when
available and preserves the existing graceful SSH shutdown behavior.

Regarding the PTY connections, when the agent shut down, the
reconnecting PTY server closed each PTY
and its attached connections, and the connection handler then returned
without an error. The server therefore logged and reported those
connections as graceful closes. Its fallback for a forced shutdown
reported a free-form reason that coderd did not recognize, so coderd
never refined either case into a workspace shutdown.

We change the reconnecting PTY server so that it reports any connection
that ends after the agent begins shutting down as a server shutdown
initiated by the agent, and its log keeps the client session ID. The
forced shutdown fallback now reports the same standard reason, which
lets coderd translate it into a workspace stop or delete in the
connection log, as it already does for SSH. A client that closes its
connection before the shutdown still keeps its graceful reason.

Regression tests cover shutdown attribution, fallback behavior, CLI
logging, and PostgreSQL persistence with and without a client session
ID. The test logging helper also synchronizes with cleanup to prevent
logging after a test completes

- resolves
https://linear.app/codercom/issue/CLIENT-665/close-workspace-tunnel-with-a-reason

<!--

If you have used AI to produce some or all of this PR, please ensure you
have read our [AI Contribution
guidelines](https://github.com/coder/coder/blob/main/contributing/AI_CONTRIBUTING.md)
before submitting.

-->

---------

Co-authored-by: Claude Opus 5.5 <noreply@anthropic.com>
2026-10-19 23:06:41 +03:00

670 lines
19 KiB
SQL

-- name: GetWorkspaceAgentByID :one
SELECT
*
FROM
workspace_agents
WHERE
id = $1
-- Filter out deleted sub agents.
AND deleted = FALSE;
-- name: GetWorkspaceAgentsByInstanceID :many
SELECT
*
FROM
workspace_agents
WHERE
auth_instance_id = @auth_instance_id :: TEXT
-- Filter out deleted agents.
AND deleted = FALSE
-- Filter out sub agents, they do not authenticate with auth_instance_id.
AND parent_id IS NULL
ORDER BY
created_at DESC;
-- 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;
-- name: GetWorkspaceAgentsByResourceIDs :many
SELECT
*
FROM
workspace_agents
WHERE
resource_id = ANY(@ids :: uuid [ ])
-- Filter out deleted sub agents.
AND deleted = FALSE;
-- name: GetWorkspaceAgentsCreatedAfter :many
SELECT * FROM workspace_agents
WHERE
created_at > $1
-- Filter out deleted sub agents.
AND deleted = FALSE;
-- name: InsertWorkspaceAgent :one
INSERT INTO
workspace_agents (
id,
parent_id,
created_at,
updated_at,
name,
resource_id,
auth_token,
auth_instance_id,
architecture,
environment_variables,
operating_system,
directory,
instance_metadata,
resource_metadata,
connection_timeout_seconds,
troubleshooting_url,
motd_file,
display_apps,
display_order,
api_key_scope
)
VALUES
($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20) RETURNING *;
-- name: UpdateWorkspaceAgentConnectionByID :exec
UPDATE
workspace_agents
SET
first_connected_at = $2,
last_connected_at = $3,
last_connected_replica_id = $4,
disconnected_at = $5,
updated_at = $6
WHERE
id = $1;
-- name: UpdateWorkspaceAgentStartupByID :exec
UPDATE
workspace_agents
SET
version = $2,
expanded_directory = $3,
subsystems = $4,
api_version = $5
WHERE
id = $1;
-- name: GetWorkspaceAgentLifecycleStateByID :one
SELECT
lifecycle_state,
started_at,
ready_at
FROM
workspace_agents
WHERE
id = $1;
-- name: UpdateWorkspaceAgentLifecycleStateByID :exec
UPDATE
workspace_agents
SET
lifecycle_state = $2,
started_at = $3,
ready_at = $4
WHERE
id = $1;
-- name: InsertWorkspaceAgentMetadata :exec
INSERT INTO
workspace_agent_metadata (
workspace_agent_id,
display_name,
key,
script,
timeout,
interval,
display_order
)
VALUES
($1, $2, $3, $4, $5, $6, $7);
-- name: UpdateWorkspaceAgentMetadata :exec
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
)
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 = $1
AND wam.key = m.key;
-- 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;
-- name: GetWorkspaceAgentMetadata :many
SELECT
*
FROM
workspace_agent_metadata
WHERE
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;
-- name: UpdateWorkspaceAgentLogOverflowByID :exec
UPDATE
workspace_agents
SET
logs_overflowed = $2
WHERE
id = $1;
-- name: UpdateWorkspaceAgentDisplayAppsByID :exec
UPDATE
workspace_agents
SET
display_apps = $2, updated_at = $3
WHERE
id = $1;
-- name: UpdateWorkspaceAgentDirectoryByID :exec
UPDATE
workspace_agents
SET
directory = $2, updated_at = $3
WHERE
id = $1;
-- name: GetWorkspaceAgentLogsAfter :many
SELECT
*
FROM
workspace_agent_logs
WHERE
agent_id = $1
AND (
id > @created_after
) ORDER BY id ASC;
-- name: InsertWorkspaceAgentLogs :many
WITH new_length AS (
UPDATE workspace_agents SET
logs_length = logs_length + @output_length WHERE workspace_agents.id = @agent_id
)
INSERT INTO
workspace_agent_logs (agent_id, created_at, output, level, log_source_id)
SELECT
@agent_id :: uuid AS agent_id,
@created_at :: timestamptz AS created_at,
unnest(@output :: VARCHAR(1024) [ ]) AS output,
unnest(@level :: log_level [ ]) AS level,
@log_source_id :: uuid AS log_source_id
RETURNING workspace_agent_logs.*;
-- 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 [ ]);
-- If an agent hasn't connected within the retention period, we purge its logs.
-- Exception: if the logs are related to the latest build, we keep those around.
-- Logs can take up a lot of space, so it's important we clean up frequently.
-- name: DeleteOldWorkspaceAgentLogs :execrows
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);
-- 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
)
-- Filter out deleted sub agents.
AND workspace_agents.deleted = FALSE;
-- name: GetWorkspaceAgentsInLatestBuildByWorkspaceIDs :many
SELECT
workspace_builds.workspace_id,
workspace_builds.id AS build_id,
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;
-- 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
workspace_builds.build_number = @build_number :: int
-- Filter out deleted sub agents.
AND workspace_agents.deleted = FALSE;
-- 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;
-- 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
-- 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.
-- name: GetAuthenticatedWorkspaceAgentAndBuildByAuthToken :one
SELECT
sqlc.embed(workspaces),
sqlc.embed(workspace_agents),
sqlc.embed(workspace_build_with_user)
FROM
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
WHERE
-- This should only match 1 agent, so 1 returned row or 0.
workspace_agents.auth_token = @auth_token::uuid
AND workspaces.deleted = FALSE
-- Filter out deleted sub agents.
AND workspace_agents.deleted = FALSE
-- 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
-- Shutdown case: Agent from previous START build during STOP or DELETE
-- build execution.
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'
-- Latest build is a STOP or DELETE build whose job is still active,
-- 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
)
AND latest.transition IN ('stop', 'delete')
AND pj.job_status IN ('pending', 'running')
) THEN TRUE
ELSE FALSE
END
;
-- name: InsertWorkspaceAgentScriptTimings :one
INSERT INTO
workspace_agent_script_timings (
script_id,
started_at,
ended_at,
exit_code,
stage,
status
)
VALUES
($1, $2, $3, $4, $5, $6)
RETURNING workspace_agent_script_timings.*;
-- name: GetWorkspaceAgentScriptTimingsByBuildID :many
SELECT
DISTINCT ON (workspace_agent_script_timings.script_id) workspace_agent_script_timings.*,
workspace_agent_scripts.display_name,
workspace_agents.id as workspace_agent_id,
workspace_agents.name as workspace_agent_name
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
WHERE workspace_builds.id = $1
ORDER BY workspace_agent_script_timings.script_id, workspace_agent_script_timings.started_at;
-- name: GetWorkspaceAgentsByParentID :many
SELECT
*
FROM
workspace_agents
WHERE
parent_id = @parent_id::uuid
AND deleted = FALSE;
-- name: DeleteWorkspaceSubAgentByID :exec
-- 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);
-- 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;
-- 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;
-- 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.
--
-- 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);
-- 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.
--
-- 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);