rls.sql

-- =====================================================
-- TURISTEI - RLS POLICIES (DOCUMENTATION)
-- IMPORTANT:
-- This file documents the RLS rules applied in production.
-- Execution in Supabase should follow the project’s hardened security flow.
-- =====================================================

-- This project enforces:
-- - RLS ON + FORCE RLS on all tables
-- - No direct GRANT for anon/authenticated
-- - app_* and meu_* functions for authenticated access
-- - admin_* functions for service_role only
-- - Public access ONLY through view v_servicos_busca

-- =====================================================
-- NOTE
-- Detailed policies are intentionally maintained in the database (production).
-- This repository mirrors the rules for traceability and auditing.
-- =====================================================

-- =====================================================
-- CORE OWNERSHIP POLICIES
-- Tables:
-- - prestadores
-- - servicos
-- - servicos_midias
-- =====================================================

-- -----------------------------------------------------
-- prestadores
-- Ownership: prestadores.user_id = auth.uid()
-- -----------------------------------------------------
-- Expected in production:
--   ALTER TABLE public.prestadores ENABLE ROW LEVEL SECURITY;
--   ALTER TABLE public.prestadores FORCE ROW LEVEL SECURITY;
--
-- Policies (conceptual mirror):
-- - SELECT: authenticated users can read only their own provider record
-- - INSERT: authenticated users can create only with user_id = auth.uid()
-- - UPDATE: authenticated users can update only their own provider record
-- - DELETE: restricted (typically admin/service_role only)

-- -----------------------------------------------------
-- servicos
-- Ownership: servicos.prestador_id belongs to prestadores.user_id = auth.uid()
-- -----------------------------------------------------
-- Expected in production:
--   ALTER TABLE public.servicos ENABLE ROW LEVEL SECURITY;
--   ALTER TABLE public.servicos FORCE ROW LEVEL SECURITY;
--
-- Policies (conceptual mirror):
-- - SELECT: authenticated users can read only services of their provider
-- - INSERT: authenticated users can insert only for their provider
-- - UPDATE: authenticated users can update only services of their provider
-- - DELETE: restricted (typically admin/service_role only)

-- -----------------------------------------------------
-- servicos_midias
-- Ownership: servicos_midias.servico_id belongs to servicos.prestador_id
--          which belongs to prestadores.user_id = auth.uid()
-- -----------------------------------------------------
-- Expected in production:
--   ALTER TABLE public.servicos_midias ENABLE ROW LEVEL SECURITY;
--   ALTER TABLE public.servicos_midias FORCE ROW LEVEL SECURITY;
--
-- Policies (conceptual mirror):
-- - SELECT: authenticated users can read only media for their services
-- - INSERT: authenticated users can insert only for their services
-- - UPDATE: authenticated users can update only media for their services
-- - DELETE: restricted (typically admin/service_role only)

-- =====================================================
-- MARKETPLACE & FINANCIAL RLS POLICIES
-- Tables:
-- - pedidos
-- - pedidos_itens
-- - pagamentos_pedido
-- - comissoes_itens
-- - repasses_prestador
-- =====================================================

-- -----------------------------------------------------
-- pedidos
-- Ownership: pedidos.turista_id = auth.uid()
-- -----------------------------------------------------
-- Policies (conceptual mirror):
-- - SELECT: tourists can read only their own orders
-- - INSERT: tourists can create orders only for themselves
-- - UPDATE: restricted (status changes via secured RPCs)
-- - DELETE: restricted (financial integrity preserved)

-- -----------------------------------------------------
-- pedidos_itens
-- Ownership:
-- - linked to pedidos of auth.uid() for tourists
-- - linked to prestadores.user_id for providers
-- -----------------------------------------------------
-- Policies (conceptual mirror):
-- - SELECT: tourists see their items, providers see their own items
-- - INSERT: via secured order creation flow (RPC)
-- - UPDATE: restricted
-- - DELETE: restricted

-- -----------------------------------------------------
-- pagamentos_pedido
-- Controlled strictly by backend logic
-- -----------------------------------------------------
-- Policies (conceptual mirror):
-- - SELECT: tourists can read their own payments
-- - INSERT/UPDATE: backend automation only
-- - DELETE: restricted

-- -----------------------------------------------------
-- comissoes_itens
-- Immutable financial history
-- -----------------------------------------------------
-- Policies (conceptual mirror):
-- - SELECT: providers can read their own commission records
-- - INSERT: automated only (trigger)
-- - UPDATE/DELETE: not allowed

-- -----------------------------------------------------
-- repasses_prestador
-- Immutable payout records per provider
-- -----------------------------------------------------
-- Policies (conceptual mirror):
-- - SELECT: providers can read only their own payouts
-- - INSERT: automated only (financial trigger)
-- - UPDATE/DELETE: not allowed