Debug why Postgres wants to use an uninstalled extension for a function
Why is Postgres trying to look in the intpair extension for the app_user_id function instead of looking in heap_exts?

It sounds like you still have (or had) an entry in pg_proc for app_user_id(bigint, bigint) which points to the old function definition (probin would be $libdir/intpair).
Assuming there is an entry there, I'd guess it was somehow not properly associated with the old intpair extension (maybe manually added, or the code pre-dates PostgreSQL's extension functionality and the function was missed from CREATE EXTENSION FROM unpackaged or something) so was not removed when the extension was dropped, so is still present in your restored instance - but the associated extension library file no longer exists, of course.
The question is whether that pg_proc entry has somehow ended up associated with your new extension, or you have two entries in pg_proc for the function.
What does this modified version of your above query report?
SELECT e.extname, ne.nspname AS extschema, p.proname, np.nspname AS proschema, p.probin
FROM pg_catalog.pg_extension AS e
INNER JOIN pg_catalog.pg_depend AS d ON (d.refobjid = e.oid)
INNER JOIN pg_catalog.pg_proc AS p ON (p.oid = d.objid)
INNER JOIN pg_catalog.pg_namespace AS ne ON (ne.oid = e.extnamespace)
INNER JOIN pg_catalog.pg_namespace AS np ON (np.oid = p.pronamespace)
WHERE d.deptype = 'e'
  AND p.proname = 'app_user_id'
ORDER BY 1, 3;