-- name: GetUserSecretByUserIDAndName :one

SELECT *

FROM user_secrets

WHERE user_id = @user_id AND name = @name;

-- name: GetUserSecretByID :one

SELECT *

FROM user_secrets

WHERE id = @id;

-- name: GetUserSecretByUserIDAndNameForUpdate :one

SELECT *

FROM user_secrets

WHERE user_id = @user_id AND name = @name

FOR UPDATE;

-- name: ListUserSecrets :many

-- Returns metadata only (no value or value_key_id) for the

-- REST API list and get endpoints.

SELECT

id, user_id, name, description,

env_name, file_path, enabled,

created_at, updated_at

FROM user_secrets

WHERE user_id = @user_id

ORDER BY name ASC;

-- name: ListUserSecretsWithValues :many

-- Returns all columns including the secret value. Used by the

-- provisioner (build-time injection) and the agent manifest

-- (runtime injection).

SELECT *

FROM user_secrets

WHERE user_id = @user_id

ORDER BY name ASC;

-- name: CreateUserSecret :one

INSERT INTO user_secrets (

id,

user_id,

name,

description,

value,

value_key_id,

env_name,

file_path,

enabled

) VALUES (

@id,

@user_id,

@name,

@description,

@value,

@value_key_id,

@env_name,

@file_path,

@enabled

) RETURNING *;

-- name: UpdateUserSecretByUserIDAndName :one

UPDATE user_secrets

SET

value = CASE WHEN @update_value::bool THEN @value ELSE value END,

value_key_id = CASE WHEN @update_value::bool THEN @value_key_id ELSE value_key_id END,

description = CASE WHEN @update_description::bool THEN @description ELSE description END,

env_name = CASE WHEN @update_env_name::bool THEN @env_name ELSE env_name END,

file_path = CASE WHEN @update_file_path::bool THEN @file_path ELSE file_path END,

enabled = CASE WHEN @update_enabled::bool THEN @enabled ELSE enabled END,

updated_at = CURRENT_TIMESTAMP

WHERE user_id = @user_id AND name = @name

RETURNING *;

-- name: DeleteUserSecretByUserIDAndName :one

DELETE FROM user_secrets

WHERE user_id = @user_id AND name = @name

RETURNING *;

-- name: GetUserSecretsTelemetrySummary :one

-- Returns deployment-wide aggregates for the telemetry snapshot.

--

-- The denominator for both user-level counts and the per-user

-- distribution is active non-system users. Specifically:

--

-- * deleted = false: Coder soft-deletes by flipping users.deleted

-- rather than removing rows. The delete_deleted_user_resources()

-- trigger now removes their user_secrets, but soft-deleted users

-- are still excluded here so they don't dilute the percentile

-- distribution as zero-secret entries.

-- * status = 'active': dormant users (no recent activity) and

-- suspended users (explicitly disabled) cannot use secrets, so

-- they shouldn't dilute the percentile distribution as

-- zero-secret entries.

-- * is_system = false: internal subjects like the prebuilds user

-- never use secrets in the normal flow.

--

-- Status transitions move users in and out of this denominator, so a

-- snapshot's UsersWithSecrets can drop without any secret being

-- deleted.

--

-- The percentile distribution is computed across all active non-system

-- users, including those with zero secrets, so the percentiles reflect

-- deployment-wide adoption rather than only the power-user subset.

-- percentile_disc returns an actual integer count from the underlying

-- values rather than interpolating between rows.

WITH active_users AS (

SELECT id AS user_id

FROM users

WHERE deleted = false

AND is_system = false

AND status = 'active'::user_status

),

per_user AS (

SELECT au.user_id, COUNT(us.id)::bigint AS n

FROM active_users au

LEFT JOIN user_secrets us ON us.user_id = au.user_id

GROUP BY au.user_id

),

secrets_filtered AS (

SELECT us.env_name, us.file_path

FROM user_secrets us

JOIN active_users au ON au.user_id = us.user_id

)

SELECT

COUNT(*) FILTER (WHERE n > 0)::bigint AS users_with_secrets,

(SELECT COUNT(*) FROM secrets_filtered)::bigint AS total_secrets,

(SELECT COUNT(*) FROM secrets_filtered WHERE env_name != '' AND file_path = '' )::bigint AS env_name_only,

(SELECT COUNT(*) FROM secrets_filtered WHERE env_name = '' AND file_path != '')::bigint AS file_path_only,

(SELECT COUNT(*) FROM secrets_filtered WHERE env_name != '' AND file_path != '')::bigint AS both,

(SELECT COUNT(*) FROM secrets_filtered WHERE env_name = '' AND file_path = '' )::bigint AS neither,

COALESCE(MAX(n), 0)::bigint AS secrets_per_user_max,

COALESCE(percentile_disc(0.25) WITHIN GROUP (ORDER BY n), 0)::bigint AS secrets_per_user_p25,

COALESCE(percentile_disc(0.50) WITHIN GROUP (ORDER BY n), 0)::bigint AS secrets_per_user_p50,

COALESCE(percentile_disc(0.75) WITHIN GROUP (ORDER BY n), 0)::bigint AS secrets_per_user_p75,

COALESCE(percentile_disc(0.90) WITHIN GROUP (ORDER BY n), 0)::bigint AS secrets_per_user_p90

FROM per_user;