Summary

When attempting to add or remove a label via the chat label API, the operation fails and returns a Prisma/Postgres error:

PrismaClientKnownRequestError: 
Invalid `prisma.$executeRawUnsafe()` invocation:
Raw query failed. Code: `42P10`. Message: `ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification`

How to Reproduce

• Send a POST request to /label/handleLabel/:instanceName with a body like:

  {
    "number": "120363418973228591",
    "labelId": "1",
    "action": "add"
  }

• The API returns status 400 with the error above.

Root Cause

• The raw SQL used in addLabel and removeLabel methods (see src/api/integrations/channel/whatsapp/whatsapp.baileys.service.ts) uses:

  INSERT INTO "Chat" (...) ON CONFLICT ("instanceId", "remoteJid") DO UPDATE ...

• Postgres requires a UNIQUE constraint or index on ("instanceId", "remoteJid") for ON CONFLICT to work, but the table "Chat" does not have this.

Solution Proposal

• Add a UNIQUE constraint (or unique index) on ("instanceId", "remoteJid") to the "Chat" table.
• Example:

  ALTER TABLE "Chat"
  ADD CONSTRAINT chat_instance_remotejid_unique UNIQUE ("instanceId", "remoteJid");

• Alternatively, update your Prisma schema and generate/apply a migration to enforce uniqueness at the ORM level.
• Before applying, check for and resolve existing duplicates:

  SELECT "instanceId", "remoteJid", COUNT(*) 
  FROM "Chat"
  GROUP BY "instanceId", "remoteJid"
  HAVING COUNT(*) > 1;

References to Related Code

• Raw SQL in addLabel:
  src/api/integrations/channel/whatsapp/whatsapp.baileys.service.ts
• Raw SQL in removeLabel:
  src/api/integrations/channel/whatsapp/whatsapp.baileys.service.ts
• Error propagation:
  src/api/integrations/channel/whatsapp/whatsapp.baileys.service.ts

Impact

• The API cannot reliably manage chat labels for WhatsApp contacts until this constraint is in place.

Additional Context

• If you use Prisma migrations, update your schema model for the Chat table:

  model Chat {
    id         String @id @default(cuid())
    instanceId String
    remoteJid  String
    labels     Json?
    createdAt  DateTime @default(now())
    updatedAt  DateTime @updatedAt
  
    @@unique([instanceId, remoteJid], name: "chat_instance_remotejid_unique")
  }

Suggested Steps

1. Audit the Chat table for duplicates.
2. Clean duplicates if they exist.
3. Add the unique constraint via migration or direct SQL.
4. Test the label API again.

Please prioritize this bug, as it blocks chat label management for WhatsApp contacts.