> ## Documentation Index
> Fetch the complete documentation index at: https://mintlify.com/Ishaq74/concordia/llms.txt
> Use this file to discover all available pages before exploring further.

# Reviews & Ratings

> Review system with ratings, threaded replies, moderation, and provider responses for services

# Reviews & Ratings

Concordia's review system enables customers to rate and review services with threaded discussion support, moderation workflow, and provider response capabilities.

## Overview

The review system is implemented in `src/database/schemas/services_reviews.schema.ts` and provides:

* Star ratings (1-5 scale)
* Written review content
* Threaded replies (provider responses)
* Moderation workflow
* Multi-language support

## Review Schema

From `src/database/schemas/services_reviews.schema.ts`:

```typescript theme={null}
export const servicesReviews = pgTable("services_reviews", {
  id: text("id").primaryKey(),
  serviceId: text("service_id").notNull(),
  parentId: text("parent_id"),
  authorName: text("author_name").notNull(),
  authorEmail: text("author_email").notNull(),
  authorId: text("author_id"),
  content: jsonb("content").notNull(),
  rating: integer("rating").notNull(),
  status: text("status").notNull().default("pending"),
  inLanguage: text("in_language").notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});
```

## Review Fields

### Identification

<ParamField path="id" type="text" required>
  Unique review identifier
</ParamField>

<ParamField path="serviceId" type="text" required>
  Reference to the service being reviewed
</ParamField>

<ParamField path="parentId" type="text">
  Parent review ID for threaded replies

  * `null` for root reviews (customer reviews)
  * Set to parent review ID for replies (provider responses)
</ParamField>

### Author Information

<ParamField path="authorName" type="text" required>
  Display name of the reviewer

  Example: `"John Smith"`
</ParamField>

<ParamField path="authorEmail" type="text" required>
  Email address of the reviewer

  Used for notifications, not displayed publicly
</ParamField>

<ParamField path="authorId" type="text">
  Optional authenticated user ID

  Links review to user account if authenticated
</ParamField>

<Info>
  Reviews can be left by authenticated users (with `authorId`) or guests (authenticated via email verification).
</Info>

### Review Content

<ParamField path="content" type="jsonb" required>
  Review text as structured JSON

  Supports rich text formatting, allowing paragraphs, lists, etc.

  Example:

  ```json theme={null}
  {
    "text": "Excellent service! Very professional and timely.",
    "pros": ["Professional", "On time", "Good value"],
    "cons": ["Could improve communication"]
  }
  ```
</ParamField>

<ParamField path="rating" type="integer" required>
  Star rating on a scale

  Typically 1-5 stars, where:

  * `1` = Very poor
  * `2` = Poor
  * `3` = Average
  * `4` = Good
  * `5` = Excellent
</ParamField>

<ParamField path="inLanguage" type="text" required>
  ISO 639-1 language code of the review

  Example: `"fr"`, `"en"`, `"es"`
</ParamField>

<Warning>
  Root reviews (customer reviews) must have a `rating` value. Replies (provider responses) can have `rating` set to 0 or omit rating logic.
</Warning>

### Moderation

<ParamField path="status" type="text" default="pending">
  Review moderation status:

  * `pending` - Awaiting moderator approval
  * `approved` - Visible to public
  * `rejected` - Hidden, rejected by moderator
</ParamField>

### Timestamps

<ParamField path="createdAt" type="timestamp" required>
  When the review was submitted
</ParamField>

<ParamField path="updatedAt" type="timestamp" required>
  Last modification time (automatically updated)
</ParamField>

## Threaded Replies

The schema supports threaded discussions through the `parentId` field:

```typescript theme={null}
export const servicesReviewsRelations = relations(servicesReviews, ({ one, many }) => ({
  service: one(servicesListings, {
    fields: [servicesReviews.serviceId],
    references: [servicesListings.id],
  }),
  parent: one(servicesReviews, {
    fields: [servicesReviews.parentId],
    references: [servicesReviews.id],
    relationName: "review_replies",
  }),
  replies: many(servicesReviews, {
    relationName: "review_replies",
  }),
}));
```

### Review Structure

```mermaid theme={null}
graph TD
    A[Customer Review] --> B[Provider Response]
    A --> C[Provider Response 2]
    D[Customer Review 2] --> E[Provider Response]
```

<Note>
  Each customer review can have multiple provider replies. This enables ongoing conversation and clarification.
</Note>

## Review Workflow

### Customer Review Process

1. **Customer completes booking** - Booking status must be `completed`
2. **Customer writes review** - Submits rating and written feedback
3. **Review enters moderation** - Status: `pending`
4. **Moderator reviews** - Checks for spam, inappropriate content
5. **Approval decision**:
   * Approved → Status: `approved`, visible to public
   * Rejected → Status: `rejected`, hidden

```mermaid theme={null}
stateDiagram-v2
    [*] --> pending : Customer submits
    pending --> approved : Moderator approves
    pending --> rejected : Moderator rejects
    approved --> [*]
    rejected --> [*]
```

### Provider Response Process

1. **Review is approved** - Customer review visible
2. **Provider writes response** - Creates reply with `parentId`
3. **Response moderation** - Status: `pending`
4. **Moderator approves** - Status: `approved`
5. **Response displayed** - Shown under original review

<Info>
  Provider responses also go through moderation to ensure professional, appropriate communication.
</Info>

## Example Review & Reply

### Customer Review (Root)

```json theme={null}
{
  "id": "review-123",
  "serviceId": "service-456",
  "parentId": null,
  "authorName": "Marie Dubois",
  "authorEmail": "marie@example.com",
  "authorId": "user-789",
  "content": {
    "text": "Excellent plumbing service. Arrived on time, fixed the issue quickly, and cleaned up after. Very professional!",
    "pros": ["Professional", "Punctual", "Clean work"],
    "cons": ["Slightly expensive"]
  },
  "rating": 5,
  "status": "approved",
  "inLanguage": "fr",
  "createdAt": "2026-03-10T15:30:00Z",
  "updatedAt": "2026-03-10T16:00:00Z"
}
```

### Provider Response (Reply)

```json theme={null}
{
  "id": "review-124",
  "serviceId": "service-456",
  "parentId": "review-123",
  "authorName": "Plomberie Pro",
  "authorEmail": "contact@plomberiepro.fr",
  "authorId": "provider-101",
  "content": {
    "text": "Thank you Marie for your kind words! We strive to provide quality service and it's great to hear we met your expectations. We appreciate your business!"
  },
  "rating": 0,
  "status": "approved",
  "inLanguage": "fr",
  "createdAt": "2026-03-11T09:00:00Z",
  "updatedAt": "2026-03-11T09:15:00Z"
}
```

## Database Indexes

From `services_reviews.schema.ts`:

```sql theme={null}
CREATE INDEX idx_services_reviews_service ON services_reviews(service_id);
CREATE INDEX idx_services_reviews_status ON services_reviews(status);
CREATE INDEX idx_services_reviews_rating ON services_reviews(rating);
CREATE INDEX idx_services_reviews_parent ON services_reviews(parent_id);
CREATE INDEX idx_services_reviews_author ON services_reviews(author_id);
```

### Index Usage

<ParamField path="service_id index">
  Fast retrieval of all reviews for a service

  Query: `SELECT * FROM services_reviews WHERE service_id = ? AND status = 'approved'`
</ParamField>

<ParamField path="status index">
  Moderation queue queries

  Query: `SELECT * FROM services_reviews WHERE status = 'pending' ORDER BY created_at`
</ParamField>

<ParamField path="rating index">
  Filter reviews by rating level

  Query: `SELECT * FROM services_reviews WHERE service_id = ? AND rating >= 4`
</ParamField>

<ParamField path="parent_id index">
  Retrieve all replies for a review

  Query: `SELECT * FROM services_reviews WHERE parent_id = ?`
</ParamField>

<ParamField path="author_id index">
  User's review history

  Query: `SELECT * FROM services_reviews WHERE author_id = ?`
</ParamField>

## Integration with Bookings

From the specifications, reviews should only be allowed for completed bookings:

```typescript theme={null}
function canReview(userId, serviceId, bookings) {
  return bookings.some(booking => 
    booking.serviceId === serviceId &&
    booking.customerId === userId &&
    booking.status === 'completed'
  );
}
```

<Warning>
  Always verify that the reviewer has actually used the service before allowing review submission. Check for a completed booking.
</Warning>

## Moderation Guidelines

### Approve Reviews That:

* Provide honest, constructive feedback
* Are based on actual service experience
* Use respectful language
* Include specific details about the service

### Reject Reviews That:

* Contain profanity or hate speech
* Are spam or promotional content
* Include personal attacks
* Are from users without completed bookings
* Contain false or misleading information

<Note>
  Moderators should review both customer reviews and provider responses to maintain a professional, helpful review section.
</Note>

## Review Aggregation

From `services_listings.schema.ts`, services can cache aggregate rating data:

```typescript theme={null}
// In application logic, calculate and cache
function updateServiceRating(serviceId) {
  const reviews = getApprovedReviews(serviceId);
  const avgRating = reviews.reduce((sum, r) => sum + r.rating, 0) / reviews.length;
  const count = reviews.length;
  
  // Update service listing
  updateService(serviceId, {
    averageRating: avgRating.toFixed(1),
    reviewCount: count,
  });
}
```

<Info>
  Trigger rating recalculation when reviews are approved, rejected, or deleted to keep aggregate data accurate.
</Info>

## Display Logic

### Showing Reviews

```typescript theme={null}
function getDisplayableReviews(serviceId) {
  // Get root reviews (customer reviews)
  const rootReviews = getReviews({
    serviceId,
    parentId: null,
    status: 'approved'
  });
  
  // For each root review, get replies
  return rootReviews.map(review => ({
    ...review,
    replies: getReviews({
      parentId: review.id,
      status: 'approved'
    })
  }));
}
```

### Sorting Options

* **Most Recent**: `ORDER BY created_at DESC`
* **Highest Rated**: `ORDER BY rating DESC, created_at DESC`
* **Lowest Rated**: `ORDER BY rating ASC, created_at DESC`
* **Most Helpful**: Requires additional helpful votes system

## Best Practices

### For Customers

1. **Be specific** - Mention particular aspects of the service
2. **Be fair** - Balance pros and cons
3. **Be timely** - Review soon after service completion
4. **Be respectful** - Critique service, not person

### For Providers

1. **Respond professionally** - Thank customers, address concerns
2. **Be timely** - Respond within 24-48 hours
3. **Be constructive** - Turn negative reviews into learning opportunities
4. **Don't argue** - Stay professional even with unfair reviews

### For Moderators

1. **Review promptly** - Process within 24 hours
2. **Be consistent** - Apply guidelines uniformly
3. **Be fair** - Consider context and intent
4. **Document reasons** - Note why reviews are rejected

### For Developers

1. **Verify booking completion** - Check completed booking before allowing review
2. **Prevent duplicates** - One review per customer per service
3. **Sanitize content** - Clean HTML/scripts from review text
4. **Send notifications** - Notify provider of new reviews, customer of responses
5. **Cache aggregates** - Update service average rating on review changes