name: trenza-crm
description: >
Gestión de clientes y relaciones: leads, oportunidades, actividades para TrenzaOS.
Trigger: Al trabajar con clientes, leads, oportunidades, actividades comerciales, o CRM.
license: MIT
metadata:
author: trenza
version: "1.0"

TrenzaOS CRM Skills

Purpose

Este skill enforce las reglas de negocio para gestión de relaciones con clientes.

Core Rules

1. Clientes

CREATE TABLE customers (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id       UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,

  -- Identificación
  name            TEXT NOT NULL,  -- Empresa o nombre
  type            TEXT NOT NULL,  -- company, individual

  -- Datos fiscales (si empresa)
  tax_id          TEXT,  -- NIF/CIF
  legal_name      TEXT,

  -- Contacto principal
  email           TEXT,
  phone           TEXT,
  website         TEXT,

  -- Dirección
  address         TEXT,
  city            TEXT,
  state           TEXT,
  country         TEXT,

  -- Clasificación
  customer_type   TEXT,  -- prospect, active, vip, inactive
  industry        TEXT,

  -- metadata
  metadata        JSONB DEFAULT '{}',

  -- Estado
  status          TEXT DEFAULT 'active',

  -- Auditoría
  created_by      UUID REFERENCES users(id),
  created_at      TIMESTAMPTZ DEFAULT NOW(),
  updated_at      TIMESTAMPTZ DEFAULT NOW()
);

-- Contactos (personas de una empresa)
CREATE TABLE contacts (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id       UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,

  customer_id     UUID REFERENCES customers(id),

  first_name      TEXT NOT NULL,
  last_name       TEXT NOT NULL,
  email           TEXT,
  phone           TEXT,
  position        TEXT,

  is_primary      BOOLEAN DEFAULT false,

  status          TEXT DEFAULT 'active',

  created_at      TIMESTAMPTZ DEFAULT NOW()
);

2. Leads

CREATE TABLE leads (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id       UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,

  -- Fuente
  source          TEXT,  -- website, referral, cold_call, trade_show
  campaign_id     UUID REFERENCES campaigns(id),

  -- Datos del lead
  name            TEXT NOT NULL,  -- Nombre de la oportunidad/empresa
  contact_name    TEXT NOT NULL,
  email           TEXT,
  phone           TEXT,

  -- Clasificación inicial
  status          TEXT DEFAULT 'new',  -- new, contacted, qualified, converted, lost

  -- Scoring
  score           INTEGER DEFAULT 0,

  -- Valor estimado
  estimated_value NUMERIC(12, 2),

  -- Notas
  notes           TEXT,

  -- Asignación
  assigned_to     UUID REFERENCES users(id),

  -- Conversión
  converted_to    UUID REFERENCES customers(id),
  converted_at    TIMESTAMPTZ,

  created_at      TIMESTAMPTZ DEFAULT NOW(),
  updated_at      TIMESTAMPTZ DEFAULT NOW()
);

3. Oportunidades

CREATE TABLE opportunities (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id       UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,

  -- Referencia
  customer_id     UUID REFERENCES customers(id),
  lead_id         UUID REFERENCES leads(id),

  -- Datos
  name            TEXT NOT NULL,
  description     TEXT,

  -- Valor
  value           NUMERIC(12, 2),
  currency        TEXT DEFAULT 'EUR',

  -- Etapa (Kanban)
  stage           TEXT DEFAULT 'prospecting',  -- prospecting, qualification, proposal, negotiation, closed_won, closed_lost

  -- Probabilidad
  probability     INTEGER DEFAULT 10,  -- 0-100

  -- Fechas
  expected_close   DATE,
  closed_at       TIMESTAMPTZ,

  -- Estado
  status          TEXT DEFAULT 'active',

  -- owner
  owner_id        UUID REFERENCES users(id),

  created_at      TIMESTAMPTZ DEFAULT NOW(),
  updated_at      TIMESTAMPTZ DEFAULT NOW()
);

4. Actividades

CREATE TABLE activities (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id       UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,

  -- Tipo
  activity_type   TEXT NOT NULL,  -- call, meeting, email, task, note

  -- Relacionado con
  customer_id     UUID REFERENCES customers(id),
  opportunity_id UUID REFERENCES opportunities(id),
  lead_id         UUID REFERENCES leads(id),

  -- Datos
  subject         TEXT NOT NULL,
  description     TEXT,

  -- Fechas
  due_date        TIMESTAMPTZ,
  completed_at    TIMESTAMPTZ,

  -- Estado
  status          TEXT DEFAULT 'pending',  -- pending, completed, cancelled

  -- Asignación
  assigned_to     UUID REFERENCES users(id),
  created_by      UUID REFERENCES users(id),

  created_at      TIMESTAMPTZ DEFAULT NOW()
);

5. Server Actions

// Crear lead
const CreateLeadSchema = z.object({
  name: z.string().min(1),
  contactName: z.string().min(1),
  email: z.string().email().optional(),
  phone: z.string().optional(),
  source: z.enum(['website', 'referral', 'cold_call', 'trade_show']).optional(),
  estimatedValue: z.number().positive().optional()
})

async function createLead(prevState, formData: FormData) {
  const data = CreateLeadSchema.parse(Object.fromEntries(formData))

  const lead = await db.insert(leads).values({
    tenantId,
    ...data,
    status: 'new',
    score: calculateInitialScore(data.source)
  }).returning()

  // Crear actividad de seguimiento
  await createFollowUpActivity(lead.id)

  return { status: 'success', data: { leadId: lead.id } }
}

// Convertir lead a cliente
async function convertLead(leadId: string, customerData: CustomerData) {
  const lead = await getLead(leadId)

  if (lead.status !== 'qualified') {
    return { status: 'error', error_code: 'LEAD_NOT_QUALIFIED' }
  }

  const customer = await db.transaction(async (tx) => {
    // Crear cliente
    const cust = await tx.insert(customers).values({
      tenantId,
      name: customerData.name,
      email: lead.email,
      phone: lead.phone,
      type: customerData.type,
      status: 'active'
    }).returning()

    // Actualizar lead
    await tx
      .update(leads)
      .set({ 
        status: 'converted',
        convertedTo: cust.id,
        convertedAt: new Date()
      })
      .where(eq(leads.id, leadId))

    return cust
  })

  return { status: 'success', data: { customerId: customer.id } }
}

6. Kanban de Oportunidades

// Mover oportunidad de etapa
async function moveOpportunityStage(
  opportunityId: string, 
  newStage: string
) {
  // Validar transición
  const validTransitions = {
    'prospecting': ['qualification'],
    'qualification': ['proposal', 'prospecting'],
    'proposal': ['negotiation', 'qualification'],
    'negotiation': ['closed_won', 'closed_lost'],
    'closed_won': [],
    'closed_lost': []
  }

  const opportunity = await getOpportunity(opportunityId)
  if (!validTransitions[opportunity.stage].includes(newStage)) {
    return { status: 'error', error_code: 'INVALID_STAGE_TRANSITION' }
  }

  // Actualizar
  await db
    .update(opportunities)
    .set({ 
      stage: newStage,
      updatedAt: new Date(),
      closedAt: newStage.startsWith('closed_') ? new Date() : null
    })
    .where(eq(opportunities.id, opportunityId))

  return { status: 'success' }
}

7. Pipeline de Ventas

// Calcular forecast
async function getSalesForecast(tenantId: string) {
  const opportunities = await db
    .select({
      stage: opportunities.stage,
      value: opportunities.value,
      probability: opportunities.probability
    })
    .from(opportunities)
    .where(
      and(
        eq(opportunities.tenantId, tenantId),
        eq(opportunities.status, 'active'),
        sql`${opportunities.expectedClose} >= NOW()`
      )
    )

  // Calcular weighted value
  const pipeline = opportunities.reduce((acc, opp) => {
    const weighted = opp.value * (opp.probability / 100)

    acc.total += opp.value
    acc.weighted += weighted
    acc.byStage[opp.stage] = (acc.byStage[opp.stage] || 0) + weighted

    return acc
  }, { total: 0, weighted: 0, byStage: {} })

  return pipeline
}

CRM Checklist

• [ ] ¿Tienes diferenciación entre leads y clientes?
• [ ] ¿Validas transiciones de etapa en oportunidades?
• [ ] ¿Registras todas las actividades?
• [ ] ¿El scoring de leads es automático?
• [ ] ¿Tienes pipeline/kanban?

References

• Database Schema
• API Standards