Not every retro computer uses composite. Some of them use things that are composite-like, but need conversion. For example, built in CRT systems and Amiga signals. While it's true that things like an Amiga and similar can generate Composite, the signal is far from clean and sharp, especially when you blow it up to 40+ inches. The key is to convert it from the native (sharp) signal to something upscaled, which is why I recommend an FPGA solution specific to the signal. It requires minimal electronics knowledge and can be done (usually) for less than $30.

Fully agree. If you are handy and you need something for a specific system, this is definitely the way to go. Things I've built that have cost me $20 each: Mezzanine board that sits between the 6502 and the motherboard in a PET, snooping on and copying all writes to video RAM to a dual ported SRAM. The other port on the SRAM is hooked up to an FPGA that continuously reads the SRAM bytes, maps the bits through an image of the font ROM and generates an upscaled 1080p HDMI signal. Of course, the letters are "jaggy", but everything is also super sharp since it's a native signal from the native data rather than a translation of the composite-ish signal that the PET builds. FPGA that takes the raw Amiga video output from an Amiga 2000 and upscales it to 1080p over HDMI. This same FPGA has a module that can be switched to take as input any of the resolutions that the Picasso II video card can produce, converting all to reasonable upscaled versions at 1080p. If you've never done it, this might sound really hard... Amazingly, building an HDMI output on an FPGA can be done from scratch in just a few minutes. The hardest part is figuring out how to configure the PLLs in the FPGA!