coder/coderd/database/queries/provisionerdaemons.sql

-- name: GetProvisionerDaemons :many
SELECT
	*
FROM
	provisioner_daemons;

-- name: GetProvisionerDaemonsByOrganization :many
SELECT
	*
FROM
	provisioner_daemons
WHERE
	-- This is the original search criteria:
	organization_id = @organization_id :: uuid
	AND
	-- adding support for searching by tags:
	(@want_tags :: tagset = 'null' :: tagset OR provisioner_tagset_contains(provisioner_daemons.tags::tagset, @want_tags::tagset));

-- name: GetEligibleProvisionerDaemonsByProvisionerJobIDs :many
SELECT DISTINCT
    provisioner_jobs.id as job_id, sqlc.embed(provisioner_daemons)
FROM
    provisioner_jobs
JOIN
    provisioner_daemons ON provisioner_daemons.organization_id = provisioner_jobs.organization_id
    AND provisioner_tagset_contains(provisioner_daemons.tags::tagset, provisioner_jobs.tags::tagset)
    AND provisioner_jobs.provisioner = ANY(provisioner_daemons.provisioners)
WHERE
    provisioner_jobs.id = ANY(@provisioner_job_ids :: uuid[]);

-- name: GetProvisionerDaemonsWithStatusByOrganization :many
SELECT
	sqlc.embed(pd),
	CASE
		WHEN current_job.id IS NOT NULL THEN 'busy'::provisioner_daemon_status
		WHEN (COALESCE(sqlc.narg('offline')::bool, false) = true
		      OR 'offline'::provisioner_daemon_status = ANY(@statuses::provisioner_daemon_status[]))
		     AND (pd.last_seen_at IS NULL OR pd.last_seen_at < (NOW() - (@stale_interval_ms::bigint || ' ms')::interval))
		THEN 'offline'::provisioner_daemon_status
		ELSE 'idle'::provisioner_daemon_status
	END AS status,
	pk.name AS key_name,
	-- NOTE(mafredri): sqlc.embed doesn't support nullable tables nor renaming them.
	current_job.id AS current_job_id,
	current_job.job_status AS current_job_status,
	previous_job.id AS previous_job_id,
	previous_job.job_status AS previous_job_status,
	COALESCE(current_template.name, ''::text) AS current_job_template_name,
	COALESCE(current_template.display_name, ''::text) AS current_job_template_display_name,
	COALESCE(current_template.icon, ''::text) AS current_job_template_icon,
	COALESCE(previous_template.name, ''::text) AS previous_job_template_name,
	COALESCE(previous_template.display_name, ''::text) AS previous_job_template_display_name,
	COALESCE(previous_template.icon, ''::text) AS previous_job_template_icon
FROM
	provisioner_daemons pd
JOIN
	provisioner_keys pk ON pk.id = pd.key_id
LEFT JOIN
	provisioner_jobs current_job ON (
		current_job.worker_id = pd.id
		AND current_job.organization_id = pd.organization_id
		AND current_job.completed_at IS NULL
	)
LEFT JOIN
	provisioner_jobs previous_job ON (
		previous_job.id = (
			SELECT
				id
			FROM
				provisioner_jobs
			WHERE
				worker_id = pd.id
				AND organization_id = pd.organization_id
				AND completed_at IS NOT NULL
			ORDER BY
				completed_at DESC
			LIMIT 1
		)
		AND previous_job.organization_id = pd.organization_id
	)
-- Current job information.
LEFT JOIN
	workspace_builds current_build ON current_build.id = CASE WHEN current_job.input ? 'workspace_build_id' THEN (current_job.input->>'workspace_build_id')::uuid END
LEFT JOIN
	-- We should always have a template version, either explicitly or implicitly via workspace build.
	template_versions current_version ON (
		current_version.id = CASE WHEN current_job.input ? 'template_version_id' THEN (current_job.input->>'template_version_id')::uuid ELSE current_build.template_version_id END
		AND current_version.organization_id = pd.organization_id
	)
LEFT JOIN
	templates current_template ON (
		current_template.id = current_version.template_id
		AND current_template.organization_id = pd.organization_id
	)
-- Previous job information.
LEFT JOIN
	workspace_builds previous_build ON previous_build.id = CASE WHEN previous_job.input ? 'workspace_build_id' THEN (previous_job.input->>'workspace_build_id')::uuid END
LEFT JOIN
	-- We should always have a template version, either explicitly or implicitly via workspace build.
	template_versions previous_version ON (
		previous_version.id = CASE WHEN previous_job.input ? 'template_version_id' THEN (previous_job.input->>'template_version_id')::uuid ELSE previous_build.template_version_id END
		AND previous_version.organization_id = pd.organization_id
	)
LEFT JOIN
	templates previous_template ON (
		previous_template.id = previous_version.template_id
		AND previous_template.organization_id = pd.organization_id
	)
WHERE
	pd.organization_id = @organization_id::uuid
	AND (COALESCE(array_length(@ids::uuid[], 1), 0) = 0 OR pd.id = ANY(@ids::uuid[]))
	AND (@tags::tagset = 'null'::tagset OR provisioner_tagset_contains(pd.tags::tagset, @tags::tagset))
	-- Filter by max age if provided
	AND (
		sqlc.narg('max_age_ms')::bigint IS NULL
		OR pd.last_seen_at IS NULL
		OR pd.last_seen_at >= (NOW() - (sqlc.narg('max_age_ms')::bigint || ' ms')::interval)
	)
	AND (
		-- Always include online daemons
		(pd.last_seen_at IS NOT NULL AND pd.last_seen_at >= (NOW() - (@stale_interval_ms::bigint || ' ms')::interval))
		-- Include offline daemons if offline param is true or 'offline' status is requested
		OR (
			(pd.last_seen_at IS NULL OR pd.last_seen_at < (NOW() - (@stale_interval_ms::bigint || ' ms')::interval))
			AND (
				COALESCE(sqlc.narg('offline')::bool, false) = true
				OR 'offline'::provisioner_daemon_status = ANY(@statuses::provisioner_daemon_status[])
			)
		)
	)
	AND (
		-- Filter daemons by any statuses if provided
		COALESCE(array_length(@statuses::provisioner_daemon_status[], 1), 0) = 0
		OR (current_job.id IS NOT NULL AND 'busy'::provisioner_daemon_status = ANY(@statuses::provisioner_daemon_status[]))
		OR (current_job.id IS NULL AND 'idle'::provisioner_daemon_status = ANY(@statuses::provisioner_daemon_status[]))
		OR (
		    'offline'::provisioner_daemon_status = ANY(@statuses::provisioner_daemon_status[])
		    AND (pd.last_seen_at IS NULL OR pd.last_seen_at < (NOW() - (@stale_interval_ms::bigint || ' ms')::interval))
		)
		OR (
		    COALESCE(sqlc.narg('offline')::bool, false) = true
		    AND (pd.last_seen_at IS NULL OR pd.last_seen_at < (NOW() - (@stale_interval_ms::bigint || ' ms')::interval))
		)
	)
ORDER BY
	pd.created_at DESC
LIMIT
	sqlc.narg('limit')::int;

-- name: DeleteOldProvisionerDaemons :exec
-- Delete provisioner daemons that have been created at least a week ago
-- and have not connected to coderd since a week.
-- A provisioner daemon with "zeroed" last_seen_at column indicates possible
-- connectivity issues (no provisioner daemon activity since registration).
DELETE FROM provisioner_daemons WHERE (
	(created_at < (NOW() - INTERVAL '7 days') AND last_seen_at IS NULL) OR
	(last_seen_at IS NOT NULL AND last_seen_at < (NOW() - INTERVAL '7 days'))
);

-- name: UpsertProvisionerDaemon :one
INSERT INTO
	provisioner_daemons (
		id,
		created_at,
		"name",
		provisioners,
		tags,
		last_seen_at,
		"version",
		organization_id,
		api_version,
		key_id
	)
VALUES (
	gen_random_uuid(),
	@created_at,
	@name,
	@provisioners,
	@tags,
	@last_seen_at,
	@version,
	@organization_id,
	@api_version,
	@key_id
) ON CONFLICT("organization_id", "name", LOWER(COALESCE(tags ->> 'owner'::text, ''::text))) DO UPDATE SET
	provisioners = @provisioners,
	tags = @tags,
	last_seen_at = @last_seen_at,
	"version" = @version,
	api_version = @api_version,
	organization_id = @organization_id,
	key_id = @key_id
RETURNING *;

-- name: UpdateProvisionerDaemonLastSeenAt :exec
UPDATE provisioner_daemons
SET
	last_seen_at = @last_seen_at
WHERE
	id = @id
AND
	last_seen_at <= @last_seen_at;