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

'all',

'application_connect'

);

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'

);

CREATE TYPE automatic_updates AS ENUM (

'always',

'never'

);

CREATE TYPE build_reason AS ENUM (

'initiator',

'autostart',

'autostop',

'dormancy',

'failedstop',

'autodelete'

);

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'

);

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'

);

CREATE TYPE startup_script_behavior AS ENUM (

'blocking',

'non-blocking'

);

CREATE DOMAIN tagset AS jsonb;

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

CREATE TYPE tailnet_status AS ENUM (

'ok',

'lost'

);

CREATE TYPE user_status AS ENUM (

'active',

'suspended',

'dormant'

);

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

CREATE TYPE workspace_agent_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 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;

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 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_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 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 FUNCTION tailnet_notify_agent_change() RETURNS trigger

LANGUAGE plpgsql

AS $$

BEGIN

IF (OLD IS NOT NULL) THEN

PERFORM pg_notify('tailnet_agent_update', OLD.id::text);

RETURN NULL;

END IF;

IF (NEW IS NOT NULL) THEN

PERFORM pg_notify('tailnet_agent_update', NEW.id::text);

RETURN NULL;

END IF;

END;

$$;

CREATE FUNCTION tailnet_notify_client_change() RETURNS trigger

LANGUAGE plpgsql

AS $$

DECLARE

var_client_id uuid;

var_coordinator_id uuid;

var_agent_ids uuid[];

var_agent_id uuid;

BEGIN

IF (NEW.id IS NOT NULL) THEN

var_client_id = NEW.id;

var_coordinator_id = NEW.coordinator_id;

ELSIF (OLD.id IS NOT NULL) THEN

var_client_id = OLD.id;

var_coordinator_id = OLD.coordinator_id;

END IF;

-- Read all agents the client is subscribed to, so we can notify them.

SELECT

array_agg(agent_id)

INTO

var_agent_ids

FROM

tailnet_client_subscriptions subs

WHERE

subs.client_id = NEW.id AND

subs.coordinator_id = NEW.coordinator_id;

-- No agents to notify

if (var_agent_ids IS NULL) THEN

return NULL;

END IF;

-- pg_notify is limited to 8k bytes, which is approximately 221 UUIDs.

-- Instead of sending all agent ids in a single update, send one for each

-- agent id to prevent overflow.

FOREACH var_agent_id IN ARRAY var_agent_ids

LOOP

PERFORM pg_notify('tailnet_client_update', var_client_id || ',' || var_agent_id);

END LOOP;

return NULL;

END;

$$;

CREATE FUNCTION tailnet_notify_client_subscription_change() RETURNS trigger

LANGUAGE plpgsql

AS $$

BEGIN

IF (NEW IS NOT NULL) THEN

PERFORM pg_notify('tailnet_client_update', NEW.client_id || ',' || NEW.agent_id);

RETURN NULL;

ELSIF (OLD IS NOT NULL) THEN

PERFORM pg_notify('tailnet_client_update', OLD.client_id || ',' || OLD.agent_id);

RETURN NULL;

END IF;

END;

$$;

CREATE FUNCTION tailnet_notify_coordinator_heartbeat() RETURNS trigger

LANGUAGE plpgsql

AS $$

BEGIN

PERFORM pg_notify('tailnet_coordinator_heartbeat', NEW.id::text);

RETURN NULL;

END;

$$;

CREATE FUNCTION tailnet_notify_peer_change() RETURNS trigger

LANGUAGE plpgsql

AS $$

BEGIN

IF (OLD IS NOT NULL) THEN

PERFORM pg_notify('tailnet_peer_update', OLD.id::text);

RETURN NULL;

END IF;

IF (NEW IS NOT NULL) THEN

PERFORM pg_notify('tailnet_peer_update', NEW.id::text);

RETURN NULL;

END IF;

END;

$$;

CREATE FUNCTION tailnet_notify_tunnel_change() RETURNS trigger

LANGUAGE plpgsql

AS $$

BEGIN

IF (NEW IS NOT NULL) THEN

PERFORM pg_notify('tailnet_tunnel_update', NEW.src_id || ',' || NEW.dst_id);

RETURN NULL;

ELSIF (OLD IS NOT NULL) THEN

PERFORM pg_notify('tailnet_tunnel_update', OLD.src_id || ',' || OLD.dst_id);

RETURN NULL;

END IF;

END;

$$;

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,

scope api_key_scope DEFAULT 'all'::api_key_scope NOT NULL,

token_name text DEFAULT ''::text NOT NULL

);

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

);

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

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

);

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

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

);

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

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

);

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

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,

groups.organization_id,

groups.name AS group_name,

all_members.group_id

FROM ((all_members