complete_database_setup.sql

-- SharedMarks Bookmarks - Complete Database Setup
-- Run this entire script in your Supabase SQL editor to set up everything from scratch
-- Enable required extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
-- =====================================================
-- STEP 1: CREATE ALL TABLES IN CORRECT ORDER
-- =====================================================
-- Note: Profiles table removed - using auth.users.raw_user_meta_data for display names
-- Create teams table (base table that others reference)
CREATE TABLE IF NOT EXISTS teams (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT,
    visibility TEXT DEFAULT 'private' CHECK (visibility IN ('private', 'public')),
    join_code TEXT UNIQUE,
    created_by UUID REFERENCES auth.users(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
-- Create user_teams junction table for team membership with roles
CREATE TABLE IF NOT EXISTS user_teams (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
    team_id BIGINT REFERENCES teams(id) ON DELETE CASCADE,
    role TEXT DEFAULT 'member' CHECK (role IN ('admin', 'member', 'viewer')),
    joined_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(user_id, team_id)
);
-- Create folders table (references teams)
CREATE TABLE IF NOT EXISTS folders (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    team_id BIGINT REFERENCES teams(id) ON DELETE CASCADE,
    parent_folder_id BIGINT REFERENCES folders(id) ON DELETE CASCADE,
    created_by UUID REFERENCES auth.users(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    permissions JSONB DEFAULT '{"read": true, "write": true}'
);
-- Create bookmarks table (references folders)
CREATE TABLE IF NOT EXISTS bookmarks (
    id BIGSERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    url TEXT NOT NULL,
    folder_id BIGINT REFERENCES folders(id) ON DELETE CASCADE,
    created_by UUID REFERENCES auth.users(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    tags TEXT [],
    description TEXT
);
-- Create team_activities table for activity tracking
CREATE TABLE IF NOT EXISTS team_activities (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    team_id BIGINT REFERENCES teams(id) ON DELETE CASCADE,
    user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
    activity_type TEXT NOT NULL CHECK (
        activity_type IN (
            'team_created',
            'member_joined',
            'member_left',
            'member_removed',
            'folder_shared',
            'folder_unshared',
            'bookmark_added',
            'bookmark_removed',
            'folder_created',
            'folder_deleted',
            'team_updated'
        )
    ),
    description TEXT NOT NULL,
    metadata JSONB,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
-- Create team_invitations table for email invitations
CREATE TABLE IF NOT EXISTS team_invitations (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    team_id BIGINT REFERENCES teams(id) ON DELETE CASCADE,
    invited_by UUID REFERENCES auth.users(id) ON DELETE CASCADE,
    email TEXT NOT NULL,
    role TEXT DEFAULT 'member' CHECK (role IN ('admin', 'member', 'viewer')),
    token TEXT UNIQUE NOT NULL,
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
    accepted_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
-- Create bookmark_collections table for organized bookmark sets
CREATE TABLE IF NOT EXISTS bookmark_collections (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    team_id BIGINT REFERENCES teams(id) ON DELETE CASCADE,
    created_by UUID REFERENCES auth.users(id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    description TEXT,
    color TEXT DEFAULT '#4c51bf',
    is_public BOOLEAN DEFAULT false,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
-- Create collection_bookmarks junction table
CREATE TABLE IF NOT EXISTS collection_bookmarks (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    collection_id UUID REFERENCES bookmark_collections(id) ON DELETE CASCADE,
    bookmark_id BIGINT REFERENCES bookmarks(id) ON DELETE CASCADE,
    added_by UUID REFERENCES auth.users(id) ON DELETE CASCADE,
    added_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(collection_id, bookmark_id)
);
-- =====================================================
-- STEP 2: CREATE INDEXES FOR PERFORMANCE
-- =====================================================
CREATE INDEX IF NOT EXISTS idx_user_teams_user_id ON user_teams(user_id);
CREATE INDEX IF NOT EXISTS idx_user_teams_team_id ON user_teams(team_id);
CREATE INDEX IF NOT EXISTS idx_team_activities_team_id ON team_activities(team_id);
CREATE INDEX IF NOT EXISTS idx_team_activities_created_at ON team_activities(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_teams_join_code ON teams(join_code);
CREATE INDEX IF NOT EXISTS idx_team_invitations_email ON team_invitations(email);
CREATE INDEX IF NOT EXISTS idx_team_invitations_token ON team_invitations(token);
CREATE INDEX IF NOT EXISTS idx_bookmarks_title ON bookmarks USING gin(to_tsvector('english', title));
CREATE INDEX IF NOT EXISTS idx_folders_name ON folders USING gin(to_tsvector('english', name));
CREATE INDEX IF NOT EXISTS idx_folders_team_id ON folders(team_id);
CREATE INDEX IF NOT EXISTS idx_bookmarks_folder_id ON bookmarks(folder_id);
-- =====================================================
-- STEP 3: ENABLE ROW LEVEL SECURITY
-- =====================================================
-- Profiles table removed
ALTER TABLE teams ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_teams ENABLE ROW LEVEL SECURITY;
ALTER TABLE folders ENABLE ROW LEVEL SECURITY;
ALTER TABLE bookmarks ENABLE ROW LEVEL SECURITY;
ALTER TABLE team_activities ENABLE ROW LEVEL SECURITY;
ALTER TABLE team_invitations ENABLE ROW LEVEL SECURITY;
ALTER TABLE bookmark_collections ENABLE ROW LEVEL SECURITY;
ALTER TABLE collection_bookmarks ENABLE ROW LEVEL SECURITY;
-- =====================================================
-- STEP 4: CREATE HELPER FUNCTIONS
-- =====================================================
-- Non-recursive helper function to check team membership
CREATE OR REPLACE FUNCTION public.is_member_of_team(p_team_id BIGINT, p_user_id UUID) RETURNS BOOLEAN AS $$ BEGIN RETURN EXISTS (
        SELECT 1
        FROM public.user_teams ut
        WHERE ut.team_id = p_team_id
            AND ut.user_id = p_user_id
    );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- Non-recursive helper function to check team admin status
CREATE OR REPLACE FUNCTION public.is_admin_of_team(p_team_id BIGINT, p_user_id UUID) RETURNS BOOLEAN AS $$ BEGIN RETURN EXISTS (
        SELECT 1
        FROM public.user_teams ut
        WHERE ut.team_id = p_team_id
            AND ut.user_id = p_user_id
            AND ut.role = 'admin'
    );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- Helper function to get user information from auth.users
CREATE OR REPLACE FUNCTION public.get_user_info(user_ids UUID []) RETURNS TABLE (
        id UUID,
        email VARCHAR(255),
        display_name TEXT
    ) AS $$ BEGIN -- Check if the current user is authenticated
    IF auth.uid() IS NULL THEN RAISE EXCEPTION 'Access denied: User not authenticated';
END IF;
RETURN QUERY
SELECT u.id,
    u.email,
    COALESCE(
        u.raw_user_meta_data->>'display_name',
        split_part(u.email, '@', 1)
    ) as display_name
FROM auth.users u
WHERE u.id = ANY(user_ids)
    AND (
        u.id = auth.uid()
        OR EXISTS (
            SELECT 1
            FROM user_teams ut1
                JOIN user_teams ut2 ON ut1.team_id = ut2.team_id
            WHERE ut1.user_id = auth.uid()
                AND ut2.user_id = u.id
        )
    );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- Helper function to check if user exists by email
CREATE OR REPLACE FUNCTION public.user_exists_by_email(user_email TEXT) RETURNS BOOLEAN AS $$ BEGIN -- Check if the current user is authenticated
    IF auth.uid() IS NULL THEN RAISE EXCEPTION 'Access denied: User not authenticated';
END IF;
RETURN EXISTS (
    SELECT 1
    FROM auth.users
    WHERE email = user_email
);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- Helper function to get user info by ID for invitations
CREATE OR REPLACE FUNCTION public.get_user_by_id(user_id UUID) RETURNS TABLE (
        id UUID,
        email VARCHAR(255),
        display_name TEXT
    ) AS $$ BEGIN -- Check if the current user is authenticated
    IF auth.uid() IS NULL THEN RAISE EXCEPTION 'Access denied: User not authenticated';
END IF;
RETURN QUERY
SELECT u.id,
    u.email,
    COALESCE(
        u.raw_user_meta_data->>'display_name',
        split_part(u.email, '@', 1)
    ) as display_name
FROM auth.users u
WHERE u.id = user_id
    AND (
        u.id = auth.uid()
        OR EXISTS (
            SELECT 1
            FROM user_teams ut1
                JOIN user_teams ut2 ON ut1.team_id = ut2.team_id
            WHERE ut1.user_id = auth.uid()
                AND ut2.user_id = u.id
        )
    );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- Function to generate join codes
CREATE OR REPLACE FUNCTION generate_join_code() RETURNS TEXT AS $$ BEGIN RETURN upper(
        substring(
            md5(random()::text)
            from 1 for 6
        )
    );
END;
$$ LANGUAGE plpgsql;
-- Function to update updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column() RETURNS TRIGGER AS $$ BEGIN NEW.updated_at = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- Secure function for validating and using invitation codes
CREATE OR REPLACE FUNCTION public.use_invitation_code(invitation_token TEXT) RETURNS JSON AS $$
DECLARE invitation_record RECORD;
team_record RECORD;
current_user_id UUID;
BEGIN -- Get current user ID
current_user_id := auth.uid();
IF current_user_id IS NULL THEN RETURN json_build_object('error', 'Not authenticated');
END IF;
-- Find the invitation
SELECT * INTO invitation_record
FROM team_invitations
WHERE token = UPPER(invitation_token)
    AND (
        email = ''
        OR email IS NULL
    )
    AND accepted_at IS NULL
    AND expires_at > NOW();
IF NOT FOUND THEN RETURN json_build_object('error', 'Invalid or expired invitation code');
END IF;
-- Get team info
SELECT * INTO team_record
FROM teams
WHERE id = invitation_record.team_id;
-- Check if user is already a member
IF EXISTS (
    SELECT 1
    FROM user_teams ut
    WHERE ut.user_id = current_user_id
        AND ut.team_id = invitation_record.team_id
) THEN RETURN json_build_object('error', 'You are already a member of this team');
END IF;
-- Add user to team
INSERT INTO user_teams (user_id, team_id, role)
VALUES (
        current_user_id,
        invitation_record.team_id,
        invitation_record.role
    );
-- Mark invitation as accepted
UPDATE team_invitations
SET accepted_at = NOW()
WHERE id = invitation_record.id;
-- Log activity
INSERT INTO team_activities (team_id, user_id, activity_type, description)
VALUES (
        invitation_record.team_id,
        current_user_id,
        'member_joined',
        (
            SELECT u.email
            FROM auth.users u
            WHERE u.id = current_user_id
        ) || ' joined the team with ' || invitation_record.role || ' role'
    );
-- Return success with team info
RETURN json_build_object(
    'success',
    true,
    'team',
    json_build_object(
        'id',
        team_record.id,
        'name',
        team_record.name
    ),
    'role',
    invitation_record.role
);
EXCEPTION
WHEN OTHERS THEN RETURN json_build_object('error', 'Failed to join team: ' || SQLERRM);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- Grant execute permission to authenticated users
GRANT EXECUTE ON FUNCTION public.is_member_of_team(BIGINT, UUID) TO authenticated;
GRANT EXECUTE ON FUNCTION public.is_admin_of_team(BIGINT, UUID) TO authenticated;
GRANT EXECUTE ON FUNCTION public.get_user_info(UUID []) TO authenticated;
GRANT EXECUTE ON FUNCTION public.user_exists_by_email(TEXT) TO authenticated;
GRANT EXECUTE ON FUNCTION public.get_user_by_id(UUID) TO authenticated;
GRANT EXECUTE ON FUNCTION public.use_invitation_code(TEXT) TO authenticated;
-- =====================================================
-- STEP 5: CREATE RLS POLICIES (FIXED FOR NO RECURSION)
-- =====================================================
-- Drop any existing policies first to ensure a clean slate
DROP POLICY IF EXISTS "Users can view teams they belong to" ON teams;
DROP POLICY IF EXISTS "Users can create teams" ON teams;
DROP POLICY IF EXISTS "Team admins can update teams" ON teams;
DROP POLICY IF EXISTS "Team members can view memberships" ON user_teams;
DROP POLICY IF EXISTS "Users can join teams" ON user_teams;
DROP POLICY IF EXISTS "Team admins can update memberships" ON user_teams;
DROP POLICY IF EXISTS "Users can leave or be removed from teams" ON user_teams;
-- Teams policies (using helper functions for clarity and safety)
CREATE POLICY "Users can view teams they belong to" ON teams FOR
SELECT USING (
        created_by = auth.uid()
        OR public.is_member_of_team(teams.id, auth.uid())
    );
CREATE POLICY "Users can create teams" ON teams FOR
INSERT WITH CHECK (created_by = auth.uid());
CREATE POLICY "Team admins can update teams" ON teams FOR
UPDATE USING (
        public.is_admin_of_team(teams.id, auth.uid())
    );
-- User teams policies (fixed to avoid recursion)
CREATE POLICY "Team members can view memberships" ON user_teams FOR
SELECT USING (
        public.is_member_of_team(user_teams.team_id, auth.uid())
    );
CREATE POLICY "Users can join teams" ON user_teams FOR
INSERT WITH CHECK (user_id = auth.uid());
CREATE POLICY "Team admins can update memberships" ON user_teams FOR
UPDATE USING (
        public.is_admin_of_team(user_teams.team_id, auth.uid())
    );
CREATE POLICY "Users can leave or be removed from teams" ON user_teams FOR DELETE USING (
    user_id = auth.uid()
    OR -- User can delete their own membership (leave team)
    public.is_admin_of_team(user_teams.team_id, auth.uid()) -- Admin can remove any member
);
-- Folders policies
CREATE POLICY "Team members can view folders" ON folders FOR
SELECT USING (
        public.is_member_of_team(folders.team_id, auth.uid())
    );
CREATE POLICY "Team members can create folders" ON folders FOR
INSERT WITH CHECK (
        created_by = auth.uid()
        AND public.is_member_of_team(folders.team_id, auth.uid())
    );
CREATE POLICY "Team members can update folders" ON folders FOR
UPDATE USING (
        public.is_member_of_team(folders.team_id, auth.uid())
    );
CREATE POLICY "Team members can delete folders" ON folders FOR DELETE USING (
    public.is_member_of_team(folders.team_id, auth.uid())
);
-- Bookmarks policies
CREATE POLICY "Team members can view bookmarks" ON bookmarks FOR
SELECT USING (
        EXISTS (
            SELECT 1
            FROM folders f
            WHERE f.id = bookmarks.folder_id
                AND public.is_member_of_team(f.team_id, auth.uid())
        )
    );
CREATE POLICY "Team members can create bookmarks" ON bookmarks FOR
INSERT WITH CHECK (
        created_by = auth.uid()
        AND EXISTS (
            SELECT 1
            FROM folders f
            WHERE f.id = bookmarks.folder_id
                AND public.is_member_of_team(f.team_id, auth.uid())
        )
    );
CREATE POLICY "Team members can update bookmarks" ON bookmarks FOR
UPDATE USING (
        EXISTS (
            SELECT 1
            FROM folders f
            WHERE f.id = bookmarks.folder_id
                AND public.is_member_of_team(f.team_id, auth.uid())
        )
    );
CREATE POLICY "Team members can delete bookmarks" ON bookmarks FOR DELETE USING (
    EXISTS (
        SELECT 1
        FROM folders f
        WHERE f.id = bookmarks.folder_id
            AND public.is_member_of_team(f.team_id, auth.uid())
    )
);
-- Team activities policies
CREATE POLICY "Team members can view activities" ON team_activities FOR
SELECT USING (
        public.is_member_of_team(team_activities.team_id, auth.uid())
    );
CREATE POLICY "Team members can create activities" ON team_activities FOR
INSERT WITH CHECK (
        user_id = auth.uid()
        AND public.is_member_of_team(team_activities.team_id, auth.uid())
    );
-- Team invitations policies
CREATE POLICY "Team admins can create invitations" ON team_invitations FOR
INSERT WITH CHECK (
        public.is_admin_of_team(team_invitations.team_id, auth.uid())
    );
CREATE POLICY "Team admins can view invitations" ON team_invitations FOR
SELECT USING (
        public.is_admin_of_team(team_invitations.team_id, auth.uid())
    );
CREATE POLICY "Team admins can update invitations" ON team_invitations FOR
UPDATE USING (
        public.is_admin_of_team(team_invitations.team_id, auth.uid())
    );
CREATE POLICY "Team admins can delete invitations" ON team_invitations FOR DELETE USING (
    public.is_admin_of_team(team_invitations.team_id, auth.uid())
);
-- Bookmark collections policies
CREATE POLICY "Team members can view collections" ON bookmark_collections FOR
SELECT USING (
        public.is_member_of_team(bookmark_collections.team_id, auth.uid())
    );
CREATE POLICY "Team members can create collections" ON bookmark_collections FOR
INSERT WITH CHECK (
        created_by = auth.uid()
        AND public.is_member_of_team(bookmark_collections.team_id, auth.uid())
    );
-- Collection bookmarks policies
CREATE POLICY "Team members can manage collection bookmarks" ON collection_bookmarks FOR ALL USING (
    EXISTS (
        SELECT 1
        FROM bookmark_collections bc
        WHERE bc.id = collection_bookmarks.collection_id
            AND public.is_member_of_team(bc.team_id, auth.uid())
    )
);
-- =====================================================
-- STEP 6: CREATE TRIGGERS
-- =====================================================
-- Drop existing triggers first
-- Profile creation trigger removed (profiles table no longer exists)
DROP TRIGGER IF EXISTS update_teams_updated_at ON teams;
DROP TRIGGER IF EXISTS update_folders_updated_at ON folders;
DROP TRIGGER IF EXISTS update_bookmarks_updated_at ON bookmarks;
DROP TRIGGER IF EXISTS update_bookmark_collections_updated_at ON bookmark_collections;
-- Triggers for updated_at timestamps
CREATE TRIGGER update_teams_updated_at BEFORE
UPDATE ON teams FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_folders_updated_at BEFORE
UPDATE ON folders FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_bookmarks_updated_at BEFORE
UPDATE ON bookmarks FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_bookmark_collections_updated_at BEFORE
UPDATE ON bookmark_collections FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
-- =====================================================
-- STEP 7: GRANT PERMISSIONS
-- =====================================================
-- Grant necessary permissions to authenticated users
GRANT USAGE ON SCHEMA public TO anon,
    authenticated;
GRANT ALL ON ALL TABLES IN SCHEMA public TO anon,
    authenticated;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO anon,
    authenticated;
GRANT ALL ON ALL FUNCTIONS IN SCHEMA public TO anon,
    authenticated;
-- =====================================================
-- STEP 8: INSERT SAMPLE DATA (OPTIONAL)
-- =====================================================
-- Create a sample team for testing (only if no teams exist)
DO $$ BEGIN IF NOT EXISTS (
    SELECT 1
    FROM teams
    LIMIT 1
) THEN -- Insert sample teams with generated join codes
INSERT INTO teams (name, description, visibility, join_code)
VALUES (
        'Development Team',
        'Main development team for sharing technical resources',
        'private',
        generate_join_code()
    ),
    (
        'Design Team',
        'Creative team for sharing design inspiration and assets',
        'private',
        generate_join_code()
    );
RAISE NOTICE 'Sample teams created for testing';
END IF;
END $$;
-- =====================================================
-- STEP 9: VERIFICATION
-- =====================================================
-- Verify all tables were created
DO $$
DECLARE table_count INTEGER;
BEGIN
SELECT COUNT(*) INTO table_count
FROM information_schema.tables
WHERE table_schema = 'public'
    AND table_name IN (
        'teams',
        'user_teams',
        'folders',
        'bookmarks',
        'team_activities',
        'team_invitations',
        'bookmark_collections',
        'collection_bookmarks'
    );
IF table_count = 8 THEN RAISE NOTICE '✅ All 8 tables created successfully';
ELSE RAISE NOTICE '❌ Only % out of 8 tables created',
table_count;
END IF;
END $$;
-- Show created tables
SELECT schemaname,
    tablename,
    tableowner
FROM pg_tables
WHERE schemaname = 'public'
    AND tablename IN (
        'teams',
        'user_teams',
        'folders',
        'bookmarks',
        'team_activities',
        'team_invitations',
        'bookmark_collections',
        'collection_bookmarks'
    )
ORDER BY tablename;
-- Show sample join codes for testing
SELECT name as team_name,
    join_code,
    description
FROM teams
ORDER BY created_at;
-- Final success message
SELECT '🎉 SharedMarks Bookmarks Database Setup Complete!' as status,
    'You can now use the extension with full SaaS features!' as message;