-- 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 build_reason AS ENUM (
    'initiator',
    'autostart',
    'autostop',
    'autolock',
    'failedstop',
    'autodelete'
);

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

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

CREATE TYPE startup_script_behavior AS ENUM (
    'blocking',
    'non-blocking'
);

CREATE TYPE user_status AS ENUM (
    'active',
    'suspended',
    'dormant'
);

COMMENT ON TYPE user_status IS 'Defines the user 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_log_source AS ENUM (
    'startup_script',
    'shutdown_script',
    'kubernetes_logs',
    'envbox',
    'envbuilder',
    'external'
);

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_user_api_keys() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
BEGIN
	IF (NEW.deleted) THEN
		DELETE FROM api_keys
		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 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 $$
BEGIN
	IF (OLD IS NOT NULL) THEN
		PERFORM pg_notify('tailnet_client_update', OLD.id || ',' || OLD.agent_id);
		RETURN NULL;
	END IF;
	IF (NEW IS NOT NULL) THEN
		PERFORM pg_notify('tailnet_client_update', NEW.id || ',' || NEW.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 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 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 git_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
);

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

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,
    updated_at timestamp with time zone,
    name character varying(64) NOT NULL,
    provisioners provisioner_type[] NOT NULL,
    replica_id uuid,
    tags jsonb DEFAULT '{}'::jsonb NOT NULL
);

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

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_clients (
    id uuid NOT NULL,
    coordinator_id uuid NOT NULL,
    agent_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 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,
    git_auth_providers text[],
    message character varying(1048576) DEFAULT ''::character varying NOT NULL
);

COMMENT ON COLUMN template_versions.git_auth_providers IS 'IDs of Git 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,
    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
);

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

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.git_auth_providers,
    template_versions.message,
    COALESCE(visible_users.avatar_url, ''::text) AS created_by_avatar_url,
    COALESCE(visible_users.username, ''::text) AS created_by_username
   FROM (public.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,
    max_ttl bigint DEFAULT '0'::bigint 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,
    restart_requirement_days_of_week smallint DEFAULT 0 NOT NULL,
    restart_requirement_weeks bigint DEFAULT 0 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.restart_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.restart_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.';

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.max_ttl,
    templates.allow_user_autostart,
    templates.allow_user_autostop,
    templates.failure_ttl,
    templates.time_til_dormant,
    templates.time_til_dormant_autodelete,
    templates.restart_requirement_days_of_week,
    templates.restart_requirement_weeks,
    COALESCE(visible_users.avatar_url, ''::text) AS created_by_avatar_url,
    COALESCE(visible_users.username, ''::text) AS created_by_username
   FROM (public.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
);

CREATE 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,
    source workspace_agent_log_source DEFAULT 'startup_script'::workspace_agent_log_source 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
);

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

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,
    startup_script character varying(65534),
    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,
    startup_script_timeout_seconds integer DEFAULT 0 NOT NULL,
    expanded_directory character varying(4096) DEFAULT ''::character varying NOT NULL,
    shutdown_script character varying(65534),
    shutdown_script_timeout_seconds integer DEFAULT 0 NOT NULL,
    logs_length integer DEFAULT 0 NOT NULL,
    logs_overflowed boolean DEFAULT false NOT NULL,
    startup_script_behavior startup_script_behavior DEFAULT 'non-blocking'::startup_script_behavior NOT NULL,
    started_at timestamp with time zone,
    ready_at timestamp with time zone,
    subsystems workspace_agent_subsystem[] DEFAULT '{}'::workspace_agent_subsystem[],
    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.startup_script_timeout_seconds IS 'The number of seconds to wait for the startup script to complete. If the script does not complete within this time, the agent lifecycle will be marked as start_timeout.';

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.shutdown_script IS 'Script that is executed before the agent is stopped.';

COMMENT ON COLUMN workspace_agents.shutdown_script_timeout_seconds IS 'The number of seconds to wait for the shutdown script to complete. If the script does not complete within this time, the agent lifecycle will be marked as shutdown_timeout.';

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.startup_script_behavior IS 'When startup script behavior is non-blocking, the workspace will be ready and accessible upon agent connection, when it is blocking, workspace will wait for the startup script to complete before becoming ready and accessible.';

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

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

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

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.provisioner_state,
    workspace_builds.job_id,
    workspace_builds.deadline,
    workspace_builds.reason,
    workspace_builds.daily_cost,
    workspace_builds.max_deadline,
    COALESCE(visible_users.avatar_url, ''::text) AS initiator_by_avatar_url,
    COALESCE(visible_users.username, ''::text) AS initiator_by_username
   FROM (public.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 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
);

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

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 without time zone DEFAULT '0001-01-01 00:00:00'::timestamp without time zone NOT NULL,
    dormant_at timestamp with time zone,
    deleting_at timestamp with time zone
);

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 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 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 git_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_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 licenses
    ADD CONSTRAINT licenses_jwt_key UNIQUE (jwt);

ALTER TABLE ONLY licenses
    ADD CONSTRAINT licenses_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_name_key UNIQUE (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 site_configs
    ADD CONSTRAINT site_configs_key_key UNIQUE (key);

ALTER TABLE ONLY tailnet_agents
    ADD CONSTRAINT tailnet_agents_pkey PRIMARY KEY (id, coordinator_id);

ALTER TABLE ONLY tailnet_clients
    ADD CONSTRAINT tailnet_clients_pkey PRIMARY KEY (id, coordinator_id);

ALTER TABLE ONLY tailnet_coordinators
    ADD CONSTRAINT tailnet_coordinators_pkey PRIMARY KEY (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_variables
    ADD CONSTRAINT template_version_variables_template_version_id_name_key UNIQUE (template_version_id, name);

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 user_links
    ADD CONSTRAINT user_links_pkey PRIMARY KEY (user_id, login_type);

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

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

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

ALTER TABLE ONLY workspace_agents
    ADD CONSTRAINT workspace_agents_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_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 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 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_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 ON organizations USING btree (name);

CREATE UNIQUE INDEX idx_organization_name_lower ON organizations USING btree (lower(name));

CREATE INDEX idx_tailnet_agents_coordinator ON tailnet_agents USING btree (coordinator_id);

CREATE INDEX idx_tailnet_clients_agent ON tailnet_clients USING btree (agent_id);

CREATE INDEX idx_tailnet_clients_coordinator ON tailnet_clients USING btree (coordinator_id);

CREATE UNIQUE INDEX idx_users_email ON users USING btree (email) WHERE (deleted = false);

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

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 UNIQUE INDEX templates_organization_id_name_idx ON templates USING btree (organization_id, lower((name)::text)) WHERE (deleted = false);

CREATE UNIQUE INDEX users_email_lower_idx ON users USING btree (lower(email)) WHERE (deleted = false);

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

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

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 INDEX workspace_app_stats_workspace_id_idx ON workspace_app_stats USING btree (workspace_id);

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 UNIQUE INDEX workspaces_owner_id_lower_idx ON workspaces USING btree (owner_id, lower((name)::text)) WHERE (deleted = false);

CREATE TRIGGER tailnet_notify_agent_change AFTER INSERT OR DELETE OR UPDATE ON tailnet_agents FOR EACH ROW EXECUTE FUNCTION tailnet_notify_agent_change();

CREATE TRIGGER tailnet_notify_client_change AFTER INSERT OR DELETE OR UPDATE ON tailnet_clients FOR EACH ROW EXECUTE FUNCTION tailnet_notify_client_change();

CREATE TRIGGER tailnet_notify_coordinator_heartbeat AFTER INSERT OR UPDATE ON tailnet_coordinators FOR EACH ROW EXECUTE FUNCTION tailnet_notify_coordinator_heartbeat();

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

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

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 gitsshkeys
    ADD CONSTRAINT gitsshkeys_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id);

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 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_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_jobs
    ADD CONSTRAINT provisioner_jobs_organization_id_fkey FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE;

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

ALTER TABLE ONLY tailnet_clients
    ADD CONSTRAINT tailnet_clients_coordinator_id_fkey FOREIGN KEY (coordinator_id) REFERENCES tailnet_coordinators(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_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_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_links
    ADD CONSTRAINT user_links_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(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_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_agents
    ADD CONSTRAINT workspace_agents_resource_id_fkey FOREIGN KEY (resource_id) REFERENCES workspace_resources(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_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_workspace_id_fkey FOREIGN KEY (workspace_id) REFERENCES workspaces(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;

