Right now querying posts has logic like this:
WHERE (((((((((("community"."removed" = $9) AND ("community"."deleted" = $10)) AND ("post"."removed" = $11)) AND ("post"."deleted" = $12)) AND (("community"."hidden" = $13)
Note that a community can be hidden or deleted, separate fields. And it also has logic to see if the creator of the post is banned in the community:
LEFT OUTER JOIN "community_person_ban" ON (("post"."community_id" = "community_person_ban"."community_id") AND ("community_person_ban"."person_id" = "post"."creator_id"))
And there is both a deleted boolean (end-user delete) and removed boolean (moderator removed) on a post.
Much of this also applies to comments. Which are also owned by the post, which are also owned by the community.
This is a difficult change to make, and I’m likely to focus more on testing code at this point.
the use of two entire fields for deleted and removed could be consolidated into a flag with multiple meanings. Right now, WHERE clauses have to include conditions for both, when a smallint, optimized enum, or character could hold values such as: a = open, d = deleted, r = removed, e = deleted + removed, f = spam filtered pending analysis, p = pending mod approval for troubled user, q = pending mod approval for all.
Right now, queries to list post in a community do this:
AND ("post"."nsfw" = $15)) AND ("community"."nsfw" = $16))
It should be rare for a community to flip their nsfw flag. And posts are owned by a community. So why not set the post.nsfw flag to true and rewrite it based on a community flipping value. Instead of every refresh to a page having to query these independent variables.
featured_community and featured_local could be combine into a unified enum/value field and allowing expansion of features for other uses. Maybe only on post_aggregates to start.