docs: propose custom_role and principal_group schema for [ADR6 (BDMS-1557)] - #1047

Coverage

✅ 81.81% total — gate is 75%.

Review: docs/permission-bundle-and-group-membership-schema.md

custom_role_item copies permission_grant. Both tables have the same five permission columns: capability, scope_type, scope_id, data_type, field_group. Only the holder is different. In custom_role_item, the holder is a role. In permission_grant, the holder is a principal.

Problems

1. The proposal removes a safety constraint. db/permission_grant.py sets capability, scope_type and scope_id to NOT NULL. The code comment gives the reason: if scope_id has no value, the constraint fails and the row cannot give global access. A bundle-assignment row keeps these columns NULL. Thus, the columns must become nullable. After this change, only a CHECK constraint stops a partly filled row.
2. Two tables need the same validation. Each table needs the lexicon foreign keys, the "*" rule for global scope, and the data-type checks. The two sets of rules can become different over time.
3. The audit log does not record role edits. ADR5 section 3 says that the log must answer "who granted that, and when". A new custom_role_item row gives access to all members of each assigned group. But custom_role_item has no granted_by, granted_at, reason or end_date. In the worked example, permission_grant does not change. Thus, the audit log does not record the new access.
4. The permission check needs two code paths. One path reads direct grants. The other path joins custom_role_item. The engine must then merge the two results.

Is the bundle necessary?

A principal_group with grants is already a bundle. Put the grants directly on the group. When you add a grant to the group, all members get it on their next request. This is the same live-reference result that the document wants.

The worked example shows the problem. It has one group, "AMMP Viewer", and one role, "AMMP Viewer Base Permissions". Each has one function, so the role adds no value.

A separate bundle is useful only when two or more groups, users or API keys use the same set of permissions. The document does not give an example of this. The example in open question 8 shows one group with two bundles. You can also do this with direct grants on the group.

Recommendation

• Now: Remove custom_role and custom_role_item. Add principal_group and principal_group_member. Put grants in permission_grant with principal_type='principal_group'. Do not change the permission_grant constraints. Open questions 3 to 6 and 8 then become unnecessary.
• Later, if more than one principal must use the same bundle: Do not copy the permission columns. Use one of these two options:
  • Make custom_role a principal type. The role's permissions are then permission_grant rows with principal_type='custom_role'. Record each assignment in a small custom_role_assignment table: role, principal, start and end dates, granted_by, reason. Validation and audit stay in one location.
  • Let a group be a member of a different group. This needs no new concept, but you must prevent cycles.

With both options, one table holds all permission definitions. That table has one set of constraints and one audit path.

Small error

The custom_role_item table in the worked example has an id column. The schema table above it does not have this column.

Generated by Claude Code