Authorization state drift: missing SpiceDB cleanup on role/permission/resource/invitation changes, no one-role DB constraint, schema pollution

[whoAbhishekSah]

Several write paths don't fully reconcile the relational store (policies, relations, roles, permissions) with the SpiceDB datastore, so authorization data drifts over time: access that lingers after a delete, roles that grant more than they define, and stray permissions in the schema. PAT-specific gaps are tracked separately. Tracking here — I'll work on these.

1. Role deletion doesn't cascade to rolebindings / policies

core/role Delete removes the role's own permission relations (app/role:<id>#<perm>) but leaves the rolebindings that reference it (app/rolebinding:<id>#role@app/role:<id>) and the policies rows using it. A deleted role can keep granting access via surviving rolebindings, invisible/unremovable through the API.
Fix: on role delete, remove dependent policies (and their rolebinding tuples) first.

2. Role migration is add-only / doesn't prune

core/role Update correctly prunes removed permission relations, but the boot-time predefined-role migration in internal/bootstrap (a) rewrites predefined roles without removing dropped permission relations, and (b) never deletes roles removed from the schema. Result: narrowed roles still grant old permissions; removed roles persist.
Fix: during role migration, diff existing vs desired and delete removed permission relations and removed roles.

3. Invitations don't fully remove their SpiceDB relations

core/invitation creates two relations on create (invitation#user@user and invitation#org@organization) but Delete removes only the org relation — the user relation is never deleted, and accept/expire don't clean up either.
Fix: delete both relations on accept / expire / delete.

4. No database backstop for one role per (resource, principal)

Membership APIs enforce one role per principal per resource (replace-not-add), but the policies unique constraint includes role_id, so the generic policy-create path / direct writes can still create multiple roles for the same principal on the same resource.
Fix: tighten the policies unique constraint to (resource_id, resource_type, principal_id, principal_type) (after de-duplicating) and route writes through an upsert.

5. Ad-hoc permissions can pollute the compiled schema

internal/bootstrap builds the SpiceDB schema partly from the permissions table, so permissions created outside the intended resource definitions (e.g. via the create-permission API) get compiled into the schema and leave app/role relations/tuples not in the canonical base + generated schema. Nothing reconciles this.
Fix: reconcile the permissions table against the canonical resource definitions; drop permissions not in the compiled schema and prune relations no longer defined. (Resource instances are unaffected — only stray permission definitions.)

6. permission delete is a no-op

core/permission Delete removes only the DB row (with a comment acknowledging it doesn't clean relations), leaving every app/role:<id>#<perm> relation that referenced it — roles keep granting a permission that no longer exists.
Fix: remove the role↔permission relations on permission delete (or block delete while in use).

7. resource update doesn't reconcile #project / #owner

core/resource Update is DB-only. Changing a resource's project (transfer) or owner updates the row but not SpiceDB — the old resource#project@project:<old> / resource#owner@<old> stays and the new one isn't added, so the resource is linked to both projects / has stale owners.
Fix: reconcile #project and #owner relations on update (delete old, add new).

8. Decide & document disable-vs-revoke

Disabling an org / project / group / user sets state=disabled but leaves all SpiceDB tuples/policies; access is gated by the app's disabled-state check, not by SpiceDB. If any check reads SpiceDB directly, disabled entities still authorize.
Fix: decide whether disable should suspend tuples; document the chosen behavior and add a test.

Lower-priority / by-design notes

• Permission slug change doesn't update existing role↔permission relations.
• Namespace has no delete path; removing one from config would leave dangling relations.
• Platform admins removed from the admin config between boots aren't pruned.
• The raw relation API (CreateRelation) is admin-only and has no lifecycle owner by design.
• A consistency check between the relational store and SpiceDB (policies/relations/roles ↔ tuples) would catch this class of drift early — consider a maintenance command or a test-time invariant.

Each fix should ship with a test that performs the original action (delete a role, narrow/remove a predefined role, accept an invitation, attempt a duplicate policy, transfer a resource, boot with a stray permission) and asserts no orphaned tuples / duplicates remain.

Checklist

• 1. Cascade role delete → dependent rolebindings + policies
• 2. Role migration prunes removed perms + removed predefined roles
• 3. Invitation removes both relations on accept/expire/delete
• 4. One-role policies unique constraint + upsert writes
• 5. Reconcile permissions table ↔ compiled schema; prune undefined relations
• 6. permission delete removes role↔permission relations
• 7. resource update reconciles #project / #owner
• 8. Decide & document disable-vs-revoke semantics
• 9. (optional) relational-store ↔ SpiceDB consistency check