I don't see why a fountain pen shouldn't fit your use case. You could do worse than use a disposable fountain pen for awhile to see how it suits you. For example:
That would be my recommendation since it fits all your criteria and would probably bring your further along in your thinking to then make your initial purchase of a 'proper' fountain pen easier given that you would have more of an idea of what does and does not suit you.
Incidentally, fountain pens can by gently manipulated e.g. rolled, shaken in the hand to encourage ink flow although I don't find the need to do this with the above V Pen. I have several fountain pens now so do not use the V Pen that often but it still writes when I need it to. What was the fountain pen you mention that wouldn't write at all, if you don't mind me asking?
With respect to nib size:
Like you I generally have small writing although this can vary with the nib size. I've tried to keep it all small for you in this so that you more easily compare them though and see what would suit you better. Despite writing small I generally prefer a broad nib but for you a Japanese e.g. Pilot, medium or fine, or a European e.g. Lamy, fine or very fine might suit you best, as you suspect
. That Herbin was an impulse purchase from Bureau Direct as it was cheap and cute and the talk on here about green ink got me wanting some! It's a very fine nib [for me] and I'm not entirely sure what I think of it yet. I suspect it will favour better quality paper.
I also just treated myself to a Pilot Kakuno for the smiley face nib! From my googling about a converter for it yesterday it seems that Pilot is one of those manufacturers that uses proprietary connections so you might be out of luck with it fitting other manufacturer's pens. I might well be wrong though as I struggled to really get to the bottom of it all yesterday.
Okay, so after less than ideal first try (whenever I needed to use ink the FP wouldn't write at all) I want to give fountain pens a proper second chance. A little bit of additional information: I mostly write using a mechanical pencil (Pentel Technica-X 0.5) and when I do have to use ink my instrument of choice is a Pilot G-2 07 (gotta love that pen!). I'd say my writing is fairly small, with capital letters no taller than 0.5cm. Knowing this:
1) Would a fountain pen be a viable option or is my sporadic use of ink more suitable for a regular pen?
2) What fountain pen/nib size would you recommend? I've mainly looked at Pilot Metropolitan M or Lamy Safari F.
3) Does the Pilot CON-50 fit only Pilot fountain pens or can it be fitted into other manufacturer's FP?
Thank you