feat(organizations): add organization_project_id FK to team_project_roles - #20540

Refs #19748. This is phase 1 of 2.

What this does

Adds team_project_roles.organization_project_id, a FK to organization_projects.id with ON DELETE CASCADE, so the database can enforce the invariant that a team's project role cannot outlive the org-project association it depends on.

• TeamProjectRole gains the column, an index, and an organization_project relationship
• Migration adds the column, backfills it by joining teams.organization_id → organization_projects, then creates the index and FK
• add_team_project_role populates the column for new roles

Why the column is nullable

I originally planned NOT NULL, but the migration header in this repo rules it out for a single PR:

migrations will be ran automatically as part of the deployment process, but while the previous version of the code is still up and running

The previous version of the code inserts TeamProjectRole rows without this column, so a NOT NULL column with no default would break those inserts mid-deploy. Following the guidance to break backwards-incompatible changes across multiple PRs, this one only expands.

What is deliberately left for phase 2

• NOT NULL. Once every writer populates the column.
• Orphan cleanup. Rows whose (team.organization_id, project_id) has no matching organization_projects row are exactly the stale grants this issue describes. The backfill here leaves them NULL rather than deleting them, so this migration performs no destructive writes against production data. I'd rather surface the count and have you decide than delete rows unilaterally — happy to add a reporting step if useful.
• Removing the service-layer cleanup. The manual delete(TeamProjectRole) in delete_organization_project stays, since it is still the only thing covering rows where the column is NULL. The comment there now says so.
• OIDCPublisherProjectAssociation (item 4 in the issue). Publishers aren't inherently org-scoped, and bundling it would make this much harder to review. Happy to do it separately.

Testing

Three tests in tests/unit/organizations/test_services.py:

• add_team_project_role links the association when the project belongs to the team's organization
• add_team_project_role leaves the column NULL when there is no association (valid while nullable)
• deleting an OrganizationProject directly — bypassing the service-layer cleanup — cascades the role away, which is the actual invariant

I left TeamProjectRoleFactory alone on purpose. Having it auto-create an OrganizationProject looked tempting, but several existing tests build a team via TeamFactory.create() with no organization, so the team invents its own org distinct from the one the project was added to. The factory would then give one project two rows in organization_projects, and Project.organization is uselist=False over that table. Tests that need the link now pass it explicitly.

Notes

team_project_roles is organization-only, so the backfill UPDATE should be well inside the default 5s statement timeout, but flagging it in case the production row count says otherwise.

Opening as a draft while I finish running the suite locally — I'll mark it ready for review once it's green. Feedback on the phasing and on the orphan question above is welcome in the meantime.

🤖 Generated with Claude Code