Role-based access control: roles in their own table, rules in the database

Why roles live in their own table

If a role is a column on the user's profile, and users can edit their profile, then users can edit their role. Storing roles as separate rows removes that entire class of privilege escalation, and it allows one user to hold several roles at once, a branch manager who is also a head-office reviewer, for instance.

• One row per user and role, so multiple roles are natural
• Profile edits can never change what a user is allowed to do
• Roles are readable in an admin view without guesswork

Checks run inside the policy

Role checks are wrapped in security-definer functions and called from row-level security policies. The practical result is that the rule applies to every access path, the site, an admin screen, a server function, a direct API call, because Postgres itself enforces it.

In multi-branch projects the same pattern carries branch scope: helper functions establish whether the current user is staff or a manager at the branch that owns the row, and head office sees across branches while a branch sees only its own.

What this does not include

To be precise: a generic before-and-after audit trail across every table is not implemented as a platform-wide feature. Purpose-built event logs exist where they matter, document signing events and affiliate invite auditing, for example, and additional logging is added deliberately per project rather than assumed.