72 lines
2.8 KiB
PL/PgSQL
72 lines
2.8 KiB
PL/PgSQL
BEGIN;
|
|
|
|
CREATE INDEX IF NOT EXISTS sessions_user_id_useridNN_idx ON sessions (user_id) WHERE user_id IS NOT NULL;
|
|
CREATE INDEX IF NOT EXISTS sessions_uid_projectid_startts_sessionid_uidNN_durGTZ_idx ON sessions (user_id, project_id, start_ts, session_id) WHERE user_id IS NOT NULL AND duration > 0;
|
|
CREATE INDEX IF NOT EXISTS pages_base_path_base_pathLNGT2_idx ON events.pages (base_path) WHERE length(base_path) > 2;
|
|
|
|
CREATE INDEX IF NOT EXISTS users_tenant_id_deleted_at_N_idx ON users (tenant_id) WHERE deleted_at ISNULL;
|
|
CREATE INDEX IF NOT EXISTS issues_issue_id_timestamp_idx ON events_common.issues (issue_id, timestamp);
|
|
CREATE INDEX IF NOT EXISTS issues_timestamp_idx ON events_common.issues (timestamp);
|
|
CREATE INDEX IF NOT EXISTS issues_project_id_issue_id_idx ON public.issues (project_id, issue_id);
|
|
|
|
CREATE TABLE roles
|
|
(
|
|
role_id integer generated BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
tenant_id integer NOT NULL REFERENCES tenants (tenant_id) ON DELETE CASCADE,
|
|
name text NOT NULL,
|
|
description text DEFAULT NULL,
|
|
permissions text[] NOT NULL DEFAULT '{}',
|
|
protected bool NOT NULL DEFAULT FALSE,
|
|
created_at timestamp NOT NULL DEFAULT timezone('utc'::text, now()),
|
|
deleted_at timestamp NULL DEFAULT NULL
|
|
);
|
|
|
|
INSERT INTO roles(tenant_id, name, description, permissions, protected)
|
|
SELECT *
|
|
FROM (SELECT tenant_id FROM tenants) AS tenants,
|
|
(VALUES ('Owner', 'Owner',
|
|
'{"SESSION_REPLAY", "DEV_TOOLS", "ERRORS", "METRICS", "ASSIST_LIVE", "ASSIST_CALL"}'::text[], TRUE),
|
|
('Member', 'Member',
|
|
'{"SESSION_REPLAY", "DEV_TOOLS", "ERRORS", "METRICS", "ASSIST_LIVE", "ASSIST_CALL"}'::text[], FALSE)
|
|
) AS default_roles(name, description, permissions, protected);
|
|
|
|
|
|
ALTER TABLE users
|
|
ADD COLUMN role_id integer REFERENCES roles (role_id) ON DELETE SET NULL;
|
|
|
|
UPDATE users
|
|
SET role_id = r.role_id
|
|
FROM (SELECT tenant_id, role_id
|
|
FROM tenants
|
|
INNER JOIN roles USING (tenant_id)
|
|
WHERE roles.name = 'Owner') AS r(tenant_id, role_id)
|
|
WHERE users.tenant_id = r.tenant_id
|
|
AND users.role = 'owner';
|
|
|
|
UPDATE users
|
|
SET role_id = r.role_id
|
|
FROM (SELECT tenant_id, role_id
|
|
FROM tenants
|
|
INNER JOIN roles USING (tenant_id)
|
|
WHERE roles.name = 'Member') AS r(tenant_id, role_id)
|
|
WHERE users.tenant_id = r.tenant_id
|
|
AND users.role != 'owner';
|
|
|
|
DO
|
|
$$
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM pg_type WHERE typname = 'user_origin') THEN
|
|
CREATE TYPE user_origin AS ENUM ('saml');
|
|
END IF;
|
|
END
|
|
$$;
|
|
ALTER TABLE public.users
|
|
ADD COLUMN IF NOT EXISTS origin user_origin NULL DEFAULT NULL,
|
|
ADD COLUMN IF NOT EXISTS internal_id text NULL DEFAULT NULL;
|
|
|
|
|
|
|
|
ALTER TABLE public.users
|
|
ALTER COLUMN origin TYPE text;
|
|
DROP TYPE IF EXISTS user_origin;
|
|
COMMIT;
|