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

CREATE TYPE api_key_scope AS ENUM (

'all',

'application_connect'

);

CREATE TYPE app_sharing_level AS ENUM (

'owner',

'authenticated',

'public'

);

CREATE TYPE audit_action AS ENUM (

'create',

'write',

'delete',

'start',

'stop',

'login',

'logout',

'register'

);

CREATE TYPE automatic_updates AS ENUM (

'always',

'never'

);

CREATE TYPE build_reason AS ENUM (

'initiator',

'autostart',

'autostop',

'dormancy',

'failedstop',

'autodelete'

);

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

'none',

'environment_variable',

'provisioner_variable'

);

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

);

CREATE TYPE startup_script_behavior AS ENUM (

'blocking',

'non-blocking'

);

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

'envbuilder',

'envbox',

'none',

'exectrace'

);

CREATE TYPE workspace_app_health AS ENUM (

'disabled',

'initializing',

'healthy',

'unhealthy'

);

CREATE TYPE workspace_transition AS ENUM (

'start',

'stop',

'delete'

);

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

);

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

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

);

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

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

);

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

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 '{organization-member}'::text[] NOT NULL

);

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

);

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

);

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

);

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

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 character varying(8192) NOT NULL

);

CREATE TABLE tailnet_agents (

id uuid NOT NULL,

coordinator_id uuid NOT NULL,

updated_at timestamp with time zone NOT NULL,

node jsonb NOT NULL

);

CREATE TABLE tailnet_client_subscriptions (

client_id uuid NOT NULL,

coordinator_id uuid NOT NULL,

agent_id uuid NOT NULL,

updated_at timestamp with time zone NOT NULL

);

CREATE TABLE tailnet_clients (

id uuid NOT NULL,

coordinator_id uuid NOT NULL,

updated_at timestamp with time zone NOT NULL,

node jsonb NOT NULL

);

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

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

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

);

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

theme_preference text DEFAULT ''::text NOT NULL,

name text DEFAULT ''::text 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.theme_preference IS '"" can be interpreted as "the user does not care", falling back to the default theme';

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

CREATE VIEW visible_users AS

SELECT users.id,

users.username,

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

COALESCE(visible_users.avatar_url, ''::text) AS created_by_avatar_url,

COALESCE(visible_users.username, ''::text) AS created_by_username

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

);

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

CREATE VIEW template_with_users 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,

COALESCE(visible_users.avatar_url, ''::text) AS created_by_avatar_url,

COALESCE(visible_users.username, ''::text) AS created_by_username

FROM (templates

LEFT JOIN visible_users ON ((templates.created_by = visible_users.id)));

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

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,

debug_context 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.debug_context IS 'Debug information includes information like id_token and userinfo claims.';

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,