Modeling State Transitions in Postgres

Unfortunately this approach requires upgrading your transaction isolation level to serializable for correct read-then-update on user status across concurrent transactions. Repeatable read is sufficient for a status column.

This is the kind of stuff you want to be sending to an audit log in a denormalised form. That audit log can live in your primary database, but it shouldn't be connected to the rest of your schema.

At work we have something a tad more complicated where the user's status (especially deactivation) is also inferred from a settings inherited from its user group. So we log the changes in our audit log database but there client logic to offer the "effective" user status in our API.

requires unsetting all existing rows for that user and setting the new one on every status change

Relevant: https://lobste.rs/s/vjelns/postgresql_mvcc_tradeoffs_compared