SCHEMA-PLANES-ADDON

📦 ADD-ON SCHEMA: Sistema de Planes y Suscripciones

🎯 Tablas necesarias para implementar la Guía Técnica de Planes

Tu schema actual está muy bien para el MVP. Para agregar el sistema completo de planes descrito en la guía técnica, necesitas estas 5 tablas adicionales:

---

✅ TABLAS A AGREGAR

1. subscription_plans (Catálogo de planes)

CREATE TABLE subscription_plans (
    id BIGSERIAL PRIMARY KEY,
 
    -- Identificación
    name VARCHAR(50) UNIQUE NOT NULL, -- 'individual', 'starter', 'pro', 'business'
    display_name VARCHAR(100) NOT NULL, -- 'Individual', 'Starter', 'Pro', 'Business'
    description TEXT,
 
    -- Pricing
    price INT NOT NULL, -- Precio base mensual (CLP sin decimales)
    extra_professional_price INT NOT NULL DEFAULT 4000, -- $4.000 por profesional adicional
 
    -- Límites (JSONB para flexibilidad)
    limits JSONB NOT NULL DEFAULT '{}',
    /* Estructura del JSON:
    {
        "workers": 7,              // Máximo de workers (-1 = ilimitado)
        "base_workers": 2,         // Workers incluidos en precio base
        "services": 10,            // Máximo de servicios (-1 = ilimitado)
        "locations": 1,            // Máximo de sucursales (-1 = ilimitado)
        "emails": 1000,            // Emails por mes (-1 = ilimitado)
        "whatsapp_messages": 150,  // WhatsApp por mes
        "storage_mb": 500          // Almacenamiento
    }
    */
 
    -- Features (JSONB con array de features)
    features JSONB NOT NULL DEFAULT '[]',
    /* Estructura del JSON:
    [
        "dashboard",
        "calendar",
        "team_management",
        "basic_finances",
        "advanced_analytics",
        ...
    ]
    */
 
    -- Orden y visibilidad
    sort_order INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    is_visible_marketplace BOOLEAN DEFAULT TRUE,
 
    -- Metadata
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);
 
-- Índices
CREATE INDEX idx_subscription_plans_name ON subscription_plans(name);
CREATE INDEX idx_subscription_plans_active ON subscription_plans(is_active);
CREATE INDEX idx_subscription_plans_sort ON subscription_plans(sort_order);

---

2. team_subscriptions (Suscripción activa del team)

CREATE TABLE team_subscriptions (
    id BIGSERIAL PRIMARY KEY,
    team_id BIGINT NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
    subscription_plan_id BIGINT NOT NULL REFERENCES subscription_plans(id),
 
    -- Estado
    status VARCHAR(50) DEFAULT 'active', -- active, cancelled, suspended, past_due, trialing
 
    -- Pricing
    base_price INT NOT NULL, -- Snapshot del precio base al contratar
    extra_professionals INT DEFAULT 0, -- Cantidad de profesionales adicionales contratados
    extra_professionals_cost INT DEFAULT 0, -- Costo de profesionales adicionales
 
    -- Add-ons WhatsApp
    whatsapp_packs INT DEFAULT 0, -- Cantidad de packs WhatsApp comprados
    whatsapp_packs_cost INT DEFAULT 0, -- Costo mensual de packs WhatsApp
 
    -- Total calculado
    total_monthly_cost INT NOT NULL, -- base_price + extra_professionals_cost + whatsapp_packs_cost
 
    -- Períodos
    current_period_start TIMESTAMP NOT NULL,
    current_period_end TIMESTAMP NOT NULL,
 
    -- Trial
    trial_ends_at TIMESTAMP,
    trial_days INT DEFAULT 14,
 
    -- Cancelación
    cancelled_at TIMESTAMP,
    cancel_reason TEXT,
    ends_at TIMESTAMP, -- Si cancela, hasta cuándo tiene acceso
 
    -- Renovación automática
    auto_renew BOOLEAN DEFAULT TRUE,
 
    -- Metadata
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
 
    -- Constraint: Solo 1 suscripción activa por team
    UNIQUE(team_id) WHERE status = 'active'
);
 
-- Índices
CREATE INDEX idx_team_subscriptions_team_id ON team_subscriptions(team_id);
CREATE INDEX idx_team_subscriptions_plan_id ON team_subscriptions(subscription_plan_id);
CREATE INDEX idx_team_subscriptions_status ON team_subscriptions(status);
CREATE INDEX idx_team_subscriptions_trial ON team_subscriptions(trial_ends_at) WHERE trial_ends_at IS NOT NULL;
CREATE INDEX idx_team_subscriptions_period ON team_subscriptions(current_period_end);
 
-- RLS
ALTER TABLE team_subscriptions ENABLE ROW LEVEL SECURITY;
CREATE POLICY team_isolation ON team_subscriptions
    USING (team_id = current_setting('app.current_team_id')::bigint);

---

3. subscription_usage (Tracking de uso mensual)

CREATE TABLE subscription_usage (
    id BIGSERIAL PRIMARY KEY,
    team_subscription_id BIGINT NOT NULL REFERENCES team_subscriptions(id) ON DELETE CASCADE,
    team_id BIGINT NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
 
    -- Período de medición
    period_start DATE NOT NULL,
    period_end DATE NOT NULL,
 
    -- Contadores de uso
    emails_sent INT DEFAULT 0,
    whatsapp_messages_sent INT DEFAULT 0,
    storage_used_mb INT DEFAULT 0,
    appointments_created INT DEFAULT 0,
 
    -- Workers activos en el período
    active_workers INT DEFAULT 0,
 
    -- Metadata adicional
    usage_data JSONB DEFAULT '{}',
 
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
 
    -- Un registro por team por período
    UNIQUE(team_id, period_start, period_end)
);
 
-- Índices
CREATE INDEX idx_subscription_usage_team_id ON subscription_usage(team_id);
CREATE INDEX idx_subscription_usage_period ON subscription_usage(period_start, period_end);
CREATE INDEX idx_subscription_usage_subscription ON subscription_usage(team_subscription_id);
 
-- RLS
ALTER TABLE subscription_usage ENABLE ROW LEVEL SECURITY;
CREATE POLICY team_isolation ON subscription_usage
    USING (team_id = current_setting('app.current_team_id')::bigint);

---

4. subscription_invoices (Historial de facturación)

CREATE TABLE subscription_invoices (
    id BIGSERIAL PRIMARY KEY,
    team_subscription_id BIGINT NOT NULL REFERENCES team_subscriptions(id) ON DELETE CASCADE,
    team_id BIGINT NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
 
    -- Identificadores
    invoice_number VARCHAR(50) UNIQUE NOT NULL, -- INV-2025-001234
 
    -- Montos
    subtotal INT NOT NULL,
    tax_amount INT DEFAULT 0, -- IVA (19% en Chile)
    total_amount INT NOT NULL,
 
    -- Líneas de cobro
    line_items JSONB NOT NULL DEFAULT '[]',
    /* Estructura del JSON:
    [
        {
            "description": "Plan Pro - Enero 2025",
            "quantity": 1,
            "unit_price": 47990,
            "total": 47990
        },
        {
            "description": "3 profesionales adicionales",
            "quantity": 3,
            "unit_price": 4000,
            "total": 12000
        },
        {
            "description": "2 packs WhatsApp",
            "quantity": 2,
            "unit_price": 2500,
            "total": 5000
        }
    ]
    */
 
    -- Período facturado
    period_start DATE NOT NULL,
    period_end DATE NOT NULL,
 
    -- Estado
    status VARCHAR(50) DEFAULT 'draft', -- draft, issued, paid, overdue, void
 
    -- Fechas importantes
    issued_at TIMESTAMP,
    due_at TIMESTAMP,
    paid_at TIMESTAMP,
 
    -- Método de pago
    payment_method VARCHAR(50), -- credit_card, debit, transfer, cash
    payment_reference VARCHAR(255), -- ID de transacción externa
 
    -- Metadata
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);
 
-- Índices
CREATE INDEX idx_subscription_invoices_team_id ON subscription_invoices(team_id);
CREATE INDEX idx_subscription_invoices_subscription ON subscription_invoices(team_subscription_id);
CREATE INDEX idx_subscription_invoices_number ON subscription_invoices(invoice_number);
CREATE INDEX idx_subscription_invoices_status ON subscription_invoices(status);
CREATE INDEX idx_subscription_invoices_due ON subscription_invoices(due_at) WHERE status IN ('issued', 'overdue');
 
-- RLS
ALTER TABLE subscription_invoices ENABLE ROW LEVEL SECURITY;
CREATE POLICY team_isolation ON subscription_invoices
    USING (team_id = current_setting('app.current_team_id')::bigint);

---

5. subscription_changes_log (Auditoría de cambios de plan)

CREATE TABLE subscription_changes_log (
    id BIGSERIAL PRIMARY KEY,
    team_subscription_id BIGINT NOT NULL REFERENCES team_subscriptions(id) ON DELETE CASCADE,
    team_id BIGINT NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
 
    -- Cambio realizado
    change_type VARCHAR(50) NOT NULL, -- plan_upgrade, plan_downgrade, addon_purchase, addon_cancel, cancellation, reactivation
 
    -- Plan anterior y nuevo (si aplica)
    old_plan_id BIGINT REFERENCES subscription_plans(id),
    new_plan_id BIGINT REFERENCES subscription_plans(id),
 
    -- Valores antes y después
    old_values JSONB DEFAULT '{}',
    new_values JSONB DEFAULT '{}',
    /* Ejemplo:
    old_values: {"plan": "starter", "extra_professionals": 2, "whatsapp_packs": 1}
    new_values: {"plan": "pro", "extra_professionals": 5, "whatsapp_packs": 3}
    */
 
    -- Metadata del cambio
    reason TEXT, -- Razón del cambio (opcional)
    changed_by BIGINT REFERENCES users(id), -- Usuario que realizó el cambio
 
    -- Impacto financiero
    price_difference INT, -- Diferencia de precio (positivo = más caro, negativo = más barato)
    effective_from TIMESTAMP, -- Cuándo entra en efecto
 
    created_at TIMESTAMP DEFAULT NOW()
);
 
-- Índices
CREATE INDEX idx_subscription_changes_team_id ON subscription_changes_log(team_id);
CREATE INDEX idx_subscription_changes_subscription ON subscription_changes_log(team_subscription_id);
CREATE INDEX idx_subscription_changes_type ON subscription_changes_log(change_type);
CREATE INDEX idx_subscription_changes_date ON subscription_changes_log(created_at DESC);
 
-- RLS
ALTER TABLE subscription_changes_log ENABLE ROW LEVEL SECURITY;
CREATE POLICY team_isolation ON subscription_changes_log
    USING (team_id = current_setting('app.current_team_id')::bigint);

---

🔧 MODIFICACIONES A TABLAS EXISTENTES

Actualizar teams (agregar campos de tracking)

ALTER TABLE teams ADD COLUMN IF NOT EXISTS current_plan_name VARCHAR(50) DEFAULT 'free';
ALTER TABLE teams ADD COLUMN IF NOT EXISTS trial_ends_at TIMESTAMP;
 
-- Contadores de uso mensual (se resetean cada mes)
ALTER TABLE teams ADD COLUMN IF NOT EXISTS emails_sent_this_month INT DEFAULT 0;
ALTER TABLE teams ADD COLUMN IF NOT EXISTS whatsapp_sent_this_month INT DEFAULT 0;
ALTER TABLE teams ADD COLUMN IF NOT EXISTS usage_reset_date DATE DEFAULT CURRENT_DATE;
 
-- Índices
CREATE INDEX idx_teams_plan_name ON teams(current_plan_name);
CREATE INDEX idx_teams_trial ON teams(trial_ends_at) WHERE trial_ends_at IS NOT NULL;

Nota: Estos campos son cache/denormalizados para queries rápidas. La fuente de verdad sigue siendo team_subscriptions.

---

Actualizar subscriptions existente (si la tienes muy básica)

Si ya tienes una tabla subscriptions muy simple, puedes:

Opción A: Renombrarla y usar team_subscriptions (recomendado)

ALTER TABLE subscriptions RENAME TO team_subscriptions_old;
-- Crear team_subscriptions nueva
-- Migrar datos manualmente

Opción B: Extenderla con los campos necesarios

ALTER TABLE subscriptions ADD COLUMN whatsapp_packs INT DEFAULT 0;
ALTER TABLE subscriptions ADD COLUMN whatsapp_packs_cost INT DEFAULT 0;
ALTER TABLE subscriptions ADD COLUMN extra_professionals INT DEFAULT 0;
ALTER TABLE subscriptions ADD COLUMN total_monthly_cost INT;
-- etc...

---

📊 VERSIÓN DBML PARA dbdiagram.io

Agrega esto al final de tu schema actual:

// ========================================
// GRUPO 6: SISTEMA DE PLANES
// ========================================
 
Table subscription_plans {
  id bigserial [pk, increment]
  name varchar(50) [unique, not null, note: 'individual, starter, pro, business']
  display_name varchar(100) [not null]
  description text
 
  price int [not null, note: 'Precio base mensual CLP']
  extra_professional_price int [not null, default: 4000]
 
  limits jsonb [not null, default: '{}', note: 'workers, services, locations, emails, whatsapp_messages']
  features jsonb [not null, default: '[]', note: 'Array de features permitidas']
 
  sort_order int [default: 0]
  is_active boolean [default: true]
  is_visible_marketplace boolean [default: true]
 
  created_at timestamp [default: `now()`]
  updated_at timestamp [default: `now()`]
 
  Indexes {
    name
    is_active
    sort_order
  }
 
  Note: 'Catálogo de planes disponibles'
}
 
Table team_subscriptions {
  id bigserial [pk, increment]
  team_id bigint [not null, ref: > teams.id]
  subscription_plan_id bigint [not null, ref: > subscription_plans.id]
 
  status varchar(50) [default: 'active', note: 'active, cancelled, suspended, past_due, trialing']
 
  base_price int [not null]
  extra_professionals int [default: 0]
  extra_professionals_cost int [default: 0]
 
  whatsapp_packs int [default: 0, note: 'Cantidad de packs WhatsApp']
  whatsapp_packs_cost int [default: 0]
 
  total_monthly_cost int [not null]
 
  current_period_start timestamp [not null]
  current_period_end timestamp [not null]
 
  trial_ends_at timestamp
  trial_days int [default: 14]
 
  cancelled_at timestamp
  cancel_reason text
  ends_at timestamp
 
  auto_renew boolean [default: true]
  metadata jsonb [default: '{}']
 
  created_at timestamp [default: `now()`]
  updated_at timestamp [default: `now()`]
 
  Indexes {
    team_id
    subscription_plan_id
    status
    trial_ends_at
    current_period_end
  }
 
  Note: 'Suscripción activa del team - RLS enabled'
}
 
Table subscription_usage {
  id bigserial [pk, increment]
  team_subscription_id bigint [not null, ref: > team_subscriptions.id]
  team_id bigint [not null, ref: > teams.id]
 
  period_start date [not null]
  period_end date [not null]
 
  emails_sent int [default: 0]
  whatsapp_messages_sent int [default: 0]
  storage_used_mb int [default: 0]
  appointments_created int [default: 0]
  active_workers int [default: 0]
 
  usage_data jsonb [default: '{}']
 
  created_at timestamp [default: `now()`]
  updated_at timestamp [default: `now()`]
 
  Indexes {
    team_id
    (period_start, period_end)
    team_subscription_id
    (team_id, period_start, period_end) [unique]
  }
 
  Note: 'Tracking de uso mensual - RLS enabled'
}
 
Table subscription_invoices {
  id bigserial [pk, increment]
  team_subscription_id bigint [not null, ref: > team_subscriptions.id]
  team_id bigint [not null, ref: > teams.id]
 
  invoice_number varchar(50) [unique, not null]
 
  subtotal int [not null]
  tax_amount int [default: 0]
  total_amount int [not null]
 
  line_items jsonb [not null, default: '[]', note: 'Detalle de cobros']
 
  period_start date [not null]
  period_end date [not null]
 
  status varchar(50) [default: 'draft', note: 'draft, issued, paid, overdue, void']
 
  issued_at timestamp
  due_at timestamp
  paid_at timestamp
 
  payment_method varchar(50)
  payment_reference varchar(255)
 
  metadata jsonb [default: '{}']
 
  created_at timestamp [default: `now()`]
  updated_at timestamp [default: `now()`]
 
  Indexes {
    team_id
    team_subscription_id
    invoice_number
    status
    due_at
  }
 
  Note: 'Historial de facturación - RLS enabled'
}
 
Table subscription_changes_log {
  id bigserial [pk, increment]
  team_subscription_id bigint [not null, ref: > team_subscriptions.id]
  team_id bigint [not null, ref: > teams.id]
 
  change_type varchar(50) [not null, note: 'plan_upgrade, plan_downgrade, addon_purchase, cancellation']
 
  old_plan_id bigint [ref: > subscription_plans.id]
  new_plan_id bigint [ref: > subscription_plans.id]
 
  old_values jsonb [default: '{}']
  new_values jsonb [default: '{}']
 
  reason text
  changed_by bigint [ref: > users.id]
 
  price_difference int
  effective_from timestamp
 
  created_at timestamp [default: `now()`]
 
  Indexes {
    team_id
    team_subscription_id
    change_type
    created_at [note: 'DESC']
  }
 
  Note: 'Auditoría de cambios de plan - RLS enabled'
}

---

🎯 RESUMEN DE CAMBIOS

Schema completo pasaría de:

• 27 tablas (tu MVP actual)

A:

• 32 tablas (MVP + Sistema de Planes completo)

5 tablas nuevas:

1. subscription_plans - Catálogo de planes
2. team_subscriptions - Suscripción activa
3. subscription_usage - Tracking de uso
4. subscription_invoices - Facturación
5. subscription_changes_log - Auditoría

Plus: 3 campos nuevos en teams para cache/performance

---

✅ LO QUE YA TIENES (no necesitas cambiar)

Tu schema actual YA cubre estas necesidades de la guía:

• ✅ users - Para el sistema de permisos
• ✅ teams - Team principal
• ✅ user_teams - Relación con roles
• ✅ services - Con límites por plan
• ✅ appointments - Core del negocio
• ✅ transactions - Para pagos de suscripciones
• ✅ notifications - Para avisos de renovación
• ✅ activity_logs - Para auditoría

---

💡 RECOMENDACIÓN FINAL

Para el MVP (primeros 3 meses):

Agrega solo 2 tablas mínimas:

1. subscription_plans (catálogo)
2. team_subscriptions (suscripción activa)

Esto te permite:

• ✅ Implementar el sistema de permisos (PlanPermissions.php)
• ✅ Validar límites (servicios, workers)
• ✅ Cobrar profesionales adicionales
• ✅ Tracking básico de uso (en teams)

Para Fase 2 (mes 4-6):

Agrega las otras 3 tablas: 3. subscription_usage (tracking detallado) 4. subscription_invoices (facturación formal) 5. subscription_changes_log (auditoría)

---

🚀 PRIORIDAD DE IMPLEMENTACIÓN

FASE MVP (ahora):
├── subscription_plans ⭐⭐⭐⭐⭐ CRÍTICO
├── team_subscriptions ⭐⭐⭐⭐⭐ CRÍTICO
└── campos tracking en teams ⭐⭐⭐⭐ MUY IMPORTANTE

FASE 2 (después):
├── subscription_usage ⭐⭐⭐ IMPORTANTE
├── subscription_invoices ⭐⭐⭐ IMPORTANTE
└── subscription_changes_log ⭐⭐ ÚTIL (auditoría)

---

¿Quieres que genere el SQL completo de creación de estas 5 tablas listo para ejecutar?