4. A role hierarchy instead of an admin flag

Context

Access was originally binary: a member either held an admin row in user_roles or did not. Every check asked whether that exact role was present.

That stopped fitting as the community grew. People were needed who could publish announcements without also being able to approve profiles, delete members, or read the subscriber list. There was also no record of who had granted access to whom.

Decision

Four ranked levels: user 0, editor 10, admin 20, owner 30. Checks became hierarchical through has_role_at_least, ranks were stored in a table, role changes were routed through a single function, and every change is recorded in an audit log.

Consequences

Exact-match checks had to be rewritten. An owner holding only the owner role failed every check that asked for exactly admin. Six policies were rewritten in the migration, and any new check must use the hierarchy-aware function rather than comparing roles directly.

One role per member. The old unique constraint allowed several rows per user; a hierarchy needs exactly one. The migration deduplicated existing rows keeping the highest rank, because dropping the wrong row would have silently demoted a working administrator.

Rank lives in a table, not in the enum's order. Enum declaration order cannot be changed later without rebuilding the type, and a database that received the labels in a different order would compare differently with no error at all. Storing ranks explicitly removes that class of failure.

The enum change needed its own migration. PostgreSQL will not allow a new enum value to be used in the transaction that adds it, so adding the labels and using them had to be two files applied in sequence.

Role changes have one path. user_roles is unwritable through the API; the only way in is a function that requires owner level, takes a lock before reading, refuses to leave zero owners, and writes an audit row. That makes escalation attempts visible and concurrent changes safe.

Studio cannot change roles. The function reads the caller's identity from their token, which does not exist in the SQL editor or for the service role, so it always refuses there. Recovery from a total lockout means editing the table directly as a superuser. This is deliberate, and it is the documented break-glass path.

The zero-owner rule is checked after applying the change, so that raising the error rolls it back. The alternative, forbidding anyone from changing their own role, sounds safer but makes the last owner permanently irremovable.