Multi-Tenant RBAC Data Model, Part 2: Roles, Permissions, and the Routing Table

Roles carry a tenant_id; permissions do not. That one asymmetry is the whole design. A role is something a tenant invents - “Regional Manager” means whatever that company decides it means - while a permission describes a capability of the software, and every tenant runs the same software. This part builds the four remaining tables on top of the tenants, users, and members foundation from Part 1, and then shows where the permission rows actually come from: your routing table.

We are assuming a micro framework with no opinion about authorization - no built-in users, no roles, no permissions, nothing to configure. Everything gets built from scratch. That sounds like more work, and it is, but it means every table below exists for a reason we can state out loud.

The Complete Multi-Tenant RBAC Schema

Here is where Part 1 was heading. Seven tables, and every relationship is one we can now derive.

Read it from the outside in. users and tenants are the two independent things. members sits between them as the tenant-scoped membership. roles hangs off tenants. pems hangs off nothing at all. And two junction tables - members_roles and roles_pems - carry the two N:N relationships that make role-based access control work: a member has many roles, and a role grants many permissions.

Why Roles Belong to a Tenant

roles carries tenant_id, and the unique constraint spans (tenant_id, name) rather than name alone. Both facts follow from the same observation: the word “Admin” is not a global concept. Every tenant gets to have one, and two tenants’ Admins have nothing to do with each other.

Scoping the constraint to the pair is what makes that possible. A single-column unique index on name would mean the first tenant to create “Manager” takes the name away from everyone else - which is exactly the kind of accidental cross-tenant coupling that only shows up in production, on the day your second customer signs up. Scoping it to (tenant_id, name) says the real rule out loud: names must be unique within a tenant, and mean nothing outside it.

Why Permissions Are System-Wide, Not Tenant-Scoped

Now the table that surprises people: pems has no tenant_id.

The instinct in a multi-tenant schema is to stamp tenant_id on everything, because tenant isolation is the thing you are most afraid of getting wrong. But a permission is not tenant data. A permission says this software has an endpoint that archives a course. That fact is produced by your codebase, not by your customer. Every tenant is running the same deployment, hitting the same routes, exercising the same capabilities. There is exactly one true list of them.

Think about what a tenant_id on pems would actually commit you to:

• Duplication with no variance. With 40 modules averaging 5 methods each, you have 200 permission rows. Add a tenant_id and 500 tenants turn that into 100,000 rows that are all copies of the same 200 facts.
• A write on every deploy. Ship a new endpoint and you now have to fan a new permission row out to every tenant, forever, as a migration that grows with your customer count.
• Rows that can disagree. The moment the same permission exists 500 times, 500 copies can drift. One tenant’s row says the module is course, another says courses, and the middleware silently stops matching.

So the direction of adaptation runs one way: tenants adapt to permissions, not the other way around. A tenant cannot invent a capability the software does not have. What a tenant can do is compose - build its own roles out of the fixed permission vocabulary, and call them whatever it likes. That is the entire freedom a tenant needs, and roles already provides it.

This is also why pems sits alone on the diagram with a single line into it. It has no foreign key to anything. It is a vocabulary, and vocabularies do not belong to anyone.

Where Permission Rows Come From: Module and Method

If permissions are produced by the codebase, they should be read from the codebase rather than typed by hand into a seed file. That is what module and method are for.

Organize every feature as a module, and every module as a set of methods. course has list, read, create, update, archive. report has list, read, export. The pair is what identifies a capability, which is why the unique constraint on pems spans (module, method) - the same reasoning as (tenant_id, name) on roles, applied to a different pair. Neither half means anything alone; create is not a permission, and course is not a permission, but course.create is.

The way to keep that list honest is to attach the pair to the route itself. Whatever your framework’s routing looks like, every route gains two pieces of metadata:

GET    /courses            -> module: course,  method: list
POST   /courses            -> module: course,  method: create
GET    /courses/{id}       -> module: course,  method: read
PATCH  /courses/{id}       -> module: course,  method: update
POST   /courses/{id}/archive -> module: course, method: archive

Now the routing table is the permission list. At deploy time, walk the registered routes, collect the distinct (module, method) pairs, and upsert them into pems. Adding an endpoint adds a permission automatically. More importantly, it becomes structurally difficult to ship an unguarded endpoint, because the route that has no module and method is the one that stands out in review.

How Default Roles Bootstrap a New Tenant

There is a third piece of metadata on the route, and it is the one that pays off biggest: which default roles this capability belongs to.

POST   /configs   -> module: config,  method: create,  default_roles: {admin}
POST   /courses   -> module: course,  method: create,  default_roles: {admin, manager}
GET    /courses   -> module: course,  method: list,    default_roles: {admin, manager, user}

Creating a system config is an owner-level act, so it is {admin} and nothing else. Creating a course is ordinary operational work, so {admin, manager}. Listing courses is something everyone does, so all three. The developer writing the endpoint is the person best placed to make that call, and they make it once, at the route, next to the code it guards. That array lands in pems.default_role_names.

Now invert it at tenant creation. Every permission already knows which default roles it belongs to, so the bootstrap routine reads the whole pems table, groups by role name, and creates Admin, Manager and User for the new tenant with their grants already correct. No per-tenant configuration, no checklist, no human deciding for the four hundredth time that a manager can create a course. A brand-new tenant gets a complete, working set of roles for free, on the first request after signup.

Those default roles are read-only. A tenant can assign them to members as they are, but nobody - not even the tenant admin - can edit their permissions. Customization happens by duplicate and modify: need a “Course Coordinator” who is a Manager without billing access? Copy Manager, drop the permissions that do not apply, save it under a new name. The copy belongs to the tenant and is theirs to change however they like.

That restriction looks like a limitation and is actually the thing that makes the whole system maintainable, for a reason that only shows up on the next deploy. Hold onto it.

Either way, this is the composition freedom from earlier made concrete: a tenant cannot invent capabilities, but it can slice the fixed vocabulary any way it likes, starting from a sensible default instead of a blank page.

Storing this as an array rather than another junction table is deliberate. These are role names, not foreign keys - they cannot point at any tenant’s roles rows, because at deploy time those rows do not exist yet, and after bootstrap each tenant has its own. The array is a template, read once per tenant and never joined against. Array types are first-class in PostgreSQL, and this is exactly the case they are for.

What Happens to Existing Tenants When You Add a Route

Bootstrap answers the new-tenant case. It says nothing about tenant number one, which signed up last year and whose Admin role predates the route you just shipped. Deploy course.archive with {admin, manager} and the permission row lands in pems immediately - but no existing tenant can use it, because roles_pems holds no grant for it. The capability exists and nobody has it.

The answer is a command - call it syncrolepems - that runs after every deployment carrying a permission change. It reads pems, works out what each default role should grant, and makes every tenant’s default roles match.

Here is where the read-only rule pays off. Because no tenant can have edited a default role, there is no local intent to preserve, and the sync does not have to be careful. It does not need to work out which permissions are new, diff against the last run, or track what it did previously. It just asserts the desired state:

For every tenant, the grants on a default role are exactly the permissions naming that role in default_role_names. No more, no less.

That declarative form is strictly better than an incremental backfill, and it handles a case an incremental one silently gets wrong. Remove manager from a route’s default_role_names - you have decided that archiving a course is an owner-level act after all - and a sync that only ever adds new grants leaves every existing tenant’s Manager holding a permission you just revoked at the source. A declarative sync removes it everywhere on the next deploy, because the desired state no longer contains it. Additions and removals travel by the same mechanism.

Matching is by name, which is the second reason default_role_names stores names rather than ids: the same array that builds roles for a new tenant locates the equivalent roles in every old one. The (tenant_id, name) unique constraint makes that lookup exact - within one tenant, “Manager” identifies precisely one role.

The rules the command lives by:

• Never touch a user-defined role. This is the hard boundary, and the reason the sync can be aggressive everywhere else. The Course Coordinator a tenant copied from Manager is their artifact. It was correct on the day they saved it, and your deploy pipeline has no standing to add powers the admin never chose or remove ones they did. A duplicated role is frozen at the moment of copying and only ever changes when its owner changes it.
• Reconcile both directions on default roles. Add the grants that should exist, remove the ones that should not. Anything else lets the defaults rot.
• Stay idempotent. The composite primary key on roles_pems means re-running is free - insert on conflict, do nothing - so a deploy that fails halfway can simply be run again.
• Run it on every deploy that changes permissions. It is cheap, it is idempotent, and the alternative is remembering.

Removal of a route is the one case where deleting reaches further. When a route disappears the capability genuinely no longer exists, so the pems row goes and every grant referencing it follows through the foreign key - including grants held by user-defined roles. That is not a violation of the boundary above: you are not revoking anyone’s access, you are retiring a verb from the vocabulary, and a grant to a capability that no longer exists is not a permission, it is a dangling row.

One thing the ERD does not show: nothing in roles marks which rows are the read-only defaults. You can derive it - a role is a default if its name appears in some permission’s default_role_names, and the unique constraint keeps that unambiguous - or you can make it explicit with a boolean column and let the database refuse the edit rather than the application layer. The derived version costs no schema change; the explicit one survives someone writing a second code path.

Which Role Does a New Member Get?

A members row appears the moment someone joins a tenant, and until they hold a role they can do precisely nothing. Something has to decide their starting permissions.

The schema-shaped answer is another boolean on roles: mark one row per tenant as the default, and assign it on signup. One column, per-tenant flexible, obvious. It is also the one column I deliberately leave out.

Consider how it fails. A flag is a switch, and switches get flipped - by a support engineer in an admin panel, by a migration that seeds the wrong row, by a helpful script. Flip it onto Admin and nothing breaks. No error, no failed request, no alert. Every person who joins that tenant from then on arrives holding full administrative access, and the system is behaving exactly as configured. You find out weeks later, if you find out at all.

That is what makes it disqualifying. It is not a one-time mistake, it is a standing condition that keeps manufacturing over-privileged accounts until somebody notices, and the blast radius grows with every signup.

So the default role is named in source code instead. User is the role with the fewest permissions, that fact is known while the routes are being written, and it belongs in the same place default_role_names already lives - the codebase. Changing which role new members receive becomes a code change: reviewed, diffed, deployed, attributable to a person. It stops being a checkbox.

The consequence is that privilege only ever increases by explicit human action. A new member who needs to be a Manager asks an admin to upgrade them, and somebody with the authority to grant it does so on purpose. That is a slightly worse user experience and a considerably better security posture: every escalation has a name attached, and the path of least resistance is the least-privileged one.

This is a different kind of flag from the read-only marker discussed above, and the difference is the whole argument. A read-only marker is descriptive - it states what a row is, and getting it wrong lets through an edit that should have been refused. An auto-assign marker is imperative - it causes privilege to be granted, on every future signup, with nobody looking. Those failure modes are not comparable, which is why one is a reasonable column and the other is not.

It costs something, and the cost is worth naming: a tenant cannot decide that its new members start as Managers. Everyone starts at the bottom and gets promoted. Some customers will ask for it, and the flexible version is entirely buildable - the boolean is easy to add and it would work. Choosing not to add it is a judgment that a silent, persistent failure pointing toward more privilege is the wrong risk to accept for the convenience it buys.

How Middleware Turns a Route into an Authorization Check

The two ends now meet in the middle, and they meet on the same pair of strings.

At deploy time, the routing table writes into pems: these are the capabilities that exist.

At request time, the middleware reads the matched route’s metadata to learn which (module, method) this request requires, then asks one question: does the current member hold a role that grants it? The check never looks at the URL, the HTTP verb, or the controller name. It compares a pair of strings from the route against a set of pairs belonging to the member.

That is what makes the guarantee from Part 1 real - even if they know the API specs and bypass the UI, they will still be blocked at the API layer. There is no UI-side permission logic to bypass, because the UI was never the thing enforcing it. The route declares what it needs; the middleware decides. A client that calls the endpoint directly hits exactly the same check.

Resolving the member’s permission set is a walk across the whole schema: members to members_roles to roles to roles_pems to pems. Four joins to answer one boolean.

Why Role Changes Need a Logout

Part 1 mentioned that when an admin changes a user’s roles, the affected user has to log out and back in. Now we can see why that is a design decision rather than a bug.

Those four joins run once, at login. The resulting set of (module, method) pairs is flattened into the session or token, and every subsequent request checks against the copy it is carrying. The middleware never touches the database to authorize a request.

The tradeoff is plain: one permission lookup per session instead of one per request, paid for with a propagation delay when an admin edits a role. For most systems that is the right trade - authorization changes are rare and logins are frequent, and a stale permission set that expires with the session is an acceptable window. When it is not acceptable, the fix is not to abandon the cache but to add a revocation signal: bump a version number on the tenant or the member, carry it in the token, and reject tokens whose version has fallen behind. You keep the fast path and buy back immediate propagation for the rare case.

Why the Junction Tables Have No ID

Both members_roles and roles_pems use the two foreign keys as their primary key, with no surrogate id in sight. Part 1 gave members its own id and argued hard for it - so why the opposite answer here?

Because these two really are nothing but pairings. roles_pems has no lifecycle: the row means this role grants this permission and it will never mean anything else. No status, no history worth keeping on the row itself, and nothing else in the schema will ever need a foreign key pointing at one specific grant. The pairing is the row’s identity, so making the two keys the primary key is the honest shape, and it comes with duplicate protection for free.

members failed both of those tests, which is why it got an id. Look at the U badges on its tenant_id and user_id in the diagram: that composite unique constraint is doing the job the primary key does for roles_pems. One membership per user per tenant, guaranteed either way - the difference is only whether that guarantee also has to serve as the row’s identity.

Same schema, same rule, opposite conclusions - which is the point. The shape of a junction table is a consequence of what the row means, not a habit you apply uniformly.

Let the Table Name Announce Its Shape

Since the shape is a decision rather than a default, the name is a good place to record which decision was made. The convention is small and worth adopting: pluralize both halves for a pure junction, and only the second half for a junction with its own identity.

• members_roles - both plural. A pure pairing, keyed on the two foreign keys, nothing else on the row.
• member_roles - singular then plural. An entity in its own right: surrogate id, the two foreign keys as ordinary fields, and a unique constraint over the pair.

The second form is what you reach for when the junction stops being an internal detail - most often when it spans two bounded contexts, with one parent in the account model and the other in auth. Those are exactly the junctions that acquire a lifecycle, get referenced by other tables, and may one day have to survive the two contexts splitting into separate databases, which is why they need an identity that does not depend on the pairing staying put.

The payoff is that reading the schema stops requiring a lookup. roles_pems tells you it is a composite-key pairing; member_roles tells you it has an id and a unique constraint, and that somebody thought about why. You know the structure of the table before you open it, and a name that disagrees with its own shape becomes an obvious thing to fix in review.

Drawing these is also where an ERD tool earns its keep or gets in the way. In Schemity, dragging an N:N relationship between two entities creates the junction table with both foreign keys and the composite primary key already in place, so the default shape is the correct one for a pure pairing - and when a junction turns out to need its own identity, you add the id and move the uniqueness into a named composite constraint instead of rebuilding the table.

The Constraint This ERD Cannot Express

One honest gap, because it is the classic multi-tenant RBAC bug and the diagram will not warn you about it.

Nothing in the foreign keys stops members_roles from linking a member in tenant A to a role owned by tenant B. Both foreign keys are individually valid - the member exists, the role exists - and the row is a cross-tenant privilege escalation. Referential integrity checks that rows exist, not that they belong to the same tenant.

There are three ways to close it, and the schema should pick one deliberately:

• Carry tenant_id into the junction and use composite foreign keys, so the database itself refuses a mismatched pair. Strongest guarantee, at the cost of a wider key.
• Enforce it in the application layer, where role assignment already knows the acting member’s tenant. Cheapest, and correct until someone writes a second code path.
• Enforce it with row-level security, if your database supports it and you are already scoping queries by tenant.

Whichever you choose, notice that the ERD is what surfaced the question. The gap is visible precisely because the auth tables and the account tables sit in different bounded contexts and you can see the lines crossing between them - roles.tenant_id and members_roles.member_id reaching into the account model from opposite directions, with nothing tying them together. When the coupling surface between two contexts is small enough to read in one glance, the missing constraint in it is findable.

Seven tables, one asymmetry, and a routing table doing double duty as the source of truth for what your software can do. That is a complete role-based access control model for a multi-tenant system, and none of it needed a framework’s opinion to get there.