Database Tables Reference

This document provides a complete reference for the custom database tables used by the Auth0 WordPress plugin. These tables store the mapping between WordPress users and Auth0 connections, as well as queued synchronization events for background processing.

For information about configuration storage in WordPress's standard wp_options table, see Configuration Options. For information about WordPress's standard user tables (wp_users, wp_usermeta), refer to WordPress core documentation.

Tables Overview

The plugin creates two custom database tables:

Table Name	Purpose	Created By
{prefix}_auth0_accounts	Maps WordPress user IDs to Auth0 connection identifiers (sub claims)	src/Database.php101-118
{prefix}_auth0_sync	Queues user synchronization events for background processing	src/Database.php121-140

The {prefix} placeholder represents WordPress's configured table prefix (typically wp_), obtained via $wpdb->prefix. Table names are constructed by the getTableName() method in src/Database.php50-54

Sources: src/Database.php1-150

auth0_accounts Table

Purpose

The auth0_accounts table maintains the relationship between WordPress user accounts and Auth0 connection identifiers. This mapping enables the plugin to:

• Resolve WordPress users during authentication by Auth0 connection
• Support multiple Auth0 connections per WordPress user (flexible matching mode)
• Enable back-channel logout by associating sessions with specific connections
• Track which users authenticate via Auth0 versus native WordPress login

Schema Definition

Table Creation Query:

The table is created by src/Database.php101-118 using the following schema:

Sources: src/Database.php101-118

Column Reference

Column	Type	Nullable	Description
id	BIGINT	NO	Auto-incrementing primary key
site	TINYINT	NO	WordPress network (site) ID. Value from get_current_network_id(). Used for multisite installations to isolate connections per network.
blog	BIGINT	NO	WordPress blog ID. Value from get_current_blog_id(). Used for multisite installations to isolate connections per blog.
user	BIGINT	NO	Foreign key to wp_users.ID. The WordPress user ID associated with this connection.
auth0	TEXT	NO	The Auth0 connection identifier (sub claim). Format: `{provider}

Sources: src/Database.php110-117

Indexes

• Primary Key: id column provides unique identification for each account mapping record

Note: The current schema does not include additional indexes. For performance optimization in large installations, consider adding composite indexes on (site, blog, user) and (site, blog, auth0(255)) for common query patterns.

Sources: src/Database.php116

Usage Patterns

Creating Account Connections

When a user successfully authenticates via Auth0, the plugin creates or verifies an account connection record in src/Actions/Authentication.php53-88:

The createAccountConnection() method:

1. Generates a cache key from the connection identifier, network ID, and blog ID
2. Checks WordPress cache and transients to avoid duplicate queries
3. Queries the table for existing connection: src/Actions/Authentication.php69
4. Inserts a new record if no connection exists: src/Actions/Authentication.php75-86

Sources: src/Actions/Authentication.php53-88

Resolving Users by Connection

During authentication callback processing, the plugin resolves the WordPress user from the Auth0 connection identifier in src/Actions/Authentication.php111-154:

This three-tier lookup strategy (cache → transient → database) minimizes database queries for frequently accessed connections.

Sources: src/Actions/Authentication.php111-154

Deleting Account Connections

When a WordPress user is deleted, the plugin removes all associated Auth0 connections in src/Actions/Authentication.php90-109:

The method returns the deleted connection identifiers so they can be queued for synchronization to Auth0.

Sources: src/Actions/Authentication.php90-109 src/Actions/Authentication.php310-351

Multisite Support

The site and blog columns enable proper isolation in WordPress multisite installations:

• Network isolation: Different networks can have users with the same ID mapped to different Auth0 connections
• Blog isolation: Different blogs within a network maintain separate connection mappings
• Query patterns: All queries include WHERE site = %d AND blog = %d predicates

Sources: src/Actions/Authentication.php55-56 src/Actions/Authentication.php94-95

auth0_sync Table

Purpose

The auth0_sync table functions as a persistent event queue for asynchronous user synchronization operations. It enables:

• Background synchronization of WordPress user changes to Auth0
• Reliable event processing via WordPress Cron
• Event deduplication using content-based checksums
• Decoupling user operations from Auth0 API latency

Schema Definition

Table Creation Query:

The table is created by src/Database.php121-140 using the following schema:

Sources: src/Database.php121-140

Column Reference

Column	Type	Nullable	Description
id	BIGINT	NO	Auto-incrementing primary key
site	TINYINT	NO	WordPress network ID from get_current_network_id(). Isolates events per network in multisite installations.
blog	BIGINT	NO	WordPress blog ID from get_current_blog_id(). Isolates events per blog in multisite installations.
created	INT(11)	NO	Unix timestamp when event was queued. Value from time(). Used for ordering event processing.
payload	TEXT	NO	JSON-encoded event data. Contains event type and event-specific fields (e.g., user, connection).
hashsum	VARCHAR(64)	NO	SHA-256 hash of the payload field. Enforces event uniqueness via UNIQUE constraint. Prevents duplicate event processing.
locked	INT(1)	NO	Lock flag for concurrent processing protection. Currently stores 0 (unlocked). Reserved for future locking mechanism.

Sources: src/Database.php130-139

Indexes

• Primary Key: id column
• Unique Key: hashsum column prevents duplicate events from being queued

The unique constraint on hashsum ensures that identical events (same payload) are not queued multiple times, providing automatic deduplication.

Sources: src/Database.php136

Event Payload Structure

Events stored in the payload column are JSON objects with the following structure:

User Creation Event

Created by: src/Actions/Authentication.php283-286

User Deletion Event

Created by: src/Actions/Authentication.php323-327

User Update Event

Created by: src/Actions/Authentication.php604-607

Sources: src/Actions/Authentication.php270-351 src/Actions/Authentication.php591-629

Usage Patterns

Queuing Sync Events

When WordPress user events occur (creation, update, deletion), the plugin queues corresponding sync events:

The pattern used in src/Actions/Authentication.php270-308 src/Actions/Authentication.php310-351 and src/Actions/Authentication.php591-629:

1. Encode event data as JSON
2. Calculate SHA-256 hash of JSON payload
3. Query for existing event with same hash: SELECT id WHERE hashsum = "%s"
4. Insert new record only if no duplicate exists

This ensures each unique event is queued exactly once, even if the WordPress action fires multiple times.

Sources: src/Actions/Authentication.php270-308 src/Actions/Authentication.php310-351 src/Actions/Authentication.php591-629

Processing Sync Queue

WordPress Cron jobs process queued events in src/Actions/Sync.php214-254:

Processing characteristics:

• Batch size: 10 events per cron execution (hard-coded in query LIMIT)
• Ordering: Events processed in chronological order (ORDER BY created)
• Scope: Only events for current network and blog are processed
• Event type filtering: Configuration controls which event types are processed
• Cleanup: Successfully processed events are deleted from the queue

Sources: src/Actions/Sync.php214-254

Event Processing Methods

Each event type has a dedicated handler method:

Event Type	Handler Method	Lines	Operations
wp_user_created	eventUserCreated()	src/Actions/Sync.php76-115	Creates Auth0 user via Management API, stores connection in auth0_accounts, triggers password reset email
wp_user_deleted	eventUserDeleted()	src/Actions/Sync.php117-138	Verifies connection not re-claimed, deletes Auth0 user if still exists
wp_user_updated	eventUserUpdated()	src/Actions/Sync.php140-186	Updates Auth0 user profile fields, triggers email verification if email changed

Sources: src/Actions/Sync.php76-186

Cron Schedule Configuration

The sync schedule is configured via the auth0_sync option group and registered in src/Actions/Sync.php261-268:

Default interval is 3600 seconds (1 hour) if not configured. Available intervals are defined in src/Actions/Configuration.php124-141

Sources: src/Actions/Sync.php261-268 src/Actions/Configuration.php124-141

Table Management

Table Creation

Both tables are created automatically by the plugin using WordPress's maybe_create_table() function, which creates tables only if they don't already exist.

Creation Timing:

Tables are created lazily on first use, triggered by prepDatabase() in src/Actions/Authentication.php645-658:

1. Check transient cache auth0_db_check_{hash} to avoid repeated creation attempts
2. If not cached (expires after 1800 seconds), call createTable()
3. Cache the check result for 30 minutes

This ensures tables exist before queries execute while avoiding unnecessary CREATE TABLE IF NOT EXISTS queries on every request.

Sources: src/Actions/Authentication.php645-658 src/Database.php31-40

Database Abstraction Layer

The Database class provides CRUD abstraction over WordPress's $wpdb global:

Method Reference:

Method	Purpose	Used In
getTableName(table)	Returns fully-qualified table name with WordPress prefix	All database operations
createTable(table)	Creates table if it doesn't exist	src/Actions/Authentication.php656
insertRow(table, data, formats)	Inserts a single row	src/Actions/Authentication.php75-86
selectRow(select, from, query, args)	Returns single row	src/Actions/Authentication.php69
selectResults(select, from, query, args)	Returns multiple rows	src/Actions/Sync.php223
selectDistinctResults(select, from, query, args)	Returns distinct rows	src/Actions/Sync.php58
deleteRow(table, where, format)	Deletes rows matching criteria	src/Actions/Authentication.php102

All query methods use $wpdb->prepare() for SQL injection protection.

Sources: src/Database.php1-150

Maintenance Operations

Orphaned Connection Cleanup

The plugin includes a maintenance task to remove orphaned connections (connections pointing to deleted WordPress users) in src/Actions/Sync.php49-74:

This maintenance task runs every 5 minutes via the AUTH0_MAINTENANCE cron schedule defined in src/Actions/Sync.php265

Use Case: If a WordPress user is deleted through direct database manipulation or via a process that bypasses WordPress hooks, the connections table may retain orphaned records. This maintenance task ensures those orphaned connections are eventually cleaned up.

Sources: src/Actions/Sync.php49-74 src/Actions/Sync.php265

Data Flow Architecture

Complete Synchronization Flow

Key Characteristics:

1. Asynchronous Processing: User events are queued immediately but processed asynchronously via cron
2. Deduplication: SHA-256 checksums prevent duplicate events in the queue
3. Multisite Isolation: site and blog columns ensure events are scoped correctly
4. Batch Processing: Events are processed in batches of 10 per cron execution
5. Eventual Consistency: Changes propagate to Auth0 within the configured sync interval

Sources: src/Actions/Authentication.php48-51 src/Actions/Sync.php39-43 src/Actions/Sync.php214-254

Authentication Flow with Database Tables

Sources: src/Actions/Authentication.php433-551

Code Entity Reference

Primary Classes

Class	File	Purpose
Database	src/Database.php	Database abstraction layer and table management
Authentication	src/Actions/Authentication.php	Manages account connections and queues sync events
Sync	src/Actions/Sync.php	Processes sync queue and performs background synchronization

Key Methods by Operation

Account Connection Management:

• Authentication::createAccountConnection() - src/Actions/Authentication.php53-88
• Authentication::getAccountByConnection() - src/Actions/Authentication.php111-154
• Authentication::getAccountConnections() - src/Actions/Authentication.php156-172
• Authentication::deleteAccountConnections() - src/Actions/Authentication.php90-109

Sync Event Management:

• Authentication::onCreatedUser() - src/Actions/Authentication.php270-308
• Authentication::onDeletedUser() - src/Actions/Authentication.php310-351
• Authentication::onUpdatedUser() - src/Actions/Authentication.php591-629
• Sync::onBackgroundSync() - src/Actions/Sync.php214-254
• Sync::eventUserCreated() - src/Actions/Sync.php76-115
• Sync::eventUserDeleted() - src/Actions/Sync.php117-138
• Sync::eventUserUpdated() - src/Actions/Sync.php140-186

Table Management:

• Database::createTable() - src/Database.php31-40
• Database::createTableAccounts() - src/Database.php101-118
• Database::createTableSync() - src/Database.php121-140
• Authentication::prepDatabase() - src/Actions/Authentication.php645-658
• Sync::cleanupOrphanedConnections() - src/Actions/Sync.php49-74

Sources: src/Database.php1-150 src/Actions/Authentication.php1-726 src/Actions/Sync.php1-284