# FluentBoards Database Schema
FluentBoards Core AdvancedFluentBoards use custom database tables to store all the Boards data. Here are the list of database tables and it's schema to understand overall database design and related data attributes of each model.
# Schema Design
# Database Tables
# _fbs_boards Table
This table stores the basic information of a board.
| Column | Type | Comment |
|---|---|---|
| id | INT UNSIGNED Auto Increment | Primary key of the board |
| parent_id | INT UNSIGNED NULL | For SuperBoard like Project or Company, for sub-board etc. |
| title | TEXT NULL | Title of the board, it can be longer than 255 characters. |
| description | LONGTEXT NULL | Description of the board |
| type | VARCHAR(50) NULL | Type of the board, e.g., to-do, sales-pipeline, roadmap, task, etc. |
| currency | VARCHAR(50) NULL | Currency related to the board |
| background | TEXT NULL | Serialized array for background settings |
| settings | TEXT NULL | Serialized array for other board settings |
| created_by | INT UNSIGNED | ID of the user who created the board |
| archived_at | TIMESTAMP NULL | Timestamp when the board was archived |
| created_at | TIMESTAMP NULL | Timestamp when the board was created |
| updated_at | TIMESTAMP NULL | Timestamp when the board was last updated |
# _fbs_board_terms Table
This table is used for storing the board labels and stages.
| Column | Type | Comment |
|---|---|---|
| id | INT UNSIGNED Auto Increment | Primary key of the term |
| board_id | INT UNSIGNED | ID of the board associated with the term |
| title | VARCHAR(100) NULL | Title of the stage or label. In case of a label, the title can be null with only a color. |
| slug | VARCHAR(100) NULL | Slug of the stage or label |
| type | VARCHAR(50) NOT NULL DEFAULT 'stage' | Type of the term: 'stage' or 'label' |
| position | DECIMAL(10,2) NOT NULL DEFAULT '1' | Position of the stage or label. 1 = first, 2 = second, etc. |
| color | VARCHAR(50) NULL | Text color of the stage or label |
| bg_color | VARCHAR(50) NULL | Background color of the stage or label |
| settings | TEXT NULL | Serialized settings for the term |
| archived_at | TIMESTAMP NULL | Timestamp when the term was archived |
| created_at | TIMESTAMP NULL | Timestamp when the term was created |
| updated_at | TIMESTAMP NULL | Timestamp when the term was last updated |
# _fbs_tasks Table
This table is used for managing tasks within the board.
| Column | Type | Comment |
|---|---|---|
| id | INT UNSIGNED Auto Increment | Primary key of the task |
| parent_id | INT UNSIGNED NULL | Parent task ID if this is a subtask |
| board_id | INT UNSIGNED NULL | ID of the board the task is in |
| crm_contact_id | BIGINT UNSIGNED NULL | User ID, Contact ID, Deal ID, Subscriber ID, etc. |
| title | TEXT NULL | Title or name of the task; it can be longer than 255 characters. |
| slug | VARCHAR(255) NULL | Slug of the task |
| type | VARCHAR(50) NULL | Type of the task, e.g., task, deal, idea, to-do, etc. |
| status | VARCHAR(50) NULL DEFAULT 'open' | Status of the task: open, completed; for boards: won or lost for pipelines |
| stage_id | INT UNSIGNED NULL | ID of the stage the task is in |
| source | VARCHAR(50) NULL DEFAULT 'web' | Source of the task, e.g., web, funnel, contact-section, etc. |
| source_id | VARCHAR(255) NULL | Source ID related to the task |
| priority | VARCHAR(50) NULL DEFAULT 'low' | Priority of the task: low, medium, high |
| description | LONGTEXT NULL | Description of the task |
| lead_value | DECIMAL(10,2) DEFAULT 0.00 | Lead value associated with the task |
| created_by | BIGINT UNSIGNED NULL | ID of the user who created the task |
| position | DECIMAL(10,2) NOT NULL DEFAULT '1' | Position of the task within the board. 1 = first, 2 = second, etc. |
| comments_count | INT UNSIGNED NULL DEFAULT 0 | Number of comments associated with the task |
| issue_number | INT UNSIGNED NULL | Board-specific issue number to track the task |
| reminder_type | VARCHAR(100) NULL DEFAULT 'none' | Type of reminder set for the task |
| settings | TEXT NULL | Serialized settings for the task |
| remind_at | TIMESTAMP NULL | Timestamp when a reminder is set for the task |
| started_at | TIMESTAMP NULL | Timestamp when the task was started |
| due_at | TIMESTAMP NULL | Timestamp when the task is due |
| last_completed_at | TIMESTAMP NULL | Timestamp when the task was last completed |
| archived_at | TIMESTAMP NULL | Timestamp when the task was archived |
| created_at | TIMESTAMP NULL | Timestamp when the task was created |
| updated_at | TIMESTAMP NULL | Timestamp when the task was last updated |
# _fbs_task_metas Table
This table is used for storing metadata related to tasks.
| Column | Type | Comment |
|---|---|---|
| id | INT UNSIGNED Auto Increment | Primary key of the task meta |
| task_id | INT UNSIGNED | ID of the associated task |
| key | VARCHAR(100) | Key for the meta information |
| value | LONGTEXT NULL | Value of the meta information |
| created_at | TIMESTAMP NULL | Timestamp when the meta was created |
| updated_at | TIMESTAMP NULL | Timestamp when the meta was last updated |
# _fbs_attachments Table
This table stores information about attachments related to tasks.
| Column | Type | Comment |
|---|---|---|
| id | INT UNSIGNED Auto Increment | Primary key of the attachment |
| object_id | INT UNSIGNED | ID of the associated object (Task ID, Comment ID, or Board ID) |
| object_type | VARCHAR(100) DEFAULT 'TASK' | Type of the object (TASK, COMMENT, BOARD) |
| attachment_type | VARCHAR(100) NULL | Type of the attachment |
| file_path | TEXT NULL | File path of the attachment |
| full_url | TEXT NULL | Full URL of the attachment |
| settings | TEXT NULL | Serialized settings |
| title | VARCHAR(192) NULL | Title of the attachment |
| file_hash | VARCHAR(192) NULL | File hash for verifying integrity |
| driver | VARCHAR(100) DEFAULT 'local' | Storage driver (local, cloud, etc.) |
| status | VARCHAR(100) DEFAULT 'ACTIVE' | Status of the attachment (ACTIVE, INACTIVE, DELETED) |
| file_size | VARCHAR(100) NULL | Size of the file |
| created_at | TIMESTAMP NULL | Timestamp when the attachment was created |
| updated_at | TIMESTAMP NULL | Timestamp when the attachment was last updated |
# _fbs_comments Table
This table stores comments, notes, and replies related to tasks on boards.
| Column | Type | Comment |
|---|---|---|
| id | INT UNSIGNED Auto Increment | Primary key of the comment |
| board_id | INT UNSIGNED | ID of the associated board |
| task_id | INT UNSIGNED | ID of the associated task |
| parent_id | BIGINT UNSIGNED NULL | ID of the parent comment if it's a reply |
| type | VARCHAR(50) DEFAULT 'comment' | Type of the entry (comment, note, reply) |
| privacy | VARCHAR(50) DEFAULT 'public' | Privacy level of the comment (public, private) |
| status | VARCHAR(50) DEFAULT 'published' | Status of the comment (published, draft, spam) |
| author_name | VARCHAR(192) DEFAULT '' | Name of the comment author |
| author_email | VARCHAR(192) DEFAULT '' | Email of the comment author |
| author_ip | VARCHAR(50) DEFAULT '' | IP address of the comment author |
| description | TEXT NULL | Content of the comment |
| created_by | BIGINT UNSIGNED NULL | ID of the user who created the comment |
| created_at | TIMESTAMP NULL | Timestamp when the comment was created |
| updated_at | TIMESTAMP NULL | Timestamp when the comment was last updated |
# _fbs_activities Table
This table stores activity logs for tasks, including changes and actions taken on various objects.
| Column | Type | Comment |
|---|---|---|
| id | INT UNSIGNED Auto Increment | Primary key of the activity log |
| object_id | INT UNSIGNED | ID of the associated object (e.g., Task ID) |
| object_type | VARCHAR(100) | Type of the object (e.g., Task, Comment, Board) |
| action | VARCHAR(50) | Action performed (e.g., create, update, delete) |
| column | VARCHAR(50) NULL | The specific column that was changed (if applicable) |
| old_value | VARCHAR(50) NULL | The old value before the change |
| new_value | VARCHAR(50) NULL | The new value after the change |
| description | LONGTEXT NULL | Description of the activity or change |
| created_by | BIGINT UNSIGNED NULL | ID of the user who performed the action |
| settings | TEXT NULL | Serialized array for additional settings or metadata |
| created_at | TIMESTAMP NULL | Timestamp when the activity was created |
| updated_at | TIMESTAMP NULL | Timestamp when the activity was last updated |
# _fbs_notifications Table
This table is designed to store notifications related to task management, including various actions performed on tasks.
| Column | Type | Comment |
|---|---|---|
| id | INT UNSIGNED Auto Increment | Primary key of the notification |
| object_id | INT UNSIGNED | ID of the associated object (e.g., Task ID, Board ID) |
| object_type | VARCHAR(100) | Type of the object (e.g., Task, Comment, Board) |
| task_id | INT UNSIGNED NULL | ID of the task associated with the notification (if applicable) |
| action | VARCHAR(255) NULL | Action performed (e.g., task_created, priority_changed) |
| activity_by | BIGINT UNSIGNED | ID of the user who performed the action |
| description | LONGTEXT NULL | Description of the notification or action |
| settings | TEXT NULL | Serialized array for additional settings or metadata |
| created_at | TIMESTAMP NULL | Timestamp when the notification was created |
| updated_at | TIMESTAMP NULL | Timestamp when the notification was last updated |
# Keys and Indexes:
object_id: Index for the associated object ID.object_type: Index for the type of the associated object.activity_by: Index for the user who performed the action.
# _fbs_notification_users Table
This table is designed to track which users have received and read specific notifications.
| Column | Type | Comment |
|---|---|---|
| id | INT UNSIGNED Auto Increment | Primary key of the record |
| notification_id | INT UNSIGNED NULL | ID of the related notification |
| user_id | BIGINT UNSIGNED | ID of the user who received the notification |
| marked_read_at | TIMESTAMP NULL | Timestamp when the notification was marked as read |
| created_at | TIMESTAMP NULL | Timestamp when the record was created |
| updated_at | TIMESTAMP NULL | Timestamp when the record was last updated |
# Keys and Indexes:
notification_id: Index for the related notification ID.user_id: Index for the user ID who received the notification.
# _fbs_teams Table
This table stores information about teams within the system.
| Column | Type | Comment |
|---|---|---|
| id | INT UNSIGNED Auto Increment | Primary key of the record |
| parent_id | INT UNSIGNED NULL | ID of the parent team if this is a sub-team |
| title | VARCHAR(100) | Name of the team |
| description | TEXT NULL | Description of the team |
| type | VARCHAR(50) | Type of the team (e.g., project, department) |
| visibility | VARCHAR(50) DEFAULT 'VISIBLE' | Visibility of the team (VISIBLE/SECRET) |
| notifications_enabled | TINYINT(1) DEFAULT 1 | Whether notifications are enabled for the team |
| settings | TEXT NULL | Serialized settings for the team |
| created_by | BIGINT UNSIGNED | ID of the user who created the team |
| created_at | TIMESTAMP NULL | Timestamp when the team was created |
| updated_at | TIMESTAMP NULL | Timestamp when the team was last updated |
# Keys and Indexes:
type: Index for the type of team.visibility: Index for the visibility status of the team.created_by: Index for the ID of the user who created the team.parent_id: Index for the parent team ID.notifications_enabled: Index for the notification settings.title: Index for the team title.
# _fbs_metas Table
This table stores metadata associated with various objects in the system.
| Column | Type | Comment |
|---|---|---|
| id | INT UNSIGNED Auto Increment | Primary key of the record |
| object_id | INT UNSIGNED NULL | ID of the associated object (e.g., task, comment) |
| object_type | VARCHAR(100) | Type of the object (e.g., task, comment) |
| key | VARCHAR(100) NULL | Metadata key |
| value | LONGTEXT NULL | Metadata value |
| created_at | TIMESTAMP NULL | Timestamp when the metadata was created |
| updated_at | TIMESTAMP NULL | Timestamp when the metadata was last updated |
# Keys and Indexes:
object_id: Index for the ID of the associated object.
# _fbs_relations Table
This table manages relationships between different objects.
| Column | Type | Comment |
|---|---|---|
| id | INT UNSIGNED Auto Increment | Primary key of the record |
| object_id | INT UNSIGNED | ID of the primary object |
| object_type | VARCHAR(100) | Type of the primary object (e.g., task, comment) |
| foreign_id | INT UNSIGNED | ID of the related object |
| settings | TEXT NULL | Serialized settings for the relationship |
| preferences | TEXT NULL | Serialized preferences for the relationship |
| created_at | TIMESTAMP NULL | Timestamp when the relationship was created |
| updated_at | TIMESTAMP NULL | Timestamp when the relationship was last updated |
# Keys and Indexes:
object_type: Index for the type of the primary object.object_id: Index for the ID of the primary object.foreign_id: Index for the ID of the related object.
# _fbs_time_tracks Table
This table tracks time-related information for tasks.
| Column | Type | Comment |
|---|---|---|
| id | INT UNSIGNED Auto Increment | Primary key of the record |
| user_id | BIGINT UNSIGNED | ID of the user who tracked the time |
| board_id | INT UNSIGNED | ID of the board |
| task_id | INT UNSIGNED | ID of the task |
| started_at | TIMESTAMP NULL | Timestamp when the time tracking started |
| completed_at | TIMESTAMP NULL | Timestamp when the time tracking was completed |
| message | TEXT NULL | Optional message or note related to the time tracking |
| status | VARCHAR(50) NULL DEFAULT 'commited' | Status of the time track (e.g., committed) |
| working_minutes | INT UNSIGNED NOT NULL DEFAULT 0 | Total minutes worked |
| billable_minutes | INT UNSIGNED NOT NULL DEFAULT 0 | Minutes that are billable |
| is_manual | TINYINT(1) NOT NULL DEFAULT 0 | Indicates if the time track was entered manually |
| created_at | TIMESTAMP NULL | Timestamp when the record was created |
| updated_at | TIMESTAMP NULL | Timestamp when the record was last updated |
# Keys and Indexes:
user_id: Index for the ID of the user.status: Index for the status of the time track.task_id: Index for the ID of the task.board_id: Index for the ID of the board.
# _users Table
This table contains information about users, including login details, email, and registration data.
| Column | Type | Comment |
|---|---|---|
| ID | Integer | |
| user_login | String | |
| user_pass | String | |
| user_nicename | String | |
| user_email | String | |
| user_url | String | |
| user_registered | Date Time | |
| user_activation_key | String | |
| user_status | Integer | |
| display_name | String |