Paradoxical and other unusual effects can occur with any kind of drug. Brain drugs in particular seem to be prone to a wide variance in the effect profile. I speculate that in part it is due to the heavy "reuse" of neurotransmitters for various purposes (neurotransmitters themselves being mostly reused modified amino acids). For example, dopamine modulates motor activity, reward pathways, attention, and error correction, and more. Serotonin modulates smooth muscle, appetite, uterine contraction, mood, visual salience, and more. Both can affect circadian rhythms. NMDA/AMPA and GABA are widely present and are the basic "wires" for excitatory/inhibitory signals, so drugs that affect these can do all kinds of zany things.
Neurotransmitters tend to be context-depended, so when you wash over the whole body with a small molecule which hits in a neurotransmitter-specific rather than context-specific way, you can get some funky effects. Most of the activity comes from the coincidence that there are some domains which correlate well with context: dopamine tends to affect anything resembling taxis, so physical movement, but also goal seeking, prediction, etc.
On top of all that, receptors and enzymes are physical things, and thus different folks have different affinities for transmitters and drugs. Pharmacokinetics - how your body distributes and clears drugs - is a huge variable.
Modafinil in particular is interesting because it's what drug chemists call a "greasy brick" - highly lipophilic, low solubility, to the point where ensuring consistent bioavailability is hard. Cephalon has put in a fair bit of work ensuring a certain particle size and excipient profile (emulsifiers to facilitate absorption) and personally I've experienced quite the difference between name brand and generic, the later often does jack-all for me.
td;dr - small molecules act on broad regions and impact many subsystems at once. Biology is complicated and crufty.