A pinhole is limited by a tradeoff between geometric blur and diffraction. Making the hole smaller usually improves sharpness at first, but once it gets too small diffraction makes the image softer again. So yes, diffraction is one of the main limits, and there is an optimal pinhole diameter for a given focal length and wavelength.

To improve a pinhole camera, use the optimal hole size and make sure the hole is very round and cleanly made; a precisely made or laser-drilled pinhole can help. Using larger film can also improve the perceived result.

If you want significantly sharper images, you generally need to replace the pinhole with a lens. A sharp lens plus strong neutral-density filtration can still allow very long exposures while being much sharper than a pinhole. For film, also be aware of reciprocity failure during very long exposures, which can require even longer times and may cause color shifts.

So: no special lens design magically combines pinhole simplicity with much better sharpness at the same tiny aperture, but a real lens with heavy ND filtration is the practical way to get sharper long exposures.