Reviews - Backend Documentation

1. Documentation Evidence

2. Backend Scope and Boundaries

Owns

• product_review, product_rating_summary, product_review_event, product_review_report.
• Review eligibility (which delivered order line entitles which customer to review which product).
• The review lifecycle and every transition, with the aggregate maintained by ±1 deltas in the same transaction.
• Moderation state, reasons, and the immutable event history; abuse reports and their resolution.

Does Not Own

• Review content after submission — there is no admin create and no admin edit of review content. Moderation changes visibility only; product_review_event records each action with no UPDATE path anywhere.
• Products — the detail response projects the rating aggregate; Products reads product_rating_summary and writes nothing.

Source of Truth

3. Module Composition

4. File and Directory Map

apps/api/src/modules/reviews/
  customer/
    product-review/   public controller + service + dto
    my-review/        my-review controller + service + dto
  admin/
    review/           review-admin controller + service + dto
    report/           report-admin controller + service + dto
  shared/             shared services + types
  reviews-worker.module.ts
packages/db/src/schema/reviews/
  product-review.ts  product-rating-summary.ts
  product-review-event.ts  product-review-report.ts  enums.ts
packages/db/src/migrations/0013_product_reviews_and_ratings.sql

Key files:

5. Data Model

5.1 Schema Source

packages/db/src/schema/reviews/   (4 tables + enums)

5.2 Tables

product_review

Key constraints:

• uq_product_review_customer_product — partial unique on (customer_id, product_id) WHERE deleted_at IS NULL: one live review per pair; a withdrawal frees the slot.
• RESTRICT FKs on customer and order keys — account deletion anonymises rather than deletes, so they cannot fire today; a future hard delete must confront the aggregate instead of silently drifting it.

product_rating_summary

One CHECK: every counter >= 0. The aggregate cannot be internally inconsistent: review_count, rating_sum and average_rating are GENERATED ALWAYS from one set of counters, so moving the average and moving the count are the same write, and the database refuses (428C9) any attempt to write them directly. The ±1 runs inside the same transaction as the status change that caused it, and decrementing an empty bucket aborts (23514) rather than producing a negative average.

product_review_event

Append-only: every moderation action and customer write, with no UPDATE path anywhere. event_type = 'published' needs no reason; rejected and hidden do.

product_review_report

One report per customer per review (unique); reason enum + optional detail (≤500, admin-only); status open/resolved; resolved by hide (same tx) or dismiss. The reporting customer is never stored against the report in a consumer-visible way — deliberate.

5.3 Relationship Diagram

6. Services and Responsibilities

6.1 Eligibility service

A review cannot exist without a delivered order line — an in-transit order confers none (probe-verified cross-customer isolation). The response's canCreate already accounts for both the purchase and any existing review; existingReview.version is what the edit call must send back.

6.2 Review lifecycle service

The three frozen policies:

1. One review per (customer, product) — partial on deleted_at IS NULL; a repeat purchase edits, it does not add a second vote.
2. Auto-publish, moderate reactively — status defaults to published.
3. An edit of a published review returns it to pending and decrements the aggregate in the same transaction.

Decisions 2 and 3 are deliberately asymmetric: new content is trusted, changed content is not. That closes bait-and-switch without gating the honest first submission, and keeps pending a reachable state and the moderation queue meaningful.

6.3 Moderation service

reject and hide require a reason; hide also resolves the review's open reports in the same transaction. Recalculate is a repair path, never the write path — making it the write path reintroduces exactly what the GENERATED columns forbid. The hourly sweep repairs drifted aggregates and logs every repair.

6.4 The 23505 recovery note

The create path handles the unique-violation race (two concurrent creates). The recovery query must run on a live transaction — a code-review blocker found the recovery ran on an already-aborted transaction and returned 25P02, making the documented 409 unreachable. Fixed; the 409 is reachable.

7. Runtime Flows

7.1 Create

Why a review submission raises an outbox row. A review publishes immediately — moderation here is reactive, not a gate — so by the time anyone looks it is already on the product page. That is precisely why an operator needs telling: the window to catch something abusive is after publication.

The row's job is an internal notification email to the shop's own support address (InternalNoticeQueueService, SUPPORT_EMAIL), and the dispatcher's relay turns the same row into a live admin toast — the feed is a side-effect of dispatch, so an event with no job cannot reach the stream. The email is the durable half: a review posted at 3am is still in the inbox at 9.

review.reported works the same way and fires only on a report that was actually recorded. The insert is onConflictDoNothing, so a customer re-reporting the same review returns recorded: false and raises nothing — which is what stops one person generating an unbounded number of emails and toasts about one review.

Both are mapped in REALTIME_EVENT_MAP under Reviews_READ.

7.2 Moderation

8. Cache

The product detail's rating block and the summary endpoint ride this domain. The aggregate itself is never cached independently — it is a row read.

9. Jobs and Workers

The email dedupe key carries the review's version — a review can be hidden, restored and hidden again, and outbox inserts are onConflictDoNothing. An hourly sweep repairs drifted aggregates and logs every repair.

10. Security and Authorization

• Public routes: @Public() + ParseUUIDPipe on the product id — a missing pipe surfaced a 500 on an unauthenticated route (live-HTTP-caught); a non-uuid segment must be a clean 400.
• Customer routes: JwtAuthGuard; ownership-scoped (REVIEW_NOT_FOUND for another customer's review — no existence oracle). Create 5/hour (CUSTOMER_REVIEW_SUBMIT), report 10/hour (CUSTOMER_REVIEW_REPORT), both account-keyed.
• Admin: JwtAuthGuard + RoleGuard with Reviews_*; Reviews is already in the permission catalog — only permissions:sync is needed.
• The reporting customer is deliberately not identified and never will be.
• Every admin leaf module is in the Swagger allowlist (listing the aggregate does nothing).

11. Operational notes

• {productId} on the public review routes is the product's public_id uuid (the value exposed as basic.id) — not the slug, even though other mobile product routes are slug-keyed. A slug here returns 400.
• order is a reserved word — every hand-written statement quotes it as "order"; Drizzle quotes automatically, probes and psql sessions do not.
• The admin prefix is /api/admin/ (this module follows the eighteen-module convention, not the top-level orders/payments style); /api/admin/review-reports, never /api/admin/reviews/reports — the latter is matched against /api/admin/reviews/{id} and dies in ParseUUIDPipe.
• Text non-blank CHECKs use ~ '[^[:space:]]' — one-argument btrim strips spaces only and let a body of tabs and newlines through (probe-caught, fixed).