Skip to main content

Documentation Index

Fetch the complete documentation index at: https://mintlify.com/15aozzz/Lab-Nova-Salud/llms.txt

Use this file to discover all available pages before exploring further.

All persistent data for Botica Nova Salud lives in a single MySQL 8 database named BoticaNovaSalud_Final. The application layer never issues ad-hoc SQL against the business tables. Instead, every API endpoint calls a named stored procedure, and two triggers on the Detalle_Ventas table enforce stock rules automatically within each transaction.

​
Connection pool

The backend creates a mysql2 promise-based connection pool configured from environment variables:
db.js
const mysql = require('mysql2/promise');

const pool = mysql.createPool({
  host: process.env.DB_HOST || 'localhost',
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || '',
  database: process.env.DB_NAME || 'BoticaNovaSalud_Final',
  port: process.env.DB_PORT || 3306,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
  multipleStatements: true   // required for SPs that return multiple result sets
});
multipleStatements: true is required specifically for sp_get_venta_detalle, which returns two result sets in one call: the sale header (cabecera) and the line items (detalle).

​
Stored procedures

​
Authentication

ProcedureEndpointDescription
sp_loginPOST /api/auth/loginLooks up a user by username and returns their hashed password and profile data for JWT generation.

​
Document and series lookup

ProcedureEndpointDescription
sp_get_tipos_comprobanteGET /api/comprobantes/tiposReturns available document types (BOLETA, FACTURA, etc.).
sp_get_serie_correlativoGET /api/comprobantes/serie/:idReturns the series code and the next correlativo number for a given document type.
ProcedureEndpointDescription
sp_buscar_clienteGET /api/clientes/buscar?doc=Looks up a client by their document number.
sp_buscar_productosGET /api/productos/buscar?q=Full-text product search returning each product with its available prices.
sp_get_precios_productoGET /api/productos/:id/preciosReturns all price tiers for a specific product.

​
Sales

ProcedureEndpointDescription
sp_registrar_ventaPOST /api/ventas/registrarRegisters a complete sale. Returns OUT parameters: id_venta, serie, numero_doc, mensaje.
sp_get_venta_detalleGET /api/ventas/:idReturns two result sets: the sale header (cabecera) and its line items (detalle).

​
Dashboard

ProcedureEndpointDescription
sp_dashboard_kpisGET /api/dashboard/resumenAggregated KPI metrics (total sales, revenue, etc.).
sp_dashboard_ventas_semanaGET /api/dashboard/resumenDaily sales totals for the current week, used to render the bar chart.
sp_dashboard_alertasGET /api/dashboard/resumenProducts whose stock has fallen below the minimum threshold.
sp_dashboard_ultimos_comprobantesGET /api/dashboard/resumenThe most recent receipts/invoices issued.

​
Database triggers

Two triggers fire automatically on the Detalle_Ventas table whenever a sale line item is inserted. The application does not need to manage stock — the database handles it transactionally.

​
trg_validar_stock_antes_venta — BEFORE INSERT

Before each line item is inserted, this trigger checks whether the current stock is sufficient to fulfil the requested quantity. If stock is insufficient, the trigger raises a signal error, which aborts the entire transaction and rolls back the sale. 
Trigger: trg_validar_stock_antes_venta
Event:   BEFORE INSERT ON Detalle_Ventas
Effect:  Raises SQLSTATE '45000' if stock < quantity required.
         The full sale transaction is rolled back automatically.

​
trg_reducir_stock_post_venta — AFTER INSERT

After a line item is successfully inserted, this trigger deducts the sold quantity from stock_actual_unidades, taking the product’s unit equivalence into account. 
Trigger: trg_reducir_stock_post_venta
Event:   AFTER INSERT ON Detalle_Ventas
Effect:  Deducts (quantity × unit_equivalence) from stock_actual_unidades.

Example: selling 2 blisters of Paracetamol (10 units/blister)
         → deducts 2 × 10 = 20 units from stock

​
Sale registration flow

The following example shows the complete request and response for registering a sale: 
{
  "id_tipo_comprobante": 1,
  "numero_documento_cliente": "12345678",
  "nombres_razon_social": "Juan Pérez",
  "id_usuario": 2,
  "total": 5.00,
  "detalle": [
    {
      "id_producto": 1,
      "id_producto_precio": 1,
      "cantidad": 10,
      "precio_unitario": 0.50,
      "subtotal": 5.00
    }
  ]
}
Internally, the route handler calls CALL sp_registrar_venta(...) with the sale data. The stored procedure inserts the header and each line item, at which point:
  1. trg_validar_stock_antes_venta fires before each insert — if any line fails, the whole call is aborted.
  2. trg_reducir_stock_post_venta fires after each successful insert and deducts stock.
  3. The procedure returns id_venta, serie, numero_doc, and mensaje as OUT parameters.
To inspect or modify stored procedures and triggers, open boticanovasalud_final.sql — it contains the full database definition including all tables, seed data, procedures, and triggers.

Build docs developers (and LLMs) love

Get started for freeTalk to us