Database Schema

Database Tables

OEngage creates 8 custom tables on activation.

wp_oregister_xp_log

sqlid         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY
user_id    BIGINT UNSIGNED NOT NULL
points     INT NOT NULL
event      VARCHAR(100)
note       VARCHAR(255)
created_at DATETIME DEFAULT CURRENT_TIMESTAMP

INDEX idx_user_id (user_id)
INDEX idx_created (created_at)

wp_oregister_badges

sqlid          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY
user_id     BIGINT UNSIGNED NOT NULL
badge_slug  VARCHAR(100) NOT NULL
awarded_at  DATETIME DEFAULT CURRENT_TIMESTAMP

UNIQUE KEY unique_badge (user_id, badge_slug)

wp_oregister_streaks

sqluser_id      BIGINT UNSIGNED PRIMARY KEY
current      INT UNSIGNED DEFAULT 0
longest      INT UNSIGNED DEFAULT 0
last_login   DATE

wp_oregister_magic_tokens

sqlid         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY
user_id    BIGINT UNSIGNED NOT NULL
token_hash VARCHAR(64) UNIQUE   -- SHA-256 of raw token
expires_at DATETIME
used_at    DATETIME NULL
created_at DATETIME DEFAULT CURRENT_TIMESTAMP

wp_oregister_social_accounts

sqlid          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY
user_id     BIGINT UNSIGNED NOT NULL
provider    VARCHAR(30)       -- 'google' | 'apple' | 'github' | 'linkedin'
provider_id VARCHAR(255)
email       VARCHAR(150)
created_at  DATETIME DEFAULT CURRENT_TIMESTAMP

UNIQUE KEY unique_social (provider, provider_id)

wp_oregister_2fa_secrets

sqluser_id      BIGINT UNSIGNED PRIMARY KEY
method       ENUM('totp','email')
secret       VARCHAR(255)     -- encrypted TOTP secret or NULL for email method
recovery_codes TEXT           -- JSON array of bcrypt hashes
enabled_at   DATETIME

wp_oregister_login_log

sqlid         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY
user_id    BIGINT UNSIGNED NOT NULL
ip_address VARCHAR(45)
user_agent VARCHAR(500)
result     ENUM('success','fail','2fa_fail','locked')
created_at DATETIME DEFAULT CURRENT_TIMESTAMP

INDEX idx_user_ip (user_id, ip_address)
INDEX idx_created (created_at)

wp_oregister_lockouts

sqlip_address  VARCHAR(45) PRIMARY KEY
attempts    TINYINT UNSIGNED DEFAULT 0
locked_until DATETIME NULL
last_attempt DATETIME