> ## Documentation Index
> Fetch the complete documentation index at: https://daun-docs.syahmi.fyi/llms.txt
> Use this file to discover all available pages before exploring further.
# DB Schema Overview
> An overview of the data structures used in the database schema and their relationships
## 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. |
{/* | `emailHash` | varchar(64) | Not Null, Unique | Hash of the email address. | */}
### 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.
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.