CREATE TYPE agent_id_name_pair AS (

id uuid,

name text

);

CREATE TYPE agent_key_scope_enum AS ENUM (

'all',

'no_user_data'

);

CREATE TYPE ai_seat_usage_reason AS ENUM (

'aibridge',

'task'

);

CREATE TYPE api_key_scope AS ENUM (

'coder:all',

'coder:application_connect',

'aibridge_interception:create',

'aibridge_interception:read',

'aibridge_interception:update',

'api_key:create',

'api_key:delete',

'api_key:read',

'api_key:update',

'assign_org_role:assign',

'assign_org_role:create',

'assign_org_role:delete',

'assign_org_role:read',

'assign_org_role:unassign',

'assign_org_role:update',

'assign_role:assign',

'assign_role:read',

'assign_role:unassign',

'audit_log:create',

'audit_log:read',

'connection_log:read',

'connection_log:update',

'crypto_key:create',

'crypto_key:delete',

'crypto_key:read',

'crypto_key:update',

'debug_info:read',

'deployment_config:read',

'deployment_config:update',

'deployment_stats:read',

'file:create',

'file:read',

'group:create',

'group:delete',

'group:read',

'group:update',

'group_member:read',

'idpsync_settings:read',

'idpsync_settings:update',

'inbox_notification:create',

'inbox_notification:read',

'inbox_notification:update',

'license:create',

'license:delete',

'license:read',

'notification_message:create',

'notification_message:delete',

'notification_message:read',

'notification_message:update',

'notification_preference:read',

'notification_preference:update',

'notification_template:read',

'notification_template:update',

'oauth2_app:create',

'oauth2_app:delete',

'oauth2_app:read',

'oauth2_app:update',

'oauth2_app_code_token:create',

'oauth2_app_code_token:delete',

'oauth2_app_code_token:read',

'oauth2_app_secret:create',

'oauth2_app_secret:delete',

'oauth2_app_secret:read',

'oauth2_app_secret:update',

'organization:create',

'organization:delete',

'organization:read',

'organization:update',

'organization_member:create',

'organization_member:delete',

'organization_member:read',

'organization_member:update',

'prebuilt_workspace:delete',

'prebuilt_workspace:update',

'provisioner_daemon:create',

'provisioner_daemon:delete',

'provisioner_daemon:read',

'provisioner_daemon:update',

'provisioner_jobs:create',

'provisioner_jobs:read',

'provisioner_jobs:update',

'replicas:read',

'system:create',

'system:delete',

'system:read',

'system:update',

'tailnet_coordinator:create',

'tailnet_coordinator:delete',

'tailnet_coordinator:read',

'tailnet_coordinator:update',

'template:create',

'template:delete',

'template:read',

'template:update',

'template:use',

'template:view_insights',

'usage_event:create',

'usage_event:read',

'usage_event:update',

'user:create',

'user:delete',

'user:read',

'user:read_personal',

'user:update',

'user:update_personal',

'user_secret:create',

'user_secret:delete',

'user_secret:read',

'user_secret:update',

'webpush_subscription:create',

'webpush_subscription:delete',

'webpush_subscription:read',

'workspace:application_connect',

'workspace:create',

'workspace:create_agent',

'workspace:delete',

'workspace:delete_agent',

'workspace:read',

'workspace:ssh',

'workspace:start',

'workspace:stop',

'workspace:update',

'workspace_agent_devcontainers:create',

'workspace_agent_resource_monitor:create',

'workspace_agent_resource_monitor:read',

'workspace_agent_resource_monitor:update',

'workspace_dormant:application_connect',

'workspace_dormant:create',

'workspace_dormant:create_agent',

'workspace_dormant:delete',

'workspace_dormant:delete_agent',

'workspace_dormant:read',

'workspace_dormant:ssh',

'workspace_dormant:start',

'workspace_dormant:stop',

'workspace_dormant:update',

'workspace_proxy:create',

'workspace_proxy:delete',

'workspace_proxy:read',

'workspace_proxy:update',

'coder:workspaces.create',

'coder:workspaces.operate',

'coder:workspaces.delete',

'coder:workspaces.access',

'coder:templates.build',

'coder:templates.author',

'coder:apikeys.manage_self',

'aibridge_interception:*',

'api_key:*',

'assign_org_role:*',

'assign_role:*',

'audit_log:*',

'connection_log:*',

'crypto_key:*',

'debug_info:*',

'deployment_config:*',

'deployment_stats:*',

'file:*',

'group:*',

'group_member:*',

'idpsync_settings:*',

'inbox_notification:*',

'license:*',

'notification_message:*',

'notification_preference:*',

'notification_template:*',

'oauth2_app:*',

'oauth2_app_code_token:*',

'oauth2_app_secret:*',

'organization:*',

'organization_member:*',

'prebuilt_workspace:*',

'provisioner_daemon:*',

'provisioner_jobs:*',

'replicas:*',

'system:*',

'tailnet_coordinator:*',

'template:*',

'usage_event:*',

'user:*',

'user_secret:*',

'webpush_subscription:*',

'workspace:*',

'workspace_agent_devcontainers:*',

'workspace_agent_resource_monitor:*',

'workspace_dormant:*',

'workspace_proxy:*',

'task:create',

'task:read',

'task:update',

'task:delete',

'task:*',

'workspace:share',

'workspace_dormant:share',

'boundary_usage:*',

'boundary_usage:delete',

'boundary_usage:read',

'boundary_usage:update',

'workspace:update_agent',

'workspace_dormant:update_agent',

'chat:create',

'chat:read',

'chat:update',

'chat:delete',

'chat:*',

'chat_automation:create',

'chat_automation:read',

'chat_automation:update',

'chat_automation:delete',

'chat_automation:*'

);

CREATE TYPE app_sharing_level AS ENUM (

'owner',

'authenticated',

'organization',

'public'

);

CREATE TYPE audit_action AS ENUM (

'create',

'write',

'delete',

'start',

'stop',

'login',

'logout',

'register',

'request_password_reset',

'connect',

'disconnect',

'open',

'close'

);

COMMENT ON TYPE audit_action IS 'NOTE: `connect`, `disconnect`, `open`, and `close` are deprecated and no longer used - these events are now tracked in the connection_logs table.';

CREATE TYPE automatic_updates AS ENUM (

'always',

'never'

);

CREATE TYPE build_reason AS ENUM (

'initiator',

'autostart',

'autostop',

'dormancy',

'failedstop',

'autodelete',

'dashboard',

'cli',

'ssh_connection',

'vscode_connection',

'jetbrains_connection',

'task_auto_pause',

'task_manual_pause',

'task_resume'

);

CREATE TYPE chat_automation_event_status AS ENUM (

'filtered',

'preview',

'created',

'continued',

'rate_limited',

'error'

);

CREATE TYPE chat_automation_status AS ENUM (

'disabled',

'preview',

'active'

);

CREATE TYPE chat_automation_trigger_type AS ENUM (

'webhook',

'cron'

);

CREATE TYPE chat_message_role AS ENUM (

'system',

'user',

'assistant',

'tool'

);

CREATE TYPE chat_message_visibility AS ENUM (

'user',

'model',

'both'

);

CREATE TYPE chat_mode AS ENUM (

'computer_use'

);

CREATE TYPE chat_status AS ENUM (

'waiting',

'pending',

'running',

'paused',

'completed',

'error'

);

CREATE TYPE connection_status AS ENUM (

'connected',

'disconnected'

);

CREATE TYPE connection_type AS ENUM (

'ssh',

'vscode',

'jetbrains',

'reconnecting_pty',

'workspace_app',

'port_forwarding'

);

CREATE TYPE cors_behavior AS ENUM (

'simple',

'passthru'

);

CREATE TYPE crypto_key_feature AS ENUM (

'workspace_apps_token',

'workspace_apps_api_key',

'oidc_convert',

'tailnet_resume'

);

CREATE TYPE display_app AS ENUM (

'vscode',

'vscode_insiders',

'web_terminal',

'ssh_helper',

'port_forwarding_helper'

);

CREATE TYPE group_source AS ENUM (

'user',

'oidc'

);

CREATE TYPE inbox_notification_read_status AS ENUM (

'all',

'unread',

'read'

);

CREATE TYPE log_level AS ENUM (

'trace',

'debug',

'info',

'warn',

'error'

);

CREATE TYPE log_source AS ENUM (

'provisioner_daemon',

'provisioner'

);

CREATE TYPE login_type AS ENUM (

'password',

'github',

'oidc',

'token',

'none',

'oauth2_provider_app'

);

COMMENT ON TYPE login_type IS 'Specifies the method of authentication. "none" is a special case in which no authentication method is allowed.';

CREATE TYPE name_organization_pair AS (

name text,

organization_id uuid

);

CREATE TYPE notification_message_status AS ENUM (

'pending',

'leased',

'sent',

'permanent_failure',

'temporary_failure',

'unknown',

'inhibited'

);

CREATE TYPE notification_method AS ENUM (

'smtp',

'webhook',

'inbox'

);

CREATE TYPE notification_template_kind AS ENUM (

'system',

'custom'

);

CREATE TYPE parameter_destination_scheme AS ENUM (

'none',

'environment_variable',

'provisioner_variable'

);

CREATE TYPE parameter_form_type AS ENUM (

'',

'error',

'radio',

'dropdown',

'input',

'textarea',

'slider',

'checkbox',

'switch',

'tag-select',

'multi-select'

);

COMMENT ON TYPE parameter_form_type IS 'Enum set should match the terraform provider set. This is defined as future form_types are not supported, and should be rejected. Always include the empty string for using the default form type.';

CREATE TYPE parameter_scope AS ENUM (

'template',

'import_job',

'workspace'

);

CREATE TYPE parameter_source_scheme AS ENUM (

'none',

'data'

);

CREATE TYPE parameter_type_system AS ENUM (

'none',

'hcl'

);

CREATE TYPE port_share_protocol AS ENUM (

'http',

'https'

);

CREATE TYPE prebuild_status AS ENUM (

'healthy',

'hard_limited',

'validation_failed'

);

CREATE TYPE provisioner_daemon_status AS ENUM (

'offline',

'idle',

'busy'

);

COMMENT ON TYPE provisioner_daemon_status IS 'The status of a provisioner daemon.';

CREATE TYPE provisioner_job_status AS ENUM (

'pending',

'running',

'succeeded',

'canceling',

'canceled',

'failed',

'unknown'

);

COMMENT ON TYPE provisioner_job_status IS 'Computed status of a provisioner job. Jobs could be stuck in a hung state, these states do not guarantee any transition to another state.';

CREATE TYPE provisioner_job_timing_stage AS ENUM (

'init',

'plan',

'graph',

'apply'

);

CREATE TYPE provisioner_job_type AS ENUM (

'template_version_import',

'workspace_build',

'template_version_dry_run'

);

CREATE TYPE provisioner_storage_method AS ENUM (

'file'

);

CREATE TYPE provisioner_type AS ENUM (

'echo',

'terraform'

);

CREATE TYPE resource_type AS ENUM (

'organization',

'template',

'template_version',

'user',

'workspace',

'git_ssh_key',

'api_key',

'group',

'workspace_build',

'license',

'workspace_proxy',

'convert_login',

'health_settings',

'oauth2_provider_app',

'oauth2_provider_app_secret',

'custom_role',

'organization_member',

'notifications_settings',

'notification_template',

'idp_sync_settings_organization',

'idp_sync_settings_group',

'idp_sync_settings_role',

'workspace_agent',

'workspace_app',

'prebuilds_settings',

'task',

'ai_seat'

);

CREATE TYPE shareable_workspace_owners AS ENUM (

'none',

'everyone',

'service_accounts'

);

CREATE TYPE startup_script_behavior AS ENUM (

'blocking',

'non-blocking'

);

CREATE DOMAIN tagset AS jsonb;

COMMENT ON DOMAIN tagset IS 'A set of tags that match provisioner daemons to provisioner jobs, which can originate from workspaces or templates. tagset is a narrowed type over jsonb. It is expected to be the JSON representation of map[string]string. That is, {"key1": "value1", "key2": "value2"}. We need the narrowed type instead of just using jsonb so that we can give sqlc a type hint, otherwise it defaults to json.RawMessage. json.RawMessage is a suboptimal type to use in the context that we need tagset for.';

CREATE TYPE tailnet_status AS ENUM (

'ok',

'lost'

);

CREATE TYPE task_status AS ENUM (

'pending',

'initializing',

'active',

'paused',

'unknown',

'error'

);

CREATE TYPE user_status AS ENUM (

'active',

'suspended',

'dormant'

);

COMMENT ON TYPE user_status IS 'Defines the users status: active, dormant, or suspended.';

CREATE TYPE workspace_agent_lifecycle_state AS ENUM (

'created',

'starting',

'start_timeout',

'start_error',

'ready',

'shutting_down',

'shutdown_timeout',

'shutdown_error',

'off'

);

CREATE TYPE workspace_agent_monitor_state AS ENUM (

'OK',

'NOK'

);

CREATE TYPE workspace_agent_script_timing_stage AS ENUM (

'start',

'stop',

'cron'

);

COMMENT ON TYPE workspace_agent_script_timing_stage IS 'What stage the script was ran in.';

CREATE TYPE workspace_agent_script_timing_status AS ENUM (

'ok',

'exit_failure',

'timed_out',

'pipes_left_open'

);

COMMENT ON TYPE workspace_agent_script_timing_status IS 'What the exit status of the script is.';

CREATE TYPE workspace_agent_subsystem AS ENUM (

'envbuilder',

'envbox',

'none',

'exectrace'

);

CREATE TYPE workspace_app_health AS ENUM (

'disabled',

'initializing',

'healthy',

'unhealthy'

);

CREATE TYPE workspace_app_open_in AS ENUM (

'tab',

'window',

'slim-window'

);

CREATE TYPE workspace_app_status_state AS ENUM (

'working',

'complete',

'failure',

'idle'

);

CREATE TYPE workspace_transition AS ENUM (

'start',

'stop',

'delete'

);

CREATE FUNCTION aggregate_usage_event() RETURNS trigger

LANGUAGE plpgsql

AS $$

-- Check for supported event types and throw error for unknown types.

IF NEW.event_type NOT IN ('dc_managed_agents_v1', 'hb_ai_seats_v1') THEN

RAISE EXCEPTION 'Unhandled usage event type in aggregate_usage_event: %', NEW.event_type;

END IF;

INSERT INTO usage_events_daily (day, event_type, usage_data)

VALUES (

date_trunc('day', NEW.created_at AT TIME ZONE 'UTC')::date,

NEW.event_type,

NEW.event_data

)

ON CONFLICT (day, event_type) DO UPDATE SET

usage_data = CASE

-- Handle simple counter events by summing the count.

WHEN NEW.event_type IN ('dc_managed_agents_v1') THEN

jsonb_build_object(

'count',

COALESCE((usage_events_daily.usage_data->>'count')::bigint, 0) +

COALESCE((NEW.event_data->>'count')::bigint, 0)

)

-- Heartbeat events: keep the max value seen that day

WHEN NEW.event_type IN ('hb_ai_seats_v1') THEN

jsonb_build_object(

'count',

GREATEST(

COALESCE((usage_events_daily.usage_data->>'count')::bigint, 0),

COALESCE((NEW.event_data->>'count')::bigint, 0)

)

)

END;

RETURN NEW;

END;

$$;

CREATE FUNCTION check_workspace_agent_name_unique() RETURNS trigger

LANGUAGE plpgsql

AS $$

DECLARE

workspace_build_id uuid;

agents_with_name int;

-- Find the workspace build the workspace agent is being inserted into.

SELECT workspace_builds.id INTO workspace_build_id

FROM workspace_resources

JOIN workspace_builds ON workspace_builds.job_id = workspace_resources.job_id

WHERE workspace_resources.id = NEW.resource_id;

-- If the agent doesn't have a workspace build, we'll allow the insert.

IF workspace_build_id IS NULL THEN

RETURN NEW;

END IF;

-- Count how many agents in this workspace build already have the given agent name.

SELECT COUNT(*) INTO agents_with_name

FROM workspace_agents

JOIN workspace_resources ON workspace_resources.id = workspace_agents.resource_id

JOIN workspace_builds ON workspace_builds.job_id = workspace_resources.job_id

WHERE workspace_builds.id = workspace_build_id

AND workspace_agents.name = NEW.name

AND workspace_agents.id != NEW.id

AND workspace_agents.deleted = FALSE; -- Ensure we only count non-deleted agents.

-- If there's already an agent with this name, raise an error

IF agents_with_name > 0 THEN

RAISE EXCEPTION 'workspace agent name "%" already exists in this workspace build', NEW.name

USING ERRCODE = 'unique_violation';

END IF;

RETURN NEW;

END;

$$;

CREATE FUNCTION compute_notification_message_dedupe_hash() RETURNS trigger

LANGUAGE plpgsql

AS $$

NEW.dedupe_hash := MD5(CONCAT_WS(':',

NEW.notification_template_id,

NEW.user_id,

NEW.method,

NEW.payload::text,

ARRAY_TO_STRING(NEW.targets, ','),

DATE_TRUNC('day', NEW.created_at AT TIME ZONE 'UTC')::text

));

RETURN NEW;

END;

$$;

COMMENT ON FUNCTION compute_notification_message_dedupe_hash() IS 'Computes a unique hash which will be used to prevent duplicate messages from being enqueued on the same day';

CREATE FUNCTION delete_deleted_oauth2_provider_app_token_api_key() RETURNS trigger

LANGUAGE plpgsql

AS $$

DECLARE

DELETE FROM api_keys

WHERE id = OLD.api_key_id;

RETURN OLD;

END;

$$;

CREATE FUNCTION delete_deleted_user_resources() RETURNS trigger

LANGUAGE plpgsql

AS $$

DECLARE

IF (NEW.deleted) THEN

-- Remove their api_keys

DELETE FROM api_keys

WHERE user_id = OLD.id;

-- Remove their user_links

-- Their login_type is preserved in the users table.

-- Matching this user back to the link can still be done by their

-- email if the account is undeleted. Although that is not a guarantee.

DELETE FROM user_links

WHERE user_id = OLD.id;

END IF;

RETURN NEW;

END;

$$;

CREATE FUNCTION delete_group_members_on_org_member_delete() RETURNS trigger

LANGUAGE plpgsql

AS $$

DECLARE

-- Remove the user from all groups associated with the same

-- organization as the organization_member being deleted.

DELETE FROM group_members

WHERE

user_id = OLD.user_id

AND group_id IN (

SELECT id

FROM groups

WHERE organization_id = OLD.organization_id

);

RETURN OLD;

END;

$$;

CREATE FUNCTION inhibit_enqueue_if_disabled() RETURNS trigger

LANGUAGE plpgsql

AS $$

-- Fail the insertion if one of the following:

-- * the user has disabled this notification.

-- * the notification template is disabled by default and hasn't

-- been explicitly enabled by the user.

IF EXISTS (

SELECT 1 FROM notification_templates

LEFT JOIN notification_preferences

ON notification_preferences.notification_template_id = notification_templates.id

AND notification_preferences.user_id = NEW.user_id

WHERE notification_templates.id = NEW.notification_template_id AND (

-- Case 1: The user has explicitly disabled this template

notification_preferences.disabled = TRUE

OR

-- Case 2: The template is disabled by default AND the user hasn't enabled it

(notification_templates.enabled_by_default = FALSE AND notification_preferences.notification_template_id IS NULL)

)

) THEN

RAISE EXCEPTION 'cannot enqueue message: notification is not enabled';

END IF;

RETURN NEW;

END;

$$;

CREATE FUNCTION insert_apikey_fail_if_user_deleted() RETURNS trigger

LANGUAGE plpgsql

AS $$

DECLARE

IF (NEW.user_id IS NOT NULL) THEN

IF (SELECT deleted FROM users WHERE id = NEW.user_id LIMIT 1) THEN

RAISE EXCEPTION 'Cannot create API key for deleted user';

END IF;

END IF;

RETURN NEW;

END;

$$;

CREATE FUNCTION insert_organization_system_roles() RETURNS trigger

LANGUAGE plpgsql

AS $$

INSERT INTO custom_roles (

name,

display_name,

organization_id,

site_permissions,

org_permissions,

user_permissions,

member_permissions,

is_system,

created_at,

updated_at

) VALUES

(

'organization-member',

'',

NEW.id,

'[]'::jsonb,

'[]'::jsonb,

'[]'::jsonb,

'[]'::jsonb,

true,

NOW(),

NOW()

),

(

'organization-service-account',

'',

NEW.id,

'[]'::jsonb,

'[]'::jsonb,

'[]'::jsonb,

'[]'::jsonb,

true,

NOW(),

NOW()

);

RETURN NEW;

END;

$$;

CREATE FUNCTION insert_user_links_fail_if_user_deleted() RETURNS trigger

LANGUAGE plpgsql

AS $$

DECLARE

IF (NEW.user_id IS NOT NULL) THEN

IF (SELECT deleted FROM users WHERE id = NEW.user_id LIMIT 1) THEN

RAISE EXCEPTION 'Cannot create user_link for deleted user';

END IF;

END IF;

RETURN NEW;

END;

$$;

CREATE FUNCTION nullify_next_start_at_on_workspace_autostart_modification() RETURNS trigger

LANGUAGE plpgsql

AS $$

DECLARE

-- A workspace's next_start_at might be invalidated by the following:

-- * The autostart schedule has changed independent to next_start_at

-- * The workspace has been marked as dormant

IF (NEW.autostart_schedule <> OLD.autostart_schedule AND NEW.next_start_at = OLD.next_start_at)

OR (NEW.dormant_at IS NOT NULL AND NEW.next_start_at IS NOT NULL)

THEN

UPDATE workspaces

SET next_start_at = NULL

WHERE id = NEW.id;

END IF;

RETURN NEW;

END;

$$;

CREATE FUNCTION protect_deleting_organizations() RETURNS trigger

LANGUAGE plpgsql

AS $$

DECLARE

workspace_count int;

template_count int;

group_count int;

member_count int;

provisioner_keys_count int;

workspace_count := (

SELECT count(*) as count FROM workspaces

WHERE

workspaces.organization_id = OLD.id

AND workspaces.deleted = false

);

template_count := (

SELECT count(*) as count FROM templates

WHERE

templates.organization_id = OLD.id

AND templates.deleted = false

);

group_count := (

SELECT count(*) as count FROM groups

WHERE

groups.organization_id = OLD.id

);

member_count := (

SELECT

count(*) AS count

FROM

organization_members

LEFT JOIN users ON users.id = organization_members.user_id

WHERE

organization_members.organization_id = OLD.id

AND users.deleted = FALSE

);

provisioner_keys_count := (

Select count(*) as count FROM provisioner_keys

WHERE

provisioner_keys.organization_id = OLD.id

);

-- Fail the deletion if one of the following:

-- * the organization has 1 or more workspaces

-- * the organization has 1 or more templates

-- * the organization has 1 or more groups other than "Everyone" group

-- * the organization has 1 or more members other than the organization owner

-- * the organization has 1 or more provisioner keys

-- Only create error message for resources that actually exist

IF (workspace_count + template_count + provisioner_keys_count) > 0 THEN

DECLARE

error_message text := 'cannot delete organization: organization has ';

error_parts text[] := '{}';

BEGIN

IF workspace_count > 0 THEN

error_parts := array_append(error_parts, workspace_count || ' workspaces');

END IF;

IF template_count > 0 THEN

error_parts := array_append(error_parts, template_count || ' templates');

END IF;

IF provisioner_keys_count > 0 THEN

error_parts := array_append(error_parts, provisioner_keys_count || ' provisioner keys');

END IF;

error_message := error_message || array_to_string(error_parts, ', ') || ' that must be deleted first';

RAISE EXCEPTION '%', error_message;

END;

END IF;

IF (group_count) > 1 THEN

RAISE EXCEPTION 'cannot delete organization: organization has % groups that must be deleted first', group_count - 1;

END IF;

-- Allow 1 member to exist, because you cannot remove yourself. You can

-- remove everyone else. Ideally, we only omit the member that matches

-- the user_id of the caller, however in a trigger, the caller is unknown.

IF (member_count) > 1 THEN

RAISE EXCEPTION 'cannot delete organization: organization has % members that must be deleted first', member_count - 1;

END IF;

RETURN NEW;

END;