As discussed in today's engineering meeting, we would like to simplify the ce_organizers table so that certain operations like limit, count, and pagination are easier.

Rough proposal

We currently store tuples of (event, user, role), so there can be multiple rows for the same event+user combination, one for each role. The new proposal is that we make the "roles" column a bit set, and store all roles in a single row.

The motivations for this are:

• Limit, count, and pagination would be much easier
• We don't need timestamps (created_at, deleted_at) for each role

AC

• Rename the column
• Remove it from the index
• Adjust logic in OrganizersStore

Deployment plan

• Write the patch that changes the schema in the code. Review it, but DO NOT MERGE IT. Only give it a +1, explicitly writing in a comment that the +1 means you are approving the change (and not that you have a working mouse).
• Merge the patch and wait for it to reach beta
• Immediately rename the column on beta

ALTERs to run on beta

Before merging the patch: nothing. Note that DB errors are expected in the short period between when the patch reaches beta and the queries below are run.

After merging the patch (noting that beta runs MariaDB 10.6, so we can use RENAME COLUMN):

ALTER TABLE ce_organizers DROP INDEX ceo_event_user_role;
ALTER TABLE ce_organizers RENAME COLUMN ceo_role_id TO ceo_roles;
ALTER TABLE ce_organizers ADD UNIQUE INDEX ceo_event_user (ceo_event_id, ceo_user_id);