Mapa do Banco de Dados PostgreSQL
Host: 72.62.137.163 | Banco: postgres | User: zpro Schema: public | ORM: Sequelize (TypeScript)
Tabelas principais
Tickets (core do atendimento)
Tickets {
id SERIAL PRIMARY KEY
status ENUM('open','pending','closed')
channel VARCHAR -- 'waba','baileys','gupshup','dialog360','instagram',...
contactId INT → Contacts.id
userId INT → Users.id (agente responsavel)
queueId INT → Queues.id
whatsappId INT → Whatsapps.id (canal de conexao)
tenantId INT → Tenants.id
chatFlowId INT → ChatFlows.id (chatbot ativo)
isGroup BOOLEAN
isBot BOOLEAN
isPaused BOOLEAN
pauseReason TEXT
unreadMessages INT
lastMessageAt TIMESTAMP
firstResponseAt TIMESTAMP
closedAt TIMESTAMP
isFarewellMessage BOOLEAN
createdAt TIMESTAMP
updatedAt TIMESTAMP
}Messages
Messages {
id UUID PRIMARY KEY
body TEXT
fromMe BOOLEAN
read BOOLEAN
mediaType VARCHAR -- 'chat','image','video','audio','document','sticker'
mediaUrl TEXT
mediaName VARCHAR
ack INT -- 0=aguardando,1=enviado,2=entregue,3=lido,4=reproduzido
ticketId INT → Tickets.id
contactId INT → Contacts.id
quotedMsgId VARCHAR
scheduleDate TIMESTAMP
isDeleted BOOLEAN
isEdited BOOLEAN
editedBody TEXT
isForwarded BOOLEAN
tenantId INT
createdAt TIMESTAMP
}Contacts
Contacts {
id SERIAL PRIMARY KEY
name VARCHAR
number VARCHAR -- numero normalizado (55DDD...)
email VARCHAR
profilePicUrl TEXT
cpf VARCHAR
birthdayDate VARCHAR
firstName VARCHAR
lastName VARCHAR
businessName VARCHAR
isGroup BOOLEAN
tenantId INT
walletId INT → Wallets.id
kanban INT → Kanbans.id
lid VARCHAR -- WhatsApp LID (novo formato)
messengerId VARCHAR
instagramPK VARCHAR
-- campos de hub/outros canais...
}ContactTags (relacao N:N)
ContactTags {
contactId INT → Contacts.id
tagId INT → Tags.id
tenantId INT
}API Frontend: PUT /contact-tags/:contactId com body { tags: number[] }
Whatsapps (canais/sessoes)
Whatsapps {
id SERIAL PRIMARY KEY
name VARCHAR
status ENUM('CONNECTED','DISCONNECTED','TIMEOUT','OPENING','qrcode')
type VARCHAR -- 'baileys','waba','gupshup','dialog360','instagram',...
number VARCHAR
tokenAPI VARCHAR -- phoneNumberId (WABA) ou apiKey (outros)
bmToken VARCHAR -- token de acesso Meta (WABA)
wabaId VARCHAR
wabaVersion VARCHAR -- 'v19.0'
wavoipToken TEXT -- tokens WaVoIP separados por virgula
baileysLib VARCHAR -- 'fork','v7','infiniteapi'
queueId INT → Queues.id
tenantId INT
isDefault BOOLEAN
}BaileysSessions (estado Signal Protocol)
BaileysSessions {
id SERIAL PRIMARY KEY
sessionId VARCHAR -- identificador da sessao Baileys
session TEXT -- JSON com chaves criptograficas Signal Protocol
type VARCHAR -- tipo de dado da sessao
tenantId INT
}⚠️ Esta tabela e limpa pelo
resetBaileysConnection. Se corrompida → PreKeyError → mensagens nao chegam.
Queues (filas)
Queues {
id SERIAL PRIMARY KEY
name VARCHAR
color VARCHAR
greetingMessage TEXT
outOfHoursMessage TEXT
farewellMessage TEXT
isActive BOOLEAN
businessHours JSONB -- horarios de funcionamento
autoDistribute BOOLEAN
autoDistributeLimit INT
tenantId INT
}Users (agentes)
Users {
id SERIAL PRIMARY KEY
name VARCHAR
email VARCHAR UNIQUE
password VARCHAR (hash)
profile ENUM('user','admin','super','superadmin','custom')
isOnline BOOLEAN
tenantId INT
whatsappId INT → Whatsapps.id
}Tenants (empresas/clientes)
Tenants {
id SERIAL PRIMARY KEY
name VARCHAR
status ENUM('active','inactive','blocked')
smsToken VARCHAR
conectaSmsToken VARCHAR
livsonSmsToken VARCHAR
infobipRcsApiKey VARCHAR
infobipRcsBaseUrl VARCHAR
sinchRcsApiKey VARCHAR
sinchRcsSenderId VARCHAR
zenviaRcsToken VARCHAR
-- muitos outros tokens de integracao...
}Pipelines / Stages / Opportunities (Funil)
Pipelines {
id SERIAL PRIMARY KEY
name VARCHAR
tenantId INT
}
Stages {
id SERIAL PRIMARY KEY
name VARCHAR
color VARCHAR
order INT
pipelineId INT → Pipelines.id
notifyQueueId INT → Queues.id -- notificacao ao avancar
notifyUserId INT → Users.id
tenantId INT
}
Opportunities {
id SERIAL PRIMARY KEY
name VARCHAR
value DECIMAL
status ENUM('open','win','lose','pending')
closingForecast DATE
contactId INT → Contacts.id
stageId INT → Stages.id
pipelineId INT → Pipelines.id
userId INT → Users.id (responsavel)
tenantId INT
createdAt TIMESTAMP
updatedAt TIMESTAMP
}Kanbans / KanbanContacts
Kanbans {
id SERIAL PRIMARY KEY
name VARCHAR -- nome da lane/coluna
color VARCHAR
tag VARCHAR
tenantId INT
}
-- Contacts.kanban = KanbanId (FK direto no contato)Tags
Tags {
id SERIAL PRIMARY KEY
name VARCHAR
color VARCHAR
tenantId INT
isActive BOOLEAN
triggerKeyword VARCHAR -- para ativar chatbot automaticamente
}ChatFlows (FlowBuilder)
ChatFlows {
id SERIAL PRIMARY KEY
name VARCHAR
status BOOLEAN
isActive BOOLEAN
celularTeste VARCHAR
flow JSONB -- { name, nodeList, lineList }
tenantId INT
}Queries críticas frequentes
Tickets abertos por canal
SELECT w.name, COUNT(t.id) as total
FROM "Tickets" t
JOIN "Whatsapps" w ON t."whatsappId" = w.id
WHERE t.status = 'open' AND t."tenantId" = :tenantId
GROUP BY w.name ORDER BY total DESC;Contatos sem ticket nos ultimos 7 dias
SELECT c.name, c.number, t."lastMessageAt"
FROM "Contacts" c
JOIN "Tickets" t ON t."contactId" = c.id
WHERE t."tenantId" = :tenantId
AND t."lastMessageAt" < NOW() - INTERVAL '7 days'
AND t.status = 'open'
ORDER BY t."lastMessageAt" ASC;Sessions Baileys corrompidas
-- Ver quantidade de sessoes por canal
SELECT "sessionId", COUNT(*) as registros
FROM "BaileysSessions"
WHERE "tenantId" = :tenantId
GROUP BY "sessionId"
ORDER BY registros DESC;Problema: terminating connection due to administrator command
Visto nos logs: Error: terminating connection due to administrator command
Causa: PgBouncer (pool de conexoes) matando conexoes ociosas apos timeout. Com 6 workers × multiplas conexoes por worker = muitas conexoes ativas.
Impacto: Worker crasha → Baileys daquela instancia desconecta → sessoes perdem sincronismo.
Mitigacao: Reduzir workers de 6 para 4 (ou configurar keepAlive no Sequelize).