Summary
AccountEmail and UserAvatar are ownership-style child records: they do not make sense without their parent Account/User, and the ORM already treats them as delete-orphan relationships. The database schema should enforce the same invariant with ON DELETE CASCADE foreign keys.
The app-level account deletion path should also handle organizations that would otherwise be orphaned when the deleted user is the last user in that organization.
Current behavior
Account deletion relies partly on DB-level cascades and partly on ORM-level cascades. In particular:
private.accountemail.account_id -> private.account.id is handled by SQLAlchemy delete-orphan behavior, but the DB foreign key does not cascade.useravatar.user_id -> user.id is also handled by SQLAlchemy delete-orphan behavior, but the DB foreign key does not cascade.- Deleting an account/user removes role memberships, but it does not explicitly delete organizations where that user was the only remaining user. That can leave an organization, roles, surveys, and org-level integrations with no users attached.
That means the app path works when deleting through session.delete(account), but direct SQL/admin operations can fail or require manual child-row cleanup. It also means account deletion can leave orphaned organization-owned resources unless the application handles the last-user case.
Suggested change
Add a migration that recreates these foreign keys with ON DELETE CASCADE:
private.accountemail(account_id) REFERENCES private.account(id) ON DELETE CASCADEuseravatar(user_id) REFERENCES "user"(id) ON DELETE CASCADE
The SQLModel declarations should also include matching ondelete="CASCADE" settings so the model and database remain aligned.
Update the app account-delete path so that, before deleting the account/user, it identifies organizations where the user is the only remaining user. Those organizations should be deleted through the ORM/session in the same transaction, allowing existing organization cascades to remove dependent roles, surveys, invitations, and org-level integrations.
Tests
Please add tests covering both sides of the organization behavior:
- When the deleted account's user is the only user in an organization, account deletion removes that organization and its organization-owned resources, such as surveys and org-level Qualtrics connections.
- When the organization still has another user, account deletion removes only the deleted user's account, user row, user-scoped memberships/resources, and leaves the organization, surveys, org-level integrations, and remaining users intact.
Why
These rows are dependent data, not independent business records. DB-level cascades would make account/user deletion consistent regardless of whether it is performed through the ORM, psql, a migration, or an admin recovery script.
Likewise, an organization with no users is usually not a useful retained business object in this template's ownership model. The app should clean up organization-owned resources when account deletion creates that last-user case, while preserving shared organizations whenever another user remains.
Summary
AccountEmailandUserAvatarare ownership-style child records: they do not make sense without their parentAccount/User, and the ORM already treats them as delete-orphan relationships. The database schema should enforce the same invariant withON DELETE CASCADEforeign keys.The app-level account deletion path should also handle organizations that would otherwise be orphaned when the deleted user is the last user in that organization.
Current behavior
Account deletion relies partly on DB-level cascades and partly on ORM-level cascades. In particular:
private.accountemail.account_id -> private.account.idis handled by SQLAlchemy delete-orphan behavior, but the DB foreign key does not cascade.useravatar.user_id -> user.idis also handled by SQLAlchemy delete-orphan behavior, but the DB foreign key does not cascade.That means the app path works when deleting through
session.delete(account), but direct SQL/admin operations can fail or require manual child-row cleanup. It also means account deletion can leave orphaned organization-owned resources unless the application handles the last-user case.Suggested change
Add a migration that recreates these foreign keys with
ON DELETE CASCADE:private.accountemail(account_id) REFERENCES private.account(id) ON DELETE CASCADEuseravatar(user_id) REFERENCES "user"(id) ON DELETE CASCADEThe SQLModel declarations should also include matching
ondelete="CASCADE"settings so the model and database remain aligned.Update the app account-delete path so that, before deleting the account/user, it identifies organizations where the user is the only remaining user. Those organizations should be deleted through the ORM/session in the same transaction, allowing existing organization cascades to remove dependent roles, surveys, invitations, and org-level integrations.
Tests
Please add tests covering both sides of the organization behavior:
Why
These rows are dependent data, not independent business records. DB-level cascades would make account/user deletion consistent regardless of whether it is performed through the ORM, psql, a migration, or an admin recovery script.
Likewise, an organization with no users is usually not a useful retained business object in this template's ownership model. The app should clean up organization-owned resources when account deletion creates that last-user case, while preserving shared organizations whenever another user remains.