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
| 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.
| 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.
