Enforce invitation identity at the database level with role-less unique indexes

[mroderick]

Summary

PR #2867 enforces one invitation per member per event/workshop in application code (InvitationManager). The database still encodes the old identity, so nothing prevents a second row — and any future code path (like the one fixed in the review commit for WorkshopsController#find_or_create_invitation) can reintroduce duplicates.

Current state in db/schema.rb:

• invitations: unique index on (member_id, event_id, role) — role-inclusive, so it permits one row per role
• workshop_invitations: unique index on (member_id, workshop_id, role) — same problem
• Invitation and WorkshopInvitation model validations: uniqueness: { scope: [..., :role] }

Proposal

1. Drop the role-inclusive unique indexes
2. Add role-less unique indexes: (member_id, event_id) on invitations, (member_id, workshop_id) on workshop_invitations
3. Update the model uniqueness validations to match

These indexes also make create_or_find_by race-safe.

Prerequisites

• Historical duplicate rows must be deduped first (companion issue) or index creation fails
• The "which role survives" decision (companion issue) determines what the dedupe keeps

Related

• PR Add unique token indexes to invitations and meeting_invitations #2892 adds unique token indexes to invitations and meeting_invitations (different column, same tables) — coordinate migration ordering to avoid conflicting algorithm: :concurrently runs