Database Schema

BuddyPress Hashtags creates two custom tables at plugin activation. Both tables use the WordPress table prefix (default wp_).

---

Tables

Table	Purpose
{prefix}bpht_hashtags	Aggregated hashtag registry: name, type, and total usage count
{prefix}bpht_hashtags_items	Per-item records linking a hashtag to a specific activity, post, page, or user follow

The schema version is stored in the bpht_db_version WordPress option. The current version is 1.1. Tables are created and upgraded via dbDelta() inside bpht_create_hashtag_table(), which runs on activation and on the admin_init / plugins_loaded hooks when the stored version is lower than the constant.

---

{prefix}bpht_hashtags

Stores one row per unique (ht_name, ht_type) combination. The ht_count column is incremented each time the hashtag is used; it is decremented (and the row deleted when count reaches 1) when the associated content is deleted.

Schema

CREATE TABLE wp_bpht_hashtags (
    ht_id        bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    ht_name      varchar(128),
    ht_type      varchar(28),
    ht_count     bigint(20) UNSIGNED NULL DEFAULT '0',
    ht_last_count TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (ht_id),
    UNIQUE KEY ht_name_type (ht_name, ht_type),
    KEY idx_ht_type (ht_type),
    KEY idx_ht_count (ht_count),
    KEY idx_ht_last_count (ht_last_count)
);

Columns

Column	Type	Notes
ht_id	bigint(20) UNSIGNED	Primary key, auto-increment
ht_name	varchar(128)	Hashtag text without #, stored as entered (case-sensitive in MySQL)
ht_type	varchar(28)	Content type — see values below
ht_count	bigint(20) UNSIGNED	Cumulative usage count across all content items
ht_last_count	TIMESTAMP	Timestamp of the most recent insert or update

ht_type values

Value	Source
buddypress	BuddyPress activities (current; written by bpht_save_activity_hashtags())
activity	BuddyPress activities (legacy; migrated to buddypress in v3.5.2)
bbpress	bbPress topics and replies
post	WordPress posts
page	WordPress pages
profile	User-followed hashtags (rows written when a member follows a hashtag via the settings page)

Both buddypress and activity represent BuddyPress activity content. After the v3.5.2 migration, new rows always use buddypress. Existing sites may still have activity rows until cleaned up.

Indexes

Name	Columns	Purpose
PRIMARY	ht_id	Primary key
ht_name_type	ht_name, ht_type	UNIQUE — prevents duplicate (name, type) pairs; used in existence checks
idx_ht_type	ht_type	Fast filter by content type
idx_ht_count	ht_count	Fast ORDER BY ht_count (widget queries)
idx_ht_last_count	ht_last_count	Fast ORDER BY recency

---

{prefix}bpht_hashtags_items

Stores one row per (user, content item, hashtag) usage event. This table drives two features:

1. Usage tracking — Each activity/post/page that contains a hashtag gets a row here, linked by item_id and type. The bpht_get_post_hashtags() function queries this table to build the list of hashtags known for a given post.

2. Follow hashtags — When a member follows a hashtag, a row is inserted with type = 'profile' and item_id = 0. The bpht_get_user_hashtags() function queries this table to retrieve a user’s followed hashtags.

Schema

CREATE TABLE wp_bpht_hashtags_items (
    id           int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id      bigint(20),
    item_id      bigint(20) UNSIGNED NULL DEFAULT '0',
    type         varchar(255),
    hashtag_items varchar(255),
    created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_user_id (user_id),
    KEY idx_item_id (item_id),
    KEY idx_type (type),
    KEY idx_hashtag_items (hashtag_items),
    KEY idx_user_item_type (user_id, item_id, type),
    KEY idx_created_date (created_date)
);

Columns

Column	Type	Notes
id	int(11) UNSIGNED	Primary key, auto-increment
user_id	bigint(20)	WordPress user ID of the person who authored the content or followed the hashtag
item_id	bigint(20) UNSIGNED	Activity ID, post ID, or other content identifier. 0 for followed hashtags.
type	varchar(255)	Same type codes as ht_type — see table above
hashtag_items	varchar(255)	Hashtag text without #
created_date	TIMESTAMP	When this record was created

Uniqueness behavior

There is no UNIQUE constraint on this table. Duplicate prevention is handled in PHP inside bpht_db_buddypress_hashtag_entry(): before inserting, it checks whether an identical (user_id, item_id, type, hashtag_items) row already exists and skips the insert if so.

Indexes

Name	Columns	Purpose
PRIMARY	id	Primary key
idx_user_id	user_id	Filter by user
idx_item_id	item_id	Filter by content item
idx_type	type	Filter by content type
idx_hashtag_items	hashtag_items	Filter by hashtag name
idx_user_item_type	user_id, item_id, type	Composite queries — used in the duplicate-prevention check
idx_created_date	created_date	Sort by creation date

---

Useful queries

All examples use $wpdb and $wpdb->prepare(). Replace wp_ with your actual table prefix.

Get the top hashtags for a content type

global $wpdb;
$table = $wpdb->prefix . 'bpht_hashtags';

$hashtags = $wpdb->get_results(
    $wpdb->prepare(
        "SELECT ht_name, ht_count
         FROM {$table}
         WHERE ht_type = %s
         ORDER BY ht_count DESC
         LIMIT %d",
        'buddypress',
        20
    )
);

Get hashtags a specific user is following

global $wpdb;
$items_table = $wpdb->prefix . 'bpht_hashtags_items';

$followed = $wpdb->get_col(
    $wpdb->prepare(
        "SELECT DISTINCT hashtag_items
         FROM {$items_table}
         WHERE user_id = %d AND type = %s",
        $user_id,
        'profile'
    )
);

Note: the type value for followed hashtags is 'profile', not 'follow'.

Get all hashtags indexed for a specific post

global $wpdb;
$items_table = $wpdb->prefix . 'bpht_hashtags_items';

$tags = $wpdb->get_col(
    $wpdb->prepare(
        "SELECT DISTINCT hashtag_items
         FROM {$items_table}
         WHERE item_id = %d AND type = %s",
        $post_id,
        'post'
    )
);

Check whether a hashtag exists in the registry

global $wpdb;
$table = $wpdb->prefix . 'bpht_hashtags';

$exists = $wpdb->get_var(
    $wpdb->prepare(
        "SELECT ht_id
         FROM {$table}
         WHERE ht_name = %s AND ht_type = %s",
        'wordpress',
        'buddypress'
    )
);

if ( $exists ) {
    // Hashtag exists.
}

Get recently active hashtags across all types

global $wpdb;
$table = $wpdb->prefix . 'bpht_hashtags';

$recent = $wpdb->get_results(
    $wpdb->prepare(
        "SELECT ht_name, ht_type, ht_count, ht_last_count
         FROM {$table}
         ORDER BY ht_last_count DESC
         LIMIT %d",
        10
    )
);

Count total distinct hashtags

global $wpdb;
$table = $wpdb->prefix . 'bpht_hashtags';

$count = $wpdb->get_var( "SELECT COUNT(*) FROM {$table}" ); // phpcs:ignore

Count hashtags for a specific type only

$count = $wpdb->get_var(
    $wpdb->prepare(
        "SELECT COUNT(*) FROM {$wpdb->prefix}bpht_hashtags WHERE ht_type = %s",
        'buddypress'
    )
);

---

How counts are maintained

Incrementing. bpht_db_buddypress_hashtag_entry() increments ht_count by 1 each time a new (user_id, item_id, hashtag_items) tuple is recorded. For BuddyPress activities the hashtag-to-activity linking is done in bpht_save_activity_hashtags() (hooked to bp_activity_after_save). For posts and pages it runs inside the wp_insert_post_data filter (priority 99).

Decrementing on delete.

• BuddyPress activity deleted: bpht_delete_buddypress_activity_hashtag_table() (hooked to bp_before_activity_delete) queries bpht_hashtags_items for the activity ID, decrements ht_count for each matching hashtag, and deletes the bpht_hashtags row when count drops to 1 or below.
• Post deleted: bpht_delete_buddypress_post_hashtag_table() (hooked to delete_post) parses the post content with a regex to find hashtags, then decrements or deletes accordingly.
• Comment deleted: bpht_deleted_comment_hashtag_table() (hooked to deleted_comment) follows the same pattern as the post handler.

Clearing all counts. The Manage tab in the admin provides buttons that fire AJAX handlers to delete all rows for a given type (bpht_clear_buddypress_hashtag_table, bpht_clear_post_hashtag_table, etc.). These delete rows from bpht_hashtags only, not from bpht_hashtags_items.

---

Backup and migration

Both tables use the standard WordPress prefix and are included automatically by any backup tool that exports the full database. When migrating a site, export both tables along with the WordPress options table (which stores bpht_db_version, bpht_plugin_version, and the legacy bpht_hashtags / bpht_bbpress_hashtags options used for the pre-3.x migration).

When importing on a new install with a different table prefix, update the bpht_db_version option if needed so the upgrade check does not run unnecessarily.