Newer
Older
CREATE EXTENSION IF NOT EXISTS ltree;
CREATE TABLE gms_group (
id varchar NOT NULL,
name text NOT NULL,
path ltree NOT NULL,
locked boolean,
primary key(id)
);
CREATE INDEX group_path_gist_idx ON gms_group USING GIST(path);
CREATE UNIQUE INDEX group_path_idx ON gms_group USING btree(path);
CREATE INDEX group_name_idx ON gms_group USING btree(name);
CREATE TABLE gms_membership (
group_id varchar NOT NULL,
user_id varchar NOT NULL,
primary key (group_id, user_id),
foreign key (group_id) references gms_group(id)
);
CREATE TYPE permission_type AS ENUM ('VIEW_MEMBERS', 'MANAGE_MEMBERS', 'ADMIN');
CREATE TABLE gms_permission (
group_id varchar NOT NULL,
user_id varchar NOT NULL,
permission permission_type NOT NULL,
group_path ltree NOT NULL, -- group_path is copied here for performance reasons
primary key (group_id, user_id), -- we can have 1 permission for each group
foreign key (group_id) references gms_group(id),
foreign key (group_path) references gms_group(path)
CREATE TABLE error_log (
"date" timestamp DEFAULT NOW(),
"exception_class" varchar,
"stack_trace" TEXT
);
CREATE TABLE audit_log (
"date" timestamp DEFAULT NOW(),
"user" varchar,
"ip_address" varchar,
"action" TEXT
);
CREATE TABLE invited_registration_request (
id varchar NOT NULL,
token_hash varchar NOT NULL,
email varchar NOT NULL,
creation_time timestamp DEFAULT NOW(),
"user" varchar NULL, -- for audit purposes
done boolean,
PRIMARY KEY(id)
);
CREATE TABLE invited_registration_request_group (
request_id varchar NOT NULL,
group_id varchar NOT NULL,
permission permission_type NOT NULL,
PRIMARY KEY (request_id, group_id, permission),
FOREIGN KEY (request_id) REFERENCES invited_registration_request(id),
FOREIGN KEY (group_id) REFERENCES gms_group(id)