The database schema for a low-budget social media site (Reddit-like or BlueSky-like) using either MySQL, SQLite or PostgreSQL as primary database

low_budget_social_media_app_design_database.sql

-- MySQL v8.0.16
-- PostgresSQL v16.9.2

CREATE DATABASE IF NOT EXISTS `test`
DEFAULT CHARACTER SET utf8 -- utf8mb4
DEFAULT COLLATE utf8_general_ci; -- utf8mb4_unicode_ci

SET default_storage_engine = INNODB;

CREATE TABLE user_details (
	id bigint NOT NULL,
  	first_name varchar(100) NOT NULL,
  	last_name varchar(100) NOT NULL,
	email varchar(50) NOT NULL UNIQUE,
  	profile_avatar_url varchar(130), -- start with value of NULL and use background job to upload image to `cloudinary` OR `aws-s3` an then later write url value back
  	gender enum('male', 'female') NOT NULL,
  	date_of_birth date NOT NULL,
  	registered_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  	CONSTRAINT userunq UNIQUE (id, first_name, last_name),
  	PRIMARY KEY (id)
);

CREATE UNIQUE INDEX idx_namex
ON user_details (id, last_name, registered_at);

CREATE TABLE user_accounts (
	id bigint NOT NULL,
  	email varchar(50) NOT NULL UNIQUE,
	full_name varchar(200) NOT NULL,
  	password varchar(36) NOT NULL,
  	last_login_at datetime,
  	is_active tinyint(1) NOT NULL DEFAULT 1,
	email_verified tinyint(1) NOT NULL DEFAULT 0,
  	PRIMARY KEY (id),
  	FOREIGN KEY (id) REFERENCES user_details(id) ON DELETE CASCADE
);

CREATE UNIQUE INDEX idx_authx
ON user_accounts (id, email, password, full_name);

CREATE INDEX idx_authacctx
ON user_accounts (email, password, is_active, last_login_at);

CREATE TABLE interests (
	id bigint NOT NULL AUTO_INCREMENT,
  	topic varchar(30) NOT NULL,
  	description text,
  	CONSTRAINT interstunq UNIQUE KEY ix_length_description (id, topic, description(125)),
  	PRIMARY KEY (id)
);


CREATE TABLE user_interests (
	user_id bigint NOT NULL,
  	interest_id bigint NOT NULL,
	action enum('experiencing', 'watching', 'playing', 'driving', 'listening', 'having', 'making', 'eating') NOT NULL,
  	FOREIGN KEY (user_id) REFERENCES user_details(id) ON DELETE CASCADE,
  	FOREIGN KEY (interest_id) REFERENCES interests(id) ON DELETE CASCADE
);

CREATE UNIQUE INDEX idx_usrintrstx
ON user_interests (user_id, interest_id, action);

CREATE TABLE followerships (
	follow_source bigint NOT NULL,
  	follow_target bigint NOT NULL,
  	linked_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  	CONSTRAINT followershipunq UNIQUE (follow_source, follow_target),
  	FOREIGN KEY (follow_source) REFERENCES user_details(id) ON DELETE CASCADE,
  	FOREIGN KEY (follow_target) REFERENCES user_details(id) ON DELETE CASCADE
);

CREATE INDEX idx_followx
ON followerships (follow_source, follow_target, linked_at);

-- `file:document-compressed` can be: docx, pptx, xlsx, zip, rar; `url:http` are web urls; `file:document-binary` can be: doc, pdf; `file:video` can be: mov, mkv, mp4; `file:text` can be: json, xml, html, css, csv;
CREATE TABLE thread_assets (
	id bigint NOT NULL,
	type enum('file:audio', 'file:video', 'file:text', 'url:http', 'url:whatsapp-chat', 'live:video-recorded', 'live:audio-recorded', 'file:document-binary', 'file:executable-binary', 'file:document-compressed', 'file:text-compressed') NOT NULL,
	extension char(3) NOT NULL DEFAULT 'nil',
	name varchar(150) NOT NULL DEFAULT 'Untitled',
	secure_storage_url longtext NOT NULL,
	creator_id bigint NOT NULL,
	uploaded_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (id),
	FOREIGN KEY (creator_id) REFERENCES user_details(id) ON DELETE NO ACTION
);

CREATE INDEX idx_thrdassetsx
ON thread_assets (id, uploaded_at, name);

CREATE TABLE thread_channels (
	id bigint NOT NULL,
	topic varchar(100) NOT NULL DEFAULT 'Untitled',
	participant_ids json NOT NULL DEFAULT ('[]'),
	parent_id bigint,
	creator_id bigint NOT NULL,
	messages_has_single_receipient tinyint(1) NOT NULL DEFAULT 0,
	access_type enum('open', 'invite_only', 'follow-only') NOT NULL DEFAULT 'open',
  	is_pinned tinyint(1) NOT NULL DEFAULT 0,
	created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (id),
	CONSTRAINT threadchannllnkunq UNIQUE (id, parent_id),
	FOREIGN KEY (parent_id) REFERENCES thread_channels(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (creator_id) REFERENCES user_details(id) ON DELETE NO ACTION,
);

CREATE INDEX idx_thrdchannlsx
ON thread_channels (id, creator_id, access_type);

CREATE TABLE thread_channel_messages (
  	id bigint NOT NULL,
  	content longtext,
  	parent_id bigint, -- indicates hierarchical posts OR post with replies and/or reply-to-replies (self-referencing foreign key)
  	author_id bigint NOT NULL,
  	repost_id bigint, -- indicates reposted posts OR posts that are created by reposting another post (self-referencing foreign key)
	channel_id bigint,
	quote_id bigint, -- indicates quoting posts OR posts that quote another post (self-referencing foreign key)
	has_reply tinyint(1) NOT NULL DEFAULT 0, -- indicates whether a post has at least one reply
	reaction_count_up bigint NOT NULL DEFAULT 0, -- PN Counter modelling (from CRDTs)
	reaction_count_down bigint NOT NULL DEFAULT 0, -- PN Counter modelling (from CRDTs)
  	created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  	updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  	CONSTRAINT threadunq UNIQUE KEY ix_length_content (id, content(255)),
  	CONSTRAINT contentchk CHECK (char_length(content) <= 300),
	CONSTRAINT threadlnkunq UNIQUE (id, parent_id),
	PRIMARY KEY (id),
	FOREIGN KEY (channel_id) REFERENCES thread_channels(id) ON DELETE CASCADE,
	FOREIGN KEY (parent_id) REFERENCES thread_channel_messages(id) ON DELETE CASCADE ON UPDATE CASCADE,
  	FOREIGN KEY (repost_id) REFERENCES thread_channel_messages(id) ON DELETE NO ACTION ON UPDATE CASCADE,
	FOREIGN KEY (quote_id) REFERENCES thread_channel_messages(id) ON DELETE NO ACTION ON UPDATE CASCADE,
  	FOREIGN KEY (author_id) REFERENCES user_details(id) ON DELETE CASCADE
);

CREATE UNIQUE INDEX idx_threadx
ON thread_channel_messages (id, content(255), author_id, created_at);

CREATE TABLE thread_channel_asset_records (
	thread_message_id bigint NOT NULL,
	thread_asset_id bigint NOT NULL,
	thread_channel_id bigint  NOT NULL,
	PRIMARY KEY(thread_message_id, thread_asset_id, thread_channel_id),
	FOREIGN KEY (thread_asset_id) REFERENCES thread_assets(id) ON DELETE NO ACTION,
	FOREIGN KEY (thread_message_id) REFERENCES thread_channel_messages(id) ON DELETE NO ACTION,
	FOREIGN KEY (thread_chaneel_id) REFERENCES thread_channels(id) ON DELETE NO ACTION
);

CREATE TABLE thread_trend_tags (
	id bigint NOT NULL,
	tag_type enum('word', 'bigram') NOT NULL, -- Only 'adjectives', 'main verbs' & 'nouns' are strored as trend tags. So, 'articles', 'prepositions', 'conjunctions', 'auxillary verbs' and 'pronouns' are all discarded
	is_hash_tag tinyint(1) NOT NULL DEFAULT 0,
	tag_value varchar(210) NOT NULL, -- prior to storing this, we apply parts-of-speech tagging + lemmatization on each message/post/tweet to know which words to store for trend analysis
	is_abbreviation tinyint(1) NOT NULL DEFAULT 0,
	is_slang tinyint(1) NOT NULL DEFAULT 0,
	is_compound tinyint(1) NOT NULL DEFAULT 0,
	CONSTRAINT threatgunq UNIQUE (tag_value),
	PRIMARY KEY(id)
);

CREATE TABLE thread_geo_positions (
    id bigint NOT NULL,
    country_code char(2) NOT NULL, -- ISO 3166-1 alpha-2,
	position_name_slug varchar(60) NOT NULL,
    position_latitude decimal(9,6),
    position_longitude decimal(9,6),
    position_type enum('region','city','town') NOT NULL,
    PRIMARY KEY (id)
);

CREATE INDEX idx_country_code
ON thread_geo_positions (country_code);

CREATE UNIQUE INDEX idx_city_name
ON thread_geo_positions (position_name_slug);

CREATE INDEX idx_place_type
ON thread_geo_positions (position_type);

CREATE UNIQUE INDEX idx_geo_coordinates
ON thread_geo_positions (latitude, longitude);

--  Inverted index-like table for listing and searching for words and/or hash tags
CREATE TABLE thread_trend_records (
  	tag_id bigint NOT NULL,
	message_id bigint NOT NULL,
  	author_id bigint NOT NULL,
	tag_value_position_in_message int NOT NULL,
	tag_value_lemma_applied tinyint(0) NOT NULL, -- was lemmatization applied to the tag value or not
	message_geo_position_id bigint NOT NULL,
  	PRIMARY KEY (tag_id, message_id, author_id),
	FOREIGN KEY (tag_id) REFERENCES thread_trend_tags(id) ON DELETE CASCADE,
  	FOREIGN KEY (message_id) REFERENCES thread_channel_messages(id) ON DELETE CASCADE,
  	FOREIGN KEY (author_id) REFERENCES user_details(id) ON DELETE CASCADE,
	FOREIGN KEY (message_geo_position_id) REFERENCES thread_geo_positions(id) ON DELETE NO ACTION
);

CREATE UNIQUE INDEX idx_threadtrendsx
ON thread_trends (message_id, tag_id, author_id);

CREATE TABLE thread_reactions (
	user_id bigint NOT NULL,
  	message_id bigint NOT NULL,
  	type enum('fav', 'clap', 'support', 'sad', 'repost', 'upvote', 'downvote', 'quote') NOT NULL,
  	reacted_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  	CONSTRAINT thread_reacunq UNIQUE (user_id, message_id, type),
  	FOREIGN KEY (user_id) REFERENCES user_details(id) ON DELETE CASCADE,
  	FOREIGN KEY (message_id) REFERENCES thread_channel_messages(id)
);

CREATE INDEX idx_threadx
ON thread_reactions (message_id, user_id, reacted_at, type);

CREATE TABLE custom_feed_settings (
	id bigint NOT NULL,
	name varchar(100) NOT NULL,
	description text,
	access_type enum('private', 'public') NOT NULL DEFAULT 'public',
	show_on_profile_timeline tinyint(1) NOT NULL DEFAULT 1,
	subject_ids json NOT NULL DEFAULT ('[]'),
	trendy_tags json NOT NULL DEFAULT ('[]'),
	owner_id bigint NOT NULL,
	updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (id),
	FOREIGN KEY (owner_id) REFERENCES user_details(id) ON DELETE CASCADE
);

CREATE INDEX idx_custmfeedx
ON custom_feed_settings (id, owner_id, name);

CREATE TABLE thread_bookmarks (
	id bigint NOT NULL,
	message_id bigint NOT NULL,
  	saved_by bigint NOT NULL,
	saved_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (id),
  	FOREIGN KEY (saved_by) REFERENCES user_details(id) ON DELETE CASCADE,
	FOREIGN KEY (message_id) REFERENCES thread_channel_messages(id) ON DELETE CASCADE
);

CREATE INDEX idx_threadbmk
ON thread_bookmarks (message_id, saved_by);

CREATE TABLE thread_drafts (
	id bigint NOT NULL,
	auto_publish_at timestamp, -- If `auto_publish_at` is NULL, then draft type is 'UNSENT' else draft type is 'SCHEDULED'
	author_id bigint NOT NULL,
	content longtext,
	is_published tinyint(1) NOT NULL DEFAULT 0,
	drafted_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY(id),
	FOREIGN KEY (author_id) REFERENCES user_details(id) ON DELETE CASCADE
);

CREATE INDEX idx_threadrft
ON thread_drafts (author_id);

CREATE TABLE account_blocks (
  	id bigint NOT NULL,
    blocker_user_id bigint NOT NULL,
    blocked_user_id bigint NOT NULL,
    blocked_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(id),
    FOREIGN KEY (blocker_user_id) REFERENCES user_details(id) ON DELETE CASCADE,
    FOREIGN KEY (blocked_user_id) REFERENCES user_details(id) ON DELETE CASCADE,
    -- Prevents the same user from being blocked twice by the same person
    UNIQUE KEY (blocker_user_id, blocked_user_id)
);

CREATE INDEX idx_acctblcks
ON account_blocks (blocked_at);

-- DELIMITER //

-- CREATE TRIGGER check_thread_channels_messages_lock
-- BEFORE INSERT ON thread_channel_messages
-- FOR EACH ROW
-- BEGIN
--     IF (NEW.quote_id IS NOT NULL AND NEW.repost_id IS NOT NULL) THEN
--         SIGNAL SQLSTATE '45000'
--         SET MESSAGE_TEXT = CONCAT('Constraint thread_channel_messages_lock violated: one or both of columns quote_id or repost_id must be null. repost_id: ', CAST(NEW.repost_id AS CHAR), ', quote_id: ', CAST(NEW.quote_id AS CHAR))//
--     END IF//
-- END//

-- DELIMITER //


-- CREATE TRIGGER check_thread_channels_recipients_lock
-- BEFORE INSERT ON thread_channels
-- FOR EACH ROW
-- BEGIN
--     IF (NEW.messages_has_single_receipient = 0 AND NEW.access_type = 'follow-only') THEN
--         SIGNAL SQLSTATE '45000'
--         SET MESSAGE_TEXT = CONCAT('Constraint thread_channel_recipients_lock violated: messages_has_single_recipient must not be false while access is of type follow-only. messages_has_single_receipient: ', CAST(NEW.messages_has_single_receipient AS CHAR))//
--     END IF//
-- END//

-- DELIMITER //


-- CREATE TRIGGER check_user_details_names
-- BEFORE INSERT ON user_details
-- FOR EACH ROW
-- BEGIN
--     IF (CHAR_LENGTH(NEW.first_name) < 2 OR CHAR_LENGTH(NEW.last_name) < 2) THEN
--         SIGNAL SQLSTATE '12345'
--         SET MESSAGE_TEXT = 'Check constraint on user_details first or last name(s) failed: Name length must be at least 2 characters.'//
--     END IF//
-- END//

-- DELIMITER ;





INSERT INTO user_details (id, first_name, last_name, gender, profile_avatar_url, date_of_birth) VALUES
(1, 'Akin', 'Lewi-Soyinka', 'male', 'https://me.assets/image/sBSd65749', '1988-04-11'),
(2, 'Tabitha', 'Ngorh', 'female', 'https://me.assets/image/o84Z8401Rytr', '1994-10-02'),
(3, 'Eze', 'Barnabas', 'male', 'https://me.assets/image/pO74Judc748', '1990-08-25'),
(4, 'Destiny', 'Okondo', 'female', 'https://me.assets/image/placeholder', '1991-09-12');

INSERT INTO user_accounts (id, email, password, last_login_at, full_name, email_verified) VALUES
(2, 'akinlw@yahoo.com', 'xbu648dbs8849', '2025-03-11', 'Tabitha Ngorh', 1),
(4, 'destiny.okogmail.com', 'sby378bT%#3bjZ', '2025-04-23', 'Destiny Okondo', 1),
(3, 'eze4reel@hotmail.com', 'cBv$33!LodMre', NULL, 'Eze Barnabas', 1);


INSERT INTO interests (topic, description) VALUES
('american movies', 'all hollywood movies in any genre'),
('songs', 'any song at all');

INSERT INTO user_interests (user_id, interest_id, action) VALUES
(2, 2, 'listening'),
(4, 1, 'experiencing');

INSERT INTO followerships (follow_source, follow_target) VALUES
(3, 1),
(1, 2),
--(4, 1), user with id 4 blocked user with id 1 so they cannot follow themselves either way
(3, 4),
(2, 4),
(2, 3),
(4, 2),
(4, 3);

INSERT INTO thread_channels (id, participant_ids, parent_id, creator_id, messages_has_single_receipient, access_type) VALUES
(1, '[2]', NULL, 4, 1, 'follow-only');

INSERT INTO thread_channel_messages (id, content, parent_id, author_id, repost_id, channel_id, quote_id, has_reply, reaction_count_up, reaction_count_down) VALUES
(1, 'Initial post about the importance of good sleep habits.', NULL, 2, NULL, NULL, NULL, 1, 2, 0),
(2, 'Reply to post 1: Absolutely! Lack of sleep affects productivity.', 1, 3, NULL, NULL, NULL, 0, 0, 0),
(3, 'Another root thread: Best places to visit in the summer.', NULL, 2, NULL, NULL, NULL, 1, 2, 0),
(4, 'Reply to post 3: I recommend the Amalfi Coast in Italy!', 3, 1, NULL, NULL, NULL, 1, 0, 0),
(5, 'Reply to post 1: I find a consistent bedtime routine helps me most.', 1, 2, NULL, NULL, NULL, 0, 0, 0),
(6, 'Reply to post 2: Do you have any specific tips for improving sleep quality?', 2, 3, NULL, NULL, NULL, 0, 0, 0), -- This is a reply to a reply, not a direct child of a root post.
(7, 'Root thread: Discussing the latest advancements in AI.', NULL, 4, NULL, NULL, NULL, 1, 2, 0),
(8, 'Reply to post 7: Generative AI has been revolutionary.', 7, 3, NULL, NULL, NULL, 0, 0, 0),
(9, 'Reply to post 4: Amalfi Coast is beautiful, but quite crowded in summer.', 4, 2, NULL, NULL, NULL, 0, 0, 0),
-- This is a reply to a reply, not a direct child of a root post.
(10, 'Reply to post 7: What are the ethical implications of AI?', 7, 4, NULL, NULL, NULL, 0, 0, 0),
-- This is a repost of a reply.
(11, 'Repost of post 7: What are the ethical implications of AI?', NULL, 2, 10, NULL, NULL, 0, 0, 0),
(12, 'Lets get ready for the #FifaClubWorldCup2029. Yass!!', NULL, 3, NULL, NULL, NULL, 0, 0, 0),
-- This is a direct message to another user (single recipient)
(13, 'How are you my friend?', NULL, 4, NULL, 1, NULL, 1, 0, 0);

INSERT INTO thread_trend_tags (id, tag_type, is_hash_tag, tag_value, is_abbreviation, is_slang, is_compound) VALUES
(2, 'word', 1, '#FifaClubWorldCup2029', 0, 0, 1),
(3, 'word', 0, 'AI', 1, 0, 0),
(4, 'bigram', 0, 'Donald Trump', 0, 0, 0),
(5, 'word', 0, 'Yass', 0, 1, 0)
ON CONFLICT (tag_value) DO NOTHING;

INSERT INTO thread_geo_positions (id, country_code, position_name_slug, position_longitude, position_latitude, position_type) VALUES
(4, 'NG', 'Surlere', NULL, NULL, 'town'),
(6, 'FR', 'Paris', NULL, NULL, 'city');

INSERT INTO thread_trend_records (tag_id, message_id, author_id, tag_value_position_in_message, tag_value_lemma_applied, message_geo_position_id) VALUES
(2, 12, 3, 6, 0, 4), -- This post (added to the trends table) was sent from Surulere, Nigeria and the tag value is '#FifaClubWorldCup2029'
(3, 7, 2, 6, 0, 6),
(3, 11, 4, 7, 0, 6); -- This post (added to the trends table) was sent from Paris, France and the tag value is 'AI'

INSERT INTO thread_reactions (user_id, message_id, type) VALUES
(3, 1, 'clap'),
(1, 1, 'fav'),
(1, 3, 'support'),
(4, 7, 'fav'),
(2, 7, 'repost'),
(4, 3, 'sad');

INSERT INTO thread_drafts (id, auto_publish_at, author_id, content, is_published) VALUES
(9, NULL, 3, 'You dey mad?', 0);

INSERT INTO account_blocks (id, blocker_user_id, blocked_user_id) VALUES
(6, 4, 1);

QUERIES

-- Get all root/top-level posts (that aren't reposts or direct messages) and only their immediate replies (i.e. not the replies to the replies and so on)
SELECT DISTINCT
    t1.id AS post_id,
    t1.content AS post_content,
    t1.created_at AS post_content_date_published,
    t1.author_id AS post_content_author_id,
    CONCAT(u2.first_name, " ", u2.last_name) AS post_content_author_fullname,
    t2.author_id AS reply_content_author_id,
    CONCAT(u1.first_name, " ", u1.last_name) AS reply_content_author_fullname,
    t2.id AS reply_id,
    t2.content AS reply_content
FROM
        thread_channel_messages t1
LEFT JOIN
        thread_channel_messages t2 ON t2.parent_id = t1.id
JOIN
        user_details u1 ON u1.id = t2.author_id
JOIN
       user_details u2 ON u2.id = t1.author_id
WHERE t1.parent_id IS NULL -- Get only root/top-level threads
AND t1.repost_id IS NULL -- Get only posts that aren't reposts
AND t1.channel_id IS NULL -- Get only posts that aren't direct messages or group messages
AND t1.quote_id IS NULL -- Get only posts that haven't been quoted (like in Bluesky/Twitter/X) yet
ORDER BY t1.id, t2.id;

-- The result of the query above can be transformed in the app domain layer to
-- [ 
--   {
--     post: {
--       id: 1,
--       text: "...",
--       author: {
--         id: 2,
--         fullname: "___",
--         avatar_url: "==="
--       },
--       replies: [{
--         id: 3,
--         text: "...",
--         author: { id: 4, fullname: "___", avatar_url: "===" },
--         published_at: "2025-03-12 11:04:56"
--       }],
--       published_at: "2025-06-29 12:34:28"
--     }
--   }
-- ];

-- Get the likes count on a post/comment with (id = 1)
SELECT DISTINCT COUNT(user_id) AS likes_count FROM thread_reactions WHERE message_id = 1 AND (type = 'fav' OR type = 'clap' OR type = 'support');

-- Get all users who liked on a post/comment with (id = 1)
SELECT DISTINCT
    tr1.type as reaction,
    CONCAT(u1.first_name, " ", u1.last_name) AS post_content_reaction_author_fullname FROM
    thread_reactions tr1
JOIN
    user_details u1 ON u1.id = tr1.user_id
WHERE tr1.message_id = 1;

-- Get all the followers count for a user with (id = 2)
SELECT DISTINCT COUNT(follow_source) AS followers_count FROM followerships WHERE follow_target = 2;

-- Get all the followings count for a user with (id = 3)
SELECT DISTINCT COUNT(follow_target) AS followings_count FROM followerships WHERE follow_source = 3;

-- Get all users which user with (id = 1) is following
SELECT DISTINCT
	CONCAT(u.first_name, " ", u.last_name) AS fullname
FROM
	user_details u
JOIN
	followerships f ON u.id = f.follow_target
WHERE f.follow_source = 1;

-- Get all followers of user with (id = 2)
SELECT DISTINCT
	CONCAT(u.first_name, " ", u.last_name) AS fullname
FROM
	user_details u
JOIN
	followerships f ON u.id = f.follow_source
WHERE f.follow_target = 2;

-- Get all mutual followers of users with ids (id = 4) and (id = 3)
SELECT DISTINCT
	CONCAT(u.first_name, " ", u.last_name) AS fullname
FROM
	user_details u
JOIN
	followerships f1 ON u.id = f1.follow_source
JOIN
	followerships f2 ON f2.follow_source = u.id
WHERE
	f1.follow_target = 4 AND f2.follow_target = 3;
    
-- Get all root/top-level posts and reposts only for the timeline for everyone whom a user with (id = 4) follows and any posts liked by user with (id = 4) |For You Tab|
SELECT DISTINCT
	t.id AS post_id,
	t.content AS post_content,
    CONCAT(u.first_name, " ", u.last_name) AS post_author_fullname,
    u.profile_avatar_url AS post_author_avatar_url,
    (SELECT COUNT(user_id) from thread_reactions WHERE message_id = t.id) AS post_reactions_count
FROM
	thread_channel_messages t
JOIN
       user_details u ON u.id = t.author_id
JOIN
 	thread_reactions tr ON tr.user_id = u.id OR tr.message_id = t.id
JOIN
	followerships f ON f.follow_source = u.id
WHERE
 	t.parent_id IS NULL
AND
	t.channel_id IS NULL
AND
	(f.follow_target = 4 OR t.author_id = 4)
GROUP BY
	t.id
ORDER BY
	t.created_at DESC
LIMIT 10;

-- Get all root/top-level posts and reposts only for the timeline for everyone following a user with (id = 4) |Following Tab|
SELECT DISTINCT
	t.id AS post_id,
	t.content AS post_content,
    CONCAT(u.first_name, " ", u.last_name) AS post_author_fullname,
    u.profile_avatar_url AS post_author_avatar_url,
    (SELECT COUNT(user_id) from thread_reactions WHERE thread_id = t.id) AS post_reactions_count
FROM
	thread_channel_messages t
JOIN
       user_details u ON u.id = t.author_id
JOIN
 	thread_reactions tr ON tr.user_id = t.author_id OR tr.message_id = t.id
JOIN
	followerships f ON f.follow_target = u.id
WHERE
 	t.parent_id IS NULL
AND
	(f.follow_source = 4)
GROUP BY
	t.id
ORDER BY
	t.created_at DESC
LIMIT 10;

-- Fetch posts for user 4's timeline, excluding posts from blocked users
SELECT t.*, u.first_name
FROM thread_channel_messages t
JOIN user_details u ON t.author_id = u.id
LEFT JOIN account_blocks b ON t.author_id = b.blocked_user_id AND b.blocker_user_id = 4
WHERE b.id IS NULL -- This condition filters out rows where a block relationship exists
ORDER BY t.created_at DESC;

-- Check if user 1 (commenter) is blocked by user 4 (post owner)
SELECT COUNT(*)
FROM account_blocks
WHERE blocker_user_id = 4 AND blocked_user_id = 1;

-- If COUNT() > 0, then, prevent the comment.

SQL Fiddle: https://www.db-fiddle.com/f/aMyhHPLGi5G7mw9dAa7uMQ/12