rckrdmrd
5043a640e4
- Replace old DDL structure with new numbered files (01-24) - Update migrations and seeds for new schema - Clean up deprecated files Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
272 lines
15 KiB
SQL
272 lines
15 KiB
SQL
-- =============================================================
|
|
-- ARCHIVO: 01-auth-profiles.sql
|
|
-- DESCRIPCION: Perfiles de usuario, herramientas y personas responsables
|
|
-- VERSION: 1.0.0
|
|
-- PROYECTO: ERP-Core V2
|
|
-- FECHA: 2026-01-10
|
|
-- =============================================================
|
|
|
|
-- =====================
|
|
-- SCHEMA: auth (si no existe)
|
|
-- =====================
|
|
CREATE SCHEMA IF NOT EXISTS auth;
|
|
|
|
-- =====================
|
|
-- TABLA: persons
|
|
-- Personas fisicas responsables de cuentas (Persona Fisica/Moral)
|
|
-- =====================
|
|
CREATE TABLE IF NOT EXISTS auth.persons (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
|
|
-- Datos personales
|
|
full_name VARCHAR(200) NOT NULL,
|
|
first_name VARCHAR(100),
|
|
last_name VARCHAR(100),
|
|
maternal_name VARCHAR(100),
|
|
|
|
-- Contacto
|
|
email VARCHAR(255) NOT NULL,
|
|
phone VARCHAR(20),
|
|
mobile_phone VARCHAR(20),
|
|
|
|
-- Identificacion oficial
|
|
identification_type VARCHAR(50), -- INE, pasaporte, cedula_profesional
|
|
identification_number VARCHAR(50),
|
|
identification_expiry DATE,
|
|
|
|
-- Direccion
|
|
address JSONB DEFAULT '{}',
|
|
|
|
-- Metadata
|
|
is_verified BOOLEAN DEFAULT FALSE,
|
|
verified_at TIMESTAMPTZ,
|
|
verified_by UUID,
|
|
is_responsible_for_tenant BOOLEAN DEFAULT FALSE,
|
|
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
deleted_at TIMESTAMPTZ
|
|
);
|
|
|
|
-- Indices para persons
|
|
CREATE INDEX IF NOT EXISTS idx_persons_email ON auth.persons(email);
|
|
CREATE INDEX IF NOT EXISTS idx_persons_identification ON auth.persons(identification_type, identification_number);
|
|
|
|
-- =====================
|
|
-- TABLA: user_profiles
|
|
-- Perfiles de usuario del sistema (ADM, CNT, VNT, etc.)
|
|
-- =====================
|
|
CREATE TABLE IF NOT EXISTS auth.user_profiles (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
code VARCHAR(10) NOT NULL,
|
|
name VARCHAR(100) NOT NULL,
|
|
description TEXT,
|
|
is_system BOOLEAN DEFAULT FALSE,
|
|
color VARCHAR(20),
|
|
icon VARCHAR(50),
|
|
|
|
-- Permisos base
|
|
base_permissions JSONB DEFAULT '[]',
|
|
available_modules TEXT[] DEFAULT '{}',
|
|
|
|
-- Precios y plataformas
|
|
monthly_price DECIMAL(10,2) DEFAULT 0,
|
|
included_platforms TEXT[] DEFAULT '{web}',
|
|
|
|
-- Configuracion de herramientas
|
|
default_tools TEXT[] DEFAULT '{}',
|
|
|
|
-- Feature flags especificos del perfil
|
|
feature_flags JSONB DEFAULT '{}',
|
|
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
created_by UUID REFERENCES auth.users(id),
|
|
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
updated_by UUID REFERENCES auth.users(id),
|
|
deleted_at TIMESTAMPTZ,
|
|
|
|
UNIQUE(tenant_id, code)
|
|
);
|
|
|
|
-- Indices para user_profiles
|
|
CREATE INDEX IF NOT EXISTS idx_user_profiles_tenant ON auth.user_profiles(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_user_profiles_code ON auth.user_profiles(code);
|
|
|
|
-- =====================
|
|
-- TABLA: profile_tools
|
|
-- Herramientas disponibles por perfil
|
|
-- =====================
|
|
CREATE TABLE IF NOT EXISTS auth.profile_tools (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
profile_id UUID NOT NULL REFERENCES auth.user_profiles(id) ON DELETE CASCADE,
|
|
tool_code VARCHAR(50) NOT NULL,
|
|
tool_name VARCHAR(100) NOT NULL,
|
|
description TEXT,
|
|
category VARCHAR(50),
|
|
is_mobile_only BOOLEAN DEFAULT FALSE,
|
|
is_web_only BOOLEAN DEFAULT FALSE,
|
|
icon VARCHAR(50),
|
|
configuration JSONB DEFAULT '{}',
|
|
sort_order INTEGER DEFAULT 0,
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
UNIQUE(profile_id, tool_code)
|
|
);
|
|
|
|
-- Indices para profile_tools
|
|
CREATE INDEX IF NOT EXISTS idx_profile_tools_profile ON auth.profile_tools(profile_id);
|
|
CREATE INDEX IF NOT EXISTS idx_profile_tools_code ON auth.profile_tools(tool_code);
|
|
|
|
-- =====================
|
|
-- TABLA: profile_modules
|
|
-- Modulos accesibles por perfil
|
|
-- =====================
|
|
CREATE TABLE IF NOT EXISTS auth.profile_modules (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
profile_id UUID NOT NULL REFERENCES auth.user_profiles(id) ON DELETE CASCADE,
|
|
module_code VARCHAR(50) NOT NULL,
|
|
access_level VARCHAR(20) NOT NULL DEFAULT 'read', -- read, write, admin
|
|
can_export BOOLEAN DEFAULT FALSE,
|
|
can_print BOOLEAN DEFAULT TRUE,
|
|
|
|
UNIQUE(profile_id, module_code)
|
|
);
|
|
|
|
-- Indices para profile_modules
|
|
CREATE INDEX IF NOT EXISTS idx_profile_modules_profile ON auth.profile_modules(profile_id);
|
|
|
|
-- =====================
|
|
-- TABLA: user_profile_assignments
|
|
-- Asignacion de perfiles a usuarios
|
|
-- =====================
|
|
CREATE TABLE IF NOT EXISTS auth.user_profile_assignments (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
profile_id UUID NOT NULL REFERENCES auth.user_profiles(id) ON DELETE CASCADE,
|
|
is_primary BOOLEAN DEFAULT FALSE,
|
|
assigned_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
assigned_by UUID REFERENCES auth.users(id),
|
|
expires_at TIMESTAMPTZ,
|
|
|
|
UNIQUE(user_id, profile_id)
|
|
);
|
|
|
|
-- Indices para user_profile_assignments
|
|
CREATE INDEX IF NOT EXISTS idx_user_profile_assignments_user ON auth.user_profile_assignments(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_user_profile_assignments_profile ON auth.user_profile_assignments(profile_id);
|
|
|
|
-- =====================
|
|
-- RLS POLICIES
|
|
-- =====================
|
|
ALTER TABLE auth.user_profiles ENABLE ROW LEVEL SECURITY;
|
|
CREATE POLICY tenant_isolation_profiles ON auth.user_profiles
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid OR tenant_id IS NULL);
|
|
|
|
ALTER TABLE auth.profile_tools ENABLE ROW LEVEL SECURITY;
|
|
CREATE POLICY tenant_isolation_profile_tools ON auth.profile_tools
|
|
USING (profile_id IN (
|
|
SELECT id FROM auth.user_profiles
|
|
WHERE tenant_id = current_setting('app.current_tenant_id', true)::uuid OR tenant_id IS NULL
|
|
));
|
|
|
|
ALTER TABLE auth.profile_modules ENABLE ROW LEVEL SECURITY;
|
|
CREATE POLICY tenant_isolation_profile_modules ON auth.profile_modules
|
|
USING (profile_id IN (
|
|
SELECT id FROM auth.user_profiles
|
|
WHERE tenant_id = current_setting('app.current_tenant_id', true)::uuid OR tenant_id IS NULL
|
|
));
|
|
|
|
-- =====================
|
|
-- SEED DATA: Perfiles del Sistema
|
|
-- =====================
|
|
INSERT INTO auth.user_profiles (id, tenant_id, code, name, description, is_system, monthly_price, included_platforms, available_modules, icon, color) VALUES
|
|
('00000000-0000-0000-0000-000000000001', NULL, 'ADM', 'Administrador', 'Control total del sistema', TRUE, 500, '{web,mobile,desktop}', '{all}', 'shield', '#dc2626'),
|
|
('00000000-0000-0000-0000-000000000002', NULL, 'CNT', 'Contabilidad', 'Operaciones contables y fiscales', TRUE, 350, '{web}', '{financial,reports,partners,audit}', 'calculator', '#059669'),
|
|
('00000000-0000-0000-0000-000000000003', NULL, 'VNT', 'Ventas', 'Punto de venta y CRM', TRUE, 250, '{web,mobile}', '{sales,crm,inventory,partners,reports}', 'shopping-cart', '#2563eb'),
|
|
('00000000-0000-0000-0000-000000000004', NULL, 'CMP', 'Compras', 'Gestion de proveedores y compras', TRUE, 200, '{web}', '{purchases,inventory,partners}', 'truck', '#7c3aed'),
|
|
('00000000-0000-0000-0000-000000000005', NULL, 'ALM', 'Almacen', 'Inventario y logistica', TRUE, 150, '{mobile}', '{inventory}', 'package', '#ea580c'),
|
|
('00000000-0000-0000-0000-000000000006', NULL, 'HRH', 'Recursos Humanos', 'Gestion de personal', TRUE, 300, '{web}', '{hr,partners,reports}', 'users', '#db2777'),
|
|
('00000000-0000-0000-0000-000000000007', NULL, 'PRD', 'Produccion', 'Manufactura y proyectos', TRUE, 200, '{web,mobile}', '{projects,inventory}', 'factory', '#ca8a04'),
|
|
('00000000-0000-0000-0000-000000000008', NULL, 'EMP', 'Empleado', 'Acceso self-service basico', TRUE, 50, '{mobile}', '{hr}', 'user', '#64748b'),
|
|
('00000000-0000-0000-0000-000000000009', NULL, 'GER', 'Gerente', 'Reportes y dashboards ejecutivos', TRUE, 400, '{web,mobile}', '{reports,dashboards,financial,sales,inventory}', 'bar-chart', '#0891b2'),
|
|
('00000000-0000-0000-0000-00000000000A', NULL, 'AUD', 'Auditor', 'Acceso de solo lectura para auditorias', TRUE, 150, '{web}', '{audit,reports,financial}', 'search', '#4b5563')
|
|
ON CONFLICT DO NOTHING;
|
|
|
|
-- =====================
|
|
-- SEED DATA: Herramientas por Perfil
|
|
-- =====================
|
|
|
|
-- Herramientas para CONTABILIDAD (CNT)
|
|
INSERT INTO auth.profile_tools (profile_id, tool_code, tool_name, description, category, is_web_only, icon, sort_order) VALUES
|
|
('00000000-0000-0000-0000-000000000002', 'calculadora_fiscal', 'Calculadora Fiscal', 'Calculo de impuestos y retenciones', 'fiscal', TRUE, 'calculator', 1),
|
|
('00000000-0000-0000-0000-000000000002', 'generador_cfdi', 'Generador CFDI', 'Generacion de comprobantes fiscales', 'fiscal', TRUE, 'file-text', 2),
|
|
('00000000-0000-0000-0000-000000000002', 'conciliacion_bancaria', 'Conciliacion Bancaria', 'Conciliar movimientos bancarios', 'contabilidad', TRUE, 'git-merge', 3),
|
|
('00000000-0000-0000-0000-000000000002', 'reportes_sat', 'Reportes SAT', 'Generacion de reportes para SAT', 'fiscal', TRUE, 'file-spreadsheet', 4),
|
|
('00000000-0000-0000-0000-000000000002', 'balance_general', 'Balance General', 'Generacion de balance general', 'contabilidad', TRUE, 'scale', 5),
|
|
('00000000-0000-0000-0000-000000000002', 'estado_resultados', 'Estado de Resultados', 'Generacion de estado de resultados', 'contabilidad', TRUE, 'trending-up', 6)
|
|
ON CONFLICT DO NOTHING;
|
|
|
|
-- Herramientas para VENTAS (VNT)
|
|
INSERT INTO auth.profile_tools (profile_id, tool_code, tool_name, description, category, is_mobile_only, icon, sort_order) VALUES
|
|
('00000000-0000-0000-0000-000000000003', 'pos_movil', 'POS Movil', 'Punto de venta en dispositivo movil', 'ventas', TRUE, 'smartphone', 1),
|
|
('00000000-0000-0000-0000-000000000003', 'cotizador_rapido', 'Cotizador Rapido', 'Generar cotizaciones rapidamente', 'ventas', FALSE, 'file-plus', 2),
|
|
('00000000-0000-0000-0000-000000000003', 'catalogo_productos', 'Catalogo de Productos', 'Consultar catalogo con precios', 'ventas', FALSE, 'book-open', 3),
|
|
('00000000-0000-0000-0000-000000000003', 'terminal_pago', 'Terminal de Pago', 'Cobrar con terminal Clip/MercadoPago', 'ventas', TRUE, 'credit-card', 4),
|
|
('00000000-0000-0000-0000-000000000003', 'registro_visitas', 'Registro de Visitas', 'Registrar visitas a clientes con GPS', 'crm', TRUE, 'map-pin', 5)
|
|
ON CONFLICT DO NOTHING;
|
|
|
|
-- Herramientas para ALMACEN (ALM)
|
|
INSERT INTO auth.profile_tools (profile_id, tool_code, tool_name, description, category, is_mobile_only, icon, sort_order) VALUES
|
|
('00000000-0000-0000-0000-000000000005', 'escaner_barcode', 'Escaner Codigo de Barras', 'Escanear productos por codigo de barras', 'inventario', TRUE, 'scan-line', 1),
|
|
('00000000-0000-0000-0000-000000000005', 'escaner_qr', 'Escaner QR', 'Escanear codigos QR', 'inventario', TRUE, 'qr-code', 2),
|
|
('00000000-0000-0000-0000-000000000005', 'conteo_fisico', 'Conteo Fisico', 'Realizar conteos de inventario', 'inventario', TRUE, 'clipboard-list', 3),
|
|
('00000000-0000-0000-0000-000000000005', 'recepcion_mercancia', 'Recepcion de Mercancia', 'Registrar recepciones de compras', 'inventario', TRUE, 'package-check', 4),
|
|
('00000000-0000-0000-0000-000000000005', 'transferencias', 'Transferencias', 'Transferir entre ubicaciones', 'inventario', TRUE, 'repeat', 5),
|
|
('00000000-0000-0000-0000-000000000005', 'etiquetado', 'Etiquetado', 'Imprimir etiquetas de productos', 'inventario', FALSE, 'tag', 6)
|
|
ON CONFLICT DO NOTHING;
|
|
|
|
-- Herramientas para RRHH (HRH)
|
|
INSERT INTO auth.profile_tools (profile_id, tool_code, tool_name, description, category, icon, sort_order) VALUES
|
|
('00000000-0000-0000-0000-000000000006', 'reloj_checador', 'Reloj Checador', 'Control de asistencia con biometrico', 'asistencia', 'clock', 1),
|
|
('00000000-0000-0000-0000-000000000006', 'control_asistencia', 'Control de Asistencia', 'Reportes de asistencia', 'asistencia', 'calendar-check', 2),
|
|
('00000000-0000-0000-0000-000000000006', 'nomina', 'Nomina', 'Gestion de nomina', 'nomina', 'dollar-sign', 3),
|
|
('00000000-0000-0000-0000-000000000006', 'expedientes', 'Expedientes', 'Gestion de expedientes de empleados', 'personal', 'folder', 4),
|
|
('00000000-0000-0000-0000-000000000006', 'vacaciones_permisos', 'Vacaciones y Permisos', 'Gestion de ausencias', 'personal', 'calendar-x', 5),
|
|
('00000000-0000-0000-0000-000000000006', 'organigrama', 'Organigrama', 'Visualizar estructura organizacional', 'personal', 'git-branch', 6)
|
|
ON CONFLICT DO NOTHING;
|
|
|
|
-- Herramientas para EMPLEADO (EMP)
|
|
INSERT INTO auth.profile_tools (profile_id, tool_code, tool_name, description, category, is_mobile_only, icon, sort_order) VALUES
|
|
('00000000-0000-0000-0000-000000000008', 'checada_entrada', 'Checada Entrada/Salida', 'Registrar entrada y salida con GPS y biometrico', 'asistencia', TRUE, 'log-in', 1),
|
|
('00000000-0000-0000-0000-000000000008', 'mis_recibos', 'Mis Recibos de Nomina', 'Consultar recibos de nomina', 'nomina', TRUE, 'file-text', 2),
|
|
('00000000-0000-0000-0000-000000000008', 'solicitar_permiso', 'Solicitar Permiso', 'Solicitar permisos o vacaciones', 'personal', TRUE, 'calendar-plus', 3),
|
|
('00000000-0000-0000-0000-000000000008', 'mi_horario', 'Mi Horario', 'Consultar mi horario asignado', 'asistencia', TRUE, 'clock', 4)
|
|
ON CONFLICT DO NOTHING;
|
|
|
|
-- Herramientas para GERENTE (GER)
|
|
INSERT INTO auth.profile_tools (profile_id, tool_code, tool_name, description, category, icon, sort_order) VALUES
|
|
('00000000-0000-0000-0000-000000000009', 'dashboard_ejecutivo', 'Dashboard Ejecutivo', 'Vista general de KPIs del negocio', 'reportes', 'layout-dashboard', 1),
|
|
('00000000-0000-0000-0000-000000000009', 'reportes_ventas', 'Reportes de Ventas', 'Analisis de ventas y tendencias', 'reportes', 'trending-up', 2),
|
|
('00000000-0000-0000-0000-000000000009', 'reportes_financieros', 'Reportes Financieros', 'Estados financieros resumidos', 'reportes', 'pie-chart', 3),
|
|
('00000000-0000-0000-0000-000000000009', 'alertas_negocio', 'Alertas de Negocio', 'Notificaciones de eventos importantes', 'alertas', 'bell', 4)
|
|
ON CONFLICT DO NOTHING;
|
|
|
|
-- Herramientas para AUDITOR (AUD)
|
|
INSERT INTO auth.profile_tools (profile_id, tool_code, tool_name, description, category, is_web_only, icon, sort_order) VALUES
|
|
('00000000-0000-0000-0000-00000000000A', 'visor_auditoria', 'Visor de Auditoria', 'Consultar logs de auditoria', 'auditoria', TRUE, 'search', 1),
|
|
('00000000-0000-0000-0000-00000000000A', 'exportador_datos', 'Exportador de Datos', 'Exportar datos para analisis', 'auditoria', TRUE, 'download', 2),
|
|
('00000000-0000-0000-0000-00000000000A', 'comparador_periodos', 'Comparador de Periodos', 'Comparar datos entre periodos', 'auditoria', TRUE, 'git-compare', 3)
|
|
ON CONFLICT DO NOTHING;
|
|
|
|
-- =====================
|
|
-- COMENTARIOS DE TABLAS
|
|
-- =====================
|
|
COMMENT ON TABLE auth.persons IS 'Personas fisicas responsables de cuentas (representante legal de Persona Moral o titular de Persona Fisica)';
|
|
COMMENT ON TABLE auth.user_profiles IS 'Perfiles de usuario del sistema con precios y configuraciones';
|
|
COMMENT ON TABLE auth.profile_tools IS 'Herramientas disponibles para cada perfil';
|
|
COMMENT ON TABLE auth.profile_modules IS 'Modulos del sistema accesibles por perfil';
|
|
COMMENT ON TABLE auth.user_profile_assignments IS 'Asignacion de perfiles a usuarios';
|