Complete database schema for a Twitter/X-like microblogging platform covering user accounts, tweet content, media attachments, hashtags, follow relationships, likes, retweets, notifications, direct messaging, and trending topics.
Three different things can point at a tweet, and this schema keeps them apart. A reply is reply_to_id on the tweet itself. A quote is quote_tweet_id on the tweet itself. A plain retweet is a row in retweets, because it creates no new text and should not be a tweet with a null body.
That split is the whole design. Replies and quotes are tweets — they have content, they can be replied to, they show up in the author's timeline. A retweet is an endorsement, so it is closer to a like than to a post, and retweets.comment exists only to let a quote be recorded there too if you prefer that shape.
Everything read on a timeline is denormalized onto the tweet: retweet_count, like_count, reply_count, view_count. The tables that produce those numbers are still there, so the counters are a cache of a truth that can be recomputed, not the truth itself.
A reply and a quote both carry text, so they need everything a tweet has — media, hashtags, their own replies. Making them tweets with a pointer costs one nullable column each. A plain retweet carries nothing; making it a tweet would mean a row with null content that every timeline query has to special-case, and un-retweeting would be a delete of something that looked like a post. As a separate table it is an insert and a delete, like a like.
deleted_at rather than a delete, because a tweet is referenced by replies, quotes, bookmarks and notifications. Removing the row would either cascade and take the replies with it or leave dangling pointers. Soft delete lets the thread survive with a 'this tweet was deleted' gap, which is what people expect to see.
trending_topics has started_at and ended_at rather than being truncated and rewritten. A trending list is interesting after the fact — it is how you answer what was happening at a given hour — and rewriting the table throws that away every cycle. rank is stored because it is the output of a ranking run, not something to recompute later.
list_members.added_by exists because a list can be collaborative. Without it, removing a contributor leaves no way to tell which members came from them, and there is no audit for a member who does not want to be on a list.