RBAC mapping rows not cleaned up when resources are deleted

[Tim020]

Problem

The RBAC system uses dynamically-created mapping tables (e.g. rbac_user_cuetypes, rbac_user_shows, rbac_user_script) to store per-user, per-resource-instance permission grants. When a mapped resource is deleted, none of the delete handlers call rbac.delete_resource(), so the corresponding rows in these tables are never removed and accumulate as orphans.

This was a latent issue but became more prominent in #1154, which now auto-creates an rbac_user_cuetypes row on every cue type creation — meaning every cue type deletion leaves behind a guaranteed orphan.

Root cause

rbac_db.add_mapping() creates the RBAC tables with plain ForeignKey columns (no ondelete="CASCADE"):

# server/rbac/rbac_db.py ~line 130
resource_columns = {
    f"..._id": Column(col.type, ForeignKey(f"...{col.key}"), primary_key=True)
    for col in resource_inspect.primary_key
}

No ON DELETE CASCADE means SQLAlchemy/SQLite won't auto-remove the RBAC rows when the referenced resource is deleted. SQLite also does not enforce FK constraints by default (requires PRAGMA foreign_keys = ON), so the orphans accumulate silently rather than raising a constraint error.

Affected resources and locations

Three resource types have RBAC mappings (configured in server/digi_server/app_server.py:_configure_rbac()):

Resource	RBAC table	Delete handler	Missing call
CueType	rbac_user_cuetypes	CueTypesController.delete() — server/controllers/api/show/cues.py	rbac.delete_resource(entry)
Show	rbac_user_shows	ShowsController.delete() — server/controllers/api/show/shows.py	rbac.delete_resource(show)
Script	rbac_user_script	No independent delete endpoint; Script objects are cascade-deleted when a Show is deleted	rbac.delete_resource(script) for each script in the show

delete_actor() is correctly called when a User is deleted (server/controllers/api/auth/user.py:131), so the actor-side cleanup exists — only the resource-side is missing.

Suggested fix

Option A — call delete_resource in each handler (minimal, surgical):

1. CueTypesController.delete() — add before session.delete(entry):

   self.application.rbac.delete_resource(entry)

2. ShowsController.delete() — add before session.delete(show):

   for script in show.scripts:
       self.application.rbac.delete_resource(script)
   self.application.rbac.delete_resource(show)

3. No change needed for Script if handled via step 2 above.

Option B — add ON DELETE CASCADE to the RBAC FK columns (systemic fix):

Change add_mapping() to use ondelete="CASCADE" on the resource FK columns:

ForeignKey(f"{resource_inspect.persist_selectable.fullname}.{col.key}", ondelete="CASCADE")

This would require enabling PRAGMA foreign_keys = ON in the SQLite connection setup (or ensuring it's set in database.py), but would make cleanup automatic for all current and future resource types without touching individual delete handlers.

Option B is more robust long-term but is a bigger change. Option A is the safe, immediate fix.

Testing

• Add a test that creates a cue type, deletes it, and asserts the rbac_user_cuetypes row is gone.
• Add a test that creates a show with RBAC grants, deletes the show, and asserts rbac_user_shows rows are gone.