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

low_budget_social_media_site_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,
  	profile_avatar_url text,
  	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,
	full_name varchar(200) NOT NULL,
  	password varchar(36) NOT NULL,
  	last_login_at date,
  	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,
	uploaded_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (id)
);

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',
	topic_media_id bigint,
  	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,
	FOREIGN KEY (topic_media_id) REFERENCES thread_assets(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,
  	author_id bigint NOT NULL,
  	repost_id bigint,
	channel_id bigint,
	quote_id bigint,
  	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);

--  Inverted index-like table for listing and searching for hash tags
CREATE TABLE thread_trends (
  	hash_tag varchar(60) NOT NULL,
	thread_id bigint NOT NULL,
  	author_id bigint NOT NULL,
  	CONSTRAINT trendsunq UNIQUE (hash_tag, thread_id, author_id),
  	FOREIGN KEY (thread_id) REFERENCES thread_channel_messages(id) ON DELETE CASCADE,
  	FOREIGN KEY (author_id) REFERENCES user_details(id) ON DELETE CASCADE
);

CREATE UNIQUE INDEX idx_threadtrendsx
ON thread_trends (thread_id, hash_tag);

CREATE TABLE thread_reactions (
	user_id bigint NOT NULL,
  	thread_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, thread_id, type),
  	FOREIGN KEY (user_id) REFERENCES user_details(id) ON DELETE CASCADE,
  	FOREIGN KEY (thread_id) REFERENCES thread_channel_messages(id)
);

CREATE INDEX idx_threadx
ON thread_reactions (thread_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,
	thread_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 (thread_id) REFERENCES thread_channel_messages(id) ON DELETE CASCADE
);

CREATE TABLE thread_trend_records (
	thread_id bigint NOT NULL,
	trended_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	FOREIGN KEY (thread_id) REFERENCES thread_channel_messages(id) ON DELETE CASCADE
);

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, '[email protected]', 'xbu648dbs8849', '2025-03-11', 'Tabitha Ngorh', 1),
(4, 'destiny.okogmail.com', 'sby378bT%#3bjZ', '2025-04-23', 'Destiny Okondo', 1),
(3, '[email protected]', '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),
(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, topic_media_id) VALUES
(1, '[2]', NULL, 4, 1, 'follow-only', NULL);

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

INSERT INTO thread_trends (hash_tag, thread_id, author_id) VALUES
('#FifaClubWorldCup2024', 12, 3),
('AI', 7, 2),
('AI', 11, 4);

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

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
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 thread_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.thread_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 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 = u.id OR tr.thread_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.thread_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;

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