Roles and Permissions Schema Design for Multi-Tenant RBAC (Part 2)

Disclosure: I build Schemity, a desktop ERD tool - this post is from our blog and uses it for the examples.

TL;DR: Roles belong to a tenant; permissions do not. A permission describes what the software can do, so it is system-wide and every tenant adapts to it. This part derives the roles, members_roles, roles_pems, and pems tables, and shows how a module and method pair extracted from your routing becomes the permission check your middleware runs on every request. A stable key on each permission survives module and method renames, and a deprecated_at timestamp retires removed permissions visibly instead of silently deleting grants from custom roles.

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.

	roles	pems
Scope	One tenant	The whole system
Defined by	The customer	The codebase
Changes when	An admin edits them	You deploy a new route
Unique over	(tenant_id, name)	(module, method), plus a stable key
Row count	Grows with customers	Grows with features

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 - and never frozen into a database enum type whose value set only a migration can change. 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.

Why Each Permission Carries a Key That Never Changes

Matching by (module, method) has one failure mode, and it is the most ordinary act in a codebase: renaming something. Decide that course should have been courses, update the routes, and the next deploy's upsert finds no row for the new pair - so it inserts one. The old row now matches no route, so it reads as removed, and every grant that referenced it - in every tenant, custom roles included - is attached to a row the middleware will never look up again. A rename, the safest refactor you can make in code, becomes a silent permission wipe in the database.

The fix is to separate what a permission is called from what it is. Each route carries one more piece of metadata: a key - an opaque unique string, a UUID generated once when the route is first written and hardcoded in source right next to module and method:

POST /courses -> key: 6b9d5f2a-..., module: course, method: create, default_roles: {Admin, Manager}

The deploy-time sync upserts by key: if the key exists, update its module, method and default_role_names in place; if not, insert a new row. Renaming course to courses is now an UPDATE to one row that every grant already points at, and nothing anywhere is lost. The unique constraint over (module, method) stays - the pair is still what the middleware matches at request time and still the human-readable identity - but the key is the identity that survives time.

The rename hazard does not disappear; it moves onto a field whose only job is to never change, and which nobody has a reason to change, because a UUID does not drift toward better naming the way words do. The one discipline it demands is that keys are generated fresh, never copy-pasted from the route above - a duplicated key is the new way to corrupt the list, and it is exactly the kind of mistake a unique constraint on key turns into a deploy-time error instead of a silent merge.

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

Nothing - until a sync runs, no existing tenant can use the new capability. Deploy course.archive with {Admin, Manager} and the permission row lands in pems immediately, but roles_pems holds no grant for it: tenant number one signed up last year, and its Admin role predates the route. So the deploy pipeline ends with a command - call it syncrolepems - that walks every tenant and syncs its default roles against the pems table: after it runs, each default role grants exactly the permissions that name it in default_role_names - no more, no less.

In member terms: every member holding a default role picks up the new permission the moment the pipeline runs syncrolepems, in every tenant at once. Members on custom roles get nothing - their roles stay exactly as their admin built them, until that admin grants the new permission on purpose.

This is where the read-only rule pays off. No tenant can have edited a default role, so there is no local intent to preserve - no diffing against the last run, no working out which permissions are new, just asserting the desired state in both directions. The removal direction is the one an incremental, add-only backfill silently gets wrong: decide that archiving a course is an owner-level act after all, drop Manager from the route's default_role_names, and the next deploy revokes the grant from every tenant's Manager, because the desired state no longer contains it. Re-running is free - the composite primary key on roles_pems makes every insert an on-conflict-do-nothing - so the command simply runs on every deploy.

Matching is by name, which is the second reason default_role_names stores names rather than ids: the same array that builds Admin, Manager and User for a new tenant locates the equivalent roles in every old one, and the (tenant_id, name) unique constraint makes that lookup exact.

The one hard boundary: never touch a user-defined role. The Course Coordinator a tenant copied from Manager is their artifact, frozen at the moment of copying, and the deploy pipeline has no standing to add powers the admin never chose or remove ones they did. Route removal is where that rule gets tested, because when a route disappears the capability genuinely no longer exists, and something has to happen to the grants pointing at it. The tempting implementation is to delete the pems row and let the foreign key cascade take the grants - clean, and silent, which is exactly the problem. The admin who added that permission to their Course Coordinator yesterday opens the role tomorrow and finds it shorter than they left it, with nothing anywhere to say why.

So removal is a state, not a deletion. When a key no longer appears in the routing table, the sync stamps that row's deprecated_at timestamp instead of issuing a DELETE. A deprecated permission denies at the middleware exactly as if it were gone - the capability really did leave the software, and the check simply requires deprecated_at to be null - but the pems row itself stays.

From there, the two kinds of role part ways, exactly along the ownership line drawn above. Default roles belong to the pipeline, so syncrolepems drops the grant outright: the desired state no longer contains the deprecated permission, and no notification is owed for rows no tenant ever edited. Custom roles belong to the tenant, so their grants are left precisely where they are - and the role editor renders the permission as deprecated. The custom role shows what happened to it instead of hiding it: the permission is right there, visibly retired, and the admin who added it yesterday learns the vocabulary changed rather than suspecting their role was tampered with. And if the route comes back next sprint, the sync clears deprecated_at on the same key - default roles get the grant re-asserted, and every custom-role grant resumes working, because none of them was ever destroyed.

Seen whole, the pems side of the sync is nothing but set membership on key. Because keys never change, one pass compares the keys extracted from the routes against the keys in the table, and every row falls into one of four branches:

• Key in the routes and in the table, with a different module or method - a rename. Update the row in place; every grant stays attached.
• Key in the routes but not in the table - a new capability. Insert it.
• Key in the table but not in the routes - a deleted route. Stamp deprecated_at.
• Key in both, but the row is stamped - the route came back. Clear deprecated_at.

A deleted route is an absence in source, not an event - but the sync never needs the event, because comparing full key sets makes the absence computable. And since all four branches are decided by key membership alone, renames and deletions cannot touch the same row, the branches run in any order, and the command stays safe to re-run blindly - the property this whole pipeline is built on.

One edge deserves a comment in the sync code before it costs a debugging session: in-place renames under the (module, method) unique constraint can transiently collide. If a deploy renames one permission onto the pair another permission just vacated, row-by-row UPDATE order decides whether the sync hits a unique violation halfway through. Declaring that constraint DEFERRABLE INITIALLY DEFERRED and running the sync in a single transaction moves the check to commit time, after every rename has landed - which is precisely the situation that PostgreSQL feature exists for. Retiring a verb from the vocabulary and erasing every sentence that used it are different operations - the first is a deploy, the second is a decision, made later, if ever.

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 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.

	members	members_roles, roles_pems
Primary key	Surrogate id	The two foreign keys
Pairing enforced by	A composite unique constraint	The primary key itself
Has its own state	Password, login attempts, status	Nothing beyond the pairing
Referenced by other tables	Yes, extensively	Never

And since the shape is a decision, the name is where to record it. The convention is small and worth adopting: pluralize both halves for a pure pairing, only the second half for a pairing that carries its own identity, and give the table a domain name once it is an entity in its own right.

• members_roles, roles_pems - both plural. Composite key, nothing but the pairing.
• member_roles - singular then plural. Surrogate id, the two foreign keys as ordinary fields, a unique constraint over the pair. The form for a junction that spans two bounded contexts and may have to survive them splitting into separate databases - it needs an identity that does not depend on the pairing staying put, but it is still recognizably a pairing.
• members - a name of its own. Mechanical naming would have produced tenant_users, but the table on the left of that comparison is an entity that happens to join two tables, and the domain already has a word for what its rows represent. tenant_users would describe how the table came to exist; members describes what it is.

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; members tells you to stop thinking of it as a junction at all. 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.

Comments

[Mihir kanzariya]

the roles-carry-tenant_id-permissions-dont asymmetry is the right call and underrated. you ship a new permission with a code deploy (it's just a capability) and never touch tenant data, while roles stay fully customer-defined. one gotcha on deriving permissions from the routing table: key the permission on a stable module+action id, not the route path. the moment someone renames or refactors a route, every roles_pems grant pointing at the old string silently orphans and people lose access with nothing thrown. that bug class is brutal because it just quietly denies instead of crashing.

[Son Tran]

Thanks Mihir, that's a good point and the string-stability angle is the real risk. Worth noting the design already keys on (module, method) rather than the route path, so renaming or refactoring a route doesn't orphan anything. Where you're right is renaming the module/method identifiers themselves: that still breaks grants. Default roles self-heal there, since syncrolepems reasserts them declaratively on the next deploy; it's the custom roles that silently orphan, exactly because the sync leaves them untouched. Have you hit this in practice, and did you solve it with a migration on the identifier or something more automatic?

[Mihir kanzariya]

Fair correction, (module, method) is the right key and I misread the routing-table part.

Honest answer on practice: I have hit the class rather than your exact schema, so treat this as reasoning rather than a war story.

The thing I would avoid is a migration per rename. It works right until someone forgets, and the failure mode is a silent denial rather than an error, which is the whole problem. Two things make it structural instead.

Treat identifiers as append-only and keep a rename map the resolver consults, so a rename is additive and old grants keep resolving while you backfill custom roles lazily. Drop the alias once nothing points at it any more.

Then put a guard in CI. If identifiers live as constants in code, diff them against the previous release and fail the build when one disappears without a matching alias entry. That turns the exact failure we are both worried about into a build error, which is about the only reliable way I know to catch a bug whose symptom is nothing happening.

Cheap either way: a health check that lists roles_pems rows whose permission id no longer resolves. Custom roles orphan silently by design here, since sync deliberately leaves them alone, so something has to be looking on their behalf.

[Son Tran]

The alias map + CI guard is a solid structural answer and reasoning through this thread pushed me to make the fix schema-level instead. I've added two fields since (post is updated):

pems.key: An opaque UUID generated once when the route is written, hardcoded in source next to module/method. The deploy sync now upserts by key, so renaming a module or method is an UPDATE to the one row every grant already points at. That's your append-only identifier idea taken to its endpoint: no alias table to consult and later garbage-collect, because nothing human-meaningful is the identity anymore. The rename hazard moves onto a field nobody has a reason to rename.

pems.deprecated_at: Route removal stamps this instead of issuing a DELETE. Middleware requires it to be null, so denial is still immediate, but the row and every custom-role grant survive. Default roles drop the grant on the next declarative sync (nothing to notify, no tenant edited them); custom roles keep theirs and the role editor renders the permission as deprecated. So the "something looking on their behalf" you're asking for exists, but it's not a health check finding orphans after the fact - the orphan state is unrepresentable. A grant always resolves to a row, and the row carries its own tombstone.

Your CI guard still earns a place though: keys are hand-written constants, so the remaining failure mode is a copy-pasted key from the route above - and the unique constraint on key turns that into a deploy-time error instead of a silent merge, which is the same "make nothing-happening impossible" property you're after.

[Mihir kanzariya]

the opaque key is the right endpoint, that is cleaner than the alias table i was picturing.

one thing i cannot tell from the description: how does the sync notice a route that is simply deleted rather than renamed? an upsert-by-key pass only ever sees what is still in source, and a removed route is an absence rather than an event. unless the deploy also stamps deprecated_at on every key it did not see in that run, a deleted route leaves a live permission row that middleware still lets through.

same shape as the orphan problem, just pointed the other way. not a grant with no row, but a row with no route.

[Son Tran]

You've found the core idea, and the answer turns out to be simple because the keys never change: the sync doesn't need to delete, it computes. syncrolepems compares the full key set extracted from the routes against the full key set in pems, and every row falls out of set membership:

• Same key, different module/method → a rename: update in place, grants untouched.
• Key in routes only → new: insert.
• Key in the table only → deleted: stamp deprecated_at.
• Key in both, but the row is stamped → the route came back: clear it.

So yes, "stamp every key it did not see in that run" is exactly what happens, it's just one pass inside the same command rather than a separate sweep. A removed route is an absence in source but a computable difference in the sync. And because all four branches derive from key membership alone, renames and deletions can never touch the same row, order doesn't matter, and re-running blindly on every deploy stays free.

One small correction to the failure mode, though: a row with no route can't actually be let through by middleware. If there's no route left to match then the 404 fires before the permission check ever runs. The stale row's real harm is quieter: without the stamp, the role editor would keep offering a capability the software no longer has, and nobody would know. With deprecated_at, that same row shows up with the warning attached then users can see exactly which pems are deprecated. That's the gap the stamp closes.

[Mihir kanzariya]

you are right about the 404 and my failure mode was wrong. if the route is gone the request never reaches the permission check, so nothing is being let through. the harm is the role editor still advertising a capability the software no longer has, which is quieter and in some ways worse, because nobody gets an error to chase.

set membership in one pass is also better than the separate sweep i was picturing. four branches derived from the same key set cannot disagree with each other, which a sweep running alongside an upsert absolutely can.

the one thing i would guard is the input to that comparison. absence in source is what drives deprecation, so an incomplete extraction looks exactly like a mass deletion. if a module fails to import during a deploy and its routes never make it into the key set, the sync will happily stamp every one of them. a size check before applying, refuse to proceed if the extracted set shrank by more than some percentage, costs nothing and turns that into a failed deploy instead of a silent capability wipe.