LemmyNet
Merge moderation database tables
At the moment we store mod actions in a separate table for each type of action, for example mod_ban, mod_lock_post, mod_sticky_post etc. This seems relatively complex, and causes problems like the mod log requiring many separate queries, and showing items in the wrong order (on lemmy.ml, some 3 year old items are on page 1, while 7 day old items are on page 2). The current implementation probably also makes changes to the mod log #2313 much more difficult.
I propose to store the mod log in this way instead:
id, mod_id, user/community id, reason, type(string), extra_data(json)
This means that all mod actions are stored in the same database table, distinguished by the type field (ban, lock_post, sticky_post etc). Though it might be necessary to have one table for actions that affect users, and another for actions that affect communities. In any case, only data common to all mod actions would be stored directly in database columns. Other data would be stored in the extra_data column in json format, which gets (de)serialized to a struct depending on the type. So for a ban, extra_data would contain the expiration time, and so on.
cc @makotech222
I would agree 100%, but probably do it after the mod filters is finished :)
A much simpler solution, would just be to do paging in the modlog, with time-based queries, rather than count-based. IE page 1 would be < month, page 2 would be (> 1 month < 2 months ), etc.
The main problem is that different types of moderator actions have different data, and filtering them based on json columns will get annoying and complicated very quickly. Some mod actions have target communities, some target persons, some no target at all ( such as purge_community ). These are already all organized, well-typed on both the front and back-end, can have SQL constraints, etc.
Closing this, as specific tables for that specific action are preferable storage wise. The issue is mainly with presentation of that data, which is #2444