--- name: corporate-fullstack-rbac description: >- Standard architecture, database schemas, RBAC permission engine, UI/UX design patterns, and corporate Excel reporting templates for building enterprise fullstack web applications from scratch with Express, MySQL, SPA, JWT, and visual permission matrices. --- # Enterprise Fullstack & Granular RBAC Standard Architecture This skill provides the comprehensive architectural blueprint, database design, backend security middleware, dynamic SPA frontend, theme styling, and corporate reporting engines to build or bootstrap any enterprise management application from day one. --- ## 1. Core Architecture Overview Every corporate system built using this standard follows a 3-tier decoupled architecture: 1. **Database Layer (MySQL / PostgreSQL):** - Core security tables: `usuarios`, `roles`, `rol_permisos`. - Domain-specific master and transactional tables with indexed foreign keys. 2. **Backend REST API (Node.js + Express):** - Authentication via JWT and bcrypt password hashing. - Granular RBAC middleware: `requirePermission(modulo, accion)` & `verifyToken`. - Safe SQL parameterized queries (`mysql2/promise` pool). - Corporate Excel generation with `exceljs`. 3. **Frontend Single Page Application (Vanilla CSS + HTML5 + Modular JS):** - Premium corporate UI with dynamic dark/executive palettes, glassmorphism cards, and fluid animations. - Dynamic sidebar navigation filtering based on user permissions. - Interactive visual **Permission Matrix** modal with instant batch toggles. - Standard reusable components: Data Tables, Filter Bars with Date Presets, Toast Notifications, Modal Dialogs, Chart.js visualizations. --- ## 2. Universal RBAC Database Schema When starting any new project, execute this baseline migration before adding business tables: ```sql -- 1. Roles Master Table CREATE TABLE IF NOT EXISTS roles ( id INT AUTO_INCREMENT PRIMARY KEY, nombre VARCHAR(60) NOT NULL UNIQUE, descripcion VARCHAR(255) NULL, activo TINYINT(1) DEFAULT 1, fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- 2. Granular Permissions Table by Module and Action CREATE TABLE IF NOT EXISTS rol_permisos ( id INT AUTO_INCREMENT PRIMARY KEY, rol_id INT NOT NULL, modulo VARCHAR(50) NOT NULL, puede_ver TINYINT(1) DEFAULT 0, puede_crear TINYINT(1) DEFAULT 0, puede_editar TINYINT(1) DEFAULT 0, puede_eliminar TINYINT(1) DEFAULT 0, puede_exportar TINYINT(1) DEFAULT 0, UNIQUE KEY uq_rol_modulo (rol_id, modulo), CONSTRAINT fk_rol_permisos_rol FOREIGN KEY (rol_id) REFERENCES roles(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- 3. System Users Table with Dynamic Role Linkage CREATE TABLE IF NOT EXISTS usuarios ( id INT AUTO_INCREMENT PRIMARY KEY, nombre VARCHAR(100) NOT NULL, email VARCHAR(100) NOT NULL UNIQUE, PASSWORD VARCHAR(255) NOT NULL, rol VARCHAR(60) NOT NULL DEFAULT 'usuario', token_recuperacion VARCHAR(255) NULL, token_expiracion DATETIME NULL, fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_usuarios_rol (rol) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; ``` --- ## 3. Backend RBAC Engine & Middleware ### `middleware/auth.js` Standard Implementation ```javascript const jwt = require('jsonwebtoken'); const { pool } = require('../config/db'); const JWT_SECRET = process.env.JWT_SECRET || 'corporate_default_jwt_secret_key_2026'; // 1. Verify JWT Token function verifyToken(req, res, next) { let token = req.headers['authorization']; if (token && token.startsWith('Bearer ')) { token = token.slice(7, token.length); } else if (req.query && req.query.token) { token = req.query.token; } if (!token) { return res.status(401).json({ success: false, message: 'Acceso no autorizado: Token requerido.' }); } try { const decoded = jwt.verify(token, JWT_SECRET); req.user = decoded; next(); } catch (err) { return res.status(401).json({ success: false, message: 'Token inválido o expirado.' }); } } // 2. Fetch Granular Permissions Map for a Role async function getUserPermissions(roleName) { try { const [roles] = await pool.query('SELECT id FROM roles WHERE nombre = ?', [roleName]); if (roles.length === 0) { if (roleName === 'admin') return { _all: true }; return {}; } const roleId = roles[0].id; const [perms] = await pool.query( 'SELECT modulo, puede_ver, puede_crear, puede_editar, puede_eliminar, puede_exportar FROM rol_permisos WHERE rol_id = ?', [roleId] ); const permMap = {}; perms.forEach(p => { permMap[p.modulo] = { puede_ver: Boolean(p.puede_ver), puede_crear: Boolean(p.puede_crear), puede_editar: Boolean(p.puede_editar), puede_eliminar: Boolean(p.puede_eliminar), puede_exportar: Boolean(p.puede_exportar) }; }); return permMap; } catch (err) { console.error('Error fetching role permissions:', err); return {}; } } // 3. Granular Permission Guard Middleware function requirePermission(modulo, accion = 'puede_ver') { return async (req, res, next) => { if (!req.user) { return res.status(401).json({ success: false, message: 'Usuario no autenticado.' }); } // Superadmin bypass if (req.user.rol === 'admin') return next(); try { const perms = await getUserPermissions(req.user.rol); if (perms[modulo] && perms[modulo][accion]) { return next(); } return res.status(403).json({ success: false, message: `Acceso denegado: No cuenta con privilegios para '${accion}' en el módulo '${modulo}'.` }); } catch (err) { return res.status(500).json({ success: false, message: 'Error al verificar permisos.' }); } }; } module.exports = { JWT_SECRET, verifyToken, requirePermission, getUserPermissions }; ``` --- ## 4. Frontend Architecture & Permission Reactive UI ### Dynamic Sidebar Navigation (`data-module` attribute) In `index.html`, every navigation item has a `data-module` attribute corresponding to a system module: ```html ``` ### Action Controls (`data-permission` attribute) Action buttons are tagged with `data-permission="modulo:accion"`: ```html ``` ### Client-Side Permission Enforcement (`public/js/auth.js`) ```javascript const Auth = { currentUser: null, hasPermission(modulo, accion = 'puede_ver') { if (!this.currentUser) return false; if (this.currentUser.rol === 'admin') return true; if (this.currentUser.permisos && this.currentUser.permisos[modulo]) { return Boolean(this.currentUser.permisos[modulo][accion]); } return false; }, applyPermissions() { if (!this.currentUser) return; let firstVisibleTab = null; // 1. Sidebar items visibility document.querySelectorAll('.nav-item[data-module]').forEach(item => { const mod = item.getAttribute('data-module'); if (this.hasPermission(mod, 'puede_ver')) { item.classList.remove('hidden'); if (!firstVisibleTab) firstVisibleTab = item.getAttribute('data-tab'); } else { item.classList.add('hidden'); } }); // 2. Action buttons visibility document.querySelectorAll('[data-permission]').forEach(el => { const [mod, act] = el.getAttribute('data-permission').split(':'); if (this.hasPermission(mod, act || 'puede_ver')) { el.classList.remove('hidden'); } else { el.classList.add('hidden'); } }); // 3. Fallback tab switch if current active tab was hidden if (typeof App !== 'undefined' && App.currentTab) { const activeNav = document.querySelector(`.nav-item[data-tab="${App.currentTab}"]`); if (activeNav && activeNav.classList.contains('hidden') && firstVisibleTab) { App.switchTab(firstVisibleTab); } } } }; ``` --- ## 5. Interactive Permission Matrix Modal The Permission Matrix gives administrators a visual grid to toggle any action for every registered module: ```html ``` --- ## 6. Corporate Excel Generation Standard (`exceljs`) Enterprise reports generated in Excel must always follow the 2-level corporate presentation standard: 1. **Embedded Official Brand Logo:** PNG image added to top left (e.g. `B2:D4`) with `worksheet.addImage`. 2. **Two-Level Grouped Headers:** Category row (e.g., *Reuniones*, *Áreas de Servicio*) merged across sub-columns, and sub-header row (*1er Reu.*, *2do Reu.*, *Total Día*). 3. **Harmonious Pastel Palette:** - Blue Header Category: `#0F4C81` / Pastel sub-headers `#E8F1F8` - Green Area: `#1B5E20` / Pastel `#EAF4E8` - Warm Amber Summary: `#E65100` / Pastel `#FEF8E7` - Gold Highlight: `#FFF2CC` 4. **Live Excel Formulas:** Always write formulas (`{ formula: 'C6+D6' }`, `{ formula: 'SUM(C6:C40)' }`) instead of static numbers so the spreadsheet remains interactive. 5. **Accounting Double Border:** The totals footer must use `{ bottom: { style: 'double', color: { argb: '000000' } } }`. --- ## 7. New Project Checklist & Workflow When creating or onboarding a new project with this architecture: 1. **Step 1: Database Setup** - Create the database and run the universal `usuarios`, `roles`, `rol_permisos` script. - Seed default roles: `admin` (superadmin), `supervisor`, `auditor`, `operador`. 2. **Step 2: Backend Boilerplate** - Initialize Express with `cors`, `morgan`, `express.json()`. - Setup `config/db.js` using `mysql2/promise` connection pool. - Mount `/api/auth` (`routes/auth.routes.js`) and `/api/roles` (`routes/roles.routes.js`). - Implement `middleware/auth.js` with `requirePermission`. 3. **Step 3: Frontend SPA Shell** - Create responsive layout with Sidebar, Topbar, Global Filter Bar, and Tab Panels. - Implement `Auth.hasPermission()` in `public/js/auth.js`. - Implement `public/js/api.js` client wrapper with Bearer token header interceptor. - Create `tab-roles` and `tab-users` with the interactive matrix modal. 4. **Step 4: Business Modules Implementation** - Add domain-specific routes, tables, and views. - Register each new module in the RBAC module registry. - Apply `requirePermission(modulo, accion)` on every endpoint and UI component. 5. **Step 5: Automated Verification** - Create `tests/rbac-test-suite.js` to guarantee access rules and token lifecycle end-to-end.