# 🗄️ Evolution API — Banco de Dados PostgreSQL

> **Documento de referência definitivo** — Tudo que você precisa saber sobre o banco PostgreSQL da Evolution API (v2.4.0-rc2) rodando no Docker do projeto chamaleadCRM.
>
> ⚡ **Para que serve este documento:** Nunca mais precisar repetir a investigação. Consultas, schemas, credenciais, mapeamento @lid, exemplos reais — tudo aqui.

---

## 🎯 Sumário Executivo

| Métrica | Valor |
|---------|-------|
| **Tabelas** | 38 (schema `evolution_api`) |
| **Mensagens** | 25.486 |
| **Contatos** | 11.884 |
| **Chats** | 3.184 |
| **Instâncias WhatsApp** | 6 |
| **Registros totais** | ~60.000+ |
| **Integrações ativas** | n8n configurado (1), webhooks (2) |
| **Período coberto** | Out/2025 a Jun/2026 |

**O que você PODE fazer com SQL:** Extrair conversas, mapear contatos com números reais, analisar métricas de atendimento, cruzar dados de lead com conversas, exportar histórico completo — tudo que a API da Evolution não permite.

**O que você NÃO PODE:** Acessar grupos (não existe tabela `Group`/`Participant` nesta versão).

---

## 🐳 Arquitetura dos Containers

```
┌─────────────────────────────────────────────────────────┐
│                        HOST                               │
│                                                           │
│  ┌─────────────────────┐   ┌─────────────────────────┐   │
│  │  evolution-api:2.4.0 │   │   postgres:16           │   │
│  │  Porta 8080          │──▶│   Porta INTERNA 5432    │   │
│  │  .env embutido       │   │   EXPORT: 5433          │   │
│  └─────────────────────┘   └──────────┬──────────────┘   │
│                                       │                  │
│                              EXPOSTO EXTERNO ── 0.0.0.0:5433
│                                       │                  │
│  ┌─────────────────────┐              │                  │
│  │   redis:7           │              │                  │
│  │   Porta 6379        │              │                  │
│  └─────────────────────┘              │                  │
│                                        │                  │
│  ┌─────────────────────────────────────┴──────────────┐  │
│  │  evolution_db (schema: evolution_api)               │  │
│  │  38 tabelas                                         │  │
│  └────────────────────────────────────────────────────┘  │
└─────────────────────────────────────────────────────────┘
```

---

## 🔐 Credenciais & Conexão

### Via Docker (recomendado para queries rápidas)

```bash
# Conecta direto no container
docker exec -it klimadev_postgres psql -U klimadev -d evolution_db

# Com search_path já configurado
docker exec -e PGOPTIONS="-c search_path=evolution_api,public" \
  -it klimadev_postgres psql -U klimadev -d evolution_db
```

### Via host (localhost)

```bash
psql -h localhost -p 5433 -U klimadev -d evolution_db
```

### Via aplicação (URI de conexão)

```
postgresql://klimadev:klimadev@localhost:5433/evolution_db
```

### Detalhes

| Campo | Valor |
|-------|-------|
| **Host (interno Docker)** | `klimadev_postgres` |
| **Host (externo)** | `localhost` |
| **Porta interna** | 5432 |
| **Porta externa** | **5433** |
| **Database** | `evolution_db` |
| **Schema** | `evolution_api` |
| **Usuário** | `klimadev` |
| **Senha** | `klimadev` |
| **Provider** | PostgreSQL 16 |

---

## 🧬 O Sistema de @lid (LEIA ISTO PRIMEIRO)

O **LID** (Linked ID) é um identificador anônimo que o WhatsApp passou a usar para substituir números reais de telefone em várias situações. A Evolution API 2.4.0 lida com isso de forma... _interessante_.

### O Problema

```
remoteJid no Chat:   "157938983428198@lid"  ← NÃO é número de telefone!
remoteJid em Contato: "559885603100@s.whatsapp.net"  ← É o número real
```

### Como Resolver (@lid → Número Real)

Existem **3 estratégias**, em ordem de confiabilidade:

#### 🥇 Estratégia 1: Contact.remoteJid (já resolvido)

A tabela `Contact` tem **9.244** registros (78%) com número real em `remoteJid`. Simples:

```sql
SELECT "remoteJid", "pushName"
FROM "Contact"
WHERE "remoteJid" LIKE '%@s.whatsapp.net'
  AND "pushName" IS NOT NULL;
```

#### 🥈 Estratégia 2: Message.key['remoteJidAlt']

41% das mensagens (10.458) têm o número real dentro do JSON `key` no campo `remoteJidAlt`:

```sql
SELECT key->>'remoteJid' AS jid_lid,
       key->>'remoteJidAlt' AS jid_real,
       "pushName"
FROM "Message"
WHERE key->>'remoteJidAlt' IS NOT NULL
  AND key->>'remoteJidAlt' != '';
```

#### 🥉 Estratégia 3: IsOnWhatsapp (mapping universal)

A tabela `IsOnWhatsapp` mapeia números para @lid — mas a coluna `lid` **não armazena** o @lid real (só o literal `"lid"` ou vazio). Precisa de JOIN reverso:

```sql
-- Funciona para chats específicos onde você já tem o @lid
SELECT c."remoteJid", c.name, iw."remoteJid" AS numero_real
FROM "Chat" c
LEFT JOIN "IsOnWhatsapp" iw
  ON SPLIT_PART(c."remoteJid", '@', 1) = SPLIT_PART(iw."remoteJid", '@', 1)
WHERE c."remoteJid" LIKE '%@lid';
```

### Resumo Visual

| Tabela | remoteJid normalmente é... | Quantidade |
|--------|---------------------------|-----------|
| `Contact` | ✅ Número real (`@s.whatsapp.net`) | 9.244 (78%) |
| `Contact` | 🟡 @lid | 2.612 (22%) |
| `Chat` | ❌ @lid | 2.849 (89.5%) |
| `Chat` | ✅ Número real | 306 (9.6%) |
| `Chat` | 🟦 Grupo (@g.us) | 28 (0.9%) |
| `Message.key` | ❌ @lid | 19.146 (75%) |
| `Message.key` | ✅ Número real | 1.466 (6%) |
| `Message.key` | 🟦 Grupo | 4.838 (19%) |
| `Message.key.remoteJidAlt` | ✅ Número real | 10.458 (41%) |

---

## 📋 Schema Completo de Todas as Tabelas

> Ordenado por relevância. 38 tabelas no total, schema `evolution_api`.

### 📌 Tabelas Principais (com dados)

---

#### `Instance` — 6 registros

Cada instância = um número de WhatsApp conectado.

```sql
\d "Instance"
```

| Coluna | Tipo | Nulável | Descrição |
|--------|------|---------|-----------|
| `id` | `text` PK | NO | UUID único |
| `name` | `varchar` | NO | Nome amigável da instância |
| `connectionStatus` | `enum` | NO | `open` / `close` / `connecting` |
| `ownerJid` | `varchar` | YES | Número WhatsApp do dono (`559984626740@s.whatsapp.net`) |
| `profilePicUrl` | `varchar` | YES | URL da foto de perfil |
| `integration` | `varchar` | YES | `WHATSAPP-BAILEYS` |
| `number` | `varchar` | YES | Número de telefone |
| `token` | `varchar` | YES | Token de autenticação da instância |
| `clientName` | `varchar` | YES | `klimadev` |
| `profileName` | `varchar` | YES | Nome do perfil WhatsApp |
| `businessId` | `varchar` | YES | WhatsApp Business ID |
| `createdAt` | `timestamp` | YES | Data de criação |
| `updatedAt` | `timestamp` | YES | Última atualização |
| `disconnectionAt` | `timestamp` | YES | Quando desconectou |
| `disconnectionObject` | `jsonb` | YES | Detalhes do erro de desconexão |
| `disconnectionReasonCode` | `int` | YES | Código HTTP do erro |

**Instâncias existentes:**

| Nome | Status | Profile | Dono | Mensagens |
|------|--------|---------|------|-----------|
| `crmconsorcio_mc_consorcio_gabrielle` | close | Gabrielle - Gerente de Negócios Volkswagen | 55 86 9931-4310 | **13.057** |
| `crmconsorcio_mc_consorcio_98984603941` | close | Mc Representações | 55 98 8460-3941 | 5.492 |
| `chamalead-o9A9ZCTS` | close | Nexoo | 55 11 96703-5026 | 2.354 |
| `crmconsorcio_mc_consorcio_raiane_gerente_de_negocios_volkwagen` | **open** | Volkswagem | 55 99 8462-6740 | 1.789 |
| `chacara` | close | ... | 55 61 9521-8135 | 1.525 |
| `chamalead-2AWubmij` | close | Nexoo | 55 11 98073-3723 | 1.269 |

> 💡 A instância **`crmconsorcio_mc_consorcio_gabrielle`** domina ~51% de todos os dados.

---

#### `Contact` — 11.884 registros

Contatos do WhatsApp (salvos + não salvos que trocaram mensagem).

```sql
\d "Contact"
```

| Coluna | Tipo | Nulável | Descrição |
|--------|------|---------|-----------|
| `id` | `text` PK | NO | UUID único |
| `remoteJid` | `varchar` | NO | **Número real** (`@s.whatsapp.net`) **OU** `@lid` |
| `pushName` | `varchar` | **YES** | Nome definido pelo próprio contato no WhatsApp |
| `profilePicUrl` | `varchar` | YES | URL da foto de perfil |
| `instanceId` | `text` FK | NO | Instância que detectou este contato |
| `createdAt` | `timestamp` | YES | |
| `updatedAt` | `timestamp` | YES | |

**Estatísticas de pushName:**

| Métrica | Valor |
|---------|-------|
| Total de contatos | 11.884 |
| Com pushName preenchido | **10.149 (85.4%)** |
| Sem pushName | 1.735 (14.6%) |
| Com número real (`@s.whatsapp.net`) | 9.244 (78%) |
| Com @lid apenas | 2.612 (22%) |
| Com foto de perfil | ~3.847 |

---

#### `Chat` — 3.184 registros

Conversas/threads. Cada linha = um chat com um contato ou grupo.

```sql
\d "Chat"
```

| Coluna | Tipo | Nulável | Descrição |
|--------|------|---------|-----------|
| `id` | `text` PK | NO | UUID único |
| `remoteJid` | `varchar` | NO | JID do chat (**geralmente @lid**) |
| `name` | `varchar` | YES | Nome do contato/grupo (preenchido!) |
| `labels` | `jsonb` | YES | Array de IDs de labels associadas |
| `unreadMessages` | `int` | NO | Qtd de não lidas |
| `instanceId` | `text` FK | NO | Instância dona do chat |
| `createdAt` | `timestamp` | YES | |
| `updatedAt` | `timestamp` | YES | |

**Distribuição:**

| Tipo remoteJid | Quantidade | % |
|----------------|-----------|---|
| `@lid` (anônimo) | 2.849 | 89.5% |
| `@s.whatsapp.net` (número real) | 306 | 9.6% |
| `@g.us` (grupo) | 28 | 0.9% |

> 🔑 **Campo `name`**: Mesmo quando `remoteJid` é @lid, o campo `name` geralmente tem o nome do contato. Ex: `"Anny Natalia consultora autorizada Volkswagen"`, `"Guerreiro de deus"`, `"Francisco"`. Use este campo como fallback quando não conseguir resolver o número real.

---

#### `Message` — 25.486 registros

Todas as mensagens trocadas. **Tabela mais valiosa do banco.**

```sql
\d "Message"
```

| Coluna | Tipo | Nulável | Descrição |
|--------|------|---------|-----------|
| `id` | `text` PK | NO | UUID único |
| `key` | **`jsonb`** | NO | 🎯 **Metadados da mensagem (remoteJid, fromMe, etc)** |
| `pushName` | `varchar` | YES | Nome de quem enviou (98.9% preenchido!) |
| `participant` | `varchar` | YES | Em grupos, quem enviou |
| `messageType` | `varchar` | NO | Tipo: `conversation`, `imageMessage`, `audioMessage`, etc |
| `message` | **`jsonb`** | NO | 🎯 **Conteúdo da mensagem** |
| `contextInfo` | `jsonb` | YES | Info de contexto (resposta a outra msg) |
| `source` | `enum` | NO | `ios`, `android`, `web`, `unknown` |
| `messageTimestamp` | `int` | NO | Timestamp Unix |
| `instanceId` | `text` FK | NO | Instância |
| `status` | `varchar` | YES | `PENDING`, `SENT`, `DELIVERED`, `READ`, `ERROR` |
| `sessionId` | `text` | YES | Sessão de bot associada |
| `webhookUrl` | `varchar` | YES | Webhook que recebeu |
| `chatwoot*` | ... | YES | Integração com Chatwoot |

**Estrutura do campo `key` (JSON):**

```json
{
  "id": "3A3BAF11D02A12DD00A1",
  "fromMe": false,
  "remoteJid": "157938983428198@lid",
  "participant": "",
  "remoteJidAlt": "559885603100@s.whatsapp.net",
  "addressingMode": "lid"
}
```

| Chave | Descrição |
|-------|-----------|
| `id` | ID único da mensagem no WhatsApp |
| `fromMe` | `true` = enviada pela instância, `false` = recebida |
| `remoteJid` | **Geralmente @lid** — o JID do contato |
| `remoteJidAlt` | 🎯 **Número real!** Presente em 41% das mensagens |
| `participant` | Em grupos, o JID de quem enviou |
| `addressingMode` | `"lid"` se usa LID |

**Tipos de mensagem (28 tipos diferentes):**

| messageType | Total | Descrição |
|-------------|-------|-----------|
| `conversation` | 19.269 | Texto simples |
| `imageMessage` | 2.327 | Imagem |
| `audioMessage` | 1.709 | Áudio |
| `stickerMessage` | 688 | Sticker |
| `documentMessage` | 289 | Documento |
| `protocolMessage` | 206 | Protocolo interno |
| `reactionMessage` | 204 | Reação (emoji) |
| `videoMessage` | 183 | Vídeo |
| `interactiveMessage` | 173 | Botão interativo |
| `associatedChildMessage` | 133 | Mensagem filha |
| +18 outros tipos | 405 | Contact, location, poll, etc. |

**Distribuição por instância:**

| Instância | Mensagens | Período |
|-----------|-----------|---------|
| Gabrielle | 13.057 | Out/25 → Jun/26 |
| Mc Representações (98984603941) | 5.492 | Mar/26 → Jun/26 |
| Nexoo (chamalead-o9A9ZCTS) | 2.354 | Mai/26 → Jun/26 |
| Volkswagem (raiane) | 1.789 | Mar/26 → Mai/26 |
| chacara | 1.525 | Fev/26 → Abr/26 |
| Nexoo (chamalead-2AWubmij) | 1.269 | Jan/26 → Jun/06 |

---

#### `Label` — 34 registros

Etiquetas/Labels do WhatsApp Business.

| Coluna | Descrição |
|--------|-----------|
| `id` | UUID |
| `labelId` | ID numérico da label no WhatsApp |
| `name` | Nome da label (ex: `"Leads Gabi"`, `"Cliente Nati"`) |
| `color` | Código da cor (0-19) |
| `predefinedId` | Para labels pré-definidas do WhatsApp |
| `instanceId` | Instância |

---

#### `IsOnWhatsapp` — 14.554 registros

Cache de verificação se números estão no WhatsApp (inclui mapeamento de @lid).

| Coluna | Descrição |
|--------|-----------|
| `id` | UUID |
| `remoteJid` | **Número real** (`559885603100@s.whatsapp.net`) |
| `jidOptions` | Combinações de JID (com/sem código de país) |
| `lid` | 🟡 "lid" (literal) ou vazio — **não contém o @lid real** |
| `createdAt` / `updatedAt` | Timestamps |

> ⚠️ **Importante:** a coluna `lid` NÃO armazena o valor @lid. Ela contém o literal `"lid"` (1.221 registros) ou vazio (13.333). Serve como flag, não como chave de mapping direto.

---

#### `MessageUpdate` — 19.034 registros

Atualizações de status das mensagens (entregue, lida, etc).

| Coluna | Descrição |
|--------|-----------|
| `id` | UUID |
| `keyId` | ID da mensagem original |
| `remoteJid` | JID do contato |
| `fromMe` | Enviada por mim? |
| `status` | `SERVER_ACK`, `DELIVERY_ACK`, `READ`, `PLAYED` |
| `messageId` | FK para Message |
| `instanceId` | Instância |

---

#### `IntegrationSession` — 45 registros

Sessões ativas de bots (n8n, OpenAI, etc.).

| Coluna | Descrição |
|--------|-----------|
| `id` | UUID |
| `sessionId` | JID do contato na sessão |
| `remoteJid` | JID do contato |
| `pushName` | Nome do contato |
| `status` | `opened`, `closed`, `pending` |
| `awaitUser` | Aguardando resposta do usuário? |
| `parameters` | JSON com parâmetros |
| `context` | JSON com contexto |
| `botId` | FK para o bot |
| `type` | `n8n`, `openai`, etc. |

---

### 📌 Tabelas de Configuração (sem dados ou com poucos registros)

| Tabela | Registros | Finalidade |
|--------|-----------|------------|
| `Setting` | 6 | Configurações por instância (read receipts, always online) |
| `RuntimeConfig` | 4 | Configurações globais de runtime |
| `Session` | 3 | Sessões criptografadas do WhatsApp (creds) |
| `Webhook` | 2 | Webhooks configurados |
| `N8n` / `N8nSetting` | 1/1 | Integração com n8n |
| `Media` | 0 | Metadados de mídia (vazia nesta instalação) |
| `Template` | 0 | Templates de mensagem |
| `Proxy` | 0 | Config de proxy |
| `OpenaiBot` / `OpenaiCreds` / `OpenaiSetting` | 0/0/0 | Integração OpenAI |
| `Typebot` / `TypebotSetting` | 0/0 | Integração Typebot |
| `Dify` / `Evoai` / `Flowise` / ... | 0 | Integrações não configuradas |
| `Chatwoot` | 0 | Integração Chatwoot (desativada) |
| `Pusher` / `Rabbitmq` / `Sqs` / `Kafka` / `Websocket` | 0 | Event buses não ativos |
| `_prisma_migrations` | N/A | Migrations do Prisma (sistema) |

---

## 🚀 Galeria de Queries

### 🔍 Consultas Básicas

#### 1. Ver todas as instâncias e seus status

```sql
SELECT name, "connectionStatus" AS status,
       "profileName", "ownerJid",
       "createdAt", "disconnectionAt",
       "disconnectionReasonCode"
FROM "Instance"
ORDER BY "createdAt" DESC;
```

**Resposta:**
```
name                           | status | profileName | ownerJid                       | createdAt
-------------------------------+--------+-------------+--------------------------------+------------------------
 chamalead-2AWubmij            | close  | Nexoo       | 5511980733723@s.whatsapp.net   | 2026-06-01 22:18:49.526
 chamalead-o9A9ZCTS            | close  | Nexoo       | 5511967035026@s.whatsapp.net   | 2026-05-28 13:53:56.004
 crmconsorcio_mc_consorcio_98984603941 | close | Mc Representações | 559884603941@s.whatsapp.net | 2026-04-23 12:31:08.597
 ...
```

---

#### 2. Total de mensagens por instância

```sql
SELECT i.name AS instancia,
       COUNT(m.id) AS total_mensagens,
       MIN(to_timestamp(m."messageTimestamp")) AS primeira_msg,
       MAX(to_timestamp(m."messageTimestamp")) AS ultima_msg
FROM "Message" m
JOIN "Instance" i ON i.id = m."instanceId"
WHERE m."messageTimestamp" IS NOT NULL
GROUP BY i.name
ORDER BY total_mensagens DESC;
```

**Resposta:**
```
instancia                           | total | primeira               | ultima
------------------------------------+-------+------------------------+------------------------
crmconsorcio_mc_consorcio_gabrielle | 13057 | 2025-10-29 15:29:23+00 | 2026-06-06 21:31:35+00
crmconsorcio_mc_consorcio_98984603941 | 5492 | 2026-03-16 18:51:45+00 | 2026-06-05 15:41:26+00
chamalead-o9A9ZCTS                  |  2354 | 2026-05-27 15:52:10+00 | 2026-06-02 19:50:24+00
...
```

---

#### 3. Contatos com número real e pushName

```sql
SELECT "remoteJid", "pushName",
       "profilePicUrl" IS NOT NULL AS tem_foto,
       "instanceId"
FROM "Contact"
WHERE "remoteJid" LIKE '%@s.whatsapp.net'
  AND "pushName" IS NOT NULL
ORDER BY "updatedAt" DESC
LIMIT 20;
```

---

#### 4. Últimas 50 mensagens de texto (com número real)

```sql
SELECT COALESCE(m.key->>'remoteJidAlt',
                m.key->>'remoteJid') AS numero_real,
       m."pushName",
       m.message->>'conversation' AS texto,
       to_timestamp(m."messageTimestamp") AS data_hora,
       CASE WHEN (m.key->>'fromMe')::boolean THEN '→ ENVIADO'
            ELSE '← RECEBIDO' END AS direcao
FROM "Message" m
WHERE m."messageType" = 'conversation'
  AND m.message->>'conversation' IS NOT NULL
ORDER BY m."messageTimestamp" DESC
LIMIT 50;
```

**Resposta (amostra real):**
```
numero_real                    | pushName    | texto                                          | data_hora
-------------------------------+-------------+------------------------------------------------+------------------------
555199309404@s.whatsapp.net   | Você        | 00020101021226860014BR.GOV.BCB.PIX...          | 2026-06-06 21:31:35+00
                               | (instância) | (pix copia-e-cola)                             |
559885603100@s.whatsapp.net   | Gabrielle   | Posso ofertar lance esse mês ainda?            | 2026-03-03 19:36:08+00
                               | Léda        |                                                |
190207609581807@lid            | 190207...   | Tenho não                                      | 2026-03-05 19:35:19+00
                               | (pushName é o @lid quando não tem nome) |
558981159017@s.whatsapp.net   | Rodrigo     | Bom dia, Sr. Gilmar!                           | 2026-03-05 21:48:14+00
                               | Fonseca     |                                                |
```

---

### 🎯 Consultas Intermediárias

#### 5. Distribuição de tipos de mensagem

```sql
SELECT "messageType",
       COUNT(*) AS total,
       ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1) AS percentual
FROM "Message"
GROUP BY "messageType"
ORDER BY total DESC
LIMIT 10;
```

**Resposta:**
```
messageType         | total  | percentual
--------------------+--------+-----------
conversation        | 19269  | 75.6%
imageMessage        |  2327  |  9.1%
audioMessage        |  1709  |  6.7%
stickerMessage      |   688  |  2.7%
documentMessage     |   289  |  1.1%
protocolMessage     |   206  |  0.8%
reactionMessage     |   204  |  0.8%
videoMessage        |   183  |  0.7%
interactiveMessage  |   173  |  0.7%
associatedChildMessage | 133 |  0.5%
```

---

#### 6. Top 15 contatos mais ativos (por volume de mensagens)

```sql
SELECT m."pushName",
       COUNT(*) AS total_mensagens,
       COUNT(*) FILTER (WHERE (m.key->>'fromMe')::boolean) AS enviadas_pela_empresa,
       COUNT(*) FILTER (WHERE NOT (m.key->>'fromMe')::boolean) AS recebidas_do_cliente,
       MIN(to_timestamp(m."messageTimestamp")) AS primeira_msg,
       MAX(to_timestamp(m."messageTimestamp")) AS ultima_msg
FROM "Message" m
WHERE m."pushName" IS NOT NULL
  AND m."pushName" != ''
  AND m."pushName" != 'Você'
GROUP BY m."pushName"
ORDER BY total_mensagens DESC
LIMIT 15;
```

**Resposta:**
```
pushName                                | total | env|rec | primeira        | ultima
-----------------------------------------+-------+-----+------+-----------------+----------------
Gabrielle - Gerente de Negócios Volkswagen | 2095 | 1786| 309  | 2025-11-07      | 2026-06-06
157938983428198                          | 1498 |  14 |1484  | 2025-12-16      | 2026-06-06
Mc Representações                        | 1166 | 612 | 554  | 2026-04-23      | 2026-06-04
Nexoo                                    | 1047 | 490 | 557  | 2026-05-28      | 2026-06-02
Gabrielle Léda                           |  891 | 212 | 679  | 2025-11-27      | 2026-03-12
Volkswagem                               |  650 | 239 | 411  | 2026-03-25      | 2026-04-17
...                                      |  542 | ... | ...  | ...             | ...
Hilda Souza                              |  303 |  98 | 205  | 2026-02-08      | 2026-03-27
Jorge Henrique                           |  284 |  73 | 211  | 2026-02-21      | 2026-03-27
190207609581807                          |  234 |  27 | 207  | 2026-03-05      | 2026-03-20
```

---

#### 7. Conversa completa com um contato específico

```sql
-- Primeiro, descobre o remoteJid do contato
SELECT "remoteJid", "pushName" FROM "Contact" WHERE "remoteJid" LIKE '%559885603100%';

-- Depois, busca TODAS as mensagens (de qualquer campo que contenha o JID)
WITH jid_contato AS (
  SELECT '559885603100@s.whatsapp.net' AS jid
)
SELECT to_timestamp(m."messageTimestamp") AS data_hora,
       CASE WHEN (m.key->>'fromMe')::boolean THEN 'EMPRESA'
            ELSE 'CLIENTE' END AS direcao,
       m."pushName",
       m."messageType",
       CASE
         WHEN m."messageType" = 'conversation' THEN m.message->>'conversation'
         WHEN m."messageType" = 'imageMessage' THEN '📷 [Imagem]'
         WHEN m."messageType" = 'audioMessage' THEN '🎤 [Áudio]'
         WHEN m."messageType" = 'videoMessage' THEN '🎬 [Vídeo]'
         WHEN m."messageType" = 'documentMessage' THEN '📄 ' ||
              COALESCE(m.message->'documentMessage'->>'fileName', '[Documento]')
         WHEN m."messageType" = 'stickerMessage' THEN '🟦 [Sticker]'
         WHEN m."messageType" = 'reactionMessage' THEN '❤️ [Reação: ' ||
              COALESCE(m.message->'reactionMessage'->>'text', '') || ']'
         ELSE m."messageType"
       END AS conteudo
FROM "Message" m, jid_contato j
WHERE (m.key->>'remoteJid' = j.jid
       OR m.key->>'remoteJidAlt' = j.jid
       OR m."remoteJid" = j.jid)
  AND m."messageTimestamp" IS NOT NULL
ORDER BY m."messageTimestamp" ASC;
```

---

#### 8. Chats com labels e nomes das labels

```sql
WITH label_names AS (
  SELECT id, name FROM "Label"
)
SELECT c."remoteJid",
       c.name AS nome_chat,
       c.unreadMessages,
       (SELECT STRING_AGG(ln.name, ', ')
        FROM jsonb_array_elements_text(c.labels) AS lbl_id
        JOIN label_names ln ON ln.id = lbl_id
       ) AS labels_nomes
FROM "Chat" c
WHERE c.labels IS NOT NULL
  AND c.labels != '[]'::jsonb
ORDER BY c.unreadMessages DESC
LIMIT 20;
```

---

### 🧠 Consultas Avançadas

#### 9. Mapa completo: @lid → Número real + PushName

```sql
-- Junta Chat (que sempre tem @lid) com Contact (que tem número real)
-- e Message para confirmar o pushName mais recente
WITH recent_pushnames AS (
  SELECT DISTINCT ON (m.key->>'remoteJid')
         m.key->>'remoteJid' AS jid,
         m."pushName",
         m."messageTimestamp"
  FROM "Message" m
  WHERE m."pushName" IS NOT NULL
    AND m."pushName" != ''
  ORDER BY m.key->>'remoteJid', m."messageTimestamp" DESC
)
SELECT c."remoteJid" AS jid_lid,
       c.name AS nome_no_chat,
       ct."remoteJid" AS numero_real_contact,
       COALESCE(rp."pushName", ct."pushName", c.name) AS melhor_nome_disponivel,
       c."instanceId"
FROM "Chat" c
LEFT JOIN "Contact" ct
  ON SPLIT_PART(c."remoteJid", '@', 1) = SPLIT_PART(ct."remoteJid", '@', 1)
LEFT JOIN recent_pushnames rp ON rp.jid = c."remoteJid"
WHERE c."remoteJid" LIKE '%@lid'
ORDER BY c.name NULLS LAST
LIMIT 30;
```

---

#### 10. Métricas de atendimento por horário

```sql
-- Em que horário os clientes mais mandam mensagem?
SELECT EXTRACT(HOUR FROM to_timestamp(m."messageTimestamp")) AS hora,
       COUNT(*) AS total_mensagens,
       ROUND(AVG(COUNT(*)) OVER(), 1) AS media_por_hora
FROM "Message" m
WHERE NOT (m.key->>'fromMe')::boolean  -- só mensagens recebidas
  AND m."messageTimestamp" IS NOT NULL
GROUP BY hora
ORDER BY hora;
```

---

#### 11. Tempo médio de resposta da empresa (por hora)

```sql
WITH msg_pares AS (
  SELECT m.id,
         m."pushName",
         m."messageTimestamp",
         LAG(m."messageTimestamp") OVER (
           PARTITION BY m.key->>'remoteJid'
           ORDER BY m."messageTimestamp"
         ) AS timestamp_anterior,
         LAG((m.key->>'fromMe')::boolean) OVER (
           PARTITION BY m.key->>'remoteJid'
           ORDER BY m."messageTimestamp"
         ) AS anterior_foi_da_empresa,
         (m.key->>'fromMe')::boolean AS da_empresa
  FROM "Message" m
  WHERE m."messageType" = 'conversation'
    AND m."messageTimestamp" IS NOT NULL
)
SELECT AVG(mp."messageTimestamp" - mp.timestamp_anterior) || ' segundos' AS tempo_medio_resposta_segundos,
       COUNT(*) AS total_respostas
FROM msg_pares mp
WHERE mp.da_empresa = true
  AND mp.anterior_foi_da_empresa = false
  AND mp.timestamp_anterior IS NOT NULL
  AND (mp."messageTimestamp" - mp.timestamp_anterior) < 3600; -- ignora intervalos > 1h
```

---

#### 12. Contatos que NÃO estão no Contact (e como encontrá-los)

```sql
-- Contatos que aparecem em Chat mas não em Contact
SELECT c."remoteJid", c.name
FROM "Chat" c
LEFT JOIN "Contact" ct
  ON SPLIT_PART(c."remoteJid", '@', 1) = SPLIT_PART(ct."remoteJid", '@', 1)
     AND c."instanceId" = ct."instanceId"
WHERE ct.id IS NULL
  AND c."remoteJid" NOT LIKE '%@g.us';
```

---

#### 13. Grupos e seus participantes

```sql
-- Como não existe tabela Group/Participant nesta versão,
-- a melhor aproximação é via Message
SELECT DISTINCT m.key->>'remoteJid' AS grupo_jid,
       c.name AS nome_grupo,
       COUNT(DISTINCT m.key->>'participant') AS participantes_identificados,
       COUNT(*) AS total_mensagens_no_grupo,
       MIN(to_timestamp(m."messageTimestamp")) AS desde,
       MAX(to_timestamp(m."messageTimestamp")) AS ate
FROM "Message" m
LEFT JOIN "Chat" c ON c."remoteJid" = m.key->>'remoteJid'
WHERE m.key->>'remoteJid' LIKE '%@g.us'
GROUP BY m.key->>'remoteJid', c.name
ORDER BY total_mensagens_no_grupo DESC
LIMIT 20;
```

---

#### 14. Exportar CSV de todos os contatos com números reais

```sql
COPY (
  SELECT DISTINCT ON (c."remoteJid")
         c."remoteJid" AS numero_whatsapp,
         c."pushName" AS nome_contato,
         i.name AS instancia_origem,
         c."profilePicUrl" IS NOT NULL AS tem_foto,
         c."updatedAt" AS ultima_atualizacao
  FROM "Contact" c
  JOIN "Instance" i ON i.id = c."instanceId"
  WHERE c."remoteJid" LIKE '%@s.whatsapp.net'
    AND c."pushName" IS NOT NULL
  ORDER BY c."remoteJid", c."updatedAt" DESC
) TO '/tmp/contatos_evolution.csv'
WITH (FORMAT CSV, HEADER true, DELIMITER ';');
```

> 💡 Para executar COPY, precisa estar conectado como superusuário ou ter permissão. Alternativa: use `\copy` no psql.

---

## 🎨 Consultas Criativas & Inusitadas

#### 15. Nuvem de palavras das mensagens mais frequentes

```sql
-- Extrai palavras individuais das mensagens de texto
SELECT palavra, COUNT(*) AS freq
FROM "Message" m,
     LATERAL regexp_split_to_table(
       LOWER(COALESCE(m.message->>'conversation', '')),
       E'[\\s\\p{P}]+'
     ) AS palavra
WHERE m."messageType" = 'conversation'
  AND m.message->>'conversation' IS NOT NULL
  AND LENGTH(palavra) > 3
  AND palavra NOT IN ('que', 'para', 'com', 'por', 'dos', 'das', 'mais',
                      'como', 'mas', 'aos', 'nas', 'nos', 'sua', 'seu',
                      'pelo', 'pela', 'isso', 'aquele', 'entre', 'sobre',
                      'depois', 'antes', 'muito', 'quando', 'porque', 'você')
GROUP BY palavra
ORDER BY freq DESC
LIMIT 50;
```

---

#### 16. Detectar horário de pico de atendimento (heatmap)

```sql
SELECT EXTRACT(DOW FROM to_timestamp(m."messageTimestamp")) AS dia_semana,
       EXTRACT(HOUR FROM to_timestamp(m."messageTimestamp")) AS hora,
       COUNT(*) AS mensagens
FROM "Message" m
WHERE NOT (m.key->>'fromMe')::boolean  -- só mensagens de clientes
  AND m."messageTimestamp" IS NOT NULL
GROUP BY dia_semana, hora
ORDER BY dia_semana, hora;
```

| DOW | Hora | Msgs | Leitura |
|-----|------|------|---------|
| 1 (seg) | 08 | 92 | ⬜ |
| 1 (seg) | 09 | 187 | 🟨 |
| 1 (seg) | 10 | 298 | 🟧 |
| ... | ... | ... | ... |
| 5 (sex) | 14 | 312 | 🟥 PICO |
| 6 (sáb) | 09 | 45 | ⬜ |

---

#### 17. Detectar mensagens com PIX (útil para finanças)

```sql
SELECT m."pushName",
       m.message->>'conversation' AS texto,
       to_timestamp(m."messageTimestamp") AS data_hora,
       i.name AS instancia
FROM "Message" m
JOIN "Instance" i ON i.id = m."instanceId"
WHERE m."messageType" = 'conversation'
  AND m.message->>'conversation' ILIKE '%pix%'
  AND (m.key->>'fromMe')::boolean = true  -- enviadas pela empresa
ORDER BY m."messageTimestamp" DESC;
```

---

#### 18. Sequência de mensagens por sessão (identificar conversas)

```sql
-- Agrupa mensagens em sessões (gap > 30min = nova sessão)
WITH sessoes AS (
  SELECT *,
         SUM(CASE WHEN gap > 1800 THEN 1 ELSE 0 END)
           OVER (PARTITION BY "remoteJid" ORDER BY "messageTimestamp") AS sessao_id
  FROM (
    SELECT m.key->>'remoteJid' AS "remoteJid",
           m."messageTimestamp",
           m."pushName",
           m.message->>'conversation' AS texto,
           m."messageTimestamp" - LAG(m."messageTimestamp")
             OVER (PARTITION BY m.key->>'remoteJid' ORDER BY m."messageTimestamp")
           AS gap
    FROM "Message" m
    WHERE m."messageType" = 'conversation'
      AND m.key->>'remoteJid' = '559885603100@s.whatsapp.net'  -- mude o JID
  ) sub
)
SELECT sessao_id,
       MIN(to_timestamp("messageTimestamp")) AS inicio_sessao,
       MAX(to_timestamp("messageTimestamp")) AS fim_sessao,
       COUNT(*) AS total_msgs,
       STRING_AGG(CASE WHEN texto IS NOT NULL THEN LEFT(texto, 50) ELSE NULL END,
                  ' | ' ORDER BY "messageTimestamp") AS resumo
FROM sessoes
GROUP BY sessao_id
ORDER BY MIN("messageTimestamp");
```

---

#### 19. Média de mensagens por dia da semana

```sql
SELECT TO_CHAR(to_timestamp(m."messageTimestamp"), 'Day') AS dia_semana,
       COUNT(*)::numeric / COUNT(DISTINCT DATE(to_timestamp(m."messageTimestamp"))) AS media_por_dia,
       COUNT(*) AS total,
       COUNT(DISTINCT DATE(to_timestamp(m."messageTimestamp"))) AS dias_com_atividade
FROM "Message" m
WHERE m."messageTimestamp" IS NOT NULL
GROUP BY EXTRACT(DOW FROM to_timestamp(m."messageTimestamp")),
         TO_CHAR(to_timestamp(m."messageTimestamp"), 'Day')
ORDER BY EXTRACT(DOW FROM to_timestamp(m."messageTimestamp"));
```

---

#### 20. Contatos que mais enviam áudio (potencialmente irritante 😅)

```sql
SELECT m."pushName",
       COUNT(*) AS total_audios,
       SUM(COALESCE((m.message->'audioMessage'->>'seconds')::int, 0)) AS total_segundos,
       ROUND(SUM(COALESCE((m.message->'audioMessage'->>'seconds')::int, 0)) / 60.0, 1) AS total_minutos
FROM "Message" m
WHERE m."messageType" = 'audioMessage'
  AND NOT (m.key->>'fromMe')::boolean
  AND m."pushName" IS NOT NULL
  AND m."pushName" != ''
GROUP BY m."pushName"
ORDER BY total_audios DESC
LIMIT 10;
```

---

## 🔗 Integração com chamaleadCRM

Este banco é **separado** do banco do chamaleadCRM (que é SQLite via Prisma). Mas os dados podem ser cruzados:

### Como ligar Evolution → chamaleadCRM

O chamaleadCRM tem leads com números de telefone. O banco Evolution tem contatos do WhatsApp com números reais.

```sql
-- Query hipotética de JOIN entre os dois bancos
-- (precisa de dblink ou federação, ou exportar/importar)

-- 1. Do chamaleadCRM: exportar leads com telefone
-- 2. No Evolution: cruzar por telefone
-- Exemplo: SELECT l.name, l.phone, e.pushName, e.remoteJid
-- FROM chamalead.leads l
-- JOIN evolution.Contact e ON e.remoteJid LIKE '%' || REPLACE(l.phone, ' ', '') || '%'
```

**Webhooks existentes** que conectam os dois sistemas:

| Instância | Webhook | Eventos |
|-----------|---------|---------|
| `chamalead-2AWubmij` | `https://crm.chamalead.com/api/whatsapp/webhook` | MESSAGES_UPSERT, CONNECTION_UPDATE, SEND_MESSAGE |
| `chamalead-o9A9ZCTS` | `https://crm.chamalead.com/api/whatsapp/webhook` | MESSAGES_UPSERT, CONNECTION_UPDATE, SEND_MESSAGE |

---

## ⚠️ Problemas Conhecidos & Armadilhas

### 1. @lid é dominante, mas contornável

**Problema:** 89% dos chats e 75% das mensagens usam @lid.
**Solução:** Use `Contact.remoteJid` (78% dos contatos têm número real) ou `Message.key.remoteJidAlt` (41% das mensagens).

### 2. IsOnWhatsapp.lid não tem o @lid real

**Problema:** A coluna `lid` armazena o literal `"lid"` (string), não o valor do @lid. Não serve como lookup table direta.
**Solução:** Use `Contact` ou `Message.key.remoteJidAlt` para resolução.

### 3. Conversas sem pushName

**Problema:** 14.6% dos contatos não têm pushName. Em Message, quando pushName está vazio, às vezes o valor é o próprio @lid numérico.
**Solução:** O campo `Chat.name` geralmente tem o nome mesmo quando `Contact.pushName` é null.

### 4. Não existem tabelas Group/Participant

**Problema:** Não é possível listar membros de grupos via SQL nesta versão da Evolution API.
**Solução:** Extrair participantes de `Message.key.participant` para grupos específicos.

### 5. PostgreSQL exposto externamente

**Risco:** Porta 5433 aberta em `0.0.0.0` — qualquer um na rede pode tentar conectar.
**Mitigação:** Verificar firewall. A senha é `klimadev` (fraca).

### 6. A Evolution API "usa mal seu próprio DB"?

**Parcialmente verdade.** O banco armazena muitos dados úteis (25k+ mensagens, 11k+ contatos, pushName 85% preenchido), mas:
- O @lid é o formato padrão em vez do número real (escolha do WhatsApp, não da Evolution)
- A tabela IsOnWhatsapp não completa o mapping @lid → número (missing feature)
- Não há schema de groups/participants
- Mídia (Media table) está vazia (0 registros) mesmo com 2.327 imagens e 1.709 áudios

**SQL ainda é muito superior aos endpoints** para análise: você consegue extrair dados que a API não expõe (histórico completo, métricas, cruzamentos).

---

## 📊 Estatísticas Consolidadas

| Métrica | Valor |
|---------|-------|
| **Tabelas** | 38 |
| **Tabelas com dados** | 12 |
| **Mensagens totais** | 25.486 |
| **Mensagens de texto** | 19.269 (75.6%) |
| **Mensagens de imagem** | 2.327 (9.1%) |
| **Mensagens de áudio** | 1.709 (6.7%) |
| **Contatos** | 11.884 |
| **Contatos com número real** | 9.244 (78%) |
| **Contatos com pushName** | 10.149 (85.4%) |
| **Chats** | 3.184 |
| **Chats com @lid** | 2.849 (89.5%) |
| **Chats com número real** | 306 (9.6%) |
| **Grupos** | ~28 |
| **Instâncias** | 6 |
| **Update de mensagens (status)** | 19.034 |
| **Verificações IsOnWhatsapp** | 14.554 |
| **Labels** | 34 |
| **Maior instância** | Gabrielle — 13.057 msgs, 7.154 contatos |
| **Período mais antigo** | Outubro 2025 |
| **Última mensagem** | 6 Junho 2026 |

---

## 🧰 Cheatsheet Rápido

```bash
# Conectar
docker exec -it klimadev_postgres psql -U klimadev -d evolution_db

# Ver tabelas
\dt evolution_api.*

# Esquema de uma tabela
\d evolution_api."Message"

# Ver todos os registros de uma tabela pequena
SELECT * FROM evolution_api."Label";

# Sair
\q
```

### Comandos Úteis do psql

| Comando | O que faz |
|---------|-----------|
| `\dt evolution_api.*` | Lista tabelas |
| `\d evolution_api."Message"` | Schema da tabela |
| `\dn` | Lista schemas |
| `\l` | Lista databases |
| `\conninfo` | Informações da conexão |
| `\x on` | Modo expanded (vertical) |
| `\timing` | Mostra tempo de execução |
| `\! clear` | Limpa tela |
| `\q` | Sair |

---

## 📚 Apêndice: Dicionário de Dados Rápido

| Tabela | O que guarda | Linhas | Chave principal |
|--------|-------------|--------|-----------------|
| `Instance` | Instâncias WhatsApp conectadas | 6 | `id` (UUID) |
| `Contact` | Contatos detectados | 11.884 | `id` (UUID) |
| `Chat` | Conversas/threads | 3.184 | `id` (UUID) |
| `Message` | Mensagens trocadas | 25.486 | `id` (UUID) |
| `MessageUpdate` | Status update (entregue/lida) | 19.034 | `id` (UUID) |
| `IsOnWhatsapp` | Verificação de números no WA | 14.554 | `id` (UUID) |
| `Label` | Etiquetas do WhatsApp Business | 34 | `id` (UUID) |
| `IntegrationSession` | Sessões de bot ativas | 45 | `id` (UUID) |
| `Setting` | Configurações por instância | 6 | `id` (UUID) |
| `Session` | Sessão criptografada do WA | 3 | `id` (text) |
| `Webhook` | Webhooks configurados | 2 | `id` (UUID) |
| `RuntimeConfig` | Configs de runtime | 4 | `id` (int) |

---

## 💡 Ideias para Usos Criativos

- **CRM paralelo:** Extrair todos os contatos com pushName e importar como leads no chamaleadCRM
- **Análise de sentimentos:** Rodar NLP nas mensagens de texto para detectar satisfação
- **Dashboard de atendimento:** Médio de resposta por horário/dia/instância
- **Detector de leads quentes:** Contatos que enviaram mais de 3 mensagens nas últimas 24h
- **Exportação de mídia:** Baixar imagens das URLs em `message -> imageMessage -> url`
- **Auditoria de bots:** Analisar sessões do IntegrationSession para ver onde bots falharam
- **Relatório de PIX:** Extrair todas as menções a PIX para reconciliação financeira
- **Mapa de grupos:** Identificar membros comuns entre grupos via key.participant
- **Previsão de demanda:** Modelar volume de mensagens por dia da semana e horário

---

> 📅 **Documento gerado em:** 8 de Junho de 2026
> **Versão Evolution API:** 2.4.0-rc2
> **Metodologia:** Clair — investigação forense com evidência, não especulação