E01 - Plataforma de Inteligência Artificial
Modelo de Dados
Versão: 2.0 | Data: 05/01/2026 | API Base: ConectEnvios API V1
1. Diagrama ER
┌─────────────────────────────────────────────────────────────────────────────────────────┐
│ MODELO DE DADOS - E01 │
├─────────────────────────────────────────────────────────────────────────────────────────┤
│ │
│ ┌─────────────────┐ ┌─────────────────┐ │
│ │ customers │───────────────────▶│ conversations │ │
│ ├─────────────────┤ 1:N ├─────────────────┤ │
│ │ id │ │ id │ │
│ │ phone │ │ customer_id │───────┐ │
│ │ name │ │ channel │ │ │
│ │ email │ │ status │ │ │
│ │ document │ │ context │ │ │
│ └─────────────────┘ └────────┬────────┘ │ │
│ │ │ │
│ │ 1:N │ │
│ ▼ │ │
│ ┌─────────────────┐ │ │
│ │ messages │ │ │
│ ├─────────────────┤ │ │
│ │ id │ │ │
│ │ conversation_id │ │ │
│ │ direction │ │ │
│ │ type │ │ │
│ │ content │ │ │
│ │ processing │ │ │
│ └─────────────────┘ │ │
│ │ │
│ ┌─────────────────┐ ┌─────────────────┐ │ │
│ │ quotes │───────────────────▶│ labels │ │ │
│ ├─────────────────┤ 1:1 ├─────────────────┤ │ │
│ │ id │ │ id │ │ │
│ │ customer_id │ │ quote_id │ │ │
│ │ origin │ │ tracking_code │ │ │
│ │ destination │ │ carrier │ │ │
│ │ package │ │ label_url │ │ │
│ │ options (API) │ │ cost │ │ │
│ └─────────────────┘ └─────────────────┘ │ │
│ │ │
└─────────────────────────────────────────────────────────────────────────────────────────┘
2. Tabelas Principais
2.1 customers
Armazena informações dos clientes que interagem com o chatbot.
CREATE TABLE customers (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
phone VARCHAR(20) NOT NULL UNIQUE,
name VARCHAR(255),
email VARCHAR(255),
document VARCHAR(14), -- CPF ou CNPJ
preferences JSONB DEFAULT '{}',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_customers_phone ON customers(phone);
2.2 conversations
Armazena as sessões de conversa.
CREATE TABLE conversations (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
customer_id UUID REFERENCES customers(id),
channel VARCHAR(20) DEFAULT 'whatsapp',
status VARCHAR(20) DEFAULT 'active',
context JSONB DEFAULT '{}',
message_count INTEGER DEFAULT 0,
started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
last_message_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_conversations_customer ON conversations(customer_id);
CREATE INDEX idx_conversations_status ON conversations(status);
2.3 messages
Armazena todas as mensagens trocadas.
CREATE TABLE messages (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
conversation_id UUID REFERENCES conversations(id),
direction VARCHAR(10) NOT NULL, -- inbound, outbound
sender_type VARCHAR(20) NOT NULL, -- customer, bot, agent
type VARCHAR(20) NOT NULL, -- text, image, document, etc
content JSONB NOT NULL,
processing JSONB, -- NLP processing results
whatsapp_id VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_messages_conversation ON messages(conversation_id);
CREATE INDEX idx_messages_created ON messages(created_at DESC);
2.4 quotes
Armazena cotações geradas via API ConectEnvios.
CREATE TABLE quotes (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
quote_code VARCHAR(20) NOT NULL UNIQUE,
customer_id UUID REFERENCES customers(id),
-- Estrutura alinhada com API ConectEnvios
origin JSONB NOT NULL,
/* {
"cep": "70000000",
"city_id": 5564,
"city_title": "Brasília",
"state_abbreviation": "DF"
} */
destination JSONB NOT NULL,
package JSONB NOT NULL,
/* {
"type": "box",
"weight": 2000,
"height": 10,
"width": 20,
"length": 30
} */
-- Response do POST /package/shipping
options JSONB NOT NULL,
/* [{
"postal_company_id": 1,
"company_name": "Correios",
"name": "SEDEX",
"price_discounted": 70.04,
"deadline": 1
}] */
status VARCHAR(20) DEFAULT 'active',
expires_at TIMESTAMP NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Mapeamento de Transportadoras:
-- 1 = Correios | 2 = Jadlog | 3 = AzulCargo | 4 = Loggi | 5 = JTExpress
3. Cache Redis
3.1 Estruturas de Dados
# Rastreamento (GET /package/track/:id)
tracking:{package_id}:
type: hash
fields:
status: string # correios_type (PO, RO, DO, OEC, BDE)
description: string
location: string # city/state
events: json_array # Array de TrackingEvent
updated_at: timestamp
ttl:
in_transit: 300 # 5 minutos
out_for_delivery: 60 # 1 minuto
delivered: 86400 # 24 horas
# CEP (GET /cep/address)
cep:{zipcode}:
type: hash
fields:
address: string
city_id: integer
city_title: string
state_abbreviation: string
neighborhood: string
ttl: 86400 # 24 horas
# Contexto de conversa
context:{phone}:
type: hash
fields:
conversation_id: uuid
variables: json
last_intent: string
pending_action: string
ttl: 1800 # 30 minutos
4. Histórico de Revisões
| Versão | Data | Descrição |
|---|---|---|
| 1.0 | 05/01/2026 | Versão inicial |
| 2.0 | 05/01/2026 | Estruturas alinhadas com API ConectEnvios V1 |