name: trenza-finance
description: >
Gestión financiera: facturas, cobros, pagos y reportes para TrenzaOS.
Trigger: Al trabajar con facturas, cobros, pagos, reportes financieros, o contabilidad.
license: MIT
metadata:
author: trenza
version: "1.0"

TrenzaOS Finance Skills

Purpose

Este skill enforce las reglas de negocio para gestión financiera.

⚠️ Reglas Críticas

• Facturas pagadas NO pueden modificarse
• Solo admins pueden aprobar pagos
• Todo movimiento financiero debe auditarse

Core Rules

1. Estructura de Facturas

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

  invoice_number  TEXT NOT NULL,
  series          TEXT DEFAULT 'INV',  -- INV, FAC, A

  -- Cliente
  customer_id     UUID REFERENCES customers(id),
  customer_name   TEXT NOT NULL,
  customer_tax_id  TEXT,  -- NIF/CIF

  -- Fechas
  issue_date      DATE NOT NULL,
  due_date        DATE,

  -- Totales
  subtotal        NUMERIC(12, 2) NOT NULL,
  tax_amount      NUMERIC(12, 2) DEFAULT 0,
  discount_amount NUMERIC(12, 2) DEFAULT 0,
  total           NUMERIC(12, 2) NOT NULL,
  currency        TEXT DEFAULT 'EUR',

  -- Estado (CRÍTICO)
  status          TEXT DEFAULT 'draft',  -- draft, sent, viewed, paid, overdue, cancelled

  -- Notas
  notes           TEXT,
  terms           TEXT,  -- Condiciones

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

-- Número de factura único por serie y tenant
CREATE UNIQUE INDEX idx_invoices_number_tenant ON invoices(tenant_id, series, invoice_number);

2. Líneas de Factura

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

  -- Producto (opcional)
  product_id      UUID REFERENCES products(id),

  -- Descripción
  description     TEXT NOT NULL,

  -- Cantidad y precios
  quantity        NUMERIC(10, 2) NOT NULL,
  unit_price      NUMERIC(12, 2) NOT NULL,
  tax_rate        NUMERIC(5, 2) DEFAULT 0,
  discount_rate   NUMERIC(5, 2) DEFAULT 0,

  -- Calculados
  subtotal        NUMERIC(12, 2) NOT NULL,
  tax_amount      NUMERIC(12, 2) DEFAULT 0,
  total           NUMERIC(12, 2) NOT NULL,

  created_at      TIMESTAMPTZ DEFAULT NOW()
);

3. Cobros y Pagos

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

  -- Referencia
  invoice_id      UUID REFERENCES invoices(id),

  -- Método de pago
  payment_method  TEXT NOT NULL,  -- cash, transfer, card, stripe, mercadopago
  reference       TEXT,  -- Número de transferencia, ID de Stripe, etc.

  -- Fechas
  payment_date    DATE NOT NULL,

  -- Amount
  amount          NUMERIC(12, 2) NOT NULL,
  currency        TEXT DEFAULT 'EUR',

  -- Estado
  status          TEXT DEFAULT 'pending',  -- pending, completed, failed, refunded

  -- Metadata
  metadata        JSONB DEFAULT '{}',

  created_at      TIMESTAMPTZ DEFAULT NOW()
);

4. Server Actions

// Crear factura (borrador)
const CreateInvoiceSchema = z.object({
  customerId: z.string().uuid(),
  dueDate: z.string().datetime().optional(),
  items: z.array(z.object({
    productId: z.string().uuid().optional(),
    description: z.string(),
    quantity: z.number().positive(),
    unitPrice: z.number().positive(),
    taxRate: z.number().min(0).max(100).default(0)
  })).min(1)
})

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

  // 1. Calcular totales
  const items = data.items.map(item => ({
    ...item,
    subtotal: item.quantity * item.unitPrice,
    taxAmount: item.quantity * item.unitPrice * (item.taxRate / 100),
    total: item.quantity * item.unitPrice * (1 + item.taxRate / 100)
  }))

  const subtotal = items.reduce((sum, i) => sum + i.subtotal, 0)
  const taxAmount = items.reduce((sum, i) => sum + i.taxAmount, 0)
  const total = items.reduce((sum, i) => sum + i.total, 0)

  // 2. Generar número de factura
  const invoiceNumber = await generateInvoiceNumber(tenantId, 'INV')

  // 3. Crear en transacción
  const invoice = await db.transaction(async (tx) => {
    const inv = await tx.insert(invoices).values({
      tenantId,
      invoiceNumber,
      customerId: data.customerId,
      issueDate: new Date(),
      dueDate: data.dueDate,
      subtotal,
      taxAmount,
      total,
      status: 'draft',
      createdBy: userId
    }).returning()

    // Insertar líneas
    for (const item of items) {
      await tx.insert(invoiceItems).values({
        invoiceId: inv.id,
        tenantId,
        ...item
      })
    }

    return inv
  })

  return { status: 'success', data: { invoiceId: invoice.id } }
}

5. Cobrar Factura

// Registrar pago de factura
async function payInvoice(invoiceId: string, paymentData: PaymentData) {
  // 1. Verificar factura existe y está pendiente
  const invoice = await getInvoice(invoiceId)

  if (invoice.status === 'paid') {
    return { status: 'error', error_code: 'INVOICE_ALREADY_PAID' }
  }

  if (invoice.status === 'cancelled') {
    return { status: 'error', error_code: 'INVOICE_CANCELLED' }
  }

  // 2. Verificar monto
  if (paymentData.amount < invoice.total) {
    return { status: 'error', error_code: 'INSUFFICIENT_PAYMENT' }
  }

  // 3. Registrar pago
  await db.transaction(async (tx) => {
    // Actualizar factura
    await tx
      .update(invoices)
      .set({ 
        status: 'paid',
        paidAt: new Date(),
        updatedAt: new Date()
      })
      .where(eq(invoices.id, invoiceId))

    // Insertar pago
    await tx.insert(payments).values({
      tenantId,
      invoiceId,
      paymentMethod: paymentData.method,
      reference: paymentData.reference,
      paymentDate: new Date(),
      amount: paymentData.amount,
      status: 'completed'
    })

    // Auditar
    await tx.insert(auditLogs).values({
      tenantId,
      action: 'INVOICE_PAID',
      resourceType: 'invoice',
      resourceId: invoiceId,
      outcome: 'success'
    })
  })

  return { status: 'success' }
}

6. Facturación Recurrente

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

  customer_id     UUID NOT NULL REFERENCES customers(id),

  -- Frecuencia
  frequency       TEXT NOT NULL,  -- monthly, quarterly, yearly

  -- Próxima factura
  next_date       DATE NOT NULL,

  -- Plantilla
  items           JSONB NOT NULL,  -- items de la factura

  -- Estado
  is_active       BOOLEAN DEFAULT true,

  created_at      TIMESTAMPTZ DEFAULT NOW()
);

7. Reportes Financieros

// Reporte de cuentas por cobrar
async function getAccountsReceivable(tenantId: string) {
  return await db
    .select({
      customer: customers.name,
      invoiceNumber: invoices.invoiceNumber,
      total: invoices.total,
      dueDate: invoices.dueDate,
      daysOverdue: sql`EXTRACT(DAY FROM NOW() - ${invoices.dueDate})`
    })
    .from(invoices)
    .leftJoin(customers, eq(invoices.customerId, customers.id))
    .where(
      and(
        eq(invoices.tenantId, tenantId),
        eq(invoices.status, 'sent'),
        or(
          eq(invoices.dueDate, sql`NOW()::date`),
          sql`${invoices.dueDate} < NOW()::date`
        )
      )
    )
    .orderBy(invoices.dueDate)
}

Finance Checklist

• [ ] ¿Facturas pagadas no se pueden modificar?
• [ ] ¿Tienes auditoría de todos los pagos?
• [ ] ¿Generas números de factura secuenciales?
• [ ] ¿Validas monto mínimo en cobros?
• [ ] ¿Manejas facturas recurrentes?

References

• Database Schema
• API Standards
• Security Manifesto