Helpers de base de datos

Toda interacción con PostgreSQL pasa por los helpers de web/api/src/db/query.ts. Los servicios nunca abren conexiones ni escriben SQL: llaman funciones PL/pgSQL por nombre. Los helpers resuelven el pool del tenant activo, fijan el scope de empresa para RLS y normalizan filas.

callSp — llamada estándar

// Firma
callSp<T>(spName: string, inputs?: Record<string, unknown>): Promise<T[]>

// Uso
import { callSp } from '../../db/query.js';

const rows = await callSp('usp_master_product_getbyid', {
  CompanyId: 1,
  ProductId: 42,
});
const product = rows[0];  // primera fila o undefined

Internamente genera SELECT * FROM usp_master_product_getbyid($1, $2) con parámetros posicionales. El nombre se pasa a minúsculas tal cual (snake_case exacto) y se valida contra un patrón seguro — nunca se interpola un valor en el SQL.

callSpOut — con parámetros de salida

// Firma
callSpOut<T>(spName, inputs?, outputs?):
  Promise<{ rows: T[]; output: Record<string, unknown>; rowsAffected: number[] }>

// Lista paginada — TotalCount viene como columna de cada fila
const { rows, output } = await callSpOut('usp_master_product_list',
  { CompanyId: 1, Search: 'widget', PageSize: 20, PageNumber: 1 },
  ['TotalCount']
);
// output.TotalCount ← extraído de la primera fila

En PostgreSQL no hay parámetros OUTPUT: callSpOut toma los nombres pedidos (TotalCount, ok, mensaje…) de las columnas de la primera fila del resultado. Es la interfaz heredada de la época dual-DB y sigue siendo la forma estándar de leer TotalCount en listados.

Otros helpers de query.ts

Row-Level Security por request

Cuando el request tiene empresa activa, los helpers ejecutan el SP dentro de una transacción que primero fija el scope:

BEGIN;
SELECT set_config('app.current_company_id', '12', true);  -- SET LOCAL
SELECT * FROM usp_master_product_list($1, $2, $3, $4);
COMMIT;

Las políticas RLS de las tablas compartidas filtran por ese GUC. Procesos sin empresa (backoffice, cron, provisioning) usan el pool directo. El scope viene del middleware de auth vía AsyncLocalStorage — el servicio no lo pasa a mano.

Pools de conexión (pg-pool-manager.ts)

No hay un pool singleton: hay un cache de pools por base de datos (dbName → Pool), porque cada tenant Enterprise tiene BD dedicada:

• getMasterPool() — BD principal (PG_DATABASE): login, health, resolución de tenants.
• getTenantPool(config) — crea o reutiliza el pool de la BD dedicada (default max 5 conexiones por tenant).
• getPoolStats() + monitor periódico (cada PG_POOL_STATS_INTERVAL_SEC, default 30 s): si algún pool acumula waitingCount > 0, se loguea la cola (ALERT-4).
• closeTenantPool() / closeAllPools() — provisioning, borrado, shutdown.

El pool principal usa PG_POOL_MAX=40 por defecto (subido de 10 tras la alerta de saturación); se puede subir por env si el tenant lo requiere.

Resolución de tenant (tenant-resolver.ts)

resolveTenantDb(companyId, strictMode = false): Promise<TenantDbConfig>

• Consulta usp_sys_tenantdb_resolve en la BD master; cache en memoria con TTL 5 min.
• strictMode = true (request que llegó por subdominio de tenant): un fallo de resolución lanza error — jamás degrada a otra BD.
• strictMode = false (dominios conocidos como app.zentto.net): sin registro cae a la BD master (retrocompatibilidad).
• invalidateTenantCache() tras provisioning o cambios de registro.

Variables de entorno

# web/api/.env (PostgreSQL — único motor operativo)
PG_HOST=172.18.0.1        # gateway Docker en producción; localhost en local
PG_PORT=5432
PG_DATABASE=zentto_prod   # datqboxweb en local
PG_USER=zentto_app
PG_PASSWORD=****
PG_POOL_MAX=40            # default en código
PG_POOL_STATS_INTERVAL_SEC=30
DB_TYPE=postgres          # la rama sqlserver del código es legacy congelado