-- 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:*'
);

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

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 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
    LANGUAGE plpgsql
    AS $$

DECLARE
BEGIN
	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 $$
BEGIN
    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
BEGIN
	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 insert_user_secret_fail_if_user_deleted() RETURNS trigger
    LANGUAGE plpgsql
    AS $$

DECLARE
BEGIN
	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_secret for deleted user';
		END IF;
	END IF;
	RETURN NEW;
END;
$$;

CREATE FUNCTION insert_user_skill_fail_if_user_deleted() RETURNS trigger
    LANGUAGE plpgsql
    AS $$

BEGIN
    PERFORM 1
    FROM users
    WHERE id = NEW.user_id
      AND deleted = true
    LIMIT 1;
    IF FOUND THEN
        RAISE EXCEPTION 'Cannot create user_skill for deleted user'
            USING ERRCODE = 'check_violation',
                  CONSTRAINT = 'user_skill_user_deleted';
    END IF;
    RETURN NEW;
END;
$$;

CREATE FUNCTION nullify_next_start_at_on_workspace_autostart_modification() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
BEGIN
	-- 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;
BEGIN
    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;
$$;

CREATE FUNCTION provisioner_tagset_contains(provisioner_tags tagset, job_tags tagset) RETURNS boolean
    LANGUAGE plpgsql
    AS $$
BEGIN
	RETURN CASE
		-- Special case for untagged provisioners, where only an exact match should count
		WHEN job_tags::jsonb = '{"scope": "organization", "owner": ""}'::jsonb THEN job_tags::jsonb = provisioner_tags::jsonb
		-- General case
		ELSE job_tags::jsonb <@ provisioner_tags::jsonb
	END;
END;
$$;

COMMENT ON FUNCTION provisioner_tagset_contains(provisioner_tags tagset, job_tags tagset) IS 'Returns true if the provisioner_tags contains the job_tags, or if the job_tags represents an untagged provisioner and the superset is exactly equal to the subset.';

CREATE FUNCTION record_user_status_change() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
    IF TG_OP = 'INSERT' OR OLD.status IS DISTINCT FROM NEW.status THEN
        INSERT INTO user_status_changes (
            user_id,
            new_status,
            changed_at
        ) VALUES (
            NEW.id,
            NEW.status,
            NEW.updated_at
        );
    END IF;

    IF OLD.deleted = FALSE AND NEW.deleted = TRUE THEN
        INSERT INTO user_deleted (
            user_id,
            deleted_at
        ) VALUES (
            NEW.id,
            NEW.updated_at
        );
    END IF;

    RETURN NEW;
END;
$$;

CREATE FUNCTION remove_organization_member_role() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
	-- Delete the role from all organization members that have it.
	-- TODO: When site wide custom roles are supported, if the
	--	organization_id is null, we should remove the role from the 'users'
	--	table instead.
	IF OLD.organization_id IS NOT NULL THEN
		UPDATE organization_members
		-- this is a noop if the role is not assigned to the member
		SET roles = array_remove(roles, OLD.name)
		WHERE
			-- Scope to the correct organization
			organization_members.organization_id = OLD.organization_id;
	END IF;
	RETURN OLD;
END;
$$;

CREATE TABLE ai_model_prices (
    provider text NOT NULL,
    model text NOT NULL,
    input_price bigint,
    output_price bigint,
    cache_read_price bigint,
    cache_write_price bigint,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT ai_model_prices_cache_read_price_check CHECK ((cache_read_price >= 0)),
    CONSTRAINT ai_model_prices_cache_write_price_check CHECK ((cache_write_price >= 0)),
    CONSTRAINT ai_model_prices_input_price_check CHECK ((input_price >= 0)),
    CONSTRAINT ai_model_prices_output_price_check CHECK ((output_price >= 0))
);

COMMENT ON TABLE ai_model_prices IS 'Per-model token prices used by AI Bridge to compute interception cost.';

CREATE TABLE ai_provider_keys (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    provider_id uuid NOT NULL,
    api_key text NOT NULL,
    api_key_key_id text,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL
);

COMMENT ON TABLE ai_provider_keys IS 'API keys associated with AI providers. Bedrock providers have zero keys (they authenticate via settings). OpenAI and Anthropic providers have one or more keys for failover.';

COMMENT ON COLUMN ai_provider_keys.api_key IS 'API key used to authenticate with the upstream AI provider. Encrypted at rest via dbcrypt when api_key_key_id is set.';

COMMENT ON COLUMN ai_provider_keys.api_key_key_id IS 'The ID of the key used to encrypt the provider API key. If this is NULL, the API key is not encrypted.';

CREATE TABLE ai_providers (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    type ai_provider_type NOT NULL,
    name text NOT NULL,
    display_name text,
    enabled boolean DEFAULT true NOT NULL,
    deleted boolean DEFAULT false NOT NULL,
    base_url text NOT NULL,
    settings text,
    settings_key_id text,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT ai_providers_name_check CHECK ((name ~ '^[a-z0-9]+(-[a-z0-9]+)*$'::text))
);

COMMENT ON TABLE ai_providers IS 'Runtime configuration for AI providers. Authoritative source for the provider set served by aibridged. Replaces deployment-time CODER_AIBRIDGE_* environment variables.';

COMMENT ON COLUMN ai_providers.display_name IS 'Optional human-readable label. When NULL, callers should fall back to name.';

COMMENT ON COLUMN ai_providers.deleted IS 'Soft delete flag. Soft-deleted rows are preserved for audit and FK history but do not block name reuse by future live rows.';

COMMENT ON COLUMN ai_providers.settings IS 'Encrypted JSON blob holding type-specific configuration (e.g. AWS Bedrock region, model, access key secret). Plaintext is a JSON object. NULL when no type-specific settings are required.';

COMMENT ON COLUMN ai_providers.settings_key_id IS 'The ID of the key used to encrypt settings. If this is NULL, settings is not encrypted.';

CREATE TABLE ai_seat_state (
    user_id uuid NOT NULL,
    first_used_at timestamp with time zone NOT NULL,
    last_used_at timestamp with time zone NOT NULL,
    last_event_type ai_seat_usage_reason NOT NULL,
    last_event_description text NOT NULL,
    updated_at timestamp with time zone NOT NULL
);

CREATE TABLE aibridge_interceptions (
    id uuid NOT NULL,
    initiator_id uuid NOT NULL,
    provider text NOT NULL,
    model text NOT NULL,
    started_at timestamp with time zone NOT NULL,
    metadata jsonb,
    ended_at timestamp with time zone,
    api_key_id text,
    client character varying(64) DEFAULT 'Unknown'::character varying,
    thread_parent_id uuid,
    thread_root_id uuid,
    client_session_id character varying(256),
    session_id text GENERATED ALWAYS AS (COALESCE(client_session_id, ((thread_root_id)::text)::character varying, ((id)::text)::character varying)) STORED NOT NULL,
    provider_name text DEFAULT ''::text NOT NULL,
    credential_kind credential_kind DEFAULT 'centralized'::credential_kind NOT NULL,
    credential_hint character varying(15) DEFAULT ''::character varying NOT NULL
);

COMMENT ON TABLE aibridge_interceptions IS 'Audit log of requests intercepted by AI Bridge';

COMMENT ON COLUMN aibridge_interceptions.initiator_id IS 'Relates to a users record, but FK is elided for performance.';

COMMENT ON COLUMN aibridge_interceptions.thread_parent_id IS 'The interception which directly caused this interception to occur, usually through an agentic loop or threaded conversation.';

COMMENT ON COLUMN aibridge_interceptions.thread_root_id IS 'The root interception of the thread that this interception belongs to.';

COMMENT ON COLUMN aibridge_interceptions.client_session_id IS 'The session ID supplied by the client (optional and not universally supported).';

COMMENT ON COLUMN aibridge_interceptions.session_id IS 'Groups related interceptions into a logical session. Determined by a priority chain: (1) client_session_id — an explicit session identifier supplied by the calling client (e.g. Claude Code); (2) thread_root_id — the root of an agentic thread detected by Bridge through tool-call correlation, used when the client does not supply its own session ID; (3) id — the interception''s own ID, used as a last resort so every interception belongs to exactly one session even if it is standalone. This is a generated column stored on disk so it can be indexed and joined without recomputing the COALESCE on every query.';

COMMENT ON COLUMN aibridge_interceptions.provider_name IS 'The provider instance name which may differ from provider when multiple instances of the same provider type exist.';

COMMENT ON COLUMN aibridge_interceptions.credential_kind IS 'How the request was authenticated: centralized or byok.';

COMMENT ON COLUMN aibridge_interceptions.credential_hint IS 'Masked credential identifier for audit (e.g. sk-a***efgh).';

CREATE TABLE aibridge_model_thoughts (
    interception_id uuid NOT NULL,
    content text NOT NULL,
    metadata jsonb,
    created_at timestamp with time zone NOT NULL
);

COMMENT ON TABLE aibridge_model_thoughts IS 'Audit log of model thinking in intercepted requests in AI Bridge';

CREATE TABLE aibridge_token_usages (
    id uuid NOT NULL,
    interception_id uuid NOT NULL,
    provider_response_id text NOT NULL,
    input_tokens bigint NOT NULL,
    output_tokens bigint NOT NULL,
    metadata jsonb,
    created_at timestamp with time zone NOT NULL,
    cache_read_input_tokens bigint DEFAULT 0 NOT NULL,
    cache_write_input_tokens bigint DEFAULT 0 NOT NULL
);

COMMENT ON TABLE aibridge_token_usages IS 'Audit log of tokens used by intercepted requests in AI Bridge';

COMMENT ON COLUMN aibridge_token_usages.provider_response_id IS 'The ID for the response in which the tokens were used, produced by the provider.';

CREATE TABLE aibridge_tool_usages (
    id uuid NOT NULL,
    interception_id uuid NOT NULL,
    provider_response_id text NOT NULL,
    server_url text,
    tool text NOT NULL,
    input text NOT NULL,
    injected boolean DEFAULT false NOT NULL,
    invocation_error text,
    metadata jsonb,
    created_at timestamp with time zone NOT NULL,
    provider_tool_call_id text
);

COMMENT ON TABLE aibridge_tool_usages IS 'Audit log of tool calls in intercepted requests in AI Bridge';

COMMENT ON COLUMN aibridge_tool_usages.provider_response_id IS 'The ID for the response in which the tools were used, produced by the provider.';

COMMENT ON COLUMN aibridge_tool_usages.server_url IS 'The name of the MCP server against which this tool was invoked. May be NULL, in which case the tool was defined by the client, not injected.';

COMMENT ON COLUMN aibridge_tool_usages.injected IS 'Whether this tool was injected; i.e. Bridge injected these tools into the request from an MCP server. If false it means a tool was defined by the client and already existed in the request (MCP or built-in).';

COMMENT ON COLUMN aibridge_tool_usages.invocation_error IS 'Only injected tools are invoked.';

CREATE TABLE aibridge_user_prompts (
    id uuid NOT NULL,
    interception_id uuid NOT NULL,
    provider_response_id text NOT NULL,
    prompt text NOT NULL,
    metadata jsonb,
    created_at timestamp with time zone NOT NULL
);

COMMENT ON TABLE aibridge_user_prompts IS 'Audit log of prompts used by intercepted requests in AI Bridge';

COMMENT ON COLUMN aibridge_user_prompts.provider_response_id IS 'The ID for the response to the given prompt, produced by the provider.';

CREATE TABLE api_keys (
    id text NOT NULL,
    hashed_secret bytea NOT NULL,
    user_id uuid NOT NULL,
    last_used timestamp with time zone NOT NULL,
    expires_at timestamp with time zone NOT NULL,
    created_at timestamp with time zone NOT NULL,
    updated_at timestamp with time zone NOT NULL,
    login_type login_type NOT NULL,
    lifetime_seconds bigint DEFAULT 86400 NOT NULL,
    ip_address inet DEFAULT '0.0.0.0'::inet NOT NULL,
    token_name text DEFAULT ''::text NOT NULL,
    scopes api_key_scope[] NOT NULL,
    allow_list text[] NOT NULL,
    CONSTRAINT api_keys_allow_list_not_empty CHECK ((array_length(allow_list, 1) > 0))
);

COMMENT ON COLUMN api_keys.hashed_secret IS 'hashed_secret contains a SHA256 hash of the key secret. This is considered a secret and MUST NOT be returned from the API as it is used for API key encryption in app proxying code.';

CREATE TABLE audit_logs (
    id uuid NOT NULL,
    "time" timestamp with time zone NOT NULL,
    user_id uuid NOT NULL,
    organization_id uuid NOT NULL,
    ip inet,
    user_agent character varying(256),
    resource_type resource_type NOT NULL,
    resource_id uuid NOT NULL,
    resource_target text NOT NULL,
    action audit_action NOT NULL,
    diff jsonb NOT NULL,
    status_code integer NOT NULL,
    additional_fields jsonb NOT NULL,
    request_id uuid NOT NULL,
    resource_icon text NOT NULL
);

CREATE TABLE boundary_logs (
    id uuid NOT NULL,
    session_id uuid NOT NULL,
    sequence_number integer NOT NULL,
    captured_at timestamp with time zone NOT NULL,
    created_at timestamp with time zone NOT NULL,
    proto text DEFAULT ''::text NOT NULL,
    method text DEFAULT ''::text NOT NULL,
    detail text DEFAULT ''::text NOT NULL,
    matched_rule text,
    CONSTRAINT boundary_logs_sequence_number_check CHECK ((sequence_number >= 0))
);

COMMENT ON TABLE boundary_logs IS 'Persisted boundary audit events. Each row is a single audit event processed by a Boundary proxy.';

COMMENT ON COLUMN boundary_logs.session_id IS 'The session ID generated by the Boundary process on startup. Groups all events from one invocation.';

COMMENT ON COLUMN boundary_logs.sequence_number IS 'Monotonically increasing integer assigned by Boundary, starting at 0 per session. Primary ordering key when Boundary is in use.';

COMMENT ON COLUMN boundary_logs.captured_at IS 'When the log was sent to the DB.';

COMMENT ON COLUMN boundary_logs.created_at IS 'When the event happened on the workspace.';

COMMENT ON COLUMN boundary_logs.proto IS 'The protocol of the audited action. e.g. http, dns, git, fs.';

COMMENT ON COLUMN boundary_logs.method IS 'The operation within the protocol. e.g. GET/POST for http, clone for git, A for dns, read/write for fs.';

COMMENT ON COLUMN boundary_logs.detail IS 'Protocol-specific detail. e.g. the full URL for http, the hostname for dns, the path for fs.';

COMMENT ON COLUMN boundary_logs.matched_rule IS 'The allow-list rule that matched. NULL when the request was denied; non-NULL implies the request was allowed.';

CREATE TABLE boundary_sessions (
    id uuid NOT NULL,
    workspace_agent_id uuid NOT NULL,
    confined_process_name text NOT NULL,
    started_at timestamp with time zone NOT NULL,
    updated_at timestamp with time zone NOT NULL
);

COMMENT ON TABLE boundary_sessions IS 'Boundary session metadata. Each row represents a single invocation of a Boundary process wrapping a confined agent.';

COMMENT ON COLUMN boundary_sessions.id IS 'The unique session ID generated by the Boundary process on startup.';

COMMENT ON COLUMN boundary_sessions.workspace_agent_id IS 'The workspace agent that this Boundary session is associated with.';

COMMENT ON COLUMN boundary_sessions.confined_process_name IS 'Name of the confined process (e.g. claude-code, codex, copilot).';

COMMENT ON COLUMN boundary_sessions.started_at IS 'Time when the first log for this session was received by coderd.';

COMMENT ON COLUMN boundary_sessions.updated_at IS 'Time when the session was last updated.';

CREATE TABLE boundary_usage_stats (
    replica_id uuid NOT NULL,
    unique_workspaces_count bigint DEFAULT 0 NOT NULL,
    unique_users_count bigint DEFAULT 0 NOT NULL,
    allowed_requests bigint DEFAULT 0 NOT NULL,
    denied_requests bigint DEFAULT 0 NOT NULL,
    window_start timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL
);

COMMENT ON TABLE boundary_usage_stats IS 'Per-replica boundary usage statistics for telemetry aggregation.';

COMMENT ON COLUMN boundary_usage_stats.replica_id IS 'The unique identifier of the replica reporting stats.';

COMMENT ON COLUMN boundary_usage_stats.unique_workspaces_count IS 'Count of unique workspaces that used boundary on this replica.';

COMMENT ON COLUMN boundary_usage_stats.unique_users_count IS 'Count of unique users that used boundary on this replica.';

COMMENT ON COLUMN boundary_usage_stats.allowed_requests IS 'Total allowed requests through boundary on this replica.';

COMMENT ON COLUMN boundary_usage_stats.denied_requests IS 'Total denied requests through boundary on this replica.';

COMMENT ON COLUMN boundary_usage_stats.window_start IS 'Start of the time window for these stats, set on first flush after reset.';

COMMENT ON COLUMN boundary_usage_stats.updated_at IS 'Timestamp of the last update to this row.';

CREATE TABLE chat_debug_runs (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    chat_id uuid NOT NULL,
    root_chat_id uuid,
    parent_chat_id uuid,
    model_config_id uuid,
    trigger_message_id bigint,
    history_tip_message_id bigint,
    kind text NOT NULL,
    status text NOT NULL,
    provider text,
    model text,
    summary jsonb DEFAULT '{}'::jsonb NOT NULL,
    started_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    finished_at timestamp with time zone
);

CREATE TABLE chat_debug_steps (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    run_id uuid NOT NULL,
    chat_id uuid NOT NULL,
    step_number integer NOT NULL,
    operation text NOT NULL,
    status text NOT NULL,
    history_tip_message_id bigint,
    assistant_message_id bigint,
    normalized_request jsonb NOT NULL,
    normalized_response jsonb,
    usage jsonb,
    attempts jsonb DEFAULT '[]'::jsonb NOT NULL,
    error jsonb,
    metadata jsonb DEFAULT '{}'::jsonb NOT NULL,
    started_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    finished_at timestamp with time zone
);

CREATE TABLE chat_diff_statuses (
    chat_id uuid NOT NULL,
    url text,
    pull_request_state text,
    changes_requested boolean DEFAULT false NOT NULL,
    additions integer DEFAULT 0 NOT NULL,
    deletions integer DEFAULT 0 NOT NULL,
    changed_files integer DEFAULT 0 NOT NULL,
    refreshed_at timestamp with time zone,
    stale_at timestamp with time zone DEFAULT now() NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    git_branch text DEFAULT ''::text NOT NULL,
    git_remote_origin text DEFAULT ''::text NOT NULL,
    pull_request_title text DEFAULT ''::text NOT NULL,
    pull_request_draft boolean DEFAULT false NOT NULL,
    author_login text,
    author_avatar_url text,
    base_branch text,
    pr_number integer,
    commits integer,
    approved boolean,
    reviewer_count integer,
    head_branch text
);

CREATE TABLE chat_file_links (
    chat_id uuid NOT NULL,
    file_id uuid NOT NULL
);

CREATE TABLE chat_files (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    owner_id uuid NOT NULL,
    organization_id uuid NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    name text DEFAULT ''::text NOT NULL,
    mimetype text NOT NULL,
    data bytea NOT NULL
);

CREATE TABLE chat_messages (
    id bigint NOT NULL,
    chat_id uuid NOT NULL,
    model_config_id uuid,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    role chat_message_role NOT NULL,
    content jsonb,
    visibility chat_message_visibility DEFAULT 'both'::chat_message_visibility NOT NULL,
    input_tokens bigint,
    output_tokens bigint,
    total_tokens bigint,
    reasoning_tokens bigint,
    cache_creation_tokens bigint,
    cache_read_tokens bigint,
    context_limit bigint,
    compressed boolean DEFAULT false NOT NULL,
    created_by uuid,
    content_version smallint NOT NULL,
    total_cost_micros bigint,
    runtime_ms bigint,
    deleted boolean DEFAULT false NOT NULL,
    provider_response_id text,
    api_key_id text
);

CREATE SEQUENCE chat_messages_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE chat_messages_id_seq OWNED BY chat_messages.id;

CREATE TABLE chat_model_configs (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    provider text NOT NULL,
    model text NOT NULL,
    display_name text DEFAULT ''::text NOT NULL,
    created_by uuid,
    updated_by uuid,
    enabled boolean DEFAULT true NOT NULL,
    is_default boolean DEFAULT false NOT NULL,
    deleted boolean DEFAULT false NOT NULL,
    deleted_at timestamp with time zone,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    context_limit bigint NOT NULL,
    compression_threshold integer NOT NULL,
    options jsonb DEFAULT '{}'::jsonb NOT NULL,
    ai_provider_id uuid,
    CONSTRAINT chat_model_configs_ai_provider_required_when_active CHECK (((deleted = true) OR (ai_provider_id IS NOT NULL))),
    CONSTRAINT chat_model_configs_compression_threshold_check CHECK (((compression_threshold >= 0) AND (compression_threshold <= 100))),
    CONSTRAINT chat_model_configs_context_limit_check CHECK ((context_limit > 0))
);

CREATE TABLE chat_queued_messages (
    id bigint NOT NULL,
    chat_id uuid NOT NULL,
    content jsonb NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    model_config_id uuid,
    api_key_id text
);

CREATE SEQUENCE chat_queued_messages_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE chat_queued_messages_id_seq OWNED BY chat_queued_messages.id;

CREATE TABLE chat_usage_limit_config (
    id bigint NOT NULL,
    singleton boolean DEFAULT true NOT NULL,
    enabled boolean DEFAULT false NOT NULL,
    default_limit_micros bigint DEFAULT 0 NOT NULL,
    period text DEFAULT 'month'::text NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT chat_usage_limit_config_default_limit_micros_check CHECK ((default_limit_micros >= 0)),
    CONSTRAINT chat_usage_limit_config_period_check CHECK ((period = ANY (ARRAY['day'::text, 'week'::text, 'month'::text]))),
    CONSTRAINT chat_usage_limit_config_singleton_check CHECK (singleton)
);

CREATE SEQUENCE chat_usage_limit_config_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE chat_usage_limit_config_id_seq OWNED BY chat_usage_limit_config.id;

CREATE TABLE chats (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    owner_id uuid NOT NULL,
    workspace_id uuid,
    title text DEFAULT 'New Chat'::text NOT NULL,
    status chat_status DEFAULT 'waiting'::chat_status NOT NULL,
    worker_id uuid,
    started_at timestamp with time zone,
    heartbeat_at timestamp with time zone,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    parent_chat_id uuid,
    root_chat_id uuid,
    last_model_config_id uuid NOT NULL,
    archived boolean DEFAULT false NOT NULL,
    last_error jsonb,
    mode chat_mode,
    mcp_server_ids uuid[] DEFAULT '{}'::uuid[] NOT NULL,
    labels jsonb DEFAULT '{}'::jsonb NOT NULL,
    build_id uuid,
    agent_id uuid,
    pin_order integer DEFAULT 0 NOT NULL,
    last_read_message_id bigint,
    last_injected_context jsonb,
    dynamic_tools jsonb,
    organization_id uuid NOT NULL,
    plan_mode chat_plan_mode,
    client_type chat_client_type DEFAULT 'api'::chat_client_type NOT NULL,
    last_turn_summary text,
    user_acl jsonb DEFAULT '{}'::jsonb NOT NULL,
    group_acl jsonb DEFAULT '{}'::jsonb NOT NULL,
    CONSTRAINT chat_acl_only_on_root_chats CHECK ((((parent_chat_id IS NULL) AND (root_chat_id IS NULL)) OR ((user_acl = '{}'::jsonb) AND (group_acl = '{}'::jsonb)))),
    CONSTRAINT chat_group_acl_not_null_jsonb CHECK (((group_acl IS NOT NULL) AND (jsonb_typeof(group_acl) = 'object'::text))),
    CONSTRAINT chat_user_acl_not_null_jsonb CHECK (((user_acl IS NOT NULL) AND (jsonb_typeof(user_acl) = 'object'::text))),
    CONSTRAINT chats_pin_order_archived_check CHECK (((pin_order = 0) OR (archived = false))),
    CONSTRAINT chats_pin_order_parent_check CHECK (((pin_order = 0) OR (parent_chat_id IS NULL)))
);

CREATE TABLE users (
    id uuid NOT NULL,
    email text NOT NULL,
    username text DEFAULT ''::text NOT NULL,
    hashed_password bytea NOT NULL,
    created_at timestamp with time zone NOT NULL,
    updated_at timestamp with time zone NOT NULL,
    status user_status DEFAULT 'dormant'::user_status NOT NULL,
    rbac_roles text[] DEFAULT '{}'::text[] NOT NULL,
    login_type login_type DEFAULT 'password'::login_type NOT NULL,
    avatar_url text DEFAULT ''::text NOT NULL,
    deleted boolean DEFAULT false NOT NULL,
    last_seen_at timestamp without time zone DEFAULT '0001-01-01 00:00:00'::timestamp without time zone NOT NULL,
    quiet_hours_schedule text DEFAULT ''::text NOT NULL,
    name text DEFAULT ''::text NOT NULL,
    github_com_user_id bigint,
    hashed_one_time_passcode bytea,
    one_time_passcode_expires_at timestamp with time zone,
    is_system boolean DEFAULT false NOT NULL,
    is_service_account boolean DEFAULT false NOT NULL,
    chat_spend_limit_micros bigint,
    CONSTRAINT one_time_passcode_set CHECK ((((hashed_one_time_passcode IS NULL) AND (one_time_passcode_expires_at IS NULL)) OR ((hashed_one_time_passcode IS NOT NULL) AND (one_time_passcode_expires_at IS NOT NULL)))),
    CONSTRAINT users_chat_spend_limit_micros_check CHECK (((chat_spend_limit_micros IS NULL) OR (chat_spend_limit_micros > 0))),
    CONSTRAINT users_email_not_empty CHECK (((is_service_account = true) = (email = ''::text))),
    CONSTRAINT users_service_account_login_type CHECK (((is_service_account = false) OR (login_type = 'none'::login_type))),
    CONSTRAINT users_username_min_length CHECK ((length(username) >= 1))
);

COMMENT ON COLUMN users.quiet_hours_schedule IS 'Daily (!) cron schedule (with optional CRON_TZ) signifying the start of the user''s quiet hours. If empty, the default quiet hours on the instance is used instead.';

COMMENT ON COLUMN users.name IS 'Name of the Coder user';

COMMENT ON COLUMN users.github_com_user_id IS 'The GitHub.com numerical user ID. It is used to check if the user has starred the Coder repository. It is also used for filtering users in the users list CLI command, and may become more widely used in the future.';

COMMENT ON COLUMN users.hashed_one_time_passcode IS 'A hash of the one-time-passcode given to the user.';

COMMENT ON COLUMN users.one_time_passcode_expires_at IS 'The time when the one-time-passcode expires.';

COMMENT ON COLUMN users.is_system IS 'Determines if a user is a system user, and therefore cannot login or perform normal actions';

COMMENT ON COLUMN users.is_service_account IS 'Determines if a user is an admin-managed account that cannot login';

CREATE VIEW visible_users AS
 SELECT users.id,
    users.username,
    users.name,
    users.avatar_url
   FROM users;

COMMENT ON VIEW visible_users IS 'Visible fields of users are allowed to be joined with other tables for including context of other resources.';

CREATE VIEW chats_expanded AS
 SELECT c.id,
    c.owner_id,
    c.workspace_id,
    c.title,
    c.status,
    c.worker_id,
    c.started_at,
    c.heartbeat_at,
    c.created_at,
    c.updated_at,
    c.parent_chat_id,
    c.root_chat_id,
    c.last_model_config_id,
    c.archived,
    c.last_error,
    c.mode,
    c.mcp_server_ids,
    c.labels,
    c.build_id,
    c.agent_id,
    c.pin_order,
    c.last_read_message_id,
    c.last_injected_context,
    c.dynamic_tools,
    c.organization_id,
    c.plan_mode,
    c.client_type,
    c.last_turn_summary,
    COALESCE(root.user_acl, c.user_acl) AS user_acl,
    COALESCE(root.group_acl, c.group_acl) AS group_acl,
    owner.username AS owner_username,
    owner.name AS owner_name
   FROM ((chats c
     LEFT JOIN chats root ON ((root.id = COALESCE(c.root_chat_id, c.parent_chat_id))))
     JOIN visible_users owner ON ((owner.id = c.owner_id)));

CREATE TABLE connection_logs (
    id uuid NOT NULL,
    connect_time timestamp with time zone NOT NULL,
    organization_id uuid NOT NULL,
    workspace_owner_id uuid NOT NULL,
    workspace_id uuid NOT NULL,
    workspace_name text NOT NULL,
    agent_name text NOT NULL,
    type connection_type NOT NULL,
    ip inet,
    code integer,
    user_agent text,
    user_id uuid,
    slug_or_port text,
    connection_id uuid,
    disconnect_time timestamp with time zone,
    disconnect_reason text
);

COMMENT ON COLUMN connection_logs.code IS 'Either the HTTP status code of the web request, or the exit code of an SSH connection. For non-web connections, this is Null until we receive a disconnect event for the same connection_id.';

COMMENT ON COLUMN connection_logs.user_agent IS 'Null for SSH events. For web connections, this is the User-Agent header from the request.';

COMMENT ON COLUMN connection_logs.user_id IS 'Null for SSH events. For web connections, this is the ID of the user that made the request.';

COMMENT ON COLUMN connection_logs.slug_or_port IS 'Null for SSH events. For web connections, this is the slug of the app or the port number being forwarded.';

COMMENT ON COLUMN connection_logs.connection_id IS 'The SSH connection ID. Used to correlate connections and disconnections. As it originates from the agent, it is not guaranteed to be unique.';

COMMENT ON COLUMN connection_logs.disconnect_time IS 'The time the connection was closed. Null for web connections. For other connections, this is null until we receive a disconnect event for the same connection_id.';

COMMENT ON COLUMN connection_logs.disconnect_reason IS 'The reason the connection was closed. Null for web connections. For other connections, this is null until we receive a disconnect event for the same connection_id.';

CREATE TABLE crypto_keys (
    feature crypto_key_feature NOT NULL,
    sequence integer NOT NULL,
    secret text,
    secret_key_id text,
    starts_at timestamp with time zone NOT NULL,
    deletes_at timestamp with time zone
);

CREATE TABLE custom_roles (
    name text NOT NULL,
    display_name text NOT NULL,
    site_permissions jsonb DEFAULT '[]'::jsonb NOT NULL,
    org_permissions jsonb DEFAULT '{}'::jsonb NOT NULL,
    user_permissions jsonb DEFAULT '[]'::jsonb NOT NULL,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    organization_id uuid,
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    is_system boolean DEFAULT false NOT NULL,
    member_permissions jsonb DEFAULT '[]'::jsonb NOT NULL,
    CONSTRAINT organization_id_not_zero CHECK ((organization_id <> '00000000-0000-0000-0000-000000000000'::uuid))
);

COMMENT ON TABLE custom_roles IS 'Custom roles allow dynamic roles expanded at runtime';

COMMENT ON COLUMN custom_roles.organization_id IS 'Roles can optionally be scoped to an organization';

COMMENT ON COLUMN custom_roles.id IS 'Custom roles ID is used purely for auditing purposes. Name is a better unique identifier.';

COMMENT ON COLUMN custom_roles.is_system IS 'System roles are managed by Coder and cannot be modified or deleted by users.';

CREATE TABLE dbcrypt_keys (
    number integer NOT NULL,
    active_key_digest text,
    revoked_key_digest text,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    revoked_at timestamp with time zone,
    test text NOT NULL
);

COMMENT ON TABLE dbcrypt_keys IS 'A table used to store the keys used to encrypt the database.';

COMMENT ON COLUMN dbcrypt_keys.number IS 'An integer used to identify the key.';

COMMENT ON COLUMN dbcrypt_keys.active_key_digest IS 'If the key is active, the digest of the active key.';

COMMENT ON COLUMN dbcrypt_keys.revoked_key_digest IS 'If the key has been revoked, the digest of the revoked key.';

COMMENT ON COLUMN dbcrypt_keys.created_at IS 'The time at which the key was created.';

COMMENT ON COLUMN dbcrypt_keys.revoked_at IS 'The time at which the key was revoked.';

COMMENT ON COLUMN dbcrypt_keys.test IS 'A column used to test the encryption.';

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

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

CREATE TABLE files (
    hash character varying(64) NOT NULL,
    created_at timestamp with time zone NOT NULL,
    created_by uuid NOT NULL,
    mimetype character varying(64) NOT NULL,
    data bytea NOT NULL,
    id uuid DEFAULT gen_random_uuid() NOT NULL
);

CREATE TABLE gitsshkeys (
    user_id uuid NOT NULL,
    created_at timestamp with time zone NOT NULL,
    updated_at timestamp with time zone NOT NULL,
    private_key text NOT NULL,
    public_key text NOT NULL
);

CREATE TABLE group_ai_budgets (
    group_id uuid NOT NULL,
    spend_limit_micros bigint NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT group_ai_budgets_spend_limit_micros_check CHECK ((spend_limit_micros >= 0))
);

COMMENT ON TABLE group_ai_budgets IS 'Per-group AI spend limit applied to each member of the group. No row means no budget is enforced.';

CREATE TABLE group_members (
    user_id uuid NOT NULL,
    group_id uuid NOT NULL
);

CREATE TABLE groups (
    id uuid NOT NULL,
    name text NOT NULL,
    organization_id uuid NOT NULL,
    avatar_url text DEFAULT ''::text NOT NULL,
    quota_allowance integer DEFAULT 0 NOT NULL,
    display_name text DEFAULT ''::text NOT NULL,
    source group_source DEFAULT 'user'::group_source NOT NULL,
    chat_spend_limit_micros bigint,
    CONSTRAINT groups_chat_spend_limit_micros_check CHECK (((chat_spend_limit_micros IS NULL) OR (chat_spend_limit_micros > 0)))
);

COMMENT ON COLUMN groups.display_name IS 'Display name is a custom, human-friendly group name that user can set. This is not required to be unique and can be the empty string.';

COMMENT ON COLUMN groups.source IS 'Source indicates how the group was created. It can be created by a user manually, or through some system process like OIDC group sync.';

CREATE TABLE organization_members (
    user_id uuid NOT NULL,
    organization_id uuid NOT NULL,
    created_at timestamp with time zone NOT NULL,
    updated_at timestamp with time zone NOT NULL,
    roles text[] DEFAULT '{}'::text[] NOT NULL
);

CREATE VIEW group_members_expanded AS
 WITH all_members AS (
         SELECT group_members.user_id,
            group_members.group_id
           FROM group_members
        UNION
         SELECT organization_members.user_id,
            organization_members.organization_id AS group_id
           FROM organization_members
        )
 SELECT users.id AS user_id,
    users.email AS user_email,
    users.username AS user_username,
    users.hashed_password AS user_hashed_password,
    users.created_at AS user_created_at,
    users.updated_at AS user_updated_at,
    users.status AS user_status,
    users.rbac_roles AS user_rbac_roles,
    users.login_type AS user_login_type,
    users.avatar_url AS user_avatar_url,
    users.deleted AS user_deleted,
    users.last_seen_at AS user_last_seen_at,
    users.quiet_hours_schedule AS user_quiet_hours_schedule,
    users.name AS user_name,
    users.github_com_user_id AS user_github_com_user_id,
    users.is_system AS user_is_system,
    users.is_service_account AS user_is_service_account,
    groups.organization_id,
    groups.name AS group_name,
    all_members.group_id
   FROM ((all_members
     JOIN users ON ((users.id = all_members.user_id)))
     JOIN groups ON ((groups.id = all_members.group_id)))
  WHERE (users.deleted = false);

CREATE TABLE inbox_notifications (
    id uuid NOT NULL,
    user_id uuid NOT NULL,
    template_id uuid NOT NULL,
    targets uuid[],
    title text NOT NULL,
    content text NOT NULL,
    icon text NOT NULL,
    actions jsonb NOT NULL,
    read_at timestamp with time zone,
    created_at timestamp with time zone DEFAULT now() NOT NULL
);

CREATE TABLE jfrog_xray_scans (
    agent_id uuid NOT NULL,
    workspace_id uuid NOT NULL,
    critical integer DEFAULT 0 NOT NULL,
    high integer DEFAULT 0 NOT NULL,
    medium integer DEFAULT 0 NOT NULL,
    results_url text DEFAULT ''::text NOT NULL
);

CREATE TABLE licenses (
    id integer NOT NULL,
    uploaded_at timestamp with time zone NOT NULL,
    jwt text NOT NULL,
    exp timestamp with time zone NOT NULL,
    uuid uuid NOT NULL
);

COMMENT ON COLUMN licenses.exp IS 'exp tracks the claim of the same name in the JWT, and we include it here so that we can easily query for licenses that have not yet expired.';

CREATE SEQUENCE licenses_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE licenses_id_seq OWNED BY licenses.id;

CREATE TABLE mcp_server_configs (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    display_name text NOT NULL,
    slug text NOT NULL,
    description text DEFAULT ''::text NOT NULL,
    icon_url text DEFAULT ''::text NOT NULL,
    transport text DEFAULT 'streamable_http'::text NOT NULL,
    url text NOT NULL,
    auth_type text DEFAULT 'none'::text NOT NULL,
    oauth2_client_id text DEFAULT ''::text NOT NULL,
    oauth2_client_secret text DEFAULT ''::text NOT NULL,
    oauth2_client_secret_key_id text,
    oauth2_auth_url text DEFAULT ''::text NOT NULL,
    oauth2_token_url text DEFAULT ''::text NOT NULL,
    oauth2_scopes text DEFAULT ''::text NOT NULL,
    api_key_header text DEFAULT 'Authorization'::text NOT NULL,
    api_key_value text DEFAULT ''::text NOT NULL,
    api_key_value_key_id text,
    custom_headers text DEFAULT '{}'::text NOT NULL,
    custom_headers_key_id text,
    tool_allow_list text[] DEFAULT '{}'::text[] NOT NULL,
    tool_deny_list text[] DEFAULT '{}'::text[] NOT NULL,
    availability text DEFAULT 'default_off'::text NOT NULL,
    enabled boolean DEFAULT false NOT NULL,
    created_by uuid,
    updated_by uuid,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    model_intent boolean DEFAULT false NOT NULL,
    allow_in_plan_mode boolean DEFAULT false NOT NULL,
    forward_coder_headers boolean DEFAULT false NOT NULL,
    CONSTRAINT mcp_server_configs_auth_type_check CHECK ((auth_type = ANY (ARRAY['none'::text, 'oauth2'::text, 'api_key'::text, 'custom_headers'::text, 'user_oidc'::text]))),
    CONSTRAINT mcp_server_configs_availability_check CHECK ((availability = ANY (ARRAY['force_on'::text, 'default_on'::text, 'default_off'::text]))),
    CONSTRAINT mcp_server_configs_transport_check CHECK ((transport = ANY (ARRAY['streamable_http'::text, 'sse'::text])))
);

CREATE TABLE mcp_server_user_tokens (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    mcp_server_config_id uuid NOT NULL,
    user_id uuid NOT NULL,
    access_token text NOT NULL,
    access_token_key_id text,
    refresh_token text DEFAULT ''::text NOT NULL,
    refresh_token_key_id text,
    token_type text DEFAULT 'Bearer'::text NOT NULL,
    expiry timestamp with time zone,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL
);

CREATE TABLE notification_messages (
    id uuid NOT NULL,
    notification_template_id uuid NOT NULL,
    user_id uuid NOT NULL,
    method notification_method NOT NULL,
    status notification_message_status DEFAULT 'pending'::notification_message_status NOT NULL,
    status_reason text,
    created_by text NOT NULL,
    payload jsonb NOT NULL,
    attempt_count integer DEFAULT 0,
    targets uuid[],
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at timestamp with time zone,
    leased_until timestamp with time zone,
    next_retry_after timestamp with time zone,
    queued_seconds double precision,
    dedupe_hash text
);

COMMENT ON COLUMN notification_messages.dedupe_hash IS 'Auto-generated by insert/update trigger, used to prevent duplicate notifications from being enqueued on the same day';

CREATE TABLE notification_preferences (
    user_id uuid NOT NULL,
    notification_template_id uuid NOT NULL,
    disabled boolean DEFAULT false NOT NULL,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TABLE notification_report_generator_logs (
    notification_template_id uuid NOT NULL,
    last_generated_at timestamp with time zone NOT NULL
);

COMMENT ON TABLE notification_report_generator_logs IS 'Log of generated reports for users.';

CREATE TABLE notification_templates (
    id uuid NOT NULL,
    name text NOT NULL,
    title_template text NOT NULL,
    body_template text NOT NULL,
    actions jsonb,
    "group" text,
    method notification_method,
    kind notification_template_kind DEFAULT 'system'::notification_template_kind NOT NULL,
    enabled_by_default boolean DEFAULT true NOT NULL
);

COMMENT ON TABLE notification_templates IS 'Templates from which to create notification messages.';

COMMENT ON COLUMN notification_templates.method IS 'NULL defers to the deployment-level method';

CREATE TABLE oauth2_provider_app_codes (
    id uuid NOT NULL,
    created_at timestamp with time zone NOT NULL,
    expires_at timestamp with time zone NOT NULL,
    secret_prefix bytea NOT NULL,
    hashed_secret bytea NOT NULL,
    user_id uuid NOT NULL,
    app_id uuid NOT NULL,
    resource_uri text,
    code_challenge text,
    code_challenge_method text,
    state_hash text,
    redirect_uri text
);

COMMENT ON TABLE oauth2_provider_app_codes IS 'Codes are meant to be exchanged for access tokens.';

COMMENT ON COLUMN oauth2_provider_app_codes.resource_uri IS 'RFC 8707 resource parameter for audience restriction';

COMMENT ON COLUMN oauth2_provider_app_codes.code_challenge IS 'PKCE code challenge for public clients';

COMMENT ON COLUMN oauth2_provider_app_codes.code_challenge_method IS 'PKCE challenge method (S256)';

COMMENT ON COLUMN oauth2_provider_app_codes.state_hash IS 'SHA-256 hash of the OAuth2 state parameter, stored to prevent state reflection attacks.';

COMMENT ON COLUMN oauth2_provider_app_codes.redirect_uri IS 'The redirect_uri provided during authorization, to be verified during token exchange (RFC 6749 §4.1.3).';

CREATE TABLE oauth2_provider_app_secrets (
    id uuid NOT NULL,
    created_at timestamp with time zone NOT NULL,
    last_used_at timestamp with time zone,
    hashed_secret bytea NOT NULL,
    display_secret text NOT NULL,
    app_id uuid NOT NULL,
    secret_prefix bytea NOT NULL
);

COMMENT ON COLUMN oauth2_provider_app_secrets.display_secret IS 'The tail end of the original secret so secrets can be differentiated.';

CREATE TABLE oauth2_provider_app_tokens (
    id uuid NOT NULL,
    created_at timestamp with time zone NOT NULL,
    expires_at timestamp with time zone NOT NULL,
    hash_prefix bytea NOT NULL,
    refresh_hash bytea NOT NULL,
    app_secret_id uuid NOT NULL,
    api_key_id text NOT NULL,
    audience text,
    user_id uuid NOT NULL
);

COMMENT ON COLUMN oauth2_provider_app_tokens.refresh_hash IS 'Refresh tokens provide a way to refresh an access token (API key). An expired API key can be refreshed if this token is not yet expired, meaning this expiry can outlive an API key.';

COMMENT ON COLUMN oauth2_provider_app_tokens.audience IS 'Token audience binding from resource parameter';

COMMENT ON COLUMN oauth2_provider_app_tokens.user_id IS 'Denormalized user ID for performance optimization in authorization checks';

CREATE TABLE oauth2_provider_apps (
    id uuid NOT NULL,
    created_at timestamp with time zone NOT NULL,
    updated_at timestamp with time zone NOT NULL,
    name character varying(64) NOT NULL,
    icon character varying(256) NOT NULL,
    callback_url text NOT NULL,
    redirect_uris text[],
    client_type text DEFAULT 'confidential'::text,
    dynamically_registered boolean DEFAULT false,
    client_id_issued_at timestamp with time zone DEFAULT now(),
    client_secret_expires_at timestamp with time zone,
    grant_types text[] DEFAULT '{authorization_code,refresh_token}'::text[],
    response_types text[] DEFAULT '{code}'::text[],
    token_endpoint_auth_method text DEFAULT 'client_secret_basic'::text,
    scope text DEFAULT ''::text,
    contacts text[],
    client_uri text,
    logo_uri text,
    tos_uri text,
    policy_uri text,
    jwks_uri text,
    jwks jsonb,
    software_id text,
    software_version text,
    registration_access_token bytea,
    registration_client_uri text
);

COMMENT ON TABLE oauth2_provider_apps IS 'A table used to configure apps that can use Coder as an OAuth2 provider, the reverse of what we are calling external authentication.';

COMMENT ON COLUMN oauth2_provider_apps.redirect_uris IS 'List of valid redirect URIs for the application';

COMMENT ON COLUMN oauth2_provider_apps.client_type IS 'OAuth2 client type: confidential or public';

COMMENT ON COLUMN oauth2_provider_apps.dynamically_registered IS 'Whether this app was created via dynamic client registration';

COMMENT ON COLUMN oauth2_provider_apps.client_id_issued_at IS 'RFC 7591: Timestamp when client_id was issued';

COMMENT ON COLUMN oauth2_provider_apps.client_secret_expires_at IS 'RFC 7591: Timestamp when client_secret expires (null for non-expiring)';

COMMENT ON COLUMN oauth2_provider_apps.grant_types IS 'RFC 7591: Array of grant types the client is allowed to use';

COMMENT ON COLUMN oauth2_provider_apps.response_types IS 'RFC 7591: Array of response types the client supports';

COMMENT ON COLUMN oauth2_provider_apps.token_endpoint_auth_method IS 'RFC 7591: Authentication method for token endpoint';

COMMENT ON COLUMN oauth2_provider_apps.scope IS 'RFC 7591: Space-delimited scope values the client can request';

COMMENT ON COLUMN oauth2_provider_apps.contacts IS 'RFC 7591: Array of email addresses for responsible parties';

COMMENT ON COLUMN oauth2_provider_apps.client_uri IS 'RFC 7591: URL of the client home page';

COMMENT ON COLUMN oauth2_provider_apps.logo_uri IS 'RFC 7591: URL of the client logo image';

COMMENT ON COLUMN oauth2_provider_apps.tos_uri IS 'RFC 7591: URL of the client terms of service';

COMMENT ON COLUMN oauth2_provider_apps.policy_uri IS 'RFC 7591: URL of the client privacy policy';

COMMENT ON COLUMN oauth2_provider_apps.jwks_uri IS 'RFC 7591: URL of the client JSON Web Key Set';

COMMENT ON COLUMN oauth2_provider_apps.jwks IS 'RFC 7591: JSON Web Key Set document value';

COMMENT ON COLUMN oauth2_provider_apps.software_id IS 'RFC 7591: Identifier for the client software';

COMMENT ON COLUMN oauth2_provider_apps.software_version IS 'RFC 7591: Version of the client software';

COMMENT ON COLUMN oauth2_provider_apps.registration_access_token IS 'RFC 7592: Hashed registration access token for client management';

COMMENT ON COLUMN oauth2_provider_apps.registration_client_uri IS 'RFC 7592: URI for client configuration endpoint';

CREATE TABLE organizations (
    id uuid NOT NULL,
    name text NOT NULL,
    description text NOT NULL,
    created_at timestamp with time zone NOT NULL,
    updated_at timestamp with time zone NOT NULL,
    is_default boolean DEFAULT false NOT NULL,
    display_name text NOT NULL,
    icon text DEFAULT ''::text NOT NULL,
    deleted boolean DEFAULT false NOT NULL,
    shareable_workspace_owners shareable_workspace_owners DEFAULT 'everyone'::shareable_workspace_owners NOT NULL
);

COMMENT ON COLUMN organizations.shareable_workspace_owners IS 'Controls whose workspaces can be shared: none, everyone, or service_accounts.';

CREATE TABLE parameter_schemas (
    id uuid NOT NULL,
    created_at timestamp with time zone NOT NULL,
    job_id uuid NOT NULL,
    name character varying(64) NOT NULL,
    description character varying(8192) DEFAULT ''::character varying NOT NULL,
    default_source_scheme parameter_source_scheme NOT NULL,
    default_source_value text NOT NULL,
    allow_override_source boolean NOT NULL,
    default_destination_scheme parameter_destination_scheme NOT NULL,
    allow_override_destination boolean NOT NULL,
    default_refresh text NOT NULL,
    redisplay_value boolean NOT NULL,
    validation_error character varying(256) NOT NULL,
    validation_condition character varying(512) NOT NULL,
    validation_type_system parameter_type_system NOT NULL,
    validation_value_type character varying(64) NOT NULL,
    index integer NOT NULL
);

CREATE TABLE parameter_values (
    id uuid NOT NULL,
    created_at timestamp with time zone NOT NULL,
    updated_at timestamp with time zone NOT NULL,
    scope parameter_scope NOT NULL,
    scope_id uuid NOT NULL,
    name character varying(64) NOT NULL,
    source_scheme parameter_source_scheme NOT NULL,
    source_value text NOT NULL,
    destination_scheme parameter_destination_scheme NOT NULL
);

CREATE TABLE provisioner_daemons (
    id uuid NOT NULL,
    created_at timestamp with time zone NOT NULL,
    name character varying(64) NOT NULL,
    provisioners provisioner_type[] NOT NULL,
    replica_id uuid,
    tags jsonb DEFAULT '{}'::jsonb NOT NULL,
    last_seen_at timestamp with time zone,
    version text DEFAULT ''::text NOT NULL,
    api_version text DEFAULT '1.0'::text NOT NULL,
    organization_id uuid NOT NULL,
    key_id uuid NOT NULL
);

COMMENT ON COLUMN provisioner_daemons.api_version IS 'The API version of the provisioner daemon';

CREATE TABLE provisioner_job_logs (
    job_id uuid NOT NULL,
    created_at timestamp with time zone NOT NULL,
    source log_source NOT NULL,
    level log_level NOT NULL,
    stage character varying(128) NOT NULL,
    output character varying(1024) NOT NULL,
    id bigint NOT NULL
);

CREATE SEQUENCE provisioner_job_logs_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE provisioner_job_logs_id_seq OWNED BY provisioner_job_logs.id;

CREATE VIEW provisioner_job_stats AS
SELECT
    NULL::uuid AS job_id,
    NULL::provisioner_job_status AS job_status,
    NULL::uuid AS workspace_id,
    NULL::uuid AS worker_id,
    NULL::text AS error,
    NULL::text AS error_code,
    NULL::timestamp with time zone AS updated_at,
    NULL::double precision AS queued_secs,
    NULL::double precision AS completion_secs,
    NULL::double precision AS canceled_secs,
    NULL::double precision AS init_secs,
    NULL::double precision AS plan_secs,
    NULL::double precision AS graph_secs,
    NULL::double precision AS apply_secs;

CREATE TABLE provisioner_job_timings (
    job_id uuid NOT NULL,
    started_at timestamp with time zone NOT NULL,
    ended_at timestamp with time zone NOT NULL,
    stage provisioner_job_timing_stage NOT NULL,
    source text NOT NULL,
    action text NOT NULL,
    resource text NOT NULL
);

CREATE TABLE provisioner_jobs (
    id uuid NOT NULL,
    created_at timestamp with time zone NOT NULL,
    updated_at timestamp with time zone NOT NULL,
    started_at timestamp with time zone,
    canceled_at timestamp with time zone,
    completed_at timestamp with time zone,
    error text,
    organization_id uuid NOT NULL,
    initiator_id uuid NOT NULL,
    provisioner provisioner_type NOT NULL,
    storage_method provisioner_storage_method NOT NULL,
    type provisioner_job_type NOT NULL,
    input jsonb NOT NULL,
    worker_id uuid,
    file_id uuid NOT NULL,
    tags jsonb DEFAULT '{"scope": "organization"}'::jsonb NOT NULL,
    error_code text,
    trace_metadata jsonb,
    job_status provisioner_job_status GENERATED ALWAYS AS (
CASE
    WHEN (completed_at IS NOT NULL) THEN
    CASE
        WHEN (error <> ''::text) THEN 'failed'::provisioner_job_status
        WHEN (canceled_at IS NOT NULL) THEN 'canceled'::provisioner_job_status
        ELSE 'succeeded'::provisioner_job_status
    END
    ELSE
    CASE
        WHEN (error <> ''::text) THEN 'failed'::provisioner_job_status
        WHEN (canceled_at IS NOT NULL) THEN 'canceling'::provisioner_job_status
        WHEN (started_at IS NULL) THEN 'pending'::provisioner_job_status
        ELSE 'running'::provisioner_job_status
    END
END) STORED NOT NULL,
    logs_length integer DEFAULT 0 NOT NULL,
    logs_overflowed boolean DEFAULT false NOT NULL,
    CONSTRAINT max_provisioner_logs_length CHECK ((logs_length <= 1048576))
);

COMMENT ON COLUMN provisioner_jobs.job_status IS 'Computed column to track the status of the job.';

COMMENT ON COLUMN provisioner_jobs.logs_length IS 'Total length of provisioner logs';

COMMENT ON COLUMN provisioner_jobs.logs_overflowed IS 'Whether the provisioner logs overflowed in length';

CREATE TABLE provisioner_keys (
    id uuid NOT NULL,
    created_at timestamp with time zone NOT NULL,
    organization_id uuid NOT NULL,
    name character varying(64) NOT NULL,
    hashed_secret bytea NOT NULL,
    tags jsonb NOT NULL
);

CREATE TABLE replicas (
    id uuid NOT NULL,
    created_at timestamp with time zone NOT NULL,
    started_at timestamp with time zone NOT NULL,
    stopped_at timestamp with time zone,
    updated_at timestamp with time zone NOT NULL,
    hostname text NOT NULL,
    region_id integer NOT NULL,
    relay_address text NOT NULL,
    database_latency integer NOT NULL,
    version text NOT NULL,
    error text DEFAULT ''::text NOT NULL,
    "primary" boolean DEFAULT true NOT NULL
);

CREATE TABLE site_configs (
    key character varying(256) NOT NULL,
    value text NOT NULL
);

CREATE UNLOGGED TABLE tailnet_coordinators (
    id uuid NOT NULL,
    heartbeat_at timestamp with time zone NOT NULL
);

COMMENT ON TABLE tailnet_coordinators IS 'We keep this separate from replicas in case we need to break the coordinator out into its own service';

CREATE UNLOGGED TABLE tailnet_peers (
    id uuid NOT NULL,
    coordinator_id uuid NOT NULL,
    updated_at timestamp with time zone NOT NULL,
    node bytea NOT NULL,
    status tailnet_status DEFAULT 'ok'::tailnet_status NOT NULL
);

CREATE UNLOGGED TABLE tailnet_tunnels (
    coordinator_id uuid NOT NULL,
    src_id uuid NOT NULL,
    dst_id uuid NOT NULL,
    updated_at timestamp with time zone NOT NULL
);

CREATE TABLE task_snapshots (
    task_id uuid NOT NULL,
    log_snapshot jsonb NOT NULL,
    log_snapshot_created_at timestamp with time zone DEFAULT now() NOT NULL
);

COMMENT ON TABLE task_snapshots IS 'Stores snapshots of task state when paused, currently limited to conversation history.';

COMMENT ON COLUMN task_snapshots.task_id IS 'The task this snapshot belongs to.';

COMMENT ON COLUMN task_snapshots.log_snapshot IS 'Task conversation history in JSON format, allowing users to view logs when the workspace is stopped.';

COMMENT ON COLUMN task_snapshots.log_snapshot_created_at IS 'When this log snapshot was captured.';

CREATE TABLE task_workspace_apps (
    task_id uuid NOT NULL,
    workspace_agent_id uuid,
    workspace_app_id uuid,
    workspace_build_number integer NOT NULL
);

CREATE TABLE tasks (
    id uuid NOT NULL,
    organization_id uuid NOT NULL,
    owner_id uuid NOT NULL,
    name text NOT NULL,
    workspace_id uuid,
    template_version_id uuid NOT NULL,
    template_parameters jsonb DEFAULT '{}'::jsonb NOT NULL,
    prompt text NOT NULL,
    created_at timestamp with time zone NOT NULL,
    deleted_at timestamp with time zone,
    display_name character varying(127) DEFAULT ''::character varying NOT NULL
);

COMMENT ON COLUMN tasks.display_name IS 'Display name is a custom, human-friendly task name.';

CREATE TABLE workspace_agents (
    id uuid NOT NULL,
    created_at timestamp with time zone NOT NULL,
    updated_at timestamp with time zone NOT NULL,
    name character varying(64) NOT NULL,
    first_connected_at timestamp with time zone,
    last_connected_at timestamp with time zone,
    disconnected_at timestamp with time zone,
    resource_id uuid NOT NULL,
    auth_token uuid NOT NULL,
    auth_instance_id character varying,
    architecture character varying(64) NOT NULL,
    environment_variables jsonb,
    operating_system character varying(64) NOT NULL,
    instance_metadata jsonb,
    resource_metadata jsonb,
    directory character varying(4096) DEFAULT ''::character varying NOT NULL,
    version text DEFAULT ''::text NOT NULL,
    last_connected_replica_id uuid,
    connection_timeout_seconds integer DEFAULT 0 NOT NULL,
    troubleshooting_url text DEFAULT ''::text NOT NULL,
    motd_file text DEFAULT ''::text NOT NULL,
    lifecycle_state workspace_agent_lifecycle_state DEFAULT 'created'::workspace_agent_lifecycle_state NOT NULL,
    expanded_directory character varying(4096) DEFAULT ''::character varying NOT NULL,
    logs_length integer DEFAULT 0 NOT NULL,
    logs_overflowed boolean DEFAULT false NOT NULL,
    started_at timestamp with time zone,
    ready_at timestamp with time zone,
    subsystems workspace_agent_subsystem[] DEFAULT '{}'::workspace_agent_subsystem[],
    display_apps display_app[] DEFAULT '{vscode,vscode_insiders,web_terminal,ssh_helper,port_forwarding_helper}'::display_app[],
    api_version text DEFAULT ''::text NOT NULL,
    display_order integer DEFAULT 0 NOT NULL,
    parent_id uuid,
    api_key_scope agent_key_scope_enum DEFAULT 'all'::agent_key_scope_enum NOT NULL,
    deleted boolean DEFAULT false NOT NULL,
    CONSTRAINT max_logs_length CHECK ((logs_length <= 1048576)),
    CONSTRAINT subsystems_not_none CHECK ((NOT ('none'::workspace_agent_subsystem = ANY (subsystems))))
);

COMMENT ON COLUMN workspace_agents.version IS 'Version tracks the version of the currently running workspace agent. Workspace agents register their version upon start.';

COMMENT ON COLUMN workspace_agents.connection_timeout_seconds IS 'Connection timeout in seconds, 0 means disabled.';

COMMENT ON COLUMN workspace_agents.troubleshooting_url IS 'URL for troubleshooting the agent.';

COMMENT ON COLUMN workspace_agents.motd_file IS 'Path to file inside workspace containing the message of the day (MOTD) to show to the user when logging in via SSH.';

COMMENT ON COLUMN workspace_agents.lifecycle_state IS 'The current lifecycle state reported by the workspace agent.';

COMMENT ON COLUMN workspace_agents.expanded_directory IS 'The resolved path of a user-specified directory. e.g. ~/coder -> /home/coder/coder';

COMMENT ON COLUMN workspace_agents.logs_length IS 'Total length of startup logs';

COMMENT ON COLUMN workspace_agents.logs_overflowed IS 'Whether the startup logs overflowed in length';

COMMENT ON COLUMN workspace_agents.started_at IS 'The time the agent entered the starting lifecycle state';

COMMENT ON COLUMN workspace_agents.ready_at IS 'The time the agent entered the ready or start_error lifecycle state';

COMMENT ON COLUMN workspace_agents.display_order IS 'Specifies the order in which to display agents in user interfaces.';

COMMENT ON COLUMN workspace_agents.api_key_scope IS 'Defines the scope of the API key associated with the agent. ''all'' allows access to everything, ''no_user_data'' restricts it to exclude user data.';

COMMENT ON COLUMN workspace_agents.deleted IS 'Indicates whether or not the agent has been deleted. This is currently only applicable to sub agents.';

CREATE TABLE workspace_apps (
    id uuid NOT NULL,
    created_at timestamp with time zone NOT NULL,
    agent_id uuid NOT NULL,
    display_name character varying(64) NOT NULL,
    icon character varying(256) NOT NULL,
    command character varying(65534),
    url character varying(65534),
    healthcheck_url text DEFAULT ''::text NOT NULL,
    healthcheck_interval integer DEFAULT 0 NOT NULL,
    healthcheck_threshold integer DEFAULT 0 NOT NULL,
    health workspace_app_health DEFAULT 'disabled'::workspace_app_health NOT NULL,
    subdomain boolean DEFAULT false NOT NULL,
    sharing_level app_sharing_level DEFAULT 'owner'::app_sharing_level NOT NULL,
    slug text NOT NULL,
    external boolean DEFAULT false NOT NULL,
    display_order integer DEFAULT 0 NOT NULL,
    hidden boolean DEFAULT false NOT NULL,
    open_in workspace_app_open_in DEFAULT 'slim-window'::workspace_app_open_in NOT NULL,
    display_group text,
    tooltip character varying(2048) DEFAULT ''::character varying NOT NULL
);

COMMENT ON COLUMN workspace_apps.display_order IS 'Specifies the order in which to display agent app in user interfaces.';

COMMENT ON COLUMN workspace_apps.hidden IS 'Determines if the app is not shown in user interfaces.';

COMMENT ON COLUMN workspace_apps.tooltip IS 'Markdown text that is displayed when hovering over workspace apps.';

CREATE TABLE workspace_builds (
    id uuid NOT NULL,
    created_at timestamp with time zone NOT NULL,
    updated_at timestamp with time zone NOT NULL,
    workspace_id uuid NOT NULL,
    template_version_id uuid NOT NULL,
    build_number integer NOT NULL,
    transition workspace_transition NOT NULL,
    initiator_id uuid NOT NULL,
    provisioner_state bytea,
    job_id uuid NOT NULL,
    deadline timestamp with time zone DEFAULT '0001-01-01 00:00:00+00'::timestamp with time zone NOT NULL,
    reason build_reason DEFAULT 'initiator'::build_reason NOT NULL,
    daily_cost integer DEFAULT 0 NOT NULL,
    max_deadline timestamp with time zone DEFAULT '0001-01-01 00:00:00+00'::timestamp with time zone NOT NULL,
    template_version_preset_id uuid,
    has_ai_task boolean,
    has_external_agent boolean,
    CONSTRAINT workspace_builds_deadline_below_max_deadline CHECK ((((deadline <> '0001-01-01 00:00:00+00'::timestamp with time zone) AND (deadline <= max_deadline)) OR (max_deadline = '0001-01-01 00:00:00+00'::timestamp with time zone)))
);

CREATE TABLE workspaces (
    id uuid NOT NULL,
    created_at timestamp with time zone NOT NULL,
    updated_at timestamp with time zone NOT NULL,
    owner_id uuid NOT NULL,
    organization_id uuid NOT NULL,
    template_id uuid NOT NULL,
    deleted boolean DEFAULT false NOT NULL,
    name character varying(64) NOT NULL,
    autostart_schedule text,
    ttl bigint,
    last_used_at timestamp with time zone DEFAULT '0001-01-01 00:00:00+00'::timestamp with time zone NOT NULL,
    dormant_at timestamp with time zone,
    deleting_at timestamp with time zone,
    automatic_updates automatic_updates DEFAULT 'never'::automatic_updates NOT NULL,
    favorite boolean DEFAULT false NOT NULL,
    next_start_at timestamp with time zone,
    group_acl jsonb DEFAULT '{}'::jsonb NOT NULL,
    user_acl jsonb DEFAULT '{}'::jsonb NOT NULL,
    CONSTRAINT group_acl_is_object CHECK ((jsonb_typeof(group_acl) = 'object'::text)),
    CONSTRAINT user_acl_is_object CHECK ((jsonb_typeof(user_acl) = 'object'::text))
);

COMMENT ON COLUMN workspaces.favorite IS 'Favorite is true if the workspace owner has favorited the workspace.';

CREATE VIEW tasks_with_status AS
 SELECT tasks.id,
    tasks.organization_id,
    tasks.owner_id,
    tasks.name,
    tasks.workspace_id,
    tasks.template_version_id,
    tasks.template_parameters,
    tasks.prompt,
    tasks.created_at,
    tasks.deleted_at,
    tasks.display_name,
    COALESCE(workspaces.group_acl, '{}'::jsonb) AS workspace_group_acl,
    COALESCE(workspaces.user_acl, '{}'::jsonb) AS workspace_user_acl,
        CASE
            WHEN (tasks.workspace_id IS NULL) THEN 'pending'::task_status
            WHEN (build_status.status <> 'active'::task_status) THEN build_status.status
            WHEN (agent_status.status <> 'active'::task_status) THEN agent_status.status
            ELSE app_status.status
        END AS status,
    jsonb_build_object('build', jsonb_build_object('transition', latest_build_raw.transition, 'job_status', latest_build_raw.job_status, 'computed', build_status.status), 'agent', jsonb_build_object('lifecycle_state', agent_raw.lifecycle_state, 'computed', agent_status.status), 'app', jsonb_build_object('health', app_raw.health, 'computed', app_status.status)) AS status_debug,
    task_app.workspace_build_number,
    task_app.workspace_agent_id,
    task_app.workspace_app_id,
    agent_raw.lifecycle_state AS workspace_agent_lifecycle_state,
    app_raw.health AS workspace_app_health,
    task_owner.owner_username,
    task_owner.owner_name,
    task_owner.owner_avatar_url
   FROM (((((((((tasks
     LEFT JOIN workspaces ON ((workspaces.id = tasks.workspace_id)))
     CROSS JOIN LATERAL ( SELECT vu.username AS owner_username,
            vu.name AS owner_name,
            vu.avatar_url AS owner_avatar_url
           FROM visible_users vu
          WHERE (vu.id = tasks.owner_id)) task_owner)
     LEFT JOIN LATERAL ( SELECT task_app_1.workspace_build_number,
            task_app_1.workspace_agent_id,
            task_app_1.workspace_app_id
           FROM task_workspace_apps task_app_1
          WHERE (task_app_1.task_id = tasks.id)
          ORDER BY task_app_1.workspace_build_number DESC
         LIMIT 1) task_app ON (true))
     LEFT JOIN LATERAL ( SELECT workspace_build.transition,
            provisioner_job.job_status,
            workspace_build.job_id
           FROM (workspace_builds workspace_build
             JOIN provisioner_jobs provisioner_job ON ((provisioner_job.id = workspace_build.job_id)))
          WHERE ((workspace_build.workspace_id = tasks.workspace_id) AND (workspace_build.build_number = task_app.workspace_build_number))) latest_build_raw ON (true))
     LEFT JOIN LATERAL ( SELECT workspace_agent.lifecycle_state
           FROM workspace_agents workspace_agent
          WHERE (workspace_agent.id = task_app.workspace_agent_id)) agent_raw ON (true))
     LEFT JOIN LATERAL ( SELECT workspace_app.health
           FROM workspace_apps workspace_app
          WHERE (workspace_app.id = task_app.workspace_app_id)) app_raw ON (true))
     CROSS JOIN LATERAL ( SELECT
                CASE
                    WHEN (latest_build_raw.job_status IS NULL) THEN 'pending'::task_status
                    WHEN (latest_build_raw.job_status = ANY (ARRAY['failed'::provisioner_job_status, 'canceling'::provisioner_job_status, 'canceled'::provisioner_job_status])) THEN 'error'::task_status
                    WHEN ((latest_build_raw.transition = ANY (ARRAY['stop'::workspace_transition, 'delete'::workspace_transition])) AND (latest_build_raw.job_status = 'succeeded'::provisioner_job_status)) THEN 'paused'::task_status
                    WHEN ((latest_build_raw.transition = 'start'::workspace_transition) AND (latest_build_raw.job_status = 'pending'::provisioner_job_status)) THEN 'pending'::task_status
                    WHEN ((latest_build_raw.transition = 'start'::workspace_transition) AND (latest_build_raw.job_status = ANY (ARRAY['running'::provisioner_job_status, 'succeeded'::provisioner_job_status]))) THEN 'active'::task_status
                    ELSE 'unknown'::task_status
                END AS status) build_status)
     CROSS JOIN LATERAL ( SELECT
                CASE
                    WHEN ((agent_raw.lifecycle_state IS NULL) OR (agent_raw.lifecycle_state = ANY (ARRAY['created'::workspace_agent_lifecycle_state, 'starting'::workspace_agent_lifecycle_state]))) THEN 'initializing'::task_status
                    WHEN (agent_raw.lifecycle_state = ANY (ARRAY['ready'::workspace_agent_lifecycle_state, 'start_timeout'::workspace_agent_lifecycle_state, 'start_error'::workspace_agent_lifecycle_state])) THEN 'active'::task_status
                    WHEN (agent_raw.lifecycle_state <> ALL (ARRAY['created'::workspace_agent_lifecycle_state, 'starting'::workspace_agent_lifecycle_state, 'ready'::workspace_agent_lifecycle_state, 'start_timeout'::workspace_agent_lifecycle_state, 'start_error'::workspace_agent_lifecycle_state])) THEN 'unknown'::task_status
                    ELSE 'unknown'::task_status
                END AS status) agent_status)
     CROSS JOIN LATERAL ( SELECT
                CASE
                    WHEN (app_raw.health = 'initializing'::workspace_app_health) THEN 'initializing'::task_status
                    WHEN (app_raw.health = 'unhealthy'::workspace_app_health) THEN 'error'::task_status
                    WHEN (app_raw.health = ANY (ARRAY['healthy'::workspace_app_health, 'disabled'::workspace_app_health])) THEN 'active'::task_status
                    ELSE 'unknown'::task_status
                END AS status) app_status)
  WHERE (tasks.deleted_at IS NULL);

CREATE TABLE telemetry_items (
    key text NOT NULL,
    value text NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL
);

CREATE TABLE telemetry_locks (
    event_type text NOT NULL,
    period_ending_at timestamp with time zone NOT NULL,
    CONSTRAINT telemetry_lock_event_type_constraint CHECK ((event_type = ANY (ARRAY['aibridge_interceptions_summary'::text, 'boundary_usage_summary'::text, 'user_secrets_summary'::text])))
);

COMMENT ON TABLE telemetry_locks IS 'Telemetry lock tracking table for deduplication of heartbeat events across replicas.';

COMMENT ON COLUMN telemetry_locks.event_type IS 'The type of event that was sent.';

COMMENT ON COLUMN telemetry_locks.period_ending_at IS 'The heartbeat period end timestamp.';

CREATE TABLE template_usage_stats (
    start_time timestamp with time zone NOT NULL,
    end_time timestamp with time zone NOT NULL,
    template_id uuid NOT NULL,
    user_id uuid NOT NULL,
    median_latency_ms real,
    usage_mins smallint NOT NULL,
    ssh_mins smallint NOT NULL,
    sftp_mins smallint NOT NULL,
    reconnecting_pty_mins smallint NOT NULL,
    vscode_mins smallint NOT NULL,
    jetbrains_mins smallint NOT NULL,
    app_usage_mins jsonb
);

COMMENT ON TABLE template_usage_stats IS 'Records aggregated usage statistics for templates/users. All usage is rounded up to the nearest minute.';

COMMENT ON COLUMN template_usage_stats.start_time IS 'Start time of the usage period.';

COMMENT ON COLUMN template_usage_stats.end_time IS 'End time of the usage period.';

COMMENT ON COLUMN template_usage_stats.template_id IS 'ID of the template being used.';

COMMENT ON COLUMN template_usage_stats.user_id IS 'ID of the user using the template.';

COMMENT ON COLUMN template_usage_stats.median_latency_ms IS 'Median latency the user is experiencing, in milliseconds. Null means no value was recorded.';

COMMENT ON COLUMN template_usage_stats.usage_mins IS 'Total minutes the user has been using the template.';

COMMENT ON COLUMN template_usage_stats.ssh_mins IS 'Total minutes the user has been using SSH.';

COMMENT ON COLUMN template_usage_stats.sftp_mins IS 'Total minutes the user has been using SFTP.';

COMMENT ON COLUMN template_usage_stats.reconnecting_pty_mins IS 'Total minutes the user has been using the reconnecting PTY.';

COMMENT ON COLUMN template_usage_stats.vscode_mins IS 'Total minutes the user has been using VSCode.';

COMMENT ON COLUMN template_usage_stats.jetbrains_mins IS 'Total minutes the user has been using JetBrains.';

COMMENT ON COLUMN template_usage_stats.app_usage_mins IS 'Object with app names as keys and total minutes used as values. Null means no app usage was recorded.';

CREATE TABLE template_version_parameters (
    template_version_id uuid NOT NULL,
    name text NOT NULL,
    description text NOT NULL,
    type text NOT NULL,
    mutable boolean NOT NULL,
    default_value text NOT NULL,
    icon text NOT NULL,
    options jsonb DEFAULT '[]'::jsonb NOT NULL,
    validation_regex text NOT NULL,
    validation_min integer,
    validation_max integer,
    validation_error text DEFAULT ''::text NOT NULL,
    validation_monotonic text DEFAULT ''::text NOT NULL,
    required boolean DEFAULT true NOT NULL,
    display_name text DEFAULT ''::text NOT NULL,
    display_order integer DEFAULT 0 NOT NULL,
    ephemeral boolean DEFAULT false NOT NULL,
    form_type parameter_form_type DEFAULT ''::parameter_form_type NOT NULL,
    CONSTRAINT validation_monotonic_order CHECK ((validation_monotonic = ANY (ARRAY['increasing'::text, 'decreasing'::text, ''::text])))
);

COMMENT ON COLUMN template_version_parameters.name IS 'Parameter name';

COMMENT ON COLUMN template_version_parameters.description IS 'Parameter description';

COMMENT ON COLUMN template_version_parameters.type IS 'Parameter type';

COMMENT ON COLUMN template_version_parameters.mutable IS 'Is parameter mutable?';

COMMENT ON COLUMN template_version_parameters.default_value IS 'Default value';

COMMENT ON COLUMN template_version_parameters.icon IS 'Icon';

COMMENT ON COLUMN template_version_parameters.options IS 'Additional options';

COMMENT ON COLUMN template_version_parameters.validation_regex IS 'Validation: regex pattern';

COMMENT ON COLUMN template_version_parameters.validation_min IS 'Validation: minimum length of value';

COMMENT ON COLUMN template_version_parameters.validation_max IS 'Validation: maximum length of value';

COMMENT ON COLUMN template_version_parameters.validation_error IS 'Validation: error displayed when the regex does not match.';

COMMENT ON COLUMN template_version_parameters.validation_monotonic IS 'Validation: consecutive values preserve the monotonic order';

COMMENT ON COLUMN template_version_parameters.required IS 'Is parameter required?';

COMMENT ON COLUMN template_version_parameters.display_name IS 'Display name of the rich parameter';

COMMENT ON COLUMN template_version_parameters.display_order IS 'Specifies the order in which to display parameters in user interfaces.';

COMMENT ON COLUMN template_version_parameters.ephemeral IS 'The value of an ephemeral parameter will not be preserved between consecutive workspace builds.';

COMMENT ON COLUMN template_version_parameters.form_type IS 'Specify what form_type should be used to render the parameter in the UI. Unsupported values are rejected.';

CREATE TABLE template_version_preset_parameters (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    template_version_preset_id uuid NOT NULL,
    name text NOT NULL,
    value text NOT NULL
);

CREATE TABLE template_version_preset_prebuild_schedules (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    preset_id uuid NOT NULL,
    cron_expression text NOT NULL,
    desired_instances integer NOT NULL
);

CREATE TABLE template_version_presets (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    template_version_id uuid NOT NULL,
    name text NOT NULL,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    desired_instances integer,
    invalidate_after_secs integer DEFAULT 0,
    prebuild_status prebuild_status DEFAULT 'healthy'::prebuild_status NOT NULL,
    scheduling_timezone text DEFAULT ''::text NOT NULL,
    is_default boolean DEFAULT false NOT NULL,
    description character varying(128) DEFAULT ''::character varying NOT NULL,
    icon character varying(256) DEFAULT ''::character varying NOT NULL,
    last_invalidated_at timestamp with time zone
);

COMMENT ON COLUMN template_version_presets.description IS 'Short text describing the preset (max 128 characters).';

COMMENT ON COLUMN template_version_presets.icon IS 'URL or path to an icon representing the preset (max 256 characters).';

CREATE TABLE template_version_terraform_values (
    template_version_id uuid NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    cached_plan jsonb NOT NULL,
    cached_module_files uuid,
    provisionerd_version text DEFAULT ''::text NOT NULL
);

COMMENT ON COLUMN template_version_terraform_values.provisionerd_version IS 'What version of the provisioning engine was used to generate the cached plan and module files.';

CREATE TABLE template_version_variables (
    template_version_id uuid NOT NULL,
    name text NOT NULL,
    description text NOT NULL,
    type text NOT NULL,
    value text NOT NULL,
    default_value text NOT NULL,
    required boolean NOT NULL,
    sensitive boolean NOT NULL
);

COMMENT ON COLUMN template_version_variables.name IS 'Variable name';

COMMENT ON COLUMN template_version_variables.description IS 'Variable description';

COMMENT ON COLUMN template_version_variables.type IS 'Variable type';

COMMENT ON COLUMN template_version_variables.value IS 'Variable value';

COMMENT ON COLUMN template_version_variables.default_value IS 'Variable default value';

COMMENT ON COLUMN template_version_variables.required IS 'Required variables needs a default value or a value provided by template admin';

COMMENT ON COLUMN template_version_variables.sensitive IS 'Sensitive variables have their values redacted in logs or site UI';

CREATE TABLE template_versions (
    id uuid NOT NULL,
    template_id uuid,
    organization_id uuid NOT NULL,
    created_at timestamp with time zone NOT NULL,
    updated_at timestamp with time zone NOT NULL,
    name character varying(64) NOT NULL,
    readme character varying(1048576) NOT NULL,
    job_id uuid NOT NULL,
    created_by uuid NOT NULL,
    external_auth_providers jsonb DEFAULT '[]'::jsonb NOT NULL,
    message character varying(1048576) DEFAULT ''::character varying NOT NULL,
    archived boolean DEFAULT false NOT NULL,
    source_example_id text,
    has_ai_task boolean,
    has_external_agent boolean
);

COMMENT ON COLUMN template_versions.external_auth_providers IS 'IDs of External auth providers for a specific template version';

COMMENT ON COLUMN template_versions.message IS 'Message describing the changes in this version of the template, similar to a Git commit message. Like a commit message, this should be a short, high-level description of the changes in this version of the template. This message is immutable and should not be updated after the fact.';

CREATE VIEW template_version_with_user AS
 SELECT template_versions.id,
    template_versions.template_id,
    template_versions.organization_id,
    template_versions.created_at,
    template_versions.updated_at,
    template_versions.name,
    template_versions.readme,
    template_versions.job_id,
    template_versions.created_by,
    template_versions.external_auth_providers,
    template_versions.message,
    template_versions.archived,
    template_versions.source_example_id,
    template_versions.has_ai_task,
    template_versions.has_external_agent,
    COALESCE(visible_users.avatar_url, ''::text) AS created_by_avatar_url,
    COALESCE(visible_users.username, ''::text) AS created_by_username,
    COALESCE(visible_users.name, ''::text) AS created_by_name
   FROM (template_versions
     LEFT JOIN visible_users ON ((template_versions.created_by = visible_users.id)));

COMMENT ON VIEW template_version_with_user IS 'Joins in the username + avatar url of the created by user.';

CREATE TABLE template_version_workspace_tags (
    template_version_id uuid NOT NULL,
    key text NOT NULL,
    value text NOT NULL
);

CREATE TABLE templates (
    id uuid NOT NULL,
    created_at timestamp with time zone NOT NULL,
    updated_at timestamp with time zone NOT NULL,
    organization_id uuid NOT NULL,
    deleted boolean DEFAULT false NOT NULL,
    name character varying(64) NOT NULL,
    provisioner provisioner_type NOT NULL,
    active_version_id uuid NOT NULL,
    description character varying(128) DEFAULT ''::character varying NOT NULL,
    default_ttl bigint DEFAULT '604800000000000'::bigint NOT NULL,
    created_by uuid NOT NULL,
    icon character varying(256) DEFAULT ''::character varying NOT NULL,
    user_acl jsonb DEFAULT '{}'::jsonb NOT NULL,
    group_acl jsonb DEFAULT '{}'::jsonb NOT NULL,
    display_name character varying(64) DEFAULT ''::character varying NOT NULL,
    allow_user_cancel_workspace_jobs boolean DEFAULT true NOT NULL,
    allow_user_autostart boolean DEFAULT true NOT NULL,
    allow_user_autostop boolean DEFAULT true NOT NULL,
    failure_ttl bigint DEFAULT 0 NOT NULL,
    time_til_dormant bigint DEFAULT 0 NOT NULL,
    time_til_dormant_autodelete bigint DEFAULT 0 NOT NULL,
    autostop_requirement_days_of_week smallint DEFAULT 0 NOT NULL,
    autostop_requirement_weeks bigint DEFAULT 0 NOT NULL,
    autostart_block_days_of_week smallint DEFAULT 0 NOT NULL,
    require_active_version boolean DEFAULT false NOT NULL,
    deprecated text DEFAULT ''::text NOT NULL,
    activity_bump bigint DEFAULT '3600000000000'::bigint NOT NULL,
    max_port_sharing_level app_sharing_level DEFAULT 'owner'::app_sharing_level NOT NULL,
    use_classic_parameter_flow boolean DEFAULT false NOT NULL,
    cors_behavior cors_behavior DEFAULT 'simple'::cors_behavior NOT NULL,
    disable_module_cache boolean DEFAULT false NOT NULL
);

COMMENT ON COLUMN templates.default_ttl IS 'The default duration for autostop for workspaces created from this template.';

COMMENT ON COLUMN templates.display_name IS 'Display name is a custom, human-friendly template name that user can set.';

COMMENT ON COLUMN templates.allow_user_cancel_workspace_jobs IS 'Allow users to cancel in-progress workspace jobs.';

COMMENT ON COLUMN templates.allow_user_autostart IS 'Allow users to specify an autostart schedule for workspaces (enterprise).';

COMMENT ON COLUMN templates.allow_user_autostop IS 'Allow users to specify custom autostop values for workspaces (enterprise).';

COMMENT ON COLUMN templates.autostop_requirement_days_of_week IS 'A bitmap of days of week to restart the workspace on, starting with Monday as the 0th bit, and Sunday as the 6th bit. The 7th bit is unused.';

COMMENT ON COLUMN templates.autostop_requirement_weeks IS 'The number of weeks between restarts. 0 or 1 weeks means "every week", 2 week means "every second week", etc. Weeks are counted from January 2, 2023, which is the first Monday of 2023. This is to ensure workspaces are started consistently for all customers on the same n-week cycles.';

COMMENT ON COLUMN templates.autostart_block_days_of_week IS 'A bitmap of days of week that autostart of a workspace is not allowed. Default allows all days. This is intended as a cost savings measure to prevent auto start on weekends (for example).';

COMMENT ON COLUMN templates.deprecated IS 'If set to a non empty string, the template will no longer be able to be used. The message will be displayed to the user.';

COMMENT ON COLUMN templates.use_classic_parameter_flow IS 'Determines whether to default to the dynamic parameter creation flow for this template or continue using the legacy classic parameter creation flow.This is a template wide setting, the template admin can revert to the classic flow if there are any issues. An escape hatch is required, as workspace creation is a core workflow and cannot break. This column will be removed when the dynamic parameter creation flow is stable.';

CREATE VIEW template_with_names AS
 SELECT templates.id,
    templates.created_at,
    templates.updated_at,
    templates.organization_id,
    templates.deleted,
    templates.name,
    templates.provisioner,
    templates.active_version_id,
    templates.description,
    templates.default_ttl,
    templates.created_by,
    templates.icon,
    templates.user_acl,
    templates.group_acl,
    templates.display_name,
    templates.allow_user_cancel_workspace_jobs,
    templates.allow_user_autostart,
    templates.allow_user_autostop,
    templates.failure_ttl,
    templates.time_til_dormant,
    templates.time_til_dormant_autodelete,
    templates.autostop_requirement_days_of_week,
    templates.autostop_requirement_weeks,
    templates.autostart_block_days_of_week,
    templates.require_active_version,
    templates.deprecated,
    templates.activity_bump,
    templates.max_port_sharing_level,
    templates.use_classic_parameter_flow,
    templates.cors_behavior,
    templates.disable_module_cache,
    COALESCE(visible_users.avatar_url, ''::text) AS created_by_avatar_url,
    COALESCE(visible_users.username, ''::text) AS created_by_username,
    COALESCE(visible_users.name, ''::text) AS created_by_name,
    COALESCE(organizations.name, ''::text) AS organization_name,
    COALESCE(organizations.display_name, ''::text) AS organization_display_name,
    COALESCE(organizations.icon, ''::text) AS organization_icon
   FROM ((templates
     LEFT JOIN visible_users ON ((templates.created_by = visible_users.id)))
     LEFT JOIN organizations ON ((templates.organization_id = organizations.id)));

COMMENT ON VIEW template_with_names IS 'Joins in the display name information such as username, avatar, and organization name.';

CREATE TABLE usage_events (
    id text NOT NULL,
    event_type text NOT NULL,
    event_data jsonb NOT NULL,
    created_at timestamp with time zone NOT NULL,
    publish_started_at timestamp with time zone,
    published_at timestamp with time zone,
    failure_message text,
    CONSTRAINT usage_event_type_check CHECK ((event_type = ANY (ARRAY['dc_managed_agents_v1'::text, 'hb_ai_seats_v1'::text])))
);

COMMENT ON TABLE usage_events IS 'usage_events contains usage data that is collected from the product and potentially shipped to the usage collector service.';

COMMENT ON COLUMN usage_events.id IS 'For "discrete" event types, this is a random UUID. For "heartbeat" event types, this is a combination of the event type and a truncated timestamp.';

COMMENT ON COLUMN usage_events.event_type IS 'The usage event type with version. "dc" means "discrete" (e.g. a single event, for counters), "hb" means "heartbeat" (e.g. a recurring event that contains a total count of usage generated from the database, for gauges).';

COMMENT ON COLUMN usage_events.event_data IS 'Event payload. Determined by the matching usage struct for this event type.';

COMMENT ON COLUMN usage_events.publish_started_at IS 'Set to a timestamp while the event is being published by a Coder replica to the usage collector service. Used to avoid duplicate publishes by multiple replicas. Timestamps older than 1 hour are considered expired.';

COMMENT ON COLUMN usage_events.published_at IS 'Set to a timestamp when the event is successfully (or permanently unsuccessfully) published to the usage collector service. If set, the event should never be attempted to be published again.';

COMMENT ON COLUMN usage_events.failure_message IS 'Set to an error message when the event is temporarily or permanently unsuccessfully published to the usage collector service.';

CREATE TABLE usage_events_daily (
    day date NOT NULL,
    event_type text NOT NULL,
    usage_data jsonb NOT NULL
);

COMMENT ON TABLE usage_events_daily IS 'usage_events_daily is a daily rollup of usage events. It stores the total usage for each event type by day.';

COMMENT ON COLUMN usage_events_daily.day IS 'The date of the summed usage events, always in UTC.';

CREATE TABLE user_ai_provider_keys (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    user_id uuid NOT NULL,
    ai_provider_id uuid NOT NULL,
    api_key text NOT NULL,
    api_key_key_id text,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT user_ai_provider_keys_api_key_check CHECK ((api_key <> ''::text))
);

COMMENT ON TABLE user_ai_provider_keys IS 'User-owned API keys associated with AI providers. These keys are used only when BYOK is enabled.';

COMMENT ON COLUMN user_ai_provider_keys.api_key IS 'User-owned API key used to authenticate with the upstream AI provider. Encrypted at rest via dbcrypt when api_key_key_id is set.';

COMMENT ON COLUMN user_ai_provider_keys.api_key_key_id IS 'The ID of the key used to encrypt the user-owned provider API key. If this is NULL, the API key is not encrypted.';

CREATE TABLE user_configs (
    user_id uuid NOT NULL,
    key character varying(256) NOT NULL,
    value text NOT NULL
);

CREATE TABLE user_deleted (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    user_id uuid NOT NULL,
    deleted_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL
);

COMMENT ON TABLE user_deleted IS 'Tracks when users were deleted';

CREATE TABLE user_links (
    user_id uuid NOT NULL,
    login_type login_type NOT NULL,
    linked_id text DEFAULT ''::text NOT NULL,
    oauth_access_token text DEFAULT ''::text NOT NULL,
    oauth_refresh_token text DEFAULT ''::text NOT NULL,
    oauth_expiry timestamp with time zone DEFAULT '0001-01-01 00:00:00+00'::timestamp with time zone NOT NULL,
    oauth_access_token_key_id text,
    oauth_refresh_token_key_id text,
    claims jsonb DEFAULT '{}'::jsonb NOT NULL
);

COMMENT ON COLUMN user_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 user_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 user_links.claims IS 'Claims from the IDP for the linked user. Includes both id_token and userinfo claims. ';

CREATE TABLE user_secrets (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    user_id uuid NOT NULL,
    name text NOT NULL,
    description text NOT NULL,
    value text NOT NULL,
    env_name text DEFAULT ''::text NOT NULL,
    file_path text DEFAULT ''::text NOT NULL,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    value_key_id text
);

CREATE TABLE user_skills (
    id uuid NOT NULL,
    user_id uuid NOT NULL,
    name text NOT NULL,
    description text DEFAULT ''::text NOT NULL,
    content text NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT user_skills_content_size CHECK ((octet_length(content) <= 65536)),
    CONSTRAINT user_skills_description_size CHECK ((octet_length(description) <= 4096)),
    CONSTRAINT user_skills_name_format CHECK ((name ~ '^[a-z0-9]+(-[a-z0-9]+)*$'::text)),
    CONSTRAINT user_skills_name_size CHECK ((octet_length(name) <= 256))
);

CREATE TABLE user_status_changes (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    user_id uuid NOT NULL,
    new_status user_status NOT NULL,
    changed_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL
);

COMMENT ON TABLE user_status_changes IS 'Tracks the history of user status changes';

CREATE TABLE webpush_subscriptions (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    user_id uuid NOT NULL,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    endpoint text NOT NULL,
    endpoint_p256dh_key text NOT NULL,
    endpoint_auth_key text NOT NULL
);

CREATE TABLE workspace_agent_devcontainers (
    id uuid NOT NULL,
    workspace_agent_id uuid NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    workspace_folder text NOT NULL,
    config_path text NOT NULL,
    name text NOT NULL,
    subagent_id uuid
);

COMMENT ON TABLE workspace_agent_devcontainers IS 'Workspace agent devcontainer configuration';

COMMENT ON COLUMN workspace_agent_devcontainers.id IS 'Unique identifier';

COMMENT ON COLUMN workspace_agent_devcontainers.workspace_agent_id IS 'Workspace agent foreign key';

COMMENT ON COLUMN workspace_agent_devcontainers.created_at IS 'Creation timestamp';

COMMENT ON COLUMN workspace_agent_devcontainers.workspace_folder IS 'Workspace folder';

COMMENT ON COLUMN workspace_agent_devcontainers.config_path IS 'Path to devcontainer.json.';

COMMENT ON COLUMN workspace_agent_devcontainers.name IS 'The name of the Dev Container.';

CREATE TABLE workspace_agent_log_sources (
    workspace_agent_id uuid NOT NULL,
    id uuid NOT NULL,
    created_at timestamp with time zone NOT NULL,
    display_name character varying(127) NOT NULL,
    icon text NOT NULL
);

CREATE UNLOGGED TABLE workspace_agent_logs (
    agent_id uuid NOT NULL,
    created_at timestamp with time zone NOT NULL,
    output character varying(1024) NOT NULL,
    id bigint NOT NULL,
    level log_level DEFAULT 'info'::log_level NOT NULL,
    log_source_id uuid DEFAULT '00000000-0000-0000-0000-000000000000'::uuid NOT NULL
);

CREATE TABLE workspace_agent_memory_resource_monitors (
    agent_id uuid NOT NULL,
    enabled boolean NOT NULL,
    threshold integer NOT NULL,
    created_at timestamp with time zone NOT NULL,
    updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    state workspace_agent_monitor_state DEFAULT 'OK'::workspace_agent_monitor_state NOT NULL,
    debounced_until timestamp with time zone DEFAULT '0001-01-01 00:00:00+00'::timestamp with time zone NOT NULL
);

CREATE UNLOGGED TABLE workspace_agent_metadata (
    workspace_agent_id uuid NOT NULL,
    display_name character varying(127) NOT NULL,
    key character varying(127) NOT NULL,
    script character varying(65535) NOT NULL,
    value character varying(65535) DEFAULT ''::character varying NOT NULL,
    error character varying(65535) DEFAULT ''::character varying NOT NULL,
    timeout bigint NOT NULL,
    "interval" bigint NOT NULL,
    collected_at timestamp with time zone DEFAULT '0001-01-01 00:00:00+00'::timestamp with time zone NOT NULL,
    display_order integer DEFAULT 0 NOT NULL
);

COMMENT ON COLUMN workspace_agent_metadata.display_order IS 'Specifies the order in which to display agent metadata in user interfaces.';

CREATE TABLE workspace_agent_port_share (
    workspace_id uuid NOT NULL,
    agent_name text NOT NULL,
    port integer NOT NULL,
    share_level app_sharing_level NOT NULL,
    protocol port_share_protocol DEFAULT 'http'::port_share_protocol NOT NULL
);

CREATE TABLE workspace_agent_script_timings (
    script_id uuid NOT NULL,
    started_at timestamp with time zone NOT NULL,
    ended_at timestamp with time zone NOT NULL,
    exit_code integer NOT NULL,
    stage workspace_agent_script_timing_stage NOT NULL,
    status workspace_agent_script_timing_status NOT NULL
);

CREATE TABLE workspace_agent_scripts (
    workspace_agent_id uuid NOT NULL,
    log_source_id uuid NOT NULL,
    log_path text NOT NULL,
    created_at timestamp with time zone NOT NULL,
    script text NOT NULL,
    cron text NOT NULL,
    start_blocks_login boolean NOT NULL,
    run_on_start boolean NOT NULL,
    run_on_stop boolean NOT NULL,
    timeout_seconds integer NOT NULL,
    display_name text NOT NULL,
    id uuid DEFAULT gen_random_uuid() NOT NULL
);

CREATE SEQUENCE workspace_agent_startup_logs_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE workspace_agent_startup_logs_id_seq OWNED BY workspace_agent_logs.id;

CREATE TABLE workspace_agent_stats (
    id uuid NOT NULL,
    created_at timestamp with time zone NOT NULL,
    user_id uuid NOT NULL,
    agent_id uuid NOT NULL,
    workspace_id uuid NOT NULL,
    template_id uuid NOT NULL,
    connections_by_proto jsonb DEFAULT '{}'::jsonb NOT NULL,
    connection_count bigint DEFAULT 0 NOT NULL,
    rx_packets bigint DEFAULT 0 NOT NULL,
    rx_bytes bigint DEFAULT 0 NOT NULL,
    tx_packets bigint DEFAULT 0 NOT NULL,
    tx_bytes bigint DEFAULT 0 NOT NULL,
    connection_median_latency_ms double precision DEFAULT '-1'::integer NOT NULL,
    session_count_vscode bigint DEFAULT 0 NOT NULL,
    session_count_jetbrains bigint DEFAULT 0 NOT NULL,
    session_count_reconnecting_pty bigint DEFAULT 0 NOT NULL,
    session_count_ssh bigint DEFAULT 0 NOT NULL,
    usage boolean DEFAULT false NOT NULL
);

CREATE TABLE workspace_agent_volume_resource_monitors (
    agent_id uuid NOT NULL,
    enabled boolean NOT NULL,
    threshold integer NOT NULL,
    path text NOT NULL,
    created_at timestamp with time zone NOT NULL,
    updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    state workspace_agent_monitor_state DEFAULT 'OK'::workspace_agent_monitor_state NOT NULL,
    debounced_until timestamp with time zone DEFAULT '0001-01-01 00:00:00+00'::timestamp with time zone NOT NULL
);

CREATE UNLOGGED TABLE workspace_app_audit_sessions (
    agent_id uuid NOT NULL,
    app_id uuid NOT NULL,
    user_id uuid NOT NULL,
    ip text NOT NULL,
    user_agent text NOT NULL,
    slug_or_port text NOT NULL,
    status_code integer NOT NULL,
    started_at timestamp with time zone NOT NULL,
    updated_at timestamp with time zone NOT NULL,
    id uuid NOT NULL
);

COMMENT ON TABLE workspace_app_audit_sessions IS 'Audit sessions for workspace apps, the data in this table is ephemeral and is used to deduplicate audit log entries for workspace apps. While a session is active, the same data will not be logged again. This table does not store historical data.';

COMMENT ON COLUMN workspace_app_audit_sessions.agent_id IS 'The agent that the workspace app or port forward belongs to.';

COMMENT ON COLUMN workspace_app_audit_sessions.app_id IS 'The app that is currently in the workspace app. This is may be uuid.Nil because ports are not associated with an app.';

COMMENT ON COLUMN workspace_app_audit_sessions.user_id IS 'The user that is currently using the workspace app. This is may be uuid.Nil if we cannot determine the user.';

COMMENT ON COLUMN workspace_app_audit_sessions.ip IS 'The IP address of the user that is currently using the workspace app.';

COMMENT ON COLUMN workspace_app_audit_sessions.user_agent IS 'The user agent of the user that is currently using the workspace app.';

COMMENT ON COLUMN workspace_app_audit_sessions.slug_or_port IS 'The slug or port of the workspace app that the user is currently using.';

COMMENT ON COLUMN workspace_app_audit_sessions.status_code IS 'The HTTP status produced by the token authorization. Defaults to 200 if no status is provided.';

COMMENT ON COLUMN workspace_app_audit_sessions.started_at IS 'The time the user started the session.';

COMMENT ON COLUMN workspace_app_audit_sessions.updated_at IS 'The time the session was last updated.';

CREATE TABLE workspace_app_stats (
    id bigint NOT NULL,
    user_id uuid NOT NULL,
    workspace_id uuid NOT NULL,
    agent_id uuid NOT NULL,
    access_method text NOT NULL,
    slug_or_port text NOT NULL,
    session_id uuid NOT NULL,
    session_started_at timestamp with time zone NOT NULL,
    session_ended_at timestamp with time zone NOT NULL,
    requests integer NOT NULL
);

COMMENT ON TABLE workspace_app_stats IS 'A record of workspace app usage statistics';

COMMENT ON COLUMN workspace_app_stats.id IS 'The ID of the record';

COMMENT ON COLUMN workspace_app_stats.user_id IS 'The user who used the workspace app';

COMMENT ON COLUMN workspace_app_stats.workspace_id IS 'The workspace that the workspace app was used in';

COMMENT ON COLUMN workspace_app_stats.agent_id IS 'The workspace agent that was used';

COMMENT ON COLUMN workspace_app_stats.access_method IS 'The method used to access the workspace app';

COMMENT ON COLUMN workspace_app_stats.slug_or_port IS 'The slug or port used to to identify the app';

COMMENT ON COLUMN workspace_app_stats.session_id IS 'The unique identifier for the session';

COMMENT ON COLUMN workspace_app_stats.session_started_at IS 'The time the session started';

COMMENT ON COLUMN workspace_app_stats.session_ended_at IS 'The time the session ended';

COMMENT ON COLUMN workspace_app_stats.requests IS 'The number of requests made during the session, a number larger than 1 indicates that multiple sessions were rolled up into one';

CREATE SEQUENCE workspace_app_stats_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE workspace_app_stats_id_seq OWNED BY workspace_app_stats.id;

CREATE TABLE workspace_app_statuses (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    agent_id uuid NOT NULL,
    app_id uuid NOT NULL,
    workspace_id uuid NOT NULL,
    state workspace_app_status_state NOT NULL,
    message text NOT NULL,
    uri text
);

CREATE TABLE workspace_build_parameters (
    workspace_build_id uuid NOT NULL,
    name text NOT NULL,
    value text NOT NULL
);

COMMENT ON COLUMN workspace_build_parameters.name IS 'Parameter name';

COMMENT ON COLUMN workspace_build_parameters.value IS 'Parameter value';

CREATE VIEW workspace_build_with_user AS
 SELECT workspace_builds.id,
    workspace_builds.created_at,
    workspace_builds.updated_at,
    workspace_builds.workspace_id,
    workspace_builds.template_version_id,
    workspace_builds.build_number,
    workspace_builds.transition,
    workspace_builds.initiator_id,
    workspace_builds.job_id,
    workspace_builds.deadline,
    workspace_builds.reason,
    workspace_builds.daily_cost,
    workspace_builds.max_deadline,
    workspace_builds.template_version_preset_id,
    workspace_builds.has_ai_task,
    workspace_builds.has_external_agent,
    COALESCE(visible_users.avatar_url, ''::text) AS initiator_by_avatar_url,
    COALESCE(visible_users.username, ''::text) AS initiator_by_username,
    COALESCE(visible_users.name, ''::text) AS initiator_by_name
   FROM (workspace_builds
     LEFT JOIN visible_users ON ((workspace_builds.initiator_id = visible_users.id)));

COMMENT ON VIEW workspace_build_with_user IS 'Joins in the username + avatar url of the initiated by user.';

CREATE VIEW workspace_latest_builds AS
 SELECT latest_build.id,
    latest_build.workspace_id,
    latest_build.template_version_id,
    latest_build.job_id,
    latest_build.template_version_preset_id,
    latest_build.transition,
    latest_build.created_at,
    latest_build.job_status
   FROM (workspaces
     LEFT JOIN LATERAL ( SELECT workspace_builds.id,
            workspace_builds.workspace_id,
            workspace_builds.template_version_id,
            workspace_builds.job_id,
            workspace_builds.template_version_preset_id,
            workspace_builds.transition,
            workspace_builds.created_at,
            provisioner_jobs.job_status
           FROM (workspace_builds
             JOIN provisioner_jobs ON ((provisioner_jobs.id = workspace_builds.job_id)))
          WHERE (workspace_builds.workspace_id = workspaces.id)
          ORDER BY workspace_builds.build_number DESC
         LIMIT 1) latest_build ON (true))
  WHERE (workspaces.deleted = false)
  ORDER BY workspaces.id;

CREATE TABLE workspace_modules (
    id uuid NOT NULL,
    job_id uuid NOT NULL,
    transition workspace_transition NOT NULL,
    source text NOT NULL,
    version text NOT NULL,
    key text NOT NULL,
    created_at timestamp with time zone NOT NULL
);

CREATE VIEW workspace_prebuild_builds AS
 SELECT workspace_builds.id,
    workspace_builds.workspace_id,
    workspace_builds.template_version_id,
    workspace_builds.transition,
    workspace_builds.job_id,
    workspace_builds.template_version_preset_id,
    workspace_builds.build_number
   FROM workspace_builds
  WHERE (workspace_builds.initiator_id = 'c42fdf75-3097-471c-8c33-fb52454d81c0'::uuid);

CREATE TABLE workspace_resources (
    id uuid NOT NULL,
    created_at timestamp with time zone NOT NULL,
    job_id uuid NOT NULL,
    transition workspace_transition NOT NULL,
    type character varying(192) NOT NULL,
    name character varying(64) NOT NULL,
    hide boolean DEFAULT false NOT NULL,
    icon character varying(256) DEFAULT ''::character varying NOT NULL,
    instance_type character varying(256),
    daily_cost integer DEFAULT 0 NOT NULL,
    module_path text
);

CREATE VIEW workspace_prebuilds AS
 WITH all_prebuilds AS (
         SELECT w.id,
            w.name,
            w.template_id,
            w.created_at
           FROM workspaces w
          WHERE (w.owner_id = 'c42fdf75-3097-471c-8c33-fb52454d81c0'::uuid)
        ), workspaces_with_latest_presets AS (
         SELECT DISTINCT ON (workspace_builds.workspace_id) workspace_builds.workspace_id,
            workspace_builds.template_version_preset_id
           FROM workspace_builds
          WHERE (workspace_builds.template_version_preset_id IS NOT NULL)
          ORDER BY workspace_builds.workspace_id, workspace_builds.build_number DESC
        ), workspaces_with_agents_status AS (
         SELECT w.id AS workspace_id,
            bool_and((wa.lifecycle_state = 'ready'::workspace_agent_lifecycle_state)) AS ready
           FROM (((workspaces w
             JOIN workspace_latest_builds wlb ON ((wlb.workspace_id = w.id)))
             JOIN workspace_resources wr ON ((wr.job_id = wlb.job_id)))
             JOIN workspace_agents wa ON (((wa.resource_id = wr.id) AND (wa.deleted = false))))
          WHERE (w.owner_id = 'c42fdf75-3097-471c-8c33-fb52454d81c0'::uuid)
          GROUP BY w.id
        ), current_presets AS (
         SELECT w.id AS prebuild_id,
            wlp.template_version_preset_id
           FROM (workspaces w
             JOIN workspaces_with_latest_presets wlp ON ((wlp.workspace_id = w.id)))
          WHERE (w.owner_id = 'c42fdf75-3097-471c-8c33-fb52454d81c0'::uuid)
        )
 SELECT p.id,
    p.name,
    p.template_id,
    p.created_at,
    COALESCE(a.ready, false) AS ready,
    cp.template_version_preset_id AS current_preset_id
   FROM ((all_prebuilds p
     LEFT JOIN workspaces_with_agents_status a ON ((a.workspace_id = p.id)))
     JOIN current_presets cp ON ((cp.prebuild_id = p.id)));

CREATE TABLE workspace_proxies (
    id uuid NOT NULL,
    name text NOT NULL,
    display_name text NOT NULL,
    icon text NOT NULL,
    url text NOT NULL,
    wildcard_hostname text NOT NULL,
    created_at timestamp with time zone NOT NULL,
    updated_at timestamp with time zone NOT NULL,
    deleted boolean NOT NULL,
    token_hashed_secret bytea NOT NULL,
    region_id integer NOT NULL,
    derp_enabled boolean DEFAULT true NOT NULL,
    derp_only boolean DEFAULT false NOT NULL,
    version text DEFAULT ''::text NOT NULL
);

COMMENT ON COLUMN workspace_proxies.icon IS 'Expects an emoji character. (/emojis/1f1fa-1f1f8.png)';

COMMENT ON COLUMN workspace_proxies.url IS 'Full url including scheme of the proxy api url: https://us.example.com';

COMMENT ON COLUMN workspace_proxies.wildcard_hostname IS 'Hostname with the wildcard for subdomain based app hosting: *.us.example.com';

COMMENT ON COLUMN workspace_proxies.deleted IS 'Boolean indicator of a deleted workspace proxy. Proxies are soft-deleted.';

COMMENT ON COLUMN workspace_proxies.token_hashed_secret IS 'Hashed secret is used to authenticate the workspace proxy using a session token.';

COMMENT ON COLUMN workspace_proxies.derp_only IS 'Disables app/terminal proxying for this proxy and only acts as a DERP relay.';

CREATE SEQUENCE workspace_proxies_region_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE workspace_proxies_region_id_seq OWNED BY workspace_proxies.region_id;

CREATE TABLE workspace_resource_metadata (
    workspace_resource_id uuid NOT NULL,
    key character varying(1024) NOT NULL,
    value character varying(65536),
    sensitive boolean NOT NULL,
    id bigint NOT NULL
);

CREATE SEQUENCE workspace_resource_metadata_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE workspace_resource_metadata_id_seq OWNED BY workspace_resource_metadata.id;

CREATE VIEW workspaces_expanded AS
 SELECT workspaces.id,
    workspaces.created_at,
    workspaces.updated_at,
    workspaces.owner_id,
    workspaces.organization_id,
    workspaces.template_id,
    workspaces.deleted,
    workspaces.name,
    workspaces.autostart_schedule,
    workspaces.ttl,
    workspaces.last_used_at,
    workspaces.dormant_at,
    workspaces.deleting_at,
    workspaces.automatic_updates,
    workspaces.favorite,
    workspaces.next_start_at,
    workspaces.group_acl,
    workspaces.user_acl,
    visible_users.avatar_url AS owner_avatar_url,
    visible_users.username AS owner_username,
    visible_users.name AS owner_name,
    organizations.name AS organization_name,
    organizations.display_name AS organization_display_name,
    organizations.icon AS organization_icon,
    organizations.description AS organization_description,
    templates.name AS template_name,
    templates.display_name AS template_display_name,
    templates.icon AS template_icon,
    templates.description AS template_description,
    tasks.id AS task_id,
    COALESCE(( SELECT jsonb_object_agg(acl.key, jsonb_build_object('name', COALESCE(g.name, ''::text), 'avatar_url', COALESCE(g.avatar_url, ''::text))) AS jsonb_object_agg
           FROM (jsonb_each(workspaces.group_acl) acl(key, value)
             LEFT JOIN groups g ON ((g.id = (acl.key)::uuid)))), '{}'::jsonb) AS group_acl_display_info,
    COALESCE(( SELECT jsonb_object_agg(acl.key, jsonb_build_object('name', COALESCE(vu.name, ''::text), 'avatar_url', COALESCE(vu.avatar_url, ''::text))) AS jsonb_object_agg
           FROM (jsonb_each(workspaces.user_acl) acl(key, value)
             LEFT JOIN visible_users vu ON ((vu.id = (acl.key)::uuid)))), '{}'::jsonb) AS user_acl_display_info
   FROM ((((workspaces
     JOIN visible_users ON ((workspaces.owner_id = visible_users.id)))
     JOIN organizations ON ((workspaces.organization_id = organizations.id)))
     JOIN templates ON ((workspaces.template_id = templates.id)))
     LEFT JOIN tasks ON ((workspaces.id = tasks.workspace_id)));

COMMENT ON VIEW workspaces_expanded IS 'Joins in the display name information such as username, avatar, and organization name.';

ALTER TABLE ONLY chat_messages ALTER COLUMN id SET DEFAULT nextval('chat_messages_id_seq'::regclass);

ALTER TABLE ONLY chat_queued_messages ALTER COLUMN id SET DEFAULT nextval('chat_queued_messages_id_seq'::regclass);

ALTER TABLE ONLY chat_usage_limit_config ALTER COLUMN id SET DEFAULT nextval('chat_usage_limit_config_id_seq'::regclass);

ALTER TABLE ONLY licenses ALTER COLUMN id SET DEFAULT nextval('licenses_id_seq'::regclass);

ALTER TABLE ONLY provisioner_job_logs ALTER COLUMN id SET DEFAULT nextval('provisioner_job_logs_id_seq'::regclass);

ALTER TABLE ONLY workspace_agent_logs ALTER COLUMN id SET DEFAULT nextval('workspace_agent_startup_logs_id_seq'::regclass);

ALTER TABLE ONLY workspace_app_stats ALTER COLUMN id SET DEFAULT nextval('workspace_app_stats_id_seq'::regclass);

ALTER TABLE ONLY workspace_proxies ALTER COLUMN region_id SET DEFAULT nextval('workspace_proxies_region_id_seq'::regclass);

ALTER TABLE ONLY workspace_resource_metadata ALTER COLUMN id SET DEFAULT nextval('workspace_resource_metadata_id_seq'::regclass);

ALTER TABLE ONLY workspace_agent_stats
    ADD CONSTRAINT agent_stats_pkey PRIMARY KEY (id);

ALTER TABLE ONLY ai_model_prices
    ADD CONSTRAINT ai_model_prices_pkey PRIMARY KEY (provider, model);

ALTER TABLE ONLY ai_provider_keys
    ADD CONSTRAINT ai_provider_keys_pkey PRIMARY KEY (id);

ALTER TABLE ONLY ai_providers
    ADD CONSTRAINT ai_providers_pkey PRIMARY KEY (id);

ALTER TABLE ONLY ai_seat_state
    ADD CONSTRAINT ai_seat_state_pkey PRIMARY KEY (user_id);

ALTER TABLE ONLY aibridge_interceptions
    ADD CONSTRAINT aibridge_interceptions_pkey PRIMARY KEY (id);

ALTER TABLE ONLY aibridge_token_usages
    ADD CONSTRAINT aibridge_token_usages_pkey PRIMARY KEY (id);

ALTER TABLE ONLY aibridge_tool_usages
    ADD CONSTRAINT aibridge_tool_usages_pkey PRIMARY KEY (id);

ALTER TABLE ONLY aibridge_user_prompts
    ADD CONSTRAINT aibridge_user_prompts_pkey PRIMARY KEY (id);

ALTER TABLE ONLY api_keys
    ADD CONSTRAINT api_keys_pkey PRIMARY KEY (id);

ALTER TABLE ONLY audit_logs
    ADD CONSTRAINT audit_logs_pkey PRIMARY KEY (id);

ALTER TABLE ONLY boundary_logs
    ADD CONSTRAINT boundary_logs_pkey PRIMARY KEY (id);

ALTER TABLE ONLY boundary_sessions
    ADD CONSTRAINT boundary_sessions_pkey PRIMARY KEY (id);

ALTER TABLE ONLY boundary_usage_stats
    ADD CONSTRAINT boundary_usage_stats_pkey PRIMARY KEY (replica_id);

ALTER TABLE ONLY chat_debug_runs
    ADD CONSTRAINT chat_debug_runs_pkey PRIMARY KEY (id);

ALTER TABLE ONLY chat_debug_steps
    ADD CONSTRAINT chat_debug_steps_pkey PRIMARY KEY (id);

ALTER TABLE ONLY chat_diff_statuses
    ADD CONSTRAINT chat_diff_statuses_pkey PRIMARY KEY (chat_id);

ALTER TABLE ONLY chat_file_links
    ADD CONSTRAINT chat_file_links_chat_id_file_id_key UNIQUE (chat_id, file_id);

ALTER TABLE ONLY chat_files
    ADD CONSTRAINT chat_files_pkey PRIMARY KEY (id);

ALTER TABLE ONLY chat_messages
    ADD CONSTRAINT chat_messages_pkey PRIMARY KEY (id);

ALTER TABLE ONLY chat_model_configs
    ADD CONSTRAINT chat_model_configs_pkey PRIMARY KEY (id);

ALTER TABLE ONLY chat_queued_messages
    ADD CONSTRAINT chat_queued_messages_pkey PRIMARY KEY (id);

ALTER TABLE ONLY chat_usage_limit_config
    ADD CONSTRAINT chat_usage_limit_config_pkey PRIMARY KEY (id);

ALTER TABLE ONLY chat_usage_limit_config
    ADD CONSTRAINT chat_usage_limit_config_singleton_key UNIQUE (singleton);

ALTER TABLE ONLY chats
    ADD CONSTRAINT chats_pkey PRIMARY KEY (id);

ALTER TABLE ONLY connection_logs
    ADD CONSTRAINT connection_logs_pkey PRIMARY KEY (id);

ALTER TABLE ONLY crypto_keys
    ADD CONSTRAINT crypto_keys_pkey PRIMARY KEY (feature, sequence);

ALTER TABLE ONLY custom_roles
    ADD CONSTRAINT custom_roles_unique_key UNIQUE (name, organization_id);

ALTER TABLE ONLY dbcrypt_keys
    ADD CONSTRAINT dbcrypt_keys_active_key_digest_key UNIQUE (active_key_digest);

ALTER TABLE ONLY dbcrypt_keys
    ADD CONSTRAINT dbcrypt_keys_pkey PRIMARY KEY (number);

ALTER TABLE ONLY dbcrypt_keys
    ADD CONSTRAINT dbcrypt_keys_revoked_key_digest_key UNIQUE (revoked_key_digest);

ALTER TABLE ONLY files
    ADD CONSTRAINT files_hash_created_by_key UNIQUE (hash, created_by);

ALTER TABLE ONLY files
    ADD CONSTRAINT files_pkey PRIMARY KEY (id);

ALTER TABLE ONLY external_auth_links
    ADD CONSTRAINT git_auth_links_provider_id_user_id_key UNIQUE (provider_id, user_id);

ALTER TABLE ONLY gitsshkeys
    ADD CONSTRAINT gitsshkeys_pkey PRIMARY KEY (user_id);

ALTER TABLE ONLY group_ai_budgets
    ADD CONSTRAINT group_ai_budgets_pkey PRIMARY KEY (group_id);

ALTER TABLE ONLY group_members
    ADD CONSTRAINT group_members_user_id_group_id_key UNIQUE (user_id, group_id);

ALTER TABLE ONLY groups
    ADD CONSTRAINT groups_name_organization_id_key UNIQUE (name, organization_id);

ALTER TABLE ONLY groups
    ADD CONSTRAINT groups_pkey PRIMARY KEY (id);

ALTER TABLE ONLY inbox_notifications
    ADD CONSTRAINT inbox_notifications_pkey PRIMARY KEY (id);

ALTER TABLE ONLY jfrog_xray_scans
    ADD CONSTRAINT jfrog_xray_scans_pkey PRIMARY KEY (agent_id, workspace_id);

ALTER TABLE ONLY licenses
    ADD CONSTRAINT licenses_jwt_key UNIQUE (jwt);

ALTER TABLE ONLY licenses
    ADD CONSTRAINT licenses_pkey PRIMARY KEY (id);

ALTER TABLE ONLY mcp_server_configs
    ADD CONSTRAINT mcp_server_configs_pkey PRIMARY KEY (id);

ALTER TABLE ONLY mcp_server_configs
    ADD CONSTRAINT mcp_server_configs_slug_key UNIQUE (slug);

ALTER TABLE ONLY mcp_server_user_tokens
    ADD CONSTRAINT mcp_server_user_tokens_mcp_server_config_id_user_id_key UNIQUE (mcp_server_config_id, user_id);

ALTER TABLE ONLY mcp_server_user_tokens
    ADD CONSTRAINT mcp_server_user_tokens_pkey PRIMARY KEY (id);

ALTER TABLE ONLY notification_messages
    ADD CONSTRAINT notification_messages_pkey PRIMARY KEY (id);

ALTER TABLE ONLY notification_preferences
    ADD CONSTRAINT notification_preferences_pkey PRIMARY KEY (user_id, notification_template_id);

ALTER TABLE ONLY notification_report_generator_logs
    ADD CONSTRAINT notification_report_generator_logs_pkey PRIMARY KEY (notification_template_id);

ALTER TABLE ONLY notification_templates
    ADD CONSTRAINT notification_templates_name_key UNIQUE (name);

ALTER TABLE ONLY notification_templates
    ADD CONSTRAINT notification_templates_pkey PRIMARY KEY (id);

ALTER TABLE ONLY oauth2_provider_app_codes
    ADD CONSTRAINT oauth2_provider_app_codes_pkey PRIMARY KEY (id);

ALTER TABLE ONLY oauth2_provider_app_codes
    ADD CONSTRAINT oauth2_provider_app_codes_secret_prefix_key UNIQUE (secret_prefix);

ALTER TABLE ONLY oauth2_provider_app_secrets
    ADD CONSTRAINT oauth2_provider_app_secrets_pkey PRIMARY KEY (id);

ALTER TABLE ONLY oauth2_provider_app_secrets
    ADD CONSTRAINT oauth2_provider_app_secrets_secret_prefix_key UNIQUE (secret_prefix);

ALTER TABLE ONLY oauth2_provider_app_tokens
    ADD CONSTRAINT oauth2_provider_app_tokens_hash_prefix_key UNIQUE (hash_prefix);

ALTER TABLE ONLY oauth2_provider_app_tokens
    ADD CONSTRAINT oauth2_provider_app_tokens_pkey PRIMARY KEY (id);

ALTER TABLE ONLY oauth2_provider_apps
    ADD CONSTRAINT oauth2_provider_apps_pkey PRIMARY KEY (id);

ALTER TABLE ONLY organization_members
    ADD CONSTRAINT organization_members_pkey PRIMARY KEY (organization_id, user_id);

ALTER TABLE ONLY organizations
    ADD CONSTRAINT organizations_pkey PRIMARY KEY (id);

ALTER TABLE ONLY parameter_schemas
    ADD CONSTRAINT parameter_schemas_job_id_name_key UNIQUE (job_id, name);

ALTER TABLE ONLY parameter_schemas
    ADD CONSTRAINT parameter_schemas_pkey PRIMARY KEY (id);

ALTER TABLE ONLY parameter_values
    ADD CONSTRAINT parameter_values_pkey PRIMARY KEY (id);

ALTER TABLE ONLY parameter_values
    ADD CONSTRAINT parameter_values_scope_id_name_key UNIQUE (scope_id, name);

ALTER TABLE ONLY provisioner_daemons
    ADD CONSTRAINT provisioner_daemons_pkey PRIMARY KEY (id);

ALTER TABLE ONLY provisioner_job_logs
    ADD CONSTRAINT provisioner_job_logs_pkey PRIMARY KEY (id);

ALTER TABLE ONLY provisioner_jobs
    ADD CONSTRAINT provisioner_jobs_pkey PRIMARY KEY (id);

ALTER TABLE ONLY provisioner_keys
    ADD CONSTRAINT provisioner_keys_pkey PRIMARY KEY (id);

ALTER TABLE ONLY site_configs
    ADD CONSTRAINT site_configs_key_key UNIQUE (key);

ALTER TABLE ONLY tailnet_coordinators
    ADD CONSTRAINT tailnet_coordinators_pkey PRIMARY KEY (id);

ALTER TABLE ONLY tailnet_peers
    ADD CONSTRAINT tailnet_peers_pkey PRIMARY KEY (id, coordinator_id);

ALTER TABLE ONLY tailnet_tunnels
    ADD CONSTRAINT tailnet_tunnels_pkey PRIMARY KEY (coordinator_id, src_id, dst_id);

ALTER TABLE ONLY task_snapshots
    ADD CONSTRAINT task_snapshots_pkey PRIMARY KEY (task_id);

ALTER TABLE ONLY task_workspace_apps
    ADD CONSTRAINT task_workspace_apps_pkey PRIMARY KEY (task_id, workspace_build_number);

ALTER TABLE ONLY tasks
    ADD CONSTRAINT tasks_pkey PRIMARY KEY (id);

ALTER TABLE ONLY telemetry_items
    ADD CONSTRAINT telemetry_items_pkey PRIMARY KEY (key);

ALTER TABLE ONLY telemetry_locks
    ADD CONSTRAINT telemetry_locks_pkey PRIMARY KEY (event_type, period_ending_at);

ALTER TABLE ONLY template_usage_stats
    ADD CONSTRAINT template_usage_stats_pkey PRIMARY KEY (start_time, template_id, user_id);

ALTER TABLE ONLY template_version_parameters
    ADD CONSTRAINT template_version_parameters_template_version_id_name_key UNIQUE (template_version_id, name);

ALTER TABLE ONLY template_version_preset_parameters
    ADD CONSTRAINT template_version_preset_parameters_pkey PRIMARY KEY (id);

ALTER TABLE ONLY template_version_preset_prebuild_schedules
    ADD CONSTRAINT template_version_preset_prebuild_schedules_pkey PRIMARY KEY (id);

ALTER TABLE ONLY template_version_presets
    ADD CONSTRAINT template_version_presets_pkey PRIMARY KEY (id);

ALTER TABLE ONLY template_version_terraform_values
    ADD CONSTRAINT template_version_terraform_values_template_version_id_key UNIQUE (template_version_id);

ALTER TABLE ONLY template_version_variables
    ADD CONSTRAINT template_version_variables_template_version_id_name_key UNIQUE (template_version_id, name);

ALTER TABLE ONLY template_version_workspace_tags
    ADD CONSTRAINT template_version_workspace_tags_template_version_id_key_key UNIQUE (template_version_id, key);

ALTER TABLE ONLY template_versions
    ADD CONSTRAINT template_versions_pkey PRIMARY KEY (id);

ALTER TABLE ONLY template_versions
    ADD CONSTRAINT template_versions_template_id_name_key UNIQUE (template_id, name);

ALTER TABLE ONLY templates
    ADD CONSTRAINT templates_pkey PRIMARY KEY (id);

ALTER TABLE ONLY usage_events_daily
    ADD CONSTRAINT usage_events_daily_pkey PRIMARY KEY (day, event_type);

ALTER TABLE ONLY usage_events
    ADD CONSTRAINT usage_events_pkey PRIMARY KEY (id);

ALTER TABLE ONLY user_ai_provider_keys
    ADD CONSTRAINT user_ai_provider_keys_pkey PRIMARY KEY (id);

ALTER TABLE ONLY user_ai_provider_keys
    ADD CONSTRAINT user_ai_provider_keys_user_id_ai_provider_id_key UNIQUE (user_id, ai_provider_id);

ALTER TABLE ONLY user_configs
    ADD CONSTRAINT user_configs_pkey PRIMARY KEY (user_id, key);

ALTER TABLE ONLY user_deleted
    ADD CONSTRAINT user_deleted_pkey PRIMARY KEY (id);

ALTER TABLE ONLY user_links
    ADD CONSTRAINT user_links_pkey PRIMARY KEY (user_id, login_type);

ALTER TABLE ONLY user_secrets
    ADD CONSTRAINT user_secrets_pkey PRIMARY KEY (id);

ALTER TABLE ONLY user_skills
    ADD CONSTRAINT user_skills_pkey PRIMARY KEY (id);

ALTER TABLE ONLY user_status_changes
    ADD CONSTRAINT user_status_changes_pkey PRIMARY KEY (id);

ALTER TABLE ONLY users
    ADD CONSTRAINT users_pkey PRIMARY KEY (id);

ALTER TABLE ONLY webpush_subscriptions
    ADD CONSTRAINT webpush_subscriptions_pkey PRIMARY KEY (id);

ALTER TABLE ONLY workspace_agent_devcontainers
    ADD CONSTRAINT workspace_agent_devcontainers_pkey PRIMARY KEY (id);

ALTER TABLE ONLY workspace_agent_log_sources
    ADD CONSTRAINT workspace_agent_log_sources_pkey PRIMARY KEY (workspace_agent_id, id);

ALTER TABLE ONLY workspace_agent_memory_resource_monitors
    ADD CONSTRAINT workspace_agent_memory_resource_monitors_pkey PRIMARY KEY (agent_id);

ALTER TABLE ONLY workspace_agent_metadata
    ADD CONSTRAINT workspace_agent_metadata_pkey PRIMARY KEY (workspace_agent_id, key);

ALTER TABLE ONLY workspace_agent_port_share
    ADD CONSTRAINT workspace_agent_port_share_pkey PRIMARY KEY (workspace_id, agent_name, port);

ALTER TABLE ONLY workspace_agent_script_timings
    ADD CONSTRAINT workspace_agent_script_timings_script_id_started_at_key UNIQUE (script_id, started_at);

ALTER TABLE ONLY workspace_agent_scripts
    ADD CONSTRAINT workspace_agent_scripts_id_key UNIQUE (id);

ALTER TABLE ONLY workspace_agent_logs
    ADD CONSTRAINT workspace_agent_startup_logs_pkey PRIMARY KEY (id);

ALTER TABLE ONLY workspace_agent_volume_resource_monitors
    ADD CONSTRAINT workspace_agent_volume_resource_monitors_pkey PRIMARY KEY (agent_id, path);

ALTER TABLE ONLY workspace_agents
    ADD CONSTRAINT workspace_agents_pkey PRIMARY KEY (id);

ALTER TABLE ONLY workspace_app_audit_sessions
    ADD CONSTRAINT workspace_app_audit_sessions_agent_id_app_id_user_id_ip_use_key UNIQUE (agent_id, app_id, user_id, ip, user_agent, slug_or_port, status_code);

ALTER TABLE ONLY workspace_app_audit_sessions
    ADD CONSTRAINT workspace_app_audit_sessions_pkey PRIMARY KEY (id);

ALTER TABLE ONLY workspace_app_stats
    ADD CONSTRAINT workspace_app_stats_pkey PRIMARY KEY (id);

ALTER TABLE ONLY workspace_app_stats
    ADD CONSTRAINT workspace_app_stats_user_id_agent_id_session_id_key UNIQUE (user_id, agent_id, session_id);

ALTER TABLE ONLY workspace_app_statuses
    ADD CONSTRAINT workspace_app_statuses_pkey PRIMARY KEY (id);

ALTER TABLE ONLY workspace_apps
    ADD CONSTRAINT workspace_apps_agent_id_slug_idx UNIQUE (agent_id, slug);

ALTER TABLE ONLY workspace_apps
    ADD CONSTRAINT workspace_apps_pkey PRIMARY KEY (id);

ALTER TABLE ONLY workspace_build_parameters
    ADD CONSTRAINT workspace_build_parameters_workspace_build_id_name_key UNIQUE (workspace_build_id, name);

ALTER TABLE ONLY workspace_builds
    ADD CONSTRAINT workspace_builds_job_id_key UNIQUE (job_id);

ALTER TABLE ONLY workspace_builds
    ADD CONSTRAINT workspace_builds_pkey PRIMARY KEY (id);

ALTER TABLE ONLY workspace_builds
    ADD CONSTRAINT workspace_builds_workspace_id_build_number_key UNIQUE (workspace_id, build_number);

ALTER TABLE ONLY workspace_proxies
    ADD CONSTRAINT workspace_proxies_pkey PRIMARY KEY (id);

ALTER TABLE ONLY workspace_proxies
    ADD CONSTRAINT workspace_proxies_region_id_unique UNIQUE (region_id);

ALTER TABLE ONLY workspace_resource_metadata
    ADD CONSTRAINT workspace_resource_metadata_name UNIQUE (workspace_resource_id, key);

ALTER TABLE ONLY workspace_resource_metadata
    ADD CONSTRAINT workspace_resource_metadata_pkey PRIMARY KEY (id);

ALTER TABLE ONLY workspace_resources
    ADD CONSTRAINT workspace_resources_pkey PRIMARY KEY (id);

ALTER TABLE ONLY workspaces
    ADD CONSTRAINT workspaces_pkey PRIMARY KEY (id);

CREATE UNIQUE INDEX ai_providers_name_unique ON ai_providers USING btree (name) WHERE (deleted = false);

CREATE INDEX api_keys_last_used_idx ON api_keys USING btree (last_used DESC);

COMMENT ON INDEX api_keys_last_used_idx IS 'Index for optimizing api_keys queries filtering by last_used';

CREATE INDEX idx_agent_stats_created_at ON workspace_agent_stats USING btree (created_at);

CREATE INDEX idx_agent_stats_user_id ON workspace_agent_stats USING btree (user_id);

CREATE INDEX idx_ai_provider_keys_provider_id ON ai_provider_keys USING btree (provider_id);

CREATE INDEX idx_ai_providers_enabled ON ai_providers USING btree (enabled) WHERE (deleted = false);

CREATE INDEX idx_aibridge_interceptions_client ON aibridge_interceptions USING btree (client);

CREATE INDEX idx_aibridge_interceptions_client_session_id ON aibridge_interceptions USING btree (client_session_id) WHERE (client_session_id IS NOT NULL);

CREATE INDEX idx_aibridge_interceptions_initiator_id ON aibridge_interceptions USING btree (initiator_id);

CREATE INDEX idx_aibridge_interceptions_model ON aibridge_interceptions USING btree (model);

CREATE INDEX idx_aibridge_interceptions_provider ON aibridge_interceptions USING btree (provider);

CREATE INDEX idx_aibridge_interceptions_session_id ON aibridge_interceptions USING btree (session_id) WHERE (ended_at IS NOT NULL);

CREATE INDEX idx_aibridge_interceptions_sessions_filter ON aibridge_interceptions USING btree (initiator_id, started_at DESC, id DESC) WHERE (ended_at IS NOT NULL);

CREATE INDEX idx_aibridge_interceptions_started_id_desc ON aibridge_interceptions USING btree (started_at DESC, id DESC);

CREATE INDEX idx_aibridge_interceptions_thread_parent_id ON aibridge_interceptions USING btree (thread_parent_id);

CREATE INDEX idx_aibridge_interceptions_thread_root_id ON aibridge_interceptions USING btree (thread_root_id);

CREATE INDEX idx_aibridge_model_thoughts_interception_id ON aibridge_model_thoughts USING btree (interception_id);

CREATE INDEX idx_aibridge_token_usages_interception_id ON aibridge_token_usages USING btree (interception_id);

CREATE INDEX idx_aibridge_token_usages_provider_response_id ON aibridge_token_usages USING btree (provider_response_id);

CREATE INDEX idx_aibridge_tool_usages_interception_id ON aibridge_tool_usages USING btree (interception_id);

CREATE INDEX idx_aibridge_tool_usages_provider_tool_call_id ON aibridge_tool_usages USING btree (provider_tool_call_id);

CREATE INDEX idx_aibridge_tool_usagesprovider_response_id ON aibridge_tool_usages USING btree (provider_response_id);

CREATE INDEX idx_aibridge_user_prompts_interception_created ON aibridge_user_prompts USING btree (interception_id, created_at DESC, id DESC);

CREATE INDEX idx_aibridge_user_prompts_interception_id ON aibridge_user_prompts USING btree (interception_id);

CREATE INDEX idx_aibridge_user_prompts_provider_response_id ON aibridge_user_prompts USING btree (provider_response_id);

CREATE UNIQUE INDEX idx_api_key_name ON api_keys USING btree (user_id, token_name) WHERE (login_type = 'token'::login_type);

CREATE INDEX idx_api_keys_user ON api_keys USING btree (user_id);

CREATE INDEX idx_audit_log_organization_id ON audit_logs USING btree (organization_id);

CREATE INDEX idx_audit_log_resource_id ON audit_logs USING btree (resource_id);

CREATE INDEX idx_audit_log_user_id ON audit_logs USING btree (user_id);

CREATE INDEX idx_audit_logs_time_desc ON audit_logs USING btree ("time" DESC);

CREATE INDEX idx_boundary_logs_captured_at ON boundary_logs USING btree (captured_at);

CREATE INDEX idx_boundary_logs_session_seq ON boundary_logs USING btree (session_id, sequence_number);

CREATE INDEX idx_chat_debug_runs_chat_started ON chat_debug_runs USING btree (chat_id, started_at DESC);

CREATE UNIQUE INDEX idx_chat_debug_runs_id_chat ON chat_debug_runs USING btree (id, chat_id);

CREATE INDEX idx_chat_debug_runs_stale ON chat_debug_runs USING btree (updated_at) WHERE (finished_at IS NULL);

CREATE INDEX idx_chat_debug_runs_updated_at ON chat_debug_runs USING btree (updated_at);

CREATE INDEX idx_chat_debug_steps_chat_assistant_msg ON chat_debug_steps USING btree (chat_id, assistant_message_id) WHERE (assistant_message_id IS NOT NULL);

CREATE INDEX idx_chat_debug_steps_chat_tip ON chat_debug_steps USING btree (chat_id, history_tip_message_id);

CREATE UNIQUE INDEX idx_chat_debug_steps_run_step ON chat_debug_steps USING btree (run_id, step_number);

CREATE INDEX idx_chat_debug_steps_stale ON chat_debug_steps USING btree (updated_at) WHERE (finished_at IS NULL);

CREATE INDEX idx_chat_diff_statuses_stale_at ON chat_diff_statuses USING btree (stale_at);

CREATE INDEX idx_chat_diff_statuses_url_lower ON chat_diff_statuses USING btree (lower(url)) WHERE ((url IS NOT NULL) AND (url <> ''::text));

CREATE INDEX idx_chat_file_links_chat_id ON chat_file_links USING btree (chat_id);

CREATE INDEX idx_chat_files_org ON chat_files USING btree (organization_id);

CREATE INDEX idx_chat_files_owner ON chat_files USING btree (owner_id);

CREATE INDEX idx_chat_messages_chat ON chat_messages USING btree (chat_id);

CREATE INDEX idx_chat_messages_chat_created ON chat_messages USING btree (chat_id, created_at);

CREATE INDEX idx_chat_messages_compressed_summary_boundary ON chat_messages USING btree (chat_id, created_at DESC, id DESC) WHERE ((compressed = true) AND (role = 'system'::chat_message_role) AND (visibility = ANY (ARRAY['model'::chat_message_visibility, 'both'::chat_message_visibility])));

CREATE INDEX idx_chat_messages_created_at ON chat_messages USING btree (created_at);

CREATE INDEX idx_chat_messages_owner_spend ON chat_messages USING btree (chat_id, created_at) WHERE (total_cost_micros IS NOT NULL);

CREATE INDEX idx_chat_messages_user_prompts ON chat_messages USING btree (chat_id, id DESC) WHERE ((deleted = false) AND (role = 'user'::chat_message_role) AND (visibility = ANY (ARRAY['user'::chat_message_visibility, 'both'::chat_message_visibility])));

CREATE INDEX idx_chat_model_configs_ai_provider_id ON chat_model_configs USING btree (ai_provider_id);

CREATE INDEX idx_chat_model_configs_enabled ON chat_model_configs USING btree (enabled);

CREATE INDEX idx_chat_model_configs_provider ON chat_model_configs USING btree (provider);

CREATE INDEX idx_chat_model_configs_provider_model ON chat_model_configs USING btree (provider, model);

CREATE UNIQUE INDEX idx_chat_model_configs_single_default ON chat_model_configs USING btree ((1)) WHERE ((is_default = true) AND (deleted = false));

CREATE INDEX idx_chat_queued_messages_chat_id ON chat_queued_messages USING btree (chat_id);

CREATE INDEX idx_chats_agent_id ON chats USING btree (agent_id) WHERE (agent_id IS NOT NULL);

CREATE INDEX idx_chats_auto_archive_candidates ON chats USING btree (created_at) WHERE ((archived = false) AND (pin_order = 0) AND (parent_chat_id IS NULL));

CREATE INDEX idx_chats_labels ON chats USING gin (labels);

CREATE INDEX idx_chats_last_model_config_id ON chats USING btree (last_model_config_id);

CREATE INDEX idx_chats_organization_id ON chats USING btree (organization_id);

CREATE INDEX idx_chats_owner ON chats USING btree (owner_id);

CREATE INDEX idx_chats_parent_chat_id ON chats USING btree (parent_chat_id);

CREATE INDEX idx_chats_pending ON chats USING btree (status) WHERE (status = 'pending'::chat_status);

CREATE INDEX idx_chats_root_chat_id ON chats USING btree (root_chat_id);

CREATE INDEX idx_chats_workspace ON chats USING btree (workspace_id);

CREATE INDEX idx_connection_logs_connect_time_desc ON connection_logs USING btree (connect_time DESC);

CREATE UNIQUE INDEX idx_connection_logs_connection_id_workspace_id_agent_name ON connection_logs USING btree (connection_id, workspace_id, agent_name);

COMMENT ON INDEX idx_connection_logs_connection_id_workspace_id_agent_name IS 'Connection ID is NULL for web events, but present for SSH events. Therefore, this index allows multiple web events for the same workspace & agent. For SSH events, the upsertion query handles duplicates on this index by upserting the disconnect_time and disconnect_reason for the same connection_id when the connection is closed.';

CREATE INDEX idx_connection_logs_organization_id ON connection_logs USING btree (organization_id);

CREATE INDEX idx_connection_logs_workspace_id ON connection_logs USING btree (workspace_id);

CREATE INDEX idx_connection_logs_workspace_owner_id ON connection_logs USING btree (workspace_owner_id);

CREATE INDEX idx_custom_roles_id ON custom_roles USING btree (id);

CREATE UNIQUE INDEX idx_custom_roles_name_lower_organization_id ON custom_roles USING btree (lower(name), COALESCE(organization_id, '00000000-0000-0000-0000-000000000000'::uuid));

CREATE INDEX idx_inbox_notifications_user_id_read_at ON inbox_notifications USING btree (user_id, read_at);

CREATE INDEX idx_inbox_notifications_user_id_template_id_targets ON inbox_notifications USING btree (user_id, template_id, targets);

CREATE INDEX idx_mcp_server_configs_enabled ON mcp_server_configs USING btree (enabled) WHERE (enabled = true);

CREATE INDEX idx_mcp_server_configs_forced ON mcp_server_configs USING btree (enabled, availability) WHERE ((enabled = true) AND (availability = 'force_on'::text));

CREATE INDEX idx_mcp_server_user_tokens_user_id ON mcp_server_user_tokens USING btree (user_id);

CREATE INDEX idx_notification_messages_status ON notification_messages USING btree (status);

CREATE INDEX idx_organization_member_organization_id_uuid ON organization_members USING btree (organization_id);

CREATE INDEX idx_organization_member_user_id_uuid ON organization_members USING btree (user_id);

CREATE UNIQUE INDEX idx_organization_name_lower ON organizations USING btree (lower(name)) WHERE (deleted = false);

CREATE UNIQUE INDEX idx_provisioner_daemons_org_name_owner_key ON provisioner_daemons USING btree (organization_id, name, lower(COALESCE((tags ->> 'owner'::text), ''::text)));

COMMENT ON INDEX idx_provisioner_daemons_org_name_owner_key IS 'Allow unique provisioner daemon names by organization and user';

CREATE INDEX idx_provisioner_jobs_status ON provisioner_jobs USING btree (job_status);

CREATE INDEX idx_tailnet_peers_coordinator ON tailnet_peers USING btree (coordinator_id);

CREATE INDEX idx_tailnet_tunnels_dst_id ON tailnet_tunnels USING hash (dst_id);

CREATE INDEX idx_tailnet_tunnels_src_id ON tailnet_tunnels USING hash (src_id);

CREATE INDEX idx_telemetry_locks_period_ending_at ON telemetry_locks USING btree (period_ending_at);

CREATE UNIQUE INDEX idx_template_version_presets_default ON template_version_presets USING btree (template_version_id) WHERE (is_default = true);

CREATE INDEX idx_template_versions_has_ai_task ON template_versions USING btree (has_ai_task);

CREATE UNIQUE INDEX idx_unique_preset_name ON template_version_presets USING btree (name, template_version_id);

CREATE INDEX idx_usage_events_ai_seats ON usage_events USING btree (event_type, created_at) WHERE (event_type = 'hb_ai_seats_v1'::text);

CREATE INDEX idx_usage_events_select_for_publishing ON usage_events USING btree (published_at, publish_started_at, created_at);

CREATE INDEX idx_user_ai_provider_keys_ai_provider_id ON user_ai_provider_keys USING btree (ai_provider_id);

CREATE INDEX idx_user_deleted_deleted_at ON user_deleted USING btree (deleted_at);

CREATE INDEX idx_user_status_changes_changed_at ON user_status_changes USING btree (changed_at);

CREATE UNIQUE INDEX idx_users_email ON users USING btree (email) WHERE ((deleted = false) AND (email <> ''::text));

CREATE UNIQUE INDEX idx_users_username ON users USING btree (username) WHERE (deleted = false);

CREATE INDEX idx_workspace_app_statuses_workspace_id_created_at ON workspace_app_statuses USING btree (workspace_id, created_at DESC);

CREATE INDEX idx_workspace_builds_initiator_id ON workspace_builds USING btree (initiator_id);

CREATE UNIQUE INDEX notification_messages_dedupe_hash_idx ON notification_messages USING btree (dedupe_hash);

CREATE UNIQUE INDEX organizations_single_default_org ON organizations USING btree (is_default) WHERE (is_default = true);

CREATE INDEX provisioner_job_logs_id_job_id_idx ON provisioner_job_logs USING btree (job_id, id);

CREATE INDEX provisioner_jobs_started_at_idx ON provisioner_jobs USING btree (started_at) WHERE (started_at IS NULL);

CREATE INDEX provisioner_jobs_worker_id_organization_id_completed_at_idx ON provisioner_jobs USING btree (worker_id, organization_id, completed_at DESC);

COMMENT ON INDEX provisioner_jobs_worker_id_organization_id_completed_at_idx IS 'Support index for finding the latest completed jobs for a worker (and organization), nulls first so that active jobs have priority; targets: GetProvisionerDaemonsWithStatusByOrganization';

CREATE UNIQUE INDEX provisioner_keys_organization_id_name_idx ON provisioner_keys USING btree (organization_id, lower((name)::text));

CREATE INDEX tasks_organization_id_idx ON tasks USING btree (organization_id);

CREATE INDEX tasks_owner_id_idx ON tasks USING btree (owner_id);

CREATE UNIQUE INDEX tasks_owner_id_name_unique_idx ON tasks USING btree (owner_id, lower(name)) WHERE (deleted_at IS NULL);

COMMENT ON INDEX tasks_owner_id_name_unique_idx IS 'Index to ensure uniqueness for task owner/name';

CREATE INDEX tasks_workspace_id_idx ON tasks USING btree (workspace_id);

CREATE INDEX template_usage_stats_start_time_idx ON template_usage_stats USING btree (start_time DESC);

COMMENT ON INDEX template_usage_stats_start_time_idx IS 'Index for querying MAX(start_time).';

CREATE UNIQUE INDEX template_usage_stats_start_time_template_id_user_id_idx ON template_usage_stats USING btree (start_time, template_id, user_id);

COMMENT ON INDEX template_usage_stats_start_time_template_id_user_id_idx IS 'Index for primary key.';

CREATE UNIQUE INDEX templates_organization_id_name_idx ON templates USING btree (organization_id, lower((name)::text)) WHERE (deleted = false);

CREATE UNIQUE INDEX user_links_linked_id_login_type_idx ON user_links USING btree (linked_id, login_type) WHERE (linked_id <> ''::text);

CREATE UNIQUE INDEX user_secrets_user_env_name_idx ON user_secrets USING btree (user_id, env_name) WHERE (env_name <> ''::text);

CREATE UNIQUE INDEX user_secrets_user_file_path_idx ON user_secrets USING btree (user_id, file_path) WHERE (file_path <> ''::text);

CREATE UNIQUE INDEX user_secrets_user_name_idx ON user_secrets USING btree (user_id, name);

CREATE UNIQUE INDEX user_skills_user_id_name_idx ON user_skills USING btree (user_id, name);

CREATE UNIQUE INDEX users_email_lower_idx ON users USING btree (lower(email)) WHERE ((deleted = false) AND (email <> ''::text));

CREATE UNIQUE INDEX users_username_lower_idx ON users USING btree (lower(username)) WHERE (deleted = false);

CREATE UNIQUE INDEX webpush_subscriptions_user_id_endpoint_idx ON webpush_subscriptions USING btree (user_id, endpoint);

CREATE INDEX workspace_agent_devcontainers_workspace_agent_id ON workspace_agent_devcontainers USING btree (workspace_agent_id);

COMMENT ON INDEX workspace_agent_devcontainers_workspace_agent_id IS 'Workspace agent foreign key and query index';

CREATE INDEX workspace_agent_scripts_workspace_agent_id_idx ON workspace_agent_scripts USING btree (workspace_agent_id);

COMMENT ON INDEX workspace_agent_scripts_workspace_agent_id_idx IS 'Foreign key support index for faster lookups';

CREATE INDEX workspace_agent_startup_logs_id_agent_id_idx ON workspace_agent_logs USING btree (agent_id, id);

CREATE INDEX workspace_agent_stats_template_id_created_at_user_id_idx ON workspace_agent_stats USING btree (template_id, created_at, user_id) INCLUDE (session_count_vscode, session_count_jetbrains, session_count_reconnecting_pty, session_count_ssh, connection_median_latency_ms) WHERE (connection_count > 0);

COMMENT ON INDEX workspace_agent_stats_template_id_created_at_user_id_idx IS 'Support index for template insights endpoint to build interval reports faster.';

CREATE INDEX workspace_agents_auth_instance_id_deleted_idx ON workspace_agents USING btree (auth_instance_id, deleted);

CREATE INDEX workspace_agents_auth_token_idx ON workspace_agents USING btree (auth_token);

CREATE INDEX workspace_agents_resource_id_idx ON workspace_agents USING btree (resource_id);

CREATE UNIQUE INDEX workspace_app_audit_sessions_unique_index ON workspace_app_audit_sessions USING btree (agent_id, app_id, user_id, ip, user_agent, slug_or_port, status_code);

COMMENT ON INDEX workspace_app_audit_sessions_unique_index IS 'Unique index to ensure that we do not allow duplicate entries from multiple transactions.';

CREATE INDEX workspace_app_stats_workspace_id_idx ON workspace_app_stats USING btree (workspace_id);

CREATE INDEX workspace_app_statuses_app_id_idx ON workspace_app_statuses USING btree (app_id, created_at DESC);

CREATE INDEX workspace_modules_created_at_idx ON workspace_modules USING btree (created_at);

CREATE INDEX workspace_next_start_at_idx ON workspaces USING btree (next_start_at) WHERE (deleted = false);

CREATE UNIQUE INDEX workspace_proxies_lower_name_idx ON workspace_proxies USING btree (lower(name)) WHERE (deleted = false);

CREATE INDEX workspace_resources_job_id_idx ON workspace_resources USING btree (job_id);

CREATE INDEX workspace_template_id_idx ON workspaces USING btree (template_id) WHERE (deleted = false);

CREATE UNIQUE INDEX workspaces_owner_id_lower_idx ON workspaces USING btree (owner_id, lower((name)::text)) WHERE (deleted = false);

CREATE OR REPLACE VIEW provisioner_job_stats AS
 SELECT pj.id AS job_id,
    pj.job_status,
    wb.workspace_id,
    pj.worker_id,
    pj.error,
    pj.error_code,
    pj.updated_at,
    GREATEST(date_part('epoch'::text, (pj.started_at - pj.created_at)), (0)::double precision) AS queued_secs,
    GREATEST(date_part('epoch'::text, (pj.completed_at - pj.started_at)), (0)::double precision) AS completion_secs,
    GREATEST(date_part('epoch'::text, (pj.canceled_at - pj.started_at)), (0)::double precision) AS canceled_secs,
    GREATEST(date_part('epoch'::text, (max(
        CASE
            WHEN (pjt.stage = 'init'::provisioner_job_timing_stage) THEN pjt.ended_at
            ELSE NULL::timestamp with time zone
        END) - min(
        CASE
            WHEN (pjt.stage = 'init'::provisioner_job_timing_stage) THEN pjt.started_at
            ELSE NULL::timestamp with time zone
        END))), (0)::double precision) AS init_secs,
    GREATEST(date_part('epoch'::text, (max(
        CASE
            WHEN (pjt.stage = 'plan'::provisioner_job_timing_stage) THEN pjt.ended_at
            ELSE NULL::timestamp with time zone
        END) - min(
        CASE
            WHEN (pjt.stage = 'plan'::provisioner_job_timing_stage) THEN pjt.started_at
            ELSE NULL::timestamp with time zone
        END))), (0)::double precision) AS plan_secs,
    GREATEST(date_part('epoch'::text, (max(
        CASE
            WHEN (pjt.stage = 'graph'::provisioner_job_timing_stage) THEN pjt.ended_at
            ELSE NULL::timestamp with time zone
        END) - min(
        CASE
            WHEN (pjt.stage = 'graph'::provisioner_job_timing_stage) THEN pjt.started_at
            ELSE NULL::timestamp with time zone
        END))), (0)::double precision) AS graph_secs,
    GREATEST(date_part('epoch'::text, (max(
        CASE
            WHEN (pjt.stage = 'apply'::provisioner_job_timing_stage) THEN pjt.ended_at
            ELSE NULL::timestamp with time zone
        END) - min(
        CASE
            WHEN (pjt.stage = 'apply'::provisioner_job_timing_stage) THEN pjt.started_at
            ELSE NULL::timestamp with time zone
        END))), (0)::double precision) AS apply_secs
   FROM ((provisioner_jobs pj
     JOIN workspace_builds wb ON ((wb.job_id = pj.id)))
     LEFT JOIN provisioner_job_timings pjt ON ((pjt.job_id = pj.id)))
  GROUP BY pj.id, wb.workspace_id;

CREATE TRIGGER inhibit_enqueue_if_disabled BEFORE INSERT ON notification_messages FOR EACH ROW EXECUTE FUNCTION inhibit_enqueue_if_disabled();

CREATE TRIGGER protect_deleting_organizations BEFORE UPDATE ON organizations FOR EACH ROW WHEN (((new.deleted = true) AND (old.deleted = false))) EXECUTE FUNCTION protect_deleting_organizations();

CREATE TRIGGER remove_organization_member_custom_role BEFORE DELETE ON custom_roles FOR EACH ROW EXECUTE FUNCTION remove_organization_member_role();

COMMENT ON TRIGGER remove_organization_member_custom_role ON custom_roles IS 'When a custom_role is deleted, this trigger removes the role from all organization members.';

CREATE TRIGGER trigger_aggregate_usage_event AFTER INSERT ON usage_events FOR EACH ROW EXECUTE FUNCTION aggregate_usage_event();

CREATE TRIGGER trigger_delete_group_members_on_org_member_delete BEFORE DELETE ON organization_members FOR EACH ROW EXECUTE FUNCTION delete_group_members_on_org_member_delete();

CREATE TRIGGER trigger_delete_oauth2_provider_app_token AFTER DELETE ON oauth2_provider_app_tokens FOR EACH ROW EXECUTE FUNCTION delete_deleted_oauth2_provider_app_token_api_key();

CREATE TRIGGER trigger_insert_apikeys BEFORE INSERT ON api_keys FOR EACH ROW EXECUTE FUNCTION insert_apikey_fail_if_user_deleted();

CREATE TRIGGER trigger_insert_organization_system_roles AFTER INSERT ON organizations FOR EACH ROW EXECUTE FUNCTION insert_organization_system_roles();

CREATE TRIGGER trigger_nullify_next_start_at_on_workspace_autostart_modificati AFTER UPDATE ON workspaces FOR EACH ROW EXECUTE FUNCTION nullify_next_start_at_on_workspace_autostart_modification();

CREATE TRIGGER trigger_update_users AFTER INSERT OR UPDATE ON users FOR EACH ROW WHEN ((new.deleted = true)) EXECUTE FUNCTION delete_deleted_user_resources();

CREATE TRIGGER trigger_upsert_user_links BEFORE INSERT OR UPDATE ON user_links FOR EACH ROW EXECUTE FUNCTION insert_user_links_fail_if_user_deleted();

CREATE TRIGGER trigger_upsert_user_secrets BEFORE INSERT OR UPDATE ON user_secrets FOR EACH ROW EXECUTE FUNCTION insert_user_secret_fail_if_user_deleted();

CREATE TRIGGER trigger_upsert_user_skills BEFORE INSERT OR UPDATE ON user_skills FOR EACH ROW EXECUTE FUNCTION insert_user_skill_fail_if_user_deleted();

CREATE TRIGGER trigger_user_secrets_per_user_limits BEFORE INSERT OR UPDATE ON user_secrets FOR EACH ROW EXECUTE FUNCTION enforce_user_secrets_per_user_limits();

CREATE TRIGGER trigger_user_skills_per_user_limit BEFORE INSERT ON user_skills FOR EACH ROW EXECUTE FUNCTION enforce_user_skills_per_user_limit();

CREATE TRIGGER update_notification_message_dedupe_hash BEFORE INSERT OR UPDATE ON notification_messages FOR EACH ROW EXECUTE FUNCTION compute_notification_message_dedupe_hash();

CREATE TRIGGER user_status_change_trigger AFTER INSERT OR UPDATE ON users FOR EACH ROW EXECUTE FUNCTION record_user_status_change();

CREATE TRIGGER workspace_agent_name_unique_trigger BEFORE INSERT OR UPDATE OF name, resource_id ON workspace_agents FOR EACH ROW EXECUTE FUNCTION check_workspace_agent_name_unique();

COMMENT ON TRIGGER workspace_agent_name_unique_trigger ON workspace_agents IS 'Use a trigger instead of a unique constraint because existing data may violate
the uniqueness requirement. A trigger allows us to enforce uniqueness going
forward without requiring a migration to clean up historical data.';

ALTER TABLE ONLY ai_provider_keys
    ADD CONSTRAINT ai_provider_keys_api_key_key_id_fkey FOREIGN KEY (api_key_key_id) REFERENCES dbcrypt_keys(active_key_digest);

ALTER TABLE ONLY ai_provider_keys
    ADD CONSTRAINT ai_provider_keys_provider_id_fkey FOREIGN KEY (provider_id) REFERENCES ai_providers(id) ON DELETE CASCADE;

ALTER TABLE ONLY ai_providers
    ADD CONSTRAINT ai_providers_settings_key_id_fkey FOREIGN KEY (settings_key_id) REFERENCES dbcrypt_keys(active_key_digest);

ALTER TABLE ONLY ai_seat_state
    ADD CONSTRAINT ai_seat_state_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

ALTER TABLE ONLY aibridge_interceptions
    ADD CONSTRAINT aibridge_interceptions_initiator_id_fkey FOREIGN KEY (initiator_id) REFERENCES users(id);

ALTER TABLE ONLY api_keys
    ADD CONSTRAINT api_keys_user_id_uuid_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

ALTER TABLE ONLY boundary_logs
    ADD CONSTRAINT boundary_logs_session_id_fkey FOREIGN KEY (session_id) REFERENCES boundary_sessions(id) ON DELETE CASCADE;

ALTER TABLE ONLY boundary_sessions
    ADD CONSTRAINT boundary_sessions_workspace_agent_id_fkey FOREIGN KEY (workspace_agent_id) REFERENCES workspace_agents(id);

ALTER TABLE ONLY chat_debug_runs
    ADD CONSTRAINT chat_debug_runs_chat_id_fkey FOREIGN KEY (chat_id) REFERENCES chats(id) ON DELETE CASCADE;

ALTER TABLE ONLY chat_debug_steps
    ADD CONSTRAINT chat_debug_steps_chat_id_fkey FOREIGN KEY (chat_id) REFERENCES chats(id) ON DELETE CASCADE;

ALTER TABLE ONLY chat_diff_statuses
    ADD CONSTRAINT chat_diff_statuses_chat_id_fkey FOREIGN KEY (chat_id) REFERENCES chats(id) ON DELETE CASCADE;

ALTER TABLE ONLY chat_file_links
    ADD CONSTRAINT chat_file_links_chat_id_fkey FOREIGN KEY (chat_id) REFERENCES chats(id) ON DELETE CASCADE;

ALTER TABLE ONLY chat_file_links
    ADD CONSTRAINT chat_file_links_file_id_fkey FOREIGN KEY (file_id) REFERENCES chat_files(id) ON DELETE CASCADE;

ALTER TABLE ONLY chat_files
    ADD CONSTRAINT chat_files_organization_id_fkey FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE;

ALTER TABLE ONLY chat_files
    ADD CONSTRAINT chat_files_owner_id_fkey FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE;

ALTER TABLE ONLY chat_messages
    ADD CONSTRAINT chat_messages_api_key_id_fkey FOREIGN KEY (api_key_id) REFERENCES api_keys(id) ON DELETE SET NULL;

ALTER TABLE ONLY chat_messages
    ADD CONSTRAINT chat_messages_chat_id_fkey FOREIGN KEY (chat_id) REFERENCES chats(id) ON DELETE CASCADE;

ALTER TABLE ONLY chat_messages
    ADD CONSTRAINT chat_messages_model_config_id_fkey FOREIGN KEY (model_config_id) REFERENCES chat_model_configs(id);

ALTER TABLE ONLY chat_model_configs
    ADD CONSTRAINT chat_model_configs_ai_provider_id_fkey FOREIGN KEY (ai_provider_id) REFERENCES ai_providers(id);

ALTER TABLE ONLY chat_model_configs
    ADD CONSTRAINT chat_model_configs_created_by_fkey FOREIGN KEY (created_by) REFERENCES users(id);

ALTER TABLE ONLY chat_model_configs
    ADD CONSTRAINT chat_model_configs_updated_by_fkey FOREIGN KEY (updated_by) REFERENCES users(id);

ALTER TABLE ONLY chat_queued_messages
    ADD CONSTRAINT chat_queued_messages_api_key_id_fkey FOREIGN KEY (api_key_id) REFERENCES api_keys(id) ON DELETE SET NULL;

ALTER TABLE ONLY chat_queued_messages
    ADD CONSTRAINT chat_queued_messages_chat_id_fkey FOREIGN KEY (chat_id) REFERENCES chats(id) ON DELETE CASCADE;

ALTER TABLE ONLY chats
    ADD CONSTRAINT chats_agent_id_fkey FOREIGN KEY (agent_id) REFERENCES workspace_agents(id) ON DELETE SET NULL;

ALTER TABLE ONLY chats
    ADD CONSTRAINT chats_build_id_fkey FOREIGN KEY (build_id) REFERENCES workspace_builds(id) ON DELETE SET NULL;

ALTER TABLE ONLY chats
    ADD CONSTRAINT chats_last_model_config_id_fkey FOREIGN KEY (last_model_config_id) REFERENCES chat_model_configs(id);

ALTER TABLE ONLY chats
    ADD CONSTRAINT chats_organization_id_fkey FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE;

ALTER TABLE ONLY chats
    ADD CONSTRAINT chats_owner_id_fkey FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE;

ALTER TABLE ONLY chats
    ADD CONSTRAINT chats_parent_chat_id_fkey FOREIGN KEY (parent_chat_id) REFERENCES chats(id) ON DELETE SET NULL;

ALTER TABLE ONLY chats
    ADD CONSTRAINT chats_root_chat_id_fkey FOREIGN KEY (root_chat_id) REFERENCES chats(id) ON DELETE SET NULL;

ALTER TABLE ONLY chats
    ADD CONSTRAINT chats_workspace_id_fkey FOREIGN KEY (workspace_id) REFERENCES workspaces(id) ON DELETE SET NULL;

ALTER TABLE ONLY connection_logs
    ADD CONSTRAINT connection_logs_organization_id_fkey FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE;

ALTER TABLE ONLY connection_logs
    ADD CONSTRAINT connection_logs_workspace_id_fkey FOREIGN KEY (workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE;

ALTER TABLE ONLY connection_logs
    ADD CONSTRAINT connection_logs_workspace_owner_id_fkey FOREIGN KEY (workspace_owner_id) REFERENCES users(id) ON DELETE CASCADE;

ALTER TABLE ONLY crypto_keys
    ADD CONSTRAINT crypto_keys_secret_key_id_fkey FOREIGN KEY (secret_key_id) REFERENCES dbcrypt_keys(active_key_digest);

ALTER TABLE ONLY chat_debug_steps
    ADD CONSTRAINT fk_chat_debug_steps_run_chat FOREIGN KEY (run_id, chat_id) REFERENCES chat_debug_runs(id, chat_id) ON DELETE CASCADE;

ALTER TABLE ONLY oauth2_provider_app_tokens
    ADD CONSTRAINT fk_oauth2_provider_app_tokens_user_id FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

ALTER TABLE ONLY external_auth_links
    ADD CONSTRAINT git_auth_links_oauth_access_token_key_id_fkey FOREIGN KEY (oauth_access_token_key_id) REFERENCES dbcrypt_keys(active_key_digest);

ALTER TABLE ONLY external_auth_links
    ADD CONSTRAINT git_auth_links_oauth_refresh_token_key_id_fkey FOREIGN KEY (oauth_refresh_token_key_id) REFERENCES dbcrypt_keys(active_key_digest);

ALTER TABLE ONLY gitsshkeys
    ADD CONSTRAINT gitsshkeys_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id);

ALTER TABLE ONLY group_ai_budgets
    ADD CONSTRAINT group_ai_budgets_group_id_fkey FOREIGN KEY (group_id) REFERENCES groups(id) ON DELETE CASCADE;

ALTER TABLE ONLY group_members
    ADD CONSTRAINT group_members_group_id_fkey FOREIGN KEY (group_id) REFERENCES groups(id) ON DELETE CASCADE;

ALTER TABLE ONLY group_members
    ADD CONSTRAINT group_members_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

ALTER TABLE ONLY groups
    ADD CONSTRAINT groups_organization_id_fkey FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE;

ALTER TABLE ONLY inbox_notifications
    ADD CONSTRAINT inbox_notifications_template_id_fkey FOREIGN KEY (template_id) REFERENCES notification_templates(id) ON DELETE CASCADE;

ALTER TABLE ONLY inbox_notifications
    ADD CONSTRAINT inbox_notifications_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

ALTER TABLE ONLY jfrog_xray_scans
    ADD CONSTRAINT jfrog_xray_scans_agent_id_fkey FOREIGN KEY (agent_id) REFERENCES workspace_agents(id) ON DELETE CASCADE;

ALTER TABLE ONLY jfrog_xray_scans
    ADD CONSTRAINT jfrog_xray_scans_workspace_id_fkey FOREIGN KEY (workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE;

ALTER TABLE ONLY mcp_server_configs
    ADD CONSTRAINT mcp_server_configs_api_key_value_key_id_fkey FOREIGN KEY (api_key_value_key_id) REFERENCES dbcrypt_keys(active_key_digest);

ALTER TABLE ONLY mcp_server_configs
    ADD CONSTRAINT mcp_server_configs_created_by_fkey FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL;

ALTER TABLE ONLY mcp_server_configs
    ADD CONSTRAINT mcp_server_configs_custom_headers_key_id_fkey FOREIGN KEY (custom_headers_key_id) REFERENCES dbcrypt_keys(active_key_digest);

ALTER TABLE ONLY mcp_server_configs
    ADD CONSTRAINT mcp_server_configs_oauth2_client_secret_key_id_fkey FOREIGN KEY (oauth2_client_secret_key_id) REFERENCES dbcrypt_keys(active_key_digest);

ALTER TABLE ONLY mcp_server_configs
    ADD CONSTRAINT mcp_server_configs_updated_by_fkey FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL;

ALTER TABLE ONLY mcp_server_user_tokens
    ADD CONSTRAINT mcp_server_user_tokens_access_token_key_id_fkey FOREIGN KEY (access_token_key_id) REFERENCES dbcrypt_keys(active_key_digest);

ALTER TABLE ONLY mcp_server_user_tokens
    ADD CONSTRAINT mcp_server_user_tokens_mcp_server_config_id_fkey FOREIGN KEY (mcp_server_config_id) REFERENCES mcp_server_configs(id) ON DELETE CASCADE;

ALTER TABLE ONLY mcp_server_user_tokens
    ADD CONSTRAINT mcp_server_user_tokens_refresh_token_key_id_fkey FOREIGN KEY (refresh_token_key_id) REFERENCES dbcrypt_keys(active_key_digest);

ALTER TABLE ONLY mcp_server_user_tokens
    ADD CONSTRAINT mcp_server_user_tokens_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

ALTER TABLE ONLY notification_messages
    ADD CONSTRAINT notification_messages_notification_template_id_fkey FOREIGN KEY (notification_template_id) REFERENCES notification_templates(id) ON DELETE CASCADE;

ALTER TABLE ONLY notification_messages
    ADD CONSTRAINT notification_messages_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

ALTER TABLE ONLY notification_preferences
    ADD CONSTRAINT notification_preferences_notification_template_id_fkey FOREIGN KEY (notification_template_id) REFERENCES notification_templates(id) ON DELETE CASCADE;

ALTER TABLE ONLY notification_preferences
    ADD CONSTRAINT notification_preferences_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

ALTER TABLE ONLY oauth2_provider_app_codes
    ADD CONSTRAINT oauth2_provider_app_codes_app_id_fkey FOREIGN KEY (app_id) REFERENCES oauth2_provider_apps(id) ON DELETE CASCADE;

ALTER TABLE ONLY oauth2_provider_app_codes
    ADD CONSTRAINT oauth2_provider_app_codes_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

ALTER TABLE ONLY oauth2_provider_app_secrets
    ADD CONSTRAINT oauth2_provider_app_secrets_app_id_fkey FOREIGN KEY (app_id) REFERENCES oauth2_provider_apps(id) ON DELETE CASCADE;

ALTER TABLE ONLY oauth2_provider_app_tokens
    ADD CONSTRAINT oauth2_provider_app_tokens_api_key_id_fkey FOREIGN KEY (api_key_id) REFERENCES api_keys(id) ON DELETE CASCADE;

ALTER TABLE ONLY oauth2_provider_app_tokens
    ADD CONSTRAINT oauth2_provider_app_tokens_app_secret_id_fkey FOREIGN KEY (app_secret_id) REFERENCES oauth2_provider_app_secrets(id) ON DELETE CASCADE;

ALTER TABLE ONLY organization_members
    ADD CONSTRAINT organization_members_organization_id_uuid_fkey FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE;

ALTER TABLE ONLY organization_members
    ADD CONSTRAINT organization_members_user_id_uuid_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

ALTER TABLE ONLY parameter_schemas
    ADD CONSTRAINT parameter_schemas_job_id_fkey FOREIGN KEY (job_id) REFERENCES provisioner_jobs(id) ON DELETE CASCADE;

ALTER TABLE ONLY provisioner_daemons
    ADD CONSTRAINT provisioner_daemons_key_id_fkey FOREIGN KEY (key_id) REFERENCES provisioner_keys(id) ON DELETE CASCADE;

ALTER TABLE ONLY provisioner_daemons
    ADD CONSTRAINT provisioner_daemons_organization_id_fkey FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE;

ALTER TABLE ONLY provisioner_job_logs
    ADD CONSTRAINT provisioner_job_logs_job_id_fkey FOREIGN KEY (job_id) REFERENCES provisioner_jobs(id) ON DELETE CASCADE;

ALTER TABLE ONLY provisioner_job_timings
    ADD CONSTRAINT provisioner_job_timings_job_id_fkey FOREIGN KEY (job_id) REFERENCES provisioner_jobs(id) ON DELETE CASCADE;

ALTER TABLE ONLY provisioner_jobs
    ADD CONSTRAINT provisioner_jobs_organization_id_fkey FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE;

ALTER TABLE ONLY provisioner_keys
    ADD CONSTRAINT provisioner_keys_organization_id_fkey FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE;

ALTER TABLE ONLY tailnet_peers
    ADD CONSTRAINT tailnet_peers_coordinator_id_fkey FOREIGN KEY (coordinator_id) REFERENCES tailnet_coordinators(id) ON DELETE CASCADE;

ALTER TABLE ONLY tailnet_tunnels
    ADD CONSTRAINT tailnet_tunnels_coordinator_id_fkey FOREIGN KEY (coordinator_id) REFERENCES tailnet_coordinators(id) ON DELETE CASCADE;

ALTER TABLE ONLY task_snapshots
    ADD CONSTRAINT task_snapshots_task_id_fkey FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE;

ALTER TABLE ONLY task_workspace_apps
    ADD CONSTRAINT task_workspace_apps_task_id_fkey FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE;

ALTER TABLE ONLY task_workspace_apps
    ADD CONSTRAINT task_workspace_apps_workspace_agent_id_fkey FOREIGN KEY (workspace_agent_id) REFERENCES workspace_agents(id) ON DELETE CASCADE;

ALTER TABLE ONLY task_workspace_apps
    ADD CONSTRAINT task_workspace_apps_workspace_app_id_fkey FOREIGN KEY (workspace_app_id) REFERENCES workspace_apps(id) ON DELETE CASCADE;

ALTER TABLE ONLY tasks
    ADD CONSTRAINT tasks_organization_id_fkey FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE;

ALTER TABLE ONLY tasks
    ADD CONSTRAINT tasks_owner_id_fkey FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE;

ALTER TABLE ONLY tasks
    ADD CONSTRAINT tasks_template_version_id_fkey FOREIGN KEY (template_version_id) REFERENCES template_versions(id) ON DELETE CASCADE;

ALTER TABLE ONLY tasks
    ADD CONSTRAINT tasks_workspace_id_fkey FOREIGN KEY (workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE;

ALTER TABLE ONLY template_version_parameters
    ADD CONSTRAINT template_version_parameters_template_version_id_fkey FOREIGN KEY (template_version_id) REFERENCES template_versions(id) ON DELETE CASCADE;

ALTER TABLE ONLY template_version_preset_parameters
    ADD CONSTRAINT template_version_preset_paramet_template_version_preset_id_fkey FOREIGN KEY (template_version_preset_id) REFERENCES template_version_presets(id) ON DELETE CASCADE;

ALTER TABLE ONLY template_version_preset_prebuild_schedules
    ADD CONSTRAINT template_version_preset_prebuild_schedules_preset_id_fkey FOREIGN KEY (preset_id) REFERENCES template_version_presets(id) ON DELETE CASCADE;

ALTER TABLE ONLY template_version_presets
    ADD CONSTRAINT template_version_presets_template_version_id_fkey FOREIGN KEY (template_version_id) REFERENCES template_versions(id) ON DELETE CASCADE;

ALTER TABLE ONLY template_version_terraform_values
    ADD CONSTRAINT template_version_terraform_values_cached_module_files_fkey FOREIGN KEY (cached_module_files) REFERENCES files(id);

ALTER TABLE ONLY template_version_terraform_values
    ADD CONSTRAINT template_version_terraform_values_template_version_id_fkey FOREIGN KEY (template_version_id) REFERENCES template_versions(id) ON DELETE CASCADE;

ALTER TABLE ONLY template_version_variables
    ADD CONSTRAINT template_version_variables_template_version_id_fkey FOREIGN KEY (template_version_id) REFERENCES template_versions(id) ON DELETE CASCADE;

ALTER TABLE ONLY template_version_workspace_tags
    ADD CONSTRAINT template_version_workspace_tags_template_version_id_fkey FOREIGN KEY (template_version_id) REFERENCES template_versions(id) ON DELETE CASCADE;

ALTER TABLE ONLY template_versions
    ADD CONSTRAINT template_versions_created_by_fkey FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE RESTRICT;

ALTER TABLE ONLY template_versions
    ADD CONSTRAINT template_versions_organization_id_fkey FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE;

ALTER TABLE ONLY template_versions
    ADD CONSTRAINT template_versions_template_id_fkey FOREIGN KEY (template_id) REFERENCES templates(id) ON DELETE CASCADE;

ALTER TABLE ONLY templates
    ADD CONSTRAINT templates_created_by_fkey FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE RESTRICT;

ALTER TABLE ONLY templates
    ADD CONSTRAINT templates_organization_id_fkey FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE;

ALTER TABLE ONLY user_ai_provider_keys
    ADD CONSTRAINT user_ai_provider_keys_ai_provider_id_fkey FOREIGN KEY (ai_provider_id) REFERENCES ai_providers(id) ON DELETE CASCADE;

ALTER TABLE ONLY user_ai_provider_keys
    ADD CONSTRAINT user_ai_provider_keys_api_key_key_id_fkey FOREIGN KEY (api_key_key_id) REFERENCES dbcrypt_keys(active_key_digest);

ALTER TABLE ONLY user_ai_provider_keys
    ADD CONSTRAINT user_ai_provider_keys_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

ALTER TABLE ONLY user_configs
    ADD CONSTRAINT user_configs_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

ALTER TABLE ONLY user_deleted
    ADD CONSTRAINT user_deleted_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id);

ALTER TABLE ONLY user_links
    ADD CONSTRAINT user_links_oauth_access_token_key_id_fkey FOREIGN KEY (oauth_access_token_key_id) REFERENCES dbcrypt_keys(active_key_digest);

ALTER TABLE ONLY user_links
    ADD CONSTRAINT user_links_oauth_refresh_token_key_id_fkey FOREIGN KEY (oauth_refresh_token_key_id) REFERENCES dbcrypt_keys(active_key_digest);

ALTER TABLE ONLY user_links
    ADD CONSTRAINT user_links_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

ALTER TABLE ONLY user_secrets
    ADD CONSTRAINT user_secrets_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

ALTER TABLE ONLY user_secrets
    ADD CONSTRAINT user_secrets_value_key_id_fkey FOREIGN KEY (value_key_id) REFERENCES dbcrypt_keys(active_key_digest);

ALTER TABLE ONLY user_skills
    ADD CONSTRAINT user_skills_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

ALTER TABLE ONLY user_status_changes
    ADD CONSTRAINT user_status_changes_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id);

ALTER TABLE ONLY webpush_subscriptions
    ADD CONSTRAINT webpush_subscriptions_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

ALTER TABLE ONLY workspace_agent_devcontainers
    ADD CONSTRAINT workspace_agent_devcontainers_subagent_id_fkey FOREIGN KEY (subagent_id) REFERENCES workspace_agents(id) ON DELETE CASCADE;

ALTER TABLE ONLY workspace_agent_devcontainers
    ADD CONSTRAINT workspace_agent_devcontainers_workspace_agent_id_fkey FOREIGN KEY (workspace_agent_id) REFERENCES workspace_agents(id) ON DELETE CASCADE;

ALTER TABLE ONLY workspace_agent_log_sources
    ADD CONSTRAINT workspace_agent_log_sources_workspace_agent_id_fkey FOREIGN KEY (workspace_agent_id) REFERENCES workspace_agents(id) ON DELETE CASCADE;

ALTER TABLE ONLY workspace_agent_memory_resource_monitors
    ADD CONSTRAINT workspace_agent_memory_resource_monitors_agent_id_fkey FOREIGN KEY (agent_id) REFERENCES workspace_agents(id) ON DELETE CASCADE;

ALTER TABLE ONLY workspace_agent_metadata
    ADD CONSTRAINT workspace_agent_metadata_workspace_agent_id_fkey FOREIGN KEY (workspace_agent_id) REFERENCES workspace_agents(id) ON DELETE CASCADE;

ALTER TABLE ONLY workspace_agent_port_share
    ADD CONSTRAINT workspace_agent_port_share_workspace_id_fkey FOREIGN KEY (workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE;

ALTER TABLE ONLY workspace_agent_script_timings
    ADD CONSTRAINT workspace_agent_script_timings_script_id_fkey FOREIGN KEY (script_id) REFERENCES workspace_agent_scripts(id) ON DELETE CASCADE;

ALTER TABLE ONLY workspace_agent_scripts
    ADD CONSTRAINT workspace_agent_scripts_workspace_agent_id_fkey FOREIGN KEY (workspace_agent_id) REFERENCES workspace_agents(id) ON DELETE CASCADE;

ALTER TABLE ONLY workspace_agent_logs
    ADD CONSTRAINT workspace_agent_startup_logs_agent_id_fkey FOREIGN KEY (agent_id) REFERENCES workspace_agents(id) ON DELETE CASCADE;

ALTER TABLE ONLY workspace_agent_volume_resource_monitors
    ADD CONSTRAINT workspace_agent_volume_resource_monitors_agent_id_fkey FOREIGN KEY (agent_id) REFERENCES workspace_agents(id) ON DELETE CASCADE;

ALTER TABLE ONLY workspace_agents
    ADD CONSTRAINT workspace_agents_parent_id_fkey FOREIGN KEY (parent_id) REFERENCES workspace_agents(id) ON DELETE CASCADE;

ALTER TABLE ONLY workspace_agents
    ADD CONSTRAINT workspace_agents_resource_id_fkey FOREIGN KEY (resource_id) REFERENCES workspace_resources(id) ON DELETE CASCADE;

ALTER TABLE ONLY workspace_app_audit_sessions
    ADD CONSTRAINT workspace_app_audit_sessions_agent_id_fkey FOREIGN KEY (agent_id) REFERENCES workspace_agents(id) ON DELETE CASCADE;

ALTER TABLE ONLY workspace_app_stats
    ADD CONSTRAINT workspace_app_stats_agent_id_fkey FOREIGN KEY (agent_id) REFERENCES workspace_agents(id);

ALTER TABLE ONLY workspace_app_stats
    ADD CONSTRAINT workspace_app_stats_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id);

ALTER TABLE ONLY workspace_app_stats
    ADD CONSTRAINT workspace_app_stats_workspace_id_fkey FOREIGN KEY (workspace_id) REFERENCES workspaces(id);

ALTER TABLE ONLY workspace_app_statuses
    ADD CONSTRAINT workspace_app_statuses_agent_id_fkey FOREIGN KEY (agent_id) REFERENCES workspace_agents(id);

ALTER TABLE ONLY workspace_app_statuses
    ADD CONSTRAINT workspace_app_statuses_app_id_fkey FOREIGN KEY (app_id) REFERENCES workspace_apps(id);

ALTER TABLE ONLY workspace_app_statuses
    ADD CONSTRAINT workspace_app_statuses_workspace_id_fkey FOREIGN KEY (workspace_id) REFERENCES workspaces(id);

ALTER TABLE ONLY workspace_apps
    ADD CONSTRAINT workspace_apps_agent_id_fkey FOREIGN KEY (agent_id) REFERENCES workspace_agents(id) ON DELETE CASCADE;

ALTER TABLE ONLY workspace_build_parameters
    ADD CONSTRAINT workspace_build_parameters_workspace_build_id_fkey FOREIGN KEY (workspace_build_id) REFERENCES workspace_builds(id) ON DELETE CASCADE;

ALTER TABLE ONLY workspace_builds
    ADD CONSTRAINT workspace_builds_job_id_fkey FOREIGN KEY (job_id) REFERENCES provisioner_jobs(id) ON DELETE CASCADE;

ALTER TABLE ONLY workspace_builds
    ADD CONSTRAINT workspace_builds_template_version_id_fkey FOREIGN KEY (template_version_id) REFERENCES template_versions(id) ON DELETE CASCADE;

ALTER TABLE ONLY workspace_builds
    ADD CONSTRAINT workspace_builds_template_version_preset_id_fkey FOREIGN KEY (template_version_preset_id) REFERENCES template_version_presets(id) ON DELETE SET NULL;

ALTER TABLE ONLY workspace_builds
    ADD CONSTRAINT workspace_builds_workspace_id_fkey FOREIGN KEY (workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE;

ALTER TABLE ONLY workspace_modules
    ADD CONSTRAINT workspace_modules_job_id_fkey FOREIGN KEY (job_id) REFERENCES provisioner_jobs(id) ON DELETE CASCADE;

ALTER TABLE ONLY workspace_resource_metadata
    ADD CONSTRAINT workspace_resource_metadata_workspace_resource_id_fkey FOREIGN KEY (workspace_resource_id) REFERENCES workspace_resources(id) ON DELETE CASCADE;

ALTER TABLE ONLY workspace_resources
    ADD CONSTRAINT workspace_resources_job_id_fkey FOREIGN KEY (job_id) REFERENCES provisioner_jobs(id) ON DELETE CASCADE;

ALTER TABLE ONLY workspaces
    ADD CONSTRAINT workspaces_organization_id_fkey FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE RESTRICT;

ALTER TABLE ONLY workspaces
    ADD CONSTRAINT workspaces_owner_id_fkey FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE RESTRICT;

ALTER TABLE ONLY workspaces
    ADD CONSTRAINT workspaces_template_id_fkey FOREIGN KEY (template_id) REFERENCES templates(id) ON DELETE RESTRICT;

