Fix Chatwoot DB Connection Instability and Implement Stale Conversation Cache Handling #2017

Vitordotpy · 2025-09-25T20:25:00Z

This pull request addresses two critical issues in the Chatwoot integration to improve its reliability and resilience. It introduces on-demand database connections to prevent intermittent connection failures and implements a retry mechanism to handle stale conversation data cached in Redis.

Fixes:

Prevent Intermittent DB Connection Errors: Refactored the ChatwootService to initialize the PostgreSQL client on-demand (getPgClient) rather than at service instantiation. This resolves a race condition where the client could be created with an incorrect or default database connection string, causing sporadic database does not exist errors.
Handle Stale Conversation Cache: Implemented a try-catch mechanism within the createMessage and sendData methods. When a 404 Not Found error is received from the Chatwoot API (indicating a stale conversation ID in the Redis cache), the system now automatically:
1. Deletes the invalid conversation entry from Redis.
2. Creates a new conversation in Chatwoot.
3. Retries the original message-sending operation with the new, valid conversation ID.

Enhancements:

Increased Resilience: The integration is now more robust against temporary inconsistencies between the Redis cache and the Chatwoot database state, preventing message delivery failures.
Improved Stability: Lazily initializing the database connection ensures that the correct, instance-specific credentials are used for every operation, eliminating a significant source of production errors.

Summary

This PR resolves critical stability issues in the Chatwoot integration by implementing on-demand database connections to fix intermittent connection errors and adding a retry mechanism that automatically handles stale conversation IDs from the Redis cache, ensuring more reliable message delivery.

Summary by Sourcery

Fix intermittent database connection errors and add automatic retry handling for stale Chatwoot conversation IDs by lazily initializing the Postgres client and evicting and recreating conversations on 404 errors.

Bug Fixes:

Prevent intermittent 'database does not exist' errors by initializing the PostgreSQL client on-demand.

Enhancements:

Automatically detect and recover from stale Chatwoot conversation IDs by evicting the Redis cache entry, creating a new conversation, and retrying message operations.

…ento de mensagens - Alterado método de obtenção da conexão PostgreSQL para ser assíncrono, melhorando a gestão de conexões. - Implementada lógica de retry para criação de mensagens e conversas, garantindo maior robustez em caso de falhas. - Ajustadas chamadas de consulta ao banco de dados para utilizar a nova abordagem de conexão. - Adicionada nova propriedade `messageBodyForRetry` para facilitar o reenvio de mensagens em caso de erro.

sourcery-ai · 2025-09-25T20:25:07Z

Reviewer's Guide

This PR refactors ChatwootService to initialize the PostgreSQL client on-demand via getPgClient to fix intermittent connection errors and adds a robust retry mechanism for stale conversation IDs: both createMessage and sendData flows now catch 404 errors, purge the invalid cache entry, create a fresh conversation, and retry the operation.

File-Level Changes

Change	Details	Files
Lazy initialization of PostgreSQL client	Replaced private pgClient field with async getPgClient() Updated all .query calls to await getPgClient() before execution	`src/api/integrations/chatbot/chatwoot/services/chatwoot.service.ts`
Automatic retry for stale conversation IDs	Wrapped core logic in doCreateMessage/doSendData helper functions Added try-catch around API calls to detect 404 errors On 404: delete Redis cache key, create new conversation, and retry original operation Introduced optional messageBodyForRetry parameter to carry retry context	`src/api/integrations/chatbot/chatwoot/services/chatwoot.service.ts`

Possibly linked issues

#0: PR implements try-catch to handle Chatwoot 'conversation not found' errors due to stale Redis cache, by deleting cache and retrying.

Tips and commands

Interacting with Sourcery

Trigger a new review: Comment @sourcery-ai review on the pull request.
Continue discussions: Reply directly to Sourcery's review comments.
Generate a GitHub issue from a review comment: Ask Sourcery to create an
issue from a review comment by replying to it. You can also reply to a
review comment with @sourcery-ai issue to create an issue from it.
Generate a pull request title: Write @sourcery-ai anywhere in the pull
request title to generate a title at any time. You can also comment
@sourcery-ai title on the pull request to (re-)generate the title at any time.
Generate a pull request summary: Write @sourcery-ai summary anywhere in
the pull request body to generate a PR summary at any time exactly where you
want it. You can also comment @sourcery-ai summary on the pull request to
(re-)generate the summary at any time.
Generate reviewer's guide: Comment @sourcery-ai guide on the pull
request to (re-)generate the reviewer's guide at any time.
Resolve all Sourcery comments: Comment @sourcery-ai resolve on the
pull request to resolve all Sourcery comments. Useful if you've already
addressed all the comments and don't want to see them anymore.
Dismiss all Sourcery reviews: Comment @sourcery-ai dismiss on the pull
request to dismiss all existing Sourcery reviews. Especially useful if you
want to start fresh with a new review - don't forget to comment
@sourcery-ai review to trigger a new review!

Customizing Your Experience

Access your dashboard to:

Enable or disable review features such as the Sourcery-generated pull request
summary, the reviewer's guide, and others.
Change the review language.
Add, remove or edit custom review instructions.
Adjust other review settings.

Getting Help

Contact our support team for questions or feedback.
Visit our documentation for detailed guides and information.
Keep in touch with the Sourcery team by following us on X/Twitter, LinkedIn or GitHub.

sourcery-ai

Hey there - I've reviewed your changes - here's some feedback:

Consider abstracting the retry logic for stale-conversation handling into a shared helper to reduce duplication between createMessage and sendData.
Instead of calling getPgClient() for every query, acquire the client once per operation and reuse it to minimize overhead and simplify the code paths.
Relying on error.toString().toLowerCase() to detect 404s can be brittle—use AxiosError.response.status or error.isAxiosError to more reliably handle HTTP status codes.

Prompt for AI Agents

Please address the comments from this code review:

## Overall Comments
- Consider abstracting the retry logic for stale-conversation handling into a shared helper to reduce duplication between createMessage and sendData.
- Instead of calling getPgClient() for every query, acquire the client once per operation and reuse it to minimize overhead and simplify the code paths.
- Relying on error.toString().toLowerCase() to detect 404s can be brittle—use AxiosError.response.status or error.isAxiosError to more reliably handle HTTP status codes.

## Individual Comments

### Comment 1
<location> `src/api/integrations/chatbot/chatwoot/services/chatwoot.service.ts:919` </location>
<code_context>
        const remoteJid = bodyForRetry.key.remoteJid;

</code_context>

<issue_to_address>
**suggestion (code-quality):** Prefer object destructuring when accessing and using properties. ([`use-object-destructuring`](https://docs.sourcery.ai/Reference/Rules-and-In-Line-Suggestions/TypeScript/Default-Rules/use-object-destructuring))

```suggestion
        const {remoteJid} = bodyForRetry.key;
```

<br/><details><summary>Explanation</summary>Object destructuring can often remove an unnecessary temporary reference, as well as making your code more succinct.

From the [Airbnb Javascript Style Guide](https://airbnb.io/javascript/#destructuring--object)
</details>
</issue_to_address>

### Comment 2
<location> `src/api/integrations/chatbot/chatwoot/services/chatwoot.service.ts:1101` </location>
<code_context>
        const remoteJid = bodyForRetry.key.remoteJid;

</code_context>

<issue_to_address>
**suggestion (code-quality):** Prefer object destructuring when accessing and using properties. ([`use-object-destructuring`](https://docs.sourcery.ai/Reference/Rules-and-In-Line-Suggestions/TypeScript/Default-Rules/use-object-destructuring))

```suggestion
        const {remoteJid} = bodyForRetry.key;
```

<br/><details><summary>Explanation</summary>Object destructuring can often remove an unnecessary temporary reference, as well as making your code more succinct.

From the [Airbnb Javascript Style Guide](https://airbnb.io/javascript/#destructuring--object)
</details>
</issue_to_address>

Sourcery is free for open source - if you like our reviews please consider sharing them ✨

_{Help me be more useful! Please click 👍 or 👎 on each comment and I'll use the feedback to improve your reviews.}

src/api/integrations/chatbot/chatwoot/services/chatwoot.service.ts

…e.ts aplicação de desestruturação de objetos que é uma boa prática do ts Co-authored-by: sourcery-ai[bot] <58596630+sourcery-ai[bot]@users.noreply.github.com>

- Implementada a função `handleStaleConversationError` para centralizar a lógica de tratamento de erros relacionados a conversas não encontradas. - A lógica de retry foi aprimorada para as funções `createMessage` e `sendData`, garantindo que as operações sejam reprocessadas corretamente em caso de falhas. - Removido código duplicado e melhorada a legibilidade do serviço Chatwoot.

andres99x · 2025-09-25T21:02:17Z

@Vitordotpy what issues are you experiencing because of this bug? I'm having issue where certain instances won't send messages to Chatwoot, but Chatwoot can send messages to Whatsapp. Wondering if this is related to it and solves it.

Vitordotpy · 2025-09-25T21:58:42Z

@Vitordotpy what issues are you experiencing because of this bug? I'm having issue where certain instances won't send messages to Chatwoot, but Chatwoot can send messages to Whatsapp. Wondering if this is related to it and solves it.

@andres99x I'm currently experiencing race conditions when Evolution tries to access the Chatwoot database, as well as other issues caused by the Redis cache, which causes an error when Evolution tries to send a message to a contact whose conversations were previously deleted. Evolution tries to use cached conversations that don't exist and fails to send the messages to Chatwoot. My solution was to add a retry when sending messages to Chatwoot. If the Redis cache is out of date, the Chatwoot service will retry without cached data.

sourcery-ai bot reviewed Sep 25, 2025

View reviewed changes

src/api/integrations/chatbot/chatwoot/services/chatwoot.service.ts Outdated Show resolved Hide resolved

src/api/integrations/chatbot/chatwoot/services/chatwoot.service.ts Outdated Show resolved Hide resolved

Vitordotpy and others added 3 commits September 25, 2025 17:30

Update src/api/integrations/chatbot/chatwoot/services/chatwoot.servic…

58b5561

…e.ts aplicação de desestruturação de objetos que é uma boa prática do ts Co-authored-by: sourcery-ai[bot] <58596630+sourcery-ai[bot]@users.noreply.github.com>

Update src/api/integrations/chatbot/chatwoot/services/chatwoot.servic…

8697329

…e.ts aplicação de desestruturação de objetos que é uma boa prática do ts Co-authored-by: sourcery-ai[bot] <58596630+sourcery-ai[bot]@users.noreply.github.com>

DavidsonGomes merged commit bd0c43f into EvolutionAPI:develop Sep 26, 2025
5 checks passed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Fix Chatwoot DB Connection Instability and Implement Stale Conversation Cache Handling #2017

Fix Chatwoot DB Connection Instability and Implement Stale Conversation Cache Handling #2017

Uh oh!

Vitordotpy commented Sep 25, 2025 •

edited by sourcery-ai bot

Loading

Uh oh!

sourcery-ai bot commented Sep 25, 2025 •

edited

Loading

Interacting with Sourcery

Customizing Your Experience

Getting Help

Uh oh!

sourcery-ai bot left a comment

Uh oh!

Uh oh!

Uh oh!

andres99x commented Sep 25, 2025

Uh oh!

Vitordotpy commented Sep 25, 2025

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

Fix Chatwoot DB Connection Instability and Implement Stale Conversation Cache Handling #2017

Fix Chatwoot DB Connection Instability and Implement Stale Conversation Cache Handling #2017

Uh oh!

Conversation

Vitordotpy commented Sep 25, 2025 • edited by sourcery-ai bot Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Fixes:

Enhancements:

Summary

Summary by Sourcery

Uh oh!

sourcery-ai bot commented Sep 25, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Reviewer's Guide

File-Level Changes

Possibly linked issues

Interacting with Sourcery

Customizing Your Experience

Getting Help

Uh oh!

sourcery-ai bot left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

andres99x commented Sep 25, 2025

Uh oh!

Vitordotpy commented Sep 25, 2025

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

Vitordotpy commented Sep 25, 2025 •

edited by sourcery-ai bot

Loading

sourcery-ai bot commented Sep 25, 2025 •

edited

Loading