Design a customer issue resolution system. like in amazon and flipkart, customer is able to log complaints and customer care team resolve issues based on priority.

Let's dissect it

FR:

1. customer log complaint

2. complain is register in system and ranked based on priority

3. customer is able to track/delete/view- raised complaint

4. admin should be able to change status of raised complaint

5. user should be able to view complain in real time

NFR:

1. High Availability
   The system must ensure high availability (≥ 99.99%) to allow customers to log and track their complaints at any time without downtime. Services should be deployed in multiple availability zones and regions with failover mechanisms.

2. Eventual Consistency
   The system should favor eventual consistency over strong consistency to improve availability and partition tolerance. It is acceptable for different parts of the system (e.g., customer dashboard, internal SLA tracker) to show slightly stale data temporarily.

3. Durability and Fault Tolerance
   All customer complaints and resolution workflows must be stored durably using replicated, persistent storage (e.g., multi-AZ databases or distributed log systems like Kafka). The system should be able to recover from hardware or software failures without data loss or interruption in service.

4. Low Latency
   The system must provide low-latency APIs (< 100 ms) for customer-facing operations such as submitting complaints or checking status, to ensure a smooth and responsive user experience.

5. High Scalability
   The system should be horizontally scalable to handle millions of users and concurrent complaint submissions, especially during peak shopping seasons. It should support elastic scaling of services and data stores with load-based auto-scaling strategies

Daily Active Users (DAU): 100 million users

Average complaints per user per day: 5

Total complaints per day:100×10^6*5=500 M 
QPS=500M/10^5=5k QPS

Database Schema

1. User Table

Stores information about customers.

User (
  user_id      BIGINT PRIMARY KEY,
  name         VARCHAR(100),
  email        VARCHAR(100) UNIQUE,
  contact_no   VARCHAR(15)
)

2. Complaint Table

Stores details of each complaint raised by users.

Complaint (
  complaint_id    BIGINT PRIMARY KEY,
  user_id         BIGINT REFERENCES User(user_id),
  title           VARCHAR(255),
  description     TEXT,
  status          ENUM('OPEN', 'IN_PROGRESS', 'RESOLVED', 'CLOSED', 'ESCALATED'),
  category        VARCHAR(100),       -- e.g., 'Delivery', 'Payment', 'Product Issue'
  priority        ENUM('LOW', 'MEDIUM', 'HIGH', 'URGENT'),
  created_time    TIMESTAMP,
  modified_time   TIMESTAMP,
  attachment_id   BIGINT REFERENCES Attachment(attachment_id)
)

3. Comment Table

Tracks comments on complaints by both customers and support agents.

Comment (
  comment_id     BIGINT PRIMARY KEY,
  complaint_id   BIGINT REFERENCES Complaint(complaint_id),
  user_id        BIGINT REFERENCES User(user_id),
  comment_text   TEXT,
  reaction       VARCHAR(50),           -- e.g., 'thumbs_up', 'thumbs_down', 'like'
  timestamp      TIMESTAMP
)

4. Attachment Table

Stores metadata about file attachments linked to complaints.

Attachment (
  attachment_id  BIGINT PRIMARY KEY,
  complaint_id   BIGINT REFERENCES Complaint(complaint_id),
  url            TEXT                    -- S3 or blob storage URL
)

🔁 Relationships

• One User → can raise many Complaints

• One Complaint → can have many Comments

• One Complaint → can have multiple Attachments (can modify schema to support one-to-many if needed)

• One Comment → is made by one User on one Complaint\

API’s

📌 1. Register Complaint

POST /app/v1/complaint/register

✅ Request Body:

{
  "user_id": 12345,
  "title": "Item not delivered",
  "description": "I haven't received my order yet.",
  "category": "Delivery",
  "priority": "HIGH",
  "attachment_id": 987
}
response 
{
  "complaint_id": 456789,
  "message": "Complaint registered successfully",
  "status": "OPEN",
  "created_time": "2025-06-24T10:30:00Z"
}

📌 2. Get Complaint by ID

GET /app/v1/complaint?complaint_id=456789

✅ Response:

{
  "complaint_id": 456789,
  "user_id": 12345,
  "title": "Item not delivered",
  "description": "I haven't received my order yet.",
  "category": "Delivery",
  "priority": "HIGH",
  "status": "IN_PROGRESS",
  "created_time": "2025-06-24T10:30:00Z",
  "modified_time": "2025-06-24T12:00:00Z",
  "attachment_url": "https://s3.amazonaws.com/xyz/attachment987.png"
}

📌 3. Get Complaint Status

GET /app/v1/complaint/status?complaint_id=456789

✅ Response:

{
  "complaint_id": 456789,
  "status": "RESOLVED",
  "last_updated": "2025-06-24T16:00:00Z"
}

📌 4. Update Complaint Status

POST /app/v1/complaint/status

✅ Request Body:

{
  "user_id": 98765,
  "complaint_id": 456789,
  "status": "RESOLVED"
}
response
{
  "complaint_id": 456789,
  "new_status": "RESOLVED",
  "message": "Status updated successfully",
  "updated_time": "2025-06-24T16:00:00Z"
}

📌 5. Get All Complaints by User

GET /app/v1/complaints?user_id=12345

response {
  "user_id": 12345,
  "complaints": [
    {
      "complaint_id": 456789,
      "title": "Item not delivered",
      "status": "RESOLVED",
      "created_time": "2025-06-24T10:30:00Z"
    },
    {
      "complaint_id": 456790,
      "title": "Wrong item received",
      "status": "OPEN",
      "created_time": "2025-06-23T09:15:00Z"
    }
  ]
}

📌 6. Post a Comment on a Complaint

POST /app/v1/complaint/comment

request 
{
  "user_id": 12345,
  "complaint_id": 456789,
  "comment_text": "Please resolve this as soon as possible."
}
response {
  "comment_id": 78901,
  "message": "Comment added successfully",
  "timestamp": "2025-06-24T11:00:00Z"
}

HLD