WhatsAppCentralizado/docs/database/README.md

Esquema de Base de Datos

Diagrama ER

┌─────────────────┐       ┌─────────────────┐       ┌─────────────────┐
│     users       │       │     queues      │       │ whatsapp_accounts│
├─────────────────┤       ├─────────────────┤       ├─────────────────┤
│ id (PK)         │       │ id (PK)         │       │ id (PK)         │
│ email           │       │ name            │       │ phone_number    │
│ password_hash   │       │ description     │       │ name            │
│ name            │       │ assignment_method       │ status          │
│ role            │       │ max_per_agent   │       │ session_data    │
│ status          │       │ sla_first_resp  │       │ qr_code         │
│ is_active       │       │ sla_resolution  │       │ created_at      │
│ created_at      │       │ business_hours  │       └────────┬────────┘
│ updated_at      │       │ fallback_flow_id│                │
└────────┬────────┘       │ is_active       │                │
         │                └────────┬────────┘                │
         │                         │                         │
         │    ┌────────────────────┼─────────────────────────┤
         │    │                    │                         │
         │    ▼                    ▼                         ▼
         │  ┌─────────────────┐  ┌─────────────────┐  ┌─────────────────┐
         │  │  queue_agents   │  │  conversations  │  │    contacts     │
         │  ├─────────────────┤  ├─────────────────┤  ├─────────────────┤
         │  │ id (PK)         │  │ id (PK)         │  │ id (PK)         │
         │  │ queue_id (FK)───┼──│ queue_id (FK)───│  │ phone_number    │
         ├──│ user_id (FK)    │  │ whatsapp_acc_id─┼──│ name            │
         │  │ is_supervisor   │  │ contact_id (FK)─┼──│ email           │
         │  │ skills          │  │ assigned_to (FK)│  │ company         │
         │  └─────────────────┘  │ status          │  │ metadata        │
         │                       │ priority        │  │ tags            │
         │                       │ current_flow_id │  │ odoo_partner_id │
         │                       │ flow_context    │  │ created_at      │
         │                       │ sla_*           │  └─────────────────┘
         │                       │ csat_*          │
         │                       │ created_at      │
         │                       └────────┬────────┘
         │                                │
         │                                │
         │                                ▼
         │                       ┌─────────────────┐
         │                       │    messages     │
         │                       ├─────────────────┤
         │                       │ id (PK)         │
         │                       │ conversation_id │
         │                       │ direction       │
         │                       │ type            │
         │                       │ content         │
         └───────────────────────│ sent_by (FK)    │
                                 │ is_internal_note│
                                 │ status          │
                                 │ created_at      │
                                 └─────────────────┘

┌─────────────────┐       ┌─────────────────┐       ┌─────────────────┐
│     flows       │       │ message_templates│      │  quick_replies  │
├─────────────────┤       ├─────────────────┤       ├─────────────────┤
│ id (PK)         │       │ id (PK)         │       │ id (PK)         │
│ name            │       │ name            │       │ shortcut        │
│ description     │       │ content         │       │ content         │
│ trigger_type    │       │ attachments     │       │ attachments     │
│ trigger_value   │       │ variables       │       │ queue_id (FK)   │
│ nodes (JSONB)   │       │ created_at      │       │ created_by (FK) │
│ variables       │       └─────────────────┘       │ created_at      │
│ is_active       │                                 └─────────────────┘
│ version         │
│ created_at      │       ┌─────────────────┐       ┌─────────────────┐
└─────────────────┘       │   odoo_config   │       │odoo_automations │
                          ├─────────────────┤       ├─────────────────┤
┌─────────────────┐       │ id (PK)         │       │ id (PK)         │
│      tags       │       │ url             │       │ name            │
├─────────────────┤       │ database        │       │ odoo_model      │
│ id (PK)         │       │ username        │       │ odoo_trigger    │
│ name            │       │ api_key_enc     │       │ odoo_condition  │
│ color           │       │ is_active       │       │ template_id (FK)│
│ created_at      │       │ last_sync_at    │       │ is_active       │
└─────────────────┘       └─────────────────┘       │ created_at      │
                                                    └─────────────────┘

---

Tablas Detalladas

users

Usuarios del sistema (admins, supervisores, agentes).

Campo	Tipo	Descripción
id	UUID	Identificador único
email	VARCHAR(255)	Email único
password_hash	VARCHAR(255)	Hash bcrypt
name	VARCHAR(100)	Nombre completo
role	ENUM	admin, supervisor, agent
status	ENUM	online, offline, away, busy, lunch, on_call
is_active	BOOLEAN	Usuario activo
created_at	TIMESTAMP	Fecha creación
updated_at	TIMESTAMP	Última modificación

Índices:

• idx_users_email (email)
• idx_users_role (role)
• idx_users_status (status)

---

whatsapp_accounts

Números de WhatsApp conectados.

Campo	Tipo	Descripción
id	UUID	Identificador único
phone_number	VARCHAR(20)	Número con código país
name	VARCHAR(100)	Alias (ej: "Ventas")
status	ENUM	connected, disconnected, banned
session_data	JSONB	Datos de sesión Baileys
qr_code	TEXT	QR para reconexión
created_at	TIMESTAMP	Fecha creación

Índices:

• idx_wa_phone (phone_number)
• idx_wa_status (status)

---

contacts

Contactos (clientes que escriben).

Campo	Tipo	Descripción
id	UUID	Identificador único
phone_number	VARCHAR(20)	Teléfono único
name	VARCHAR(100)	Nombre
email	VARCHAR(255)	Email
company	VARCHAR(100)	Empresa
metadata	JSONB	Datos personalizados
tags	VARCHAR[]	Array de etiquetas
odoo_partner_id	INTEGER	ID en Odoo
created_at	TIMESTAMP	Primer contacto

Índices:

• idx_contacts_phone UNIQUE (phone_number)
• idx_contacts_email (email)
• idx_contacts_odoo (odoo_partner_id)
• idx_contacts_tags GIN (tags)

---

queues

Colas de atención.

Campo	Tipo	Descripción
id	UUID	Identificador único
name	VARCHAR(100)	Nombre de cola
description	TEXT	Descripción
assignment_method	ENUM	round_robin, least_busy, skill_based, sticky
max_per_agent	INTEGER	Máx. conversaciones por agente
sla_first_response	INTEGER	Segundos para primera respuesta
sla_resolution	INTEGER	Segundos para resolución
business_hours	JSONB	Horario de atención
fallback_flow_id	UUID	Flujo fuera de horario
is_active	BOOLEAN	Cola activa

---

queue_agents

Relación agentes-colas.

Campo	Tipo	Descripción
id	UUID	Identificador único
queue_id	UUID	FK a queues
user_id	UUID	FK a users
is_supervisor	BOOLEAN	Es supervisor de cola
skills	VARCHAR[]	Habilidades del agente

Índices:

• idx_qa_queue (queue_id)
• idx_qa_user (user_id)
• idx_qa_unique UNIQUE (queue_id, user_id)

---

conversations

Conversaciones.

Campo	Tipo	Descripción
id	UUID	Identificador único
whatsapp_account_id	UUID	FK a whatsapp_accounts
contact_id	UUID	FK a contacts
queue_id	UUID	FK a queues (nullable)
assigned_to	UUID	FK a users (nullable)
status	ENUM	bot, waiting, active, resolved
priority	ENUM	low, normal, high, urgent
current_flow_id	UUID	Flujo en ejecución
flow_context	JSONB	Estado del flujo
sla_first_response_at	TIMESTAMP	Hora primera respuesta
sla_first_response_met	BOOLEAN	SLA cumplido
resolved_at	TIMESTAMP	Hora resolución
csat_score	INTEGER	Calificación 1-5
csat_feedback	TEXT	Feedback del cliente
last_message_at	TIMESTAMP	Último mensaje
created_at	TIMESTAMP	Inicio conversación

Índices:

• idx_conv_contact (contact_id)
• idx_conv_status (status)
• idx_conv_assigned (assigned_to)
• idx_conv_queue (queue_id)
• idx_conv_last_msg (last_message_at)
• idx_conv_created (created_at)

---

messages

Mensajes de conversaciones.

Campo	Tipo	Descripción
id	UUID	Identificador único
conversation_id	UUID	FK a conversations
direction	ENUM	inbound, outbound
type	ENUM	text, image, audio, video, document, buttons, list, location, contact, sticker
content	TEXT	Contenido del mensaje
media_url	VARCHAR(500)	URL de media
metadata	JSONB	Datos adicionales (botones, etc.)
sent_by	UUID	FK a users (si fue agente)
is_internal_note	BOOLEAN	Nota interna
status	ENUM	pending, sent, delivered, read, failed
created_at	TIMESTAMP	Fecha envío/recepción

Índices:

• idx_msg_conv (conversation_id)
• idx_msg_created (created_at)
• idx_msg_direction (direction)
• idx_msg_status (status)

Particionamiento: Por fecha (mensual) para alto volumen.

---

flows

Flujos de chatbot.

Campo	Tipo	Descripción
id	UUID	Identificador único
name	VARCHAR(100)	Nombre del flujo
description	TEXT	Descripción
trigger_type	ENUM	welcome, keyword, fallback, event, schedule, manual
trigger_value	VARCHAR(255)	Valor del trigger
nodes	JSONB	Definición del flujo
variables	JSONB	Variables del flujo
is_active	BOOLEAN	Flujo activo
version	INTEGER	Versión del flujo
created_at	TIMESTAMP	Fecha creación

Índices:

• idx_flows_trigger (trigger_type, is_active)
• idx_flows_active (is_active)

---

message_templates

Templates de mensajes reutilizables.

Campo	Tipo	Descripción
id	UUID	Identificador único
name	VARCHAR(100)	Nombre del template
content	TEXT	Contenido con variables
attachments	JSONB	Archivos adjuntos
variables	VARCHAR[]	Variables usadas
created_at	TIMESTAMP	Fecha creación

---

quick_replies

Respuestas rápidas para agentes.

Campo	Tipo	Descripción
id	UUID	Identificador único
shortcut	VARCHAR(50)	Atajo (/saludo)
content	TEXT	Contenido
attachments	JSONB	Archivos adjuntos
queue_id	UUID	FK a queues (opcional)
created_by	UUID	FK a users
created_at	TIMESTAMP	Fecha creación

Índices:

• idx_qr_shortcut (shortcut)
• idx_qr_queue (queue_id)

---

tags

Etiquetas para contactos.

Campo	Tipo	Descripción
id	UUID	Identificador único
name	VARCHAR(50)	Nombre único
color	VARCHAR(7)	Color hex (#FF5733)
created_at	TIMESTAMP	Fecha creación

---

odoo_config

Configuración de conexión Odoo.

Campo	Tipo	Descripción
id	UUID	Identificador único
url	VARCHAR(255)	URL de Odoo
database	VARCHAR(100)	Nombre de BD
username	VARCHAR(100)	Usuario API
api_key_encrypted	TEXT	API key encriptada
is_active	BOOLEAN	Conexión activa
last_sync_at	TIMESTAMP	Última sincronización
created_at	TIMESTAMP	Fecha creación

---

odoo_automations

Automatizaciones Odoo → WhatsApp.

Campo	Tipo	Descripción
id	UUID	Identificador único
name	VARCHAR(100)	Nombre
odoo_model	VARCHAR(100)	Modelo Odoo (sale.order)
odoo_trigger	VARCHAR(100)	Evento trigger
odoo_condition	JSONB	Condiciones
message_template_id	UUID	FK a templates
is_active	BOOLEAN	Automatización activa
created_at	TIMESTAMP	Fecha creación

---

Migraciones

Crear migración

docker compose exec api-gateway alembic revision --autogenerate -m "descripcion"

Aplicar migraciones

docker compose exec api-gateway alembic upgrade head

Revertir migración

docker compose exec api-gateway alembic downgrade -1

Ver historial

docker compose exec api-gateway alembic history

---

Índices Recomendados

-- Búsqueda full-text en mensajes
CREATE INDEX idx_messages_content_fts ON messages
USING gin(to_tsvector('spanish', content));

-- Búsqueda en metadata JSONB
CREATE INDEX idx_contacts_metadata ON contacts
USING gin(metadata);

-- Queries frecuentes
CREATE INDEX idx_conv_active ON conversations (status, assigned_to)
WHERE status IN ('active', 'waiting');

CREATE INDEX idx_msg_recent ON messages (conversation_id, created_at DESC);

---

Mantenimiento

Vacuum

-- Vacuum manual
VACUUM ANALYZE conversations;
VACUUM ANALYZE messages;

-- Configurar autovacuum agresivo para messages
ALTER TABLE messages SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_analyze_scale_factor = 0.02
);

Archivado de mensajes antiguos

-- Mover mensajes > 1 año a tabla de archivo
INSERT INTO messages_archive
SELECT * FROM messages
WHERE created_at < NOW() - INTERVAL '1 year';

DELETE FROM messages
WHERE created_at < NOW() - INTERVAL '1 year';

Estadísticas

-- Tamaño de tablas
SELECT
  relname as table,
  pg_size_pretty(pg_total_relation_size(relid)) as size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

-- Mensajes por día
SELECT
  DATE(created_at) as date,
  COUNT(*) as count
FROM messages
GROUP BY DATE(created_at)
ORDER BY date DESC
LIMIT 30;