P2 · Diseño y aplicación

Publicado: semana 6 · Entrega: semana 9 · En parejas

Chasqui va a abrir nuevos servicios y el esquema actual no alcanza. Esta entrega tiene dos partes: diseñar la ampliación con el modelo E-R y la teoría de normalización, y construir encima una aplicación de línea de comandos en Node.js. Cubre las semanas 5 a 8.

1. Requisitos de la ampliación

La gerencia los dictó en una reunión; están escritos como los dijo, con ambigüedades incluidas. Parte del trabajo es encontrarlas y decidir:

• Las tarifas ya no dependen solo de la ruta: hay zonas (costa, sierra, selva) y una tabla de precios por rango de peso para cada par de zonas. Una tarifa tiene vigencia desde una fecha; la anterior no se borra.
• Cada agencia tiene empleados; algunos son conductores, con número de licencia y categoría. Cada viaje tiene un conductor titular y puede tener un copiloto.
• Un envío se paga una o más veces (un adelanto y el saldo), en efectivo, con tarjeta o por transferencia; cada medio guarda datos distintos.
• Los clientes pueden presentar reclamos sobre un envío; un reclamo tiene un estado y una historia de respuestas de los empleados.

Hoy las tarifas viven en una planilla de Excel. El área comercial la exportó tal cual a rate_sheet.sql, que crea y llena la tabla rate_sheet: una fila por par de ciudades (las de agency.city), por rango de peso y por versión de la tarifa, con las zonas de las dos ciudades, los límites del rango (min_kg, max_kg), el precio, lo que se cobra por kilo adicional (extra_per_kg) y la vigencia (valid_from, valid_to). El comentario al inicio del archivo explica cada columna. Iquitos no aparece en la planilla porque no tiene rutas; la gerencia aclara que es selva.

2. Diseño (design/)

1. El diagrama E-R de todo el esquema (el de P1 más la ampliación), en la notación de Silberschatz o en crow’s foot, exportado a PDF o PNG. Marcar claves, cardinalidades, participación total y entidades débiles.
2. design/normalization.md: las dependencias funcionales de cada tabla nueva y la verificación de que está en BCNF; si alguna queda en 3NF, el motivo. Incluir la descomposición de la tabla rate_sheet: sus dependencias funcionales, sus claves candidatas, por qué no está en BCNF, un ejemplo de cada anomalía (de actualización, de inserción y de borrado) con sus filas, y la descomposición paso a paso, mostrando que es sin pérdida y si preserva las dependencias. Una dependencia que se cumple en los datos se puede revisar con una consulta (GROUP BY del lado izquierdo, HAVING count(DISTINCT ...) > 1 del derecho); que se cumpla en estos datos no prueba que sea una regla del negocio.
3. schema/migration.sql: pasa del esquema de P1, con datos, al nuevo, en una sola transacción, sin perder datos. Las pruebas cargan los datos de P1, corren la migración y verifican el resultado (sección 4).

3. La aplicación chasqui

node chasqui.mjs <comando> [argumentos], con estos comandos:

<origen> y <destino> son agency_id (los nombres de las agencias tienen espacios). La conexión sale de las variables de entorno de PostgreSQL (PGDATABASE, PGHOST, PGUSER, …), como en top-routes.mjs.

Requisitos:

• Todas las consultas son parametrizadas. Las pruebas privadas intentan inyecciones SQL en cada argumento.
• ship, assign y deliver son transacciones: o se hace todo o nada. Usan pool.connect() y el mismo cliente para BEGIN, las sentencias y COMMIT/ROLLBACK, y liberan el cliente en un finally.
• assign es correcto aunque dos empleados lo corran a la vez sobre el mismo viaje (lo revisaremos de nuevo en la semana 13; por ahora, basta con SELECT ... FOR UPDATE sobre el viaje).
• Errores de uso o de datos (un DNI que no existe, un peso negativo): mensaje claro en stderr y código de salida distinto de 0, sin un stack trace.

Argumentos y códigos de salida

Un argumento está bien escrito si cumple su formato:

Cuando hay un error, stdout queda vacío y el mensaje va a stderr, sin stack trace. Con un error de uso conviene imprimir también cómo se usa el programa.

Qué imprime cada comando

Las salidas son texto con los campos separados por un tabulador, así se pueden comparar con un archivo y procesar con otros programas. Las horas y los meses van en hora de Lima, aunque la sesión use otra zona horaria: las pruebas corren la aplicación con la sesión en UTC. Los números salen como los devuelve PostgreSQL para las columnas de P1 (3.50, 45.07).

• customer <dni>. Un encabezado y la fila del cliente; una línea en blanco; un encabezado y sus últimos 10 envíos, como remitente o como destinatario, del más reciente al más antiguo (created_at descendente; a igual hora, shipment_id descendente). Si no tiene teléfono, phone va vacío. Si no tiene envíos, se imprime solo el encabezado de los envíos. Por ejemplo, node chasqui.mjs customer 61830975 con los datos de ejemplo empieza así:

  customer_id	dni	name	phone	city
  1	61830975	Wilmer Chávez Ríos	909975104	Ayacucho
  
  shipment_id	role	created_at	origin_agency	dest_agency	weight_kg	price	status
  8298	sender	2026-06-26 17:50	Ayacucho - Centro	Lima - La Victoria	58.50	80.15	delivered
  9956	sender	2026-05-30 19:01	Ayacucho - Centro	Lima - La Victoria	11.90	38.21	delivered
  1636	recipient	2026-05-17 00:39	Ica - Centro	Ayacucho - Centro	90.80	109.17	delivered
  ...

  name es first_name || ' ' || last_name; role es sender o recipient; created_at va con el formato AAAA-MM-DD HH:MM; origin_agency y dest_agency son los nombres de las agencias.

• quote. Solo el precio, con dos decimales: 18.50.

• ship. Solo el shipment_id del envío nuevo.

• assign. Los shipment_id asignados, uno por línea, en el orden en que se asignaron. Si no se asigna ninguno, no imprime nada (y sale con 0).

• deliver. No imprime nada.

• report <AAAA-MM>. Un encabezado y una fila por agencia, todas las agencias, ordenadas por agency_id. Los ingresos de una agencia son la suma de price de los envíos que se registraron en ella (agencia de origen) durante ese mes en hora de Lima, cualquiera sea su estado; es la misma cuenta que la vista agency_daily_revenue de P1. Una agencia sin envíos ese mes sale con 0 y 0.00. Las dos primeras líneas de node chasqui.mjs report 2026-03:

  agency_id	agency	shipments	revenue
  1	Lima - La Victoria	313	20395.34

Cómo calcula quote el precio

1. Las dos agencias existen y hay una ruta de <origen> a <destino> en route; si no, es un error de datos. Chasqui solo cotiza lo que puede llevar.
2. Cada agencia está en la zona de su ciudad.
3. El rango de peso es el que cumple min_kg < peso <= max_kg; el último rango no tiene máximo. Un envío de 5 kg está en el rango de 1 a 5 kg.
4. La tarifa vigente en una fecha es, para ese par de zonas y ese rango, la de valid_from más reciente que no pasa de la fecha. Sin fecha, la fecha es hoy en Lima. Si no hay ninguna (una fecha anterior a todas las tarifas), es un error de datos.
5. El precio es round(price + extra_per_kg * (peso - min_kg), 2), con el round de PostgreSQL. extra_per_kg es 0 en todos los rangos menos el último.

Por ejemplo, 45.5 kg de Lima a Trujillo (costa a costa) con la tarifa de 2026: round(29.00 + 0.75 * (45.5 - 30), 2) = 40.63.

ship, assign y deliver en detalle

• ship busca a los dos clientes por DNI y calcula el precio como quote para hoy. Inserta el envío en estado registered, sin viaje, sin declared_value, con created_at igual a la hora actual, y su evento número 1, received, en la agencia de origen y con la misma hora que created_at. Dos ship a la vez registran dos envíos distintos (las pruebas lo hacen): max(shipment_id) + 1 no sirve, por eso la migración le da a shipment_id un valor por defecto (sección 4).
• assign solo acepta un viaje que todavía no sale (departure_at posterior a ahora). Un envío está pendiente si está registered (y por lo tanto sin viaje) y tiene el mismo origen y destino que la ruta del viaje. Recorre los pendientes en orden de llegada (created_at, y a igual hora, shipment_id) y asigna cada uno que cabe en lo que le queda al viaje (capacity_kg menos la carga que ya lleva); el que no cabe se salta y sigue pendiente, y se sigue con el siguiente. Un envío asignado queda con el trip_id del viaje y en estado in_transit (la regla 3 de P1 no permite otra cosa). Si otra transacción tiene bloqueado un envío pendiente, assign lo espera; no se lo salta (con SKIP LOCKED, por ejemplo), porque se rompería el orden de llegada. assign no registra eventos: el departed se registra cuando el viaje sale, y eso no es parte de la aplicación.
• deliver solo acepta un envío in_transit cuyo viaje ya llegó (arrival_at no es NULL y no es posterior a ahora). Lo pasa a delivered con delivered_at igual a la hora actual y agrega su evento delivered, con el número de secuencia siguiente, en la agencia de destino y a la misma hora.

4. La migración: qué tiene que dejar

make test aplica la migración así: carga los datos de ejemplo, aplica schema/constraints.sql y schema/views.sql de P1, carga rate_sheet.sql y corre schema/migration.sql con psql --single-transaction -v ON_ERROR_STOP=1. Es una sola transacción: si algo falla, no queda nada de la migración y make test se detiene. Por eso migration.sql no lleva BEGIN ni COMMIT.

Las pruebas de P0 y de P1 corren después de la migración, sobre el esquema nuevo. Así que la migración:

• no borra ni modifica ninguna fila de las tablas de P1, y no les quita ni les cambia el nombre a sus columnas;
• deja en pie las restricciones, los triggers y las vistas de P1;
• si agrega columnas a una tabla de P1 (por ejemplo, el conductor de un viaje), tienen que aceptar que no se las den, con NULL o con un DEFAULT: las pruebas de P1 y de P2 insertan clientes, viajes y envíos solo con las columnas de partida. Qué significa un viaje de P1 sin conductor es una de las ambigüedades que tienen que resolver;
• le da a shipment_id un valor por defecto que sirva con varios ship a la vez y que empiece después del mayor shipment_id de los datos. Las pruebas de P1 siguen insertando envíos con shipment_id explícito, y eso también tiene que funcionar.

Además, la migración:

• crea las tablas de la ampliación, cada una con su clave primaria;
• pasa los datos de rate_sheet a las tablas nuevas y borra rate_sheet;
• crea dos vistas, que son lo único del diseño nuevo que miran las pruebas (el resto lo mira la revisión). Si alguna tabla suya se llama igual, cámbienle el nombre a la tabla.

rate_card tiene una fila por tarifa distinta de la planilla: por par de zonas, rango y valid_from, también las que ya no rigen. Los tipos de las columnas los eligen ustedes; las pruebas los convierten antes de comparar.

README

El diagrama, las ambigüedades que encontraron en los requisitos y cómo las resolvieron, y cómo probaron la migración.

Archivos

Los archivos de P0 y de P1 se quedan en la misma carpeta, como antes.

Pruebas

make test corre las pruebas de P0, P1 y P2. El Makefile es el de P1 con más pasos:

1. crea de nuevo la base de datos de pruebas con los datos de ejemplo;
2. aplica schema/constraints.sql y schema/views.sql, carga rate_sheet.sql y aplica schema/migration.sql en una sola transacción, todo con psql -v ON_ERROR_STOP=1: si un script falla, make test se detiene ahí y no corre ninguna prueba;
3. copia esa base de datos en una segunda, chasqui_test_app. ship, assign y deliver cambian datos, y las pruebas de P0 y P1 comparan los datos de la primera con expected/: por eso la aplicación corre sobre la copia;
4. corre todos los *.test.mjs, uno detrás de otro: las pruebas de P0, de P1 y de P2, y las suyas.

Las pruebas de P2 corren node chasqui.mjs como lo haría un usuario y miran la salida, el código de salida y lo que quedó en la base de datos. Para revisar que una transacción no deje nada a medias, simulan una falla en la mitad (con un trigger temporal que hace fallar un INSERT o un UPDATE) y comparan una huella de todas las tablas antes y después. Para revisar la concurrencia, lanzan dos procesos a la vez y los hacen chocar: mientras una transacción de la prueba tiene un lock, los dos se quedan esperando, y al soltarlo siguen al mismo tiempo. Si sus pruebas propias corren comandos que cambian datos, que lo hagan en la copia (su nombre está en la variable CHASQUI_APP_DB) y con viajes y envíos que insertan ellas mismas, como las pruebas públicas: la copia no se vuelve a crear entre un archivo de pruebas y el siguiente.