Spaces:
Sleeping
Sleeping
File size: 4,723 Bytes
3493993 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 | begin;
-- Teams
create table if not exists teams (
id text primary key,
workspace_id text not null references workspaces(id) on delete cascade,
name varchar(255) not null,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now()
);
-- Team Members
create table if not exists team_members (
id text primary key,
workspace_id text not null references workspaces(id) on delete cascade,
team_id text not null references teams(id) on delete cascade,
user_id text not null references users(id) on delete cascade,
role varchar(50) not null,
created_at timestamptz not null default now(),
unique(team_id, user_id)
);
-- Invitations
create table if not exists invitations (
id text primary key,
workspace_id text not null references workspaces(id) on delete cascade,
email text not null,
role varchar(50) not null,
status varchar(20) not null check (status in ('pending', 'accepted', 'expired', 'revoked')),
expires_at timestamptz not null,
created_at timestamptz not null default now()
);
-- Project Collaborators
create table if not exists project_collaborators (
id text primary key,
workspace_id text not null references workspaces(id) on delete cascade,
project_id text not null references projects(id) on delete cascade,
user_id text not null references users(id) on delete cascade,
role varchar(50) not null,
created_at timestamptz not null default now(),
unique(project_id, user_id)
);
-- Approval Workflows
create table if not exists approval_workflows (
id text primary key,
workspace_id text not null references workspaces(id) on delete cascade,
project_id text not null references projects(id) on delete cascade,
name varchar(255) not null,
created_at timestamptz not null default now()
);
-- Approval Requests
create table if not exists approval_requests (
id text primary key,
workspace_id text not null references workspaces(id) on delete cascade,
workflow_id text not null references approval_workflows(id) on delete cascade,
project_id text not null references projects(id) on delete cascade,
status varchar(20) not null check (status in ('pending', 'approved', 'rejected')),
created_by text not null references users(id) on delete cascade,
created_at timestamptz not null default now()
);
-- Review Comments
create table if not exists review_comments (
id text primary key,
workspace_id text not null references workspaces(id) on delete cascade,
request_id text not null references approval_requests(id) on delete cascade,
user_id text not null references users(id) on delete cascade,
content text not null,
created_at timestamptz not null default now()
);
-- Collaboration Activity
create table if not exists collaboration_activity (
id text primary key,
workspace_id text not null references workspaces(id) on delete cascade,
user_id text not null references users(id) on delete cascade,
action varchar(100) not null,
entity_id text not null,
entity_type varchar(50) not null,
metadata jsonb not null default '{}',
created_at timestamptz not null default now()
);
-- Indexes
create index ix_teams_workspace_id on teams(workspace_id);
create index ix_invitations_workspace_id on invitations(workspace_id);
create index ix_team_members_team_id on team_members(team_id);
create index ix_project_collaborators_project_id on project_collaborators(project_id);
create index ix_approval_workflows_project_id on approval_workflows(project_id);
create index ix_approval_requests_workflow_id on approval_requests(workflow_id);
create index ix_review_comments_request_id on review_comments(request_id);
create index ix_collaboration_activity_workspace_id on collaboration_activity(workspace_id);
-- RLS
do $$
declare
table_name text;
begin
for table_name in select t.table_name
from information_schema.tables t
where t.table_schema = 'public'
and t.table_name in ('teams', 'team_members', 'invitations', 'project_collaborators', 'approval_workflows', 'approval_requests', 'review_comments', 'collaboration_activity')
loop
execute format('alter table %I enable row level security', table_name);
execute format('alter table %I force row level security', table_name);
execute format('drop policy if exists collaboration_workspace_isolation on %I', table_name);
execute format('create policy collaboration_workspace_isolation on %I using (workspace_id = current_setting(''app.workspace_id'', true)) with check (workspace_id = current_setting(''app.workspace_id'', true))', table_name);
end loop;
end $$;
commit;
|