*

workspaces_expanded

id = $1

1;

*

workspaces_expanded as workspaces

workspaces.id = (

SELECT

workspace_id

FROM

workspace_builds

WHERE

workspace_builds.job_id = (

SELECT

job_id

FROM

workspace_resources

WHERE

workspace_resources.id = @resource_id

)

)

1;

*

workspaces_expanded as workspaces

workspaces.id = (

SELECT

workspace_id

FROM

workspace_builds

WHERE

workspace_builds.job_id = (

SELECT

job_id

FROM

workspace_resources

WHERE

workspace_resources.id = (

SELECT

resource_id

FROM

workspace_agents

WHERE

workspace_agents.id = (

SELECT

agent_id

FROM

workspace_apps

WHERE

workspace_apps.id = @workspace_app_id

)

)

)

);

*

workspaces_expanded as workspaces

workspaces.id = (

SELECT

workspace_id

FROM

workspace_builds

WHERE

workspace_builds.job_id = (

SELECT

job_id

FROM

workspace_resources

WHERE

workspace_resources.id = (

SELECT

resource_id

FROM

workspace_agents

WHERE

workspace_agents.id = @agent_id

)

)

);

WITH

build_params AS (

LOWER(unnest(@param_names :: text[])) AS name,

LOWER(unnest(@param_values :: text[])) AS value

),

filtered_workspaces AS (

workspaces.*,

latest_build.template_version_id,

latest_build.template_version_name,

latest_build.completed_at as latest_build_completed_at,

latest_build.canceled_at as latest_build_canceled_at,

latest_build.error as latest_build_error,

latest_build.transition as latest_build_transition,

latest_build.job_status as latest_build_status,

latest_build.has_external_agent as latest_build_has_external_agent

workspaces_expanded as workspaces

users

workspaces.owner_id = users.id

LEFT JOIN LATERAL (

SELECT

workspace_builds.id,

workspace_builds.transition,

workspace_builds.template_version_id,

workspace_builds.has_ai_task,

workspace_builds.has_external_agent,

template_versions.name AS template_version_name,

provisioner_jobs.id AS provisioner_job_id,

provisioner_jobs.started_at,

provisioner_jobs.updated_at,

provisioner_jobs.canceled_at,

provisioner_jobs.completed_at,

provisioner_jobs.error,

provisioner_jobs.job_status

FROM

workspace_builds

JOIN

provisioner_jobs

ON

provisioner_jobs.id = workspace_builds.job_id

LEFT JOIN

template_versions

ON

template_versions.id = workspace_builds.template_version_id

WHERE

workspace_builds.workspace_id = workspaces.id

ORDER BY

build_number DESC

LIMIT

1

) latest_build ON TRUE

LEFT JOIN LATERAL (

SELECT

*

FROM

templates

WHERE

templates.id = workspaces.template_id

) template ON true

-- Optionally include deleted workspaces

workspaces.deleted = @deleted

AND CASE

WHEN @status :: text != '' THEN

CASE

-- Some workspace specific status refer to the transition

-- type. By default, the standard provisioner job status

-- search strings are supported.

-- 'running' states

WHEN @status = 'starting' THEN

latest_build.job_status = 'running'::provisioner_job_status AND

latest_build.transition = 'start'::workspace_transition

WHEN @status = 'stopping' THEN

latest_build.job_status = 'running'::provisioner_job_status AND

latest_build.transition = 'stop'::workspace_transition

WHEN @status = 'deleting' THEN

latest_build.job_status = 'running' AND

latest_build.transition = 'delete'::workspace_transition

-- 'succeeded' states

WHEN @status = 'deleted' THEN

latest_build.job_status = 'succeeded'::provisioner_job_status AND

latest_build.transition = 'delete'::workspace_transition

WHEN @status = 'stopped' THEN

latest_build.job_status = 'succeeded'::provisioner_job_status AND

latest_build.transition = 'stop'::workspace_transition

WHEN @status = 'started' THEN

latest_build.job_status = 'succeeded'::provisioner_job_status AND

latest_build.transition = 'start'::workspace_transition

-- Special case where the provisioner status and workspace status

-- differ. A workspace is "running" if the job is "succeeded" and

-- the transition is "start". This is because a workspace starts

-- running when a job is complete.

WHEN @status = 'running' THEN

latest_build.job_status = 'succeeded'::provisioner_job_status AND

latest_build.transition = 'start'::workspace_transition

WHEN @status != '' THEN

-- By default just match the job status exactly

latest_build.job_status = @status::provisioner_job_status

ELSE

true

END

ELSE true

END

-- Filter by owner_id

AND CASE

WHEN @owner_id :: uuid != '00000000-0000-0000-0000-000000000000'::uuid THEN

workspaces.owner_id = @owner_id

ELSE true

END

-- Filter by organization_id

AND CASE

WHEN @organization_id :: uuid != '00000000-0000-0000-0000-000000000000'::uuid THEN

workspaces.organization_id = @organization_id

ELSE true

END

-- Filter by build parameter

-- @has_param will match any build that includes the parameter.

AND CASE WHEN array_length(@has_param :: text[], 1) > 0 THEN

EXISTS (

SELECT

1

FROM

workspace_build_parameters

WHERE

workspace_build_parameters.workspace_build_id = latest_build.id AND

-- ILIKE is case insensitive

workspace_build_parameters.name ILIKE ANY(@has_param)

)

ELSE true

END

-- @param_value will match param name an value.

-- requires 2 arrays, @param_names and @param_values to be passed in.

-- Array index must match between the 2 arrays for name=value

AND CASE WHEN array_length(@param_names :: text[], 1) > 0 THEN

EXISTS (

SELECT

1

FROM

workspace_build_parameters

INNER JOIN

build_params

ON

LOWER(workspace_build_parameters.name) = build_params.name AND

LOWER(workspace_build_parameters.value) = build_params.value AND

workspace_build_parameters.workspace_build_id = latest_build.id

)

ELSE true

END

-- Filter by owner_name

AND CASE

WHEN @owner_username :: text != '' THEN

workspaces.owner_id = (SELECT id FROM users WHERE lower(users.username) = lower(@owner_username) AND deleted = false)

ELSE true

END

-- Filter by template_name

-- There can be more than 1 template with the same name across organizations.

-- Use the organization filter to restrict to 1 org if needed.

AND CASE

WHEN @template_name :: text != '' THEN

workspaces.template_id = ANY(SELECT id FROM templates WHERE lower(name) = lower(@template_name) AND deleted = false)

ELSE true

END

-- Filter by template_ids

AND CASE

WHEN array_length(@template_ids :: uuid[], 1) > 0 THEN

workspaces.template_id = ANY(@template_ids)

ELSE true

END

-- Filter by workspace_ids

AND CASE

WHEN array_length(@workspace_ids :: uuid[], 1) > 0 THEN

workspaces.id = ANY(@workspace_ids)

ELSE true

END

-- Filter by name, matching on substring

AND CASE

WHEN @name :: text != '' THEN

workspaces.name ILIKE '%' || @name || '%'

ELSE true

END

-- Filter by agent status

-- has-agent: is only applicable for workspaces in "start" transition. Stopped and deleted workspaces don't have agents.

AND CASE

WHEN array_length(@has_agent_statuses :: text[], 1) > 0 THEN

(

SELECT COUNT(*)

FROM

workspace_resources

JOIN

workspace_agents

ON

workspace_agents.resource_id = workspace_resources.id

WHERE

workspace_resources.job_id = latest_build.provisioner_job_id AND

latest_build.transition = 'start'::workspace_transition AND

-- Filter out deleted sub agents.

workspace_agents.deleted = FALSE AND

(

CASE

WHEN workspace_agents.first_connected_at IS NULL THEN

CASE

WHEN workspace_agents.connection_timeout_seconds > 0 AND NOW() - workspace_agents.created_at > workspace_agents.connection_timeout_seconds * INTERVAL '1 second' THEN

'timeout'

ELSE

'connecting'

END

WHEN workspace_agents.disconnected_at > workspace_agents.last_connected_at THEN

'disconnected'

WHEN NOW() - workspace_agents.last_connected_at > INTERVAL '1 second' * @agent_inactive_disconnect_timeout_seconds :: bigint THEN

'disconnected'

WHEN workspace_agents.last_connected_at IS NOT NULL THEN

'connected'

ELSE

NULL

END

) = ANY(@has_agent_statuses :: text[])

) > 0

ELSE true

END

-- Filter by dormant workspaces.

AND CASE

WHEN @dormant :: boolean != 'false' THEN

dormant_at IS NOT NULL

ELSE true

END

-- Filter by last_used

AND CASE

WHEN @last_used_before :: timestamp with time zone > '0001-01-01 00:00:00Z' THEN

workspaces.last_used_at <= @last_used_before

ELSE true

END

AND CASE

WHEN @last_used_after :: timestamp with time zone > '0001-01-01 00:00:00Z' THEN

workspaces.last_used_at >= @last_used_after

ELSE true

END

AND CASE

WHEN sqlc.narg('using_active') :: boolean IS NOT NULL THEN

(latest_build.template_version_id = template.active_version_id) = sqlc.narg('using_active') :: boolean

ELSE true

END

-- Filter by has_ai_task, checks if this is a task workspace.

AND CASE

WHEN sqlc.narg('has_ai_task')::boolean IS NOT NULL

THEN sqlc.narg('has_ai_task')::boolean = EXISTS (

SELECT

1

FROM

tasks

WHERE

-- Consider all tasks, deleting a task does not turn the

-- workspace into a non-task workspace.

tasks.workspace_id = workspaces.id

)

ELSE true

END

-- Filter by has_external_agent in latest build

AND CASE

WHEN sqlc.narg('has_external_agent') :: boolean IS NOT NULL THEN

latest_build.has_external_agent = sqlc.narg('has_external_agent') :: boolean

ELSE true

END

-- Filter by shared status

AND CASE

WHEN sqlc.narg('shared') :: boolean IS NOT NULL THEN

(workspaces.user_acl != '{}'::jsonb OR workspaces.group_acl != '{}'::jsonb) = sqlc.narg('shared') :: boolean

ELSE true

END

-- Filter by shared_with_user_id

AND CASE

WHEN @shared_with_user_id :: uuid != '00000000-0000-0000-0000-000000000000'::uuid THEN

workspaces.user_acl ? (@shared_with_user_id :: uuid) :: text

ELSE true

END

-- Filter by shared_with_group_id

AND CASE

WHEN @shared_with_group_id :: uuid != '00000000-0000-0000-0000-000000000000'::uuid THEN

workspaces.group_acl ? (@shared_with_group_id :: uuid) :: text

ELSE true

END

-- Authorize Filter clause will be injected below in GetAuthorizedWorkspaces

-- @authorize_filter

), filtered_workspaces_order AS (

SELECT

fw.*

FROM

filtered_workspaces fw

ORDER BY

-- To ensure that 'favorite' workspaces show up first in the list only for their owner.

CASE WHEN favorite AND owner_username = (SELECT users.username FROM users WHERE users.id = @requester_id) THEN 0 ELSE 1 END ASC,

(latest_build_completed_at IS NOT NULL AND

latest_build_canceled_at IS NULL AND

latest_build_error IS NULL AND

latest_build_transition = 'start'::workspace_transition) DESC,

LOWER(owner_username) ASC,

LOWER(name) ASC

LIMIT

CASE

WHEN @limit_ :: integer > 0 THEN

@limit_

END

OFFSET

@offset_

), filtered_workspaces_order_with_summary AS (

SELECT

fwo.*

FROM

filtered_workspaces_order fwo

-- Return a technical summary row with total count of workspaces.

-- It is used to present the correct count if pagination goes beyond the offset.

UNION ALL

SELECT

'00000000-0000-0000-0000-000000000000'::uuid, -- id

'0001-01-01 00:00:00+00'::timestamptz, -- created_at

'0001-01-01 00:00:00+00'::timestamptz, -- updated_at

'00000000-0000-0000-0000-000000000000'::uuid, -- owner_id

'00000000-0000-0000-0000-000000000000'::uuid, -- organization_id

'00000000-0000-0000-0000-000000000000'::uuid, -- template_id

false, -- deleted

'**TECHNICAL_ROW**', -- name

'', -- autostart_schedule

0, -- ttl

'0001-01-01 00:00:00+00'::timestamptz, -- last_used_at

'0001-01-01 00:00:00+00'::timestamptz, -- dormant_at

'0001-01-01 00:00:00+00'::timestamptz, -- deleting_at

'never'::automatic_updates, -- automatic_updates

false, -- favorite

'0001-01-01 00:00:00+00'::timestamptz, -- next_start_at

'{}'::jsonb, -- group_acl

'{}'::jsonb, -- user_acl

'', -- owner_avatar_url

'', -- owner_username

'', -- owner_name

'', -- organization_name

'', -- organization_display_name

'', -- organization_icon

'', -- organization_description

'', -- template_name

'', -- template_display_name

'', -- template_icon

'', -- template_description

'00000000-0000-0000-0000-000000000000'::uuid, -- task_id

'{}'::jsonb, -- group_acl_display_info

'{}'::jsonb, -- user_acl_display_info

-- Extra columns added to `filtered_workspaces`

'00000000-0000-0000-0000-000000000000'::uuid, -- template_version_id

'', -- template_version_name

'0001-01-01 00:00:00+00'::timestamptz, -- latest_build_completed_at,

'0001-01-01 00:00:00+00'::timestamptz, -- latest_build_canceled_at,

'', -- latest_build_error

'start'::workspace_transition, -- latest_build_transition

'unknown'::provisioner_job_status, -- latest_build_status

false -- latest_build_has_external_agent

WHERE

@with_summary :: boolean = true

), total_count AS (

SELECT

count(*) AS count

FROM

filtered_workspaces

)

fwos.*,

tc.count

filtered_workspaces_order_with_summary fwos

total_count tc;

*

workspaces_expanded as workspaces

owner_id = @owner_id

AND deleted = @deleted

AND LOWER("name") = LOWER(@name)

ORDER BY created_at DESC;

SELECT templates.id AS template_id, COUNT(DISTINCT workspaces.owner_id) AS unique_owners_sum

FROM templates

LEFT JOIN workspaces ON workspaces.template_id = templates.id AND workspaces.deleted = false

WHERE templates.id = ANY(@template_ids :: uuid[])

GROUP BY templates.id;

workspaces (

id,

created_at,

updated_at,

owner_id,

organization_id,

template_id,

name,

autostart_schedule,

ttl,

last_used_at,

automatic_updates,

next_start_at

)

($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12) RETURNING *;

workspaces

deleted = $2

id = $1;

workspaces

name = $2

id = $1

AND deleted = false

RETURNING *;

workspaces

autostart_schedule = $2,

next_start_at = $3

id = $1

-- Prebuilt workspaces (identified by having the prebuilds system user as owner_id)

-- are managed by the reconciliation loop, not the lifecycle executor which handles

-- autostart_schedule and next_start_at

AND owner_id != 'c42fdf75-3097-471c-8c33-fb52454d81c0'::UUID;

workspaces

next_start_at = $2

id = $1

-- Prebuilt workspaces (identified by having the prebuilds system user as owner_id)

-- are managed by the reconciliation loop, not the lifecycle executor which handles

-- next_start_at

AND owner_id != 'c42fdf75-3097-471c-8c33-fb52454d81c0'::UUID;

workspaces

next_start_at = CASE

WHEN batch.next_start_at = '0001-01-01 00:00:00+00'::timestamptz THEN NULL

ELSE batch.next_start_at

END

FROM (

SELECT

unnest(sqlc.arg(ids)::uuid[]) AS id,

unnest(sqlc.arg(next_start_ats)::timestamptz[]) AS next_start_at

) AS batch

workspaces.id = batch.id;

workspaces

ttl = $2

id = $1

-- Prebuilt workspaces (identified by having the prebuilds system user as owner_id)

-- are managed by the reconciliation loop, not the lifecycle executor which handles

-- ttl

AND owner_id != 'c42fdf75-3097-471c-8c33-fb52454d81c0'::UUID;

workspaces

ttl = $2

template_id = $1

-- Prebuilt workspaces (identified by having the prebuilds system user as owner_id)

-- should not have their TTL updated, as they are handled by the prebuilds

-- reconciliation loop.

AND workspaces.owner_id != 'c42fdf75-3097-471c-8c33-fb52454d81c0'::UUID;

workspaces

last_used_at = $2

id = $1;

workspaces

last_used_at = @last_used_at

id = ANY(@ids :: uuid[])

-- Do not overwrite with older data

last_used_at < @last_used_at;

WITH workspaces_with_jobs AS (

SELECT

latest_build.* FROM workspaces

LEFT JOIN LATERAL (

SELECT

workspace_builds.transition,

provisioner_jobs.id AS provisioner_job_id,

provisioner_jobs.started_at,

provisioner_jobs.updated_at,

provisioner_jobs.canceled_at,

provisioner_jobs.completed_at,

provisioner_jobs.error

FROM

workspace_builds

LEFT JOIN

provisioner_jobs

ON

provisioner_jobs.id = workspace_builds.job_id

WHERE

workspace_builds.workspace_id = workspaces.id

ORDER BY

build_number DESC

LIMIT

1

) latest_build ON TRUE WHERE deleted = false

), pending_workspaces AS (

SELECT COUNT(*) AS count FROM workspaces_with_jobs WHERE

started_at IS NULL

), building_workspaces AS (

SELECT COUNT(*) AS count FROM workspaces_with_jobs WHERE

started_at IS NOT NULL AND

canceled_at IS NULL AND

completed_at IS NULL AND

updated_at - INTERVAL '30 seconds' < NOW()

), running_workspaces AS (

SELECT COUNT(*) AS count FROM workspaces_with_jobs WHERE

completed_at IS NOT NULL AND

canceled_at IS NULL AND

error IS NULL AND

transition = 'start'::workspace_transition

), failed_workspaces AS (

SELECT COUNT(*) AS count FROM workspaces_with_jobs WHERE

(canceled_at IS NOT NULL AND

error IS NOT NULL) OR

(completed_at IS NOT NULL AND

error IS NOT NULL)

), stopped_workspaces AS (

SELECT COUNT(*) AS count FROM workspaces_with_jobs WHERE

completed_at IS NOT NULL AND

canceled_at IS NULL AND

error IS NULL AND

transition = 'stop'::workspace_transition

)

pending_workspaces.count AS pending_workspaces,

building_workspaces.count AS building_workspaces,

running_workspaces.count AS running_workspaces,

failed_workspaces.count AS failed_workspaces,

stopped_workspaces.count AS stopped_workspaces

FROM pending_workspaces, building_workspaces, running_workspaces, failed_workspaces, stopped_workspaces;

workspaces.id,

workspaces.name,

workspace_builds.template_version_id as build_template_version_id

workspaces

workspace_builds ON workspace_builds.workspace_id = workspaces.id

provisioner_jobs ON workspace_builds.job_id = provisioner_jobs.id

templates ON workspaces.template_id = templates.id

users ON workspaces.owner_id = users.id

workspace_builds.build_number = (

SELECT

MAX(build_number)

FROM

workspace_builds

WHERE

workspace_builds.workspace_id = workspaces.id

) AND

(

-- A workspace may be eligible for autostop if the following are true:

-- * The provisioner job has not failed.

-- * The workspace is not dormant.

-- * The workspace build was a start transition.

-- * The workspace's owner is suspended OR the workspace build deadline has passed.

(

provisioner_jobs.job_status != 'failed'::provisioner_job_status AND

workspaces.dormant_at IS NULL AND

workspace_builds.transition = 'start'::workspace_transition AND (

users.status = 'suspended'::user_status OR (

workspace_builds.deadline != '0001-01-01 00:00:00+00'::timestamptz AND

workspace_builds.deadline < @now :: timestamptz

)

)

) OR

-- A workspace may be eligible for autostart if the following are true:

-- * The workspace's owner is active.

-- * The provisioner job did not fail.

-- * The workspace build was a stop transition.

-- * The workspace is not dormant

-- * The workspace has an autostart schedule.

-- * It is after the workspace's next start time.

(

users.status = 'active'::user_status AND

provisioner_jobs.job_status != 'failed'::provisioner_job_status AND

workspace_builds.transition = 'stop'::workspace_transition AND

workspaces.dormant_at IS NULL AND

workspaces.autostart_schedule IS NOT NULL AND

(

-- next_start_at might be null in these two scenarios:

-- * A coder instance was updated and we haven't updated next_start_at yet.

-- * A database trigger made it null because of an update to a related column.

--

-- When this occurs, we return the workspace so the Coder server can

-- compute a valid next start at and update it.

workspaces.next_start_at IS NULL OR

workspaces.next_start_at <= @now :: timestamptz

)

) OR

-- A workspace may be eligible for dormant stop if the following are true:

-- * The workspace is not dormant.

-- * The template has set a time 'til dormant.

-- * The workspace has been unused for longer than the time 'til dormancy.

(

workspaces.dormant_at IS NULL AND

templates.time_til_dormant > 0 AND

(@now :: timestamptz) - workspaces.last_used_at > (INTERVAL '1 millisecond' * (templates.time_til_dormant / 1000000))

) OR

-- A workspace may be eligible for deletion if the following are true:

-- * The workspace is dormant.

-- * The workspace is scheduled to be deleted.

-- * If there was a prior attempt to delete the workspace that failed:

-- * This attempt was at least 24 hours ago.

(

workspaces.dormant_at IS NOT NULL AND

workspaces.deleting_at IS NOT NULL AND

workspaces.deleting_at < @now :: timestamptz AND

templates.time_til_dormant_autodelete > 0 AND

CASE

WHEN (

workspace_builds.transition = 'delete'::workspace_transition AND

provisioner_jobs.job_status = 'failed'::provisioner_job_status

) THEN (

(

provisioner_jobs.canceled_at IS NOT NULL OR

provisioner_jobs.completed_at IS NOT NULL

) AND (

(@now :: timestamptz) - (CASE

WHEN provisioner_jobs.canceled_at IS NOT NULL THEN provisioner_jobs.canceled_at

ELSE provisioner_jobs.completed_at

END) > INTERVAL '24 hours'

)

)

ELSE true

END

) OR

-- A workspace may be eligible for failed stop if the following are true:

-- * The template has a failure ttl set.

-- * The workspace build was a start transition.

-- * The provisioner job failed.

-- * The provisioner job had completed.

-- * The provisioner job has been completed for longer than the failure ttl.

(

templates.failure_ttl > 0 AND

workspace_builds.transition = 'start'::workspace_transition AND

provisioner_jobs.job_status = 'failed'::provisioner_job_status AND

provisioner_jobs.completed_at IS NOT NULL AND

(@now :: timestamptz) - provisioner_jobs.completed_at > (INTERVAL '1 millisecond' * (templates.failure_ttl / 1000000))

)

)

AND workspaces.deleted = 'false'

-- Prebuilt workspaces (identified by having the prebuilds system user as owner_id)

-- should not be considered by the lifecycle executor, as they are handled by the

-- prebuilds reconciliation loop.

AND workspaces.owner_id != 'c42fdf75-3097-471c-8c33-fb52454d81c0'::UUID;

workspaces

dormant_at = $2,

-- When a workspace is active we want to update the last_used_at to avoid the workspace going

-- immediately dormant. If we're transition the workspace to dormant then we leave it alone.

last_used_at = CASE WHEN $2::timestamptz IS NULL THEN

now() at time zone 'utc'

ELSE

last_used_at

END,

-- If dormant_at is null (meaning active) or the template-defined time_til_dormant_autodelete is 0 we should set

-- deleting_at to NULL else set it to the dormant_at + time_til_dormant_autodelete duration.

deleting_at = CASE WHEN $2::timestamptz IS NULL OR templates.time_til_dormant_autodelete = 0 THEN

NULL

ELSE

$2::timestamptz + (INTERVAL '1 millisecond' * (templates.time_til_dormant_autodelete / 1000000))

END

templates

workspaces.id = $1

AND templates.id = workspaces.template_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

-- dormant_at and deleting_at

AND owner_id != 'c42fdf75-3097-471c-8c33-fb52454d81c0'::UUID

RETURNING

workspaces.*;

UPDATE workspaces

deleting_at = CASE

WHEN @time_til_dormant_autodelete_ms::bigint = 0 THEN NULL

WHEN @dormant_at::timestamptz > '0001-01-01 00:00:00+00'::timestamptz THEN (@dormant_at::timestamptz) + interval '1 milliseconds' * @time_til_dormant_autodelete_ms::bigint

ELSE dormant_at + interval '1 milliseconds' * @time_til_dormant_autodelete_ms::bigint

END,

dormant_at = CASE WHEN @dormant_at::timestamptz > '0001-01-01 00:00:00+00'::timestamptz THEN @dormant_at::timestamptz ELSE dormant_at END

template_id = @template_id

AND dormant_at IS NOT NULL

AND deleted = false

-- Prebuilt workspaces (identified by having the prebuilds system user as owner_id)

-- should not have their dormant or deleting at set, as these are handled by the

-- prebuilds reconciliation loop.

AND workspaces.owner_id != 'c42fdf75-3097-471c-8c33-fb52454d81c0'::UUID

RETURNING *;

UPDATE workspaces

last_used_at = @last_used_at::timestamptz

template_id = @template_id;

workspaces

automatic_updates = $2

id = $1;

UPDATE workspaces SET favorite = true WHERE id = @id;

UPDATE workspaces SET favorite = false WHERE id = @id;

workspaces.id as id,

workspaces.name as name,

job_status,

transition,

(array_agg(ROW(agent_id, agent_name)::agent_id_name_pair) FILTER (WHERE agent_id IS NOT NULL))::agent_id_name_pair[] as agents

FROM workspaces

LEFT JOIN LATERAL (

SELECT

workspace_id,

job_id,

transition,

job_status

FROM workspace_builds

JOIN provisioner_jobs ON provisioner_jobs.id = workspace_builds.job_id

WHERE workspace_builds.workspace_id = workspaces.id

ORDER BY build_number DESC

LIMIT 1

) latest_build ON true

LEFT JOIN LATERAL (

SELECT

workspace_agents.id as agent_id,

workspace_agents.name as agent_name,

job_id

FROM workspace_resources

JOIN workspace_agents ON (

workspace_agents.resource_id = workspace_resources.id

-- Filter out deleted sub agents.

AND workspace_agents.deleted = FALSE

)

WHERE job_id = latest_build.job_id

) resources ON true

-- Filter by owner_id

workspaces.owner_id = @owner_id :: uuid

AND workspaces.deleted = false

-- Authorize Filter clause will be injected below in GetAuthorizedWorkspacesAndAgentsByOwnerID

-- @authorize_filter

GROUP BY workspaces.id, workspaces.name, latest_build.job_status, latest_build.job_id, latest_build.transition;

SELECT * FROM workspaces WHERE template_id = $1 AND deleted = false;

group_acl as groups,

user_acl as users

workspaces

id = @id;

workspaces

group_acl = @group_acl,

user_acl = @user_acl

id = @id;

workspaces

group_acl = '{}'::json,

user_acl = '{}'::json

id = @id;

workspaces

group_acl = '{}'::jsonb,

user_acl = '{}'::jsonb

organization_id = @organization_id

AND (

NOT @exclude_service_accounts::boolean

OR owner_id NOT IN (

SELECT id FROM users WHERE is_service_account = true

)

);

WITH first_success_build AS (

-- Earliest successful 'start' build per workspace

SELECT DISTINCT ON (wb.workspace_id)

wb.workspace_id,

wb.template_version_preset_id,

wb.initiator_id

FROM workspace_builds wb

JOIN provisioner_jobs pj ON pj.id = wb.job_id

WHERE

wb.transition = 'start'::workspace_transition

AND pj.job_status = 'succeeded'::provisioner_job_status

ORDER BY wb.workspace_id, wb.build_number, wb.id

)

t.name AS template_name,

COALESCE(tvp.name, '') AS preset_name,

o.name AS organization_name,

COUNT(*) AS created_count

FROM first_success_build fsb

JOIN workspaces w ON w.id = fsb.workspace_id

JOIN templates t ON t.id = w.template_id

LEFT JOIN template_version_presets tvp ON tvp.id = fsb.template_version_preset_id

JOIN organizations o ON o.id = w.organization_id

NOT t.deleted

-- Exclude workspaces whose first successful start was the prebuilds system user

AND fsb.initiator_id != 'c42fdf75-3097-471c-8c33-fb52454d81c0'::uuid

GROUP BY t.name, COALESCE(tvp.name, ''), o.name

ORDER BY t.name, preset_name, o.name;