Esquema de Base de Datos

Arquitectura de datos del sistema PrepaBE Admin

info

Resumen del Sistema

El sistema PrepaBE Admin gestiona una red de franquicias educativas. La base de datos está diseñada para manejar prospectos, estudiantes, pagos, planes educativos, y la administración de múltiples sucursales.

Tablas Principales

Módulos Core

20+

Relaciones

account_tree

Diagrama Entidad-Relación

erDiagram
    OFICINAS ||--o{ USUARIOS : "administran"
    OFICINAS ||--o{ ESTUDIANTES : "atienden"
    OFICINAS ||--o{ PROSPECTOS : "gestionan"
    
    USUARIOS ||--o{ PROSPECTOS : "asignan"
    USUARIOS ||--o{ PAGOS : "registran"
    
    PROSPECTOS ||--o| ESTUDIANTES : "convierten_a"
    
    ESTUDIANTES ||--o{ PAGOS : "realizan"
    ESTUDIANTES ||--|| PLANES : "inscriben_en"
    
    PLANES ||--o{ PAGOS : "generan"
    
    PAGOS }|--|| METODOS_PAGO : "usan"
    
    OFICINAS {
        int id PK
        string nombre
        string direccion
        string telefono
        string email
        timestamp created_at
        boolean activo
    }
    
    USUARIOS {
        int id PK
        int oficina_id FK
        string nombre
        string email
        string password_hash
        string rol
        timestamp created_at
        boolean activo
    }
    
    PROSPECTOS {
        int id PK
        int oficina_id FK
        int asignado_a FK
        string nombre
        string email
        string telefono
        string whatsapp
        string interes_certificado
        string modalidad
        string fuente
        string estado
        text notas
        timestamp created_at
    }
    
    ESTUDIANTES {
        int id PK
        int prospecto_id FK
        int oficina_id FK
        string matricula
        string nombre
        string email
        string telefono
        string tipo_certificado
        int plan_id FK
        decimal saldo_pendiente
        timestamp fecha_inscripcion
        boolean activo
    }
    
    PLANES {
        int id PK
        string nombre
        string tipo_certificado
        string modalidad
        decimal costo_total
        int duracion_meses
        text descripcion
        boolean activo
    }
    
    PAGOS {
        int id PK
        int estudiante_id FK
        int plan_id FK
        int metodo_pago_id FK
        int registrado_por FK
        decimal monto
        string concepto
        string estado
        timestamp fecha_pago
        text notas
    }
    
    METODOS_PAGO {
        int id PK
        string nombre
        string tipo
        boolean activo
    }

💡 El diagrama muestra las relaciones principales entre las entidades del sistema.

Documentación de Tablas

apartment

OFICINAS

Gestión de sucursales y franquicias

Almacena información de cada sucursal o franquicia de PrepaBE. Relaciona usuarios, estudiantes y prospectos con su oficina correspondiente.

Campo	Tipo	Restricciones	Descripción
id	INT	PK, AUTO_INCREMENT	Identificador único
nombre	VARCHAR(100)	NOT NULL	Nombre de la oficina
direccion	TEXT	-	Dirección completa
telefono	VARCHAR(20)	-	Teléfono de contacto
email	VARCHAR(100)	UNIQUE	Email institucional
created_at	TIMESTAMP	DEFAULT NOW()	Fecha de creación
activo	BOOLEAN	DEFAULT TRUE	Estado de la oficina

badge

USUARIOS

Control de acceso y roles

Gestiona los usuarios del sistema (administradores, gerentes, instructores). Cada usuario está asignado a una oficina y tiene roles específicos.

🔐 Roles disponibles:

SUPER_ADMIN GERENTE_FRANQUICIA INSTRUCTOR ASISTENTE_ADMIN

Campo	Tipo	Restricciones	Descripción
id	INT	PK	Identificador único
oficina_id	INT	FK → OFICINAS	Oficina asignada
nombre	VARCHAR(100)	NOT NULL	Nombre completo
email	VARCHAR(100)	UNIQUE, NOT NULL	Email de acceso
password_hash	VARCHAR(255)	NOT NULL	Contraseña encriptada
rol	ENUM	NOT NULL	Rol del usuario
activo	BOOLEAN	DEFAULT TRUE	Estado del usuario

person_search

PROSPECTOS

CRM - Gestión de leads

Captura y seguimiento de prospectos. Cada prospecto puede convertirse en estudiante. El sistema rastrea la fuente de captación y el estado del proceso comercial.

📊 Estados posibles:

NUEVO CONTACTADO EN_SEGUIMIENTO CONVERTIDO PERDIDO

🎯 Fuentes de captación:

• Redes Sociales

• Referido

• Página Web

• Walk-in / Visita directa

Campo	Tipo	Descripción
id	INT	Identificador único
nombre	VARCHAR(100)	Nombre del prospecto
email / telefono / whatsapp	VARCHAR	Datos de contacto
interes_certificado	VARCHAR(50)	SECUNDARIA, PREPA, LICENCIATURA
fuente	VARCHAR(50)	Origen del lead
estado	ENUM	Estado del proceso

school

ESTUDIANTES

Base de alumnos activos

Registro completo de estudiantes activos. Cada estudiante está vinculado a un prospecto original, tiene un plan educativo asignado, y un saldo financiero que se actualiza con cada pago.

⚠️ Relaciones importantes:

• Cada estudiante proviene de un prospecto_id
• Tiene un plan_id que define costo y duración
• El campo saldo_pendiente se calcula: Plan.costo_total - SUM(Pagos.monto)

Campo	Tipo	Descripción
id	INT	Identificador único
matricula	VARCHAR(20)	Número de matrícula único (#PB-XXXXX)
prospecto_id	INT	FK → PROSPECTOS (origen)
plan_id	INT	FK → PLANES
saldo_pendiente	DECIMAL(10,2)	Monto por pagar
fecha_inscripcion	TIMESTAMP	Fecha de registro

payments

PAGOS

Transacciones financieras

Registro de todos los pagos realizados por estudiantes. Soporta múltiples métodos de pago y permite trazabilidad completa (quién registró, cuándo, concepto).

Campo	Tipo	Descripción
estudiante_id	INT	FK → ESTUDIANTES
metodo_pago_id	INT	FK → METODOS_PAGO (Efectivo, Tarjeta, Transferencia)
monto	DECIMAL(10,2)	Cantidad pagada
concepto	VARCHAR(200)	Descripción del pago
estado	ENUM	COMPLETADO, PENDIENTE, CANCELADO

workspace_premium

PLANES

Catálogo de programas educativos

Catálogo de planes educativos disponibles. Define costos, duración, modalidades y tipos de certificación.

Certificados

Secundaria, Preparatoria, Licenciatura

Modalidades

Presencial, En Línea, Mixta

Planes

Standard, Premium, Express

code

Ejemplos de Queries SQL

📊 Obtener estudiantes con saldo pendiente por oficina:

SELECT 
    o.nombre AS oficina,
    e.matricula,
    e.nombre,
    e.saldo_pendiente,
    p.nombre AS plan
FROM estudiantes e
JOIN oficinas o ON e.oficina_id = o.id
JOIN planes p ON e.plan_id = p.id
WHERE e.saldo_pendiente > 0 AND e.activo = TRUE
ORDER BY e.saldo_pendiente DESC;

💰 Reporte de ingresos diarios por método de pago:

SELECT 
    mp.nombre AS metodo,
    COUNT(pa.id) AS num_transacciones,
    SUM(pa.monto) AS total_ingresos,
    DATE(pa.fecha_pago) AS fecha
FROM pagos pa
JOIN metodos_pago mp ON pa.metodo_pago_id = mp.id
WHERE DATE(pa.fecha_pago) = CURDATE() AND pa.estado = 'COMPLETADO'
GROUP BY mp.nombre, DATE(pa.fecha_pago);

🎯 Funnel de conversión (Prospectos → Estudiantes):

SELECT 
    COUNT(DISTINCT pr.id) AS total_prospectos,
    COUNT(DISTINCT e.id) AS total_convertidos,
    ROUND((COUNT(DISTINCT e.id) / COUNT(DISTINCT pr.id)) * 100, 2) AS tasa_conversion
FROM prospectos pr
LEFT JOIN estudiantes e ON pr.id = e.prospecto_id
WHERE pr.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY);

🏢 Top 5 oficinas por ingresos del mes:

SELECT 
    o.nombre AS oficina,
    COUNT(DISTINCT e.id) AS num_estudiantes,
    SUM(pa.monto) AS ingresos_totales
FROM oficinas o
JOIN estudiantes e ON e.oficina_id = o.id
JOIN pagos pa ON pa.estudiante_id = e.id
WHERE MONTH(pa.fecha_pago) = MONTH(NOW()) 
  AND YEAR(pa.fecha_pago) = YEAR(NOW())
  AND pa.estado = 'COMPLETADO'
GROUP BY o.id, o.nombre
ORDER BY ingresos_totales DESC
LIMIT 5;

info

Notas de Implementación

• Todas las contraseñas deben almacenarse con bcrypt o argon2 (mínimo 10 rounds)
• Los timestamps deben usar zona horaria UTC y convertirse en la capa de aplicación
• Implementar índices en campos FK y campos de búsqueda frecuente (email, matricula, telefono)
• Configurar soft deletes para mantener historial (campo deleted_at )
• Usar transacciones para operaciones que modifiquen múltiples tablas (ej: crear estudiante + primer pago)