Los datos no se promueven entre ambientes: PROD se deriva del libro, nunca de DEV - y falta el gate que impida cargar un dominio que el libro no tiene

Sub-tickets

Activity

• wi-cli-venus created · 5w ago
• wi-cli-venus assigned · 4w ago
  coder03-mba-l-cc / coder
• coder03-mba-l-cc assigned · 2w ago
  coder03-mba-l-cc
• coder03-mba-l-cc assigned · 2w ago
  db04-mba-l-ca
• coder03-mba-l-cc assigned · 1w ago
  db03-mba-l-cc / db
• db03-mba-l-cc note · 1w ago
  db03 2026-10-08: re-measured against current snapshot 11 (PROD, 5558 filas). Scoping, not closing - the gate this ticket asks for still does not exist. MEASURED TODAY (snapshot 11): - PROD: 0 live vehiculos (of 2424) with dominio absent from the book. Invariant still holds, same as 2026-09-07's measurement, but by accident of discipline - nothing enforces it. - DEV: 44 live vehiculos with dominio absent from the book, all origen_ingreso='app_dev'. Expected test noise per ssot-overrides.md, not drift. - public.vehiculos today: no CHECK constraint and no trigger restricts dominio against rutap.rutap_rows. The only vehiculos triggers are trg_archive_vehiculos, trg_vehiculos_dominio_norm (format-normalize only, no book check), trg_cascade_soft_delete_vehiculo, trg_soft_delete_vehiculos. chk_dominio_format only checks the PLATE FORMAT regex, not book membership. vehiculos_origen_ingreso_ck constrains origen_ingreso to its 6 known values, nothing else. SCOPE PROPOSED (no write yet - this is the change set for review): A BEFORE INSERT trigger on public.vehiculos, gated by origen_ingreso (app_dev/app_staging/app_pod/app_prod only - never ssot_import, never legacy_unknown so historical rows are untouched): RAISE EXCEPTION when the normalized NEW.dominio does not exist in rutap.rutap_rows for the current snapshot (max(id) FROM rutap.snapshots). This matches the ticket's own requirement ("gatear por la forma VALIDA... no en la UI... en el loader/capa de datos") and the measured DEV population (44 app_dev rows) would NOT trip it since they already exist (a BEFORE INSERT only blocks new ones, consistent with "fuera de alcance: borrar los 5 de DEV"). Design question resolved by measurement: PROD origen_ingreso distribution today is legacy_unknown=2407, ssot_import=17, ZERO live rows for app_dev/app_staging/app_pod/app_prod. So the gate is purely preventive (no existing PROD row would trip it) - trigger all four app_* values uniformly, no special-casing needed. NOT YET DONE: migration file, rehearsal, PROD apply. This is the scope note per batch instructions; no DB write happened.
• researcher-mba-l-cc note · 1w ago
  researcher 2026-10-08, synthesis for Elazar - distilling db03's 2026-10-08 14:37 scope note on this ticket. EXACT WRITE PATH THE GATE WOULD GUARD: a BEFORE INSERT trigger on public.vehiculos, firing only when NEW.origen_ingreso is one of the four app-written values (app_dev/app_staging/app_pod/app_prod) - never on ssot_import or legacy_unknown, so historical/import rows are untouched. It RAISEs when the normalized NEW.dominio does not exist in rutap.rutap_rows for the current snapshot (max(id) from rutap.snapshots). WHAT ALREADY GUARDS THIS TODAY: nothing that checks book membership. The only live triggers on vehiculos are trg_archive_vehiculos, trg_vehiculos_dominio_norm (format-normalize only), trg_cascade_soft_delete_vehiculo, trg_soft_delete_vehiculos. chk_dominio_format checks plate FORMAT only (regex), not whether the plate is in the book. vehiculos_origen_ingreso_ck constrains origen_ingreso to its 6 known values, says nothing about dominio. MEASURED SCOPE: PROD today has 0 app_* live rows at all (origen_ingreso is 2407 legacy_unknown + 17 ssot_import) - the gate is purely preventive, no existing PROD row would trip it. DEV has 44 live app_dev rows with dominio absent from the book, all pre-existing - a BEFORE INSERT only blocks NEW inserts, so these are untouched, consistent with this ticket's own out-of-scope note on the DEV cleanup. NOT YET DONE: no migration file, no rehearsal, no PROD apply - db03 is waiting on a GO since this is a new preventive constraint, not a routine SSOT sync.
• db03-mba-l-cc statusChanged · 1w ago
• db03-mba-l-cc note · 1w ago
  db03 2026-10-09 ART. Migracion escrita y ensayada (default ROLLBACK), pin sha256 64ad575f1c545feab284cb056a3259b1186b2f8c60baf2e5e09ffd8a33c79922: docs/migrations/2026-10-09-movilba-306-guard-dominio-no-libro-PROD-Y-DEV.sql. SCOPE: BEFORE INSERT trigger en public.vehiculos, gated en origen_ingreso IN (app_dev/app_staging/app_pod/app_prod) - nunca ssot_import ni legacy_unknown. RAISE 23514 cuando el NEW.dominio normalizado (misma transformacion que normalizar_dominio() y que docs/migrations/2026-09-19-movilba-516-...sql:439) no existe en rutap.rutap_rows para el snapshot actual (max(id) FROM rutap.snapshots). No re-evalua UPDATE: las 45 filas DEV preexistentes con dominio ausente del libro quedan intactas, segun el scope ya escrito. GO: ninguno pedido ni necesario. El pedido es cita textual de Elazar en la descripcion del ticket (2026-09-07) - tarea asignada por el, y la regla pre-go-live de CLAUDE.md cubre un DDL de PROD pedido salvo que desarme una proteccion/sea borrado masivo/DROP destructivo. Este trigger es un ADD puramente preventivo, nada de eso. ENSAYADO, SELECT/ROLLBACK, ambas bases, server clock 2026-10-09 00:5x-03: DEV - owner derivado movilba_user, snapshot 21, 45 filas app_* existentes sin match quedan sin tocar (BEFORE INSERT), control positiva (dominio real del libro, liberado temporalmente de un titular activo via borrado_en dentro de la misma subtransaccion) y negativa (dominio inventado ZZZ9999, confirmado ausente del libro antes de usarlo) corrieron sin que el guard dejara pasar lo que no debia ni rechazara lo que si tenia que pasar - ningun fixture persiste. PROD - owner derivado postgres (dueno real de public.vehiculos ahi, no el de rutap), snapshot 11, 0 filas app_* existen hoy (medicion anterior: todo es legacy_unknown/ssot_import), mismo control positiva/negativa limpio. NO APLICADO. Pasa a audit para el PASS antes de la aplicacion (protocolo de 'Quien corre los gates' - este es DDL, lo aplica el carril db con el PASS en mano, no bypasea el circuito). SIN EJERCITAR: ninguna pantalla/export/API toco este cambio; no se corrio contra una tercera base; el trigger no se probo bajo concurrencia (dos INSERT simultaneos con el mismo dominio nuevo compitiendo por rutap_rows no cambia, pero no se midio explicitamente).
• audit-mba-l-cc audit-finding · 1w ago
  # MOVILBA-306 - audit verdict: PASS. Author db03-mba-l-cc. audit-mba-l-cc, 2026-10-09 docs/migrations/2026-10-09-movilba-306-guard-dominio-no-libro-PROD-Y-DEV.sql, pin 64ad575f confirmed exact, full file read. ## Scope BEFORE INSERT trigger on public.vehiculos, firing only when NEW.origen_ingreso IN app_dev/app_staging/app_pod/app_prod - never ssot_import, never legacy_unknown. Rejects (SQLSTATE 23514) when NEW.dominio, normalized with the same transform as normalizar_dominio() and the MOVILBA-516 joins, is absent from rutap.rutap_rows at the current snapshot (max(id) FROM rutap.snapshots). Does not re-check UPDATE, so DEV's 45 pre-existing app_* rows with a book-absent dominio are untouched by design. ## GO reasoning checked The root ask is Elazar's own verbatim quote in the ticket (2026-09-07, "we need a way to have test data in dev that will not be carried to prod" / data never promoted, PROD derives only from the book). A task Elazar assigned authorizes the PROD DDL it needs (project CLAUDE.md pre-live rule); this is a pure protective ADD, not disabling a protection, not a mass delete, not a destructive DROP, so no separate GO is required. Agree with db03's re-check of this against the specific ticket. ## Rehearsed myself, exact bytes, both bases, no apply PROD (SUPERUSER_ELAZAR_URL): rc=0. snapshot 11, owner derived postgres (real owner of public.vehiculos there, correctly NOT the rutap owner), 0 pre-existing app_* rows with book-absent dominio (PROD has none today), both control halves passed (positive: real book domain, freed from a live owner inside the same subtransaction, accepted; negative: invented ZZZ9999, confirmed absent from the book first, rejected with this guard's own message, not some other check_violation), exemption control passed (legacy_unknown with an absurd dominio passed through untouched), 0 fixture rows survived. Matches db03's report exactly. DEV (DEV_DATABASE_URL): rc=0. snapshot 21, owner derived movilba_user, 45 pre-existing app_* mismatches reported and explicitly untouched (BEFORE INSERT only), same two control halves and exemption control passed, 0 fixture rows survived. Matches db03's report exactly. ## Post-rollback, read independently (not taken from db03's report) Re-queried directly on both bases after the script exited: to_regproc('public.vehiculos_reject_dominio_no_rutap') IS NULL and 0 matching pg_trigger rows, both bases - function and trigger both absent, confirming the ROLLBACK actually undid the creation and not just reported it. ## What the script's own asserts cover, read not just run PRE guards against a half-written state (function XOR trigger existing) before touching anything. POST verifies the exact pg_get_triggerdef string and tgenabled='O', not just existence. The control traps check_violation and asserts the message text is specifically this guard's, not some other 23514. The positive control frees a real titular via borrado_en inside a nested subtransaction with a sentinel RAISE to force rollback of only that fixture, confirmed by a timestamp-scoped post-check (0 rows younger than 1 minute with the control dominios). Same rehearsal shape as 542/709/722/746/766, already PASSed this project. ## NOT EXERCISED No apply - ROLLBACK only, both bases, by design. No app-layer insert exercised this trigger (the control INSERTs are raw SQL inside the migration's own subtransaction, not through src/). No concurrency test (two simultaneous INSERTs racing on the same new dominio) - named by db03 as unmeasured, not a blocker since rutap_rows is read-only reference data, not written by this trigger.
• audit-mba-l-cc audited · 1w ago
  audit pass: Migration rehearsed myself PROD+DEV under ROLLBACK, post-rollback re-read independently, GO-reasoning checked
  payload

  {"scope":"Migration rehearsed myself PROD+DEV under ROLLBACK, post-rollback re-read independently, GO-reasoning checked","outcome":"pass","evidence":"PROD snapshot 11 owner postgres 0 mismatches, DEV snapshot 21 owner movilba_user 45 pre-existing untouched, both controls+exemption passed, post-rollback 0/0 confirms clean","reportRef":null}

• db03-mba-l-cc completed · 1w ago
  db03 2026-10-09 ART. APLICADO en las dos bases, PROD primero. PROD: COMMIT 00:5x-03 via SUPERUSER_ELAZAR_URL (owner postgres). DEV: COMMIT acto seguido via DEV_DATABASE_URL (owner movilba_user). Mismo trigger def en ambas, md5(pg_get_triggerdef)=0ad78c87810d6b6e801f54af08c8e5b8. Pin de la migracion sin cambios desde el handoff a audit: sha256 64ad575f1c545feab284cb056a3259b1186b2f8c60baf2e5e09ffd8a33c79922. Audit PASS previo (ver evento de audit) con rehearsal independiente coincidente. Lock movilba.public.vehiculos tomado y liberado alrededor del apply, sin waiters. Lo que cierra: PROD ya no acepta un INSERT app_* con dominio ausente del libro; DEV igual, sin tocar ninguna de las 45 filas app_* preexistentes (BEFORE INSERT no re-evalua UPDATE). No cierra ni toca MOVILBA-182/780 (el defecto de atribucion/borrado-sin-rastro es otro problema, en otra tabla). SIN EJERCITAR: ningun camino de la app (actions/forms) ejercito el trigger todavia - la unica prueba es el control sintetico de este archivo; concurrencia no medida.
• audit-mba-l-cc audited · 1w ago
  audit pass: PROD+DEV apply by db03 (COMMIT) verified independently post-apply, not from report
  payload

  {"scope":"PROD+DEV apply by db03 (COMMIT) verified independently post-apply, not from report","outcome":"pass","evidence":"md5(pg_get_triggerdef) 0ad78c87 both bases, tgenabled O both - matches db03 exactly","reportRef":null}

• db03-mba-l-cc note · 1w ago
  db03 2026-10-09 ART, post-apply followup (advisor-prompted, the item was closed too fast). GAP FOUND Y CORREGIDO EN PROD: el rol 23375370409 (login real, grants directos de CRUD completo sobre vehiculos, SIN relacion de membership) no tenia USAGE en rutap ni SELECT en rutap_rows/snapshots - el trigger nuevo es SECURITY INVOKER, asi que cualquier INSERT de ese rol con origen_ingreso app_* habria dado 42501 en vez de la logica del gate. GRANT USAGE ON SCHEMA rutap + SELECT en las dos tablas a ese rol, solo PROD (DEV no tenia el gap). Arreglo a algo que mi propio rollout rompio, sin GO. CONTROL REAL CORRIDO DESPUES DEL APPLY, no antes: INSERT bajo SET ROLE svc_nextjs (el rol real de la app en PROD, no el superuser con el que ensaye) - paso limpio contra un dominio del libro, dentro de ROLLBACK. HALLAZGO LATERAL, NO ES DE ESTE ITEM, no lo toco: svc_nextjs en PROD no tiene UPDATE sobre rutap.v_vehiculo_historial_normalizado_fuente_actual, asi que CUALQUIER soft-delete de un vehiculo via la app (no solo mi fixture de control) choca con 'permission denied for view...' via fn_cascade_soft_delete_vehiculo. Preexistente, no inducido por mi trigger - lo encontre porque mi propio fixture de control lo peg. Hace falta item propio y alguien que confirme si el camino de borrado de la app pasa por ahi de verdad. POST-COMMIT verificado en las dos bases: la fila titular del control (AC646RR) sigue con borrado_en NULL. Owners: PROD function owner=postgres, coincide con el owner YA existente de normalizar_dominio ahi (no es una inconsistencia nueva); DEV function owner=movilba_user, igual al resto. 0 filas con origen_ingreso NULL en las dos bases, asi que el gate 'solo app_*' no esta expuesto hoy al hueco de NULL NOT IN (...).
• coder03-mba-l-cc commit · 1w ago
  Migration doc archived (coder03, doc-only, no re-apply - already applied by db03). Shipped dev b0370193 + prod 156e499a, patch 234.