Inserting State Transitions in Postgres

In Modeling State Transitions in Postgres , we replaced a status column on users with an append-only user_statuses table. That design gives us full history that can be queried efficiently for the most common cases. But it introduces an edge case worth addressing: what happens when two transactions try to change the same user’s status at the same time? With a column, the overwrite is harmless at the database level. With an append-only model, both records survive, so the problem needs to be add...