Aire Acondicionado: 'Con Aire Acondicionado' catalog label keeps getting recreated after deletion

Sub-tickets

Activity

• db03-mba-l-cc created · 2w ago
• db03-mba-l-cc progress · 2w ago
  Checked for an active reseed mechanism (script/import that recreates 126/127 on every run): tools/import/01_add_missing_opciones.sql:32-33 carries the exact literal INSERT ('Con Aire Acondicionado'/'Sin Aire Acondicionado') but the file is RETIRED/fail-fast (lines 1-7, RAISE EXCEPTION on any call) -- it cannot run today, so it is NOT the live recreation mechanism, only a stale artifact holding the wrong text. No other seed/import writes to opciones for this field found in src/, tools/, docs/migrations/ (grepped 2026-09-28). Most likely recreation path: docs/reference/opciones-catalogo.md's own MOVILBA-514 section documents that 126/127 MUST exist for carrocerias.carroceria_aire_acondicionado_id FK integrity (70 rows depend on them) -- an agent who finds them soft/hard-deleted and follows that doc (or the general opciones-check.sh sinfk gate) correctly restores them, because from the doc's own framing a missing catalog id IS a defect. The 8-month loop is Elazar deleting a row the doc mandates and an agent correctly re-adding it per current written rule -- not an agent ignoring instructions. Fix scope, two parts: 1. DB: UPDATE opciones SET nombre_mostrable='Si' WHERE id=126, nombre_mostrable='No' WHERE id=127, in PROD then DEV. Keep id/valor_opcion/grupo_nombre/campo_nombre unchanged (do NOT delete -- MOVILBA-514's FK-integrity reasoning stays valid, only the display text was wrong). 2. Docs: opciones-catalogo.md's MOVILBA-514 section and tools/import/01_add_missing_opciones.sql (dead but readable) both still show the old 'Con Aire Acondicionado' text as if correct -- update or annotate both so nobody reads the old string as current truth and reintroduces it as the 'proper' fix during a future restore.
• coder03-mba-l-cc assigned · 2w ago
  db04-mba-l-ca
• db03-mba-l-cc progress · 2w ago
  HANDOVER db03->db04 (lane consolidation 2026-09-28 13:37). FINAL DESIGN (Elazar confirmed, reverses MOVILBA-514/495): repoint aire_acondicionado from the dedicated carroceria/aire_acondicionado catalog (126 SI/127 NO) BACK to the generic general/respuesta_binaria catalog (24 SI/25 NO) - the same pair every other Si/No field uses (cabina_antivandalica, adas, mdvr, pantallas, wifi, conexiones_usb, identidad). Then HARD DELETE opciones 126 and 127 in both PROD and DEV. Root cause of the 8mo recreate loop: the dedicated catalog's label text ('Con/Sin Aire Acondicionado') read as wrong and kept getting deleted; the actual defect was reusing a bespoke catalog instead of the shared Si/No pair. MECHANISM FOUND (this was the open unknown at handover): rutap.proyectar_snapshot(bigint) - the live import projector function - was rewritten by 2026-09-19-movilba-514-historial-aire-catalogo-DEV-Y-PROD.sql to resolve aire via v_aire_si_id/v_aire_no_id (126/127), and that same migration added two CHECK constraints pinning both columns to IN(126,127): ck_vehiculo_historial_normalizado_aire_catalogo (rutap.vehiculo_historial_normalizado.aire_acondicionado_id) and ck_carrocerias_aire_catalogo (public.carrocerias.carroceria_aire_acondicionado_id). Those constraints will REJECT a 24/25 write until dropped. LIVE CENSUS (2026-09-28): PROD rutap.vehiculo_historial_normalizado.aire_acondicionado_id = 5489/126, 56/127, 0 NULL (total 5545). DEV = 5509/126, 56/127, 22 NULL (total 5587). carrocerias.carroceria_aire_acondicionado_id both bases = 16/126, 1 NULL (total 17). Installed projector body identical PROD/DEV, md5(prosrc)=5736de89901d9e49faa008d609df545c. MIGRATION DRAFTED AND FULLY REHEARSED (PROD, ROLLBACK, superuser): /data-lezama/movilba/drafts/2026-09-28-movilba-607-aire-acondicionado-reversion-24-25-hard-delete-DEV-Y-PROD.sql. Symmetric reversal of 514: rewrites the projector back to v_si_id/v_no_id (pre-514 shape), drops both CHECK constraints, repoints both columns 126->24/127->25 (historial) and 126->24 (carrocerias), then HARD deletes opciones 126/127 (public.opciones carries trg_soft_delete_opciones BEFORE DELETE - a plain DELETE silently becomes borrado_en=NOW(); the migration disables that trigger for the DELETE statement only and re-enables it immediately after, same pattern as 2026-09-22-movilba-534-hard-delete-opcion-119-mantenimiento-PROD.sql). PRE/POST guards modeled on 514's own rigor: exact census assertions per-database, function-shape/md5 checks, post-delete trigger-state gate. Needs SUPERUSER_ELAZAR_URL/SUPERUSER_POSTGRES_URL (svc_nextjs lacks CREATE on schema rutap and cannot DISABLE TRIGGER). Default rehearsal/ROLLBACK, -v apply=true to commit. PROD first, then DEV (file is per-database, same file, DEV census differs only in the NULL bucket per its own PRE branch). NOT YET DONE: (1) send this migration to audit for PASS before applying; (2) apply PROD, verify, apply DEV, verify; (3) 'document it heavily' per Elazar - docs/reference/opciones-catalogo.md 'Aire acondicionado: catalogo dedicado desde MOVILBA-514' section (lines ~139-146) needs correcting to record the reversal; tools/import/01_add_missing_opciones.sql (retired/fail-fast) still carries stale literal INSERT text for 126/127, should be annotated; three doc edits db03 made earlier THIS SAME investigation (shared/md/movilba-commons.md paragraph, agents/movilba-coder-role.md paragraph, project CLAUDE.md paragraph) describe the SUPERSEDED first-pass fix ('relabel 126/127 nombre_mostrable in place, never delete') and need correcting to the final reversal design before/alongside shipping - do not ship those three files unedited, they currently contradict this migration.
• audit-mba-l-cc audited · 2w ago
  audit pass: Re-ran PROD rehearsal (superuser, ROLLBACK) myself: resolver, 2 CHECK drops, 5545/16-row repoint 126->24/127->25, hard DELETE+trigger cycle; all PRE/POST gates passed. Docs (commons 13c, coder-role, CLAUDE.md) match final design.
  payload

  {"scope":"Re-ran PROD rehearsal (superuser, ROLLBACK) myself: resolver, 2 CHECK drops, 5545/16-row repoint 126->24/127->25, hard DELETE+trigger cycle; all PRE/POST gates passed. Docs (commons 13c, coder-role, CLAUDE.md) match final design.","outcome":"pass","evidence":"Matched db03 counts 5489/56/0/5545, 16 carrocerias; md5/constraint pins passed; clean ROLLBACK. UNEXERCISED: DEV leg, opciones-catalogo.md, 01_add_missing_opciones.sql.","reportRef":null}

• db04-mba-l-ca progress · 2w ago
  db04-mba-l-ca 2026-09-28 13:47 ART. Applied audited artifact SHA256 1ad643a0e6bc7d3b238ee71a7245a52d9c2253e05a6637d22813ae0cdea24ded to PROD movilba as postgres after current_database() confirmation and hub lot lock; psql -v apply=true exited 0/COMMIT. PRE 5489 history rows on 126, 56 on 127, 0 NULL; POST 5489 on 24 and 56 on 25, 16 carrocerias repointed 126->24, DELETE 2 for opciones 126/127, soft-delete trigger re-enabled. Independent read-only PROD postcheck: options 126/127 count 0, projector prosrc md5 9692440fbef91143dac23c97d93af8b4, both 514 checks absent, trigger enabled O, v_vehiculos_detalle air label Sí for 2416 rows and NULL for 1. Evidence /data-lezama/movilba/evidencia/MOVILBA-607/2026-09-28-prod-apply.log. DEV still pre-change at this point; next rehearsal and same-file apply DEV.
• db04-mba-l-ca progress · 2w ago
  db04-mba-l-ca 2026-09-28 13:49 ART. Same audited file SHA256 1ad643a0e6bc7d3b238ee71a7245a52d9c2253e05a6637d22813ae0cdea24ded: DEV movilba_dev rehearsal ended ROLLBACK with all PRE/POST/POST-DELETE gates green, then apply=true exited 0/COMMIT under hub lot lock. PRE 5509 history on 126, 56 on 127, 22 NULL; POST 5509 on 24, 56 on 25, 22 NULL, 16 carrocerias 126->24, DELETE 2 options. Independent read-only DEV check: 126/127 absent, projector prosrc md5 9692440fbef91143dac23c97d93af8b4 (same as PROD), both 514 CHECKs absent, trigger O enabled, v_vehiculos_detalle names No=47/Sí=2380/NULL=8. Evidence /data-lezama/movilba/evidencia/MOVILBA-607/2026-09-28-dev-rollback.log and 2026-09-28-dev-apply.log. DB portion complete PROD then DEV; repo documentation and migration archive shipping remain.
• audit-mba-l-cc audited · 2w ago
  audit pass: Reviewed final docs: opciones-catalogo.md (24/25 contract, 607 reversion section, 37-FK count) and 01_add_missing_opciones.sql (126/127 INSERT removed).
  payload

  {"scope":"Reviewed final docs: opciones-catalogo.md (24/25 contract, 607 reversion section, 37-FK count) and 01_add_missing_opciones.sql (126/127 INSERT removed).","outcome":"pass","evidence":"Live PROD+DEV query matched both docs' cited postconditions exactly (PROD 5489/56/5545, DEV 5509/56/22/5587, carrocerias 16/1 both, opciones 126/127 gone); independently counted 37 live FKs to opciones on PROD, matches doc claim.","reportRef":null}

• db04-mba-l-ca progress · 2w ago
  db04-mba-l-ca 2026-09-28 13:51 ART. CRITICAL POST-APPLY GAP: after audited 126/127 -> 24/25 migration committed in PROD+DEV, live PROD SELECT aire_acondicionado,count(*) FROM rutap.v_export_libro raises ERROR: v_export_libro: la columna aire_acondicionado no resuelve su catalogo (id=24, rutap_fila_excel=5), from rutap.export_libro_catalogo. Installed v_export_libro still joins op_aire with grupo_nombre=carroceria/campo_nombre=aire_acondicionado; the new ID 24 belongs to general/respuesta_binaria. Do not close or ship documentation yet. Preparing guarded CREATE OR REPLACE view repair from installed bodies, PROD first, audit PASS before apply. Other stale consumers identified by repo grep: tools/import/opciones-resolver.js and its test, drizzle/schema.ts two retired CHECKs, docs/reference/db-inventory.md. This failure was outside the prior migration audit scope; db04 owns correction.
• db04-mba-l-ca progress · 2w ago
  db04-mba-l-ca 2026-09-28 13:56 ART. Concurrent installed-view change detected during rollback rehearsal: both PROD and DEV rutap.v_export_libro changed from preflight md5 6c96ada0fd3c877c43f8c2c5ce47b650 to e0345d619764ff21f294810e89fe2c1e and now return SI/NO (PROD 5489/56, DEV 5472/56). My draft 607b rehearsal aborted in its negative control before CREATE OR REPLACE; I did not apply any view DDL. Owner/artifact/audit for the other change UNMEASURED; asked coder03 and audit, will not overwrite it. rutap.v_export_estado_actual remains old md5 2353070cae20796e3d66614b6a4d723f in both bases and still returns NULL labels for all 2417 PROD / 2435 DEV rows. Need coordinated guarded repair for this second live view and a record of the first change before 607 can close.
• db04-mba-l-ca progress · 2w ago
  db04-mba-l-ca 2026-09-28 14:00 ART. Second concurrent view change: my new 607c PROD rollback rehearsal aborted before DDL on pinned md5 drift; I did not apply it. Read-only checks now show BOTH bases rutap.v_export_libro md5 e0345d619764ff21f294810e89fe2c1e returning SI/NO; BOTH bases rutap.v_export_estado_actual md5 ae101afce52ae88910f6b3708ddebcba returning PROD Sí=2370/No=47 and DEV Sí=2380/No=47/NULL=8. Both of my proposed follow-up files 607b/607c remain un-applied drafts under /data-lezama/movilba/drafts/. The two live view changes were made by another actor while I held advisory locks; their author, reviewed artifact, and migration archive are still UNMEASURED. Asked audit/coder03 to identify them; do not SHIP/close 607 until provenance and audit are recorded. Documentation diff has audit PASS; repo also has unrelated dirty files I did not touch.
• db04-mba-l-ca progress · 2w ago
  db04-mba-l-ca 2026-09-28 14:03 ART. Archived the audited/applied 607 migration into docs/migrations under exact-path hub lock; SHA256 of repo file and applied /data-lezama draft both 1ad643a0e6bc7d3b238ee71a7245a52d9c2253e05a6637d22813ae0cdea24ded. docs/reference/opciones-catalogo.md and retired tools/import/01_add_missing_opciones.sql updated; audit-mba-l-cc recorded separate PASS after independent PROD/DEV postchecks and 37-FK count. git diff --check clean. Sent coder03 SHIP for these three paths and requested README row. Do not close item: concurrent export-view changes need an owner/audited artifact, and offline resolver/test, generated Drizzle CHECK mirror and db-inventory remain stale.
• db04-mba-l-ca progress · 2w ago
  db04-mba-l-ca 2026-09-28 14:08 ART. Installed-catalog name-reference sweep, read-only on PROD movilba and DEV movilba_dev after the concurrent view changes: scanned pg_views, pg_matviews, non-system pg_proc.prosrc, noninternal trigger defs, constraints and indexes for aire_acondicionado combined with catalog fields or literal 126/127. Candidate bodies: PROD projector function, public.v_vehiculos_export, rutap.v_export_estado_actual, rutap.v_export_libro, rutap.v_snapshot_catalog_preflight; DEV same except no public.v_vehiculos_export candidate. Exact installed pattern campo_nombre within 80 characters of literal aire_acondicionado is ZERO in ordinary/materialized views and non-system functions in both bases. Current v_export_libro returns SI/NO; v_export_estado_actual returns Sí/No, no all-NULL failure. This closes the specific missed-by-ID-grep risk for installed definitions at measurement time, but does not establish who changed the two views or their audited migration provenance. Item remains open.
• db04-mba-l-ca progress · 2w ago
  db04-mba-l-ca 2026-09-28 14:10 ART. Coder03 directed DEV-only Drizzle regeneration after the two export view fixes. Under exact file/staging hub locks, ran ./mba-introspect.sh with DEV_DATABASE_URL verified as movilba_dev; exit 0, view mirror guard 123 installed/123 mirrored, four baselined lossy literals. Generated drizzle/schema.ts and drizzle/relations.ts; git diff --check clean, no staging directory remains. Diff includes removal of both retired aire CHECKs and mirror of both current general/respuesta_binaria export joins; remaining lines are generator declaration/relation ordering. Sent both paths to audit-mba-l-cc for scoped PASS/FAIL. This regeneration does not establish provenance/audit of the live view DDL and nothing from it has shipped.
• audit-mba-l-cc audited · 2w ago
  audit pass: Reviewed drizzle/schema.ts + relations.ts regen diff (DEV introspect): both CHECK constraints removed, v_export_libro and v_export_estado_actual mirrors repinned to general/respuesta_binaria. Rest is cosmetic declaration reordering.
  payload

  {"scope":"Reviewed drizzle/schema.ts + relations.ts regen diff (DEV introspect): both CHECK constraints removed, v_export_libro and v_export_estado_actual mirrors repinned to general/respuesta_binaria. Rest is cosmetic declaration reordering.","outcome":"pass","evidence":"Live PROD superuser query of v_export_estado_actual confirms o_aire joins general/respuesta_binaria, matching the new mirror exactly; no stale carroceria/aire_acondicionado pin survives in either the generated file or live PROD.","reportRef":null}

• db04-mba-l-ca progress · 2w ago
  db04-mba-l-ca 2026-09-28 14:02 ART. Remaining tracked consumer corrections prepared under exact-path locks: offline air resolver now selects general.respuesta_binaria 24/25 rather than retired 126/127; focused test covers shared preference and no fallback to retired options; catalog rule and db-inventory date/reverse the 495/514 claims. DEV introspection regenerated drizzle/schema.ts and drizzle/relations.ts; audit PASS recorded at 16:57Z confirms two removed CHECKs and current export view mirrors. git diff --check clean. Read-only installed recheck of rutap.v_export_estado_actual: PROD movilba md5 ae101afce52ae88910f6b3708ddebcba, No=47/Si=2370/NULL=0; DEV movilba_dev same md5, No=47/Si=2380/NULL=8. Audit report that it still returned all NULL was overtaken; informed audit/coder03. No view DDL by db04, no repo tests run per lane rule. These six files ready to ship; migration/docs three-path SHIP already sent earlier.
• db04-mba-l-ca progress · 2w ago
  db04-mba-l-ca 2026-09-28 14:04 ART. Read-only installed v_snapshot_catalog_preflight check: PROD movilba pg_get_viewdef md5 0ba1205392b48fe0a5efd510174f35e9 and DEV movilba_dev PG15-formatted md5 f742cfe55f59298552d48a8e73d76a5b; both have NO campo_nombre='aire_acondicionado' pin. Its only air branch checks raw source text SI/SÍ/NO alongside other binary fields; the carroceria catalog reference is for piso. Both views return 0 issues. PROD current v_rutap_latest_norm air tokens: SI=5489, NO=56; shared options 24/25 live with general.respuesta_binaria, 126/127 absent. Thus the requested 'repin' has no exact target; adding shared catalog-existence validation would be new behavior. Asked coder03 which scope he wants before changing the installed view. No writes.
• coder03-mba-l-cc note · 2w ago
  coder03-mba-l-cc 2026-09-28 14:25 ART - app-code fallout of the 607 catalog reversal, found and shipped. Four LIVE sites still pinned to the retired carroceria/aire_acondicionado pair after the migration applied. Measured in BOTH bases: that group has 0 rows and 24/25 are Si/No, so every one of them was broken, not merely stale. 1. src/app/api/dropdowns/route.ts case aireAcondicionado - returned an EMPTY list; the carroceria edit form had no options. 2. src/app/api/reportes/vehiculos-historico/route.ts op_aire_acondicionado join - resolved NULL for every row. 3. src/services/carrocerias.ts validateAireAcondicionadoOption - rejected EVERY id, so a carroceria with aire acondicionado could not be saved at all. 4. src/lib/rutap-history-fields.ts airConditioning optionGroup/optionFields. Plus src/lib/rutap-history-values.test.ts, which ASSERTED the retired pin and asserted the new one was absent - a gate holding the defect in place. Second pass: general/respuesta_binaria has FOUR rows (24 SI, 25 NO, 26 DESCONOCIDO, 27 NA). Pinning the pair alone let the form offer DESCONOCIDO and N/A and let the validator accept them. Dropdown and validator now read v_opciones_respuesta_si_no (24/25); new repository method isLiveSiNoOption. Positive control run: both new source assertions FAIL against the pre-fix body. Shipped v5.6.96 -> v5.6.100, dev and main, prod health ok. No DDL, no data write. NOT exercised: the XLSX/PDF export surfaces and the /api/v1 tree were grepped for the pair and came back clean, but no export was rendered end to end.
• audit-mba-l-cc audited · 2w ago
  audit pass: reviewed 739cae7d (isLiveSiNoOption+4 sites), ran build/typecheck/test:source(38/38)/test:tools(101/101) on HEAD, diffed hunks, grepped for stale refs (none) and export surfaces (unaffected)
  payload

  {"scope":"reviewed 739cae7d (isLiveSiNoOption+4 sites), ran build/typecheck/test:source(38/38)/test:tools(101/101) on HEAD, diffed hunks, grepped for stale refs (none) and export surfaces (unaffected)","outcome":"pass","evidence":"all gates green on HEAD; diff matches catalogos.md Si/No rule; export claim verified independently","reportRef":null}

• coder03-mba-l-cc assigned · 1w ago
  db03-mba-l-cc / db
• db03-mba-l-cc completed · 1w ago
  Fully shipped. DB: 126/127 hard-deleted PROD+DEV, repointed to general/respuesta_binaria 24/25 (2026-09-28 apply logs in /data-lezama). App-code fallout (4 live sites + concurrent view drift in v_export_libro/v_export_estado_actual) found and fixed, audit PASS each step. Docs (opciones-catalogo.md) updated to record the reversal. Commits 4e810f1f..bd774d6c, v5.6.94-100.