84 lines
2.8 KiB
SQL
84 lines
2.8 KiB
SQL
-- Teams/Roles system for organization structure
|
|
-- Like TipiLAN: infra team, sponsors, etc.
|
|
|
|
-- Create teams table
|
|
CREATE TABLE IF NOT EXISTS teams (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
org_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
description TEXT,
|
|
color TEXT DEFAULT '#00A3E0',
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW(),
|
|
UNIQUE(org_id, name)
|
|
);
|
|
|
|
-- Create team members junction table
|
|
CREATE TABLE IF NOT EXISTS team_members (
|
|
team_id UUID NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
|
|
user_id UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
|
|
role TEXT DEFAULT 'member' CHECK (role IN ('lead', 'member')),
|
|
joined_at TIMESTAMPTZ DEFAULT NOW(),
|
|
PRIMARY KEY (team_id, user_id)
|
|
);
|
|
|
|
-- Create indexes
|
|
CREATE INDEX IF NOT EXISTS idx_teams_org ON teams(org_id);
|
|
CREATE INDEX IF NOT EXISTS idx_team_members_team ON team_members(team_id);
|
|
CREATE INDEX IF NOT EXISTS idx_team_members_user ON team_members(user_id);
|
|
|
|
-- Enable RLS
|
|
ALTER TABLE teams ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE team_members ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- RLS policies for teams
|
|
CREATE POLICY "Users can view teams in their orgs"
|
|
ON teams FOR SELECT
|
|
USING (
|
|
org_id IN (
|
|
SELECT org_id FROM org_members WHERE user_id = auth.uid()
|
|
)
|
|
);
|
|
|
|
CREATE POLICY "Admins can manage teams"
|
|
ON teams FOR ALL
|
|
USING (
|
|
org_id IN (
|
|
SELECT org_id FROM org_members
|
|
WHERE user_id = auth.uid()
|
|
AND role IN ('owner', 'admin')
|
|
)
|
|
);
|
|
|
|
-- RLS policies for team_members
|
|
CREATE POLICY "Users can view team members in their orgs"
|
|
ON team_members FOR SELECT
|
|
USING (
|
|
team_id IN (
|
|
SELECT id FROM teams WHERE org_id IN (
|
|
SELECT org_id FROM org_members WHERE user_id = auth.uid()
|
|
)
|
|
)
|
|
);
|
|
|
|
CREATE POLICY "Admins can manage team members"
|
|
ON team_members FOR ALL
|
|
USING (
|
|
team_id IN (
|
|
SELECT id FROM teams WHERE org_id IN (
|
|
SELECT org_id FROM org_members
|
|
WHERE user_id = auth.uid()
|
|
AND role IN ('owner', 'admin')
|
|
)
|
|
)
|
|
);
|
|
|
|
-- Add team_id to kanban_boards for team-specific boards
|
|
ALTER TABLE kanban_boards ADD COLUMN IF NOT EXISTS team_id UUID REFERENCES teams(id) ON DELETE SET NULL;
|
|
ALTER TABLE kanban_boards ADD COLUMN IF NOT EXISTS is_personal BOOLEAN DEFAULT FALSE;
|
|
ALTER TABLE kanban_boards ADD COLUMN IF NOT EXISTS created_by UUID REFERENCES profiles(id);
|
|
|
|
-- Create index for team boards
|
|
CREATE INDEX IF NOT EXISTS idx_kanban_boards_team ON kanban_boards(team_id);
|
|
CREATE INDEX IF NOT EXISTS idx_kanban_boards_personal ON kanban_boards(is_personal, created_by);
|