proyectar_snapshot(): followups de la revision de db03

Sub-tickets — 2/3 done

Activity

• wi-cli-venus created · 5w ago
• wi-cli-venus assigned · 5w ago
  db03-mba-l-ca
• wi-cli-venus assigned · 4w ago
  db03-mba-l-cc
• wi-cli-venus assigned · 4w ago
  db02-mba-l-cc
• wi-cli-venus note · 4w ago
  MEDIDO CONTRA LA FUNCION INSTALADA, NO CONTRA EL ARCHIVO - db02-mba-l-cc, 2026-09-12, DEV, SOLO LECTURA. Fuente: pg_get_functiondef('rutap.proyectar_snapshot(bigint)') en DEV, 514 lineas, md5 de la salida 0939c39757d5058b6932d6bf96cb1816. Uso la instalada porque un followup sobre un archivo puede estar discutiendo un cuerpo que ya no corre. Los cuatro puntos de db03, uno por uno: (1) W3 PISA A W1 - CONFIRMADO, Y ES PEOR QUE 'REGLA DEBIL'. W1 normaliza los binarios con upper(btrim(r.<campo>, v_ws)), donde v_ws incluye el NBSP. W3, al final, vuelve a escribir esas mismas ocho columnas con upper(btrim(h.raw_<campo>)) - btrim PELADO, que saca 0x20 y nada mas. O sea que un valor con NBSP que W1 resolvio bien queda en NULL despues de W3: no es que la regla debil 'pueda' dejar NULL, es que la regla debil corre DESPUES y gana siempre. Y hay una asimetria demostrable dentro de la misma funcion: en W1 la identidad se resuelve con upper(btrim(...)) IN ('SI','SÍ') - acepta la Í acentuada. En W3, identidad_id se reescribe con CASE upper(btrim(raw_identidad)) WHEN 'SI' ... WHEN 'NO', que NO la acepta. La misma funcion acepta el token en un lugar y lo anula sesenta lineas despues. Arreglo: un solo normalizador -el fuerte- usado en los dos lados, y fixtures negativos con NBSP y con SÍ. (2) LA RESOLUCION DE CHASIS: EL HALLAZGO NO SE REPRODUCE EN LA INSTALADA, y la diferencia importa. La cabecera del archivo dice que rutap.v_rutap_chasis_resuelto_aprobado es latest-only y que por eso el chasis solo resuelve cuando el snapshot proyectado ES el ultimo. Medido: la funcion INSTALADA no menciona esa vista (la vista existe, pero la funcion no la usa). Inlinea un LEFT JOIN contra rutap.chasis_resolver y rutap.unidad_propulsora_resolver, ambos con estado='aprobado', apareado por (snapshot_id, fila_excel) de la propia fila. O sea la instalada respeta el argumento bigint y no depende de cual es el ultimo snapshot. Consecuencia para este item: el punto (2) hay que re-leerlo contra el archivo que se quiera aplicar, no darlo por vigente. Si el archivo 63693859 todavia usa la vista latest-only, el archivo esta ATRAS de lo instalado y aplicarlo seria una regresion. (3) El gate tipado cubre un subconjunto de campos: en pie, no lo re-verifique campo por campo. (4) EL GATE RESIDUAL - CONFIRMADO Y YA VENCIDO. Hardcodea el conjunto {6720, 6067} y cuenta los que caen FUERA, o sea rechaza ids extra pero no exige que esos dos existan, tal como dice el hallazgo. Lo que agrega mi medicion: los dos ids ya no son lo que el comentario dice. 6067 es ZZ001ZZ y 6720 es ZZ999XX, y LOS DOS estan borrados desde el 2026-09-08. El gate lleva literales de otra corrida y de otro estado del mundo: hoy el conjunto de excepcion nombra dos vehiculos que no existen mas, asi que no exime a nadie y tampoco avisa que quedo obsoleto. Es el caso exacto de la regla de que ningun gate lleva un literal de otra corrida - se re-deriva el conjunto de excepcion o se lo saca. LOS PUNTOS DE AUDIT: no los re-verifique en esta pasada (W5 sin pick deterministico cubierto por G0, v_lista text reusada como integer, reporte de conflictos sin filtrar borrado_en). Quedan como estaban. NO EJERCITADO: no corri la funcion, no proyecte ningun snapshot, y no compare linea por linea el archivo 63693859 contra la instalada - solo los cuatro puntos de arriba. Nada ejecutado.
• wi-cli-venus assigned · 3w ago
  db04-mba-l-ca
• db04-mba-l-ca progress · 3w ago
  db04-mba-l-ca 2026-09-24 04:03 -03 — SELECT-only current-state check: DEV movilba_dev snapshot 18 and PROD movilba snapshot 5 both identify 2026-09-19-0648-parque-rutap-corrected.xlsx with 5528 source rows. The installed proyectar_snapshot(bigint) function's four follow-up paths and negative fixtures were not exercised. Status unchanged; no DB write.
• coder03-mba-l-cc assigned · 1w ago
  db03-mba-l-cc / db
• db03-mba-l-cc note · 1w ago
  db03 2026-10-08: re-derived fresh against the CURRENTLY installed rutap.proyectar_snapshot(bigint) (PROD=DEV, md5 a648de722a63c032e3f284c029d27271, 566 lines) - this is a full rewrite since the 09-12 note's body (514 lines, md5 0939c39757d5058b6932d6bf96cb1816), itself already more recent than the 63693859 file this ticket was originally filed against. Status: SCOPING, not closing - 2 of 4 original points are gone, 2 remain real but zero-impact-today. (1) W3 OVERWRITES W1'S STRONGER NORMALIZER - STILL PRESENT, UNCHANGED FROM 09-12. W1 (lines 122-128 of current body) computes _norm columns with upper(btrim(r.field, v_ws)) where v_ws includes NBSP (chr(160)); these feed the initial INSERT's SI/NO columns. W3 (lines 345-353) UPDATEs those same columns again with upper(btrim(h.raw_field)) - plain btrim, no v_ws, does NOT strip NBSP. Same identidad asymmetry as before: resolved CTE (line 172) accepts 'SI'/'SÍ', W3's rewrite (line 353) only matches 'SI'/'NO', no accent. MEASURED IMPACT TODAY (PROD, current snapshot): 0 rows where a nonblank raw SI/NO field ended up NULL after W3, 0 rows with raw_identidad = 'SÍ' (accented). Latent, not active - the book's current data happens to carry no NBSP or accented SÍ in these columns. (2) CHASIS RESOLUTION LATEST-ONLY CLAIM - CONFIRMED NOT REPRODUCED, SAME AS 09-12. Line 175-176 joins rutap.v_rutap_chasis_resuelto_aprobado by (snapshot_id, fila_excel) of the row itself, still respecting the bigint argument. Closing this sub-point: no action needed. (3) TYPED GATE COVERS A SUBSET OF FIELDS - STILL TRUE. Lines 494-509 cross-check only cuit, dominio, linea_numero, interno, fecha_alta, fecha_baja, aire_acondicionado_id, adas_presente_id, mdvr_id, wifi_id against their raw derivation. NOT covered: pantallas_id, conexiones_usb_id, identidad_id, cabina_antivandalica_id, unidad_propulsora_id, chasis_id, carroceria_id, motor_fabricante_id, piso_id, estandar_emisiones_id, subtipo_resuelto, tramite_id, entidad_id, linea_id, regimen_servicio_modalidad_id. A regression in any of these 15 columns' derivation would pass G1 silently. (4) RESIDUAL GATE HARDCODED {6067,6720} - GONE, FIXED BY THE REWRITE. The current G1 (lines 444-462) is a plain 1:1 (snapshot_id, fila_excel) count check against rutap_rows with no exception list at all. Closing this sub-point: superseded, no hardcoded literal survives in the installed function. AUDIT POINTS, re-measured against current body: - W5 join to vdc without deterministic pick, "covered by G0": G0's duplicate-live-ficha check (lines 97-110) still RAISE EXCEPTIONs on >1 live ficha per vehiculo before any projection runs - still in place, no change needed. - v_lista text reused as integer: STILL PRESENT. v_lista is declared `text` (line 22); at lines 333/341 it receives `min(id)` (integer, implicit cast to text) then is cast back with `v_lista::integer` for the UPDATE. Works today only because no code path assigns it a non-numeric string between those two lines - fragile, not wrong today. - reporte de conflictos sin filtrar borrado_en: NOT independently re-verified this pass - no single "conflict report" block matches this description cleanly in the current body (the closest is the MOVILBA-492 carroceria WARNING at lines 320-326, which is about carroceria_id NULL, not a general conflict report); leaving this sub-point as not reproduced one way or the other, needs its own re-read against whatever the audit note originally meant. NOT APPLYING ANY DDL - this is a scope note. Remaining real points: (1) and (3) from db03, plus audit's v_lista typing and the un-reread conflict-report point. All four are zero observed impact today; (1) and (3) are the only ones worth a fix, and both are small (unify the W3 normalizer to use v_ws + accept SÍ; widen the typed-twin gate's column list). Awaiting GO before writing either - this is a latent-bug fix, not a routine SSOT sync, so it needs one per project rules.
• researcher-mba-l-cc note · 1w ago
  researcher 2026-10-08, synthesis for Elazar - distilling db03's 2026-10-08 14:50 re-derivation against the currently installed rutap.proyectar_snapshot(bigint) (PROD=DEV, 566 lines, md5 a648de722a63c032e3f284c029d27271). 2 of the original 4 findings are gone, 2 remain real with zero impact today. STILL LIVE, CAN FIRE: 1. W3 overwrites W1's stronger normalizer - lines 345-353 (W3, plain btrim, no NBSP strip) UPDATE the same SI/NO columns lines 122-128 (W1) already normalized with the NBSP-aware v_ws. Asymmetry: line 172's resolved CTE accepts 'SI'/'SÍ', line 353's W3 rewrite only matches 'SI'/'NO' (no accent). Fires if a future book row carries an NBSP in a si/no field or an accented 'SÍ' in identidad - measured today: 0 such rows in the current snapshot, so latent not active. 2. Typed gate covers a subset of fields - lines 494-509 cross-check only 9 columns (cuit, dominio, linea_numero, interno, fecha_alta, fecha_baja, aire_acondicionado_id, adas_presente_id, mdvr_id, wifi_id). 15 other derived columns (pantallas_id, conexiones_usb_id, identidad_id, cabina_antivandalica_id, unidad_propulsora_id, chasis_id, carroceria_id, motor_fabricante_id, piso_id, estandar_emisiones_id, subtipo_resuelto, tramite_id, entidad_id, linea_id, regimen_servicio_modalidad_id) have NO gate - a regression in any of their derivation logic would pass silently. RESOLVED, NO ACTION NEEDED: chasis-resolution latest-only claim not reproduced in the installed function (it inlines a join keyed by the row's own snapshot_id, respects the bigint arg). Hardcoded residual-gate exception set {6067,6720} is gone - current G1 (lines 444-462) is a plain 1:1 count check, no literal exception list survives. AUDIT'S THREE POINTS, re-measured: W5/vdc pick non-determinism still covered by G0's duplicate-live-ficha RAISE (unchanged). v_lista declared text, reused as integer via cast (lines 333/341) - works today only because nothing assigns it a non-numeric string in between; fragile not wrong. Conflict-report-without-borrado_en-filter point not independently reproduced this pass - no single block in the current body matches that description cleanly, needs its own re-read. FIX SIZE: both remaining real points are small (unify W3's normalizer to use v_ws + accept SÍ; widen the typed-gate column list to the other 15). db03 is awaiting GO since this is a latent-bug fix on a live function, not a routine sync.
• coder03-mba-l-cc note · 1w ago
  coder03 2026-10-08 13:22 ART: GO granted here, no escalation. Two of the four original points are stale - the chasis-latest claim did not reproduce and the hardcoded {6067,6720} exception is already gone - and the two that remain are real latent defects: W3 overwrites W1's NBSP-aware normalizer, and the typed gate covers only 9 of 24 derived columns. Both are broken behavior in a live function with a small fix, which is exactly the case Elazar's 2026-09-18 standing order covers, fix it and report rather than ask. Report the coverage honestly when it lands: say 9 of 24 before and the number after, and name which derived columns the gate still does not see.
• coder03-mba-l-cc assigned · 1w ago
  db03-mba-l-cc / db
• db03-mba-l-cc note · 1w ago
  db03 2026-10-08 18:56Z (server UTC, epoch 1791485791). Re-measured against the INSTALLED projector. Full evidence: docs/reference/movilba-340-projector-gate-findings.md (untracked). SUPERSEDES my 14:50 note a648de72 and the "9 of 24" coverage figure quoted in the dispatch. Artifacts are alive, so this is a RE-MEASUREMENT, not an unvouchable withdrawal. BODY IDENTITY. proyectar_snapshot(bigint) was replaced TODAY by 2026-10-08-movilba-709. Installed now: prosrc md5 e7ce54fc36f39795fb7feede4150b5b2 / 570 lines; functiondef md5 ef750d175e3a65bf889ceeacc78f720a / 574 lines; byte-identical PROD and DEV. Every figure on this item predating the 709 apply describes a superseded body. The 566->574 delta is functiondef; 570 is prosrc. Not interchangeable. COVERAGE, CORRECTED: the typed gate covers 10 of 36, not 9 of 24. "9" is a miscount of its own ten-name list (cuit, dominio, linea_numero, interno, fecha_alta, fecha_baja, aire_acondicionado_id, adas_presente_id, mdvr_id, wifi_id). "24" was 9+15 over a mixed denominator. Stated denominator: INSERT writes 60 columns, less 10 bookkeeping, less 13 catalog/FK-resolved, less carroceria_modelo (literal NULL) = 36 one-expression derivations. carroceria_categoria_id was already inside the 13, so 709 does not move the denominator. FINDING (1) CONFIRMED AS WRITTEN. W3 (lines 350-359) rewrites all 8 si/no columns with bare btrim for the whole snapshot and is the last in-function writer, so W1 NBSP handling and identidad accent handling are both dead code. G1 uses the same bare btrim, so gate and writer AGREE today - the gate false-failure I previously claimed does not exist and is withdrawn. Hazard is the reverse: fixing W3 alone makes G1 abort on correctly-normalized data, so W3 and G1 must change in one edit sharing one expression. Reachability over 44464 non-null si/no cells, both bases: 0 NBSP, 0 accented, 0 outside SI/NO, 0 divergent - each predicate positive-controlled against seeded values. Latent, not active. WITHDRAWN - the ip_mdvr candidate is NOT a defect and needs no decision from Elazar. I had it as the strongest case on this item; it is intended, decided and implemented behaviour. The apostrophe IS in the book (xl/sharedStrings.xml stores <t>%016400014815</t>; 1580 apostrophe-prefixed shared strings, all 12 chars after it; PROD count(DISTINCT id_mdvr)=1580 over 1591 cells). It is Elazar own Excel text guard, and removing it from the business value was decided on 2026-09-30 by docs/migrations/2026-09-30-movilba-618-mdvr-text-guard-DEV-Y-PROD.sql. The strip lives in tools/rutap-snapshot-import.py:704-738, called at :1275, AFTER projection and BEFORE reconciliation. Stored state matches exactly: of 5558 rows, 1591 guarded raw with ip_mdvr=substring(raw from 2) 1591/1591, and 3967 unguarded with ip_mdvr=raw 3967/3967, 0 NULL raw. The vdc archive dates 618 apply at 2026-09-30 03:41 with 1588 guarded pre-images, matching its own asserted v_vdc_before=1588. The figure 1591 survives; the VERDICT over it is withdrawn. Root error: I measured the FUNCTION and called it the PIPELINE. Three instrument defects behind it, all returning wrong answers at exit 0 - a regprocedure cast with no arg list (check never ran, looked clean), a prosrc regex where Postgres lets . match newline (paired substring( at line 217 with ip_mdvr at 258, falsely implicating proyectar_snapshot), and an inference that the apostrophe was an Excel display marker. I also had a quotePrefix-count argument; it is void and deleted, since one style record serves any number of cells. CONSEQUENCE FOR THE GATE: ip_mdvr stays OUT, structurally - the gate runs inside the function, before the importer strip, so it would pass while asserting nothing about the shipped value. Widened gate target is 35 of 36. The importer already asserts raw=book and no-guard-remaining; what nothing asserts is the RELATIONSHIP (ip_mdvr=substring(raw from 2) when guarded, =raw otherwise). Missing assertion over correct data, belongs in the importer, not here, and not a new defect. FINDING (2) belongs to MOVILBA-367, not here. Chasis resolution is latest-only: line 172 joins v_rutap_chasis_resuelto_aprobado -> v_rutap_chasis_resuelto -> v_rutap_latest, which pins the tip by fecha_descarga DESC, id DESC. Correcting myself: this is NOT a 709 regression and my calling it RE-OPENED was wrong - my 14:50 note already cited this view. NOT EXERCISED: one snapshot per base (PROD 11, DEV 21, same md5), so it cannot be observed today. MIGRATION NOT WRITTEN, and why: no base holds rows written by the current body, so the 35 PROD zeros measure two bodies against each other, not my transcription against today writer. Widening on their strength risks aborting the next real import. Validation is a synthetic tip snapshot projected under ROLLBACK. Constraints measured for it: snapshots_md5_key is UNIQUE on md5 (needs its own hash); vh_norm_raw_key is UNIQUE on (rutap_snapshot_id, rutap_fila_excel) so copied rows under a new id do not collide and NO row deletion is needed; no one-current-row index on that table; chasis approvals are computed not stored, so a copied tip recomputes identically; G0 refuses re-projection only for a snapshot that already has rows. proowner DIFFERS - movilba_owner in PROD, movilba_user in DEV, prosecdef false in both - so one file for both bases must preserve each base owner rather than hardcode. normalizar_si_no returns TEXT, not ids, because the ids come from a catalog read and an IMMUTABLE function resolving them would be a false promise. DEV app rows cannot reach the widened gate BY CONSTRAINT, not by census: vh_norm_app_snapshot_ck is CHECK (origen=import OR rutap_snapshot_id IS NULL) and the gate predicate is WHERE h.rutap_snapshot_id = p_snapshot_id. DEV census consistent: (21,import) 5558, (NULL,app) 169. NOT EXERCISED / NOT MEASURED: no projection run, no gate observed firing, no write to either base - both validation transactions ended in ROLLBACK. Whether any ship path exercises this gate at all is unverified. Point (4) of the item (residual gate literals) not re-checked against the 709 body. audit v_lista and borrado_en points untouched. \r is still absent from v_ws and nothing here changes that.
• db03-mba-l-cc note · 1w ago
  db03 correction to my 18:57Z note, one character. The quoted shared-string value renders there as <t>%016400014815</t>; the % is an artifact of my own shell quoting, not the book. The book stores an APOSTROPHE then the twelve digits: <t>'016400014815</t>, i.e. chr(39) || '016400014815'. Nothing else in that note changes - the 1580 / 1591 / 1580-distinct figures and the 618 conclusion are unaffected. Flagged because a mis-transcribed evidence string reads as compliance with quoting the source while being the one thing a reader cannot re-derive.
• db03-mba-l-cc note · 1w ago
  db03-mba-l-cc 2026-10-08 ~19:55Z. ENTREGABLE LISTO PARA AUDIT, no aplicado. docs/migrations/2026-10-08-movilba-340-normalizador-si-no-y-gate-tipado-PROD-Y-DEV.sql sha256 ca08f209f7cfe3caca4d137d6f11ec9664382ad7934030a788280833d373463e, 305 lineas. tools/verification/movilba-340-ensayo.sql sha256 fb64b28e4f1e424f5862f5bd7188165447968654139e6a6a3654c49f5c1b8365, 565 lineas. Los dos UNTRACKED. QUE HACE. Un solo rutap.normalizar_si_no(text) IMMUTABLE STRICT (strip de v_ws incl. NBSP, fold de acentos, devuelve Si/No/NULL) compartido por W1 (7 sitios), identidad y W3 (8 sitios), MAS el gate tipado de 10 a 35 columnas. 16 anclas de escritor + 1 bloque de gate, 24 llamadas al normalizador, cada ancla con hits=1 afirmado. POR QUE EN UN SOLO ARCHIVO. W3 (lineas 350-359, el ULTIMO escritor dentro de la funcion) y el gate G1 usan hoy el MISMO btrim pelado, asi que coinciden. Arreglar W3 solo haria que el gate abortara sobre datos escritos CORRECTAMENTE. COBERTURA: 10 de 36 antes, 35 de 36 despues. ip_mdvr queda FUERA a proposito y esto es lo importante: su valor final lo escribe tools/rutap-snapshot-import.py:724 DESPUES de la proyeccion (decision MOVILBA-618), asi que el gate corre antes del ultimo escritor y meterlo afirmaria algo que no es el valor que MovilBA sirve. '35 de 36' es cobertura de la FUNCION, no del pipeline: ip_mdvr (:724) y estado_id (:877) los finaliza el importador despues del gate. Fuera del denominador de 36 quedan las 13 columnas resueltas por catalogo, que no tienen derivacion por expresion. IMPACTO EN FILAS VIVAS DE LA MITAD si/no: CERO, medido, y lo digo plano. Las 44.464 celdas si/no de PROD tienen 5 valores crudos (No 24.234, Si 19.953, SI 263, si 9, NO 5), NI UNO con NBSP, acento ni relleno; los cinco resuelven igual con la expresion vieja y la nueva. El defecto es LATENTE: W1 strippea NBSP y identidad acepta SI con acento, pero W3 reescribe las 8 columnas con btrim pelado despues, asi que ese manejo es CODIGO MUERTO y un libro futuro con un NBSP se guardaria NULL en silencio. El grueso del valor de este archivo es el gate. ENSAYADO EN LAS DOS BASES, cada una en UNA transaccion cerrada con ROLLBACK (-v base=11 PROD, -v base=21 DEV): 1. Pre-imagen md5(pg_get_functiondef)=ef750d175e3a65bf889ceeacc78f720a AFIRMADO en ambas. 2. Las 35 expresiones reproducen lo guardado: 35/35, 0 divergentes, 189.746 celdas no nulas sobre 5558 filas en PROD sin sembrar. NINGUNA columna SIN EJERCITAR (minimo 6962 celdas, expte_baja). 3. Proyeccion REAL de un snapshot sintetico bajo el cuerpo nuevo: (5558,5558,0,0,0). El gate de 35 pasa una proyeccion entera. 4. TESTIGO FAILING-BEFORE. Hace falta porque el libro de hoy no trae ni un NBSP: sin testigo, '0 diferencias' no distingue 'el arreglo sirve' de 'el arreglo no hace nada'. Sembrando chr(160)||'SI' en filas 3 y 5 y 'SI' con acento en la 4, el cuerpo VIEJO guarda NULL en las TRES y el NUEVO resuelve las tres al id 24. Identico en PROD y DEV. 5. CONTROL de la afirmacion de 340, VISTO FALLAR: con UN sitio de W3 devuelto al btrim pelado y el gate normalizado, aborta MOVILBA-269 sobre 2 filas - la divergencia escritor/gate que el archivo elimina. 6. CONTROL del gate ampliado, VISTO FALLAR: mutante que corrompe la derivacion de consumo aborta MOVILBA-269 sobre 5450 filas. 7. EXECUTE afirmado: proyectar_snapshot es prosecdef=false, asi que quien importa ejecuta el normalizador con SU rol. proacl del proyector es NULL (EXECUTE a PUBLIC) y rutap no tiene pg_default_acl, asi que el normalizador hereda lo mismo; el archivo lo AFIRMA y chequea svc_nextjs explicito en vez de confiarse. 8. Dueno POR BASE, no fijo: SET LOCAL ROLE al proowner del proyector - movilba_owner en PROD, movilba_user en DEV, las dos corridas lo reportaron bien. svc_nextjs no es miembro de movilba_owner y no tiene CREATE en rutap (medido), asi que la pata PROD va por la URL de superusuario. 9. ROLLBACK verificado en las dos: 1 snapshot, 5558 filas crudas, cuerpo en ef750d17, normalizar_si_no en 0 filas de pg_proc, y en DEV las 169 filas origen='app' INTACTAS. POLARIDAD SEGURA: ROLLBACK por default, -v movilba340_apply=1 para commitear. Una corrida sin flag no escribe nada. (Opuesta a la de 123, que coder03 marco como riesgo de apply.) DOS DEFECTOS DEL INSTRUMENTO que encontre y arregle, los dos de la forma que ya me conozco: a) El instrumento medía TODA la tabla mientras el gate filtra por rutap_snapshot_id. En DEV eso reportaba ~169 divergencias que son exactamente las filas origen='app', que por vh_norm_app_snapshot_ck tienen rutap_snapshot_id NULL y NUNCA pueden llegar al gate. Un instrumento mas ancho que el control que valida reporta como divergencia lo que el control no mira. b) Un 'WHERE id = 11' sobrevivio a la parametrizacion por tener otra indentacion. En PROD funciono por accidente (base ES 11) y en DEV selecciono CERO filas, delatado por la FK de rutap_rows. Un literal correcto en una base no es un parametro. HALLAZGO COLATERAL, para MOVILBA-367: el ensayo demuestra empiricamente la resolucion latest-only de chasis. Un snapshot sintetico que deja de ser el tope NO se puede proyectar - aborta 'RUTAP strict catalog gate rejected snapshot 9341, Excel row 2833, dominio OWG349: chasis_no_resuelto' antes de llegar al gate. Es el control de snapshot-no-tope que 367 esperaba, producido en PROD bajo ROLLBACK. ADYACENTE, NO TOCADO: la linea 348 del cuerpo compara upper(btrim(h.raw_unidad_propulsora))='GNC' con btrim PELADO - misma clase de perdida de NBSP, otra columna, fuera del alcance de 340. Queda por filar. SIN EJERCITAR: ningun import real corrio contra este cuerpo; el paso ip_mdvr del importador no se ejecuto; ninguna superficie de la app (ficha, XLSX, PDF, API) se abrio; ningun camino de ship invoca el ensayo. LAS SECUENCIAS NO VUELVEN CON ROLLBACK: los ensayos consumieron ids de vehiculo_historial_normalizado_id_seq, last_value 271051 contra max(id) 209907 en PROD. Inofensivo en bigint, pero es escritura que sobrevivio. snapshots_id_seq quedo en 11 porque los sinteticos usan ids explicitos 9339-9342. Una sesion mia de ensayo en DEV quedo colgada tras un timeout del shell y la cancele con pg_cancel_backend (era mi propia transaccion, destinada a ROLLBACK); DEV verificado sin residuo despues.
• audit-mba-l-cc audited · 1w ago
  audit pass: 340: migration ca08f209 (305 ln) + ensayo fb64b28e (565 ln), both NOT APPLIED, recovered from the park and re-pinned. Static read + my own read-only SQL in BOTH bases. NOT exercised: neither file run by audit; no real import; no app surface; the \r case and the ACL branch exercised by nothing.
  payload

  {"scope":"340: migration ca08f209 (305 ln) + ensayo fb64b28e (565 ln), both NOT APPLIED, recovered from the park and re-pinned. Static read + my own read-only SQL in BOTH bases. NOT exercised: neither file run by audit; no real import; no app surface; the \\r case and the ACL branch exercised by nothing.","outcome":"pass","evidence":"audit-mba-l-cc 2026-10-08 20:55Z. PASS on ca08f209 + fb64b28e, both matching db03 and re-verified after the tree was parked under my live lock (recovered from stash commit cabbf757, byte-identical).\n\nORDERING - READ BEFORE APPLYING: 340 and 490@1b340b53 each pin the projector body the other rewrites. 340 pins md5(pg_get_functiondef)=ef750d17 at :135; 490 pins md5(prosrc)=e7ce54fc at its preflight. Both describe todays MOVILBA-709 body, so whichever applies first makes the other abort on drift - correctly. 490 right after 340 cannot run as written, and MY PASS ON 1b340b53 DOES NOT TRAVEL to a re-pinned 490. 367 pins the same functiondef. No content collision: 490s anchors are on the propulsion column and 340 touches no propulsion text, so re-pinning is mechanical. Pick the order once, re-pin the rest in it.\n\nVERIFIED BY ME: polarity is the SAFE one, ROLLBACK default, needs -v movilba340_apply=1 (:301-305); the ensayo closes with an unconditional ROLLBACK at :565 and has no \\i include that could end its transaction early. Pre-image ef750d17 matches in movilba AND movilba_dev. The 24-call assertion is right: 8 normalizar_si_no in new_gate :90-124 plus 16 in the pares replacements. Anchors cannot double-apply: hits=1 per anchor and once on the WHOLE old gate block, and the W3 anchors carry \"= CASE\" where the gate carries \"IS DISTINCT FROM (CASE\", so the pares loop cannot perturb the gate text. normalizar_si_no exists in 0 rows of pg_proc in both bases, confirming the ROLLBACK. Owner handling is the part I expected to break and does not: the owner differs for real (movilba_owner PROD, movilba_user DEV) and :144-145 reads it from THIS bases projector, not a literal. CORRECTION TO MY OWN DRAFT: I wrote that a C-ctype base would break the accent fold - wrong, withdrawn. The translate from-set carries the lowercase accented letters mapped to UPPERCASE ASCII, so the fold holds with or without upper() folding; measured.","reportRef":null}

• audit-mba-l-cc audit-finding · 1w ago
  audit-mba-l-cc 2026-10-08 20:58Z - findings on ca08f209, none blocking, PASS already recorded. 1. "35 de 36" rests on an unstated denominator, and the written book-derived columns the gate does not cover are FOUR, not one. Measured on PROD: rutap.vehiculo_historial_normalizado has 39 raw_* columns and the gate block at :90-124 references exactly 35 - counted on those lines alone, because a whole-file grep also picks up raw_unidad_propulsora from the prose at :268 and would have hidden a gap. The four uncovered are raw_id_mdvr -> ip_mdvr (the acknowledged MOVILBA-618 exclusion), raw_piso -> piso_id, raw_tipo -> vehiculo_subtipo_transporte_id and raw_unidad_propulsora -> unidad_propulsora_id. All four targets are written by the projector (piso_id 1, vehiculo_subtipo_transporte_id 1, unidad_propulsora_id 2, ip_mdvr 1 occurrences of the exact name in prosrc). The three extra are catalog-resolved by lookup rather than by an inlinable expression, so excluding them from an expression-re-derivation gate is defensible - but the phrase "36 columnas de derivacion por expresion" states the denominator without saying three written catalog-resolved columns were removed from it, and the si/no columns the gate DOES cover are catalog-resolved too (via v_si_id/v_no_id), so the boundary is not "expression vs catalog". unidad_propulsora_id is separately gated by 490s parity check, a sound division of labour. piso_id and vehiculo_subtipo_transporte_id are gated by nothing INSIDE proyectar_snapshot - I searched prosrc only, not the importer or the reconciler, so I am not claiming they are ungated end to end. Fix the sentence, not the file. 2. \r remains a silent-NULL hazard and it is the same class this file exists to close. The normalizer strips E' \t\n' || chr(160) - no carriage return. Measured in both bases: the full expression over E'SI\r' yields SI\r, which matches neither CASE arm and returns NULL. The projector itself does regexp_replace(..., '[\r\n]', '', 'g') for numero_serie_chasis and numero_serie_motor, so \r is a KNOWN and already-handled hazard in this book - which makes its omission from the si/no normalizer the one remaining instance of exactly the latent defect :23-25 describes. The ensayo seeds NBSP and accents, no \r. Adding chr(13) to the strip set is a one-character change. A finding and not a FAIL because todays 44,464 cells contain no \r either, so it changes no live row. 3. The ACL-replication branch at :172-178 is dead code in BOTH bases. Measured: the projectors proacl IS NULL in movilba and movilba_dev, so old_acl IS NOT NULL is false and the REVOKE/GRANT loop never runs; only the assertions at :179-189 are reachable. Not a defect - it guards a configuration that does not exist - but it has never been exercised and would run for the first time on a base whose ACLs had been tightened, which is the worst moment. Its (aclexplode(ARRAY[...])).grantee form inside a format() argument is also a set-returning call in an expression context, which does not behave the way it reads. 4. Stale comment at :280: "tiene que aparecer en los 20 sitios" sits immediately above an assertion of 24. The assertion is the correct number. 5. The rehearsal consumed sequence values ROLLBACK does not return, which db03 declares openly at :50-52 (vehiculo_historial_normalizado_id_seq.last_value 271051 vs max(id) 209907 in PROD). Harmless for a surrogate key, and declared rather than discovered. NOT EXERCISED BY AUDIT: neither file was run, in either base. All execution evidence is db03s rehearsal, which I read and did not reproduce - both bases, one transaction each closed with ROLLBACK, 35/35 gate expressions reproducing the stored value over 189,746 non-null cells on 5558 rows, a synthetic-snapshot projection of (5558,5558,0,0,0), the failing-before witness (chr(160)||SI and SI-with-accent stored NULL by the old body, resolved to id 24 by the new one), and TWO controls seen to fail (one W3 site reverted to bare btrim aborting with MOVILBA-269 on 2 rows; a consumo mutant aborting on 5450). No real import ran against the new body, so "35 of 36" is coverage of the FUNCTION and not of the pipeline - db03 states that plainly and it is the correct framing; the 1591/1591 and 3967/3967 ip_mdvr figures are db03s and I did not reproduce them. No app surface opened. The \r case and the ACL branch are exercised by nothing.
• db03-mba-l-cc note · 1w ago
  db03-mba-l-cc 2026-10-08. APPLIED on audit PASS of ca08f209, PROD FIRST then DEV, same file, no re-derived instrument. Projector lock on rutap.proyectar_snapshot(bigint) RELEASED after the apply, not before - I held it to apply. POST-APPLY DIGESTS, both bases BYTE-IDENTICAL: md5(pg_get_functiondef)=edb0748ad9ed895afe1616bab6b9d03f, md5(prosrc)=017b35d5d9578da56ef5ee48bf031f6b, prosrc length 38375, 24 calls to rutap.normalizar_si_no(. Owner resolved per base as designed: movilba_owner in PROD, movilba_user in DEV. PROD ran through SUPERUSER_ELAZAR_URL, DEV through DEV_DATABASE_URL. The pre-image assertion ef750d17 held on both before the write, so neither base had drifted since the rehearsal. CONSEQUENCE FOR THE QUEUE, as audit stated: the two pins that described the 709 body are now dead. 490@1b340b53 pins md5(prosrc)=e7ce54fc and 367 pins md5(functiondef)=ef750d17; both will now abort on drift, CORRECTLY. Re-pin to edb0748a / 017b35d5 above. No content collision - 490's anchors are propulsion and 340 touched none of them - so re-pinning is mechanical, and each re-pinned file is a new SHA that needs its own verdict. audit's PASS on 1b340b53 does not travel. LIVE VERIFICATION of the fix, run after the apply in both bases: rutap.normalizar_si_no(chr(160)||' sI ') returns Si and ('no') returns No, so NBSP, accent and case now resolve where the old body stored NULL. AUDIT FINDING 2 REPRODUCED AND MEASURED, and it is real: the strip set is E' \t\n' || chr(160) with NO chr(13), so rutap.normalizar_si_no(E'SI\r') returns NULL in both bases - I ran it. It is the one surviving instance of the exact defect class this file exists to close. MEASURED SCOPE: zero si/no cells in either base carry a carriage return today, 0 of 5558 rows across all eight si/no columns in both movilba and movilba_dev, so like the rest of the si/no half it is LATENT, not active. The fix is one chr(13) added to the strip set of the shared normalizer, which both the writer and the gate call, so writer and gate cannot disagree. It goes in its own item with its own verdict rather than silently riding this applied file - an applied migration is not edited again. AUDIT FINDING 1 ACCEPTED, sentence not file: 35 of 36 excludes three WRITTEN catalog-resolved columns without saying so. 39 raw_* exist, the gate references 35, and the uncovered four are ip_mdvr plus piso_id, vehiculo_subtipo_transporte_id and unidad_propulsora_id. The NOTICE the apply emitted says ip_mdvr excluido por MOVILBA-618 and names only that one, so the installed NOTICE text carries the same understatement. Correcting it means re-pinning an applied file, so the correction lives here and in the follow-up item, not in the migration. AUDIT FINDING 3 ACCEPTED: the ACL branch at :172-178 is dead in both bases because proacl IS NULL, so the proacl mirroring and the has_function_privilege assertions never exercised a non-trivial ACL. Confirmed by the same measurement that gave the owner per base. NOT EXERCISED: no real snapshot projection ran after the apply - the last projection under the new body was the synthetic 9340 of the rehearsal, under ROLLBACK. No live HTTP request. The next genuine import is the first execution of the installed gate against real book data, and nothing in a ship path runs it before then.
• db03-mba-l-cc note · 1w ago
  db03 2026-10-09 ART. Re-checked parent findings against the CURRENTLY INSTALLED rutap.proyectar_snapshot body (rewritten many times since the 09-08 review this ticket was filed against - now also covers MOVILBA-709/492/282/342/438/341/333). (1) W3/G1 normalizer weakness: RESOLVED, folded into MOVILBA-748/749 (chr13 strip) - both done - plus 751 (blank-propulsion flag) now blocked pending Elazar's A/B pick. (2) Chasis resolution 'reads only latest, ignores the bigint arg': NOT REPRODUCIBLE against current code, re-read in full. The join is cr.snapshot_id = s.snapshot_id AND cr.fila_excel = s.fila_excel (line ~173), and the underlying chain rutap.v_rutap_chasis_resuelto_aprobado -> v_rutap_chasis_resuelto -> rutap.rutap_rows carries snapshot_id end to end with no 'latest' filter anywhere. Correctly parametrized today. Marking CLOSED/stale, superseded by a later fix - not independently reproduced as ever having been true of a body I can inspect. (3)/(4) and the three audit addenda (W5 vdc-join determinism/G0, v_lista reused as integer, conflict report not filtering borrado_en): NOT RE-MEASURED this pass - the typed-gate at line ~517 now covers ~32 value-derived columns (comprehensive for that class), but lookup-resolved columns (chasis_id/entidad_id/linea_id/etc) are gated separately by existence/exact-set checks elsewhere in the body, which is probably why (3)'s 'subset' framing was written - needs its own read before calling it a defect or closing it. (4)'s '6067/6720' literals do not appear anywhere in the current body at all - likely from code already replaced; needs fresh measurement of whatever exact-set gate exists today, not a search for those specific ids. STOPPING HERE for this pass - diminishing returns vs. budget. Remaining open sub-items: (3), (4), three audit addenda - each needs a fresh 10-minute read against today's body before verdict.
• db03-mba-l-cc note · 1w ago
  db03 2026-10-09 ART, closing items (3)/(4) against the body installed right now (rutap.proyectar_snapshot(bigint), DEV, dumped fresh via pg_get_functiondef). (4) RESOLVED-STALE: no '6067'/'6720' literal exists anywhere in the current ~616-line body (grepped). The exact-set gate those ids referred to is gone, replaced by the generic 1:1 check at line ~485 and the raw-twin match at ~515. Nothing to fix - the original finding's target code no longer exists. (3) CONFIRMED STRUCTURAL, NOT CURRENTLY LIVE: the typed-twin gate (line ~519-558) covers exactly 32 value-derived columns, confirmed by reading its full predicate list. Four lookup-resolved columns have no equivalent check at all: chasis_id, unidad_propulsora_id, piso_id, carroceria_categoria_id - none are covered by the typed gate, none have a MOVILBA-492/282-style WARNING the way carroceria_id/tramite_id do. Measured against DEV live population: all four currently show 0 rows where the column is NULL while its raw text is non-blank (piso_id/unidad_propulsora_id each have 32 NULL rows, but raw text is blank in all 32 - correct NULL, not a miss). So the gap is real in the code but not exercised by any row today - latent, not an active defect. Remaining 3 audit addenda (W5 vdc-join determinism/G0, v_lista reused as integer, conflict report not filtering borrado_en) still not re-measured this pass - stopping here, budget. If anyone picks those up: same method, read against the CURRENT installed body first, this one has moved under MOVILBA-709/492/282/342/438/341/333 since the ticket was filed.
• db03-mba-l-cc note · 1w ago
  db03 2026-10-09 ART. Took the two 'trivial fix' items per coder03's GO - both already moot, no DDL needed. chr(13): rutap.normalizar_si_no (DEV, live) already strips it - btrim(p_valor, E' \t\n\r' || chr(160)), confirmed via pg_get_functiondef just now. This is MOVILBA-748/749 (both done) - shipped after the audit finding was written against an older body, finding is stale. Stale '20 sitios' comment: grepped the full current proyectar_snapshot functiondef, no 'sitios' string anywhere. Removed/superseded by a later migration (709 or after). Nothing to fix. Remaining real scope: the 3 original addenda (W5 vdc-join/G0, v_lista-as-integer, borrado_en filter on conflict report) - unmeasured, parking as backlog p2 per coder03's call, correctly sized for its own pass not a tail-end of today's.
• db03-mba-l-cc statusChanged · 1w ago