-- Code generated by 'make coderd/database/generate'. DO NOT EDIT.

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_provider_type AS ENUM (

'openai',

'anthropic',

'azure',

'bedrock',

'google',

'openai-compat',

'openrouter',

'vercel',

'copilot'

);

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:*',

'ai_seat:*',

'ai_seat:create',

'ai_seat:read',

'ai_model_price:*',

'ai_model_price:read',

'ai_model_price:update',

'ai_provider:*',

'ai_provider:create',

'ai_provider:delete',

'ai_provider:read',

'ai_provider:update',

'chat:share',

'user_skill:create',

'user_skill:read',

'user_skill:update',

'user_skill:delete',

'user_skill:*',

'boundary_log:*',

'boundary_log:create',

'boundary_log:delete',

'boundary_log:read',

'ai_gateway_key:*',

'ai_gateway_key:create',

'ai_gateway_key:delete',

'ai_gateway_key:read'

);

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_client_type AS ENUM (

'ui',

'api'

);

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',

'explore'

);

CREATE TYPE chat_plan_mode AS ENUM (

'plan'

);

CREATE TYPE chat_status AS ENUM (

'waiting',

'pending',

'running',

'paused',

'completed',

'error',

'requires_action'

);

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 credential_kind AS ENUM (

'centralized',

'byok'

);

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',

'chat',

'user_secret',

'ai_provider',

'ai_provider_key',

'group_ai_budget',

'user_skill',

'ai_gateway_key'

);

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 $$

BEGIN

-- 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;

BEGIN

-- 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 $$

BEGIN

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

BEGIN

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

BEGIN

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;

-- Remove their user_secrets.

-- user_secrets.user_id has ON DELETE CASCADE, but soft-delete

-- does not remove the users row so the FK cascade never fires.

DELETE FROM user_secrets

WHERE user_id = OLD.id;

-- Remove their user AI provider keys.

-- user_ai_provider_keys.user_id has ON DELETE CASCADE, but soft-delete

-- does not remove the users row so the FK cascade never fires.

DELETE FROM user_ai_provider_keys

WHERE user_id = OLD.id;

-- Remove their organization memberships.

-- This also triggers group membership cleanup via

-- trigger_delete_group_members_on_org_member_delete.

DELETE FROM organization_members

WHERE user_id = OLD.id;

-- Remove their user_skills.

-- user_skills.user_id has ON DELETE CASCADE, but soft-delete

-- does not remove the users row so the FK cascade never fires.

DELETE FROM user_skills

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

BEGIN

-- 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 delete_user_ai_budget_overrides_on_group_member_delete() RETURNS trigger

LANGUAGE plpgsql

AS $$

BEGIN

DELETE FROM user_ai_budget_overrides

WHERE user_id = OLD.user_id AND group_id = OLD.group_id;

RETURN OLD;

END;

$$;

CREATE FUNCTION delete_user_ai_budget_overrides_on_org_member_delete() RETURNS trigger

LANGUAGE plpgsql

AS $$

BEGIN

DELETE FROM user_ai_budget_overrides

WHERE user_id = OLD.user_id AND group_id = OLD.organization_id;

RETURN OLD;

END;

$$;

CREATE FUNCTION enforce_user_ai_budget_override_membership() RETURNS trigger

LANGUAGE plpgsql

AS $$

BEGIN

IF NOT EXISTS (

SELECT 1 FROM group_members_expanded

WHERE user_id = NEW.user_id AND group_id = NEW.group_id

) THEN

RAISE EXCEPTION 'user % is not a member of group %', NEW.user_id, NEW.group_id

USING ERRCODE = 'check_violation',

CONSTRAINT = 'user_ai_budget_overrides_must_be_group_member';

END IF;

RETURN NEW;

END;

$$;

CREATE FUNCTION enforce_user_secrets_per_user_limits() RETURNS trigger

LANGUAGE plpgsql

AS $$

DECLARE

existing_count int;

existing_total_bytes bigint;

existing_env_bytes bigint;

new_count int;

new_total_bytes bigint;

new_env_bytes bigint;

count_limit constant int := 50;

total_bytes_limit constant bigint := 204800; -- 200 KiB

env_bytes_limit constant bigint := 24576; -- 24 KiB

BEGIN

-- Serialize cap checks per user so concurrent inserts cannot all

-- observe the same pre-insert aggregates and exceed the cap.

PERFORM 1 FROM users WHERE id = NEW.user_id FOR UPDATE;

-- Sum existing rows excluding the row being updated (so UPDATE statements

-- don't double-count NEW). On INSERT, no row matches NEW.id, so

-- the FILTER is a no-op.

SELECT

count(*) FILTER (WHERE id IS DISTINCT FROM NEW.id),

coalesce(sum(octet_length(value)) FILTER (WHERE id IS DISTINCT FROM NEW.id), 0),

coalesce(sum(octet_length(value)) FILTER (WHERE id IS DISTINCT FROM NEW.id AND env_name <> ''), 0)

INTO existing_count, existing_total_bytes, existing_env_bytes

FROM user_secrets

WHERE user_id = NEW.user_id;

new_count := existing_count + 1;

new_total_bytes := existing_total_bytes + octet_length(NEW.value);

new_env_bytes := existing_env_bytes

+ CASE WHEN NEW.env_name <> '' THEN octet_length(NEW.value) ELSE 0 END;

IF new_count > count_limit THEN

RAISE EXCEPTION 'user has reached the user secrets count limit (% > %)',

new_count, count_limit

USING ERRCODE = 'check_violation',

CONSTRAINT = 'user_secrets_per_user_count_limit';

END IF;

IF new_total_bytes > total_bytes_limit THEN

RAISE EXCEPTION 'user has reached the user secrets total value bytes limit (% > %)',

new_total_bytes, total_bytes_limit

USING ERRCODE = 'check_violation',

CONSTRAINT = 'user_secrets_per_user_total_bytes_limit';

END IF;

IF new_env_bytes > env_bytes_limit THEN

RAISE EXCEPTION 'user has reached the env-injected user secrets bytes limit (% > %)',

new_env_bytes, env_bytes_limit

USING ERRCODE = 'check_violation',

CONSTRAINT = 'user_secrets_per_user_env_bytes_limit';

END IF;

RETURN NEW;

END;

$$;

CREATE FUNCTION enforce_user_skills_per_user_limit() RETURNS trigger

LANGUAGE plpgsql

AS $$

DECLARE

skill_count int;

skill_limit constant int := 100;

BEGIN

-- Serialize skill-cap checks per user so concurrent inserts cannot all

-- observe the same pre-insert count and exceed the hard limit.

PERFORM 1

FROM users

WHERE id = NEW.user_id

FOR UPDATE;

SELECT count(*) INTO skill_count

FROM user_skills

WHERE user_id = NEW.user_id;

IF skill_count >= skill_limit THEN

RAISE EXCEPTION 'user has reached the personal skill limit'

USING ERRCODE = 'check_violation',

CONSTRAINT = 'user_skills_per_user_limit';

END IF;

RETURN NEW;

END;

$$;

CREATE FUNCTION inhibit_enqueue_if_disabled() RETURNS trigger

LANGUAGE plpgsql

AS $$

BEGIN

-- 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