Database Tables

MediaShield Pro owns 7 database tables. All use the {$wpdb->prefix} prefix (typically wp_) and utf8mb4_unicode_ci collation; the engine is whatever dbDelta defaults to on the host (InnoDB on any modern MySQL/MariaDB). Tables are created on activation and dropped on plugin deletion.

An eighth table, ms_email_captures, existed before 1.2.0. The email gate it belonged to was removed and the migrator drops the table on upgrade.

ms_playback_events

Granular playback event log. Input for heatmap aggregation. Rows are written by Analytics\PlaybackRecorder from free’s session heartbeats (default one every 30 seconds), so this is a coarse sampling of playback, not a per-interaction event stream.

Column	Type	Notes
id	bigint PK
session_id	bigint	FK to ms_watch_sessions.id (free table). Video and user are resolved through that join - this table has no video_id or user_id column
event_type	enum	play, pause, seek, buffer, complete, focus_lost, focus_gained. Heartbeat-derived rows only ever use play, pause, focus_lost
position	float	Seconds from video start
timestamp	datetime	UTC, defaults to CURRENT_TIMESTAMP
metadata	JSON	{playing, focused, source} for heartbeat rows

Indexes: idx_session (session_id), idx_position (session_id, position), idx_timestamp (timestamp).

Retention: rows older than 90 days are deleted daily by the ms_playback_event_retention job. The window is filterable via mediashield_pro_playback_event_retention_days and floored at 2 days. Aggregated totals survive in ms_heatmap_cache.

ms_platform_connections

Encrypted API credentials for external platforms.

Column	Type	Notes
id	bigint PK
platform	varchar(50)	bunny, youtube, vimeo, wistia
api_key	text	Encrypted (AES-256-CBC, key derived from SECURE_AUTH_SALT)
api_secret	text	Encrypted the same way. Empty string when the platform needs no secret
extra_config	JSON	Platform-specific fields: label, channel_id, pull_zone_hostname, cdn_token_key
is_active	tinyint(1)	Default 1
connected_by	bigint	User ID that created the connection
connected_at	datetime	UTC, defaults to CURRENT_TIMESTAMP

Index: idx_platform (platform). There is no uniqueness constraint, so multiple connections per platform are allowed.

ms_upload_queue

Upload job tracking.

Column	Type	Notes
id	bigint PK
video_id	bigint	CPT post ID, nullable
file_path	text	Source file
target_platform	varchar(50)	Driver/target name
status	enum	pending, uploading, processing, complete, failed
progress	tinyint	0-100
error_message	text	Set on failure
uploaded_by	bigint	User ID
created_at	datetime	UTC, defaults to CURRENT_TIMESTAMP
completed_at	datetime	NULL until the job finishes

Index: idx_status (status).

ms_activity_alerts

Suspicious viewing pattern alerts.

Column	Type	Notes
id	bigint PK
user_id	bigint	0 for anonymous
video_id	bigint	0 for site-wide alerts (devtools alerts always use 0)
alert_type	enum	multi_ip, devtools, rapid_seek, concurrent_stream, vpn_detected. Nothing in 1.3.0 writes concurrent_stream - the value exists for the UI label and the CLI seeder only
severity	varchar(20)	Default info. Detectors write warning (multi_ip, devtools, rapid_seek) or info (vpn_detected)
message	varchar(500)	Human-readable description
details	JSON	Reserved; the detectors do not populate it
is_dismissed	tinyint(1)	0 = active
created_at	datetime	UTC, defaults to CURRENT_TIMESTAMP

Indexes: idx_user (user_id), idx_severity (severity), idx_created (created_at).

Retention: dismissed alerts older than 90 days are removed daily by the ms_alert_pruning job. Undismissed alerts are kept indefinitely.

ms_drm_licenses

DRM license records.

Column	Type	Notes
id	bigint PK
video_id	bigint
user_id	bigint
license_type	enum	streaming or persistent. Only streaming can be issued since 1.2.0; persistent remains for legacy rows
license_token	varchar(255)	UUID plus 16 random bytes
device_id	varchar(255)	Optional client-supplied identifier, default empty
expires_at	datetime	UTC
created_at	datetime	UTC, defaults to CURRENT_TIMESTAMP
revoked_at	datetime	NULL unless revoked. Any revoked row for a video+user pair blocks future issuance for that pair

Indexes: idx_video_user (video_id, user_id), idx_expires (expires_at).

ms_heatmap_cache

Pre-aggregated heatmap data per video. Values are cumulative running totals, not a rolling window.

Column	Type	Notes
id	bigint PK
video_id	bigint
position_bucket	smallint	10-second bucket start position in seconds
view_count	int	Playback events counted into this bucket
avg_duration	float	Weighted average session length for the bucket
last_aggregated	datetime	UTC

Index: UNIQUE uk_video_position (video_id, position_bucket). Updated hourly by the ms_heatmap_aggregation job, which upserts everything recorded since the ms_heatmap_last_aggregated option watermark.

ms_drm_keys

Encrypted content keys for DRM-packaged videos.

Column	Type	Notes
id	bigint PK
video_id	bigint	UNIQUE - one key per video
key_id	varchar(255)	Hex key ID
content_key_encrypted	text	AES-128 content key, encrypted at rest
created_at	datetime	UTC, defaults to CURRENT_TIMESTAMP

Index: UNIQUE uk_video (video_id). A legacy iv column shipped in DB v1 but was never read; the migrator drops it and fresh installs do not create it.

This table has no writer. KeyServer::generate_key() was its only one, and it was called only from DRM\Packager - both deleted in 1.3.0 because nothing reached them. KeyServer::get_key() still reads the table for WidevineLicense, so it stays, but it will be empty until a packaging path is written.

Scheduled Jobs

All Pro jobs run through Action Scheduler in the mediashield-pro group.

Hook	Cadence	Work
ms_heatmap_aggregation	hourly	Bucket new playback events into ms_heatmap_cache
ms_playback_event_retention	daily	Prune ms_playback_events past the retention window
ms_alert_pruning	daily	Delete dismissed alerts older than 90 days
ms_weekly_digest	weekly	Send the digest email
ms_vpn_lookup	on demand	One-off IP lookup queued at session start when VPN detection is on