Stored Procedures

Toda la lógica de acceso a datos en Zentto pasa por stored procedures (SQL Server) o funciones plpgsql (PostgreSQL). No existe SQL directo en el código TypeScript. Cada SP tiene su equivalente en ambos motores.

Convención de nombres

usp_[Schema]_[Entity]_[Action]

Ejemplos:
  usp_Master_Product_List       -- Listar productos
  usp_Master_Product_GetById    -- Obtener producto por ID
  usp_Master_Product_Insert     -- Insertar producto
  usp_Master_Product_Update     -- Actualizar producto
  usp_Master_Product_Delete     -- Eliminar (soft delete)
  usp_Doc_Invoice_ListByCustomer -- Listar facturas por cliente
  usp_Sec_User_ValidateLogin    -- Validar credenciales

Acciones comunes

List — listar con paginación y filtros
GetById — obtener un registro por ID
Insert — crear nuevo registro
Update — actualizar registro existente
Delete — soft delete (IsActive = 0/FALSE)
Search — búsqueda avanzada con múltiples criterios
Validate — validaciones de negocio

Patrones de salida

Patrón List (lecturas paginadas)

Las listas retornan los registros en un result set y el total de registros para paginación:

SQL Server

CREATE PROCEDURE usp_Master_Product_List
  @CompanyId   INT,
  @Search      NVARCHAR(100) = NULL,
  @PageSize    INT = 20,
  @PageNumber  INT = 1,
  @TotalCount  INT OUTPUT
AS
BEGIN
  SET NOCOUNT ON;

  SELECT @TotalCount = COUNT(*)
  FROM master.Product
  WHERE CompanyId = @CompanyId
    AND IsActive = 1
    AND (@Search IS NULL
      OR Name LIKE '%' + @Search + '%');

  SELECT
    ProductId, Name, Sku, Price,
    Stock, IsActive, CreatedAtUtc
  FROM master.Product
  WHERE CompanyId = @CompanyId
    AND IsActive = 1
    AND (@Search IS NULL
      OR Name LIKE '%' + @Search + '%')
  ORDER BY Name
  OFFSET (@PageNumber - 1) * @PageSize ROWS
  FETCH NEXT @PageSize ROWS ONLY;
END;

PostgreSQL

CREATE OR REPLACE FUNCTION
  usp_master_product_list(
    p_company_id   INT,
    p_search       VARCHAR(100) DEFAULT NULL,
    p_page_size    INT DEFAULT 20,
    p_page_number  INT DEFAULT 1
)
RETURNS TABLE(
  "ProductId"    INT,
  "Name"         VARCHAR,
  "Sku"          VARCHAR,
  "Price"        NUMERIC,
  "Stock"        NUMERIC,
  "IsActive"     BOOLEAN,
  "CreatedAtUtc" TIMESTAMP,
  "TotalCount"   BIGINT
) LANGUAGE plpgsql AS $$
BEGIN
  RETURN QUERY
  SELECT
    p."ProductId", p."Name", p."Sku",
    p."Price", p."Stock", p."IsActive",
    p."CreatedAtUtc",
    COUNT(*) OVER() AS "TotalCount"
  FROM master."Product" p
  WHERE p."CompanyId" = p_company_id
    AND p."IsActive" = TRUE
    AND (p_search IS NULL
      OR p."Name" ILIKE '%' || p_search || '%')
  ORDER BY p."Name"
  LIMIT p_page_size
  OFFSET (p_page_number - 1) * p_page_size;
END;
$$;

Patrón Write (escrituras con resultado)

Las escrituras retornan un código de resultado y un mensaje:

SQL Server

CREATE PROCEDURE usp_Master_Product_Insert
  @CompanyId  INT,
  @Name       NVARCHAR(200),
  @Sku        NVARCHAR(50),
  @Price      DECIMAL(18,2),
  @Resultado  INT OUTPUT,
  @Mensaje    NVARCHAR(500) OUTPUT
AS
BEGIN
  SET NOCOUNT ON;
  BEGIN TRY
    -- Validar SKU único
    IF EXISTS (SELECT 1 FROM master.Product
      WHERE CompanyId = @CompanyId
        AND Sku = @Sku AND IsActive = 1)
    BEGIN
      SET @Resultado = 0;
      SET @Mensaje = N'SKU ya existe';
      RETURN;
    END;

    INSERT INTO master.Product
      (CompanyId, Name, Sku, Price,
       IsActive, CreatedAtUtc)
    VALUES
      (@CompanyId, @Name, @Sku, @Price,
       1, SYSUTCDATETIME());

    SET @Resultado = SCOPE_IDENTITY();
    SET @Mensaje = N'Producto creado';
  END TRY
  BEGIN CATCH
    SET @Resultado = -1;
    SET @Mensaje = ERROR_MESSAGE();
  END CATCH;
END;

PostgreSQL

CREATE OR REPLACE FUNCTION
  usp_master_product_insert(
    p_company_id INT,
    p_name       VARCHAR(200),
    p_sku        VARCHAR(50),
    p_price      NUMERIC(18,2)
)
RETURNS TABLE(
  "ok"      INT,
  "mensaje" VARCHAR
) LANGUAGE plpgsql AS $$
DECLARE
  v_id INT;
BEGIN
  -- Validar SKU único
  IF EXISTS (SELECT 1 FROM master."Product"
    WHERE "CompanyId" = p_company_id
      AND "Sku" = p_sku
      AND "IsActive" = TRUE)
  THEN
    RETURN QUERY SELECT 0,
      'SKU ya existe'::VARCHAR;
    RETURN;
  END IF;

  INSERT INTO master."Product"
    ("CompanyId", "Name", "Sku", "Price",
     "IsActive", "CreatedAtUtc")
  VALUES
    (p_company_id, p_name, p_sku, p_price,
     TRUE, NOW() AT TIME ZONE 'UTC')
  RETURNING "ProductId" INTO v_id;

  RETURN QUERY SELECT v_id,
    'Producto creado'::VARCHAR;
EXCEPTION WHEN OTHERS THEN
  RETURN QUERY SELECT -1,
    SQLERRM::VARCHAR;
END;
$$;

Operaciones masivas con JSON

Para insertar o actualizar múltiples registros (ej. detalle de factura), se envía un arreglo JSON como parámetro:

SQL Server (OPENJSON)

-- Parámetro: @Items NVARCHAR(MAX)
INSERT INTO doc.InvoiceDetail
  (InvoiceId, ProductId, Qty, Price)
SELECT
  @InvoiceId,
  j.ProductId,
  j.Qty,
  j.Price
FROM OPENJSON(@Items)
WITH (
  ProductId INT '$.productId',
  Qty       DECIMAL(18,2) '$.qty',
  Price     DECIMAL(18,2) '$.price'
) j;

PostgreSQL (jsonb_array_elements)

-- Parámetro: p_items JSONB
INSERT INTO doc."InvoiceDetail"
  ("InvoiceId", "ProductId", "Qty", "Price")
SELECT
  v_invoice_id,
  (j->>'productId')::INT,
  (j->>'qty')::NUMERIC(18,2),
  (j->>'price')::NUMERIC(18,2)
FROM jsonb_array_elements(p_items) j;

Llamada desde la API

// web/api/src/modules/inventario/service.ts
import { callSp, callSpOut } from '../../db/query';

export async function listProducts(companyId: number, search: string, page: number) {
  const { rows, outputs } = await callSpOut(
    'usp_Master_Product_List',
    { CompanyId: companyId, Search: search, PageSize: 20, PageNumber: page },
    ['TotalCount']
  );
  return { items: rows, total: outputs.TotalCount };
}

export async function createProduct(companyId: number, data: ProductInput) {
  const { outputs } = await callSpOut(
    'usp_Master_Product_Insert',
    { CompanyId: companyId, Name: data.name, Sku: data.sku, Price: data.price },
    ['Resultado', 'Mensaje']
  );
  return { ok: outputs.Resultado > 0, id: outputs.Resultado, message: outputs.Mensaje };
}