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:

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

id · text · required

Unique review identifier

serviceId · text · required

Reference to the service being reviewed

parentId · text

Parent review ID for threaded replies

• null for root reviews (customer reviews)
• Set to parent review ID for replies (provider responses)

Author Information

authorName · text · required

Display name of the reviewer

Example: "John Smith"

authorEmail · text · required

Email address of the reviewer

Used for notifications, not displayed publicly

authorId · text

Optional authenticated user ID

Links review to user account if authenticated

Reviews can be left by authenticated users (with authorId) or guests (authenticated via email verification).

Review Content

content · jsonb · required

Review text as structured JSON

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

Example:

{
  "text": "Excellent service! Very professional and timely.",
  "pros": ["Professional", "On time", "Good value"],
  "cons": ["Could improve communication"]
}

rating · integer · required

Star rating on a scale

Typically 1-5 stars, where:

• 1 = Very poor
• 2 = Poor
• 3 = Average
• 4 = Good
• 5 = Excellent

inLanguage · text · required

ISO 639-1 language code of the review

Example: "fr", "en", "es"

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

Moderation

status · text · default: "pending"

Review moderation status:

• pending - Awaiting moderator approval
• approved - Visible to public
• rejected - Hidden, rejected by moderator

Timestamps

createdAt · timestamp · required

When the review was submitted

updatedAt · timestamp · required

Last modification time (automatically updated)

Threaded Replies

The schema supports threaded discussions through the parentId field:

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

Each customer review can have multiple provider replies. This enables ongoing conversation and clarification.

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

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

Provider responses also go through moderation to ensure professional, appropriate communication.

Example Review & Reply

Customer Review (Root)

{
  "id": "review-123",
  "serviceId": "service-456",
  "parentId": null,
  "authorName": "Marie Dubois",
  "authorEmail": "[e-mail]",
  "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)

{
  "id": "review-124",
  "serviceId": "service-456",
  "parentId": "review-123",
  "authorName": "Plomberie Pro",
  "authorEmail": "[e-mail]",
  "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:

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

service_id index

Fast retrieval of all reviews for a service

Query: SELECT * FROM services_reviews WHERE service_id = ? AND status = 'approved'

status index

Moderation queue queries

Query: SELECT * FROM services_reviews WHERE status = 'pending' ORDER BY created_at

rating index

Filter reviews by rating level

Query: SELECT * FROM services_reviews WHERE service_id = ? AND rating >= 4

parent_id index

Retrieve all replies for a review

Query: SELECT * FROM services_reviews WHERE parent_id = ?

author_id index

User’s review history

Query: SELECT * FROM services_reviews WHERE author_id = ?

Integration with Bookings

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

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

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

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

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

Review Aggregation

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

// 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,
  });
}

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

Display Logic

Showing Reviews

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