backend/sql/enable_rls.sql

-- Enable Row Level Security (RLS) for all tables in the public schema
-- This script addresses the security issues identified by Supabase Security Advisor

-- 1. Enable RLS on all tables
ALTER TABLE public.agents ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.agent_versions ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.datasources ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.documents ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.chunks ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.embeddings ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.collections ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.collection_items ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.pipeline_runs ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.chat_logs ENABLE ROW LEVEL SECURITY;

-- 2. Create RLS policies for service role (backend API access)
-- These policies allow the service role full access while protecting from direct client access

-- Agents table policies
CREATE POLICY "Service role can manage agents" ON public.agents
    FOR ALL
    USING (auth.role() = 'service_role')
    WITH CHECK (auth.role() = 'service_role');

-- Agent versions table policies
CREATE POLICY "Service role can manage agent versions" ON public.agent_versions
    FOR ALL
    USING (auth.role() = 'service_role')
    WITH CHECK (auth.role() = 'service_role');

-- Datasources table policies
CREATE POLICY "Service role can manage datasources" ON public.datasources
    FOR ALL
    USING (auth.role() = 'service_role')
    WITH CHECK (auth.role() = 'service_role');

-- Documents table policies
CREATE POLICY "Service role can manage documents" ON public.documents
    FOR ALL
    USING (auth.role() = 'service_role')
    WITH CHECK (auth.role() = 'service_role');

-- Chunks table policies
CREATE POLICY "Service role can manage chunks" ON public.chunks
    FOR ALL
    USING (auth.role() = 'service_role')
    WITH CHECK (auth.role() = 'service_role');

-- Embeddings table policies
CREATE POLICY "Service role can manage embeddings" ON public.embeddings
    FOR ALL
    USING (auth.role() = 'service_role')
    WITH CHECK (auth.role() = 'service_role');

-- Collections table policies
CREATE POLICY "Service role can manage collections" ON public.collections
    FOR ALL
    USING (auth.role() = 'service_role')
    WITH CHECK (auth.role() = 'service_role');

-- Collection items table policies
CREATE POLICY "Service role can manage collection items" ON public.collection_items
    FOR ALL
    USING (auth.role() = 'service_role')
    WITH CHECK (auth.role() = 'service_role');

-- Pipeline runs table policies
CREATE POLICY "Service role can manage pipeline runs" ON public.pipeline_runs
    FOR ALL
    USING (auth.role() = 'service_role')
    WITH CHECK (auth.role() = 'service_role');

-- Chat logs table policies
CREATE POLICY "Service role can manage chat logs" ON public.chat_logs
    FOR ALL
    USING (auth.role() = 'service_role')
    WITH CHECK (auth.role() = 'service_role');

-- 3. Optional: Add read-only policies for authenticated users (if needed in future)
-- Uncomment these if you want authenticated users to read certain data

-- CREATE POLICY "Authenticated users can read documents" ON public.documents
--     FOR SELECT
--     USING (auth.role() = 'authenticated');

-- CREATE POLICY "Authenticated users can read chunks" ON public.chunks
--     FOR SELECT
--     USING (auth.role() = 'authenticated');

-- 4. Grant necessary permissions to service role
GRANT ALL ON public.agents TO service_role;
GRANT ALL ON public.agent_versions TO service_role;
GRANT ALL ON public.datasources TO service_role;
GRANT ALL ON public.documents TO service_role;
GRANT ALL ON public.chunks TO service_role;
GRANT ALL ON public.embeddings TO service_role;
GRANT ALL ON public.collections TO service_role;
GRANT ALL ON public.collection_items TO service_role;
GRANT ALL ON public.pipeline_runs TO service_role;
GRANT ALL ON public.chat_logs TO service_role;

-- Note: After running this script, the backend API should use the service_role key
-- Never expose the service_role key to the client/frontend
-- The anon key should be used for any client-side operations (currently none)