# Innovia Messaging — Guía Técnica, Esquemas SQL & Scripts

> **Documento de Referencia Técnica para Implementación**  
> Este documento consolida todo el código fuente, contratos de interfaz, DDL de base de datos, comandos cURL y suites de pruebas automatizadas para el equipo de desarrollo.

---

## Índice
1. [Esquema de Base de Datos DDL (PostgreSQL 15+)](#1-esquema-de-base-de-datos-ddl-postgresql-15)
2. [Contratos de Interfaz TypeScript (Provider Layer)](#2-contratos-de-interfaz-typescript-provider-layer)
3. [Comandos y Scripts del Spike Técnico (Fase 0)](#3-comandos-y-scripts-del-spike-técnico-fase-0)
4. [Especificación de Payloads de la API REST V1](#4-especificación-de-payloads-de-la-api-rest-v1)
5. [Suite de Pruebas Automatizadas (Jest / Vitest)](#5-suite-de-pruebas-automatizadas-jest--vitest)
6. [Alcance Cerrado V1, Puertas de Avance & Criterios de Evolución](#6-alcance-cerrado-v1-puertas-de-avance--criterios-de-evolución)

---

## 1. Esquema de Base de Datos DDL (PostgreSQL 15+)

Ejecutar en la base de datos principal de Innovia para aprovisionar las tablas multi-tenant:

```sql
-- Habilitar extensión para generación de identificadores UUIDv4
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- 1. Tabla de Tenants (Empresas / Clientes)
CREATE TABLE tenants (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(120) NOT NULL,
    slug VARCHAR(80) UNIQUE NOT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'ACTIVE',
    provider_customer_id VARCHAR(100), -- Mapeo Kapso (kps_cus_...)
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 2. Tabla de Usuarios y Permisos
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    email VARCHAR(150) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role VARCHAR(30) NOT NULL DEFAULT 'OPERATOR', -- ADMIN, OPERATOR, VIEWER
    full_name VARCHAR(120) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE UNIQUE INDEX idx_users_tenant_email ON users(tenant_id, email);

-- 3. Tabla de API Keys (Sistemas Externos)
CREATE TABLE api_keys (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    name VARCHAR(80) NOT NULL,
    key_prefix VARCHAR(15) NOT NULL, -- Ej: inn_live_981a
    key_hash VARCHAR(255) NOT NULL,
    last_used_at TIMESTAMP WITH TIME ZONE,
    expires_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE INDEX idx_api_keys_tenant ON api_keys(tenant_id);

-- 4. Tabla de Líneas de WhatsApp
CREATE TABLE phone_numbers (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    e164_number VARCHAR(20) NOT NULL,
    display_name VARCHAR(100),
    waba_id VARCHAR(100),
    meta_phone_number_id VARCHAR(100),
    provider VARCHAR(30) NOT NULL DEFAULT 'kapso', -- kapso, gupshup, meta
    provider_phone_id VARCHAR(100), -- ID interno en Kapso
    status VARCHAR(30) NOT NULL DEFAULT 'PENDING',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE INDEX idx_phone_numbers_tenant ON phone_numbers(tenant_id);
CREATE INDEX idx_phone_numbers_provider_id ON phone_numbers(provider_phone_id);

-- 5. Tabla de Plantillas Meta
CREATE TABLE templates (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    name VARCHAR(120) NOT NULL,
    language VARCHAR(10) NOT NULL DEFAULT 'es',
    category VARCHAR(50) NOT NULL, -- MARKETING, UTILITY, AUTHENTICATION
    status VARCHAR(30) NOT NULL, -- APPROVED, REJECTED, PENDING
    components JSONB NOT NULL DEFAULT '[]'::jsonb,
    provider_template_id VARCHAR(100),
    synced_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE INDEX idx_templates_tenant ON templates(tenant_id);

-- 6. Tabla de Campañas Outbound (Broadcasts)
CREATE TABLE campaigns (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    phone_number_id UUID NOT NULL REFERENCES phone_numbers(id),
    template_id UUID NOT NULL REFERENCES templates(id),
    name VARCHAR(150) NOT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'DRAFT', -- DRAFT, SCHEDULED, IN_FLIGHT, COMPLETED, FAILED
    scheduled_at TIMESTAMP WITH TIME ZONE,
    executed_at TIMESTAMP WITH TIME ZONE,
    provider_broadcast_id VARCHAR(100),
    total_recipients INT DEFAULT 0,
    sent_count INT DEFAULT 0,
    delivered_count INT DEFAULT 0,
    read_count INT DEFAULT 0,
    failed_count INT DEFAULT 0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE INDEX idx_campaigns_tenant ON campaigns(tenant_id);

-- 7. Tabla de Mensajes Individuales
CREATE TABLE messages (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    campaign_id UUID REFERENCES campaigns(id) ON DELETE SET NULL,
    phone_number_id UUID NOT NULL REFERENCES phone_numbers(id),
    recipient_phone VARCHAR(25) NOT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'PENDING', -- PENDING, SENT, DELIVERED, READ, FAILED
    provider VARCHAR(30) NOT NULL DEFAULT 'kapso',
    provider_message_id VARCHAR(100),
    variables JSONB DEFAULT '{}'::jsonb,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    delivered_at TIMESTAMP WITH TIME ZONE,
    read_at TIMESTAMP WITH TIME ZONE
);
CREATE INDEX idx_messages_tenant ON messages(tenant_id);
CREATE INDEX idx_messages_provider_id ON messages(provider_message_id);
CREATE INDEX idx_messages_campaign ON messages(campaign_id);

-- 8. Tabla de Auditoría de Eventos de Webhook
CREATE TABLE message_events (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    message_id UUID NOT NULL REFERENCES messages(id) ON DELETE CASCADE,
    event_type VARCHAR(40) NOT NULL, -- MESSAGE_SENT, MESSAGE_DELIVERED, etc.
    error_code VARCHAR(50),
    error_message TEXT,
    raw_payload JSONB NOT NULL,
    occurred_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE INDEX idx_message_events_message ON message_events(message_id);
```

---

## 2. Contratos de Interfaz TypeScript (Provider Layer)

Archivo: `src/core/providers/messaging-provider.interface.ts`

```typescript
export interface CustomerSetupResult {
  providerCustomerId: string;
  setupLinkUrl?: string;
}

export interface SendMessagePayload {
  to: string; // Formato E.164 (+569...)
  templateName: string;
  languageCode: string;
  components: Array<{
    type: 'header' | 'body' | 'button';
    parameters: Array<{
      type: 'text' | 'image' | 'document';
      text?: string;
      url?: string;
    }>;
  }>;
}

export interface ProviderMessageResult {
  providerMessageId: string;
  status: 'sent' | 'queued' | 'failed';
  errorDetails?: string;
}

export interface CreateBroadcastPayload {
  name: string;
  providerPhoneId: string;
  templateName: string;
  recipients: Array<{
    to: string;
    variables: Record<string, string>;
  }>;
  scheduledAt?: Date;
}

export interface NormalizedWebhookEvent {
  providerMessageId: string;
  eventType: 'SENT' | 'DELIVERED' | 'READ' | 'FAILED' | 'RECEIVED';
  timestamp: Date;
  errorCode?: string;
  errorMessage?: string;
  rawPayload: Record<string, any>;
}

export interface MessagingProvider {
  readonly providerName: string;

  // Onboarding
  createCustomer(tenantName: string, externalId: string): Promise<CustomerSetupResult>;
  createSetupLink(providerCustomerId: string): Promise<string>;
  syncPhoneNumbers(providerCustomerId: string): Promise<any[]>;

  // Plantillas
  syncTemplates(providerWabaId: string): Promise<any[]>;

  // Envíos individuales
  sendTemplateMessage(
    providerPhoneId: string,
    payload: SendMessagePayload
  ): Promise<ProviderMessageResult>;

  // Campañas masivas
  createAndExecuteBroadcast(
    payload: CreateBroadcastPayload
  ): Promise<{ providerBroadcastId: string }>;

  // Normalización
  normalizeWebhook(body: any, headers: Record<string, string>): Promise<NormalizedWebhookEvent[]>;
}
```

Implementación de referencia para Kapso: `src/core/providers/kapso/kapso.adapter.ts`

```typescript
import axios, { AxiosInstance } from 'axios';
import { MessagingProvider, CustomerSetupResult, SendMessagePayload, ProviderMessageResult, CreateBroadcastPayload, NormalizedWebhookEvent } from '../messaging-provider.interface';

export class KapsoAdapter implements MessagingProvider {
  readonly providerName = 'kapso';
  private client: AxiosInstance;

  constructor(apiKey: string, baseURL = 'https://api.kapso.ai/v1') {
    this.client = axios.create({
      baseURL,
      headers: {
        Authorization: `Bearer ${apiKey}`,
        'Content-Type': 'application/json'
      }
    });
  }

  async createCustomer(tenantName: string, externalId: string): Promise<CustomerSetupResult> {
    const res = await this.client.post('/customers', {
      name: tenantName,
      external_id: externalId
    });
    return { providerCustomerId: res.data.id };
  }

  async createSetupLink(providerCustomerId: string): Promise<string> {
    const res = await this.client.post(`/customers/${providerCustomerId}/setup_links`);
    return res.data.url;
  }

  async syncPhoneNumbers(providerCustomerId: string): Promise<any[]> {
    const res = await this.client.get(`/customers/${providerCustomerId}/phone_numbers`);
    return res.data.data;
  }

  async syncTemplates(providerWabaId: string): Promise<any[]> {
    const res = await this.client.get(`/wabas/${providerWabaId}/templates`);
    return res.data.data;
  }

  async sendTemplateMessage(providerPhoneId: string, payload: SendMessagePayload): Promise<ProviderMessageResult> {
    const res = await this.client.post('/messages', {
      phone_number_id: providerPhoneId,
      to: payload.to,
      type: 'template',
      template: {
        name: payload.templateName,
        language: { code: payload.languageCode },
        components: payload.components
      }
    });
    return { providerMessageId: res.data.id, status: 'sent' };
  }

  async createAndExecuteBroadcast(payload: CreateBroadcastPayload): Promise<{ providerBroadcastId: string }> {
    const res = await this.client.post('/broadcasts', {
      name: payload.name,
      phone_number_id: payload.providerPhoneId,
      template_name: payload.templateName,
      recipients: payload.recipients,
      scheduled_at: payload.scheduledAt?.toISOString()
    });
    return { providerBroadcastId: res.data.id };
  }

  async normalizeWebhook(body: any): Promise<NormalizedWebhookEvent[]> {
    const event = body.event;
    const data = body.data;
    const mapType: Record<string, 'SENT' | 'DELIVERED' | 'READ' | 'FAILED' | 'RECEIVED'> = {
      'message.sent': 'SENT',
      'message.delivered': 'DELIVERED',
      'message.read': 'READ',
      'message.failed': 'FAILED',
      'message.received': 'RECEIVED'
    };

    return [{
      providerMessageId: data.id,
      eventType: mapType[event] || 'SENT',
      timestamp: new Date(data.timestamp || Date.now()),
      errorCode: data.error?.code,
      errorMessage: data.error?.message,
      rawPayload: body
    }];
  }
}
```

---

## 3. Comandos y Scripts del Spike Técnico (Fase 0)

### 3.1 Crear Customer de Prueba
```bash
curl -X POST "https://api.kapso.ai/v1/customers" \
  -H "Authorization: Bearer YOUR_KAPSO_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "Cliente Piloto Innovia",
    "external_id": "tenant_test_001"
  }'
```

### 3.2 Generar Setup Link (Embedded Signup)
```bash
curl -X POST "https://api.kapso.ai/v1/customers/KAPSO_CUSTOMER_ID/setup_links" \
  -H "Authorization: Bearer YOUR_KAPSO_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "redirect_url": "https://dashboard.innovia.io/numbers/callback"
  }'
```

### 3.3 Listar Templates Aprobados
```bash
curl -X GET "https://api.kapso.ai/v1/phone_numbers/KAPSO_PHONE_ID/templates" \
  -H "Authorization: Bearer YOUR_KAPSO_API_KEY"
```

### 3.4 Enviar Mensaje Individual
```bash
curl -X POST "https://api.kapso.ai/v1/messages" \
  -H "Authorization: Bearer YOUR_KAPSO_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "phone_number_id": "KAPSO_PHONE_ID",
    "to": "+56912345678",
    "type": "template",
    "template": {
      "name": "hello_world",
      "language": { "code": "en_US" }
    }
  }'
```

### 3.5 Disparar Mini-Broadcast (2 Destinatarios)
```bash
curl -X POST "https://api.kapso.ai/v1/broadcasts" \
  -H "Authorization: Bearer YOUR_KAPSO_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "Prueba Mini Broadcast",
    "phone_number_id": "KAPSO_PHONE_ID",
    "template_name": "hello_world",
    "recipients": [
      { "to": "+56911112222" },
      { "to": "+56933334444" }
    ]
  }'
```

---

## 4. Especificación de Payloads de la API REST V1

### 4.1 `POST /v1/messages` (Disparo Transaccional)
* **Headers**:
  * `Authorization: Bearer inn_live_981a...`
  * `Content-Type: application/json`

* **Request**:
```json
{
  "to": "+56912345678",
  "phone_id": "phn_01h8a9bc-d901-44ab",
  "template": {
    "name": "aviso_cobranza_v2",
    "language": "es",
    "parameters": {
      "cliente": "Carlos Pérez",
      "concepto": "Renovación Fibra Óptica",
      "monto": "$29.990"
    }
  }
}
```

* **Response (202 Accepted)**:
```json
{
  "id": "msg_01h8b001-22cc-44aa",
  "status": "queued",
  "to": "+56912345678",
  "created_at": "2026-09-16T17:00:00Z"
}
```

### 4.2 `GET /v1/messages/:id` (Consulta de Estado)
* **Response (200 OK)**:
```json
{
  "id": "msg_01h8b001-22cc-44aa",
  "status": "delivered",
  "to": "+56912345678",
  "created_at": "2026-09-16T17:00:00Z",
  "sent_at": "2026-09-16T17:00:02Z",
  "delivered_at": "2026-09-16T17:00:05Z",
  "read_at": null,
  "events": [
    { "type": "SENT", "timestamp": "2026-09-16T17:00:02Z" },
    { "type": "DELIVERED", "timestamp": "2026-09-16T17:00:05Z" }
  ]
}
```

---

## 5. Suite de Pruebas Automatizadas (Jest / Vitest)

### 5.1 Test Unitario: Normalizador de Eventos
```typescript
import { KapsoAdapter } from './kapso.adapter';

describe('KapsoAdapter.normalizeWebhook', () => {
  const adapter = new KapsoAdapter('mock_key');

  it('debe mapear message.delivered a MESSAGE_DELIVERED', async () => {
    const rawWebhook = {
      event: 'message.delivered',
      data: {
        id: 'kps_msg_100',
        timestamp: '2026-09-16T17:10:00Z'
      }
    };

    const events = await adapter.normalizeWebhook(rawWebhook);
    expect(events[0].eventType).toBe('DELIVERED');
    expect(events[0].providerMessageId).toBe('kps_msg_100');
  });
});
```

### 5.2 Test de Integración: Aislamiento Multi-Tenant
```typescript
import request from 'supertest';
import { app } from '../src/app';

describe('Multi-Tenancy Security', () => {
  it('impide que el Tenant B acceda a las campañas del Tenant A', async () => {
    const res = await request(app)
      .get('/api/campaigns/campaign_id_belonging_to_tenant_a')
      .set('Authorization', 'Bearer token_tenant_b');

    expect(res.status).toBe(404);
  });
});
```

---

## 6. Alcance Cerrado V1, Puertas de Avance & Criterios de Evolución

*(Extraído del Brief Ejecutivo de Producto y Desarrollo)*

### 6.1 Matriz de Alcance Cerrado V1

| Frente | Incluido en V1 | Fuera de Alcance V1 (No Construir) |
| :--- | :--- | :--- |
| **Cuenta** | Acceso, workspace por empresa, aislamiento multiempresa y roles mínimos | Permisos avanzados o estructuras corporativas multinivel |
| **WhatsApp** | Onboarding WABA, conexión y estado del número | Multicanalidad, RCS, SMS, email o voz |
| **Plantillas** | Sincronización, listado, estado y selección | Editor visual propio (salvo que el spike lo exija) |
| **Audiencias** | CSV, validación E.164 y mapeo de variables | CRM, segmentación avanzada o enriquecimiento de datos |
| **Campañas** | Creación, plantilla, audiencia, envío inmediato/programado | Journeys, constructor de workflows o automatizaciones complejas |
| **Resultados** | Procesados, enviados, entregados, leídos y fallidos | BI avanzado o analítica predictiva |
| **Integraciones** | API transaccional propia (`/v1/messages`) y webhooks | Marketplace de apps o conectores genéricos |

### 6.2 Las 4 Puertas de Avance (Stage Gates)

1. **Puerta 1: Spike ➔ Desarrollo V1**
   * *Evidencia:* Happy path completado, webhooks de entrega capturados, mappings propios probados.
   * *Decisión habilitada:* Aprobar estimación de backlog y comenzar desarrollo de V1.
2. **Puerta 2: V1 ➔ Piloto Comercial**
   * *Evidencia:* Flujo integral probado en entorno multiempresa, seguridad y conciliación básica.
   * *Decisión habilitada:* Operar con el 1er cliente real en producción.
3. **Puerta 3: Piloto ➔ Producto Repetible**
   * *Evidencia:* Clientes pagan, repiten uso sin soporte intensivo y la operación conserva margen positivo.
   * *Decisión habilitada:* Invertir en autoservicio y robustez de infraestructura.
4. **Puerta 4: Kapso ➔ Arquitectura con Mayor Control**
   * *Evidencia:* Costos de Kapso limitan margen, o Meta App de Kapso bloquea acuerdos clave.
   * *Decisión habilitada:* Tramitar Meta App propia, MPS o integrar segundo BSP (Gupshup).

### 6.3 Mandato de Producto y Desarrollo

* **Líder de Producto:** Defender el alcance cerrado (evitar convertirse en un "Chattigo pequeño"), definir criterios de aceptación y preparar hipótesis de pricing comercial.
* **Desarrollo:** Ejecutar primero el Spike de 48h, proponer el stack más simple (NestJS/Fastify + PostgreSQL), mantener los contratos propios en frontend/API y no construir abstracciones multi-BSP innecesarias en la V1.