35 KiB
35 KiB
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
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.
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.
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.
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.
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.
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.
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).
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.
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
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
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
-- 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
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
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
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
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)
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
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)
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
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
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 |