Skip to content

fix(coderd/database): improve task status in tasks_with_status view #20683

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
mafredri merged 4 commits into from
Nov 14, 2025
Merged

fix(coderd/database): improve task status in tasks_with_status view #20683

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
4 commits
Select commit Hold shift + click to select a range
136839b
fix(coderd/database): improve task status in tasks_with_status view
mafredri Nov 13, 2025
ea8080a
add agent lifecycle and app health
mafredri Nov 13, 2025
f432217
make build cancel an error status
mafredri Nov 13, 2025
030fb9e
add tests
mafredri Nov 13, 2025
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
64 changes: 36 additions & 28 deletions coderd/database/dump.sql
View file
Open in desktop

Some generated files are not rendered by default. Learn more about how customized files appear on GitHub.

82 changes: 82 additions & 0 deletions coderd/database/migrations/000397_update_task_status_view.down.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,82 @@
-- Restore previous view.
DROP VIEW IF EXISTS tasks_with_status;

CREATE VIEW
tasks_with_status
AS
SELECT
tasks.*,
CASE
WHEN tasks.workspace_id IS NULL OR latest_build.job_status IS NULL THEN 'pending'::task_status

WHEN latest_build.job_status = 'failed' THEN 'error'::task_status

WHEN latest_build.transition IN ('stop', 'delete')
AND latest_build.job_status = 'succeeded' THEN 'paused'::task_status

WHEN latest_build.transition = 'start'
AND latest_build.job_status = 'pending' THEN 'initializing'::task_status

WHEN latest_build.transition = 'start' AND latest_build.job_status IN ('running', 'succeeded') THEN
CASE
WHEN agent_status.none THEN 'initializing'::task_status
WHEN agent_status.connecting THEN 'initializing'::task_status
WHEN agent_status.connected THEN
CASE
WHEN app_status.any_unhealthy THEN 'error'::task_status
WHEN app_status.any_initializing THEN 'initializing'::task_status
WHEN app_status.all_healthy_or_disabled THEN 'active'::task_status
ELSE 'unknown'::task_status
END
ELSE 'unknown'::task_status
END

ELSE 'unknown'::task_status
END AS status,
task_app.*,
task_owner.*
FROM
tasks
CROSS JOIN LATERAL (
SELECT
vu.username AS owner_username,
vu.name AS owner_name,
vu.avatar_url AS owner_avatar_url
FROM visible_users vu
WHERE vu.id = tasks.owner_id
) task_owner
LEFT JOIN LATERAL (
SELECT workspace_build_number, workspace_agent_id, workspace_app_id
FROM task_workspace_apps task_app
WHERE task_id = tasks.id
ORDER BY workspace_build_number DESC
LIMIT 1
) task_app ON TRUE
LEFT JOIN LATERAL (
SELECT
workspace_build.transition,
provisioner_job.job_status,
workspace_build.job_id
FROM workspace_builds workspace_build
JOIN provisioner_jobs provisioner_job ON provisioner_job.id = workspace_build.job_id
WHERE workspace_build.workspace_id = tasks.workspace_id
AND workspace_build.build_number = task_app.workspace_build_number
) latest_build ON TRUE
CROSS JOIN LATERAL (
SELECT
COUNT(*) = 0 AS none,
bool_or(workspace_agent.lifecycle_state IN ('created', 'starting')) AS connecting,
bool_and(workspace_agent.lifecycle_state = 'ready') AS connected
FROM workspace_agents workspace_agent
WHERE workspace_agent.id = task_app.workspace_agent_id
) agent_status
CROSS JOIN LATERAL (
SELECT
bool_or(workspace_app.health = 'unhealthy') AS any_unhealthy,
bool_or(workspace_app.health = 'initializing') AS any_initializing,
bool_and(workspace_app.health IN ('healthy', 'disabled')) AS all_healthy_or_disabled
FROM workspace_apps workspace_app
WHERE workspace_app.id = task_app.workspace_app_id
) app_status
WHERE
tasks.deleted_at IS NULL;
142 changes: 142 additions & 0 deletions coderd/database/migrations/000397_update_task_status_view.up.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,142 @@
-- Update task status in view.
DROP VIEW IF EXISTS tasks_with_status;

CREATE VIEW
tasks_with_status
AS
SELECT
tasks.*,
-- Combine component statuses with precedence: build -> agent -> app.
CASE
WHEN tasks.workspace_id IS NULL THEN 'pending'::task_status
WHEN build_status.status != 'active' THEN build_status.status::task_status
WHEN agent_status.status != 'active' THEN agent_status.status::task_status
ELSE app_status.status::task_status
END AS status,
-- Attach debug information for troubleshooting status.
jsonb_build_object(
'build', jsonb_build_object(
'transition', latest_build_raw.transition,
'job_status', latest_build_raw.job_status,
'computed', build_status.status
),
'agent', jsonb_build_object(
'lifecycle_state', agent_raw.lifecycle_state,
'computed', agent_status.status
),
'app', jsonb_build_object(
'health', app_raw.health,
'computed', app_status.status
)
) AS status_debug,
task_app.*,
agent_raw.lifecycle_state AS workspace_agent_lifecycle_state,
app_raw.health AS workspace_app_health,
task_owner.*
FROM
tasks
CROSS JOIN LATERAL (
SELECT
vu.username AS owner_username,
vu.name AS owner_name,
vu.avatar_url AS owner_avatar_url
FROM
visible_users vu
WHERE
vu.id = tasks.owner_id
) task_owner
LEFT JOIN LATERAL (
SELECT
task_app.workspace_build_number,
task_app.workspace_agent_id,
task_app.workspace_app_id
FROM
task_workspace_apps task_app
WHERE
task_id = tasks.id
ORDER BY
task_app.workspace_build_number DESC
LIMIT 1
) task_app ON TRUE

-- Join the raw data for computing task status.
LEFT JOIN LATERAL (
SELECT
workspace_build.transition,
provisioner_job.job_status,
workspace_build.job_id
FROM
workspace_builds workspace_build
JOIN
provisioner_jobs provisioner_job
ON provisioner_job.id = workspace_build.job_id
WHERE
workspace_build.workspace_id = tasks.workspace_id
AND workspace_build.build_number = task_app.workspace_build_number
) latest_build_raw ON TRUE
LEFT JOIN LATERAL (
SELECT
workspace_agent.lifecycle_state
FROM
workspace_agents workspace_agent
WHERE
workspace_agent.id = task_app.workspace_agent_id
) agent_raw ON TRUE
LEFT JOIN LATERAL (
SELECT
workspace_app.health
FROM
workspace_apps workspace_app
WHERE
workspace_app.id = task_app.workspace_app_id
) app_raw ON TRUE

-- Compute the status for each component.
CROSS JOIN LATERAL (
SELECT
CASE
WHEN latest_build_raw.job_status IS NULL THEN 'pending'::task_status
WHEN latest_build_raw.job_status IN ('failed', 'canceling', 'canceled') THEN 'error'::task_status
WHEN
latest_build_raw.transition IN ('stop', 'delete')
AND latest_build_raw.job_status = 'succeeded' THEN 'paused'::task_status
WHEN
latest_build_raw.transition = 'start'
AND latest_build_raw.job_status = 'pending' THEN 'initializing'::task_status
-- Build is running or done, defer to agent/app status.
WHEN
latest_build_raw.transition = 'start'
AND latest_build_raw.job_status IN ('running', 'succeeded') THEN 'active'::task_status
ELSE 'unknown'::task_status
END AS status
) build_status
CROSS JOIN LATERAL (
SELECT
CASE
-- No agent or connecting.
WHEN
agent_raw.lifecycle_state IS NULL
OR agent_raw.lifecycle_state IN ('created', 'starting') THEN 'initializing'::task_status
-- Agent is running, defer to app status.
-- NOTE(mafredri): The start_error/start_timeout states means connected, but some startup script failed.
-- This may or may not affect the task status but this has to be caught by app health check.
WHEN agent_raw.lifecycle_state IN ('ready', 'start_timeout', 'start_error') THEN 'active'::task_status
-- If the agent is shutting down or turned off, this is an unknown state because we would expect a stop
-- build to be running.
-- This is essentially equal to: `IN ('shutting_down', 'shutdown_timeout', 'shutdown_error', 'off')`,
-- but we cannot use them because the values were added in a migration.
WHEN agent_raw.lifecycle_state NOT IN ('created', 'starting', 'ready', 'start_timeout', 'start_error') THEN 'unknown'::task_status
ELSE 'unknown'::task_status
END AS status
) agent_status
CROSS JOIN LATERAL (
SELECT
CASE
WHEN app_raw.health = 'initializing' THEN 'initializing'::task_status
WHEN app_raw.health = 'unhealthy' THEN 'error'::task_status
WHEN app_raw.health IN ('healthy', 'disabled') THEN 'active'::task_status
ELSE 'unknown'::task_status
END AS status
) app_status
WHERE
tasks.deleted_at IS NULL;
37 changes: 20 additions & 17 deletions coderd/database/models.go
View file
Open in desktop

Some generated files are not rendered by default. Learn more about how customized files appear on GitHub.

Loading
Loading