Summary
For the Suggested investigations feature we need to have database tables that store a list of accounts in investigations, their connection to each other, and what makes them suspicious. This task tracks creating the database tables for this feature.
Background
- We need to track the following things:
- Which accounts are in a suggested investigation
- Which accounts are related to other accounts
- What made the software suggest the investigation
- The database table needs to support the following operations
- Creating a suggested investigation based on some signal that matched to the accounts
- Seeing a list of suggested investigations
- This includes filtering out suggested investigations which have been reviewed
- Marking a suggested investigation as resolved or invalid, including giving a reason for this
- Adding additional users to an existing suggested investigations which are yet to be closed as resolved or invalid
- Checking if a user is any suggested investigations
Technical notes & database schema
Table schema layout (as a diagram):
Output of DESCRIBE and SHOW INDEXES FROM
cusi_case
describe cusi_case; +-----------------------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+---------------------+------+-----+---------+----------------+ | sic_id | int(10) unsigned | NO | PRI | NULL | auto_increment | | sic_status | tinyint(3) unsigned | NO | MUL | 0 | | | sic_status_reason_id | bigint(20) unsigned | NO | | 0 | | | sic_created_timestamp | binary(14) | NO | MUL | NULL | | +-----------------------+---------------------+------+-----+---------+----------------+
show indexes from cusi_case; +-----------+------------+---------------------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored | +-----------+------------+---------------------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ | cusi_case | 0 | PRIMARY | 1 | sic_id | A | 0 | NULL | NULL | | BTREE | | | NO | | cusi_case | 0 | sic_status_created_timestamp_id | 1 | sic_status | A | 0 | NULL | NULL | | BTREE | | | NO | | cusi_case | 0 | sic_status_created_timestamp_id | 2 | sic_created_timestamp | A | 0 | NULL | NULL | | BTREE | | | NO | | cusi_case | 0 | sic_status_created_timestamp_id | 3 | sic_id | A | 0 | NULL | NULL | | BTREE | | | NO | | cusi_case | 0 | sic_created_timestamp_id | 1 | sic_created_timestamp | A | 0 | NULL | NULL | | BTREE | | | NO | | cusi_case | 0 | sic_created_timestamp_id | 2 | sic_id | A | 0 | NULL | NULL | | BTREE | | | NO | +-----------+------------+---------------------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
cusi_user
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 | | +-------------+------------------+------+-----+---------+-------+
show indexes from cusi_user; +-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored | +-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ | cusi_user | 0 | PRIMARY | 1 | siu_sic_id | A | 0 | NULL | NULL | | BTREE | | | NO | | cusi_user | 0 | PRIMARY | 2 | siu_user_id | A | 0 | NULL | NULL | | BTREE | | | NO | | cusi_user | 1 | siu_user_id | 1 | siu_user_id | A | 0 | NULL | NULL | | BTREE | | | NO | +-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
cusi_signal
describe cusi_signal; +------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+------------------+------+-----+---------+----------------+ | sis_sic_id | int(10) unsigned | NO | PRI | NULL | auto_increment | | sis_name | varbinary(255) | NO | PRI | NULL | | | sis_value | varbinary(255) | NO | PRI | NULL | | +------------+------------------+------+-----+---------+----------------+
show indexes from cusi_signal; +-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored | +-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ | cusi_signal | 0 | PRIMARY | 1 | sis_name | A | 0 | NULL | NULL | | BTREE | | | NO | | cusi_signal | 0 | PRIMARY | 2 | sis_value | A | 0 | NULL | NULL | | BTREE | | | NO | | cusi_signal | 0 | PRIMARY | 3 | sis_sic_id | A | 0 | NULL | NULL | | BTREE | | | NO | | cusi_signal | 1 | sis_sic_id | 1 | sis_sic_id | A | 0 | NULL | NULL | | BTREE | | | NO | +-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
Acceptance criteria
- The database tables to support this feature are created