Get data from 3 tables using UNION ALL
I'm afraid that there's no way that you can avoid using a UNION [ALL] in this case. Your timelapses, compares and archives tables are connected to users and to cameras but not to each other, so in order to link them, we have no choice but to use a UNION.
I played around a wee bit (with a subset of the fields) and I came up with this which may help (all the code below is in a fiddle here):
WITH core AS (
  SELECT 
    'Archives' AS tab_name, a.id, a.exid, LEFT(a.title, 10) AS title, a.camera_id, a.requested_by
  FROM archives a
  UNION ALL
  SELECT
    'Compares', c.id, c.exid, RIGHT(name, 10), c.camera_id, c.requested_by
  FROM compares c
  UNION ALL
  SELECT 'Timelapse', t.id, t.exid, t.title, t.camera_id, t.user_id
  FROM timelapses t)
SELECT * FROM core;

Result:
tab_name    id  exid    title   camera_id   requested_by
Archives    1   concre-r6ys     Concrete P  1   1
Archives    2   201809-27xn     2018-09-26  1   1
Archives    3   201902-htu9     2019-02-01  4   1
...
...
...
Archives    9   spark-kyzrq     Sparks Bui  1   1
Archives    10  201611-luw8     2016-11-06  2   4
Compares    1   compa-ylxwctg   o 29th Oct  1   2
Compares    2   test-hroqe      test        2   3
...
...

So, we can see that in the combined data, we have a way of getting at the source table name - by selecting it as a TEXT string at the beginning.
You can add in users info (like email, firstname, lastname) and cameras by JOINING to this &quot;core&quot; - see fiddle.
You can search now by doing something like this (again, see fiddle):
...
... core CTE query
...
)
SELECT * FROM core
WHERE title LIKE '%Pond%';

Result:
tab_name    tab_id         exid     title      camera_id    requested_by
Timelapse        1  longp-mvxla     Long Ponds         1               2

So, now we know that the photo with the title &quot;Long Ponds&quot; is in the Timelapse table and it's id in that table is 1.
A few words of advice:

I would consolidate my (photograph) tables - this will make matters considerably easier. There should be some combination of fields that is UNIQUE - and you can keep the tab_name field (and use it to enforce uniqueness).

you have some field names (location, type...) as quoted identifiers. This should be avoided - use t_type... something with an underscore - which remains legible but isn't an SQL keyword.
If you decide to consolidate, you can have NULLs where there isn't a corresponding field in the other tables.

a note on performance: As you can see from the EXPLAIN (ANALYZE, BUFFERS) at the end of the fiddle, your UNION plan has more than twice as many operations as my more_info table - this is because I perform the JOIN on users and cameras only once for all the photos whereas you do it three times.
However you decide to do it - via sub-queries or CTEs, only doing it once is the better option. You could wrap your original UNIONs (without JOINs) in a SELECT and JOIN on that if you prefer... YMMV...


I know that this probably isn't the outcome you hoped for, but I hope this has helped a little - best of luck with your project - agus  slán go fóill a chara!