Vehiculo entidad differs from linea's assigned entidad: 35 rows/4 lineas + 11 rows/2 lineas with no assignment (v_audit_vehiculo_vs_linea_entidad)

Sub-tickets

Activity

• wi-cli-venus created · 2026-08-20
• wi-cli-venus note · 2026-08-21
  OBSOLETO A NIVEL ESQUEMA, verificado por coder02 2026-08-21: drizzle/schema.ts:504-508 declara linea_entidad_asignaciones_entidad_id_fkey hacia entidades.id con onDelete restrict, mas uniqueIndex uq_linea_entidad_activa sobre linea_id WHERE borrado_en IS NULL (una entidad viva por linea). El constraint existe. CAVEAT: schema.ts es salida de introspect, o sea que prueba que existia en el ultimo introspect, no que exista en prod ahora - confirmarlo con un SELECT sobre pg_constraint antes de cerrar. Del lado de la app no hay enforcement propio: services/lineas.ts:114-115 solo chequea que entidadId sea truthy y repositories/lineas.ts:222-228 inserta sin verificar existencia, delegando en el codigo de error de Postgres.
• wi-cli-venus note · 2026-08-21
  CAVEAT CERRADO: SELECT sobre pg_constraint en PROD 2026-08-21 devuelve linea_entidad_asignaciones_entidad_id_fkey. El constraint existe en la base, no solo en el archivo de introspect. Este ticket se puede cerrar salvo que su autor haya querido decir otra arista (linea->entidad tiene FK; lo que NO tiene enforcement propio es la app).
• wi-cli-venus verified · 2026-08-21
  CANDIDATO A CIERRE. coder02-mba-l-cc verifico en PROD por SELECT sobre pg_constraint, no leyendo schema.ts: linea_entidad_asignaciones_entidad_id_fkey EXISTE, y tambien uq_linea_entidad_activa. La restriccion de integridad que este item pide ya esta puesta. Queda vivo solo un residuo distinto del titulo: la app no hace validacion propia, se apoya enteramente en la base. Si eso alcanza, se cierra; si se quiere validacion en el choke point de servicio, es otro item con otro titulo.
• wi-cli-venus assigned · 6w ago
  db02-mba-l-cc / db
• wi-cli-venus assigned · 4w ago
  db03-mba-l-cc
• wi-cli-venus titleChanged · 4w ago
  Vehiculo entidad differs from linea's assigned entidad: 35 rows/4 lineas + 11 rows/2 lineas with no assignment (v_audit_vehiculo_vs_linea_entidad)
• wi-cli-venus progress · 4w ago
  DEV and PROD identical 2026-09-18. FKs exist (linea_entidad_asignaciones_entidad_id_fkey, linea_id_fkey) so original title was false. Real defect: entidad_distinta 35 rows/4 lineas; linea_sin_asignacion_vigente 11 rows/2 lineas. By linea: 9->53 vs 69 x1; 11 no assignment x5 (entidad 65); 71: 54 vs 71 x19; 84: 53 vs 69 x10; 85 no assignment x6 (entidad 64); 138: 64 vs 61 x5. Business question (which side is right), related to 166. Kept open.
• 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 fresh, PROD, via public.v_audit_vehiculo_vs_linea_entidad (SELECT, no write). Still open, "NO empezar sin GO" honored - measurement only. PROD TODAY vs the 09-18 note, by linea_id (same grouping column as before): - linea 9: entidad_historial 53 vs entidad_asignada_a_linea 69, x1 - UNCHANGED. - linea 11: linea_sin_asignacion_vigente, entidad_historial 65, x5 - UNCHANGED. - linea 71: 54 vs 71, x17 (was x19 on 09-18) - 2 rows resolved since. - linea 84: 53 vs 69, x10 - UNCHANGED. - linea 85: linea_sin_asignacion_vigente, entidad_historial 64, x6 - UNCHANGED. - linea 138: 64 vs 61, x4 (was x5 on 09-18) - 1 row resolved since. TOTAL: 43 rows today (was 46 on 09-18) - same 6 lineas, same entidad pairs, minor incremental convergence (3 rows), structural divergence otherwise exactly as measured three weeks ago. This is not noise or a stale artifact: same lineas keep reproducing the same entidad mismatch, three weeks apart, two independent measurements. DEV: 50 rows total via the same view (not broken down by linea this pass - DEV test-data divergence from PROD is expected per project rule, not chased further here). STATUS UNCHANGED FROM 09-18's framing: FKs/uniqueIndex on linea_entidad_asignaciones are confirmed to exist (re-verified this session is unnecessary - 08-21's pg_constraint SELECT already settled that the original "no FK" title was false). The live defect is the ORIGINAL one restated by the 09-18 title: two sources of entidad-for-linea (vehiculo_historial_normalizado's own entidad_id on the current period, vs linea_entidad_asignaciones' current row) disagree for 4 lineas and are silent (no row at all) for 2 more - nothing stops them from drifting because nothing ties them together. Business question unresolved: which source wins when they disagree, and whether a FK/CHECK across the two tables is even expressible given vigencia-based assignment vs per-vehiculo override. Related to MOVILBA-166 per the 09-18 note (not independently re-read this pass). NOT APPLYING ANYTHING. Awaiting the business decision this ticket has always needed before any constraint or reconciliation-view design.
• db03-mba-l-cc note · 1w ago
  db03 2026-10-08 ART. FIRST STEP OF THIS ITEM DONE: the measurement the description asks for, SELECT-only in PROD (current_database movilba, server clock 2026-10-08 22:27:53-03). No DDL, no decision taken - constraint vs reconciliation view still needs the GO the item reserves. CENSUS WITH ITS DENOMINATOR: 43 rows in v_audit_vehiculo_vs_linea_entidad, 43 distinct vehiculo_id, over 2424 live vehiculos that have a current history row - so 1.77% diverge. The view's two buckets exhaust the 43: entidad_distinta 32 rows / 4 lineas, linea_sin_asignacion_vigente 11 rows / 2 lineas. The title's 35 is now 32; the 11/2 is unchanged. THE TITLE'S TWO BUCKETS ARE TWO DIFFERENT DEFECTS AND THE SECOND ONE IS NOT WHAT ITS LABEL SAYS. For the 11, numero_linea comes back NULL, which means the LEFT JOIN to lineas did not resolve - the line is soft-deleted, not un-assigned. Measured: lineas id 85 (numero 90) and id 11 (numero 99), both borrado_en 2026-05-19 18:20:56.391 and 18:21:42.144, each with exactly one assignment row deleted in the SAME transaction (entidades 64 M.O.D.O. and 65 COLECTIVEROS UNIDOS). So the 11 are live vehiculos whose CURRENT history row points at a deleted linea, and the motivo string reads as an assignment gap. Bounding the wider class: counting live vehiculos whose current history linea is soft-deleted gives exactly 11 - this is the whole class, not a sample of it. THE 32 ARE THREE PAIRS, NOT A SPRAY: entidad on linea 71 NUEVOS RUMBOS vs 54 17 DE AGOSTO = 17 rows; 69 TRANSPORTES AUTOMOTORES RIACHUELO vs 53 GENERAL TOMAS GUIDO = 11; 61 LA NUEVA METROPOL vs 64 M.O.D.O. = 4. Lineas 25, 26, 84, 151. Three pairs is the shape of three assignment decisions, not of per-row data entry. ONE SUSPICION OF MINE RETIRED BY THE SCHEMA, recorded so nobody re-raises it: the view joins linea_entidad_asignaciones on borrado_en IS NULL with no date predicate, which would fan a vehiculo out once per live assignment and let entidad_distinta fire while another live assignment matches. It cannot happen - uq_linea_entidad_activa is UNIQUE (linea_id) WHERE borrado_en IS NULL, the table has no desde/hasta columns at all, and 43 rows = 43 distinct vehiculo_id confirms no fan-out today. 'vigente' there means 'the one live row', which is exactly what the index enforces. NOT EXERCISED: DEV not measured, no screen or API surface checked, the two remaining motivo branches (historial_sin_linea, historial_sin_entidad) returned no rows today so neither was seen to fire, and nothing establishes WHICH side is right in any of the three pairs - that is the question the GO decides.
• db03-mba-l-cc note · 1w ago
  db03 2026-10-08 ART, correction of my own note of 01:29:11.899Z, same date, and it is the kind that makes two of my notes look like they disagree. That note ended the 32-row paragraph with 'Lineas 25, 26, 84, 151'. Those are numero_linea values and the note does not say so, while my earlier note of 14:55:31.884Z breaks the same 32 rows down by linea_id as 9, 71, 84 and 138. The lists are the same four lines under two different keys, and 84 appears in both by coincidence - linea_id 84 happens to carry numero_linea 84, which is exactly what makes the omission read as a contradiction instead of a missing label. Measured mapping, PROD, SELECT-only, both keys side by side with the pair and the count: linea_id 9 = numero 25, historial 53 vs linea 69, 1 row; linea_id 71 = numero 26, 54 vs 71, 17 rows; linea_id 84 = numero 84, 53 vs 69, 10 rows; linea_id 138 = numero 151, 64 vs 61, 4 rows. Sums to 32 over four lineas, so both notes' totals stand and only my key label was missing. One wording consequence in that note: 'THREE PAIRS' counts distinct entidad pairs, not lines - the 69-vs-53 pair spans two lines (9 and 84). Four lines, three pairs. Any further reference to this item quotes linea_id and names it as such, because the two keys collide on 84.
• db03-mba-l-cc note · 1w ago
  db03 2026-10-08 ART. THIS ITEM'S OPEN QUESTION IS NOT A BUSINESS DECISION, AND THE BOOK ANSWERS MOST OF IT. My two earlier notes today, and the 09-18 framing before them, both park this on "which source wins when they disagree" as if the book were silent. It is not: the RUTAP book carries a per-vehicle operator - rutap.rutap_rows.cuit / identificador_empresa, normalized to rutap.vehiculo_historial_normalizado.entidad_id - so under ruling 3 of 2026-09-24 this is a fidelity question, measurable, not a judgement. MEASURED, PROD, SELECT-only, against the current snapshot. Joining the 43 flagged vehiculos to the book BY DOMINIO, deduplicated per dominio: 0 of 43 are absent from the book, 0 of 43 have a book that contradicts itself (even the 15 whose dominio has more than one row in the snapshot resolve to a single entidad_id), 43 of 43 agree with entidad_historial, and 0 of 43 agree with entidad_asignada_a_linea. So the vehicle side is already faithful - there is no vehiculo row to sync, and the side that disagrees with the book is linea_entidad_asignaciones. The first join I wrote returned 46 and 12 instead of 32 and 11 because a dominio can hold several rows in one snapshot; the inflated pair is wrong and the dedup is what the figures above use. WHY THE ASSIGNMENT CANNOT BE "CORRECTED" EITHER - the real shape, and it retires the constraint half of this item. Each of the four divergent lines is served by TWO operators IN THE BOOK, and the assigned one is always one of the two: linea_id 9 (numero 25) has entidad 69 with 82 dominios and 53 with 48, assigned 69; linea_id 71 (26) has 54 with 73 and 71 with 62, assigned 71; linea_id 84 (84) has 69 with 86 and 53 with 56, assigned 69; linea_id 138 (151) has 61 with 234 and 64 with 83, assigned 61. Across the whole snapshot, 6 of the book's 31 lines carry more than one entidad. linea_entidad_asignaciones can hold exactly ONE live row per line - uq_linea_entidad_activa, UNIQUE (linea_id) WHERE borrado_en IS NULL - so the table cannot represent a co-operated line at all. There is no value of entidad_id that makes those four lines correct, which means a FK or CHECK tying vehiculo entidad to the line's assignment would be UNSATISFIABLE against faithful data. That is the answer to "constraint vs reconciliation view": not the constraint. WHAT IS LEFT FOR ELAZAR is therefore narrower and different from what the item asks: whether linea_entidad_asignaciones stops being one-row-per-line (model change), or stays as it is and is documented as "one of possibly several operators", in which case v_audit_vehiculo_vs_linea_entidad's entidad_distinta bucket is reporting the model's limitation as a data defect and should be re-scoped or retired. I am not deciding that. THE 11 ARE UNAFFECTED BY ALL OF THE ABOVE and still stand as the note of 01:29 describes them: lineas id 85 (numero 90) and 11 (numero 99), soft-deleted 2026-05-19 with their assignments, and 11 live vehiculos whose current history row still points at them. The book does carry both - 85 with entidad 64 over 41 dominios and 11 with 65 over 43 - so the book has not retired those lines and PROD has. OPEN THREAD I AM NOT CHASING IN THIS PASS, with its numbers so nobody has to re-find it: the book puts 48 dominios on linea 9 under entidad 53, and the audit view flags only 1 vehiculo there. The other 47 are either not live in PROD or their current history row carries a different linea_id. That is a PROD-vs-book fidelity measurement on the (linea, entidad) pair across the whole fleet, much wider than this item, and it needs its own census. NOT EXERCISED here: DEV, any screen or API surface, and the per-vehicle comparison for the 2381 vehiculos the audit view does not flag.
• db03-mba-l-cc blocked · 1w ago
  Measurement complete, PROD: all 43 rows verified faithful to the book (vehiculo side is correct in 100% of cases; linea_entidad_asignaciones is the side that disagrees). The constraint-vs-view question the ticket asks is retired: a FK/CHECK is unsatisfiable, because 6 of the book's 31 lineas are co-operated by two entidades and the table allows only one live row per linea (uq_linea_entidad_activa). DECISION NEEDED FROM ELAZAR, narrower than the ticket's original framing: (A) model change - let linea_entidad_asignaciones carry more than one live operator per linea, or (B) keep one-row-per-line as a deliberate simplification and re-scope/retire v_audit_vehiculo_vs_linea_entidad's entidad_distinta bucket (32 rows/4 lineas), since it is reporting the model's own limitation as if it were a data defect. The 11-row linea_sin_asignacion_vigente bucket is unaffected either way - 2 soft-deleted lineas (85, 11) the book still carries live; separate, smaller question: re-point or leave as documented gap. Full figures in notes above.