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

low_budget_social_media_app_design_database.sql

-- MySQL v9.7.0
-- PostgresSQL v16.9.2

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

SET enable_cascade_triggers = ON;
SET default_storage_engine = INNODB;
SET GLOBAL time_zone = '+00:00'; -- I don't want MySQL to convert to current database session timezone

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),
	CONSTRAINT chk_fullname_length CHECK (
		(CHAR_LENGTH(first_name) > 2 
		 	AND CHAR_LENGTH(last_name) > 2
		)
	)
);

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 char(16) NOT NULL,
	PRIMARY KEY (user_id, interest_id),
	CONSTRAINT chk_action CHECK (action IN ('experiencing', 'watching', 'playing', 'driving', 'listening', 'having', 'making', 'eating')),
  	FOREIGN KEY (user_id) REFERENCES user_details(id) ON DELETE CASCADE ON UPDATE CASCADE,
  	FOREIGN KEY (interest_id) REFERENCES interests(id) ON DELETE CASCADE ON UPDATE 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 ON UPDATE CASCADE,
  	FOREIGN KEY (follow_target) REFERENCES user_details(id) ON DELETE CASCADE ON UPDATE CASCADE
);

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

-- `file:compressed` can be: docx, pptx, xlsx, zip, rar; `file:executable` can be: exe, jar; `file:video` can be: mov, mkv, mp4; `file:text` can be: pdf, json, xml, html, css, csv;
CREATE TABLE thread_assets (
	id bigint NOT NULL,
	type varchar(18) NOT NULL,
	extension char(3) NOT NULL DEFAULT 'nil',
	name varchar(150) NOT NULL DEFAULT 'Untitled',
	secure_storage_url varchar(220) NOT NULL,
	creator_id bigint NOT NULL,
	deleted tinyint(1) NOT NULL DEFAULT 0,
	uploaded_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	deleted_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (id),
	CONSTRAINT chk_resource_type CHECK (type IN ('file:audio', 'file:video', 'file:text', 'file:compressed', 'file:executable')),
	FOREIGN KEY (creator_id) REFERENCES user_details(id) ON DELETE NO ACTION ON UPDATE CASCADE
);

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,
	parent_id bigint,
	creator_id bigint NOT NULL,
	messages_has_single_receipient tinyint(1) NOT NULL DEFAULT 0,
	access_type char(12) 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),
	CONSTRAINT chk_channel_access_type CHECK (access_type IN ('open', 'invite_only', 'follow-only')),
	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 ON UPDATE CASCADE,
	CONSTRAINT chk_access_restrict CHECK (
		(access_type = 'follow-only'
		 	AND messages_has_single_receipient = 1
		 	AND JSON_SCHEMA_VALID(
            	'{ "type": "array", "minItems": 2, "maxItems": 2 }', 
            	participant_ids
			)
        )

		OR
		 
		 ((access_type = 'open' OR access_type = 'invite-only')
		 	AND messages_has_single_receipient = 0
		  	AND JSON_SCHEMA_VALID(
            	'{ "type": "array", "minItems": 2, "maxItems": 20 }', -- maximum of 20 people can be members of a group.
            	participant_ids
			)
		 )
	)
);

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

CREATE TABLE thread_channel_direct_messages ( -- This table is for `thread_channel`s with `access_type` of 'follow-only'
	id bigint NOT NULL,
  	content longtext,
  	parent_id bigint, -- indicates hierarchical DMs OR DM with replies and/or reply-to-replies (self-referencing foreign key)
  	author_id bigint NOT NULL,
  	channel_id bigint NOT NULL,
	quote_id bigint, -- indicates quoting DMs OR DMs that quote anoother DM (self-referencing foreign key)
	has_reply tinyint(1) NOT NULL DEFAULT 0, -- indicates whether a DM 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(455)),
  	CONSTRAINT chk_content_dms CHECK (char_length(content) <= 400),
	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_direct_messages(id) ON DELETE NO ACTION ON UPDATE RESTRICT,
	FOREIGN KEY (quote_id) REFERENCES thread_channel_direct_messages(id) ON DELETE NO ACTION ON UPDATE RESTRICT,
  	FOREIGN KEY (author_id) REFERENCES user_details(id) ON DELETE CASCADE ON UPDATE CASCADE,
	CONSTRAINT chk_quote_dms_setup CHECK ( -- one or both of columns `quote_id` and `repost_id` must be NULL but both must EVER be NOT NULL
		(quote_id IS NULL AND parent_id IS NULL) -- A top-level DM that is not quoting another DM is a possibility
		
		OR
		
		(quote_id IS NULL AND parent_id IS NOT NULL) -- A reply DM that is not quoting another DM is also a possibility
		
		OR
		
		(quote_id IS NOT NULL AND parent_id IS NULL) -- A top-level DM that is quoting another DM is also a possibility
		
		OR
		
		(quote_id IS NOT NULL AND parent_id IS NOT NULL) -- A reply DM that is quoting another DM is also a possibility
	)
);

CREATE TABLE thread_channel_messages ( -- This table is for `thread_channel`s with `access_type` of 'invite-only' OR 'open'
  	id bigint NOT NULL,
  	content longtext,
	slug_id varchar(19) NOT NULL,
  	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 chk_content CHECK (char_length(content) <= 250),
	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 NO ACTION ON UPDATE RESTRICT,
  	FOREIGN KEY (repost_id) REFERENCES thread_channel_messages(id) ON DELETE NO ACTION ON UPDATE RESTRICT,
	FOREIGN KEY (quote_id) REFERENCES thread_channel_messages(id) ON DELETE NO ACTION ON UPDATE RESTRICT,
  	FOREIGN KEY (author_id) REFERENCES user_details(id) ON DELETE CASCADE ON UPDATE CASCADE,
	CONSTRAINT chk_quote_repost_posts_setup CHECK ( -- one or both of columns `quote_id` and `repost_id` must be NULL but both must EVER be NOT NULL
		(quote_id IS NULL AND repost_id IS NULL) -- A top-level post or reply post can be neither a repost nor a quote post
		
		OR
		
		(parent_id IS NULL AND quote_id IS NULL AND repost_id IS NOT NULL) -- If the top-level post is a repost then, it must not be a direct reply to another post (indirect reply usig post URL)
		
		OR
		
		(parent_id IS NULL AND quote_id IS NOT NULL AND repost_id IS NULL) -- If the top-level post is quoting another post then, it must not be a direct reply to another post
		
		OR
		
		(parent_id IS NOT NULL AND quote_id IS NOT NULL AND repost_id IS NULL) -- A reply post which quotes another post is a possibility
	)
);

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

CREATE TABLE resource_permission_policies (
    id bigint NOT NULL,
    
    -- Actors
    subject_id bigint NOT NULL, -- The user receiving granular modifications to their exisiting permissions
    initiator_id bigint NOT NULL, -- The user granting/revoking modifications to existing permissions
    
    -- ABAC Action
    action enum('granted:view', 'granted:view-with-duration', 'granted:all-with-duration', 'granted:edit', 'revoked:all', 'revoked:edit', 'revoked:view', 'seek_approval:edit') NOT NULL,
    
    -- Resource Context
    asset_type enum('application:file', 'application:message', 'application:channel') NOT NULL,
    
    -- Nullable FKs - only one should be populated based on `asset_type` per row
    file_id bigint NULL,
    message_id bigint NULL,
	channel_id bigint NULL,
    
    created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	duration_in_seconds char(11) NOT NULL DEFAULT '00000000000',
    
    -- Constraints
	PRIMARY KEY (id),
    FOREIGN KEY (subject_id) REFERENCES user_details(id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (initiator_id) REFERENCES user_details(id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (file_id) REFERENCES thread_assets(id) ON DELETE NO ACTION ON UPDATE RESTRICT,
    FOREIGN KEY (channel_id) REFERENCES thread_channels(id) ON DELETE NO ACTION ON UPDATE RESTRICT,
    FOREIGN KEY (message_id) REFERENCES thread_channel_messages(id) ON DELETE NO ACTION ON UPDATE RESTRICT,
	CONSTRAINT chk_perms_mapper CHECK (
		-- 1. application:file: file_id != NULL AND message_id = NULL AND channel_id = NULL
        (asset_type = 'application:file' 
            AND file_id IS NOT NULL 
            AND message_id IS NULL
			AND channel_id IS NULL)

		OR 
		
		-- 2. application:message: file_id = NULL AND message_id != NULL AND channel_id = NULL
		(asset_type = 'application:message' 
            AND file_id IS NULL 
            AND message_id IS NOT NULL
			AND channel_id IS NULL)
		
		OR
		
		-- 3. application:channel: file_id = NULL AND message_id = NULL AND channel_id != NULL
		(asset_type = 'application:channel' 
            AND file_id IS NULL 
            AND message_id IS NULL
			AND channel_id IS NOT NULL)
	)
);

CREATE INDEX idx_subject_action
ON resource_permission_policies(subject_id, action);

CREATE INDEX idx_resource_lookup
ON resource_permission_policies(asset_type, file_id);

-- @INFO: Create CHECK constraints on `resource_permission_policies` table for:
-- 		1. The `file_id`, `channel_id` and `message_id` cannot all be NULL for any row
-- 		2. If the value of `asset_type` is 'application:file', then `file_id` must not be NULL
-- 		3. If the value of `asset_type` is 'application:message' then `message_id` must not be NULL
--		4. If the value of `asset_type` is 'application:channel' then `channel_id` must not be NULL
--		5. If the value of `asset_type` is 'application:message` then `action` must not be 'granted:all-with-duration' or 'granted:view-with-duration'
--      6. If the value of `asset_type` is 'application:file' then `action` must not be 'granted:all-with-duration'
--      7. If the value of `asset_type` is 'application:channel' the `action` must not be 'granted:view-with-duration'

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 ON UPDATE RESTRICT,
	FOREIGN KEY (thread_message_id) REFERENCES thread_channel_messages(id) ON DELETE NO ACTION ON UPDATE RESTRICT,
	FOREIGN KEY (thread_channel_id) REFERENCES thread_channels(id) ON DELETE NO ACTION ON UPDATE RESTRICT
);

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 (position_latitude, position_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_trend_records (message_id, tag_id, author_id);

CREATE TABLE thread_reactions (
	user_id bigint NOT NULL,
  	message_id bigint NOT NULL,
  	type char(10) 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 ON UPDATE CASCADE,
  	FOREIGN KEY (message_id) REFERENCES thread_channel_messages(id) ON DELETE CASCADE ON UPDATE CASCADE,
	CONSTRAINT chk_type CHECK (type IN ('fav', 'clap', 'support', 'sad', 'repost', 'upvote', 'downvote', 'quote'))
);

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 char(8) 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),
	CONSTRAINT chk_access_type CHECK (access_type IN ('private', 'public')),
	FOREIGN KEY (owner_id) REFERENCES user_details(id) ON DELETE CASCADE ON UPDATE 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 ON UPDATE CASCADE,
	FOREIGN KEY (message_id) REFERENCES thread_channel_messages(id) ON DELETE CASCADE ON UPDATE 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 ON UPDATE 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 ON UPDATE CASCADE,
    FOREIGN KEY (blocked_user_id) REFERENCES user_details(id) ON DELETE CASCADE ON UPDATE CASCADE,
    -- Prevents the same user from being blocked twice by the same person
    UNIQUE KEY unq_single_block (blocker_user_id, blocked_user_id)
);

CREATE INDEX idx_acctblcks
ON account_blocks (blocked_at);

CREATE TABLE activity_notification_entries (
  id bigint NOT NULL AUTO_INCREMENT,
  entry_type varchar(17) NOT NULL,
  entry_subject_id bigint NOT NULL,
  entry_object_id_for_message bigint NULL,
  entry_object_id_for_user bigint NULL,
  entered_on TIMESTAMP NOT NULL,
  payload json NOT NULL DEFAULT (JSON_OBJECT()), -- '{ "activity_type": "mention", "source_entities": ["user_details", "thread_channels"], "source_entities_data": [{}, {}], "target_entities": ["user_details", "thread_channel_messages"], "target_entities_data": [{},{}] }', '{ "activity_type": "follow", "source_entities": ["user_details"], "source_entities_data": [{}], "target_entities": ["user_details"], "target_entities_data": [{}] }', { "activity_type": "reaction", "source_entities": ["user_details", "thread_reactions"], "source_entities_data": [{}, {}], "target_entities": ["thread_channel_messages"], "target_entities_data": [{}] }'
  PRIMARY KEY (id),
  CONSTRAINT chk_entry_type CHECK (entry_type IN ('event-follow', 'event-reaction', 'event-mention', 'event-reply')),
  CONSTRAINT fk_entry_subject FOREIGN KEY (entry_subject_id) REFERENCES user_details(id) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT fk_entry_object_msgs FOREIGN KEY (entry_object_id_for_message) REFERENCES thread_channel_messages(id) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT fk_entry_object_usrs FOREIGN KEY (entry_object_id_for_user) REFERENCES user_details(id) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT chk_entry_sanity CHECK (
  	(entry_type = 'event-mention'
	 	AND entry_object_id_for_message IS NOT NULL
	 	AND entry_object_id_for_user IS NOT NULL
	 	AND JSON_SCHEMA_VALID('{
            "type": "object",
            "properties": {
                "activity_type": { "type": "string" },
                "source_entities": { "type": "array" },
                "source_entities_data": { "type": "array" },
                "target_entities": { "type": "array" },
                "target_entities_data": { "type": "array" }
            },
            "required": ["activity_type"]
        }', payload)
	 )
	  
	 OR
	  
	 (entry_type = 'event-follow'
		AND entry_object_id_for_message IS NULL
	  	AND entry_object_id_for_user IS NOT NULL
	  	AND JSON_SCHEMA_VALID('{
            "type": "object",
            "properties": {
                "activity_type": { "type": "string" },
                "source_entities": { "type": "array" },
                "source_entities_data": { "type": "array" },
                "target_entities": { "type": "array" },
                "target_entities_data": { "type": "array" }
            },
            "required": ["activity_type"]
        }', payload)
	 )
	  
	 OR
	  
	 (entry_type = 'event-reaction'
	 	AND entry_object_id_for_message IS NOT NULL
	  	AND entry_object_id_for_user IS NULL
		AND JSON_SCHEMA_VALID('{
            "type": "object",
            "properties": {
                "activity_type": { "type": "string" },
                "source_entities": { "type": "array" },
                "source_entities_data": { "type": "array" },
                "target_entities": { "type": "array" },
                "target_entities_data": { "type": "array" }
            },
            "required": ["activity_type"]
        }', payload)
	 )
	  
	 OR
	  
	 (entry_type = 'event-reply'
	 	AND entry_object_id_for_message IS NOT NULL
	  	AND entry_object_id_for_user IS NULL
		AND JSON_SCHEMA_VALID('{
            "type": "object",
            "properties": {
                "activity_type": { "type": "string" },
                "source_entities": { "type": "array" },
                "source_entities_data": { "type": "array" },
                "target_entities": { "type": "array" },
                "target_entities_data": { "type": "array" }
            },
            "required": ["activity_type"]
        }', payload)
	 )
  )
);
 

CREATE TABLE notifications (
  id bigint NOT NULL AUTO_INCREMENT,
  notification_entry_id bigint NOT NULL,
  recipient_id bigint NOT NULL,
  delivered_at TIMESTAMP NULL,
  sent_at TIMESTAMP NULL,
  is_read tinyint(1) NOT NULL DEFAULT 0,
  notification_status_pending tinyint(1) NOT NULL DEFAULT 1,
  notification_status_sent tinyint(1) NOT NULL DEFAULT 0,
  notification_status_delivered tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  CONSTRAINT fk_notification_entry FOREIGN KEY (notification_entry_id) REFERENCES activity_notification_entries(id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT fk_notification_recipient_id FOREIGN KEY (recipient_id) REFERENCES user_details(id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
);











CREATE TRIGGER after_thread_assets_delete
	AFTER DELETE ON thread_assets
	FOR EACH ROW
    -- Delete all child rows referencing the deleted parent
    DELETE FROM resource_permission_policies 
    WHERE file_id = OLD.id;










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

INSERT INTO user_accounts (id, email, password, last_login_at, full_name, email_verified) VALUES
(2, 'tabbi@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 (id, topic, description) VALUES
(1, 'american movies', 'all hollywood movies in any genre'),
(2, '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,4]', NULL, 4, 1, 'follow-only');

INSERT INTO thread_channel_messages (id, slug_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);
 -- PostgreSQL: 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 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 = 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/13