Database Schema

This document describes the custom database tables used by the Auth0 WordPress plugin for account mapping and event synchronization. The plugin uses custom MySQL tables rather than WordPress's standard options/meta tables to achieve high performance with large user bases.

For information about how these tables are used during authentication flows, see Authentication Flow. For details on the synchronization system that processes the queue table, see User Synchronization.

Overview

The plugin creates two custom database tables with the wp_auth0_ prefix (where wp_ is the configurable WordPress table prefix):

Table Name	Purpose	Primary Use Case
wp_auth0_accounts	Maps WordPress users to Auth0 connection identifiers	Identity resolution during login
wp_auth0_sync	Queues user lifecycle events for background processing	Asynchronous synchronization to Auth0

Both tables support WordPress multisite installations through site and blog columns that namespace data by network and blog ID.

Sources: src/Database.php14-29

Table: auth0_accounts

Schema Definition

Sources: src/Database.php101-119

Column Specifications

Column	Type	Constraints	Description
id	BIGINT	PRIMARY KEY, AUTO_INCREMENT	Unique row identifier
site	TINYINT	NOT NULL	WordPress network ID (0 for single-site)
blog	BIGINT	NOT NULL	WordPress blog/site ID
user	BIGINT	NOT NULL	WordPress user ID (foreign key to wp_users.ID)
auth0	TEXT	NOT NULL	Auth0 connection identifier (sub claim from JWT)

The table is created using WordPress's maybe_create_table() function, which only creates the table if it doesn't already exist, making it safe to call on every request.

Sources: src/Database.php101-119

Purpose and Usage

The auth0_accounts table maintains the many-to-one relationship between Auth0 connection identifiers and WordPress users. A single WordPress user can have multiple Auth0 connections (e.g., logging in via Google, GitHub, or username/password), each represented by a unique row.

Write Operations

Account connections are created during successful authentication in Authentication::createAccountConnection():

The method generates a cache key using hash('sha256', $connection . '::' . $network . '!' . $blog) to avoid redundant database queries within a 120-second window.

Sources: src/Actions/Authentication.php53-88

Read Operations

During login, Authentication::getAccountByConnection() looks up WordPress users by their Auth0 sub:

Sources: src/Actions/Authentication.php111-154

Delete Operations

When WordPress users are deleted, Authentication::deleteAccountConnections() removes all associated Auth0 connections:

Sources: src/Actions/Authentication.php90-109

Multisite Considerations

The site and blog columns enable proper isolation in WordPress multisite networks. Queries always filter by both:

This ensures users on different sites within a network can have independent Auth0 connections, even with the same email address.

Sources: src/Actions/Authentication.php113-132

Table: auth0_sync

Schema Definition

Sources: src/Database.php121-141

Column Specifications

Column	Type	Constraints	Description
id	BIGINT	PRIMARY KEY, AUTO_INCREMENT	Unique row identifier
site	TINYINT	NOT NULL	WordPress network ID
blog	BIGINT	NOT NULL	WordPress blog/site ID
created	INT(11)	NOT NULL	Unix timestamp when event was queued
payload	TEXT	NOT NULL	JSON-encoded event data
hashsum	VARCHAR(64)	NOT NULL, UNIQUE	SHA-256 hash of payload for deduplication
locked	INT(1)	NOT NULL	Concurrent processing lock (0=unlocked, 1=locked)

The hashsum column has a UNIQUE constraint, preventing duplicate events from being queued.

Sources: src/Database.php121-141

Purpose and Usage

The auth0_sync table implements a durable event queue for asynchronous synchronization between WordPress and Auth0. When WordPress user events occur (creation, update, deletion), they are immediately queued here for later processing by WordPress cron jobs.

Event Types

The payload column contains JSON objects with an event field indicating the type:

Event Type	Triggered By	Payload Structure
wp_user_created	User created via WordPress UI	{"event": "wp_user_created", "user": <id>}
wp_user_updated	User profile updated	{"event": "wp_user_updated", "user": <id>}
wp_user_deleted	User deleted	{"event": "wp_user_deleted", "user": <id>, "connection": "<sub>"}

Sources: src/Actions/Authentication.php283-308 src/Actions/Authentication.php323-348 src/Actions/Authentication.php604-628

Write Operations

Events are queued synchronously when WordPress hooks fire:

The duplicate check ensures that rapidly repeated events (e.g., multiple profile updates) don't flood the queue.

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

Read and Process Operations

The WordPress cron job AUTH0_CRON_SYNC processes queued events via Sync::onBackgroundSync():

The processing is batched (10 events per cron run) and deletions occur regardless of API success to prevent infinite retry loops.

Sources: src/Actions/Sync.php214-254

Deduplication Strategy

The hashsum column serves as a deduplication key. Before inserting, the code checks:

If a row with the same hash exists, the insertion is skipped. This prevents duplicate events from being queued when WordPress hooks fire multiple times (a common occurrence during bulk operations or plugin conflicts).

Sources: src/Actions/Authentication.php287-290 src/Actions/Authentication.php328-331 src/Actions/Authentication.php608-611

Database Abstraction Layer

Database Class

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

Sources: src/Database.php1-149

Key Methods

Method	Purpose	Usage Pattern
createTable($table)	Creates table if not exists	Called before first access via prepDatabase()
getTableName($table)	Returns prefixed table name	wp_auth0_{table}
insertRow($table, $data, $formats)	Insert with prepared statement	Queuing events, creating connections
selectRow($select, $from, $query, $args)	Single row query	Looking up connections
selectResults($select, $from, $query, $args)	Multi-row query	Fetching queue items
deleteRow($table, $where, $format)	Delete with prepared statement	Removing processed events

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

Sources: src/Database.php31-99

Table Creation Pattern

Tables are created lazily using WordPress's maybe_create_table() function, which compares the desired schema against the existing table structure and only executes DDL if necessary:

This is idempotent and safe to call repeatedly. The plugin calls createTable() before accessing tables through the prepDatabase() helper, which adds an additional caching layer using transients (30-minute TTL) to avoid redundant checks.

Sources: src/Database.php101-141 src/Actions/Authentication.php645-658

Database Access Patterns

Connection Mapping Flow

Sources: src/Actions/Authentication.php660-724 src/Actions/Authentication.php111-154

Synchronization Queue Flow

Sources: src/Actions/Authentication.php270-351 src/Actions/Sync.php214-254

Performance Considerations

Why Custom Tables?

The plugin uses custom database tables instead of WordPress's options/meta tables for three primary reasons:

1. Query Performance: Direct table access with indexes is significantly faster than WordPress's serialized option storage or meta queries with JOIN operations
2. Scalability: Large user bases generate thousands of account mappings and sync events; custom tables handle this volume efficiently
3. Transaction Integrity: Queue operations require atomic inserts with duplicate checks, which are better supported by dedicated tables with UNIQUE constraints

The documentation explicitly notes this decision in comments about performance optimization.

Sources: src/Database.php1-10

Caching Strategy

The plugin implements multi-tier caching for auth0_accounts lookups:

TTL: 120 seconds for both transients and object cache Cache Key Format: auth0_account_ + SHA-256 hash of {sub}::{network}!{blog}

This strategy minimizes database queries during high-traffic periods when the same users repeatedly authenticate.

Sources: src/Actions/Authentication.php111-154

Database Preparation Optimization

Before accessing custom tables, the code checks if table creation is needed using a cached flag:

This ensures createTable() (which calls maybe_create_table()) runs at most once per 30 minutes per table, avoiding repeated filesystem and database metadata checks.

Sources: src/Actions/Authentication.php645-658

Multisite Architecture

Both tables include site and blog columns to support WordPress multisite (network) installations:

Column	WordPress Function	Scope
site	get_current_network_id()	Network-level isolation
blog	get_current_blog_id()	Site-level isolation within network

All queries filter by both columns, ensuring complete data isolation between sites. This allows different sites in a network to:

• Use different Auth0 tenants
• Map the same Auth0 connection to different WordPress users
• Maintain independent synchronization queues

For single-site WordPress installations, site is 0 and blog is 1.

Sources: src/Actions/Authentication.php55-56 src/Actions/Authentication.php113-114 src/Actions/Authentication.php276-277

---

Sources:

• src/Database.php1-149
• src/Actions/Authentication.php53-724
• src/Actions/Sync.php49-254
• src/Plugin.php59-70