DB Schema Overview

User Table

Description: Stores user information.

Column Name	Type	Constraints	Description
`id`	serial	Primary Key	Unique identifier for the user.
`name`	text	Not Null	Name of the user (encrypted).
`username`	text	Not Null, Unique	Username of the user.
`email`	text	Not Null, Unique	User’s email address (encrypted).
`phone`	text	Unique	User’s phone number (encrypted).
`image`	text	Not Null	URL of the user’s profile image (encrypted).
`bio`	text		User’s biography.
`role`	enum `user_role`	Not Null, Default: `user`	Role of the user (`admin` or `user`).
`phoneVerifiedAt`	timestamp		Timestamp when the phone was verified.
`verifiedAt`	timestamp		Timestamp when the user was verified.
`createdAt`	timestamp	Not Null, Default Now	Timestamp when the user was created.
`code`	text		Verification code.
`codeExpiredAt`	timestamp		Timestamp when the verification code expires.
`tags`	text[]	Not Null, Default: empty array	Array of tags associated with the user.
`isSuspended`	boolean	Not Null, Default: false	Indicates if the user is suspended.

Relationships

Sessions: A user can have multiple sessions.
Posts: A user can create multiple posts.
Following: A user can follow other users.
Followers: A user can be followed by other users.

Session Table

Description: Stores session information for users.

Column Name	Type	Constraints	Description
`id`	text	Primary Key	Unique session identifier.
`userId`	integer	Not Null, Foreign Key	References `user.id`.
`expiresAt`	timestamp	Not Null	Timestamp when the session expires.

Relationships

User: Each session is associated with one user.

Post Table

Description: Stores posts created by users.

Column Name	Type	Constraints	Description
`id`	serial	Primary Key	Unique identifier for the post.
`snowflakeId`	bigint	Not Null	Unique identifier for the post (Snowflake ID).
`userId`	integer	Not Null, Foreign Key	References `user.id`.
`content`	text		Content of the post.
`createdAt`	timestamp	Not Null, Default Now	Timestamp when the post was created.
`updatedAt`	timestamp	Not Null, Default Now	Timestamp when the post was last updated.
`type`	enum `post_type`	Not Null	Type of the post (e.g., `post`, `quote`, `reply`,`repost`).
`originalId`	integer	Foreign Key	References the original post if it’s a reply, repost or quote.
`parentId`	integer	Foreign Key	References the parent post if it’s a reply, repost or quote.
`hashtags`	text[]	Not Null, Default: empty array	Array of hashtags associated with the post.
`repliesCount`	integer	Not Null, Default: 0	Number of replies to the post.
`repostsCount`	integer	Not Null, Default: 0	Number of reposts of the post.
`reposts`	integer[]	Not Null, Default: empty array	Array of userIds reposted.
`isSuspended`	boolean	Not Null, Default: false	Indicates if the user is suspended.

Relationships

User: Each post is created by one user.
Original Post: Posts can reference other posts as the original post.
Parent Post: Posts can reference other posts as the parent post.
Children: A post can have multiple child posts.
Media: Posts can have multiple media attachments.
Notifications: Posts can trigger notifications.
Links: Posts may have multiple links.

Post Child Table

This table is reserved only for Post type reply

Description: Stores the relationships between parent and child posts.

Column Name	Type	Constraints	Description
`id`	serial	Primary Key	Unique identifier for the record.
`parentId`	integer	Not Null, Foreign Key	References the parent post.
`childId`	integer	Not Null, Foreign Key	References the child post.
`type`	enum `post_type`	Not Null	Type of the relationship.

Relationships

Parent Post: Each child record references a parent post.
Child Post: Each child record references a child post.

Post Media Table

Although there is no limit at the database level, we put a limit of 4 media files per post via backend validation.

Description: Stores media attachments for posts.

Column Name	Type	Constraints	Description
`id`	serial	Primary Key	Unique identifier for the media.
`postId`	integer	Not Null, Foreign Key	References the post.
`mediaUrl`	text	Not Null	URL of the media.
`mimeType`	text	Not Null	MIME type of the media.
`updatedAt`	timestamp	Not Null, Default Now	Timestamp when the media was last updated.

Relationships

Post: Each media record is associated with one post.

Follow Table

Description: Stores follow relationships between users.

Column Name	Type	Constraints	Description
`id`	serial	Primary Key	Unique identifier for the follow record.
`followerId`	integer	Not Null, Foreign Key	References the user who follows.
`followedId`	integer	Not Null, Foreign Key	References the user who is followed.

Relationships

Follower: Each follow record references a follower user.
Followed: Each follow record references a followed user.

Notification Table

Description: Stores notifications for users.

Column Name	Type	Constraints	Description
`id`	serial	Primary Key	Unique identifier for the notification.
`userId`	integer	Not Null, Foreign Key	References the user who receives the notification.
`actorId`	integer	Not Null, Foreign Key	References the user who triggered the notification.
`type`	enum `post_type`	Not Null	Type of the notification (e.g., `mention`,`follow`, `quote`, `reply`,`repost`).
`postId`	integer	Foreign Key	References the post associated with the notification.
`createdAt`	timestamp	Not Null, Default Now	Timestamp when the notification was created.
`read`	boolean	Not Null, Default: false	Indicates whether the notification has been read.

Relationships

User: Each notification is associated with the user who receives it.
Actor: Each notification references the user who triggered it.
Post: Each notification can be associated with a post.

Link Table

Description: Stores metadata for links shared in posts.

Column Name	Type	Constraints	Description
`url`	text	Primary Key	The URL of the link.
`title`	text		The title of the linked content.
`description`	text		A description of the linked content.
`image`	text		URL of an image associated with the link.

Relationships

Post Links: A link can be associated with multiple posts.

Post Link Table

Description: Associates links with posts.

Column Name	Type	Constraints	Description
`url`	text	Not Null, Foreign Key	References `link.url`.
`postId`	integer	Not Null, Foreign Key	References `post.id`.
`userId`	integer	Not Null, Foreign Key	References `user.id`.

Relationships

Post: Each post link record is associated with one post.
User: Each post link record is associated with one user.
Link: Each post link record references one link.

Report Table

Description: Stores reports made by users.

Column Name	Type	Constraints	Description
`id`	serial	Primary Key	Unique identifier for the report.
`reporterUserId`	integer	Not Null, Foreign Key	References the user who made the report.
`targetUserId`	integer	Not Null, Foreign Key	References the user being reported.
`targetPostId`	integer	Foreign Key	References the post being reported, if any.
`reason`	enum `report_reason`	Not Null	Reason for the report.
`status`	enum `report_status`	Not Null, Default: `reviewing`	Current status of the report.
`notes`	text		Additional notes about the report.
`createdAt`	timestamp	Not Null, Default Now	Timestamp when the report was created.
`updatedAt`	timestamp	Not Null, Default Now	Timestamp when the report was last updated.

Relationships

Reporter: Each report is associated with the user who made it.
Target User: Each report is associated with the user being reported.
Target Post: A report may be associated with a specific post.
Report Actions: A report can have multiple actions taken on it.

Report Reason & Status Enums

Refer to Enum & JSON list for more info

Report Action Table

Description: Stores actions taken on reports.

Column Name	Type	Constraints	Description
`id`	serial	Primary Key	Unique identifier for the report action.
`reportId`	integer	Not Null, Foreign Key	References the associated report.
`actionTaken`	enum `report_status`	Not Null	The action taken on the report.
`actionTakenBy`	integer	Not Null, Foreign Key	References the admin who took the action.
`actionTakenAt`	timestamp	Not Null, Default Now	Timestamp when the action was taken.
`notes`	text		Additional notes about the action taken.

Relationships

Report: Each report action is associated with one report.
Admin: Each report action is associated with the admin who took the action.

Notes and Warnings

Ensure data integrity by using foreign key constraints to maintain relationships between tables.
Be cautious with unique and uniqueIndex constraints to avoid duplication issues.
Use appropriate data types for timestamps and enums to ensure compatibility and correct representation.

​User Table

​Relationships

​Session Table

​Relationships

​Post Table

​Relationships

​Post Child Table

​Relationships

​Post Media Table

​Relationships

​Follow Table

​Relationships

​Notification Table

​Relationships

​Link Table

​Relationships

​Post Link Table

​Relationships

​Report Table

​Relationships

Report Reason & Status Enums

​Report Action Table

​Relationships

​Notes and Warnings

User Table

Relationships

Session Table

Relationships

Post Table

Relationships

Post Child Table

Relationships

Post Media Table

Relationships

Follow Table

Relationships

Notification Table

Relationships

Link Table

Relationships

Post Link Table

Relationships

Report Table

Relationships

Report Action Table

Relationships

Notes and Warnings