Database Schema

WP Social Ninja stores its data in a small set of custom tables (created by the core plugin's migration runner) combined with WordPress's own posts, postmeta, and options tables. All custom tables use your site's WordPress table prefix — shown here as wpsr_. Pro and Custom Feed for TikTok add no tables of their own; they read and write these core tables.

Entity Overview

🔍 Interactive diagram

Click the diagram to zoom in. It shows the core tables and how templates/widgets map onto WordPress posts / postmeta.

Schema Overview

The WP Social Ninja schema is built around a few core concepts:

• Reviews & Testimonials: a single wpsr_reviews table holds every review, testimonial, and custom/form-collected review, keyed by platform and source.
• Caching: wpsr_caches stores remote API/feed responses with an expiration and a failure counter for back-off.
• Review Forms: wpsr_review_forms stores native and Pro review-collection form definitions (schema + settings).
• Categories 4.4.0: wpsr_review_categories plus a pivot table give reviews and testimonials a shared, many-to-many vocabulary, replacing the single wpsr_reviews.category column.
• Image Optimization: wpsr_optimize_images tracks locally optimized media.
• Templates & Widgets: stored as WordPress posts with _wpsr_* post meta.
• Settings: stored as WordPress options (prefixed wpsr_, autoload disabled).

Key Design Principles

1. Single reviews table — all review-type content lives in wpsr_reviews, distinguished by platform_name and recommendation_type rather than separate per-platform tables.
2. JSON overflow column — fields (LONGTEXT, cast to json on the model) holds platform-specific data that doesn't warrant its own column.
3. Strategic indexing — frequently filtered columns (platform_name, source_id, review_id, review_approved, reviewer_email) are indexed, plus composite indexes on (review_time, id) and (rating, review_time, id) for the sorted listings.
4. WordPress-native where it fits — templates/widgets reuse posts/postmeta; settings reuse options, so no bespoke tables are needed for them.
5. Incremental, idempotent migrations — migrators create-or-alter, adding missing columns and indexes on upgrade; bump WPSOCIALREVIEWS_DB_VERSION when the schema changes.
6. Application-level uniqueness — e.g. one-review-per-email is enforced in PHP (respecting the one_review_per_email toggle), with reviewer_email indexed for fast lookups.

Database Tables

wpsr_reviews Table

Stores all reviews, testimonials, and custom reviews. Migrator: ReviewsMigrator.php.

Column	Type	Comment
id	int(11) NOT NULL Auto Increment	Primary key
platform_name	varchar(255)	Platform slug (google, facebook, custom, …)
source_id	varchar(255)	Connected source / form ID
review_id	varchar(255)	Remote review ID
category	varchar(255)	Legacy single category — superseded in 4.4.0, see below
review_title	varchar(255)	Review title
reviewer_name	varchar(255)	Reviewer display name
reviewer_email	varchar(255) DEFAULT NULL	Reviewer email
reviewer_url	varchar(255)	Reviewer profile URL
reviewer_img	TEXT NULL	Reviewer avatar URL
reviewer_text	LONGTEXT NULL	Review body
review_time	timestamp NULL	Review timestamp
rating	int(11)	Star rating
review_approved	int(11) DEFAULT 1	Approval flag
recommendation_type	varchar(255)	e.g. Facebook recommendation type
fields	LONGTEXT NULL	JSON blob of extra fields (model cast: json)
created_at	TIMESTAMP NULL
updated_at	TIMESTAMP NULL

Indexes:

Index	Columns
idx_platform_name	platform_name
idx_review_id	review_id
idx_source_id	source_id
idx_review_approved	review_approved
idx_reviewer_email	reviewer_email
idx_review_time 4.4.0	review_time, id
idx_rating_time 4.4.0	rating, review_time, id

idx_rating was dropped in 4.4.0

idx_rating_time leads on rating, so it serves everything the single-column index did. The migration drops idx_rating only after confirming the replacement is really present, so a failed ALTER leaves the old index in place.

Both sort indexes end in id to match the query's tie-break. If you issue your own queries against wpsr_reviews with an explicit USE INDEX (idx_rating), switch to idx_rating_time.

wpsr_review_categories Table 4.4.0

Reusable review/testimonial categories — one shared vocabulary across both screens. Migrator: ReviewCategoriesMigrator.php.

Column	Type	Comment
id	bigint(20) NOT NULL Auto Increment	Primary key
name	varchar(191) NOT NULL	Display name
slug	varchar(191) NOT NULL	Derived from the name
created_at	TIMESTAMP NULL
updated_at	TIMESTAMP NULL

Indexes:

Index	Columns
idx_slug (UNIQUE)	slug

varchar(191) rather than 255 because slug is UNIQUE-indexed and utf8mb4 past 191 characters exceeds InnoDB's 767-byte key limit. name matches that width so two names cannot truncate to the same slug.

wpsr_review_category_relationships Table 4.4.0

Pivot between reviews and categories. A review may carry several categories. Migrator: ReviewCategoriesMigrator.php.

Column	Type	Comment
id	bigint(20) NOT NULL Auto Increment	Primary key
review_id	int(11) NOT NULL	wpsr_reviews.id
category_id	bigint(20) NOT NULL	wpsr_review_categories.id

Indexes:

Index	Columns
idx_review_category (UNIQUE)	review_id, category_id
idx_category_id	category_id

review_id is int(11) to match wpsr_reviews.id, not bigint(20) like the other keys — a mismatched width would prevent the join from using the index.

No foreign keys

There are no FK constraints, so nothing at the database level stops an orphaned pivot row. The application compensates: RecommendationsController verifies a client-supplied category_id resolves to a real category before writing, and the migrator runs a bounded orphan sweep. If you write pivot rows directly, validate both ids yourself.

Backfill from the legacy category column

The migrator copies wpsr_reviews.category into the taxonomy in bounded batches across requests rather than in one pass, so a large install does not time out. It tracks progress in options:

Option	Purpose
wpsr_review_categories_backfilled	true once the legacy column is fully copied
wpsr_review_categories_backfill_cursor	Batch cursor between runs
wpsr_review_categories_orphan_sweep_pending	A deferred orphan sweep is outstanding

The legacy category column is retained, not dropped — the backfill reads it and skips when it is absent. Treat it as read-only for new code and go through the pivot instead.

wpsr_caches Table

Feed and API response cache. Migrator: CacheMigrator.php.

Column	Type	Comment
id	bigint(20) NOT NULL Auto Increment	Primary key
platform	varchar(255) NULL	Platform slug
name	varchar(255)	Cache key
value	LONGTEXT NULL	Cached payload
expiration	TIMESTAMP NULL	Expiry time
failed_count	int(11) DEFAULT 0	Consecutive fetch failures (back-off)
created_at	TIMESTAMP NULL
updated_at	TIMESTAMP NULL

Indexes:

• platform
• name

See Caching for how services use this table.

wpsr_review_forms Table

Native review-collection form definitions. Core native forms and Pro review forms both write here. Migrator: ReviewFormsMigrator.php.

Column	Type	Comment
id	INT(11) NOT NULL Auto Increment	Primary key
title	VARCHAR(255) NOT NULL	Form title
slug	VARCHAR(255) NOT NULL	Form slug
schema	LONGTEXT NOT NULL	Field schema (JSON)
settings	LONGTEXT NULL	Form settings (JSON)
status	VARCHAR(20) DEFAULT 'active'	active / inactive
created_by	INT(11) NULL	Author user ID
created_at	TIMESTAMP NULL
updated_at	TIMESTAMP NULL

Indexes:

• status
• slug

wpsr_optimize_images Table

Local image-optimization records. Migrator: ImageOptimizationMigrator.php.

Column	Type	Comment
id	bigint(20) NOT NULL Auto Increment	Primary key
platform	varchar(255) NULL	Platform slug
user_id	varchar(255)	Owner user ID
user_name	varchar(255)	Owner name
json_data	LONGTEXT NULL	Source media metadata
fields	LONGTEXT NULL	Extra fields
media_id	varchar(1000)	WordPress attachment ID(s)
sizes	varchar(1000)	Generated sizes
aspect_ratio	DECIMAL(4,2) NOT NULL DEFAULT 0	Image aspect ratio
images_resized	tinyint(1)	Resize flag
last_requested	TIMESTAMP NULL	Last access time
created_at	TIMESTAMP NULL
updated_at	TIMESTAMP NULL

Indexes:

• user_id
• platform

WordPress-Native Storage

Not everything lives in a wpsr_ table:

Data	Stored in	Key pattern
Templates & widgets	wp_posts + wp_postmeta	_wpsr_* meta keys
Plugin settings	wp_options	wpsr_* (autoload no)

---

Verify columns before relying on them

Columns are added incrementally — migrators run alterTable() / addMissingIndexes() on upgrade. Confirm against the migration classes in database/Migrations/ for the version you target.