89 lines
2.8 KiB
SQL
89 lines
2.8 KiB
SQL
create type group_role_v2 as enum ('MEMBER', 'GROUP_ADMIN', 'GROUP_OWNER');
|
|
|
|
alter table group_members
|
|
alter column role type group_role_v2
|
|
using role::text::group_role_v2;
|
|
|
|
drop type group_role;
|
|
alter type group_role_v2 rename to group_role;
|
|
|
|
with ranked_admins as (
|
|
select
|
|
group_id,
|
|
user_id,
|
|
row_number() over (partition by group_id order by created_at asc, user_id asc) as rn
|
|
from group_members
|
|
where role = 'GROUP_ADMIN'
|
|
)
|
|
update group_members gm
|
|
set role = 'GROUP_OWNER'
|
|
from ranked_admins ra
|
|
where gm.group_id = ra.group_id
|
|
and gm.user_id = ra.user_id
|
|
and ra.rn = 1;
|
|
|
|
create unique index group_members_owner_unique on group_members(group_id) where role = 'GROUP_OWNER';
|
|
|
|
alter table users
|
|
add column last_login_at timestamptz;
|
|
|
|
alter table group_members
|
|
add column last_active_at timestamptz;
|
|
|
|
create type group_join_policy as enum ('NOT_ACCEPTING', 'AUTO_ACCEPT', 'APPROVAL_REQUIRED');
|
|
|
|
alter table group_settings
|
|
add column join_policy group_join_policy not null default 'NOT_ACCEPTING';
|
|
|
|
create type group_join_request_status as enum ('PENDING', 'APPROVED', 'DENIED', 'CANCELED');
|
|
|
|
create table group_join_requests(
|
|
id bigserial primary key,
|
|
group_id bigint not null references groups(id) on delete cascade,
|
|
user_id bigint not null references users(id) on delete cascade,
|
|
status group_join_request_status not null default 'PENDING',
|
|
decided_by bigint references users(id) on delete set null,
|
|
decided_at timestamptz,
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now()
|
|
);
|
|
|
|
create unique index group_join_requests_pending_unique
|
|
on group_join_requests(group_id, user_id)
|
|
where status = 'PENDING';
|
|
|
|
create index group_join_requests_group_idx on group_join_requests(group_id);
|
|
|
|
create table group_invite_links(
|
|
id bigserial primary key,
|
|
group_id bigint not null references groups(id) on delete cascade,
|
|
created_by bigint not null references users(id) on delete cascade,
|
|
token text not null unique,
|
|
policy group_join_policy not null,
|
|
single_use boolean not null default false,
|
|
expires_at timestamptz not null,
|
|
used_at timestamptz,
|
|
revoked_at timestamptz,
|
|
created_at timestamptz not null default now()
|
|
);
|
|
|
|
create index group_invite_links_group_idx on group_invite_links(group_id);
|
|
|
|
create table group_audit_log(
|
|
id bigserial primary key,
|
|
group_id bigint not null references groups(id) on delete cascade,
|
|
actor_user_id bigint references users(id) on delete set null,
|
|
actor_role group_role,
|
|
event_type text not null,
|
|
request_id text not null,
|
|
ip inet,
|
|
user_agent text,
|
|
success boolean not null default true,
|
|
error_code text,
|
|
metadata jsonb not null default '{}'::jsonb,
|
|
created_at timestamptz not null default now()
|
|
);
|
|
|
|
create index group_audit_log_group_idx on group_audit_log(group_id, created_at desc);
|
|
create index group_audit_log_request_idx on group_audit_log(request_id);
|