Phase 04: Database Schema

Goal

Define all 15 MySQL tables that WPLM needs, using WordPress dbDelta() for creation and upgrades.

Files Written

src/Install/Installer.php — get_schema() returns 15 CREATE TABLE statements
src/Install/Seeder.php — generates cryptographic secrets on first activation

Table Inventory

Table	Purpose
`wp_wplm_licenses`	Core license records (key hash, encrypted key, status, seats, expiry)
`wp_wplm_license_meta`	EAV key-value store for per-license metadata
`wp_wplm_machines`	Registered devices: fingerprint, IP (VARBINARY 16), lease timestamp
`wp_wplm_activation_logs`	Audit trail: activate / deactivate / validate events
`wp_wplm_blacklist`	Blocked keys and fingerprints
`wp_wplm_api_keys`	REST API consumer credentials (consumer key SHA-256, hashed secret)
`wp_wplm_generators`	Key-format templates (pattern, character set, separator)
`wp_wplm_product_mappings`	WooCommerce product ID → generator ID map
`wp_wplm_subscriptions`	Subscription billing records (status, period, next_payment)
`wp_wplm_renewals`	Individual renewal attempts and outcomes
`wp_wplm_webhooks`	Webhook endpoints (URL, events, HMAC secret)
`wp_wplm_releases`	Software release metadata and download URLs
`wp_wplm_environments`	Named deployment environments for a license
`wp_wplm_audit_log`	Admin action audit trail
`wp_wplm_notifications`	In-plugin notification queue

Key Design Decisions

dbDelta compatibility — All CREATE TABLE statements use exactly two spaces before column definitions and put PRIMARY KEY on its own line. This satisfies WordPress’s dbDelta() parser.

IPv4/IPv6 storage — Machine IP addresses are stored as VARBINARY(16) and written with INET6_ATON() / read with INET6_NTOA() inside $wpdb->prepare(). This keeps all user data parameterised and supports both IPv4 and IPv6 without string storage ambiguity.

Schema versioning — wplm_db_version option tracks the current schema integer. Installer::maybe_upgrade() compares against WPLM_DB_VERSION and runs dbDelta() only when the versions differ, making upgrades idempotent.

Seeder

On first activation Seeder::run() generates:

Ed25519 keypair — sodium_crypto_sign_keypair() → stored as JSON {kp, pub, sec} all base64-encoded, unautoloaded.
32-byte AES key — random_bytes(32) → base64 stored in wplm_aes_key.
32-byte HMAC secret — random_bytes(32) → base64 stored in wplm_hmac_secret.
32-byte webhook signing secret — random_bytes(32) → base64 stored in wplm_webhook_secret.
Default plugin options (activation limit strategy, heartbeat interval, etc.).

All secrets are stored with autoload = 'no' to avoid loading them on every page.