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

'default',

'custom'

);

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

'bad_request',

'unauthorized',

'rate_limited',

'overloaded',

'server_error',

'timeout',

'unknown'

);

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

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

'ai_gateway_key:update',

'workspace_build_orchestration:*',

'workspace_build_orchestration:create',

'workspace_build_orchestration:delete',

'workspace_build_orchestration:read',

'workspace_build_orchestration:update',

'mcp_server_config:*',

'mcp_server_config:create',

'mcp_server_config:read',

'mcp_server_config:update',

'mcp_server_config:delete',

'mcp_server_config:share',

'chat_model_config:*',

'chat_model_config:create',

'chat_model_config:read',

'chat_model_config:update',

'chat_model_config:delete',

'chat_model_config:share'

);

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'

);

CREATE TYPE chat_client_type AS ENUM (

'ui',

'api'

);

CREATE TYPE chat_message_role AS ENUM (

'system',

'user',

'assistant',

'tool'

);

CREATE TYPE chat_message_search_tsv_config AS ENUM (

'simple',

'english'

);

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

'none',

'minimal',

'low',

'medium',

'high',

'xhigh',

'max'

);

CREATE TYPE chat_status AS ENUM (

'waiting',

'running',

'error',

'requires_action',

'interrupting'

);

CREATE TYPE connection_status AS ENUM (

'connected',

'disconnected'

);

CREATE TYPE connection_type AS ENUM (

'ssh',

'vscode',

'jetbrains',

'reconnecting_pty',

'workspace_app',

'port_forwarding',

'tunnel'

);

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

'nats_ca',

'chat_files_token'

);

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

'user_ai_budget_override',

'oauth2_provider_settings',

'chat_instruction_settings',

'mcp_server_config',

'chat_model_config',

'chat_operational_settings'

);

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

'instruction_file',

'skill',

'mcp_config',

'mcp_server',

'plugin',

'hook',

'subagent',

'command'

);

CREATE TYPE workspace_agent_context_resource_status AS ENUM (

'ok',

'oversize',

'unreadable',

'invalid',

'excluded'

);

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 TABLE external_auth_links (

provider_id text NOT NULL,

user_id uuid NOT NULL,

created_at timestamp with time zone NOT NULL,

updated_at timestamp with time zone NOT NULL,

oauth_access_token text NOT NULL,

oauth_refresh_token text NOT NULL,

oauth_expiry timestamp with time zone NOT NULL,

oauth_access_token_key_id text,

oauth_refresh_token_key_id text,

oauth_extra jsonb,

oauth_refresh_failure_reason text DEFAULT ''::text NOT NULL,

refresh_lease_expires_at timestamp with time zone

);

COMMENT ON COLUMN external_auth_links.oauth_access_token_key_id IS 'The ID of the key used to encrypt the OAuth access token. If this is NULL, the access token is not encrypted';

COMMENT ON COLUMN external_auth_links.oauth_refresh_token_key_id IS 'The ID of the key used to encrypt the OAuth refresh token. If this is NULL, the refresh token is not encrypted';

COMMENT ON COLUMN external_auth_links.oauth_refresh_failure_reason IS 'This error means the refresh token is invalid. Cached so we can avoid calling the external provider again for the same error.';

COMMENT ON COLUMN external_auth_links.refresh_lease_expires_at IS 'Indicates a replica is refreshing the token; prevents concurrent refreshes.';

CREATE FUNCTION acquire_external_auth_link_refresh_lease(arg_provider_id text, arg_user_id uuid, timeout_ms bigint) RETURNS SETOF external_auth_links

LANGUAGE plpgsql

AS $$

DECLARE r external_auth_links;

BEGIN

UPDATE external_auth_links

SET

refresh_lease_expires_at = NOW() + (timeout_ms || ' ms')::interval

WHERE

provider_id = arg_provider_id

AND user_id = arg_user_id

AND (refresh_lease_expires_at IS NULL OR refresh_lease_expires_at < NOW())

RETURNING * INTO r;

-- Got the lease, return the one row.

IF FOUND THEN

RETURN NEXT r;

RETURN;

END IF;

-- Differentiate between unable to get the lease and the row being gone.

IF EXISTS (SELECT 1 FROM external_auth_links WHERE provider_id = arg_provider_id AND user_id = arg_user_id) THEN

RAISE EXCEPTION 'row is currently leased by another replica'

USING ERRCODE = 'check_violation',

CONSTRAINT = 'external_auth_link_active_lease';

END IF;

-- Row is gone, return nothing.

RETURN;

END;

$$;

COMMENT ON FUNCTION acquire_external_auth_link_refresh_lease(arg_provider_id text, arg_user_id uuid, timeout_ms bigint) IS 'Acquire a lease on the external auth link and return the row. If there is already an active lease, an exception is raised.';

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

)

)

-- Hourly runtime heartbeats: sum the runtime per day.

WHEN NEW.event_type IN ('hb_agent_runtime_v1') THEN

jsonb_build_object(

'runtime_ms',

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

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

)

END;

RETURN NEW;

END;

$$;

CREATE FUNCTION bump_chat_queue_version_on_queued_message_change() RETURNS trigger

LANGUAGE plpgsql

AS $$

DECLARE

changed_chat_id uuid;

BEGIN

IF TG_OP = 'DELETE' THEN

changed_chat_id = OLD.chat_id;

ELSE

changed_chat_id = NEW.chat_id;

END IF;

UPDATE chats

SET queue_version = snapshot_version

WHERE id = changed_chat_id;

IF TG_OP = 'DELETE' THEN

RETURN OLD;

END IF;

RETURN NEW;

END;

$$;

CREATE FUNCTION chat_message_search_text(content jsonb) RETURNS text

LANGUAGE sql IMMUTABLE PARALLEL SAFE

AS $$

SELECT CASE WHEN jsonb_typeof(content) = 'array' THEN (

SELECT string_agg(part->>'text', ' ' ORDER BY ordinality)

FROM jsonb_array_elements(content) WITH ORDINALITY AS t(part, ordinality)

WHERE part->>'type' = 'text'

) END

$$;

COMMENT ON FUNCTION chat_message_search_text(content jsonb) IS 'Extracts searchable content from chat_messages. Returns NULL for scalar JSON strings (content_version=0). Immutable as it is used in indexes.';

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;

$$;