forward-assist/packages/api/prisma/rls-setup.sql

-- ============================================================
-- Forward Assist — Row Level Security Setup
-- Run after Prisma migrations to enable tenant isolation
-- ============================================================

-- Enable RLS on tenant-scoped tables
-- Note: Prisma handles the actual filtering via middleware,
-- but these policies provide defense-in-depth at the DB level.

-- We use a session variable app.current_tenant_id set per connection.

-- Function to get current tenant
CREATE OR REPLACE FUNCTION current_tenant_id() RETURNS TEXT AS $$
  SELECT current_setting('app.current_tenant_id', true);
$$ LANGUAGE sql STABLE;

-- Enable RLS on all tenant-scoped tables
DO $$
DECLARE
  t TEXT;
BEGIN
  FOR t IN
    SELECT unnest(ARRAY[
      'users', 'email_accounts', 'tickets', 'messages',
      'ai_drafts', 'knowledge_base', 'audit_log',
      'customer_profiles', 'canned_responses', 'notification_preferences'
    ])
  LOOP
    EXECUTE format('ALTER TABLE %I ENABLE ROW LEVEL SECURITY', t);

    -- Policy: tenant isolation
    EXECUTE format(
      'CREATE POLICY IF NOT EXISTS tenant_isolation ON %I
       FOR ALL
       USING (tenant_id = current_tenant_id())
       WITH CHECK (tenant_id = current_tenant_id())',
      t
    );

    -- Allow the app user to bypass RLS (Prisma uses this role)
    EXECUTE format(
      'ALTER TABLE %I FORCE ROW LEVEL SECURITY', t
    );
  END LOOP;
END $$;

-- Grant the application user the ability to set tenant context
-- The Prisma middleware will SET app.current_tenant_id before each query