erp-core/docs/04-modelado/database-design/DDL-SPEC-integrations.md

# DDL Specification: integrations Schema

## Identificacion

| Campo | Valor |
|-------|-------|
| **Schema** | integrations |
| **Modulo** | MGN-016 Integraciones de Pagos y POS |
| **Version** | 1.0 |
| **Fecha** | 2025-12-05 |
| **Estado** | Ready |

---

## Diagrama ER

```mermaid
erDiagram
    tenants ||--o{ payment_providers : has
    payment_providers ||--o| payment_credentials : has
    payment_providers ||--o{ payment_terminals : manages
    tenants ||--o{ payment_transactions : processes
    payment_transactions }o--|| payment_providers : via
    payment_transactions }o--|| payment_terminals : on
    tenants ||--o{ webhook_logs : receives
    tenants ||--o{ reconciliation_batches : runs

    payment_providers {
        uuid id PK
        uuid tenant_id FK
        string provider_type
        string name
        boolean is_active
        boolean is_sandbox
    }

    payment_credentials {
        uuid id PK
        uuid provider_id FK UK
        bytea access_token_encrypted
        bytea refresh_token_encrypted
        timestamp token_expires_at
        jsonb metadata
    }

    payment_terminals {
        uuid id PK
        uuid provider_id FK
        string terminal_id
        string name
        string status
        jsonb device_info
        boolean is_active
    }

    payment_transactions {
        uuid id PK
        uuid tenant_id FK
        uuid provider_id FK
        uuid terminal_id FK
        string external_id UK
        string order_id
        decimal amount
        string currency
        string status
        jsonb gateway_response
    }

    webhook_logs {
        uuid id PK
        uuid tenant_id FK
        uuid provider_id FK
        string event_type
        jsonb payload
        string status
        timestamp received_at
    }

    reconciliation_batches {
        uuid id PK
        uuid tenant_id FK
        uuid provider_id FK
        date batch_date
        int total_transactions
        decimal total_amount
        string status
    }
```

---

## Tablas

### 1. payment_providers

Proveedores de pago configurados por tenant.

```sql
CREATE TABLE integrations.payment_providers (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES core_tenants.tenants(id) ON DELETE CASCADE,

    -- Tipo de proveedor
    provider_type VARCHAR(30) NOT NULL,
    -- mercadopago, clip, stripe, conekta, openpay

    -- Identificacion
    name VARCHAR(100) NOT NULL,
    display_name VARCHAR(100),

    -- Configuracion
    settings JSONB NOT NULL DEFAULT '{}'::jsonb,
    -- Ejemplo: {"country": "MX", "public_key": "...", "webhook_secret": "..."}

    -- Estado
    is_active BOOLEAN NOT NULL DEFAULT true,
    is_sandbox BOOLEAN NOT NULL DEFAULT true,
    connection_status VARCHAR(20) NOT NULL DEFAULT 'disconnected',
    last_connected_at TIMESTAMPTZ,
    last_error VARCHAR(500),

    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by UUID,

    CONSTRAINT uq_payment_providers UNIQUE (tenant_id, provider_type, is_sandbox),
    CONSTRAINT chk_payment_providers_type CHECK (provider_type IN (
        'mercadopago', 'clip', 'stripe', 'conekta', 'openpay', 'paypal'
    )),
    CONSTRAINT chk_payment_providers_status CHECK (connection_status IN (
        'disconnected', 'connecting', 'connected', 'error', 'expired'
    ))
);

-- Indices
CREATE INDEX idx_payment_providers_tenant ON integrations.payment_providers(tenant_id);
CREATE INDEX idx_payment_providers_type ON integrations.payment_providers(provider_type);
CREATE INDEX idx_payment_providers_active ON integrations.payment_providers(tenant_id, is_active)
    WHERE is_active = true;

-- Trigger para updated_at
CREATE TRIGGER trg_payment_providers_updated_at
    BEFORE UPDATE ON integrations.payment_providers
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- RLS
ALTER TABLE integrations.payment_providers ENABLE ROW LEVEL SECURITY;

CREATE POLICY payment_providers_tenant_isolation ON integrations.payment_providers
    FOR ALL
    USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- Comentarios
COMMENT ON TABLE integrations.payment_providers IS 'Proveedores de pago configurados por tenant (MGN-016)';
COMMENT ON COLUMN integrations.payment_providers.is_sandbox IS 'true para ambiente de pruebas';
```

---

### 2. payment_credentials

Credenciales encriptadas de proveedores.

```sql
CREATE TABLE integrations.payment_credentials (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    provider_id UUID NOT NULL UNIQUE REFERENCES integrations.payment_providers(id) ON DELETE CASCADE,

    -- OAuth tokens (encriptados con AES-256)
    access_token_encrypted BYTEA,
    refresh_token_encrypted BYTEA,
    token_type VARCHAR(20) DEFAULT 'Bearer',
    token_expires_at TIMESTAMPTZ,

    -- API Keys (encriptadas)
    api_key_encrypted BYTEA,
    api_secret_encrypted BYTEA,

    -- Metadata OAuth
    oauth_user_id VARCHAR(100),
    oauth_scopes TEXT[],

    -- Estado
    is_valid BOOLEAN NOT NULL DEFAULT true,
    last_refresh_at TIMESTAMPTZ,
    refresh_error VARCHAR(500),

    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Trigger para updated_at
CREATE TRIGGER trg_payment_credentials_updated_at
    BEFORE UPDATE ON integrations.payment_credentials
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- Comentarios
COMMENT ON TABLE integrations.payment_credentials IS 'Credenciales encriptadas de proveedores (PCI compliant)';
COMMENT ON COLUMN integrations.payment_credentials.access_token_encrypted IS 'Token encriptado con AES-256-GCM';
```

---

### 3. payment_terminals

Terminales de pago fisicas.

```sql
CREATE TABLE integrations.payment_terminals (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    provider_id UUID NOT NULL REFERENCES integrations.payment_providers(id) ON DELETE CASCADE,

    -- Identificacion
    external_terminal_id VARCHAR(100) NOT NULL,
    name VARCHAR(100) NOT NULL,
    serial_number VARCHAR(100),

    -- Tipo y modelo
    terminal_type VARCHAR(30) NOT NULL,
    -- point, point_pro, clip_plus, clip_pro, verifone_p400, bbpos_wise
    model VARCHAR(50),
    firmware_version VARCHAR(50),

    -- Ubicacion
    location_name VARCHAR(100),
    location_address VARCHAR(200),

    -- Estado
    status VARCHAR(20) NOT NULL DEFAULT 'offline',
    last_seen_at TIMESTAMPTZ,
    battery_level INT,  -- 0-100 para terminales portatiles

    -- Configuracion
    settings JSONB DEFAULT '{}'::jsonb,
    -- Ejemplo: {"print_receipt": true, "timeout_seconds": 60}

    is_active BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT uq_payment_terminals UNIQUE (provider_id, external_terminal_id),
    CONSTRAINT chk_payment_terminals_type CHECK (terminal_type IN (
        'point', 'point_smart', 'point_pro',
        'clip_plus', 'clip_pro',
        'verifone_p400', 'bbpos_wise', 'stripe_reader'
    )),
    CONSTRAINT chk_payment_terminals_status CHECK (status IN (
        'online', 'offline', 'busy', 'error', 'maintenance'
    ))
);

-- Indices
CREATE INDEX idx_payment_terminals_provider ON integrations.payment_terminals(provider_id);
CREATE INDEX idx_payment_terminals_status ON integrations.payment_terminals(status);
CREATE INDEX idx_payment_terminals_active ON integrations.payment_terminals(provider_id, is_active)
    WHERE is_active = true;

-- Trigger para updated_at
CREATE TRIGGER trg_payment_terminals_updated_at
    BEFORE UPDATE ON integrations.payment_terminals
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- Comentarios
COMMENT ON TABLE integrations.payment_terminals IS 'Terminales de pago fisicas (MGN-016)';
COMMENT ON COLUMN integrations.payment_terminals.external_terminal_id IS 'ID asignado por el proveedor';
```

---

### 4. payment_transactions

Transacciones de pago procesadas.

```sql
CREATE TABLE integrations.payment_transactions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES core_tenants.tenants(id),
    provider_id UUID NOT NULL REFERENCES integrations.payment_providers(id),
    terminal_id UUID REFERENCES integrations.payment_terminals(id),

    -- Identificadores
    external_id VARCHAR(100) NOT NULL,  -- ID del gateway
    order_id VARCHAR(100),               -- ID del pedido interno
    reference VARCHAR(100),              -- Referencia para el cliente

    -- Monto
    amount DECIMAL(15,2) NOT NULL,
    currency VARCHAR(3) NOT NULL DEFAULT 'MXN',
    exchange_rate DECIMAL(10,6) DEFAULT 1.0,

    -- Metodo de pago
    payment_method VARCHAR(30) NOT NULL,
    -- card_present, card_not_present, oxxo, spei, mercado_credito
    card_brand VARCHAR(20),     -- visa, mastercard, amex
    card_last4 VARCHAR(4),
    card_funding VARCHAR(20),   -- credit, debit, prepaid
    installments INT DEFAULT 1,

    -- Estado
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    failure_code VARCHAR(50),
    failure_message VARCHAR(500),

    -- Respuesta del gateway
    gateway_response JSONB NOT NULL DEFAULT '{}'::jsonb,
    authorization_code VARCHAR(50),

    -- Metadata
    metadata JSONB DEFAULT '{}'::jsonb,
    -- Ejemplo: {"customer_email": "...", "description": "..."}

    -- Timestamps
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    processed_at TIMESTAMPTZ,
    settled_at TIMESTAMPTZ,

    -- Conciliacion
    reconciliation_batch_id UUID,
    reconciled_at TIMESTAMPTZ,

    CONSTRAINT uq_payment_transactions UNIQUE (provider_id, external_id),
    CONSTRAINT chk_payment_transactions_status CHECK (status IN (
        'pending', 'processing', 'approved', 'rejected', 'cancelled',
        'refunded', 'partially_refunded', 'chargebacked', 'expired'
    )),
    CONSTRAINT chk_payment_transactions_method CHECK (payment_method IN (
        'card_present', 'card_not_present', 'oxxo', 'spei',
        'mercado_credito', 'paypal', 'bank_transfer'
    )),
    CONSTRAINT chk_payment_transactions_amount CHECK (amount > 0)
);

-- Indices
CREATE INDEX idx_payment_transactions_tenant ON integrations.payment_transactions(tenant_id);
CREATE INDEX idx_payment_transactions_provider ON integrations.payment_transactions(provider_id);
CREATE INDEX idx_payment_transactions_terminal ON integrations.payment_transactions(terminal_id);
CREATE INDEX idx_payment_transactions_order ON integrations.payment_transactions(order_id)
    WHERE order_id IS NOT NULL;
CREATE INDEX idx_payment_transactions_status ON integrations.payment_transactions(status);
CREATE INDEX idx_payment_transactions_date ON integrations.payment_transactions(created_at DESC);
CREATE INDEX idx_payment_transactions_reconciliation ON integrations.payment_transactions(reconciliation_batch_id)
    WHERE reconciliation_batch_id IS NOT NULL;

-- RLS
ALTER TABLE integrations.payment_transactions ENABLE ROW LEVEL SECURITY;

CREATE POLICY payment_transactions_tenant_isolation ON integrations.payment_transactions
    FOR ALL
    USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- Comentarios
COMMENT ON TABLE integrations.payment_transactions IS 'Transacciones de cobro a clientes del tenant';
COMMENT ON COLUMN integrations.payment_transactions.order_id IS 'ID del pedido en el sistema (sales.orders)';
```

---

### 5. refunds

Devoluciones de transacciones.

```sql
CREATE TABLE integrations.refunds (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    transaction_id UUID NOT NULL REFERENCES integrations.payment_transactions(id),

    -- Identificadores
    external_id VARCHAR(100) NOT NULL,
    reference VARCHAR(100),

    -- Monto
    amount DECIMAL(15,2) NOT NULL,
    currency VARCHAR(3) NOT NULL DEFAULT 'MXN',
    refund_type VARCHAR(20) NOT NULL DEFAULT 'full',

    -- Estado
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    failure_message VARCHAR(500),

    -- Motivo
    reason VARCHAR(50),
    notes VARCHAR(500),

    -- Gateway response
    gateway_response JSONB DEFAULT '{}'::jsonb,

    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    processed_at TIMESTAMPTZ,
    created_by UUID,

    CONSTRAINT chk_refunds_type CHECK (refund_type IN ('full', 'partial')),
    CONSTRAINT chk_refunds_status CHECK (status IN ('pending', 'processing', 'approved', 'rejected')),
    CONSTRAINT chk_refunds_amount CHECK (amount > 0)
);

-- Indices
CREATE INDEX idx_refunds_transaction ON integrations.refunds(transaction_id);
CREATE INDEX idx_refunds_status ON integrations.refunds(status);
CREATE INDEX idx_refunds_date ON integrations.refunds(created_at DESC);

-- Comentarios
COMMENT ON TABLE integrations.refunds IS 'Devoluciones de transacciones';
```

---

### 6. webhook_logs

Log de webhooks recibidos.

```sql
CREATE TABLE integrations.webhook_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID REFERENCES core_tenants.tenants(id),
    provider_id UUID REFERENCES integrations.payment_providers(id),

    -- Evento
    event_type VARCHAR(100) NOT NULL,
    event_id VARCHAR(100),

    -- Payload
    payload JSONB NOT NULL,
    headers JSONB DEFAULT '{}'::jsonb,
    signature VARCHAR(500),

    -- Estado de procesamiento
    status VARCHAR(20) NOT NULL DEFAULT 'received',
    processed_at TIMESTAMPTZ,
    error_message VARCHAR(500),
    retry_count INT NOT NULL DEFAULT 0,

    -- Request info
    source_ip VARCHAR(45),

    received_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT chk_webhook_logs_status CHECK (status IN (
        'received', 'processing', 'processed', 'failed', 'ignored'
    ))
);

-- Indices
CREATE INDEX idx_webhook_logs_tenant ON integrations.webhook_logs(tenant_id);
CREATE INDEX idx_webhook_logs_provider ON integrations.webhook_logs(provider_id);
CREATE INDEX idx_webhook_logs_event ON integrations.webhook_logs(event_type);
CREATE INDEX idx_webhook_logs_status ON integrations.webhook_logs(status);
CREATE INDEX idx_webhook_logs_date ON integrations.webhook_logs(received_at DESC);

-- Particionamiento por mes (opcional, para alto volumen)
-- CREATE TABLE integrations.webhook_logs_y2025m01 PARTITION OF integrations.webhook_logs
--     FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

-- Comentarios
COMMENT ON TABLE integrations.webhook_logs IS 'Log de webhooks de proveedores de pago';
COMMENT ON COLUMN integrations.webhook_logs.signature IS 'Firma HMAC para validacion';
```

---

### 7. reconciliation_batches

Lotes de conciliacion.

```sql
CREATE TABLE integrations.reconciliation_batches (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES core_tenants.tenants(id),
    provider_id UUID NOT NULL REFERENCES integrations.payment_providers(id),

    -- Periodo
    batch_date DATE NOT NULL,
    period_start TIMESTAMPTZ NOT NULL,
    period_end TIMESTAMPTZ NOT NULL,

    -- Totales esperados (del gateway)
    expected_count INT NOT NULL DEFAULT 0,
    expected_amount DECIMAL(15,2) NOT NULL DEFAULT 0,

    -- Totales encontrados (en sistema)
    matched_count INT NOT NULL DEFAULT 0,
    matched_amount DECIMAL(15,2) NOT NULL DEFAULT 0,

    -- Diferencias
    unmatched_count INT NOT NULL DEFAULT 0,
    difference_amount DECIMAL(15,2) NOT NULL DEFAULT 0,

    -- Estado
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    completed_at TIMESTAMPTZ,

    -- Detalle de diferencias
    discrepancies JSONB DEFAULT '[]'::jsonb,

    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by UUID,

    CONSTRAINT uq_reconciliation_batches UNIQUE (tenant_id, provider_id, batch_date),
    CONSTRAINT chk_reconciliation_status CHECK (status IN (
        'pending', 'in_progress', 'completed', 'completed_with_discrepancies', 'failed'
    ))
);

-- Indices
CREATE INDEX idx_reconciliation_batches_tenant ON integrations.reconciliation_batches(tenant_id);
CREATE INDEX idx_reconciliation_batches_provider ON integrations.reconciliation_batches(provider_id);
CREATE INDEX idx_reconciliation_batches_date ON integrations.reconciliation_batches(batch_date DESC);
CREATE INDEX idx_reconciliation_batches_status ON integrations.reconciliation_batches(status);

-- RLS
ALTER TABLE integrations.reconciliation_batches ENABLE ROW LEVEL SECURITY;

CREATE POLICY reconciliation_batches_tenant_isolation ON integrations.reconciliation_batches
    FOR ALL
    USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- Comentarios
COMMENT ON TABLE integrations.reconciliation_batches IS 'Lotes de conciliacion con proveedores';
```

---

## Funciones de Utilidad

### Procesar Webhook de Pago

```sql
CREATE OR REPLACE FUNCTION integrations.process_payment_webhook(
    p_provider_type VARCHAR,
    p_event_type VARCHAR,
    p_external_id VARCHAR,
    p_status VARCHAR,
    p_gateway_response JSONB
) RETURNS UUID AS $$
DECLARE
    v_transaction_id UUID;
    v_old_status VARCHAR;
BEGIN
    -- Buscar transaccion
    SELECT id, status INTO v_transaction_id, v_old_status
    FROM integrations.payment_transactions
    WHERE external_id = p_external_id;

    IF v_transaction_id IS NULL THEN
        RAISE EXCEPTION 'Transaction not found: %', p_external_id;
    END IF;

    -- Actualizar estado
    UPDATE integrations.payment_transactions
    SET
        status = p_status,
        gateway_response = gateway_response || p_gateway_response,
        processed_at = CASE WHEN p_status IN ('approved', 'rejected') THEN NOW() ELSE processed_at END,
        updated_at = NOW()
    WHERE id = v_transaction_id;

    -- TODO: Notificar cambio de estado via MGN-014

    RETURN v_transaction_id;
END;
$$ LANGUAGE plpgsql;
```

### Obtener Estadisticas de Proveedor

```sql
CREATE OR REPLACE FUNCTION integrations.get_provider_stats(
    p_provider_id UUID,
    p_date_from DATE DEFAULT CURRENT_DATE - 30,
    p_date_to DATE DEFAULT CURRENT_DATE
) RETURNS TABLE (
    total_transactions INT,
    total_amount DECIMAL,
    approved_count INT,
    approved_amount DECIMAL,
    rejected_count INT,
    avg_transaction_amount DECIMAL,
    approval_rate DECIMAL
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        COUNT(*)::INT AS total_transactions,
        COALESCE(SUM(amount), 0) AS total_amount,
        COUNT(*) FILTER (WHERE status = 'approved')::INT AS approved_count,
        COALESCE(SUM(amount) FILTER (WHERE status = 'approved'), 0) AS approved_amount,
        COUNT(*) FILTER (WHERE status = 'rejected')::INT AS rejected_count,
        COALESCE(AVG(amount) FILTER (WHERE status = 'approved'), 0) AS avg_transaction_amount,
        CASE
            WHEN COUNT(*) > 0
            THEN (COUNT(*) FILTER (WHERE status = 'approved')::DECIMAL / COUNT(*) * 100)
            ELSE 0
        END AS approval_rate
    FROM integrations.payment_transactions
    WHERE provider_id = p_provider_id
      AND created_at::date BETWEEN p_date_from AND p_date_to;
END;
$$ LANGUAGE plpgsql;
```

---

## Vistas

### Vista: Transacciones Recientes

```sql
CREATE VIEW integrations.vw_recent_transactions AS
SELECT
    pt.id,
    pt.tenant_id,
    pp.provider_type,
    pp.name AS provider_name,
    pterm.name AS terminal_name,
    pt.external_id,
    pt.order_id,
    pt.amount,
    pt.currency,
    pt.payment_method,
    pt.card_brand,
    pt.card_last4,
    pt.status,
    pt.created_at,
    pt.processed_at
FROM integrations.payment_transactions pt
JOIN integrations.payment_providers pp ON pp.id = pt.provider_id
LEFT JOIN integrations.payment_terminals pterm ON pterm.id = pt.terminal_id
WHERE pt.created_at > CURRENT_TIMESTAMP - INTERVAL '7 days';
```

### Vista: Estado de Proveedores

```sql
CREATE VIEW integrations.vw_provider_status AS
SELECT
    pp.id,
    pp.tenant_id,
    pp.provider_type,
    pp.name,
    pp.is_active,
    pp.is_sandbox,
    pp.connection_status,
    pp.last_connected_at,
    pc.is_valid AS credentials_valid,
    pc.token_expires_at,
    (SELECT COUNT(*) FROM integrations.payment_terminals t WHERE t.provider_id = pp.id AND t.is_active) AS active_terminals,
    (SELECT COUNT(*) FROM integrations.payment_transactions t
     WHERE t.provider_id = pp.id AND t.created_at > CURRENT_DATE) AS transactions_today,
    (SELECT SUM(amount) FROM integrations.payment_transactions t
     WHERE t.provider_id = pp.id AND t.status = 'approved' AND t.created_at > CURRENT_DATE) AS amount_today
FROM integrations.payment_providers pp
LEFT JOIN integrations.payment_credentials pc ON pc.provider_id = pp.id;
```

---

## Resumen de Tablas

| Tabla | Columnas | Descripcion |
|-------|----------|-------------|
| payment_providers | 15 | Proveedores de pago por tenant |
| payment_credentials | 14 | Credenciales encriptadas |
| payment_terminals | 18 | Terminales fisicas |
| payment_transactions | 27 | Transacciones de cobro |
| refunds | 14 | Devoluciones |
| webhook_logs | 14 | Logs de webhooks |
| reconciliation_batches | 16 | Lotes de conciliacion |

**Total: 7 tablas, 118 columnas**

---

## Historial

| Version | Fecha | Autor | Cambios |
|---------|-------|-------|---------|
| 1.0 | 2025-12-05 | System | Creacion inicial (MGN-016) |