wip: the PR is currently in draft mode to discuss the migration of the org-member role to the DB

Migrating Org-Member Role to Database

Problem

Theorganization-member role is hardcoded. This makes it easy to keep it in sync with resource types supported by Coder, but prevents per-org customization (e.g.,workspace_sharing_disabled).

Solution: Database-Backed System Roles

Storeorganization-member roles per-org in thecustom_roles table with anis_system flag. Create the roles during org creation and reconcile them at startup to keep permissions in sync with the codebase.

1. Schema Changes

• Addedis_system boolean column to thecustom_roles table — marks Coder-managed roles
• Addedmember_permissions — for member-scoped permissions (resources owned by the user)
• Addedworkspace_sharing_disabled toorganizations — per-org setting

2. Migration

• Brings existing organizations up to date by creating empty placeholderorganization-member system roles for all existing organizations
• Permissions are empty initially (eventually backfilled by the startup reconciliation hook)

3. Permission Generation (coderd/rbac/roles.go)

• OrgMemberPermissions(workspaceSharingDisabled bool) is the source of truth
• Generates org-level and member-level permissions dynamically
• Respects the workspace sharing setting (adds negation forActionShare when disabled)

4. Org Creation Hook (enterprise/coderd/organizations.go)

• When a new org is created, a placeholderorganization-member role is created by a trigger in the DB.
• ReconcileOrgMemberRoles() is called to populate the role with permissions.

5. Startup Reconciliation (coderd/systemroles.go)

• ReconcileOrgMemberRoles() runs at startup with an advisory lock
• Compares expected vs stored permissions using set-based comparison
• Updates roles if permissions differ (due to the initial migration or RBAC resource changes)
• Blocking lock ensures each instance reconciles, catching orgs created by old instances

Key Design Decisions

closes:coder/internal#1073