PostgreSQL — motor único

Zentto opera sobre un solo motor de base de datos: PostgreSQL 16, en desarrollo y en producción. La lógica de datos vive en funciones PL/pgSQL y los cambios de esquema se aplican con migraciones Goose. No hay SQL directo en TypeScript.

Nota histórica: la arquitectura dual

Hasta julio de 2026 el ERP mantenía paridad entre SQL Server (T-SQL, sqlweb/) y PostgreSQL (sqlweb-pg/), con la variable DB_TYPE como switch de motor. Ese esquema se retiró: duplicaba el esfuerzo de cada cambio y el 100% de los despliegues (producción, dev, tenants) ya corría en PostgreSQL. Lo que queda de aquella etapa:

El SQL Server DELLXEONE31545 (BD DatqBoxWeb) queda solo como referencia de lectura del sistema legacy VB6 — hoy vive en la VM 102 del centro de datos dev y es la fuente de la que migra clientes el Zentto Migrador.

Flujo de un cambio de BD

Todo cambio de base de datos sigue el mismo camino, sin excepciones:

1. Migración Goose en web/api/migrations/postgres/NNNNN_descripcion.sql — DDL, ALTER, seeds y también el CREATE OR REPLACE FUNCTION del SP nuevo/modificado.
2. Espejo de la función en web/api/sqlweb-pg/includes/sp/usp_*.sql — para que la definición completa quede versionada y las BDs nuevas se provisionen con run-functions.sql.
3. Deploy: el CI ejecuta goose up sobre la BD principal y después sobre cada BD dedicada de tenant (ver Backoffice y multi-tenant).

-- web/api/migrations/postgres/NNNNN_mi_cambio.sql
-- +goose Up
ALTER TABLE master."Product" ADD COLUMN IF NOT EXISTS "Brand" VARCHAR(100);

DROP FUNCTION IF EXISTS usp_master_product_list(INT, VARCHAR, INT, INT);
CREATE OR REPLACE FUNCTION usp_master_product_list(...)
RETURNS TABLE(...) LANGUAGE plpgsql AS $$ ... $$;

-- +goose Down
-- reverso mínimo (el mirror de sqlweb-pg NO debe tomarse del bloque Down)

Ejecución local y en producción

# Local (BD datqboxweb)
cd web/api
goose -dir migrations/postgres postgres "host=localhost dbname=datqboxweb user=postgres" up

# Recrear funciones completas (idempotente)
psql -U postgres -d datqboxweb -f sqlweb-pg/run-functions.sql

# Producción: lo hace el CI (job migrate-pg del deploy) — nunca a mano

Reglas y trampas de PL/pgSQL que ya nos costaron bugs

• Nombres en snake_case exacto al llamar: callSp() solo pasa a minúsculas, no convierte camelCase — el nombre en la API debe coincidir con el de la función.
• Literales con cast: en RETURN QUERY SELECT, los strings van con 'texto'::VARCHAR — sin cast, PG infiere TEXT y no coincide con el RETURNS TABLE.
• p_param IS NULL necesita CAST cuando el driver manda el parámetro como NULL sin tipo: (p_search IS NULL OR ...) falla con "could not determine data type" — castear en la llamada o en la condición.
• Ambigüedad en RETURNS TABLE: las columnas de salida son variables dentro del cuerpo; toda columna de tabla homónima debe llevar alias (p."Name", no "Name").
• Cambiar el tipo de retorno exige DROP FUNCTION antes: CREATE OR REPLACE no puede cambiar el RETURNS (error 42P13).
• Upserts sobre tablas con soft-delete: el ON CONFLICT debe excluir (o manejar explícitamente) los registros con IsDeleted — un upsert ingenuo revive registros borrados.

Checklist para cambios de BD

1. Migración goose nueva en migrations/postgres/ (número verificado contra developer)
2. Función espejo actualizada en sqlweb-pg/includes/sp/
3. Probada contra PostgreSQL local (o una BD desechable)
4. Contrato OpenAPI actualizado si cambia un endpoint
5. Tipos TypeScript de la API actualizados
6. Cero T-SQL en el diff