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

);

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'

);

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

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

);

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,