Use db-generated UUID "id" primary keys. Add a bigserial "order" field to tables that require stable temporal ordering, and then either create a view to "order by" it or always add "order by" to stable-required statements.
One downside is UUIDs take twice the space of a BIGINT which is minor on the surface, but makes a huge difference when doing lots of joins on that key. I used to see 3-4x difference in some queries even using postgres’ uuid type(not the string column of fools). Doubling the space used by your keys also means less of your indexes stay locked in RAM.
