Thanks
New spells are definitely possible, just not new spells that add scripts. So you won't get every new spell that is available on Nexus since some of them require scripting. But there are a lot of new spells that can be created with just the CK and an esp file. Any in game magic effect you see in a shout or a potion or perk or standing stone can be manipulated in the CK into a spell with whatever stats you want without needing any external assets.
Combat can be enhanced in many ways, some of which can be done with just the CK and an esp file. For instance, you can make the AI more aggressive, with just an esp file. You can also rework many of the combat related perks to make them more effective and you can add new perks, like a bow perk (or even a melee perk) that disarms enemies, but if you are wanting something that adds new types of attacks that you can do with the press of a new button, like what Duke Patrick's mods do then that's probably going to require external assets and not be something that can be done for PS4. But he's got a thread here, so if you are interested in that type of stuff you probably want to ask in his thread.
Apocalypse Spells does use external assets so it won't be on PS4.
SkyUI requires script extender and thus won't be seen on either console. Currently it is unknown if we see SSE compatible version of SkyUI on PC either as it requires some extra effort.
Kirottu gave you the correct answers, but I thought I give you a little more detail about Apocalypse (one of my favorite mods) and clarify that not all the spells in Apocalypse require external assets. Many of them do not, or at least could be offered in a version that do not. Apocalypse adds new meshes to make the spells look better. That cannot be done with only an esp. Apocalypse also has some scripted spells, and those can't be done on PS4 either. But not all of the Apocalypse spells require scripting (many are made with just the CK) and anything that relies on new meshes or textures could be done with in game resources instead, just might not look as nice.
So, while you won't see Apocalypse coming to PS4, at least not in its current form, that doesn't mean you won't see a spell pack mod of some form. Unlike survival mods, which I don't think can be done without scripting, you can make a lot of new spells without scripts. If Enai doesn't offer something for PS4 users, I might create something eventually. I had to make a bunch of new magic effects for my mod Immersive Ingestibles, and figuring out how to get more out of magic with only the CK sounds like a fun project.