Count `coder_script_order` declarations and literal `rule` blocks in coderd from the existing START plan in `CompletedJob.TemplateImport.Plan`, including child modules, without multiplying counts by resource or module instances. Persist these counts per template version on successful import after START plan graph validation, without changing the provisioner API. Report a deployment-wide `script_order_summary` with the number of templates using script ordering and total declaration and rule counts. Include only active, successfully imported versions of non-deleted templates so repeated imports do not inflate adoption. Elect one reporting replica per snapshot period and report explicit zeros when no templates use ordering. The summary contains only aggregate counts, without template identifiers or rule contents. Dynamic rule blocks are not counted because Terraform omits them from configuration JSON. Configuration-decoding failures are logged and skip updating the counts without independently failing the import. Script-order summary collection failures are logged and omit that summary while allowing the rest of the snapshot to proceed. Refs: https://linear.app/codercom/issue/PLAT-551
264 lines
6.9 KiB
SQL
264 lines
6.9 KiB
SQL
-- name: GetTemplateVersionsByTemplateID :many
|
|
SELECT
|
|
*
|
|
FROM
|
|
template_version_with_user AS template_versions
|
|
WHERE
|
|
template_id = @template_id :: uuid
|
|
AND CASE
|
|
-- If no filter is provided, default to returning ALL template versions.
|
|
-- The called should always provide a filter if they want to omit
|
|
-- archived versions.
|
|
WHEN sqlc.narg('archived') :: boolean IS NULL THEN true
|
|
ELSE template_versions.archived = sqlc.narg('archived') :: boolean
|
|
END
|
|
AND CASE
|
|
-- This allows using the last element on a page as effectively a cursor.
|
|
-- This is an important option for scripts that need to paginate without
|
|
-- duplicating or missing data.
|
|
WHEN @after_id :: uuid != '00000000-0000-0000-0000-000000000000'::uuid THEN (
|
|
-- The pagination cursor is the last ID of the previous page.
|
|
-- The query is ordered by the created_at field, so select all
|
|
-- rows after the cursor.
|
|
(created_at, id) > (
|
|
SELECT
|
|
created_at, id
|
|
FROM
|
|
template_versions
|
|
WHERE
|
|
id = @after_id
|
|
)
|
|
)
|
|
ELSE true
|
|
END
|
|
ORDER BY
|
|
-- Deterministic and consistent ordering of all rows, even if they share
|
|
-- a timestamp. This is to ensure consistent pagination.
|
|
(created_at, id) ASC OFFSET @offset_opt
|
|
LIMIT
|
|
-- A null limit means "no limit", so 0 means return all
|
|
NULLIF(@limit_opt :: int, 0);
|
|
|
|
-- name: GetTemplateVersionByJobID :one
|
|
SELECT
|
|
*
|
|
FROM
|
|
template_version_with_user AS template_versions
|
|
WHERE
|
|
job_id = $1;
|
|
|
|
-- name: GetTemplateVersionsCreatedAfter :many
|
|
SELECT * FROM template_version_with_user AS template_versions WHERE created_at > $1;
|
|
|
|
-- name: GetTemplateVersionByTemplateIDAndName :one
|
|
SELECT
|
|
*
|
|
FROM
|
|
template_version_with_user AS template_versions
|
|
WHERE
|
|
template_id = $1
|
|
AND "name" = $2;
|
|
|
|
-- name: GetTemplateVersionByID :one
|
|
SELECT
|
|
*
|
|
FROM
|
|
template_version_with_user AS template_versions
|
|
WHERE
|
|
id = $1;
|
|
|
|
-- name: GetTemplateVersionsByIDs :many
|
|
SELECT
|
|
*
|
|
FROM
|
|
template_version_with_user AS template_versions
|
|
WHERE
|
|
id = ANY(@ids :: uuid [ ]);
|
|
|
|
-- name: InsertTemplateVersion :exec
|
|
INSERT INTO
|
|
template_versions (
|
|
id,
|
|
template_id,
|
|
organization_id,
|
|
created_at,
|
|
updated_at,
|
|
"name",
|
|
message,
|
|
readme,
|
|
job_id,
|
|
created_by,
|
|
source_example_id
|
|
)
|
|
VALUES
|
|
($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11);
|
|
|
|
-- name: UpdateTemplateVersionByID :exec
|
|
UPDATE
|
|
template_versions
|
|
SET
|
|
template_id = $2,
|
|
updated_at = $3,
|
|
name = $4,
|
|
message = $5
|
|
WHERE
|
|
id = $1;
|
|
|
|
-- name: UpdateTemplateVersionDescriptionByJobID :exec
|
|
UPDATE
|
|
template_versions
|
|
SET
|
|
readme = $2,
|
|
updated_at = $3
|
|
WHERE
|
|
job_id = $1;
|
|
|
|
-- name: UpdateTemplateVersionExternalAuthProvidersByJobID :exec
|
|
UPDATE
|
|
template_versions
|
|
SET
|
|
external_auth_providers = $2,
|
|
updated_at = $3
|
|
WHERE
|
|
job_id = $1;
|
|
|
|
-- name: GetPreviousTemplateVersion :one
|
|
SELECT
|
|
*
|
|
FROM
|
|
template_version_with_user AS template_versions
|
|
WHERE
|
|
created_at < (
|
|
SELECT created_at
|
|
FROM template_version_with_user AS tv
|
|
WHERE tv.organization_id = $1 AND tv.name = $2 AND tv.template_id = $3
|
|
)
|
|
AND organization_id = $1
|
|
AND template_id = $3
|
|
ORDER BY created_at DESC
|
|
LIMIT 1;
|
|
|
|
-- name: UnarchiveTemplateVersion :exec
|
|
-- This will always work regardless of the current state of the template version.
|
|
UPDATE
|
|
template_versions
|
|
SET
|
|
archived = false,
|
|
updated_at = sqlc.arg('updated_at')
|
|
WHERE
|
|
id = sqlc.arg('template_version_id');
|
|
|
|
-- name: ArchiveUnusedTemplateVersions :many
|
|
-- Archiving templates is a soft delete action, so is reversible.
|
|
-- Archiving prevents the version from being used and discovered
|
|
-- by listing.
|
|
-- Only unused template versions will be archived, which are any versions not
|
|
-- referenced by the latest build of a workspace.
|
|
UPDATE
|
|
template_versions
|
|
SET
|
|
archived = true,
|
|
updated_at = sqlc.arg('updated_at')
|
|
FROM
|
|
-- Archive all versions that are returned from this query.
|
|
(
|
|
SELECT
|
|
scoped_template_versions.id
|
|
FROM
|
|
-- Scope an archive to a single template and ignore already archived template versions
|
|
(
|
|
SELECT
|
|
*
|
|
FROM
|
|
template_versions
|
|
WHERE
|
|
template_versions.template_id = sqlc.arg('template_id') :: uuid
|
|
AND
|
|
archived = false
|
|
AND
|
|
-- This allows archiving a specific template version.
|
|
CASE
|
|
WHEN sqlc.arg('template_version_id')::uuid != '00000000-0000-0000-0000-000000000000'::uuid THEN
|
|
template_versions.id = sqlc.arg('template_version_id') :: uuid
|
|
ELSE
|
|
true
|
|
END
|
|
) AS scoped_template_versions
|
|
LEFT JOIN
|
|
provisioner_jobs ON scoped_template_versions.job_id = provisioner_jobs.id
|
|
LEFT JOIN
|
|
templates ON scoped_template_versions.template_id = templates.id
|
|
WHERE
|
|
-- Actively used template versions (meaning the latest build is using
|
|
-- the version) are never archived. A "restart" command on the workspace,
|
|
-- even if failed, would use the version. So it cannot be archived until
|
|
-- the build is outdated.
|
|
NOT EXISTS (
|
|
-- Return all "used" versions, where "used" is defined as being
|
|
-- used by a latest workspace build.
|
|
SELECT template_version_id FROM (
|
|
SELECT
|
|
DISTINCT ON (workspace_id) template_version_id, transition
|
|
FROM
|
|
workspace_builds
|
|
ORDER BY workspace_id, build_number DESC
|
|
) AS used_versions
|
|
WHERE
|
|
used_versions.transition != 'delete'
|
|
AND
|
|
scoped_template_versions.id = used_versions.template_version_id
|
|
)
|
|
-- Also never archive the active template version
|
|
AND active_version_id != scoped_template_versions.id
|
|
AND CASE
|
|
-- Optionally, only archive versions that match a given
|
|
-- job status like 'failed'.
|
|
WHEN sqlc.narg('job_status') :: provisioner_job_status IS NOT NULL THEN
|
|
provisioner_jobs.job_status = sqlc.narg('job_status') :: provisioner_job_status
|
|
ELSE
|
|
true
|
|
END
|
|
-- Pending or running jobs should not be archived, as they are "in progress"
|
|
AND provisioner_jobs.job_status != 'running'
|
|
AND provisioner_jobs.job_status != 'pending'
|
|
) AS archived_versions
|
|
WHERE
|
|
template_versions.id IN (archived_versions.id)
|
|
RETURNING template_versions.id;
|
|
|
|
-- name: UpdateTemplateVersionFlagsByJobID :exec
|
|
UPDATE
|
|
template_versions
|
|
SET
|
|
has_external_agent = $2,
|
|
updated_at = $3
|
|
WHERE
|
|
job_id = $1;
|
|
|
|
-- name: UpdateTemplateVersionScriptOrderUsageByJobID :exec
|
|
UPDATE
|
|
template_versions
|
|
SET
|
|
script_order_data_source_count = $2,
|
|
script_order_rule_count = $3,
|
|
updated_at = $4
|
|
WHERE
|
|
job_id = $1;
|
|
|
|
-- name: GetScriptOrderTelemetrySummary :one
|
|
-- Count only the active, successfully imported version of each non-deleted
|
|
-- template, so repeated imports do not inflate adoption.
|
|
SELECT
|
|
COUNT(*) AS templates_with_script_order,
|
|
COALESCE(SUM(tv.script_order_data_source_count), 0)::bigint AS data_source_count,
|
|
COALESCE(SUM(tv.script_order_rule_count), 0)::bigint AS rule_count
|
|
FROM templates t
|
|
JOIN template_versions tv ON tv.id = t.active_version_id
|
|
JOIN provisioner_jobs pj ON pj.id = tv.job_id
|
|
WHERE t.deleted = false
|
|
AND tv.script_order_data_source_count > 0
|
|
AND pj.type = 'template_version_import'
|
|
AND pj.completed_at IS NOT NULL
|
|
AND COALESCE(pj.error, '') = ''
|
|
AND pj.canceled_at IS NULL;
|