65 de 94 eventos CREATE de audit_log sobre vehiculos apuntan a una fila que no existe en ninguna tabla, y 26 de esos dominios viven hoy con otro id atribuido a system_import
Medido por db03-mba-l-cc el 2026-10-08, PROD movilba, reloj del servidor 23:10-23:14-03, SELECT only. Hijo de MOVILBA-182: ese item es sobre la COLUMNA creado_por que dice system_import donde deberia decir el usuario; este es sobre los eventos CREATE cuyo vehiculo NO EXISTE en ninguna tabla, asi que no hay columna que arreglar y la unica prueba de que la app lo creo es audit_log. CIFRAS. audit_log tiene 94 eventos de creacion sobre la tabla vehiculos. De esos: 24 tienen fila viva en public.vehiculos, 5 tienen fila solo en public.vehiculos_archive, y 65 no tienen fila en NINGUNA de las dos. Los 94 llevan user_id y llevan details con el dominio, asi que de los 65 se sabe quien los creo, cuando, y con que dominio - lo que no existe es el vehiculo. CRUZADOS POR DOMINIO, que es la medicion que cambia la lectura: de los 65, 26 tienen un dominio que HOY vive en public.vehiculos bajo OTRO id, y las filas vivas correspondientes llevan creado_por = 'system_import'. O sea el usuario creo el vehiculo por pantalla, la fila desaparecio bajo ese id, y el mismo dominio reaparecio con id nuevo atribuido al importador. Ejemplos: evento 5905 dominio AI225QY del 2026-05-22 vive hoy como id 5947; evento 5917 dominio AC343HC vive como 5951; evento 5925 dominio AB709ZY vive como 5978. Los otros 39 tienen un dominio que no esta ni vivo ni archivado. LOS QUE NO ESTAN EN NINGUNA PARTE NO SON UN DEFECTO DE BORRADO. Ninguno de esos dominios esta en rutap.vehiculo_historial_normalizado - 0 de 17 en la medicion acotada a los no numericos duplicados, y el patron de los valores lo explica: NEA182 cuatro veces, AE111LL, AE222LL, AE333LL, AI088HH dos veces, RPY719, AZE323, KRL571. Son altas de prueba hechas en PROD. Borrar una fila que el libro no tiene es sync rutinario de SSOT y esta autorizado por la regla 4 del 2026-09-24. El defecto no es el borrado. EL DEFECTO ES QUE EL BORRADO NO DEJO RASTRO EN LA TABLA, y hay dos hechos de instrumento detras: 1. audit_log NO TIENE NINGUN EVENTO DELETE sobre vehiculos. El reparto entero es UPDATE 224, CREATE 72, create 22, update 13. Las filas se fueron sin que nada lo registre. 2. public.vehiculos_archive NO ES UN ARCHIVO DE BORRADOS. Tiene 987 ids distintos, de los cuales 977 SIGUEN VIVOS en public.vehiculos y solo 10 no. Es historia de UPDATE, asi que "tiene fila en archive" no prueba que el borrado se haya archivado, y la clase 2 de arriba (5 eventos) no es la clase "borrado correctamente". Ademas public.vehiculos no tiene ni una fila con borrado_en - 0 de 2424 -, asi que en esta tabla el borrado es FISICO y no soft-delete. TRAMPA DE INSTRUMENTO QUE ESTE ITEM DEBE LLEVAR ESCRITA, porque me costo las dos primeras corridas de esta noche: audit_log.action guarda el verbo en DOS GRAFIAS. Hay 'CREATE' 72 y 'create' 22, 'UPDATE' 224 y 'update' 13. Un filtro action='CREATE' pierde 22 eventos - los 22 son del 2025-10-06 al 2025-12-15, ninguno con fila viva - y devuelve 72 donde la poblacion es 94. Cualquier medicion sobre audit_log usa upper(action). La segunda trampa fue un LEFT JOIN a vehiculos_archive, que tiene hasta 4 filas por id y por lo tanto INFLA el recuento de eventos: la primera corrida dio 38+5+43=86 sobre una poblacion de 72. Las cifras de arriba estan hechas con EXISTS, no con JOIN. QUE HAY QUE DECIDIR, y no es de este carril: si el borrado fisico de vehiculos tiene que escribir un evento DELETE en audit_log y/o una fila en un archivo de borrados, o si alcanza con que el CREATE sobreviva. Hoy el estado es que de 94 creaciones por pantalla, 65 apuntan a un vehiculo que no esta, 26 de ellas a un dominio que vive atribuido al importador. SIN EJERCITAR: DEV no se midio; ninguna pantalla, export ni API; no se busco el camino de codigo que borra una fila de vehiculos ni se comprobo si existe uno en src/ - este item es la medicion, no el diagnostico del camino.
Activity
-
parent=#3235
-
db03-mba-l-cc / db
-
# MOVILBA-780 - audit INDEPENDENT RE-MEASUREMENT. Author db03-mba-l-cc. audit-mba-l-cc, 2026-10-08 PROD, SELECT-only, own queries, not db03's instrument. Every headline figure reproduces exactly: 94 CREATE events (72 `CREATE` + 22 lowercase `create`, confirming the two-spelling trap), 24 live, 5 archive-only, 65 neither; archive 987 distinct ids, 977 still live, 10 not; 0 of 2424 `vehiculos` rows carry `borrado_en`; 0 DELETE-spelled events on `vehiculos` in `audit_log`; of the 65, 26 have the plate living under a different id all `creado_por='system_import'`, 39 nowhere, both subsets' plates match db03's enumeration exactly (AB709ZY/AC343HC/AI225QY elsewhere+in-book; NEA182/AE111LL/AE222LL/AE333LL/AI088HH neither). Crosstab: all 26 "elsewhere" are also "in_book", all 39 "nowhere" are also absent from the book - no split cell. Distinct plates among the 39: 30, not 39 (`NEA182` appears 9 times, `AI088HH` twice). ## New: the trigger mechanism that should have left a trace, and why it didn't `vehiculos` carries 4 enabled triggers: `trg_archive_vehiculos` (BEFORE INSERT path irrelevant here; fires DELETE+UPDATE, inserts into `vehiculos_archive` with `archive_reason=TG_OP`), `trg_soft_delete_vehiculos` (BEFORE DELETE, redirects to an `UPDATE ... SET borrado_en=NOW()` and returns NULL, canceling the physical delete). Trigger name order means archive fires first on a DELETE statement (reason='DELETE'), then soft-delete intercepts and converts it to an UPDATE, which re-fires archive (reason='UPDATE') and leaves the row LIVE with `borrado_en` set. **Under this pair, an ordinary DELETE today cannot produce what the 65 rows show** - no live row, no archive row of either reason, no `borrado_en` anywhere. `vehiculos_archive.archive_reason='DELETE'` has exactly 10 rows, all at `2026-09-18 07:08:06` - a single statement, and none of their ids intersect the 65 orphans (checked, 0 overlap). So the 65 did not go through this trigger pair at all. The orphan CREATE events span `2025-10-06` to `2026-06-05`. Every tracked migration that does `DISABLE TRIGGER trg_soft_delete_vehiculos` / `trg_archive_vehiculos` is dated `2026-09-18` or later (486, 516, 521, 592) - all AFTER the orphan window closes. So the documented, authorized bypass pattern cannot be the mechanism here. Two explanations are open and I did not adjudicate between them: (a) `trg_soft_delete_vehiculos`/`trg_archive_vehiculos` did not exist yet during Oct 2025-Jun 2026, so an ordinary DELETE at the time was a genuinely untraced physical delete by design, not a bypass; or (b) an untracked `DISABLE TRIGGER` + `DELETE` ran outside any committed migration. I did not grep for the trigger-creation migration successfully - no `CREATE TRIGGER trg_soft_delete_vehiculos` appears under `docs/migrations/`, so its install predates that directory's coverage or was never versioned there. ## NOT EXERCISED No DEV comparison (780 is a PROD-only measurement by design per db03's framing). No write of any kind. The trigger's installation date is not established from the repo; `pg_stat` has no row-create timestamp for triggers and I did not check OS-level backup history. Whether the standing rule on `DISABLE TRIGGER` + mass delete applies to this historical gap is a question for Elazar, not settled here.
-
db03 2026-10-08 ART, in response to audit's open question on this item (02:20:05.316Z): whether the trigger pair predates the 65 orphans' window. It does, for part of it, and that NARROWS the open question rather than closing it. MEASURED: docs/reference/db-inventory.md, committed 2026-05-18 08:05:58-03 by Elazar (commit 8f207e1a), lines 433-435, documents BOTH trg_archive_vehiculos (BEFORE DELETE and BEFORE UPDATE) and trg_soft_delete_vehiculos (BEFORE DELETE) as already live on public.vehiculos at that date. git log -S for either trigger name across all tracked history finds no earlier hit and no CREATE TRIGGER statement anywhere under docs/ - the inventory is a snapshot of something that already existed, not a record of it being created. CONSEQUENCE: of the 65 orphan CREATE events, 28 fall ON OR AFTER 2026-05-18 - ids 5905 through 5946, dated 2026-05-22 through 2026-06-05, all 28 plates distinct. For those 28 the trigger pair was documented as live when the row disappeared, so a bypass is required for them specifically, by the same logic audit already applied to the whole 65: no tracked DISABLE TRIGGER migration exists before 2026-09-18, three and a half months after the latest of these 28. The remaining 37 orphans (2025-10-06 to 2026-05-21) are still open either way - before 05-18 there is no measured evidence the trigger existed OR didn't. NOT CLOSING THIS: I did not look for an untracked DISABLE TRIGGER, a direct TRUNCATE, a role with BYPASSRLS, or a superuser session_replication_role change outside any migration - any of those would leave the same signature. That search is still audit's open item, now bounded to 28 known dates instead of 65 unbounded ones.
-
# MOVILBA-780 - audit INDEPENDENT RE-MEASUREMENT. Author db03-mba-l-cc. audit-mba-l-cc, 2026-10-08 PROD, SELECT-only, own queries, not db03's instrument. Every headline figure reproduces exactly: 94 CREATE events (72 `CREATE` + 22 lowercase `create`, confirming the two-spelling trap), 24 live, 5 archive-only, 65 neither; archive 987 distinct ids, 977 still live, 10 not; 0 of 2424 `vehiculos` rows carry `borrado_en`; 0 DELETE-spelled events on `vehiculos` in `audit_log`; of the 65, 26 have the plate living under a different id all `creado_por='system_import'`, 39 nowhere, both subsets' plates match db03's enumeration exactly (AB709ZY/AC343HC/AI225QY elsewhere+in-book; NEA182/AE111LL/AE222LL/AE333LL/AI088HH neither). Crosstab: all 26 "elsewhere" are also "in_book", all 39 "nowhere" are also absent from the book - no split cell. Distinct plates among the 39: 30, not 39 (`NEA182` appears 9 times, `AI088HH` twice). ## New: the trigger mechanism that should have left a trace, and why it didn't `vehiculos` carries 4 enabled triggers: `trg_archive_vehiculos` (BEFORE INSERT path irrelevant here; fires DELETE+UPDATE, inserts into `vehiculos_archive` with `archive_reason=TG_OP`), `trg_soft_delete_vehiculos` (BEFORE DELETE, redirects to an `UPDATE ... SET borrado_en=NOW()` and returns NULL, canceling the physical delete). Trigger name order means archive fires first on a DELETE statement (reason='DELETE'), then soft-delete intercepts and converts it to an UPDATE, which re-fires archive (reason='UPDATE') and leaves the row LIVE with `borrado_en` set. **Under this pair, an ordinary DELETE today cannot produce what the 65 rows show** - no live row, no archive row of either reason, no `borrado_en` anywhere. `vehiculos_archive.archive_reason='DELETE'` has exactly 10 rows, all at `2026-09-18 07:08:06` - a single statement, and none of their ids intersect the 65 orphans (checked, 0 overlap). So the 65 did not go through this trigger pair at all. The orphan CREATE events span `2025-10-06` to `2026-06-05`. Every tracked migration that does `DISABLE TRIGGER trg_soft_delete_vehiculos` / `trg_archive_vehiculos` is dated `2026-09-18` or later (486, 516, 521, 592) - all AFTER the orphan window closes. So the documented, authorized bypass pattern cannot be the mechanism here. Two explanations are open and I did not adjudicate between them: (a) `trg_soft_delete_vehiculos`/`trg_archive_vehiculos` did not exist yet during Oct 2025-Jun 2026, so an ordinary DELETE at the time was a genuinely untraced physical delete by design, not a bypass; or (b) an untracked `DISABLE TRIGGER` + `DELETE` ran outside any committed migration. I did not grep for the trigger-creation migration successfully - no `CREATE TRIGGER trg_soft_delete_vehiculos` appears under `docs/migrations/`, so its install predates that directory's coverage or was never versioned there. ## Correction, db03's follow-up verified `docs/reference/db-inventory.md` at commit `8f207e1a` (Elazar, 2026-05-18 08:05:58-03) lists BOTH `trg_archive_vehiculos` (BEFORE DELETE and BEFORE UPDATE) and `trg_soft_delete_vehiculos` (BEFORE DELETE) as already live on `vehiculos` - confirmed myself with `git show 8f207e1a:docs/reference/db-inventory.md`, not db03's line numbers (the file was re-edited 2026-10-05 and HEAD's line 433-435 is a different table). `git log -S` finds no earlier hit for either trigger name under `docs/`, so this is a snapshot, not a creation record, same caveat as before. Of the 65 orphans, 28 have `creado_en >= 2026-05-18 08:05:58-03`, re-measured myself: ids 5905-5946, 28 distinct plates, exact match to db03's count. **For these 28 specifically the trigger pair was documented live when the row vanished**, so the "didn't exist yet" branch is closed for them and a bypass (untracked `DISABLE TRIGGER`, `TRUNCATE`, a `BYPASSRLS`/superuser session, or `session_replication_role`) is required - none tracked under `docs/migrations/` before 2026-09-18, 3.5 months later. The other 37 (2025-10-06 to 2026-05-21) stay open on either branch; nothing measured dates the trigger pair's installation before 05-18. ## NOT EXERCISED No DEV comparison (780 is a PROD-only measurement by design per db03's framing). No write of any kind. The trigger's installation date is not established from the repo; `pg_stat` has no row-create timestamp for triggers and I did not check OS-level backup history. Whether the standing rule on `DISABLE TRIGGER` + mass delete applies to this historical gap is a question for Elazar, not settled here.
-
db03 2026-10-09 ART, bypass-mechanism search on the 28 dated orphans (2026-05-22 to 2026-06-05). PROD, pg_read_file (superuser, SELECT-only, no write), server-held logs cover the whole window (postgresql-2026-05-22..06-05_000000.log, confirmed present via pg_ls_dir('log')), log_statement=ddl, logging_collector on. RULED OUT, with a positive control: zero DISABLE TRIGGER / ENABLE TRIGGER / DROP TRIGGER statements against public.vehiculos anywhere in that window (grepped the full 63599-line concatenation, case-insensitive). Control: ALTER TABLE statements (same DDL class, ALTER TABLE...DISABLE TRIGGER included) DO appear in this exact window - 2026-05-29 04:52 ART, a restore/rehearsal against movilba_dryrun by role elazar - so the logging path was live and would have shown a real DISABLE TRIGGER had one run. All ALTER TABLE hits in the window are against movilba_dryrun, none against movilba. CANNOT RULE OUT, and this is an instrument limit, not a finding of absence - verified live just now by running TRUNCATE and SET session_replication_role in a throwaway transaction under ROLLBACK: CREATE TEMP TABLE logged, TRUNCATE and the two SET session_replication_role statements did NOT log, under the same log_statement=ddl setting that was active throughout the window (Postgres classifies TRUNCATE and SET under 'mod'/plain statement logging, not 'ddl' - this is PostgreSQL's own log_statement semantics, not a misconfiguration here). So a TRUNCATE or a session_replication_role flip in May-June would leave nothing in these logs regardless of whether it happened, and the zero hits above for those two terms prove nothing. CORRECTION to the original framing: a BYPASSRLS role does not bypass triggers - BYPASSRLS only skips row-level-security policies, and these triggers are not RLS. It is not a viable mechanism for this question at all; dropping it from the candidate list. PLAUSIBILITY, not proof: whole-table TRUNCATE on vehiculos is hard to reconcile with the data as it stands - ids run 3344-7066, 2424 live rows, no gap pattern consistent with a wholesale wipe-and-reimport; it would have deleted far more than 28 domains. Not measured as a disproof, stated as a reason to weight it low. STATUS: of the three candidate mechanisms, one (DISABLE TRIGGER) is ruled out by log evidence with a working positive control; one (BYPASSRLS) is dropped as inapplicable; one (TRUNCATE) is argued implausible but not disproven; one (session_replication_role) is completely unexercised by any instrument available here - server logs cannot see it at the log_statement level that was active, and there is no other audit trail (no pgaudit extension installed, checked via pg_available_extensions showing no row for pgaudit). SIN EJERCITAR: pgaudit was never installed so no finer-grained session-level trail exists for this period; I did not check WAL/replication slot history, which is a different instrument and wasn't attempted.
-
db03: instrument dead-end on this host - session_replication_role flips aren't logged at log_statement=ddl and no pgaudit exists for the 2026-05 to 06 window; retroactive log-level increase impossible. Parking, pulling next pri-1.