Database Diagram #375
Closed
Database Diagram #375
Conversation
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
imath
suggested changes
Oct 8, 2024
Comment on lines
+1
to
+166
| # BuddyPress Database Schema Overview | ||
|
|
||
| ## Introduction | ||
|
|
||
| BuddyPress is a powerful plugin that extends WordPress by adding community features, including activity streams, user profiles, groups, private messaging, and more. BuddyPress creates additional database tables to manage these community-centric features. This document provides an overview of the BuddyPress database schema to help developers understand and extend the data structure. | ||
|
|
||
| --- | ||
|
|
||
| ## Database Diagram | ||
|
|
||
| Below is a visual representation of the BuddyPress database schema: | ||
|
|
||
|  | ||
|
|
||
| # BuddyPress Database Schema - Detailed Text-based Design | ||
|
|
||
| ``` | ||
| +-----------------+ +-------------------+ | ||
| | wp_bp_activity | | wp_bp_friends | | ||
| +-----------------+ +-------------------+ | ||
| | id | | id | | ||
| | user_id | | initiator_user_id | | ||
| | component | | friend_user_id | | ||
| | type | | is_confirmed | | ||
| | action | | is_limited | | ||
| | content | | date_created | | ||
| | primary_link | +-------------------+ | ||
| | item_id | | | ||
| | secondary_item_id| v | ||
| | date_recorded | +-------------------+ | ||
| | hide_sitewide | | wp_bp_groups | | ||
| | mptt_left | +-------------------+ | ||
| | mptt_right | | id | | ||
| | is_spam | | creator_id | | ||
| +-----------------+ | name | | ||
| | slug | | ||
| +-------------------+ | description | | ||
| | wp_bp_activity_meta | | status | | ||
| +-------------------+ | parent_id | | ||
| | id | | enable_forum | | ||
| | activity_id | | date_created | | ||
| | meta_key | +-------------------+ | ||
| | meta_value | | ||
| +-------------------+ | ||
| ``` | ||
|
|
||
| ``` | ||
| +---------------------+ +---------------------+ | ||
| | wp_bp_messages | | wp_bp_notifications | | ||
| +---------------------+ +---------------------+ | ||
| | id | | id | | ||
| | thread_id | | user_id | | ||
| | sender_id | | item_id | | ||
| | subject | | secondary_item_id | | ||
| | message | | component_name | | ||
| | date_sent | | component_action | | ||
| +---------------------+ | date_notified | | ||
| | is_new | | ||
| +---------------------+ | ||
| ``` | ||
|
|
||
| ``` | ||
| +---------------------+ +---------------------+ | ||
| | wp_bp_messages_meta | | wp_bp_notifications_meta | | ||
| +---------------------+ +--------------------------+ | ||
| | id | | id | | ||
| | message_id | | notification_id | | ||
| | meta_key | | meta_key | | ||
| | meta_value | | meta_value | | ||
| +---------------------+ +--------------------------+ | ||
| ``` | ||
|
|
||
| ``` | ||
| +--------------------+ +-------------------+ | ||
| | wp_bp_groups | | wp_bp_groups_meta | | ||
| +--------------------+ +-------------------+ | ||
| | id | | id | | ||
| | creator_id | | group_id | | ||
| | name | | meta_key | | ||
| | slug | | meta_value | | ||
| | description | +-------------------+ | ||
| | status | | ||
| | parent_id | | ||
| | enable_forum | | ||
| | date_created | | ||
| +--------------------+ | ||
| ``` | ||
|
|
||
| ``` | ||
| +---------------------+ +------------------+ | ||
| | wp_bp_groups_members | | wp_bp_invitations| | ||
| +---------------------+ +------------------+ | ||
| | id | | id | | ||
| | group_id | | user_id | | ||
| | user_id | | inviter_id | | ||
| | inviter_id | | invitee_email | | ||
| | is_admin | | class | | ||
| | is_mod | | item_id | | ||
| | user_title | | secondary_item_id| | ||
| | date_modified | | type | | ||
| | comments | | content | | ||
| | is_confirmed | | date_modified | | ||
| | is_banned | | invite_sent | | ||
| | invite_sent | | accepted | | ||
| +---------------------+ +------------------+ | ||
| ``` | ||
|
|
||
| ``` | ||
| +------------------------+ +---------------------+ | ||
| | wp_bp_xprofile_data | | wp_bp_xprofile_fields | | ||
| +------------------------+ +---------------------+ | ||
| | id | | id | | ||
| | field_id | | group_id | | ||
| | user_id | | parent_id | | ||
| | value | | type | | ||
| | last_updated | | name | | ||
| +------------------------+ | description | | ||
| | is_required | | ||
| | is_default_option | | ||
| | field_order | | ||
| | option_order | | ||
| | order_by | | ||
| | can_delete | | ||
| +---------------------+ | ||
| ``` | ||
|
|
||
| ``` | ||
| +------------------------+ +------------------------+ | ||
| | wp_bp_xprofile_groups | | wp_bp_xprofile_meta | | ||
| +------------------------+ +------------------------+ | ||
| | id | | id | | ||
| | name | | object_id | | ||
| | description | | object_type | | ||
| | group_order | | meta_key | | ||
| | can_delete | | meta_value | | ||
| +------------------------+ +------------------------+ | ||
| ``` | ||
|
|
||
| ``` | ||
| +-------------------+ +----------------------+ | ||
| | wp_bp_user_blogs | | wp_bp_user_blogs_meta| | ||
| +-------------------+ +----------------------+ | ||
| | id | | id | | ||
| | user_id | | blog_id | | ||
| | blog_id | | meta_key | | ||
| +-------------------+ | meta_value | | ||
| +----------------------+ | ||
| ``` | ||
|
|
||
| ``` | ||
| +---------------------+ +--------------------+ | ||
| | wp_bp_optouts | | wp_bp_signups | | ||
| +---------------------+ +--------------------+ | ||
| | id | | signup_id | | ||
| | email_address_hash | | domain | | ||
| | user_id | | path | | ||
| | email_type | | title | | ||
| | date_modified | | user_login | | ||
| +---------------------+ | user_email | | ||
| | registered | | ||
| | activated | | ||
| | active | | ||
| | activation_key | | ||
| | meta | | ||
| +--------------------+ | ||
| ``` |
Contributor
There was a problem hiding this comment.
These are very interesting schema representations. But I believe we should keep it simple. The image would need to be edited for instance.
Comment on lines
+168
to
+205
| ## Activity Tables | ||
|
|
||
| ### wp_bp_activity | ||
|
|
||
| This table stores all activities (such as posts, comments, updates) made by users in BuddyPress. | ||
|
|
||
| - **Columns:** | ||
| - `id`: The unique ID for each activity entry. | ||
| - `user_id`: The ID of the user who performed the activity. | ||
| - `component`: Specifies which BuddyPress component (e.g., groups, members) the activity is associated with. | ||
| - `type`: Type of activity (e.g., new post, comment, group creation). | ||
| - `action`: Human-readable description of the action performed. | ||
| - `content`: Additional content (text) related to the activity. | ||
| - `primary_link`: A link to the activity item or content. | ||
| - `item_id`: ID associated with the primary item (group or other). | ||
| - `secondary_item_id`: Additional context ID, if applicable. | ||
| - `date_recorded`: When the activity was recorded. | ||
| - `hide_sitewide`: Whether the activity is hidden from the sitewide activity stream. | ||
| - `mptt_left` & `mptt_right`: Used for threaded comments/activities. | ||
| - `is_spam`: Marks the activity as spam. | ||
|
|
||
| #### Example Query: | ||
|
|
||
| ```sql | ||
| SELECT * FROM wp_bp_activity WHERE user_id = 1; | ||
| ``` | ||
|
|
||
| ### wp_bp_activity_meta | ||
|
|
||
| Stores metadata associated with activity entries. | ||
|
|
||
| - **Columns:** | ||
| - `id`: Meta ID. | ||
| - `activity_id`: The ID of the associated activity. | ||
| - `meta_key`: Metadata key. | ||
| - `meta_value`: Metadata value (usually stored as serialized data). | ||
|
|
||
| --- |
Contributor
There was a problem hiding this comment.
I believe this way of describing the schema is the easier to keep updated. I suggest to use tables and include the data type.
Suggested change
| ## Activity Tables | |
| ### wp_bp_activity | |
| This table stores all activities (such as posts, comments, updates) made by users in BuddyPress. | |
| - **Columns:** | |
| - `id`: The unique ID for each activity entry. | |
| - `user_id`: The ID of the user who performed the activity. | |
| - `component`: Specifies which BuddyPress component (e.g., groups, members) the activity is associated with. | |
| - `type`: Type of activity (e.g., new post, comment, group creation). | |
| - `action`: Human-readable description of the action performed. | |
| - `content`: Additional content (text) related to the activity. | |
| - `primary_link`: A link to the activity item or content. | |
| - `item_id`: ID associated with the primary item (group or other). | |
| - `secondary_item_id`: Additional context ID, if applicable. | |
| - `date_recorded`: When the activity was recorded. | |
| - `hide_sitewide`: Whether the activity is hidden from the sitewide activity stream. | |
| - `mptt_left` & `mptt_right`: Used for threaded comments/activities. | |
| - `is_spam`: Marks the activity as spam. | |
| #### Example Query: | |
| ```sql | |
| SELECT * FROM wp_bp_activity WHERE user_id = 1; | |
| ``` | |
| ### wp_bp_activity_meta | |
| Stores metadata associated with activity entries. | |
| - **Columns:** | |
| - `id`: Meta ID. | |
| - `activity_id`: The ID of the associated activity. | |
| - `meta_key`: Metadata key. | |
| - `meta_value`: Metadata value (usually stored as serialized data). | |
| --- | |
| # Activity database tables | |
| When first activated, the Activity component creates 2 tables. One to store activity stream items (`{$table_prefix}bp_activity`), the other to store metadata about items (`{$table_prefix}bp_activity_meta`). | |
| > [!NOTE] | |
| > The `{$table_prefix}` value is `wp_` by default but it can be customized within the your WordPress [wp-config.php file](https://developer.wordpress.org/advanced-administration/wordpress/wp-config/#table-prefix). | |
| > [!IMPORTANT] | |
| > The `{$table_prefix}bp_activity` is always created even if the Activity component is not active. BuddyPress is using this table to log members last connexion date/time. | |
| ## `{$table_prefix}bp_activity` | |
| This table stores all activities (such as posts, comments, updates) made by users in BuddyPress. It also logs user interactions or community events. | |
| | Name | Type | Description | | |
| | --- | --- | --- | | |
| | id | BIGINT | The unique ID for each activity entry. | | |
| | user_id | BIGINT | The ID of the user who performed the activity. | | |
| | component | VARCHAR | Specifies which BuddyPress component (e.g., groups, members) the activity is associated with. | | |
| | type | VARCHAR | Type of activity (e.g., new post, comment, group creation). | | |
| | action | TEXT | Human-readable description of the action performed[^1]. | | |
| | content | LONGTEXT | Main content of the activity. | | |
| | primary_link | TEXT | The link to the activity item[^2]. | | |
| | item_id | BIGINT | ID associated with the primary item (group or other). | | |
| | secondary_item_id | BIGINT | Additional context ID, if applicable. | | |
| | date_recorded | DATETIME | When the activity was recorded. | | |
| | hide_sitewide | TINYINT | Whether the activity is hidden from the sitewide activity stream. | | |
| | mptt_left | INT | Node boundary start for activity or activity comment. | | |
| | mptt_right | INT | Node boundary end for activity or activity comment. | | |
| | is_spam | TINYINT | Whether the activity item is marked as spam. | |
| ## `{$table_prefix}bp_activity_meta` | |
| Stores metadata associated with activity entries. | |
| | Name | Type | Description | | |
| | --- | --- | --- | | |
| | id | BIGINT | Meta ID | | |
| | activity_id | BIGINT | The ID of the associated activity. | | |
| | meta_key | `VARCHAR` | Metadata key. | | |
| | meta_value | LONGTEXT | Metadata value[^3]. | | |
| [^1]: BuddyPress is re-generating this action during runtime to make it translatable. | |
| [^2]: BuddyPress may re-generate this link at runtime for specific activity types, e.g.: it uses post's permalink for `new_blog_post` typed activities. | |
| [^3]: Arrays or Objects are stored as serialized data. |
I also think we should split this page to use a specific page for each component inside docs/developer/components/activity/db-schema.md for instance.
Comment on lines
+207
to
+227
| ## Friends Tables | ||
|
|
||
| ### wp_bp_friends | ||
|
|
||
| This table manages the friendship connections between users. | ||
|
|
||
| - **Columns:** | ||
| - `id`: Unique ID for each friendship connection. | ||
| - `initiator_user_id`: The user who sent the friend request. | ||
| - `friend_user_id`: The user who received the friend request. | ||
| - `is_confirmed`: Whether the friendship has been confirmed (1) or is pending (0). | ||
| - `is_limited`: Whether the friendship has any limitations. | ||
| - `date_created`: Timestamp of when the friendship was initiated. | ||
|
|
||
| #### Example Query: | ||
|
|
||
| ```sql | ||
| SELECT * FROM wp_bp_friends WHERE initiator_user_id = 1; | ||
| ``` | ||
|
|
||
| --- |
Contributor
There was a problem hiding this comment.
It should be in docs/developer/components/friends/db-schema.md and use a table like the what I've written for the Activity above.
Comment on lines
+229
to
+279
| ## Groups Tables | ||
|
|
||
| ### wp_bp_groups | ||
|
|
||
| This table stores the details of groups created in BuddyPress. | ||
|
|
||
| - **Columns:** | ||
| - `id`: Unique ID for the group. | ||
| - `creator_id`: User ID of the group creator. | ||
| - `name`: Name of the group. | ||
| - `slug`: Unique slug for the group. | ||
| - `description`: Group description. | ||
| - `status`: Group visibility status (public, private, hidden). | ||
| - `parent_id`: Parent group ID (if applicable). | ||
| - `enable_forum`: Whether the group has forums enabled. | ||
| - `date_created`: When the group was created. | ||
|
|
||
| ### wp_bp_groups_groupmeta | ||
|
|
||
| Stores metadata associated with groups. | ||
|
|
||
| - **Columns:** | ||
| - `id`: Unique ID for group metadata. | ||
| - `group_id`: The ID of the associated group. | ||
| - `meta_key`: Metadata key. | ||
| - `meta_value`: Metadata value (usually stored as serialized data). | ||
|
|
||
| ### wp_bp_groups_members | ||
|
|
||
| Manages membership of users in groups. | ||
|
|
||
| - **Columns:** | ||
| - `id`: Unique membership ID. | ||
| - `group_id`: The ID of the group. | ||
| - `user_id`: The ID of the user. | ||
| - `inviter_id`: The ID of the user who invited the member (if applicable). | ||
| - `is_admin`: Whether the user is an admin of the group. | ||
| - `is_mod`: Whether the user is a moderator of the group. | ||
| - `user_title`: The role or title of the user within the group. | ||
| - `date_modified`: When the membership was last modified. | ||
| - `is_confirmed`: Whether the membership is confirmed. | ||
| - `is_banned`: Whether the user is banned from the group. | ||
| - `invite_sent`: Whether an invitation has been sent. | ||
|
|
||
| #### Example Query: | ||
|
|
||
| ```sql | ||
| SELECT * FROM wp_bp_groups_members WHERE group_id = 2; | ||
| ``` | ||
|
|
||
| --- |
Contributor
There was a problem hiding this comment.
It should be in docs/developer/components/groups/db-schema.md and use a table like the what I've written for the Activity above.
Comment on lines
+281
to
+305
| ## Messaging Tables | ||
|
|
||
| ### wp_bp_messages_messages | ||
|
|
||
| Stores private messages sent between users. | ||
|
|
||
| - **Columns:** | ||
| - `id`: Unique message ID. | ||
| - `thread_id`: ID of the conversation thread. | ||
| - `sender_id`: User ID of the message sender. | ||
| - `subject`: Subject of the message. | ||
| - `message`: The actual message content. | ||
| - `date_sent`: When the message was sent. | ||
|
|
||
| ### wp_bp_messages_meta | ||
|
|
||
| Stores metadata associated with messages. | ||
|
|
||
| - **Columns:** | ||
| - `id`: Unique meta ID. | ||
| - `message_id`: The ID of the associated message. | ||
| - `meta_key`: Metadata key. | ||
| - `meta_value`: Metadata value. | ||
|
|
||
| --- |
Contributor
There was a problem hiding this comment.
It should be in docs/developer/components/messages/db-schema.md and use a table like the what I've written for the Activity above.
wp_bp_messages_recipients table is missing
Comment on lines
+307
to
+333
| ## Notifications Tables | ||
|
|
||
| ### wp_bp_notifications | ||
|
|
||
| Stores notifications for user actions. | ||
|
|
||
| - **Columns:** | ||
| - `id`: Unique notification ID. | ||
| - `user_id`: The ID of the user receiving the notification. | ||
| - `item_id`: Primary item related to the notification. | ||
| - `secondary_item_id`: Secondary item, if applicable. | ||
| - `component_name`: Name of the BuddyPress component (e.g., groups, messages). | ||
| - `component_action`: Action triggering the notification (e.g., group invite, message received). | ||
| - `date_notified`: When the notification was created. | ||
| - `is_new`: Whether the notification is new. | ||
|
|
||
| ### wp_bp_notifications_meta | ||
|
|
||
| Stores metadata for notifications. | ||
|
|
||
| - **Columns:** | ||
| - `id`: Unique meta ID. | ||
| - `notification_id`: The ID of the associated notification. | ||
| - `meta_key`: Metadata key. | ||
| - `meta_value`: Metadata value. | ||
|
|
||
| --- |
Contributor
There was a problem hiding this comment.
It should be in docs/developer/components/notifications/db-schema.md and use a table like the what I've written for the Activity above.
Comment on lines
+335
to
+377
| ## XProfile (Extended Profiles) Tables | ||
|
|
||
| ### wp_bp_xprofile_data | ||
|
|
||
| Stores custom profile data for users. | ||
|
|
||
| - **Columns:** | ||
| - `id`: Unique profile data ID. | ||
| - `field_id`: ID of the profile field. | ||
| - `user_id`: ID of the user. | ||
| - `value`: Value of the profile field for the user. | ||
| - `last_updated`: Timestamp of the last update. | ||
|
|
||
| ### wp_bp_xprofile_fields | ||
|
|
||
| Stores the fields available in extended profiles. | ||
|
|
||
| - **Columns:** | ||
| - `id`: Unique field ID. | ||
| - `group_id`: ID of the group the field belongs to. | ||
| - `parent_id`: Parent field ID (for hierarchical fields). | ||
| - `type`: The type of field (e.g., text, checkbox, select). | ||
| - `name`: The name of the field. | ||
| - `description`: Description of the field. | ||
| - `is_required`: Whether the field is required. | ||
| - `is_default_option`: Whether this is the default option. | ||
| - `field_order`: The order of the field. | ||
| - `option_order`: The order of field options. | ||
| - `order_by`: The sorting method for the field. | ||
| - `can_delete`: Whether the field can be deleted. | ||
|
|
||
| ### wp_bp_xprofile_groups | ||
|
|
||
| Stores groups of profile fields. | ||
|
|
||
| - **Columns:** | ||
| - `id`: Unique group ID. | ||
| - `name`: Name of the group. | ||
| - `description`: Description of the group. | ||
| - `group_order`: The order of the group. | ||
| - `can_delete`: Whether the group can be deleted. | ||
|
|
||
| --- |
Contributor
There was a problem hiding this comment.
It should be in docs/developer/components/groups/db-schema.md and use a table like the what I've written for the Activity above.
Comment on lines
+379
to
+384
| ## Other BuddyPress Tables | ||
|
|
||
| - **wp_bp_user_blogs**: Stores user blog associations in multisite. | ||
| - **wp_bp_user_blogs_blogmeta**: Metadata for user blogs. | ||
| - **wp_bp_invitations**: Stores invitations sent by users. | ||
| - **wp_bp_optouts**: Stores opt-out preferences for users who do not want to receive emails. |
Contributor
There was a problem hiding this comment.
It should be in docs/developer/components/blogs/db-schema.md and use a table like the what I've written for the Activity above.
dcavins
pushed a commit
to dcavins/buddypress-wp-svn
that referenced
this pull request
Nov 3, 2024
Props vapvarun See buddypress/bp-documentation#272 Closes buddypress/buddypress#375 git-svn-id: http://buddypress.svn.wordpress.org/trunk@14046 cdf35c40-ae34-48e0-9cc9-0c9da1808c22
emaralive
pushed a commit
to emaralive/omt-buddypress-sync
that referenced
this pull request
Nov 2, 2025
Props vapvarun See buddypress/bp-documentation#272 Closes buddypress/buddypress#375 git-svn-id: https://buddypress.svn.wordpress.org/trunk@14046 cdf35c40-ae34-48e0-9cc9-0c9da1808c22
emaralive
pushed a commit
to emaralive/buddypress-sync
that referenced
this pull request
Nov 3, 2025
Props vapvarun See buddypress/bp-documentation#272 Closes buddypress/buddypress#375 git-svn-id: https://buddypress.svn.wordpress.org/trunk@14046 cdf35c40-ae34-48e0-9cc9-0c9da1808c22
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
2 participants
Add this suggestion to a batch that can be applied as a single commit.
This suggestion is invalid because no changes were made to the code.
Suggestions cannot be applied while the pull request is closed.
Suggestions cannot be applied while viewing a subset of changes.
Only one suggestion per line can be applied in a batch.
Add this suggestion to a batch that can be applied as a single commit.
Applying suggestions on deleted lines is not supported.
You must change the existing code in this line in order to create a valid suggestion.
Outdated suggestions cannot be applied.
This suggestion has been applied or marked resolved.
Suggestions cannot be applied from pending reviews.
Suggestions cannot be applied on multi-line comments.
Suggestions cannot be applied while the pull request is queued to merge.
Suggestion cannot be applied right now. Please check back later.
This document outlines the structure of BuddyPress database tables, providing developers with the information needed to understand, query, and extend the BuddyPress data model.
Trac ticket: buddypress/bp-documentation#272
This Pull Request is for code review only. Please keep all other discussion in the BuddyPress Trac ticket. Do not merge this Pull Request. See GitHub Pull Requests for Code Review in the WordPress Core Handbook for more details.