Clean up schema for table audit_events
From https://gitlab.com/gitlab-com/infrastructure/issues/1709 (private to GitLab employees):
- Wrong data type: created_at should be timestamptz
- Wrong data type: updated_at should be timestamptz
- Missing FK: author_id -> users (?) (351798 entries violate this rule)
- Missing constraint: created_at NOT NULL
- Missing constraint: updated_at NOT NULL
- Missing constraint: details NOT NULL
- "Polymorphic" table prevents regular FKs, use triggers or change to independent columns or non-polymorphic tables
- Missing trigger for FK check: entity_type = 'Group', entity_id -> namespaces.id (4575 entries violate this rule)
- Missing trigger for FK check: entity_type = 'User', entity_id -> users.id (346884 entries violate this rule)
- Missing trigger for FK check: entity_type = 'Project', entity_id -> projects.id (32192 entries violate this rule)
- Missing constraint: CHECK (entity_type = 'Project' OR entity_type = 'User' OR entity_type = 'Group')
- Missing constraint: entity_type NOT NULL
- 'type' is only ever 'SecurityEvent'
-
Removing the 'type' column would reduce the size of the table by 168MB
-
Replacing 'type' with an enum or 4-byte integer would reduce the size by 126MB
-
- 'details' appears to actually be structured data
-
Average length of 92 bytes
-
Storing as proper columns would reduce the size of the table
-
- 'type' is only ever 'SecurityEvent'
We should probably move the polymorphing columns to separate columns, given I don't see us adding more than 3. These columns (group_id
, user_id
, and project_id
) should have a foreign key and a constraint that ensures only one can be set for every row.
Further, we should also nuke the SecurityEvent
class and just use AuditEvent
, removing the need for the type
column (saving space in the process). SecurityEvent
is currently empty so there's no need for it to exist.