v_snapshot_catalog_preflight never fires - no positive control on any branch

Sub-tickets

Activity

• audit-mba-l-cc created · 2w ago
• coder03-mba-l-cc assigned · 2w ago
  db04-mba-l-ca
• coder03-mba-l-cc note · 2w ago
  No es un defecto de catalogo de 607: la rama si_no valida texto literal (SI/SI/NO) y no hace ningun lookup a opciones - verificado, 0 de las 3 referencias a opciones del cuerpo tocan aire_acondicionado. Mi hallazgo original decia 'pin vencido' y era un falso positivo de un LIKE sin control. Lo que queda en pie: la vista devuelve 0 filas para TODOS los campos en las dos bases, asi que nunca disparo y su 'limpio' no esta verificado. Pide un control positivo - una fila armada para violar el check SI/SI/NO - antes de que su salida vacia se pueda leer como cobertura.
• coder03-mba-l-cc deferred · 2w ago
  Diferido por proporcion (Elazar, 2026-09-28). La vista es un preflight de import que hoy no rompe nada: falta su control positivo, pero el costo de armarlo no se justifica mientras el import no este corriendo. Se retoma cuando alguien vaya a confiar en su salida vacia.
• coder03-mba-l-cc assigned · 1w ago
  researcher-mba-l-cc / solo
• researcher-mba-l-cc spec · 1w ago
  Positive-control design for rutap.v_snapshot_catalog_preflight (identical body PROD+DEV). View body has 12 issue-producing branches in its UNION ALL (drizzle/schema.ts:3221). For each, one engineered row in rutap.rutap_rows (same live snapshot_id) with ONLY that column invalid, everything else blank/valid so no other branch also fires. Run under BEGIN ... ROLLBACK (test, not a real import) — do not commit: 1. entidad: cuit='99999999999' (no entidades match) 2. linea: linea='ABC' (fails ^\d+$, linea_n NULL) 3. chasis: chasis_marca='TESTMARCA NOEXISTE', chasis_modelo='TESTMODELO NOEXISTE', unidad_propulsora='GO' (valid, isolates chasis branch from branch 6) 4. tipo_servicio: tipo_servicio='NOEXISTE' 5. tramite: tramite='NOEXISTE' 6. unidad_propulsora: unidad_propulsora='DIESEL' (not in GO/GNC/ELECTRICO/ELECTRICO-CABA) 7. piso: piso='NOEXISTE' 8. estandar_emisiones: tipo_motor='NOEXISTE' 9. subtipo: tipo='ZZZ' (no leading digits -> substring NULL -> no vehiculos_subtipos_transporte match) 10. motor_fabricante: motor='FABRICANTE NOEXISTENTE' 11. si_no x8 (one row covers all 8, CROSS JOIN LATERAL fires per-field): aire_acondicionado='MAYBE', cabina_antivandalica='MAYBE', adas='MAYBE', mdvr='MAYBE', pantallas='MAYBE', wifi='MAYBE', conexionesusb='MAYBE', identidad='MAYBE' -> expect 8 si_no:* rows from this one row 12. consumo: consumo='ABC' (not numeric, not '-') Use fila_excel sentinels (e.g. 900001..900011) and nro_dominio like 'TESTCTL01'..'TESTCTL11' so rows are visually identifiable, outside real fila_excel range. Expected result: SELECT * FROM v_snapshot_catalog_preflight WHERE snapshot_id=<test> returns 11 campo groups (branch 11 yields 8 si_no:* rows, so 18 total issue rows across the 11 inserted test rows), confirming the view fires correctly on all branches it was designed to catch. ROLLBACK after. db03 applies/runs; status stays deferred — this is a control design, not an implementation request, per Elazar's 2026-09-28 proportion call (ticket not worth running for real until import runs again).
• coder03-mba-l-cc assigned · 1w ago
  researcher-mba-l-cc / solo
• coder03-mba-l-cc statusChanged · 1w ago
• coder03-mba-l-cc deferred · 1w ago
• coder03-mba-l-cc note · 1w ago
  coder03-mba-l-cc 2026-10-08 17:35Z / 14:35 ART - devuelto a deferred. Lo habia sacado a backlog a las 17:32:31Z creyendo que era trabajo sin arrancar; esta mal de dos maneras. El diseno del control positivo ya estaba escrito por researcher a las 15:19:40Z - 12 ramas del UNION ALL enumeradas, una fila armada por rama, centinelas fila_excel 900001-900011 y dominios TESTCTL01-11, esperado 11 grupos de campo y 18 filas de issue. Y el aplazamiento lo atribuye researcher a una llamada de proporcion de Elazar del 2026-09-28, que no se revisa por iniciativa de un carril. Lo que queda es el APPLY, de db03, cuando alguien necesite confiar en la salida vacia de rutap.v_snapshot_catalog_preflight. Hoy nadie lo necesita, asi que no se adelanta. Hueco de registro que si hay que cerrar, porque es lo que me hizo equivocar: el historial de eventos de este item NO contiene el aplazamiento ni su motivo - los unicos eventos son mis dos assigned, el spec de researcher y mi statusChanged. Un estado deferred sin el evento que lo justifica se lee como backlog olvidado, y el proximo carril lo va a volver a sacar igual que yo. Quien lo aplazo deja el motivo escrito en el item.
• coder03-mba-l-cc unblocked · 1w ago
  Reactivado y asignado a researcher-mba-l-cc: es exactamente su forma - una vista de preflight que nunca dispara y que no tiene control positivo en ninguna rama. La pregunta a medir no es si el codigo existe sino si ALGUNA rama puede evaluar a verdadero con datos reales, y el control es construir la fila que la haga disparar. Un preflight del que solo sobreviven los verdes es una linea verde, no un control.
• coder03-mba-l-cc assigned · 1w ago
  researcher-mba-l-cc / solo
• researcher-mba-l-cc note · 1w ago
  2026-10-08T17:02:40-03:00 / 20:02:40Z researcher-mba-l-cc. Static analysis of rutap.v_snapshot_catalog_preflight (docs/migrations/2026-09-18-movilba-494b-vista-preflight-DEV-Y-PROD.sql), no live query run yet - blocked on credentials, see below. STRUCTURE: the view's "issues" CTE is 12 UNION ALL branches, each a WHERE predicate that should fire when a rutap_rows field fails to resolve against a catalog/resolver: entidad(cuit vs entidades), linea(vs lineas.numero_linea), chasis(marca+modelo vs unidad_propulsora_resolver+chasis_resolver, both must be estado='aprobado'), tipo_servicio(vs regimen_servicio_modalidad), tramite(vs v_opciones), unidad_propulsora(vs fixed array GO/GNC/ELECTRICO/ELECTRICO-CABA), piso(vs v_opciones), estandar_emisiones(vs v_opciones, accent-stripped), subtipo(vs vehiculos_subtipos_transporte where vehiculo_tipo_id=1), motor_fabricante(vs fabricantes), si_no(8 columns collapsed into one predicate via a LATERAL VALUES list), consumo(regex-not-numeric-and-not-dash). BUILT a positive-control script (scratchpad/611/preflight-control.sql): one synthetic rutap_rows row per branch under a fake snapshot_id (-999001), each engineered to fail exactly one predicate (nonexistent cuit, non-numeric linea, unresolvable chasis combo, nonexistent tipo_servicio/tramite/piso/estandar_emisiones/motor_fabricante labels, an out-of-allowlist unidad_propulsora, a garbage si_no value, a non-numeric consumo), wrapped in BEGIN/ROLLBACK end to end, re-reads the view filtered to the fake snapshot_id, expects 12 distinct `campo` rows back if every branch is satisfiable - any branch absent from that result for a value built to fail it is a dead/unsatisfiable predicate, not an absence of real-world issues. BLOCKED: no DB credential for researcher-mba-l-cc. No ~/.config/postgres/researcher-mba-l-cc.env, no DATABASE_URL in ~/.llmmsg-env, no env var set. Per the project's credential-order rule this falls to a db-lane-provisioned role, not something I can self-serve or guess. Need either (a) a read credential provisioned for me, or (b) db04 to run scratchpad/611/preflight-control.sql against DEV under its own ROLLBACK (script already rolls back, never commits) and report the per-branch result back. NOT YET MEASURED: whether the view returns 0 rows on CURRENT real data (the "never fires" claim in the assignment) - that baseline SELECT is also in the script, first line, same blocker.
• coder03-mba-l-cc note · 1w ago
  researcher-mba-l-cc entrego el diseno del control positivo por rama (12 ramas del UNION ALL, una fila sintetica de rutap_rows por rama bajo snapshot_id -999001, BEGIN/ROLLBACK, re-lee la vista filtrada a ese snapshot_id y espera 12 filas distintas de campo; cualquier rama faltante es un predicado MUERTO, no ausencia de problemas). BLOQUEADO para ese carril por falta de credencial: no existe ~/.config/postgres/researcher-mba-l-cc.env ni DATABASE_URL en ninguna parte, asi que no puede correr ni el control ni el baseline de 'hoy devuelve 0 filas'. Lo corre db04-mba-l-ca contra DEV, que es el carril con credencial, y devuelve resultados por rama mas el baseline; researcher sigue desde ahi. Esto NO es una excepcion a la regla de SELECT-only: la corrida es un ensayo cerrado con ROLLBACK y el gate ES la medicion, que es exactamente el caso que el carril db corre por si mismo. Advertencia de instrumento que hay que resolver ANTES de la primera corrida, para no quemar una ronda: si rutap_rows tiene FK contra rutap.snapshots, el snapshot_id -999001 sintetico va a abortar el INSERT por violacion de FK y eso se va a leer como 'la rama no dispara' cuando en realidad el fixture nunca se planto. Verificar la FK primero y, si existe, crear el snapshot sintetico dentro de la misma transaccion antes de las filas. Esto es la misma forma que MOVILBA-648 pero en otro carril: un carril al que se le pide una medicion de base sin credencial de base produce una afirmacion sobre el archivo, no una ejecucion. 648 lo nombra para audit; queda constancia de que tambien alcanza a researcher.
• db04-mba-l-ca progress · 1w ago
  DEV rollback control EXECUTED on installed v_snapshot_catalog_preflight, body md5(pg_get_viewdef)=f742cfe55f59298552d48a8e73d76a5b. Preflight checked rutap_rows.snapshot_id FK to snapshots; synthetic snapshot -999001 inserted before 12 fixture rows in one transaction, then ROLLBACK, postcheck 0 surviving snapshots/rows. All 12 UNION ALL branches fired: 11 single-field issues plus si_no eight fields = 19 issue rows, each affected 1 row. Expected-vs-actual (case,campo) EXCEPT ALL: 0 missing, 0 extra. REAL baseline was NOT 0: one motor_fabricante issue on snapshot 21 row 5560 AH992CY, raw CUMMINS 6 CIL; normalized history resolves it to CUMMINS id24, estado resuelto (MOVILBA-722). Thus title never-fires disproven, but diagnostic has stale manufacturer-alias false positive. Exact script/output: /data-lezama/movilba/evidencia/MOVILBA-611/2026-10-08-dev-positive-control-results.md. No persistent DB or view change; next align preflight alias with installed projector and rerun baseline/control.
• researcher-mba-l-cc note · 1w ago
  2026-10-08T17:06:18-03:00 / 20:06:18Z researcher-mba-l-cc. Root cause for db04's baseline false positive (snapshot 21 row 5560 AH992CY, motor_fabricante=CUMMINS 6 CIL). db04's result confirms the view is a working control: 12/12 branches fired on the synthetic rows, 0/0 EXCEPT ALL - the instrument is NOT dead. The baseline nonzero result is real, and here is why: MOVILBA-722 (docs/migrations/2026-10-06-movilba-722-cummins-6-cil-alias-PROD-Y-DEV.sql) patched the manufacturer-match JOIN inside `rutap.proyectar_snapshot(bigint)` - the function that actually materializes the normalized history - adding a CASE WHEN for the exact string 'CUMMINS 6 CIL' -> 'CUMMINS' (fabricantes.id=24). It did NOT touch `rutap.v_snapshot_catalog_preflight` (2026-09-18-movilba-494b, line ~250), whose motor_fabricante branch still runs the OLD join (`replace(s.motor_n,'MERCEDEZ','MERCEDES')`, no CUMMINS case). Same shape as the project's known "two functions write/read the same field, fixing one lets the other drift back" trap: here one function PROJECTS and the other DIAGNOSES, and only the projector got the 722 fix. The projector resolves this row correctly in real data; the diagnostic view still flags it as unresolved. False positive, not evidence of a real unresolved catalog entry. CLOSING MOVILBA-611's original question: the view is not dead code and was never a no-op "green line" - it fires, and its branches are individually satisfiable (db04's control). The actual defect is narrower: the view's motor_fabricante predicate needs the same CASE WHEN MOVILBA-722 added to the projector, or it will keep reporting this row (and any future alias-only fix that patches the projector without the view) as unresolved. Not fixing it here - read-only census, flagging for whichever lane owns 494b/722 follow-up.
• researcher-mba-l-cc note · 1w ago
  2026-10-08T17:32:53-03:00 / 20:32:53Z researcher-mba-l-cc. Status: not idle, designing against the moved ground per coder03's 17:31 DM. Two new targeted controls at scratchpad/611/preflight-control-v2.sql, still blocked on the same missing DB credential (cannot execute myself). CONTROL A (si_no branch, real defect not synthetic guess): checked - the preflight view's si_no predicate is UNCHANGED by MOVILBA-340 (grepped the 340 migration file, zero references to v_snapshot_catalog_preflight; 340 only touched rutap.proyectar_snapshot and the typed gate). It still does its own literal upper/btrim-against-ARRAY['SI','SÍ','NO'] check, with no second argument to btrim - so it does NOT strip chr(13) either, same gap as normalizar_si_no per MOVILBA-749. Prediction before running: inserting aire_acondicionado='SI'||chr(13) should make the view's si_no:aire_acondicionado branch fire, because 'SI\r' survives the view's own trim and fails the ARRAY match - which would mean the view correctly flags the exact value the projector fails on (a real control seen to fail, not a guess). If it does NOT fire, that is a second, opposite-direction defect (false negative) beyond the CUMMINS false positive. CONTROL B (motor_fabricante, no insert needed): pure scalar comparison of the view's own predicate (replace(...,'MERCEDEZ','MERCEDES'), no CUMMINS case) against the projector's post-722 predicate (adds the CASE WHEN for 'CUMMINS 6 CIL'), both evaluated on the literal string 'CUMMINS 6 CIL' with no table insert. Isolates the exact gap MOVILBA-722 left, independent of whether row 5560 still exists in any snapshot. Needs db04 (or whoever has DEV creds) to run preflight-control-v2.sql and report both results. Not parking - this is next-step design work on the same item, no decision owed from Elazar.
• db04-mba-l-ca progress · 1w ago
  DEV-only v2 controls executed after repairing researcher SHA 884a0cc2 fixture FK: original inserts rutap_rows.snapshot_id=-999002 without parent and cannot run. Durable db04 copy /data-lezama/movilba/evidencia/MOVILBA-611/2026-10-08-preflight-control-v2-db04-DEV-ROLLBACK.sql SHA256 43931989c71d74327184fc46b71cabfb23a0baebf8d520654fa38f46cb77e955 adds guarded synthetic rutap.snapshots parent and real active line, all in BEGIN/ROLLBACK. current_database=movilba_dev, preflight viewdef md5 f742cfe5. Control A: rutap.normalizar_si_no(E\x27SI\\r\x27) NULL, confirms_749=true; view returns exactly 1 synthetic issue: campo=si_no:aire_acondicionado, valor_fuente=SI+CR, filas_afectadas=1, dominio ZZZ611D. Thus view detects the current 749 writer defect, no false negative for this value. Control B: view_flags_as_unresolved=true for CUMMINS 6 CIL while projector resolves fabricante_id=24, reproducing 746 false positive. Post-ROLLBACK snapshot -999002 count 0, rutap_rows count 0, baseline preflight groups still 1. No persistent write.
• researcher-mba-l-cc note · 1w ago
  2026-10-08T17:42:39-03:00 / 20:42:39Z researcher-mba-l-cc. v2 controls executed by db04 (movilba_dev, BEGIN/ROLLBACK, SHA 43931989 in /data-lezama/movilba/evidencia/MOVILBA-611/2026-10-08-preflight-control-v2-db04-DEV-ROLLBACK.sql). CONTROL A result: normalizar_si_no('SI'||chr(13)) = NULL (confirms 749), AND the preflight view emitted exactly 1 si_no:aire_acondicionado row for the planted value. The view correctly flags the same value the projector fails on. NOT a false negative - the si_no branch is sound against this real defect. CONTROL B result: confirms MOVILBA-746 exactly as predicted - view says 'CUMMINS 6 CIL' unresolved, projector's post-722 predicate resolves it to fabricante id 24. Same finding, now also demonstrated as a pure scalar divergence, independent of row 5560's continued existence. CLOSING MOVILBA-611 from this side: v_snapshot_catalog_preflight is a live, working instrument. All 12 branches are individually satisfiable (v1 control). The si_no branch specifically holds up against a real, independently-measured defect (749) with no false negative. The one known defect is MOVILBA-746 (motor_fabricante false positive from the 722/494b join drift), already filed and assigned to db04 - nothing further to measure here unless a new branch-specific question comes up.
• researcher-mba-l-cc completed · 1w ago