As example I took archivers/blosc2 (GH_*) & archives/zpaqfranz (DIST_TUPLE).
Main difference are mastersites & distfiles from portroach.db :

archivers/blosc2 = https://github.com/Blosc/c-blosc2/archive/refs/tags/v2.14.3/ + c-blosc2-2.14.3.tar.gz
archives/zpaqfranz = https://github.com/ + fcorbelli/zpaqfranz/archive/refs/tags/59.5.tar.gz

Portroach use the first one (mastersites) which does not contain project/name on DIST_TUPLE.
If we append distfiles to mastersites before GetFiles then SiteHandler can find new version of zpaqfranz.
For me it's not clear yet if this should be changed somewhere downside (mk implementation, sqlports, portroach build).

I checked quickly how SiteHandler use url and, except Mozilla.pm (but does it match any port ?), including distfile into url should not be a problem.
But to be safe, I choose to implement a fallback, first try with only mastersite and if GetFiles failed then try harder with distfiles.

I do not run portroach in prod and I just discovered it (& perl), this diff was only lightly tested on this small example.
I will experiment further but maybe this small diff is enough, sry if I screw up something.

[zpaqfranz                     ] VersionCheck()
[zpaqfranz                     ] Checking site: https://github.com/                                         
[zpaqfranz                     ] [https://github.com/           ] Using dedicated site handler for site.
[zpaqfranz                     ] [https://github.com/           ] UPDATE 59.5 -> 59.7
[zpaqfranz                     ] Done
[blosc2                        ] VersionCheck()
[blosc2                        ] Checking site: https://github.com/Blosc/c-blosc2/archive/refs/tags/v2.14.3/
[blosc2                        ] [https://gith...s/tags/v2.14.3/] Using dedicated site handler for site.
[blosc2                        ] [https://gith...s/tags/v2.14.3/] UPDATE 2.14.3 -> 2.14.4
[blosc2                        ] Done


Index: Makefile
===================================================================
RCS file: /cvs/ports/misc/portroach/Makefile,v
diff -u -p -r1.40 Makefile
--- Makefile	11 Sep 2023 14:45:35 -0000	1.40
+++ Makefile	6 Jun 2024 17:07:13 -0000
@@ -3,7 +3,7 @@ COMMENT=		OpenBSD ports distfile version
 GH_ACCOUNT=		jasperla
 GH_PROJECT=		portroach
 GH_TAGNAME=		2.0.11
-REVISION=		7
+REVISION=		8
 
 CATEGORIES=		misc
 
Index: patches/patch-portroach_pl
===================================================================
RCS file: patches/patch-portroach_pl
diff -N patches/patch-portroach_pl
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ patches/patch-portroach_pl	6 Jun 2024 17:07:13 -0000
@@ -0,0 +1,30 @@
+Index: portroach.pl
+--- portroach.pl.orig
++++ portroach.pl
+@@ -503,12 +503,21 @@ sub VersionCheck
+ 		if (my $sh = Portroach::SiteHandler->FindHandler($site))
+ 		{
+ 			info(0, $k, $site, 'Using dedicated site handler for site.');
+-
+-			if (!$sh->GetFiles($site, $port, \@files)) {
+-				info(0, $k, $site, 'SiteHandler::GetFiles() failed for ' . $site);
+-				next;
+-			} else {
++			if ($sh->GetFiles($site, $port, \@files)) {
+ 				$method = METHOD_HANDLER;
++			} else {
++				foreach my $distfile (split ' ', $port->{distfiles})
++				{
++					my $uri = URI->new($distfile)->abs($site);
++					if ($sh->GetFiles($uri, $port, \@files)) {
++						$method = METHOD_HANDLER;
++						last;
++					}
++				}
++			}
++			if ($method != METHOD_HANDLER) {
++				info(0, $k, $site, 'SiteHandler::GetFiles() failed for ' . $port->{distfiles});
++				next;
+ 			}
+ 		}
+ 		elsif ($site->scheme eq 'ftp')