# DDL Specification: core_tenants Schema ## Identificacion | Campo | Valor | |-------|-------| | **Schema** | core_tenants | | **Modulo** | MGN-004 Tenants | | **Version** | 1.0 | | **Fecha** | 2025-12-05 | | **Estado** | Ready | --- ## Diagrama ER ```mermaid erDiagram tenants ||--o| tenant_settings : has tenants ||--o{ subscriptions : has tenants ||--o{ tenant_modules : has tenants ||--o{ invoices : has tenants ||--o{ tenants : has_children plans ||--o{ subscriptions : used_by plans ||--o{ plan_modules : includes modules ||--o{ plan_modules : included_in modules ||--o{ tenant_modules : activated_for tenants { uuid id PK uuid parent_tenant_id FK string name string slug UK string status timestamp trial_ends_at timestamp created_at timestamp updated_at timestamp deleted_at uuid deleted_by FK } tenant_settings { uuid id PK uuid tenant_id FK UK jsonb company jsonb branding jsonb regional jsonb operational jsonb security timestamp updated_at uuid updated_by FK } plans { uuid id PK string name string slug UK string pricing_model decimal base_price int included_seats decimal per_seat_price int max_seats string currency string interval jsonb limits jsonb features boolean is_public boolean is_active int sort_order } subscriptions { uuid id PK uuid tenant_id FK uuid plan_id FK string status timestamp current_period_start timestamp current_period_end boolean cancel_at_period_end timestamp trial_end int quantity decimal unit_amount string external_subscription_id string payment_method_id timestamp created_at timestamp canceled_at } modules { uuid id PK string code UK string name string description boolean is_core boolean is_active } plan_modules { uuid id PK uuid plan_id FK uuid module_id FK } tenant_modules { uuid id PK uuid tenant_id FK uuid module_id FK boolean is_active timestamp activated_at uuid activated_by FK } invoices { uuid id PK uuid tenant_id FK uuid subscription_id FK string invoice_number UK decimal amount string currency string status timestamp due_date timestamp paid_at jsonb line_items timestamp created_at } ``` --- ## Tablas ### 1. tenants Tabla principal de tenants/organizaciones. ```sql CREATE TABLE core_tenants.tenants ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), parent_tenant_id UUID REFERENCES core_tenants.tenants(id), -- Holdings/Sucursales (Odoo: res.company.parent_id) name VARCHAR(100) NOT NULL, slug VARCHAR(50) NOT NULL UNIQUE, status VARCHAR(20) NOT NULL DEFAULT 'trial', tenant_type VARCHAR(20) NOT NULL DEFAULT 'standalone', -- standalone, holding, subsidiary trial_ends_at TIMESTAMPTZ, domain VARCHAR(100), logo_url VARCHAR(500), created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, deleted_at TIMESTAMPTZ, deleted_by UUID, CONSTRAINT chk_tenants_status CHECK ( status IN ('trial', 'active', 'suspended', 'pending_deletion', 'deleted', 'trial_expired') ), CONSTRAINT chk_tenants_type CHECK (tenant_type IN ('standalone', 'holding', 'subsidiary')), CONSTRAINT chk_tenants_slug CHECK (slug ~ '^[a-z0-9][a-z0-9-]*[a-z0-9]$'), CONSTRAINT chk_tenants_slug_length CHECK (char_length(slug) >= 3), CONSTRAINT chk_tenants_hierarchy CHECK ( -- Un holding no puede tener parent (tenant_type = 'holding' AND parent_tenant_id IS NULL) OR -- Una subsidiary debe tener parent (tenant_type = 'subsidiary' AND parent_tenant_id IS NOT NULL) OR -- Standalone puede o no tener parent (tenant_type = 'standalone') ) ); -- Indices CREATE INDEX idx_tenants_status ON core_tenants.tenants(status) WHERE deleted_at IS NULL; CREATE INDEX idx_tenants_slug ON core_tenants.tenants(slug) WHERE deleted_at IS NULL; CREATE INDEX idx_tenants_parent ON core_tenants.tenants(parent_tenant_id) WHERE parent_tenant_id IS NOT NULL; CREATE INDEX idx_tenants_type ON core_tenants.tenants(tenant_type) WHERE deleted_at IS NULL; CREATE INDEX idx_tenants_created_at ON core_tenants.tenants(created_at DESC); CREATE INDEX idx_tenants_trial_ends ON core_tenants.tenants(trial_ends_at) WHERE status = 'trial'; -- Trigger para updated_at CREATE TRIGGER trg_tenants_updated_at BEFORE UPDATE ON core_tenants.tenants FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); -- Comentarios COMMENT ON TABLE core_tenants.tenants IS 'Organizaciones/empresas en el sistema multi-tenant'; COMMENT ON COLUMN core_tenants.tenants.parent_tenant_id IS 'Tenant padre para holdings/sucursales (Ref: Odoo res.company.parent_id)'; COMMENT ON COLUMN core_tenants.tenants.slug IS 'Identificador URL-friendly unico'; COMMENT ON COLUMN core_tenants.tenants.status IS 'trial, active, suspended, pending_deletion, deleted, trial_expired'; COMMENT ON COLUMN core_tenants.tenants.tenant_type IS 'standalone: independiente, holding: matriz, subsidiary: sucursal'; ``` --- ### 2. tenant_settings Configuracion personalizada de cada tenant. ```sql CREATE TABLE core_tenants.tenant_settings ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), tenant_id UUID NOT NULL UNIQUE REFERENCES core_tenants.tenants(id) ON DELETE CASCADE, -- Configuracion de empresa company JSONB NOT NULL DEFAULT '{}'::jsonb, -- Ejemplo: {"companyName": "...", "taxId": "...", "address": {...}} -- Branding branding JSONB NOT NULL DEFAULT '{}'::jsonb, -- Ejemplo: {"logo": "...", "primaryColor": "#3B82F6", ...} -- Configuracion regional regional JSONB NOT NULL DEFAULT '{}'::jsonb, -- Ejemplo: {"language": "es", "timezone": "America/Mexico_City", ...} -- Configuracion operativa operational JSONB NOT NULL DEFAULT '{}'::jsonb, -- Ejemplo: {"fiscalYearStart": "01-01", "workingDays": [1,2,3,4,5], ...} -- Configuracion de seguridad security JSONB NOT NULL DEFAULT '{}'::jsonb, -- Ejemplo: {"passwordMinLength": 8, "sessionTimeout": 30, ...} updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_by UUID ); -- Indices para busquedas en JSONB CREATE INDEX idx_tenant_settings_company_name ON core_tenants.tenant_settings USING gin ((company -> 'companyName')); -- Trigger para updated_at CREATE TRIGGER trg_tenant_settings_updated_at BEFORE UPDATE ON core_tenants.tenant_settings FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); -- Comentarios COMMENT ON TABLE core_tenants.tenant_settings IS 'Configuracion personalizada de cada tenant'; COMMENT ON COLUMN core_tenants.tenant_settings.company IS 'Informacion legal y de contacto'; COMMENT ON COLUMN core_tenants.tenant_settings.branding IS 'Personalizacion visual'; COMMENT ON COLUMN core_tenants.tenant_settings.regional IS 'Idioma, timezone, formatos'; COMMENT ON COLUMN core_tenants.tenant_settings.security IS 'Politicas de seguridad'; ``` --- ### 3. plans Planes de subscripcion disponibles. ```sql CREATE TABLE core_tenants.plans ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), name VARCHAR(50) NOT NULL, slug VARCHAR(50) NOT NULL UNIQUE, description VARCHAR(500), -- Pricing Model (MGN-015-007) pricing_model VARCHAR(20) NOT NULL DEFAULT 'flat', base_price DECIMAL(10,2) NOT NULL, -- Precio base del plan included_seats INT NOT NULL DEFAULT 1, -- Usuarios incluidos en base_price per_seat_price DECIMAL(10,2) DEFAULT 0, -- Precio por usuario adicional max_seats INT, -- Maximo de usuarios (NULL = ilimitado) currency VARCHAR(3) NOT NULL DEFAULT 'USD', interval VARCHAR(20) NOT NULL DEFAULT 'monthly', -- Limites del plan limits JSONB NOT NULL DEFAULT '{}'::jsonb, -- Ejemplo: {"maxStorageBytes": 26843545600, "maxApiCalls": 50000} -- Features del plan (MGN-015, MGN-016, MGN-017, MGN-018) features JSONB NOT NULL DEFAULT '{}'::jsonb, -- Ejemplo completo: -- { -- "priority_support": true, -- "white_label": false, -- "api_access": true, -- "mercadopago_enabled": true, -- "clip_enabled": true, -- "stripe_enabled": false, -- "whatsapp_enabled": true, -- "whatsapp_chatbot": true, -- "whatsapp_marketing": false, -- "whatsapp_max_accounts": 1, -- "ai_agents_enabled": true, -- "ai_max_agents": 3, -- "ai_kb_max_documents": 100, -- "ai_monthly_token_limit": 500000 -- } is_public BOOLEAN NOT NULL DEFAULT true, is_active BOOLEAN NOT NULL DEFAULT true, sort_order INT NOT NULL DEFAULT 0, created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT chk_plans_pricing_model CHECK (pricing_model IN ('flat', 'per_seat', 'tiered')), CONSTRAINT chk_plans_interval CHECK (interval IN ('monthly', 'yearly', 'lifetime')), CONSTRAINT chk_plans_base_price CHECK (base_price >= 0), CONSTRAINT chk_plans_per_seat_price CHECK (per_seat_price >= 0), CONSTRAINT chk_plans_seats CHECK (included_seats >= 1), CONSTRAINT chk_plans_max_seats CHECK (max_seats IS NULL OR max_seats >= included_seats) ); -- Indices CREATE INDEX idx_plans_public_active ON core_tenants.plans(is_public, is_active, sort_order); CREATE INDEX idx_plans_pricing ON core_tenants.plans(pricing_model); -- Trigger para updated_at CREATE TRIGGER trg_plans_updated_at BEFORE UPDATE ON core_tenants.plans FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); -- Comentarios COMMENT ON TABLE core_tenants.plans IS 'Planes de subscripcion disponibles'; COMMENT ON COLUMN core_tenants.plans.pricing_model IS 'flat: precio fijo, per_seat: por usuario, tiered: escalonado'; COMMENT ON COLUMN core_tenants.plans.base_price IS 'Precio base que incluye included_seats usuarios'; COMMENT ON COLUMN core_tenants.plans.included_seats IS 'Usuarios incluidos en el precio base'; COMMENT ON COLUMN core_tenants.plans.per_seat_price IS 'Precio por cada usuario adicional sobre included_seats'; COMMENT ON COLUMN core_tenants.plans.limits IS 'Limites: maxStorageBytes, maxApiCalls (maxUsers ya no se usa, usar max_seats)'; COMMENT ON COLUMN core_tenants.plans.features IS 'Features: integraciones, canales, AI, etc.'; ``` --- ### 4. subscriptions Subscripciones de tenants a planes. ```sql CREATE TABLE core_tenants.subscriptions ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), tenant_id UUID NOT NULL REFERENCES core_tenants.tenants(id) ON DELETE CASCADE, plan_id UUID NOT NULL REFERENCES core_tenants.plans(id), status VARCHAR(20) NOT NULL DEFAULT 'active', current_period_start TIMESTAMPTZ NOT NULL, current_period_end TIMESTAMPTZ NOT NULL, cancel_at_period_end BOOLEAN NOT NULL DEFAULT false, trial_end TIMESTAMPTZ, -- Per-Seat Billing (MGN-015-007) quantity INT NOT NULL DEFAULT 1, -- Numero de asientos/usuarios contratados unit_amount DECIMAL(10,2), -- Precio unitario al momento de contratar -- Integracion con payment gateway external_subscription_id VARCHAR(100), payment_method_id VARCHAR(100), created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, canceled_at TIMESTAMPTZ, CONSTRAINT chk_subscriptions_status CHECK ( status IN ('trialing', 'active', 'past_due', 'canceled', 'unpaid', 'incomplete') ), CONSTRAINT chk_subscriptions_period CHECK (current_period_end > current_period_start), CONSTRAINT chk_subscriptions_quantity CHECK (quantity >= 1) ); -- Indices CREATE INDEX idx_subscriptions_tenant ON core_tenants.subscriptions(tenant_id); CREATE INDEX idx_subscriptions_status ON core_tenants.subscriptions(status); CREATE INDEX idx_subscriptions_period_end ON core_tenants.subscriptions(current_period_end) WHERE status = 'active'; CREATE INDEX idx_subscriptions_external ON core_tenants.subscriptions(external_subscription_id); -- Trigger para updated_at CREATE TRIGGER trg_subscriptions_updated_at BEFORE UPDATE ON core_tenants.subscriptions FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); -- Comentarios COMMENT ON TABLE core_tenants.subscriptions IS 'Subscripciones activas y pasadas de tenants'; COMMENT ON COLUMN core_tenants.subscriptions.external_subscription_id IS 'ID en Stripe/PayPal'; COMMENT ON COLUMN core_tenants.subscriptions.quantity IS 'Numero de asientos contratados (usuarios pagados)'; COMMENT ON COLUMN core_tenants.subscriptions.unit_amount IS 'Precio por asiento al momento de la subscripcion'; ``` --- ### 5. modules Catalogo de modulos del sistema. ```sql CREATE TABLE core_tenants.modules ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), code VARCHAR(50) NOT NULL UNIQUE, name VARCHAR(100) NOT NULL, description VARCHAR(500), is_core BOOLEAN NOT NULL DEFAULT false, is_active BOOLEAN NOT NULL DEFAULT true, sort_order INT NOT NULL DEFAULT 0, created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP ); -- Comentarios COMMENT ON TABLE core_tenants.modules IS 'Modulos disponibles en el sistema'; COMMENT ON COLUMN core_tenants.modules.is_core IS 'Modulos core siempre incluidos'; ``` --- ### 6. plan_modules Modulos incluidos en cada plan. ```sql CREATE TABLE core_tenants.plan_modules ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), plan_id UUID NOT NULL REFERENCES core_tenants.plans(id) ON DELETE CASCADE, module_id UUID NOT NULL REFERENCES core_tenants.modules(id) ON DELETE CASCADE, CONSTRAINT uq_plan_modules UNIQUE (plan_id, module_id) ); -- Indices CREATE INDEX idx_plan_modules_plan ON core_tenants.plan_modules(plan_id); CREATE INDEX idx_plan_modules_module ON core_tenants.plan_modules(module_id); ``` --- ### 7. tenant_modules Modulos activos para cada tenant (puede exceder plan con addons). ```sql CREATE TABLE core_tenants.tenant_modules ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), tenant_id UUID NOT NULL REFERENCES core_tenants.tenants(id) ON DELETE CASCADE, module_id UUID NOT NULL REFERENCES core_tenants.modules(id), is_active BOOLEAN NOT NULL DEFAULT true, activated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, activated_by UUID, CONSTRAINT uq_tenant_modules UNIQUE (tenant_id, module_id) ); -- Indices CREATE INDEX idx_tenant_modules_tenant ON core_tenants.tenant_modules(tenant_id); CREATE INDEX idx_tenant_modules_active ON core_tenants.tenant_modules(tenant_id, is_active) WHERE is_active = true; ``` --- ### 8. invoices Historial de facturacion. ```sql CREATE TABLE core_tenants.invoices ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), tenant_id UUID NOT NULL REFERENCES core_tenants.tenants(id), subscription_id UUID REFERENCES core_tenants.subscriptions(id), invoice_number VARCHAR(50) NOT NULL UNIQUE, amount DECIMAL(10,2) NOT NULL, currency VARCHAR(3) NOT NULL DEFAULT 'USD', status VARCHAR(20) NOT NULL DEFAULT 'draft', due_date TIMESTAMPTZ, paid_at TIMESTAMPTZ, -- Detalle de items line_items JSONB NOT NULL DEFAULT '[]'::jsonb, -- Ejemplo: [{"description": "Professional Plan", "amount": 99.00, "quantity": 1}] -- Integracion con payment gateway external_invoice_id VARCHAR(100), external_payment_id VARCHAR(100), created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT chk_invoices_status CHECK ( status IN ('draft', 'open', 'paid', 'void', 'uncollectible') ), CONSTRAINT chk_invoices_amount CHECK (amount >= 0) ); -- Indices CREATE INDEX idx_invoices_tenant ON core_tenants.invoices(tenant_id); CREATE INDEX idx_invoices_status ON core_tenants.invoices(status); CREATE INDEX idx_invoices_due_date ON core_tenants.invoices(due_date) WHERE status = 'open'; CREATE INDEX idx_invoices_number ON core_tenants.invoices(invoice_number); -- Comentarios COMMENT ON TABLE core_tenants.invoices IS 'Historial de facturacion'; ``` --- ## Data Seed: Modulos del Sistema ```sql INSERT INTO core_tenants.modules (code, name, description, is_core, sort_order) VALUES ('auth', 'Autenticacion', 'Login, JWT, sesiones', true, 10), ('users', 'Usuarios', 'Gestion de usuarios', true, 20), ('roles', 'Roles y Permisos', 'RBAC, control de acceso', true, 30), ('tenants', 'Multi-Tenancy', 'Configuracion del tenant', true, 40), ('inventory', 'Inventario', 'Gestion de productos y stock', false, 50), ('financial', 'Finanzas', 'Contabilidad basica', false, 60), ('reports', 'Reportes', 'Reportes y dashboards', false, 70), ('crm', 'CRM', 'Gestion de clientes', false, 80), ('api', 'API Access', 'Acceso API REST', false, 90), ('analytics', 'Analytics Avanzado', 'Analisis avanzado de datos', false, 100), ('whitelabel', 'White Label', 'Personalizacion completa', false, 110); ``` --- ## Data Seed: Planes de Subscripcion ```sql INSERT INTO core_tenants.plans ( name, slug, description, pricing_model, base_price, included_seats, per_seat_price, max_seats, currency, interval, limits, features, sort_order ) VALUES ( 'Trial', 'trial', 'Prueba gratuita por 14 dias', 'flat', 0.00, 3, -- 3 usuarios incluidos 0.00, -- Sin costo adicional 5, -- Maximo 5 usuarios 'USD', 'monthly', '{"maxStorageBytes": 1073741824, "maxApiCalls": 1000}'::jsonb, '{ "email_support": false, "priority_support": false, "api_access": false, "white_label": false, "mercadopago_enabled": false, "clip_enabled": false, "stripe_enabled": false, "whatsapp_enabled": false, "whatsapp_chatbot": false, "whatsapp_marketing": false, "whatsapp_max_accounts": 0, "ai_agents_enabled": false, "ai_max_agents": 0, "ai_kb_max_documents": 0, "ai_monthly_token_limit": 0 }'::jsonb, 10 ), ( 'Starter', 'starter', 'Para equipos pequenos', 'per_seat', 29.00, -- Precio base 3, -- 3 usuarios incluidos 9.00, -- $9 por usuario adicional 15, -- Maximo 15 usuarios 'USD', 'monthly', '{"maxStorageBytes": 5368709120, "maxApiCalls": 10000}'::jsonb, '{ "email_support": true, "priority_support": false, "api_access": false, "white_label": false, "mercadopago_enabled": true, "clip_enabled": true, "stripe_enabled": false, "whatsapp_enabled": true, "whatsapp_chatbot": false, "whatsapp_marketing": false, "whatsapp_max_accounts": 1, "ai_agents_enabled": false, "ai_max_agents": 0, "ai_kb_max_documents": 0, "ai_monthly_token_limit": 0 }'::jsonb, 20 ), ( 'Professional', 'professional', 'Para empresas en crecimiento', 'per_seat', 99.00, -- Precio base 5, -- 5 usuarios incluidos 15.00, -- $15 por usuario adicional 100, -- Maximo 100 usuarios 'USD', 'monthly', '{"maxStorageBytes": 26843545600, "maxApiCalls": 50000}'::jsonb, '{ "email_support": true, "priority_support": true, "api_access": true, "white_label": false, "mercadopago_enabled": true, "clip_enabled": true, "stripe_enabled": true, "whatsapp_enabled": true, "whatsapp_chatbot": true, "whatsapp_marketing": true, "whatsapp_max_accounts": 3, "ai_agents_enabled": true, "ai_max_agents": 3, "ai_kb_max_documents": 100, "ai_monthly_token_limit": 500000 }'::jsonb, 30 ), ( 'Enterprise', 'enterprise', 'Para grandes organizaciones', 'per_seat', 299.00, -- Precio base 10, -- 10 usuarios incluidos 25.00, -- $25 por usuario adicional NULL, -- Sin limite de usuarios 'USD', 'monthly', '{"maxStorageBytes": 107374182400, "maxApiCalls": 500000}'::jsonb, '{ "email_support": true, "priority_support": true, "api_access": true, "white_label": true, "advanced_analytics": true, "dedicated_support": true, "mercadopago_enabled": true, "clip_enabled": true, "stripe_enabled": true, "whatsapp_enabled": true, "whatsapp_chatbot": true, "whatsapp_marketing": true, "whatsapp_max_accounts": 10, "ai_agents_enabled": true, "ai_max_agents": -1, "ai_kb_max_documents": 500, "ai_custom_tools": true, "ai_monthly_token_limit": 5000000 }'::jsonb, 40 ); -- Asignar modulos a planes -- Trial: solo core INSERT INTO core_tenants.plan_modules (plan_id, module_id) SELECT p.id, m.id FROM core_tenants.plans p, core_tenants.modules m WHERE p.slug = 'trial' AND m.is_core = true; -- Starter: core + inventory + financial INSERT INTO core_tenants.plan_modules (plan_id, module_id) SELECT p.id, m.id FROM core_tenants.plans p, core_tenants.modules m WHERE p.slug = 'starter' AND (m.is_core = true OR m.code IN ('inventory', 'financial')); -- Professional: core + inventory + financial + reports + crm + api INSERT INTO core_tenants.plan_modules (plan_id, module_id) SELECT p.id, m.id FROM core_tenants.plans p, core_tenants.modules m WHERE p.slug = 'professional' AND (m.is_core = true OR m.code IN ('inventory', 'financial', 'reports', 'crm', 'api')); -- Enterprise: todos INSERT INTO core_tenants.plan_modules (plan_id, module_id) SELECT p.id, m.id FROM core_tenants.plans p, core_tenants.modules m WHERE p.slug = 'enterprise'; ``` --- ## Data Seed: Configuracion Default ```sql -- Configuracion default de plataforma (para herencia) INSERT INTO core_tenants.tenant_settings (tenant_id, company, branding, regional, operational, security) VALUES ( '00000000-0000-0000-0000-000000000000', -- Tenant placeholder para defaults '{}'::jsonb, '{"primaryColor": "#3B82F6", "secondaryColor": "#10B981", "accentColor": "#F59E0B"}'::jsonb, '{"defaultLanguage": "es", "defaultTimezone": "America/Mexico_City", "dateFormat": "DD/MM/YYYY", "timeFormat": "24h", "currency": "MXN"}'::jsonb, '{"fiscalYearStart": "01-01", "workingDays": [1,2,3,4,5], "businessHoursStart": "09:00", "businessHoursEnd": "18:00"}'::jsonb, '{"passwordMinLength": 8, "passwordRequireSpecial": true, "sessionTimeout": 30, "maxLoginAttempts": 5, "lockoutDuration": 15, "mfaRequired": false}'::jsonb ); ``` --- ## Funciones de Utilidad ### Crear Tenant Completo ```sql CREATE OR REPLACE FUNCTION core_tenants.create_tenant( p_name VARCHAR, p_slug VARCHAR, p_admin_email VARCHAR, p_admin_name VARCHAR, p_plan_slug VARCHAR DEFAULT 'trial', p_trial_days INT DEFAULT 14 ) RETURNS UUID AS $$ DECLARE v_tenant_id UUID; v_plan_id UUID; v_admin_user_id UUID; v_admin_role_id UUID; BEGIN -- Crear tenant INSERT INTO core_tenants.tenants (name, slug, status, trial_ends_at) VALUES ( p_name, p_slug, 'trial', CURRENT_TIMESTAMP + (p_trial_days || ' days')::interval ) RETURNING id INTO v_tenant_id; -- Crear settings vacios (heredara defaults) INSERT INTO core_tenants.tenant_settings (tenant_id) VALUES (v_tenant_id); -- Obtener plan SELECT id INTO v_plan_id FROM core_tenants.plans WHERE slug = p_plan_slug; -- Crear subscripcion INSERT INTO core_tenants.subscriptions ( tenant_id, plan_id, status, current_period_start, current_period_end, trial_end ) VALUES ( v_tenant_id, v_plan_id, 'trialing', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP + (p_trial_days || ' days')::interval, CURRENT_TIMESTAMP + (p_trial_days || ' days')::interval ); -- Activar modulos del plan INSERT INTO core_tenants.tenant_modules (tenant_id, module_id) SELECT v_tenant_id, pm.module_id FROM core_tenants.plan_modules pm WHERE pm.plan_id = v_plan_id; -- Crear roles built-in para el tenant PERFORM core_rbac.create_builtin_roles(v_tenant_id); -- Crear usuario admin -- (Esto se hace en la capa de aplicacion para enviar email) RETURN v_tenant_id; END; $$ LANGUAGE plpgsql; ``` ### Verificar Limite de Tenant ```sql CREATE OR REPLACE FUNCTION core_tenants.check_tenant_limit( p_tenant_id UUID, p_limit_type VARCHAR ) RETURNS TABLE ( current_value BIGINT, max_value BIGINT, can_add BOOLEAN, percentage INT ) AS $$ DECLARE v_limits JSONB; v_current BIGINT; v_max BIGINT; BEGIN -- Obtener limites del plan actual SELECT pl.limits INTO v_limits FROM core_tenants.subscriptions s JOIN core_tenants.plans pl ON pl.id = s.plan_id WHERE s.tenant_id = p_tenant_id AND s.status IN ('active', 'trialing') ORDER BY s.created_at DESC LIMIT 1; -- Obtener valor actual segun tipo CASE p_limit_type WHEN 'users' THEN SELECT COUNT(*) INTO v_current FROM core_users.users WHERE tenant_id = p_tenant_id AND deleted_at IS NULL; v_max := (v_limits->>'maxUsers')::BIGINT; WHEN 'storage' THEN -- Calcular storage usado (simplificado) v_current := 0; -- Implementar calculo real v_max := (v_limits->>'maxStorageBytes')::BIGINT; WHEN 'api_calls' THEN -- Calcular API calls del mes (simplificado) v_current := 0; -- Implementar calculo real v_max := (v_limits->>'maxApiCalls')::BIGINT; END CASE; -- -1 significa ilimitado IF v_max = -1 THEN RETURN QUERY SELECT v_current, v_max, true, 0; ELSE RETURN QUERY SELECT v_current, v_max, v_current < v_max, CASE WHEN v_max > 0 THEN (v_current * 100 / v_max)::INT ELSE 0 END; END IF; END; $$ LANGUAGE plpgsql STABLE; ``` ### Obtener Subsidiarias de un Holding ```sql CREATE OR REPLACE FUNCTION core_tenants.get_subsidiaries( p_tenant_id UUID, p_include_self BOOLEAN DEFAULT false ) RETURNS TABLE ( tenant_id UUID, tenant_name VARCHAR, tenant_slug VARCHAR, tenant_type VARCHAR, level INT ) AS $$ WITH RECURSIVE tenant_tree AS ( -- Base: el tenant solicitado SELECT t.id, t.name, t.slug, t.tenant_type::VARCHAR, 0 AS level FROM core_tenants.tenants t WHERE t.id = p_tenant_id AND t.deleted_at IS NULL UNION ALL -- Recursivo: subsidiarias SELECT child.id, child.name, child.slug, child.tenant_type::VARCHAR, tt.level + 1 FROM core_tenants.tenants child JOIN tenant_tree tt ON child.parent_tenant_id = tt.id WHERE child.deleted_at IS NULL ) SELECT id, name, slug, tenant_type, level FROM tenant_tree WHERE p_include_self = true OR level > 0 ORDER BY level, name; $$ LANGUAGE SQL STABLE; COMMENT ON FUNCTION core_tenants.get_subsidiaries IS 'Obtiene arbol de subsidiarias de un holding (Ref: Odoo res.company.child_ids)'; ``` ### Obtener Holding Raiz ```sql CREATE OR REPLACE FUNCTION core_tenants.get_root_holding( p_tenant_id UUID ) RETURNS UUID AS $$ WITH RECURSIVE tenant_tree AS ( SELECT id, parent_tenant_id FROM core_tenants.tenants WHERE id = p_tenant_id AND deleted_at IS NULL UNION ALL SELECT t.id, t.parent_tenant_id FROM core_tenants.tenants t JOIN tenant_tree tt ON t.id = tt.parent_tenant_id WHERE t.deleted_at IS NULL ) SELECT id FROM tenant_tree WHERE parent_tenant_id IS NULL; $$ LANGUAGE SQL STABLE; COMMENT ON FUNCTION core_tenants.get_root_holding IS 'Obtiene el holding raiz de una subsidiaria'; ``` ### Verificar Feature de Plan (MGN-015/016/017/018) ```sql CREATE OR REPLACE FUNCTION core_tenants.has_feature( p_tenant_id UUID, p_feature_name VARCHAR ) RETURNS BOOLEAN AS $$ DECLARE v_features JSONB; v_result BOOLEAN; BEGIN -- Obtener features del plan activo SELECT pl.features INTO v_features FROM core_tenants.subscriptions s JOIN core_tenants.plans pl ON pl.id = s.plan_id WHERE s.tenant_id = p_tenant_id AND s.status IN ('active', 'trialing') ORDER BY s.created_at DESC LIMIT 1; IF v_features IS NULL THEN RETURN false; END IF; -- Verificar si el feature existe y es true v_result := COALESCE((v_features->>p_feature_name)::BOOLEAN, false); RETURN v_result; EXCEPTION WHEN OTHERS THEN -- Si el valor no es booleano, verificar si existe RETURN v_features ? p_feature_name; END; $$ LANGUAGE plpgsql STABLE; COMMENT ON FUNCTION core_tenants.has_feature IS 'Verifica si un tenant tiene habilitado un feature (usado por MGN-015/016/017/018)'; ``` ### Obtener Limite de Feature ```sql CREATE OR REPLACE FUNCTION core_tenants.get_feature_limit( p_tenant_id UUID, p_feature_name VARCHAR ) RETURNS INT AS $$ DECLARE v_features JSONB; v_value INT; BEGIN -- Obtener features del plan activo SELECT pl.features INTO v_features FROM core_tenants.subscriptions s JOIN core_tenants.plans pl ON pl.id = s.plan_id WHERE s.tenant_id = p_tenant_id AND s.status IN ('active', 'trialing') ORDER BY s.created_at DESC LIMIT 1; IF v_features IS NULL THEN RETURN 0; END IF; -- Obtener valor numerico v_value := COALESCE((v_features->>p_feature_name)::INT, 0); RETURN v_value; EXCEPTION WHEN OTHERS THEN RETURN 0; END; $$ LANGUAGE plpgsql STABLE; COMMENT ON FUNCTION core_tenants.get_feature_limit IS 'Obtiene limite numerico de un feature (ej: ai_max_agents, whatsapp_max_accounts)'; ``` ### Calcular Precio de Subscripcion (Per-Seat) ```sql CREATE OR REPLACE FUNCTION core_tenants.calculate_subscription_price( p_plan_id UUID, p_quantity INT ) RETURNS TABLE ( base_price DECIMAL(10,2), included_seats INT, extra_seats INT, extra_seats_cost DECIMAL(10,2), total_price DECIMAL(10,2) ) AS $$ DECLARE v_plan RECORD; BEGIN SELECT pl.base_price, pl.included_seats, pl.per_seat_price, pl.max_seats, pl.pricing_model INTO v_plan FROM core_tenants.plans pl WHERE pl.id = p_plan_id; IF v_plan IS NULL THEN RETURN; END IF; -- Validar cantidad maxima IF v_plan.max_seats IS NOT NULL AND p_quantity > v_plan.max_seats THEN RAISE EXCEPTION 'Quantity % exceeds max_seats %', p_quantity, v_plan.max_seats; END IF; RETURN QUERY SELECT v_plan.base_price, v_plan.included_seats, GREATEST(0, p_quantity - v_plan.included_seats), GREATEST(0, p_quantity - v_plan.included_seats) * v_plan.per_seat_price, v_plan.base_price + (GREATEST(0, p_quantity - v_plan.included_seats) * v_plan.per_seat_price); END; $$ LANGUAGE plpgsql STABLE; COMMENT ON FUNCTION core_tenants.calculate_subscription_price IS 'Calcula precio total de subscripcion segun cantidad de asientos'; ``` --- ## Vistas ### Vista: Resumen de Tenants ```sql CREATE VIEW core_tenants.vw_tenants_summary AS SELECT t.id, t.parent_tenant_id, t.name, t.slug, t.status, t.tenant_type, t.trial_ends_at, t.created_at, pt.name AS parent_tenant_name, p.name AS plan_name, p.slug AS plan_slug, s.status AS subscription_status, s.current_period_end, (SELECT COUNT(*) FROM core_users.users u WHERE u.tenant_id = t.id AND u.deleted_at IS NULL) AS users_count, (SELECT COUNT(*) FROM core_tenants.tenant_modules tm WHERE tm.tenant_id = t.id AND tm.is_active = true) AS modules_count, (SELECT COUNT(*) FROM core_tenants.tenants child WHERE child.parent_tenant_id = t.id AND child.deleted_at IS NULL) AS subsidiaries_count FROM core_tenants.tenants t LEFT JOIN core_tenants.tenants pt ON pt.id = t.parent_tenant_id LEFT JOIN core_tenants.subscriptions s ON s.tenant_id = t.id AND s.status IN ('active', 'trialing') LEFT JOIN core_tenants.plans p ON p.id = s.plan_id WHERE t.deleted_at IS NULL; ``` ### Vista: Uso de Tenant ```sql CREATE VIEW core_tenants.vw_tenant_usage AS SELECT t.id AS tenant_id, t.name AS tenant_name, -- Per-Seat info (MGN-015-007) pl.pricing_model, pl.included_seats, pl.max_seats, s.quantity AS contracted_seats, (SELECT COUNT(*) FROM core_users.users u WHERE u.tenant_id = t.id AND u.deleted_at IS NULL) AS current_users, -- Calculo de asientos disponibles CASE WHEN pl.max_seats IS NULL THEN NULL -- Ilimitado ELSE pl.max_seats - (SELECT COUNT(*) FROM core_users.users u WHERE u.tenant_id = t.id AND u.deleted_at IS NULL) END AS available_seats, -- Storage y API (pl.limits->>'maxStorageBytes')::BIGINT AS max_storage, 0::BIGINT AS current_storage, -- Implementar calculo real (pl.limits->>'maxApiCalls')::INT AS max_api_calls, 0::INT AS current_api_calls, -- Implementar calculo real -- Features de integraciones COALESCE((pl.features->>'whatsapp_enabled')::BOOLEAN, false) AS whatsapp_enabled, COALESCE((pl.features->>'ai_agents_enabled')::BOOLEAN, false) AS ai_enabled, COALESCE((pl.features->>'ai_monthly_token_limit')::INT, 0) AS ai_token_limit, 0::INT AS ai_tokens_used -- Implementar calculo real FROM core_tenants.tenants t JOIN core_tenants.subscriptions s ON s.tenant_id = t.id AND s.status IN ('active', 'trialing') JOIN core_tenants.plans pl ON pl.id = s.plan_id WHERE t.deleted_at IS NULL; ``` --- ## Resumen de Tablas | Tabla | Columnas | Descripcion | |-------|----------|-------------| | tenants | 13 | Tenants/organizaciones (con parent_tenant_id y tenant_type) | | tenant_settings | 8 | Configuracion JSONB | | plans | 17 | Planes con pricing per-seat y features (MGN-015-007) | | subscriptions | 14 | Subscripciones con quantity per-seat (MGN-015-007) | | modules | 7 | Catalogo de modulos | | plan_modules | 3 | Modulos por plan | | tenant_modules | 6 | Modulos activos por tenant | | invoices | 14 | Historial de facturacion | **Total: 8 tablas, 82 columnas** --- ## Funciones de Utilidad | Funcion | Retorna | Descripcion | |---------|---------|-------------| | create_tenant | UUID | Crea tenant con settings, subscription y modulos | | check_tenant_limit | TABLE | Verifica limites de usuarios, storage, API calls | | get_subsidiaries | TABLE | Arbol de subsidiarias de un holding | | get_root_holding | UUID | Holding raiz de una subsidiaria | | has_feature | BOOLEAN | Verifica si tenant tiene feature habilitado (MGN-015/016/017/018) | | get_feature_limit | INT | Obtiene limite numerico de un feature | | calculate_subscription_price | TABLE | Calcula precio total per-seat | --- ## Historial | Version | Fecha | Autor | Cambios | |---------|-------|-------|---------| | 1.0 | 2025-12-05 | System | Creacion inicial | | 1.1 | 2025-12-05 | System | Agregar parent_tenant_id y tenant_type (Ref: Odoo res.company.parent_id) | | 1.2 | 2025-12-05 | System | Pricing per-seat (MGN-015-007): pricing_model, base_price, included_seats, per_seat_price, max_seats en plans; quantity, unit_amount en subscriptions; features JSONB para MGN-016/017/018 |