Your situation looks like a race condition and time-of-check/time-of-use, and locks must be used to make those inserts not parallel but serial.
My guess is that SELECT ... FOR UPDATE can lock more rows than needed (depend on ORDER BY in select statement), so causing lock timeouts.

Try using Advisory Locs (https://www.postgresql.org/docs/15/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS) to avoid parallel execution of some part of code.
Just grab the lock (pg_advisory_lock) before selecting the "last" row, and release it (pg_advisory_unlock) after insertion new one.