To cut to the chase, Flatpak is nice to have, but isn't a complete replacement, and the other suggested "replacement", Pacstall, targets Ubuntu instead of base Debian. If you want more detail, read the spoiler:
spoiler
I use EndeavourOS as my daily driver, so I'm used to the sheer convenience of the AUR. However, I'm not a fan of how that convenience is tied to a continually-changing rolling release distro. This becomes especially apparent whenever a major KDE update comes out, or the Arch Linux people decide to make yet another big change.
Debian is the other main distro I use. I mostly install it on systems I don't intend to use often, but recently I started dual-booting it on my main desktop. I like how slowly things change in the Debian world, and if it were easier to run uncommon software on it, I'd probably use it more.
I know Debian can use Flatpak, and while it is nice to have, it's not a complete replacement for the AUR. The other "replacement" MPR/Makedeb's developer suggested is Pacstall, but that's for Ubuntu and not base Debian. If Ubuntu were the same distro it were back when 20.04 came out, I wouldn't be opposed to using it, but Ubuntu's gone to shit since then.
EDIT: Inb4 Distrobox too.
EDIT2: The reason I want an AUR equivalent is convenience. Yes, I commit the cardinal sin of using an AUR helper when I use EndeavourOS. I like being able to install things and have them integrate into my normal system package management without having to manually set up scripts.
Famous last words. But if you really know how to use the AUR you can create your own PKGBUILDs for whatever it is you want.
What do you want? Your OP sounds like you want to combine different package management systems.
I want an AUR, but for Debian. Same thing, PKGBUILDS included, but targeting apt instead of pacman, and Debian's packages instead of Arch's. I feel like it would work well since Debian has a stable base, so it'd be easy to build packages against it.
Ah, OK. There's no marketplace for self-made Debian packages that I know of, but of course it's still possible to make them, even create your own repositories and add them to your apt. And in good old Debian fashion it's going to be way more low-level, and confusing to find definite documentation. One thing I'm seeing again and again is
dpkg-deband otherdpkg-*commands.https://debian-handbook.info/browse/stable/debian-packaging.html
https://dario.griffo.io/posts/ultimate-guide-debian-packaging/
https://www.baeldung.com/linux/create-debian-package
Pity makedeb is unmaintained. But maybe still usable?