BuddyNext - community engine for WordPress

Schema: Auth Tokens, Search Index, and Webhooks

Free & Pro

This page documents the bn_* tables for verification/security tokens, the unified full-text search index, and the inbound/outbound webhook surface. All tables are created in BuddyNext\Core\Installer::schema() via dbDelta(). The exact tables that exist in these domains are listed below - none are invented.

The front-end login flow backed by the bn_* verification and security-token tables on this page

Overview / Contract

Tables documented here, exactly as they exist in the installer:

Domain Table Purpose
Auth / security tokens bn_verify_tokens Single token store for email verification, email-change confirmation, and 2FA - discriminated by the type column
Search bn_search_index Unified searchable mirror of posts/profiles/spaces with a FULLTEXT index
Webhooks (inbound log) bn_webhook_log Log of inbound webhook/integration events received
Webhooks (outbound config) bn_outbound_webhooks Registered outbound endpoints the site POSTs events to
Webhooks (outbound delivery log) bn_outbound_webhook_log Per-attempt delivery log for outbound webhooks

Note on auth/2FA: There is exactly one token table, bn_verify_tokens. Email verification, email-change confirmation, and two-factor tokens all share it and are distinguished by the type column (type defaults to email_verify). There is no separate bn_2fa_tokens / bn_two_factor / bn_auth_codes table in the installer.

Scale-Contract rules that shaped these tables:

  • FULLTEXT for search, with a transaction-safe fallback. bn_search_index carries a FULLTEXT KEY ft_search (title, content) added after dbDelta() (dbDelta does not manage FULLTEXT reliably). When the index is absent (notably under the PHPUnit harness, where it is actively dropped), SearchService detects this via has_fulltext_index() and runs a LIKE fallback.
  • Single-row token lookup by unique token. bn_verify_tokens.token is unique so verification is one indexed read.
  • Delivery log split from config. Outbound webhook endpoints (bn_outbound_webhooks) are configuration; each send attempt is appended to bn_outbound_webhook_log so retries and failures are auditable without bloating the config row.

bn_verify_tokens

One-time security tokens. The type column lets a single table serve email verification, email-change confirmation, and two-factor flows. Tokens are single-use and time-boxed via expires_at.

Column Type Notes
id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT Primary key.
user_id BIGINT(20) UNSIGNED NOT NULL The member the token belongs to. References wp_users.ID.
token VARCHAR(64) NOT NULL The opaque token value sent to the user. Unique.
type VARCHAR(32) NOT NULL DEFAULT 'email_verify' Token purpose, for example email_verify, email_change_confirm, or a 2FA type. Defaults to email_verify.
expires_at DATETIME NOT NULL Hard expiry; expired tokens are rejected and pruned.
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP When the token was issued.

Indexes:

  • PRIMARY KEY (id).
  • UNIQUE KEY token (token) - the verification lookup is a single indexed read on the token value.
  • KEY user_type (user_id, type) - find/invalidate a user's existing tokens of a given type before issuing a new one.

Relationships: user_id -> wp_users.ID. The matching email templates (email_verify, email_change_confirm) live in bn_email_templates and reference {{verify_url}}, which embeds the token.

bn_search_index

A denormalized, searchable mirror of indexable content (posts, profiles, spaces, and other object types). Keeping search in its own table lets one query span every content type and lets the FULLTEXT index live where it is needed without touching the source tables. Reindexing is scheduled on activation via SearchService::schedule_reindex_all().

Column Type Notes
id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT Primary key.
object_type VARCHAR(32) NOT NULL The kind of indexed object (for example post, profile, space).
object_id BIGINT(20) UNSIGNED NOT NULL Source row id within that object type.
title VARCHAR(500) NOT NULL DEFAULT '' Indexed title text (FULLTEXT).
content LONGTEXT DEFAULT NULL Indexed body text (FULLTEXT).
author_id BIGINT(20) UNSIGNED DEFAULT NULL Author/owner of the object, for filtering.
space_id BIGINT(20) UNSIGNED DEFAULT NULL Owning space, when the object belongs to one.
visibility ENUM('public','private') NOT NULL DEFAULT 'public' Visibility gate applied to search results.
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP When the index row was created.
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP Last reindex time; used for ordering.

Indexes:

  • PRIMARY KEY (id).
  • UNIQUE KEY object (object_type, object_id) - one index row per source object; the upsert key for reindexing.
  • KEY visibility_type (visibility, object_type) - filter results by visibility and type.
  • KEY author (author_id) - "content by this member".
  • KEY space (space_id) - "content in this space".
  • KEY updated_order (updated_at) - recency ordering.
  • FULLTEXT KEY ft_search (title, content) - added by a direct ALTER TABLE after dbDelta(). Under the test harness this index is dropped so MATCH/AGAINST is never run against uncommitted fixture rows.

Relationships: object_id references the source table named by object_type; author_id -> wp_users.ID; space_id -> bn_spaces.id.

-- The FULLTEXT search query (when the index is present)
SELECT object_type, object_id, title
FROM wp_bn_search_index
WHERE MATCH (title, content) AGAINST (%s IN NATURAL LANGUAGE MODE)
  AND visibility = 'public'
ORDER BY updated_at DESC;

Tip: Always gate a search through SearchService::has_fulltext_index(). On engines or environments without the FULLTEXT index, MATCH/AGAINST errors; the service routes to a LIKE query instead so search still works.

bn_webhook_log

Log of inbound webhook / integration events the site received. This is the receive-side audit trail - distinct from the outbound tables below. Uses signed BIGINT(20) ids (not unsigned) and lightweight VARCHAR status, consistent with its role as an append-only log.

Column Type Notes
id BIGINT(20) NOT NULL AUTO_INCREMENT Primary key.
source VARCHAR(100) NOT NULL DEFAULT '' Originating integration/source name.
action VARCHAR(100) NOT NULL DEFAULT '' The event/action received.
user_id BIGINT(20) NOT NULL DEFAULT 0 Related member, when applicable; 0 if none.
payload LONGTEXT NOT NULL Raw received payload.
status VARCHAR(20) NOT NULL DEFAULT 'success' Processing outcome, default success.
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP When the event was logged.

Indexes:

  • PRIMARY KEY (id).
  • KEY action (action) - filter by event type.
  • KEY user_id (user_id) - events related to a member.
  • KEY created_at (created_at) - time-ordered scans and pruning.

Relationships: user_id references wp_users.ID when non-zero.

bn_outbound_webhooks

Registered outbound endpoints. Each row is one destination URL the site POSTs selected events to, with an optional signing secret and a JSON list of subscribed events.

Column Type Notes
id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT Primary key.
label VARCHAR(100) NOT NULL Human-readable name for the endpoint.
url VARCHAR(2083) NOT NULL Destination URL the payload is POSTed to.
secret VARCHAR(64) DEFAULT NULL Optional shared secret used to sign deliveries.
events JSON DEFAULT NULL List of event names this endpoint subscribes to.
is_active TINYINT(1) NOT NULL DEFAULT 1 Whether the endpoint receives deliveries.
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP When the endpoint was registered.
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP Last edit time.

Indexes:

  • PRIMARY KEY (id). No secondary indexes - the table is small (a handful of configured endpoints).

Relationships: referenced by bn_outbound_webhook_log.webhook_id. Delivery and retry are handled reactively by OutboundWebhookService (single-event scheduling, not a recurring cron).

bn_outbound_webhook_log

Per-attempt delivery log for outbound webhooks. One row per send attempt records the target endpoint, the event, the payload, and the HTTP response, so retries and failures are fully auditable.

Column Type Notes
id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT Primary key.
webhook_id BIGINT(20) UNSIGNED NOT NULL The endpoint this attempt targeted. References bn_outbound_webhooks.id.
event VARCHAR(64) NOT NULL The event delivered.
payload JSON DEFAULT NULL The payload that was sent.
response_code SMALLINT UNSIGNED DEFAULT NULL HTTP status returned by the endpoint.
response_body TEXT DEFAULT NULL Response body (truncated as needed).
status ENUM('success','error') NOT NULL DEFAULT 'success' Delivery outcome.
attempt TINYINT UNSIGNED NOT NULL DEFAULT 1 Retry attempt number for this delivery.
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP When the attempt was made.

Indexes:

  • PRIMARY KEY (id).
  • KEY webhook_event (webhook_id, event, created_at) - delivery history for an endpoint/event, time-ordered.
  • KEY status_date (status, created_at) - find recent failures to retry.

Relationships: webhook_id -> bn_outbound_webhooks.id.

Notes / gotchas

  • One token table, three purposes. Do not look for a separate 2FA table - bn_verify_tokens serves email verification, email-change confirmation, and 2FA, keyed by type. Always include type in lookups so flows do not cross-consume each other's tokens.
  • FULLTEXT is created outside dbDelta. The ft_search index is added by a guarded ALTER TABLE after table creation and is deliberately dropped in the test environment. Any code that searches must check has_fulltext_index() and fall back to LIKE.
  • Inbound vs outbound logs are different tables. bn_webhook_log records events received; bn_outbound_webhook_log records events sent. They are not interchangeable, and bn_webhook_log uses signed BIGINT ids while the outbound tables use unsigned.
  • Outbound config has no secondary indexes because it stays small; the delivery log carries the indexes for history and retry scans.
  • No foreign keys. Cross-table references (webhook_id, object_id, user_id) are by id only; integrity is enforced in the service layer.