template-saas-database-v2/ddl/schemas/commissions/04-rls.sql

-- ============================================
-- TEMPLATE-SAAS: Commissions Row Level Security
-- Version: 1.0.0
-- Module: SAAS-020
-- ============================================

-- ============================================
-- Enable RLS on all tables
-- ============================================
ALTER TABLE commissions.schemes ENABLE ROW LEVEL SECURITY;
ALTER TABLE commissions.assignments ENABLE ROW LEVEL SECURITY;
ALTER TABLE commissions.entries ENABLE ROW LEVEL SECURITY;
ALTER TABLE commissions.periods ENABLE ROW LEVEL SECURITY;

-- ============================================
-- Schemes Policies
-- ============================================
CREATE POLICY schemes_tenant_isolation ON commissions.schemes
    USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

CREATE POLICY schemes_insert ON commissions.schemes
    FOR INSERT
    WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

CREATE POLICY schemes_update ON commissions.schemes
    FOR UPDATE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

CREATE POLICY schemes_delete ON commissions.schemes
    FOR DELETE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

-- ============================================
-- Assignments Policies
-- ============================================
CREATE POLICY assignments_tenant_isolation ON commissions.assignments
    USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

CREATE POLICY assignments_insert ON commissions.assignments
    FOR INSERT
    WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

CREATE POLICY assignments_update ON commissions.assignments
    FOR UPDATE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

CREATE POLICY assignments_delete ON commissions.assignments
    FOR DELETE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

-- ============================================
-- Entries Policies
-- ============================================
CREATE POLICY entries_tenant_isolation ON commissions.entries
    USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

CREATE POLICY entries_insert ON commissions.entries
    FOR INSERT
    WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

CREATE POLICY entries_update ON commissions.entries
    FOR UPDATE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

CREATE POLICY entries_delete ON commissions.entries
    FOR DELETE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

-- User can view their own entries (additional policy)
CREATE POLICY entries_user_view ON commissions.entries
    FOR SELECT
    USING (
        tenant_id = current_setting('app.current_tenant_id', true)::UUID
        AND (
            user_id = current_setting('app.current_user_id', true)::UUID
            OR current_setting('app.user_role', true) IN ('admin', 'manager')
        )
    );

-- ============================================
-- Periods Policies
-- ============================================
CREATE POLICY periods_tenant_isolation ON commissions.periods
    USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

CREATE POLICY periods_insert ON commissions.periods
    FOR INSERT
    WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

CREATE POLICY periods_update ON commissions.periods
    FOR UPDATE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

CREATE POLICY periods_delete ON commissions.periods
    FOR DELETE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

-- ============================================
-- Bypass policies for service role (optional)
-- ============================================
-- These allow backend services with elevated privileges to bypass RLS
-- when needed (e.g., for admin operations, reporting, etc.)

-- CREATE POLICY schemes_service_bypass ON commissions.schemes
--     FOR ALL
--     TO template_saas_service
--     USING (true);

-- CREATE POLICY assignments_service_bypass ON commissions.assignments
--     FOR ALL
--     TO template_saas_service
--     USING (true);

-- CREATE POLICY entries_service_bypass ON commissions.entries
--     FOR ALL
--     TO template_saas_service
--     USING (true);

-- CREATE POLICY periods_service_bypass ON commissions.periods
--     FOR ALL
--     TO template_saas_service
--     USING (true);