Review Module Backend

Summary

The review module now has two distinct persistence models:

• order_reviews for product reviews, including both customer-submitted reviews and admin-seeded fake-persona reviews
• testimonials for admin-created storefront social proof

Testimonials remain separate. Admin-seeded product reviews now reuse order_reviews because they are intended to appear inside the actual product review stream and affect the same rating aggregates.

Data Model

order_reviews

Purpose:

• customer-submitted reviews for delivered order items
• admin-seeded reviews with explicit fake reviewer persona fields
• admin moderation before storefront display

Key fields:

• orderId
• productId
• customerId
• reviewSource
• reviewerName
• reviewerImageUrl
• rating
• comment
• status
• adminRemarks
• reviewedByAdminId
• reviewedAt
• timestamps

Key invariants:

• customer reviews are unique per customerId + orderId + productId
• rating must be between 1 and 5
• reviewSource = 'customer' requires a real customerId
• reviewSource = 'admin_seeded' requires customerId = null and a non-blank reviewerName
• pending reviews have no admin review metadata
• approved/rejected reviews require admin review metadata

testimonials

Purpose:

• admin-authored trust content
• public storefront list not tied to product ownership

Key fields:

• name
• rating
• comment
• imageUrl
• isVisible
• order
• timestamps

Key invariants:

• rating must be between 1 and 5
• name and comment must remain non-blank after trimming
• visibility drives storefront exposure
• order supports merchandising order independent of create time

Module Layout

Admin side

• existing review admin controller/service still own order review moderation
• new testimonial admin controller/service own testimonial CRUD
• both stay under the review admin module and reuse Reviews_* permissions

Public side

• existing review public controller/service still own product page review reads
• new testimonial public controller/service own storefront testimonial reads

Mobile side

• existing authenticated mobile controller still owns customer-submitted product reviews
• existing mobile public product-review controller remains unchanged
• new mobile testimonial controller exposes storefront testimonials under mobile route prefix

Query Behavior

Admin testimonial list

Filters:

• search
• isVisible

Sort fields:

• order
• createdAt
• updatedAt
• rating

Ordering:

• primary requested field
• secondary createdAt desc for stable output

Public testimonial list

Filters:

• visible only
• optional exact rating

Sort fields:

• order
• createdAt
• rating

Ordering:

• primary requested field
• secondary createdAt desc

Why Testimonials Stay Separate

Trying to reuse order_reviews for testimonials would force one of these bad tradeoffs:

• make orderId and productId nullable and loosen existing purchase rules
• invent fake orders/products just to store admin-authored content
• overload existing controllers and DTOs with branching logic

The separate testimonials table keeps:

• purchased product reviews trustworthy
• testimonial content simple
• API docs easy to understand

Admin-seeded fake product reviews are different from testimonials:

• they are attached to a specific productId
• they participate in the same approved-review totals and averages
• they appear through the same product review endpoints as customer reviews

That is why the product-review stream now supports two row modes inside order_reviews instead of creating a third table.

Product Review Discovery

Product reviews are surfaced on product detail pages through product-specific endpoints:

• GET /api/products/:slug/reviews — public: approved reviews for a product by its slug
• GET /api/products/id/:productId/reviews — public: approved reviews by product ID
• GET /api/mobile/products/id/:productId/reviews — public mobile variant

The review-to-product linkage is through order_reviews.product_id, which is a FK to product.id. Only approved reviews are returned in public responses. Review count and average rating may be cached alongside product detail data.

Discovery Flow

1. Customer views product detail (GET /api/products/:slug)
2. Customer requests product reviews (GET /api/products/:slug/reviews) for social proof
3. System returns approved reviews with pagination, filtered by product slug resolution
4. Customer sees star ratings, comments, and review metadata on the product page

Integration Notes

• permissions already exist under Reviews_*, so no new permission family is needed
• default Swagger must include both review admin and public modules so testimonial routes appear in generated docs
• mobile docs inherit testimonial routes through ReviewMobileModule

Product Average Rating Integration

Average ratings are computed on the fly from the order_reviews table using SQL aggregation. There is no separate summary table; every product list and detail query derives averageRating and reviewCount from approved reviews in real time.

Computation Strategy

The module uses two complementary patterns depending on the query context:

Fields

Where the computation lives

• ProductCustomerService.findAll() — correlated subquery in each product list variant (all products, featured products, new arrivals, related products). The subquery filters by product_id and status = 'approved'.
• ProductCustomerService.findBySlugForCustomer() — calls getApprovedReviewSummary(productId) to fetch aggregate data for the product detail page.
• ProductAdminService.findAll() — uses the same correlated subquery pattern for the admin list view.
• ReviewPublicService.findApprovedByProductSlug() — computes summary alongside the paginated review list using AVG() and COUNT() for the response aggregate.

Consistency

Because the average is computed from the source table rather than cached in a summary table, the value is always consistent with the current set of approved reviews:

• Creating a new approved review immediately changes the average for subsequent queries.
• Changing a review status from approved to rejected (or vice versa) is reflected in the next query.
• Deleting a review recalculates the average on the next read.
• No staleness window, no reconciliation job needed, no cache invalidation coordination.

Structured data (JSON-LD)

The product detail endpoint embeds the review summary as AggregateRating structured data:

{
  "@type": "AggregateRating",
  "ratingValue": "4.50",
  "reviewCount": 12
}

This is conditionally included when totalReviews > 0 and attached to the Product schema.org entity in the product detail response.

Performance notes

• The correlated subquery adds a per-row aggregation cost on product list queries. Indexes on order_reviews.product_id and order_reviews.status mitigate this. Both columns are covered by existing composite query patterns.
• For detail views the cost is negligible since it's a single product with a filtered aggregation.
• If list query performance becomes a concern, a materialized summary table (product_review_summary) can be introduced as a future optimisation without changing the API contract.

See also

• Product Backend: See Product Customer Service for the product list and detail query architecture.
• API Reference: See Review Module API section "Review Summary Integration" for the response shape changes.

Admin Review Creation

Admin users can seed fake product reviews directly through POST /api/admin/reviews.

Key Rules

• Admin-created seeded reviews use order_reviews with reviewSource = 'admin_seeded'.
• customerId is null for seeded reviews.
• reviewerName is required and is shown publicly exactly as entered.
• reviewerImageUrl is optional and is used anywhere the public review DTO already supports images.
• The orderId can be null for seeded reviews.
• Seeded reviews are inserted as approved, so they immediately affect public product review reads and rating aggregates.
• Customer-submitted reviews still follow the purchased-order validation path and store reviewSource = 'customer'.