Threads Module

[andreyasko]

“Threads” module and a feed with hierarchical post-reply relationships, similar to the structure used in X (formerly Twitter). The module will allow posts and replies to behave as equal entities, capable of being parents or children, with efficient database queries and seamless user experience.

1. General Module Design

The “Threads” module will enable users to:

• Create standalone posts.

• Reply to posts (or replies) to form nested threads.

• View threads in a feed that dynamically organizes posts and their relationships.

• Handle operations such as editing, deleting, or navigating through threads.

2. Post and Reply Logic

Key Principles:

1. Equal Entity Structure: Both standalone posts and replies are stored in the same database table and treated equally.

2. Parent-Child Relationship: Each post or reply references its parent via a parent_id column.

• A standalone post has parent_id = NULL.

• A reply has parent_id = [ID of the parent post].

3. Root Post: The first post in a thread (with parent_id = NULL) is referred to as the root.

4. Nested Replies: Replies can have their own replies, forming a recursive hierarchy.

3. Database Design

Table: threads_posts

Column Name Data Type Description

id INT (PK, AUTO_INCREMENT) Unique identifier for each post.

parent_id INT (FK, NULLABLE) ID of the parent post. NULL for standalone posts.

root_id INT (FK) ID of the root post in the thread.

user_id INT ID of the user who created the post.

content TEXT Content of the post.

created_at TIMESTAMP Time when the post was created.

updated_at TIMESTAMP Time when the post was last updated.

status ENUM(‘active’, ‘deleted’) Status of the post for soft deletion.

Table: threads_feed

Column Name Data Type Description

id INT (PK, AUTO_INCREMENT) Unique identifier for the feed item.

user_id INT ID of the user subscribed to the feed.

post_id INT (FK) ID of the post in the thread.

created_at TIMESTAMP Time when the feed item was created.

4. Post Creation Logic

API Workflow:

1. Create Standalone Post:

• parent_id = NULL

• root_id = id of the created post.

2. Create Reply:

• parent_id = ID of the post being replied to.

• root_id = root_id of the parent post.

SQL Example:

-- Create a standalone post

INSERT INTO threads_posts (parent_id, root_id, user_id, content, created_at)

VALUES (NULL, NULL, 1, 'This is a standalone post.', NOW());

-- Retrieve the newly created post ID

SET @post_id = LAST_INSERT_ID();

-- Update the root_id to the same ID (self-referencing)

UPDATE threads_posts

SET root_id = @post_id

WHERE id = @post_id;

-- Create a reply to a post

INSERT INTO threads_posts (parent_id, root_id, user_id, content, created_at)

VALUES (5, 1, 2, 'This is a reply to post ID 5.', NOW());

5. Displaying Threads

Recursive Query for Full Thread:

To retrieve a full thread:

-- Get all posts in a thread by root_id

SELECT * FROM threads_posts

WHERE root_id = 1

ORDER BY created_at ASC;

Recursive Functionality:

Threads should be displayed hierarchically. This requires a recursive function in your application logic to:

1. Fetch all posts with the same root_id.

2. Organize them into a tree structure based on their parent_id.

6. Feed Design

Feed Retrieval:

The feed should:

1. Show the latest posts (root posts).

2. Allow navigation into individual threads.

Example SQL Query:

-- Get all root posts for the feed

SELECT * FROM threads_posts

WHERE parent_id IS NULL

ORDER BY created_at DESC

LIMIT 20;

7. Post Management

Editing:

Users can edit posts unless they are deleted. Update the content and updated_at fields.

SQL Example:

UPDATE threads_posts

SET content = 'Updated content.', updated_at = NOW()

WHERE id = 1 AND status = 'active';

Deletion:

Soft delete posts by updating the status column to deleted. Replies to a deleted post should remain visible but show the parent as “deleted.”

SQL Example:

-- Soft delete a post

UPDATE threads_posts

SET status = 'deleted'

WHERE id = 1;

-- Optional: Handle orphaned replies

SELECT * FROM threads_posts

WHERE parent_id = 1;

8. Performance Optimization

1. Indexing:

• Index root_id and parent_id to speed up thread and reply queries.

• Example: CREATE INDEX idx_root_id ON threads_posts(root_id);

2. Pagination:

• Use LIMIT/OFFSET for feed and thread retrieval.

• Lazy load nested replies for large threads.

3. Cache Frequent Threads:

• Use in-memory caching (e.g., Redis) for frequently accessed threads.

9. Expected Outcome

• A Threads module that supports standalone posts and threaded replies.

• A feed that dynamically displays posts and allows seamless navigation of threads.

• Efficient performance for both small and large-scale deployments.

[linear]

UNA-293 Threads Module