Yes, an AirTag is not a GPS tracker so it will need the assistance of another phone to determine the location. It does not have to be your phone, and any iPhone that comes within bluetooth range will update the location of the AirTag to Apple servers and you will be able to see where it is at.
That is not a software limitation, that is solely because the device itself is not able to determine the location or broadcast using a cellular network of its own. You can buy devices like that, but they are more expensive, larger, and requires much more power so it will need to be charged frequently and/or replace batteries frequently. Apple does not sell a device like that.
A dead phone that is not able to make any network connection or use the bluetooth network is always going to be untraceable. When a phone goes dead, there is still reserve power left on the battery and the BLE connection for tracking is Low Energy, so for a short period of time there may still be enough power left for it to make that Bluetooth connection to another phone.