VerticalsRolesPermissions

A reusable, dependency-injected Role-Based Access Control (RBAC) engine for Node.js / Express / Sequelize / MySQL projects.

The engine is generic: users have roles, roles grant permissions over resources. It owns no database connection, no credentials, and no host paths — everything it needs is injected by the host through a single factory. That is what makes the same code run unchanged across projects.

Scope: backend / enforcement only. Each host builds its own admin UI. Database: MySQL-specific (ON DUPLICATE KEY, UUID(), information_schema).

Mental model

There are two moments in the engine's life:

• At startup (once): the host calls createRbac(config) to build the engine, then calls syncSchema() to prepare the database.
• Per request: the middleware computes the user's effective permissions onto the request, and controllers ask can(req, resource, permission).

Usage

const { createRbac } = require('./RolesPermissions'); // or the published package

const rbac = createRbac({
  sequelize, // an already-initialised Sequelize instance (host-owned)
  dbName: 'my_schema',
  logger, // optional, defaults to console
  resourceCatalog: require('./config/application_resources.json'),
  defaultRoles: require('./config/default_roles.json'),
  resolveIdentity: (req) => ({
    userKey: req.session?.currentUser?.vvUserId, // the VV user PK — see below
    tenantKey: req.session?.customerId,
  }),
});

// Startup — seed permissions, upsert the resource catalog, provision default
// roles (the tables themselves come from the module's migrations — see below):
await rbac.syncSchema();

// Express — load permissions onto the request on protected routes:
app.use('/', requireAuth, rbac.middleware, router);

// Controllers — enforce:
if (!rbac.can(req, 'Units', 'edit')) {
  return res.status(403).json({ error: 'Forbidden' });
}

The one thing you must get right: userKey

resolveIdentity must return the VisualVault user primary key — the immutable id. Not a login id, and not an email. Both are editable in VV's user admin screen, and keying enforcement on either is a live defect:

1. Someone corrects a typo in a user's login id or email in VV.
2. The user logs in fine — VV is the authentication authority, so the new credential resolves.
3. The app looks them up by the value it stored, finds nothing, and grants no permissions at all.

No error, no log line. It fails worst on administrator accounts, and the affected person cannot fix it themselves. Anchoring on the immutable PK is what makes it impossible, and it is why the engine reads no host table on the enforcement path.

resolveIdentity: (req) => ({
  userKey: req.session?.currentUser?.vvUserId, // the VV user PK — never the login id
  tenantKey: req.session?.customerId,
});

The seven users-table options are gone in 1.0 — usersTable, userKeyColumn, tenantColumn, roleIdsColumn, userIdColumn, assignedUserColumns, assignedUserSortColumns. They existed to adapt the engine to each project's users table; it no longer reads one. Passing any of them now logs a warning at startup rather than being ignored silently — see Upgrading to 1.0.

createRbac(config) — configuration

| Option | Required | Default | Description | | ------------------------ | :------: | --------------------------------------- | ------------------------------------------------------------------------- | | sequelize | ✅ | — | Initialised Sequelize instance. The engine borrows it; never creates one. | | dbName | ✅ | — | MySQL schema name used to qualify tables (`${dbName}.Resources`). | | resolveIdentity | ✅ | — | (req) => ({ userKey, tenantKey }). How the host identifies the caller. | | logger | — | console | Object with info / warn / error. | | cache | — | null | Redis-like client (get / set / unlink). null = DB-only. | | cacheTtlSeconds | — | 900 | TTL for cached permission maps. | | skipDefaultRoles | — | false | Skip seeding default roles during syncSchema(). | | resourceCatalog | — | [] | Resource catalog (see shape below). Source of truth for Resources. | | defaultRoles | — | [] | Default roles (see shape below). | | devBypass | — | false | When identity is unresolved, grant everything (local/dev only). | | appResourcePermissions | — | View,Create,Edit,Delete,Export,Upload | Granular permissions for APP_RESOURCE rows. | | systemRoleType | — | 'System Role' | Roles.type value marking a built-in role (not updatable/deletable, sorts first). | | userRoleType | — | 'User Role' | Roles.type value stamped on roles created via repositories.roles.create(). | | writePermissionsToSession | — | true | Also mirror the permission map into req.session.userPermissions. See Where the permission map lives. |

Returns

| Function | When | Purpose | | -------------------------------- | ----------- | ------------------------------------------------------------------------------------------------------------------------------------------- | | middleware | per request | Express middleware; loads effective permissions onto the request. | | can(req, resource, perm) | per request | true/false check (honours dev bypass). | | canByRole(req, resource, perm) | per request | Strict check (ignores dev bypass). | | invalidateCache(users) | on change | Drops cached permissions for the given users. | | computeRolePermissions(uk,tk) | — | Builds the raw permission map (used internally / diagnostics). | | upsertRolePermissions(...) | on save | Persists a role's module / resource / capability grants (see Saving a role's grants). | | syncSchema() | startup | Seeds permissions, upserts the resource catalog, provisions default roles (idempotent). Tables come from the module's migrations, not here. | | syncDefaultRolesForCustomer() | on demand | Provisions default roles for one tenant. | | models | — | Built Sequelize models { Role, Permission, RolePermission, Resource, UserRole }. | | repositories | — | Data-access layer { roles, resourcePermissions, permissionsTest, userRoles } (see Data layer below). |

Where the permission map lives

The middleware always publishes the computed map to two places:

• req.userPermissions — the canonical location, read by can() / canByRole().
• res.locals.userPermissions — for views/templates.

By default it also mirrors the map into req.session.userPermissions, which is where it used to live exclusively. That third write is what writePermissionsToSession controls.

Why you may want it off

With express-session and resave: false, assigning to req.session marks the session dirty, so every request pays one write to the session store (Redis in production, a file on disk in dev). When the engine runs without a cache — the recommended setup, see below — that copy is never read back: the next request recomputes the map and overwrites it before any can() call runs. It is a write-only value, paid on all traffic.

const rbac = createRbac({
  /* … */
  writePermissionsToSession: false, // drops one session-store write per request
});

When it is safe to turn off

Only when nothing outside the engine reads req.session.userPermissions directly. can() and canByRole() are unaffected — they read req.userPermissions first and fall back to the session — but host code that touches the session copy by hand will start seeing undefined.

Before flipping it, grep the host:

grep -rn "session.userPermissions" --include=*.js . | grep -v node_modules

Zero hits → safe. Any hits → either leave the default, or migrate those call sites first. The migration is mechanical and works with the option either way, so it can land before the flip:

- const perms = req.session.userPermissions;
+ const perms = req.userPermissions;

Better still, replace direct reads with the public checks — rbac.can(req, resource, permission) — which stay correct regardless of how the map is published.

Default is true on purpose. Existing consumers read the session copy directly, so changing the default would break them silently. Opting out is a per-host decision, made after the grep above comes back clean.

Caching

The engine still supports a cache, but per the architecture decision of 2026-08-10 the verticals resolve permissions per request, with no cache, and scale read load with RDS read replicas instead. Caching remains available for hosts that need it; it is no longer the recommended path.

cache and cacheTtlSeconds only work as a pair. Every cache branch is guarded by if (cache), so a TTL without a client does nothing — the engine reads from the database on every request. Since that is easy to not notice, createRbac warns about it at startup:

createRbac: `cacheTtlSeconds` was provided but no `cache` client — permissions will
be read from the database on every request. …

It is a warning, never a throw. To silence it, either pass a cache client or drop cacheTtlSeconds so the per-request behaviour is explicit in the config.

Host responsibilities

The engine is portable because the host owns a few things it must supply:

1. The Sequelize connection. The engine never opens one — pass an initialised instance. Credentials and pooling stay entirely host-side.
2. Identity. resolveIdentity(req) must return the VisualVault user PK as userKey. That is the whole contract — a function signature, not a schema. The engine no longer requires anything of the host's users table, and does not read it.
3. The user sync. Keeping the host's profile table in step with VisualVault is the host's job: it needs VV credentials, and every project's users table holds different columns. It writes profile data only — never UserRoles. That separation is the point: the sync may insert, update, delete or recreate a profile row freely and access is unaffected.
4. The catalogs. resourceCatalog and defaultRoles are DATA, specific to each project. The engine's logic is fixed; the catalogs are what vary.
5. Running the module's migrations and then syncSchema() at startup (see Schema & migrations below).
6. Cascading its own user deletes. Removing a user from the app must call repositories.userRoles.removeAllFor(...) — see Deleting a user.

Schema & migrations

The engine ships the base structure of its five tables as Umzug migrations in migrations/, plus any later change to them (each guarded against information_schema so it is idempotent). The host runs them by pointing its own Umzug runner at that folder (sharing its SequelizeMeta), so they run alongside the app's migrations:

// Umzug v3 `glob` takes ONE pattern (optionally with a [pattern, options] tuple),
// so use a brace pattern to cover both the host's and the module's migrations:
const umzug = new Umzug({
  migrations: {
    glob: [
      '{database/migrations,node_modules/@visualvault/vv-vertical-roles-permissions/migrations}/*.js',
      { cwd: process.cwd() },
    ],
  },
  context: sequelize.getQueryInterface(),
  storage: new SequelizeStorage({ sequelize }),
});

Change control lives here: because the schema is defined as migrations, you get history, ordering and rollback via SequelizeMeta — the same control you have for any migration.

| Migration | What it does | | --- | --- | | 20250101000000-create-rbac-schema | the four original tables and their indexes | | 20250102000000-add-roles-is-protected | adds Roles.is_protected | | 20250103000000-drop-role-permissions-allowed | drops RolePermissions.allowed — see How grants are stored before running it | | 20250104000000-create-user-roles | adds UserRoles — the user↔role edge, moved out of the host's users.role_ids. See Upgrading to 1.0 |

Adopting on a database that already has the tables: if a host created these tables before wiring the module in, pre-seed the module migration's name into SequelizeMeta before the next startup, so Umzug treats it as already-applied and does not try to recreate them:

INSERT INTO `<schema>`.`SequelizeMeta` (name)
VALUES ('20250101000000-create-rbac-schema.js');

What belongs where:

• The module owns the base structure (creating the five tables). Its migrations are the single source of that baseline for every host.
• The host owns any change that is specific to its project — add those through the host's own migration system, not the module's. Schema tweaks are a per-project concern; the module only guarantees the base structure.

Models

The engine's five tables are also shipped as standalone Sequelize model definitions (models/). The engine itself runs raw SQL and does not need them — they exist for hosts that build a data/admin layer on top (e.g. R&P repositories). Build them once against your sequelize instance:

const { buildModels } = require('@visualvault/vv-vertical-roles-permissions');
const { Role, Permission, RolePermission, Resource, UserRole } = buildModels(sequelize);

Call buildModels once per sequelize instance (defining a model name twice on the same instance errors) — typically from your own model registry. The models are standalone (no associations) and carry no host dependencies.

The assignment model is UserRole, not User. Every host already defines its own User, and buildModels resolves by name — so an engine model called User would silently bind to the host's, against the host's columns, with the winner decided by require order.

Data layer (repositories)

createRbac(...) also returns a repositories object — the R&P data-access layer, so a host builds only its HTTP/UI on top:

const { roles, resourcePermissions, permissionsTest, userRoles } = rbac.repositories;

await roles.create(tenantKey, { name: 'Inspector', description: '…' });
await resourcePermissions.setRolePermissions(resourceId, roleId, tenantKey, ['view', 'edit']);
await userRoles.setRoles(vvUserId, tenantKey, [roleId]);
const effective = await permissionsTest.getEffectivePermissions(vvUserId, tenantKey);

• roles — role CRUD, duplicate, module access, per-role resource permissions, per-role admin capabilities.
• resourcePermissions — resource-first view, assignable roles, set/remove a role's permissions on a resource.
• permissionsTest — a user's effective permissions (additive union across roles).
• userRoles — who holds which role. See below.

Assigning roles (repositories.userRoles)

Before 1.0 the engine read assignments — out of the host's users.role_ids — but never wrote them. Every host hand-rolled the write against its own table, so the engine owned the read contract and nobody owned consistency. That is exactly how a user sync keyed on a mutable login id could quietly destroy grants.

Every method takes the VV user PK, and none of them touches a host table.

| Method | Purpose | | --- | --- | | getRoles(vvUserId, tenantKey) | the user's roles, as full rows (id, name, description, type) | | setRoles(vvUserId, tenantKey, roleIds) | replace the set. [] revokes everything | | assign(vvUserId, tenantKey, roleIds) | add without clearing. Idempotent | | unassign(vvUserId, tenantKey, roleId) | remove one. false when it was not there | | removeAllFor(vvUserIds, tenantKey, [transaction]) | revoke everything for one or many users | | getAssignedUserIds(roleId, tenantKey) | the identities holding a role | | getHoldersOfRoleType(tenantKey, [roleType], [excludeVvUserIds]) | backs a last-administrator guard |

setRoles and assign resolve the submitted role ids against the tenant first, so a caller cannot assign another customer's role by guessing an id. Unknown ids are not dropped silently — they come back in rejected:

const { assigned, rejected } = await userRoles.setRoles(vvUserId, tenantKey, roleIds);
if (rejected.length) {
  return res.status(400).json({ error: 'Unknown role', rejected });
}

A missing vvUserId or tenantKey throws with code = 'IDENTITY_REQUIRED' rather than reaching SQL — on the delete paths an undefined identity would build a WHERE matching more than one user.

Deleting a user must cascade

The assignment no longer lives on the host's users row, so deleting that row leaves the grants behind — and since the engine resolves permissions without reading users at all, a re-synced user would come back with full access.

Removing a user from the app and having the sync recreate their profile row are two different intents, and only the first revokes. So the first has to say so:

await sequelize.transaction(async (transaction) => {
  await rbac.repositories.userRoles.removeAllFor(vvUserIds, tenantKey, transaction);
  await MyUser.destroy({ where: { vv_user_id: vvUserIds, customerDatabaseId: tenantKey }, transaction });
});

Pass the transaction so both commit or roll back together. Half-applied, the user is gone from the app while their grants survive — and the next sync restores them.

Invalidating the cache

Assignment writes invalidate themselves. setRoles, assign, unassign and removeAllFor drop the affected users' cached permission maps after the write commits — the engine owns the write, so it owns the drop. Before 1.0 that was the host's job, and coherently so: the host wrote users.role_ids itself.

Two cases are still yours:

1. Changing what a role grants. setResourcePermissions, setModuleAccess and upsertRolePermissions change the effective permissions of every holder, and the engine does not fan out to them for you:

const ids = await rbac.repositories.userRoles.getAssignedUserIds(roleId, tenantKey);
await rbac.invalidateCache(ids.map((userKey) => ({ userKey, tenantKey })));

getAssignedUserIds returns exactly the vocabulary invalidateCache speaks, so the two chain directly. (roles.delete() is the exception — it revokes the role from its holders, so it reads them and invalidates on its own.)

2. removeAllFor with your own transaction. Given a transaction, the commit is yours, so there is no moment inside the call at which the revocation is durable — dropping then would open a window for a concurrent request to repopulate the cache from rows about to disappear, and you may still roll back. Invalidate after committing:

await sequelize.transaction(async (transaction) => {
  await rbac.repositories.userRoles.removeAllFor(vvUserIds, tenantKey, transaction);
  await MyUser.destroy({ where: { vv_user_id: vvUserIds }, transaction });
});
await rbac.invalidateCache(vvUserIds.map((userKey) => ({ userKey, tenantKey })));

A failing cache drop never fails the write: the row change is durable either way, and the TTL bounds the stale read. It is logged as a warning.

All of this is a no-op without a cache client — the recommended setup, where permissions are resolved per request from the database.

It returns identities only — no names, no emails. Display names live in the host's profile table together with its own fallback rules (one project concatenates firstName + lastName, another has a single name column falling back to email), so the host resolves them:

const ids = await userRoles.getAssignedUserIds(roleId, tenantKey);
const members = await MyUser.findAll({ where: { vv_user_id: ids, customerDatabaseId: tenantKey } });

This replaces roles.getAssignedUsers(), which projected display columns off the host users table — the one thing the engine no longer reads. The two options that configured that projection are gone with it.

Role type vocabulary

The engine recognises two kinds of role through the Roles.type column, and both strings are configurable because projects name them differently:

const rbac = createRbac({
  /* … */
  systemRoleType: 'System Role', // default
  userRoleType: 'User Created', // e.g. a host that does not say 'User Role'
});

• systemRoleType — built-in roles. update() and delete() refuse them, and every role listing sorts them first.
• userRoleType — stamped on roles created through repositories.roles.create().

The defaults are the values the engine has always written, so a host that sets neither is unaffected. These options only change what the engine writes and compares — they do not rewrite rows already in the database, so a host adopting a different vocabulary has to migrate existing Roles.type values itself.

Protected roles (Roles.is_protected)

roles.delete() refuses two kinds of role:

| Role | Result | Host should answer | | --------------------- | -------------------------------------------- | ------------------ | | type = 'System Role' | returns false | 400 / 404 | | is_protected = 1 | throws Error with code = 'PROTECTED_ROLE' | 403 | | anything else | returns true (deleted) or false (missing) | 200 / 404 |

The two differ on purpose: false is indistinguishable from "no such role", so a deliberate refusal is raised as a typed error the host can map to 403.

A successful delete cascades. The role's RolePermissions and UserRoles rows are deleted in the same transaction. The engine's tables carry no foreign keys — hosts own their schema, and storage engines and collations vary — so the cascade is enforced in code, not by the database. A refused delete (System Role, protected, or missing) touches nothing.

The UserRoles half is new in 1.0, and it closes a gap that used to be the host's problem: assignments lived in users.role_ids, so deleting a role left its id in every holder's array. The engine owns the assignment now, so it cleans up after itself — a deleted role cannot leave a dangling assignment.

Refusing to delete a role that is still in use is still a host decision. Ask userRoles.getAssignedUserIds(roleId, tenantKey) first and refuse if it comes back non-empty.

try {
  const deleted = await roles.delete(roleId, tenantKey);
  return deleted ? res.sendStatus(204) : res.sendStatus(404);
} catch (err) {
  if (err.code === 'PROTECTED_ROLE') {
    return res.status(403).json({ error: 'This role cannot be deleted' });
  }
  throw err;
}

Where the flag comes from. is_protected TINYINT(1) NOT NULL DEFAULT 0 is added by the module's migration 20250102000000-add-roles-is-protected.js, guarded against information_schema so it is a no-op on a host that already added the column itself. Adding it changes nothing on its own — every existing row defaults to 0. A host opts in by marking the roles it wants protected:

UPDATE `Roles` SET is_protected = 1 WHERE name IN ('Administrator', 'Read Only');

Marking is the host's job: which out-of-box roles are untouchable is a per-project decision, so the engine enforces the flag but never sets it. duplicate() copies a role's permissions but not its protection — a copy is always deletable.

Before the migration runs. delete() probes once per repository instance for the column and, if it is missing, falls back to the System-Role-only guard rather than failing on an unknown column. So upgrading the package without running migrations is safe; you just do not get the protection until you do.

How grants are stored

A RolePermissions row IS the grant. There is no flag on it. Every write path — upsertRolePermissions, roles.setModuleAccess, roles.setResourcePermissions, resourcePermissions.setRolePermissions — deletes the role's rows for what it is writing and re-inserts only what was granted. Revoking removes the row, because removing it is the only way to say "not granted".

That follows from the permission model itself: it is additive with no deny — a user's permissions are the union of their roles, computed at read time. Whichever role grants a permission wins, and nothing can take it away. So a row saying "not granted" could never have carried information: no reader could have acted on it. Every read asks the same question — MAX(rp.id IS NOT NULL) across the user's roles, or rp.id IS NOT NULL for a single role.

RolePermissions.allowed was dropped in 20250103000000-drop-role-permissions-allowed.js. It encoded a distinction the model cannot express. The migration deletes the pre-existing allowed = 0 rows before dropping the column, in that order and in one step — those rows read as "not granted" only because the queries filtered them out, so leaving any behind while removing the filters would silently turn them into grants.

A host that reads the column in its own SQL must stop before running this migration. Nothing that goes through the engine's repositories is affected: the read contracts are unchanged, down to the field names and their 0 / 1 values.

Disabling a module cascades (BR-7)

Turning a module off revokes everything under it, whatever the client submitted for those sections. "Under it" is matched by Resources.section_name = the module's name, and covers both:

| section_type | In the cascade | Why | | ----------------------- | -------------- | -------------------------------------------- | | 3 — APP_RESOURCE | yes | the module's resources | | 2 — ADMIN_CAPABILITY | yes, if scoped | a capability whose section_name is a module | | 2 — ADMIN_CAPABILITY | no, if global | its section_name is the global bucket, never a module name | | 1 — MODULE | no | the module row itself is written directly |

A scoped capability is one that hangs off a module (e.g. Configuration under Settings); a global one sits in the catalog's global bucket and belongs to no module, so no module name can ever select it. The cascade lives in one place — moduleAccess.js — and both write paths call it, so the two cannot drift apart.

The cascade shares a transaction with the write that triggered it, so turning a module off is all-or-nothing. This matters because can() is a flat lookup that never consults the module: BR-7 holds only because the cascade ran. Applied halfway, a module would read as off while its resources stayed granted — it would fail open. roles.setResourcePermissions and resourcePermissions.setRolePermissions are transactional for the same reason, though those fail closed.

upsertRolePermissions is the exception, by design: the host passes it a connection that is already inside a transaction, so atomicity is the caller's.

Saving a role's grants (upsertRolePermissions)

await rbac.upsertRolePermissions(conn, roleId, tenantKey, {
  capabilities: { 'Employee Access Management': true, 'Role Management': false },
  modules: [{ resourceId, allowed: true }],
  permissions: [{ resourceName: 'Units', permissionName: 'View', allowed: true }],
});

All three keys are optional, and omitting one leaves that kind of grant untouched:

| capabilities | Effect | | ----------------------------- | -------------------------------------------------------- | | omitted | capabilities are not read or written at all | | {} | every capability is cleared | | { 'A': true, 'B': false } | A granted, B cleared, and every other one cleared too |

Listing capabilities is a full replace, not a patch — the ones you leave out are cleared. Omitting the key entirely is how you say "I am not editing capabilities", which matters for a screen that only saves modules.

"Cleared" means the row is deleted, per How grants are stored — the same for modules and permissions. modules also triggers the cascade: any entry with allowed: false revokes that module's resources and scoped capabilities.

Deprecated: the positional form

// Deprecated — still works exactly as before, unchanged.
await rbac.upsertRolePermissions(conn, roleId, tenantKey, req.body, modules, permissions);

It is deprecated for two reasons, both silent:

1. It reads capability flags out of req.body by resource name and compares against the string 'true'. A caller sending a real boolean (true), a number (1), or 'on' has that capability revoked instead of granted.
2. It always writes every capability, so a request body without capability fields clears all of them — even when the screen was only saving modules.

Migrating is a local change: build the map yourself, so your form field names no longer have to match your resource names.

- await upsertRolePermissions(conn, roleId, tenantKey, req.body, modules, permissions);
+ await upsertRolePermissions(conn, roleId, tenantKey, {
+   capabilities: {
+     'Employee Access Management': req.body.eam_checkbox === 'true',
+   },
+   modules,
+   permissions,
+ });

Both forms are recognised automatically: a 5th or 6th argument means the positional form, and otherwise a 4th argument carrying capabilities, modules or permissions means the object form.

Reading a role's grants, by section_type

One method per resource kind, all (roleId, tenantKey) and all returning the full catalog for that kind — including rows the role has no grant for — so a UI can render the complete tab without a second query:

| section_type | Method | Per-row grant field | | ------------------------- | -------------------------- | ------------------------------------------ | | 1 — MODULE | getModuleAccess | granted (0 / 1) | | 2 — ADMIN_CAPABILITY | getCapabilities | allowed (0 / 1) | | 3 — APP_RESOURCE | getResourcePermissions | permissions (granted names) + applicable_permissions |

const capabilities = await roles.getCapabilities(roleId, tenantKey);
// [{ id, name, description, section_name, allowed }, …]

getCapabilities resolves allowed from the Allow permission for that role, defaulting to 0 where no RolePermissions row exists. Writes go through upsertRolePermissions, which persists capability flags with the same Allow permission.

All three return the same field names and the same 0 / 1 values they always have. granted and allowed are now computed from whether the RolePermissions row exists (rp.id IS NOT NULL over the LEFT JOIN these queries already used) rather than read off a column — the same answer, from the only thing that ever meant anything. See How grants are stored.

The repositories use the models for plain CRUD and raw SQL for analytical/pivot reads. No repository reads the host users table — a user's roles come from UserRoles, keyed on the VV user PK. User management itself (creating/syncing users, and the profile columns each project happens to store) is not part of the engine: it stays with the host, and the engine is unaffected by what it does there.

Upgrading to 1.0

1.0 moves the user↔role edge out of the host's users.role_ids and into the engine's own UserRoles, anchored on the VisualVault user PK.

Why

The assignment used to live on a host row that the user sync owned. A sync matching on a mutable key — a login id, an email — could not tell "this person's login id changed" from "this is a new person", so it inserted a fresh row, and the new row carried no roles. The user kept logging in and had no permissions. Nothing errored.

Anchoring on the immutable PK makes it structurally impossible: the sync may update, delete or recreate a profile row freely, and access is unaffected.

Breaking changes

| Change | What to do | | --- | --- | | Seven users-table options removed | Delete them from createRbac. Startup warns if any remain | | resolveIdentity().userKey must be the VV user PK | Put the PK in the session at login and return it here | | roles.getAssignedUsers() removed | userRoles.getAssignedUserIds(), then resolve names in the host | | permissionsTest.getEffectivePermissions(vvUserId, …) | First argument is the VV user PK, and it no longer returns null for an unknown user — decide 404 from your own lookup | | Assignment writes | Use repositories.userRoles. Stop writing users.role_ids | | Deleting a user | Must call userRoles.removeAllFor(...), or a re-synced user returns with full access | | roles.delete() now also clears assignments | Nothing to do — this closes a gap |

Step 0: find out whether you already store the VV user PK

Check before writing any migration. VisualVault's user PK is UsID on [dbo].[Users], a GUID. Some hosts already store it and some do not, and the two observed cases sit at opposite ends:

| Host | Where the VV PK is | What it needs | | --- | --- | --- | | Retention Manager | users.id — its own primary key. The sync writes CAST(UsID AS NVARCHAR(36)) there | nothing; point resolveIdentity at it | | Licensing | nowhere. users.userDatabaseId is a locally generated uuidv4() | a new column, populated from the VV payload |

So trace your own sync before assuming either. Licensing is the instructive case: the UsID does arrive — the VV REST API returns it and the controller maps it — and the insert discards it in favour of a fresh UUID. The value being absent from your table does not mean it is absent from your code.

If you do need a column, do not repurpose an existing primary key that holds locally generated ids. Existing rows keep their old values, and you end up with two meanings in one column — worse than adding one.

Order of operations

Steps are ordered: the backfill cannot run until the PK is present on every row.

1. Make the VV PK available — a new column with UNIQUE (customerDatabaseId, <col>) if you do not have one. Skip entirely if you already store it.
2. Host sync — select VisualVault's user PK, diff on it instead of the login id, and UPSERT so a rename updates the row in place. Run it once to populate.
3. Verify — every row must carry a real UsID. Rows created outside the sync (by hand in dev, or by a pre-adoption code path) may hold a random UUID that looks just as valid, and they are the rows that lose access when enforcement moves.
4. Backfill — copy users.role_ids into UserRoles.
5. Wire and drop — point resolveIdentity at the PK, move assignment writes to repositories.userRoles, then drop role_ids.

The engine ships no backfill: it would need to know your table's name and columns, which is precisely the coupling this release removes. Substitute your own column for vv_user_id below — for a host like RM that is u.id, and the IS NOT NULL guard is unnecessary because it is the primary key:

INSERT IGNORE INTO UserRoles (id, customerDatabaseId, vv_user_id, role_id, created_at)
SELECT UUID(), u.customerDatabaseId, u.vv_user_id, jt.role_id, NOW()
FROM users u
JOIN JSON_TABLE(
  COALESCE(NULLIF(u.role_ids, ''), '[]'), '$[*]' COLUMNS (role_id VARCHAR(36) PATH '$')
) AS jt
JOIN Roles r ON r.id = jt.role_id AND r.customerDatabaseId = u.customerDatabaseId
WHERE u.vv_user_id IS NOT NULL;

The Roles join drops ids that no longer exist — assignments to deleted roles, which users.role_ids had no way to clean up. Check what it skips before running:

SELECT u.vv_user_id, jt.role_id FROM users u
JOIN JSON_TABLE(COALESCE(NULLIF(u.role_ids, ''), '[]'), '$[*]'
  COLUMNS (role_id VARCHAR(36) PATH '$')) AS jt
LEFT JOIN Roles r ON r.id = jt.role_id AND r.customerDatabaseId = u.customerDatabaseId
WHERE r.id IS NULL;

A pre-customer project can skip the backfill entirely — re-sync and re-assign by hand. It is only worth writing where there is real assignment data to preserve.

Resource catalog shape (resourceCatalog)

One entry per protected feature. id is a stable UUID assigned once and never changed after shipping.

{
  "id": "33fe0891-caf1-4ed1-bf32-fcf49edd1c28",
  "section_type": 1,
  "section_name": "Inspections",
  "name": "Inspections",
  "description": "...",
  "permissions": ["view"]
}

section_type:

• 1 — MODULE — gated by a single Granted permission (nav-level access).
• 2 — ADMIN_CAPABILITY — gated by a single Allow permission.
• 3 — APP_RESOURCE — gated by the granular CRUD set in permissions.

Default roles shape (defaultRoles)

{
  "name": "Inspector",
  "description": "...",
  "type": "Staff",
  "defaultPermissions": {
    "modules": "*",
    "resourcePermissions": [{ "name": "Regulations", "permissions": ["view", "edit"] }],
    "adminCapabilities": []
  }
}

Each defaultPermissions key accepts "*" (all), a string array (named subset), or [] (none).

Effective permissions

A user's permissions are the additive union across all their roles: any role granting a permission is enough (MAX(rp.id IS NOT NULL) in SQL). Removing a permission from one role does not deny it if another role still grants it — there is no deny, which is why the grant needs no flag. See How grants are stored.

Testing

Uses Node's built-in test runner (node:test) — no runtime dependencies.

npm test                 # unit tests (no database needed)

Unit tests cover the enforcement engine with a mocked sequelize and the createRbac contract. Integration tests exercise the repositories against a real MySQL database and are skipped unless a test DB is configured:

RBAC_TEST_DB_NAME=your_test_db \
RBAC_TEST_DB_HOST=127.0.0.1 \
RBAC_TEST_DB_USER=root \
RBAC_TEST_DB_PASSWORD=secret \
npm run test:integration

Integration tests scope all data to a dedicated test tenant (customerDatabaseId) and clean it up afterwards, so a shared local/QA database's real data is untouched. They require the mysql2 driver (a devDependency).