Summary

We want to persist information about a given user relating to the case and the signals in the cusi_user table, so that changes to this information elsewhere do not lead to a change in Suggested Investigations unless intended

Background

• We need a way to store information relating to each user in a CheckUser-SuggestedInvestigations case
  • This information may be different for each user
  • This information will have fixed allowable values
• We can represent this data using an integer field which we treat as a BIT field, using each bit as a bit flag which is parsed in PHP
  • This means that we can store information about the user efficiently as long as the information is representable using fixed values
    • For our use cases, we only for now need to store information about a state as opposed to the raw value
    • We don't expect the need to store raw data for other use cases, and a bit field should be acceptable for other use cases
• See T411118 (private task) for more detail on the need for this column

Proposed schema change

Addition of a siu_info field to cusi_user. This field will not need to be indexed or added to the primary key. On WMF wikis this table is on the extension1 cluster

describe cusi_user;
+-------------+------------------+------+-----+---------+-------+
| Field       | Type             | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+-------+
| siu_user_id | int(10) unsigned | NO   | PRI | NULL    |       |
| siu_sic_id  | int(10) unsigned | NO   | PRI | NULL    |       |
| siu_info    | int(10) unsigned | NO   |     | 0       |       |
+-------------+------------------+------+-----+---------+-------+

Acceptance criteria

• An unsigned integer field is added to cusi_user which is treated as field storing bit flags
• A Schema-change-in-production ticket is filed to apply this change to all wikis in checkuser-suggested-investigations.dblist - T417386