name: chatwoot-vps-debug

description: Debug and integrate Chatwoot on VPS — access Rails console, PostgreSQL database, inspect containers, and discover inboxes/conversations/contacts schema.

triggers:

- "chatwoot debug"

- "chatwoot database"

- "chatwoot rails console"

- "chatwoot webhook"

- "chatwoot inbox api"

- "chatwoot container names"

- "chatwoot webhook token mismatch"

- "chatwoot 401 unauthorized webhook"

Chatwoot VPS Debug & Integration

Container Architecture (Docker on VPS)

Chatwoot runs on VPS with specific container names that differ from standard naming patterns:

`

root-rails-1 # Rails server (NOT deploy-vps-rails-1)

root-sidekiq-1 # Background worker

root-redis-1 # Redis

root-postgres-1 # PostgreSQL (banco: chatwoot_production)

`

Container discovery:

`bash

docker ps --format "{{.Names}} {{.Ports}}" | grep -i chat

`

Container names are NOT deploy-vps- — the debug workflow skill incorrectly assumed deploy-vps-rails-1. The actual names are root-.

Rails Console Access

Rails is NOT accessible via bash — use sh:

`bash

WRONG

docker exec root-rails-1 rails c -e production

CORRECT

docker exec root-rails-1 sh -c "cd /app && bundle exec rails c -e production"

`

For one-liner scripts (no interactive console):

`bash

docker exec root-rails-1 sh -c "cd /app && bundle exec rails runner ''" 2>&1 | grep -v WARN | grep -v RubyLLM | grep -v INFO

`

Database Access (PostgreSQL)

Connect directly to the Chatwoot database:

`bash

docker exec root-postgres-1 psql -U postgres -d chatwoot_production -c "SELECT ..." 2>&1 | grep -v "password"

`

Key Tables and Columns

Conversation Status Values

• - 0 = open
• - 1 = resolved
• - 2 = pending

Channel Types (inboxes)

• - Channel::Api = API inbox
• - Channel::Whatsapp = WhatsApp
• - Channel::Email, Channel::Facebook, etc.

---

Inbox Map (Rocha Sales — Account ID 1)

Total: 1.434 conversas abertas, 3 resolvidas, 1 pending (as of May 2026)

Chatwoot API

API Authentication

Use Personal Access Token generated from Chatwoot Dashboard:

`

Settings → Account → Access Tokens → Create Access Token

`

Pass as header: api_access_token:

Key API Endpoints

`

GET /api/v1/accounts/{account_id}/conversations

GET /api/v1/accounts/{account_id}/conversations/{id}

PUT /api/v1/accounts/{account_id}/conversations/{id} # status update

GET /api/v1/accounts/{account_id}/contacts

POST /api/v1/accounts/{account_id}/conversations/{id}/messages

`

Webhook Configuration

URL format: https://.supabase.co/functions/v1/chatwoot-webhook

Dashboard path: Settings → Integrations → Webhooks → Add new webhook

Available events:

Webhook Payload (conversation_created)

`json

{

"event": "conversation_created",

"id": 1760,

"status": "open",

"inbox_id": 17,

"account_id": 1,

"contact": {

"id": 2047,

"name": "Valdomiro Pimentel Barbos",

"email": "",

"phone_number": "+5562999991756"

},

"conversation": {

"id": 1760,

"display_id": 1760,

"status": "open",

"inbox_id": 17,

"meta": {

"assignee": { "id": 11, "name": "Renato Godoy" },

"sender": { "id": 2047, "name": "Valdomiro" }

}

}

}

`

Webhook Security

Set a verification token in Chatwoot dashboard and validate it:

`typescript

const token = Deno.env.get('CHATWOOT_WEBHOOK_TOKEN');

if (headers.get('chatwoot-webhook-token') !== token) {

return new Response('Unauthorized', { status: 401 });

}

`

Rails Runner Tips

Common Chatwoot model queries via Rails runner:

`bash

List inboxes

docker exec root-rails-1 sh -c "cd /app && bundle exec rails runner \

'puts Inbox.pluck(:id,:name,:channel_type).to_yaml'" 2>&1 | grep -v WARN | grep -v RubyLLM

List users

docker exec root-rails-1 sh -c "cd /app && bundle exec rails runner \

'puts User.order(:id).limit(10).pluck(:id,:name,:email,:type).to_yaml'" 2>&1 | grep -v WARN | grep -v RubyLLM

Sample conversation with contact

docker exec root-postgres-1 psql -U postgres -d chatwoot_production -c "

SELECT c.id, c.status, c.inbox_id, c.assignee_id, c.created_at,

con.name, con.email, con.phone_number

FROM conversations c

JOIN contacts con ON con.id = c.contact_id

ORDER BY c.created_at DESC

LIMIT 3;" 2>&1 | grep -v "password"

`

Integration with Painel do Corretor

Chatwoot can be used as a capture channel that feeds leads to Painel do Corretor:

1. Chatwoot fires conversation_created webhook

2. Edge Function extracts contact data

3. POST to https://api.paineldocorretor.net/crm/negocios with ApiKey header

4. INSERT into Supabase tasks table for Kanban

Painel ApiKey: Stored in user's memory (not in this skill for security).

Sidekiq Stuck Workers — Diagnosis and Fix (CRITICAL)

Symptoms:

Root Cause: Sidekiq container restarted WITHOUT the database environment variables. It defaults to POSTGRES_HOST=postgres (Docker DNS name), but with --network host that hostname does NOT resolve → every job fails immediately with DatabaseConnectionError. Workers hang on failed jobs, queue depth grows, Rails waits for Sidekiq to process broadcasts → everything slows to a crawl.

Diagnosis:

`bash

Check Sidekiq workers — all busy/hung is the key indicator

docker exec root-sidekiq-1 sh -c "ps aux | grep sidekiq" 2>&1 | grep -v grep

Check Sidekiq logs for connection errors

docker logs --since 5m root-sidekiq-1 2>&1 | grep -iE 'error|connection|failed|fail'

Check Redis queue depth

docker exec root-redis-1 redis-cli LLEN queue:critical 2>/dev/null || echo "0"

docker exec root-redis-1 redis-cli LLEN queue:default 2>/dev/null || echo "0"

Verify env vars are loaded in Sidekiq container

docker exec root-sidekiq-1 env | grep -E 'POSTGRES|REDIS'

CORRECT output should show:

POSTGRES_HOST=127.0.0.1

POSTGRES_PORT=5432

POSTGRES_DB=chatwoot_production

POSTGRES_USERNAME=postgres

POSTGRES_PASSWORD=postgres

REDIS_URL=redis://127.0.0.1:6379

If POSTGRES_HOST=postgres appears → BROKEN (needs 127.0.0.1)

`

Fix — Restart Sidekiq with correct env vars:

`bash

Kill stuck container (workaround if docker kill hangs)

kill -9 $(docker inspect root-sidekiq-1 --format '{{.State.Pid}}') 2>/dev/null || true

docker rm -f root-sidekiq-1 2>/dev/null || true

Start new Sidekiq with ALL required env vars explicitly set

docker run -d --name root-sidekiq-1 \

--network host \

--restart always \

-e POSTGRES_HOST=127.0.0.1 \

-e POSTGRES_PORT=5432 \

-e POSTGRES_DB=chatwoot_production \

-e POSTGRES_USERNAME=postgres \

-e POSTGRES_PASSWORD=postgres \

-e REDIS_URL=redis://127.0.0.1:6379 \

-e RAILS_ENV=production \

-e INSTALLATION_ENV=docker \

-e NODE_ENV=production \

-e SIDEKIQ_CONCURRENCY=20 \

chatwoot/chatwoot:latest \

bundle exec sidekiq -e production -C config/sidekiq.yml -c 20

`

Why --network host requires 127.0.0.1 for Postgres:

Performance verification after fix:

`bash

Rails should return fast (3-100ms) once Sidekiq is healthy

docker logs --since 1m root-rails-1 2>&1 | grep 'Completed 200' | tail -5

All Redis queues should be at 0

docker exec root-redis-1 redis-cli KEYS 'queue:*' | wc -l

Should show 0 jobs across all queues

`

Prevention: Update docker-compose.yml to include all env vars explicitly for the Sidekiq service, so future docker-compose up -d deployments get them automatically.

Docker Daemon Unresponsive — Diagnosis and Fix

Symptom: All docker commands hang/timing out (10-60s), even docker ps. Host commands like ps aux also hang. Load average spikes to 20+.

Root Cause: Docker daemon blocked on overlayfs or a container removal operation. The daemon itself is alive but not responding to API calls.

Diagnosis:

`bash

Check if docker daemon is alive

systemctl status docker | grep "Active:"

Try docker info (often faster than docker ps)

docker info 2>&1 | head -5

If it hangs → daemon is blocked

Check system load

uptime

cat /proc/loadavg

Load 20+ with docker commands timing out = daemon blocked

`

Fix — Restart Docker daemon:

`bash

sudo systemctl restart docker

Wait 10-15 seconds for daemon to fully restart

sleep 10

docker ps -a # Should now respond

`

After Docker restart: Check for containers that auto-started with wrong env vars (see section above: "Docker Restart Creates Sidekiq With Wrong Env Vars"). A new root-sidekiq-new container may have been created with --env-file pointing to Docker DNS names.

Rails Timeout / Slow Conversation Loading — Diagnosis and Fix

Symptom:

Root Cause: RAILS_MAX_THREADS is set to default (5). When conversation listing queries take >5 seconds (large datasets with many messages, contacts, assignee data to render), all 5 threads get blocked. New requests queue up, hit the 15s timeout, and return 500. The database itself is NOT slow — the Ruby/Rails layer serialization is the bottleneck.

Diagnosis:

`bash

Check Rails log for slow requests and timeout count

docker logs root-rails-1 --since 5m 2>&1 | grep -E 'Completed 500|Completed 200|Timeout'

Check Puma thread config inside container

docker exec root-rails-1 sh -c 'grep -A2 "max_threads" config/puma.rb'

Check current RAILS_MAX_THREADS value

docker exec root-rails-1 sh -c 'echo "RAILS_MAX_THREADS=$RAILS_MAX_THREADS"'

Verify DB query speed (if this is fast, problem is Rails layer, not DB)

docker exec root-postgres-1 psql -U postgres -d chatwoot_production \

-c "SELECT COUNT(*) FROM conversations WHERE inbox_id = 17 AND status = 0;" \

2>&1 | grep -v "password"

Should return in <5ms if index exists

`

Fix — Increase Puma threads to 15:

`bash

Edit docker-compose.yml — add to rails service environment block:

- RAILS_MAX_THREADS=15

Stop and restart Rails container

docker stop root-rails-1 && docker rm root-rails-1

cd /root && docker compose up -d rails

Verify the change

docker exec root-rails-1 sh -c 'echo "RAILS_MAX_THREADS=$RAILS_MAX_THREADS"'

Should output: RAILS_MAX_THREADS=15

Verify Puma started with correct thread count

docker logs root-rails-1 --since 1m 2>&1 | grep -E "Min threads|Max threads"

Should show: Min threads: 15 / Max threads: 15

Test site responsiveness

curl -s -o /dev/null -w "HTTP %{http_code} | %{time_total}s\n" https://chat.rochasalesseguros.com.br/app

Should return HTTP 200 in <3s (was timing out at 15s before)

`

Why this works: Puma runs with 15 threads instead of 5. Even if some threads are blocked on slow queries (5-10s each), the remaining threads can still accept new requests. The 15s timeout is no longer hit because requests don't queue up behind 5 blocked threads.

Prevention: Always set RAILS_MAX_THREADS=15 in the docker-compose.yml rails service environment when deploying Chatwoot on a VPS with non-trivial conversation counts (500+ per inbox).

WhatsApp Cloud "Inactive Channel" — Diagnosis and Fix

Symptom:

Root Cause: The WhatsApp Cloud channel exists in the database but Chatwoot considers it "inactive". Webhooks arrive and are processed by Sidekiq but rejected as inactive. This is a Meta WhatsApp Business API configuration issue, NOT a performance issue.

Diagnosis:

`bash

Check Sidekiq logs for inactive channel warnings

docker logs root-sidekiq-1 --since 1h 2>&1 | grep -i "inactive"

Check last conversation timestamps per inbox

docker exec root-postgres-1 psql -U postgres -d chatwoot_production \

-c "SELECT inbox_id, COUNT(*) as total, MAX(created_at) as ultima \

FROM conversations GROUP BY inbox_id ORDER BY ultima DESC LIMIT 10;" \

2>&1 | grep -v "password"

Check WhatsApp channel config in database

docker exec root-postgres-1 psql -U postgres -d chatwoot_production \

-c "SELECT id, phone_number, provider, updated_at FROM channel_whatsapp;" \

2>&1 | grep -v "password"

If updated_at is stale (days old), channel likely inactive

Check if webhooks are arriving (but being rejected)

docker logs root-sidekiq-1 --since 30m 2>&1 | grep "Webhooks::WhatsappEventsJob" | wc -l

`

Fix — Reactivate the WhatsApp channel via Dashboard:

1. Access: https://chat.rochasalesseguros.com.br/app/accounts/1/settings/inboxes/17/whatsapp-health

2. Check the channel status — look for "Inactive" or "Disconnected" state

3. Click "Sync" or "Reactivate" the WhatsApp Cloud channel

4. If that doesn't work, verify the Meta WhatsApp Business API credentials:

- phone_number_id: 812251211981254

- business_account_id: 1264409148885415

- api_key: stored in channel_whatsapp.provider_config->>'api_key'

Key indicator: If updated_at on channel_whatsapp is more than 24h old and webhooks are still arriving (visible in Sidekiq logs), the channel is likely inactive despite receiving events.

Docker Restart Creates Sidekiq With Wrong Env Vars (CRITICAL)

Symptom: After Docker daemon restart (systemctl restart docker), Sidekiq appears to be running but is CRASHED. docker logs root-sidekiq-X shows:

`

Missing secret_key_base for 'production' environment

`

Or Sidekiq silently fails to process jobs (queues stay at 0 but no errors visible because stdout is piped to /dev/null).

Root Cause: When Docker restarts, it may auto-start containers via --restart always policy. A new container gets created with --env-file /root/.env, but the .env file contains:

1. POSTGRES_HOST=postgres — BROKEN for --network host mode (Docker DNS unavailable)

2. POSTGRES_DATABASE — WRONG variable name (should be POSTGRES_DB)

3. SECRET_KEY_BASE=... value is preceded by a comment line in .env, so grep "SECRET_KEY_BASE" .env returns the COMMENT, not the actual secret (use grep "^SECRET_KEY_BASE" .env | tail -1 to get the real value)

Diagnosis:

`bash

Check which sidekiq container exists (may have been recreated as root-sidekiq-new)

docker ps -a | grep sidekiq

Check env vars — POSTGRES_HOST=postgres means BROKEN

docker exec root-sidekiq-X env | grep POSTGRES

If you see POSTGRES_DATABASE instead of POSTGRES_DB → wrong var name

If SECRET_KEY_BASE is missing → .env extraction failed

`

Fix — Always use inline env vars (NEVER rely on --env-file for Sidekiq):

`bash

Extract SECRET_KEY_BASE correctly (grep for line starting with SECRET_KEY_BASE, not comment)

SECRET_KEY=$(grep "^SECRET_KEY_BASE" /root/.env | tail -1 | sed 's/SECRET_KEY_BASE=//')

Kill, remove old container, recreate with ALL vars inline

docker rm -f root-sidekiq-1 2>/dev/null || true

docker run -d \

--name root-sidekiq-1 \

--network host \

--restart always \

-e POSTGRES_HOST=127.0.0.1 \

-e POSTGRES_PORT=5432 \

-e POSTGRES_DB=chatwoot_production \

-e POSTGRES_USERNAME=postgres \

-e POSTGRES_PASSWORD=postgres \

-e REDIS_URL=redis://127.0.0.1:6379 \

-e SECRET_KEY_BASE=${SECRET_KEY} \

-e RAILS_ENV=production \

-e INSTALLATION_ENV=docker \

-e NODE_ENV=production \

-e RAILS_LOG_TO_STDOUT=true \

-e RAILS_MAX_THREADS=5 \

chatwoot/chatwoot:latest \

bundle exec sidekiq -e production -C config/sidekiq.yml -c 20

`

NEVER use --env-file /root/.env for Sidekiq when using --network host — the .env file has Docker DNS hostnames (postgres, redis) that fail to resolve. Always inline the critical vars.

Network Architecture: Two Ways Chatwoot Connects to Redis/Postgres

Chatwoot's Rails container can run in two network modes, and the Redis/Postgres connection strings differ:

Mode 1: Docker Bridge Network (docker-compose default)

Containers on root_default network use Docker DNS:

`

REDIS_URL=redis://redis:6379

POSTGRES_HOST=postgres

POSTGRES_PORT=5432

`

Docker DNS resolves postgres → 172.21.0.5, redis → 172.21.0.2.

Mode 2: Host Network (--network host)

Rails shares the host's network namespace:

`

REDIS_URL=redis://127.0.0.1:6379

POSTGRES_HOST=127.0.0.1

POSTGRES_PORT=5432

`

Rails connects to Docker's proxy on host's 127.0.0.1 (docker-proxy processes).

CRITICAL: Never mix modes. If Postgres/Redis use Docker bridge but Rails uses --network host, the connection will fail or behave inconsistently.

Chatwoot 401 Unauthorized Login — Diagnostic Flow

When login returns 401 even with correct credentials, follow this checklist:

Step 1: Check if user exists in database

`bash

docker exec root-postgres-1 psql -U postgres -d chatwoot_production \

-c "SELECT email, name FROM users WHERE email = 'YOUR@EMAIL.com';"

`

If 0 rows → user doesn't exist, create via Chatwoot dashboard.

Step 2: Check if SECRET_KEY_BASE matches between .env and docker-compose.yml

`bash

.env value

grep SECRET_KEY_BASE /root/.env

docker-compose.yml value

grep SECRET_KEY_BASE /root/docker-compose.yml

`

If they differ → Rails can't verify session tokens → 401 on every request.

Fix: Always use the SAME SECRET_KEY_BASE when recreating the container:

`bash

SECRET=$(grep SECRET_KEY_BASE /root/docker-compose.yml | cut -d= -f2)

docker rm -f root-rails-1

docker run -d --name root-rails-1 --network host --restart always \

-e SECRET_KEY_BASE="$SECRET" \

-e FRONTEND_URL=https://chat.rochasalesseguros.com.br \

-e FORCE_SSL=true \

-e RAILS_SERVE_STATIC_FILES=true \

-e RAILS_ENV=production \

-e REDIS_URL=redis://127.0.0.1:6379 \

-e POSTGRES_HOST=127.0.0.1 \

-e POSTGRES_PORT=5432 \

-e POSTGRES_DB=chatwoot_production \

-e POSTGRES_USERNAME=postgres \

-e POSTGRES_PASSWORD=postgres \

-e INSTALLATION_ENV=docker \

-e NODE_ENV=production \

chatwoot/chatwoot:latest bundle exec rails s -p 3000 -b 0.0.0.0

`

Step 3: Check nginx is proxying WebSocket /cable correctly

The /cable location block must upgrade to WebSocket:

`nginx

location /cable {

proxy_pass http://127.0.0.1:3000/cable;

proxy_set_header Upgrade $http_upgrade;

proxy_set_header Connection "upgrade";

proxy_http_version 1.1;

}

`

If WebSocket fails, conversations will "process forever" and never load.

Step 4: Docker stuck — container removal timing out

Symptom: docker restart/kill/rm all hang/timing out. docker ps -a hangs.

Cause: Docker daemon blocked on overlayfs or container removal operation.

Workaround:

`bash

Start replacement container with different name while old one is being removed

docker run -d --name root-sidekiq-new \

--network host --restart always \

-e REDIS_URL=redis://127.0.0.1:6379 \

-e RAILS_ENV=production \

-e INSTALLATION_ENV=docker \

-e NODE_ENV=production \

--env-file /root/.env \

chatwoot/chatwoot:latest \

bundle exec sidekiq -e production -C config/sidekiq.yml -c 20

Wait for old container removal to complete, then replace

docker stop root-sidekiq-new

docker rm -f root-sidekiq-1 2>/dev/null || true

docker run -d --name root-sidekiq-1 \

--network host --restart always \

-e REDIS_URL=redis://127.0.0.1:6379 \

-e RAILS_ENV=production -e INSTALLATION_ENV=docker -e NODE_ENV=production \

-e SIDEKIQ_CONCURRENCY=20 \

--env-file /root/.env \

chatwoot/chatwoot:latest \

bundle exec sidekiq -e production -C config/sidekiq.yml -c 20

`

Step 5: Check Rails logs for login attempts

`bash

docker logs --tail 50 root-rails-1 2>&1 | grep -E 'SessionsController|401|Unauthorized|Completed 4'

`

Sidekiq Concurrency Override

The sidekiq.yml uses ERB ENV.fetch("SIDEKIQ_CONCURRENCY", 10) — the .env SIDEKIQ_CONCURRENCY may be commented out or missing. To force 20 workers, add -c 20 to the bundle exec command:

`bash

bundle exec sidekiq -e production -C config/sidekiq.yml -c 20

`

Step 6: Clear browser storage and retry

`javascript

localStorage.clear(); sessionStorage.clear();

document.cookie.split(";").forEach(c => document.cookie = c.replace(/^ +/, "").replace(/=.*/, "=;expires=" + new Date().toUTCString() + ";path=/"));

`

Step 7: Conversation Analysis — SQL Queries (PostgreSQL)

Port Conflicts: Vite Dev Server vs Chatwoot

Symptom: Chatwoot domain returns Vite JavaScript assets (/vite/assets/dashboard-*.js) instead of Chatwoot HTML.

Cause: Another project (e.g., rocha-sales-apresentacao) has a Vite dev server running on port 3000.

Diagnosis:

`bash

ss -tlnp | grep :3000

or

ps aux | grep vite | grep -v grep

`

Fix:

`bash

Find the process on port 3000

fuser 3000/tcp

Kill it

kill -9

Then restart Chatwoot if needed

docker restart root-rails-1

`

Conversation Analysis — SQL Queries (PostgreSQL)

Use these queries to analyze conversation quality, identify orphaned leads,

and extract full message threads for training reports.

Message Type Reference

`sql

-- message_type: 0 = Contact/Client (inbound), 1 = User/Agent (outbound), 2 = System

-- sender_type: 'Contact' = client, 'User' = agent

`

1. Map all inboxes with orphan detection

`sql

SELECT

i.id as inbox_id,

i.name as inbox,

u.name as agente,

COUNT(c.id) as total_conv,

COUNT(CASE WHEN c.assignee_id IS NULL THEN 1 END) as orfaas,

ROUND(100.0 * COUNT(CASE WHEN c.assignee_id IS NULL THEN 1 END) / NULLIF(COUNT(c.id), 0), 1) as pct_orfaas

FROM conversations c

JOIN inboxes i ON i.id = c.inbox_id

LEFT JOIN users u ON u.id = c.assignee_id

WHERE c.status = 0

GROUP BY i.id, i.name, u.name

ORDER BY total_conv DESC;

`

2. Find all orphaned conversations (no agent assigned)

`sql

SELECT c.id, c.created_at, i.name as inbox, con.name as contato,

COUNT(m.id) as total_msgs,

SUM(CASE WHEN m.message_type = 0 THEN 1 ELSE 0 END) as msgs_cliente,

SUM(CASE WHEN m.message_type = 1 THEN 1 ELSE 0 END) as msgs_agente

FROM conversations c

JOIN contacts con ON con.id = c.contact_id

JOIN inboxes i ON i.id = c.inbox_id

LEFT JOIN messages m ON m.conversation_id = c.id

WHERE c.status = 0 AND c.assignee_id IS NULL

GROUP BY c.id, c.created_at, i.name, con.name

HAVING SUM(CASE WHEN m.message_type = 0 THEN 1 ELSE 0 END) >= 3

ORDER BY total_msgs DESC;

`

3. List rich conversations (4+ client msgs, 2+ agent msgs) — for training analysis

`sql

SELECT

c.id as conv_id,

c.created_at,

i.name as inbox,

u.name as agente,

con.name as contato,

COUNT(m.id) as total_msgs,

SUM(CASE WHEN m.message_type = 0 THEN 1 ELSE 0 END) as msgs_cliente,

SUM(CASE WHEN m.message_type = 1 THEN 1 ELSE 0 END) as msgs_agente,

ROUND(SUM(CASE WHEN m.message_type = 0 THEN 1 ELSE 0 END)::numeric /

NULLIF(SUM(CASE WHEN m.message_type = 1 THEN 1 ELSE 0 END), 0), 1) as ratio_cliente_agente

FROM conversations c

JOIN contacts con ON con.id = c.contact_id

LEFT JOIN inboxes i ON i.id = c.inbox_id

LEFT JOIN users u ON u.id = c.assignee_id

LEFT JOIN messages m ON m.conversation_id = c.id

WHERE c.status = 0

AND con.name NOT LIKE '%~Rocha%'

AND con.name NOT LIKE '%Rocha Sales%'

AND con.name NOT LIKE '%Operador%'

AND con.name NOT LIKE '%Teste%'

AND i.name NOT LIKE '%Rocha Sales%'

GROUP BY c.id, c.created_at, i.name, u.name, con.name

HAVING SUM(CASE WHEN m.message_type = 0 THEN 1 ELSE 0 END) >= 4

AND SUM(CASE WHEN m.message_type = 1 THEN 1 ELSE 0 END) >= 2

ORDER BY total_msgs DESC

LIMIT 50;

`

4. Extract full message thread from one conversation

`sql

SELECT message_type, LEFT(content, 400) as msg, created_at, sender_type

FROM messages

WHERE conversation_id = [ID] AND message_type IN (0,1)

ORDER BY created_at

LIMIT 60;

`

5. Find conversations where client is dominant (ratio > 2:1) — possible ignored leads

`sql

SELECT

c.id, i.name as inbox, u.name as agente,

SUM(CASE WHEN m.message_type = 0 THEN 1 ELSE 0 END) as cliente_msgs,

SUM(CASE WHEN m.message_type = 1 THEN 1 ELSE 0 END) as agente_msgs,

ROUND(SUM(CASE WHEN m.message_type = 0 THEN 1 ELSE 0 END)::numeric /

NULLIF(SUM(CASE WHEN m.message_type = 1 THEN 1 ELSE 0 END), 0), 1) as ratio

FROM conversations c

JOIN inboxes i ON i.id = c.inbox_id

LEFT JOIN users u ON u.id = c.assignee_id

LEFT JOIN messages m ON m.conversation_id = c.id

WHERE c.status = 0

GROUP BY c.id, i.name, u.name

HAVING SUM(CASE WHEN m.message_type = 0 THEN 1 ELSE 0 END) >

SUM(CASE WHEN m.message_type = 1 THEN 1 ELSE 0 END) * 2

AND SUM(CASE WHEN m.message_type = 1 THEN 1 ELSE 0 END) >= 2

ORDER BY ratio DESC;

`

6. Find conversations with ZERO agent response — completely ignored

`sql

SELECT c.id, c.created_at, i.name as inbox, con.name as contato,

COUNT(m.id) as total_msgs

FROM conversations c

JOIN contacts con ON con.id = c.contact_id

JOIN inboxes i ON i.id = c.inbox_id

LEFT JOIN messages m ON m.conversation_id = c.id AND m.message_type = 1

WHERE c.status = 0

GROUP BY c.id, c.created_at, i.name, con.name

HAVING COUNT(CASE WHEN m.message_type = 1 THEN 1 END) = 0

AND COUNT(CASE WHEN m.message_type = 0 THEN 1 END) >= 2

ORDER BY c.created_at DESC;

`

7. Sales performance by inbox (who responds, who ignores)

`sql

SELECT

i.name as inbox,

COUNT(c.id) as total_conv,

COUNT(CASE WHEN c.assignee_id IS NULL THEN 1 END) as orfaas,

ROUND(100.0 * COUNT(CASE WHEN c.assignee_id IS NULL THEN 1 END) /

NULLIF(COUNT(c.id), 0), 1) as pct_orfaas,

COUNT(CASE WHEN c.assignee_id IS NOT NULL THEN 1 END) as atribuidas

FROM conversations c

JOIN inboxes i ON i.id = c.inbox_id

WHERE c.status = 0

GROUP BY i.name

ORDER BY total_conv DESC;

`

Key insight: 7 inboxes are 100% orphaned

As of June 2026, these inboxes have ZERO assignee_id set on any conversation:

Julietti Maer (87), Keila Mariana (79), Julia Lima (24)

→ Total: 872 clients completely ignored

Filtering automation contacts: Chatwoot creates contacts like ~Rocha Sales,

Rocha Sales Seguros, Comercial Flow from webhook payloads — these are NOT real

clients and must be excluded with con.name NOT LIKE '%~Rocha%' AND con.name NOT LIKE '%Rocha Sales%'.

Notes

CRITICAL: Docker OverlayFS Masking — File Changes Invisible

Problem: Chatwoot runs in a Docker container with an overlayfs filesystem. When you modify files inside the container (e.g., docker exec ... vim Sidebar.vue), those changes land in the overlay's top writable layer and mask the original files in the base image. The running Vite dev server serves the ORIGINAL files from the base image, NOT your modified versions.

Symptoms:

Diagnosis:

`bash

Check if Vite is using overlay-mounted files

docker exec root-rails-1 sh -c "cat /app/app/javascript/dashboard/components-next/sidebar/Sidebar.vue | grep -A2 'KANBAN'"

Check overlay mount points

docker exec root-rails-1 sh -c "mount | grep overlay"

Check file modification time inside container

docker exec root-rails-1 sh -c "stat /app/app/javascript/dashboard/components-next/sidebar/Sidebar.vue | grep Modify"

`

Solution — Trigger Rails Asset Precompilation (CORRECT method):

Chatwoot does NOT use pnpm build — that command does NOT exist in package.json. The correct rebuild command is:

`bash

Step 1: Install pnpm (if not present)

docker exec root-rails-1 sh -c "npm install -g pnpm"

Step 2: Ensure dependencies are up to date

docker exec root-rails-1 sh -c "cd /app && pnpm install"

Step 3: PRECOMPILE Rails assets (2-3 min) — this is the ONLY build command that works

docker exec root-rails-1 sh -c "cd /app && RAILS_ENV=production bundle exec rails assets:precompile 2>&1"

Step 4: Restart container to apply

docker restart root-rails-1

Step 5: Wait for Rails to start (~15s), then hard refresh browser (Ctrl+Shift+R)

`

Why this happens: The overlayfs mounts the container's writable layer ON TOP of the base image's files. Read operations see the top layer (your changes), but Rails' asset pipeline serves ORIGINAL files from the base image, not your modified versions. Running assets:precompile forces Rails to recompile ALL assets (including your modified Vue files) into the public/ directory, which bypasses the overlay issue.

How to verify assets were recompiled:

`bash

Check timestamp of compiled assets

docker exec root-rails-1 sh -c "stat /app/public/vite/assets/*.js 2>/dev/null | grep Modify"

Should show recent timestamp after precompilation

`

IMPORTANT — pnpm build does NOT exist:

`bash

This will FAIL with "ERR_PNPM_RECURSIVE_EXEC_FIRST_FAIL Command "build" not found"

docker exec root-rails-1 sh -c "cd /app && pnpm build"

`

Chatwoot's package.json scripts are: eslint, test, dev, build:sdk, story:dev, story:build — there is NO build script. The frontend assets are built via the Rails asset pipeline (rails assets:precompile), not via Vite CLI directly.

Alternative (faster iterative development for Vue files):

`bash

Copy the file out, edit it, copy it back, then touch to invalidate Vite cache

docker cp root-rails-1:/app/app/javascript/dashboard/components-next/sidebar/Sidebar.vue /tmp/Sidebar.vue

Edit /tmp/Sidebar.vue

docker cp /tmp/Sidebar.vue root-rails-1:/app/app/javascript/dashboard/components-next/sidebar/Sidebar.vue

docker exec root-rails-1 sh -c "touch /app/app/javascript/dashboard/components-next/sidebar/Sidebar.vue"

Then run assets:precompile and restart (OR wait for Vite HMR if dev mode is active)

`

Adding Sidebar Menu Items (Vue 3 components-next)

The Chatwoot sidebar uses Vue 3 Composition API in components-next/sidebar/Sidebar.vue (NOT the older dashboard/components/layout/sidebarComponents/).

File: /app/app/javascript/dashboard/components-next/sidebar/Sidebar.vue

Menu item format (~line 504-507):

`vue

{

icon: 'ion-kanban',

label: t('SIDEBAR.KANBAN'),

to: '/kanban',

roles: ['administrator', 'agent'],

}

`

Steps to add a menu item:

1. Add the route to kanban.routes.js (if new route)

2. Add the translation key to pt_BR/settings.json → "SIDEBAR.KANBAN": "Kanban Comercial"

3. Add the menu entry to Sidebar.vue under getSidebarItems()

4. Rebuild Vite assets (overlayfs issue — see above)

5. Hard refresh browser

Translation file location:

`

/app/app/javascript/dashboard/i18n/locale/pt_BR/settings.json

`

Key: SIDEBAR.KANBAN → "Kanban Comercial"

Dashboard App (Kanban Embed inside Chatwoot)

Chatwoot supports Dashboard Apps — iframes loaded inside Chatwoot at /app/accounts/:accountId/kanban.

Configuration: Settings → Integrations → Dashboard Apps → Add Dashboard App

URL format for embed:

`

https://chat.rochasalesseguros.com.br/kanban/?account_id=1&token=2AfB8VhS79FTmxG1dL8774RJ

`

Route handler (KanbanPage.vue → DashboardAppFrame → loads standalone Kanban in iframe)

nginx static serving (standalone Kanban):

`nginx

location /kanban/ {

alias /var/www/chatwoot-kanban/dist/;

try_files $uri $uri/ /kanban/index.html;

add_header Cache-Control "no-cache, no-store, must-revalidate";

}

`

The standalone Kanban app at /kanban/ is served by nginx as static files from /var/www/chatwoot-kanban/dist/. This is independent of the Chatwoot Rails app.

Kanban embedded in Chatwoot via DashboardApp — KanbanPage.vue renders which loads https://chat.rochasalesseguros.com.br/kanban/ in an iframe. This requires the Dashboard App route /accounts/:accountId/kanban to be registered and the assets to be precompiled.

Available Kanban stages (Painel do Corretor pipeline):

1. Novos Leads

2. Qualificação

3. Reunião Agendada

4. Proposta Enviada p/ cliente

5. Remarketing

Chatwoot Vue 3 Architecture (components-next)

Chatwoot uses two frontend stacks:

The Vue 3 version uses Composition API with menuItems computed property. The sidebar is rendered by Dashboard.vue which imports NextSidebar from 'dashboard/components-next/sidebar/Sidebar.vue'.

All new sidebar menu items MUST be added to the Vue 3 component (components-next/sidebar/Sidebar.vue). The Vue 2 sidebar is NOT used in production.

Menu item format in menuItems (~line 503-507):

`javascript

{

name: 'Kanban',

label: t('SIDEBAR.KANBAN'),

icon: 'i-lucide-layout-dashboard',

to: accountScopedRoute('kanban_comercial'),

},

`

Route file location:

`

/app/app/javascript/dashboard/routes/dashboard/kanban/kanban.routes.js

`

Dashboard App route registration (dashboard.routes.js):

`javascript

import { routes as kanbanRoutes } from './kanban/kanban.routes';

export default {

routes: [

{

path: frontendURL('accounts/:accountId'),

component: AppContainer,

children: [

...kanbanRoutes, // ← already included

...

],

},

],

};

`

Steps to add a sidebar menu item:

1. Add the route to kanban.routes.js (if new route)

2. Add the translation key to pt_BR/settings.json → "SIDEBAR.KANBAN": "Kanban Comercial"

3. Add the menu entry to components-next/sidebar/Sidebar.vue under menuItems computed

4. Rebuild assets — RAILS_ENV=production bundle exec rails assets:precompile (NOT pnpm build)

5. Restart container — docker restart root-rails-1

6. Hard refresh browser (Ctrl+Shift+R or Cmd+Shift+R)

Operational Issues & Fixes (Jun 2026)

Puma timeout / 500 errors on large inboxes

Symptom: Opening an inbox with many conversations (500+) causes HTTP 500 or timeout (15s+). Rails logs show Completed 500 in 15098ms (ActiveRecord: 9450.6ms).

Cause: Default RAILS_MAX_THREADS=5 is too low. With 5 threads and slow ActiveRecord queries (3-9s per call), all threads get blocked → timeouts.

Fix — edit docker-compose.yml, add to rails environment:

`yaml

environment:

- RAILS_MAX_THREADS=15

`

Then restart: docker stop root-rails-1 && docker rm root-rails-1 && docker compose up -d rails

Confirm: docker exec root-rails-1 sh -c 'echo "RAILS_MAX_THREADS=$RAILS_MAX_THREADS"' → should return 15

Sidekiq stuck / workers not processing jobs

Symptom: Messages arrive via webhook but no conversation is created. Sidekiq queues grow. Webhooks are processed by Rails (200 OK) but discarded by Sidekiq with "Inactive WhatsApp channel" warning.

Cause: Sidekiq has been running 2+ weeks without restart. Workers get stuck on RubyLLM + webhook jobs. 10/12 workers show traversed state.

Fix:

`bash

If docker kill times out (daemon unresponsive), use host-level kill

kill -9 $(ps aux | grep sidekiq | grep -v grep | awk '{print $2}')

sleep 2

docker rm -f root-sidekiq-1

Recreate with explicit env vars (--network host uses 127.0.0.1, not Docker DNS)

docker run -d --name root-sidekiq-1 --network host --restart always \

-e POSTGRES_HOST=127.0.0.1 -e POSTGRES_PORT=5432 \

-e POSTGRES_DB=chatwoot_production -e POSTGRES_USERNAME=postgres \

-e POSTGRES_PASSWORD=postgres -e REDIS_URL=redis://127.0.0.1:6379 \

-e RAILS_ENV=production -e INSTALLATION_ENV=docker \

-e NODE_ENV=production \

chatwoot/chatwoot:latest bundle exec sidekiq -e production -C config/sidekiq.yml -c 20

`

Critical networking note: Chatwoot Docker install uses --network host. Docker DNS (service names like postgres, redis) is NOT available in host network mode. Sidekiq MUST use 127.0.0.1 for all hosts.

Critical concurrency note: The env var SIDEKIQ_CONCURRENCY is ignored if sidekiq.yml has :concurrency: defined. Must use -c 20 CLI flag.

Note on logs: Sidekiq stdout/stderr are piped to /dev/null inside the container — docker logs returns nothing. Check via Rails logs or Redis sidekiq:processed keys instead.

Docker daemon becomes unresponsive

Symptom: docker ps, docker kill, docker exec all hang for 60+ seconds.

Fix:

`bash

sudo systemctl restart docker

`

Wait 10 seconds, then retry.

Webhook verification fails — token mismatch (#WBxP error)

Symptom: Meta/Facebook returns (#N/A:WBxP-XXXX) Não foi possível validar a URL de callback. Token in Meta matches token in DB but verification still fails with 401 Unauthorized.

Root Cause: Phone number format mismatch between channel_whatsapp.phone_number in DB and what Chatwoot's controller expects. The DB had a hyphen (+551191508-1701) while the URL used E.164 format without hyphen (+5511915081701). Rails valid_token? does a Channel::Whatsapp.find_by(phone_number: params[:phone_number]) lookup — since the formats don't match, it finds no channel → returns nil → 401 Unauthorized.

Diagnosis:

`bash

1. Check token in DB

docker exec root-postgres-1 psql -U postgres -d chatwoot_production \

-c "SELECT phone_number, length(phone_number) as len FROM channel_whatsapp;"

2. Check DB token matches what you entered in Meta

docker exec root-postgres-1 psql -U postgres -d chatwoot_production \

-c "SELECT provider_config->>'webhook_verify_token' as token FROM channel_whatsapp;"

3. Test the verification endpoint directly

curl -s "https://chat.rochasalesseguros.com.br/webhooks/whatsapp/+5511915081701?hub.mode=subscribe&hub.verify_token=TOKEN&hub.challenge=test123"

Returns "test123" = SUCCESS, "Error; wrong verify token" = FAIL

`

Fix — ensure DB phone_number matches the URL format WITHOUT hyphen (E.164):

`sql

-- CORRECT: E.164 format without hyphen

UPDATE channel_whatsapp SET phone_number = '+5511915081701' WHERE id = 2;

-- WRONG (has hyphen) — this was the original bug:

UPDATE channel_whatsapp SET phone_number = '+551191508-1701' WHERE id = 2;

`

Meta webhook URL format to use: The URL in Meta's Webhooks page should use E.164 without hyphen:

`

https://chat.rochasalesseguros.com.br/webhooks/whatsapp/+5511915081701

`

Key insight: The Chatwoot controller (Webhooks::WhatsappController#verify) looks up the channel by params[:phone_number] from the URL. If the URL has a different format than the DB, the lookup fails silently and returns 401 even with the correct token. Always ensure the phone_number in channel_whatsapp matches exactly what appears in the webhook URL path.

Rails runner commands always timeout

Symptom: bundle exec rails runner hangs until 60s timeout.

Workaround: Use direct psql or redis-cli queries instead of Rails runner. All data is accessible directly via the database.