Documentation Index
Fetch the complete documentation index at: https://mintlify.com/DanielRivera03/SistemaBancario/llms.txt
Use this file to discover all available pages before exploring further.
The cashmanha database runs on MySQL/MariaDB with the utf8mb4 character set and InnoDB storage engine throughout. It contains 21 tables, 148 stored procedures, 67 views, 21 triggers, and 5 scheduled events. All data access from the PHP application layer goes through stored procedures — no inline SQL is written in controller or model code.
Domain Overview
| Domain | Tables |
|---|
| Users & Auth | usuarios, roles, detalleusuarios, accesos, recuperacion |
| Credits | creditos, cuotas, historicocreditos, historicocuotascreditos |
| Credit Payments | transacciones, historicotransacciones |
| Savings Accounts | cuentas, transaccionescuentasclientes, transferencias |
| Products | productos |
| References & Vehicles | referenciaspersonales, datosvehiculoscreditos |
| Communication | mensajeria, notificaciones |
| Transfer Security | codigostransferencias |
| System | reporteproblemasplataforma |
Table Reference
usuarios
Core identity and permission record for every platform user (clients, employees, management, presidency).
| Column | Type | Constraints | Notes |
|---|
idusuarios | int(11) | PK, AUTO_INCREMENT | Surrogate key |
nombres | varchar(255) | NOT NULL | First name(s) |
apellidos | varchar(255) | NOT NULL | Last name(s) |
codigousuario | varchar(255) | UNIQUE, NOT NULL | Login username |
contrasenia | varchar(255) | NOT NULL | Hashed password |
correo | varchar(255) | UNIQUE, NOT NULL | Email address |
fotoperfil | varchar(255) | DEFAULT foto_usuarios_nuevos.png | Profile photo filename |
idrol | int(11) | FK → roles.idrol | Assigned role |
estado_usuario | varchar(25) | DEFAULT activo | activo / inactivo / bloqueado |
completoperfil | varchar(2) | DEFAULT no | si once detalleusuarios row inserted |
habilitarsistema | char(2) | DEFAULT no | si after first installment plan generated |
nuevousuario | char(2) | DEFAULT si | Cleared on first login credential change |
poseecuenta | char(2) | DEFAULT no | Set to si by trigger on account creation |
poseecredito | char(2) | DEFAULT no | Indicates active credit on file |
habilitarnuevoscreditos | char(2) | DEFAULT si | Disabled while a credit is in progress |
quienregistro | varchar(255) | NOT NULL | codigousuario of the registering employee |
roles
Lookup table for the five platform roles.
| Column | Type | Constraints |
|---|
idrol | int(11) | PK, AUTO_INCREMENT |
nombrerol | varchar(75) | UNIQUE, NOT NULL |
descripcionrol | varchar(255) | NOT NULL |
detalleusuarios
Extended KYC profile for clients — populated during onboarding and required before a credit application can be submitted.
| Column | Type | Notes |
|---|
iddetalle | int(11) PK | |
dui | varchar(10) | El Salvador national ID |
nit | varchar(17) | Tax ID |
telefono / celular / telefonotrabajo | varchar(9) | Contact numbers |
direccion | varchar(255) | Home address |
empresa / cargo / direcciontrabajo | varchar(255) | Employment details |
fechanacimiento | date | |
genero | char(1) | Single character code |
estadocivil | varchar(30) | Marital status |
fotoduifrontal / fotoduireverso | varchar(255) | Uploaded document filenames |
fotonit | varchar(255) | |
fotofirma | varchar(255) | Signature scan filename |
idusuarios | int(11) | FK → usuarios, ON DELETE CASCADE |
A composite UNIQUE constraint exists on (dui, nit) — duplicate identity documents are rejected at the database level.
accesos
Immutable login audit trail. Every successful login appends one row.
| Column | Type | Notes |
|---|
idacceso | int(11) PK | AUTO_INCREMENT |
fechaacceso | timestamp | DEFAULT current_timestamp() |
dispositivo | varchar(255) | Device description |
sistemaoperativo | varchar(255) | OS string |
idusuarios | int(11) | FK → usuarios, ON DELETE CASCADE |
recuperacion
One-time tokens for password recovery flows.
| Column | Type | Notes |
|---|
idrecuperaciones | int(11) PK | AUTO_INCREMENT |
correo | varchar(255) | NOT NULL |
token | varchar(255) | Random token |
codigo | int(11) | Numeric verification code |
fecha | timestamp | Issued at |
estado | varchar(15) | DEFAULT nousado; set to usado after use |
creditos
Central credit application and lifecycle table. Each row represents one credit from submission through full repayment or archival.
| Column | Type | Notes |
|---|
idcreditos | int(11) PK | AUTO_INCREMENT |
idusuarios | int(11) | FK → usuarios, ON DELETE CASCADE |
idproducto | int(11) | FK → productos, ON DELETE CASCADE |
tipocliente | varchar(50) | Asalariado / Independiente / Jubilado |
montocredito | decimal(9,2) | Approved principal amount |
interescredito | float | Annual interest rate |
plazocredito | int(11) | Term in months (years × 12 for mortgages) |
cuotamensual | decimal(9,2) | Computed monthly installment |
fechasolicitud | date | Application date |
salariocliente | decimal(9,2) | Declared monthly salary |
saldocredito | decimal(15,6) | Running principal balance (decremented by triggers) |
estado | varchar(30) | en proceso / aprobacioninicial / aprobado / denegado / reestructuracion / cancelado — DEFAULT en proceso |
observaciones | varchar(1500) | Client-side notes |
observacion_gerencia | varchar(1500) | Gerencia review notes |
observacion_presidencia | varchar(1500) | Presidencia decision notes |
usuario_empleado | varchar(255) | codigousuario of the assigning employee |
progreso_solicitud | tinyint(4) | 0–100 progress bar value; DEFAULT 10 |
progreso_pagocredito | tinyint(4) | Payment progress 0–100; DEFAULT 0 |
fecha_ultimarevision | timestamp | Nullable; set on each review action |
usuario_gestionando | varchar(255) | Last reviewer username |
cuotas_generadas | char(2) | DEFAULT no; set to si by trigger after installments inserted |
copiacontratocliente | varchar(255) | Signed contract filename |
estadocrediticio | varchar(255) | DEFAULT Nuevo Cliente; updated to Excelente / Regular / Deficiente by triggers |
proceso_finalizado | char(2) | DEFAULT no |
enviaralhistorico | char(2) | DEFAULT no; set by trigger when history installments written |
ocultartransacciones_clientes | char(2) | DEFAULT no; hides old transactions from client portal |
creditoactivo | char(2) | DEFAULT si |
cuotas
Monthly installment schedule generated after a credit is approved.
| Column | Type | Notes |
|---|
idcuotas | int(11) PK | AUTO_INCREMENT |
idcreditos | int(11) | FK → creditos, ON DELETE CASCADE |
idproducto | int(11) | FK → productos |
idusuarios | int(11) | FK → usuarios |
montocancelar | decimal(9,2) | Amount due (increases when overdue penalty applied) |
estadocuota | varchar(30) | DEFAULT pendiente; set to cancelado by trigger on payment |
nombreproducto | varchar(255) | Denormalized product name |
montocapital | decimal(9,2) | Capital portion of installment |
fechavencimiento | date | Due date |
incumplimiento_pago | char(2) | DEFAULT NO; set to SI by event when overdue, then PT (paid-late) by trigger after payment |
disponiblehistorico | char(2) | DEFAULT no |
historicocreditos and historicocuotascreditos
Archive tables that receive credit and installment records respectively when a credit is fully repaid or deleted. Populated by the trigger EnviarSolicitudesCreditosDenegadas_HistoricoCreditos (on DELETE from creditos) and by the procedure RegistrarCuotasMensualesHistoricoCreditosClientes.
transacciones
Immutable record of every installment payment processed through the platform.
| Column | Type | Notes |
|---|
idtransaccion | int(11) PK | AUTO_INCREMENT |
idusuarios | int(11) | FK → usuarios, ON DELETE CASCADE |
idproducto | int(11) | FK → productos, ON DELETE CASCADE |
idcreditos | int(11) | FK → creditos, ON DELETE CASCADE |
idcuotas | int(11) | FK → cuotas, ON DELETE CASCADE |
referencia | varchar(255) | Payment reference code |
monto | decimal(9,2) | Amount paid |
fecha | timestamp | DEFAULT current_timestamp() |
dias_incumplimiento | int(11) | Days overdue at time of payment (0 if on-time) |
empleado_gestion | varchar(255) | codigousuario of processing employee |
An INSERT into transacciones fires eight triggers simultaneously: installment status change, credit balance recalculation, credit status (cancelled if balance = 0), credit rating updates (two triggers), overdue flag reset, payment notification, and history archival.
historicotransacciones
Mirror of transacciones, written by trigger RegistroTransaccionesCuotasCreditosClientes_Historico on every INSERT. Provides an immutable audit log that cannot be affected by cascade deletes on the main tables.
cuentas
Savings account master record. Each user may have at most one active account (enforced by a UNIQUE key on idusuarios).
| Column | Type | Notes |
|---|
idcuentas | int(11) PK | AUTO_INCREMENT |
numerocuenta | int(12) | UNIQUE — account number presented to clients |
montocuenta | decimal(9,2) | Current balance (updated by triggers) |
fechaapertura | timestamp | DEFAULT current_timestamp() |
idproducto | int(11) | FK → productos |
idusuarios | int(11) | FK → usuarios, UNIQUE |
estadocuenta | varchar(50) | DEFAULT activa; bloqueada / cerrada |
transaccionescuentasclientes
Ledger of every deposit, withdrawal, and transfer movement on a savings account.
| Column | Type | Notes |
|---|
idtransaccioncuentas | int(11) PK | AUTO_INCREMENT |
idusuarios | int(11) | FK → usuarios, ON DELETE CASCADE |
idproducto | int(11) | FK → productos, ON DELETE CASCADE |
idcuentas | int(11) | FK → cuentas, ON DELETE CASCADE |
referencia | varchar(255) | Operation reference code |
monto | decimal(9,2) | Transaction amount |
fecha | timestamp | DEFAULT current_timestamp() |
empleado_gestion | varchar(255) | Teller or Clientes (self-service) |
tipotransaccion | varchar(50) | Entrada / Salida / EnvioTransferencia / DepositoTransferencia |
estadotransaccion | varchar(50) | Procesada / AnularDeposito / AnularRetiro |
saldonuevocuenta_transaccion | decimal(9,2) | Balance snapshot after transaction |
transferencias
Outbound transfer request record. One row represents a full inter-account transfer; the trigger RecalcularSaldoFinal_TransferenciasClientes debits the source account and credits the destination atomically.
| Column | Type | Notes |
|---|
idtransferencia | int(11) PK | AUTO_INCREMENT |
numerocuenta | int(11) | Source account number |
monto | decimal(9,2) | Transfer amount |
referencia | varchar(255) | Reference string |
fecha | timestamp | DEFAULT current_timestamp() |
estado | varchar(15) | DEFAULT NoProcesado |
idusuarios | int(11) | FK → usuarios (sender) |
idusuariodestino | int(11) | FK → usuarios (recipient) |
idproducto | int(11) | FK → productos |
idcuentas | int(11) | FK → cuentas (source) |
idcuentadestino | int(11) | FK → cuentas (destination) |
codigostransferencias
Ephemeral table for OTP security codes used to authorise outbound transfers. A scheduled event expires codes after 2 minutes.
| Column | Type | Notes |
|---|
idcodigo | int(11) PK | AUTO_INCREMENT |
codigoseguridad | int(11) | Numeric OTP |
fecha | timestamp | DEFAULT current_timestamp() |
estado | varchar(50) | DEFAULT Valido; set to Vencido by event |
idusuarios | int(11) | FK → usuarios, ON DELETE CASCADE |
productos
Financial product catalogue (savings accounts, personal loans, vehicle loans, mortgages, etc.).
| Column | Type | Notes |
|---|
idproducto | int(11) PK | AUTO_INCREMENT |
codigo | varchar(255) | Unique product code |
nombreproducto | varchar(255) | Display name |
descripcionproducto | varchar(255) | Short description |
requisitosproductos | varchar(1000) | Requirements text |
estado | varchar(15) | activo / inactivo / expirado |
referenciaspersonales
Stores two personal/professional references per credit application.
| Column | Notes |
|---|
idreferencias PK | |
idcreditos | FK → creditos, CASCADE |
idusuarios | FK → usuarios |
idproducto | FK → productos |
nombres_referencia1, apellidos_referencia1 | Reference 1 name |
empresa_referencia1, profesion_oficioreferencia1, telefono_referencia1 | Reference 1 employment |
nombres_referencia2 … telefono_referencia2 | Reference 2 (same structure) |
datosvehiculoscreditos
Vehicle details required for vehicle-backed credit products.
| Column | Notes |
|---|
iddatosvehiculos PK | |
idcreditos | FK → creditos, CASCADE |
placa | varchar(8) — licence plate |
clase, anio, capacidad, asientos | Vehicle classification |
marca, modelo | Make and model |
numeromotor, chasisgrabado, chasisvin | Chassis identifiers (varchar(17)) |
color | varchar(40) |
mensajeria
Internal messaging system allowing any user to send messages to any other user.
| Column | Type | Notes |
|---|
idmensajeria | int(11) PK | AUTO_INCREMENT |
idusuarios | int(11) | FK → usuarios (sender) |
nombremensaje | varchar(255) | Sender display name |
asuntomensaje | varchar(255) | Subject line |
detallemensaje | varchar(5000) | Message body |
fechamensaje | timestamp | DEFAULT + ON UPDATE current_timestamp() |
idusuariosdestinatario | int(11) | FK → usuarios (recipient) |
ocultarmensaje | char(2) | DEFAULT no; set to si to archive |
An AFTER INSERT trigger on this table automatically inserts a notification row in notificaciones for the recipient.
notificaciones
Platform notification feed. Every event that should surface in the UI notifications bar writes here.
| Column | Type | Notes |
|---|
idnotificacion | int(11) PK | AUTO_INCREMENT |
idusuarios | int(11) | FK → usuarios (recipient) |
titulonotificacion | varchar(255) | Notification title |
descripcionnotificacion | varchar(255) | Detail text |
fechanotificacion | timestamp | DEFAULT current_timestamp() |
clasificacionnotificacion | varchar(100) | Type tag (e.g. nuevomensaje, pagorecibido) |
ocultarnotificacion | char(2) | DEFAULT no; dismissed notifications set to si |
Help-desk ticket table. Users submit bug reports; staff resolve and close them. A scheduled event auto-closes tickets idle for more than 3 days.
| Column | Notes |
|---|
idreporte PK | |
idusuarios | FK → usuarios |
nombrereporte | Ticket title |
descripcionreporte | varchar(3000) |
fotoreporte | Screenshot filename |
fecharegistroreporte | datetime |
fechaactualizacionreporte | timestamp ON UPDATE |
estado | varchar(50) — open / resuelto / cerrado |
comentarioactualizacion | Staff response |
empleado_gestion | Handling employee |
Foreign Key Summary
| Child Table | Column | References | On Delete |
|---|
accesos | idusuarios | usuarios | CASCADE |
codigostransferencias | idusuarios | usuarios | CASCADE |
creditos | idusuarios | usuarios | CASCADE |
creditos | idproducto | productos | CASCADE |
cuentas | idusuarios | usuarios | RESTRICT |
cuentas | idproducto | productos | RESTRICT |
cuotas | idusuarios | usuarios | NO ACTION |
cuotas | idcreditos | creditos | CASCADE |
cuotas | idproducto | productos | NO ACTION |
datosvehiculoscreditos | idcreditos | creditos | CASCADE |
detalleusuarios | idusuarios | usuarios | CASCADE |
mensajeria | idusuarios | usuarios | CASCADE |
mensajeria | idusuariosdestinatario | usuarios | CASCADE |
notificaciones | idusuarios | usuarios | CASCADE |
referenciaspersonales | idcreditos | creditos | CASCADE |
reporteproblemasplataforma | idusuarios | usuarios | CASCADE |
transacciones | idusuarios | usuarios | CASCADE |
transacciones | idcreditos | creditos | CASCADE |
transacciones | idcuotas | cuotas | CASCADE |
transaccionescuentasclientes | idcuentas | cuentas | CASCADE |
transferencias | idusuarios | usuarios | CASCADE |
transferencias | idcuentas | cuentas | CASCADE |
transferencias | idcuentadestino | cuentas | CASCADE |
usuarios | idrol | roles | RESTRICT |
All 21 tables use ENGINE=InnoDB and DEFAULT CHARSET=utf8mb4, ensuring full referential integrity enforcement and support for the complete Unicode character set, including emoji and multi-byte Latin characters used in names and addresses.