CSAT (Customer Satisfaction)

Customer satisfaction survey module for collecting, viewing, filtering, and analyzing post-service ratings across multiple channels.

Overview

The CSAT module enables administrators to track and analyze customer satisfaction feedback. Customers submit ratings after interactions with tickets, carriers, or shops. The module provides a centralized table with advanced filtering, source platform identification, and inline survey visualization within tickets.

Ratings can originate from two channels: the web platform and WhatsApp. The module distinguishes these sources so teams can analyze satisfaction trends per channel.

Key Concepts

Concept	Description
Rating	A 1–5 star score submitted by a customer after an interaction
Source	The entity being rated: ticket, carrier, or shop
Source Platform	The channel where the rating was submitted: platform (web) or whatsapp
Follow-up Comment	An admin comment added after reviewing a rating
Reference ID	The ID of the related entity (ticket, carrier, or shop)
Resolution Agent	The admin who resolved the ticket associated with the rating
Resolution Time	Hours elapsed between ticket creation and resolution

Data Flow

Main Flow: Viewing CSAT Ratings

Flow: Inline CSAT in Ticket Chat

Flow: Admin Follow-up Comment

Backend Structure

Architecture

The CSAT backend follows the standard MVC pattern with a single route file, controller, and utility library:

backend/
├── routes/csat.routes.js         # Route definitions with Joi validation
├── controllers/csat.controller.js # Request handlers
└── libraries/csat.util.js         # SQL query builders and business logic

Query Strategy

The utility uses a two-phase query approach for the main table:

1. Phase 1 — ID query (buildQueryIds): Applies all filters, pagination, and sorting to get matching company_ratings.id values. This keeps the filtered query lightweight.
2. Phase 2 — Principal query (buildPrincipalQuery): Takes the IDs from phase 1 and JOINs to all related tables (companies, tickets, carriers, locales, etc.) for full row data.

This avoids expensive JOINs on the full dataset and enables efficient pagination.

Filter Builders

Method	Filter	SQL Condition
buildSourceFilters	Source type (ticket/carrier/shop)	rating.ticket_id IS NOT NULL (etc.)
buildCompanyFilters	Company IDs	rating.company_id = ?
whereSourcePlatform	Source platform (platform/whatsapp)	rating.source IN (?)
whereScoreRating	Star score	rating.rating IN (?)
whereAgents	Resolution agents	agents.id IN (?)
whereCarrier	Carrier	carrier.name IN (?)
whereDateCreation	Date range	rating.created_at BETWEEN ? AND ?

Database

Tables

Table	Purpose
company_ratings	Stores all CSAT ratings with score, comment, source references, and follow-up data
companies	Company (customer) information — joined for company name and locale
company_tickets	Ticket data — joined when the rating source is a ticket
company_ticket_comments	Ticket comments — used to determine resolution agent and resolution time
carriers	Carrier information — joined when the rating source is a carrier
shops	Shop information — joined when the rating source is a shop
catalog_ticket_types	Ticket type catalog — provides ticket type labels
catalog_ticket_statuses	Ticket status catalog — provides status badge display

Entity Relationship

Key Fields — company_ratings

Column	Type	Description
id	INT (PK)	Auto-increment
company_id	INT (FK)	Reference to companies
user_id	INT (FK)	User who submitted the rating
rating	INT	Star rating (1–5)
comment	TEXT	Customer's text feedback
source	ENUM('whatsapp','platform')	Channel where the rating was submitted
ticket_id	INT (FK, nullable)	Reference to company_tickets (if source is ticket)
carrier_id	INT (FK, nullable)	Reference to carriers (if source is carrier)
shop_id	INT (FK, nullable)	Reference to shops (if source is shop)
follow_up_comments	TEXT	Admin follow-up comment
follow_up_created_by	INT (FK, nullable)	Admin who created the follow-up
follow_up_created_at	DATETIME	When follow-up was created
follow_up_updated_by	INT (FK, nullable)	Admin who last updated the follow-up
follow_up_updated_at	DATETIME	When follow-up was last updated
is_active	TINYINT	Soft delete flag (1 = active)
created_at	DATETIME	When the rating was submitted

Key Decisions

Decision	Reasoning	Alternatives Considered
Two-phase ID + detail query	Filtering and pagination on a lightweight ID-only query avoids expensive JOINs on the full dataset. The principal query only JOINs for the page of results.	Single query with all JOINs (too slow with many filters), materialized view (too complex for this volume)
Polymorphic source via nullable FKs	A rating can reference a ticket, carrier, or shop. Using three nullable FK columns (ticket_id, carrier_id, shop_id) with a CASE expression keeps the schema simple.	Separate tables per source (too many tables), generic source_type + source_id (loses FK integrity)
Separate source column for platform	The source ENUM column tracks the channel (platform/whatsapp) independently from the rated entity. This enables channel analytics without affecting the existing source logic.	Reusing the source entity logic (conflates two different dimensions), adding a separate table (over-engineering)
Inline CSAT card in ticket chat	Showing the survey directly in the ticket conversation eliminates context-switching. The card only loads for terminal-status tickets to avoid unnecessary API calls.	Separate tab in ticket detail (requires extra navigation), always-visible section (wastes space for non-rated tickets)
company-request-view permission for ticket CSAT endpoint	Any admin who can view tickets should also see the related CSAT data. Using the same permission avoids creating a new one.	menu-csat permission (too restrictive — ticket-focused admins couldn't see it)

Dependencies

• Internal: Tickets module (ticket data, comments, resolution info), Companies module (company and locale data), Carriers module (carrier info)
• External: vue-star-rating (star rating display in frontend)

Related Documentation

• API Endpoints — Complete endpoint reference for CSAT
• UI Screens & Flows — Frontend screens and components
• User Guide — End-user instructions