Data generated from the users in cases needed for filtering in T433840: Implement case filtering on "edits on shared pages" for SuggestedInvestigations and subsequent similar filters will need to be persistently stored and queried against.
This table needs to store:
- the case id this property belongs do
- a key-value pair denoting a property being tracked (eg. "number of edits on shared pages")
- key is an int, correlating to a string key defined in PHP
- value is float to account for the possibility of decimal values in the future. All properties we're interested in so far are numeric.
- a tinyint flag that denotes whether or not the value is considered stale
The primary key is a combination of the case id and the property id and no index should be needed as it should only ever be queried on primary key columns.
Should this table be replicated to wiki replicas (does it not contain private data)?
Probably not but this data itself is not private. Suggested Investigations is a private feature but the data we collect here is not derived from private values. It probably will not need to be in the near future, if ever.
Will you be doing cross-joins with the wiki metadata?
No.
Size of the table (number of rows expected).
As all cases are expected to have all properties, cusi_case * (property count + 1 stale indicator). As of writing, this works out to up to low 100k rows but is expected to grow as cases are generated.
Expected growth per year (number of rows).
It grows in direct proportion with cusi_case
Expected amount of queries, both writes and reads (per minute, per hour...per day, any of those are ok).
We're still workshopping this. The most ideal case would be to have the most up to date data, refreshing stale data as users take actions. There aren't many of them, proportional to the number of users/edits, but we'd like to instrument and step down fidelity as necessary based on what we see.
Examples of queries that will be using the table.
// Pull properties for display SELECT * FROM cusi_case_property WHERE sicp_sic_id IN (1,2, 3); // Update stale property UPDATE cusi_case_property SET sicp_stale = 0, sicp_value = 2 WHERE sicp_sic_id = 1 AND sicp_property = 1;
The release plan for the feature (are there specific wikis you'd like to test first etc).
Suggested Investigations is live on enwiki and we'd want to implement there but we can start by only adding properties onto new cases with activity.