Columnas de negocio que guardan un id de catalogo sin FK que lo restrinja: 7 sin FK alguna mas la clase de FK a opciones(id) sin restringir catalogo

Sub-tickets — 3/3 done

Activity

• coder03-mba-l-cc created · 1w ago
• db03-mba-l-cc note · 1w ago
  db03-mba-l-cc 2026-10-08 - MOVILBA-738 census. Every business column that resolves an id against public.opciones, both bases. Read-only: the instrument writes two TEMP tables and every transaction ended in ROLLBACK. Instrument now IN THE REPO, re-runnable, not left in /tmp: tools/movilba-738-censo-catalogos.sql, sha256 6ad786d299c481c4c0e4af4657b487ff73962ddfc2322cac0b8e92f73d103f1d, UNTRACKED. Both results below were reproduced from that file, not only from the ad-hoc version I started with. ## First, the correction that reframes the ticket: this shape was ALREADY censused Before anything I report as new: docs/reference/movilba-481-opciones-view-join-census.md is a census of this exact shape, by view instead of by column, and src/repositories/parque-movil.ts:595-612 already handles the clase_licencia case in code with a UNION of both catalogs, naming it MOVILBA-308. So the wrong-catalog FK is not an unexamined class, and I am not reporting licencias_choferes as a discovery. What my run adds is an independent re-derivation at today's date, which AGREES with 481 on the live cases, REFUTES one of its two open items, and finds one class 481 does not cover. ## A. Columns pointing at MORE THAN ONE catalog, with FK PROD 0 columns DEV 1 column public.licencias_choferes.clase_licencia_id 38 non-null personas/clase_licencia 35 rows ids 38-44, labels A B C D E F G licencia/clase_lnc 3 rows ids 293/295, labels "D.1 - Hasta 8 pasajeros", "D.3 - Mas de 20 pasajeros" Matches 481's row exactly (38/0/0 DEV, 0/0/0 PROD) and matches the in-code comment: both catalogs are real and live, neither is noise, and the general class A..G and the passenger subclass D.x are different things. The 3 clase_lnc rows are licence ids 36-38, the newest; the 35 letter rows are ids 1-35, five of each class, which is a seeded pattern. I cannot attribute either group to a writer: licencias_choferes has no creado_en/creado_por columns at all, and audit_log holds zero rows for licencias_choferes, asociaciones_entidad_persona or opciones. So which code path writes which catalog is NOT established by the database, and I am not inferring it from the id ordering. PROD has zero licencias_choferes rows. The column, the FK and both catalogs exist in PROD, so the split is reachable there, not present there. ## B. Name/catalog mismatch - reported, NOT a defect list 17 rows PROD, 21 DEV, and the count reconciles entirely to correct states. Si/No fields resolve against the generic general/respuesta_binaria by standing rule, which accounts for 15 of PROD's 17 (adas_presente, wifi, mdvr, pantallas, cabina_antivandalica, identidad, conexiones_usb, aire_acondicionado, carroceria_aire_acondicionado and the vhn repeats); motor_unidad_propulsora_id -> motor/unidad_propulsora is a prefix difference; and carroceria_categoria_id -> vehiculo/categoria_regulatoria is the known MOVILBA-709 case on the retired-but-authoritative catalog MOVILBA-546 kept. 15 + 1 + 1 = 17. DEV adds the validadoras pair (modalidad_id -> validadoras/modalidad_validadora, tipo_id -> validadoras/tipo_validadora, 46 rows each), also naming, not wrong catalog. Nothing in section B is a finding. ## C/D. Coverage of the FK pass PROD 29 columns with rows, 95844 rows resolving against opciones DEV 37 columns with rows, 98169 rows One column in both points at a BORRADA option: rutap.vehiculo_historial_normalizado.carroceria_categoria_id, 5558 PROD / 5560 DEV. Known, and the reason MOVILBA-123 excludes that catalog from the suffix. ## REFUTED: 481's open item 2, carrocerias.carroceria_aire_acondicionado_id 481 lists it in bold as "colision de dos catalogos vivos, misma forma que clase_licencia_id en 308", with PROD at "69 en general + 1 en carroceria.aire_acondicionado", and its "Que sigue" item 2 is to establish the UNION pair and verify PROD row id=70 against the book first. Measured today, both bases: carrocerias 17 rows total, 17 live, 16 with the column set carroceria_aire_acondicionado_id -> general/respuesta_binaria 16 rows, label "Si", option live rows pointing at carroceria/aire_acondicionado 0 options existing in carroceria/aire_acondicionado 0 <-- in BOTH bases There is no second catalog to collide with: carroceria/aire_acondicionado holds zero options, so no row can point at it. The population is also 17 where 481 read 70. That item cannot be worked as written and should be closed or re-derived rather than left as queued DDL. I am not asserting how it changed - 481 measured a different population and I did not reconstruct which. ## NEW, and the class 481 does not cover: catalog columns with NO foreign key 481 censuses columns by the view JOIN that resolves them, so a column with no FK is still in scope there if a view joins it - but the integrity question is not asked anywhere. Seven columns, the same seven in both bases, carry a catalog id with no FK at all, so neither the catalog nor the EXISTENCE of the id is constrained: public.personas.tipo_persona_id 36 PROD / 82 DEV public.ramales.sentido_id 36 PROD / 41 DEV public.ramales.temporada_id 36 PROD / 41 DEV public.ramales.tipo_dia_id 36 PROD / 41 DEV public.entidades.tipo_entidad_id 25 PROD / 46 DEV public.carrocerias.carroceria_piso_id 16 PROD / 16 DEV public.numeros_telefonicos.tipo_telefono_id 9 PROD / 17 DEV All seven resolve to the catalog their name implies, so there is no wrong-catalog instance among them. There is an orphan instance, DEV only: public.personas.tipo_persona_id = 1, 3 rows (ids 172, 173, 174), 2 of them LIVE 481 records this column as "profilactico, unico catalogo, 79/-/-" for DEV. 79 is exactly my count of rows that RESOLVE; the table has 82 non-null. The 3 that resolve to nothing were invisible to a census that joins and groups, because a row matching no option contributes no group. That is the finding: not a wrong label, a missing row. Whether opciones id 1 ever existed is UNRESOLVED and I am not claiming it never did. min(opciones.id) is 2 today, which only establishes that id 1 is not live. There is no opciones_archive table in either base (the only relation matching %opcion% is opciones itself), and audit_log has zero rows for any opciones table, so neither instrument can tell a hard delete from an id that was never issued. The remedy differs - remap the 3 rows, or restore an option - so this needs resolving before anyone writes. Provenance of the 3 rows: origen_ingreso = 'legacy_unknown'. That is the class for rows whose provenance cannot be established, so it does NOT establish that they are imported, and it does not establish that they are user test data either - ruling 1 names exactly this gap, since the tag is stamped on INSERT and never re-stamped. Unclassified, therefore not covered by the DEV-test-divergence carve-out on the strength of the tag. I have not touched them. What the surfaces show for them: v_personas_completas.tipo_persona and v_personas_list.tipo_persona both emit NULL for all three. The field is silently blank, with nothing distinguishing "no tipo_persona recorded" from "points at an option that is not there". An FK would have refused the write; the LEFT JOIN absorbs it. ## Instrument artifacts - NOT findings, and left visible on purpose Pass 2 joins by bare id, so any column holding another table's id collides with opciones.id arithmetically: public.audit_log.record_id - the audited record's id in its own table. Reports 2 catalogs PROD / 3 DEV and 420 PROD / 840 DEV "orphans". Not a catalog column. rutap.vdc_mdvr_import_baseline.snapshot_id - a snapshot id, 28 DEV rows colliding with transporte/tipo_dia. Named rather than filtered, and documented in the script header, because this same collision is what would make a future run look like it found something. ## Positive controls Section A's PROD zero has a real positive control and no synthetic one is needed: the same script, same code path, returns the 35/3 split in DEV, so a two-catalog column IS reported when present. Section E3's PROD zero for personas.tipo_persona_id is controlled the same way by DEV's 3 orphans. Both results were reproduced from the repo copy of the script. ## NOT covered - _archive tables are excluded from pass 2 and have no FK pass of their own. They hold the same columns, so an archived row can carry a wrong-catalog or orphan id too, and a hard delete of an opciones row takes the label from them - the MOVILBA-489 interaction. Not measured. - Views and matviews: only base tables enumerated. 481 covers the view side. - Any catalog column whose name does not end in _id and has no FK to opciones is invisible to both passes. - Which code path writes which catalog for clase_licencia_id is NOT established - no write attribution exists in the schema or audit_log, and I did not read the dropdown/form code far enough to state which catalog each surface offers. That is the question that decides whether PROD's zero rows are a latent or a live defect, and it is open. - Nothing was fixed and no DDL is proposed. Constraining any of these columns to one catalog is a data-model decision, and for clase_licencia_id it cannot be made at all until the duplicate-catalog question (A..G vs D.x as two catalogs for one column) is answered - which is MOVILBA-308's open end, not mine to settle.
• db03-mba-l-cc note · 1w ago
  db03-mba-l-cc 2026-10-08 - MOVILBA-738 addendum. The number that sets severity, plus the 481 refutation landed in the doc. ## PROD orphan count, per each of the 7 no-FK columns Counted DIRECTLY per column, not derived from the census join. This matters: the census groups by the result of the join, so a column whose values ALL fail to resolve contributes no group and reads as absent. The count below is `WHERE col IS NOT NULL AND NOT EXISTS (SELECT 1 FROM opciones WHERE id = col)`, which cannot hide that case. columna PROD huerfanos DEV huerfanos public.personas.tipo_persona_id 0 3 public.ramales.sentido_id 0 0 public.ramales.temporada_id 0 0 public.ramales.tipo_dia_id 0 0 public.entidades.tipo_entidad_id 0 0 public.carrocerias.carroceria_piso_id 0 0 public.numeros_telefonicos.tipo_telefono_id 0 0 PROD is ZERO on all seven. So this is not a PROD data defect and it does not change priority on that axis: it is a MISSING-FK HARDENING item, not a data-repair item. The only orphan instance in either base is DEV's 3 personas rows with tipo_persona_id = 1 (ids 172-174, 2 live). Positive control for the PROD zeros: the same predicate, same session, returns 3 for personas.tipo_persona_id in DEV, so the query does report orphans when they exist. The PROD zeros are measured absences, not an unexercised probe. One qualifier on applying ruling 1 to those 3 rows. The conclusion (do not touch them) holds, because PROD has no instance and nothing is owed there. But the premise should not be stated as "a user created them": their origen_ingreso is 'legacy_unknown', which is the class for rows whose provenance cannot be established, and the tag is stamped on INSERT and never re-stamped - exactly the gap ruling 1 names. So they are DEV-only and unclassified, not DEV-only and known to be user-created. The remedy is the same either way; the evidence is weaker than the tag looks. ## 481's open item 2 refuted IN THE DOC, not left as prose docs/reference/movilba-481-opciones-view-join-census.md edited, sha256 499db72177f2401186e87265444c5c92406e919e2aa6b0ab50d87de236fdb73b, TRACKED and MODIFIED (no gates run by me, no commit). Four places, so the file does not contradict itself: 1. The table row for carrocerias.carroceria_aire_acondicionado_id: struck through, re-stated as single-catalog with today's 16/0/0 in both bases. 2. A new "Correccion 2026-10-08" section carrying the measurement table: carrocerias 17 rows total in both bases, 16 with the column set, 16 resolving against general/respuesta_binaria with the live label Si, 0 pointing at carroceria/aire_acondicionado, and 0 OPTIONS EXISTING in carroceria/aire_acondicionado. A catalog with no options cannot be collided with. 3. "Que sigue" item 2: struck out as sin objeto, and it records that the PROD row id=70 the item ordered verified against the book does not exist - the population is 17 rows, not 70. 4. The COALESCE note, which still asserted that the second source column resolved against two catalogs. Corrected, with the prior wording named so the change is visible rather than silent. What the edit does NOT claim: I did not reconstruct how the original 70-row population became 17, and I say so in the doc. This is today's measurement with its instrument named replacing a conclusion that no longer holds, not a diagnosis of the earlier measurement. The method lesson is recorded next to the 2026-09-18 correction it rhymes with: count the options in EACH catalog before declaring a collision, because an empty catalog and a catalog nobody points at look identical to a query that groups by the join's result. ## State Instrument tools/movilba-738-censo-catalogos.sql stays UNTRACKED pending a verdict, as instructed. dad14cbd untouched. No write to either base; every measurement in this addendum was read-only and the census transactions ended in ROLLBACK.
• db03-mba-l-cc descriptionChanged · 1w ago
  was: Medido por db03 el 2026-10-08 trabajando el sufijo de MOVILBA-123/737. DEFECTO: la FK de la columna de tipo_asociacion es REFERENCES opciones(id) SIN restriccion de catalogo, asi que acepta cualquier opcion de cualquier catalogo. Las 2 filas aep de DEV guardan la opcion 113, que pertenece a personas/tipo_persona ('Apoderado') y esta VIVA - no es una opcion borrada ni un id huerfano, es una opcion de otro catalogo. El dato pasa toda validacion porque la unica que hay es la existencia del id. Por que importa mas que las dos filas: el catalogo correcto se elige en la aplicacion y nada en la base lo sostiene, asi que el mismo error entra por cualquier camino de escritura, presente o futuro. Un raise en la base es la unica capa que un boton de bypass no puede ignorar. ALCANCE: censar primero cuantas columnas de negocio con FK a opciones(id) carecen de restriccion de catalogo en las DOS bases - el censo es la entrega, no el arreglo de las 2 filas. Las 2 filas de DEV son de prueba y se corrigen o se descartan segun quien las creo. SELECT only hasta que haya veredicto y un GO para el DDL. La restriccion se disena contra el catalogo correcto por columna, no se agrega a ciegas.
• coder03-mba-l-cc titleChanged · 1w ago
  Columnas de negocio que guardan un id de catalogo sin FK que lo restrinja: 7 sin FK alguna mas la clase de FK a opciones(id) sin restringir catalogo
• db03-mba-l-cc note · 1w ago
  db03 2026-10-08, from the MOVILBA-737 apply: a LIVE instance of this item's second class - an FK to opciones(id) that does not restrict the catalog - with rows already wrong, not just a shape that permits it. asociaciones_entidad_persona.tipo_asociacion_id is 'FOREIGN KEY (tipo_asociacion_id) REFERENCES opciones(id)', with no grupo_nombre/campo_nombre restriction, so it accepts any option. DEV's 2 live rows store option 113, which belongs to personas/tipo_persona ('Apoderado'), not to entidades/tipo_asociacion. The FK is satisfied and the field shows a live label from a DIFFERENT catalog. PROD has 0 rows on that column, so the bad data is DEV-only even though the permissive FK is in both. This is the same shape MOVILBA-709 already fixed elsewhere by a COMPOSITE FK onto the opciones(id, grupo_nombre, campo_nombre) triple plus the matching unique constraint, so the instrument for the fix exists and does not need designing. Measured as a side effect of 737 and not re-derived here; the 737 apply does not change it either way - the suffix CASE now installed on that field reads borrado_en of whatever option the column points at, so a wrong-catalog option that is alive renders as a plain live label. NOT MEASURED: whether any other column in this item's list has rows pointing outside its own catalog. The 737 run only looked at tipo_asociacion.
• db03-mba-l-cc note · 1w ago
  db03 2026-10-08 22:33Z, measured in BOTH bases while working MOVILBA-371, SELECT only. This is the 'siete sin FK alguna' half of this ticket, now measured rather than named. Detail in docs/reference/movilba-371-unidad-propulsora-no-libro.md (sha256 ebf82655), section 'Hallazgo lateral'; instrument section 7 of tools/movilba-371-censo-unidad-propulsora.sql (sha256 68f5a67c). THE SEVEN, live tables, identical in both bases, as rows-resolving/rows-nonnull: personas.tipo_persona_id -> personas/tipo_persona 36/36 PROD, 79/82 DEV; entidades.tipo_entidad_id -> entidades/tipo_entidad 25/25, 46/46; ramales.sentido_id -> transporte/sentido 36/36, 41/41; ramales.temporada_id -> transporte/temporada 36/36, 41/41; ramales.tipo_dia_id -> transporte/tipo_dia 36/36, 41/41; carrocerias.carroceria_piso_id -> carroceria/piso 16/16, 16/16; numeros_telefonicos.tipo_telefono_id -> contacto/tipo_telefono 9/9, 17/17. THE DEFECT IS NO LONGER HYPOTHETICAL. DEV personas 172, 173 and 174 carry tipo_persona_id = 1, which is not an opciones id at all. A declared FK would have rejected them. And opciones IS the target the code intends for that column, not my inference from its name: src/repositories/personas.ts:410 does leftJoin(opciones, eq(personas.tipoPersonaId, opciones.id)). Being a LEFT join those three rows do not fail - they come out with a NULL label, which is the silent form of this defect. PROD resolves 36 of 36. INSTRUMENT DISCRIMINANT, because a membership test over small integers false-positives: what separates a real reference from a numeric collision is the values landing in ONE catalog coherent with the column's name. audit_log.record_id resolves 10 of 430 spread across contacto/tipo_correo and personas/tipo_persona - it is a row id, not a catalog id. rutap.vdc_mdvr_import_baseline.snapshot_id lands entirely in transporte/tipo_dia, pure coincidence. The seven above are not coincidence: one catalog each, and the column name names it. RETRACTION: my MOVILBA-763 note said there was nothing here for 738. That was the instrument, not the world - 763's census drives off pg_constraint and therefore CANNOT see a column that stores an opciones id without declaring the FK, which is exactly this half of 738. Corrected in the 763 report too (sha256 6a54f992, 'No medido aqui', first bullet). NOT DONE: no DDL proposed, no FK declared, nothing written. Only these seven plus _archive-excluded tables were considered; I did not probe non _id-suffixed columns, and the second half of this ticket (FKs to opciones(id) that do not restrict the catalog) stays as MOVILBA-763 measured it.
• db03-mba-l-cc note · 1w ago
  db03 2026-10-08 ART, taken off the backlog. THE SECOND CLASS OF THIS ITEM IS NOW CENSUSED IN BOTH BASES, and the census produces a counter-example that changes the remedy: the blanket form of this ticket - restrict every FK to its catalog - would BREAK a column that is deliberately two-catalog. Instrument: tools/verification/movilba-738-censo-fk-opciones.sql, sha256 c017c024362b34861020f57cab5fa7b15fcc198cc276122eefa713a3b1117856, taken with sha256sum after the run and not before. SELECT plus one TEMP table, no explicit transaction, no persistent DDL. Ran rc=0 on BOTH bases with its coverage NOTICE printing "38 columnas censadas, una por cada FK de una sola columna a opciones". PROD movilba 17.6, server clock 2026-10-08 22:35:41-03; DEV movilba_dev same pass. Written under the hub lock gates-checkout-movilba-web; untracked, so it needs the coder lane to commit it. SHAPE, keyed by constraint name, each total from its own count(DISTINCT): 39 foreign keys point at public.opciones across 16 tables. 38 of them name opciones(id) alone, so they accept any option of any catalog. Exactly ONE is already composite - rutap's vh_norm_categoria_catalogo_fk, (carroceria_categoria_id, carroceria_categoria_grupo, carroceria_categoria_campo) -> (id, grupo_nombre, campo_nombre) - and it works because public.opciones carries opciones_id_grupo_campo_uniq, UNIQUE (id, grupo_nombre, campo_nombre). So the mechanism this ticket asks for is not hypothetical: it is installed, in production, with its supporting unique constraint, and it is the template. PROD IS CLEAN TODAY AND THE DEFECT IS LATENT THERE. Of the 38 single-column FKs, 29 have rows and all 29 resolve to EXACTLY ONE (grupo_nombre, campo_nombre) pair, and 0 rows anywhere point at a soft-deleted option. The other 9 columns have ZERO rows in PROD and are therefore UNEXERCISED, not verified - among them public.asociaciones_entidad_persona.tipo_asociacion_id, which is the column this ticket's earlier note found already wrong in DEV. So the known live instance does not exist in PROD because nothing has been written there yet. NEW LIVE INSTANCE IN DEV, not previously on this item: public.licencias_choferes.clase_licencia_id spans TWO catalogs over 38 rows - 35 rows on personas/clase_licencia (ids 38-44, A through G, 5 rows each) and 3 rows on licencia/clase_lnc (id 293 D1 x2, id 295 D3 x1). DEV's tipo_asociacion_id still shows the known 2 rows on personas/tipo_persona, and direcciones_postales.tipo_direccion_id shows 6 rows correctly on contacto/tipo_direccion. AND THAT SECOND ONE IS NOT A DEFECT, WHICH IS THE PART THAT MATTERS. src/repositories/parque-movil.ts:595-612 documents it under MOVILBA-308 and joins deliberately against BOTH: "la MISMA columna apunta a DOS catalogos reales y vivos -- ('personas','clase_licencia'), que es la clase general A..G, y ('licencia','clase_lnc'), que es la subclase". The query's join is written as a two-pair allowlist. A three-column FK to opciones_id_grupo_campo_uniq pins exactly one pair, so applying this ticket's remedy to that column would reject rows the application is designed to write. The counter-example was predicted by the header of my own instrument - a distinct pair does not prove error - and it is now an instance rather than a caveat. CONSEQUENCE FOR THE REMEDY, which is what I will propose rather than assume: the fix is per column, in three kinds, and no column moves without naming which kind it is. (1) Single-catalog and populated - 29 columns in PROD, the pair is derivable from the data with no ambiguity, composite FK on the vh_norm_categoria_catalogo_fk template. (2) Single-catalog but EMPTY - 9 columns, the pair cannot come from data and has to be read out of the application path, so each needs its own code citation before any DDL. (3) Legitimately multi-catalog - clase_licencia_id so far, which needs a CHECK over an allowlist of pairs, not an FK. Any proposal that reports a single count of "38 FKs to fix" has merged these three and is wrong by construction. WHERE THE EVIDENCE FOR THE 9 EMPTY COLUMNS ACTUALLY LIVES, and it is not PROD. DEV populates 8 of those 9 - tipo_asociacion_id 2 rows, tipo_direccion_id 6, clase_licencia_id 38, ambito_id 1 on chofer/ambito, sentido_id 1 on transporte/sentido, validadoras.modalidad_id 46 and tipo_id 46, validadoras_historial.evento_tipo_id 59 - and only rutap's motivo_id is empty in both. Those are test rows under ruling 3, so they are NOT evidence of the intended catalog; they only prove the columns are writable and which catalog the app happened to write. For kind (2) the catalog still has to be read out of the code path. Also note the four validadora columns among them: they stay exactly as they are under the hide rule, which is a HIDE and permits no DDL on them, so they are censused and nothing more. TWO INSTRUMENT FACTS, both seen rather than assumed. First, the PROD application role cannot run this census at all - it stops with "permission denied for table chasis_resolver" on rutap.chasis_resolver, so the figures come from the read role that sees both schemas; a per-table loop that tolerated the error would have reported a smaller population as if it were the whole one. Second, my first version of the file declared the TEMP table ON COMMIT DROP, and because the file runs in autocommit the table was dropped at the end of its own CREATE and the next INSERT failed with 'relation "censo_738" does not exist'. That is in the file as a comment now. Both of these were failures I watched happen, which is the only reason the coverage NOTICE above means anything. NOT EXERCISED: no DDL drafted or rehearsed, no CHECK or composite FK written, the 9 empty columns' intended catalogs not yet read out of the code, DEV's remaining columns not broken down individually beyond the two-pair and the known cases, and no screen, export or API surface touched.
• db03-mba-l-cc note · 1w ago
  db03 2026-10-08 ART, addendum to my note of 01:38:56.495Z, and it CORRECTS that note's own arithmetic. That note presented the fix as 'three kinds' in a way that reads as a partition of the 38 single-column FKs. It is not a partition, and the overlap is the whole point. THE COUNTS, each from the same run, PROD movilba as the base that decides: 38 single-column FKs to opciones. Of those, 29 are POPULATED and every one resolves to exactly one (grupo_nombre, campo_nombre) pair - kind 1, where the pair is derivable from PROD data with no ambiguity and the composite FK can be written from the measurement alone. The remaining 9 are EMPTY in PROD - kind 2, where no amount of PROD data can name the catalog and it has to be read out of the application path. 29 + 9 = 38, so those two DO exhaust it. KIND 3 IS NOT A THIRD SLICE, IT IS A PROPERTY THAT CUTS ACROSS. licencias_choferes.clase_licencia_id is legitimately two-catalog, and it is ONE OF THE 9 empty-in-PROD columns, not an extra one. So kind 3 has 1 known member today and that member is already counted inside kind 2. Worse for any count-based remedy: nothing measured so far rules out a kind-3 member hiding inside the 29. A column can show one pair in PROD today and still be designed for two - that is exactly what clase_licencia_id would look like if PROD had been written once. So the reason the count is not the remedy is not that the kinds are three; it is that kind 3 is INVISIBLE TO THE DATA and can only be found in the code, which makes a per-column code citation a precondition for ALL 38, not just for the 9. THE SAVE ITSELF, stated for the item: a catalog-restricting composite FK on clase_licencia_id would reject rows the application is DESIGNED to write. src/repositories/parque-movil.ts:595-612 documents it under MOVILBA-308 - the same column points at ('personas','clase_licencia'), the general class A..G, and ('licencia','clase_lnc'), the subclass - and the query's join is a two-pair allowlist. Measured in DEV: 35 rows on the first pair (ids 38-44, A through G, 5 rows each) and 3 on the second (id 293 D1 x2, id 295 D3 x1). For that column the instrument is a CHECK over an allowlist of pairs, never a three-column FK. LIMIT OF MY SEARCH FOR OTHER KIND-3 COLUMNS, and it is a real limit rather than a caveat: I swept src/ for a two-pair literal of the form ('a','b'), ('c','d') and it returned exactly ONE line - parque-movil.ts:612 - which is also the only line I had as a positive control. A sweep whose single hit is its own control cannot distinguish 'there is one such case' from 'that is the only spelling I searched for'. An allowlist written as an array, as an OR chain, or decided in a service rather than in SQL is invisible to it. So kind 3 is bounded at 'at least 1', not at 'exactly 1'. NOT EXERCISED, unchanged from the earlier note: no DDL drafted or rehearsed, no CHECK or composite FK written, and the intended catalog of the 9 empty columns not yet read out of the code.
• db03-mba-l-cc note · 1w ago
  db03 2026-10-08 ART. KIND 2 IS NOW CITED FROM CODE, which is what my addendum said was the precondition, and it turns up a ROOT CAUSE that reframes this ticket's live instance plus one further defect. WHERE THE INTENDED CATALOG IS DECLARED: src/services/opciones.ts carries a registry mapping a field name to its (grupo, campo) pair - 'tipo_direccion' -> contacto/tipo_direccion (:48), 'tipo_asociacion' -> entidades/tipo_asociacion (:49), 'sentido' -> ramales/sentido (:50). ambito is not in that registry and is pinned in SQL instead, at src/repositories/parque-movil.ts:527-532, as chofer/ambito under MOVILBA-308. motivo_id has no code citation at all in src/ and remains unresolved. ROOT CAUSE OF THIS TICKET'S KNOWN WRONG ROWS, and it is not a typo in a call site. The catalog the code names for tipo_asociacion - entidades/tipo_asociacion - has 3 options and ZERO of them live, identically in PROD and DEV: total 3, vivas 0. So the correct dropdown is EMPTY, in both bases. The 2 DEV rows holding personas/tipo_persona ('Apoderado') were not a careless pick from a long list of plausible catalogs; the intended list renders nothing, and whatever path wrote them reached for one that does render. That changes the remedy for this column: a catalog-restricting FK to entidades/tipo_asociacion would reject EVERY write until those 3 options are undeleted, so the option rows come first and the constraint second, in that order, or the column becomes unwritable. SECOND DEFECT, new on this item: the registry names ramales/sentido for sentido and that pair DOES NOT EXIST in either base, while transporte/sentido exists with 3 live options and is what DEV's single recorridos row actually holds. The instrument is a controlled absence rather than an empty filter - the same IN-list query returned 5 of the 6 pairs I asked for, so it does find pairs that exist. So the code and the data disagree about which catalog sentido is, and the data's answer is the one that has live options. VERIFIED AGREEING, so they need nothing beyond the composite FK: contacto/tipo_direccion, 9 live in both bases, matches DEV's 6 rows; chofer/ambito, 2 live in both, matches DEV's 1 row. CARVED OUT, not measured further and no DDL proposed: the four validadora columns among the 9 - validadoras.modalidad_id, validadoras.tipo_id, validadoras_historial.evento_tipo_id and the instalacion/validadora evento_tipo pair - fall under the hide rule, which is a HIDE and permits no DDL on validadora objects. They are censused and stop there. SO KIND 2's 9 COLUMNS RESOLVE AS: 2 agreeing and ready (tipo_direccion, ambito), 1 blocked on undeleting its own catalog before any constraint (tipo_asociacion), 1 where code and data name different catalogs and the code is the one that is wrong (sentido), 1 kind-3 two-catalog column (clase_licencia_id), 3 validadora columns carved out, and 1 with no code citation found (rutap motivo_id). That is 9. NOT EXERCISED: no DDL drafted or rehearsed, no option row undeleted or proposed for undeletion, no screen or form opened to confirm the empty dropdown renders empty - the emptiness is measured in opciones, not observed in the UI - and motivo_id's writer not found.
• db03-mba-l-cc note · 1w ago
  db03 2026-10-08 ART. THE THREE-PAIR COUNT, and it settles it: ONE WRONG GROUP NAME USED THREE TIMES, not three wrong campos. Measured in both bases, SELECT-only, over all 8 entries of CATEGORY_MAPPINGS (src/services/opciones.ts:44-52) rather than only the three in question, so the agreeing entries act as the control. THE GROUP 'ramales' DOES NOT EXIST. count(*) over public.opciones where grupo_nombre='ramales' is 0 in movilba and 0 in movilba_dev - not 'no live options', zero rows of any kind. So all three declared pairs resolve to nothing: ramales/sentido 0 total 0 live, ramales/temporada 0/0, ramales/tipo_dia 0/0, identically in both bases. AND THE THREE CAMPOS EXIST, UNDER 'transporte', WITH THE SAME NAMES AND LIVE OPTIONS: transporte/sentido 3 total 3 live, transporte/temporada 3/3, transporte/tipo_dia 4/4, identical in both bases. The campo names in the code need no change at all; only the grupo does. That is the difference the question asked for: one token, 'ramales' -> 'transporte', on :50, :51 and :52. THE CONTROL IS THE OTHER FIVE ENTRIES, which rules out an instrument that simply fails to find pairs: entidades/tipo_entidad 13/13, personas/tipo_persona 12/12, contacto/tipo_direccion 9/9, contacto/tipo_telefono 5/5 - all present and live in both bases. So the query does resolve declared pairs, and the three zeros are an absence rather than an empty filter. ENTIDADES/TIPO_ASOCIACION IS A DIFFERENT SHAPE AND MUST NOT BE FIXED THE SAME WAY. Its group is alive - tipo_entidad sits under 'entidades' with 13 live - so this is campo-level soft deletion, not a wrong group name. The three options are id 18 PROPIETARIO 'Propietario', id 19 EMPLEADO 'Empleado', id 20 CONTRATISTA 'Contratista', all three with the SAME borrado_en to the microsecond, 2025-12-11 13:00:08.899207, by the same actor (rol-cuit-2) - one deliberate act, not attrition. Live referrers to those three ids from asociaciones_entidad_persona: 0, in PROD. So undeleting them breaks no existing row. ORDER, which is the operational part and holds for this column only: the three options get undeleted FIRST and the restricting FK goes on SECOND. The reverse makes asociaciones_entidad_persona.tipo_asociacion_id unwritable, because the only catalog the constraint would admit renders an empty list. I am not undeleting anything here - that is a write to opciones and it goes with its own count and scope. NOT EXERCISED: no write of any kind, no DDL, no form or screen opened to see either empty dropdown render, and the question of WHY those three options were deleted in 2025-12 not investigated - only that they were, together, and that nothing references them now.
• db03-mba-l-cc note · 1w ago
  db03 2026-10-08 ART. THE LAST UNCITED COLUMN OF THIS CENSUS NOW HAS ITS WRITER, AND IT IS A SECOND INSTANCE OF KIND (3), NOT KIND (2). My note of 01:40:03.170Z left rutap.vehiculo_historial_normalizado.motivo_id as the one column empty in BOTH bases with no writer found in src/, and therefore as a kind-(2) column whose intended catalog had to be read out of the code. The code is now read and it says the opposite of what kind (2) assumes: the column deliberately accepts TWO disjoint catalogs, so it belongs with licencias_choferes.clase_licencia_id and a single-pair composite FK on it would reject ids the application validates as legal. That moves the count: kind (3) is two columns, not one, and the 9 empty columns are 8. THE WRITER, by path and not by window. The only insert that sets it is src/repositories/vehiculos.ts:1236, motivoId: data.motivoId || null, inside the .values({...}) of the insert at :1227 into vehiculoHistorialNormalizadoInRutap, which is createHistorialRecord (:1131). It is reached from src/services/vehiculos.ts:648 and :743 - two different service methods - and those are called from the two HTTP endpoints src/app/api/vehiculos/[id]/entidad/route.ts:51 and src/app/api/vehiculos/[id]/linea/route.ts:52. The column is bounded to that one .values block; the figure is not from a line window. THE TWO CATALOGS ARE EXPLICIT IN CODE AND VALIDATED SERVER-SIDE. src/services/vehiculos.ts:61-83, validarMotivoCatalogo, carries the comment "motivo_id shares one column across two disjoint catalogs (motivo_transferencia vs motivo_asignacion_linea) - the FK only proves the id is an opcion, so catalog membership must be checked server-side", and it selects from v_motivo_transferencia or v_motivo_asignacion_linea according to which service called it: the entidad path passes 'transferencia' (:633) and the linea path passes 'asignacionLinea' (:728). So the application's own rule is "this id must be in ONE of two catalogs, chosen by the operation", which is precisely the shape an FK cannot express. Note that the in-code comment cites "bwi#60", a notation that no longer resolves to anything - the bare-id form is disabled - so that reference needs re-resolving by title if anyone follows it. MEASURED, both bases, SELECT-only, server clock 2026-10-08 22:47:53-03 PROD / 22:47:54-03 DEV. Both views read grupo_nombre='vehiculo' with borrado_en IS NULL and differ only in campo_nombre; the two pairs are identical in PROD and DEV: vehiculo/motivo_transferencia 6 total 6 live, ids 161 COMPRA, 162 VENTA, 163 DONACION, 164 TRANSFERENCIA_ADMINISTRATIVA, 165 CAMBIO_OPERADOR, 166 OTRO; vehiculo/motivo_asignacion_linea 6 total 6 live, ids 167 ASIGNACION_INICIAL, 168 CAMBIO_LINEA, 169 REEMPLAZO_VEHICULO, 170 EXPANSION_FLOTA, 171 REORGANIZACION, 172 OTRO. Both campos sit under the SAME grupo, so the two catalogs are a campo-level split and not two groups - which is why a three-column FK to opciones_id_grupo_campo_uniq pins one of them and rejects the other six ids outright. THE COLUMN IS STILL UNEXERCISED, and that is a separate fact from having a writer. motivo_id is NULL in 0 of 5558 rows in PROD and 0 of 5727 in DEV - count(motivo_id)=0 in both - and the join to opciones returns 0 rows, which is the same answer from a second query rather than a restatement of the first. The pair probe is not silently empty: the same query returned both pairs with their ids. src/lib/reportes/liquidacion.ts:95-96 already records the same thing from db04 on 2026-10-01 for the 21 rows the endpoints wrote, so this is a column the app can write, offers optionally, and nobody has ever filled. CONSEQUENCE FOR THE REMEDY. motivo_id needs a CHECK over an allowlist of the two pairs, not an FK, exactly like clase_licencia_id - and the allowlist is derivable from code rather than from data, because there is no data. Any proposal that counts it among "the empty columns whose catalog we must discover" has already discovered it and must not pin one pair. NOT EXERCISED: no CHECK drafted or rehearsed in either base, no DDL, no screen or endpoint called, the two service paths not exercised with a wrong-catalog id to see validarMotivoCatalogo reject, and the remaining 8 empty columns still without code citations.
• db03-mba-l-cc note · 1w ago
  db03 2026-10-08 ART, correcting my own note of 01:48:43.877Z on two counts. Both are wording, the measurement underneath did not change, and the remedy does not change either - but one of the two sentences said the OPPOSITE of what was measured, so it is corrected here rather than left to be read. ERROR 1, the inverted sentence. I wrote "motivo_id is NULL in 0 of 5558 PROD and 0 of 5727 DEV". That reads as "never NULL, always populated", which is the reverse. The measured figure was count(motivo_id) = 0, i.e. NON-NULL in 0 rows. Correct sentence: motivo_id is NULL in 5558 of 5558 rows in PROD and 5727 of 5727 in DEV - the column has never been written in either base. ERROR 2, the count that treated the kinds as a partition. I wrote that moving motivo_id to kind (3) "drops the empty set from 9 to 8". It does not. The kinds are not a partition - I corrected exactly this shape on this item at 01:40:03.170Z and then reintroduced it. A column can be BOTH empty and legitimately multi-catalog, and motivo_id is both. The empty set stays at 9 columns, and kind (3) is 2 columns, of which one (motivo_id) is also in the empty 9 and one (clase_licencia_id) is not. "Kind (3) is two columns" stands on its own; the subtraction does not. THE DISTINCT-VALUE CENSUS coder03 asked for, both bases, SELECT-only, server clock 2026-10-08 22:49:42-03 PROD and 22:49:44-03 DEV. Grouping rutap.vehiculo_historial_normalizado by motivo_id with NULL as its own row returns exactly ONE row in each base: motivo_id NULL, 5558 rows in PROD, 5727 in DEV. No value in 161-166, no value in 167-172, and no value outside the two catalogs. Same answer from the LEFT JOIN to opciones grouped by the real (grupo_nombre, campo_nombre) pair rather than by numeric range: one bucket, "sin join: motivo_id NULL", same totals. Separately, each from its own aggregate: filas_totales 5558 / 5727, motivo_id_no_nulo 0 / 0, motivo_id_nulo 5558 / 5727, valores_distintos_no_nulos 0 / 0. POSITIVE CONTROL, because an empty answer from a probe is indistinguishable from a broken probe. The identical LEFT JOIN run against estado_id on the SAME table reports pairs: PROD 1602 rows on vehiculo/estado and 3956 NULL; DEV 1750 on vehiculo/estado and 3977 NULL. So the instrument does resolve a populated catalog column on this table, and motivo_id's single NULL bucket is the column's state and not the query's failure. WHAT THIS SETTLES. The allowlist cannot be measured from data, because there is no data - 0 distinct non-null values - so it comes from code alone: services/vehiculos.ts:61-83 and the two views, the two pairs being vehiculo/motivo_transferencia (161-166) and vehiculo/motivo_asignacion_linea (167-172), 6 live options each, identical in both bases. The "data and code must agree, a disagreement is a finding" check is therefore VACUOUS on this column today, not passed: there is nothing on the data side to agree or disagree. It becomes a real check the first time a row carries a motivo, and it is worth running then. NOT EXERCISED: no CHECK drafted or rehearsed, no DDL, neither service path called with a wrong-catalog id to watch validarMotivoCatalogo reject, and the 9 empty columns still have no DDL of any kind.
• db03-mba-l-cc note · 1w ago
  db03 2026-10-08 ART, pointer only: the sha256 ebf82655 I pinned for docs/reference/movilba-371-unidad-propulsora-no-libro.md in my note of 22:33 is WITHDRAWN - the file is untracked and now 35c8f109cec043aad376c4a7d3250fde100d8f1324bb97d1f28c6a073b9ae830, so the old bytes exist nowhere. Re-pin, the limit that it cannot be diffed, and the re-measurement of every figure in my section against both bases are in MOVILBA-371, note of this same minute. The 38-plus-1 FK count that both items rest on re-measures identically in PROD and DEV at 22:58-03.
• db03-mba-l-cc note · 1w ago
  db03 2026-10-09 ART. SYNTHESIS, no new measurement - this item's own thread already has census, root cause, and orphan counts; this note is the stopping point before a GO is needed for DDL. SCOPE AS IT STANDS: (1) 7 columns with NO FK at all to opciones, censused with orphan counts per column in docs/reference/movilba-371-unidad-propulsora-no-libro.md. (2) FKs to opciones(id) that don't restrict to a catalog GROUP - asociaciones_entidad_persona.tipo_asociacion_id is the live instance, 2 rows in DEV holding opcion 113 (wrong catalog, not orphan/deleted). (3) Root cause for a chunk of class 2: src/services/opciones.ts CATEGORY_MAPPINGS names a grupo that is actually a TABLE name (ramales), not an opciones grupo, so 3 of its 8 entries resolve to nothing - this is a code defect independent of any DDL and can ship on its own without a GO (bug fix, no schema change). WHAT NEEDS A GO: adding a catalog-group CHECK (or a scoped FK via a grupo-qualified lookup) on tipo_asociacion_id and on the 7 no-FK columns is schema-changing DDL that can reject rows a caller is currently allowed to write - not a routine SSOT sync, this is new validation. Per project convention this needs its own GO, separate from any 'obvious bug' rule. NEXT: (a) fix the CATEGORY_MAPPINGS code defect now, no GO needed, ships as its own small PR; (b) route the DDL scope (which columns, CHECK vs scoped-FK design) to Elazar via coder03 for a GO before drafting any migration.
• db03-mba-l-cc blocked · 1w ago
  DDL scope (CHECK/scoped-FK design) routed to Elazar via coder03 for GO; code-fix half (CATEGORY_MAPPINGS) is coder-lane's, not mine