New Filter schemas

[Akarys42]

Abstract

While python-discord/bot#1530 describe the new changes we want for the filter cog group in our bot functionality-wise, this issue will focus on the new database schema proposed to implement those changes. I'd highly recommend you to check out 1530 before reading this issue.

New Schema Overview

Here is a visual representation of the proposed schema :

Note: Due to technical limitations, the list notation for FK had to be placed in the column name.

The schema articulate around the filter_list table, representing the persistent state of a FilterList class, identified by its name and type. It has a list of filters representing each a Filter instance along with three tables, filter_action, filter_list_settings and channels_range to define its default values. Each filter is allowed to override its default value by setting a field in its filter_override to not NULL.

Schema Specifications

filter_list

This is the core of the schema, each one representing a particular list in its allow or deny version.

• PK name: The name used in the bot-side source code.
• PK list_type: One of "deny" or "allow". Whenever this is an allow or deny list.
• FK array filters: References to filter composing this list
• FK default_settings: Reference to the filter_list_settings making the default parameter of this list.

filter_settings

This table represents the state of a Setting object.

• array ping_type: One of "everyone", "here", "moderators", "onduty", "none" or UID. The role(s) or user(s) to ping when the filter triggers.
• filter_dm: Should the filter run on DM messages?
• array dm_ping_type: Same as ping_type. The role to ping when the filter triggers on a DM.
• delete_messages: Should the bot automatically delete messages that triggered the message?
• array bypass_roles: List of roles or user IDs that are ignored by this filter.
• enabled: Is this filter enabled?
• FK default_action and channels_range: References extending this setting.

filter_action

The moderation action to automatically take when the filter is triggered.

• user_dm: DM to send to the user when they trigger the filter. Null if disabled.
• infraction_type: One of "warn", "mute", "ban". The infraction to apply to the user if they trigger the filter. Null if disabled.
• infraction_duration: The duration of the infraction to apply, if any. Null if permanent.

channel_ranges

The channels where the filter should apply. It contains arrays of allowed and disallowed channels and categories IDs and a default boolean. The resolution is done in order:

• disallowed channels
• disallowed categories
• allowed categories
• allowed channels
• default value

filter

One entry of an allow or denylist.

• content: The string actually making this filter.
• description: Information added to the filter to explain why it has been added.
• additional: An additional field that is left to the FilterList implementation to name and use. Null is not used.
• FK override: Reference to a filter_override used to override the settings provided by the list itself.

Filter_override

This contains the override of a specific filter on the filter list default. The field is set to null if no override is performed.

• ping_type, filter_dm, dm_ping_type, delete_messages, bypass_roles: Override the corresponding fields.
• FK filter_action: Reference to a filter_action overriding default_action all at once.
• FK filter_range: Reference to a channels_range overriding default_range all at once.

Migration

Migration should be written to take the current content of our filter list and migrate them to the new schema.