Documentation Index
Fetch the complete documentation index at: https://mintlify.com/Nieto2020/portalhub/llms.txt
Use this file to discover all available pages before exploring further.
The PortalHub database uses a single schema named consultoria. Every table is created with the InnoDB storage engine, utf8mb4 character set, and utf8mb4_general_ci collation, ensuring full Unicode support including emoji and special characters used in Spanish-language content. All foreign keys use the default RESTRICT behaviour unless otherwise noted.
The complete DDL is in database/schema.sql. Initial seed data — default roles, service catalogs, document-type catalogs, and the first admin account — is in database/seed.sql.
Catalog Tables
Catalog tables hold reference data that other tables reference via foreign keys. They are populated once by seed.sql and rarely change at runtime.
cat_roles
Defines the three user roles available in the system.
| Column | Type | Nullable | Description |
|---|
id_rol | int(11) | NO | Auto-increment primary key |
nombre_rol | varchar(50) | NO | Unique role name (e.g. Administrador) |
PRIMARY KEY (id_rol), UNIQUE KEY nombre_rol
cat_servicios
Catalog of accounting service types offered to clients (e.g. Declaración Anual, Contabilidad Mensual).
| Column | Type | Nullable | Description |
|---|
id_tipo_servicio | int(11) | NO | Auto-increment primary key |
nombre_servicio | varchar(100) | NO | Unique service name |
PRIMARY KEY (id_tipo_servicio), UNIQUE KEY nombre_servicio
cat_tipos_documentos
Catalog of document categories (e.g. Constancia de Situación Fiscal, CFDI).
| Column | Type | Nullable | Description |
|---|
id_tipo_doc | int(11) | NO | Auto-increment primary key |
nombre_tipo | varchar(50) | NO | Unique document type label |
PRIMARY KEY (id_tipo_doc), UNIQUE KEY nombre_tipo
Core User Tables
usuarios
The central identity table. Every person who can log in — admin, advisor, or client — has exactly one row here.
| Column | Type | Nullable | Description |
|---|
id_usuario | int(11) | NO | Auto-increment primary key |
id_rol | int(11) | NO | FK → cat_roles.id_rol |
correo | varchar(150) | NO | Login email address; must be unique |
numero_cliente | varchar(50) | YES | Optional unique client number assigned by admin |
password_hash | varchar(255) | NO | PASSWORD_BCRYPT hash via PHP password_hash() |
estado | enum('activo','inactivo') | YES | Account status; defaults to activo |
require_password_change | tinyint(1) | YES | 1 forces a password reset on next login; defaults to 0 |
fecha_registro | timestamp | NO | Automatically set to current_timestamp() on insert |
PRIMARY KEY (id_usuario), UNIQUE KEY correo, UNIQUE KEY numero_cliente, FOREIGN KEY (id_rol) REFERENCES cat_roles(id_rol)
New users created via register.php always have require_password_change = 1, forcing them to set a personal password on first login.
perfiles
Stores extended profile data for every user. Advisor-specific and client-specific fields coexist in the same table — unused columns are NULL.
| Column | Type | Nullable | Description |
|---|
id_perfil | int(11) | NO | Auto-increment primary key |
id_usuario | int(11) | NO | FK → usuarios.id_usuario (ON DELETE CASCADE); unique — one profile per user |
nombre_completo | varchar(255) | YES | Full display name |
telefono | varchar(20) | YES | Contact phone number |
foto_perfil | varchar(255) | YES | Relative path to uploaded profile photo |
especialidad | varchar(100) | YES | Asesor only — area of accounting specialisation |
biografia | text | YES | Asesor only — professional biography |
rfc | varchar(13) | YES | Cliente only — Mexican tax ID (RFC) |
razon_social | varchar(255) | YES | Cliente only — registered business name |
direccion_fiscal | text | YES | Cliente only — registered fiscal address |
fecha_actualizacion | timestamp | NO | Auto-updates to current_timestamp() on every change |
PRIMARY KEY (id_perfil), UNIQUE KEY id_usuario, FOREIGN KEY (id_usuario) REFERENCES usuarios(id_usuario) ON DELETE CASCADE
Relationship & Workflow Tables
cliente_asesor
Tracks the assignment of clients to advisors. An admin creates and deactivates assignments; historical records are preserved with estado = 'inactivo'.
| Column | Type | Nullable | Description |
|---|
id_asignacion | int(11) | NO | Auto-increment primary key |
id_cliente | int(11) | NO | FK → usuarios.id_usuario (ON DELETE CASCADE) |
id_asesor | int(11) | NO | FK → usuarios.id_usuario (ON DELETE CASCADE) |
id_asignador | int(11) | NO | FK → usuarios.id_usuario — admin who created the assignment |
fecha_asignacion | timestamp | NO | Defaults to current_timestamp() |
fecha_fin | timestamp | YES | Set when the assignment is closed |
estado | enum('activo','inactivo') | YES | Defaults to activo |
motivo_cambio | text | YES | Optional reason recorded when reassigning or closing |
PRIMARY KEY (id_asignacion), cascading FKs on id_cliente and id_asesor; restricted FK on id_asignador
citas
Appointment records between a client and an advisor.
| Column | Type | Nullable | Description |
|---|
id_cita | int(11) | NO | Auto-increment primary key |
id_cliente | int(11) | NO | FK → usuarios.id_usuario |
id_asesor | int(11) | NO | FK → usuarios.id_usuario |
fecha_hora | datetime | NO | Scheduled date and time of the appointment |
estado | enum('Programada','Reprogramada','Cancelada') | YES | Defaults to Programada |
motivo_cancelacion | text | YES | Required narrative when estado = 'Cancelada' |
PRIMARY KEY (id_cita), FKs on id_cliente and id_asesor
servicios_contables
Associates a specific accounting service type with a client and tracks its progress.
| Column | Type | Nullable | Description |
|---|
id_servicio | int(11) | NO | Auto-increment primary key |
id_cliente | int(11) | NO | FK → usuarios.id_usuario |
id_tipo_servicio | int(11) | NO | FK → cat_servicios.id_tipo_servicio |
estado | enum('Pendiente','En proceso','Completado') | YES | Defaults to Pendiente |
fecha_actualizacion | timestamp | NO | Auto-updates on every UPDATE statement |
PRIMARY KEY (id_servicio), FKs on id_cliente and id_tipo_servicio
pagos_facturacion
Records payment and invoice state for a client’s service engagement.
| Column | Type | Nullable | Description |
|---|
id_pago | int(11) | NO | Auto-increment primary key |
id_cliente | int(11) | NO | FK → usuarios.id_usuario |
id_servicio | int(11) | YES | FK → servicios_contables.id_servicio (nullable — payment may precede service creation) |
monto | decimal(10,2) | NO | Payment amount |
estado_pago | enum('Pendiente','Registrado','Aprobado') | YES | Defaults to Pendiente |
estado_factura | enum('Pendiente','Emitida') | YES | Defaults to Pendiente |
fecha_pago | datetime | YES | Date and time payment was confirmed |
PRIMARY KEY (id_pago), FKs on id_cliente and id_servicio
Document & File Tables
documentos
Metadata record for every file uploaded to the system. The physical file is stored in backend/uploads/ and protected from direct web access by .htaccess.
| Column | Type | Nullable | Description |
|---|
id_documento | int(11) | NO | Auto-increment primary key |
id_usuario_propietario | int(11) | NO | FK → usuarios.id_usuario — file owner |
id_tipo_doc | int(11) | NO | FK → cat_tipos_documentos.id_tipo_doc |
ruta_archivo | varchar(255) | NO | Server-side path to the stored file |
nombre_original | varchar(255) | NO | Original filename as uploaded by the user |
version | int(11) | YES | Increments on re-upload of the same document; defaults to 1 |
validacion_cfdi | tinyint(1) | YES | 1 if the file has passed CFDI XML validation; defaults to 0 |
fecha_subida | timestamp | NO | Defaults to current_timestamp() |
PRIMARY KEY (id_documento), FKs on id_usuario_propietario and id_tipo_doc
Communication Tables
mensajes
Stores both internal chat messages and support tickets between any two users.
| Column | Type | Nullable | Description |
|---|
id_mensaje | int(11) | NO | Auto-increment primary key |
id_remitente | int(11) | NO | FK → usuarios.id_usuario — sender |
id_destinatario | int(11) | NO | FK → usuarios.id_usuario — recipient |
tipo | enum('Chat interno','Ticket') | YES | Defaults to Chat interno |
contenido_texto | text | NO | Message body |
ruta_archivo_adjunto | varchar(255) | YES | Optional path to an attached file |
leida | tinyint(1) | YES | 0 unread / 1 read; defaults to 0 |
fecha_envio | timestamp | NO | Defaults to current_timestamp() |
PRIMARY KEY (id_mensaje), separate FKs on id_remitente and id_destinatario
notificaciones
System-generated alerts delivered to individual users. The NotificationService.php shared service creates rows in this table.
| Column | Type | Nullable | Description |
|---|
id_notificacion | int(11) | NO | Auto-increment primary key |
id_usuario_destino | int(11) | NO | FK → usuarios.id_usuario |
tipo_evento | varchar(50) | NO | Short event code (e.g. cita_programada) |
mensaje_texto | text | NO | Human-readable notification body |
leida | tinyint(1) | YES | 0 unread / 1 read; defaults to 0 |
fecha_creacion | timestamp | NO | Defaults to current_timestamp() |
PRIMARY KEY (id_notificacion), FK on id_usuario_destino
Reporting & Content Tables
reportes
Accounting reports authored by advisors and published to their assigned clients.
| Column | Type | Nullable | Description |
|---|
id_reporte | int(11) | NO | Auto-increment primary key |
id_asesor | int(11) | NO | FK → usuarios.id_usuario (ON DELETE CASCADE) |
titulo | varchar(255) | NO | Report title |
descripcion | text | NO | Short description / abstract |
contenido | text | YES | Full report body (may be NULL for drafts) |
tipo | enum('Mensual','Trimestral','Anual','Especial') | YES | Defaults to Mensual |
estado | enum('Borrador','Publicado','Archivado') | YES | Defaults to Borrador |
fecha_creacion | timestamp | NO | Defaults to current_timestamp() |
fecha_actualizacion | timestamp | NO | Auto-updates on every change |
PRIMARY KEY (id_reporte), FOREIGN KEY (id_asesor) REFERENCES usuarios(id_usuario) ON DELETE CASCADE
anuncios
Platform-wide announcements created by administrators and shown to all users.
| Column | Type | Nullable | Description |
|---|
id_anuncio | int(11) | NO | Auto-increment primary key |
id_autor | int(11) | NO | FK → usuarios.id_usuario — admin author |
titulo | varchar(255) | NO | Announcement title |
contenido | text | NO | Full announcement body |
activo | tinyint(1) | YES | 1 visible / 0 hidden; defaults to 1 |
fecha_creacion | timestamp | NO | Defaults to current_timestamp() |
PRIMARY KEY (id_anuncio), FK on id_autor
Rating & Audit Tables
calificaciones
Anonymous ratings submitted by clients to score their assigned advisor on a 1–5 scale.
| Column | Type | Nullable | Description |
|---|
id_calificacion | int(11) | NO | Auto-increment primary key |
id_cliente | int(11) | NO | FK → usuarios.id_usuario — rating author |
id_asesor | int(11) | NO | FK → usuarios.id_usuario — rated advisor |
puntuacion | tinyint(1) | NO | Integer 1–5; enforced by CHECK (puntuacion BETWEEN 1 AND 5) |
fecha | timestamp | NO | Defaults to current_timestamp() |
PRIMARY KEY (id_calificacion), FKs on id_cliente and id_asesor, CHECK constraint on puntuacion
reset_logs
Immutable audit trail of every admin-initiated password reset. Used for compliance and security review.
| Column | Type | Nullable | Description |
|---|
id_log | int(11) | NO | Auto-increment primary key |
id_admin | int(11) | NO | FK → usuarios.id_usuario (ON DELETE CASCADE) — admin who performed the reset |
id_usuario_afectado | int(11) | NO | FK → usuarios.id_usuario (ON DELETE CASCADE) — account that was reset |
fecha_reseteo | timestamp | NO | Defaults to current_timestamp() |
PRIMARY KEY (id_log), cascading FKs on both id_admin and id_usuario_afectado
Character Set & Collation
All tables are defined with:
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
| Setting | Value | Reason |
|---|
| Engine | InnoDB | ACID transactions and foreign-key enforcement |
| Character set | utf8mb4 | Full Unicode — supports all Spanish characters and supplementary planes |
| Collation | utf8mb4_general_ci | Case-insensitive, accent-insensitive string comparisons |
If you need accent-sensitive searches (e.g. distinguishing México from Mexico in queries), consider migrating specific varchar columns to utf8mb4_unicode_ci or utf8mb4_0900_ai_ci without changing the rest of the schema.